Как выполнить простую линейную регрессию в 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:
Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Как рассчитать регрессию в Excel
Опубликовано 14.01.2014 Автор Ренат Лотфуллин
Метод линейной регрессии позволяет нам описывать прямую линию, максимально соответствующую ряду упорядоченных пар (x, y). Уравнение для прямой линии, известное как линейное уравнение, представлено ниже:
ŷ — ожидаемое значение у при заданном значении х,
x — независимая переменная,
a — отрезок на оси y для прямой линии,
b — наклон прямой линии.
На рисунке ниже это понятие представлено графически:
На рисунке выше показана линия, описанная уравнением ŷ =2+0.5х. Отрезок на оси у — это точка пересечения линией оси у; в нашем случае а = 2. Наклон линии, b, отношение подъема линии к длине линии, имеет значение 0.5. Положительный наклон означает, что линия поднимается слева направо. Если b = 0, линия горизонтальна, а это значит, что между зависимой и независимой переменными нет никакой связи. Иными словами, изменение значения x не влияет на значение y.
Часто путают ŷ и у. На графике показаны 6 упорядоченных пар точек и линия, в соответствии с данным уравнением
На этом рисунке показана точка, соответствующая упорядоченной паре х = 2 и у = 4. Обратите внимание, что ожидаемое значение у в соответствии с линией при х = 2 является ŷ. Мы можем подтвердить это с помощью следующего уравнения:
ŷ = 2 + 0.5х =2 +0.5(2) =3.
Значение у представляет собой фактическую точку, а значение ŷ — это ожидаемое значение у с использованием линейного уравнения при заданном значении х.
Следующий шаг — определить линейное уравнение, максимально соответствующее набору упорядоченных пар, об этом мы говорили в предыдущей статье, где определяли вид уравнения по методу наименьших квадратов.
Использование Excel для определения линейной регрессии
Для того, чтобы воспользоваться инструментом регрессионного анализа встроенного в Excel, необходимо активировать надстройку Пакет анализа. Найти ее можно, перейдя по вкладке Файл –> Параметры (2007+), в появившемся диалоговом окне Параметры Excel переходим во вкладку Надстройки. В поле Управление выбираем Надстройки Excel и щелкаем Перейти. В появившемся окне ставим галочку напротив Пакет анализа, жмем ОК.
Во вкладке Данные в группе Анализ появится новая кнопка Анализ данных.
Чтобы продемонстрировать работу надстройки, воспользуемся данными с предыдущей статьи, где парень и девушка делят столик в ванной. Введите данные нашего примера с ванной в столбцы А и В чистого листа.
Перейдите во вкладку Данные, в группе Анализ щелкните Анализ данных. В появившемся окне Анализ данных выберите Регрессия, как показано на рисунке, и щелкните ОК.
Установите необходимыe параметры регрессии в окне Регрессия, как показано на рисунке:
Щелкните ОК. На рисунке ниже показаны полученные результаты:
Эти результаты соответствуют тем, которые мы получили путем самостоятельных вычислений в предыдущей статье.
Вам также могут быть интересны следующие статьи
- Как построить график с нормальным распределением в Excel
- Что такое стандартное отклонение — использование функции СТАНДОТКЛОН для расчета стандартного отклонения в Excel
- Как расчитать дисперсию в Excel с помощью функции ДИСП.В
- Метод наименьших квадратов в Excel — использование функции ТЕНДЕНЦИЯ
- Как рассчитать коэффициент корреляции в Excel
Как построить график функции регрессии в Excel
График функции регрессии – одно из наиболее эффективных средств визуализации и анализа данных. Он позволяет исследовать зависимость между двумя или более переменными и предсказывать значения одной переменной на основе другой. Microsoft Excel – известная и широко используемая программа для работы с таблицами и данными, включая построение графиков.
В данной статье рассмотрим простой пример построения графика функции регрессии в Excel и дадим пошаговую инструкцию для его создания. Наш пример будет основан на известном наборе данных Iris, который содержит информацию о различных видов ирисов и их характеристиках. Мы построим график зависимости длины лепестка от ширины лепестка для одного из видов ирисов.
Примечание: Перед построением графика функции регрессии необходимо убедиться, что данные соответствуют заданным требованиям. В нашем примере, перед использованием данных Iris, мы провели предварительный анализ и выяснили, что они удовлетворяют требованиям для построения графика регрессии.
Что такое график функции регрессии и как его построить в Excel?
Для построения графика функции регрессии в Excel необходимо выполнить следующие шаги:
- Откройте Excel и введите данные в таблицу, где один столбец будет содержать значения независимой переменной (x), а другой — значения зависимой переменной (y).
- Выделите область с данными и выберите вкладку «Вставка» в верхней панели инструментов.
- Выберите тип графика, который соответствует нужному типу функции регрессии. Например, для линейной регрессии выберите Scatterplot (диаграмма рассеяния).
- На графике щелкните правой кнопкой мыши и выберите «Добавить трендовую линию» или «Добавить линию регрессии».
- Выберите тип функции регрессии (например, линейную) и установите галочку напротив опции «Отобразить уравнение на графике».
- Нажмите на кнопку «OK» и Excel автоматически построит график функции регрессии на основе введенных данных.
График функции регрессии позволяет визуализировать зависимость между переменными и определить характер связи. Он также может быть использован для предсказания значений зависимой переменной на основе значений независимой переменной.
Примечание: Построение графика функции регрессии требует наличия достаточного количества данных для точного представления зависимостей. Также необходимо помнить, что график функции регрессии является математической моделью и может быть использован только в пределах допущений и ограничений этой модели.
Примеры графика функции регрессии на практике
Графики функции регрессии используются в различных областях, включая экономику, финансы, медицину и маркетинг. Вот несколько примеров, которые помогут вам лучше понять, как функция регрессии может быть полезна в реальной жизни.
Пример 1: Прогнозирование продаж
Предположим, у вас есть данные о продажах определенного продукта на протяжении последних нескольких лет. Вы можете использовать функцию регрессии, чтобы выявить зависимость между объемом продаж и другими факторами, такими как цена, рекламный бюджет и погодные условия. На основе этой зависимости вы сможете создать прогнозный график, который поможет вам определить будущие продажи и принять более обоснованные бизнес-решения.
Пример 2: Исследование заболеваемости
Предположим, вы проводите исследование, чтобы выяснить, какие факторы влияют на заболеваемость определенным заболеванием. С помощью функции регрессии вы можете анализировать связь между заболеваемостью и такими факторами, как возраст, пол, уровень физической активности и наличие хронических заболеваний. График функции регрессии позволит вам наглядно увидеть, какие факторы оказывают наибольшее влияние на заболеваемость и выделить группы риска.
Пример 3: Прогнозирование финансовых показателей
Если вы руководитель финансового отдела или аналитик, функция регрессии может помочь вам прогнозировать финансовые показатели компании, такие как выручка, прибыль и затраты. Вы можете использовать исторические данные и другие факторы, такие как инфляция, ставки налогов, курсы валют и другие макроэкономические показатели, чтобы построить график функции регрессии. Это поможет вам более точно предсказать будущие финансовые результаты и принимать обоснованные решения.
График функции регрессии — это мощный инструмент, который помогает анализировать и предсказывать зависимости между переменными. Независимо от отрасли, функция регрессии может помочь вам выявить важные зависимости и сделать более обоснованные решения на основе данных.
Как использовать регрессионный анализ для построения графика
Для построения графика функции регрессии в Excel необходимо выполнить следующие шаги:
Шаг 1: Собрать данные. Необходимо собрать набор данных, состоящий из значений зависимой и независимой переменных. Здесь зависимая переменная будет представлять ось Y на графике, а независимая переменная — ось X.
Шаг 2: Выбрать тип графика. В Excel можно выбрать тип графика, который наилучшим образом отображает ваши данные и позволяет оценить связь между переменными. Например, для линейной регрессии можно использовать график точечной диаграммы (scatter plot) с линейным трендом.
Шаг 3: Разместить данные на графике. После того, как выбран тип графика, необходимо разместить данные на графике. В Excel это можно сделать, выбрав нужный диапазон данных и задав соответствующие оси для X и Y.
Шаг 4: Добавить линию регрессии. Чтобы добавить линию регрессии на график, нужно выбрать одну из опций регрессионного анализа в Excel. Например, можно использовать функцию TREND, чтобы получить линейное уравнение тренда и построить соответствующую линию.
Шаг 5: Интерпретировать результаты. После построения графика функции регрессии необходимо интерпретировать полученные результаты. Визуализация данных на графике позволяет оценить направление связи между переменными, а также определить, насколько точно линия регрессии соответствует данным.
Использование регрессионного анализа и построение графика функции регрессии в Excel позволяет визуализировать и анализировать связь между переменными, а также предсказывать значения зависимой переменной на основе независимых переменных. Этот метод анализа данных широко применяется в различных областях, включая статистику, экономику, маркетинг и другие.
Инструкция по построению графика функции регрессии в Excel
1. Откройте программу Excel и создайте новую таблицу. Введите набор данных, которые вы хотите проанализировать. Для простоты предположим, что у вас есть две переменные x и y, и у каждой переменной есть свои значения.
| Переменная x | Переменная y |
|---|---|
| 1 | 2 |
| 2 | 4 |
| 3 | 6 |
| 4 | 8 |
2. Выделите оба столбца с данными, включая заголовки, и выберите вкладку «Вставка» в верхней панели меню.
3. В группе «Диаграммы» выберите тип графика «Точечная диаграмма» («Scatter») и выберите один из вариантов точечной диаграммы. Это создаст пустую точечную диаграмму на листе Excel.
4. Нажмите правой кнопкой мыши на точечном графике и выберите «Добавить трендовую линию» («Add Trendline»).
5. В появившемся окне «Формат трендовой линии» («Format Trendline») выберите вкладку «Опции» («Options»).
6. В разделе «Тип трендовой линии» («Type») выберите тип «Линейная» («Linear») или другой подходящий тип функции регрессии.
7. Если вы хотите добавить уравнение функции регрессии и коэффициент детерминации (R²) на графике, установите флажки «Отобразить уравнение на диаграмме» («Display Equation on Chart») и «Отобразить R² на диаграмме» («Display R-squared Value on Chart»).
8. Щелкните на кнопке «ОК» («OK»), чтобы построить график функции регрессии и отобразить его на листе Excel.
Теперь у вас есть график функции регрессии в Excel, который отображает зависимость между переменными x и y. Это позволит вам легко взглянуть на данные и определить, существует ли между ними какая-либо линейная зависимость.
Обратите внимание, что Excel также предоставляет множество других инструментов и опций для анализа данных и построения графиков функций регрессии. Изучение этих инструментов может быть полезно для более глубокого анализа и визуализации данных.
Как интерпретировать график функции регрессии
График функции регрессии представляет собой визуальное отображение зависимости между двумя переменными в статистическом исследовании. Он позволяет увидеть, как одна переменная изменяется в зависимости от величины другой переменной.
На графике функции регрессии присутствуют две оси: горизонтальная (ось X) и вертикальная (ось Y). На оси X обычно откладывают независимую переменную (входные данные), а на оси Y — зависимую переменную (выходные данные).
Прежде чем интерпретировать график функции регрессии, необходимо обратить внимание на наклон линии регрессии. Если линия регрессии идет вверх, то можно сделать вывод о положительной зависимости между переменными. Если линия регрессии идет вниз, то это говорит о отрицательной зависимости.
Также важно обратить внимание на разброс точек вокруг линии регрессии. Если точки довольно плотно расположены вокруг линии, то это указывает на хорошую связь и достоверность модели регрессии. Если же точки разбросаны довольно широко относительно линии, то это может указывать на недостоверность модели или наличие влияния других факторов, которые не были учтены в исследовании.
График функции регрессии также может показывать наличие выбросов — точек, которые сильно отклоняются от общего тренда. Выбросы могут указывать на аномальные значения или ошибки в данных.
Интерпретация графика функции регрессии требует внимательного анализа и понимания статистических методов. Она позволяет сделать выводы о взаимосвязи между переменными и предсказать значения зависимой переменной на основе известных входных данных.
Примеры интерпретации графика функции регрессии:
1. Положительная зависимость:
На графике видно, что линия регрессии идет вверх, а точки расположены плотно вокруг линии. Это указывает на положительную зависимость между переменными. По мере увеличения значения независимой переменной, значения зависимой переменной также увеличиваются.
2. Отрицательная зависимость:
График показывает, что линия регрессии идет вниз, а точки разбросаны относительно линии. Это говорит о наличии отрицательной зависимости между переменными. По мере увеличения значения независимой переменной, значения зависимой переменной уменьшаются.
3. Недостоверность модели:
Точки на графике неравномерно расположены вокруг линии регрессии и имеют большой разброс. Это может указывать на недостоверность модели регрессии или на наличие других факторов, которые не были учтены в исследовании.
График показывает несколько точек, которые значительно отклоняются от общего тренда. Это можно считать выбросами, которые могут указывать на аномалии в данных или ошибки в измерениях.
Плюсы и минусы использования графика функции регрессии
Плюсы:
1. Визуализация зависимости между переменными. График функции регрессии позволяет наглядно оценить связь между зависимой и независимыми переменными. Это особенно полезно при анализе данных и прогнозировании.
2. Определение тренда. График функции регрессии помогает определить направление и силу тренда. Это позволяет прогнозировать будущие значения зависимой переменной.
3. Оценка качества модели. График позволяет оценить, насколько хорошо модель подходит под данные. Если точки на графике лежат близко к линии регрессии, значит, модель имеет хорошую предсказательную способность.
Минусы:
1. Ограничения представления данных. График функции регрессии позволяет увидеть только общую трендовую зависимость между переменными, но не позволяет изучить другие аспекты данных, такие как выбросы и выборосы.
2. Необходимость в дополнительных сведениях. Для полного понимания графика функции регрессии необходимо знать контекст и интерпретацию данных, а также характеристики модели и методики, использованной при её построении.
3. Не всегда точный прогноз. График функции регрессии может дать только общую оценку тренда и предсказание, основанное на имеющихся данных. Однако, он не гарантирует точность прогноза, особенно в случаях, когда данные имеют большую степень разброса.
Необходимо помнить о плюсах и минусах использования графика функции регрессии в контексте конкретной задачи и контексте анализируемых данных. Это поможет сделать правильные выводы и принять решение на основе адекватных представлений исследуемой модели.
Линейный регрессионный анализ в Excel
Представьте себе следующее: вам предоставили множество различных данных и попросили спрогнозировать показатели продаж вашей компании в следующем году. Вы обнаружили десятки, возможно, даже сотни факторов, которые могут повлиять на показатели. Но как узнать, какие из них действительно важны? Запустите регрессионный анализ в Excel. Он даст вам ответ на этот и многие другие вопросы: какие факторыНасколько тесно эти факторы связаны друг с другом? И насколько вы можете быть уверены в прогнозах?
Регрессионный анализ в Excel — основы
В статистическом моделировании, регрессионный анализ используется для оценки взаимосвязей между двумя или более переменными:
Зависимая переменная (aka критерий переменная) — это основной фактор, который вы пытаетесь понять и предсказать.
Независимые переменные (aka пояснительная переменные, или предикторы ) — это факторы, которые могут повлиять на зависимую переменную.
Смотрите также: Как выделить дублирующийся текст / слова в ячейке Excel
Регрессионный анализ помогает понять, как изменяется зависимая переменная при изменении одной из независимых переменных, и позволяет математически определить, какая из этих переменных действительно оказывает влияние.
Технически, модель регрессионного анализа основана на сумма квадратов Цель модели — получить наименьшую возможную сумму квадратов и провести линию, наиболее близко подходящую к данным.
В статистике различают простую и множественную линейную регрессию. Простая линейная регрессия моделирует связь между зависимой переменной и одной независимой переменной с помощью линейной функции. Если вы используете две или более объясняющих переменных для прогнозирования зависимой переменной, вы имеете дело с множественная линейная регрессия Если зависимая переменная моделируется как нелинейная функция, потому что зависимости данных не следуют прямой линии, используйте нелинейная регрессия вместо этого. Основное внимание в этом учебнике будет уделено простой линейной регрессии.
В качестве примера возьмем данные о продажах зонтов за последние 24 месяца и выясним среднемесячное количество осадков за тот же период. Нанесите эту информацию на график, и линия регрессии продемонстрирует связь между независимой переменной (количество осадков) и зависимой переменной (продажи зонтов):
Уравнение линейной регрессии
Математически линейная регрессия определяется этим уравнением:
- x является независимой переменной.
- y является зависимой переменной.
- a это Y-интерцепт что является ожидаемым средним значением y когда все x переменные равны 0. На графике регрессии это точка, где линия пересекает ось Y.
- b — это наклон линии регрессии, которая является скоростью изменения для y в качестве x изменения.
- ε случайный член ошибки, который представляет собой разницу между фактическим значением зависимой переменной и ее прогнозируемым значением.
Уравнение линейной регрессии всегда содержит член ошибки, поскольку в реальной жизни предсказатели никогда не бывают идеально точными. Однако некоторые программы, включая Excel, выполняют расчет члена ошибки за кадром. Так, в Excel вы выполняете линейную регрессию с помощью функции наименьшие квадраты метод и искать коэффициенты a и b такой, что:
Для нашего примера уравнение линейной регрессии имеет следующий вид:
Проданные зонтики = b * количество осадков + a
Существует несколько различных способов найти a и b Три основных метода проведения линейного регрессионного анализа в Excel:
- Инструмент регрессии, включенный в пакет Analysis ToolPak
- Диаграмма разброса с линией тренда
- Формула линейной регрессии
Ниже приведены подробные инструкции по использованию каждого метода.
Как выполнить линейную регрессию в Excel с помощью Analysis ToolPak
В этом примере показано, как запустить регрессию в Excel с помощью специального инструмента, входящего в состав надстройки Analysis ToolPak.
Включите надстройку Analysis ToolPak
Analysis ToolPak доступен во всех версиях Excel 365 до 2003, но по умолчанию он не включен. Поэтому его нужно включить вручную. Вот как это сделать:
- В Excel нажмите кнопку Файл > Опции .
- В Параметры Excel в диалоговом окне выберите Дополнения на левой боковой панели, убедитесь, что Надстройки Excel выбран в Управляйте и нажмите кнопку Перейти .
- В Дополнения диалогового окна, поставьте галочку Пакет инструментов для анализа , и нажмите OK :
Это добавит Анализ данных инструменты для Данные на вкладке ленты Excel.
Провести регрессионный анализ
В этом примере мы собираемся провести простую линейную регрессию в Excel. У нас есть список среднемесячных осадков за последние 24 месяца в столбце B, который является нашей независимой переменной (предиктором), и количество проданных зонтов в столбце C, который является зависимой переменной. Конечно, существует множество других факторов, которые могут повлиять на продажи, но пока мы сосредоточимся только на этих двух переменных:
При включенном Analysis Toolpak, выполните следующие шаги для проведения регрессионного анализа в Excel:
- На Данные во вкладке Анализ группу, нажмите кнопку Анализ данных кнопка.
- Выберите Регрессия и нажмите OK .
- В Регрессия диалоговое окно, настройте следующие параметры:
- Выберите Входной диапазон Y , который является вашим зависимая переменная В нашем случае это продажа зонтиков (C1:C25).
- Выберите Вход X Диапазон , т.е. ваш независимая переменная В данном примере это среднемесячное количество осадков (B1:B25).
Если вы строите модель множественной регрессии, выберите два или более соседних столбца с разными независимыми переменными.
- Проверьте Коробка с этикетками если есть заголовки в верхней части диапазонов X и Y.
- Выберите предпочтительный Возможность вывода, в нашем случае — новый рабочий лист.
- По желанию выберите Остатки флажок, чтобы получить разницу между прогнозируемым и фактическим значениями.
Интерпретировать результаты регрессионного анализа
Как вы только что убедились, запустить регрессию в Excel очень просто, поскольку все вычисления выполняются автоматически. Интерпретация результатов немного сложнее, поскольку вам нужно знать, что стоит за каждым числом. Ниже вы найдете разбивку 4 основных частей результатов регрессионного анализа.
Смотрите также: Формула диапазона дат Excel SUMIFS — суммировать, если между двумя датами
Выходные данные регрессионного анализа: Сводный выход
Эта часть говорит о том, насколько хорошо рассчитанное уравнение линейной регрессии соответствует вашим исходным данным.
Вот что означает каждая часть информации:
Множественные R Это С Коэффициент корреляции Коэффициент корреляции может иметь любое значение от -1 до 1, а его абсолютное значение указывает на силу связи. Чем больше абсолютное значение, тем сильнее связь:
- 1 означает сильную положительную связь
- -1 означает сильную отрицательную связь
- 0 означает полное отсутствие отношений
R-квадрат . Это Коэффициент детерминации R2 рассчитывается из общей суммы квадратов, точнее, это сумма квадратов отклонений исходных данных от среднего значения.
В нашем примере R2 равен 0,91 (округлим до двузначного числа), что является хорошим показателем. Это означает, что 91% наших значений соответствуют модели регрессионного анализа. Другими словами, 91% зависимых переменных (значения y) объясняются независимыми переменными (значениями x). Обычно хорошим показателем считается R Squared, равный 95% или более.
Скорректированный квадрат R . Это Площадь с поправкой на количество независимых переменных в модели. Вы захотите использовать это значение вместо Площадь для множественного регрессионного анализа.
Стандартная ошибка Это еще одна мера хорошего соответствия, которая показывает точность регрессионного анализа — чем меньше число, тем больше уверенности в уравнении регрессии. В то время как R2 представляет собой процент дисперсии зависимых переменных, которая объясняется моделью, стандартная ошибка — это абсолютная мера, которая показывает среднее расстояние, на котором точки данных отклоняются от регрессии.линия.
Наблюдения Это просто количество наблюдений в вашей модели.
Выход регрессионного анализа: ANOVA
Вторая часть вывода — дисперсионный анализ (ANOVA):
По сути, он разделяет сумму квадратов на отдельные компоненты, которые дают информацию об уровнях изменчивости в вашей регрессионной модели:
- df число степеней свободы, связанных с источниками дисперсии.
- SS Чем меньше остаточный SS по сравнению с общим SS, тем лучше ваша модель соответствует данным.
- MS средний квадрат.
- F F-статистика, или F-тест для нулевой гипотезы. Используется для проверки общей значимости модели.
- Значимость F P-значение F.
Часть ANOVA редко используется для простого линейного регрессионного анализа в Excel, но вы обязательно должны внимательно изучить последний компонент. The Значимость F дает представление о том, насколько надежны (статистически значимы) ваши результаты. Если значение Significance F меньше 0,05 (5%), ваша модель в порядке. Если оно больше 0,05, вам, вероятно, лучше выбрать другую независимую переменную.
Результаты регрессионного анализа: коэффициенты
В этом разделе представлена конкретная информация о компонентах вашего анализа:
Наиболее полезным компонентом в этом разделе является Коэффициенты Она позволяет построить уравнение линейной регрессии в Excel:
Для нашего набора данных, где y это количество проданных зонтиков, а x — среднемесячное количество осадков, наша формула линейной регрессии выглядит следующим образом:
Y = Коэффициент осадков * x + Перехват
При использовании значений a и b, округленных до трех знаков после запятой, получается:
Например, при среднемесячном количестве осадков, равном 82 мм, объем продаж зонтов составит приблизительно 17,8:
Аналогичным образом можно узнать, сколько зонтиков будет продано при любом другом заданном вами месячном количестве осадков (переменная x).
Выходные данные регрессионного анализа: остатки
Если сравнить расчетное и фактическое количество проданных зонтов, соответствующее месячному количеству осадков в 82 мм, то можно увидеть, что эти цифры немного отличаются:
- Предполагаемый: 17,8 (рассчитано выше)
- Фактически: 15 (строка 2 исходных данных)
Почему разница? Потому что независимые переменные никогда не являются идеальными предсказателями зависимых переменных. А остатки могут помочь вам понять, насколько далеки фактические значения от предсказанных:
Для первой точки данных (количество осадков 82 мм) остаток составляет примерно -2,8. Прибавляем это число к предсказанному значению и получаем фактическое значение: 17,8 — 2,8 = 15.
Как построить график линейной регрессии в Excel
Если вам нужно быстро визуализировать связь между двумя переменными, постройте график линейной регрессии. Это очень просто! Вот как:
- Выберите два столбца с данными, включая заголовки.
- На Вставка во вкладке Чаты группу, нажмите кнопку Диаграмма рассеяния значок и выберите Разброс миниатюра (первая):
Это позволит вставить в рабочий лист диаграмму рассеяния, которая будет похожа на эту: 
- Теперь нам нужно построить линию регрессии наименьших квадратов. Чтобы сделать это, щелкните правой кнопкой мыши на любой точке и выберите Добавить линию тренда. из контекстного меню.
- На правой панели выберите Линейный форму линии тренда и, по желанию, проверить Отображение уравнения на графике чтобы получить формулу регрессии:
Как вы можете заметить, уравнение регрессии, которое Excel создал для нас, совпадает с формулой линейной регрессии, которую мы построили на основе вывода Coefficients. - Переключитесь на Заполнение & линия и настройте линию по своему вкусу. Например, вы можете выбрать другой цвет линии и использовать сплошную линию вместо пунктирной (выберите Сплошная линия в меню Тип тире коробка):
На данном этапе ваш график уже выглядит как приличный график регрессии:
Тем не менее, вы можете захотеть сделать еще несколько улучшений:
- Перетащите уравнение туда, куда считаете нужным.
- Добавьте заголовки осей ( Элементы диаграммы кнопка> Осевые титулы ).
- Если точки данных начинаются в середине горизонтальной и/или вертикальной оси, как в данном примере, возможно, вам захочется избавиться от лишнего белого пространства. Следующий совет объясняет, как это сделать: масштабируйте оси графика, чтобы уменьшить белое пространство. А вот так выглядит наш улучшенный график регрессии: Важное замечание! На графике регрессии независимая переменная всегда должна находиться на оси X, а зависимая — на оси Y. Если ваш график построен в обратном порядке, поменяйте местами столбцы в рабочем листе, а затем постройте график заново. Если вам не разрешено переставлять исходные данные, то вы можете поменять местами оси X и Y непосредственно на графике.
Как сделать регрессию в Excel с помощью формул
В Microsoft Excel есть несколько статистических функций, которые могут помочь вам провести линейный регрессионный анализ, например, LINEST, SLOPE, INTERCEPT и CORREL.
Функция LINEST использует метод регрессии наименьших квадратов для вычисления прямой линии, которая наилучшим образом объясняет связь между вашими переменными, и возвращает массив, описывающий эту линию. Подробное объяснение синтаксиса функции вы можете найти в этом учебнике. Пока давайте просто составим формулу для нашего набора данных:
Поскольку функция LINEST возвращает массив значений, вы должны ввести ее как формулу массива. Выделите две соседние ячейки в одной строке, E2:F2 в нашем случае, введите формулу и нажмите Ctrl + Shift + Enter, чтобы завершить ее.
Формула возвращает b коэффициент (E1) и a константа (F1) для уже знакомого уравнения линейной регрессии:
y = bx + a
Если вы избегаете использования формул массивов в своих рабочих листах, вы можете вычислить a и b индивидуально с помощью обычных формул:
Получите Y-интерцепт (a):
Получите наклон (b):
Кроме того, вы можете найти коэффициент корреляции ( Множественные R в итоговом выводе регрессионного анализа), который показывает, насколько сильно две переменные связаны друг с другом:
На следующем снимке экрана показаны все эти формулы регрессии Excel в действии:
Совет. Если вы хотите получить дополнительную статистику для регрессионного анализа, используйте функцию LINEST с параметром s татуировки параметр установлен в TRUE, как показано в этом примере.
Вот как выполняется линейная регрессия в Excel. При этом следует помнить, что Microsoft Excel не является статистической программой. Если вам необходимо выполнить регрессионный анализ на профессиональном уровне, вам может понадобиться целевое программное обеспечение, такое как XLSTAT, RegressIt и т.д.
Чтобы более подробно ознакомиться с формулами линейной регрессии и другими методами, рассмотренными в этом учебнике, вы можете скачать наш образец рабочей тетради ниже. Спасибо за чтение!
Практическая тетрадь
Регрессионный анализ в Excel — примеры (файл.xlsx)
Предыдущий пост Панель инструментов быстрого доступа в Excel: как настроить, переместить и сбросить
Следующий пост Как использовать функцию MIN в Excel
Michael Brown
Майкл Браун — увлеченный технологический энтузиаст, стремящийся упростить сложные процессы с помощью программных инструментов. Имея более чем десятилетний опыт работы в технологической отрасли, он отточил свои навыки в Microsoft Excel и Outlook, а также в Google Sheets и Docs. Блог Майкла посвящен тому, чтобы делиться своими знаниями и опытом с другими, предоставляя простые советы и учебные пособия для повышения производительности и эффективности. Являетесь ли вы опытным профессионалом или новичком, в блоге Майкла вы найдете ценную информацию и практические советы, которые помогут вам максимально эффективно использовать эти важные программные инструменты.