Условные средние. Линии регрессии — Студопедия

В данной статье описано, каким образом можно выполнить регрессионный анализ в программе Microsoft Excel на конкретном примере.

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

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

  1. Перемещаемся во вкладку «Файл».

Переход во вкладку Файл в Microsoft Excel

Переходим в раздел «Параметры».

Переход в параметры в программе Microsoft Excel

Открывается окно параметров Excel. Переходим в подраздел «Надстройки».

Переход в надстройки в программе Microsoft Excel

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

Перемещение в надстройки в программе Microsoft Excel

Открывается окно доступных надстроек Эксель. Ставим галочку около пункта «Пакет анализа». Жмем на кнопку «OK».

Активация пакета анализа в программе Microsoft Excel

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

Блок настроек Анализ в программе Microsoft Excel

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

Regressivnyiy-analiz-v-Microsoft-Excel.png

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

Условные средние. Линии регрессии.

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

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

Уравнение, связывающее наблюдаемые значения величины Х и условную среднюю величины Y, называется уравнением регрессии Y на Х:

.

Уравнение, связывающее наблюдаемые значения величины Y и условную среднюю величины Х, называется уравнением регрессии Х на Y:

.

Линии на координатной плоскости, соответствующие уравнениям регрессии называются линиями регрессии.

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

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

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

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

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

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

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

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

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

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

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

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

Время и стоимость.

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

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

Функция КОРРЕЛ.

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

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

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

Корреляционная матрица.

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

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

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

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

Переход в анализ данных в программе Microsoft Excel

Открывается небольшое окошко. В нём выбираем пункт «Регрессия». Жмем на кнопку «OK».

Запуск регрессии в программе Microsoft Excel

Открывается окно настроек регрессии. В нём обязательными для заполнения полями являются «Входной интервал Y» и «Входной интервал X». Все остальные настройки можно оставить по умолчанию.

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

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

Ввод интервала в настройках регрессии в программе Microsoft Excel

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

Параметры вывода в настройках регрессии в программе Microsoft Excel

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

Запуск регрессивного анализа в программе Microsoft Excel

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

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

Результат анализа регрессии в программе Microsoft Excel

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

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

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

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

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

Наши специалисты постараются ответить максимально быстро.

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

ДА НЕТ

Пример регрессионного анализа №1

А теперь настало время разобрать практические кейсы, как можно использовать линейную регрессию. Допустим, у нас есть набор данных о расходах на ТВ-рекламу, интернет-продвижение и о том, сколько получилось реализовать товара в российской национальной валюте. Все эти данные упакованы в таблицу. Перед нами стоит задача – определить коэффициенты регрессии для независимых переменных (то есть, в нашем случае ими выступают расходы на рекламу по ТВ и в интернете, поскольку оба значения влияют на объем реализуемых товаров). Последовательность действий такая:

  1. Открыть рабочий лист и ввести данные.
  2. Активировать инструмент регрессия способом, описанным выше.
  3. В появившемся диалоговом окне необходимо задать входной интервал X, Y,  задать метки
  4. Также не стоит забывать ввести выходной интервал. Для выполнения этой задачи необходимо также указать такие параметры, как «График нормальной вероятности» и «График остатков».

Видим, что для этого кейса нам не нужно принципиально отходить от схемы, описанной выше. Линейная регрессия в этом случае позволяет уменьшить расходы на рекламу и увеличить отдачу от неё. То есть, выражаясь маркетинговым языком, увеличить ROMI – коэффициент возвратности инвестиций на маркетинг.

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...