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

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

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

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

Регрессия возникает:

• линейный (y = a + 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 предприятиях были проанализированы среднемесячная заработная плата и количество уволившихся сотрудников. Необходимо определить зависимость количества вышедших на пенсию работников от средней заработной платы.

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

Y = а0 + а1х1 +… + ахк.

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

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

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

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

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

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

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

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

Прежде всего, мы рассматриваем R-квадрат и коэффициенты.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

На практике эти два метода часто используются вместе.

Пример:

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

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

Читайте также: Решение транспортной задачи в Excel с примером и описанием

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

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

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

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

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

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

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

Более подробно о реализации последнего типа регрессионного анализа в Excel мы поговорим позже.

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

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

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

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

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

   

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

   

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

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

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

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

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

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

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

Задачи регрессионного анализа

Для проверки гипотезы о линейной зависимости между переменными Y и X берется выборка из генеральной совокупности (эта совокупность соответствует линии регрессии генеральной совокупности, т.е. µy=a*X+β). Выборка будет состоять из n точек, т.е из n пар значений {X;Y}.

На основе этой выборки мы можем вычислить оценки наклона a и сдвига β, которые мы обозначаем как a и b соответственно. Также часто используются обозначения â и b̂.

Используя эти оценки, мы также можем проверить гипотезу: существует ли линейная зависимость между X и Y, которая является статистически значимой?

Таким образом:

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

Вторая задача регрессионного анализа — проверка адекватности модели).

Примечание. Оценки параметров модели обычно рассчитываются методом наименьших квадратов (МНК), который является предметом отдельной статьи .

Оценка неизвестных параметров линейной модели (используя функции MS EXCEL)

Мы оцениваем неизвестные параметры простой модели линейной регрессии Y=a*X+β+ε с помощью метода наименьших квадратов (этот метод подробно описан в статье о LSM).

Для расчета параметров линейной модели методом наименьших квадратов получается следующее выражение:

Таким образом, мы получаем уравнение прямой линии Y= a *X+ b , которое наилучшим образом аппроксимирует имеющиеся данные.

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

Оценка параметров в MS EXCEL может производиться разными способами:

• используя функции SLOPE() и INTERCEPT() ;
• с помощью функции ЛИНЕЙН(); см статью Функция MS EXCEL LINEST
• формулы через выборочную статистику ;
• в матричной форме ;
• с помощью инструмента регрессии для надстройки пакета анализа .

Во-первых, давайте рассмотрим функции НАКЛОН(), ОТРЕЗОК() и ЛИНЕЙН() .

Оставьте значения X и Y в C 23:C 83 и B 23:B 83 соответственно (см файл примера внизу статьи).

Примечание. Значения двух переменных X и Y можно сгенерировать, задав тренд и величину случайной дисперсии (см статью Генерация данных для линейной регрессии в MS EXCEL).

В MS EXCEL наклон прямой а (оценка коэффициента регрессии) может быть найден методом наименьших квадратов с помощью функции НАКЛОН(), а сдвиг b (оценка постоянного члена или константы регрессии) с помощью функции ОТРЕЗОК () функция. В английской версии это соответственно функции SLOPE и INTERCEPT.

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

Формула =ЛИНЕЙН(C23:C83;B23:B83) вернет наклон a . И формула = ИНДЕКС(СТРОКА(C23:C83;B23:B83);2) — сдвиг b . Здесь необходимо пояснение.

Функция ЛИНЕЙН() имеет 4 аргумента и возвращает массив значений:

LINE(известные_значения_y; известные_значения_x, константа, статистика)

Если четвертый статистический аргумент имеет значение ЛОЖЬ или опущен, ЛИНЕЙН() возвращает только оценки параметров модели a и b .

Примечание. Остальные значения, возвращаемые функцией ЛИНЕЙН(), понадобятся при вычислении стандартных ошибок и для проверки значимости регрессии. В этом случае аргумент статистики должен быть TRUE.

Как показать обе оценки одновременно:

• 2 ячейки должны быть выбраны в одной строке,
• введите формулу в строку формул
• нажмите CTRL + SHIFT + ENTER (см статью о формулах массива).

Если мы выберем формулу = LINE(C23:C83;B23:B83) в строке формул и нажмем клавишу F9, мы увидим что-то вроде {3.01279389265416;154.240057900613}. Это просто значения a и b. Как видите, оба значения разделены точкой с запятой «;», что говорит о том, что функция вернула значения «в нескольких ячейках одной строки».

Если вы хотите отображать параметры линии не в одну строку, а в один столбец (ячейки друг под другом), используйте формулу = ТРАНСП(ЛИНЕЙНАЯ(C23:C83;B23:B83)) . В этом случае вам нужно выделить 2 ячейки в столбце. Если мы теперь выберем новую формулу и нажмем клавишу F9, мы увидим, что 2 значения разделены двоеточием «:», что означает, что значения отображаются в столбце (функция ТРАНСП() переставлена строку в столбец).

Чтобы разобраться в этом подробнее, необходимо ознакомиться с матричными формулами .

Вместо того, чтобы возиться с вводом формул массива, вы можете использовать функцию ИНДЕКС(). Формула = ИНДЕКС(ЛИНЕЙН(C23:C83;B23:B83);1) или просто ЛИНЕЙН(C23:C83;B23:B83) вернет параметр, отвечающий за наклон линии, т.е. A . Формула =ИНДЕКС(ЛИНЕЙН(C23:C83;B23:B83);2) вернет параметр b .

Оценка неизвестных параметров линейной модели (через статистики выборок)

Наклон линии, т е коэффициент а, также можно рассчитать через коэффициент корреляции и стандартное отклонение выборки :

= КОРРЕЛ(B23:B83;C23:C83) *(СТАНДОТКЛОН.B(C23:C83)/ СТАНДОТКЛОН.B(B23:B83))

Приведенная выше формула математически эквивалентна отношению ковариации выборок X и Y к дисперсии выборки X:

= КОВАРИАЦИЯ.B(B23:B83;C23:C83)/ДИСПЕРСИЯ.B(B23:B83)

И, наконец, напишем еще одну формулу для нахождения сдвига b. Воспользуемся тем, что линия регрессии проходит через точку средних значений переменных X и Y.

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

Оценка неизвестных параметров линейной модели (матричная форма)

Параметры линии регрессии также можно найти в матричной форме (см файл примера Матричная форма).

В формуле символ β указывает на столбец с искомыми параметрами модели: β0 (сдвиг b), β1 (наклон a).

Матрица X равна:

Матрица X называется матрицей регрессии или матрицей дизайна. Он состоит из 2 столбцов и n строк, где n — количество точек данных. Первый столбец — столбец единиц измерения, второй — значения переменной X.

Матрица XT является транспонированной матрицей X . Он состоит из n столбцов и 2 строк соответственно.

В формуле символ Y обозначает столбец значений переменной Y.

Чтобы перемножить матрицы, используйте функцию MULT(). Чтобы найти обратную матрицу, используйте функцию MIN() .

Пусть задан диапазон значений переменных X и Y (n=10, т.е. 10 баллов).

Слева от него дополняем столбец цифрой 1 для матрицы X.

Записав формулу

= MMUT(INV(MMUT(TRANSPOSER(B7:C16);(B7:C16))), MMUT(TRANSPO(B7:C16);(D7:D16)))

и введя его в виде матричной формулы в 2 ячейки, получим оценку параметров модели.

Красота использования матричной формы полностью раскрывается в случае множественной регрессии .

Построение линии регрессии

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

Чтобы нарисовать прямую линию, используйте приведенные выше оценки для параметров модели a и b (т.е вычислите y по формуле y = a * x + b) или функцию TREND() .

Формула = TREND($C$23:$C$83;$B$23:$B$83;B23) возвращает рассчитанные (прогнозные) значения ŷi для заданного значения Xi из столбца B2 .

Примечание. Линия регрессии также может быть построена с помощью функции ПРОГНОЗ(). Эта функция возвращает прогностические значения ŷi, но в отличие от функции TREND() она работает только в случае одного регрессора. Функцию TREND() можно использовать и в случае множественной регрессии (в этом случае третий аргумент функции должен быть ссылкой на массив, содержащий все значения Xi для выбранного случая i).

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

Линия регрессии также может быть построена с помощью встроенных инструментов построения графиков, т.е с помощью инструмента «Линия тренда». Для этого выберите график, выберите в меню вкладку «Макет», нажмите «Линия тренда» в группе «Анализ», затем «Линейный подход». В диалоговом окне установите флажок Показать уравнение на диаграмме (подробнее см в статье OLS).

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

Примечание: Для того, чтобы расчетные параметры уравнения a и b совпадали с параметрами уравнения на графике, необходимо, чтобы тип графика был Точечным, а не График, т.к тип графика График не использует значения X, но вместо этого для значений X используется последовательность 1; 2; 3; . Именно эти значения берутся при расчете параметров линии тренда. Убедиться в этом можно, если построить график Graph (см файл примера), а значения Xstart и Xstep установить равными 1. Только в этом случае параметры уравнения на графике будут соответствовать a и b .

Коэффициент детерминации R 2

Коэффициент детерминации R 2 показывает, насколько полезна построенная нами модель линейной регрессии .

Предположим, у нас есть n значений переменной Y и мы хотим предсказать значение yi, но без использования значений переменной X (т.е без построения регрессионной модели). Наилучшей оценкой yi является, очевидно, среднее значение ȳ. Следовательно, ошибка предсказания будет равна (yi — ȳ).

Примечание. В дальнейшем будут использоваться терминология и обозначения дисперсионного анализа .

После построения регрессионной модели для прогнозирования значения yi мы будем использовать значение ŷi=a*xi+b. Ошибка предсказания теперь будет (yi — ŷi).

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

Используя регрессионную модель, мы заведомо уменьшили начальную (суммарную) ошибку (yi — ȳ) на величину (ŷi — ȳ) до величины (yi — ŷi).

(yi — ŷi) — остаточная необъяснимая ошибка.

Ясно, что все три ошибки связаны с выражением:

(yi — ȳ) = (ŷi — ȳ) + (yi — ȳi)

Можно показать, что в общем случае верно следующее выражение:

Доказательство:

или в других обозначениях, общепринятых в зарубежной литературе:

SST = SSR + SSE

Что это значит:

Общая сумма квадратов = сумма квадратов регрессии + сумма квадратов ошибок

Примечание: SS — Сумма квадратов — Сумма квадратов.

Как видно из формулы, значения SST, SSR, SSE имеют размерность дисперсии (вариации) и, следовательно, описывают разброс (изменчивость): Суммарная вариация (Общая вариация), Изменчивость, объясняемая моделью (Объясняемая вариация) и необъяснимое изменение (необъяснимое изменение).

По определению коэффициент детерминации R 2 равен:

R 2 = Объясняемая моделью вариация / Общая вариация.

Этот показатель равен квадрату коэффициента корреляции, и в MS EXCEL его можно рассчитать с помощью функции QVPIRSON() или LINEST() :

= ИНДЕКС(СТРОКА(C23:C83,B23:B83,,ИСТИНА),3)

R 2 принимает значения от 0 до 1 (1 соответствует идеальной линейной зависимости Y от X). Однако на практике малые значения R2 не обязательно означают, что переменную X нельзя использовать для предсказания переменной Y. Небольшие значения R2 могут указывать на нелинейную зависимость или на то, что поведение переменной Y не только объясняется X, но и другими факторами.

Стандартная ошибка регрессии

Стандартная ошибка регрессии показывает, насколько велика ошибка предсказания значений переменной Y на основе значений X. Мы можем предсказать только отдельные значения Yi с точностью +/- несколько значений (обычно 2 -3, в зависимости от формы распределения ошибок ε).

Теперь вспомним уравнение для модели линейной регрессии Y=a*X+β+ε. Ошибка ε имеет случайный характер, т.е является случайной величиной и поэтому имеет свою функцию распределения со средним значением µ и дисперсией σ 2 .

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

Примечание: Помните, что при построении модели предполагается, что средняя ошибка ε равна 0, т.е. Eε=0.

Оценим разброс σ 2 . Помимо вычисления стандартной ошибки регрессии, эта оценка понадобится нам в дальнейшем также при построении доверительных интервалов для оценки параметров регрессии a и b .

Для оценки дисперсии ошибки ε мы используем остатки регрессии — разницу между имеющимися значениями yi и значениями, предсказанными моделью регрессии ŷ. Чем лучше регрессионная модель соответствует данным (точки близки к прямой), тем меньше будут невязки.

Для оценки разброса σ 2 используют следующую формулу:

где SSE — сумма квадратов значений ошибок для модели ε i =yi — ŷi (Сумма квадратов ошибок).

SSE также часто упоминается как SSres — сумма квадратов остатков (Sum of Squared Residents).

Оценка дисперсии s 2 также имеет общепринятое обозначение MSE (Mean Square of Errors), т.е среднее квадратов ошибок или MSRES (Mean Square of Residuals), т.е среднее квадратов невязок. Хотя правильнее говорить сумма квадратов невязок, поскольку ошибка чаще связана с ошибкой модели ε, которая является непрерывной случайной величиной. Однако здесь мы будем использовать термины SSE и MSE, предполагая, что мы говорим об остатках.

Примечание. Напомним, что когда мы использовали LSM для поиска параметров модели, критерием оптимизации была минимизация SSE (SSres). Это выражение представляет собой сумму квадратов расстояний между наблюдаемыми значениями yi и предсказанными моделью значениями ŷi, которые лежат на линии регрессии.

Математическое ожидание случайной величины MSE равно дисперсии ошибки ε, т е. σ2 .

Чтобы понять, почему SSE выбран в качестве основы для оценки дисперсии ошибки ε, вспомним, что σ 2 также является дисперсией случайной величины Y (относительно среднего значения µy при заданном значении Xi). А т.к оценкой µy является значение ŷi = a * Хi + b (значение уравнения регрессии при Х= Хi), то логично использовать SSE в качестве основы для оценки дисперсии σ 2 . Затем SSE усредняется по количеству точек данных n минус число 2. Значение n-2 — это число степеней свободы (df — степени свободы), т е количество параметров системы, которые могут изменяться независимо (помните, что в этом примере у нас есть n независимых наблюдений переменной Y). В простой линейной регрессии число степеней свободы равно n-2, так как при построении линии регрессии оценивались 2 параметра модели (для этого «использовались» 2 степени свободы).

Итак, как упоминалось выше, квадратный корень из s 2 имеет специальное название Standard Error для регрессии и обозначается SEy. SEy показывает, насколько велика ошибка предсказания. Мы можем предсказать отдельные значения Y с +/- несколькими значениями SEy (см этот раздел). При нормальном распределении ошибок прогноза ε примерно 2/3 всех прогнозируемых значений будут находиться на расстоянии не более чем SEy от линии регрессии. SEy имеет размерность переменной Y и нанесен вертикально. Часто на диаграмме рассеивания отображаются пределы предсказания, соответствующие +/- 2 SEy (т е. 95% точек данных будут находиться в этих пределах).

В MS EXCEL стандартную ошибку SEy можно рассчитать напрямую по формуле:

= КОРЕНЬ(СУММРАЗН(C23:C83; ТРЕНД(C23:C83;B23:B83;B23:B83)) /(СЧЁТ(B23:B83) -2))

или с помощью функции ЛИНЕЙН() :

= ИНДЕКС(СТРОКА(C23:C83,B23:B83,,ИСТИНА),3,2)

Примечание. Дополнительные сведения о функции ЛИНЕЙН() см в этой статье .

Стандартные ошибки и доверительные интервалы для наклона и сдвига

В разделе Оценка неизвестных параметров для линейной модели мы получили точечные оценки наклона a и сдвига b. Поскольку эти оценки получены на основе случайных величин (значений переменных X и Y), эти оценки сами являются случайными величинами и, следовательно, имеют функцию распределения со средним значением и дисперсией. Но чтобы перейти от точечных оценок к интервальным оценкам, необходимо вычислить соответствующую стандартную ошибку (т.е стандартное отклонение).

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

где Sx — стандартное отклонение x, рассчитанное по формуле:

где Sey — стандартная ошибка регрессии, т е ошибка предсказания значения переменной Y (см выше).

В MS EXCEL стандартную ошибку коэффициента регрессии Se можно рассчитать непосредственно по приведенной выше формуле:

= КОРЕНЬ(СУММQРАЗН(C23:C83, ТРЕНД(C23:C83,B23:B83,B23:B83)) /(СЧЁТ(B23:B83) -2))/ СТАНДОТКЛОН.B(B23:B83) /КВАДРАТ(СЧЁТ(Б23:Б83)-1)

или с помощью функции ЛИНЕЙН() :

= ИНДЕКС(СТРОКА(C23:C83,B23:B83,,ИСТИНА),2,1)

Формулы приведены в файле примера на линейном листе в разделе Статистика регрессии .

Примечание. Дополнительные сведения о функции ЛИНЕЙН() см в этой статье .

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

где – квантиль распределения Стьюдента с n-2 степенями свободы. Значение а с «крышкой» — это еще один термин для наклона а .

Для уровня значимости альфа = 0,05 вы можете, например, рассчитать по формуле = СТУДЕНТ.INR.2X(0,05; n-2)

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

есть t-распределение Стьюдента с n-2 степенями свободы (то же относится и к наклону b).

Примечание. Подробнее о построении доверительных интервалов в MS EXCEL можно прочитать в этой статье Доверительные интервалы в MS EXCEL .

В результате получаем, что найденный доверительный интервал с вероятностью 95% (1-0,05) покроет истинное значение коэффициента регрессии. Здесь мы предполагаем, что коэффициент регрессии a имеет распределение Стьюдента с n-2 степенями свободы (n — количество наблюдений, т е пар X и Y).

Примечание. Дополнительные сведения о построении доверительных интервалов с использованием t-распределения см в статье о построении средних доверительных интервалов .

Стандартная ошибка сдвига b рассчитывается по следующей формуле:

В MS EXCEL стандартную ошибку смещения Seb можно рассчитать с помощью функции ЛИНЕЙН() :

= ИНДЕКС(СТРОКА(C23:C83,B23:B83,,ИСТИНА),2,2)

При построении двустороннего доверительного интервала для сдвига границы определяются так же, как и для наклона: b +/- t*Seb.

Проверка значимости взаимосвязи переменных

Когда мы строим модель Y=αX+β+ε, мы предполагаем, что существует линейная зависимость между Y и X. Однако, как это иногда бывает в статистике, можно рассчитать параметры зависимости даже тогда, когда в действительности его не существует, и он обусловлен только случайностью.

Единственный вариант, когда Y не зависит от X (в рамках модели Y=αX+β+ε), возможен, когда коэффициент регрессии а равен 0.

Чтобы быть уверенным, что рассчитанная нами оценка наклона прямой не случайна (она не отлична от 0 случайным образом), используем проверку гипотез. В качестве нулевой гипотезы H 0 предполагается, что связи нет, т.е a=0. В качестве альтернативной гипотезы H 1 принимает, что a<>0.

На рисунках ниже показаны 2 ситуации, когда нулевая гипотеза H 0 не может быть отвергнута.

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

Ниже приведены 2 ситуации, когда нулевая гипотеза H 0 отвергается.

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

Для проверки гипотезы нам понадобится:

• Установите уровень значимости, пусть альфа=0,05;
• Используйте функцию ЛИНЕЙН() для вычисления стандартного отклонения См коэффициент регрессии (см предыдущий раздел);
• Рассчитайте количество степеней свободы: DF=n-2 или используйте формулу = ИНДЕКС(ЛИНЕЙНАЯ(C24:C84;B24:B84;;ИСТИНА);4;2)
• Вычислить значение тестовой статистики t 0 =a/S e , имеющей распределение Стьюдента с числом степеней свободы DF=n-2;
• Сравните значение тестовой статистики |t0| с пороговым значением t alpha ,n-2. Если значение тестовой статистики больше порогового значения, нулевая гипотеза отклоняется (наклон не может быть объяснен чистой случайностью на данном уровне альфа), или
• рассчитать p-значение и сравнить его с уровнем значимости .

Файл примера содержит пример проверки гипотезы:

Изменяя наклон тренда k (ячейка B8), можно увидеть, что при малых углах тренда (например, 0,05) тест часто показывает, что связь между переменными носит случайный характер. При больших углах (k>1) тест почти всегда подтверждает значимость линейной зависимости между переменными.

Примечание. Проверка значимости связи эквивалентна проверке статистической значимости коэффициента корреляции. Файл примера показывает эквивалентность обоих подходов. Проверка значимости также может быть проведена с использованием процедуры F-теста .

Доверительные интервалы для нового наблюдения Y и среднего значения

После расчета параметров простой модели линейной регрессии Y=aX+β+ε мы получили точечную оценку значения нового наблюдения Y для заданного значения Хi, а именно: Ŷ= a * Хi + b

Ŷ также является точечной оценкой среднего значения Yi при заданном Xi. Однако при построении доверительных интервалов используются разные стандартные ошибки .

Стандартная ошибка нового наблюдения Y для данного Xi учитывает два источника неопределенности:

• неопределенность, связанная со случайными оценками параметров модели a и b ;
• случайность ошибки модели ε.

учет этих неопределенностей приводит к стандартной ошибке S(Y|Xi), которая рассчитывается с учетом известного значения Xi.

где SS xx — сумма квадратов отклонений от среднего значения переменной X:

Примечание. Se — стандартная ошибка коэффициента регрессии (наклон a).

В MS EXCEL 2010 нет функции, которая будет вычислять эту стандартную ошибку, поэтому ее необходимо вычислять по приведенным выше формулам.

Доверительный интервал или интервал прогноза для нового наблюдения будет построен по форме, показанной в разделе Проверка значимости связи между переменными (см пример архивного листа Интервалы). Поскольку пределы интервала зависят от значения Xi (точнее, от расстояния Xi до среднего значения X cf), интервал будет постепенно расширяться по мере удаления от X cf .

Границы доверительного интервала для нового наблюдения рассчитываются по формуле:

Таким же образом строим доверительный интервал для среднего значения Y при заданном Хi (Доверительный интервал для среднего значения Y). В этом случае доверительный интервал будет уже, потому что средние значения имеют меньшую вариацию по сравнению с отдельными наблюдениями (средние значения в рамках нашей линейной модели Y=aX+β+ε не включают ошибку ε).

Стандартная ошибка S(Yav|Xi) вычисляется почти по тем же формулам, что и стандартная ошибка нового наблюдения:

Как видно из формул, стандартная ошибка S(Yav|Xi) меньше стандартной ошибки S(Y|Xi) для отдельного значения .

Границы доверительного интервала для среднего значения рассчитываются по формуле:

Проверка адекватности линейной регрессионной модели

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

Валидация модели в основном основана на изучении невязок модели, т.е значений ei=yi – ŷi для каждого Хi. В рамках простой линейной модели n остатков имеют только n-2 степени свободы, связанные с ними. Поэтому, даже если невязки не являются независимыми величинами, но при достаточно большом n, это не влияет на проверку адекватности модели.

Для проверки предположения о нормальности распределения ошибок строится график нормальной вероятности).

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

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

Также при проверке модели на адекватность часто строят зависимость остатков от предсказанных значений Y. Если точки не показывают характерных, так называемых «паттернов» (шаблонов) типа воронок или других неравномерностей распределения в зависимости от значения Y, то у нас нет очевидных доказательств того, что модель неадекватна.

В нашем случае точки распределены примерно равномерно.

Часто при проверке адекватности модели вместо остатков используются нормированные невязки. Как показано в разделе «Стандартная ошибка регрессии», оценка стандартного отклонения ошибок SEy равна квадратному корню из MSE. Поэтому логично производить нормировку остатков именно на это значение.

SEy можно рассчитать с помощью функции ЛИНЕЙН() :

= ИНДЕКС(СТРОКА(C23:C83,B23:B83,,ИСТИНА),3,2)

Иногда остатки нормируют значением стандартного отклонения остатков (это мы увидим в статье об инструменте регрессии, доступном в дополнительном пакете анализа MS EXCEL), т.е по формуле:

Приведенное выше равенство является приблизительным, потому что среднее значение остатков близко, но не обязательно точно к 0.

Как интерпретировать результаты анализа

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

Наиболее важным значением, на котором мы сосредоточимся, является пространство R. Он фиксирует качество используемой модели. Чем выше, тем выше. Если он меньше 0,5, зависимость считается плохой, если выше – уже лучше. Чем ближе к 1, тем лучше. Следовательно, максимальный коэффициент равен 1.

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

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

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

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

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

Мы видим, что для этого нам не нужно отступать от описанного выше принципа. Линейная регрессия в этом случае позволяет снизить затраты на рекламу и увеличить отдачу от нее. На языке маркетинга это означает увеличение ROMI — коэффициента возврата инвестиций в маркетинг.

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

Второй случай, когда можно провести регрессионный анализ, — это необходимость найти максимальную модель распределения затрат на разные виды рекламы для получения наибольшей прибыли. А такую ​​маркетинговую задачу вполне можно было бы решить с помощью обычного Excel, кто бы мог подумать?

Предположим, что максимальный рекламный бюджет, который может потратить организация, составляет 170 000 руб. Это ограничение не может быть обеспечено стандартными методами, описанными выше. Здесь вам придется использовать совсем другую надстройку под названием «Поиск решения». Есть возможность найти его в том же разделе, что и описанный нами. И так же, как пакет анализа, нам нужно активировать это дополнение в том же меню.

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

Как и в случае с пакетом анализа, инструмент поиска решения требует наличия математической модели. Он работает как целевая функция. В нашем случае это так: Y = 2102438,6 + 6,4004 X1 — 54,068 X2 > макс. В качестве ограничений используются следующие выражения: X1 + X2 <= 170000, X1>= 0, X2 >=0.

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

1. Для начала нажмите «Параметры Excel», затем перейдите на вкладку «Надстройки».
2. После этого в поле «Управление» найдите «Надстройки Excel» и нажмите «Перейти».
3. После этого в списке дополнений активируйте «Поиск решения».

После нажатия OK надстройка активируется. Затем просто нажмите соответствующую кнопку на вкладке «Данные» в той же группе, что и пакет анализа, и задайте соответствующие параметры. После этого программа все сделает сама. Таким образом, использование регрессии в Excel — очень простая вещь. Гораздо проще, чем может показаться на первый взгляд, так как большинство действий выполняет программа. Вероятно, это просто вопрос вождения в правильных настройках, а затем вы можете расслабиться. И да, вам все еще нужно правильно интерпретировать результаты. Но это не проблема. Удачи.