2015-06-28
329
Не вдаючись глибоко в математичний апарат, нагадаємо, що регресивний аналіз є видом статистичного аналізу, що дозволяє оцінити міру залежності між змінними, пропонуючи механізм обчислення передбачуваного значення змінної на основі декількох вже відомих значень. Інакше кажучи, на основі статистичної вибірки відомих значень функції F(x) та аргументів x можна спрогнозувати поведінку функції шляхом підстановки нових значень аргументів.
Умова завдання (для прикладу):
Задана статистична вибірка прибутку підприємства та витрат на рекламу за 8 місяців. Спрогнозувати очікуваний прибуток, якщо підприємство наступні 4 місяці планує затратити такі суми (для економії місця при викладці матеріалу представляємо умову вже в електронній таблиці):
| A | B | C | |
| Місяць | Затрати на | Прибуток | |
| рекламу, тис. грн. | тис. грн. | ||
| Січень | |||
| Лютий | |||
| Березень | |||
| Квітень | |||
| Травень | |||
| Червень | |||
| Липень | |||
| Серпень | |||
| Прогноз | |||
| Вересень | * | ||
| Жовтень | * | ||
| Листопад | * | ||
| Грудень | * |
Хід виконання:
Маємо випадок, коли змінна Y (прибуток - діапазон C3:C10) залежить від однієї змінної X (затрат на рекламу - діапазон B3:B10).
1. Виділяємо діапазон комірок B3:C10, з допомогою команд Data/Sort… (поставивши перемикач в позиції Идентифицировать поля по обозначениям столбцов листа та вибравши сортування Столбец В по возрастанию) сортуємо дані по зростанню даних першого стовпця (затрат на рекламу), будуємо графік залежності прибутку від затрат на рекламу „ XY(Scatter)” (Точечная).
2. Виходячи із зовнішнього виду отриманого графіка, приймаємо рішення про те, що залежність близька до поліноміальної (параболічної).
3. Клацаємо лівою кнопкою „миші” на будь-якій із точок на графіку (всі точки поміняють свою форму), натискуємо праву кнопку „миші”.
4. У вікні, що появилось, вибираємо Добавить линию тренда, вибираємо тип графіку Полиномиальная степені 2, переходимо у закладку Параметры, вмикаємо перемикач Показывать уравнение на диаграмме.
5. Отримуємо на діаграмі лінію тренду (регресії) та рівняння регресії y= -3,2609x2+95,453x-519,68
6. В комірку С12 (для прогнозу) заносимо отриману формулу з посиланням на значення х в комірці В12:
= -3,2609*В12^2+95,453*D12-519,68 та отримуємо прогнозоване значення прибутку для першого значення затрат на рекламу.
7. Копіюємо формулу з комірки С12 в діапазон С13:С15.
8. Робимо висновок: при збільшенні затрат на рекламу до певної міри прибуток зростає, при значенні затрат 15 тис.грн. прибуток максимальний, дальше збільшення затрат на рекламу веде до зменшення прибутку, при значенні затрат 23 тис. грн. у жовтні та 25 тис. грн. у листопаді маємо суттєві збитки.
