Выполнение линейной регрессии с помощью линии тренда

Представить данные в графическом виде.

Выделить данные: А3:В12. Вставка – диаграмма – точечный график. Левая кнопка – на графике появляются маркер. Правая кнопка – добавить линию тренда. Выбрать вид линии– (линейная), вкладка параметры – установить – «показать уравнение на диаграмме» и «поместить на диаграмму величину достоверности аппроксимации», «прогноз».

На графике (рисунок 3.1) показаны линия тренда, уравнение регрессии и величина достоверности.

Величина достоверности аппроксимации является одним из показателей, которые следует разместить на диаграмме. По этой величине можно судить о правомерности использовании я того или иного уравнения регрессии.

Если коэффициент лежит в диапазоне 0,9-1, то данную зависимость можно использовать для предсказания результата. Чем ближе к 1 коэффициент корреляции, тем более достоверна используемая модель.

Часто для аппроксимации произвольной выборки и разброса данных подходит полиномиальное уравнение той или иной степени. Однако при прогнозировании уравнениями таких линий следует иметь в виду, что возможны большие ошибки в прогнозах и истории данных. В таких случаях, если находится другая функция с близким коэффициентом корреляции, следует иметь в виду ее уравнение регрессии.

Сравнить все три полученных результата.

Алгоритм данного метода имеет следующий вид:

· ввод исходных данных – одномерных массивов X, Y;

· простой цикл для вычисления сумм по формулам

· вычисление искомых коэффициентов по формулам

Если аппроксимирующая функция имеет следующий вид y = a x ² + b x + c, то этот случай называется квадратичной аппроксимацией. В этом случае для определения трех неизвестных параметров a, b, c будет получена из условия минимума функции U система трех алгебраических уравнений.

 

3.3 Практический пример: повторная калибровка измерителя расхода

Измеритель расхода с завода-изготовителя попадает на производство, будучи укомплектованным градуированными таблицами, построенными для стандартных веществ (обычно это воздух или вода). Однако при критическом подходе калибровку необходимо проверять, чтобы убедиться, что после определенного периода эксплуатации инструмент продолжает давать надежные показания.

Во время покупки вертушечного расходомера его градуированная таблица описывалась уравнением (), связывающим показания расходомера (частота в Герцах) со скоростью потока воды vь/с).

V=0.0023+0.0674f (3.3)

По истечении года прибор снят с эксплуатации для повторной калибровки. Предварительное тестирование дало результаты, приведенные в таблице 3.1.

Надо выяснить:

1. Нужно ли прибору повторная калибровка?

2. Если нужна, то каким будет новое калибровочное уравнение?

Во-первых, можно вычислить значения скоростей потока, предсказываемые уравнением (3.3), и с помощью графика проверить, насколько хорошо это уравнение описывает полученные данные.

 

Таблица 3.1 – Сравнительные данные скоростей

Частота (Гц)	Скорость(м/с) Экспериментальная	Скорость(м/с) Предсказываемая
0,8	0,05	0,1
4,2	0,27	0,3
8,2	0,53	0,6
10,9	0,71	0,7
13,1	0,86	0,9
16,8	1,1	1,1
20,5	1,34	1,4
	1,5	1,6
26,6	1,74	1,8
28,4	1,85	1,9
32,9	2,15	2,2
35,6	2,33	2,4
38,5	2,52	2,4
42,1	2,75	2,8

В таблице 3.1приведены сравнительные данные экспериментальной и предсказываемой (вычисленной по формуле (3.3) скоростей. Отдельно построить графики для предсказываемой и экспериментальной скоростей.

Экспериментальные значения несколько отличаются от тех, которые вычислены с помощью уравнения. Это говорит о том, что пришло время выполнить повторную калибровку. Построим на диаграмме линию тренда в виде прямой и отобразим ее уравнение.

 

Контрольные вопросы

3.4.1.Какая функция предназначена для определения углового коэффициента прямой линии, лучше всего описывающей данные?

3.4.2 Как построить на диаграмме линию тренда?

3.4.3 Можно ли на диаграмме показать уравнение регрессии?

3.4.4 Какие есть модели линии регрессии?

3.4.5 Как поместить на диаграмму величину достоверности аппроксимации?

 

Варианты заданий

Имеются две наблюдаемые величины х и у, например, объемы потребления какого-либо вида продукции за несколько месяцев (х – месяц, у – объем потребления). Необходимо найти математическую модель, наилучшим образом описывающую наблюдаемые значения. Сделать вывод о подходящей математической модели. Выполнить задание в MSExcel.

 

Варианты 1-5

Х
У

 

Варианты 6-10

Х
У

 

Варианты 11-15

Х
У

 

Варианты 16-20

Х
У

 

Варианты 21-25

Х
У

 

 

Задание 4. Методы численного решения дифференциальных уравнений с помощьюMS Excel

 

Цель работы – ознакомиться с методами численного решения дифференциальных уравнения и приобрести навыки использования этих методов в инженерных расчетах.

Математические модели физических процессов часто формулируются на языке дифференциальных уравнений.

Y¹=f(x,y) на [a,b]; y(a)=y_0; (4.1)

Рассматриваются

1. Метод Эйлера: значения в узловых точках вычисляются по формуле

y_i+1 =f(x_i,y_i)h+y_i на [a,b]; y(a)=y₀; (4.2)

2. Метод Рунге-Кутта: значения в узловых точках вычисляются по формуле:

y_i+1 = y_i +h/6(k₁+2k₂+2k₃+k₄) I=0, 1, 2,… (4.3)

где

k₁=f(x_i, y_i); k₂=f(x_i+h/2, y_i+h ₁k₁/2);

k₃=f(x_i+h/2, y_i+hk₂/2); k₄=f(x_i+h, y_i+hk₃)

 

4.1 Задание: решить дифференциальное уравнение первого порядка с начальными условиями

у¹ =x+y, [0, 1.4], h=0.02 y(0)=0;

Для решения применяется формула Эйлера:

f(x,y)=x+y

y_i+1 =(x_{i +}y_i)h+y_i (4.4)

на [0, 1.4]; y(a)=y₀;

В ячейки A3:A73 с шагом 0,02 заносятся значения x. В ячейку B3 заносится начальное условие y(0)=0; в ячейку B4 вводится формула (4.4) - B4(=(A3+B3)*0.02+B3); протянуть маркер автозаполнения – рассчитываются значения во всех узловых точках.

Рисунок 4.1