Как рассчитать двухфакторный опыт в экселе

Обновлено: 05.07.2024

Нами разработан пакет программ AgCStat в виде надстройки Excel.

В настоящее время пакет включает 12 программ плюс лист с примерами подготовки данных для анализа:

  • получение табличных значений критериев Фишера и Стьюдента;
  • восстановление выпавших данных
  • вычисление статистик выборки;
  • однофакторный дисперсионный анализ полевых опытов по Б.А. Доспехову;
  • двухфакторный дисперсионный анализ полевых опытов по Б.А. Доспехову;
  • двухфакторный дисперсионный анализ неравномерного комплекса по Н.А. Плохинскому;
  • трехфакторный дисперсионный анализ равномерного комплекса (оригинальный алгоритм авторов);
  • одно, двух и трех факторный анализ качественных признаков по Н.А. Плохинскому;
  • парная корреляция и регрессия с полным статистическим анализом результатов;
  • оценка разности средних по критерию Стьюдента.

3. Если первые две ссылки не работают, Вы можете скачать Эксель файл AgCStat

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

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

Во-вторых, в версиях Excel младше Excel 2002 ряд функций либо отсутствуют, либо они не доступны, как, например, функции GetFisher и GetStudent – выдающих табличные значения критериев.

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

Дадим некоторые пояснения по пакету программ.

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

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

В доступной нам литературе, мы не нашли четкого алгоритма трехфакторного дис-персионного анализа для количественных признаков (равномерного комплекса), но, поскольку необходимость в нем высока, разработали его сами, опираясь на алгоритмы Н.А. Плохинского [5].

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

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

Иногда исследователя интересует всего лишь величина разности средних двух выборок и ее достоверность. Эту задачу решает последняя в списке программа. Достаточно указать диапазоны, в которых находятся выборки, диапазоны могут быть как смежными, так и несмежными и даже располагаться на разных листах книги Excel.

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

При использовании вышеизложенных материалов необходимо ссылаться на авторов.

Данный материал опубликован в:
Сборнике «Рациональное природопользование и сельскохозяйственное производство в южных регионах Российской Федерации» М. «Современные тетради», 2003, с.559-564 П.П. Гончар-Зайкин, В.Г. Чертов.

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

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

Напомним, что дисперсионный анализ (ANOVA, ANalysis Of VAriance) позволяет проверить гипотезу о равенстве средних значений выборок (взяты ли выборки из одного распределения или из разных распределений). Данная задача возникает, например, когда необходимо исследовать зависимость некой количественной величины Y от одной или нескольких переменных (факторов), которые мы можем контролировать (устанавливать их значения). Действительно, если фактор оказывает влияние на зависимую переменную Y, то при разных уровнях фактора мы должны в среднем получать различные значения Y, т.е. мы должны получить «заметно отличающиеся» средние значения выборок . В статье будет показано, что значит средние выборок «заметно отличаются».

В этой статье рассмотрим метод дисперсионного анализа в случае двух факторов (Фактор А и Фактор В) (Two Factor ANOVA with Replication).

СОВЕТ : Перед прочтением этой статьи рекомендуется освежить в памяти Однофакторный дисперсионный анализ .

Обозначения

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

Уровни фактора А будем обозначать буквой j (j изменяется от 1 до a ). Уровни фактора В будем обозначать буквой i (i изменяется от 1 до b ). Каждой паре уровней факторов соответствует одна выборка, которая состоит из m измерений, каждое измерение будем обозначать буквой k (k от 1 до m). Таким образом, измеренные значения Y при уровне j фактора А и при уровне i фактора В будем обозначать y ijk . Всего выборок a*b .

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

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

Задача

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

Метод обработки представляет собой фактор А , который может принимать 3 значения (Метод 1, Метод 2, Метод 3), а Исходный материал представляет собой фактор В , который может принимать 2 значения (№ 1, № 2). Качество изделий будем определять по количеству дефектных изделий в партии (это будет зависимой переменной Y).

Всего различных комбинаций 2-х факторов 6=3*2=a*b. Для каждой комбинации факторов было проведено по 3 измерения (т.е. m=3). Исходные данные приведены в файле примера .


Другими словами мы имеем 6 выборок по 3 значения в каждой. Средние этих выборок для каждой комбинации факторов ij можно вычислить по формуле:


Также для дальнейших вычислений нам потребуется вычислить еще несколько средних значений. Во-первых, вычислим среднее всех измерений, относящихся к каждому уровню i Фактора А:


Во-вторых, вычислим среднее всех измерений, относящихся к каждому уровню j Фактора В:


Взаимодействие факторов

Теперь, используя эти 6 средних значений, построим диаграмму, которая состоит из 2-х рядов .


По оси Х (абсцисс) отложены уровни Фактора А , по оси ординат отложены средние значения переменной Y (среднее количество дефектов для заданных уровней факторов). Средние значения сгруппированы по 2-м уровням Фактора В (Синяя и красная линии. Каждая линия представляет собой отдельный ряд диаграммы).

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

Вот еще одна диаграмма, демонстрирующая независимость 2-х факторов.


Обратная ситуация показана на диаграмме ниже, когда оба фактора взаимодействуют.


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

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

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

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

Определяем причины изменчивости исходных данных

По аналогии с однофакторным дисперсионным анализом общую изменчивость (разброс) значений Y относительно общего среднего (SST = Sum of Squares Total, общая сумма квадратов) определим как сумму нескольких компонентов, в данном случае 4-х:

SST=SSA+SSB+ SS взаим +SSE

  • SSA – изменчивость, которую можно объяснить выбором метода обработки (фактор А)
  • SSВ - изменчивость обусловленная выбором материала детали (фактор В)
  • SS взаим - изменчивость обусловленная взаимодействием 2-х факторов
  • SSE - ошибка модели (Error Sum of Squares).

SST и все 4 компонента вычисляются на основании имеющихся исходных данных:


Примечание : Вычисления SST и всех 4-х компонентов выполнены в файле примера .

Также в дисперсионном анализе используется понятие среднего квадрата отклонений (Mean Square) или сокращенно MS. Соответственно для SST имеем MST=SST/(N-1), где N= a*b*m является общим количеством измерений (18). Для других SS степени свободы приведены в таблице ниже.

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


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

  • Гипотеза Н 0 взаим об отсутствии взаимодействия Фактора А и Фактора В. Альтернативная гипотеза Н 1взаим формулируется о наличии взаимодействия.
  • гипотеза Н 01 заключается в том, что уровень фактора А (метод обработки поверхности) не влияет на измеренные значения Y (количество дефектов), т.е. средние значения выборок, относящиеся к различным уровням Фактора А не отличаются статистически значимо (их различие может быть объяснено лишь случайностью выборок).
  • гипотеза Н 0 2 заключается в том, что уровень фактора В (Исходный материал) не влияет на измеренные значения Y (количество дефектов), т.е. средние значения выборок, относящиеся к различным уровням Фактора В не отличаются статистически значимо.

Сначала тестируют гипотезу об отсутствии взаимодействия между факторами. Мы можем отклонить Н 0 взаим в пользу Н 1взаим при заданном уровне значимости α (альфа), если вычисленное значение тестовой статистики F= MS взаим /MSE больше F критич альфа – значения случайной величины F имеющей распределение Фишера с (b-1)*(a-1) и a*b*(m-1) степенями свободы.

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

Чтобы проверить гипотезы необходимо вычислить значения тестовых статистик и сравнить их с соответствующими критическими значениями F крит ич , вычисленными для заданного уровня значимости альфа . Если вычисленное значение F 01 = MSА/MSE больше F 1крит ич , то нулевую гипотезу Н 0 1 об отсутствии влияния уровней Фактора А отклоняют. Аналогичные умозаключения справедливы и для Фактора В.

Проверить гипотезу Н 01 можно и через вычисление p -значения, которое представляет собой вероятность того, что случайная величина F 1 = MSА/MSE примет значение более F 01 . Далее p -значение сравнивают с уровнем значимости. Если p -значение менее уровня значимости, то нулевую гипотезу отклоняют. Действительно, если вычисленное значение F 01 получить маловероятно, то это ставит под сомнение справедливость того, что случайная величина F 1 = MSА/MSE имеет распределение Фишера с a -1 и a * b *( m -1) степенями свободы, а следовательно и саму нулевую гипотезу. В этом случае мы можем считать, что справедлива альтернативная гипотеза: уровни фактора А влияют на зависимую переменную Y.

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

В файле примера приведено решение вышеуказанной задачи: вычислены средние значения выборок, суммы квадратов (SS), степеней свобод, средние квадратов отклонений (MS).


Для вычислений критических значений в MS EXCEL имеется специальная функция = F.ОБР.ПХ()

Формула для вычисления F 1критич = F.ОБР.ПХ(a-1; a*b*(m-1);альфа)

В MS EXCEL первое p -значение (вероятность того, что случайная величина F 1 = MSА/MSE примет значение более F 01 ) можно вычислить по формуле:

= F.РАСП.ПХ((MSА/MSE; a-1; a*b*(m-1))

Второе p -значение (вероятность того, что случайная величина F 2 = MSВ/MSE примет значение более F 0 2 ) вычисляется по аналогичным формулам.

В нашей задаче p -значения получились 0,000 и 0,253, что значительно меньше обычно принимаемого в качестве уровня значимости 0,05. Таким образом, обе нулевых гипотезы отклоняются.

Применение статистики в этой заметке будет показано на сквозном примере. Предположим, что вы — руководитель производства в компании Perfect Parachute («Идеальный парашют»). Парашюты изготавливаются из синтетических волокон, поставляемых четырьмя разными поставщиками. Совершенно очевидно, что одной из основных характеристик парашюта является его прочность. Вам необходимо убедиться, что все поставляемые волокна обладают одинаковой прочностью. Более того, на фабрике используется два вида ткацких станков: Jetta и Turk. Можно ли утверждать, что парашюты, изготовленные на станке фирмы Jetta, так же прочны, как и парашюты, произведенные на станках компании Turk? Существует ли разница между прочностью парашютов, сотканных из синтетических волокон разных поставщиков на разных станках? Чтобы ответить на этот вопрос, следует разработать схему эксперимента, в ходе которого измеряется прочность парашютов, сотканных из синтетических волокон разных поставщиков на разных станках. Информация, полученная в ходе этого эксперимента, позволит определить, какой поставщик и какой тип станка обеспечивают наибольшую прочность парашютов.

Вследствие сложности вычислений, особенно при большом количестве уровней каждого фактора и реплик, для двухфакторного анализа следует применять либо Excel, либо специализированное программное обеспечение. В двухфакторном эксперименте факторы А и В считаются взаимодействующими, если эффект фактора А зависит от уровня фактора В. Напомним, что в полностью рандомизированном плане полная сумма квадратов (SST) подразделяется на межгрупповую сумму квадратов (SSA) и внутригрупповую сумму квадратов (SSW). В двухфакторном эксперименте с одинаковым количеством реплик в каждой ячейке полная вариация (SST) подразделяется на сумму квадратов, соответствующую фактору A (SSA), сумму квадратов, соответствующую фактору В (SSB), сумму квадратов, учитывающую взаимодействие факторов А и В (SSAB), и сумму квадратов, возникающую вследствие случайной ошибки (SSE) (рис. 1).

Рис. 1. Разделение полной вариации в двухфакторном эксперименте

В двухфакторном дисперсионном анализе применяются три разных критерия:

  1. Для проверки гипотезы об отсутствии эффекта фактора А
  2. Для проверки гипотезы об отсутствии эффекта фактора В
  3. Для проверки гипотезы об отсутствии эффекта взаимодействия факторов А и В (рис. 2).

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


Рис. 2. Дисперсионный анализ в двухфакторном эксперименте

Для иллюстрации двухфакторного дисперсионного анализа вернемся к нашему сценарию. Допустим, что, будучи руководителем производства, вы решили сравнить поставщиков синтетических волокон, и оценить, на каком из станков выпускаются более прочные парашюты: Jetta или Turk. Кроме того, необходимо определить, зависит ли разница между четырьмя поставщиками от типа станков, на которых производятся парашюты. Итак, необходимо разработать план эксперимента, в котором каждому поставщику и типу станка соответствует пять парашютов (рис. 3). Для проведения анализа пройдите по меню Данные → Анализ данных и выберите строку Двухфакторный дисперсионный анализ с повторениями.

Рис. 3. Двухфакторный дисперсионный анализ с повторениями в Пакете анализа Excel

На рис. 4 показаны результаты двухфакторного дисперсионного анализа данных: объем выборки, сумма, арифметическое среднее и дисперсия каждой комбинации типа станка и поставщика. В первых двух таблицах приведены результаты дисперсионного анализа для всех типов станка, а в третьей — для каждого поставщика. В сводной таблице дисперсионного анализа идентификатор df обозначает количество степеней свободы, SS — сумму квадратов, MS — среднее квадратичное отклонение, F — вычисленную F-статистику.

Рис. 4. Результат двухфакторного дисперсионного анализа прочности парашютов

Чтобы проанализировать эти результаты, сначала следует проверить, существует ли взаимодействие между факторами А (типами станка) и В (поставщиками). Если эффект взаимодействия является значительным, дальнейший анализ ограничивается лишь оценкой этого эффекта. С другой стороны, если эффект взаимодействия незначителен, необходимо сосредоточиться на главных эффектах — потенциальных различиях между типами станков (фактор А) и поставщиками (фактор В).

Чтобы определить наличие эффекта взаимодействия при уровне значимости, равном 0,05, применяется следующее решающее правило: нулевая гипотеза об отсутствии эффекта взаимодействия отклоняется, если вычисленное значение F-статистики (см. таблицу Дисперсионный анализ, строку Взаимодействие столбец F на рис. 4), больше верхнего критического значения F-распределения (там же, столбец F-критическое). Поскольку F = 0,01 < FU = 2,90, а р-значение равно 0,998, гипотеза Н0 не отклоняется. Следовательно, у нас недостаточно оснований утверждать, что факторы станка и поставщика взаимодействуют друг с другом. Следовательно, необходимо проанализировать главные эффекты.

При заданном уровне значимости, равном 0,05, в основе проверки разности между двумя станками (фактор А) лежит следующее решающее правило: нулевая гипотеза отклоняется, если вычисленное значение F-статистики больше верхнего критического значения F-распределения (см. таблицу Дисперсионный анализ, строку Выборка на рис. 4). Поскольку F = 0,81 < FU = 4,15, а р-значение равно 0,37 и больше уровня значимости α = 0,05, гипотеза Н0 не отклоняется. Следовательно, у нас недостаточно оснований утверждать, что между прочностью парашютов, произведенных на разных станках, существует значимая разница.

При заданном уровне значимости, равном 0,05, в основе проверки разности между поставщиками (фактор В) лежит следующее решающее правило: нулевая гипотеза отклоняется, если вычисленное значение F-статистики больше верхнего критического значения F-распределения (см. таблицу Дисперсионный анализ, строку Столбцы на рис. 4). Поскольку F = 5,20 > FU = 2,92, а р-значение равно 0,005 и меньше уровня значимости, гипотеза Н0 отклоняется. Следовательно, можно утверждать, что между прочностью парашютов, произведенных из волокна, приобретенного у разных поставщиков, существует значимая разница. [2]

Интерпретация эффектов взаимодействия

Чтобы лучше разобраться во взаимодействии факторов, следует построить график средних значений в ячейках (т.е. средних значений, соответствующих конкретным уровням факторов), как показано на рис. 5 (в качестве данных для построения графика использованы области В19:Е19 и В25:Е25 рис. 4). Из графика средней прочности для каждой комбинации станок–поставщик следует, что две линии, соответствующие разным станкам, проходят почти параллельно друг другу. Это означает, что разности между средними величинами прочности парашютов, произведенных на разных станках, практически одинаковы для всех четырех поставщиков. Иначе говоря, между этими двумя факторами нет связи, что полностью подтверждается F-критерием.

Рис. 5. График средних значений прочности парашютов в зависимости от станков и поставщиков

В чем проявляется эффект взаимодействия? В некоторых ситуациях определенные уровни фактора А могут оказаться связанными с конкретными уровнями фактора В. Например, предположим, что некоторые парашюты оказываются более прочными, если они сотканы из определенных волокон на станках Jetta, а другие — если они сотканы из волокон других поставщиков на станках Turk. Если бы это было правдой, линии на рис. 5 не были бы параллельными и взаимодействие между факторами было бы статистически значимым. Следовательно, в этих ситуациях разница между станками не будет одинаковой при разных поставщиках. Это усложняет интерпретацию главных эффектов, поскольку разности, соответствующие одному фактору (например, типу станка), не согласуются с другим фактором (например, поставщиком). Проиллюстрируем эту ситуацию следующим примером.

Пример.1. Интерпретация статистически значимых эффектов взаимодействия. Данные, приведенные на рис. 6а, характеризуют продолжительность работы подшипников под воздействием двух факторов: автоколебания и нагревания. Как влияют автоколебания и нагревание на продолжительность работы подшипников? Результаты двухфакторного дисперсионного анализа продолжительности работы подшипников, полученные с помощью Пакета анализа в Excel приведены на рис. 6б. Обратите внимание на то, что, кроме сводной таблицы дисперсионного анализа, Excel вычисляет среднее значение для каждой комбинации двух факторов: степени автоколебаний и нагревания, а также среднее значение для каждого уровня факторов. Для того чтобы проанализировать эти результаты, сначала необходимо определить, наблюдается ли статистически значимый эффект взаимодействия факторов автоколебания (фактор А) и нагревания (фактор В). При уровне значимости α = 0,05 нулевую гипотезу об отсутствии эффекта взаимодействия следует отклонить, поскольку p-значение равно 0,0018, т.е. меньше 0,05. Кроме того, F-статистика равна 53,78 и превышает величину 7,71 — верхнее критическое значение F-распределения с одной степенью свободы в числителе и четырьмя степенями свободы в знаменателе.

Рис. 6. (а) Продолжительность работы подшипников при автоколебании и нагревании; (б) Результаты двухфакторного дисперсионного анализа продолжительности работы подшипников

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

Рис. 7. График средних значений продолжительности работы подшипников по ячейкам

Множественные сравнения

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

Критический размах процедуры Тьюки-Крамера для фактора А


где QU — верхнее критическое значение распределения стьюдентизированного размаха, имеющего r степеней свободы в числителе и rc(n’ – 1) степеней свободы в знаменателе.

Критический размах процедуры Тьюки-Крамера для фактора B


где QU — верхнее критическое значение распределения стьюдентизированного размаха, имеющего с степеней свободы в числителе и rc(n’ – 1) степеней свободы в знаменателе.

Применим процедуру Тьюки-Крамера к задаче о прочности парашютов (см. рис. 3). Анализ сводной таблицы дисперсионного анализа, представленной на рис. 4, показывает, что статистически значимым является лишь один главный эффект. При уровне значимости, равном 0,05, нет оснований утверждать, что между двумя типами станков (Jetta и Turk) существует значимая разница (фактор А), однако между четырьмя поставщиками (фактор В) эта разница существует. Таким образом, дальнейший анализ должен концентрироваться на разностях между поставщиками.

Поскольку компания, производящая парашюты, имеет четыре фирмы-поставщика, следует проверить 4(4 – 1)/2 = 6 пар поставщиков (рис. 8а). Вычислим модули разности между соответствующими средними значениями по выборкам отдельных поставщиков (рис. 8б).


Рис. 8. (а) Исходные данные о прочности парашютов; (б) попарные сравнения средних значений по выборкам отдельных поставщиков



Рис. 9. Критическое значение стьюдентизированного размаха QU; к сожалению, в Excel нет функции, рассчитывающей такой размах

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

[1] Используются материалы книги Левин и др. Статистика для менеджеров. – М.: Вильямс, 2004. – с. 664–676

[2] К аналогичному выводу мы пришли и при проведении однофакторного дисперсионного анализа по поставщикам.

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

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

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

Откройте вкладку Файл, нажмите кнопку Параметры и выберите категорию Надстройки.

В раскрывающемся списке Управление выберите пункт Надстройки Excel и нажмите кнопку Перейти.

Если вы используете Excel для Mac, в строке меню откройте вкладку Средства и в раскрывающемся списке выберите пункт Надстройки для Excel.

В диалоговом окне Надстройки установите флажок Пакет анализа, а затем нажмите кнопку ОК.

Если Пакет анализа отсутствует в списке поля Доступные надстройки, нажмите кнопку Обзор, чтобы выполнить поиск.

Примечание: Чтобы включить Visual Basic для приложений (VBA) для надстройки "Надстройка анализа", вы можете загрузить надстройку VBA так же, как и надстройку "Надстройка анализа". В поле Доступные надстройки выберите "Надстройка анализа — VBA".

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

Однофакторный дисперсионный анализ

Этот инструмент выполняет простой анализ дисперсии данных для двух или более выборок. Анализ дает проверку гипотезы о том, что каждая выборка взята из одного и того же распределения вероятности на основе альтернативной гипотезы о том, что для всех выборок распределение вероятности не одно и то же. Если есть только два примера, можно использовать функцию T.ТЕСТ. В более чем двух примерах нет удобного обобщения T.ВМЕСТОэтого можно использовать модель Anova для одного фактора.

Двухфакторный дисперсионный анализ с повторениями

Этот инструмент анализа применяется, если данные можно систематизировать по двум параметрам. Например, в эксперименте по измерению высоты растений последние обрабатывали удобрениями от различных изготовителей (например, A, B, C) и содержали при различной температуре (например, низкой и высокой). Таким образом, для каждой из 6 возможных пар условий , имеется одинаковый набор наблюдений за ростом растений. С помощью этого дисперсионного анализа можно проверить следующие гипотезы:

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

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

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

Двухфакторный дисперсионный анализ без повторений

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

Функции CORREL и PEARSON вычисляют коэффициент корреляции между двумя переменными измерения, если для каждой переменной наблюдаемы измерения по каждому из N-объектов. (Любые отсутствующие наблюдения по любой теме вызывают игнорирование в анализе.) Средство анализа корреляции особенно удобно использовать, если для каждого субъекта N имеется более двух переменных измерения. Она содержит выходную таблицу — матрицу корреляции, которая показывает значение CORREL (или PEARSON),примененного к каждой из возможных пар переменных измерения.

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

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

Средства корреляции и коварианс могут использоваться в одном и том же параметре, если у вас есть N различных переменных измерения, наблюдаемые для набора людей. Каждый из инструментов корреляции и ковариции дает выходную таблицу — матрицу, которая показывает коэффициент корреляции или коварианс между каждой парой переменных измерения соответственно. Разница заключается в том, что коэффициенты корреляции масштабироваться в зависимости от -1 и +1 включительно. Соответствующие ковариансы не масштабироваться. Коэффициент корреляции и коварианс — это показатели степени, в которой две переменные "различаются".

Инструмент Ковариана вычисляет значение функции КОВАРИАНА. P для каждой пары переменных измерения. (Прямое использование КОВАРИАНА. P вместо ковариана является разумной альтернативой, если есть только две переменные измерения, то есть N=2.) Запись в диагонали выходной таблицы средства Коварица в строке i, столбце i — коварианс i-й переменной измерения. Это только дисперсия по численности населения для этой переменной, вычисляемая функцией ДИСПЕРС.P.

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

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

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

Примечание: Для константы сглаживания наиболее подходящими являются значения от 0,2 до 0,3. Эти значения показывают, что ошибка текущего прогноза установлена на уровне от 20 до 30 процентов ошибки предыдущего прогноза. Более высокие значения константы ускоряют отклик, но могут привести к непредсказуемым выбросам. Низкие значения константы могут привести к большим промежуткам между предсказанными значениями.

Двухвыборочный F-тест применяется для сравнения дисперсий двух генеральных совокупностей.

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

С помощью этого инструмента вычисляется значение f F-статистики (или F-коэффициент). Значение f, близкое к 1, показывает, что дисперсии генеральной совокупности равны. В таблице результатов, если f < 1, "P(F <= f) одностороннее" дает возможность наблюдения значения F-статистики меньшего f при равных дисперсиях генеральной совокупности и F критическом одностороннем выдает критическое значение меньше 1 для выбранного уровня значимости "Альфа". Если f > 1, "P(F <= f) одностороннее" дает возможность наблюдения значения F-статистики большего f при равных дисперсиях генеральной совокупности и F критическом одностороннем дает критическое значение больше 1 для "Альфа".

Инструмент "Анализ Фурье" применяется для решения задач в линейных системах и анализа периодических данных на основе метода быстрого преобразования Фурье (БПФ). Этот инструмент поддерживает также обратные преобразования, при этом инвертирование преобразованных данных возвращает исходные данные.

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

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

Совет: В Excel 2016 теперь можно создавать гистограммы и диаграммы Парето.

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

N — число предшествующих периодов, входящих в скользящее среднее;

A j — фактическое значение в момент времени j;

F j — прогнозируемое значение в момент времени j.

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

Средство анализа Ранг и процентиль создает таблицу, которая содержит порядковую и процентную ранг каждого значения в наборе данных. Можно проанализировать относительное положение значений в наборе данных. В этом средстве используются функции РАНГ. EQ и PERCENTRANK. INC. Если вы хотите учитывать связанные значения, используйте РАНГ. Функция EQ, которая обрабатывает связанные значения как имеющие одинаковый ранг или использует РАНГ.Функция AVG, которая возвращает средний ранг связанных значений.

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

В средстве регрессии используется функция LINEST.

Инструмент анализа "Выборка" создает выборку из генеральной совокупности, рассматривая входной диапазон как генеральную совокупность. Если совокупность слишком велика для обработки или построения диаграммы, можно использовать представительную выборку. Кроме того, если предполагается периодичность входных данных, то можно создать выборку, содержащую значения только из отдельной части цикла. Например, если входной диапазон содержит данные для квартальных продаж, создание выборки с периодом 4 разместит в выходном диапазоне значения продаж из одного и того же квартала.

Двухвыборочный t-тест проверяет равенство средних значений генеральной совокупности по каждой выборке. Три вида этого теста допускают следующие условия: равные дисперсии генерального распределения, дисперсии генеральной совокупности не равны, а также представление двух выборок до и после наблюдения по одному и тому же субъекту.

Для всех трех средств, перечисленных ниже, значение t вычисляется и отображается как "t-статистика" в выводимой таблице. В зависимости от данных это значение t может быть отрицательным или неотрицательным. Если предположить, что средние генеральной совокупности равны, при t < 0 "P(T <= t) одностороннее" дает вероятность того, что наблюдаемое значение t-статистики будет более отрицательным, чем t. При t >=0 "P(T <= t) одностороннее" делает возможным наблюдение значения t-статистики, которое будет более положительным, чем t. "t критическое одностороннее" дает пороговое значение, так что вероятность наблюдения значения t-статистики большего или равного "t критическое одностороннее" равно "Альфа".

"P(T <= t) двустороннее" дает вероятность наблюдения значения t-статистики, по абсолютному значению большего, чем t. "P критическое двустороннее" выдает пороговое значение, так что значение вероятности наблюдения значения t- статистики, по абсолютному значению большего, чем "P критическое двустороннее", равно "Альфа".

Парный двухвыборочный t-тест для средних

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

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

Двухвыборочный t-тест с одинаковыми дисперсиями

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

Двухвыборочный t-тест с различными дисперсиями

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

Для определения тестовой величины t используется следующая формула.

Следующая формула используется для вычисления степеней свободы (df). Так как результат вычисления обычно не является integer, значение df округлится до ближайшего другого, чтобы получить критическое значение из таблицы t. Функция Excel T .Test использует вычисляемую величину df без округлений, так как можно вычислить значение для T.ТЕСТ с неинтегрированной df. Из-за этих разных подходов к определению степеней свободы результаты T.Тест и этот t-тест будут отличаться в случае неравных дисперсий.

Z-тест. Средство анализа "Две выборки для середины" выполняет два примера z-теста для средств со известными дисперсиями. Этот инструмент используется для проверки гипотезы NULL о том, что между двумя значениями численности населения нет различий между односторонними или двухбокльными альтернативными гипотезами. Если дисперсии не известны, функция Z .Вместо этого следует использовать тест.

При использовании этого инструмента следует внимательно просматривать результат. "P(Z <= z) одностороннее" на самом деле есть P(Z >= ABS(z)), вероятность z-значения, удаленного от 0 в том же направлении, что и наблюдаемое z-значение при одинаковых средних значениях генеральной совокупности. "P(Z <= z) двустороннее" на самом деле есть P(Z >= ABS(z) или Z <= -ABS(z)), вероятность z-значения, удаленного от 0 в том же направлении, что и наблюдаемое z-значение при одинаковых средних значениях генеральной совокупности. Двусторонний результат является односторонним результатом, умноженным на 2. Инструмент "z-тест" можно также применять для гипотезы об определенном ненулевом значении разницы между двумя средними генеральных совокупностей. Например, этот тест можно использовать для определения разницы выступлений на соревнованиях двух автомобилей разных марок.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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