Использование функции ЛИНЕЙН
Введите в столбцы 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,0085x1)*(0,338465x2)