Как рассчитать регрессию в 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

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


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

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

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

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

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

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