Прогнозирование с помощью функций регрессии Excel

Составление линейных прогнозов: функция ТЕНДЕНЦИЯ

1. Перейдите на Лист 2. Переименуйте Лист 2 в Функция ТЕНДЕНЦИЯ.

2. В ячейках C1: C25 установите формат - Числовой, число десятичных знаков - 2.

3. Введите в рабочий лист Функция ТЕНДЕНЦИЯ следующие данные:

В ячейки A1: A10 содержатся результаты наблюдений. В ячейках B1: B10 - дни месяца.

4. Выделите ячейки C1: C10. В строке формул введите:

=ТЕНДЕНЦИЯ(A1: A10; B1: B10)

По окончании ввода формулы нажмите клавиши Ctrl+Shift+Enter - для ввода формулы массива.

В результате будет получена следующая таблица:

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

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

5. В ячейку B11 введите 11. В ячейку C11 введите формулу:

=ТЕНДЕНЦИЯ(A1: A10; B1: B10; B11)

Формула фактически говорит о следующем: " Если известно, каким образом у-значения в диапазоне А1: А10 соотносятся с х-значениями в диапазоне В1: В10, то какой результат у-значения мы получим, зная новое х-значение временного момента, равное 11? " Полученное значение 15, 87 является прогнозом на основе фактических данных на пока еще не наступивший одиннадцатый временной отсчет.

6. В ячейки B12: B24 введите числа 12-24. Выделите ячейки C11: C24. В строке формул введите:

=ТЕНДЕНЦИЯ(A1: A10; B1: B10; B11: B24)

По окончании ввода формулы нажмите клавиши Ctrl+Shift+Enter - для ввода формулы массива.

Excel вернет в ячейки С11: С24 прогноз на временные моменты с 11 по 24. Данный прогноз будет базироваться на связи между данными наблюдений базовой линии диапазона А1: А10 и временными моментами базовой линии с 1 по 10, указанными в ячейках В1: В10.

7. Выделите ячейки A1: A10, C1: C24. Выполните команду Вставка-Диаграмма. Выберите тип диаграммы - График, левый шаблон в среднем ряду. Нажмите на кнопку Далее. Выберите вкладку Ряд. В списке Ряд выделите Ряд 1. В поле Имя: введите Факт. В списке Ряд выделите Ряд 2. В поле Имя: введите Прогноз. Нажмите на кнопку Готово. Вы получите следующую диаграмму:

8. Сохраните данные в файле 1.xls.

Составление нелинейного прогноза: функция РОСТ

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

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

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

1. Перейдите на Лист 3. Переименуйте Лист 3 в Функция РОСТ.

2. В ячейках С2: С14 установите формат - Числовой, число десятичных знаков - 2.

3. Введите в рабочий лист Функция РОСТ следующие данные:

4. Выделите ячейки C2: C14. В строке формул введите:

=РОСТ(A2: A11; B2: B11; B2: B14)

По окончании ввода формулы нажмите клавиши Ctrl+Shift+Enter - для ввода формулы массива.

В результате будет получена следующая таблица:

5. Выделите ячейки A2: A11, C2: C14. Выполните команду Вставка-Диаграмма. Выберите тип диаграммы - График, левый шаблон в среднем ряду. Нажмите на кнопку Далее. Выберите вкладку Ряд. В списке Ряд выделите Ряд 1. В поле Имя: введите Факт. В списке Ряд выделите Ряд 2. В поле Имя: введите Прогноз. Нажмите на кнопку Готово. Вы получите следующую диаграмму:

6. Сохраните данные в файле 1.xls.