Как провести корреляционно-регрессионный анализ в Excel

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

Подключение надстройки «Пакет анализа»

По умолчанию осуществить регрессионный анализ в программе Excel нельзя. Для вычислений в данном методе используется очень сложное уравнение регрессии, которое крайне проблематично реализовать в виде обычной формулы. Чтобы использовать этот метод, необходимо подключить вспомогательный набор функций — «Пакет анализа». С решением данной задачи ознакомимся при помощи такой инструкции:

1. В рабочем окне программы нажимаем на вкладку «Файл». Затем в выпадающем окне выбираем пункт «Параметры».
   

   Переходим в параметры
2. В открывшемся разделе параметров в меню слева находим «Надстройки». Далее в нижней части окна устанавливаем для строки «Управление» вариант «Надстройки Excel» и нажимаем Перейти.
   

   Нажимаем перейти в параметрах надстроек
3. На экране после этого отобразится перечень доступных надстроек. Ставим отметку напротив пункта «Пакет анализа» и подтверждаем его подключение нажатием OK.
   

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

Убедиться в том, что надстройка «Анализ Данных» подключена, довольно нетрудно. Достаточно перейти во вкладку «Данные». Этот набор инструментов будет доступен через новую кнопку в Панели инструментов справа.

Анализ данных во вкладке данные

Суть регрессионного анализа

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

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

Рассматриваем регрессионный анализ на примере

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

1. Переходим во вкладку «Данные» и в Панели инструментов нажимаем «Анализ данных». В появившемся окне в списке функций нужно найти «Регрессия» и подтвердить ее выбор нажатием OK.
   

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

   Вводим аргументы

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

Выполнив построение, необходимо правильно интерпретировать результаты. В первую очередь следует оценить параметр «Множественный R». Он определяет общее качество полученной модели. Чем оно выше — тем лучше.

Еще один важный параметр — Y-пересечение. Он показывает прогнозируемое значение зависимой величины в том случае, если факторная переменная будет равняться нулю. Также важным показателем для интерпретации регрессии в Excel является значимость F. Он отражает насколько сильно факторная переменная влияет на зависимую величину. Чем ниже это значение, тем более выраженное влияние. В более сложных вариантах пользователь может самостоятельно определять коэффициент регрессии, исходя из специфики задействованного для оценки набора данных.

Вывод итогов

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

Парный график

Подводим итоги

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

Видео по теме