Лабораторная работа №2
Определения уравнения регрессии на основе метода наименьших квадратов и использования функций EXCEL
Цель работы: Приобретение навыков определения уравнения регрессии на основе метода наименьших квадратов и использования функций EXCEL
Задание для лабораторной работы:
1. Используя метод наименьших квадратов (МНК), к заданному варианту подобрать одну из эмпирических функций регрессии (y=a·Ln(x)+b, y=a·xb, y=a·eb·x, y=a·bx).
2. Используя функции рабочего листа (НАКЛОН, ОТРЕЗОК, ЛИНЕЙН, ЛГРФПРИБЛ) к заданному варианту подобрать эмпирическую функцию регрессии
3. Объяснить, какая из выбранных функций подходит лучше и почему.
4. Построить графики экспериментальных и полученных теоретических данных.
Вариант 1
Плотность асфальтобетона в зависимости от времени уплотнения представлена в таблице 2.1
Таблица 2.1 – Исходные данные
Время уплотнения, мин |
0,5 |
0,8 |
1 |
1,5 |
1,8 |
2 |
2,4 |
2,7 |
3 |
Плотность асфальтобетона, т/м3 |
1,5 |
1,8 |
2,07 |
2.18 |
2,39 |
2,45 |
2,46 |
2,48 |
2,5 |
Выполнение
Переносим исходные данные из таблицы 2.1 в Excel (рисунок 2.1).
Рисунок 2.1 – Табличные данные в Excel
2. Строим начальную диаграмму (рисунок 2.2) с заданными данными из таблицы 2.1.
Рисунок 2.2 – Диаграмма рассеяния
3. Рассчитываем коэффициенты линейной регрессии по формуле (2.1) и получаем промежуточные результаты, представленные на рисунке 2.3.
(2.1)
Рисунок 2.3 – Промежуточные результаты
4. Для определения коэффициентов эмпирической функции регрессии используются функции Excel, которые находятся в категории «Статистические», первый из них будет «ЛИНЕЙН».
4.1. ЛИНЕЙН (известные значения у; известные значения х; константа; статистика) – функция определяет коэффициенты для прямой линии, имеющей следующий вид:
y = mx + b или y = m1 x1 + m2 x2 + ... + b (в случае нескольких диапазонов значений x) т. е. для многофакторной задачи.
5. Введем функцию по формуле: =ЛИНЕЙН (E4:M4; E3:M3) и выделим диапазон ячеек N5-O5 и поскольку функция ЛИНЕЙН возвращают массив значений, функции должны задаваться в виде формулы массива, т.е. для завершения ввода функций ЛИНЕЙН, необходимо нажать комбинацию клавиш Ctrl+Shift+Enter, результат наблюдаем на рисунке 2.4.
Рисунок 2.4 –Результат для функции «ЛИНЕЙН»
6. Для определения коэффициентов эмпирической функции регрессии используются функции Excel, которые находятся в категории «Статистические», будет «НАКЛОН» и «ОТРЕЗОК».
6.1 НАКЛОН(известные значения_у; известные значения_х) – определяет коэффициент наклона (а).
6.2 ОТРЕЗОК(известные значения_у; известные значения_х) – определяет точку пересечения линейного уравнения с осью ординат (в)
7. Введем функцию по формуле: = НАКЛОН(E4:M4; E3:M3) в выделенную ячейку N6 и нажать клавишу Enter, как видно коэффициент совпадает с таким же коэффициентом который искали через «ЛИНЕЙН» результат наблюдаем на рисунке 2.5.
Рисунок 2.5 –Результат для функции «НАКЛОН»
7.1 Введем функцию по формуле: = ОТРЕЗОК(E4:M4; E3:M3) в выделенную ячейку O6 и нажать клавишу Enter, как видно коэффициент совпадает с таким же коэффициентом который искали через «ЛИНЕЙН» результат наблюдаем на рисунке 2.6.
Рисунок 2.6 –Результат для функции «ОТРЕЗОК»
8. Для определения коэффициентов эмпирической функции регрессии используются функции Excel, которые находятся в категории «Статистические», будет «ЛГРФПРИБЛ».
8.1. ЛГРФПРИБЛ (известные значения_у; известные значения_х; константа; статистика) – функция определяет коэффициенты для уравнения вида у=b·mx для однофакторной зависимости или y=b·m1x1·m2x2·m3x3 для многофакторной задачи
Здесь:
известные значения у – массив известных значений зависимой наблюдаемой величины;
известные значения х – массив известных значений независимой наблюдаемой величины;
константа – логическое значение, которое указывает, требуется ли, чтобы константа «в» была равна нулю.
Если константа = истина или опущена, то «в» вычисляется обычным образом;
8.3. Введем функцию по формуле: = ЛГРФПРИБЛ (E4:M4; E3:M3) и выделим диапазон ячеек N7-O7 и поскольку функция ЛИНЕЙН возвращают массив значений, функции должны задаваться в виде формулы массива, т.е. для завершения ввода функций ЛГРФПРИБЛ, необходимо нажать комбинацию клавиш Ctrl+Shift+Enter, результат наблюдаем на рисунке 2.7.
Рисунок 2.7 –Результат для функции «ЛГРФПРИБЛ»
На рисунке 2.8 представлены результаты регрессионного анализа.
Рисунок 2.8 – Диаграмма рассеяния уравнений регрессии
Вывод
В ходе выполнения лабораторной работы используя метод наименьших квадратов, были построены уравнение регрессии экспериментальных и теоретических данных, из графика видно, что наиболее приближён к теоретическому график экспоненциальной функции.