Исходные данные:
X Y
7,45 28,59
9,41 29,72
1,32 11,74
6,54 25,76
6,94 26,17
3,97 19,34
5,47 22,99
5,54 23,42
6,97 25,4
5,17 22,12
5,8 25,8
9,85 32,85
9,33 30,46
9,62 32,26
8,43 28,94
5,69 24,32
7,43 26,35
1,12 11,23
8,42 30,4
4,88 20,55
2,25 13,76
7,95 27,77
4,34 21,48
3,15 18,58
7,55 27,71
6,86 26,39
1,89 12,07
8,69 26,83
8,05 27
6,07 24,01
Задание
На основе данных необходимо:
1. Определить параметры следующих уравнений регрессии:
а) линейного;
б) гиперболического;
в) степенного;
г) показательного (экспоненциального);
д) логарифмического;
е) параболического.
2. Оценить качество каждой модели взаимосвязи с помощью средней ошибки аппроксимации и показателя детерминации.
3. На основании результатов, полученных в пункте 2, выбрать уравнение регрессии, наилучшим образом описывающее взаимосвязь между фактором х и результативным признаком у.
4. По выбранной модели взаимосвязи сделать точечный прогноз для значения фактора равного .
Нужно полное решение этой работы?
Решение
Для расчетов в Excel листы рабочей книги назовем в соответствии с названиями функций, параметры которых определяются. Соответственно получится 6 листов:
1. Линейная
2. Гипербола
3. Степенная
4. Показательная
5. Логарифмическая
6. Параболическая
Исходные данные разместим в столбцах А и B. При этом первая строка отводится для названий столбцов. Таким образом, значения будут находится в строках с 2 по 31. Строка 32 предназначена для вычисления автосумм значений соответствующих столбцов.
Для получения качественных графиков исходные данные необходимо отсортировать. Для чего выделяем значения фактора х, находящиеся в колонке А На панели инструментов нажимается кнопка сортировки по возрастанию (). В появившемся диалоговом окне необходимо нажать кнопку [Сортировка].
В результате значения фактора будут отсортированы по возрастанию. При этом соответствие между значениями фактора х и результата у сохраняются.
1.1. Построение линейного уравнения регрессии
Для нахождения параметров a и b необходимо составить и решить следующую систему:
.
где n – число пар значений в исходных данных (n=30).
Таким образом, для составления системы необходимо вычислить следующие значения сумм: , , и .
Суммы , вычисляем соответственно в ячейках А32 и В32 с использованием кнопки [Автосумма] ([Σ]) на панели инструментов.
Квадраты значений фактора х2 вычисляем в столбце С. Для этого в ячейку С2 заносится формула: = А2^2. Данная формула распространяется на ниже лежащие ячейки путем протягивания с помощью мыши. В ячейке С32 вычисляется сумма значений этого столбца, т.е. .
Произведения х*у вычисляются в столбце D. Для этого в ячейку D2 вводится формула: =А2*В2. Данная формула распространяется на ниже лежащие ячейки путем протягивания с помощью мыши
. В ячейке D32 вычисляется сумма значений этого столбца, т.е. .
На основании проведенных вычислений составляем систему нормальных уравнений:
Эту систему необходимо решить с целью определения параметров a и b.
Для решения системы воспользуемся возможностями Excel.
Из математики известно, что решение системы линейных уравнений может быть получено, если матрицу, обратную матрице коэффициентов левых частей уравнений системы, умножить на вектор-столбец правых частей уравнений системы.
Реализуем данный подход в Excel. При этом решение будет искать в два этапа:
1. Определяем матрицу, обратную матрице коэффициентов левых частей уравнений системы, с помощью функции МОБР.
2. Определяем решение системы умножением обратной матрицы на матрицу правых частей уравнений системы с помощью МУМНОЖ.
Занесем параметры системы в ячейки диапазона А35:D36.
При этом в ячейках А35:В36 будет находится матрица левых частей уравнений, а в ячейках D35:D36 – матрица-столбец правых частей.
В ячейку А38 введем функцию МОБР (Категория: Математические). Данная функция вычисляет матрицу обратную заданной. В качестве аргумента функции зададим массив ячеек А35:В36 и нажмем ОК.
Поскольку результатом должна быть матрица, то необходимо осуществить следующие дополнительные действия. Выделяем диапазон ячеек А38:В39, на клавиатуре нажимаем клавишу F2 (при этом в ячейке А38 появится введенная ранее формула). Далее нажимается комбинация трех клавиш: Ctrl+Shift+Enter. В результате в ячейках А38:В39 получается матрица обратная заданной.
Для получения окончательного решения необходимо полученную матрицу умножить на вектор-столбец правых частей уравнений системы
Задать вопрос
на новый заказ в Автор24.
