2015-01-07
1946
Расчет параметров линейной регрессии с использованием функции ЛИНЕЙН.
Для линейной аппроксимации в Excel существует функция ЛИНЕЙН(изв. зн. Y, изв. зн. X, константа, статистика) она возвращает массив значений описывающих кривую вида:
где изв. зн. Y – это известные значения функции
изв. зн. X – это известные значения аргументов
константа – определяет чему должно равняться b, если константа имеет значение ЛОЖЬ то b полагается равным 1, иначе b вычисляется обычным образом.
статистика – если значение равно ИСТИНА то будет представлена дополнительная регрессионная статистика, если ЛОЖЬ то нет.
Для получения линейной регрессионной зависимости, с выводом всей статистической информации следует выделить диапазон A54:С58, нажать клавишу F2, и ввести формулу =ЛИНЕЙН(P2:P38;N2:O38;1;1),после окончания ввода формулы нажать комбинацию клавиш Ctrl+Shift+Enter так как данная функция возвращает массив значений. В результате в данных ячейках будет полная статистическая информация:
| Линейная зависимость | ||
| 0.645 | 0.176 | 229.123 |
| 0.039 | 0.038 | 94.969 |
| 0.963 | 115.657 | #Н/Д |
| 441.156 | #Н/Д | |
| #Н/Д |
Полученные числа имеют следующий смысл:
|
|
|
| mn | mn-1 | … | b |
| Sen | Sen-1 | … | Seb |
| R2 | Sey | ||
| F | Df | ||
| Ssreg | Ssresid |
Se – стандартная ошибка для коэффициента m
Se b – стандартная ошибка для свободного члена b
R2 – коэффициент детерминированности, который показывает как близко уравнение описывает исходные данные. Чем ближе он к 1, тем больше сходится теоретическая зависимость и экспериментальные данные.
Sey – стандартная ошибка для y
F – критерий Фишера определяет случайная или нет взаимосвязь между зависимой и независимой переменными
Df – степень свободы системы
Ssreg – регрессионная сумма квадратов
Ssresid – остаточная сумма квадратов
Аналогичным образом построим линейную регрессионную зависимость при аргументе Константа равном 0, в диапазоне E54:G58, введя формулу =ЛИНЕЙН(P2:P38;N2:O38;0;1):
| Линейная зависимость | ||
| 0.728 | 0.146 | |
| 0.021 | 0.039 | #Н/Д |
| 0.9980 | 123.365 | #Н/Д |
| 8925.124 | #Н/Д | |
| 2.7E+08 | #Н/Д |
Расчет параметров линейной регрессии с использованием инструмента Регрессия надстройки Пакет анализа.
Для проведения регрессионного анализа выберем пункт меню Данные/Анализ данных/Регрессия. Откроется следующее диалоговое окно:
После заполнения полей ввода нажимаем кнопку OK и получаем следующие результаты:
| Регрессионная статистика | |
| Множественный R | 0.981 |
| R-квадрат | 0.963 |
| Нормированный R-квадрат | 0.961 |
| Стандартная ошибка | 115.657 |
| Наблюдения |
| Дисперсионный анализ | |||||
| df | SS | MS | F | Значимость F | |
| Регрессия | 441.156 | 4.79E-25 | |||
| Остаток | 454805.4 | 13376.63 | |||
| Итого |
| Коэффициенты | Стандартная ошибка | t статистика | P Значение | Нижние 95% | Верхние 95% | Нижние 95.0% | Верхние 95.0% | |
| Y | 229.123 | 94.969 | 2.413 | 0.021 | 36.122 | 422.123 | 36.122 | 422.123 |
| X2 | 0.176 | 0.038 | 4.597 | 0.000 | 0.098 | 0.255 | 0.098 | 0.255 |
| X5 | 0.645 | 0.039 | 16.336 | 1.15E-17 | 0.565 | 0.726 | 0.565 | 0.726 |
Результаты, полученные при расчете с использованием инструмента Регрессия надстройки Пакет анализа, совпали с результатами, полученными при помощи функции ЛИНЕЙН при аргументе Константа имеющем значение ИСТИНА.
|
|
|
Расчет параметров экспоненциальной регрессии с использованием функции ЛГРФПРИБЛ.
Для экспоненциальной аппроксимации в Excel существует функция ЛГРФПРИБЛ(изв. зн. Y, изв. зн. X, константа, статистика) она возвращает массив значений описывающих кривую вида:
изв. зн. Y – это известные значения функции
изв. зн. X – это известные значения аргументов
константа – определяет чему должно равняться b, если константа имеет значение ЛОЖЬ то b полагается равным 1, иначе b вычисляется обычным образом.
статистика – если значение равно ИСТИНА то будет представлена дополнительная регрессионная статистика, если ЛОЖЬ то нет.
Для получения экспоненциальной регрессионной зависимости, с выводом всей статистической информации следует выделить диапазон I54:K58, нажать клавишу F2, и ввести формулу =ЛГРФПРИБЛ(P2:P38;N2:O38;1;1),после окончания ввода формулы нажать комбинацию клавиш Ctrl+Shift+Enter так как данная функция возвращает массив значений. В результате в данном диапазоне будет получена полная статистическая информация:
| Экспоненциальная зависимость | ||
| 1.0002 | 1.00007 | 1030.47 |
| 1.9E-05 | 0.000 | 0.046 |
| 0.940 | 0.057 | #Н/Д |
| 266.115 | #Н/Д | |
| 1.702 | 0.109 | #Н/Д |
Полученные числа имеют следующий смысл:
| mn | mn-1 | … | b |
| Sen | Sen-1 | … | Seb |
| R2 | Sey | ||
| F | Df | ||
| Ssreg | Ssresid |
Se – стандартная ошибка для коэффициента m
Se b – стандартная ошибка для свободного члена b
R2 – коэффициент детерминированности, который показывает как близко уравнение описывает исходные данные. Чем ближе он к 1, тем больше сходится теоретическая зависимость и экспериментальные данные.
Sey – стандартная ошибка для y
F – критерий Фишера определяет случайная или нет взаимосвязь между зависимой и независимой переменными
Df – степень свободы системы
Ssreg – регрессионная сумма квадратов
Ssresid – остаточная сумма квадратов
Аналогичным образом построим экспоненциальную регрессионную зависимость при аргументе Константа равном 0, в диапазоне M54:O58, введя формулу =ЛГРФПРИБЛ(P2:P38;N2:O38;0;1):
| Экспоненциальная зависимость | ||
| 1.003 | 0.99913 | |
| 0.000244 | 0.000447 | #Н/Д |
| 0.969 | 1.429 | #Н/Д |
| 542.226 | #Н/Д | |
| 2215.263 | 71.496 | #Н/Д |
