Результаты, полученные с помощью функции линейн.

Рассмотрим назначение функции ЛИНЕЙН.

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

Функция возвращает массив, который описывает полученную прямую. Уравнение для прямой линии имеет следующий вид:

y = m₁x₁ + m₂x₂ + ... + b или y = mx + b,

где зависимое значение y является функцией независимого значения x. Значения m - это коэффициенты, соответствующие каждой независимой переменной x, а b - это постоянная. Заметим, что y, x и m могут быть векторами.

Для получения результатов необходимо создать табличную формулу, которая будет занимать 5 строк и 2 столбца. Этот интервал может располагаться в произвольном месте на рабочем листе. В этот интервал требуется ввести функцию ЛИНЕЙН.

В результате должны заполниться все ячейки интервала А65:В69 (как показано в таблице 10).

Таблица 10.

	А	В
65	13,9201	-37,3899
66	1,018506	8,372879
67	0,890367	21,23079
68	186,7916	23
69	84195,63	10367,16

Поясним назначение некоторых величин, расположенных в таблице 10.

Величины, расположенные в ячейках А65 и В65 характеризуют соответственно наклон и сдвиг.

A67 – коэффициент детерминированности.

A68 -F-наблюдаемое значение.

B68 – число степеней свободы.

A69 – факторная сумма квадратов.

B69 – остаточная сумма квадратов.

Представление результатов в виде графиков.

Рис.2. График линейной аппроксимации

Рис.3. График квадратичной аппроксимации

Рис.4. График экспоненциальной аппроксимации

Оценка значимости линейной аппроксимации.

Оценку зависимости коэффициента детерминированности проводим поF-критерию Фишера, используя формула (17), а оценку параметровa₁и а₂поt-критерию Стьюдента, используя формы (20) и (19). Результаты приведены в табл.12.

Таблица 12.

	D	E	F	G	H	I	J
64			Fтабл=	4,28
65	Dобщ=	3940,117	Fлин=	186,7916	tтабл=	2,8073	Fлин>Fтабл
66	Dфакт=	84195,63	sa1=	8,372879	ta1=	4,465594	tнабл>tтабл
67	Dост=	450,7463	sa2=	1,018506	ta2=	13,66717	tнабл>tтабл

Заметим, что значения стандартных ошибок параметров a₁и а₂, вычисленные в ячейкахG66 иG67, совпадают с данными значениями, вычисленными при помощи функции ЛИНЕЙН (они находятся в ячейкахB66 иA66 табл.10), что свидетельствует о правильности вычислений.

Имеем следующие неравенства:

Значит гипотезаH₀отвергается, т.е. коэффициент детерминированностизначим.

Значит гипотезаH₀отвергается, т.е. коэффициент а₁ значим.

Значит гипотезаH₀отвергается, т.е. коэффициент а₂ значим.

Получение числовых характеристик квадратичной зависимости.

Таблица 13.

	А	B	C
71	1,052149	-2,02647	4,489804
72	0,173422	2,70449	8,664165
73	0,958987	13,27731	#Н/Д
74	257,2069	22	#Н/Д
75	90684,48	3878,315	#Н/Д

В ячейках A71:C75 введена формула {=ЛИНЕЙН(A3:A27;B3:C27;;ИСТИНА)}.

Назначение величин, расположенных в клетках табл.13, ясно из общей табл.11.