Анализ деятельности офиса

Пример применения функции ЛИНЕЙН в задаче прогнозирования

Приведем теперь пример применения функции ЛИНЕЙН. Менеджер офиса "РР" решил построить уравнение, прогнозирующее продажи одной из книг. В его распоряжении были данные по продажам этой книги за последние 10 недель. Агенты офиса фиксировали также уровень рекламы и количество конкурирующих товаров (книг на аналогичную тему). Используя функцию ЛИНЕЙН, менеджер построил уравнение множественной регрессии. Взгляните, как выглядит лист рабочей книги Excel, где размещены данные о продажах и где менеджер построил уравнение регрессии, основываясь на этих данных:

увеличить изображение
Рис. 8.26. Построение уравнения регрессии по данным продаж

Менеджер построил это уравнение дважды, получив два уравнения - Y1 и Y2, используя выборки измерений разного объема. Этот полезный прием позволяет понять, насколько полученные коэффициенты критичны к измерениям.

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

С содержательной точки зрения важен следующий полученный результат. Оба измеряемых параметра - уровень рекламы и число конкурирующих книг - являются статистически значимыми. Большее влияние на уровень продаж оказывает число книг - конкурентов.

Скажу еще, что менеджер вполне обоснованно решил использовать полученные уравнения как для прогноза будущих продаж, так и для принятия таких решений, как, скажем, повышение уровня рекламы. Чтобы визуально увидеть влияние уровня рекламы на продажи, менеджер использовал полученные уравнения для построения графиков Y1(U) и Y2(U) при фиксированных значениях параметров T и V. Результаты его работы можно увидеть на следующем рисунке:

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

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

Функция ТЕНДЕНЦИЯ и другие функции, используемые для прогноза

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

ТЕНДЕНЦИЯ(Известные_Y, Известные_X, Новые_значения_X, Конст)

Здесь появился один новый параметр, задающий в общем случае матрицу новых значений X. Все остальные параметры имеют тот же смысл, что и в функции ЛИНЕЙН. В результате возвращается вектор прогнозных значений Y, вычисленный в точках, заданных матрицей новых значений X. Каждая ее строка задает одну точку.

Функция ПРЕДСКАЗ - частный случай функции ТЕНДЕНЦИЯ - используется в линейной модели с двумя параметрами, когда уравнение регрессии имеет вид:

y = a*x + b

В этом случае Y и X представляют одномерные массивы данных. Вызов функции таков:

ПРЕДСКАЗ( x; Известные_Y; Известные_X)

Здесь x - точка, для которой строится прогноз.

Мы говорили о возможности построения нелинейного уравнения регрессии, которое простым преобразованием сводится к задаче линейной регрессии. Такое преобразование и осуществляет функция ЛГРФПРБЛ. Формально здесь используется нелинейная модель:

y = b* a1x1 * a2x2 * … * amxm

Простым логарифмированием модель сводится к линейной.

ln(y) = x1* ln(a1) + x2*ln(a2) + … + xm*ln(am) + b

Функция ЛГРФПРБЛ имеет те же параметры, что и функция ЛИНЕЙН. Обращение к ней:

ЛГРФПРБЛ (Известные_значения_Y; Известные_значения_X; Конст; Статистика)

Как работает эта функция, совершенно ясно: она вызывает функцию ЛИНЕЙН, подавая ей на вход не сами измерения Y, а их логарифмы. Полученные оценки достаточно подвергнуть обратному преобразованию - взять экспоненту, и задача решена. Так строится нелинейное уравнение регрессии. Этот нехитрый прием позволяет самому строить новые модели нелинейной регрессии.

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

РОСТ(Известные_Y, Известные_X, Новые_значения_X, Конст)

Построение модели прогноза продаж книг офиса "РР"

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

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

• доверительные интервалы;
• прямолинейный тренд и некоторые его характеристики;
• полиномиальный тренд и его характеристики.

увеличить изображение
Рис. 8.28. Диаграммы, доверительные интервалы и линии тренда

На первой из диаграмм менеджер дополнительно вывел доверительные интервалы шириной в (среднеквадратичное отклонение). Такой интервал с высокой вероятностью накрывает истинное значение. Правда, построенный интервал не отражает динамики изменения данных и, к сожалению менеджера, слишком велик.

На второй диаграмме показан прямолинейный тренд, выведено уравнение регрессии и построен прогноз на ближайшие три недели. Таким образом, здесь в визуальной форме отражены результаты вычислений функций ЛИНЕЙН и ТЕНДЕНЦИЯ. На третьей диаграмме - полиномиальный тренд, где линия регрессии задается кубическим полиномом.

Несколько слов о том, как вручную можно получить диаграммы с трендом и доверительными интервалами. Построив диаграмму, щелкните правой кнопкой в одном из рядов диаграммы и из контекстного меню выберите пункт "Формат рядов данных" и затем вкладку "Y-погрешности". В появившемся окне укажите, отображать ли планки погрешностей, одну или обе, и установите величину погрешности (ее тип, например, стандартное отклонение), количество единиц погрешности. Так визуализируются доверительные интервалы на диаграмме. Для отображения тренда из контекстного меню нужно выбрать пункт "Добавить линии тренда", в появившемся окне - вкладку "Тип" и задать один из 6 возможных типов тренда: линейный, полиномиальный, логарифмический, показательный, экспоненциальный или скользящее среднее. Вкладка "Параметры" позволяет вывести на диаграмму уравнение линии регрессии. Что более важно, тут же можно задать количество интервалов (вперед и назад), для которых будут построены и выведены на график прогнозируемые значения.