Образец выполнения задания 1 в MSExcel

Для анализа зависимости объема потребления у (в тыс. руб.) от располагаемого дохода х (в тыс. руб.) отобрана выборка объема n =10 (помесячно с сентября по июнь включительно), результаты которой приведены в таблице. Определить параметры линейной регрессии методом наименьших квадратов. Спрогнозировать потребление при доходе х =30 тыс. руб.

	21,4	21,8	22,0	22,6	24,0	24,4	24,6	25,6	27,2	28,0
	20,4	21,0	21,6	22,0	23,0	23,4	23,8	25,0	26,4	26,0

Ход работы

1) Ввести в таблицу согласно варианта эмпирические данные (столбцы В, C).

2) Построить график исходных данных. Для этого в меню Вставка выбрать Диаграмма, указать тип диаграммы Точечная. Далее выбрать Диапазон: столбцы B и С. В Ряд добавить подписи по осям. По графику убедиться в возможной линейной зависимости между х и у.

3) Произвести необходимые вычисления (столбцы D, E) Вычислить суммы , , , (в строке 12), используя встроенную функцию СУММ.

4) Составить и записать систему уравнений для нахождения коэффициентов k и b.

В данном случае в результате имеем систему:

5) Неизвестные k и b найти по формулам Крамера:

, ,

где – определитель, составленный из коэффициентов при неизвестных в составленной системе,

– определитель, полученный из определителя заменой 1-го столбца на столбец свободных членов,

– определитель, полученный из определителя заменой 2-го столбца на столбец свободных членов, то есть

,

,

.

6) Составить и записать уравнение .

В рассматриваемом случае получаем уравнение .

7) В таблице (столбец F) для эмпирических значений по найденному уравнению вычислить .

Вычислить (столбец G) и (столбец H).

Вычислить сумму квадратов отклонений (ячейка H12).

.

8) Изобразить прямую регрессии на построенном графике. Для этого в меню, вызванном правой клавишей (при подведении курсора к экспериментальным точкам), выбрать Добавить линию тренда, тип линии тренда – линейная.

9) Построить график функции .

Это можно сделать на отдельном графике. Для чего в меню Вставка выбрать Диаграмма, указать тип диаграммы График. Далее выбрать Диапазон: столбцы B и F (в строках – данные столбца B, в столбцах – данные столбца F). Сравнить полученный график с линией тренда на первом графике.

График функции можно построить и на первом графике. Для этого в меню, вызванном правой клавишей, выбрать Исходные данные, в появившемся окне Ряд, Добавить. Значения Х – данные столбца B, значения У – данные столбца F) Тип диаграммы График.

10) По полученному уравнению спрогнозировать значение y по известному значению х. В рассматриваемом случае при доходе х =30 тыс. руб. потребление (тыс. руб.