Главная | Обратная связь | Поможем написать вашу работу!

Образец выполнения задания 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 тыс. руб. потребление (тыс. руб.

Замечание

Найти коэффициенты k и b можно, используя функцию ЛИНЕЙН. При этом результат может незначительно отличаться от результата, полученного по формулам выше.

Порядок вычислений в этом случае следующий:

1. На листе с исходными данными выделить область пустых ячеек 1х2 (1 строка, 2 столбца) для получения оценок коэффициентов регрессии.

2. Активизировать Мастер функций, для чего в меню Вставка выбрать Функция. Затем в категории Статистические выбрать функция ЛИНЕЙН. Здесь Известные значения у – столбец С, Известные значения х – В, Конст и Статистика можно не указывать.

3. В левой верхней ячейке выделенной области появится значение коэффициента k. Чтобы раскрыть всю таблицу, нажать на клавишу F2, а затем на комбинацию клавиш Ctrl+Shift+Enter.

Коэффициенты k и b можно также получить, используя функцию ИНДЕКС. При этом k = ИНДЕКС (ЛИНЕЙН …; 1),

b = ИНДЕКС (ЛИНЕЙН …; 2).

⇐ Предыдущая 1 2 3 4 567 8 Следующая ⇒