3.2.5 Функции и инструменты ms excel, предназначенные для расчёта коэффициентов ковариации, корреляции и детерминации

Наиболее важные и часто употребляемые аналитические характеристики достаточно просто определяются в рамках программы MS EXCEL. Так сходные по своим аргументам функции КОВАР и КОРРЕЛ (последняя представлена на рис. 2.3) определяют соответственно коэффициенты ковариации и корреляции по формулам (2.1) и (2.3). Для этого достаточно вставить массивы переменных и нажать ОК. Ту же задачу, что и функция КОРРЕЛ (имея те же аргументы), решает функция ПИРСОН. Коэффициент парной линейной корреляции Пирсона в данном случае определяется по формуле (2.3), соответствующей для случая выборочных совокупностей и формуле (2.4). Таким образом, для выборок результаты расчётов по формулам КОРРЕЛ и ПИРСОН совпадают.

Рис. 2.3. Аргументы функции КОРРЕЛ

В MS EXCEL имеются также инструменты анализа «Ковариация» и «Корреляция», сходные своими диалоговыми окнами (последнее представлено на рис. 2.4). Они служат той же цели, что и соответствующие функции, но в отличие от функций в данном случае задаётся общий входной интервал с разделением анализируемых переменных по строкам или столбцам. При этом коэффициенты корреляции (или ковариации) рассчитываются для всех сочетаний задаваемых строк или столбцов. Таким образом, формируется так называемая «матрица корреляций» (или аналогично - «матрица ковариаций»).

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

Рис. 2.4. Диалоговое окно инструмента анализа «Корреляция»

Статистическая функция КВПИРСОН (рис. 2.5) позволяет определять квадрат коэффициента Пирсона r² для парной линейной зависимости. Он представляет собой коэффициент детерминации для такого рода зависимости. Практическое использование коэффициента детерминации, показывающего степень аппроксимации экспериментальных точек аналитической зависимостью, особенно эффективно, когда эти аналитические зависимости можно оперативно изменять. Тогда появляется возможность выбрать такую зависимость, для которой установлен максимальный коэффициент детерминации. Эти возможности открываются при использовании графических опций MS EXCEL (глава 5).

Рис. 2.5. Аргументы функции КВПИРСОН

3.3 Дисперсионный анализ

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

- исходные случайные величины (факторы) распределены по нормальному закону,

- дисперсии экспериментальных данных («откликов») одинаковы для всех условий эксперимента (на разных уровнях изучаемого фактора).

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

3.3.1. Виды классического дисперсионного анализа

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

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

Например, на автоматической ли­нии несколько станков одного класса точности (дающие одинаковую дисперсию размеров обрабатываемых деталей) работают параллельно. То есть в данном случае фактором, влияющим на размер деталей, являются станки, на которых эти детали изготавливаются. Предварительные результаты показали, что имеется некоторое различие средних размеров деталей, обработанных на разных станках. Для правильного планирования последующей обработки детали исследователя интересует, насколько это различие существенно. Будет ли влиять на размер переход от одного станка к другому? Это различие может являться просто результатом естественного рассеивания размеров, т.е. их дисперсии. Дисперсия среднего значения выборки объемаn определяется из дисперсии единичного значения по формуле

(3.1)

А может быть, это различие средних размеров объективно существует, т.е. будет сохраняться при сравнении выборок даже бесконечного объёма. Правильное решение этой и подобных задач дисперсионного анализа требует трудоёмких вычислений, но современные программные средства позволяют их избежать. Такие возможности представлены, в частности, и в рамках программы MS EXCEL (см. § 3.3).

При однофакторном дисперсионном анализе по соотношению между составляющей дисперсии y_i, вызванной действием данного фактора, и случайной составляющей делается вывод о существенном действии фактора (достаточно высокое соотношение) и необходимости учёта его влияния. Или, наоборот, при малом соотношении (меньшем некоего критического значения) между составляющей дисперсии, вызванной действием данного фактора, и случайной составляющей делается вывод о несущественности влияния фактора. Критическое («табличное») значение соотношения дисперсий определяется на основе распределения Фишера (см. § 3.3).

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

В двухфакторном анализе выясняется значимость влияния на исследуемый признак каждого из двух факторов (А и В). В этом случае дифференцируется вклад каждого фактора в суммарную дисперсию y_i. По результатам сравнения соотношения этого вклада и случайной составляющей дисперсии отклика с соответствующим значением критерия Фишера делается вывод о существенности влияния каждого фактора.

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

Итоги могут быть представлены в виде «матрицы наблюде­ний», где результаты обработки на каждом станке расположены последовательно в разных столбцах, а результаты обработки деталей из каждого вида сырья расположены последовательно в разных строках (см. § 3.3).

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

Дисперсионный анализ с повторениями требует большего объёма вычислений, особенно в том случае, когда для каждого фактора на разных уровнях проведено различное количество опытов. Как правило, стремятся делать равное количество опытов. Тогда расчёты по сравнению с неравным количеством существенно упрощаются. При использовании инструмента «Двухфакторный дисперсионный анализ с повторениями», также требующего равного количества опытов, расчёты вообще исключаются (см. § 3.3).

В таблице 3.1 приведены основные тождества каждого из рассмотренных выше вариантов дисперсионного анализа. Во всех тождествах таблицы присутствует составляющая, обусловленная случайными и неучтёнными факторами SS_остат (она определяется вариацией внутри ячейки дисперсионной матрицы). Кроме составляющих дисперсии, обусловленных действием каждого из варьируемых факторов (SS₁ и SS₂), при двухфакторном дисперсионном анализе с повторениями появляется обусловленная на­личием нескольких наблюдений в ячейке составляющая суммы SS_взаим.

Таблица 3.1. Основные тождества, рассматриваемые при анализе составляющих общей вариации отклика SS при различных методах дисперсионного анализа

Метод дисперсионного анализа	Основное тождество
Однофакторный	SS = SS1 + SSостат
Двухфакторный без повторений	SS = SS1 + SS2 + SSостат
Двухфакторный с повторениями	SS = SS1 + SS2 + SSвзаим + SSостат

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

1. По специальным формулам [5, 6] подсчитывают полную («общую», «суммарную») дисперсию SS и её компоненты.

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

3. Сравнивают рассчитанные значения критерия Фишера с соответствующими критическими (табличными) значениями критерия Фишера (F_крит), полученными для заданного уровня значимости. (Они различаются для каждого компонента, так как различаются числа степеней свободы.) При F < F_крит нулевая гипотеза о равенст­ве дисперсий не опровергается. Делается вывод, что влияния соответствующего фактора или взаимодействия факторов на исследуемый отклик (точность, шероховатость детали, производительность, процент брака и т.д.) не установлено. В этом случае данный фактор не должен присутствовать в регрессионной модели. При F > F_крит делается вывод, что нулевая гипотеза о равенст­ве дисперсий не подтверждается, т.е. влияние фактора (или взаимодействия факторов) на исследуемый признак значимо (существенно) и должно найти отражение в регрессионной модели.

Многофакторный дисперсионный анализ в чистом виде отсутствует в пакете анализа MS EXCEL, но результаты этого анализа приводятся в общих результатах инструмента анализа «регрессия» (см. главу 4). В качестве примера приведена таблица результатов такого ANOVA-анализа (табл. 3.2), полученная при одном из расчётов коэффициентов множественной регрессии. Она включает рассчитанные значения числа степеней свободы df, т.е. независимых элементов информации, суммы квадратов, SS (дисперсии) и среднего квадрата, приходящегося на одну степень свободы (MS =SS/df). Причём результаты приведены раздельно для каждого источника вариации, обусловленного как регрессией (р), так и колебаниями относительно регрессии, иначе остатками (о). Кроме того, определяется расчётное значение F-критерия значимости регрессии как отношение приходящейся на одну степень свободы дисперсии, обусловленной регрессией, и дисперсии, обусловленной действием случайных и неучтённых факторов (= МSр/S2).

Таблица 3.2. Результаты множественного дисперсионного анализа

Источник вариации	df (числ. степ. свободы)	SS (сумма квадратов)	Средний квадрат MS (=SS/df)	F-критерий значимости регрессии ( = МSр/S2)	Значимость F
Обусловленный регрессией (р)	8	158,1	19,75	10,78	5,3E-11
Относит. регрессии, остаток (о)	106	194,3	1,83
Итого	114	352,3

В отличие от одно- и двухфакторных дисперсионных анализов (§ 3.3) среди результатов множественного дисперсионного анализа (табл. 3.2) не приводятся критические табличные значения F-критерия. Но приводится величина значимости F, которую следует сравнить с принятым уровнем доверительной вероятности «альфа» (обычно берётся α = 0,05). Если рассчитанная величина значимости F оказывается меньше α, как представлено в табл. 3.2, нулевую гипотезу о равенстве дисперсий MS (р) и MS (о) отвергают. Следовательно, вариация, обусловленная регрессией, преобладает, и в этом случае регрессионный анализ имеет смысл.