Как сделать однофакторный дисперсионный анализ в excel

Обновлено: 06.01.2025

Для рассмотрения однофакторного дисперсионного анализа в MS Excel решим следующий пример.

Пример 3.2.В таблице 3 приведены данные по объемам работ, выполненных на посадке декоративных кустарников за смену для четырех бригад.

Номер бригады	Объем выполненной работы	Групповое среднее	Выборочная смещенная дисперсия
140, 144, 142, 145	142,75	3,688
150, 149, 152, 152	150,75	1,688
148, 149, 146, 147	147,50	1,25
150, 155, 154, 152	152,75	3,688

Проверить гипотезу дисперсионного анализа H₀ о равенстве средних: m₁ = m₂ = m₃ = m₄.

Решение. Для проверки гипотезы H₀ вычислим суммы Q₁ и Q₂ .

Общее выборочное среднее равно

Вычислим статистику Фишера:

По таблицам распределения Фишера для a = 0,05 и степеней свободы k₁ =3, k₂ = 12 найдем критическое значение F_кр =3,49. Так как F> F_кр, то гипотезу H₀ отклоняем, т.е. считаем, что объем ежедневной выработки зависит от работающей бригады. Оценим степень этой зависимости с помощью коэффициента детерминации. Для этого вычислим Q :

Контроль: Q = Q₁+Q₂ = 228,688 + 41,25 = 269,9 – верно!

По формуле (48) получим: , это означает, что 84,7% общей вариации (изменчивости) ежесменного объема выработки связано с работающей бригадой.

В MS Excel для проведения однофакторного дисперсионного анализа использует­ся процедура Однофакторный дисперсионный анализ.

Для проведения дисперсионного анализа необходимо:

•ввести данные в таблицу, так чтобы в каждом столбце оказались данные, соот­ветствующие одному значению исследуемого фактора, а столбцы располагались в порядке возрастания (убывания) величины исследуемого фактора,

•выполнить команду Сервис > Анализ данных;

•в появившемся диалоговом окне Анализ данныхв списке Инструментыанализа выбрать процедуру Однофакторный дисперсионный анализ, указав курсором мыши и щелкнув левой кнопкой мыши. Затем нажать кнопку ОК;

•в разделе Группировка переключатель установить в положение по столбцам;

•нажать кнопку ОК.

Результаты анализа. Выходной диапазон будет включать в себя результаты дис­персионного анализа: средние, дисперсии, критерий Фишера и другие показатели.

Однофакторный дисперсионный анализ
ИТОГИ
Группы	Счет	Сумма	Среднее	Дисперсия
Строка 1	142,75	4,916667
Строка 2	150,75	2,25
Строка 3	147,5	1,666667
Строка 4	152,75	4,916667
Дисперсионный анализ
Источник вариации	SS	df	MS	F	P-Значение	F критическое
Между группами	228,6875	76,22917	22,17576	3,48104E-05	3,490294821
Внутри групп	41,25	3,4375
Итого	269,9375

Как видно из таблицы, общая сумма квадратов SS (Q) = 269,938 разбита на компоненты: Q₁= 228,688, обусловленную различием средних значений между группами и Q₂= 41,25, обусловленную внутригрупповой изменчивостью

где Q- общая сумма квадратов отклонений наблюдений от общего среднего, Q₁- сумма квадратов отклонений выборочных средних от общего среднего, Q₂- сумма квадратов отклонений наблюдений от групповых средних.

Заметим, что MS в этой таблице есть средний квадрат, равный SS, деленная на число степеней свободы (df).

MS₁ = Q₁/ df₁ = 228,688 / 3 = 76,229

MS₂ = Q₂/ df₂ = 41,25 / 12 = 3,438

F = MS₁ / MS₂ = 76,229 / 3,438 = 22,176

Внутригрупповая изменчивость (SS) обычно называется остаточной компонентой или дисперсией ошибки. Это означает, что обычно при проведении эксперимента она не может быть предсказана или объяснена. С другой стороны, SS эффект (или компоненту дисперсии между группами) можно объяснить различием между средними значениями в группах. Иными словами, принадлежность к некоторой группе объясняет межгрупповую изменчивость, т.к. нам известно, что эти группы обладают разными средними значениями.
Интерпретация результатов. Влияние исследуемого фактора определяется по величине значимости критерия Фишера, которая находится в таблице Дисперси­онный анализ на пересечении строки Между группами и столбца Р-Значение. В случаях, когда Р-Значение < 0,05, критерий Фишера значим, и влияние исследуемого фак­тора можно считать доказанным.

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

Опора деревянной одностоечной и способы укрепление угловых опор: Опоры ВЛ - конструкции, предназначен­ные для поддерживания проводов на необходимой высоте над землей, водой.

Организация стока поверхностных вод: Наибольшее количество влаги на земном шаре испаряется с поверхности морей и океанов (88‰).

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

Пусть имеется случайная переменная Y , значения которой мы можем измерять. Исследователь предполагает, что эта переменная зависит от фактора, значения которого мы можем контролировать, т.е. задавать с требуемой точностью. Покажем как методом дисперсионного анализа ( ANOVA ) проверить гипотезу о наличии или отсутствии влияния указанного фактора на зависимую переменную Y .

Disclaimer : Эта статья – о применении MS EXCEL для целей Дисперсионного анализа, поэтому данную статью не стоит рассматривать, как пересказ главы из учебника по статистике. Статья не обладает ни полнотой, ни строгостью изложения положений статистической науки. Теоретические отступления приведены лишь из соображения логики изложения. Использование данной статьи для изучения теории Дисперсионного анализа – плохая идея. Хорошая идея - найти в этой статье формулы MS EXCEL для проведения Дисперсионного анализа.

Перед прочтением этой статьи рекомендуется освежить в памяти следующие понятия статистики:

• Проверка статистических гипотез ;
• Дисперсия и среднее значение ;
• Распределение Фишера и квантили этот распределения;
• F-тест ;
• Блочные диаграммы .

Дисперсионный анализ (ANOVA, ANalysis Of VAriance) позволяет проверить гипотезу о равенстве нескольких средних значений выборок (взяты ли выборки из одного распределения или из разных распределений).

Примечание : В статье Двухвыборочный t-тест с одинаковыми дисперсиями решалась подобная задача о сравнении средних значений 2-х распределений. Здесь рассмотрим более общую задачу – будем одновременно сравнивать несколько средних значений выборок (более 2-х).

Чтобы пояснить суть дисперсионного анализа приведем пример.

Сгенерируем 2 выборки: первую возьмем из нормального распределения со средним значением равно 4, вторую со средним - 5 ( стандартные отклонения одинаковые). Сказать, сильно ли они различаются или нет, невозможно, пока мы не знаем разброс (стандартное отклонение) значений в каждой выборке относительно среднего. Если зададим в распределениях небольшой разброс, скажем 0,1, то в каждой выборке получим близкое к нему значение. В этом случае, очевидно, что наблюдаемое различие между средними равное 1 (5-4=1) – значительное и можно говорить, что выборки взяты из разных распределений (см. картинку ниже).

Если же разброс в выборках составляет около 2, то наблюжаемое различие средних значений выборок равное 1 уже не кажется таким значительным.

В дисперсионном анализе эти значения выборок представляют собой значения зависимой переменной Y, а выборки берутся при различных уровнях фактора Х. В первом случае для того дать ответ о зависимости Y от фактора Х, даже не нужно проводить дисперсионный анализ : из диаграммы итак очевидно, что отличие между средними значениями выборок (5-4=1), гораздо больше разброса внутри выборки (0,1). Следовательно, очевидно, что выборки взяты из различных генеральных совокупностей (с различными распределениями), которые соответствуют разным значениям Х.

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

В конце статьи мы определим математически точно условие «значимости» различия средних выборок .

Немного теории

Примечание : Пользователи, уверенно владеющие методом дисперсионного анализа , могут перейти непосредственно к формулам MS EXCEL .

Пусть необходимо исследовать зависимость некой количественной случайной величины Y от одной переменной, которую мы можем контролировать (устанавливать их значения с требуемой точностью). В теории дисперсионного анализа переменная Y называется зависимой переменной ( dependent или response variable ), а переменные, от которых исследуется зависимость переменной Y, называются факторами или зависимыми переменными ( factors или dependent variables ).

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

Примечание : Случай зависимости от 2-х факторов рассмотрен в статье Двухфакторный дисперсионный анализ .

Отдельные, заданные значения фактора называются уровнями ( levels ) или испытаниями ( treatments ).

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

Целью эксперимента является исследование влияния различных уровней фактора на переменную Y. В самом деле, так как фактор нами контролируется, то у нас есть возможность сделать несколько наблюдений (измерений) величины Y при определенном заданном уровне фактора. Зачем их делать несколько, ведь значения Y должны получиться одинаковыми? Нет. Так как мы предполагаем, что на переменную Y может влиять множество неконтролируемых нами факторов, то мы будем получать в ходе каждого измерения несколько отличающиеся значения Y. Единственное, что мы можем сделать, это обеспечить одинаковые условия проведения эксперимента для всех измерений.

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

Уровни фактора (treatments) будем обозначать буквой j (j изменяется от 1 до a ). Каждому уровню фактора соответствует одна выборка (состоит из нескольких измерений). Предполагается, что дисперсии всех выборок σ 2 неизвестны, но равны между собой.

Непосредственно измеренные значения Y при заданном уровне фактора j будем обозначать y _ij . Количество наблюдений для разных уровней факторов может быть одинаковым или отличаться.

Примечание : Чем больше количество измерений/наблюдений (т.е. размер выборки) мы сделаем, тем более обоснованным будет наш статистический вывод о равенстве средних значений этих выборок.

В тексте статьи будем рассматривать только равные выборки, их размер обозначим n. В Этом случае общее количество измерений N=n*a.

Примечание : В файле примера выполнены вычисления для обоих случаев (равные и неравные по размеру выборки).

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

Остается выяснить, что значит средние выборок «заметно отличаются».

Стандартные обозначения дисперсионного анализа

Общий подход при проведении Дисперсионного анализа: проверить значимость различия средних значений выборок, сравнив один источник разброса (проверяемый фактор) с другим источником разброса (обоснованный лишь случайностью выборок/ случайным воздействием неконтролируемых факторов):

Введя нижеуказанные обозначения, выражение можно записать в компактной форме:

Эти общеупотребительные обозначения расшифровываются следующим образом: SS – это сокращение английского выражения Sum of Squares (сумма квадратов отклонений от среднего), T – это сокращение от Total (Общее среднее), А – это фактор А, E – это сокращение от Error (ошибка).

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

где, – общее среднее:

Обратите внимание, что квадраты отклонений имеют размерность дисперсии , т.е. меры изменчивости. Теперь очевидно, что левая часть выражения представляет собой общую изменчивость (разброс) каждого из наблюдений относительно общего среднего. Эта общая изменчивость (SST) состоит из двух частей: SSA - изменчивость, объясненная нашей моделью (междувыборочная изменчивость, основанная на различиях в уровнях фактора) и из SSE - ошибка модели (внутривыборочная изменчивость, сумма разбросов наблюдений внутри каждой выборки).

Также в дисперсионном анализе используется понятие среднего квадрата отклонений (Mean Square), т.е. MS. Соответственно для SST имеем MST=SST/(N-1), для SSA имеем MSA=SSA/(n-1), для ошибки модели SSE имеем MSE=SSE/(a(n-1)).

MS имеет смысл средней изменчивости на 1 наблюдение (с некоторой поправкой). Эта поправка отражает тот факт, что MS должна вычисляться не делением SS на соответствующее количество наблюдений, а на число степеней свободы (degrees of freedom, DF). Например, чтобы вычислить MST, мы из N (общего количества наблюдений) должны вычесть 1, т.к. в выражении SST присутствует одно среднее значение (аналогично тому, как мы делали при вычислении дисперсии выборки ). Одна степень свободы теряется при вычислении среднего – это видно в формуле выражения для SST.

В SSA мы имеем уже а средних значений (равно количеству уровней фактора, т.е. количеству выборок). Поэтому, из общего количества наблюдений a *n необходимо вычесть а – количество вычисленных средний значений выборок (an-a=a(n-1)).

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

Идем дальше. Оказывается, если нулевая справедлива , то:

• случайная величина MSА представляет собой оценку σ 2
• отношение MSА/MSE имеет распределение Фишера с а-1 и a(n-1) степенями свободы.

MSА/MSE обозначают как F ₀ ( тестовая статистика для однофакторного дисперсионного анализа ).

Примечание : Можно показать, что MSE также представляет собой оценку σ 2 дисперсии выборок ( математическое ожидание случайной величины MSE равно σ 2 ). Но, в отличие от MSА, MSE представляет собой оценку σ 2 вне зависимости от того, справедлива ли нулевая справедлива или нет.

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

Задача

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

Пусть предполагается, что инженер исследует влияние некой добавки на прочность нити Y. Он решает провести эксперимент:

• Использовать 4 различных концентраций добавки (1%; 5%; 7% и 10%). Прим .: эти значения концентраций не участвуют в расчетах.
• Провести по 6 (n) измерений прочности нити для каждой концентрации добавки.

Таким образом, имеется только 1 фактор (концентрация добавки). Фактор имеет 4 (а=4) различные уровня (j=1; 2; 3; 4). Всего у нас имеется 24 (N=4*6) измерения.

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

О рандомизированном эксперименте

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

Также не годится подход, когда используются 4 одинаковых, но отдельных реактора для каждого эксперимента: первый реактор для концентрации 1%, второй - для 5% и т.д. Однако, индивидуальные особенности каждого реактора (период эксплуатации, воздействие ремонтов, незначительное различие конструкции допущенное при изготовлении) могут сказаться на разбросе выборки.

То есть для постановки правильного эксперимента требуется исключить влияние конкретного устройства (experimental unit) на значение переменной Y.

Обычно используют полностью рандомизированный эксперимент (completely randomized experimental design) – это когда для каждого испытания ( treatment ) выбираются образцы экспериментального устройства выбираются случайным способом.

Например, для нашего случая можно предложить следующую схему полностью рандомизированного эксперимента : мы случайным образом выбираем из большого количества одинаковых ректоров (например, из 1000) 6 ректоров для наблюдений первого уровня фактора (для каждого наблюдения 1 реактор), 6 – для второго и т.д. Всего 24 ректора из 1000.

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

Или еще проще: каждому из 24 измерений случайным образом (вне зависимости от уровня фактора) назначаем один из 4 одинаковых реакторов. Каждый реактор участвует в 6 измерениях.

Примечание : Т.к. не всегда представляется возможным иметь в распоряжении множество одинаковых экспериментальных устройств для проведения полностью рандомизированного эксперимента , то в статистике часто используются и другие формы проведения экспериментов, например, блочный рандомизированный эксперимент ( randomized block design ).

Вычисления в MS EXCEL

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

Сначала изучим статистические характеристики набора данных, построив блочную диаграмму .

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

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

Сделаем вспомогательные вычисления по формулам из предыдущего раздела статьи: вычислим средние значения каждой выборки, общее среднее, суммы квадратов SS, степени свободы, MSE, MSA.

Тестовая статистика вычисляется по формуле:

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

В нашем случае среднее значение F -распределения с 3 и 20 степенями свободы равно 1,11. Если вычисленное нами значение F ₀ «значительно» превосходит это значение, то это является маловероятным событием и у нас есть основания для отклонения нулевой гипотезы .

В нашей задаче F ₀ равно 5,3358. «Значительно» это или нет? Для ответа на этот вопрос вычислим вероятность этого события (т.е. вероятность события, что случайная величина F, имеющая распределение Фишера с указанными степенями свободы, примет значение 5,3358 или более). Эта вероятность не высока =0,0072. Этого и следовало ожидать, т.к. 5,3358 значительно больше среднего значения 1,11. В MS EXCEL эту вероятность можно вычислить по формуле:

0,0072 – это так называемое p -значение , т.е. вероятность, что статистика F ₀ примет вычисленное значение.

Примечание : Обычно под F ₀ понимается как сама случайная величина - тестовая статистика F ₀ , так и ее конкретное значение F ₀ , вычисленное из условий задачи (исходных данных).

Теперь сравним p -значение с уровнем значимости (обычно 0,05 или 0,01). Если p -значение меньше уровня значимости , то нулевую гипотезу отклоняют.

В начале статьи мы задались вопросом о том, как математически точно определить «значимое» отличие средних значений выборок (чтобы мы могли сделать вывод, что уровни фактора влияют на значение переменной Y). Теперь мы можем утверждать, что средние выборок статистически значимо отличаются, если вычисленное p -значение меньше заданного уровня значимости .

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

Примечание : Однофакторный дисперсионный анализ можно также выполнить с помощью надстройки Пакет анализа . Об этом см. в статье здесь .

В диапазоне В8:С16 файла примера введены 6 выборок, представляющие значения зависимой переменной Y ( количество дефектов ), измеренные при 3-х уровнях фактора А (метод обработки поверхности ) и 2-х уровнях фактора В (Исходный материал ).

Необходимо методом дисперсионного анализа ответить на 2 вопроса:

• взаимодействуют ли факторы между собой?
• влияют ли уровни факторов А и В на значения зависимой переменной Y?

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

Чтобы ответить на поставленные вопросы воспользуемся надстройкой Пакет анализа . В этой надстройке имеется инструмент двухфакторный дисперсионный анализ с повторениями (Two-Factor With Replication).

После выбора этого инструмента откроется окно, в котором требуется заполнить следующие поля (см. файл примера лист Надстройка ):

Заполнив все поля диалогового можно нажимать кнопку ОК. Расчет будет выполнен в двух таблицах: ИТОГИ и Дисперсионный анализ.

Тот же результат можно получить с помощью формул (см. файл примера, столбцы A:G) .

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

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

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

Регрессионный анализ в 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 предприятиях была проанализирована среднемесячная заработная плата и количество уволившихся сотрудников. Необходимо определить зависимость числа уволившихся сотрудников от средней зарплаты.

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

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

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

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

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

1. Нажимаем кнопку «Офис» и переходим на вкладку «Параметры Excel». «Надстройки».
2. Внизу, под выпадающим списком, в поле «Управление» будет надпись «Надстройки Excel» (если ее нет, нажмите на флажок справа и выберите). И кнопка «Перейти». Жмем.
3. Открывается список доступных надстроек. Выбираем «Пакет анализа» и нажимаем ОК.

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

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

1. Открываем меню инструмента «Анализ данных». Выбираем «Регрессия».
2. Откроется меню для выбора входных значений и параметров вывода (где отобразить результат). В полях для исходных данных указываем диапазон описываемого параметра (У) и влияющего на него фактора (Х). Остальное можно и не заполнять.
3. После нажатия ОК, программа отобразит расчеты на новом листе (можно выбрать интервал для отображения на текущем листе или назначить вывод в новую книгу).

В первую очередь обращаем внимание на 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) удобнее применять «Анализ данных» (надстройка «Пакет анализа»). В списке нужно выбрать корреляцию и обозначить массив. Все.

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

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

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

1. Строим корреляционное поле: «Вставка» - «Диаграмма» - «Точечная диаграмма» (дает сравнивать пары). Диапазон значений – все числовые данные таблицы.
2. Щелкаем левой кнопкой мыши по любой точке на диаграмме. Потом правой. В открывшемся меню выбираем «Добавить линию тренда».
3. Назначаем параметры для линии. Тип – «Линейная». Внизу – «Показать уравнение на диаграмме».
4. Жмем «Закрыть».

Читайте также:

  
• Bizhub 185 программа для сканирования
  
• Virtual hardware virtual tool выдается ошибка driver failed to load
  
• Программы для экономии трафика на айфон
  
• Mercury transmit after effects что это
  
• Outlook web app нельзя отправлять почту