Регрессионный анализ с применением электронных таблиц ms excel
Электронные таблицы, такие как Excel, имеют встроенную процедуру регрессионного анализа, легкую в применении.
Регрессионный анализ с помощью MS Ехсеl требует выполнения следующих действий:
- выберите пункт меню «Сервис - Надстройки»;
- в появившемся окне отметьте галочкой надстройку Analysis ToolPak – VBA нажмите кнопку ОК.
Если в списке Analysis ToolPak - VВА отсутствует, выйдите из MS Ехсеl и добавьте эту надстройку, воспользовавшись программой установки Мiсrosоft Office. Затем запустите Ехсеl снова и повторите эти действия. Убедившись, что надстройка Analysis ToolPak - VВА доступна, запустите инструмент регрессионного анализа, выполнив следующие действия:
- выберите пункт меню «Сервис - Анализ» данных;
- в появившемся окне выберите пункт «Регрессия» и нажмите кнопку ОК. На рисунке 16.3 показано окно ввода данных для регрессионного анализа.
Рисунок 16.3 – Окно ввода данных для регрессионного анализа
Таблица 16.2 показывает выходной результат регрессии, содержащий описанные выше статистические данные.
Примечание: для того чтобы получить поточечный график (ХY график), используйте «Мастер Диаграмм» MS Excel.
Получаем: Y' = 10,5386 + 0,563197 Х (d виде Y' = а + bХ) с R2=0,608373=60,84%.
Все полученные данные ответствуют данным, рассчитанным вручную.
Таблица 16.2 – Результаты регрессионного анализа
в электронных таблицах MS Excel
| Вывод итогов | ||||||
| Регрессионная статистика | ||||||
| Множественный R | 0,7800 | |||||
| R-квадрат | 0,6084 | |||||
| Нормированный R-квадрат | 0,5692 | |||||
| Стандартная ошибка | 2,3436 | |||||
| Наблюдения | ||||||
| Дисперсионный анализ | ||||||
| df | SS | MS | F | Значимость F | ||
| Регрессия | 85,3243 | 85,3243 | 15,5345 | 0,0028 | ||
| Остаток | 54,9257 | 5,4926 | ||||
| Итого | 140,2500 | |||||
| Коэффи-циенты | Стандарт-ная ошибка | t-статистика | Р- значение* | Нижние 95% | Верхние 95% | |
| Свободный член | 10,5836 | 2,1796 | 4,8558 | 0,0007 | 5,7272 | 15,4401 |
| Линейный коэффициент | 0,563197 | 0,1429 | 3,9414 | 0,0028 | 0,2448 | 0,8816 |
| *Р – значение для переменной X, равное 0,0028 показывает, что истинное значение переменной коэффициента с 0,28%-ной вероятностью равна нулю, что предполагает высокую точность прогнозируемого значения, равного 0б563197. |
Таблица 16.3 показывает выходной результат регрессии, полученный с применением популярного программного обеспечения Minitab для статистического анализа.
Таблица 16.3 – Результаты регрессионного анализа Minitab
| Анализ регрессии Уравнение регрессии: FO=10,6+0,563 DLH | |||||
| Прогнозируемые параметры | Коэффициент | Стандартное отклонение | t-значение | P | |
| Константа | 10,584 | 2,180 | 4,86 | 0,000 | |
| DLH | 0,5632 | 0,1429 | 3,94 | 0,003 | |
| s=2,344 | R-квадрат=60,8% | R-квадрат (нормированный)=56,9% | |||
| Анализ отклонений | |||||
| Показатель | DF | SS | MS | F | P |
| Регрессия | 85,324 | 85,324 | 15,53 | 0,003 | |
| Отклонение | 54,926 | 5,493 | |||
| Итого | 140,250 |
ВЫВОДЫ
C помощью регрессионного анализа устанавливается зависимость между изменениями независимых переменных и значениями зависимой переменной. Регрессионный анализ - популярный метод для прогнозирования продаж. В этой главе обсуждался широко распространенный способ оценки значений, так называемый метод наименьших квадратов. Метод наименьших квадратов рассматривался применительно к модели простой регрессии Y = а + bх. Обсуждались различные статистические коэффициенты, характеризующие добротность и надежность уравнения (согласие модели) и помогающие установить доверительный интервал.
Показано применение электронных таблиц MS Ехсеl для проведения регрессионного анализа шаг за шагом. С помощью электронных таблиц можно не только составить уравнение регрессии, но и рассчитать статистические коэффициенты.
Глава 17. Прогнозирование денежного потока: