В какую ячейку вводить уравнение?
Как сделать прогнозирование в Excel?
Сегодня хочу поделиться информацией, как с помощью программы Excel можно легко и быстро обработать статистические данные и сделать прогноз.
Прогнозирование является неотъемлемой частью планирования. Это может понадобиться, когда Вам необходимо оценить затраты следующего года или предсказать ожидаемые результаты в будущем. В общем задача довольно актуальная сейчас.
Как сделать прогноз в Excel
И, если у Вас есть статистические данные с зависимостью от времени, то вы можете создать на их основе прогноз на то количество лет, которое Вам нужно. Также с помощью прогноза Вы можете предсказывать показатели: будущего объема продаж, потребность в складских запасах или потребительские тенденции.
Вариантов построения прогноза достаточное много, я же хочу показать, как можно сделать простой, жизнеспособный прогноз «на скорую руку». Ведь каким бы точным ни был Ваш прогноз — это всего лишь предположение, и никто не может быть уверенным в том: что и как будет развиваться в будущем.
К слову о будущем))) Наступил Новый год, и когда закончатся все праздники, можно браться за выполнение отложенных дел, планов и мечт!!! В соответствии с этим, рекомендую прочитать статью «Как начать делать то, что хочется». Может быть полезной.
Продолжаем. При помощи встроенных в Excel функций можно построить довольно неплохой прогноз, чем мы с Вами сейчас и займемся. В итоге получим красивый график с прогнозом. Примерно, вот такой, а может быть и лучше:
|
|
|
Исходные данные. Где взять?
Нам нужны исходные данные. Где их взять? На сайтах статистики, конечно. Я для своих статей беру данные на сайте Федеральной Службы Государственной Статистики. Для этого необходимо покопаться в разделе «Официальная статистика» или «Базы данных».
Исходные данные. Как скачать?
Для расчета прогноза потребуются данные за конкретный период. Чем больше данных, тем точнее будет прогноз. Желательно, чтобы были помесячные данные хотя бы за два года или за несколько лет. Для своего примера я скачаю данные «Число персональных компьютеров в организациях» с 2003 по 2014 годы. И составлю прогноз на 5 лет, т.е. до 2019 года. Для этого нужно:
Зайти на сайт Федеральной Службы Государственной Статистики, «Официальная статистика», далее захожу где «Наука, инновации и информационное общество».
Подготовка данных для расчета прогноза на 5 лет
Итак, данные у нас есть. Что с ними необходимо сделать?
Во-первых, мне для простого прогноза не нужны все данные таблицы, поэтому, я удаляю лишние строки, оставив только необходимую информацию для прогноза. А именно: года, и «Число персональных компьютеров в обследованных организациях — всего, тыс. шт.». Вот что должно остаться:
|
|
|
ля удобства уберем с графика все линии. Для этого, выделяем сначала горизонтальные линии/Delete (на клавиатуре), аналогично выделяем вертикальные линии/ Delete (на клавиатуре). Вот так:
Добавим легенду. Для этого нужно щелкнуть по таблице, в правом верхнем углу появится крестик. Щелкнув по нему, выбираем — легенда. В результате появится надпись:
Если мы планируем построить прогноз на 5 лет, то соответственно, нужно продлить столбец с цифрами 1, 2, 3, 4, 5 и т.д. на 5 ячеек. Помните, что они у нас заменят года? Я их для вас выделила желтым цветом. Получаем продленную таблицу следующего вида:
Добавим линию тренда. Для этого необходимо: щелкнуть правой кнопкой мыши по знакомому нам крестику в правом верхнем углу таблицы/выбрать «Линия тренда».На графике появится линейная функция.
Щелкнув по линии тренда мышкой два раза, открывается окно «Формат линии тренда».
Что мы можем с этим делать?
Во-первых, в моем случае все линии на графике будут появляться голубого цвета, так как именно этот цвет запрограммирован по умолчанию. Поэтому, чтобы не запутаться, предлагаю всем новым линиям менять цвета. Для этого переходим во вкладку, где изображено ведерко с краской, устанавливаем точку, где «Сплошная линия», выбираем цвет, например, «Синий».
|
|
|
о-вторых, для дальнейших расчетов необходимо получить уравнение и коэффициент тесноты связи R².
Для этого: возвращаемся в предыдущую вкладку «Параметры линии тренда», и устанавливаем две галочки в самом низу: «показать уравнение на диаграмме» и «поместить на диаграмму величину достоверности аппроксимации». Также установим «Прогноз» вперед на 5 периодов. Как здесь:
В-третьих, рекомендую полученное уравнение и коэффициент тесноты связи также покрасить в синий цвет, что и линия тренда. Так Вы никогда не перепутаете, какое уравнение к какой линии тренда относится.
Для этого: щелкаем по уравнению, чтобы выделить прямоугольник для закрашивания, справа переходим во вкладку, где ведерко с краской. Выбираем: «Заливка» — «Сплошная заливка» — «Цвет» — синий.
Готово!
Аналогично строим пять линий трендов разными цветами, включая уравнения коэффициент тесноты связи. Получаем следующее:
Для постановки точек прогноза выбираем уравнение, где коэффициент тесноты связи R² — наибольшее число. В моем случае это «Полиномиальный тренд» с уравнением и теснотой связи:
|
|
|
Теперь внимательно!!! Выбранное уравнение нужно набрать языком Excel в ячейке начала прогноза.
· Как должно выглядеть уравнение на языке Excel?
· Например, мое уравнение:
· y = −0,1837×2 + 2,9289x + 83,664
· на языке ексел будет выглядеть так:
· =-0,1837*A18^2+2,9289*A18+83,664
Что я сделала? — убрала «y», потому что в Excel все формулы начинаются со знака «=», — ввела знак умножения «*» — в Excel он обозначается звездочкой, — подставила вместо «х» — число года начала прогноза, в моем случае это «А18», — ввела знак, обозначающий степень «^».
В какую ячейку вводить уравнение?
Я ввожу в ячейку «В18», и получаю первую цифру прогноза на 2015 год (вы же помните, что все года мы заменили порядковыми цифрами?).
Аналогичным способом вводим уравнение на все 5 лет прогноза, не забывая заменять адрес ячейки года. У меня это выглядит вот так (уравнение справа поставила для вас).
Теперь добавим полученные точки прогноза на график. Для этого нужно добавить ряды — щелкнуть по области графика правой кнопкой мыши/Выбрать данные/Добавить. Дать название ряда «Прогноз на 2015 год». Ввести значения х и y: «х» — обозначение года 2015, в моем случае он заменен цифрой «13» (ячейка «А18»), а «y» — полученное число прогноза на 2015 год в ячейке «В18».
Аналогичным образом вводим остальные четыре точки.
В итоге получаем прогноз на 5 лет. Я увлеклась и составила прогноз с 2015 по 2020 год, который говорит о том, что число персональных компьютеров в организациях, будет снижаться.
Вот так легко и быстро с помощью Excel можно обработать статистические данные и составить неплохой прогноз, который украсит любую вашу научную работу.
Источник: http://officeassist.ru/excel/kak-sdelat-prognoz-v-excel/
Как в экселе сделать линию тренда. Инструменты прогнозирования в Microsoft Excel
Для наглядной иллюстрации тенденций изменения цены применяется линия тренда. Элемент технического анализа представляет собой геометрическое изображение средних значений анализируемого показателя.
Рассмотрим, как добавить линию тренда на график в Excel.
Добавление линии тренда на график
Для примера возьмем средние цены на нефть с 2000 года из открытых источников. Данные для анализа внесем в таблицу:
Линия тренда в Excel – это график аппроксимирующей функции. Для чего он нужен – для составления прогнозов на основе статистических данных. С этой целью необходимо продлить линию и определить ее значения.
Если R2 = 1, то ошибка аппроксимации равняется нулю. В нашем примере выбор линейной аппроксимации дал низкую достоверность и плохой результат. Прогноз будет неточным.
Внимание!!! Линию тренда нельзя добавить следующим типам графиков и диаграмм:
· лепестковый;
· круговой;
· поверхностный;
· кольцевой;
· объемный;
· с накоплением.
Уравнение линии тренда в Excel
В предложенном выше примере была выбрана линейная аппроксимация только для иллюстрации алгоритма. Как показала величина достоверности, выбор был не совсем удачным.
Следует выбирать тот тип отображения, который наиболее точно проиллюстрирует тенденцию изменений вводимых пользователем данных. Разберемся с вариантами.
Линейная аппроксимация
Ее геометрическое изображение – прямая. Следовательно, линейная аппроксимация применяется для иллюстрации показателя, который растет или уменьшается с постоянной скоростью.
Рассмотрим условное количество заключенных менеджером контрактов на протяжении 10 месяцев:
Обратите внимание! При линейном типе аппроксимации точки данных расположены максимально близко к прямой. Данный вид использует следующее уравнение:
y = 4,503x + 6,1333
· где 4,503 – показатель наклона;
· 6,1333 – смещения;
· y – последовательность значений,
· х – номер периода.
Прямая линия на графике отображает стабильный рост качества работы менеджера. Величина достоверности аппроксимации равняется 0,9929, что указывает на хорошее совпадение расчетной прямой с исходными данными. Прогнозы должны получиться точными.
Чтобы спрогнозировать количество заключенных контрактов, например, в 11 периоде, нужно подставить в уравнение число 11 вместо х. В ходе расчетов узнаем, что в 11 периоде этот менеджер заключит 55-56 контрактов.
Экспоненциальная линия тренда
Данный тип будет полезен, если вводимые значения меняются с непрерывно возрастающей скоростью. Экспоненциальная аппроксимация не применяется при наличии нулевых или отрицательных характеристик.
Построим экспоненциальную линию тренда в Excel. Возьмем для примера условные значения полезного отпуска электроэнергии в регионе Х:
Уравнение имеет следующий вид:
y = 7,6403е^-0,084x
· где 7,6403 и -0,084 – константы;
· е – основание натурального логарифма.
Показатель величины достоверности аппроксимации составил 0,938 – кривая соответствует данным, ошибка минимальна, прогнозы будут точными.
Логарифмическая линия тренда в Excel
Используется при следующих изменениях показателя: сначала быстрый рост или убывание, потом – относительная стабильность. Оптимизированная кривая хорошо адаптируется к подобному «поведению» величины. Логарифмический тренд подходит для прогнозирования продаж нового товара, который только вводится на рынок.
Дата добавления: 2021-04-05; просмотров: 68; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!