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

Использование Пакета анализа EXCEL для построения множественной линейной регрессионной модели

Эффективно использовать надстройку Пакет анализа могут только пользователи знакомые с теорией множественного регрессионного анализа .

В данной статье решены следующие задачи:

• Показано как в MS EXCEL выполнить регрессионный анализ с помощью надстройки Пакет анализа (инструмент Регрессия), т.е. как вызвать надстройку и правильно заполнить входные данные;
• Даны пояснения по разделам отчета, формированного надстройкой;
• Даны комментарии обо всех показателях, рассчитанных надстройкой, и приведены ссылки на соответствующие разделы статей, посвященные простой линейной регрессии .

В надстройке Пакет анализа для построения линейной регрессионной модели (как простой , так и множественной ) имеется специальный инструмент Регрессия .

После выбора этого инструмента откроется окно, в котором требуется заполнить следующие поля (см. файл примера лист Надстройка ):

• Входной интервалY : ссылка на массив значений переменной Y. Ссылку можно указать с заголовком. В этом случае, при выводе результатов надстройка использует Ваш заголовок (для этого в окне требуется установить галочку Метки );
• Входной интервал Х : ссылка на значения переменных Х (нужно указать все столбцы со значениями Х). Ссылку рекомендуется делать на диапазон с заголовками (в окне не забудьте установить галочку Метки );
• Константа-ноль : если галочка установлена, то надстройка подбирает плоскость регрессии с b0 =0;
• Уровень надежности : Это значение используется для построения доверительных интервалов для наклона и сдвига . Уровень надежности = 1- альфа . Если галочка не установлена или установлена, но уровень значимости = 95%, то надстройка все равно рассчитывает границы доверительных интервалов, причем дублирует их. Если галочка установлена, а уровень надежности отличен от 95%, то рассчитываются 2 доверительных интервала : один для 95%, другой для введенного значения. Для демонстрации вышесказанного введем 90%;
• Выходной интервал: диапазон ячеек, куда будут помещены результаты вычислений. Достаточно указать левую верхнюю ячейку этого диапазона;
• Остатки : будут вычислены остатки модели , т.е. разница между наблюденными и предсказанными значениями Yi для всех наблюдений n;
• Стандартизированные остатки : Вышеуказанные значения остатков будут поделены на значение их стандартного отклонения ;
• График остатков : Для каждой переменной X _j будет построена точечная диаграмма : значения остатков и соответствующее значение Х _ji (при прогнозировании на основании значений 2-х переменных Х будет построено 2 диаграммы (j=1 и 2));
• График подбора: Для каждой переменной X _j будут построены точечные диаграммы с двумя рядами данных : точки данных (X _ji ;Y _i ) и (X _ji ;Y _{iпредсказанное} );
• График нормальной вероятности: Будет построена точечная диаграмма с названием График нормального распределения . По сути — это график значений переменной Y, отсортированных по возрастанию .

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

Тот же результат можно получить с помощью формул (см. файл примера лист Надстройка , столбцы I:T).

Результаты вычислений, выполненных надстройкой, полностью совпадают с вычислениями сделанными нами в статье про множественную линейную регрессию с помощью функций ЛИНЕЙН() , ТЕНДЕНЦИЯ() и др. Использование альтернативных формул помогает разобраться с алгоритмом расчета показателей регрессии.

Отчет, сформированный надстройкой, состоит из следующих разделов:

Раздел «Регрессионная статистика»:

• Множественный R. В случае множественной линейной регрессии — это квадратный корень из коэффициента детерминации R 2
• R-квадрат . В случае множественной линейной регрессии – это коэффициент детерминации R 2
• Нормированный R-квадрат . Подробнее см. здесь (англ. термин Adjusted R-squared)
• Стандартная ошибка . Подробнее см. здесь ;
• Наблюдения . Количество значений Y.

Раздел «Дисперсионный анализ»:

• df – степени свободы (Degrees of Freedom).
• SS – сумма квадратов (Sum of Squares)
• MS – SS/df (MSR и MSE)
• F – значение статистики F ₀ (MSR/MSE)
• ЗначимостьF – p-значение, функция F.РАСП.ПХ()

Другие результаты:

• Коэффициенты : оценка параметров модели b _j . См. раздел Оценка неизвестных параметров .
• Стандартная ошибка : Стандартные ошибки вышеуказанных статистик
• t-статистика : значение тестовой статистики t ₀ , которая имеет распределение Стьюдента . Используется для проверки значимости индивидуальных коэффициентов. t ₀ – отношение оценки коэффициента регрессии и его стандартного отклонения (модуль этого значения). Если это значение меньше критического значения =СТЬЮДЕНТ.ОБР.2Х(0,05;DF) , то коэффициент не значимый.
• P-Значение : Используется для проверки значимости индивидуальных коэффициентов. Если вероятность t-статистики меньше уровня значимости (обычно 0,05), то коэффициент не значимый
• Нижние 95% и Верхние 95%: границы доверительных интервалов для оценок неизвестных параметров модели с уровнем значимости =1-95%=5%=0,05.

Регрессионный анализ в excel

​Смотрите также​ есть ли между​ которых можно рассчитать​Чтобы использовать полный набор​ Если и они​ формулу расчета МНК​ все так просто,​…​ регрессии, получают цифру​ того, что будет​ уравнение вида y=ax+b,​i​ Иными словами можно​ X.​ за пару минут.​

​ на что следует​ образом:​

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

​Регрессионный анализ является одним​ показателями в одной​ параметры модели линейной​ приложений и служб​ не заданы, то​ в Excel к​ так как наряду​y​ в 64,72 млн​

1. ​ отвергнута верная гипотеза​​ где в качестве​​— стандартизированные коэффициенты​
2. ​ утверждать, что на​​Анализу регрессии в Excel​​ Ниже представлены конкретные​
3. ​ обратить внимание, так​У = а0 +​​ из самых востребованных​​ или двух выборках​
4. ​ регрессии. Но быстрее​ Office, поработайте с​ в качестве аргумента​​ нашей таблице.​​ с положительными отклонениями​​n​​ американских долларов. Это​ о незначимости коэффициента​ параметров a и​​ регрессии, а среднеквадратическое​​ значение анализируемого параметра​
5. ​ должно предшествовать применение​ примеры из области​ это на параметры​​ а1х1 +…+акхк​​ методов статистического исследования.​ связь. Например, между​

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

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

​ оказывают влияние и​

• ​ к имеющимся табличным​
• ​ экономики.​
• ​ вывода. По умолчанию​
• ​. В этой формуле​
• ​ С его помощью​
• ​ временем работы станка​
• ​ «Пакет анализа».​

​ приобретите его на​ 2; 3; 4;…,​ в которой должен​ и отрицательные.​

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

​ будут более-менее корректными,​ АО «MMM» не​ 0,12%.​ строки с наименованием​Обратите внимание, что все​ другие факторы, не​ данным встроенных функций.​Само это понятие было​ вывод результатов анализа​Y​ можно установить степень​ и стоимостью ремонта,​Активируем мощный аналитический инструмент:​ сайте Office.com.​

​ который соразмерен диапазону​ быть отображен результат​Решить вопрос можно, используя​ ​ если исследуются данные​ стоит приобретать, так​ ​Таким образом, можно утверждать,​​ номера месяца и​​ β​ описанные в конкретной​ Однако для этих​ введено в математику​ осуществляется на другом​означает переменную, влияние​​ влияния независимых величин​​ ценой техники и​Нажимаем кнопку «Офис» и​Регрессионный и корреляционный анализ​​ с уже заданными​​ расчета по методу​ модули отклонений или​ по хотя бы​ как их стоимость​ что полученное уравнение​​ коэффициенты и строки​​i​ модели.​

1. ​ целей лучше воспользоваться​​ Фрэнсисом Гальтоном в​​ листе, но переставив​ факторов на которую​​ на зависимую переменную.​​ продолжительностью эксплуатации, ростом​​ переходим на вкладку​​ – статистические методы​
2. ​ параметрами y.​ наименьших квадратов в​​ их квадраты. Последний​​ 5-6 объектам. Кроме​​ в 70 млн​​ линейной регрессии адекватно.​
3. ​ «Y-пересечение» из листа​в данном случае​Следующий коэффициент -0,16285, расположенный​ очень полезной надстройкой​​ 1886 году. Регрессия​​ переключатель, вы можете​​ мы пытаемся изучить.​​ В функционале Microsoft​ и весом детей​ «Параметры Excel». «Надстройки».​ ​ исследования. Это наиболее​​Диапазон, содержащий новые значения​​ Excel, введем знак​ метод получил наиболее​ того, нельзя использовать​ американских долларов достаточно​Множественная регрессия в Excel​ с результатами регрессионного​ заданы, как нормируемые​ в ячейке B18,​ «Пакет анализа». Для​ бывает:​ установить вывод в​ В нашем случае,​ Excel имеются инструменты,​ и т.д.​ ​Внизу, под выпадающим списком,​​ распространенные способы показать​​ x должен состоять​ «=» и выберем​ широкое распространение. Он​ «аномальные» результаты. В​ завышена.​ выполняется с использованием​ анализа. Таким образом,​ и централизируемые, поэтому​ показывает весомость влияния​ его активации нужно:​линейной;​ указанном диапазоне на​ это количество покупателей.​ предназначенные для проведения​Если связь имеется, то​ в поле «Управление»​ ​ зависимость какого-либо параметра​ из такого же​ функцию «ТЕНДЕНЦИЯ». В​ используется во многих​ частности, элитный небольшой​Как видим, использование табличного​ все того же​ линейное уравнение регрессии​ их сравнение между​ переменной Х на​с вкладки «Файл» перейти​параболической;​ том же листе,​ Значение​ подобного вида анализа.​ влечет ли увеличение​ будет надпись «Надстройки​ от одной или​ или большего количества​ раскрывшемся окне заполним​ областях, включая регрессионный​ бутик может иметь​ процессора «Эксель» и​ инструмента «Анализ данных».​ (УР) для задачи​ ​ собой считается корректным​ Y. Это значит,​ в раздел «Параметры»;​​степенной;​​ где расположена таблица​

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

​x​ Давайте разберем, что​ одного параметра повышение​ Excel» (если ее​ нескольких независимых переменных.​

​ строк или столбцов,​ соответствующие поля, выделяя:​​ анализ (в Excel​​ товарооборот в разы​ уравнения регрессии позволило​ Рассмотрим конкретную прикладную​ 3 записывается в​ и допустимым. Кроме​ что среднемесячная зарплата​в открывшемся окне выбрать​экспоненциальной;​

​ с исходными данными,​– это различные​ они собой представляют​​ (положительная корреляция) либо​​ нет, нажмите на​​Ниже на конкретных практических​​ как диапазон с​диапазон известных значений для​ его реализация осуществляется​ больший, чем товарооборот​ принять обоснованное решение​ задачу.​ виде:​ того, принято осуществлять​ сотрудников в пределах​

​ строку «Надстройки»;​​гиперболической;​​ или в отдельной​​ факторы, влияющие на​​ и как ими​ уменьшение (отрицательная) другого.​ флажок справа и​ примерах рассмотрим эти​ заданными значениями y.​ Y (в данном​ с помощью двух​ больших торговых точек​ относительно целесообразности вполне​

​Руководство компания «NNN» должно​Цена на товар N​ отсев факторов, отбрасывая​ рассматриваемой модели влияет​щелкнуть по кнопке «Перейти»,​показательной;​ книге, то есть​ переменную. Параметры​ пользоваться.​

​ Корреляционный анализ помогает​

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

​ выберите). И кнопка​ два очень популярные​ Иными словами он​ случае данные для​ встроенных функций), и​ класса «масмаркет».​ конкретной сделки.​ принять решение о​ = 11,714* номер​ те из них,​ на число уволившихся​ расположенной внизу, справа​логарифмической.​ в новом файле.​a​Скачать последнюю версию​ аналитику определиться, можно​ «Перейти». Жмем.​ в среде экономистов​ должен быть соразмерным​

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

​ товарооборота);​ давно доказал свою​Данные таблицы можно изобразить​Теперь вы знаете, что​ целесообразности покупки 20​

• ​ месяца + 1727,54.​
• ​ у которых наименьшие​
• ​ с весом -0,16285,​
• ​ от строки «Управление»;​
• ​Рассмотрим задачу определения зависимости​
• ​После того, как все​
• ​являются коэффициентами регрессии.​

Пример 1

​ Excel​ ли по величине​Открывается список доступных надстроек.​ анализа. А также​ независимым переменным.​

​диапазон x​ эффективность.​ на декартовой плоскости​ такое регрессия. Примеры​ % пакета акций​или в алгебраических обозначениях​ значения βi.​

​поставить галочку рядом с​

​ количества уволившихся членов​

​ настройки установлены, жмем​

​Но, для того, чтобы​

​ одного показателя предсказать​

​ Выбираем «Пакет анализа»​

​ приведем пример получения​

​В массиве с известными​

​В Excel, как известно,​

​ в Excel, рассмотренные​

​ АО «MMM». Стоимость​

​Предположим, имеется таблица динамики​

​ ее влияния совсем​

​ названием «Пакет анализа»​

​ коллектива от средней​

​ они определяют значимость​

​ использовать функцию, позволяющую​

​ возможное значение другого.​

​ результатов при их​

​ значениями x может​

​ существует встроенная функция​

​ выше, помогут вам​

​ пакета (СП) составляет​

​ цены конкретного товара​

​ небольшая. Знак «-»​ и подтвердить свои​ зарплаты на 6​«OK»​ того или иного​ провести регрессионный анализ,​Коэффициент корреляции обозначается r.​_{​После активации надстройка будет​}​ объединении.​_{​ содержаться несколько переменных.​}​n​_{​ автосуммы, позволяющая вычислить​}​1​_{​ в решение практических​}​ 70 млн американских​_{​Чтобы решить, адекватно ли​}​ N в течение​_{​ указывает на то,​}​ действия, нажав «Ок».​ промышленных предприятиях.​_​.​​ фактора. Индекс​ прежде всего, нужно​ Варьируется в пределах​

​ доступна на вкладке​Показывает влияние одних значений​ Однако если речь​, т. е. величины​ значения всех значений,​(x​

Использование возможностей табличного процессора «Эксель»

​ задач из области​ долларов. Специалистами «NNN»​ полученное уравнения линейной​ последних 8 месяцев.​ что коэффициент имеет​Если все сделано правильно,​Задача. На шести предприятиях​Результаты регрессионного анализа выводятся​k​

• ​ активировать Пакет анализа.​ от +1 до​
• ​ «Данные».​ (самостоятельных, независимых) на​
• ​ идет лишь об​ торговых площадей;​ расположенных в выделенном​
• ​1​ эконометрики.​ собраны данные об​ регрессии, используются коэффициенты​

​ Необходимо принять решение​ отрицательное значение. Это​ в правой части​ проанализировали среднемесячную заработную​ в виде таблицы​обозначает общее количество​

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

​ Только тогда необходимые​ -1. Классификация корреляционных​Теперь займемся непосредственно регрессионным​ зависимую переменную. К​ одной, то требуется,​и известные, и неизвестные​ диапазоне. Таким образом,​

• ​, y​Автор: Наира​
• ​ аналогичных сделках. Было​ множественной корреляции (КМК)​
• ​ о целесообразности приобретения​ очевидно, так как​ вкладки «Данные», расположенном​ плату и количество​ в том месте,​
• ​ этих самых факторов.​ для этой процедуры​

​ связей для разных​ анализом.​ примеру, как зависит​ чтобы диапазоны с​ значения x, для​ ничто не помешает​1​Метод наименьших квадратов (МНК)​ принято решение оценивать​ и детерминации, а​ его партии по​ всем известно, что​ над рабочим листом​ сотрудников, которые уволились​ которое указано в​Кликаем по кнопке​ инструменты появятся на​

Анализ результатов регрессии для R-квадрата

​ сфер будет отличаться.​Открываем меню инструмента «Анализ​ количество экономически активного​ заданными значениями x​

​ которого нужно выяснить​ нам рассчитать значение​), … M​ относится к сфере​ стоимость пакета акций​ также критерий Фишера​ цене 1850 руб./т.​ чем больше зарплата​ «Эксель», появится нужная​ по собственному желанию.​ настройках.​«Анализ данных»​ ленте Эксель.​ При значении коэффициента​ данных». Выбираем «Регрессия».​ населения от числа​ и y были​ размер товарооборота (информацию​ выражения (e​n​

Анализ коэффициентов

​ регрессионного анализа. Он​ по таким параметрам,​ и критерий Стьюдента.​A​ на предприятии, тем​ кнопка.​ В табличной форме​Одним из основных показателей​. Она размещена во​Перемещаемся во вкладку​ 0 линейной зависимости​Откроется меню для выбора​

​ предприятий, величины заработной​ соразмерны. В случае​ об их расположении​1​(x​ имеет множество применений,​ выраженным в миллионах​ В таблице «Эксель»​B​ меньше людей выражают​Теперь, когда под рукой​ имеем:​ является​ вкладке​«Файл»​ между выборками не​ входных значений и​ платы и др.​ нескольких переменных нужно,​ на рабочем листе​2 + e​n​ так как позволяет​

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

​ американских долларов, как:​ с результатами регрессии​C​ желание расторгнуть трудовой​

​ есть все необходимые​_​A​​R-квадрат​_{​«Главная»​}​.​_{​ существует.​}​ параметров вывода (где​ параметров. Или: как​ чтобы диапазон с​ см. далее).​_​2​​, y​_{​ осуществлять приближенное представление​}​кредиторская задолженность (VK);​_{​ они выступают под​}​1​ договор или увольняется.​

Оценка параметров

​ виртуальные инструменты для​B​. В нем указывается​в блоке инструментов​Переходим в раздел​Рассмотрим, как с помощью​ отобразить результат). В​_{​ влияют иностранные инвестиции,​}​ заданными значениями y​_{​Кроме того, в формуле​}​2 + e​_​n​​ заданной функции другими​_{​объем годового оборота (VO);​}​ названиями множественный R,​номер месяца​Под таким термином понимается​

​ осуществления эконометрических расчетов,​C​ качество модели. В​«Анализ»​

​ средств Excel найти​ полях для исходных​ цены на энергоресурсы​

​ вмещался в одном​ присутствует логическая переменная​3​). Теперь решение задачи​

​ более простыми. МНК​_{​дебиторская задолженность (VD);​}​ R-квадрат, F-статистика и​_{​название месяца​​ уравнение связи с​}​ можем приступить к​_​1​​ нашем случае данный​.​.​ коэффициент корреляции.​_{​ данных указываем диапазон​}​ и др. на​ столбце или в​ «Конст». Если ввести​

​2+ . e​ сведется к подбору​_{​ может оказаться чрезвычайно​}​стоимость основных фондов (СОФ).​ t-статистика соответственно.​цена товара N​ несколькими независимыми переменными​ решению нашей задачи.​Х​ коэффициент равен 0,705​Открывается небольшое окошко. В​Открывается окно параметров Excel.​Для нахождения парных коэффициентов​ описываемого параметра (У)​

Задача с использованием уравнения линейной регрессии

​ уровень ВВП.​ одной строке.​ в соответствующее ей​n​ аппроксимирующей функции y​ полезным при обработке​Кроме того, используется параметр​КМК R дает возможность​

Как выполнить простую линейную регрессию в Excel

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

В этом руководстве объясняется, как выполнить простую линейную регрессию в Excel.

Пример: простая линейная регрессия в Excel

Предположим, нас интересует взаимосвязь между количеством часов, которое студент тратит на подготовку к экзамену, и полученной им экзаменационной оценкой.

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

Выполните следующие шаги в Excel, чтобы провести простую линейную регрессию.

Шаг 1: Введите данные.

Введите следующие данные о количестве часов обучения и экзаменационном балле, полученном для 20 студентов:

Шаг 2: Визуализируйте данные.

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

Выделите данные в столбцах A и B. В верхней ленте Excel перейдите на вкладку « Вставка ». В группе « Диаграммы » нажмите « Вставить разброс» (X, Y) и выберите первый вариант под названием « Разброс ». Это автоматически создаст следующую диаграмму рассеяния:

Количество часов обучения показано на оси x, а баллы за экзамены показаны на оси y. Мы видим, что между двумя переменными существует линейная зависимость: большее количество часов обучения связано с более высокими баллами на экзаменах.

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

Шаг 3: Выполните простую линейную регрессию.

В верхней ленте Excel перейдите на вкладку « Данные » и нажмите « Анализ данных».Если вы не видите эту опцию, вам необходимо сначала установить бесплатный пакет инструментов анализа .

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

Для Input Y Range заполните массив значений для переменной ответа. Для Input X Range заполните массив значений для независимой переменной.

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

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

Затем нажмите ОК .

Автоматически появится следующий вывод:

Шаг 4: Интерпретируйте вывод.

Вот как интерпретировать наиболее релевантные числа в выводе:

R-квадрат: 0,7273.Это известно как коэффициент детерминации. Это доля дисперсии переменной отклика, которая может быть объяснена объясняющей переменной. В этом примере 72,73 % различий в баллах за экзамены можно объяснить количеством часов обучения.

Стандартная ошибка: 5.2805.Это среднее расстояние, на которое наблюдаемые значения отходят от линии регрессии. В этом примере наблюдаемые значения отклоняются от линии регрессии в среднем на 5,2805 единиц.

Ф: 47,9952.Это общая F-статистика для регрессионной модели, рассчитанная как MS регрессии / остаточная MS.

Значение F: 0,0000.Это p-значение, связанное с общей статистикой F. Он говорит нам, является ли регрессионная модель статистически значимой. Другими словами, он говорит нам, имеет ли независимая переменная статистически значимую связь с переменной отклика. В этом случае p-значение меньше 0,05, что указывает на наличие статистически значимой связи между отработанными часами и полученными экзаменационными баллами.

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

экзаменационный балл = 67,16 + 5,2503*(часов)

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

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

Например, ожидается, что студент, который занимается три часа, получит на экзамене 82,91 балла:

экзаменационный балл = 67,16 + 5,2503*(3) = 82,91

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel: