Файл: Пособие Экселя.doc

Рис. 1.23. Пример заполнения диалогового окна Регрессия

О если необходимо визуально проверить отличие экспериментальных точек от предсказанных по регрессионной модели, следует установить флажок в поле График подбора;

О нажать кнопку ОК.

Результаты анализа. Выходной диапазон будет включать в себя результаты дис­персионного анализа, коэффициенты регрессии, стандартную погрешность вычис­ления Y, среднеквадратичные отклонения, число наблюдений, стандартные погреш­ности для коэффициентов.

Интерпретация результатов. Значения коэффициентов регрессии находятся в столбце Коэффициенты и соответствуют:

О Y-пересечение − a₀

О переменная X₁ − а₁

О переменная Х₂ −а₂ и т. д.

В столбце Р-Значение приводится достоверность отличия соответствующих коэффициентов от нуля. В случаях, когда Р > 0,05, коэффициент может считаться ну­левым, что означает, что соответствующая независимая переменная практически не влияет на зависимую переменную.

Приводимое значение R-квадрат (коэффициент детерминации) определяет, с ка­кой степенью точности полученное регрессионное уравнение аппроксимирует исходные данные. Если R-квадрат > 0,95, говорят о высокой точности аппроксимации (модель хорошо описывает явление). Если R-квадрат лежит в диапазоне от 0,8 до 0,95, говорят об удовлетворительной аппроксимации (модель в целом адекват­на описываемому явлению). Если R-квадрат < 0,6, принято считать, что точность аппроксимации недостаточна и модель требует улучшения (введения новых неза­висимых переменных, учета нелинейностей и т. д.).

Пример 1.15. В отделе снабжения гостиницы имеется информация об изменении стоимости стирального порошка за длительный период времени. Сопоставляя его с изменениями курса доллара за этот же период времени, можно построить рег­рессионное уравнение. Ниже приведены стоимость пачки стирального порошка (в руб.) и соответствующий курс доллара (руб./USD).

N Порошок Курс

1	5	6,3
2	7	9
3	9	12
4	12	15
5	15	19
6	16	21
7	20	25
8	25	29,3

Необходимо на основании этих данных построить регрессионное уравнение, по­зволяющее по курсу доллара определять предполагаемую стоимость пачки сти­рального порошка.

Решение

1. Введите данные в рабочую таблицу: стоимость пачки порошка − в диапазон А1:А8; курс доллара в диапазон В1:В8 (заметим, что знаку запятой, отделяющей целую часть от дробной, соответствует «запятая»).

2. В пункте меню Сервис выберите строку Анализ данных и далее укажите курсо­ром мыши на строку Регрессия.

3. В появившемся диалоговом окне (рис. 1.23) задайте Входной интервал Y. Для этого наведите указатель мыши на верхнюю ячейку столбца зависимых данных (А1), нажмите левую кнопку мыши и, не отпуская ее, протяните указатель мыши к нижней ячейке (А8), затем отпустите левую кнопку мыши. (Обратите внима­ние, что зависимые данные − это те данные, которые предполагается вычис­лять.)

4. Так же укажите Входной интервал X, то есть введите ссылку на диапазон неза­висимых данных В1:В8. (Независимые данные − это те данные, которые будут измеряться или наблюдаться.)

5. Установите флажок в поле График подбора.

6. Далее укажите выходной диапазон. Для этого поставьте переключатель в поло­жение Выходной интервал (наведите указатель мыши и щелкните левой кноп­кой), затем наведите указатель мыши на правое поле ввода Выходной интервал и, щелкнув левой кнопкой мыши, указатель мыши наведите, на левую верхнюю ячейку выходного диапазона (С1). Щелкните левой кнопкой мыши (рис. 1.1) Нажмите кнопку ОК.

Результаты анализа. В выходном диапазоне появятся следующие результаты и (рис. 1.24).

Рис. 1.24. Результаты анализа и график соответствия экспериментальных точек и предсказанных по регрессионной модели из примера 1.15

Интерпретация результатов. В таблице Дисперсионный анализ оценивается общее качество полученной модели: ее достоверность по уровню значимости критерия Фишера − р, который должен быть меньше, чем 0,05 (строка Регрессия, столба Значимость F, в примере – 1,575Е-07 (0,0000001575), то есть р = 0,0000001575 и модель значима) и степень точности описания моделью процесса − R-квадрат (вторая строка сверху в таблице Регрессионная статистика, в примере R -квадрат = 0,992) Поскольку R -квадрат > 0,95, можно говорить о высокой точности аппроксимации (модель хорошо описывает явление (рис. 1.24)).

Далее необходимо определить значения коэффициентов модели. Они определяются из таблицы в столбце Коэффициенты − в строке Y-пересечение приводится свободный член; в строках соответствующих переменных приводятся значения коэффициентов при этих переменных. В столбце р-значение приводится достоверность отличия соответствующих коэффициентов от нуля. В случаях, когда р > 0,05, ко­эффициент может считаться нулевым. Это означает, что соответствующая незави­симая переменная практически не влияет на зависимую переменную и коэффици­ент может быть убран из уравнения.

Отсюда выражение для определения стоимости пачки порошка в рублях будет иметь следующий вид: -0,83 + 0,847*(Курс доллара, руб./USD).

Полученная модель с высокой точностью позволяет определять стоимость пачки стирального порошка (R² = 99,2%).

Воспользовавшись полученным уравнением, можно рассчитать ожидаемую сто­имость пачки стирального порошка при изменениях курса доллара. Например, для расчета при курсе доллара 35 руб./USD необходимо поставить табличный курсор в любую свободную ячейку (А10); ввести с клавиатуры знак =, щелкнуть указате­лем мыши по ячейке D17, ввести с клавиатуры знак +, щелкнуть по ячейке D18, ввести с клавиатуры знак * и число 35. В результате в ячейке А10 будет получена ожидаемая стоимость пачки порошка − 28,8 руб.

Пример 1.16. Построить регрессионную модель для предсказания изменений уров­ня заболеваемости органов дыхания (Y) в зависимости от содержания в воздухе двуокиси углерода (X₁,) и степени запыленности (Х₂). В таблице приведены дан­ные наблюдений в течение 29 месяцев.

X1	Х2	Y
1,0	1,3	1160
1,0	1,3	1155
1,1	1,4	1158
1,1	1,4	1157
1,1	1,5	1160
1,1	1,5	1161
1,0	1,4	1157
1,0	1,5	1159
1,2	1,6	1256
1,2	1,7	1260
0,6	1,0	1040
0,6	1,0	1039
0,7	1,1	1039
0,7	1,15	1040
0,75	1,2	1040
0,7	1,2	1039
0,7	1,3	1040
0,7	1,3	1039
0,8	1,4	1140
0,8	1,4	1138
0,78	1,5	1240
0,80	1,5	1239
0,78	1,5	1241
0,78	1,6	1240
0,80	1,7	1239
0,80	1,8	1239
0,75	1,8	1240
0,78	1,9	1238
0,75	1,9	1238

Решение

1. Введите данные наблюдений в диапазон А1:С30 рабочей таблицы «MS Excel».

1. В пункте меню «Сервис» выберите строку «Анализ данных» и далее укажите курсором мыши на строку «Регрессия». Нажмите кнопку ОК.

2. В появившемся диалоговом окне задаем Входной интервал Y. Для этого наве­дите указатель мыши на верхнюю ячейку столбца зависимых данных (С2) и жмите левую кнопку мыши и, не отпуская ее, протяните указатель мыши к нижней ячейке (С30), затем отпустите левую кнопку мыши. (Обратите внимание, что зависимые данные − это те данные, которые предполагается вычислять).

3. Так же укажите Входной интервал X, то есть введите ссылку на диапазон независимых данных А2:В30. (Независимые данные − это те данные, которые будут измеряться или наблюдаться).

2. Установите флажок в поле График подбора.

4. Далее укажите выходной диапазон. Для этого поставьте переключатель в поло­жение Выходной интервал (наведите указатель мыши и щелкните левой кнопкой), затем наведите указатель мыши на правое поле ввода Выходной интервал и, щелкнув левой кнопкой мыши, указатель мыши наведите на левую верхнюю ячейку выходного диапазона (D1). Щелкните левой кнопкой мыши. Нажмите кнопку ОК.

5. В выходном диапазоне появятся результаты регрессионного анализа и графики предсказанных точек (рис. 1.25).

Рис. 1.25. Графики расположения фактических и предсказанных точек (пример 1.16)

Интерпретация результатов. В таблице «Дисперсионный анализ» оценивается дос­товерность полученной модели по уровню значимости критерия Фишера (строка Регрессия, столбец Значимость F, в примере − 1,4Е-09 (1,4*10^-9), то есть р « 0,05 и модель значима) и степень описания моделью процесса − R-квадрат (вторая стро­ка сверху в таблице Регрессионная статистика, в примере R-квадрат = 0,89). Поскольку R-квадрат > 0,8, можно говорить о довольно высокой точности аппроксимации (модель хорошо описывает зависимость заболеваемости от содержания углекис­лого газа и запыленности воздуха (рис. 1.25)).

Далее необходимо определить значения коэффициентов модели. Они определяются из таблицы в столбце Коэффициенты − в строке Y− пересечение приводится свободный член а₀ = 682; в строках соответствующих переменных приводятся значения коэффи­циентов при этих переменных а₁ = 91 и а₂ = 275. В столбце р-значение приводится до­стоверность отличия соответствующих коэффициентов от нуля. Все коэффициенты значимы, то есть р < 0,05, и коэффициенты могут считаться не равными нулю.

Поэтому выражение для определения уровня заболеваемости органов дыхания в зависимости от содержания углекислого газа и пыли в воздухе будет иметь вид:

У=682 + 91*Х₁ + 275*Х₂.

Упражнения

22 Постройте зависимость зарплаты (руб.) от возраста сотрудника гостиницы по следующим данным:

Возраст Зарплата

20 800

50 2500

45 2500

40 2000

25 1200

30 1800

23 Постройте зависимость жизненной емкости легких в литрах (Y) от роста в мет­рах (Х₁) и возраста в годах (Х₂) для группы мужчин:

Х1	Х2	Y
1,85	18	5,4
1,80	25	5,7
1,75	20	4,8
1,70	24	5,1
1,68	21	4,5
1,73	19	4,8
1,77	22	5,1
1,81	23	5,6
1,76	18	4,7

24. Определите должное значение жизненной емкости легких для мужчины воз­раста 22 лет и роста 183 см из регрессионного уравнения, полученного в предыдущем упражнении.

25. Имеются данные о цене на нефть х (ден. ед.) и индексе акций нефтяных компаний Y (усл. ед.):

X Y

17,28 537

17,05 534

18,30 550

18,80 555

19,20 560

18,50 552

Постройте зависимость индекса акций нефтяных компаний от цены на нефть.

8