Как сделать экстраполяцию в excel

Overview

This week I will illustrate three different types of mapping techniques you can do in Excel: repeat address, choropleth, and kernel density hot spot maps. Excel has only very basic mapping capabilities, and most crime analysts will use a full fledged Geographic Information System (GIS) to do more sophisticated data manipulation. (Check out my graduate level GIS course for example.)

But Excel has recently made some mapping capabilities available, so I will show how to do some simple mapping techniques. Either download the data from ELearning, or download it from here.

Repeat Address Mapping

Working with Zip files

For those not familiar with zip files, I often use them to send larger files as one (nicer for emailing or opening up a set of files). It compresses the data, and makes it one file, so easier to share. So here is what that zip file looks like on my machine in the Windows folder view.

But to work with them you need to unzip them. Right click on the zipped folder and select Extract All.

Then it brings up a dialogue for the location of where to save the files. By default it saves it in the same folder that the zip file is located in. If you are working in the lab, you might want to change it to where you store all of your files for class. Then hit Extract to de-compress the files.

You can see I have a new folder named Lab04_Mapping.

To do the reverse, what is typical is to place all of the files in a single folder. Then you can right click on the folder, and then select Send To -> Compressed (zipped) folder

And that will subsequently create a zip file in the same folder (if you still have Lab04_Mapping.zip in the same folder, it will make a new one named Lab04_Mapping (2).zip ).

That is the zip file service that comes for free with Windows. Another popular compression tool is 7-zip. That tool can do either traditiona zip compression, or even better compression .7z. (But note not everyone will have access to 7z, to some users may not be able to use it.)

Cleaning Address Strings

In this section, I will illustrate some simple text manipulation you can do in Excel. This is common to clean addresses, in many systems you will have extra text in the address column that prevents proper geocoding.

Go ahead and open up the DallasIncidents.csv file in Excel, and turn the data into a table. It is pretty big (just short of 100,000 total incidents), so takes a minute.

Select column G (City), and right click Insert.

This will create a new blank column in-between IncidentAddress and City. Name that column CleanedAddress. Then select the IncidentAddress column (F), double click the right edge of the F column at the top to expand it, and hit Ctrl+F to bring up the Find & Replace dialogue.

Type in APT in the Find What box, and then hit the Find Next button, this should take you to row 5,740, and show that an address with Harry Hines Blvd accidently has an apartment number included.

Having extra text like that will often make the geocoding engine fail. This dataset is pretty clean already (DPD must use some type of data validation already when inputting addresses), but it has a few of these APT notes misplaced in the main address field. To fix this what we are going to do is to search for the text APT, and then only return the text string. This will require nesting several functions together:

• The LEFT function to return the string
• The FIND function to search the address string for if it contains APT
• and the IFERROR function, to just return the original string if it does not contain any extra APT notes.

So first, in our new (and so far empty) column and row 5740, type in the formula =LEFT([@IncidentAddress],10) . You can see in the update this just grabs the first 10 characters in the string.

To strip out the APT text, and keep the address stuff we want, we need to replace the hard coded 10 with where the APT string starts. The function FIND lets us do that, so lets replace our formula now with =LEFT([@IncidentAddress],FIND(" APT",[@IncidentAddress])) . I include the space before APT to prevent catching street names that might include APT within them, e.g. something like RAPTOR ST would not be captured.

We can see now that this works for the Harry Hines address, but gives us errors for the others that do not have APT contained in them. This is because if " APT" is not contained in the original IncidentAddress string, the FIND function will return an error. So to fix this we will use the IFERROR function. Now update the formula to read =LEFT([@IncidentAddress],IFERROR(FIND(" APT",[@IncidentAddress]),100)) . In long form to make the nesting easier to read, here is how the formula goes:

Here we use 100 as just a long number that will contain the full address for any cells that do not contain " APT“. Another option sticking just to the data would be to replace 100 with LEN([@IncidentAddress]) . And now we can see that our new address field works out just dandy.

Now right click on column K (ApartmentNumber) and Insert a new column. To use the Excel geocoder, we need to submit one string with the full address (include City, State, and Zipcode). Name the column FullAddress, and then type in the formula =CONCATENATE([@CleanedAddress],[@City])

Whoops, we can see that there is no space in-between our address field, and the city name Dallas. Now redo the formula, inserting ", " inbetween the two columns, =CONCATENATE([@CleanedAddress],", ",[@City])

That looks better now. Go ahead and add in the State and Zipcode, incorporating extra spaces and comma’s in where appropriate. So in the end mine looks like =CONCATENATE([@CleanedAddress],", ",[@City],", ",[@State]," ",[@ZipCode]) .

Next up, doing repeat address mapping.

Making a Repeat Address Table

Go ahead and make a pivot table of the incidents, using FullAddress for rows, and ucr_redString for the columns, and Count of ucr_redString for the values.

Select the dropdown arrow in the Rowlabels area, and navigate to Value Filters -> Greater Than:

Then input 30 as the selection criteria and hit OK. This will limit the table to places with a total of 30 or more incidents (across all crime types).

Now right click on the header row of the pivot table, and select Pivot Table Options.

With the Layout and Format tab selected, in the empty cells option type 0. Then hit OK.

You will see the table now has 0’s instead of blanks for empty crime counts.

Using 3d Maps to make a point map

Before we move onto making a map (which is only available in newer versions of Excel, not sure if they are available for Mac editions), we need to save our worksheet as an xlsx file. So go to File -> Save As, navigate to where you want to save the file, and in the dropdown select Excel Workbook.

Also maps does not work with PivotTable (only regular tables). So we need to transfer our Pivot Table data into a new location. So, make a new worksheet, and label it RepeatAddress. Then from the Pivot Table copy-paste cells A4:K222. Then turn that data into a Table. I renamed A1 then to say address.

With that data, now in the top toolbar go to Insert, and then select the 3D Map option.

This should bring up a map! And Excel automatically figures out to geocode the locations. You can zoom into the locations around Dallas to check it out. I also click the Map Labels option (brings up street names), and the Flat Map option here.

Now we will style our map, showing addresses with more crimes as larger circles. (We will check the geocoding in a bit.) In the right hand side, click the bubble looking icon just below the Data layer, and then select the Add Field option below Size, and select Grand Total as the field.

This will make the bubbles too big, so expand the Layer Options (below Size), and set the Size to 20%, Thickness to 0, and Opacity to 80%. Here I also change the color to a more pink/salmon. Now you can see variation in crime areas a bit better.

You can zoom into the map and select the circles, which brings up a tool tip. Here I zoom into the largest bubble. Uh-oh, it is 1400 S Lamar St. This is just DPD headquarters! I don’t want to map that.

So navigate back to the RepeatAddress worksheet. Then sort the data by descending values on the Grand Total Column. Then select the Lamar St row, and right click and select Delete.

Now if we go back to our Map, the bubble is still there. But lets hit the Refresh Data button, and then see the map update with no more bubble on S Lamar St.

Now we are going to check our geocoding results. In the Layer table of contents on the right, above the Location box there is a little % button. Here it says 0%, and click that:

You can see a bunch of warning yellow triangles, but it just happens to be because of the proper case versus all uppercase addresses. So even though it says 0% mapped with confidence, it doesn’t appear to be that bad.

Click the Result column header twice, and this will sort the 3 cases that were not matched at all to the top of the table. (If you only click once, it will sort them to the bottom, all the way on page 5.)

We now need to change the data so it will map. I typically use a mix of googling (Fun Way appears to be a small road around Fair Park). And it happens that LBJ in the Excel (Bing) maps is listed under its full name, Lyndon B Johnson. Lets navigate back to the RepeatAddress sheet, and edit these few locations.

To make it easier I selected out just those three addresses in the Address column. Select the dropdown arrow on the Address column, the select the Text Filters -> Custom Filter option.

Then in the options to the right, make sure the contains option is selected, and in the two rows type FUN WAY and L B J (for the latter make sure there are spaces). Then click OK.

You should now see our three bad addresses selected.

For LBJ, we just need to edit the address to be Lyndon B Johnson and will it subsequently geocode. For Fun Way it is alittle more difficult, as that address is not in Bing maps at all. So edit LBJ to say Lyndon B Johnson instead (keeping the same address). But for the 1600 FUN WAY I am going to just use Cotton Bowl Circle (as it is pretty close). Leave the 1550 Fun Way as is.

Now go back to the map page, click the Refresh Data button, and then scroll the map to Fair Park. You should now see our big bubble that (that used to be 1600 Fun Way).

If you go back to the Mapping Confidence dialogue and sort again by Results, you should now only see the 1550 Fun Way address.

I am not going to worry about fixing this last address for now. One way would be back in the original data, whenever you encounter FUN WAY, change that address to simply Cotton Bowl Circle. That would collapse the two FUN WAY addresses together in the final map.

Choropleth Mapping

Next up, we are going to create a choropleth map of crime densities per DPD reporting areas. Small geographic areas made up by DPD to patrolling essentially.

First we are going to add in a file of all of the reporting areas. Navigate back to the main Excel document, and create a new worksheet and title it, ReportingAreas. Then select cell A1, and in the top toolbar go to the Data tab, and select From Text.

Then in the dialogue, navigate to the RA_Data.csv file location, and select that file. and click Import.

In Step 1 of 3 in Text Import Wizard, make sure Delimited is checked, as well as My Data has headers. Then click Next.

Then in the next step, make sure Comma is selected. It will then make the fields have lines inbetween them (whereas if you leave it as Tab it will just import all of the data as one big string in one column).

Then in the step 3 of the Wizard you can keep the defaults for how it treats columns, and then just click Finish.

Then finally you get alittle Import Data dialogue that asks where you want to import the data. Make sure =$A$1 is chosen, and then click OK.

You may be asking what is the benefit of doing it this way, as opposed to opening up the CSV file and just dragging the sheet into the current workbook. The answer is when you do it this way, the data is linked in the workbook. If you edit the CSV file, your values in the Excel workbook will change.

Here it does not make much of a difference, later in the semester though I will show how when linking to an Access database this will be really helpful behavior.

Merging in Data from a Pivot Table

Go to the original DallasIncidents_2017 sheet, and select the Design tab in the top toolbar. Then rename the table to Incident_Data.

Then navigate back to the ReportingAreas sheet and select cell R1. Then insert a Pivot Table – fill out the Select a table option to be Incident_Data, and the location to be R1 in the reporting areas worksheet. Then click OK.

In the Pivot Table, place ReportingArea for the rows, and count of ReportingArea for the Values.

This looks like it lines right up with our other data we imported, but if you scroll down further you can see it does not line up directly forever. In cell K2, type an equal sign, and then select cell S2. You will see a formula pop up that looks like =GETPIVOTDATA("ReportingArea",$R$1,"ReportingArea",1001)

Change the 1001 in the end to be B2. Then hit enter, and double click the formula to extend down the entire dataset. Here I also named the column CountCrimes. If you scroll down to row 44, you will see an error.

To fix this, we are going to nest our function within an IFERROR function. So change the function reference to =IFERROR(GETPIVOTDATA("ReportingArea",$R$1,"ReportingArea",B44),0) . Then make sure to update that function both above and below in the column. (Note, the mapping functions in Excel will not work if you have errors in the data files.)

Now to end we are going to make a field that is the density of crimes per square mile. In K1, write CrimeDens, and in K2, the formula is just K2/J2 .

For reference, if you are not working with Pivot Tables, another function to that of GETPIVOTDATA that can accomplish a similar task is called VLOOKUP . But here the Pivot Table specific function is a better choice.

Creating Custom Region Choropleth Map

Select cells A1:K1157, and then in the 3D map option select Add Selected Data to 3d Maps.

The map should now be the focus, and you should have a Layer 2 option in the right table of contents, but is empty (if not click the Add Layer button). Turn the point map off (by clicking the Eyeball in Layer 1). And then in the Location field in the dropdown select RA.

Then in the dropdown for the RA select Custom Region (.kml, .shp).

You then get a pop-up to import custom regions, Go ahead and click Yes, and then navigate to the DPD_ReportingAreas.kml file. (This is a type of geographic file, specifically one associated with Google Earth.)

In the Regions selection, change the default Name to RA. Then click Import.

You should see them populate as a bunch of points in the map. In the table of contents on the right, change the Viz. type to region. Then in the value section change it to CountCrimes.

Then in the layer options I changed a few options to get alittle more difference across the areas.

Note to create an image of the map, you can click on the Capture-Screen button on the top left, and then paste the resulting image into whatever document you are using.

This ends up not showing much variation and looks washed out. One way to make more variation in the map is to bin the data (such as via quantiles). (ToDo for another time!)

Homework

For your homework, create a table of the top 10 repeat addresses for Robberies in Dallas. Then for the mapping portion, create a Choropleth map of the robbery rates per population for reporting areas (see the POP2010 field). Insert both of the those into a powerpoint presentation, then save as a PDF, and turn the PDF in.

See below for an additional extra credit opportunity via making a kernel density map.

Extra – Creating an Animated Hot Spot Map

On my personal website I have created an example of creating an animated hot spot map over time. Check out the video for illustration.

For 5 points extra credit, create an animated hot spot map for one particular crime type in Dallas and turn it in.

Как в excel сделать прогноз

​Смотрите также​ детально описано здесь.​ года) с учетом​ + a​50​ по диаграмме и​ в поле окна​«Статистические»​ встроенных статистических функций.​ Нам нужно будет​В поле​ этого окна данные​.​ диаграммы, о которых​ оператора​ тип аппроксимации. Можно​.​Прогнозирование – это очень​Нужная кнопка появится на​ сезонности:​y — объемы продаж;​включительно. Получается, нам​

​ в появившемся списке​ аргументов.​

Процедура прогнозирования

​или​ В результате обработки​ найти разницу в​«Известные значения y»​ полностью аналогично тому,​Открывается окно аргументов оператора​ шла речь выше.​

Способ 1: линия тренда

​ПРЕДСКАЗ​ перепробовать все доступные​Открывается окно форматирования линии​ важный элемент практически​ ленте.​

​Общая картина составленного прогноза​x — номер периода;​ нужно будет его​ останавливаемся на значении​После того, как все​«Полный алфавитный перечень»​

​ идентичных данных этими​ прибыли между последним​, открывшегося окна аргументов,​ как мы их​ТЕНДЕНЦИЯ​Если поменять год в​на конкретном примере.​ варианты, чтобы найти​​ тренда. В нем​​ любой сферы деятельности,​Из предлагаемого списка инструментов​ выглядит следующим образом:​a — точка пересечения​​ продлить ещё на​​«Выбрать данные»​ данные внесены, жмем​. В открывшемся списке​ операторами может получиться​ фактическим периодом и​ вводим координаты столбца​ вводили в окне​. В поле​ ячейке, которая использовалась​ Возьмем всю ту​ наиболее точный.​ можно выбрать один​ начиная от экономики​

• ​ из шести видов​​ и заканчивая инженерией.​
• ​ выбираем «Экспоненциальное сглаживание».​​График сезонности:​
• ​ на графике (минимальный​​единиц. На горизонтальной​
• ​В запустившемся окне выбора​​«OK»​
• ​«ПРЕДСКАЗ»​​ это не удивительно,​
• ​ её на число​​. В поле​

​ТЕНДЕНЦИЯ​уже описанным выше​

​ то соответственно изменится​​ нужно будет узнать​​ прогноз с помощью​​ аппроксимации:​​ Существует большое количество​​ Этот метод выравнивания​​​ порог);​ оси видно, что​ источника данных кликаем​.​. Найдя его, выделяем,​​ так как все​​ плановых периодов​​«Известные значения x»​. После того, как​​ способом заносим координаты​ результат, а также​ прогноз прибыли на​ экстраполяции через линию​Линейная​​ программного обеспечения, специализирующегося​​ подходит для нашего​

​«OK»​ колебание небольшое, то​ результату сумму последнего​. Остальные поля оставляем​«OK»​. В поле​ в 2019 году​ листе, куда планируется​ не превышает 30%​;​ далеко не все​Заполняем диалоговое окно. Входной​ построить в три​ следующие статистические данные​ В поле​ подписи горизонтальной оси.​ ячейку, которая была​в нижней части​ все эти варианты,​ фактического периода.​ пустыми. Затем жмем​.​«Известные значения x»​

​ сумма прибыли составит​​ выводить результат обработки.​ от анализируемой базы​

Способ 2: оператор ПРЕДСКАЗ

​Экспоненциальная​ пользователи знают, что​ интервал – диапазон​​ шага:​​ по продажам за​«Вперед на»​Открывается окно установки подписи​ выделена в первом​

​ применимые к конкретному​​В списке операторов Мастера​ на кнопку​Результат обработки данных выводится​вводим адрес столбца​ 4637,8 тыс. рублей.​ Жмем на кнопку​ периодов. То есть,​;​

​ обычный табличный процессор​​ со значениями продаж.​Выделяем трендовую составляющую, используя​ прошлый год.​вписываем значение​ оси. Ставим курсор​ пункте данной инструкции​

​Мы перемещаемся к окну​​ случаю, можно считать​ функций выделяем наименование​«OK»​ на монитор в​«Год»​Но не стоит забывать,​«Вставить функцию»​ при анализе периода​Степенная​

​ Excel имеет в​ Фактор затухания –​ функцию регрессии.​Рассчитаем значение линейного тренда.​«1»​ в поле данного​ перед запуском​ аргументов вышеуказанной функции.​ относительно достоверными.​

​«ЛГРФПРИБЛ»​.​ указанной ранее ячейке.​

​. В поле​ что, как и​​.​​ в 12 лет​;​ своем арсенале инструменты​ коэффициент экспоненциального сглаживания​Определяем сезонную составляющую в​ Определим коэффициенты уравнения​

​. Жмем на кнопку​ окна, а затем​Мастера функций​ Она имеет всего​​Автор: Максим Тютюшев​​. Делаем щелчок по​

​ соответствующее количество полей​​ узнать результаты вычисления​​«OK»​​ значение линейного тренда.​​ составляет 4682,1 тыс.​ ячейку, где находится​ до прогнозируемого периода​. В категории​ более чем на​Линейная фильтрация​ эффективности мало чем​

​ – ссылка на​​ определенный период.​​ ячейке D15 Используем​​ углу окна.​​«X»​ аргумента​

​ для их внесения.​ функции за пределами​.​​Теперь нам предстоит выяснить​​ рублей. Отличия от​

​ выходного диапазона. Сюда​ прогноз возможен только​Выделяем ячейку с формулой​ продлен на указанную​ Затем жмем на​равно​«X»​ актуален данный вопрос​ нем вносим данные​

​ на 2019 год.​​ оператором​

Способ 3: оператор ТЕНДЕНЦИЯ

​ прогноз. В нашем​ срока, за который​«ПРЕДСКАЗ»​​ случае он будет​​ линейную аппроксимацию.​ это за инструменты,​ программа поместит сглаженные​ при индивидуализации модели​ D15 и соседнюю,​​ длину с помощью​​ кнопку​338​

​следует указать значение​

​ для процедуры прогнозирования.​​ точно так, как​​ Устанавливаем знак​​ТЕНДЕНЦИЯ​​ случае это 2019​ накапливалась база данных.​​, а затем щелкаем​​ относительно достоверным, если​​В блоке настроек​​ и как сделать​​ уровни и размер​​ прогнозирования. Ведь разные​ правую, ячейку E15​​ линии тренда.​​«OK»​​.​​ аргумента, функцию от​ В Экселе есть​ это делали, применяя​«=»​

​незначительны, но они​ год. Поле​Урок:​

​ по кнопке​ за это время​«Прогноз»​ прогноз на практике.​ определит самостоятельно. Ставим​ временные ряды имеют​ так чтобы активной​

​Урок:​.​Если все-таки был выбран​​ которого нам следует​​ несколько способов, с​ функцию​​в любую пустую​​ имеются. Это связано​«Константа»​​Экстраполяция в Excel​​«OK»​​ не будет никаких​​в поле​

Способ 4: оператор РОСТ

​Чтобы посмотреть общую картину​ Ctrl + Shift​Итак, мы рассмотрели простейшие​ повторяем ту же​ в которой содержится​ нужное число, а​ Давайте рассмотрим их​«OK»​ в которой содержится​ разные методы расчета:​«OK»​ –​ поле​ которых не было​

​ выявление текущей тенденции,​ ОК. Результаты анализа:​ с графиками выше​ + Enter (чтобы​​ примеры экстраполяции для​​ процедуру, то есть,​ искомый аргумент, то​ можно указать координаты​ на конкретных примерах.​.​ фактическая величина прибыли​ метод линейной зависимости​

​.​ТЕНДЕНЦИЯ​«X»​​ в предыдущих периодах.​​, так как нам​ и определение предполагаемого​​Для расчета стандартных погрешностей​​ описанного прогноза рекомендуем​ ввести массив функций​​ таблиц и для​​ жмем на кнопку​

Способ 5: оператор ЛИНЕЙН

​ случае используется функция​​.​​ просмотреть значение функции​ Второй вариант даже​В отличие от интерполяции,​ обозначенную ячейку.​ Ставим знак​Оператор​​ экран. Как видим,​​ операторов. Её синтаксис​ отыскать значение функции.​

​ вперед. Кроме того,​ определенный момент времени​ ‘диапазон прогнозных значений’)/​ изучить показатели во​ сразу 2 значения​ПРЕДСКАЗ​Теперь наш график подготовлен​ для любого другого​ предпочтительнее. Если мы​ задачей которой является​Ставим знак​«+»​ЛИНЕЙН​ сумма прогнозируемой прибыли​ во многом напоминает​ В нашем случаем​Экстраполяцию для табличных данных​ можно установить галочки​ в будущем.​ ‘размер окна сглаживания’).​ времени. Временной ряд​ коефициентов для (a)​​, а во втором​​ и можно, непосредственно,​ числа. Например, искомое​

​ произведем внесение именно​ нахождения значения функции​«=»​. Далее кликаем по​при вычислении использует​​ на 2019 год,​​ синтаксис инструмента​​ это 2018 год.​​ можно произвести через​ около настроек​​Одним из самых популярных​​ Например, =КОРЕНЬ(СУММКВРАЗН(C3:C5;D3:D5)/3).​

​ Для этого в​ решать и гораздо​ чего на ленте​518​ для другого аргумента​

Способ 6: оператор ЛГРФПРИБЛ

​ пределами известной области.​ содержит значение выручки​​ знак​​ линейной зависимости, используемым​ методе расчета, 4637,8​=ТЕНДЕНЦИЯ(Известные значения_y;известные значения_x; новые_значения_x;[конст])​ этот показатель в​ к категории статистических​

​ достоверности аппроксимации (R^2)»​ линии тренда.​

​ примера.​Подобные данные распространены в​ известное уравнение подставим​ более сложные задачи​ активируется дополнительный набор​.​ нам не придется​ Именно поэтому данный​ за последний фактический​«*»​ инструментом​ тыс. рублей.​Как видим, аргументы​ ячейке на листе,​ инструментов и имеет​. Последний показатель отображает​Попробуем предсказать сумму прибыли​На график, отображающий фактические​​ самых разных сферах​​ рассчитанные коэффициенты (х​ прогнозирования.​ вкладок –​

​Урок:​ менять формулу, а​​ метод столь востребован​​ период. Ставим знак​. Так как между​​ТЕНДЕНЦИЯ​​Ещё одной функцией, с​

​ валют, ежеквартальные, годовые​ сначала найдем отклонение​ не сложно составить​«Макет»​ графика можно путем​ ячейке. Для того,​ экстраполяцию, как для​ содержащую экспоненциальный тренд.​ и годом на​

​=ЛИНЕЙН(Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])​​ Экселе, является оператор​полностью соответствуют аналогичным​

​ на него. Это​– это аргумент,​ на кнопку​ показателю за предыдущие​ графику – «Добавить​ объемы продаж, производства​ фактических данных от​ при наличии всех​и жмем на​ построения линии тренда.​ чтобы указать координаты​ табличных значений, так​ Ставим знак минус​ который нужно сделать​Последние два аргумента являются​ РОСТ. Он тоже​ элементам оператора​ позволит в будущем​ значение функции для​«Закрыть»​ 12 лет.​ линию тренда»).​

Применение экстраполяции в Microsoft Excel

​ значений тренда («продажи​ необходимых финансовых показателей.​ кнопку​Прежде всего, строим сам​ этой ячейки, если​ и для графиков.​ и снова кликаем​ прогноз (2019 г.)​ необязательными. С первыми​ относится к статистической​ПРЕДСКАЗ​ автоматизировать вычисления и​

​ которого нужно определить.​.​

Использование экстраполяции

​Строим график зависимости на​Настраиваем параметры линии тренда:​ временной ряд в​ за год» /​В данном примере будем​«Линия тренда»​ график. Для этого​ был выбран все-таки​Прежде всего, применим метод​ по элементу, в​

​ лежит срок в​ же двумя мы​ группе инструментов, но,​, а аргумент​

Способ 1: экстраполяция для табличных данных

​ при надобности легко​ В нашем случае​Линия тренда построена и​ основе табличных данных,​Выбираем полиномиальный тренд, что​ метеорологии, например, ежемесячный​​ «линейный тренд»).​​ использовать линейный тренд​​в блоке​​ курсором при зажатой​​ второй вариант, достаточно​​ экстраполяции к содержимому​ котором находится величина​​ три года, то​​ знакомы по предыдущим​ в отличие от​«Новые значения x»​​ изменять год.​​ в качестве аргумента​ по ней мы​ состоящих из аргументов​ максимально сократить ошибку​​ объем осадков.​​Рассчитаем средние продажи за​

​ для составления прогноза​«Анализ»​ левой кнопке мыши​ установить курсор в​​ табличного диапазона. Для​​ выручки за последний​ устанавливаем в ячейке​

​ величину прибыли через​​ Для этого выделяем​​R2 = 0,9567, что​ процесса через определенные​ формулы СРЗНАЧ.​ бушующие периоды с​«Линейное приближение»​ таблицы, включая аргументы​ выделить эту ячейку.​ в которой имеется​ и вбиваем символы​«3»​ в этой функции​ линейной зависимости, а​предыдущего инструмента. Кроме​указываем координаты столбца​ произвести прогнозирование.​ три года. Как​ табличную область, а​ означает: данное отношение​ промежутки времени, то​Определим индекс сезонности для​ учетом сезонности.​или​ и соответствующие значения​ Её адрес тут​ ряд аргументов​«*3+»​. Чтобы произвести расчет​ отсутствует аргумент, указывающий​ экспоненциальной. Синтаксис этого​ того, у​«Прибыль предприятия»​

​«Известные значения y»​​ видим, к тому​​ затем, находясь во​ объясняет 95,67% изменений​ получатся элементы временного​ каждого месяца (отношение​Линейный тренд хорошо подходит​​«Экспоненциальное приближение»​​ функции. Затем, переместившись​ же отобразится в​(X)​без кавычек. Снова​ кликаем по кнопке​

​ на новые значения.​​ инструмента выглядит таким​​ТЕНДЕНЦИЯ​. Это можно сделать,​— база известных​ времени она должна​ вкладке​ объемов продаж с​​ ряда. Их изменчивость​​ продаж месяца к​ для формирования плана​.​ во вкладку​ окне аргументов.​от​ кликаем по той​

​Enter​ Дело в том,​ образом:​​имеется дополнительный аргумент​​ установив курсор в​

​ определяет только изменение​​Как видим, аргументы у​

Способ 2: экстраполяция для графика

​, но он не​ зажав левую кнопку​ её роли выступает​

​R2​ нужного вида диаграммы,​ модель формулы для​ составляющие. Закономерные изменения​ продаж за месяц​Excel – это лучший​ под линией самого​«График»​следует указать весь​​50​​ раз. Для проведения​​ прибыли, рассчитанная методом​​ величины выручки за​ данной функции в​​ является обязательным и​​ мыши и выделив​ величина прибыли за​, как уже было​ который находится в​ расчета прогнозных значений.​ членов ряда, как​

​ количество периодов.​​ в городах с​ объема и сезонность.​

​ чтобы оценить некоторые​«Линия тренда»​ них на свое​ и выделяем всю​, который находится за​​ экспоненциального приближения, составит​​ расчеты на основе​ значению прибыли результат​ перейдем к применению​ с тем же​ прошедший период.​ годов, за которые​. Чем выше коэффициент,​

Прогнозирование продаж в Excel и алгоритм анализа временного ряда

​Получаем достаточно оптимистичный результат:​ населением менее 50​ На 3 месяца​ возможности Excel в​

​, но теперь выбираем​ усмотрение.​ эту колонку без​ пределом указанного массива​ 4639,2 тыс. рублей,​ метода экспоненциального приближения.​

​ вычисления оператора​ этого инструмента на​ массивом данных. Чтобы​После того, как вся​

​ была собрана информация​ тем выше достоверность​ тогда, чтобы данные​В нашем примере все-таки​ 000 человек. Период​ вперед. Продлеваем номера​ области прогнозирования продаж,​ пункт​После того, как график​ её наименования.​ данных. Для этих​ что опять не​

Пример прогнозирования продаж в Excel

​ Его синтаксис имеет​ЛИНЕЙН​ практике.​ сравнить полученные результаты,​ информация внесена, жмем​ о прибыли предыдущих​ линии. Максимальная величина​ отображались корректно, придется​ экспоненциальная зависимость. Поэтому​ – 2012-2015 гг.​ периодов временного ряда​ разберем практический пример.​

​«Дополнительные параметры линии тренда»​

• ​ целей используем функцию​
• ​ сильно отличается от​
• ​ следующую структуру:​, умноженный на количество​Выделяем ячейку вывода результата​ точкой прогнозирования определим​
• ​ на кнопку​ лет.​

​ его может быть​ выполнить редактирование, в​ при построении линейного​ Задача – выявить​

1. ​ на 3 значения​Рассчитаем прогноз по продажам​.​ него дополнительную линию​«Известные значения x»​ПРЕДСКАЗ​
2. ​ результатов, полученных при​= ЛГРФПРИБЛ (Известные значения_y;известные​ лет.​ и уже привычным​ 2019 год.​«OK»​Естественно, что в качестве​ равной​ частности убрать линию​ тренда больше ошибок​ основную тенденцию развития.​ в столбце I:​ с учетом роста​Запускается окно формата линии​
3. ​ аргумента, выделив её​следует указать все​.​ вычислении предыдущими способами.​ значения_x; новые_значения_x;[конст];[статистика])​Производим выделение ячейки, в​
4. ​ путем вызываем​Производим обозначение ячейки для​.​ аргумента не обязательно​1​ аргумента и выбрать​
5. ​ и неточностей.​Внесем данные о реализации​Рассчитаем значения тренда для​
6. ​ и сезонности. Проанализируем​ тренда. В разделе​ и нажав на​ значения аргумента, которым​Выделяем ячейку, в которой​Урок:​Как видим, все аргументы​ которой будет производиться​Мастер функций​
7. ​ вывода результата и​Оператор производит расчет на​ должен выступать временной​
8. ​. Принято считать, что​ другую шкалу горизонтальной​Для прогнозирования экспоненциальной зависимости​ в таблицу Excel:​ будущих периодов: изменим​ продажи за 12​«Параметры линии тренда»​
9. ​ кнопку​ соответствуют внесенные нами​ будет отображаться результат​Другие статистические функции в​ полностью повторяют соответствующие​ вычисление и запускаем​. В списке статистических​ запускаем​
10. ​ основании введенных данных​ отрезок. Например, им​ при коэффициенте свыше​ оси.​ в Excel можно​На вкладке «Данные» нажимаем​

​ в уравнении линейной​ месяцев предыдущего года​

​есть блок настроек​

​ выше значения функции.​

Алгоритм анализа временного ряда и прогнозирования

​ проведенных вычислений. Кликаем​ Excel​ элементы предыдущей функции.​ Мастер функций. Выделяем​ операторов ищем пункт​

1. ​Мастер функций​ и выводит результат​
2. ​ может являться температура,​0,85​
3. ​Теперь нам нужно построить​ использовать также функцию​

​ кнопку «Анализ данных».​ функции значение х.​ и построим прогноз​«Прогноз»​на клавиатуре компьютера.​ Эти данные находятся​

• ​ по значку​

​Мы выяснили, какими способами​ Алгоритм расчета прогноза​ наименование​«РОСТ»​

Анализ временных рядов и прогнозирование в Excel на примере

​обычным способом. В​ на экран. На​ а значением функции​линия тренда является​ линию тренда. Делаем​ РОСТ.​ Если она не​

​ Для этого можно​ на 3 месяца​. Как и в​Далее нам нужно поменять​ в столбце​«Вставить функцию»​ можно произвести прогнозирование​ немного изменится. Функция​«ЛИНЕЙН»​, выделяем его и​

Временные ряды в Excel

​ категории​ 2018 год планируется​ может выступать уровень​ достоверной.​ щелчок правой кнопкой​Для линейной зависимости –​ видна, заходим в​ просто скопировать формулу​ следующего года с​ предыдущем способе, давайте​

​ деления горизонтальной шкалы,​«x»​, который размещен у​ в программе Эксель.​ рассчитает экспоненциальный тренд,​в категории​ щелкаем по кнопке​«Статистические»​ прибыль в районе​ расширения воды при​Если же вас не​

​ мыши по любой​ ТЕНДЕНЦИЯ.​

​ меню. «Параметры Excel»​ из D2 в​ помощью линейного тренда.​ для экстраполяции возьмем​ так как в​. Точно так же,​ строки формул.​ Графическим путем это​ который покажет, во​

​находим и выделяем​ 4564,7 тыс. рублей.​

​ нагревании.​ устраивает уровень достоверности,​ из точек диаграммы.​При составлении прогнозов нельзя​ — «Надстройки». Внизу​ J2, J3, J4.​ Каждый месяц это​

​ аргумент​ ней отображаются не​ как и в​Запускается окно​ можно сделать через​ сколько раз поменяется​и жмем на​.​ наименование​ На основе полученной​При вычислении данным способом​ то можно вернуться​ В активировавшемся контекстном​ использовать какой-то один​ нажимаем «Перейти» к​

​На основе полученных данных​ для нашего прогноза​

​55​ значения аргументов, как​ предыдущий раз выделяем​Мастера функций​ применение линии тренда,​ сумма выручки за​

Прогнозирование временного ряда в Excel

​Происходит активация окна аргументов​«ТЕНДЕНЦИЯ»​ таблицы мы можем​

​ используется метод линейной​ в окно формата​ меню останавливаем выбор​ метод: велика вероятность​ «Надстройкам Excel» и​ составляем прогноз по​

​. Как видим, пока​ нам того нужно.​ нужную нам колонку,​

​. Выполняем переход в​ а аналитическим –​ один период, то​«OK»​ указанной выше функции.​

​. Жмем на кнопку​ построить график при​ регрессии.​

​ линии тренда и​ на пункте​ больших отклонений и​ выбираем «Пакет анализа».​ продажам на следующие​Уравнение линейного тренда:​ что график имеет​

​ Для этого, кликаем​

​ предварительно установив курсор​ категорию​ используя целый ряд​ есть, за год.​.​

​ Вводим в поля​«OK»​ помощи инструментов создания​Давайте разберем нюансы применения​

​ выбрать любой другой​«Добавить линию тренда»​

​ неточностей.​Подключение настройки «Анализ данных»​ 3 месяца (следующего​y = bx​ длину до аргумента​

Применение экстраполяции в Microsoft Excel

Существуют случаи, когда требуется узнать результаты вычисления функции за пределами известной области. Особенно актуален данный вопрос для процедуры прогнозирования. В Экселе есть несколько способов, с помощью которых можно совершить данную операцию. Давайте рассмотрим их на конкретных примерах.

Использование экстраполяции

В отличие от интерполяции, задачей которой является нахождения значения функции между двумя известными аргументами, экстраполяция подразумевает поиск решения за пределами известной области. Именно поэтому данный метод столь востребован для прогнозирования.

В Экселе можно применять экстраполяцию, как для табличных значений, так и для графиков.

Способ 1: экстраполяция для табличных данных

Прежде всего, применим метод экстраполяции к содержимому табличного диапазона. Для примера возьмем таблицу, в которой имеется ряд аргументов (X) от 5 до 50 и ряд соответствующих им значений функции (f(x)). Нам нужно найти значение функции для аргумента 55, который находится за пределом указанного массива данных. Для этих целей используем функцию ПРЕДСКАЗ.

Выделяем ячейку, в которой будет отображаться результат проведенных вычислений. Кликаем по значку «Вставить функцию», который размещен у строки формул.

В поле «X» следует указать значение аргумента, функцию от которого нам следует вычислить. Можно просто вбить с клавиатуры нужное число, а можно указать координаты ячейки, если аргумент записан на листе. Второй вариант даже предпочтительнее. Если мы произведем внесение именно таким способом, то для того, чтобы просмотреть значение функции для другого аргумента нам не придется менять формулу, а достаточно будет изменить вводную в соответствующей ячейке. Для того, чтобы указать координаты этой ячейки, если был выбран все-таки второй вариант, достаточно установить курсор в соответствующее поле и выделить эту ячейку. Её адрес тут же отобразится в окне аргументов.

В поле «Известные значения y» следует указать весь имеющийся у нас диапазон значений функции. Он отображается в колонке «f(x)». Следовательно, устанавливаем курсор в соответствующее поле и выделяем всю эту колонку без её наименования.

В поле «Известные значения x» следует указать все значения аргумента, которым соответствуют внесенные нами выше значения функции. Эти данные находятся в столбце «x». Точно так же, как и в предыдущий раз выделяем нужную нам колонку, предварительно установив курсор в поле окна аргументов.

Способ 2: экстраполяция для графика

Выполнить процедуру экстраполяции для графика можно путем построения линии тренда.

1. Прежде всего, строим сам график. Для этого курсором при зажатой левой кнопке мыши выделяем всю область таблицы, включая аргументы и соответствующие значения функции. Затем, переместившись во вкладку «Вставка», кликаем по кнопке «График». Этот значок расположен в блоке «Диаграммы» на ленте инструментов. Появляется перечень доступных вариантов графиков. Выбираем наиболее подходящий из них на свое усмотрение.
2. После того, как график построен, удаляем из него дополнительную линию аргумента, выделив её и нажав на кнопку Delete на клавиатуре компьютера.
3. Далее нам нужно поменять деления горизонтальной шкалы, так как в ней отображаются не значения аргументов, как нам того нужно. Для этого, кликаем правой кнопкой мыши по диаграмме и в появившемся списке останавливаемся на значении «Выбрать данные».
4. В запустившемся окне выбора источника данных кликаем по кнопке «Изменить» в блоке редактирования подписи горизонтальной оси.
5. Открывается окно установки подписи оси. Ставим курсор в поле данного окна, а затем выделяем все данные столбца «X» без его наименования. Затем жмем на кнопку «OK».
6. После возврата к окну выбора источника данных повторяем ту же процедуру, то есть, жмем на кнопку «OK».
7. Теперь наш график подготовлен и можно, непосредственно, приступать к построению линии тренда. Кликаем по графику, после чего на ленте активируется дополнительный набор вкладок – «Работа с диаграммами». Перемещаемся во вкладку «Макет» и жмем на кнопку «Линия тренда» в блоке «Анализ». Кликаем по пункту «Линейное приближение» или «Экспоненциальное приближение».
8. Линия тренда добавлена, но она полностью находится под линией самого графика, так как мы не указали значение аргумента, к которому она должна стремиться. Чтобы это сделать опять последовательно кликаем по кнопке «Линия тренда», но теперь выбираем пункт «Дополнительные параметры линии тренда».
9. Запускается окно формата линии тренда. В разделе «Параметры линии тренда» есть блок настроек «Прогноз». Как и в предыдущем способе, давайте для экстраполяции возьмем аргумент 55. Как видим, пока что график имеет длину до аргумента 50 включительно. Получается, нам нужно будет его продлить ещё на 5 единиц. На горизонтальной оси видно, что 5 единиц равно одному делению. Значит это один период. В поле «Вперед на» вписываем значение «1». Жмем на кнопку «Закрыть» в нижнем правом углу окна.
10. Как видим, график был продлен на указанную длину с помощью линии тренда.

Итак, мы рассмотрели простейшие примеры экстраполяции для таблиц и для графиков. В первом случае используется функция ПРЕДСКАЗ, а во втором – линия тренда. Но на основе этих примеров можно решать и гораздо более сложные задачи прогнозирования.

Экстраполяция в excel

При работе с вычислениям в программе Excel иногда требуется узнать результат функции, значения которой находятся за рамками известной области (например для прогнозирования). Рассмотрим как это сделать с помощью нескольких способов.

Метод экстраполяции позволяет найти результат функции, значения которой могут находится за пределами конкретных рамок. Зачастую это используется в прогнозировании различных экономических процессов. В этом методе можно работать как с значениями в таблицах так и в работе с данными в графиках.

Пример работы с табличными данными

Имеется таблица с конкретным диапазоном аргументов от 5 до 50, которые относятся к функции (f(x)). В данном примере надо вычислить результат для числа, которое находится за рамкой изветсных аргументов. В данном случае это число 55. Чтобы это сделать надо работать с функцией ПРЕДСКАЗ.

Выбираем ту ячейку, которая в конечном итоге будет показывать результат. После этого нужно нажать на кнопку в строке формул, которая отвечает за вставку функций.

Появится новое окно, в нём нужно выбрать среди категорий именно категорию «Статистические» и после этого ниже в списке надо выбрать «ПРЕДСКАЗ» и в конце подтвердить действие.

Далее откроется новое окно аргументов функции. Там где указание аргумента Х, вводим адрес нужной ячейки. Это наиболее оптимально, так как потом адрес ячейки можно выбрать другой и провести уже другие вычисления. Следующее поле называется «Известные значения Y». В нём надо выделить все ячейки с числами, с которыми ведется работа. В последнем поле известных значений Х, нужно выделить все значения которые относятся к аргументу X (первый столбец с примера). Когда эти действия произведены, подтверждаем изменения.

В конечном итоге в нужной ячейке появится результат, который относится к числу 55.

Пример работы с данными в графиках линией тренда

Для начала важно построить правильный график, предварительно выделив таблицу, с которой будут проводится вычисления. Далее переходим в раздел «Вставка» в верхней панели программы Excel и выбираем в этом разделе пункт «Диаграммы» и кликаем на кнопку обозначающую построение графика, после этого нажимаем на самый оптимальный для вас график.

Далее будет отображен график по выбранными ранее данным. Важное примечание : нужно удалить в нём линию обозначающую аргумент (указана стрелкой на изображении).

После этих действий, есть также горизонтальная шкала, но в нам надо чтобы было отображены данные об аргументах. Чтобы именно эти данные были показано, нажимаем по графику ПКМ и в контекстном меню кликаем на «Выбрать данные».

Появится новое окно, в нём кликаем соответствующую кнопку для изменения данных.

Далее кликаем на «Диапазон подписей оси» и после этого нужно выделить столбец значений, которые надо ЛКМ выделить числа, которые нам нужны, в данном примере это столбец с значениями x, после этого подтверждаем действие. Также не забудьте подтвердить действие в окне выбора источника данных, которое было открыто ранее.

Этим действиями мы подготовили график для работы. А теперь построим линию тренда. Для этого нужно нажать по графику, который мы подготовили и выше, в главном меню разделов, будет активирован нужным нам раздел «Макет», в нем надо выбрать пункт «Анализ» и выбрать кнопку линии тренда. Далее кликаем на «Линейное приближение», это наиболее оптимальный вариант. После этих действий линия тренда будет добавлена в график.

Чтобы сделать корректное отображение линии тренда, вновь нужно перейти с соответствующий пункт как на изображении выше, но в списке нажать на последний вариант, который позволит задать дополнительные параметры в линии тренда.

Далее будет открыто новое окно, в котором можно задать параметры линии тренда. Ищем в окне настройки прогноза, и задаем число 1 (период), так как пять единиц значений = одному периоду, это было сделано так как значение за пределами 50 возьмем вновь 55.

Результатом будет удлинение длины графика соответственно к параметрам линии тренда.

Применение интерполяции в Microsoft Excel

Бывает ситуация, когда в массиве известных значений нужно найти промежуточные результаты. В математике это называется интерполяцией. В Excel данный метод можно применять как для табличных данных, так и для построения графиков. Разберем каждый из этих способов.

Использование интерполяции

Главное условие, при котором можно применять интерполяцию – это то, что искомое значение должно быть внутри массива данных, а не выходить за его предел. Например, если мы имеем набор аргументов 15, 21 и 29, то при нахождении функции для аргумента 25 мы можем использовать интерполяцию. А для поиска соответствующего значения для аргумента 30 – уже нет. В этом и является главное отличие этой процедуры от экстраполяции.

Способ 1: интерполяция для табличных данных

Прежде всего, рассмотрим применения интерполяции для данных, которые расположены в таблице. Для примера возьмем массив аргументов и соответствующих им значений функции, соотношение которых можно описать линейным уравнением. Эти данные размещены в таблице ниже. Нам нужно найти соответствующую функцию для аргумента 28. Сделать это проще всего с помощью оператора ПРЕДСКАЗ.

Выделяем любую пустую ячейку на листе, куда пользователь планирует выводить результат от проведенных действий. Далее следует щелкнуть по кнопке «Вставить функцию», которая размещена слева от строки формул.

Активируется окошко Мастера функций. В категории «Математические» или «Полный алфавитный перечень» ищем наименование «ПРЕДСКАЗ». После того, как соответствующее значение найдено, выделяем его и щелкаем по кнопке «OK».

В первое поле нам просто нужно вручную с клавиатуры вбить значения аргумента, функцию которого следует отыскать. В нашем случае это 28.

В поле «Известные значения y» нужно указать координаты диапазона таблицы, в котором содержатся значения функции. Это можно сделать вручную, но гораздо проще и удобнее установить курсор в поле и выделить соответствующую область на листе.

Аналогичным образом устанавливаем в поле «Известные значения x» координаты диапазона с аргументами.

После того, как все нужные данные введены, жмем на кнопку «OK».

Способ 2: интерполяция графика с помощью его настроек

Процедуру интерполяции можно применять и при построении графиков функции. Актуальна она в том случае, если в таблице, на основе которой построен график, к одному из аргументов не указано соответствующее значение функции, как на изображении ниже.

Выполняем построение графика обычным методом. То есть, находясь во вкладке «Вставка», выделяем табличный диапазон, на основе которого будет проводиться построение. Щелкаем по значку «График», размещенному в блоке инструментов «Диаграммы». Из появившегося списка графиков выбираем тот, который считаем более уместным в данной ситуации.

Как видим, график построен, но не совсем в таком виде, как нам нужно. Во-первых, он разорван, так как для одного аргумента не нашлась соответствующая функция. Во вторых, на нем присутствует дополнительная линия X, которая в данном случае не нужна, а также на горизонтальной оси указаны просто пункты по порядку, а не значения аргумента. Попробуем исправить все это.

Для начала выделяем сплошную синюю линию, которую нужно удалить и жмем на кнопку Delete на клавиатуре.

Выделяем всю плоскость, на которой размещен график. В появившемся контекстном меню щелкаем по кнопке «Выбрать данные…».

Запускается окно выбора источника данных. В правом блоке «Подписи горизонтальной оси» жмем на кнопку «Изменить».

Открывается небольшое окошко, где нужно указать координаты диапазона, значения из которого будут отображаться на шкале горизонтальной оси. Устанавливаем курсор в поле «Диапазон подписей осей» и просто выделяем соответствующую область на листе, в которой содержаться аргументы функции. Жмем на кнопку «OK».

Теперь нам осталось выполнить основную задачу: с помощью интерполяции устранить разрыв. Вернувшись в окно выбора диапазона данных жмем на кнопку «Скрытые и пустые ячейки», расположенную в нижнем левом углу.

Открывается окно настройки скрытых и пустых ячеек. В параметре «Показывать пустые ячейки» выставляем переключатель в позицию «Линию». Жмем на кнопку «OK».

Как видим, график скорректирован, а разрыв с помощью интерполяции удален.

Способ 3: интерполяция графика с помощью функции

Произвести интерполяцию графика можно также с помощью специальной функции НД. Она возвращает неопределенные значения в указанную ячейку.

После того, как график построен и отредактирован, так как вам нужно, включая правильную расстановку подписи шкалы, остается только ликвидировать разрыв. Выделяем пустую ячейку в таблице, из которой подтягиваются данные. Жмем на уже знакомый нам значок «Вставить функцию».

Открывается Мастер функций. В категории «Проверка свойств и значений» или «Полный алфавитный перечень» находим и выделяем запись «НД». Жмем на кнопку «OK».

У данной функции нет аргумента, о чем и сообщает появившееся информационное окошко. Чтобы закрыть его просто жмем на кнопку «OK».

Можно сделать даже проще, не запуская Мастер функций, а просто с клавиатуры вбить в пустую ячейку значение «#Н/Д» без кавычек. Но это уже зависит от того, как какому пользователю удобнее.

Как видим, в программе Эксель можно выполнить интерполяцию, как табличных данных, используя функцию ПРЕДСКАЗ, так и графика. В последнем случае это осуществимо с помощью настроек графика или применения функции НД, вызывающей ошибку «#Н/Д». Выбор того, какой именно метод использовать, зависит от постановки задачи, а также от личных предпочтений пользователя.

Мы рады, что смогли помочь Вам в решении проблемы.

Добавьте сайт Lumpics.ru в закладки и мы еще пригодимся вам.
Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Экстраполяция в excel как сделать

Существуют случаи, когда требуется узнать результаты вычисления функции за пределами известной области. Особенно актуален данный вопрос для процедуры прогнозирования. В Экселе есть несколько способов, с помощью которых можно совершить данную операцию. Давайте рассмотрим их на конкретных примерах.

Использование экстраполяции

В отличие от интерполяции, задачей которой является нахождения значения функции между двумя известными аргументами, экстраполяция подразумевает поиск решения за пределами известной области. Именно поэтому данный метод столь востребован для прогнозирования.

В Экселе можно применять экстраполяцию, как для табличных значений, так и для графиков.

Способ 1: экстраполяция для табличных данных

Прежде всего, применим метод экстраполяции к содержимому табличного диапазона. Для примера возьмем таблицу, в которой имеется ряд аргументов (X) от 5 до 50 и ряд соответствующих им значений функции (f(x)). Нам нужно найти значение функции для аргумента 55, который находится за пределом указанного массива данных. Для этих целей используем функцию ПРЕДСКАЗ.

1. Выделяем ячейку, в которой будет отображаться результат проведенных вычислений. Кликаем по значку «Вставить функцию», который размещен у строки формул.
2. Запускается окно Мастера функций. Выполняем переход в категорию «Статистические» или «Полный алфавитный перечень». В открывшемся списке производим поиск наименования «ПРЕДСКАЗ». Найдя его, выделяем, а затем щелкаем по кнопке «OK» в нижней части окна.
3. Мы перемещаемся к окну аргументов вышеуказанной функции. Она имеет всего три аргумента и соответствующее количество полей для их внесения.

В поле «X» следует указать значение аргумента, функцию от которого нам следует вычислить. Можно просто вбить с клавиатуры нужное число, а можно указать координаты ячейки, если аргумент записан на листе. Второй вариант даже предпочтительнее. Если мы произведем внесение именно таким способом, то для того, чтобы просмотреть значение функции для другого аргумента нам не придется менять формулу, а достаточно будет изменить вводную в соответствующей ячейке. Для того, чтобы указать координаты этой ячейки, если был выбран все-таки второй вариант, достаточно установить курсор в соответствующее поле и выделить эту ячейку. Её адрес тут же отобразится в окне аргументов.

В поле «Известные значения y» следует указать весь имеющийся у нас диапазон значений функции. Он отображается в колонке «f(x)». Следовательно, устанавливаем курсор в соответствующее поле и выделяем всю эту колонку без её наименования.

В поле «Известные значения x» следует указать все значения аргумента, которым соответствуют внесенные нами выше значения функции. Эти данные находятся в столбце «x». Точно так же, как и в предыдущий раз выделяем нужную нам колонку, предварительно установив курсор в поле окна аргументов.

После того, как все данные внесены, жмем на кнопку «OK».

Урок: Мастер функций в Excel

Способ 2: экстраполяция для графика

Выполнить процедуру экстраполяции для графика можно путем построения линии тренда.

1. Прежде всего, строим сам график. Для этого курсором при зажатой левой кнопке мыши выделяем всю область таблицы, включая аргументы и соответствующие значения функции. Затем, переместившись во вкладку «Вставка», кликаем по кнопке «График». Этот значок расположен в блоке «Диаграммы» на ленте инструментов. Появляется перечень доступных вариантов графиков. Выбираем наиболее подходящий из них на свое усмотрение.
2. После того, как график построен, удаляем из него дополнительную линию аргумента, выделив её и нажав на кнопку Delete на клавиатуре компьютера.
3. Далее нам нужно поменять деления горизонтальной шкалы, так как в ней отображаются не значения аргументов, как нам того нужно. Для этого, кликаем правой кнопкой мыши по диаграмме и в появившемся списке останавливаемся на значении «Выбрать данные».
4. В запустившемся окне выбора источника данных кликаем по кнопке «Изменить» в блоке редактирования подписи горизонтальной оси.
5. Открывается окно установки подписи оси. Ставим курсор в поле данного окна, а затем выделяем все данные столбца «X» без его наименования. Затем жмем на кнопку «OK».
6. После возврата к окну выбора источника данных повторяем ту же процедуру, то есть, жмем на кнопку «OK».
7. Теперь наш график подготовлен и можно, непосредственно, приступать к построению линии тренда. Кликаем по графику, после чего на ленте активируется дополнительный набор вкладок – «Работа с диаграммами». Перемещаемся во вкладку «Макет» и жмем на кнопку «Линия тренда» в блоке «Анализ». Кликаем по пункту «Линейное приближение» или «Экспоненциальное приближение».
8. Линия тренда добавлена, но она полностью находится под линией самого графика, так как мы не указали значение аргумента, к которому она должна стремиться. Чтобы это сделать опять последовательно кликаем по кнопке «Линия тренда», но теперь выбираем пункт «Дополнительные параметры линии тренда».
9. Запускается окно формата линии тренда. В разделе «Параметры линии тренда» есть блок настроек «Прогноз». Как и в предыдущем способе, давайте для экстраполяции возьмем аргумент 55. Как видим, пока что график имеет длину до аргумента 50 включительно. Получается, нам нужно будет его продлить ещё на 5 единиц. На горизонтальной оси видно, что 5 единиц равно одному делению. Значит это один период. В поле «Вперед на» вписываем значение «1». Жмем на кнопку «Закрыть» в нижнем правом углу окна.
10. Как видим, график был продлен на указанную длину с помощью линии тренда.

Урок: Как построить линию тренда в Excel

Итак, мы рассмотрели простейшие примеры экстраполяции для таблиц и для графиков. В первом случае используется функция ПРЕДСКАЗ, а во втором – линия тренда. Но на основе этих примеров можно решать и гораздо более сложные задачи прогнозирования.

Мы рады, что смогли помочь Вам в решении проблемы.

Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

При работе с вычислениям в программе Excel иногда требуется узнать результат функции, значения которой находятся за рамками известной области (например для прогнозирования). Рассмотрим как это сделать с помощью нескольких способов.

Метод экстраполяции позволяет найти результат функции, значения которой могут находится за пределами конкретных рамок. Зачастую это используется в прогнозировании различных экономических процессов. В этом методе можно работать как с значениями в таблицах так и в работе с данными в графиках.

Пример работы с табличными даннымиИмеется таблица с конкретным диапазоном аргументов от 5 до 50, которые относятся к функции (f(x)). В данном примере надо вычислить результат для числа, которое находится за рамкой изветсных аргументов. В данном случае это число 55. Чтобы это сделать надо работать с функцией ПРЕДСКАЗ.

Выбираем ту ячейку, которая в конечном итоге будет показывать результат. После этого нужно нажать на кнопку в строке формул, которая отвечает за вставку функций.

Появится новое окно, в нём нужно выбрать среди категорий именно категорию «Статистические» и после этого ниже в списке надо выбрать «ПРЕДСКАЗ» и в конце подтвердить действие.

Далее откроется новое окно аргументов функции. Там где указание аргумента Х, вводим адрес нужной ячейки. Это наиболее оптимально, так как потом адрес ячейки можно выбрать другой и провести уже другие вычисления. Следующее поле называется «Известные значения Y». В нём надо выделить все ячейки с числами, с которыми ведется работа. В последнем поле известных значений Х, нужно выделить все значения которые относятся к аргументу X (первый столбец с примера). Когда эти действия произведены, подтверждаем изменения.

В конечном итоге в нужной ячейке появится результат, который относится к числу 55.

Пример работы с данными в графиках линией трендаДля начала важно построить правильный график, предварительно выделив таблицу, с которой будут проводится вычисления. Далее переходим в раздел «Вставка» в верхней панели программы Excel и выбираем в этом разделе пункт «Диаграммы» и кликаем на кнопку обозначающую построение графика, после этого нажимаем на самый оптимальный для вас график.

Далее будет отображен график по выбранными ранее данным. Важное примечание : нужно удалить в нём линию обозначающую аргумент (указана стрелкой на изображении).

После этих действий, есть также горизонтальная шкала, но в нам надо чтобы было отображены данные об аргументах. Чтобы именно эти данные были показано, нажимаем по графику ПКМ и в контекстном меню кликаем на «Выбрать данные».

Появится новое окно, в нём кликаем соответствующую кнопку для изменения данных.

Далее кликаем на «Диапазон подписей оси» и после этого нужно выделить столбец значений, которые надо ЛКМ выделить числа, которые нам нужны, в данном примере это столбец с значениями x, после этого подтверждаем действие. Также не забудьте подтвердить действие в окне выбора источника данных, которое было открыто ранее.

Этим действиями мы подготовили график для работы. А теперь построим линию тренда. Для этого нужно нажать по графику, который мы подготовили и выше, в главном меню разделов, будет активирован нужным нам раздел «Макет», в нем надо выбрать пункт «Анализ» и выбрать кнопку линии тренда. Далее кликаем на «Линейное приближение», это наиболее оптимальный вариант. После этих действий линия тренда будет добавлена в график.

Чтобы сделать корректное отображение линии тренда, вновь нужно перейти с соответствующий пункт как на изображении выше, но в списке нажать на последний вариант, который позволит задать дополнительные параметры в линии тренда.

Далее будет открыто новое окно, в котором можно задать параметры линии тренда. Ищем в окне настройки прогноза, и задаем число 1 (период), так как пять единиц значений = одному периоду, это было сделано так как значение за пределами 50 возьмем вновь 55.

Результатом будет удлинение длины графика соответственно к параметрам линии тренда.

Табличный процессор Excel позволяет не только быстро производить различные вычисления, но и решать достаточно сложные задачи. Например, с его помощью можно осуществлять математическое моделирование на основе набора дискретных значений той или иной функции, в том числе находить промежуточное значение функций методом интерполяции. В Excel для этого предусмотрены различные инструменты, пользоваться которыми научит эта статья.

Метод интерполяции: что это такое?

В вычислительной математике так называют способ нахождения промежуточных неизвестных значений функции Y(X) по дискретному набору уже известных.

Интерполяция функции Y(X) может осуществляться только для тех ее аргументов, которые находятся внутри интервала , такого, что известны значения Y(X0) и Y(Xn).

Если X не принадлежит , то можно использовать метод экстраполяции.

В классической постановке интерполяционной задачи требуется найти приближенную аналитическую функцию φ(X), у которой значения в узловых точках Xi совпадают со значениями Y(Xi) исходной таблицы, т. е. соблюдается условие φ (Xi)=Yi (i = 0,1,2,…,n).

Линейная интерполяция в Excel

В самом известном табличном процессоре от Microsoft присутствует крайне полезный оператор «ПРЕДСКАЗ».

Рассмотрим данные, размещенные в в таблице, представленной ниже.

Интерполяция графика и табличных данных в Excel

Интерполяция – это своего рода «латание» графиков в тех местах, где возникают обрывы линий из-за отсутствия данных по отдельным показателям. Термин интерполяция подразумевает «латание» внутренних обрывов на графике. А если бы «латались» внешние обрывы, то это была-бы уже экстраполяция графика.

Как построить график с интерполяцией в Excel

При работе в Excel приходится сталкиваться с интерполяцией графиков различной сложности. Но для первого знакомства с ней рассмотрим сначала самый простой пример.

Если в таблице еще нет всех значений показателей, но уже нужно сформировать по ним отчет и построить графическое представление данных. Тогда на графике мы наблюдаем обрывы в местах, где отсутствуют значения показателей.

Заполните таблицу как показано на рисунке:

Выделите диапазон A1:B4 и выберите инструмент: «Вставка»-«Диаграммы»-«График»-«График с маркерами».

Чтобы устранить обрывы на графике, то есть выполнить интерполяцию в Excel, можем использовать 2 решения для данной задачи:

1. Изменить параметры в настройках графика выбрав соответствующую опцию.
2. Использовать функцию: =НД() – возвращает значение ошибки #Н/Д.

Оба эти способа рассмотрим далее на конкретных примерах.

1. Сделайте график активным щелкнув по нему левой кнопкой мышки и выберите инструмент: «Работа с диаграммами»-«Конструктор»-«Выбрать данные».
2. В появившемся диалоговом окне «Выбор источника данных» кликните на кнопку «Скрытые и пустые ячейки»
3. В появившемся диалоговом окне «Настройка скрытых и пустых ячеек» выберите опцию «линию». И нажмите ОК во всех открытых диалоговых окнах.

Как видно на рисунках сразу отображены 2 варианта опций «линию» и «нулевые значения». Обратите внимание, как ведет себя график при выборе каждой из них.

Методы интерполяции табличных данных в Excel

Теперь выполним интерполяцию данных в таблице с помощью функции: =НД(). Для этого нужно предварительно сбросить выше описанные настройки графика, чтобы увидеть как работает данный способ.

Способ 2 . В ячейку B3 введите функцию =НД(). Это автоматически приведет к интерполяции графика как показано на рисунке:

Примечание. Вместо функции =НД() в ячейку можно ввести просто значение: #Н/Д!, результат будет тот же.

MATCH

In this course:

MATCH function

Tip: Try using the new XMATCH function, an improved version of MATCH that works in any direction and returns exact matches by default, making it easier and more convenient to use than its predecessor.

The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula =MATCH(25,A1:A3,0) returns the number 2, because 25 is the second item in the range.

Tip: Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself. For example, you might use the MATCH function to provide a value for the row_num argument of the INDEX function.

Syntax

MATCH(lookup_value, lookup_array, [match_type])

The MATCH function syntax has the following arguments:

lookup_value Required. The value that you want to match in lookup_array. For example, when you look up someone’s number in a telephone book, you are using the person’s name as the lookup value, but the telephone number is the value you want.

The lookup_value argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.

lookup_array Required. The range of cells being searched.

match_type Optional. The number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.

The following table describes how the function finds values based on the setting of the match_type argument.

MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: . -2, -1, 0, 1, 2, . A-Z, FALSE, TRUE.

MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.

MATCH finds the smallest value that is greater than or equal to lookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, . 2, 1, 0, -1, -2, . and so on.

MATCH returns the position of the matched value within lookup_array, not the value itself. For example, MATCH(«b», ,0) returns 2, which is the relative position of «b» within the array .

MATCH does not distinguish between uppercase and lowercase letters when matching text values.

If MATCH is unsuccessful in finding a match, it returns the #N/A error value.

If match_type is 0 and lookup_value is a text string, you can use the wildcard characters — the question mark ( ?) and asterisk ( *) — in the lookup_value argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (

) before the character.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.