Для таблицы данных, описывающих некоторый процесс и представленных диаграммой, в Excel имеется эффективный инструмент регрессионного анализа, позволяющий:
· строить на основе метода наименьших квадратов и добавлять в диаграмму пять типов регрессий, которые с той или иной степенью точности моделируют исследуемый процесс;
· добавлять к диаграмме уравнение построенной регрессии;
· определять степень соответствия выбранной регрессии отображаемым на диаграмме данным.
На основе данных диаграммы Excel позволяет получать линейный, полиномиальный, логарифмический, степенной, экспоненциальный типы регрессий, которые задаются уравнением
y = y(x), (1)
где x – независимая переменная, которая часто принимает значения последовательности натурального ряда чисел (1; 2; 3; …) и производит, например, отсчет времени протекания исследуемого процесса (характеристики).
1. Линейная регрессия хороша при моделировании характеристик, значения которых увеличиваются или убывают с постоянной скоростью. Это наиболее простая в построении модель исследуемого процесса. Она строится в соответствии с уравнением
y = mx + b, (2)
где m – тангенс угла наклона линейной регрессии к оси абсцисс; b – координата точки пересечения линейной регрессии с осью ординат.
2. Полиномиальная линия тренда полезна для описания характеристик, имеющих несколько ярко выраженных экстремумов (максимумов и минимумов). Выбор степени полинома определяется количеством экстремумов исследуемой характеристики. Так, полином второй степени может хорошо описать процесс, имеющий только один максимум или минимум; полином третьей степени – не более двух экстремумов; полином четвертой степени – не более трех экстремумов и т. д.
В этом случае линия тренда строится в соответствии с уравнением
y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6, (3)
где коэффициенты c0, c1, c2,..., c6 – константы, значения которых определяются в ходе построения.
3. Логарифмическая линия тренда с успехом применяется при моделировании характеристик, значения которых вначале быстро меняются, а затем постепенно стабилизируются.
Данная линия тренда строится в соответствии с уравнением
y = c ln(x) + b, (4)
где коэффициенты b, с – константы.
4. Степенная линия тренда дает хорошие результаты, если значения исследуемой зависимости характеризуются постоянным изменением скорости роста. Примером такой зависимости может служить график равноускоренного движения автомобиля. Если среди данных встречаются нулевые или отрицательные значения, использовать степенную линию тренда нельзя.
Линия строится в соответствии с уравнением
y = c xb, (5)
где коэффициенты b, с – константы.
5. Экспоненциальную линию тренда следует использовать в том случае, если скорость изменения данных непрерывно возрастает. Для данных, содержащих нулевые или отрицательные значения, этот вид приближения также неприменим.
Линия тренда строится в соответствии с уравнением
y = cebx, (6)
где коэффициенты b, с – константы.
Трендом называется общая тенденция изменения данных в зависимости от времени.
При подборе линии тренда Excel автоматически рассчитывает значение величины R2 (коэффициент детерминации), которая характеризует достоверность аппроксимации (степень близости линии тренда к исходным данным): чем ближе значение R2 к единице, тем надежнее линия тренда аппроксимирует исследуемый процесс. При необходимости значение R2 всегда можно отобразить на диаграмме.
Для добавления линии тренда к ряду данных следует:
· активизировать построенную на основе ряда данных диаграмму, т. е. щелкнуть в пределах области диаграммы. В главном меню появится пункт Диаграмма;
· после щелчка на этом пункте на экране появится меню, в котором следует выбрать команду Добавить линию тренда.
Эти же действия легко реализуются, если навести указатель мыши на график, соответствующий одному из рядов данных, и щелкнуть правой кнопкой мыши; в появившемся контекстном меню выбрать команду Добавить линию тренда. На экране появится диалоговое окно Линия тренда с раскрытой вкладкой Тип (рис. 4.1).
Рис. 4.1. Определение линии тренда (функции)
После этого необходимо выбрать на вкладке Тип необходимый тип линии тренда (по умолчанию выбирается тип Линейный). Для типа Полиномиальная в поле Степень следует задать степень выбранного полинома.
В поле Построен на ряде перечислены все ряды данных рассматриваемой диаграммы. Для добавления линии тренда к конкретному ряду данных следует в поле Построен на ряде выбрать его имя.
Рис. 4.2. Выбор параметров
При необходимости, перейдя на вкладку Параметры (рис. 4.2), можно для линии тренда задать следующие параметры:
1) изменить название линии тренда в поле Название аппроксимирующей (сглаженной) кривой;
2) задать количество периодов (вперед или назад) для прогноза в поле Прогноз.
Прогнозирование заключается в том, чтобы каким-либо образом определить значение Y0 переменной Y при таком наборе значений и факторов t0, x10, x20,…, xm0, которого нет в исходной таблице данных.
Временной шаг, с которым представлены в таблице данные, называется шагом, или периодом, прогнозирования;
3) вывести в область диаграммы уравнение линии тренда, для чего следует включить флажок показать уравнение на диаграмме;
4) вывести в область диаграммы значение достоверности аппроксимации R2, для чего следует включить флажок поместить на диаграмму величину достоверности аппроксимации (R^2);
5) задать точку пересечения линии тренда с осью Y, для чего следует включить флажок пересечение кривой с осью Y в точке.
Для того чтобы начать редактирование уже построенной линии тренда, существует три способа.
1. Воспользоваться командой Выделенная линия тренда из меню Формат, предварительно выбрав линию тренда.
2. Выбрать команду Формат линии тренда из контекстного меню, которое вызывается щелчком правой кнопки мыши по линии тренда.
3. Двойным щелчком по линии тренда.
Рис. 4.3. Определение формата линии тренда
На экране появится диалоговое окно Формат линии тренда (рис. 4.3), содержащее три вкладки: Вид, Тип, Параметры, причем содержимое последних двух полностью совпадает с аналогичными вкладками диалогового окна Линия тренда (см. рис. 4.1, 4.2). На вкладке Вид можно задать тип линии, ее цвет и толщину.
Задание 1. С таблицей данных о прибыли автотранспортного предприятия за 2000–2007 гг. (табл. 4.1) необходимо выполнить следующие действия:
1) построить диаграмму;
2) в диаграмму добавить линейную и полиномиальную (квадратичную и кубическую) линии тренда;
3) вывести уравнения полученных линий тренда, а также величины достоверности аппроксимации R2 для каждой из них;
4) используя уравнения линий тренда, получить табличные данные по прибыли предприятия для каждой линии тренда за 2000–2009 гг.;
5) составить прогноз по прибыли предприятия на 2008 и 2009 гг.
Таблица 4.1
Исходные данные
| № п/п | Год | Прибыль, тыс. руб. |
| 159 500 | ||
| 167 800 | ||
| 165 400 | ||
| 178 600 | ||
| 178 900 | ||
| 184 700 | ||
| 184 000 | ||
| 185 500 | ||
| ? | ||
| ? |
Методика выполнения
1. В диапазон ячеек A3:C13 рабочего листа Excel вводим исходную таблицу (рис. 4.4).
2. Выделив диапазон ячеек В4:С11, строим диаграмму.
3. Активизируем построенную диаграмму и по описанной выше методике после выбора типа линии тренда в диалоговом окне Линия тренда поочередно добавляем в диаграмму линейную, квадратичную и кубическую линии тренда. В этом же диалоговом окне открываем вкладку Параметры, в поле Название аппроксимирующей (сглаженной) кривой вводим наименование добавляемого тренда, а в поле Прогноз вперед на периодов задаем значение 2, так как планируется сделать прогноз по прибыли на два года вперед. Для вывода в области диаграммы уравнения регрессии и значения достоверности аппроксимации R2 включаем флажки Показывать уравнение на экране и Поместить на диаграмму величину достоверности аппроксимации (R^2). Для лучшего визуального восприятия изменяем тип, цвет и толщину построенных линий тренда, для чего воспользуемся вкладкой Вид диалогового окна Формат линии тренда.
Рис. 4.4. Исходная таблица
4. Для получения табличных данных по прибыли предприятия для каждой линии тренда за 2000–2007 гг. воспользуемся уравнениями линий тренда, представленными на рис. 4.5. Для этого в ячейки диапазона D3:F3 вводим текстовую информацию о типе выбранной линии тренда: Линейный тренд, Квадратичный тренд, Кубический тренд. Далее вводим в ячейку D4 формулу линейной регрессии и, используя маркер заполнения, копируем эту формулу c относительными ссылками в диапазон ячеек D5:D13. Следует отметить, что каждой ячейке с формулой линейной регрессии из диапазона ячеек D4:D13 в качестве аргумента стоит соответствующая ячейка из диапазона A4:A13. Аналогично для квадратичной регрессии заполняется диапазон ячеек E4:E13, а для кубической регрессии – диапазон ячеек F4:F13. Таким образом, составлен прогноз по прибыли предприятия на 2008 и 2009 гг. с помощью трех трендов. Полученная таблица значений представлена на рис. 4.6.
Рис. 4.5. Динамика прибыли предприятия, линейная,
квадратичная и кубическая линии тренда
Рис. 4.6. Прогнозирование прибыли предприятия
Задание для самостоятельной работы
С таблицей данных о прибыли автотранспортного предприятия за 2000–2007 гг. (см. табл. 4.1), приведенной в задаче 1, необходимо выполнить следующие действия.
1) построить диаграмму, в которую необходимо добавить логарифмическую, степенную и экспоненциальную линии тренда;
2) вывести уравнения полученных линий тренда, а также величины достоверности аппроксимации R2 для каждой из них;
3) используя уравнения линий тренда, получить табличные данные о прибыли предприятия для каждой линии тренда за 2000–2007 гг.;
4) составить прогноз о прибыли предприятия на 2008 и 2009 гг., используя эти линии тренда.
