Корреляционно-регрессионный анализ в Excel: инструкция выполнения. Регрессия в Excel: уравнение, примеры. Линейная регрессия Регрессионный анализ для приравнивания групп

Парная регрессия - уравнение связи двух переменных у и х: y=f(x), где y - зависимая переменная (результативный признак); x - независимая, объясняющая переменная (признак-фактор).

Различают линейные и нелинейные регрессии.
Линейная регрессия: y = a + bx + ε
Нелинейные регрессии делятся на два класса: регрессии, нелинейные относительно включенных в анализ объясняющих переменных, но линейные по оцениваемым параметрам, и регрессии, нелинейные по оцениваемым параметрам.
Регрессии, нелинейные по объясняющим переменным:

Регрессии, нелинейные по оцениваемым параметрам:

• степенная y=a·x b ·ε
• показательная y=a·b x ·ε
• экспоненциальная y=e a+b·x ·ε

Задача дисперсионного анализа состоит в анализе дисперсии зависимой переменной:
∑(y-y )²=∑(y x -y )²+∑(y-y x)²
где ∑(y-y )² - общая сумма квадратов отклонений;
∑(y x -y )² - сумма квадратов отклонений, обусловленная регрессией («объясненная» или «факторная»);
∑(y-y x)² - остаточная сумма квадратов отклонений.
Долю дисперсии, объясняемую регрессией, в общей дисперсии результативного признака у характеризует коэффициент (индекс) детерминации R 2:

Коэффициент детерминации - квадрат коэффициента или индекса корреляции.

F-тест - оценивание качества уравнения регрессии - состоит в проверке гипотезы Но о статистической незначимости уравнения регрессии и показателя тесноты связи. Для этого выполняется сравнение фактического F факт и критического (табличного) F табл значений F-критерия Фишера. F факт определяется из соотношения значений факторной и остаточной дисперсий, рассчитанных на одну степень свободы:
,
где n - число единиц совокупности; m - число параметров при переменных х.
F табл - это максимально возможное значение критерия под влиянием случайных факторов при данных степенях свободы и уровне значимости a. Уровень значимости a - вероятность отвергнуть правильную гипотезу при условии, что она верна. Обычно a принимается равной 0,05 или 0,01.
Если F табл < F факт, то Н о - гипотеза о случайной природе оцениваемых характеристик отклоняется и признается их статистическая значимость и надежность. Если F табл > F факт, то гипотеза Н о не отклоняется и признается статистическая незначимость, ненадежность уравнения регрессии.
Для оценки статистической значимости коэффициентов регрессии и корреляции рассчитываются t-критерий Стьюдента и доверительные интервалы каждого из показателей. Выдвигается гипотеза Н о о случайной природе показателей, т.е. о незначимом их отличии от нуля. Оценка значимости коэффициентов регрессии и корреляции с помощью t-критерия Стьюдента проводится путем сопоставления их значений с величиной случайной ошибки:
; ; .
Случайные ошибки параметров линейной регрессии и коэффициента корреляции определяются по формулам:



Сравнивая фактическое и критическое (табличное) значения t-статистики - t табл и t факт - принимаем или отвергаем гипотезу Н о.
Связь между F-критерием Фишера и t-статистикой Стьюдента выражается равенством

Если t табл < t факт то H o отклоняется, т.е. a , b и r xy не случайно отличаются от нуля и сформировались под влиянием систематически действующего фактора х. Если t табл > t факт то гипотеза Н о не отклоняется и признается случайная природа формирования а, b или r xy .
Для расчета доверительного интервала определяем предельную ошибку D для каждого показателя:
Δ a =t табл ·m a , Δ b =t табл ·m b .
Формулы для расчета доверительных интервалов имеют следующий вид:
γ a =a±Δ a ; γ a =a-Δ a ; γ a =a+Δ a
γ b =b±Δ b ; γ b =b-Δ b ; γ b =b+Δ b
Если в границы доверительного интервала попадает ноль, т.е. нижняя граница отрицательна, а верхняя положительна, то оцениваемый параметр принимается нулевым, так как он не может одновременно принимать и положительное, и отрицательное значения.
Прогнозное значение y p определяется путем подстановки в уравнение регрессии y x =a+b·x соответствующего (прогнозного) значения x p . Вычисляется средняя стандартная ошибка прогноза m y x:
,
где
и строится доверительный интервал прогноза:
γ y x =y p ±Δ y p ; γ y x min=y p -Δ y p ; γ y x max=y p +Δ y p
где Δ y x =t табл ·m y x .

Пример решения

Требуется: 1. Для характеристики зависимости у от х рассчитать параметры следующих функций:
а) линейной;
б) степенной (предварительно нужно произвести процедуру линеаризации переменных, путем логарифмирования обеих частей);
в) показательной;
г) равносторонней гиперболы (так же нужно придумать как предварительно линеаризовать данную модель).
2. Оценить каждую модель через среднюю ошибку аппроксимации A и F-критерий Фишера.

Решение (Вариант №1)

Уравнение регрессии: у = 76,88 - 0,35х. С увеличением среднедневной заработной платы на 1 руб. доля расходов на покупку продовольственных товаров снижается в среднем на 0,35 %-ных пункта.
Рассчитаем линейный коэффициент парной корреляции:

Связь умеренная, обратная.
Определим коэффициент детерминации: r² xy =(-0.35)=0.127
Вариация результата на 12,7% объясняется вариацией фактора х. Подставляя в уравнение регрессии фактические значения х , определим теоретические (расчетные) значения y x . Найдем величину средней ошибки аппроксимации A :

В среднем расчетные значения отклоняются от фактических на 8,1%.
Рассчитаем F-критерий:

Полученное значение указывает на необходимость принять гипотезу Н 0 о случайной природе выявленной зависимости и статистической незначимости параметров уравнения и показателя тесноты связи.
1б. Построению степенной модели y=a·x b предшествует процедура линеаризации переменных. В примере линеаризация производится путем логарифмирования обеих частей уравнения:
lg y=lg a + b·lg x
Y=C+b·Y
где Y=lg(y), X=lg(x), C=lg(a).

Для расчетов используем данные табл. 1.3.
Таблица 1.3

Рассчитаем С иb:

C=Y -b·X = 1.7605+0.298·1.7370 = 2.278126
Получим линейное уравнение: Y=2.278-0.298·X
Выполнив его потенцирование, получим: y=10 2.278 ·x -0.298
Подставляя в данное уравнение фактические значения х, получаем теоретические значения результата. По ним рассчитаем показатели: тесноты связи - индекс корреляции p xy и среднюю ошибку аппроксимации A .

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

1в . Построению уравнения показательной кривой y=a·b x предшествует процедура линеаризации переменных при логарифмировании обеих частей уравнения:
lg y=lg a + x·lg b
Y=C+B·x
Для расчетов используем данные таблицы.

Значения параметров регрессии A и В составили:

A=Y -B·x = 1.7605+0.0023·54.9 = 1.887
Получено линейное уравнение: Y=1.887-0.0023x. Произведем потенцирование полученного уравнения и запишем его в обычной форме:
y x =10 1.887 ·10 -0.0023x = 77.1·0.9947 x
Тесноту связи оценим через индекс корреляции p xy:

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

Например, как изменится масса тела школьников 1-го класса (девочек или мальчиков), если рост их увеличится на 1 см. В этих целях применяется метод регрессионного анализа.

Наиболее часто метод регрессионного анализа применяется для разработки нормативных шкал и стандартов физического развития.

1. Определение регрессии . Регрессия - функция, позволяющая по средней величине одного признака определить среднюю величину другого признака, корреляционно связанного с первым.

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

2. Определение коэффициента регрессии . Коэффициент регрессии - абсолютная величина, на которую в среднем изменяется величина одного признака при изменении другого связанного с ним признака на установленную единицу измерения.
3. Формула коэффициента регрессии . R у/х = r ху x (σ у / σ x)
   где R у/х - коэффициент регрессии;
   r ху - коэффициент корреляции между признаками х и у;
   (σ у и σ x) - среднеквадратические отклонения признаков x и у.

   В нашем примере ;
   σ х = 4,6 (среднеквадратическое отклонение температуры воздуха в осенне-зимний период;
   σ у = 8,65 (среднеквадратическое отклонение числа инфекционно-простудных заболеваний).
   Таким образом, R у/х - коэффициент регрессии.
   R у/х = -0,96 х (4,6 / 8,65) = 1,8, т.е. при снижении среднемесячной температуры воздуха (x) на 1 градус среднее число инфекционно-простудных заболеваний (у) в осенне-зимний период будет изменяться на 1,8 случаев.

4. Уравнение регрессии . у = М у + R y/x (х - М x)
   где у - средняя величина признака, которую следует определять при изменении средней величины другого признака (х);
   х - известная средняя величина другого признака;
   R y/x - коэффициент регрессии;
   М х, М у - известные средние величины признаков x и у.

   Например, среднее число инфекционно-простудных заболеваний (у) можно определить без специальных измерений при любом среднем значении среднемесячной температуры воздуха (х). Так, если х = - 9°, R у/х = 1,8 заболеваний, М х = -7°, М у = 20 заболеваний, то у = 20 + 1,8 х (9-7) = 20 + 3,6 = 23,6 заболеваний.
   Данное уравнение применяется в случае прямолинейной связи между двумя признаками (х и у).

5. Назначение уравнения регрессии . Уравнение регрессии используется для построения линии регрессии. Последняя позволяет без специальных измерений определить любую среднюю величину (у) одного признака, если меняется величина (х) другого признака. По этим данным строится график - линия регрессии , по которой можно определить среднее число простудных заболеваний при любом значении среднемесячной температуры в пределах между расчетными значениями числа простудных заболеваний.
6. Сигма регрессии (формула) .
   где σ Rу/х - сигма (среднеквадратическое отклонение) регрессии;
   σ у - среднеквадратическое отклонение признака у;
   r ху - коэффициент корреляции между признаками х и у.

   Так, если σ у - среднеквадратическое отклонение числа простудных заболеваний = 8,65; r ху - коэффициент корреляции между числом простудных заболеваний (у) и среднемесячной температурой воздуха в осенне-зимний период (х) равен - 0,96, то

   

7. Назначение сигмы регрессии . Дает характеристику меры разнообразия результативного признака (у).

   Например, характеризует разнообразие числа простудных заболеваний при определенном значении среднемесячной температуры воздуха в осеннне-зимний период. Так, среднее число простудных заболеваний при температуре воздуха х 1 = -6° может колебаться в пределах от 15,78 заболеваний до 20,62 заболеваний.
   При х 2 = -9° среднее число простудных заболеваний может колебаться в пределах от 21,18 заболеваний до 26,02 заболеваний и т.д.

   Сигма регрессии используется при построении шкалы регрессии, которая отражает отклонение величин результативного признака от среднего его значения, отложенного на линии регрессии.

8. Данные, необходимые для расчета и графического изображения шкалы регрессии
   • коэффициент регрессии - R у/х;
   • уравнение регрессии - у = М у + R у/х (х-М x);
   • сигма регрессии - σ Rx/y
9. Последовательность расчетов и графического изображения шкалы регрессии .
   • определить коэффициент регрессии по формуле (см. п. 3). Например, следует определить, насколько в среднем будет меняться масса тела (в определенном возрасте в зависимости от пола), если средний рост изменится на 1 см.
   • по формуле уравнения регрессии (см п. 4) определить, какой будет в среднем, например, масса тела (у, у 2 , у 3 ...)* для определеного значения роста (х, х 2 , х 3 ...).
     ________________
     * Величину "у" следует рассчитывать не менее чем для трех известных значений "х".

     При этом средние значения массы тела и роста (М х, и М у) для определенного возраста и пола известны

   • вычислить сигму регрессии, зная соответствующие величины σ у и r ху и подставляя их значения в формулу (см. п. 6).
   • на основании известных значений х 1 , х 2 , х 3 и соответствующих им средних значений у 1 , у 2 у 3 , а также наименьших (у - σ rу/х)и наибольших (у + σ rу/х) значений (у) построить шкалу регрессии.

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

     Затем в соответствующих точках у 1 , y 2 , y 3 отмечаются числовые значения сигмы регрессии, т.е. на графике находят наименьшее и наибольшее значения у 1 , y 2 , y 3 .

10. Практическое использование шкалы регрессии . Разрабатываются нормативные шкалы и стандарты, в частности по физическому развитию. По стандартной шкале можно дать индивидуальную оценку развития детей. При этом физическое развитие оценивается как гармоничное, если, например, при определенном росте масса тела ребенка находится в пределах одной сигмы регрессии к средней расчетной единице массы тела - (у) для данного роста (x) (у ± 1 σ Ry/x).

    Физическое развитие считается дисгармоничным по массе тела, если масса тела ребенка для определенного роста находится в пределах второй сигмы регрессии: (у ± 2 σ Ry/x)

    Физическое развитие будет резко дисгармоничным как за счет избыточной, так и за счет недостаточной массы тела, если масса тела для определенного роста находится в пределах третьей сигмы регрессии (у ± 3 σ Ry/x).

По результатам статистического исследования физического развития мальчиков 5 лет известно, что их средний рост (х) равен 109 см, а средняя масса тела (у) равна 19 кг. Коэффициент корреляции между ростом и массой тела составляет +0,9, средние квадратические отклонения представлены в таблице.

Требуется:

• рассчитать коэффициент регрессии;
• по уравнению регрессии определить, какой будет ожидаемая масса тела мальчиков 5 лет при росте, равном х1 = 100 см, х2 = 110 см, х3= 120 см;
• рассчитать сигму регрессии, построить шкалу регрессии, результаты ее решения представить графически;
• сделать соответствующие выводы.

Условие задачи и результаты ее решения представлены в сводной таблице.

Таблица 1

Решение .

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

1. Власов В.В. Эпидемиология. - М.: ГЭОТАР-МЕД, 2004. - 464 с.
2. Лисицын Ю.П. Общественное здоровье и здравоохранение. Учебник для вузов. - М.: ГЭОТАР-МЕД, 2007. - 512 с.
3. Медик В.А., Юрьев В.К. Курс лекций по общественному здоровью и здравоохранению: Часть 1. Общественное здоровье. - М.: Медицина, 2003. - 368 с.
4. Миняев В.А., Вишняков Н.И. и др. Социальная медицина и организация здравоохранения (Руководство в 2 томах). - СПб, 1998. -528 с.
5. Кучеренко В.З., Агарков Н.М. и др.Социальная гигиена и организация здравоохранения (Учебное пособие) - Москва, 2000. - 432 с.
6. С. Гланц. Медико-биологическая статистика. Пер с англ. - М., Практика, 1998. - 459 с.

1. Впервые термин «регрессия» был введен основателем биометрии Ф. Гальтоном (XIX в.), идеи которого были развиты его последователем К. Пирсоном.

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

Признак - это основная отличительная черта, особенность изучаемого явления или процесса.

Результативный признак - исследуемый показатель.

Факторный признак - показатель, влияющий на значение результативного признака.

Целью регрессионного анализа является оценка функциональной зависимости среднего значения результативного признака (у ) от факторных (х 1 , х 2 , …, х n ), выражаемой в виде уравнения регрессии

у = f (x 1 , х 2 , …, х n ). (6.1)

Различают два вида регрессии: парную и множественную.

Парная (простая) регрессия - уравнение вида:

у = f (x ). (6.2)

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

Регрессионный анализ включает в себя следующие этапы:

· определение типа функции;

· определение коэффициентов регрессии;

· расчет теоретических значений результативного признака;

· проверку статистической значимости коэффициентов регрессии;

· проверку статистической значимости уравнения регрессии.

Множественная регрессия - уравнение вида:

у = f (x 1 , х 2 , …, х n ). (6.3)

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

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

По направлению связи регрессия делится на:

· прямую регрессию, возникающую при условии, что с увеличением или уменьшением независимой величины «х» значения зависимой величины «у» также соответственно увеличиваются или уменьшаются;

· обратную регрессию, возникающую при условии, что с увеличением или уменьшением независимой величины «х» зависимая величина «у» соответственно уменьшается или увеличивается.

Для характеристики связей используют следующие виды уравнений парной регрессии:

· у=a+bx – линейное;

· y=e ax + b – экспоненциальное;

· y=a+b/x – гиперболическое;

· y=a+b 1 x+b 2 x 2 – параболическое;

· y=ab x – показательное и др.

где a, b 1 , b 2 - коэффициенты (параметры) уравнения; у - результативный признак; х - факторный признак.

3. Построение уравнения регрессии сводится к оценке его коэффициентов (параметров), для этого используют метод наименьших квадратов (МНК).

Метод наименьших квадратов позволяет получить такие оценки параметров, при которых сумма квадратов отклонений фактических значений результативного признака «у »от теоретических «у х » минимальна, то есть

Параметры уравнения регрессии у=a+bх по методу наименьших квадратов оцениваются с помощью формул:

где а – свободный коэффициент, b - коэффициент регрессии, показывает на сколько изменится результативный признак «y » при изменении факторного признака «x » на единицу измерения.

4. Для оценки статистической значимости коэффициентов регрессии используется -критерий Стьюдента.

Схема проверки значимости коэффициентов регрессии:

1) Н 0: a =0, b =0 - коэффициенты регрессии незначимо отличаются от нуля.

Н 1: a≠ 0, b≠ 0 - коэффициенты регрессии значимо отличаются от нуля.

2) р =0,05 – уровень значимости.

где m b , m a - случайные ошибки:

; . (6.7)

4) t табл (р; f ),

где f =n-k- 1 - число степеней свободы (табличное значение), n - число наблюдений, k х».

5) Если , то отклоняется, т.е. коэффициент значимый.

Если , то принимается, т.е. коэффициент незначимый.

5. Для проверки правильности построенного уравнения регрессии применяется критерий Фишера.

Схема проверки значимости уравнения регрессии:

1) Н 0: уравнение регрессии незначимо.

Н 1: уравнение регрессии значимо.

2) р =0,05 – уровень значимости.

3) , (6.8)

где - число наблюдений; k - число параметров в уравнении при переменных «х» ; у - фактическое значение результативного признака; y x - теоретическое значение результативного признака; - коэффициент парной кореляции.

4) F табл (р; f 1 ; f 2 ),

где f 1 =k, f 2 =n-k-1- число степеней свободы (табличные значения).

5) Если F расч >F табл , то уравнение регрессии подобрано верно и может применяться на практике.

Если F расч , то уравнение регрессии подобрано неверно.

6. Основным показателем, отражающим меру качества регрессионного анализа, является коэффициент детерминации (R 2).

Коэффициент детерминации показывает, какая доля зависимой переменной «у » учтена в анализе и вызвана влиянием на нее факторов, включенных в анализ.

Коэффициент детерминации (R 2) принимает значения в промежутке . Уравнение регрессии является качественным, если R 2 ≥0,8.

Коэффициент детерминации равен квадрату коэффициента корреляции, т.е.

Пример 6.1. По следующим данным построить и проанализировать уравнение регрессии:

Решение.

1) Вычислить коэффициент корреляции: . Связь между признаками прямая и умеренная.

2) Построить уравнение парной линейной регрессии.

2.1) Составить расчетную таблицу.

№	Х	у	Ху	х 2	у х		(у-у х) 2
					55,89	47,54	65,70
					45,07	15,42	222,83
					54,85	34,19	8,11
					51,36	5,55	11,27
					42,28	45,16	13,84
					47,69	1,71	44,77
					45,86	9,87	192,05
Сумма						159,45	558,55
Среднее				77519,6		22,78	79,79	2990,6

,

Уравнение парной линейной регрессии: у х =25,17+0,087х.

3) Найти теоретические значения «у x » путем подстановки в уравнение регрессии фактических значений «х ».

4) Построить графики фактических «у» и теоретических значений «у х » результативного признака (рисунок 6.1):r xy =0,47) и небольшим числом наблюдений.

7) Вычислить коэффициент детерминации: R 2 =(0,47) 2 =0,22. Построенное уравнение некачественное.

Т.к. вычисления при проведении регрессионного анализа достаточно объемные, рекомендуется пользоваться специальными программами («Statistica 10», SPSS и др.).

На рисунке 6.2 приведена таблица с результатами регрессионного анализа, проведенного с помощью программы «Statistica 10».

Рисунок 6.2. Результаты регрессионного анализа, проведенного с помощью программы «Statistica 10»

5. Литература:

1. Гмурман В.Е. Теория вероятностей и математическая статистика: Учеб. пособие для вузов / В.Е. Гмурман. - М.: Высшая школа, 2003. - 479 с.

2. Койчубеков Б.К. Биостатистика: Учебное пособие. - Алматы: Эверо, 2014. - 154 с.

3. Лобоцкая Н.Л. Высшая математика. / Н.Л. Лобоцкая, Ю.В. Морозов, А.А. Дунаев. - Мн.: Высшая школа, 1987. - 319 с.

4. Медик В.А., Токмачев М.С., Фишман Б.Б. Статистика в медицине и биологии: Руководство. В 2-х томах / Под ред. Ю.М. Комарова. Т. 1. Теоретическая статистика. - М.: Медицина, 2000. - 412 с.

5. Применение методов статистического анализа для изучения общественного здоровья и здравоохранения: учебное пособие / ред. Кучеренко В.З. - 4-е изд., перераб. и доп. – М.: ГЭОТАР - Медиа, 2011. - 256 с.

Регрессионный и корреляционный анализ – статистические методы исследования. Это наиболее распространенные способы показать зависимость какого-либо параметра от одной или нескольких независимых переменных.

Ниже на конкретных практических примерах рассмотрим эти два очень популярные в среде экономистов анализа. А также приведем пример получения результатов при их объединении.

Регрессионный анализ в Excel

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

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

Регрессия бывает:

• линейной (у = а + bx);
• параболической (y = a + bx + cx 2);
• экспоненциальной (y = a * exp(bx));
• степенной (y = a*x^b);
• гиперболической (y = b/x + a);
• логарифмической (y = b * 1n(x) + a);
• показательной (y = a * b^x).

Рассмотрим на примере построение регрессионной модели в Excel и интерпретацию результатов. Возьмем линейный тип регрессии.

Задача. На 6 предприятиях была проанализирована среднемесячная заработная плата и количество уволившихся сотрудников. Необходимо определить зависимость числа уволившихся сотрудников от средней зарплаты.

Модель линейной регрессии имеет следующий вид:

У = а 0 + а 1 х 1 +…+а к х к.

Где а – коэффициенты регрессии, х – влияющие переменные, к – число факторов.

В нашем примере в качестве У выступает показатель уволившихся работников. Влияющий фактор – заработная плата (х).

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

Активируем мощный аналитический инструмент:

После активации надстройка будет доступна на вкладке «Данные».

Теперь займемся непосредственно регрессионным анализом.

В первую очередь обращаем внимание на R-квадрат и коэффициенты.

R-квадрат – коэффициент детерминации. В нашем примере – 0,755, или 75,5%. Это означает, что расчетные параметры модели на 75,5% объясняют зависимость между изучаемыми параметрами. Чем выше коэффициент детерминации, тем качественнее модель. Хорошо – выше 0,8. Плохо – меньше 0,5 (такой анализ вряд ли можно считать резонным). В нашем примере – «неплохо».

Коэффициент 64,1428 показывает, каким будет Y, если все переменные в рассматриваемой модели будут равны 0. То есть на значение анализируемого параметра влияют и другие факторы, не описанные в модели.

Коэффициент -0,16285 показывает весомость переменной Х на Y. То есть среднемесячная заработная плата в пределах данной модели влияет на количество уволившихся с весом -0,16285 (это небольшая степень влияния). Знак «-» указывает на отрицательное влияние: чем больше зарплата, тем меньше уволившихся. Что справедливо.



Корреляционный анализ в Excel

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

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

Коэффициент корреляции обозначается r. Варьируется в пределах от +1 до -1. Классификация корреляционных связей для разных сфер будет отличаться. При значении коэффициента 0 линейной зависимости между выборками не существует.

Рассмотрим, как с помощью средств Excel найти коэффициент корреляции.

Для нахождения парных коэффициентов применяется функция КОРРЕЛ.

Задача: Определить, есть ли взаимосвязь между временем работы токарного станка и стоимостью его обслуживания.

Ставим курсор в любую ячейку и нажимаем кнопку fx.

1. В категории «Статистические» выбираем функцию КОРРЕЛ.
2. Аргумент «Массив 1» - первый диапазон значений – время работы станка: А2:А14.
3. Аргумент «Массив 2» - второй диапазон значений – стоимость ремонта: В2:В14. Жмем ОК.

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

Для корреляционного анализа нескольких параметров (более 2) удобнее применять «Анализ данных» (надстройка «Пакет анализа»). В списке нужно выбрать корреляцию и обозначить массив. Все.

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

Корреляционно-регрессионный анализ

На практике эти две методики часто применяются вместе.

Пример:

Теперь стали видны и данные регрессионного анализа.

Регрессионный анализ является одним из самых востребованных методов статистического исследования. С его помощью можно установить степень влияния независимых величин на зависимую переменную. В функционале 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 довольно просто составить таблицу регрессионного анализа. Но, работать с полученными на выходе данными, и понимать их суть, сможет только подготовленный человек.