Регрессия в эксель как сделать

С помощью инструмента анализа данных Регрессия можно получить результаты регрессионной статистики, дисперсионного анализа, доверительных интервалов, остатки и графики подбора линии регрессии.

Если в меню Сервис еще нет команды Анализ данных, то необходимо сделать следующее. В главном меню последовательно выбираем Сервис —> Надстройки и устанавливаем «флажок» в строке Пакет анализа (рис. 8.15):

Далее следуем по следующему плану.

1. Если исходные данные уже внесены, то выбираем Сервис —? Анализ данных —> Регрессия (рис. 8.16).

Щелкните по кнопке ОК. И в открывшемся окне устанавливаем значения параметров (рис. 8.17).

1. Входной интервал Y — это диапазон данных по результативному признаку. Он должен состоять из одного столбца.

2. Входной интервал X — это диапазон ячеек, содержащих значения факторов (независимых переменных). Число входных диапазонов (столбцов) должно быть не больше 16.

Регрессия. Регрессионный анализ в Excel

Рис. 8.15. Настройка пакета анализа

• 3. Флажок Метки, устанавливается в том случае, если в первой строке диапазона стоит заголовок.
• 4. Флажок Уровень надежности активизируется, если в поле, находящееся рядом с ним, необходимо ввести уровень надежности, отличный от установленного по умолчанию. Используется для проверки значимости коэффициента детерминации г 2 и коэффициентов регрессии (по умолчанию 95 %).
• 5. Константа ноль. Данный флажок необходимо установить, если линия регрессии должна пройти через начало координат (р0 = 0).
• 6. Выходной интервал. Новый рабочий лист / Новая рабочая книга — указать адрес верхней левой ячейки выходного диапазона.
• 7. Флажки в группе Остатки устанавливаются, если необходимо включить в выходной диапазон соответствующие столбцы или графики.
• 8. Флажок График подбора необходимо сделать активным, если требуется вывести на рабочий лист точечный график зависимости теоретических результативных значений у от факторных признаков xt.
• 9. Флажок График нормальной вероятности необходимо сделать активным, если требуется вывести на лист точечный график зависимости наблюдаемых значений Y от автоматически формируемых интервалов пер- сентилей.

Рис. 8.16. Диалоговое окно анализа данных

После установки значений параметров (рис. 8.17) щелкните по кнопке ОК.

По окончанию расчета на рабочий лист выводится три группы результатов.

Первая группа — регрессионная статистика — включает в свой состав (рис. 8.18):

• • множественный ^-коэффициент множественной корреляции;
• • ^-квадрат — множественный коэффициент детерминации;
• • нормированный ^-квадрат — скорректированный коэффициент детерминации;
• • стандартную ошибку — стандартную ошибку регрессии;
• • наблюдения — количество наблюдений.

Множественная регрессия в Excel

Рис. 8.17. Диалоговое окно установки значений параметров

Рис. 8.18. Вывод итогов

Анализ рис. 8.18 показывает, что г 2 = 0,920039079. Это значит, что 92 % вариации.

Изменения касательных напряжений т объясняется изменчивостью нормального напряжения Р.

Вторая группа результатов — дисперсионный анализ, здесь использован ряд общепринятых сокращений (рис. 8.19):

• • df- степени свободы (degree of freedom);
• • SS — сумма квадратов отклонений );
• • MS — средний квадрат отклонения );
• • F-отношение дисперсий;
• • значимость F — критическое значение квантиля распределения Фишера, на котором отвергается нулевая гипотеза отсутствия влияния фактора.

Рис.8.19. Дисперсионный анализ

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

F-отношение дисперсий равно 115,061.

Значимость функции определена по функции ^.РАСП.ПХ (функция ^.РАСП.ПХ), которая возвращает правый хвост F-распределения вероятности для двух наборов данных. Эта функция позволяет определить, имеют ли два множества данных различные степени разброса результатов.

Синтаксис: ЕРАСП.ПХ (х; степенисвободы 1; степенисвободы 2).

X — обязательный аргумент. Значение, для которого вычисляется функция.

Степени свободы 1 — обязательный аргумент. Числитель степеней свободы.

Степени свободы 2 — обязательный аргумент. Знаменатель степеней свободы.

Для нашего случая ^.РАСП.ПХ (115,061;1;10) = 8,32697Е-07.

Значимость F — критическое значение квантиля распределения Фишера равно 8,32697Е-07 « 0,01, т. е. гипотеза Я0: Pi = 0 отвергается, признаётся статистическая значимость уравнения регрессии, т. е. связь между рассматриваемыми признаками есть и результаты наблюдений не противоречат предположению о её линейности.

Таким образом, мы получили те же расчеты, что и вручную.

Третья группа результатов включает в свой состав значения коэффициентов регрессии, а также статистики, на основании которых проверяется значимость влияния фактора для каждого коэффициента, включенного в модель (рис. 8.20):

• • коэффициенты — значения коэффициентов регрессии (столбец 2);
• • стандартная ошибка — стандартная ошибка коэффициентов регрессии (столбец 3);
• • /-статистика — значение статистики критерия (столбец 4);
• • Р-значение — уровень значимости отклонения гипотезы равенства коэффициентов нулю (столбец 5):

• 1) если P-значение больше заданного уровня значимости (в данном случае 0,05 для уровня доверия 0,95), то соответствующий параметр регрессии является незначимым (значение /-статистики меньше критического значения для заданного уровня доверия);
• 2) если P-значение меньше заданного уровня значимости (в данном случае 0,05 для уровня доверия 0,95), то соответствующий параметр регрессии является значимым (значение /-статистики больше критического значения для заданного уровня доверия).

Рис. 8.20. Результаты применения /-критерия, полученные с помощью Пакета анализа Excel

Уровень доверия можно задать самостоятельно в окне диалога Регрессия (см. рис. 8.17), по умолчанию уровень доверия 95 %. В этом случае в выходной информации пакета Регрессия (рис. 8.20) будут представлены доверительные интервалы для параметров регрессии для уровня 95 % и заданного уровня.

Microsoft Excel существенно облегчает выполнение расчетов и представляет результаты в наглядной форме. Заметим, что построение уравнения регрессии с несколькими независимыми переменными (уравнения множественной регрессии) без использования Microsoft Excel (или другого статистического пакета программ) становится практически невозможным.

Нижние 95 % — нижняя граница доверительного интервала, в котором находится значение коэффициента генеральной совокупности (столбец 6). Верхние 95 % — верхняя граница доверительного интервала, в котором находится значение коэффициента генеральной совокупности (столбец 7). Как следует из рис. 8.20, коэффициенты уравнения: р0 = 4,7367, a (3j = 0,24265.

Таким образом, уравнение линейной регрессии для этих данных имеет следующий вид: у = 4,7367 + 0,24265 х.

Видим, что это уравнение, совпадает с уравнением, полученным нами при расчете по МНК вручную с точностью до ошибки округления.

В четвертом столбце приведена ^-статистика коэффициентов

определяемых делением значений второго столбца на третий.

В пятом столбце приведено P-значение для коэффициентов

определяемых по СТЬЮДЕНТ.РАСП.2Х (функция СТБЮДЕНТ.РАСП.2Х), которое возвращает двустороннее ^-распределение Стьюдента.

Синтаксис: СТЫОДЕНТ.РАСП.2Х (х; степени свободы), для нашего случая:

СТЬЮДЕНТ.РАСП.2Х(6,582; 10) = 6,21676Е-05;

СТЬЮДЕНТ.РАСП.2Х( 10,73; 10) = 8,32697Е-07.

Так как P-значение значительно меньше 0,01, делается вывод: гипотеза Hq bo = 0, b = 0 отвергается в пользу альтернативной при выбранном уровне значимости. Это подтверждает статистическую значимость коэффициента регрессии Ь.

Как видим, все расчеты совпадают с расчетами сделанными вручную.

Нижние 95 % — нижняя граница доверительного интервала, в котором находится значение коэффициента генеральной совокупности.

Верхние 95 % — верхняя граница доверительного интервала, в котором находится значение коэффициента генеральной совокупности.

Источник: studref.com

Пакет анализа Excel (программа «Регрессия»)

Расчет параметров уравнения линейной регрессии, проверку их статистической значимости и построения интервальных оценок можно выполнить значительно быстрее автоматически при использовании Пакета анализа Excel (программа «Регрессия»)

Пусть исходные данные примера 2.1 (расходы на питание – личный доход) представлены в Excel.

Выбираем команду Анализ данных→Регрессия.

В диалоговом окне режима Регрессия задаются следующие параметры:

® Входной интервал У – вводится ссылка на ячейки, содержащие данные по результативному признаку.

® Входной интервал Х – вводится ссылка на ячейки, содержащие факторные признаки.

® Метки – установите флажок в активное состояние, если выделены и заголовки столбцов.

® Константа- ноль – установите флажок в активное состояние, если оцениваете регрессионное уравнение без свободного члена.

При необходимости задаются и другие параметры.

Результаты расчетов с использованием инструмента Регрессия выводятся под общим названием Вывод итогов в виде следующих таблиц.

Дисперсионный анализ
df	SS	MS	F	Значимость F
Регрессия	96,1	96,1	29,12	0,01247
Остаток	9,9	3,3
Итого

Коэффи- циенты	Стандартная ошибка	t-статис- тика	P- зна- чение	Нижнее 95%	Верхние 95%
Y – пересеч.	-1,75	1,65	-1,06	0,36669	-7,001	3,501
X	0,775	0,14361	5,40	0,01247	0,318	1,232

Результаты работы программы «Регрессия» полностью совпадают с полученными ранее расчетами.

При необходимости выводятся предсказанные значения результативного признака и значения остатков.

ВЫВОД ОСТАТКА
Наблюдение	Предсказанное у	Остатки
-0,2	1,2
2,9	-0,9
-2
9,1	1,9
12,2	-0,2

Коэффициенты регрессии, их стандартные ошибки и коэффициент детерминации составляют:

a = -1,75; b =0,775; = 1,65; =0,143; = 0,907

Результаты регрессионного анализа принято записывать в виде:

ȳ= -1,75+0,775х; = 0,907,

( 1,65 )( 0,143 )

где в скобках указаны стандартные ошибки коэффициентов регрессии.

Статическая значимость коэффициента = 0,907 устанавливается по F – тесту. Поскольку Значимость F= 0,0124 = 0,907 значим при уровне 5%. Модель в целом значима.

Обычно проверка значимости коэффициента а не производится. Оценим статистическую значимость коэффициента b.

Результаты оценивания регрессии совместимы не только с полученным значением коэффициента регрессии b = 0,775, но и с некоторым его множеством (доверительным интервалом). С вероятностью 95% доверительный интервал коэффициента b есть (0,318 ….1,232).

Пример. Имеются данные (усл. ед.) о расходах на питание y и душевого похода х для девяти групп семей:

х

у

Используя результаты работы программы «Регрессия», проанализируйте зависимость расходов на питание от величины душевого дохода.

Результаты регрессионного анализа записываем в виде:

ȳ= 66,04+0,107 х, = 0,885,

( 11,72 ) ( 0,015 )

где в скобках указаны стандартные ошибки коэффициентов регрессии.

Качество модели оценивается коэффициентов .

Величина = 0,885 означает, что фактором душевого дохода можно объяснить 88,5% вариации (разброса) расходов на питание.

Установим статистическую значимость коэффициента .

Поскольку Значимость F = 0,000158 < α = 0,05, то =0,885 значим при уровне 5%.

Направление связи между переменными у и х определяет знак коэффициента b =0,107 ˃ 0, т.е. связь является прямой(положительной).

Коэффициент b =0,107 показывает, что при увеличении душевого дохода на 1 усл. ед. расходы на питание в среднем увеличиваются на 0,107 усл. ед.

Вследствие большой популярности эконометрических исследований на Западе средства построения эконометрических моделей включены во все известные интегрированные офисные средства (Microsoft Office, Perfect Office и т. д.) и табличные процессоры (Excel, Lotus 1-2-3, Quattro Pro и др.).

Рассмотрим методику построения эконометрических моделей с помощью встроенных функций Microsoft Excel.

Построение эконометрических моделей требует выполнения множества расчетов по определению параметров и характеристик.

В зависимости от целей исследования и вида уравнения регрессии расчеты в Excel могут быть выполнены с помощью различных функций ЛИНЕЙН, ЛГРФПРИБЛ, ТЕНДЕНЦИЯ, РОСТ и др.

Приведем методику использования MS Excel для построения эконометрических уравнений на примере линейной регрессии (ЛИНЕЙН).

Встроенная статистическая функция ЛИНЕЙН определяет параметры линейной регрессии:

y=mx+b или y=m1x1 + m2x2 +. + b,

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

ЛИНЕЙН (известные значения y; известные значения x; конст; статистика)

известные значения y — это множество значений y, которые уже известны для соотношения y=mx+b.

Массив известные значения х может содержать одно или несколько множеств переменных.

Конст — это логическое значение, которое указывает, требуются ли, чтобы константа b была равна нулю. Константа принимает одно из двух значений ИСТИНА или ЛОЖЬ. Если конст имеет значение истина или опущено, то b вычисляется, если конст имеет значение ЛОЖЬ, то b полагается равным 0.

Статистика — это логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии.

Статистика также принимает одно из значений ИСТИНА или ЛОЖЬ. В первом случае дополнительная статистика рассчитывается, во втором случае не рассчитывается.

Дополнительные статистические характеристики функции ЛИНЕЙН приведены ниже Дополнительные статистические характеристики функции ЛИНЕЙН приведены ниже:

b, m1, m2,…mn – коэффициенты регрессии (параметры модели);

se1, se­2. sen — стандартные значения ошибок для коэффициентов m1,m2. mn;

seb — стандартное значение ошибки для постоянной b;

r 2 — коэффициент детерминированности;

sey — стандартная ошибка для оценки y;

F — F -статистика, используемая для определения того, является ли наблюдаемая взаимосвязь между зависимой и независимой переменными случайной или нет;

df — степени свободы, используемые для нахождения F -критических значений в статистической таблице (для определения уровня надежности модели нужно сравнить значения в таблице с F -статистикой функции ЛИНЕЙН);

ssreg — регрессионая сумма квадратов;

ssresid — остаточная сумма квадратов.

Характеристики выводятся на экран дисплея в виде приведенного ниже массива (таблицы):

mn	mn-1	…	m2	m1	b
sen	Sen-1	…	se2	se1	seb
r 2	Seу	…
F	Df	…
ssreg	ssresid	…

Порядок выполнения расчетов следующий:

1. Вводятся исходные данные или открывается существующий файл, содержащий исходные данные.

2. В рабочем окне Excel выделяется диапазон ячеек 5*(n +1) (5 число строк, (n +1) — число столбцов, n – число показателей факторов) для вывода результатов расчета.

3. Активизируются «Мастер функций» любым из способов:

а) в главном меню выбирается Вставка/Функция;

б) на панели инструментов Стандартная нажимается кнопка (fx)

4. В появившемся окне «Мастер функций шаг 1 из 2» среди категорий выбирается Статистические, среди функций — ЛИНЕЙН шаг 1 из 2 (рис. 3.1.1)

Рис. 3. 1. 1. Диалоговое окно «Мастер функций шаг 1 из 2»

5. В появившемся втором окне «Мастер функций» (рис. 3. 1. 2)

вводятся аргументы, т.е. указываются диапазоны ячеек рабочего окна EXCEL, в которых находятся исходные данные для У и Х, а также значения аргументов константа и статистика.

Рис. 3. 1. 2. Второе диалоговое окно «Мастер функций»

Рис. 3. 1. 3. Результат вычисления функции ЛИНЕЙН

6. Нажимается кнопка ОК. В выделенном диапазоне рабочего окна

Excel появляется результат — численное значение для коэффициента регрессии (b). Чтобы вывести всю статистику следует нажать клавишу , а затем — комбинацию клавиш ++.

Понравилась статья? Добавь ее в закладку (CTRL+D) и не забудь поделиться с друзьями:

Источник: studopedia.ru

Прогнозирование с помощью линейной регрессии в Microsoft Excel — inc. Сезонность и рост

В этой статье представлено пошаговое руководство о том, как выполнять простое, но эффективное прогнозирование в Microsoft Excel с использованием модели линейной регрессии.