Выполнение задания с помощью пакета анализа Excel

В среде Excel оценить уравнение регрессии и исследовать его значимость можно не только с помощью функции ЛИНЕЙН, но и используя пакет анализа. Этот пакет вызывается командой горизонтального меню Сервис/Анализ данных. Если пакет не доступен, то следует его установить с помощью ко­манды Сервис/Надстройки/Пакет анализа. В окне пакета надо выбрать возможность Регрессия. Правила заполнения окна Регрессия достаточно очевидны, при возникновении трудностей можно воспользоваться справкой этого окна. Здесь будут рассмотрены лишь некоторые особенности применения окна
Регрессия.

С помощью окна Регрессия можно обрабатывать только вертикально ориентированные таблицы данных (в которых значения переменных расположены в столбцах). Если таблица ориентирована горизонтально (как в нашем примере), то ее необходимо транспонировать с помощью функции ТРАНСП. Порядок работы с функцией ТРАНСП следующий: выделяем область таблицы-результата (в нашем случае 11 строк, 4 столбца); вызываем функцию ТРАНСП (например, вызываем мастер функций командой Вставка/Функция, а в окне мастера выбираем ТРАНСП); в окно Массив функции мышью вводим диапазон исходной таблицы; завершаем работу с функцией ТРАНСП нажатием клавиш Ctrl+Shift+Enter.

В окне Регрессия указываются диапазоны значений отклика и факторов; флажок Метки ставится в том случае, если эти диапазоны содержат заголовки столбцов. Флажок Константа-ноль ставится, если предполагается b₀=0, флажок Уровень надежности – если расчет доверительных интервалов следует проводить не только для стандартного значения надежности γ =95%, но и для другого значения γ. В простейшем случае другие флажки можно не ставить. В качестве выходного интервала достаточно задать самую левую верхнюю ячейку диапазона результатов. Заполнив таким образом окно Регрессия и нажав кнопку OK, получим результаты, представленные в таблицах 9-11. Прямым шрифтом в таблицах показана информация, выведенная Excel, курсивом приведены термины и обозначения, принятые в этом данном пособии.

 

Таблица 9. Регрессионная статистика
Множественный R	0, 90089922
R-квадрат (R2)	0, 811619404
Нормированный R-квадрат ()	0, 757796377
Стандартная ошибка (s)	0, 950908439
Наблюдения (n)

 

Таблица 10. Дисперсионный анализ
	df (число степеней свободы)	SS (суммы квадратов)	MS (средние квадраты)	F	Значимость F (amin)
Регрессия		QR =27, 27	13, 64	15, 08	0, 0029
Остаток	k 2=7	Qe =6, 320	0, 904
Итого		Q =33, 60

 

Таблица 11. Оценки коэффициентов регрессии

	Коэффи-циенты	Стандартная ошибка	t - стати стика	P-значе ние (amin)	Нижние 95%	Верхние 95%
Y-пересечение	b 0=-3, 54	=1, 91	T 0=-1, 86	0, 106	-8, 05	0, 969
толщина пласта (м)- х 1	b 1=0, 854	=0, 221	T 1=3, 87	0, 006	0, 333	1, 375
уровень механизации работ (%) – х 2	b 2=0, 367	=0, 243	T 2=1, 51	0, 175	-0, 207	0, 942

 

Поясним смысл столбца «Значимость F» таблицы 10. В нем приведено минимальное значение a_min, такое что при a³ a_min выполняется неравенство (35), и, следовательно, гипотеза о незначимости регрессии отвергается. Аналогично в столбце «P-значение» таблицы 11 даны значения a_min, такие что при a³ a_min справедливо неравенство (37), с помощью которого определяется значимость коэффициентов регрессии.

Читателю предлагается убедиться, что с помощью функции ЛИНЕЙН и окна Регрессия пакета анализа получились одинаковые результаты. Заметим, что пакет анализа использует функцию ЛИНЕЙН.

3. Задание на самостоятельную работу*

Предприниматель намерен использовать множественный регрессионный анализ для оценки стоимости офисного здания в заданном районе, используя данные таблицы 12. Для этого предлагается выполнить следующую работу:

1. Определить уравнение регрессии и его характеристики.

2. Проанализировать значения коэффициентов детерминации (стандартного и адаптированного). Можно ли говорить о сильной зависимости между объясняющими переменными и стоимостью здания?

3. Проверить значимость уравнения регрессии по критерию Фишера при уровне значимости 0, 05.

4. Построить доверительные интервалы для коэффициентов уравнения регрессии.

5. Проверить значимость коэффициентов уравнения регрессии при уровне значимости 0, 05. Все ли факторы полезны при оценке стоимости здания?

 

Таблица 12. Данные по стоимости офисных зданий

№	Общая площадь (кв.м) X 1	Количество офисов X 2	Количество входов X 3	Срок эксплуатации (год) X 4	Стоимость (млн. у. е.), Y
1.	2 310
2.	2 333
3.	2 356		1, 5**
4.	2 379
5.	2 402
6.	2 425
7.	2 448		1, 5**
8.	2 471				142, 9
9.	2 494
10.	2 517
11.	2 540