Задание №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 - Результат линейной регрессии
Таблица 2.2 - Результат экспоненциальной регрессии
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 – Результат экспоненциальной регрессии
|
