На основании приведенных данных требуется:
1) построить модель парной регрессии:
а) с использованием Анализа данных;
б) с использованием Поиска решений;
с) с использованием матричных функций;
в) с использованием функции ЛИНЕЙН.
Дать экономическую интерпретацию параметров модели регрессии.
2) оценить качество построенной модели;
3) изобразить на графике исходные данные, результаты моделирования и прогнозирования.
Вариант 10
В таблице приведены статистические данные, описывающие зависимость спроса на товар (y) от его цены (x):
Таблица 1
№ 1 2 3 4 5 6 7
Цена товара, руб. 99 82 77 69 52 44 31
Спрос на товар, шт. 100 115 210 270 323 478 544
Решение
На основании приведенных данных требуется:
1) построить модель парной регрессии:
а) с использованием Анализа данных:
Данные записать в таблицу Excel.
Выбрать команду на вкладке Данные команда Анализ данных.
В диалоговом окне Анализ данных выбрать инструмент Регрессия.
В диалоговом окне Регрессия в поле Входной интервал Y ввести адрес одного диапазона ячеек, который представляет зависимую переменную. В поле Входной интервал Х ввести адрес диапазона, который содержит значения независимой переменной (рис. 1).
Установить флажок Метки в первой строке для отображения заголовков столбцов.
Выбрать параметры вывода. В данном примере Выходной интервал $E$8.
В поле Остатки и График подбора поставить флажки.
ОК.
Рис. 1. Диалоговое окно Регрессия подготовлено к построению модели регрессии
Результаты выполнения инструмента Регрессия получим в виде протокола (рис.1.7). Протокол состоит из четырех таблиц: первая - Регрессионная статистика, вторая - Дисперсионный анализ, третья таблица без названия, в которой содержится информация о коэффициентах регрессии и четвертая, в которой содержатся предсказанные значения и остатки.
ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R 0,968505
R-квадрат 0,938002
Нормированный R-квадрат 0,925602
Стандартная ошибка 46,51056
Наблюдения 7
Дисперсионный анализ
df
SS MS F Значимость F
Регрессия 1 163643,6 163643,6 75,6477 0,000332
Остаток 5 10816,16 2163,232
Итого 6 174459,7
Коэффициенты Стандартная ошиб t-статистика P-Значение Нижние 95% Верхние 95%
Y-пересечение 743,3231 54,84982 13,55197 3,92E-05 602,3272 884,3191
х -6,96754 0,80109 -8,69757 0,000332 -9,0268 -4,90827
ВЫВОД ОСТАТКА
Наблюдение Предсказанное Остатки
1 53,53696 46,46304
2 171,9851 -56,9851
3 206,8228 3,177233
4 262,5631 7,436938
5 381,0112 -58,0112
6 436,7515 41,24852
7 527,3295 16,67054
Рис.2. Фрагмент протокола выполнения регрессионного анализа.
В ячейках В32 и В33 будут находиться параметры модели линейной регрессии и
б) с использованием Поиска решений:
Согласно принципу метода наименьших квадратов оценки и находятся путем минимизации суммы квадратов суммы квадратов отклонений RSS по всем возможным значениям при заданных (наблюдаемых) Xи Y.Задача сводится к математической задаче поиска точки минимума функции двух переменных. Задача может быть решена с использованием надстройки Excel Поиск решения.
Поиск решения – это надстройка Excel, которая позволяет решать оптимизационные задачи.
В диалоговом окне Поиск решения есть три основных параметра:
• Оптимизировать целевую функцию.
• Изменяя ячейки переменных.
• В соответствии с ограничениями.
Рассмотрим технологию оценки параметров модели линейной регрессии зависимости спроса на товар от цены товара на основании данных(табл
. 1.) с использованием надстройки Excel Поиск решения.
Изменяя ячейки переменных. Здесь указываются ячейки, значения в которых будут изменяться для того, чтобы оптимизировать результат в целевой ячейке. В нашем примере это – ячейки $C$5:$D$5 (рис.3).
Рис. 3. Введены формулы для вычисления значения целевой функции
Поле Оптимизировать целевую функцию. Целевая ячейка связана с другими ячейками этого рабочего листа с помощью формул. В нашем примере это ячейка F61, в которой в результате введенных формул получим сумму квадратов отклонений расчетных данных от фактических RSS. Для запуска Поиска решений на вкладке Данные выбрать команду Поиск решения и указать в появившемся меню адреса целевой функции, изменяемых ячеек и выбрать поиск наименьшего значения (рис. 4– 5).
Рис. 4. Заполнение диалогового окна Поиск решения
Цена товара Спрос на товар
62865-172720 264160-233045
99 100
53,54 2158,814
82 115
171,99 3247,3
77 210
206,82 10,09481
69 270 743,3231 -6,96754 262,56 55,30805
52 323
381,01 3365,298
44 478
436,75 1701,44
31 544
527,33 277,9068
2040
2040 10816,16
Рис.5. В ячейках С57 иD57 будут находиться параметры модели линейной регрессии и
с) с использованием матричных функций:
В матричной форме расчет параметров линейной модели парной регрессии по приведенной формуле может быть выполнен с помощью матричных функций МУМНОЖ и МОБР.
При подготовке данных, формируя матрицу Х для вычисления свободного члена а0, необходимо добавить столбец Х0, состоящий из единиц.
Транспонируем матрицу Х. Это можно выполнить с использованием функции ТРАНСП или путем последовательного копирования и специальной вставки транспонирования.
Скопировать матрицу Х.
Используя специальную вставку, получить транспонированную матрицу.
Умножаем транспонированную матрицу на матрицу Х.
Выделить диапазон ячеек для результата умножения матриц размером . Результатом является массив с таким же числом строк, как массив транспонированная матрица, т.е. 2 и с таким же числом столбцов, как матрица Х, т.е. тоже 2.
Ввести формулу умножения матриц = МУМНОЖ(G66:M67;A67:B73).
Рис.6. Вычисление матрицы
Нажать клавиши CTRL+SHIFT+ENTER.
Вычисляем обратную матрицу.
Выделить диапазон для размещения обратной матрицы размером (G8:H9).
В категории Математические выбрать функцию вычисления обратной матрицы =МОБР(G70:H71), в качестве массива указать диапазон ячеек G70:H71, где размещена матрица, к которой надо вычислить обратную.
Нажать клавиши CTRL+SHIFT+ENTER.
Рис. 7. Вычисление обратной матрицы
4. Умножаем обратную матрицу на транспонированную матрицу.
Выделить диапазон ячеек для результата умножения матриц размером
Задать вопрос
на новый заказ в Автор24.
