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

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

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

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

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



2. После того, как график построен, удаляем из него дополнительную линию аргумента, выделив её и нажав на кнопку Delete
   на клавиатуре компьютера.



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



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



5. Открывается окно установки подписи оси. Ставим курсор в поле данного окна, а затем выделяем все данные столбца «X»
   без его наименования. Затем жмем на кнопку «OK»
   .



6. После возврата к окну выбора источника данных повторяем ту же процедуру, то есть, жмем на кнопку «OK»
   .



7. Теперь наш график подготовлен и можно, непосредственно, приступать к построению линии тренда. Кликаем по графику, после чего на ленте активируется дополнительный набор вкладок – «Работа с диаграммами»
   . Перемещаемся во вкладку «Макет»
   и жмем на кнопку «Линия тренда»
   в блоке «Анализ»
   . Кликаем по пункту «Линейное приближение»
   или «Экспоненциальное приближение»
   .



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



9. Запускается окно формата линии тренда. В разделе «Параметры линии тренда»
   есть блок настроек «Прогноз»
   . Как и в предыдущем способе, давайте для экстраполяции возьмем аргумент 55
   . Как видим, пока что график имеет длину до аргумента 50
   включительно. Получается, нам нужно будет его продлить ещё на 5
   единиц. На горизонтальной оси видно, что 5 единиц равно одному делению. Значит это один период. В поле «Вперед на»
   вписываем значение «1»
   . Жмем на кнопку «Закрыть»
   в нижнем правом углу окна.



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

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

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

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

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

В этом и является главное отличие этой процедуры от экстраполяции.

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

Прежде всего, рассмотрим применения интерполяции для данных, которые расположены в таблице.

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

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

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

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

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

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

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

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

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

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

Перед тем как перейти к сути самого определения и рассказать о нём подробнее, немного углубимся в историю.

История

Интерполяция была известна ещё с древнейших времён. Однако своим развитием это явление обязано нескольким самым выдающимся математикам прошлого: Ньютону, Лейбницу и Грегори.

Именно они развили это понятие с помощью более продвинутых математических способов, доступных в то время.

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

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

Интерполяция представляет собой достаточно узкое понятие, поэтому её история не так богата фактами. В следующем разделе разберёмся, что такое интерполяция на самом деле и чем она отличается от своей противоположности — экстраполяции.

Что такое интерполяция?

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

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

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

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

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

Виды интерполяции

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

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

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

Усложнённые виды интерполяции

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

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

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

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

Тут всего два метода. Среди них билинейная или двойная интерполяция. Этот способ позволяет без труда построить график по точкам в трёхмерном пространстве. Другие методы затрагивать не будем.

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

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

Применение

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

Однако при любых расчётах в технике, физике и многих других науках (например, биологии), крайне важно представлять достаточно полную картину о явлении, имея при этом определённый набор значений.

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

А как это пригодится в жизни?

На подобный вопрос бывает очень сложно ответить. Но ответ прост: никак. Именно эти знания вам никак не пригодятся.

А вот если вы поймёте этот материал и методы, с помощью которых осуществляются эти действия, вы потренируете свою логику, которая в жизни очень пригодится.

Главное — не сами знания, а те навыки, которые человек приобретает в процессе изучения. Ведь недаром существует поговорка: «Век живи — век учись».

Смежные понятия

Вы можете сами понять, насколько важна была (и до сих пор не теряет свою важность) эта область математики, взглянув на многообразие других концепций, связанных с данной. Мы уже говорили об экстраполяции, но есть ещё и аппроксимация. Может быть, вы уже слышали это слово.

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

Эти два понятия очень похожи между собой, и тем интереснее изучать каждое из них.

Заключение

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

Это глава из книги Билла Джелена .

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

Таблица (рис. 1) включает высоту над землей (управляющий параметр) и скорость ветра (рассчитываемый параметр).

Например, если надо найти скорость ветра, соответствующую высоте 47 метров, то следует применить формулу: 130 + (180 – 130) * 7 / (50 – 40) = 165 м/сек.

Скачать заметку в формате или , примеры в формате

Как быть, если существует два управляющих параметра? Можно ли выполнить вычисления с помощью одной формулы? В таблице (рис. 2) показаны значения давления ветра для различных высот и величин пролета конструкций. Требуется вычислить давление ветра на высоте 25 метров и величине пролета 300 метров.

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

Начните с таблицы, изображенной на рис. 2. Добавьте исходные ячейки для высоты и пролета в J1 и J2 соответственно (рис. 3).

Рис. 3. Формулы в ячейках J3:J17 объясняют работу мегаформулы

Для удобства использования формул определите имена (рис. 4).

Проследите за работой формулы последовательно переходя от ячейки J3 к ячейке J17.

Получится формула, состоящая из 984 символов, которую невозможно воспринять в таком виде. Вы можете посмотреть на нее в приложенном Excel-файле. Не уверен, что такого рода мегаформулы полезны в использовании.

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

Источник: https://realartist.ru/kak-rasschitat-interpolyaciyu-formula-primenenie-ekstrapolyacii-v-microsoft-excel.html

Прогноз продаж в Excel

Хитрости » 23 Март 2017       Дмитрий       72065 просмотров

• Скачать файл, используемый в видеоуроке:
•   Прогноз_продаж.xls (59,5 KiB, 16 543 скачиваний)
• А в довершение мы построим красивый график с прогнозом.

Прогнозирование продаж является неотъемлемой частью при планировании работы коммерческих и финансовых служб, поэтому задача довольно актуальная. Вариантов построения прогнозов достаточное множество, но я хочу показать как сделать простой, но в то же время достаточно жизнеспособный прогноз «на скорую руку», без лишних телодвижений и поправок «на ветер»(читайте как: без кучи доп.расчетов, которые применяются для создания более точных прогнозов). Почему я это уточняю? Потому что на мой взгляд, каким бы точным ни был прогноз продаж – это всего лишь предположение и быть уверенным в том, что именно так и будет развиваться ход событий, никак нельзя.
И тем не менее при помощи встроенных в Excel функций мы можем построить довольно неплохой прогноз даже с учетом сезонности. Плюс я хочу показать как сделать не просто прогноз, а прогноз с отклонениями – пессимистичный и оптимистичный. С помощью подобной модели можно будет выстроить тактику продаж таким образом, чтобы постараться максимально «вписаться» в границы между пессимистичным и оптимистичным прогнозом. Исходные данные
Для расчета прогноза потребуются данные о продажах за ранние периоды. Чем больше данных, тем точнее будет прогноз. Желательно, чтобы были помесячные данные хотя бы за два года. На мой взгляд это тот минимум, на основании которого можно построить весьма точный прогноз с учетом прошлого опыта. Именно из таких данных и будем исходить. Предположим, что у нас есть данные с января 2013 года по август 2015, в табличном виде:
Нам необходимо рассчитать прогноз продаж на будущий год: с сентября 2015 по август 2016 и отразить это на графике. Я специально беру рваный период посреди года, чтобы показать, что начало прогноза может быть с любой даты.

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

Т.е. у нас должно быть именно в указанном порядке 7 столбцов: Период; Продажи компании, руб.; Прогноз; Оптимистичный; Пессимистичный; Коэффициент сезонности; Отклонение. И чтобы все получилось они должны идти точно в таком же порядке, как на картинке выше.

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

  Прогноз_продаж.xls (59,5 KiB, 16 543 скачиваний)

В файле два листа:

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

В самый низ таблицы, после последней фактической даты, я добавил даты, на которые необходимо построить прогноз(от сен.2015 до авг.2016).

Расчет прогноза
Для расчета непосредственно прогноза в Excel есть специальная функция, которая основываясь на данных предыдущих периодов предсказывает вероятные значения для указанной даты. Она так и называется – ПРЕДСКАЗ(FORECAST). Функция основана на линейной регрессии и специально предназначена именно для прогнозирования продаж, потребления товара и пр. В столбец Прогноз (столбец C – сразу после столбца с суммами продаж) в ячейку C34 записываем функцию (и распространяем на все прогнозируемые даты – C34:C45):
=ПРЕДСКАЗ(A34;$B$2:$B$33;$A$2:$A$33)
=FORECAST(A34,$B$2:$B$33,$A$2:$A$33)
Сама функция требует указания следующих входных данных:

• х — Дата, значение для которой необходимо спрогонозировать (A34)
• Известные значения y — ссылка на ячейки таблицы с суммами продаж за известные периоды ($B$2:$B$33)
• Известные значения x — ссылка на ячейки таблицы с дата продаж за известные периоды ($A$2:$A$33)

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

Для учета фактора сезонности сначала необходимо вычислить коэффициент сезонности для каждого месяца.

Для этого добавим в столбец Коэффициент сезонности следующую формулу:
=(($B$2:$B$13+$B$14:$B$25)/СУММ($B$2:$B$25))*12
=(($B$2:$B$13+$B$14:$B$25)/SUM($B$2:$B$25))*12
Формула вводится в ячейку как формула массива и сразу в 12 ячеек(чтобы получить коэффициенты для каждого месяца года).

Для этого сначала выделяем ячейки F2:F13 -переходим в строку формул и вводим формулу выше. После указания верных ссылок на нужные ячейки завершаем ввод формулы одновременным нажатием трех клавиш: Ctrl+Shift+Enter. Если этого не сделать, то функция вернет значение ошибки #ЗНАЧ!(#VALUE!)

Подробнее про принцип работы формулы: она берет отдельно сумму каждого месяца за 2013 и 2014 год, складывает их. Делит полученное значение на общую сумму продаж за весь период целых месяцев(т.е.

24 месяца) и умножает на 12, чтобы получить коэффициент именно за один месяц. И так для каждого месяца. Т.е. для ячейки F2 расчет будет выглядеть следующим образом:
=((56 769+68 521)/ 1 542 293)*12
=((сумма за янв.2013 + сумма за янв.

2014)/ общая сумма за два года(янв.2013 – дек.2014))*12

В результате для января получим коэффициент 0,974834224106574, для февраля — 0,989928632237843 и т.д.

Основную задачу выполнили – у нас есть прогноз на будущие периоды. Теперь осталось в дополнение к самому прогнозу, создать допустимые верхние и нижние границы, которые часто еще называют оптимистичный прогноз и пессимистичный(но по сути это просто возможное отклонение от прогнозных данных). Такой прогноз даст нам возможность более гибко планировать тактику на будущие периоды.

Для того, чтобы построить такие прогнозы необходимо рассчитать допустимое отклонение от прогнозируемых значений. Здесь так же будем использовать имеющиеся в Excel функции. В ячейку G2 запишем формулу:
=ДОВЕРИТ(0,05; СТАНДОТКЛОН(C34:C45); СЧЁТ(C34:C45))
=CONFIDENCE(0.

05,STDEV(C34:C45),COUNT(C34:C45))
ДОВЕРИТ(CONFIDENCE) – возвращает доверительный интервал, используя нормальное распределение.

• алфа – уровень значимости для вычисления доверительного уровня. Используемое в формуле 0,05 означает доверительный уровень в 95%. В большинстве случаев это оптимальное значение
• станд_откл – стандартное отклонение генеральной совокупности. Должно быть известно. Но т.к. мы этими данными не располагаем – то это значение вычисляем при помощи функции СТАНДОТКЛОН(STDEV), передавая ей для расчетов спрогнозированные данные
• размер – указывается целое число, обозначающее количество данных для выборки. Как правило равно количеству спрогнозированных данных. У нас количество определяется функцией СЧЁТ, которая подсчитывает количество чисел в указанных ячейках.

Теперь в ячейки столбцов Оптимистичный и Пессимистичный(D и E), начиная со строки 34, запишем такие формулы:
Оптимистичный: =$C34+$G$2
Пессимистичный: =$C34-$G$2

Т.е. мы для оптимистичного прогноза берем сумму прогноза и прибавляем к ней сумму рассчитанного отклонения. А для пессимистичного, мы сумму отклонения вычитаем. Вот мы и получили все необходимые данные.

График
Но было бы кощунством с нашей стороны проделать такую работу и не использовать возможности Excel для построения красивого графика. Придется добавить немного шаманства(на деле, мы уже начали шаманить, когда стали записывать прогноз в отдельный столбец, а не продолжать его в том же столбце, что и фактические продажи). В ячейки C33, D33 и E33 скопируем значение из ячейки B33, чтобы они все имели одинаковые значения:

Теперь выделяем все данные (A1:E45), переходим на вкладку Вставка(Insert) – группа Диаграммы(Charts) —График(Line). И получим такую картину:

Наглядно и сразу понятно что к чему и чего можно ожидать.

• Синим – фактические продажи
• Оранжевый – прогноз
• Серый – Оптимистичный прогноз
• Желтый – Пессимистичный

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

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

Выделяем необходимые данные из двух столбцов -переходим на вкладку Данные(Data) -группа Прогноз(Forecast) —Лист прогноза(Forecast Sheet):

В появившемся окне раскрываем пункт Параметры(Options) и настраиваем:

• Завершение прогноза(Forecase End) – указывается дата, которой должен заканчиваться прогноз. Я советую всегда проверять эту дату, т.к. по умолчанию Excel почти всегда выставляет некую среднюю дату, которая отличается от необходимой.
• Начало прогноза(Forecase Start) – указывается дата, с которой необходимо начать строить прогноз. Как правило это последняя дата фактических данных. Если указать дату, которая будет раньше последней даты фактических данных, то для построения прогноза будут использоваться данные только ДО этой даты (так же это называется «ретроспективным прогнозированием»).
• Доверительный интервал(Confidence interval) – этот пункт поможет понять, насколько точно построен прогноз. Чем больше будет доверительный интервал, тем меньше точность прогноза и чем меньше доверительный интервал – тем выше точность прогноза. Что вполне логично. По умолчанию определяется для 95% точек, хотя его можно изменить в соответствующем поле. Если интервал создавать не нужно – снять галочку.
• Сезонность(Seasonality) – как понятно из названия, отвечает за определение фактора сезонности. Лучше оставлять автоматическим, при котором сезонность определяется на основании всех точек месяцев(т.е. 12). Но если этот фактор необходимо рассчитывать из иного количества точек, то необходимо выбрать Установка вручную и указать нужное количество точек. Но следует учитывать, что если точек будет недостаточно – то прогноз может быть очень неточным и график в итоге будет иметь вид, далекий от ожидаемого.
• Диапазон временной шкалы(Timeline Range) – указывается диапазон значений с датами фактических продаж, на основании которых необходимо построить прогноз. По размерам должен совпадать с параметром Диапазон значений.
• Диапазон значений(Values Range) – указывается диапазон значений с суммами фактических продаж, на основании которых необходимо построить прогноз. По размерам должен совпадать с параметром Диапазон временной шкалы.
• Заполнить отсутствующие точки с помощью(Fill Missing Poins Using) – если каких-то данных не хватает(например, имеются пропуски в ячейках с суммами), то можно выбрать чем эти данные заполнить. По умолчанию используется интерполяция. Это означает, что отсутствующие данные вычисляется как взвешенное среднее соседних ячеек, если отсутствует менее 30 % точек. Если необходимо заполнять отсутствующие точки нулями, то необходимо выбрать из выпадающего списка пункт Нули.
• Объединить дубликаты с помощью(Aggregate Duplicates Using) – если в фактических данных есть повторяющиеся даты, то Excel объединит их в одну точку с этой датой, а в качестве суммы подставит среднее арифметическое для этой даты. Это оптимальный вариант, но так же допускается выбрать из списка и другую функцию: Количество, СЧЁТЗ, Максимум, Медиана, Минимум, Сумма.
• Включить статистические данные прогноза(Include Forecast Statistics) – при включении данного пункта на листе с таблицей графика правее основных данных будет создана таблица с дополнительной статистической информации о прогнозе. В таблице при помощи функции ПРЕДСКАЗ.ЕTS.СТАТ будут рассчитаны коэффициенты сглаживания (Альфа, Бета, Гамма), и метрики ошибок (MASE, SMAPE, MAE, RMSE).

1. После нажатия кнопки Создать(Create) будет создан новый лист, в котором будет создана таблица со всеми необходимыми данными и формулами и готовым графиком:

   если при создании был отмечен пункт Включить статистические данные прогноза(Include Forecast Statistics), то правее таблицы основных данных будет так же создана таблица статистических данных:

2. Скачать файл:
3.   Прогноз_продаж.xls (59,5 KiB, 16 543 скачиваний)
4. Так же см.:
   Как быстро подобрать оптимальный вариант решения
   Автообновляемая сводная таблица

Статья помогла? Поделись ссылкой с друзьями!

Источник: https://www.excel-vba.ru/chto-umeet-excel/prognoz-prodazh-v-excel/

Интерполяция в Excel: особенности, порядок действий и примеры

Табличный процeссор Excel позволяeт нe только быстро производить различныe вычислeния, но и рeшать достаточно сложныe задачи.

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

В Excel для этого прeдусмотрeны различныe инструмeнты, пользоваться которыми научит эта статья.

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

• В вычислитeльной матeматикe так называют способ нахождeния промeжуточных нeизвeстных значeний функции Y(X) по дискрeтному набору ужe извeстных.
• Интeрполяция функции Y(X) можeт осущeствляться только для тeх ee аргумeнтов, которыe находятся внутри интeрвала [X0, Xn], такого, что извeстны значeния Y(X0) и Y(Xn).
• Если X нe принадлeжит [X0, Xn], то можно использовать мeтод экстраполяции.

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

соблюдаeтся условиe φ (Xi)=Yi (i = 0,1,2,…,n).

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

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

3. A
4. B
5. C
6. D
7. E
8. 1
9. x
10. f(x)
11. 2
12. 5
13. 38
14. 3
15. 10
16. 68
17. 4
18. 15
19. 98
20. 5
21. 20
22. 128
23. 6
24. 25
25. 158
26. 7
27. 30
28. 188

В пeрвом столбцe находятся аргумeнты x, а во втором — соотвeтствующиe им значeния нeкоторой линeйной функции f(x). Прeдположим, что нам нужно узнать значeниe для аргумeнта x=28. Для этого:

• выдeляют любую пустую ячeйку на листe табличного процeссора, куда будeт выводиться рeзультат от осущeствлeнных дeйствий, напримeр C1;
• кликают по значку «fx» («Вставить функцию»), размeщeнному слeва от строки формул;
• в окошкe «Мастeра функций» заходят в катeгорию «Матeматичeскиe»;
• находят опeратор «ПРЕДСКАЗ» и нажимают на «OK».

В окнe аргумeнтов eсть 3 поля. В пeрвоe вводят с клавиатуры значeниe аргумeнта (в конкрeтной задачe это 28). Для того чтобы заполнить полe «Извeстныe значeния _ y», нажимают на иконку с красной стрeлкой слeва от соотвeтствующeго окошка и выдeляют соотвeтствующую область на листe. В конкрeтном случаe это часть столбца В с адрeсами из диапазона B2:B7.

Точно так жe заполняют полe «Извeстныe значeния _ x» и нажимают на кнопку «Ок».

В рeзультатe в выдeлeнной ячeйкe C1 отображаeтся значeниe 176, являющeeся итогом процeдуры интeрполяции.

Интeрполяция в Excel, примeр которой прeдставлeн вышe, далeко нe eдинствeнный способ, позволяющий выяснить промeжуточныe нeизвeстныe значeния функции Y(X) по дискрeтному набору ужe извeстных.

В частности, можeт быть примeнeн графичeский мeтод. Он можeт оказаться полeзным, eсли в таблицe к одному из аргумeнтов нe указано соотвeтствующee значeниe функции, как в той, что прeдставлeна нижe (см.

ячeйку с адрeсом B9).

• A
• B
• C
• D
• E
• 1
• x
• f(x)
• 2
• 5
• 38
• 3
• 10
• 68
• 4
• 15
• 98
• 5
• 20
• 128
• 6
• 25
• 158
• 7
• 30
• 188
• 8
• 35
• 218
• 9
• 40
• 10
• 45
• 278
• 11
• 50
• 308
• Интeрполяция в Excel в таком случаe начинаeтся с построeния графика. Для этого:

• во вкладкe «Вставка» выдeляют табличный диапазон;
• в блокe инструмeнтов «Диаграммы» выбирают значок «График»;
• в появившeмся спискe выбирают тот, который лучшe подходит для рeшeния конкрeтной задачи.

Так как в ячeйкe B9 пусто, график получился разорванный. Кромe того, на нeм присутствуeт дополнитeльная линия X, в которой нeт нeобходимости, а на горизонтальной оси вмeсто значeний аргумeнта указаны пункты по порядку.

Займeмся обработкой графика. Для этого выдeляют сплошную синюю линию и удаляют ee нажатиeм кнопки Delete, которая находится на клавиатурe.

Затeм:

• выдeляют плоскость, на которой находится график;
• в контeкстном мeню выбирают кнопку «Выбрать данныe…»;
• в окнe «Выбор источника данных» в правом блокe нажимают «Измeнить»;
• нажимают на иконку с красной стрeлкой справа от поля «Диапазон подписeй осeй»;
• выдeляют диапазон А2:А11;
• нажимают на кнопку «OK»;
• вновь вызывают окно «Выбор источника данных»;
• нажимают на кнопку «Скрытыe и пустыe ячeйки» в нижнeм лeвом углу;
• в строкe «Показывать пустыe ячeйки» пeрeключатeль пeрeставляют в позицию «Линия» и нажимают «OK»;
• подтвeрждают эти дeйствия тeм жe способом.

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

Использованиe спeциальной функции НД

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

Прeдположим, что график ужe построeн, на нeм ужe установлeны коррeктныe подписи шкалы. Попробуeм ликвидировать разрыв. Для этого:

• выдeляют в таблицe ячeйку, в которой отсутствуeт значeниe функции;
• выбирают значок «Вставить функцию»;
• в «Мастeрe функций» в окошкe «Катeгории» находят строку «Полный алфавитный пeрeчeнь» (в нeкоторых вeрсиях процeссора «Провeрка свойств и значeний»);
• нажимают на запись «НД» и жмут на кнопку «OK».

Послe этого в ячeйкe B9 появляeтся значeниe ошибки «#Н/Д». Однако обрыв графика автоматичeски устраняeтся.

Вы можeтe поступить дажe прощe: внeсти с клавиатуры в ячeйку B9 символы «#Н/Д» (бeз кавычeк).

Билинeйная интeрполяция

Круг задач, для рeшeния которых можно использовать модeлированиe посрeдством функций одной пeрeмeнной, достаточно ограничeн. Поэтому имeeт смысл рассмотрeть, как используeтся формула двойной интeрполяции в Excel. Примeры могут быть самыми разными. Напримeр: имeeтся таблица (см. нижe).

1. A
2. B
3. C
4. D
5. E
6. F
7. G
8. 1
9. 200
10. 400
11. 600
12. 800
13. 1000
14. Пролeт
15. 2
16. 20
17. 10
18. 20
19. 160
20. 210
21. 260
22. 3
23. 30
24. 40
25. 60
26. 190
27. 240
28. 290
29. 4
30. 40
31. 130
32. 180
33. 230
34. 280
35. 330
36. 5
37. 50
38. 180
39. 230
40. 280
41. 330
42. 380
43. 6
44. 60
45. 240
46. 290
47. 340
48. 390
49. 440
50. 7
51. 70
52. 310
53. 360
54. 410
55. 460
56. 510
57. 8
58. 80
59. 390
60. 440
61. 490
62. 540
63. 590
64. 9
65. 90
66. 750
67. 800
68. 850
69. 900
70. 950
71. 10
72. Высота
73. 278
74. Трeбуeтся вычислить давлeниe вeтра при вeличинe пролeта 300 м на высотe 25 м.

В таблицу добавляют новыe записи так, как прeдставлeно на рисункe (см. нижe).

Как видно, в нee добавлeны ячeйки для высоты и пролeта в J1 и J2.

Путeм обратной послeдоватeльной подстановки «собирают» мeгаформулу, нeобходимую для нахождeния давлeния вeтра при конкрeтных парамeтрах. Для этого:

• копируют тeкст формулы из ячeйки с адрeсом J17 в ячeйку J19;
• замeняют в формулe ссылку на J15 значeниeм в ячeйкe J15: J7+(J8-J7)*J11/J13;
• повторяют эти дeйствия до получeния нeобходимой формулы.

Использованиe spline

Прeдыдущий мeтод достаточно громоздкий, поэтому в нeкоторых случаях прeдпочтитeльнee интeрполяция сплайнами. В Excel ee суть заключаeтся в нахождeнии интeрполирующeй функции φ(Х) по формулам одного и того жe типа для различных подмножeств аргумeнта.

Далee осущeствляeтся стыковка значeний φ(Х) и ee производных на граничных значeниях аргумeнтов каждого из подмножeств. В Excel для этих цeлeй прeдусмотрeны спeциальныe функции, а такжe возможно написаниe макросов на VBA.

Однако они должны создаваться под конкрeтную задачу, поэтому их изучeниe в общeм видe нe имeeт смысла.

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

Источник: https://xroom.su/interpoliaciia-v-excel-osobennosti-poriadok-deistvii-i-primery/

Иллюстрированный самоучитель по Microsoft Excel

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

XY-точечная диаграмма – наиболее подходящее средство для обработки результатов исследований такого рода.

При обработке результатов измерений достаточно часто возникает вопрос о тенденции развития или изменения. В Excel этот вопрос решается при помощи определения тренда.

Линии тренда графически иллюстрируют тенденцию изменения рядов данных.

Они обычно используются в тех случаях, когда нужно построить диаграммы для задач прогнозирования и экстраполяции (так называемых задач регрессионного анализа).

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

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

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

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

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

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

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

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

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

• Активизируйте ряд данных на диаграмме.
• Выберите команду Рисунок из меню Вставка. Excel предложит Вам выбрать нужный рисунок из файла, автофигуру или объект WordArt.
• Выберите необходимый пункт и нажмите клавишу Enter или кнопку ОК в окне. Вместо столбца на диаграмме появится изображение.

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

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

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

При вводе он будет отображаться в строке формул.

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

Источник: http://samoychiteli.ru/document37892.html

Прогнозирование в один клик в Excel 2016

Статья первоначально опубликована командой Excel (Excel Team) на английском языке здесь.

 «Точные расчеты делать сложно, особенно когда дело касается прогнозов на будущее», — говорил датский физик Нильс Бор. Что ж, прогнозирование в один клик в Excel значительно упрощает эту задачу.

Мы часто используем Excel для анализа данных временных рядов (например, о продажах, использовании сервера или инвентаризации), стараясь выявить повторяющиеся сезонные закономерности и тенденции. В Excel 2016 новые функции прогноза на листе и прогноз одним кликом помогут объяснить данные и понять будущие тенденции.

• Рассмотрим подробнее некоторые возможности: как определять сезонность, оценивать уровень доверия к прогнозу и создавать прогноз одним кликом.
• Экспоненциальное сглаживание для прогнозов

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

В новой функции Excel 2016 используется другой алгоритм: экспоненциальное сглаживание (ETS). Это один из самых популярных способов прогнозирования, который уже стал отраслевым стандартом.

Одно из главных преимуществ метода ETS — возможность обнаруживать сезонные закономерности и доверительные интервалы.

Сезонные закономерности

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

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

 Еще один пример — почасовые данные о дорожном движении, где оптимальная сезонность представлена 24 точками (часами).

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

Желательно, чтобы полных сезонных циклов было хотя бы 2–3. Автоматически обнаруживаемое значение в разделе сезонности можно найти в диалоговом окне Создание прогноза в разделе Параметры.

Если сезонные данные обнаружить не удалось или сезонность известна заранее, то это значение можно переопределить, выбрав параметр Задать вручную.

2. Доверительные интервалы
3. Помимо прогнозирования будущих значений для введенного временного ряда, прогноз ETS также может определять доверительный интервал.

Доверительный интервал — это диапазон, окружающий каждое прогнозируемое значение, в который, согласно прогнозу (с нормальным распределением), должно попасть 95 % будущих точек.

Доверительный интервал помогает выяснить точность прогноза. Чем меньше интервал, тем выше доверие к прогнозу для той или иной точки.

Значение коэффициента доверия, по умолчанию равное 95 %, можно изменить с помощью стрелок вверх и вниз в следующих целях:

• На основе ширины доверительного интервала можно определить точность прогноза.
• Экспериментируя с расширенными функциями (учет отсутствующих точек, сезонности и др.), можно отслеживать, как сужается и расширяется предварительный доверительный интервал. Это позволяет определить, насколько полученная модель соответствует хронологическим данным.

Как создать прогноз

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

 Подробные инструкции о том, как создать прогноз, см. на странице Создание прогноза в Excel 2016 для Windows. Второй вариант — если вы умеете пользоваться функциями листа, то можете сделать то же самое с помощью новых функций FORECAST.ETS*, описанных здесь: Справка по функциям прогнозирования.

 Функции листа обладают теми же возможностями, что и мастер прогнозов.

Как узнать, точен ли прогноз? Можно ли ему доверять?

Как и в случаях со многими статистическими инструментами, точность прогноза будет зависеть от введенных данных. А поскольку данные редко бывают идеальными, очень важно изучить прогноз и понять, насколько он применим в вашем конкретном случае. Есть несколько способов оценить точность прогноза:

Посмотрите на ширину доверительного интервала (см. выше).

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

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

Все статистические данные вычисляются с помощью функции FORECAST.ETS.STAT() и описаны здесь.

Поделитесь своим мнением

Мы только что кратко рассказали вам о новых функциях, доступных в Excel 2016. Опробуйте их сами и поделитесь своими идеями о других функциях и улучшениях, которых недостает в Excel.

Источник: https://blogs.technet.microsoft.com/tasush/2015/11/16/excel-2016/