Пример 1.2.г. Оценить параметры модели парной регрессии с помощью надстройки Excel Поиск решения

Согласно принципу метода наименьших квадратовоценки и находятся путем минимизации суммы квадратов суммы квадратов отклонений RSS по всем возможным значениям при заданных (наблюдаемых) Xи Y.Задача сводится к математической задаче поиска точки минимума функции двух переменных. Задача может быть решена с использованием надстройки ExcelПоиск решения.

Поиск решения – это надстройка Excel, которая позволяет решать оптимизационные задачи.

В диалоговом окне Поиск решения есть три основных параметра:

• Оптимизировать целевую функцию.

• Изменяя ячейки переменных.

• В соответствии с ограничениями.

Подробно применение Поиска решения рассмотрено в учебном пособии [4] и практикуме [5].

Рассмотрим технологию оценки параметровмодели линейной регрессии зависимости стоимости ущерба, нанесенного пожаром, от расстояния до ближайшей пожарной станции на основании данных(табл. 1.1) с использованием надстройки Excel Поиск решения.

Изменяя ячейки переменных. Здесьуказываются ячейки, значения в которых будут изменяться для того, чтобы оптимизировать результат в целевой ячейке.В нашем примере это – ячейки $C$5:$D$5 (рис.1.14).

Рис. 1.14. Введены формулы для вычисления значения целевой функции

ПолеОптимизировать целевую функцию. Целевая ячейка связана с другими ячейками этого рабочего листа с помощью формул. В нашем примере это ячейка F41, в которой в результате введенных формул получим сумму квадратов отклонений расчетных данных от фактическихRSS. Для запуска Поиска решений на вкладке Данные выбрать команду Поиск решения и указать в появившемся меню адреса целевой функции, изменяемых ячеек и выбрать поиск наименьшего значения(рис. 1.15– 1.16).

Рис. 1.15. Заполнение диалогового окна Поиск решения

Индекс реального объема промышленного производства (%) - IP	Количество безработных (млн. чел.) - U
137.73	5.3			5.27	0.00
140.21	4.7			5.12	0.18
145.53	4.3			4.79	0.24
154.41	4.3	13.837	-0.062	4.24	0.00
146.07				4.75	0.06
146.37	4.4			4.74	0.11
148.42	4.4			4.61	0.04
149.4	5.2			4.55	0.43
123.41	6.6			6.17	0.19
…	…			…	…
148.34	4.1			4.61	0.26
163.18	4.3			3.69	0.37
143.92	4.5			4.89	0.15
144.79	4.4			4.83	0.19
148.12	4.1			4.63	0.28
					9.99

Рис.1.16.В ячейках C5 иD5 будут находиться параметры модели линейной регрессии и

Пример 1.2.д. Оценить параметры модели парной регрессии по формулам:

, .

Средние значения Xи Y можно получить в Excel с помощью функции СРЗНАЧ (рис1.17.)

Рис.1.17. Вычисление среднего значения

Табл. 1.2

Индекс реального объема промышленного производства (%) - IP	Количество безработных (млн. чел.) - U
137.73	5.3	-9.05	0.59	-5.339	81.963
140.21	4.7	-6.57	-0.01	0.067	43.209
145.53	4.3	-1.25	-0.41	0.514	1.571
154.41	4.3	7.63	-0.41	-3.129	58.166
146.07		-0.71	0.29	-0.207	0.509
146.37	4.4	-0.41	-0.31	0.128	0.171
148.42	4.4	1.64	-0.31	-0.508	2.679
149.4	5.2	2.62	0.49	1.281	6.847
123.41	6.6	-23.37	1.89	-44.170	546.313
126.5	6.6	-20.28	1.89	-38.330	411.414
134.09	6.1	-12.69	1.39	-17.640	161.121
143.07		-3.71	1.29	-4.789	13.789
133.2	6.4	-13.58	1.69	-22.952	184.507
135.73	5.7	-11.05	0.99	-10.940	122.176
139.67	5.1	-7.11	0.39	-2.772	50.600
153.49		6.71	0.29	1.943	44.979
139.83	5.5	-6.95	0.79	-5.491	48.349
143.89		-2.89	0.29	-0.838	8.371
147.05	4.7	0.27	-0.01	-0.003	0.071
159.11	4.6	12.33	-0.11	-1.359	151.947
145.91	4.7	-0.87	-0.01	0.009	0.763
147.07	4.2	0.29	-0.51	-0.146	0.082
151.93	3.9	5.15	-0.81	-4.170	26.488
164.08	3.9	17.30	-0.81	-14.015	299.175
144.06	4.3	-2.72	-0.41	1.117	7.417
148.1	4.1	1.32	-0.61	-0.804	1.734
152.69		5.91	-0.71	-4.195	34.889
166.12	4.1	19.34	-0.61	-11.800	373.907
145.52	4.2	-1.26	-0.51	0.645	1.596
150.76	3.8	3.98	-0.91	-3.620	15.814
154.83	3.7	8.05	-1.01	-8.129	64.749
169.7	3.9	22.92	-0.81	-18.568	525.174
144.92	4.3	-1.86	-0.41	0.764	3.472
143.33	4.3	-3.45	-0.41	1.417	11.926
148.34	4.1	1.56	-0.61	-0.950	2.423
163.18	4.3	16.40	-0.41	-6.727	268.851
143.92	4.5	-2.86	-0.21	0.602	8.199
144.79	4.4	-1.99	-0.31	0.618	3.973
148.12	4.1	1.34	-0.61	-0.816	1.787
			Сумма	-223.301	3591.165

Используя промежуточные расчеты таблицы 1.2, вычислим параметры модели парной регрессии:

.

В результате применения всех инструментов Excel были получены одинаковые параметры модели регрессии.