Прогнозирование с помощью Excel (Эксель модели примеры методы)
Практически в любой сфере деятельности, от экономики до инженерии, существует востребованность предсказать результат того или иного действия, получить значения и приблизительные данные. В этом направлении есть масса различного софта. И большинство этого программного обеспечения имеет платные функции.
Табличный процессор Microsoft имеет в своем программном обеспечении мощный инструмент для прогнозирования, который позволяет построить целый ряд различных моделей и с легкостью на практике применять различные методы. При этом в большинстве случаев этот инструмент дает более достоверные результаты, чем у платных программ. Как и каким образом? Давайте разберемся.
Прогнозирование – поиск темпов развития и получаемого результата относительно исходных данных в конкретное время.
Рассмотрим несколько способов, которые могут дать прогнозированный результат:
Линия тренда – графическое отображение прогнозирования за счет экстраполяции. Звучит заумно? На практике все проще.
Давайте попробуем спрогнозировать сумму доходов компании через 36 месяцев на основе показателей за прошлые 12 лет.
Построим точечную диаграмму на основе исходных данных компании, а именно ее прибыль в течение всех 12 лет. Запишем исходные данные по прибыли в таблицу, выделим все ее поля и перейдем в меню «Вставка» — «Диаграмма» и выберем точечный вид диаграммы.
Для построения линии тренда выберем любую точку на диаграмме, откроем контекстное меню правой клавишей мышки и выберем из списка «Добавить линию тренда. ». В появившемся меню выбора аппроксимации выберем тип «Линейная».
Произведем небольшие настройки формата линии: «Прогноз» установим на три года, вписываем «3.0», и укажем, чтобы показывалась величина достоверности и само уравнение на диаграмме.
По построенной линии тренда можем спрогнозировать доход через три года – он будет более 4500 тыс. руб. Достоверность прогнозирования принято считать верным при «0.85» ед. Эффективность прогнозирования не будет успешным, если период будет превышать 30% от периода базы.
Также в наборе функций программы есть ряд стандартных фунций создания прогноза. Одним из таких является оператор «ПРЕДСКАЗ», синтаксис которого таковой: «=ПРЕДСКАЗ(X;известные_значения_y;известные значения_x)».
Аргумент «Х», исходя из нашей таблицы, это искомый год для прогнозирования. «Значения у» — прибыль за прошлое время. «Значения х» — года, в течение которых были собраны данные.
Узнаем, на основе уже полученных данных прогноз на следующий год с помощью оператора «ПРЕДСКАЗ». Для этого вставим в ячейку прибыли на 2018 год с помощью мастера функций оператор «ПРЕДСКАЗ».
В появившемся диалоговом окне укажем все исходные данные, согласно описанию выше.
Полученный результат совпадает с результатом предыдущего метода, поэтому можно считать прогнозирование прибыли достоверным. Для визуального подтверждения можем построить диаграмму.
Еще одним статическим оператором, который можно использовать для прогнозирования, является оператор «ТЕНДЕНЦИЯ» со следующим синтаксисом: «=ТЕНДЕНЦИЯ(Известные значения_y;известные значения_x; новые_значения_x;[конст])». Аргументы оператора идентичны аргументам оператора «ПРЕДСКАЗ».
Попробуем провести прогнозирование на следующий год, используя оператор «ТЕНДЕНЦИЯ». В новую ячейку вставим функцию из мастера функций.
Заполняем аргументы исходными данными и убеждаемся, что очередной метод прогнозирования прекрасно справляется со своей задачей – его результат схож с результатами прошлых шагов и является достоверным.
Аналогичным методом для прогноза данных является функция «РОСТ», за исключением того, что он использует при расчете прогноза экспоненциальную зависимость, в отличие от предыдущих методов, которые использовали линейную. Его аргументы идентичны аргументам оператора «ТЕНДЕНЦИЯ».
Как и в предыдущих шагах, вставляем в новую ячейку функцию «РОСТ», заполняем аргументы исходными данными и сравниваем результат прогнозирования. Он также дает достоверные данные, схожие с предыдущими.
Другой оператор, который может спрогнозировать результат на определенный период времени, оператор «ЛИНЕЙН», который основан на линейном приближении. Его синтаксис схож с прошлыми операторами: «=ЛИНЕЙН(Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])».
Вставим новую функцию в ячейку с прогнозированным годом и заполним аргументы.
Как видим, у оператора отсутствует аргумент новых значений. Он измеряет само значение выручки. А сам результат прогнозирования необходимо подсчитать отдельно.
Чтобы получить прогнозирование на следующий год, необходимо полученное значение линейного тренда умножить на период времени, в нашем случае «3» года, и добавить прибыль за последний год. Получаемый прогноз также схож со всеми предыдущими.
Несмотря на используемый метод, все результаты прогнозирования очень схожи и дают достоверный результат, на который можно опираться для дальнейших действий. Стоит учитывать, что этот результат всегда может измениться из-за нестабильной компании или любых других форс-мажорных ситуаций.
Рассмотрим несколько способов, которые могут дать прогнозированный результат:
Как построить линию тренда в MS Excel — Вектор развития. Офисные системы для бизнеса
2. Производим анализ процесса и выявляем факторы — аргументы функции — x1 , x2 , . xn — наиболее сильно на наш взгляд влияющие на результат – значения функции y . Внимательно назначаем единицы измерений для переменных.
Прогнозирование в один клик в Excel 2016
«Точные расчеты делать сложно, особенно когда дело касается прогнозов на будущее», — говорил датский физик Нильс Бор. Что ж, прогнозирование в один клик в Excel значительно упрощает эту задачу.
Мы часто используем Excel для анализа данных временных рядов (например, о продажах, использовании сервера или инвентаризации), стараясь выявить повторяющиеся сезонные закономерности и тенденции. В Excel 2016 новые функции прогноза на листе и прогноз одним кликом помогут объяснить данные и понять будущие тенденции.
Рассмотрим подробнее некоторые возможности: как определять сезонность, оценивать уровень доверия к прогнозу и создавать прогноз одним кликом.
Экспоненциальное сглаживание для прогнозов
Есть множество способов создания прогноза по хронологическим данным. До появления Excel 2016 многие пользовались функцией листа FORECAST(), с помощью которой создается линейный прогноз или экстраполяция на основе продленных линий тренда в свойствах диаграммы.
В новой функции Excel 2016 используется другой алгоритм: экспоненциальное сглаживание (ETS). Это один из самых популярных способов прогнозирования, который уже стал отраслевым стандартом.
Одно из главных преимуществ метода ETS — возможность обнаруживать сезонные закономерности и доверительные интервалы.
В примере ниже видно, как ежегодная сезонность обнаруживается и применяется к прогнозу. Поскольку данные указаны по месяцам и повторяются каждые 12 точек, обнаруженная сезонность равна 12.
Помимо прогнозирования будущих значений для введенного временного ряда, прогноз ETS также может определять доверительный интервал.
- На основе ширины доверительного интервала можно определить точность прогноза.
- Экспериментируя с расширенными функциями (учет отсутствующих точек, сезонности и др.), можно отслеживать, как сужается и расширяется предварительный доверительный интервал. Это позволяет определить, насколько полученная модель соответствует хронологическим данным.
Чтобы создать лист прогноза, сначала подготовьте набор данных временного ряда (с временным рядом и рядом значений). Затем на вкладке «Данные» нажмите на кнопку Лист прогноза. Запустится диалоговое окно создания прогноза с пошаговыми инструкциями.
Как узнать, точен ли прогноз? Можно ли ему доверять?
Как и в случаях со многими статистическими инструментами, точность прогноза будет зависеть от введенных данных. А поскольку данные редко бывают идеальными, очень важно изучить прогноз и понять, насколько он применим в вашем конкретном случае. Есть несколько способов оценить точность прогноза:
Посмотрите на ширину доверительного интервала (см. выше).
Поэкспериментируйте с датой начала прогноза, установив дату, предшествующую последней хронологической точке. Вы увидите, как бы выглядел ваш прогноз, будь он вычислен только по более ранним данным. Сравнив прогнозный ряд с фактическим, вы сможете оценить точность прогноза.
В примере внизу это отмечено красным цветом: как видно, прогноз был очень близок к фактическим данным.
В этом случае тот же самый результат получился бы с помощью функций листа, если ввести только часть хронологического ряда, а затем сравнить прогноз с фактическими данными.
Если вы разбираетесь в статистике, установите флажок Включить статистические данные прогноза, чтобы отобразить сводные показатели точности.
Установите флажок «Включить статистические данные прогноза», чтобы отобразить таблицу статистических значений в прогнозе.
Пример выполнения задания № 2 в Excel и Calc — Студопедия
- На основе ширины доверительного интервала можно определить точность прогноза.
- Экспериментируя с расширенными функциями (учет отсутствующих точек, сезонности и др.), можно отслеживать, как сужается и расширяется предварительный доверительный интервал. Это позволяет определить, насколько полученная модель соответствует хронологическим данным.
Как видим, аргументы у данной функции в точности повторяют аргументы оператора ТЕНДЕНЦИЯ , так что второй раз на их описании останавливаться не будем, а сразу перейдем к применению этого инструмента на практике.
Прогнозирование в Excel
Аппроксимация функции нескольких независимых переменных (множественная регрессия) – очень интересная, имеющая огромное практическое значение задача! Если научиться ее решать, то можно стать почти волшебником, умеющим делать очень достоверные прогнозы.
. результатов различных процессов на основе данных предыдущих периодов времени. В этой статье мы рассмотрим прогнозирование в Excel при помощи очень мощного и удобного инструмента — встроенных статистических функций ЛИНЕЙН и ЛГРФПРИБЛ.
Не пугайтесь «умных» терминов! Все, на самом деле, не так страшно, как кажется вначале! Не пожалейте время и прочтите эту статью внимательно до конца. Умение применять на практике эти функции существенно увеличит ваш «вес» как специалиста в глазах коллег, руководителей и в своих собственных глазах!
Что можно научиться прогнозировать? Очень многое! В принципе, можно научиться прогнозировать любые самые разнообразные результаты процессов в повседневной жизни и работе. Всегда, когда возникает вопрос: «А что будет, если…?» зовите на помощь Excel, рассчитывайте прогноз и проверяйте его достоверность!
Можно научиться прогнозировать зависимость прибыли от цены и объемов продаж любого товара.
Можно научиться прогнозировать зависимость цены автомобилей на вторичном рынке от марки, мощности, комплектации, года выпуска, количества предыдущих владельцев, пробега.
Можно научиться устанавливать зависимость объемов продаж товаров от затрат на различные виды рекламы.
Можно научиться выполнять прогнозирование в Excel стоимости наборов любых услуг в зависимости от их состава и качества.
В производстве, используя косвенные простые параметры, можно научиться прогнозировать трудоемкость и объем выпускаемой продукции, потребление материалов и энергоресурсов, и т.д.
Подготовка к прогнозированию в Excel.
1. Четко формулируем название и единицу измерения интересующего нас результата процесса. Это и есть искомая функция — y , аналитическое выражение которой мы будем определять с помощью MS Excel.
В примере, представленном чуть ниже, y — это срок изготовления заказа в рабочих днях.
2. Производим анализ процесса и выявляем факторы — аргументы функции — x1 , x2 , . xn — наиболее сильно на наш взгляд влияющие на результат – значения функции y . Внимательно назначаем единицы измерений для переменных.
x1 — суммарная длина всех прокатных профилей в метрах, из которых изготавливается заказ
x2 — общая масса всех прокатных профилей в килограммах
x3 — суммарная площадь всех листов в метрах квадратных
x4 — общая масса всех листов в килограммах
3. Собираем статистику – фактические данные – в виде таблицы.
В примере – это фактические данные о металлопрокате и фактических сроках выполненных ранее заказов.
Очень важно при выборе переменных x1 , x2 , . xn учесть их доступность. То есть, значения этих факторов должны быть у вас в виде достоверных статистических данных. Очень желательно, чтобы получение значений статистических данных было простым, понятным и нетрудоемким процессом.
Прогнозирование в Excel сроков изготовления заказов.
Примечательно, что найденная зависимость связывает в одной формуле параметры с различными единицами измерения. Это нормально. Найденные коэффициенты не являются безразмерными. Например, размерность коэффициента b – рабочие дни, а коэффициента m1 – рабочие дни/м.
1. Запускаем MS Excel и заполняем ячейки B4. F16 таблицы Excel исходными статистическими данными. В столбцы пишем значения переменных xi и фактические значения функции y , располагая данные, относящиеся к одному заказу в одной строке.
2. Так как функции ЛИНЕЙН и ЛГРФПРИБЛ — функции выводящие результаты в виде массива, то их ввод имеет некоторые особенности. Выделяем область размером 5×5 ячеек — ячейки I9. M13. Количество выделенных строк всегда — 5, а количество столбцов должно быть равно количеству переменных x i плюс 1. В нашем случае это: 4+1=5.
3. Нажимаем на клавиатуре клавишу F2 и набираем формулу
4. После набора формулы необходимо для ее ввода нажать сочетание клавиш Ctrl+Shift+Enter. (Знак «+» нажимать не нужно, в записи он означает, что клавиши нажимаются последовательно при удержании нажатыми всех предыдущих.)
5. Считываем результаты работы функции ЛИНЕЙН в ячейках I9. M13.
Карту, поясняющую значения каких параметров в каких ячейках выводятся, я расположил в ячейках I4. M8 для удобства чтения сверху над массивом значений.
Общий вид уравнения аппроксимирующей функции y , представлен в объединенных ячейках I2. M2.
Значения коэффициентов b , m1 , m2 , m3 , m4 считываем соответственно
в ячейке M9: b =4,38464164
в ячейке L9: m1 =0,002493053
в ячейке K9: m2 =0,000101103
в ячейке J9: m3 =-0,084844006
в ячейке I9: m4 =0,002428953
6. Для определения расчетных значений функции y — срока изготовления заказа — вводим формулу
7. Копируем эту формулу во все ячейки столбца от G5 до G17 «протягиванием» и сверяем расчетные значения с фактическими. Совпадение очень хорошее!
8. Предварительные действия все выполнены. Уравнение аппроксимирующей функции y найдено. Пробуем выполнить прогнозирование в Excel срока изготовления нового заказа. Вписываем исходные данные.
8.1. Длину прокатных профилей по проекту x1 в метрах пишем
8.2. Массу прокатных профилей x2 в килограммах пишем
8.3. Площадь листового проката, используемого в новом заказе по проекту, x3 в метрах квадратных заносим
8.4. Общую массу листового проката x4 в килограммах вписываем
9. Расчетный срок изготовления заказа y в рабочих днях считываем
в ячейке G17: =$L$9*B17+$K$9*C17+$J$9*D17+$I$9*E17+$M$9 =25,4
Прогнозирование в Excel выполнено. На основе статистических данных мы рассчитали предположительный срок выполнения нового заказа — 25,4 рабочих дней. Остается выполнить заказ и сверить фактическое время с прогнозным.
Анализ результатов.
Мы не будем погружаться глубоко в дебри статистических терминов и расчетов, но некоторых практических аспектов все же придется коснуться.
Обратимся к другим данным в массиве, которые вывела функция ЛИНЕЙН.
В третьей строке в ячейке I11 выведено значение коэффициента множественной детерминации r 2 , а в ячейке J11 — стандартная ошибка для функции — sey .
В четвертой строке в ячейке I12 находится, так называемое F -наблюдаемое значение, а в ячейке J12 — df – количество степеней свободы.
Наконец, в пятой строке в ячейках I13 и J13 соответственно размещены ssreg — регрессионная сумма квадратов и ssresid — остаточная сумма квадратов.
На что следует в регрессионной статистике обратить особое внимание? Что для нас наиболее важно?
1. На сколько достоверно прогнозирует срок изготовления полученное уравнение функции y ? При высокой достоверности аппроксимации значение коэффициента детерминации r 2 близко к максимуму — к 1! Если r 2
2. Определим важность и полезность каждой из четырех переменных x1 , x2 , x3 , x4 в полученной формуле с помощью, так называемой, t -статистики.
2.1. Рассчитываем t4 , t3 , t2 , t1 , соответственно
в ячейке I16: t4 =I9/I10 =26,44474886
в ячейке J16: t3 =J9/J10 =-11,79198416
в ячейке K16: t2 =K9/K10 =3,76748771
в ячейке L16: t1 =L9/L10 =3,949105515
ti = mi / sei
2.2. Вычисляем двустороннее критическое значение tкрит с уровнем достоверности α =0,05 (предполагается 5% ошибок) и количеством степеней свободы df =8
в ячейке M16: tкрит =СТЬЮДРАСПОБР(0,05; J12) =2,306004133
Так как для всех t i справедливо неравенство | ti |> tкрит , то это означает, что все выбранные переменные x i полезны при расчете сроков изготовления заказов – y .
Наиболее значимой переменной при прогнозировании в Excel сроков изготовления заказов y является x 4 , так как | t4 |>| t3 |>| t1 |>| t2 |.
3. Не является ли случайным полученное значение коэффициента детерминации r 2 ? Проверим это, используя F -статистику (распределение Фишера), которая характеризует «неслучайность» высокого значения коэффициента r 2 .
3.1. F -наблюдаемое значение считываем
3.2. F -распределение имеет степени свободы v1 и v2 .
v1 = k — df -1=13-8-1=4
v2 = df =8
Рассчитаем вероятность получения значения F -распределения большего, чем F -наблюдаемое
в ячейке I12: =FРАСП(I12;4;J12) =6,97468*10 -13
Так как вероятность получения большего значения F -распределения, чем наблюдаемое чрезвычайно мала, то из этого следует вывод — найденное уравнение функции y можно применять для прогнозирования сроков изготовления заказов. Полученное значение коэффициента детерминации r 2 не является случайным!
Заключение.
Применение функции MS Excel ЛГРФПРИБЛ почти не отличается от работы с функцией ЛИНЕЙН кроме вида уравнения искомой функции, которое принимает для рассмотренного примера следующий вид:
Статистика множественной регрессии, которую рассчитывает функция ЛГРФПРИБЛ, базируется на линейной модели:
Это означает, что значения, например, sei нужно сравнивать не с mi , а с ln ( mi ). (Подробнее об этом почитайте в справке MS Excel.)
Если в результате использования функции ЛГРФПРИБЛ коэффициент детерминации r 2 окажется ближе к 1, чем при использовании функции ЛИНЕЙН, то применение аппроксимирующей функции вида
y = b *( m1 x 1 )*( m2 x 2 )…*( mn x n ),
Если прогнозное значение функции y находится вне интервала фактических статистических значений y , то вероятность ошибки прогноза резко возрастает!
Для получения информации о выходе новых статей и для скачивания рабочих файлов программ прошу вас подписаться на анонсы в окне, расположенном в конце статьи или в окне вверху страницы.
Отзывы, вопросы и замечания, уважаемые читатели, пишите в комментариях внизу страницы.
ПРОШУ уважающих труд автора СКАЧАТЬ файл ПОСЛЕ ПОДПИСКИ на анонсы статей!
[expert_bq id=»1570″]Степень полинома подбирают таким образом, чтобы она была на единицу больше количества экстремумов максимумов и минимумов кривой. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Прямая линия на графике отображает стабильный рост качества работы менеджера. Величина достоверности аппроксимации равняется 0,9929, что указывает на хорошее совпадение расчетной прямой с исходными данными. Прогнозы должны получиться точными.Прогнозирование в excel с помощью линии тренда. Линия тренда в Excel на разных графиках
Что же мы видим на графике? Оранжевые столбики “осени” как минимум ни чем не больше “весенних”, а то и меньше. Это свидетельствует не об успехе, а скорее наоборот – посетители прибывают, но читают в среднем меньше и на сайте не задерживаются!
