Регрессионный анализ с помощью Пакета анализа

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

 Регрессия− выполняет анализ воздействия на отдельную зависимую переменную Y значений одной или более независимых переменных с помо-щью метода наименьших квадратов;

 Скользящее среднее− рассчитывает значения в прогнозируемом пери-оде на основе среднего значения переменной для указанного числа предше-ствующих периодов; накапливая тем самым сведения о тенденциях измене-ния данных;

 Экспоненциальное сглаживание− используется для предсказания значения на основе прогноза для предыдущего периода, скорректированного с учетом погрешностей в этом прогнозе. При анализе используется константа сглаживания a, для которой наиболее подходящими являются значения от 0,2 до 0,3, что соответствует уровню 20−30 % от ошибки предыдущего прогноза.

 

Для подключения Пакета анализавыполняют команду Сервис / Надстройки…В окне Надстройки(см. рисунок 3) устанавливают флажок напротив строки Пакет анализаи нажимают OK. Далее требуемый инструмент вызывают командой Сервис / Анализ данных…

Трендовый анализ

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

Чаще всего трендовый графический анализ проводят на основе дискретной (точечной) зависимости переменной Y от одной или нескольких независимых переменных X1, X2, …, Xn, представимой в виде точечной диаграммы.

Кроме того, линию тренда можно добавить на большинство плоских диаграмм (рисунок 7), но нельзя добавить на объемные или с накоплением.

Типы линий тренда:

 Линейная− это прямая линия, наилучшим образом описывающая набор данных. Аппроксимирует значения, которые увеличиваются или убывают с постоянной скоростью;

 Логарифмическая− описывает зависимость, которая сначала изменяется быстро (возрастает или убывает), а затем постепенно стабилизируется;

 Полиномиальная− аппроксимирует нестабильные величины, то возрастающие, то убывающие. Степень полинома определяется количеством экстремумов (максимумов и минимумов) линии. Полином степени n может иметь не более чем n − 1 экстремум;

 Степеннаязависимость аппроксимирует данные, характеризуемые постоянной скоростью роста или убывания. Используется только для положи- тельных величин;

 Экспоненциальнаязависимость описывает данные, скорость изменения которых непрерывно возрастает. Используется только для положительных значений;

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

 

Примечание − для построения линейной фильтрации на точечной диаграмме предварительно необходимо отсортировать значения X для получения более достоверного результата.

О том, какой из типов линий тренда наиболее точно описывает данные, судят по значению величины , которая называется достоверностью аппроксимации. Чем ближе к единице, тем лучше линия тренда отражает фактические данные. )

Технология построения линии тренда

1 Создать таблицу.

2 Построить по данным таблицы плоскую (двумерную) диаграмму и отформатировать еѐ.

3 Из контекстного меню ряда данных (точки данных) выбрать Добавить линию тренда…

4 В диалоговом окне Линия трендавыбрать подходящий по внешнему виду тип линии тренда на одноименной вкладке, при необходимости указать дополнительные опции.

5 На вкладке Параметрыдиалогового окна Линия трендауказать назва-ние кривой, выполнение прогнозирования и опции отображения уравнения и величины достоверности аппроксимации на диаграмме и др. Нажать OK.

6 Оценить достоверность полученной линии тренда. При необходимости можно изменить тип, параметры и внешний вид полученной линии в окне Формат линии тренда, вызываемом из еѐ контекстного меню.

Порядок выполнения работы

1 Загрузите MS Excel.

2 Сохраните книгу с именем prognoz.

3 Переименуйте листы:

4 Перейдите на лист Стат. функции.

5 Создайте и отформатируйте таблицу:

 

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

Решение

Используем для расчета коэффициентов функцию ЛИНЕЙН. Напечатаем в ячейке А11Линейный тренд.

Далее выделим диапазон ячеек B14:E11.

Выполним команду Вставка / Функция…и выберем в категории Статистическиефункцию ЛИНЕЙН и заполним окно функции данными:

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

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

В ячейке А12напечатаем слово Проверка, а в ячейке Е12введем формулу, учитывая, что ЛИНЕЙН возвращает коэффициенты в обратном порядке:

=$D$11*B4+$C$11*C4+$B$11*D4+$E$11. (7)

Если скопировать формулу (7) на следующие 9 ячеек вниз, то получим аппроксимацию фактических данных прибыли для цехов со второго по десятый.

7. Выполните в ячейках диапазона A20:E26листа Стат. функцииследующие задания:

 

Задание 1. Рассчитать коэффициенты экспоненциального тренда, наилучшим образом аппроксимирующего исходные данные, используя функцию ЛГРФПРИБЛ. Проверить достоверность экспоненциального тренда, вычислив с помощью показательной функции от полученных коэффициентов (формула (6)) прибыль для всех цехов (рисунок 8). На основе полученных данных сделать вывод, какая из использованных функций дала более достоверную аппроксимацию фактических данных.

8. Выполните:

Пример 2.Спрогнозировать значение прибыли в соответствии с линейным трендом для следующих данных:

затраты на обучение персонала – 5 млн руб.;

затраты на модернизацию производства – 20 млн руб.;

время непрерывной работы – 20 ч/сут.

Решение

Подготовим новые данные на листе Стат. функции:

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

Установим курсор в ячейку E33.

Выполним Вставка / Функция…и выберем из категории Статистическиефункцию ТЕНДЕНЦИЯ. Заполним окно функции данными:

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

Получим значение прогнозируемой прибыли, рассчитанное в соответствии с линейным трендом.

9. Выполните:

Задание 2. Рассчитайте прогнозируемое значение прибыли в соответствии с экспоненциальным трендом для данных примера 9 с помощью функции РОСТ.

10. Выполните:

Задание 3. Найти прогнозируемое значение прибыли при затратах обучение персонала 5 млн руб. по существующим данным затрат на обучение и прибыли из примера 8. Использовать функцию ПРЕДСКАЗ. Сравнить результаты прогноза, полученные с помощью функций ТЕНДЕНЦИЯ, РОСТ и ПРЕДСКАЗ.

 

11. Перейдите на лист Регрессияи скопируйте таблицу «Анализ деяельности предприятия» из примера 8.

12. Преобразуйте шапку таблицы в одну строку:

13 Подключите Пакет анализа: Сервис / Надстройки…/ .

14 Выполните регрессионный анализ данных. Для этого выберите Сер-вис / Анализ данных / Регрессия.

15 Заполните диалоговое окно Регрессия(рисунок 1) и нажмите OK.

Рисунок 1 − Параметры для проведения регрессионного анализа

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

17. Перейдите на лист Анализ данных.

18 Создайте и отформатируйте таблицу:

19 Выполните.

Пример 3.Произвести прогнозирование по данным Общий объем про-дукции инструментом Пакета анализа Скользящее среднее.

Решение

Вызовем инструмент Пакета анализа: Сервис / Анализ данных… / Скользящее среднее / OK.

Входной интервалв данном случае − столбец Общий объем продукции. Кроме того, укажем опции Выходной интервал, Интервали Вывод графи-ка(рисунок 2) и нажмем OK.

Рисунок 2 − Параметры инструмента Скользящее среднее

Примечание − Скользящее среднее рассчитывает данные на основе среднего значения двух или более предыдущих периодов. Поэтому при недостатке данных возвращается ошибка #Н/Д.

 

Для полученной диаграммы выполним форматирование:

1) изменим для каждого ряда данных Тип диаграммы: Точечная диаграмма, на которой значения соединены отрезками;

3) из контекстного меню диаграммы выберем Исходные данные…и для каждого ряда (Фактический и Прогноз) добавим Значения Х:

='Анализ данных'!$A$3:$A$11

3) для ряда Прогноз изменим диапазон:

='Анализ данных'!$E$3:$E$11

4) выберем Параметры диаграммы…и укажем заголовки Ось Х (кате- горий): Годы, Ось Y (значений):Общий объем продукции;

5) выполним форматирование осей и подписей по осям данных.

В результате получим диаграмму, представленную на рисунке 3.

Рисунок 3 Прогноз с использованием скользящего среднего

Задание 4. Произвести прогнозирование инструментом Пакета анализа Экспоненциальное сглаживание. Сравнить результаты прогнозирования, выполненные с помощью двух инструментов Пакета анализа.

20. Перейдите на лист Трендовый анализи скопируйте туда таблицу «Показатели инновационной деятельности организаций промышленного производства в Республике Беларусь».

21. Выделите диапазон ячеек B3:C10 и вызовите − Мастер диаграмм.

22. На первом шаге Мастера выберите тип диаграммы: Точечная(То- чечная диаграмма позволяет сравнивать пары значений).

23. На втором шаге Мастера убедитесь, что диапазон значений

=Тренд!$B$3:$C$10

24. На вкладке Рядзадайте имяряда «Инновационная продукция».

25. На третьем шаге Мастера укажите название диаграммы:«Показатели инновационной деятельности», Ось X (категорий): «Затраты на инновации», Ось Y (значений): «Объем инновационной продукции».

26. На вкладке Линии сеткиустановите отображение основных линий по оси X, а на вкладке Легендаотмените отображение легенды.

27. На четвертом шаге Мастера укажите расположение диаграммы Поместить диаграмму на листе: имеющемсяи нажмите Готово.

28. Выполните форматирование диаграммы.

 

Примечание − форматирование элемента диаграммы выполняется в диалоговом окне, вызываемом из его контекстного меню.

 

29. Добавьте на диаграмму линию тренда. Для этого щелкните правой клавишей мыши по точке данных и выберите из контекстного меню Добавить линию тренда…

30. В диалоговом окне Линия трендаукажите тип тренда: Линейная.

31. На вкладке Параметрыокна Линия трендаустановите опции:

 

32 После нажатия на кнопку OKдиаграмма примет вид (рисунок 4).

Рисунок 4 – Точечная диаграмма по исходным данным с линейным трендом

33. Создайте копию диаграммы на том же листе.

34. Из контекстного меню линии тренда новой диаграммы выберите Формат линии тренда…Далее на вкладке Тип– Экспоненциальная.

35. Для новой линии тренда также укажите отображение уравнения и величины достоверности R2 на диаграмме.

Сравнение показывает, что зависимость более достоверно описывается линией экспоненциального тренда, что подтверждается значением величины достоверности R^2.

36. Добавьте на диаграмму еще один ряд данных. Для этого выберите из контекстного меню диаграммы команду Исходные данные…В диалоговом окне перейдите на вкладку Ряд, нажмите на кнопку Добавитьи укажите данные для общего объема продукции:

37. Добавьте для данных общего объема продукции линию тренда.

38. Установите отображение легенды и выполните дополнительное фор-матирование диаграммы (рисунок 5).

 

 

Рисунок 5 – Трендовый анализ деятельности предприятия

39. Для каждого ряда данных сделайте прогноз, как изменится объем про-дукции при увеличении затрат на 2000 млрд руб. Для этого в окне Формат линии тренда, на вкладке Параметрыукажите:

40. Перейдите на лист Заданиеи выполните:

Задание 5. Провести анализ данных о ВВП, строительстве или основных показателях розничной торговли в Республике Беларусь по вариантам. Для этого:

1) создать таблицу с информацией о выпуске товаров и услуг по отраслям (таблица 1, вариант 1−10), строительстве в (таблица 2, вариант 11), об основных показателях розничной торговли (таблица 3, вариант 12);

2) рассчитать коэффициенты линейного и экспоненциального трендов, используя соответственно функции ЛИНЕЙН и ЛГРФПРИБЛ;

3) сделать прогноз на ближайший период в соответствии с линейным и экспоненциальным трендом с помощью функций ТЕНДЕНЦИЯ и РОСТ;

4) сделать прогноз, используя функцию ПРЕДСКАЗ;

5) построить по двум столбцам данных таблицы точечную диаграмму и отформатировать еѐ;

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

7) сделать прогноз на ближайший период, исходя из наиболее достоверной линии тренда;

8) проанализировать экономический смысл выполненных расчетов и построений.

 

Таблица 1− Выпуск товаров и услуг по отраслям в текущих ценах. Валовой внутренний продукт (ВВП) производственным методом

Вариант

Таблица 2 − Строительство в РФ

Таблица 3 − Основные показатели розничной торговли в РФ