Как рассчитать регрессию в Excel

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

ŷ = a + bx

где:

ŷ — ожидаемое значение у при заданном значении х,

x — независимая переменная,

a — отрезок на оси y для прямой линии,

b — наклон прямой линии.

На рисунке ниже это понятие представлено графически:

регрессия excel

На рисунке выше показана линия, описанная уравнением ŷ =2+0.5х. Отрезок на оси у — это точка пересечения линией оси у; в нашем случае а = 2. Наклон линии, b, отношение подъема линии к длине линии, имеет значение 0.5. Положительный наклон означает, что линия поднимается слева направо. Если b = 0, линия горизонтальна, а это значит, что между зависимой и независимой переменными нет никакой связи. Иными словами, изменение значения x не влияет на значение y.

Часто путают ŷ и у. На графике показаны 6 упорядоченных пар точек и линия, в соответствии с данным уравнением

ŷ = 2 + 0.5x

регрессия excel

На этом рисунке показана точка, соответствующая упорядоченной паре х = 2 и у = 4. Обратите внимание, что ожидаемое значение у в соответствии с линией при х = 2 является ŷ. Мы можем подтвердить это с помощью следу­ющего уравнения:

ŷ = 2 + 0.5х =2 +0.5(2) =3.

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

Следующий шаг — определить линейное уравнение, максимально соответствующее набору упорядоченных пар, об этом мы говорили в предыдущей статье, где определяли вид уравнения по методу наименьших квадратов.

Использование Excel для определения линейной регрессии

Для того, чтобы воспользоваться инструментом регрессионного анализа встроенного в Excel, необходимо активировать надстройку Пакет анализа. Найти ее можно, перейдя по вкладке Файл –> Параметры (2007+), в появившемся диалоговом окне Параметры Excel переходим во вкладку Надстройки. В поле Управление выбираем Надстройки Excel и щелкаем Перейти. В появившемся окне ставим галочку напротив Пакет анализа, жмем ОК.

пакет анализа excel

Во вкладке Данные в группе Анализ появится новая кнопка Анализ данных.

регрессия excel

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

Перейдите во вкладку Данные, в группе Анализ щелкните Анализ данных. В появившемся окне Анализ данных выберите Регрессия, как показано на рисунке, и щелкните ОК.

регрессия excel

Установите необходимыe параметры регрессии в окне Рег­рессия, как показано на рисунке:

регрессия excel

Щелкните ОК. На рисунке ниже показаны полученные результаты:

регрессия excel

Эти результаты соответствуют тем, которые мы получили путем самостоя­тельных вычислений в предыдущей статье.


8 комментариев

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

  2. «Эти результаты соответствуют тем, которые мы получили путем самостоятельных вычислений в предыдущей статье.»
    каким образом они соответствуют? никакого соответствия
    где ваши коэффициенты регрессии? где вы получили с помощью пакета анализа числа «2» и «0.5»? Вы получили единственный коэффициент 0.975(75), и выходит ваша модель выглядит как y=ax, и это не исходные y=ax+b

  3. СПАСИБО ВАМ ОГРОМНОЕ!
    Сидела мучилась, не могла найти анализ данных в эксель.
    В статье все просто и легко разъяснено, благодарю!

  4. Добрый день. Подскажите, пожалуйста, такую проблему.
    При первоначальном построении регрессии коэффициент детерминации получается очень маленький. Чтобы подогнать первоначальную выборку под нужный коэффициент детерминации мы пользовались выводом остатков (строки с большими остатками выкидывали из первоначальной выборки). Этот способ всегда работал. И вдруг, перестал. Коэффициент детерминации не растет, а падает. С чем это может быть связано?

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *