Уравнение множественной регрессии в excel – Множественная регрессия в MS EXCEL. Примеры и методы

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

Подключение пакета анализа

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

Теперь, когда мы перейдем во вкладку «Данные», на ленте в блоке инструментов «Анализ» мы увидим новую кнопку – «Анализ данных».

Виды регрессионного анализа

О выполнении последнего вида регрессионного анализа в Экселе мы подробнее поговорим далее.

Линейная регрессия в программе Excel

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

Кликаем по кнопке «Анализ данных». Она размещена во вкладке «Главная» в блоке инструментов «Анализ».

В поле «Входной интервал Y» указываем адрес диапазона ячеек, где расположены переменные данные, влияние факторов на которые мы пытаемся установить. В нашем случае это будут ячейки столбца «Количество покупателей». Адрес можно вписать вручную с клавиатуры, а можно, просто выделить требуемый столбец. Последний вариант намного проще и удобнее.

В поле «Входной интервал X» вводим адрес диапазона ячеек, где находятся данные того фактора, влияние которого на переменную мы хотим установить. Как говорилось выше, нам нужно установить влияние температуры на количество покупателей магазина, а поэтому вводим адрес ячеек в столбце «Температура». Это можно сделать теми же способами, что и в поле «Количество покупателей».

После того, как все настройки установлены, жмем на кнопку «OK».

Разбор результатов анализа

Результаты регрессионного анализа выводятся в виде таблицы в том месте, которое указано в настройках.

Одним из основных показателей является R-квадрат. В нем указывается качество модели. В нашем случае данный коэффициент равен 0,705 или около 70,5%. Это приемлемый уровень качества. Зависимость менее 0,5 является плохой.

Ещё один важный показатель расположен в ячейке на пересечении строки

«Y-пересечение» и столбца «Коэффициенты». Тут указывается какое значение будет у Y, а в нашем случае, это количество покупателей, при всех остальных факторах равных нулю. В этой таблице данное значение равно 58,04.

Значение на пересечении граф «Переменная X1» и «Коэффициенты» показывает уровень зависимости Y от X. В нашем случае — это уровень зависимости количества клиентов магазина от температуры. Коэффициент 1,31 считается довольно высоким показателем влияния.

Как видим, с помощью программы Microsoft Excel довольно просто составить таблицу регрессионного анализа. Но, работать с полученными на выходе данными, и понимать их суть, сможет только подготовленный человек.

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

Помогла ли вам эта статья?

1 Построение множественного линейного уравнения регрессии в Excel

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

1. Задания аналитической формы уравнения регрессии и определение параметров регрессии

2. Определение в регрессии степени стохастической взаимосвязи результативного признака и факторов, проверка общего качества уравнения регрессии. Здесь необходимо знать следующие дисперсии:

где – среднее значение результативного признака;

При корреляционной связи результативного признака и факторов выполняется соотношение

Для анализа общего качества уравнение линейной многофакторной регрессии используют множественный коэффициент детерминации (квадрат коэффициента множественной корреляции), которые рассчитываются по формуле

Этот коэффициент определяет долю вариации результативного признака, обусловленную изменению факторных признаков, входящих в многофакторную регрессивную модель.

В математической статистике доказывается, что если гипотеза :=0 выполняется, то величина

имеет распределение (Фишера) с числом степеней свободыи.

Для оценки адекватности уравнения регрессии так же используют показатель средней ошибки аппроксимации:

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

В математической статистике доказывается, что если гипотеза :=0 выполняется, то величина

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

При экономической интерпретации уравнения регрессии используются частные коэффициенты эластичности:

показывающие, насколько процентов в среднем изменится значение результативного признака при изменении значения соответствующего факторного признака на один процент.

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

3. Метки в первой строке/метки в первом столбце – устанавливаются в активное состояние, если первая строка (столбец) в обходном диапазоне содержит заголовки.

4. Уровень надежности – устанавливается в активное состояние, если необходимо ввести уровень надежности отличный от уровня 95 %, применяемого по умолчанию.

6. Выходной интервал/Новый рабочий лист/Новая рабочая книга – указывается, куда необходимо вынести результаты исследования.

7. Остатки – флажок устанавливается в активное состояние, если требуется включить выходной диапазон в столбец остатков.

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

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

Построение модели множественной регрессии в MS Excel

скачать (14081.3 kb.)
Введение
Целью работы является построение модели множественной регрессии в MS Excel и построение прогнозов, принятие решений о спецификации и идентификации модели, интерпретация результатов.

) Нахождение уравнения регрессии зависимости объема продаж от ставки по депозитам и среднегодовой ставки по кредитам.

) Проведение регрессионного анализа. Оценивание качества построенной модели.

) Вычисление коэффициентов детерминации и F-критерия Фишера.

) Оценка статистической значимости коэффициентов уравнения множественной регрессии с помощью t-критерия Стьюдента при уровне значимости ? = 0,05.
1.

Построение системы показателей (факторов)
По десяти объектам экономической эффективности развития банков получены данные, характеризующие зависимость объема прибыли (Y) от среднегодовой ставки (Х1), ставки по депозитам (Х2) и размера внутрибанковских расходов (Х3).

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

. Применить инструмента Регрессия (Анализ данных в EXCEL).

. Оценить с помощью t-критерия Стьюдента статистическую значимость коэффициентов уравнения множественной регрессии.
Таблица 1. Статистические данные по всем переменным

Приведем промежуточные результаты при вычислении коэффициента корреляции:

Таблицы 2-4. Промежуточные результаты при вычислении коэффициента.

2. Анализ матрицы коэффициентов парной корреляции. Выбор факторных признаков для построения двухфакторной регрессионной модели
Использование инструмента Корреляция (Анализ данных в EXCEL):

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

. В диалоговом окне Анализ данных выберем инструмент Корреляция, а затем щелкнем на кнопку ОК.

4. В диалоговом окне Корреляция в поле Входной интервал вводим диапазон ячеек, содержащий исходные данные. Если и выделены и заголовки столбцов, то установим флажок Метки в первой строке.
Таблица 5. Результаты корреляционного анализа

Решим данную систему уравнений по формулам Крамера:

Найдем определители матриц:
Таблица 7. Нахождение определителей матриц

Найдем коэффициенты уравнения:=∆1/∆= 18,5158=∆2/∆= 0,185566=∆3/∆= 0,582028

=18,51583+0,185566×1+0,582028×2
Расчетные значения Y определяются путем последовательной подстановки в эту модель значений, факторов, взятых для каждого наблюдения.

4. Применение инструмента Регрессия (Анализ данных в EXCEL)
Регрессионный анализ — это статистический метод исследования зависимости случайной величины от переменных (аргументов), рассматриваемых в регрессионном анализе как неслучайные величины независимо от истинного закона распределения.

Для проведения регрессионного анализа выполним следующие действия:

. В диалоговом окне Анализ данных выбираем инструмент Регрессия, ОК.

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

. Если выделены и заголовки столбцов, то устанавливаем флажок Метки в первой строке.

5. Оценка качества модели. Значение F-критерия Фишера
В таблице 10 приведены вычисленные по модели значения Y и значения остаточной компоненты.

Рисунок 1. График остатков
Стандартная ошибка коэффициента корреляции рассчитывается по формуле:
Ser_k=

Он показывает долю вариации результативного признака под воздействием изучаемых факторов, т.е. в 83% случаев изменения х приводят к изменению y. Другими словами — точность подбора уравнения регрессии — высокая.

Проверку значимости уравнения регрессии можно произвести на основе вычисления F-критерия Фишера.

С помощью критерия Фишера оценивают качество регрессионной модели в целом и по параметрам. Для этого выполняется сравнение полученного значения F и табличного F значения. F фактический определяется из отношения значений факторной и остаточной дисперсий, рассчитанных на одну степень свободы:

где n — число наблюдений, а m — число параметров при факторе х. F табличный — это максимальное значение критерия под влиянием случайных факторов при текущих степенях свободы и уровне значимости а=0,05.

Значение F-критерия Фишера можно найти в таблице 4.2 протокола EXCEL.

Табличное значение F-критерия при доверительной вероятности 0,95 при V1=k=2 и V1=n-k=7 составляет 4,74. табличное значение F-критерия можно найти с помощью FРАСПОБР

Рисунок 2. Табличное значение F-критерия Фишера
6. Оценивание с помощью t-критерия Стьюдента статистической значимости коэффициентов уравнения множественной регрессии
Значимость коэффициентов уравнения регрессии а0, а1, а2 оценим с использованием t-критерия Стьюдента.

4.13	0.0445	-0.0696
0.0445	0.00374	-0.00252
-0.0696	-0.00252	0.00214

Расчетные значения t-критерия Стьюдента для коэффициентов уравнения регрессии а1, а2 приведены в четвертом столбце 4.3 протокола EXCEL. Табличное значение t-критерия при 5% уровне значимости и степенях свободы 7 составляет 2,36, его можно найти с помощью СТЬЮДРАСПОБР.

1) Коэффициент множественной корреляции показывает на весьма сильную связь всего набора факторов с результатом

2) Сравнивая Fтабл. и Fфакт мы видим, что Fтабл. =4,74Fфакт. = 9.3. С вероятностью 0,95 делаем заключение о статистической значимости уравнения в целом и показателя тесноты , которые сформировались под неслучайным воздействием факторов x₁ и x_2..

) Общий вывод состоит в том, что множественная модель с факторами x₁ и x₂ с = 0,83 содержит информативный фактор х1 и х2.

) Уравнение регрессии зависимости объема продаж от ставки по депозитам и среднегодовой ставки по кредитам:

Список литературы
1) Кремер, Н.Ш. Эконометрика / Н.Ш. Кремер, Б.А. Путко. — М.: ЮНИТИ-ДАНА, 2005.

) Под ред. И.И. Елисеевой — М. — Финансы и статистика, 2003.

) Практикум по эконометрике / Под ред. И.И. Елисеевой. — М.: Финансы и статистика, 2005.

) Айвазян С.А., Бухштабер В.М., Енюков С.А., Мешалкин Л.Д. Прикладная статистика. Классификация и снижение размерности. — М.: Финансы и статистика, 1989.

) Мартьянова М.Н., Сафронова Т.П. Основы статистики промышленности: Учебное пособие. — М.: Финансы и статистика, 1983

[expert_bq id=»1570″]5 во вкладке Параметры можно активизировать переключатель Показывать уравнение на диаграмме , что позволит увидеть уравнение линейной регрессии 4. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Одним из основных показателей является R-квадрат. В нем указывается качество модели. В нашем случае данный коэффициент равен 0,705 или около 70,5%. Это приемлемый уровень качества. Зависимость менее 0,5 является плохой.

Нелинейная регрессия в excel. Уравнение регрессии как сделать в excel

Это уравнение описывает линейную зависимость логарифма одной величины ln от другой величины x . Поэтому критерием применимости степенной регрессии служит требование того, чтобы точки эмпирических данных одной величины x i и логарифмы другой величины lnу i находились ближе всего к прямой (4.13).

4.13	0.0445	-0.0696
0.0445	0.00374	-0.00252
-0.0696	-0.00252	0.00214

Множественная нелинейная регрессия в excel. Основные задачи регрессии в Excel: пример построения модели

Пакет MS Excel позволяет при построении уравнения линейной регрессии большую часть работы сделать очень быстро. Важно понять, как интерпретировать полученные результаты. Для построения модели регрессии необходимо выбрать пункт Сервис\Анализ данных\Регрессия (в Excel 2007 этот режим находится в блоке Данные/Анализ данных/Регрессия). Затем полученные результаты скопировать в блок для анализа.

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

1) выделить столбцы с данными X и Y (они должны располагаться именно в таком порядке!);

2) вызвать Мастер диаграмм и выбрать в группе Тип – Точечная и сразу нажать Готово ;

3) не сбрасывая выделения с диаграммы, выбрать появившейся пункт основного меню Диаграмма , в котором следует выбрать пункт Добавить линию тренда ;

4) в появившемся диалоговом окне Линия тренда во вкладке Тип выбрать Линейная ;

5) во вкладке Параметры можно активизировать переключатель Показывать уравнение на диаграмме , что позволит увидеть уравнение линейной регрессии (4.4), в котором будут вычислены коэффициенты (4.5).

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

Параболической зависимостью величины Y от величины Х называется зависимость, выраженная квадратичной функцией (параболой 2-ого порядка):

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

Уравнение (4.8) параболической регрессии является частным случаем более общей регрессии, называемой полиномиальной. Полиномиальной зависимостью величины Y от величины Х называется зависимость, выраженная полиномом n -ого порядка:

где числа а i (i =0,1,…, n ) называются коэффициентами полиномиальной регрессии .

Степенной зависимостью величины Y от величины Х называется зависимость вида:

Это уравнение называется уравнением степенной регрессии Y на Х . Параметры а и b называются коэффициентами степенной регрессии .

Это уравнение описывает прямую на плоскости с логарифмическими координатными осями lnx и ln . Поэтому критерием применимости степенной регрессии служит требование того, чтобы точки логарифмов эмпирических данных lnx i и lnу i находились ближе всего к прямой (4.11).

Показательной (или экспоненциальной ) зависимостью величины Y от величины Х называется зависимость вида:

Это уравнение называется уравнением показательной (или экспоненциальной ) регрессии Y на Х . Параметры а (или k ) и b называются коэффициентами показательной (или экспоненциальной ) регрессии .

Если прологарифмировать обе части уравнения степенной регрессии, то получится уравнение

ln =x· lna +lnb (или ln =k·x +lnb ). (4.13)

Это уравнение описывает линейную зависимость логарифма одной величины ln от другой величины x . Поэтому критерием применимости степенной регрессии служит требование того, чтобы точки эмпирических данных одной величины x i и логарифмы другой величины lnу i находились ближе всего к прямой (4.13).

Логарифмической зависимостью величины Y от величины Х называется зависимость вида:

Это уравнение называется уравнением логарифмической регрессии Y на Х . Параметры а и b называются коэффициентами логарифмической регрессии .

Гиперболической зависимостью величины Y от величины Х называется зависимость вида:

Это уравнение называется уравнением гиперболической регрессии Y на Х . Параметры а и b называются коэффициентами гиперболической регрессии и определяются методом наименьших квадратов. Применение этого метода приводит к формулам:

В формулах (4.16-4.17) суммирование проводится по индексу i от единицы до количества наблюдений n .

Виды регрессии

Само это понятие было введено в математику в 1886 году. Регрессия бывает:

Пример 1

Рассмотрим задачу определения зависимости количества уволившихся членов коллектива от средней зарплаты на 6 промышленных предприятиях.

Задача. На шести предприятиях проанализировали среднемесячную заработную плату и количество сотрудников, которые уволились по собственному желанию. В табличной форме имеем:

[expert_bq id=»1570″]Выбор факторных признаков для построения двухфакторной регрессионной модели Использование инструмента Корреляция Анализ данных в EXCEL. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] 5.Затем откроется меню аргументов функции, которое позволит выбрать нужные нам столбики с данными. Для выбора первого столбика Агрессивность
нужно нажать на синюю кнопочку у строки Массив1

Регрессия в excel: уравнение, примеры. линейная регрессия

Выходной интервал Введите ссылку на левую верхнюю ячейку выходного диапазона. Отведите как минимум семь столбцов для выходной таблицы итогов, которая будет включать в себя: результаты дисперсионного анализа, коэффициенты, стандартную погрешность вычисления Y, среднеквадратичные отклонения, число наблюдений, стандартные погрешности для коэффициентов.

Нелинейная регрессия в excel. Уравнение регрессии как сделать в excel

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

Для работы необходима надстройка Пакет анализа , которую необходимо включить в пункте меню Сервис\Надстройки

В Excel 2007 для включения пакета анализа надо нажать перейти в блок Параметры Excel , нажав кнопку в левом верхнем углу, а затем кнопку «Параметры Excel » внизу окна:

Для построения модели регрессии необходимо выбрать пункт Сервис\Анализ данных\Регрессия . (В Excel 2007 этот режим находится в блоке Данные/Анализ данных/ Регрессия ). Появится диалоговое окно, которое нужно заполнить:

1) Входной интервал Y ¾ содержит ссылку на ячейки, которые содержат значения результативного признака y . Значения должны быть расположены в столбце;

2) Входной интервал X ¾ содержит ссылку на ячейки, которые содержат значения факторов . Значения должны быть расположены в столбцах;

3) Признак Метки ставится, если первые ячейки содержат пояснительный текст (подписи данных);

4) Уровень надежности ¾ это доверительная вероятность, которая по умолчанию считается равной 95%. Если это значение не устраивает, то нужно включить этот признак и ввести требуемое значение;

5) Признак Константа-ноль включается, если необходимо построить уравнение, в котором свободная переменная ;

6) Параметры вывода определяют, куда должны быть помещены результаты. По умолчанию строит режим Новый рабочий лист ;

7) Блок Остатки позволяет включать вывод остатков и построение их графиков.

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

множественный R определяется формулой (коэффициент корреляции Пирсона );

Нормированный R -квадрат вычисляется по формуле (используется для множественной регрессии);

2. Дисперсионный анализ , строка Регрессия :

Параметр df равен m (количество наборов факторов x );

Значимость F . Если полученное число превышает , то принимается гипотеза (нет линейной взаимосвязи), иначе принимается гипотеза (есть линейная взаимосвязь).

4. Дисперсионный анализ , строка Итого содержит сумму первых двух столбцов.

5. Дисперсионный анализ , строка Y-пересечение содержит значение коэффициента , стандартной ошибки и t -статистики .

P -значение ¾ это значение уровней значимости, соответствующее вычисленным t -статистикам. Определяется функцией СТЬЮДРАСП(t -статистика; ). Если P -значение превышает , то соответствующая переменная статистически незначима и ее можно исключить из модели.

6. Дисперсионный анализ , строки содержат значения коэффициентов, стандартных ошибок, t -статистик, P -значений и доверительных интервалов для соответствующих .

7. Блок Вывод остатка содержит значения предсказанного y (в наших обозначениях это ) и остатки .

Добрый день, уважаемые читатели блога! Сегодня мы поговорим о нелинейных регрессиях. Решение линейных регрессий можно посмотреть по ССЫЛКЕ .

Данный способ применяется, в основном, в экономическом моделировании и прогнозировании. Его цель – пронаблюдать и выявить зависимости между двумя показателями.

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

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

1) выделить столбцы с данными X и Y (они должны располагаться именно в таком порядке!);

2) вызвать Мастер диаграмм и выбрать в группе Тип – Точечная и сразу нажать Готово ;

3) не сбрасывая выделения с диаграммы, выбрать появившейся пункт основного меню Диаграмма , в котором следует выбрать пункт Добавить линию тренда ;

4) в появившемся диалоговом окне Линия тренда во вкладке Тип выбрать Линейная ;

5) во вкладке Параметры можно активизировать переключатель Показывать уравнение на диаграмме , что позволит увидеть уравнение линейной регрессии (4.4), в котором будут вычислены коэффициенты (4.5).

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

Параболической зависимостью величины Y от величины Х называется зависимость, выраженная квадратичной функцией (параболой 2-ого порядка):

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

Уравнение (4.8) параболической регрессии является частным случаем более общей регрессии, называемой полиномиальной. Полиномиальной зависимостью величины Y от величины Х называется зависимость, выраженная полиномом n -ого порядка:

где числа а i (i =0,1,…, n ) называются коэффициентами полиномиальной регрессии .

Степенной зависимостью величины Y от величины Х называется зависимость вида:

Это уравнение называется уравнением степенной регрессии Y на Х . Параметры а и b называются коэффициентами степенной регрессии .

Это уравнение описывает прямую на плоскости с логарифмическими координатными осями lnx и ln . Поэтому критерием применимости степенной регрессии служит требование того, чтобы точки логарифмов эмпирических данных lnx i и lnу i находились ближе всего к прямой (4.11).

Показательной (или экспоненциальной ) зависимостью величины Y от величины Х называется зависимость вида:

Это уравнение называется уравнением показательной (или экспоненциальной ) регрессии Y на Х . Параметры а (или k ) и b называются коэффициентами показательной (или экспоненциальной ) регрессии .

Если прологарифмировать обе части уравнения степенной регрессии, то получится уравнение

ln =x· lna +lnb (или ln =k·x +lnb ). (4.13)

Это уравнение описывает линейную зависимость логарифма одной величины ln от другой величины x . Поэтому критерием применимости степенной регрессии служит требование того, чтобы точки эмпирических данных одной величины x i и логарифмы другой величины lnу i находились ближе всего к прямой (4.13).

Логарифмической зависимостью величины Y от величины Х называется зависимость вида:

Это уравнение называется уравнением логарифмической регрессии Y на Х . Параметры а и b называются коэффициентами логарифмической регрессии .

Гиперболической зависимостью величины Y от величины Х называется зависимость вида:

Это уравнение называется уравнением гиперболической регрессии Y на Х . Параметры а и b называются коэффициентами гиперболической регрессии и определяются методом наименьших квадратов. Применение этого метода приводит к формулам:

В формулах (4.16-4.17) суммирование проводится по индексу i от единицы до количества наблюдений n .

Статистическая обработка данных может также проводиться с помощью надстройки ПАКЕТ АНАЛИЗА (рис. 62).

Из предложенных пунктов выбирает пункт «РЕГРЕССИЯ » и щелкаем на нем левой кнопкой мыши. Далее нажимаем ОК.

Инструмент «Регрессия» использует функцию ЛИНЕЙН .

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

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

Константа — ноль Установите флажок, чтобы линия регрессии прошла через начало координат.

Выходной интервал Введите ссылку на левую верхнюю ячейку выходного диапазона. Отведите как минимум семь столбцов для выходной таблицы итогов, которая будет включать в себя: результаты дисперсионного анализа, коэффициенты, стандартную погрешность вычисления Y, среднеквадратичные отклонения, число наблюдений, стандартные погрешности для коэффициентов.

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

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

Остатки Установите флажок для включения остатков в выходную таблицу.

Стандартизированные остатки Установите флажок для включения стандартизированных остатков в выходную таблицу.

График остатков Установите флажок для построения графика остатков для каждой независимой переменной.

График подбора Установите флажок для построения графика зависимости предсказанных значений от наблюдаемых.

График нормальной вероятности Установите флажок, для построения графика нормальной вероятности.

Для проведения расчетов выделяем курсором ячейку, в которой хотим отобразить среднее значение и нажимаем на клавиатуре клавишу =. Далее в поле Имя указываем нужную функцию, например СРЗНАЧ (рис. 22).

y=m 1 x 1 +m 2 x 2 +…+b (в случае нескольких диапазонов значений x),

где зависимое значение y — функция независимого значения x, значения m — коэффициенты, соответствующие каждой независимой переменной x, а b — постоянная. Обратите внимание, что y, x и m могут быть векторами. Функция ЛИНЕЙН возвращает массив. ЛИНЕЙН может также возвращать дополнительную регрессионную статистику.

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

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

Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная.

Если массив известные_значения_y имеет одну строку, то каждая строка массива известные_значения_x интерпретируется как отдельная переменная.

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

Если массив_известные_значения_x опущен, то предполагается, что этот массив имеет такой же размер, как и массив_известные_значения_y.

Конст — логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0.

Если аргумент «конст» имеет значение ИСТИНА или опущен, то константа b вычисляется обычным образом.

Если аргумент «конст» имеет значение ЛОЖЬ, то значение b полагается равным 0 и значения m подбираются таким образом, чтобы выполнялось соотношение y=mx.

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

Если аргумент «статистика» имеет значение ИСТИНА, функция ЛИНЕЙН возвращает дополнительную регрессионную статистику. Возвращаемый массив будет иметь следующий вид: .

Если аргумент «статистика» имеет значение ЛОЖЬ или опущен, функция ЛИНЕЙН возвращает только коэффициенты m и постоянную b.

На приведенном ниже рисунке показано, в каком порядке возвращается дополнительная регрессионная статистика (рис. 64).

Любую прямую можно описать ее наклоном и пересечением с осью y:

Наклон (m): чтобы определить наклон прямой, обычно обозначаемый через m, нужно взять две точки прямой (x 1 ,y 1) и(x 2 ,y 2); наклон будет равен (y 2 -y 1)/(x 2 -x 1).

Y-пересечение (b): Y-пересечением прямой, обычно обозначаемым через b, является значение y для точки, в которой прямая пересекает ось y.

Уравнение прямой имеет вид y=mx+b. Если известны значения m и b, то можно вычислить любую точку на прямой, подставляя значения y или x в уравнение. Можно также воспользоваться функцией ТЕНДЕНЦИЯ.

Если имеется только одна независимая переменная x, можно получить наклон и y-пересечение непосредственно, воспользовавшись следующими формулами:

Наклон: ИНДЕКС (ЛИНЕЙН(известные_значения_y; известные_значения_x); 1)

Y-пересечение: ИНДЕКС (ЛИНЕЙН (известные_значения_y; известные_значения_x); 2)

где x и y – выборочные средние значения, например x = СРЗНАЧ (известные_значения_x), а y = СРЗНАЧ (известные_значения_y).

Вычисление df для случаев, когда столбцы X не удаляются из модели вследствие коллинеарности происходит следующим образом: если существует k столбцов известных_значений_x и значение конст = ИСТИНА или не указано, то df = n – k – 1. Если конст = ЛОЖЬ, то df = n — k. В обоих случаях удаление столбцов X вследствие коллинеарности увеличивает значение df на 1.

Формулы, которые возвращают массивы, должны быть введены как формулы массива.

При вводе массива констант в качестве, например, аргумента известные_значения_x следует использовать точку с запятой для разделения значений в одной строке и двоеточие для разделения строк. Знаки-разделители могут быть различными в зависимости от параметров, заданных в окне «Язык и стандарты» на панели управления.

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

Основной алгоритм, используемый в функции ЛИНЕЙН , отличается от основного алгоритма функций НАКЛОН и ОТРЕЗОК . Разница между алгоритмами может привести к различным результатам при неопределенных и коллинеарных данных. Например, если точки данных аргумента известные_значения_y равны 0, а точки данных аргумента известные_значения_x равны 1, то:

Функция ЛИНЕЙН возвращает значение, равное 0. Алгоритм функции ЛИНЕЙН используется для возвращения подходящих значений для коллинеарных данных, и в данном случае может быть найден по меньшей мере один ответ.

Функции НАКЛОН и ОТРЕЗОК возвращают ошибку #ДЕЛ/0!. Алгоритм функций НАКЛОН и ОТРЕЗОК используется для поиска только одного ответа, а в данном случае их может быть несколько.

Помимо вычисления статистики для других типов регрессии функцию ЛИНЕЙН можно использовать при вычислении диапазонов для других типов регрессии, вводя функции переменных x и y как ряды переменных х и у для ЛИНЕЙН. Например, следующая формула:

работает при наличии одного столбца значений Y и одного столбца значений Х для вычисления аппроксимации куба (многочлен 3-й степени) следующей формы:

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

[expert_bq id=»1570″]После того как выбрали исходные данные и нажали кнопочку ОК, Excel выдает расчеты на новом листе активной книги если в настройках не было выставлено иначе , эти расчеты имеют следующий вид. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] 2.Создать столбцы с данными. В нашем примере мы будем считать взаимосвязь, или корреляцию, между агрессивностью и неуверенностью в себе у детей-первоклассников. В эксперименте участвовали 30 детей, данные представлены в таблице эксель:

Регрессионный анализ в Excel – линейная, множественная, степенная и нелинейная регрессия, построить уравнение, расшифровка результата и примеры

Для этой модели формула расчета выглядит так: y = a*x˄b. Выбросы для данного метода вычисляются автоматически. Используется, если уровень достоверности техники выше остальных – графа R˄2.