График уравнения регрессии в excel. Корреляция и регрессия

Линия регрессии является графическим отражением взаимосвязи между явлениями. Очень наглядно можно построить линию регрессии в программе Excel.

Для этого необходимо:

1.Открыть программу Excel

2.Создать столбцы с данными. В нашем примере мы будем строить линию регрессии, или взаимосвязи, между агрессивностью и неуверенностью в себе у детей-первоклассников. В эксперименте участвовали 30 детей, данные представлены в таблице эксель:

1 столбик — № испытуемого

2 столбик — агрессивность в баллах

3 столбик — неуверенность в себе в баллах

3.Затем необходимо выделить оба столбика (без названия столбика), нажать вкладку вставка , выбрать точечная , а из предложенных макетов выбрать самый первый точечная с маркерами .

4.Итак у нас получилась заготовка для линии регрессии — так называемая — диаграмма рассеяния . Для перехода к линии регрессии нужно щёлкнуть на получившийся рисунок, нажать вкладку конструктор, найти на панели макеты диаграмм и выбрать Ма кет9 , на нем ещё написано f(x)

5.Итак, у нас получилась линия регрессии. На графике также указано её уравнение и квадрат коэффициента корреляции

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

Линия регрессии построена в MS Excel. Теперь её можно добавить в текст работы.

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

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

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

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

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

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

О выполнении последнего вида регрессионного анализа в Экселе мы подробнее поговорим далее.

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

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

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

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

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

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

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

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

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

В Excel имеется еще более быстрый и удобный способ построить график линейной регрессии (и даже основных видов нелинейных регрессий, о чем см. далее). Это можно сделать следующим образом:

1) выделить столбцы с данными X и Y (они должны располагаться именно в таком порядке!);

2) вызвать Мастер диаграмм и выбрать в группе Тип – Точечная и сразу нажать Готово ;

3) не сбрасывая выделения с диаграммы, выбрать появившейся пункт основного меню Диаграмма , в котором следует выбрать пункт Добавить линию тренда ;

4) в появившемся диалоговом окне Линия тренда во вкладке Тип выбрать Линейная ;

5) во вкладке Параметры можно активизировать переключатель Показывать уравнение на диаграмме , что позволит увидеть уравнение линейной регрессии (4.4), в котором будут вычислены коэффициенты (4.5).

6) В этой же вкладке можно активизировать переключатель Поместить на диаграмму величину достоверности аппроксимации (R^2) . Эта величина есть квадрат коэффициента корреляции (4.3) и она показывает, насколько хорошо рассчитанное уравнение описывает экспериментальную зависимость. Если R 2 близок к единице, то теоретическое уравнение регрессии хорошо описывает экспериментальную зависимость (теория хорошо согласуется с экспериментом), а если R 2 близок к нулю, то данное уравнение не пригодно для описания экспериментальной зависимости (теория не согласуется с экспериментом).

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

§4.3. Основные виды нелинейной регрессии

Параболическая и полиномиальная регрессии.

Параболической зависимостью величины Y от величины Х называется зависимость, выраженная квадратичной функцией (параболой 2-ого порядка):

Это уравнение называется уравнением параболической регрессии Y на Х . Параметры а , b , с называются коэффициентами параболической регрессии . Вычисление коэффициентов параболической регрессии всегда громоздко, поэтому для расчетов рекомендуется использовать компьютер.

Уравнение (4.8) параболической регрессии является частным случаем более общей регрессии, называемой полиномиальной. Полиномиальной зависимостью величины Y от величины Х называется зависимость, выраженная полиномом n -ого порядка:

где числа а i (i =0,1,…, n ) называются коэффициентами полиномиальной регрессии .

Степенная регрессия.

Степенной зависимостью величины Y от величины Х называется зависимость вида:

Это уравнение называется уравнением степенной регрессии Y на Х . Параметры а и b называются коэффициентами степенной регрессии .

ln =lna +b· lnx . (4.11)

Это уравнение описывает прямую на плоскости с логарифмическими координатными осями lnx и ln . Поэтому критерием применимости степенной регрессии служит требование того, чтобы точки логарифмов эмпирических данных lnx i и lnу i находились ближе всего к прямой (4.11).

Показательная регрессия.

Показательной (или экспоненциальной ) зависимостью величины Y от величины Х называется зависимость вида:

(или ). (4.12)

Это уравнение называется уравнением показательной (или экспоненциальной ) регрессии Y на Х . Параметры а (или k ) и b называются коэффициентами показательной (или экспоненциальной ) регрессии .

Если прологарифмировать обе части уравнения степенной регрессии, то получится уравнение

ln =x· lna +lnb (или ln =k·x +lnb ). (4.13)

Это уравнение описывает линейную зависимость логарифма одной величины ln от другой величины x . Поэтому критерием применимости степенной регрессии служит требование того, чтобы точки эмпирических данных одной величины x i и логарифмы другой величины lnу i находились ближе всего к прямой (4.13).

Логарифмическая регрессия.

Логарифмической зависимостью величины Y от величины Х называется зависимость вида:

=a +b· lnx . (4.14)

Это уравнение называется уравнением логарифмической регрессии Y на Х . Параметры а и b называются коэффициентами логарифмической регрессии .

Гиперболическая регрессия.

Гиперболической зависимостью величины Y от величины Х называется зависимость вида:

Это уравнение называется уравнением гиперболической регрессии Y на Х . Параметры а и b называются коэффициентами гиперболической регрессии и определяются методом наименьших квадратов. Применение этого метода приводит к формулам:

В формулах (4.16-4.17) суммирование проводится по индексу i от единицы до количества наблюдений n .

К сожалению, в Excel нет функции, вычисляющих коэффициенты гиперболической регрессии. В тех случаях, когда заведомо не известно, что измеряемые величины связаны обратной пропорциональностью, рекомендуется вместо уравнения гиперболической регрессии искать уравнение степенной регрессии, так в Excel имеется процедура ее нахождения. Если же между измеряемыми величинами предполагается гиперболическая зависимость, то коэффициенты ее регрессии придется вычислять с помощью вспомогательных расчетных таблиц и операций суммирования по формулам (4.16-4.17).

Метод линейной регрессии позволяет нам описывать прямую линию, максимально соответствующую ряду упорядоченных пар (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 параметры регрессии в окне Рег­рессия , как показано на рисунке:

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

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

Понятие регрессии . Зависимость между переменными величинами x и y может быть описана разными способами. В частности, любую форму связи можно выразить уравнением общего вида , гдеy рассматривается в качестве зависимой переменной, или функции от другой – независимой переменной величины x, называемой аргументом . Соответствие между аргументом и функцией может быть задано таблицей, формулой, графиком и т.д. Изменение функции в зависимости от изменения одного или нескольких аргументов называется регрессией . Все средства, применяемые для описания корреляционных связей, составляет содержание регрессионного анализа .

Для выражения регрессии служат корреляционные уравнения, или уравнения регрессии, эмпирические и теоретически вычисленные ряды регрессии, их графики, называемые линиями регрессии, а также коэффициенты линейной и нелинейной регрессии.

Показатели регрессии выражают корреляционную связь двусторонне, учитывая изменение усредненных значений признакаY при изменении значений x i признака X , и, наоборот, показывают изменение средних значений признакаX по измененным значениям y i признака Y . Исключение составляют временные ряды, или ряды динамики, показывающие изменение признаков во времени. Регрессия таких рядов является односторонней.

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

12.1 Линейная регрессия

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

Линейная зависимость между переменными x и y описывается уравнением общего вида , гдеa, b, c, d, … – параметры уравнения, определяющие соотношения между аргументами x 1 , x 2 , x 3 , …, x m и функций .

В практике учитывают не все возможные, а лишь некоторые аргументы, в простейшем случае – всего один:

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

Рис. 1 Линии регрессии Y по X и X поY в системе

прямоугольных координат

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

Поскольку показатели регрессии выражают корреляционную связь двусторонне, уравнение регрессии (1) следует записывать так:

По первой формуле определяют усредненные значения при изменении признакаX на единицу меры, по второй – усредненные значения при изменении на единицу меры признакаY .

Коэффициент регрессии. Коэффициент регрессии показывает, насколько в среднем величина одного признака y изменяется при изменении на единицу меры другого, корреляционно связанного с Y признака X . Этот показатель определяют по формуле

Здесь значения s умножают на размеры классовых интервалов λ , если их находили по вариационным рядам или корреляционным таблицам.

Коэффициент регрессии можно вычислить минуя расчет средних квадратичных отклонений s y и s x по формуле

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

Связь между коэффициентами регрессии и корреляции. Сравнивая формулы (11.1) (тема 11) и (12.5), видим: в их числителе одна и та же величина , что указывает на наличие связи между этими показателями. Эта связь выражается равенством

Таким образом, коэффициент корреляции равен средней геометрической из коэффициентов b yx и b xy . Формула (6) позволяет, во-первых, по известным значениям коэффициентов регрессии b yx и b xy определять коэффициент регрессии R xy , а во-вторых, проверять правильность расчета этого показателя корреляционной связи R xy между варьирующими признаками X и Y .

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

Определение параметров линейной регрессии. Известно, что сумма квадратов отклонений вариант x i от средней есть величина наименьшая, т.е.. Эта теорема составляет основу метода наименьших квадратов. В отношении линейной регрессии [см. формулу (1)] требованию этой теоремы удовлетворяет некоторая система уравнений, называемыхнормальными :

Совместное решение этих уравнений относительно параметров a и b приводит к следующим результатам:

;

;

, откуда и.

Учитывая двусторонний характер связи между переменными Y и X , формулу для определения параметра а следует выразить так:

и . (7)

Параметр b , или коэффициент регрессии, определяют по следующим формулам:

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

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

Выравнивание эмпирических рядов регрессии. Графики эмпирических рядов регрессии оказываются, как правило, не плавно идущими, а ломаными линиями. Это объясняется тем, что наряду с главными причинами, определяющими общую закономерность в изменчивости коррелируемых признаков, на их величине сказывается влияние многочисленных второстепенных причин, вызывающих случайные колебания узловых точек регрессии. Чтобы выявить основную тенденцию (тренд) сопряженной вариации коррелируемых признаков, нужно заменить ломанные линии на гладкие, плавно идущие линии регрессии. Процесс замены ломанных линий на плавно идущие называют выравниванием эмпирических рядов и линий регрессий .

Графический способ выравнивания. Это наиболее простой способ, не требующий вычислительной работы. Его сущность сводится к следующему. Эмпирический ряд регрессии изображают в виде графика в системе прямоугольных координат. Затем визуально намечаются средние точки регрессии, по которым с помощью линейки или лекала проводят сплошную линию. Недостаток этого способа очевиден: он не исключает влияние индивидуальных свойств исследователя на результаты выравнивания эмпирических линий регрессии. Поэтому в тех случаях, когда необходима более высокая точность при замене ломанных линий регрессии на плавно идущие, используют другие способы выравнивания эмпирических рядов.

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

Метод наименьших квадратов. Этот способ предложен в начале XIX столетия А.М. Лежандром и независимо от него К. Гауссом. Он позволяет наиболее точно выравнивать эмпирические ряды. Этот метод, как было показано выше, основан на предположении, что сумма квадратов отклонений вариант x i от их средней есть величина минимальная, т.е.. Отсюда и название метода, который применяется не только в экологии, но и в технике. Метод наименьших квадратов объективен и универсален, его применяют в самых различных случаях при отыскании эмпирических уравнений рядов регрессии и определении их параметров.

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

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

Множественная линейная регрессия. Зависимость между несколькими переменными величинами принято выражать уравнением множественной регрессии, которая может быть линейной и нелинейной . В простейшем виде множественная регрессия выражается уравнением с двумя независимыми переменными величинами (x , z ):

где a – свободный член уравнения; b и c – параметры уравнения. Для нахождения параметров уравнения (10) (по способу наименьших квадратов) применяют следующую систему нормальных уравнений:

Ряды динамики. Выравнивание рядов. Изменение признаков во времени образует так называемые временные ряды или ряды динамики . Характерной особенностью таких рядов является то, что в качестве независимой переменной X здесь всегда выступает фактор времени, а зависимой Y – изменяющийся признак. В зависимости от рядов регрессии зависимость между переменными X и Y носит односторонний характер, так как фактор времени не зависит от изменчивости признаков. Несмотря на указанные особенности, ряды динамики можно уподобить рядам регрессии и обрабатывать их одними и теми же методами.

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

Анализ рядов динамики начинается с выявления формы тренда. Для этого временной ряд изображают в виде линейного графика в системе прямоугольных координат. При этом по оси абсцисс откладывают временные точки (годы, месяцы и другие единицы времени), а по оси ординат – значения зависимой переменной Y. При наличии линейной зависимости между переменными X и Y (линейного тренда) для выравнивания рядов динамики способом наименьших квадратов наиболее подходящим является уравнение регрессии в виде отклонений членов ряда зависимой переменной Y от средней арифметической ряда независимой переменнойX:

Здесь – параметр линейной регрессии.

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

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

Корреляция рядов динамики. Нередко приходится сопоставлять динамику параллельно идущих временных рядов, связанных друг с другом некоторыми общими условиями, например выяснить связь между производством сельскохозяйственной продукции и ростом поголовья скота за определенный промежуток времени. В таких случаях характеристикой связи между переменными X и Y служит коэффициент корреляции R xy (при наличии линейного тренда).

Известно, что тренд рядов динамики, как правило, затушевывается колебаниями членов ряда зависимой переменной Y. Отсюда возникает задача двоякого рода: измерение зависимости между сопоставляемыми рядами, не исключая тренд, и измерение зависимости между соседними членами одного и того же ряда, исключая тренд. В первом случае показателем тесноты связи между сопоставляемыми рядами динамики служит коэффициент корреляции (если связь линейна), во втором – коэффициент автокорреляции . Эти показатели имеют разные значения, хотя и вычисляются по одним и тем же формулам (см. тему 11).

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