Задание №2. Прогнозирование в Excel с помощью регрессионного анализа

Известны следующие данные о 5 недавно проданных подержанных автомобилях: х1 – стоимость продажи, х2 – стоимость аналогичного нового автомобиля, х3 – год выпуска, х4 – пробег, х5 – количество капитальных ремонтов, х6 – экспертные заключения о состоянии кузова и техническом состоянии автомобилей (по 10-бальной шкале). Определить, сколько может стоить автомобиль с соответствующими характеристиками: 20 000руб., 34 000руб., 1990г., 140000 км., 0, 6.

Решение:

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

2. Рассчитаем стоимость продажи необходимого автомобиля по формулам:

= ТЕНДЕНЦИЯ(A2:A6;B2:F6;B7:F7;ЛОЖЬ) - для линейной регрессии

=РОСТ(A10:A14;B10:F14;B15:F15;ЛОЖЬ) - для экспоненциальной регрессии.

 

Таблица 2.1 - Результат линейной регрессии

X1 – стоимость продажи	Х2 – стоимость аналогичного нового автомобиля	Х3 – год выпуска	Х4 - пробег	Х 5 – количество капитальных ремонтов	Х6 – экспертные заключения о состоянии кузова и тех.состоянии автомобилей
20301,06234

 

 

Таблица 2.2 - Результат экспоненциальной регрессии

X1 – стоимость продажи	Х2 – стоимость аналогичного нового автомобиля	Х3 – год выпуска	Х4 - пробег	Х 5 – количество капитальных ремонтов	Х6 – экспертные заключения о состоянии кузова и тех.состоянии автомобилей
52329,03371

 

3. Коэффициенты рассчитаем используя формулы:

=ЛИНЕЙН (A2:A6;B2:F6;;ЛОЖЬ) =13 788,39 - для линейной регрессии и =ЛГРФПРИБЛ (A2:A6;B2:F6;;ЛОЖЬ) =1,09 для экспоненциальной.

4. Рассчитаем погрешности вычислений по формулам

=СТОШYX (A2:A7;B2:F7) для прямолинейной регрессии и

=СТОШYX (A10:A15;B10:F15) для экспоненциальной.

5. Для обоих таблиц построим гистограммы и добавим линии тренда при помощи меню макет - линия тренда - линейное приближение для линейной регрессии и экспоненциальное приближение для экспоненциальной.

 

Рисунок 2.1 – Результат линейной регрессии

 

Рисунок 2.2 – Результат экспоненциальной регрессии