Часть 14. Прогнозирование и перспективные оценки

 

Для целей прогнозирования и перспективных оценок Excel содержит инструменты Скользящее среднее, Регрессия, Экспоненциальное сглаживание, которые включены в Пакет анализа (С е рвисàАна л из данных…).

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

Инструмент Регрессия (линейный регрессионный анализ) более точен и используется для предсказания поведения ряда. Каждое прогнозируемое значение вычисляется с помощью метода наименьших квадратов и «в среднем» наименее отклоняется от экспериментального набора данных.

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

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

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

 

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

 

Реклама (тыс. руб)	1,5		2,5		3,5		4,5		5,5
Продажи (тыс. шт)										?

 

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

 

 

Скользящее среднее

 

Введите исходные данные в колонки А и В, как показано на рис. 2.60. Вызовите инструмент Скользящее среднее через меню С е рвисàАна л из данных…. На рис. 2.61 показано заполнение параметров. Входной интервал $B$2:$B$11 - это значения, по которым делается прогноз. Выходной интервал $C$2 – это ячейка, начиная с которой будет выведен результат. Интервал (по умолчанию равен 3, здесь 2) – это количество предыдущих значений, используемых для расчета очередного прогнозируемого значения. Установите также флажок Вывод графика. Флажок Метки устанавливают, если в первой ячейке входного интервала записано название колонки.

Результат работы инструмента Скользящее среднее показан на рис. 2.60. в колонке С вычислены прогнозируемые значения в указанном интервале. В ячейке С2 стоит значение #Н/Д, поскольку для его вычисления нет 2-х предыдущих значений (Нет Данных).

 

Рис. 2.60

 

Рис. 2.61

 

 

Экспоненциальное сглаживание

 

Вызовите инструмент Экспоненциальное сглаживание через меню С е рвисàАна л из данных…, предварительно подготовив колонки А и В на отдельном листе. На рис. 2.62 показано заполнение параметров, которое во многом схоже с предыдущим. Фактор затухания (по умолчанию равен 0.3) – это корректировочная константа экспоненциального сглаживания, используемая для расчета очередного прогнозируемого значения.

Результат работы инструмента Экспоненциальное сглаживание показан на рис. 2.63. в колонке С вычислены прогнозируемые значения в указанном интервале. Сравните эти результаты с предыдущими.

 

Рис. 2.62

 

Рис. 2.63

 

 

Регрессия

 

Возможности инструмента Регрессия шире и интерпретировать его результаты работы несколько сложнее. Вызовите инструмент Регрессия через меню С е рвисàАна л из данных…, предварительно подготовив колонки А и В на отдельном листе.

На рис. 2.64 показано заполнение параметров инструмента. Входной интервал Y $B$2:$B$10 - это значения, для которых делается прогноз. Входной интервал X $A$2:$A$10 - это переменные, влияющие на прогноз. Их может быть в Excel до 16 (столбцов). Выходной интервал $C$2 – это ячейка, начиная с которой будет выведен результат. Установите также флажок График подбора. Флажок Константу-ноль устанавливают, чтобы линия регрессии прошла через начало координат.

 

Рис. 2.64

 

Результат работы инструмента показан на рис. 2.65. В таблице Регрессионная статистика нас интересует коэффициент детерминации R^2. Значение 0.9 означает, что степень близости достаточна. В таблице Дисперсионный анализ параметр Значимость F оценивает общее качество прогноза – значение меньше 0.05 означает приемлемую достоверность результата. Предсказанные значения Y выведены в таблицу в нижней части рис. 2.65.

В колонке Коэффициенты содержатся свободный член и параметры (коэффициенты) для линейной зависимостей вида y=b+a₁x₁+a₂x₂+…+a_nx_n. Здесь b= – 7.794, a₁=12.1. Таким образом, уравнение для данной зависимости выглядит так: y= – 7.794 + 12.1*x₁. Подставьте в уравнение значение x₁ =6: Вы получите значение 64.806 для ячейки В11.

При большом количестве независимых переменных Х важным является значение колонки Р-Значение. Оно определяет достоверность коэффициентов a₁, a₂, … a₁₆ и их влияние на зависимую переменную Y: значение, близкое к нулю (меньше 0.05) означает, что соответствующее значение X не влияет на Y. Здесь для x₁ Р-Значение =6.686Е-05.

 

Рис. 2.65

 

 

Прогрессии и ряды

 

Мы рассмотрели различные способы прогнозирования и выполнения регрессионного анализа: использование линии тренда на графике, функции ЛИНЕЙН, ТЕНДЕНЦИЯ, ЛГРФПРИБЛ, РОСТ, ПРЕДСКАЗ, инструменты Скользящее среднее, Регрессия, Экспоненциальное сглаживание, из Пакета анализа. Excel содержит еще один простой инструмент для вычисления значений ряда, называемый Прогрессией. С ее помощью можно вручную управлять созданием линейной или экспоненциальной последовательности, также как функциями ТЕНДЕНЦИЯ и РОСТ – значения получаются те же.

Введите исходные данные в колонки А и В, как показано на рис. 2.60. Выделите диапазон ячеек В2:В15 и вызовите инструмент Прогрессия: П равкаàЗаполн и тьàПро г рессия…. Параметры окна заполните, как показано на рис. 2.66. Результат показан на рис. 2.67.

 

Рис. 2.66

 

Рис. 2.67

 

Из рис. 2.66 видно, что инструмент Прогрессия может формировать арифметические и геометрические прогрессии с любым шагом, размещать их по строкам или столбцам. Если в ячейках уже содержатся первые члены прогрессии, и требуется создать прогрессию автоматически, установите флажок Автоматическое определение шага. Если выбрано построение арифметической прогрессии, то вычисление ее шага производится с применением алгоритма наименьших квадратов и аппроксимацией существующих значений формулой (y=ax+b), где b — шаг прогрессии. Если выбрано построение геометрической прогрессии, то вычисление ее шага производится также с применением алгоритма наименьших квадратов, но используется формула (y=b*a^x). В обоих случаях не учитывается значение, введенное в поле Шаг. В поле Предельное значение при необходимости вводят число, которое определяет значение последнего члена прогрессии.

Формирование числовых последовательностей и вычисление суммы n первых членов числовых рядов в Excel выполняется довольно просто: с помощью инструмента Прогрессия формируется последовательность чисел, которая затем суммируется функцией СУММ.