Для исследования статистической зависимости одного результирующего признака от двух и более факторных признаков в Excel есть две возможности: инструмент Регрессия для случая линейной статистической зависимости и непосредственное применение метода наименьших квадратов в случае зависимости любого вида.
Алгоритм применения инструмента Регрессия отличается от описанного выше для случая двумерной модели только количеством исходных данных, размещаемых на рабочем листе и соответственно диапазоном входных параметров, вводимом в диалоговом окне Регрессия. Выходные данные также отличаются только количеством информации при сохранении их смысла.
Регрессионная статистика
Множественный R
0,762322
R-квадрат
0,581135
Нормированный R-квадрат
0,563682
Стандартная ошибка
50,23613
Наблюдения
Дисперсионный анализ
df
SS
MS
F
Значимость F
Регрессия
168064,8
84032,39
33,2977
8,51E-10
Остаток
121136,1
2523,668
Итого
289200,9
Коэффициенты
Стандартная ошибка
t-статистика
P-Значение
Нижние 95%
Верхние 95%
Y-пересечение
225,7848
27,41026
8,237239
9,67E-11
170,6728
280,8968
X8
23,38168
10,96783
2,131842
0,038166
1,329382
45,43398
X4
-503,93
69,72031
-7,22788
3,29E-09
-644,112
-363,748
Рис.8. Регрессия Y2 на X4,X8.
На рис.8 приведены результаты применения инструмента Регрессия к статистическим данным по признакам X4–X8–Y2.
Оценка линейной функции регрессии y2 на x4,x8 имеет вид:
Значение F–критерия Fрасч =33,2977, что значительно больше Fкр = 3,18 Это означает, что оценка достаточно хорошо согласуется с данными наблюдений. Это подтверждается и достаточно высоким значением коэффициента детерминации R2 = 0,5811351. Расчетные значения t –статистики для свободного члена и коэффициента при x4 больше tкр = 2,009, что подтверждает их значимость. Для коэффициента при x8 tрасч близко к критическому значению, что ставит под сомнение его значимость.
В случае нелинейной регрессии необходимо выполнять действия, предусмотренные методом наименьших квадратов(МНК), используя вычислительные возможности Excel. Расположение исходных данных и формул в таблице Excel приведено на рис.9.
Все формулы вводятся только в верхнюю строку, а затем копируются по всему столбцу. На рис.9 приведены расчеты поиска оценок линейной P(x) и квадратичной P2 (x) функции регрессии. Параметры функции регрессии βj расположены в ячейках A56 ÷ C56 для линейной зависимости и в ячейках A59 ÷ F59 для квадратичной зависимости (см. рис.10). Ячейки F53 и I53 содержат значения функций Q – суммы квадратов отклонений.
Значения βj находятся с помощью надстройки Excel Поиск решения по такому алгоритму:
– установить курсор на ячейке, содержащей значение функции Q (Q2);
– Сервис – Поиск решения;
– в появившемся диалоговом окне Поиск решения (рис.11) проверить, стоит ли в поле Установить целевую ячейку адрес функции Q (Q2), и если нет, то ввести его;
– в поле Равной щелкнуть пункт минимальному значению;
– в поле Изменяя ячейки ввести диапазон ячеек, которые отведены для значений искомых параметров ;
– щелкнуть по кнопке Выполнить;
– если решение найдено, сообщение об этом появится в диалоговом окне, где нужно щелкнуть по пункту Сохранить найденное решение. Значения найдены и находятся в отведенных для них ячейках (рис.10).
Значение суммы квадратов отклонений найденной оценки функции регрессии от наблюденных значений результирующего признака, т.е. функции Q для линейной регрессии и функции Q2 для квадратичной регрессии, находятся в ячейках F53 и I53, линейная величина отклонений – в ячейке F54 и в ячейке I54.
Рис.11. Ввод информации для Поиска решения.
Таким образом, коэффициенты линейной функции регрессии P(x) следует считывать из ячеек A56,B56 и С56; коэффициенты нелинейной функции регрессии P2(x) – из ячеек A59 F59. Для рассматриваемого примера линейная функция регрессии совпадает с полученной с помощью инструмента Регрессия, а квадратичная
Проверка значимости полученной квадратичной оценки уравнения регрессии выполним так. Определим коэффициент корреляции значений эмпирической функции регрессии и выборочного среднего RyP2(x). Как видно из рис.12, коэффициент корреляции достаточно большой (0,80921). Выполним еще одну проверку значимости P2(x) с помощью коэффициента детерминации, для чего необходимо вычислить значения Sост, Sфакт.
Размещение нужных формул приведено на рис.12, а промежуточные результаты и значения коэффициента детерминации ниже. Поскольку коэффициент детерминации для случая квадратичной регрессии значительно превосходит коэффициент детерминации для случая линейной регрессии и имеет достаточно большое значение (0,472867), делаем вывод, что квадратичная регрессия достаточно хорошо согласуется со статистическими данными.
Выполним оценку значимости полученного приближения функции в целом с помощью критерия Фишера. Для этого найдем значения критерия Фишера по выборке для рассматриваемых двух видов зависимости (см. рис.12 и 13).
R
S
RyP(x)
RyP2(x)
=КОРРЕЛ(C2:C52;D2:D52)
=КОРРЕЛ(C2:C52;H2:H52)
Sост
Sост
=F53/48
=I53/45
Sфакт
Sфакт
=L53/48
=N53/45
R2
R22
=1-R5/ (R9 + R5)
=1-S5/ (S9 + S5)
Fрасч
F2расч
=R11*(51-2-1)/(1-R11)/2
=S11*(51-2-1)/(1-S11)/2
Fкрит =
3,205
Рис.12.Расчетные формулы
Как видно, расчетное значение F-критерия для квадратичной зависимости значительно превосходит значение Fкрит ,что подтверждает ее значимость. Для линейной зависимости превышение Fрасч не столь велико, что делает снова-таки предпочтительнее квадратичную оценку регрессии y2 на x4 и x8 .
K
L
M
N
O
Q
R
S
1
^2
^2
RyP(x)
RyP2(x)
66,0145
4357,91
52,4372
2749,66
0,762322
0,80921
98,0407
9611,98
63,6085
4046,04
Sост
Sост
36,9723
1366,95
39,0068
1521,53
121,189
14686,7
59,1584
3499,72
2523,668
2218,362
36,6828
1345,63
52,8333
2791,36
Sфакт
Sфакт
66,8451
4468,27
52,8975
2798,14
25,2325
636,678
31,6051
998,881
-64,2814
4132,09
-63,8871
4081,57
3501,349
4208,353
3,56772
12,7286
14,147
200,138
R2
R22
43,0760
1855,54
43,5092
1893,05
0,581135
0,654822
-12,1715
148,144
4,46566
19,9421
Fрасч
F2расч
37,1816
1382,47
39,3711
1550,09
33,29771
45,5293
68,8203
4736,24
53,556
2868,24
37,88307
1435,127
39,90716
1592,582
Fкрит =
3,205
Рис.13.Проверка значимости.
Таким образом, выборочное уравнение регрессии имеет вид:
V. Содержание и объем курсовой работы
Цель задания – выполнить статистический анализ результатов измерений и определить на основании исследования математической модели оценки законов и параметров распределения, корреляционные связи и статистические зависимости
1. Выполнить многомерный экономико-статистический анализ показателей (в соответствии с вариантом):
– проверить данные на засорение;
– проверить закон распределения всех параметров;
– оценить тесноту связей параметров;
– выполнить регрессионный анализ двумерных и трехмерной моделей.
2. Составить математическую модель задачи на основании корреляционного и регрессионного анализа статистических данных.
3. Работу выполнить средствами Excel и его надстроек Пакет анализа и Поиск решения.
4. Отчет по курсовой работе создать в среде Word с необходимыми по тексту результатами в виде таблиц и диаграмм Excel.
5. Отчет должен содержать:
– титульный лист;
– задание в соответствии с вариантом;
– названия и краткое описание, а также результаты каждого этапа выполнения задания;
– приложение в виде рабочей книги Excel, содержащей все выполненные расчеты (на дискете);
Варианты заданий приведены в приложении на странице 48.
VI. Литература
1. Магнус Я.Р., Катышев П.К., Пересецкий А.А. Эконометрика. Начальный курс. – М.: Дело, 1997. – 248 с.
2. Дубров А.М., Мхитарян В.С., Трошин Л.И. Многомерные статистические методы: Учебник. – М.: Финансы и статистика, 2000. – 352 с.
3. Колемаев В.А. и др. Теория вероятностей и математическая статистика: Учеб. пособие для экон. спец. вузов / В.А.Колемаев, О.В.Староверов, В.Б.Турундаевский; Под ред. В.А.Колемаева. – М.: Высш. шк.,!991. – 400 с.
4. Сивец С.А. Статистические методы в оценке недвижимости и бизнесе. Учебно-практическое пособие по статистике для оценщиков. – Запорожье, 2001. – 320 с.
5. Тюрин Ю.Н., Макаров А.А. Статистический анализ данных на компьютере / Под ред. Фигурнова В.Э. – М.: ИНФРА, 1998. – 528 с.
6. Савицкая Г.В. Анализ хозяйственной деятельности предприятия. – Минск: ООО «Новое знание», 2000. – 668 с.
7. Ларсен, Рональд У. Инженерные расчеты в Excel.: Пер. с англ. – М.: Издательский дом «Вильямс», 2004. – 544 с.
8. Гурман В.Е. Теория вероятностей и математическая статистика. Учеб. Пособие для втузов. М., «Высш. школа», 1977. – 479 с.
9. Математическая статистика: Учебник / Иванова В.М., Калинина В.Н., Нешумова Л.А. и др. – М.: Высш. школа, 1981. –371 с.
Примечание. В таблице указаны индексы факторных признаков X. Так, для варианта 1 следует выполнить статистический анализ всех 53 значений показателей Y1, X6, X8, X 11, X12, X17.