Корреляционно-регрессионный анализ в MS EXCEL

Регрессионный и корреляционный анализ – статистические методы исследования. Это наиболее распространенные способы показать зависимость какого-либо параметра от одной или нескольких независимых переменных.

Ниже на конкретных практических примерах рассмотрим эти два очень популярные в среде экономистов анализа. А также приведем пример получения результатов при их объединении.

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

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

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

Регрессия бывает:

  • линейной (у = а + bx);
  • параболической (y = a + bx + cx2);
  • экспоненциальной (y = a * exp(bx));
  • степенной (y = a*x^b);
  • гиперболической (y = b/x + a);
  • логарифмической (y = b * 1n(x) + a);
  • показательной (y = a * b^x).

Рассмотрим на примере построение регрессионной модели в Excel и интерпретацию результатов. Возьмем линейный тип регрессии.

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

Модель линейной регрессии имеет следующий вид:

У = а + а1х1 +…+акхк.

Где а – коэффициенты регрессии, х – влияющие переменные, к – число факторов.

В нашем примере в качестве У выступает показатель уволившихся работников. Влияющий фактор – заработная плата (х).

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

Активируем мощный аналитический инструмент:

  1. Нажимаем кнопку «Офис» и переходим на вкладку «Параметры Excel». «Надстройки».
  2. Внизу, под выпадающим списком, в поле «Управление» будет надпись «Надстройки Excel» (если ее нет, нажмите на флажок справа и выберите). И кнопка «Перейти». Жмем.
  3. Открывается список доступных надстроек. Выбираем «Пакет анализа» и нажимаем ОК.

После активации надстройка будет доступна на вкладке «Данные».

Теперь займемся непосредственно регрессионным анализом.

  1. Открываем меню инструмента «Анализ данных». Выбираем «Регрессия».
  2. Откроется меню для выбора входных значений и параметров вывода (где отобразить результат). В полях для исходных данных указываем диапазон описываемого параметра (У) и влияющего на него фактора (Х). Остальное можно и не заполнять.
  3. После нажатия ОК, программа отобразит расчеты на новом листе (можно выбрать интервал для отображения на текущем листе или назначить вывод в новую книгу).

В первую очередь обращаем внимание на R-квадрат и коэффициенты.

R-квадрат – коэффициент детерминации. В нашем примере – 0,755, или 75,5%. Это означает, что расчетные параметры модели на 75,5% объясняют зависимость между изучаемыми параметрами. Чем выше коэффициент детерминации, тем качественнее модель. Хорошо – выше 0,8. Плохо – меньше 0,5 (такой анализ вряд ли можно считать резонным). В нашем примере – «неплохо».

Коэффициент 64,1428 показывает, каким будет Y, если все переменные в рассматриваемой модели будут равны 0. То есть на значение анализируемого параметра влияют и другие факторы, не описанные в модели.

Коэффициент -0,16285 показывает весомость переменной Х на Y. То есть среднемесячная заработная плата в пределах данной модели влияет на количество уволившихся с весом -0,16285 (это небольшая степень влияния). Знак «-» указывает на отрицательное влияние: чем больше зарплата, тем меньше уволившихся. Что справедливо.

Корреляционный анализ в Excel

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

Если связь имеется, то влечет ли увеличение одного параметра повышение (положительная корреляция) либо уменьшение (отрицательная) другого. Корреляционный анализ помогает аналитику определиться, можно ли по величине одного показателя предсказать возможное значение другого.

Коэффициент корреляции обозначается r. Варьируется в пределах от +1 до -1. Классификация корреляционных связей для разных сфер будет отличаться. При значении коэффициента 0 линейной зависимости между выборками не существует.

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

Для нахождения парных коэффициентов применяется функция КОРРЕЛ.

Задача: Определить, есть ли взаимосвязь между временем работы токарного станка и стоимостью его обслуживания.

Ставим курсор в любую ячейку и нажимаем кнопку fx.

  1. В категории «Статистические» выбираем функцию КОРРЕЛ.
  2. Аргумент «Массив 1» – первый диапазон значений – время работы станка: А2:А14.
  3. Аргумент «Массив 2» – второй диапазон значений – стоимость ремонта: В2:В14. Жмем ОК.

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

Для корреляционного анализа нескольких параметров (более 2) удобнее применять «Анализ данных» (надстройка «Пакет анализа»). В списке нужно выбрать корреляцию и обозначить массив. Все.

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

Корреляционно-регрессионный анализ

На практике эти две методики часто применяются вместе.

Пример:

  1. Строим корреляционное поле: «Вставка» – «Диаграмма» – «Точечная диаграмма» (дает сравнивать пары). Диапазон значений – все числовые данные таблицы.
  2. Щелкаем левой кнопкой мыши по любой точке на диаграмме. Потом правой. В открывшемся меню выбираем «Добавить линию тренда».
  3. Назначаем параметры для линии. Тип – «Линейная». Внизу – «Показать уравнение на диаграмме».
  4. Жмем «Закрыть».

Теперь стали видны и данные регрессионного анализа.

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

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

  1. Перемещаемся во вкладку «Файл».
  2. Переходим в раздел «Параметры».
  3. Открывается окно параметров Excel. Переходим в подраздел «Надстройки».
  4. В самой нижней части открывшегося окна переставляем переключатель в блоке «Управление» в позицию «Надстройки Excel», если он находится в другом положении. Жмем на кнопку «Перейти».
  5. Открывается окно доступных надстроек Эксель. Ставим галочку около пункта «Пакет анализа». Жмем на кнопку «OK».

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

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

Существует несколько видов регрессий:

  • параболическая;
  • степенная;
  • логарифмическая;
  • экспоненциальная;
  • показательная;
  • гиперболическая;
  • линейная регрессия.

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

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

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

Общее уравнение регрессии линейного вида выглядит следующим образом: У = а0 + а1х1 +…+акхк. В этой формуле Y означает переменную, влияние факторов на которую мы пытаемся изучить. В нашем случае, это количество покупателей. Значение x – это различные факторы, влияющие на переменную. Параметры a являются коэффициентами регрессии. То есть, именно они определяют значимость того или иного фактора. Индекс k обозначает общее количество этих самых факторов.

  1. Кликаем по кнопке «Анализ данных». Она размещена во вкладке «Главная» в блоке инструментов «Анализ».
  2. Открывается небольшое окошко. В нём выбираем пункт «Регрессия». Жмем на кнопку «OK».
  3. Открывается окно настроек регрессии. В нём обязательными для заполнения полями являются «Входной интервал Y» и «Входной интервал X». Все остальные настройки можно оставить по умолчанию.

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

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

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

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

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

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

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

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

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

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

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

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

Содержание

Как провести регрессионный анализ данных в Excel

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

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

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

  1. Откройте документ с табличными данными и нажмите по кнопке “Файл” в верхней левой части интерфейса программы.
  2. Перейдя в окошко настроек программы нажмите по кнопке “Параметры”, что расположена в левом меню.
  3. В открывшемся окне “Параметры” нажмите по пункту “Надстройки”. Он расположен в левой части окна.
  4. В самой нижней части окна найдите и воспользуйтесь строкой “Управление”. Там, из контекстного меню выберите пункт “Надстройки Excel”.
  5. Нажмите кнопку “Перейти” для получения списка надстроек.
  6. Появится окошко с доступными надстройками Excel. Там установите галочку у пункта “Пакет анализа”. Нажмите “Ок”, чтобы эта надстройка добавилась в рабочую область программы.

Теперь во вкладке инструментов “Данные” будет доступен инструмент “Анализ данных“, что будет находится в блоке инструментов “Анализ” в правой части интерфейса.

Чтобы вы лучше могли понимать, как пользоваться данным инструментом, рассмотрим основные виды регрессий, которые представляет инструмент:

  • Параболическая;
  • Степенная;
  • Логарифмическая;
  • Экспоненциальная;
  • Показательная;
  • Гиперболическая;
  • Линейная регрессия.

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

Настройка линейного регрессионного анализа

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

Читайте также:Считаем среднее значение в Microsoft ExcelУдаляем пустые строки в ExcelНастройка автофильтра в программе MS ExcelВставляем таблицу из Word в Excel

Общее уравнение, которое будет использовано для проведения анализа выглядит следующим образом: У = а0 + а1х1 +…+акхк:

  • Где Y — это переменная, влияние внешних факторов на которую мы рассматриваем. В рассматриваемом примере это количество покупателей.
  • Где X — это фактор или факторы, оказывающие влияние на данную переменную.
  • Где A — это различные коэффициенты регрессии.
  • Индекс K обозначает общее количество факторов регрессии.

Давайте рассмотрим процесс регрессионного анализа на конкретном примере:

  1. Откройте таблицу с заполненными данными. Перейдите во вклакду “Данные”. Там воспользуйтесь инструментом “Анализ данных”, который появится после выполнения предыдущей инструкции.
  2. Выберите наиболее подходящий для вашей задачи тип анализа. В нашем случае это “Регрессия”. Нажмите по ней и затем “Ок” для перехода к настройке анализа.
  3. В появившемся окне настроек обязательными для заполнения являются два поля: “Входной интервал Y” и “Входной интервал X”. Они заполняются следующим образом:
    • “Входной интервал Y”. Сюда укажите диапазон адресов ячеек, где указаны данные, влияние на которые нам нужно изучить. В рассматриваемой таблице это будет “Количество покупателей”. Адрес нужных ячеек можно как вписать вручную, так и выделить их в самой таблице.
    • “Входной интервал X”. Сюда укажите значение данных ячеек, которые должны оказывать влияние на Y. В нашем случае это ячейки со средней дневной температурой на улице.
  4. Остальные данные оставляйте заполненными по умолчанию. Если вам нужно что-то настроить дополнительно, то конечно, можете внести свои корректировки.
  5. Завершив заполнение окна с настройками регрессионного анализа нажмите “Ок” для получения результата.

Изучение полученного результата

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

  • R-квадрат. Основный показатель, на который требуется обратить внимание. В нем вы видите качество рассматриваемой модели, то есть качество всего анализа. Значение выше 0.7 является приемлемым, следовательно, анализу можно доверять.
  • Пересечение строки “Y-пересечение” и столбца “Коэффициенты”. В этом блоке указывается значение Y, которое предположительно будет без влияния посторонних факторов. В нашем случае это количество покупателей.
  • Пересечение граф “Переменная X1” и “Коэффициенты” указывает на уровень зависимости Y от X. В рассматриваемом примере это зависимость количества посетителей от средней дневной температуры. Значение выше 1 считается высоким показателем.

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

Регрессионную модель будем строить для прогнозирования и оценке демографии в России. Статистические данные динамики численности населения в России за 20 лет по годам, в период с 1999 по 2019 год возьмём из вики.

Таблица численности населения России с 1999 г. по 2019 г.

X, год Y, млн.чел.
1999 147.5
2000 146.9
2001 146.3
2002 145.2
2003 145
2004 144.2
2005 143.5
2006 142.8
2007 142.2
2008 142
2009 141.9
2010 142.9
2011 142.9
2012 143.1
2013 143.3
2014 143.7
2015 146.3
2016 146.5
2017 146.8
2018 146.9
2019 146.8

В таблице переменная X – год, Y – численность населения в млн.Tablitsa-chislennosti-naseleniya-Rossii-s-1999-2019.pngДля того чтобы построить линию тренда и получить уравнение регрессии, переходим на вкладку Вставка, выбираем диаграммуточечная с гладкими кривыми и маркерамиDiagramma-tochechnaya-s-gladkimi-krivymi-i-markerami-Excel.pngЗатем переходим на область диаграммы и правым кликом мыши вызываем меню и выбираем выбрать данные и выбираем диапазон данных для диаграммы

В результате должен получиться следующий графикGrafik-dinamiki-chislennosti-naseleniya-Rossii-s-1999-g.-po-2019-g.pngАнализируя полученный график, можно сделать вывод, что этот период характеризуется демографической ямой. С 1999 года по 2009 год численность населения падала в России, а с 2010 по 2018 год наблюдается рост численности населения, а c 2018 по 2019 гг. опять идёт небольшой спад численности населения. Рост численности населения в России, начиная с 2010 годом возможно связан с ведением программы материнского капитала в 2007 году, а также с присоединением Крыма в 2014 году.Для получения уравнения регрессии для данной линии тренда, жмём плюс (элементы диаграммы) на области графика справа вверху -> линия тренда -> дополнительные параметры.
Здесь выбираем форму линии тренда и ниже ставим галочки — показать уравнение регрессии и показать на диаграмме величину достоверности аппроксимации, также указываем прогноз вперёд на один период, т.е. на 2020 год.uravnenie-regressii-i-znachenie-dostovernosti-approksimatsii-Excel.pngВыбираем полиномиальную линию тренда четвертой порядка (хотя выше 5 и 6 порядка, но они не всегда верно описывают модель), так как значение величины достоверности аппроксимации высокое по сравнению с линейной, экспоненциальной, логарифмической, степенной и т.д.Forma-linii-trenda-Polinomialnaya-Excel.pngУравнение регрессии:y = -0.0005x4 + 4.2586x3 — 12830x2 + 2E+07x — 9E+09Здесь, значение E означает 10 в какой-либо степени.Например,число 2E+07 эквивалентно числу 2*107=-20000000— 9E+09=-9*109=-90000000000Величина достоверности аппроксимации равна:R² = 0.9587С помощью полученного уравнения регрессии можно спрогнозировать население России на 2020 год.Prognoz-naseleniya-Rossii-na-2020-god-grafik.pngАнализируя график, можно сделать вывод что в 2020 году населения России снизится на 200-300 тыс.чел.Таким же образом можно построить линию тренда для динамики численности населения России после ВОВ, начиная с 1946 по 2019 г.

X Y
1946 97.5
1947 98.5
1948 99.2
1949 100.2
1950 102.1
1951 103
1952 104.6
1953 106.7
1954 108.4
1955 110.5
1956 112.3
1957 114
1958 115.7
1959 117.5
1960 119
1961 120.8
1962 122.4
1963 123.9
1964 125.2
1965 126.3
1966 127.2
1967 128
1968 128.7
1969 129.4
1970 130.1
1971 130.6
1972 131.3
1973 132.1
1974 132.8
1975 133.6
1976 134.6
1977 135.5
1978 136.5
1979 137.5
1980 138.1
1981 138.9
1982 139.6
1983 140.5
1984 141.6
1985 142.5
1986 143.5
1987 144.8
1988 146
1989 147.4
1990 147.7
1991 148.3
1992 148.5
1993 148.6
1994 148.4
1995 148.5
1996 148.3
1997 148
1998 147.8
1999 147.5
2000 146.9
2001 146.3
2002 145.2
2003 145
2004 144.2
2005 143.5
2006 142.8
2007 142.2
2008 142
2009 141.9
2010 142.9
2011 142.9
2012 143.1
2013 143.3
2014 143.7
2015 146.3
2016 146.5
2017 146.8
2018 146.9
2019 146.8

График динамики численности населения России после Великой Отечественной войны в период с 1946 по 2019 г.Dinamika-chislennosti-naseleniya-Rossii-1946-po-2019.png

Используемые источники:

  • https://exceltable.com/otchety/korrelyacionno-regressionnyy-analiz
  • https://lumpics.ru/regression-analysis-in-excel/
  • https://public-pc.com/rabota-s-regressionnym-analizom-v-excel/
  • https://www.matematicus.ru/excel/regressionnaya-model-v-excel