Технология решения задач корреляционного и регрессионного анализа с применением Microsoft Excel
Задачами регрессионного анализа являются выбор типа модели (формы связи), установление степени влияния независимых переменных на зависимую и определение расчетных значений зависимой переменной (функции регрессии). Задачи корреляционного анализа сводятся к измерению тесноты известной связи между варьирующими признаками, определению неизвестных причинных связей (причинный характер которых должен быть выяснен с помощью теоретического анализа) и оценке факторов, оказывающих наибольшее влияние на результативный признак.
Пакет анализа — это надстройка, которая предоставляет широкие возможности для проведения статистического анализа маркетинговых исследований.
Для установки средств Пакета анализа необходимо выполнить следующие действия:
1) выбрать команду Сервис — Надстройки;
2) в диалоговом окне Надстройки установить флажок Пакет анализа;
3) щелкнуть по кнопке ОК (рис. 6.5).
Рис. 6.5. Диалоговое окно Надстройки
Рассмотрим построение однофакторной линейной регрессионной модели связи изучаемых признаков на примере рынка образовательных услуг (табл. 6.17), т.е. определим взаимосвязь престижности специальности и количества студентов, желающих поступить на эту специальность.
Таблица 6.17
Исходные данные
|
Престижность специальности х, баллы |
Количество студентов, выбирающих специальность у, чел. |
|
1 |
4 |
|
2 |
9 |
|
3 |
12 |
|
4 |
17 |
|
5 |
38 |
|
Σ15 |
Σ80 |
Регрессионный анализ заключается в определении аналитического выражения между факторным (x) и результативным (у) признаками. В результате работы инструмента Регрессия производится расчет параметров a0 и а1 уравнения линейной регрессии у = а() + а1х и проверка его адекватности исследуемым фактическим данным. В Microsoft Excel необходимо перенести данные табл. 6.17, далее запустить инструмент Регрессия.
1. Выбрать Сервис — Анализ данных — Регрессия — ОК. Появится окно Регрессия (рис. 6.6). 2
Рис. 6.6. Окно Регрессия
2. Установить входной интервал Y — диапазон ячеек таблицы со значениями признака Y (В3:В7).
3. Входной интервал X — диапазон ячеек таблицы со значениями X (А3:А7).
4. Метки — не активизировать.
5. Уровень надежности — 95%.
6. Константа — ноль — не активизировать.
7. Выходной интервал — ячейка с параметрами А9.
8. Новый рабочий лист / Новая рабочая книга — не активизировать.
9. График остатков — не активизировать.
10. График подбора — активизировать.
11. График нормальной вероятности — не активизировать (рис. 6.7). Нажать О К.
Рис. 6.7. Окно Регрессия с заданными параметрами
В результате указанных действий осуществляется вывод в заданный диапазон рабочего листа выходных данных (табл. 6.18— 6.20) и одного графика (рис. 6.8).
Таблица 6.18
Вывод итогов
|
Регрессионная статистика |
Значение |
|
Множественный R |
0,912292 |
|
R-квадрат |
0,832277 |
|
Нормированный R-квадрат |
0,776369 |
|
Стандартная ошибка |
6,228965 |
|
Наблюдения |
5 |
Таблица 6.19
Дисперсионный анализ
|
Показатель |
SS |
MS |
F |
Значимость F |
||||
|
Регрессия |
1 |
577,6 |
577,6 |
14,8866 |
0,030768 |
|||
|
Остаток |
3 |
116,4 |
38,8 |
- |
- |
|||
|
Итого |
4 |
694 |
- |
- |
- |
|||
|
Показатель |
Коэффициент |
Стандартная ошибка |
t-статистика |
Р-значение |
Нижние 95% |
Верхние 95% |
Нижние 95,0% |
Верхние 95,0% |
|
Y-пересечен не |
-6,8 |
6,532993 |
-1,04087 |
0,374445 |
-27,5909 |
13,99092 |
-27,5909 |
13,99092 |
|
Переменная X1 |
7,6 |
1,969772 |
3,858315 |
0,030768 |
1,331302 |
13,8687 |
1,331302 |
13,8687 |
Таблица 6.20
Вывод остатка
|
Наблюдение |
Предсказанное Y |
Остаток |
|
1 |
0,8 |
3,2 |
|
2 |
8,4 |
0,6 |
|
3 |
16 |
-4 |
|
4 |
23,6 |
-6,6 |
|
5 |
31,2 |
6,8 |
Рис. 6.8. Переменная Х1. График подбора
Интерпретация параметров инструмента Регрессия такова:
• множественный R — линейный коэффициент корреляции (r);
• R-квадрат — коэффициент детерминации (R2);
• стандартная ошибка — среднее квадратическое отклонение расчетных значений от фактических (σε);
• наблюдения — число наблюдений (п);
• Df — число степеней свободы;
• SS — сумма квадратов;
• F — критерий Фишера;
• MS — дисперсия факторная и остаточная (
);
• Υ-пересечение — свободный член регрессии (a0);
• переменная Х1 — коэффициент регрессии (а1);
• коэффициенты — значения коэффициентов уравнения регрессии;
– нижние 95% и верхние 95% — соответственно нижние и верхние границы доверительных интервалов для коэффициентов регрессии, рассчитанные для уравнения надежности Р = 0,95;
– нижние 68,3% и верхние 68,3% — соответственно нижние и верхние границы доверительных интервалов для коэффициентов регрессии, рассчитанные для уравнения надежности Р = 0,683;
• пересеченное Υ — расчетные значения результативного признака (yi);
• остатки — отклонение расчетных значений от фактических (yi - yфi).
4. Технология прогноза объема продаж с помощью Мастера функций. Прогнозирование в маркетинге является важнейшим элементом системы планово-экономических расчетов. В Microsoft Excel есть несколько стандартных функций, позволяющих решать задачи прогнозирования.
Вызов функции ТЕНДЕНЦИЯ для прогноза объема продаж на некоторый товар по временному ряду показан на рис. 6.9. В ячейках В4:В8 находятся данные об объеме продаж за прошедшие пять лет. В ячейку В10 будет возвращено прогнозное значение объема продаж. Для открытия диалога Мастер функций нажата кнопка fx на панели управления. Функция ТЕНДЕНЦИЯ расположена в категории Статистические (левое окно диалога). Для того чтобы вызвать функцию ТЕНДЕНЦИЯ, нужно щелкнуть мышью (левой клавишей) по названию функции в правом окне и нажать кнопку ОК.
Рис. 6.9. Вызов функции ТЕНДЕНЦИЯ
Заполнение диалога функции ТЕНДЕНЦИЯ показано в табл. 6.21 и на рис. 6.10.
Таблица 6.21
Окно диалога функции ТЕНДЕНЦИЯ
|
Значение |
Формула |
|
Изв_знач_Y |
В4:В8 |
|
Изв_знач_Х |
Л4:Л8 |
|
Нов_знач_Х |
А10 |
|
Константа |
Заполнять не нужно |
Рис. 6.10. Диалог функции ТЕНДЕНЦИЯ
Функция ТЕНДЕНЦИЯ строит прогноз на основе модели
где у — прогнозируемая переменная (в данном случае объем продаж); а, b — параметры; х — независимая переменная (в данном случае время).
Известные значения х — это пять предплановых лет, пронумерованных от 1 до 5. Известные значения у — объемы продаж в эти годы. Новое значение х — это номер планового года, в данном случае 6.
После ввода формул в окна диалога нужно нажать кнопку ОК. Результат вычислений показан на рис. 6.11.
Рис. 6.11. Прогноз объема продаж