Интерпретация результатов

Результаты, полученные с помощью инструмента Регрессия, относятся к линейной модели преобразованного уравнения регрессии, в котором зависимой переменной является , а независимой – . Эти результаты нельзя сравнивать с линейной моделью, рассмотренной в лабораторной работе №6, так как стандартная ошибка определяется в единицах измерения , а значение нормированного коэффициента детерминации является долей изменений , выраженной через . Для получения уравнения степенной модели необходимо вычислить коэффициент a, выполнив обратное преобразование.

Задание 6.

На основе результатов, полученных с помощью инструмента анализа Регрессия, вычислите коэффициент a для степенной модели.

1. На Листе6 выделите ячейку G14 и введите формулу =EXP(G12) для вычисления коэффициента a.

2. Укажите, какой вид имеет уравнение регрессии полученной степенной модели.

Задание 7.

В степенной модели выполните прогнозирование средней цены для объекта с жилой площадью в 1000 квадратных метров.

1. На Листе6 выделите ячейку A20 и введите в нее значение площади, равное 1000.

2. В ячейку B20 введите формулу для предсказанной цены = G14*A20^G13.

3. Сравните полученную цену с предсказанной ценой в линейной модели.

Экспоненциальное приближение

В экспоненциальной модели уравнение регрессии имеет следующий вид.

.

При построении линии тренда Excel сначала преобразует экспоненциальную модель в линейную. Для этого проводится логарифмирование обеих частей уравнения:

.

Затем применяется обычная линейная регрессия для зависимой переменной и независимой переменной х. В результате Excel определит коэффициент регрессии, соответствующий коэффициенту b экспоненциальной модели, и постоянный член . Чтобы получить уравнение регрессии экспоненциальной модели, необходимо определить коэффициент a. Для этого выполняется обратное преобразование, то есть коэффициент a вычисляется по формуле: .

Замечание. Поскольку Excel выполняет логарифмическое преобразование исходных данных Y, то значения зависимой переменная Y должны быть положительными. Если какое-либо из значений Y равно нулю или отрицательно, то в диалоговом окне Формат линии тренда на вкладке Параметры линии тренда пиктограмма Экспоненциальная будет выделена серым цветом.

Задание 8.

Построить график экспоненциальной функции регрессии, отражающей рост продаж компьютеров за период 1987  1994 г.г.

В таблице 1 представлен временной ряд, определяющий ежегодные продажи компьютеров за период 1987  1994 г.г.

Таблица 1. Данные продаж за год

Год	Продажи
1987	10500
1988	12300
1989	15100
1990	18300
1991	23900
1992	29800
1993	38100
1994	49700

Построим график временного ряда.

1. Добавьте Лист7. В ячейку A1 введите метку Год, а в ячейку B1 введите метку Продажи. Из Таблицы 1 в диапазон A2:B9 введите соответствующие данные.

2. Выделите данные о продажах (диапазон B2:B9), откройте на ленте вкладку Вставка и выберите тип диаграммы График с маркерами. Поместите диаграмму в области D1:L25.

3. На дополнительной вкладке Работа с диаграммами откройте вкладку Конструктор и в группе Данные щелкните на кнопе Выбрать данные…

4. В диалоговом окне Выбор источника данных в области Подписи горизонтальной оси (категории) щелкните на кнопке Изменить.

5. В диалоговом окне Подписи оси укажите диапазон A2:A9. Закройте диалоговые окна, щелкнув на кнопке ОК.

6. Откройте вкладку Макет и, пользуясь кнопками группы Подписи, введите: название диаграммы Ежегодные продажи компьютеров, название основной горизонтальной оси Год (от 1 до 8 для линии тренда), название основной вертикальной оси Единицы продаж.

7. Удалите элемент Легенда

Замечание. Если заголовок диаграммы окажется в Области построения диаграммы, выделите Область построения и измените ее размер таким образом, чтобы верхняя граница была ниже заголовка.

8. Щелкните правой кнопкой мыши на любой точке ряда данных и в контекстном меню выберите команду Добавить линию тренда…

9. В диалоговом окне Формат линии тренда на вкладке Параметры линии тренда установите тип Экспоненциальная. В области Название аппроксимирующей (сглаженной) кривой выберите опцию автоматическое:. Убедитесь, что флажок пересечение кривой с осью Y в точке: не установлен. Установите флажки показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R^2).

10. Откройте вкладку Цвет линии, включите опцию Сплошная линия и установите Цвет: красный.

11. Откройте вкладку Тип линии и установите параметр Ширина: равным 1,5 пт. Щелкните на кнопке Закрыть.

Замечание. Так как для приближения экспоненциальной функции построен график, а не диаграмма рассеяния, то Excel при добавлении линии тренда в качестве значений переменной Х принимает числа от 1 до 8, соответствующие номеру позиции года.

Далее с помощью инструмента анализа Регрессия получим более полные оценки регрессии экспоненциальной модели.

12. Вставьте в книгу Лист8 и скопируйте в него данные с Листа7 (диапазон A1:B9) в такой же диапазон.

13. Выделите столбец B и на вкладке Главная в группе Ячейки выберите команду Вставить→Вставить столбцы на лист.

14. В ячейку B1 введите метку Х, а в ячейки диапазона B2:B9 введите целые числа от 1 до 8. Уменьшите ширину столбца B, дважды щелкнув на правой границе заголовка.

15. В ячейку D1 введите метку Ln(Продажи). Измените ширину столбца D, дважды щелкнув на правой границе заголовка.

16. Выделите ячейку D2 и введите в нее формулу =LN(C2).

17. Скопируйте формулу в остальные ячейки столбца, дважды щелкну по маркеру заполнения ячейки D2.

18. На вкладке Данные выберите команду Анализ данныхРегрессия. В диалоговом окне Регрессия установите следующие параметры.

• Входной интервал Y: укажите диапазон значений зависимой переменной (D1:D9), включая метку в первой строке.

• Входной интервал X: укажите диапазон значений независимой переменной (B1:B9), включая метку в первой строке.

• Метки: включите эту опцию, так как во Входные интервалы X и Y были включены подписи.

• Параметры вывода: включите Выходной интервал, щелкните в текстовой строке и введите ссылку на ячейку F1, указывающую левый верхний угол области вывода результатов. Щелкните на кнопке ОК.

19. Выделите диапазон столбцов F:N и увеличьте ширину столбцов, дважды щелкнув по правой границе в строке заголовков столбцов.

20. Удалите часть результатов, относящихся к дисперсионному анализу. Для этого выделите диапазон F10:N14 и выберите из контекстного меню команду Удалить… В диалоговом окне установите опцию ячейки, со сдвигом вверх. Щелкните на кнопке ОК.