Как построить линейную регрессию в excel

Как выполнить простую линейную регрессию в 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:

Как добавить линию регрессии на диаграмму рассеяния в Excel

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

В этом руководстве представлен пошаговый пример того, как быстро добавить простую линию линейной регрессии на диаграмму рассеивания в Excel.

Шаг 1: Создайте данные

Во-первых, давайте создадим простой набор данных для работы:

Шаг 2: Создайте диаграмму рассеивания

Затем выделите диапазон ячеек A2:B21.На верхней ленте щелкните вкладку « ВСТАВИТЬ », затем нажмите « ВСТАВИТЬ точечную (X, Y)» или «Пузырьковую диаграмму » в группе « Диаграммы » и выберите первый вариант, чтобы создать диаграмму рассеяния:

Появится следующая диаграмма рассеяния:

Шаг 3: Добавьте линию регрессии

Затем щелкните в любом месте диаграммы рассеивания. Затем щелкните знак «плюс» (+) в правом верхнем углу графика и установите флажок « Линия тренда ».

Это автоматически добавит на диаграмму рассеяния простую линию линейной регрессии:

Добавить линию регрессии в диаграмму рассеяния в Excel

Шаг 4: Добавьте уравнение линии регрессии

Вы также можете добавить уравнение линии регрессии на диаграмму, нажав Дополнительные параметры. В появившемся справа поле установите флажок рядом с Отображать уравнение на диаграмме .

На диаграмме рассеивания автоматически появится простое уравнение линейной регрессии:

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

у = 0,917х + 12,462

Интерпретировать это можно следующим образом:

• Для каждой дополнительной единицы увеличения переменной x среднее увеличение переменной y составляет 0,917 .
• Когда переменная x равна нулю, среднее значение переменной y равно 12,462 .

Мы также можем использовать это уравнение для оценки значения y на основе значения x.Например, когда x равно 15, ожидаемое значение y равно 26,217:

у = 0,917 * (15) + 12,462 = 26,217

Другие учебники по 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

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

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

Ключевые выводы

• Линейная регрессия моделирует отношения между зависимой и независимой переменной (ами).
• Регрессионный анализ может быть проведен, если переменные независимы, нет гетероскедастичности и члены ошибок переменных не коррелированы.
• Моделирование линейной регрессии в Excel стало проще с помощью пакета Data Analysis ToolPak.

Важные соображения

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

1. Переменные должны быть действительно независимыми (с использованием критерия хи-квадрат ).
2. Данные не должны иметь различную дисперсию ошибок (это называется гетероскедастичностью (также обозначается как гетероскедастичность)).
3. Члены ошибки каждой переменной не должны коррелировать. Если нет, это означает, что переменные последовательно коррелированы.

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

Вывод регрессии в Excel

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

Используя Data Analysis ToolPak, создать результат регрессии можно всего за несколько щелчков мышью.

Краткий обзор

Независимая переменная находится в диапазоне X.

Учитывая доходность S&P 500 , скажем, мы хотим знать, можем ли мы оценить силу и взаимосвязь доходностей акций Visa (

1. Выберите «Данные» на панели инструментов. Отображается меню «Данные».
2. Выберите «Анализ данных». Откроется диалоговое окно Анализ данных — Инструменты анализа.
3. В меню выберите «Регрессия» и нажмите «ОК».
4. В диалоговом окне «Регрессия» щелкните поле «Введите диапазон Y» и выберите данные зависимой переменной (доходность акций Visa (V)).
5. Щелкните поле «Входной диапазон X» и выберите данные независимых переменных (возвращается S&P 500).
6. Нажмите «ОК», чтобы просмотреть результаты.

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

Интерпретируйте результаты

Используя эти данные (те же, что и в нашей статье о R-квадрате ), мы получаем следующую таблицу:

Значение R 2, также известное как коэффициент детерминации, измеряет долю вариации в зависимой переменной, объясняемую независимой переменной, или насколько хорошо регрессионная модель соответствует данным. Значение R 2 находится в диапазоне от 0 до 1, и более высокое значение указывает на лучшее соответствие. Значение p или значение вероятности также находится в диапазоне от 0 до 1 и указывает, является ли тест значимым. В отличие от значения R 2, меньшее значение p является благоприятным, поскольку оно указывает на корреляцию между зависимыми и независимыми переменными.

Построение графика регрессии в Excel

Мы можем построить график регрессии в Excel, выделив данные и отобразив их в виде точечной диаграммы. Чтобы добавить линию регрессии, выберите «Макет» в меню «Инструменты диаграммы». В диалоговом окне выберите «Линия тренда», а затем «Линия тренда». Чтобы добавить значение R 2, выберите «Дополнительные параметры линии тренда» в меню «Линия тренда». Наконец, выберите «Показать значение R-квадрата на диаграмме». Визуальный результат суммирует силу взаимосвязи, хотя и не обеспечивает столько же деталей, сколько в таблице выше.

Похожие статьи

• Множественная линейная регрессия (MLR)
• Определение регрессии
• Основы регрессии для бизнес-анализа
• Что такое регрессия? Исчерпывающее руководство
• Линейная регрессия и множественная регрессия: в чем разница?
• Что такое нелинейная регрессия?
• Улучшите свои инвестиции с помощью Excel
• R-квадрат
• R-квадрат Определение
• Что означают положительный, отрицательный и нулевой коэффициенты корреляции?