На основании приведенных данных требуется:
1) построить модель парной регрессии:
а) с использованием Анализа данных;
б) с использованием Поиска решений;
с) с использованием матричных функций;
в) с использованием функции ЛИНЕЙН.
Дать экономическую интерпретацию параметров модели регрессии.
2) оценить качество построенной модели;
3) изобразить на графике исходные данные, результаты моделирования и
прогнозирования.
Вариант 7
Провели исследование, сколько сберегает население (y) и сколько оно
зарабатывает за год (x). Были получены следующие данные для случайно
отобранных семи человек:
Граждане 1 2 3 4 5 6 7
Доход, тыс. руб. 15 6 9 3 20 11 14
Сбережения, руб. 2000 200 500 100 2500 1800 1500
Решение
1) построим модель парной регрессии:
а) с использованием Анализа данных:
Выбрать команду на вкладке Данные команда Анализ данных.
В диалоговом окне Анализ данных выбрать инструмент Регрессия.
В диалоговом окне Регрессия в поле Входной интервал Y ввести адрес одного диапазона ячеек, который представляет зависимую переменную. В поле Входной интервал Х ввести адрес диапазона, который содержит значения независимой переменной (рис. 1).
Установить флажок Метки в первой строке для отображения заголовков столбцов.
Выбрать параметры вывода. В данном примере Выходной интервал Новый рабочий лист.
В поле Остатки и График подбора поставить флажки.
ОК.
Рис. 1. Диалоговое окно Регрессия подготовлено к построению модели регрессии
Результаты выполнения инструмента Регрессия получим в виде протокола (рис.2). Протокол состоит из четырех таблиц: первая - Регрессионная статистика, вторая - Дисперсионный анализ, третья таблица без названия, в которой содержится информация о коэффициентах регрессии и четвертая, в которой содержатся предсказанные значения и остатки.
ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R 0,9387
R-квадрат 0,881158
Нормированный R-квадрат 0,85739
Стандартная ошибка 360,7145
Наблюдения 7
Дисперсионный анализ
df
SS MS F Значимость F
Регрессия 1 4823711 4823711 37,07269 0,001729 1
Остаток 5 650574,7 130114,9
5
Итого 6 5474286 6
Коэффициенты Стандартная ошиб t-статистика P-Значение Нижние 95% Верхние 95%
Y-пересечение -506,897 315,9582 -1,60432 0,16955 -1319,09 305,2998
X 155,7471 25,57956 6,088735 0,001729 89,99279 221,5015
ВЫВОД ОСТАТКА
Наблюдение Предсказанное Остатки
1 1829,31 170,6897
2 427,5862 -227,586
3 894,8276 -394,828
4 -39,6552 139,6552
5 2608,046 -108,046
6 1206,322 593,6782
7 1673,563 -173,563
Рис.2. Протокола выполнения регрессионного анализа.
Уравнение зависимости сколько сберегает население (y) от сколько оно зарабатывает за год (x) можно записать в следующем виде:
Интерпретация параметров модели: 155,747 показывает, что при увеличении дохода на 1 тыс. руб. сбережения в среднем увеличится на 155,747 руб.
б) с использованием Поиска решений:
Согласно принципу метода наименьших квадратов оценки и находятся путем минимизации суммы квадратов суммы квадратов отклонений RSS по всем возможным значениям при заданных (наблюдаемых) X и Y. Задача сводится к математической задаче поиска точки минимума функции двух переменных. Задача может быть решена с использованием надстройки Excel Поиск решения.
Поиск решения – это надстройка Excel, которая позволяет решать оптимизационные задачи.
В диалоговом окне Поиск решения есть три основных параметра:
• Оптимизировать целевую функцию.
• Изменяя ячейки переменных.
• В соответствии с ограничениями.
Рассмотрим технологию оценки параметров модели линейной регрессии зависимости сколько сберегает население (y) и сколько оно зарабатывает за год (x) на основании исходных данных с использованием надстройки Excel Поиск решения.
Изменяя ячейки переменных. Здесь указываются ячейки, значения в которых будут изменяться для того, чтобы оптимизировать результат в целевой ячейке
. В нашем примере это – ячейки $ D$4:$Е $4 (рис.3).
Рис. 3. Введены формулы для вычисления значения целевой функции
Поле Оптимизировать целевую функцию. Целевая ячейка связана с другими ячейками этого рабочего листа с помощью формул. В нашем примере это ячейка G9, в которой в результате введенных формул получим сумму квадратов отклонений расчетных данных от фактических RSS. Для запуска Поиска решений на вкладке Данные выбрать команду Поиск решения и указать в появившемся меню адреса целевой функции, изменяемых ячеек и выбрать поиск наименьшего значения(рис. 4– 5).
Рис. 4. Заполнение диалогового окна Поиск решения
X Y
ei2
15 2000
1829,31 29134,96
6 200
427,59 51795,48
9 500 -506,897 155,74713 894,83 155888,8
3 100
-39,66 19503,57
20 2500
2608,05 11673,93
11 1800
1206,32 352453,8
14 1500
1673,56 30124,19
сумма 78 8600
8600 650574,7
Рис.5.В ячейках D4 и E4 будут находиться параметры модели линейной регрессии и
Уравнение зависимости сколько сберегает население (y) от сколько оно зарабатывает за год (x) можно записать в следующем виде:
Интерпретация параметров модели: 155,747 показывает, что при увеличении дохода на 1 тыс. руб. сбережения в среднем увеличится на 155,747 руб.
с) с использованием матричных функций:
В матричной форме расчет параметров линейной модели парной регрессии по приведенной формуле может быть выполнен с помощью матричных функций МУМНОЖ и МОБР.
При подготовке данных, формируя матрицу Х для вычисления свободного члена а0, необходимо добавить столбец Х0, состоящий из единиц.
Транспонируем матрицу Х. Это можно выполнить с использованием функции ТРАНСП или путем последовательного копирования и специальной вставки транспонирования.
Скопировать матрицу Х.
Используя специальную вставку, получить транспонированную матрицу.
Умножаем транспонированную матрицу на матрицу Х.
Выделить диапазон ячеек для результата умножения матриц размером . Результатом является массив с таким же числом строк, как массив транспонированная матрица, т.е. 2 и с таким же числом столбцов, как матрица Х, т.е. тоже 2.
Ввести формулу умножения матриц = МУМНОЖ(Н11:N122, D11: E17).
Рис.6. Вычисление матрицы
Нажать клавиши CTRL+SHIFT+ENTER.(Рис.6)
Вычисляем обратную матрицу.
Выделить диапазон для размещения обратной матрицы размером (L16:M17).
В категории Математические выбрать функцию вычисления обратной матрицы =МОБР(H16:I17), в качестве массива указать диапазон ячеек L16:M17, где размещена матрица, к которой надо вычислить обратную.
Нажать клавиши CTRL+SHIFT+ENTER.
Рис. 7. Вычисление обратной матрицы
4. Умножаем матрицуна Y.
Выделить диапазон ячеек для результата умножения матриц размером.
Ввести формулу умножения матриц МУМНОЖ
Нажать клавиши CTRL+SHIFT+ENTER.
43815133358600
(XTY)= 126800
В матричной форме расчет параметров модели может быть представлен на рис. 8.
Рис.8.
В ячейках R16 и R17 будут находиться параметры модели линейной регрессии и
Уравнение зависимости сколько сберегает население (y) от сколько оно зарабатывает за год (x) можно записать в следующем виде:
Интерпретация параметров модели: 155,747 показывает, что при увеличении дохода на 1 тыс
Задать вопрос
на новый заказ в Автор24.
