X-PDF

Проведение регрессионного анализа средствами MS Excel

Поделиться статьей

Расчет параметров линейной регрессии с использованием функции ЛИНЕЙН.

Для линейной аппроксимации в 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 и получаем следующие результаты:

Представленная информация была полезной?
ДА
58.6%
НЕТ
41.4%
Проголосовало: 988
Регрессионная статистика
Множественный 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 #Н/Д

Поделиться статьей
Автор статьи
Анастасия
Анастасия
Задать вопрос
Эксперт
Представленная информация была полезной?
ДА
58.6%
НЕТ
41.4%
Проголосовало: 988

или напишите нам прямо сейчас:

Написать в WhatsApp Написать в Telegram

ОБРАЗЦЫ ВОПРОСОВ ДЛЯ ТУРНИРА ЧГК

Поделиться статьей

Поделиться статьей(Выдержка из Чемпионата Днепропетровской области по «Что? Где? Когда?» среди юношей (09.11.2008) Редакторы: Оксана Балазанова, Александр Чижов) [Указания ведущим:


Поделиться статьей

ЛИТЕЙНЫЕ ДЕФЕКТЫ

Поделиться статьей

Поделиться статьейЛитейные дефекты — понятие относительное. Строго говоря, де­фект отливки следует рассматривать лишь как отступление от заданных требований. Например, одни


Поделиться статьей

Введение. Псковская Судная грамота – крупнейший памятник феодального права эпохи феодальной раздробленности на Руси

Поделиться статьей

Поделиться статьей1. Псковская Судная грамота – крупнейший памятник феодального права эпохи феодальной раздробленности на Руси. Специфика периода феодальной раздробленности –


Поделиться статьей

Нравственные проблемы современной биологии

Поделиться статьей

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


Поделиться статьей

Семейство Первоцветные — Primulaceae

Поделиться статьей

Поделиться статьейВключает 30 родов, около 1000 видов. Распространение: горные и умеренные области Северного полушария . многие виды произрастают в горах


Поделиться статьей

Вопрос 1. Понятие цены, функции и виды. Порядок ценообразования

Поделиться статьей

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


Поделиться статьей

или напишите нам прямо сейчас:

Написать в WhatsApp Написать в Telegram
Заявка
на расчет