Использование функции ЛГРФПРИБЛ

Использование функции ЛИНЕЙН

Введите в столбцы A и В экспериментальные значения X и Y.

Введите в ячейки F3 и G3 обозначение коэффициентов регрессии - символы “В” и “А”. Присвойте ячейкам F4 и G4 имена В и А, соответственно (чтобы не использовать абсолютный адрес при копировании формул).

В ячейку C2 введите формулу В*А2 + А (то есть используется простейшая формула линейной аппроксимации y = Bx + A) и скопируйте эту формулу в соответствующие ячейки колонки С.

Оформить таблицу регрессии в соответствии с Листингом 6.12.

Выделите блок F4:G8. Введите в первую ячейку выделенного блока функцию ЛИНЕЙН(В2:В9;А2:А9,1,1) и вставьте ее во весь блок комбинацией клавиш Ctrl+Shift+Enter.

Листинг 6.12. Линейная аппроксимация
	A	B	C	D	E	F	G	H
	Линейная аппроксимация
	Х	Y	Ожидаемое
		0,445	0,36156044		Таблица регрессии
		0,362	0,336313187			B	A	Комментарий
		0,302	0,311065934			-0,00050	0,487797	Коэффициенты
		0,256	0,285818681		SA, SB	0,00006	0,033366	Стд. Ошибка коэфф.
		0,223	0,260571429		r^2	0,87542	0,038741	Коэфф. детерминированности
		0,197	0,235324176		F	77,29486		Степени свободы
		0,176	0,210076923		Сумма кв.	0,11601	0,01651
		0,158	0,18482967

Результат: y=-0,00050*x+0,487797

Имеет место достаточное высокое совпадение результатов регрессионного анализа с исходными данными, так как R^2=0,87542.

Функция логарифмического приближения применяется аналогично функции линейного приближения. Если имеется два вектора Х то в качестве блока аргумента х указывать область A2:B12. Вектора Х1 и Х2 не должны совпадать.

Листинг 6.13. Логарифмическое приближение
	А	B	C	D	E	F	G	H	I
		Логарифмическое приближение
	Х1	Х2	Y	Ожидаемое
			0,445	,3833	Таблица регрессии
		1,5	0,362	,3408		С	B	А
			0,302	,3031		0,338465	1,0085	0,135841	Коэффициенты
		2,5	0,256	,2695					Стд. Ошибка коэфф.
			0,223	,2396	r^2	0,975031	0,08022	#Н/Д	Стд. ошибка оценки Y.
		3,5	0,197		F	195,2452		#Н/Д	Степени свободы
			0,176	,1894	Сумма кв.	2,51316	0,06435	#Н/Д
		4,5	0,158	,1684
			0,144	,1497
		5,5	0,132	,1331

Введите в столбцы A, В и С заданные значения X1 и Х2 и Y.

Введите в ячейки F3 и G3 обозначение коэффициентов регрессии - символы “C”, “В” и “А”. Присвоить ячейкам F5, G5 и H5 имена C, В и А, соответственно (чтобы не использовать абсолютный адрес при копировании формул).

В ячейку C2 введите формулу , то есть ограничимся двумя векторами Х, и скопируем эту формулу в соответствующие ячейки колонки D.

Оформите таблицу регрессии в соответствии с листингом 6.13.

Выделите блок F5:H9. Введите в первую ячейку выделенного блока функцию ЛГРФПРИБЛ(С3:С12;А3:B12,1,1) и вставьте ее во весь блок.

Результат: y=0,135841*(1,0085^x1)*(0,338465^x2)

Дата добавления: 2014-01-06