 |
Интерпретация результатов.
|
|
|
|
Результаты, полученные с помощью инструмента Регрессия относятся к линейной модели преобразованного уравнения регрессии, в котором зависимой переменной является 
, а независимой – 
. Эти результаты нельзя сравнивать с линейной моделью, рассмотренной в лабораторной работе №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. Данные продаж за год
| Год | Продажи |
Построим график временного ряда с использованием Мастера диаграмм.
1. Добавьте Лист7. В ячейку A1 введите метку Год, а в ячейку B1 введите метку Продажи. Из Таблицы 1 в диапазон A2:B9 введите соответствующие данные.
2. Выделите данные о продажах (диапазон B2:B9) и щелкните ра кнопке Мастер диаграмм.
3. В диалоговом окне Мастер диаграмм (шаг 1 из 4): тип диаграммы на вкладке Стандартные в области Тип: выберите График, а в области Вид выберите График с маркерами, помечающими точки данных. Щелкните на кнопке Далее.
4. В диалоговом окне Мастер диаграмм (шаг 2 из 4): источник данных диаграммы на вкладке Ряд щелкните в текстовой строке Подписи оси X: и выберите диапазон A2:A9. Щелкните на кнопке Далее.
5. В диалоговом окне Мастер диаграмм (шаг 3 из 4): параметры диаграммы откройте вкладку Заголовки. В текстовой строке Название диаграммы: введите Ежегодные продажи компьютеров (график). В строке Ось X (категорий): введите Год (от 1 до 8 для линии тренда). В строке Ось Y (значений): введите Единицы продаж. Откройте вкладку Легенда и снимите флажок Добавить легенду. Щелкните на кнопке Далее.
|
|
|
6. В диалоговом окне Мастер диаграмм (шаг 4 из 4): размещение диаграммы включите опцию имеющемся: и щелкните на кнопке Готово. Разместите диаграмму в области D1:L25.
7. Щелкните правой кнопкой мыши на любой точке данных (Ряд1) и в контекстном меню выберите команду Добавить линию тренда…
8. В диалоговом окне на вкладке Тип щелкните по пиктограмме Экспоненциальная. Откройте вкладку Параметры и включите опции показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R^2). Щелкните на кнопке ОК.
9. Щелкните правой кнопкой мыши по линии тренда и в контекстном меню выберите команду Формат линии тренда… В диалоговом окне на вкладке Вид выберите цвет: линии зеленый. Щелкните на кнопке ОК.
10. Щелкните в выведенном на диаграмму тексте справа от уравнения регрессии, нажмите сначала на клавишу [Delete], а затем три-четыре раза на клавишу пробела. Текст с уравнением и значением R 2 расположится в одну строку. Переместите текст под заголовок диаграммы.
Замечание. Так как для приближения экспоненциальной функцией строится график, а не точечная диаграмма рассеяния, то Excel данные о годе использует как подписи оси X, а роль значений x при построении экспоненциальной линии тренда выполняют числа от 1 до 8.
Далее с помощью инструмента анализа Регрессия получим более полные оценки регрессии экспоненциальной модели.
11. Вставьте в книгу Лист8 и скопируйте в него данные с Листа7 (диапазон A1:B9) в такой же диапазон.
12. Выделите столбец B и из контекстного меню выберите команду Добавить ячейки.
13. В ячейку B1 введите метку Х, а в ячейки диапазона B2:B9 введите целые числа от 1 до 8. Уменьшите ширину столбца B, дважды щелкнув на правой границе заголовка.
14. В ячейку D1 введите метку Ln(Продажи). Измените ширину столбца D, дважды щелкнув на правой границе заголовка.
15. Выделите ячейку D2 и введите в нее формулу =LN(C2).
16. Скопируйте формулу в остальные ячейки столбца, дважды щелкну по маркеру заполнения ячейки D2.
17. Выберите команду: Сервис®Анализ данных®Регрессия. В диалоговом окне Регрессия установите следующие параметры.
|
|
|
Ü Входной интервал Y: укажите диапазон значений зависимой переменной (D1:D9), включая метку в первой строке.
Ü Входной интервал X: укажите диапазон значений независимой переменной (B1:B9), включая метку в первой строке.
Ü Метки: включите эту опцию, так как во Входные интервалы X и Y были включены подписи.
Ü Параметры вывода: включите Выходной интервал, выберите текстовую строку и введите в нее ссылку на ячейку F1, указывающую левый верхний угол области вывода результатов. Щелкните на кнопке ОК.
18. Выделите диапазон столбцов F:N и увеличьте ширину столбцов, дважды щелкнув по правой границе в строке заголовков столбцов.
19. Удалите часть результатов, относящихся к дисперсионному анализу. Для этого выделите диапазон F10:N14 и выберите из контекстного меню команду Удалить… В диалоговом окне установите опцию ячейки, со сдвигом вверх. Щелкните на кнопке ОК.
|
|
|
