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

Добавил пользователь Владимир З.
Обновлено: 05.09.2024

На этом шаге мы рассмотрим дисперсионный анализ.

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

На рис. 1 показано диалоговое окно однофакторного дисперсионного анализа . Параметр Альфа определяет статистический уровень значимости для теста.

Рис. 1. Диалоговое окно Однофакторный дисперсионный анализ

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

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

Обращение к средствам анализа данных: они доступны через команду Анализ данных меню Сервис. Если этой команды нет в меню, необходимо загрузить надстройку Пакет анализа. Однофакторнын дисперсионный знали з

Три группы испытуемых (опытные операторы-профессионалы, операторы-новички и студенты, не имевшие опыта операторской работы) решали задачу' по слежению за движущимся объектом. По 10 опытам, проведенным с каждым испытуемым, было рассчитано среднее количество ошибок. Определите, зависит ли число ошибок от профессионального опыта испытуемых? Какие группы испытуемых значимо отличаются друг от друга по числу ошибок?

Шаги по загрузке надстройки Data Analysis Toolpak

Шаг 3 — Нажмите на ‘Надстройки’ а затем выберите ‘Надстройки Excel’ за ‘Управление’. Нажмите на ‘Идти.’

Шаг 5 —Команда ‘Анализ данных’ появится под данные’ вкладка в Excel в крайнем правом углу ленты, как показано ниже.

Список функций, доступных в пакете инструментов анализа данных Excel

Ниже приведен список доступных функций в надстройке Excel Toolpak для анализа:

ANOVA: однофакторный анализ в Excel
Корреляция в Excel
Рейтинг и процентиль в Excel
Описательная статистика в Excel

Теперь давайте подробно обсудим каждый из них —

ANOVA (дисперсионный анализ) — это первый набор опций, доступных в надстройке Excel Toolpak для анализа. В одностороннем ANOVA мы анализируем, есть ли какие-либо статистические различия между средними значениями трех или более независимых групп. Нулевая гипотеза предполагает, что статистическая значимость не существует в наборе данных наблюдений. Мы проверяем эту гипотезу, проверяя значение p.

Давайте разберемся в этом на примере ANOVA excel.

пример

Группа А получала 0,62 мг / кг алкоголя.
Группа AC получала алкоголь плюс кофеин.
Группа AR получила алкоголь и денежное вознаграждение за выступление.
Группа P получала плацебо.

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

Как провести тест ANOVA?

Чтобы запустить односторонний тест ANOVA, нам необходимо выполнить следующие шаги:

Шаг 5: Для диапазона вывода мы выбрали F1. Пожалуйста нажмите на ‘ХОРОШО.’

Теперь у нас есть анализ ANOVA.

Чем больше значение F-статистики в Excel, тем более вероятно, что группы имеют разные средние значения, что отвергает нулевую гипотезу о том, что все средние значения равны. F-статистика, превышающая критическое значение, эквивалентна значению p в Excel меньше, чем альфа, и оба значения означают, что мы отвергаем нулевую гипотезу. Отсюда можно сделать вывод, что между группами существует значительная разница.

Корреляция — это статистическая мера, доступная в надстройке Excel Toolpak для анализа, и она показывает степень, в которой две или более переменных колеблются вместе. Положительная корреляция в Excel указывает на степень, в которой эти переменные увеличиваются или уменьшаются параллельно. Отрицательная корреляция указывает на степень увеличения одной переменной при уменьшении другой.

пример

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

Как найти корреляцию между двумя наборами переменных?

Чтобы выяснить корреляцию между этими двумя наборами переменных, мы выполним следующие шаги:

Как мы видим, корреляция между стоимостью рекламы (заголовок столбца) и продажами (заголовок строки) составляет примерно +0,86274, что указывает на положительную корреляцию и составляет 86,27%. Теперь мы можем соответственно определиться с рекламным бюджетом и ожидаемыми продажами.

Процентиль в Excel — это число, в котором определенный процент оценок ниже этого числа, и доступно в надстройке Excel Toolpak для анализа. Например, если конкретный результат находится в 90-м процентиле, это означает, что учащийся набрал больше, чем 90% людей, прошедших тест. Давайте разберемся в этом на примере.

пример

У нас есть следующие данные для оценок, полученных учеником класса.

Мы хотим узнать рейтинг и процентиль для каждого ученика.

Как найти ранг и процентиль?

Шаги будут такими:

Мы получили результат как на следующем изображении.

Описательная статистика, включенная в надстройку Excel Toolpak для анализа, содержит следующую информацию об образце:

Основная тенденция
1. Имеется в виду: это называется средним.
2. Медиана: это середина распределения.
3. Режим: это наиболее часто встречающееся число.
1. Спектр: Это разница между наибольшими и наименьшими переменными.
2. Разница: Это показало, насколько далеко разбросаны числа.
3. Среднеквадратичное отклонение: Насколько сильно отклонение от среднего / среднего.
пример

Однофакторный дисперсионный анализ
Пусть, например, ставится задача исследования влияния некоторого фактора на изменения значений случайной величины x.
Различные значения фактора называют уровнями фактора.
Данные для задач однофакторного дисперсионного анализа обычно записывают в виде прямоугольной таблицы, каждая строка которой содержит значения , в столбцах – значения, полученные в разных экспериментах, т.е. – значение случайной величины, полученное при i-м уровне фактора в j-м эксперименте.

Аннотация научной статьи по строительству и архитектуре, автор научной работы — Ершова Н.М.

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

Похожие темы научных работ по строительству и архитектуре , автор научной работы — Ершова Н.М.

Корреляционный и дисперсионныйанализ данных исследований свободных колебаний массивно-плитных фундаментов под машины с вертикальной импульсной нагрузкой

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

ДИСПЕРСИОННЫЙ АНАЛИЗ ДАННЫХ НАБЛЮДЕНИЙ С ПОМОЩЬЮ ПАКЕТА АНАЛИЗА ПРИЛОЖЕНИЯ EXCEL

Н. М. Ершова, д. т. н., проф.

Цель работы. Ознакомить магистров, аспирантов, преподавателей и специалистов, занимающихся планированием и обработкой результатов наблюдений или эксперимента, с методами дисперсионного анализа и технологией их реализации в среде электронных таблиц (ЭТ).

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

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

Матрица экспериментов для однофакторного анализа

Уровни фактора Номер выборки

Значения результативного признака Хц Х21 x12 Х22 Х1Р х2 p

Х 1 ? хп 2 2 xn p Р

Объем выборки ni n2 np

Общее число наблюдений п = п^ + «2 + ••• + пр • Прежде чем судить о количественном

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

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

Пусть фактор А имеет р различных уровней, на каждом из которых выполнено п

наблюдений. Следовательно, наблюдалось N = рп значений хц признака (свойства) X, где 7

- номер наблюдения (7 = 1,2. п), ] - номер уровня фактора (j = 1,2. р). Чем существеннее влияние фактора на признак X, тем сильней будут различаться между собой средние значения групп наблюдений на разных уровнях фактора А . Существуют понятия:

• общая сумма квадратов - сумма квадратов отклонений всех возможных значений признака от их общего среднего значения

• сумма квадратов между группами или по факторам - взвешенная сумма квадратов отклонений средних значений по группам от общего среднего значения

51 = п X (X, - X)2 ; (2)

где х], X - соответственно среднее значение группы и общее среднее значение

• сумма квадратов внутри групп - сумма квадратов отклонений возможных значений

признака каждой группы (уровня фактора) от среднего значения этой группы

52 = XX (хи - X ])2, (3)

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

Для оценки влияния фактора следует разложить общую сумму квадратов на составляющие: сумму квадратов между группами (по факторам) и сумму квадратов внутри групп. Следовательно,

Сумма 51 отражает влияние на результативный признак уровней фактора, а сумма 5 2 -влияние погрешностей измерений. Так как 5^ = 5 - 51, то сумму 5^ называют еще остаточной суммой квадратов. Суммы квадратов 5, 51, 52 , деленные на соответствующие числа степеней

свободы, дают три несмещенные оценки дисперсии о2 генеральной совокупности:

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

Сумма 51 имеет т^ = р -1 степеней свободы, сумма 5 ^ - т2= р (п -1)= пр - р степеней свободы. Число степеней свободы суммы 5 - т = N -1 = (р -1) + (N - р).

Если факторная дисперсия окажется меньше остаточной , то фактор не оказывает

существенного влияния на признак X . Проверка значимости оценок дисперсии выполняется с помощью Е -критерия Фишера, расчетное значение которого определяется дисперсионным отношением

Если Г > Е^р , то на принятом уровне значимости делается вывод о существенном влиянии

фактора А на признак X.

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

Рассмотрим методику однофакторного дисперсионного анализа и возможные результаты на конкретных примерах.

Пример 1 [5]. Две группы дилеров продают автомобили, которые рекламируются соответственно рекламами А и В. Третья группа дилеров работает без рекламы. В каждой группе задействовано по 4 дилера. Таким образом, р =3, п =4. В таблице 2 приведено количество автомобилей, которые проданы различными группами дилеров.

Требуется определить влияние двух видов рекламы на объем продаж автомобилей.

Сводка исходных данных

Дилер Реклама А Реклама В Рекламы нет

Размещение информации на рабочем листе ЭТ приведено в таблице 3.

Размещение информации на рабочем листе ЭТ

1 Влияние рекламы на объем продаж автомобилей

2 | реклама А реклама В нет рекламы

3 дилеры ХИ Х\2 ХКЗ

Влияние рекламы на объем продаж автомобилей проверяем с помощью инструмента Однофакторный дисперсионный анализ (рис. 1).

Рис. 1. Диалоговое окно инструмента Однофакторный дисперсионный анализ

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

Дисперсионный анализ влияния рекламы на объем продаж

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

Группы Счет Суммэ Среднее Дисперсия

хл 4 216 54 8,666667 Отах

Х12 4 264 66 13,33333 0,46901639

Х13 4 192 48 18,66667 Окр

??л?очник еэриэции № Р Р-Значение Р критическое

Между группами 672 2 336 24,78689 0,00021849 4,256494729

Внутри групп 122 9 13,5556

В таблице 4 обозначено: ?? - суммы квадратов; df - степени свободы; Ы8 - дисперсии; Е - расчетное значение Е - критерия Фишера; Р - значение. Если Р , то реклама существенно влияет на объем продаж автомобилей.

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

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

где X1, X2 - средние значения объема продаж автомобилей сравниваемых уровней фактора;

^ = V(+ ^2)/2 ;л^, 52 - дисперсии объема продаж автомобилей сравниваемых уровней

фактора; Пр П2 - объемы сравниваемых выборок.

Тогда расчетное значение для рекламы А равно

г (54 - 48)^ = 2,295. 7(8,667 +18,667)/2

Для рекламы В г = 6,364. Критическое значение г - статистики определяем с помощью статистической функции СТЬЮДРАСПОБР мастера функций. При а = 0,05 и

т = р(п -1) = 9 гкр = 2,262 . Следовательно, реклама В существенно влияет на объем продаж автомобилей.

Пример 2 [6]. Выполним дисперсионный анализ влияния на прочность бетона в течение

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

В данной задаче факторами являются недели, в каждой из которых имеется 5 значений прочности. Следовательно, п = 5, р = 4 .

Результаты измерений прочности

Не деля Прочность х71, МПа 71

1 22 18,5 20,1 20,4 19, 7

2 18,4 19,8 20,2 20,5 21

3 21 33 18,3 19,1 20, 3

4 20,4 20,5 22 18 19

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

Дисперсионный анализ влияния технологического процесса на прочность бетона

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

Группы Счет Сумма Среднее Дисперсия

1 5 100.7 20.14 1.603

2 5 99.9 19.98 0.972

3 5 100.7 20.14 2.173

4 5 99.9 19.98 2.352

Уточник ъэриаири № Р Р-Значение Р критическое

Между группами 0.128 3 0.04267 0.0240376 0.9947486 3.238871522

Внутри групп 28.4 16 1.775

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

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

• выборки независимы, когда измерение значений признака проводится на разных, достаточно однородных объектах;

• выборки представляют собой парные наблюдения, когда множество объектов зафиксировано, а наблюдения проводятся в разные моменты времени.

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

Данные о ценах на некоторые модели офисного оборудования приведены в таблице 7.

В данном случае р = 2; п = 15. Число степеней свободы

т1 = р -1 = 1; т2 = р(п -1) = 2(15 -1) = 28; т = т1 + т2 = 29.

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

Так как Е Не можете найти то, что вам нужно? Попробуйте сервис подбора литературы.

Уров ень фактора А Уровни фактора В

А2 х 21 х 22 х 2д

Расчетные формулы средних значений признака X : • на каждом уровне фактора А

на каждом уровне фактора В

по всем наблюдениям

Расчетные формулы дисперсий признака X :

• на каждом уровне фактора А

Ч _ 2 X (хг7 - х ) 5 2 = 7=1 7 г

• на каждом уровне фактора В

52 = г=1 г 7 , 7 «1

Число степеней свободы:

«1 = р -1; т2 = ч -1; т0 = рЧ - р - Ч +1 = (Р - 1)(Ч -1);

Суммы, характеризующие влияние факторов на признак X, и общая сумма квадратов:

5а = Ч X (х. - X)2; 5, = р X (х. - X)2; г=1 г 7=1 7

5 = X X (х7 - XГ = X X х„ - пX* ; г = 17 = 1 77 г = 17 = 1 г7

где 50 - остаточная сумма квадратов отклонений. Дисперсии:

- (18) р -1 Ь ч -1 " (р - 1)(Ч -1) п -1

Статистическая значимость влияния на признак X факторов проверяется сравнением расчетных значений Е -критерия

с критическими значениями критерия при заданных значениях а; «1; т0 и

Если Г > , то влияние факторов статистически значимо. В этом случае дисперсионный

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

Пример 4 [3]. Оценить значимость влияния отношения цемент : песок (фактор А) и крупности песка (фактор В) на прочность мелкозернистого бетона для армоцемента по данным эксперимента, приведенным в таблице 10.

1 62 64 70 74 75

2 50 54 59 64 64

3 41 43 50 57 53

4 32 36 42 51 40

Примечание. Соответствие уровней фактора А: 1 - 1:1,6; 2 - 1:2; 3 - 1:2,4; 4 - 1:2,8. Размерность содержания крупных зерен песка, %.

Сводка дисперсионного анализа представлена в таблице 11.

Та б л и ц а 1 1

Дисперсионный анализ влияния факторов на прочность бетона

Д&ухфакторный дисперсионный анализ без повторений _ИТОГИ_Счет Сумма Среднее Дисперсия

2 5 291 58.2 38.2

3 5 244 48.8 45.2

4 5 201 40.2 51.2

0 4 185 46.25 164.25

15 4 197 49.25 151.5833

30 4 221 55.25 144.9167

45 4 246 61.5 97.66667

60 4 232 58 224.6667

Источник вариации № Г Р-Значение Г критическое

Строки 2300.55 3 766.85 188.9569 2.3065Е-10 3.490294821

Столбцы 625.7 4 156.425 38.54415 9.3114Е-07 3.259166727

Погрешность 48.7 12 4.05833

Итого 2974.95 19

Выполним анализ полученных результатов:

• расчетное значение Г - критерия для фактора В (крупность песка) равно 38,54. Критическое значение Г -критерия равно 3,259, т. е. влияние этого фактора существенно;

• расчетное значение Г - критерия для фактора А, характеризующего отношение Ц : П, равно 188,95. Критическое значение Г -критерия равно 3,49, т. е. влияние этого фактора существенно (примерно в 5 раз сильнее фактора В - крупность песка);

• первый уровень фактора А (Ц : П = 1 : 1,6) обеспечивает получение среднего значения прочности 69 МПа и дисперсию 34 МПа2 - это лучшие показатели из всех отношений;

• четвертый уровень фактора В (45 %) обеспечивает получение среднего значения прочности 61,5 МПа и дисперсию 97,6 МПа2 - это лучшие показатели из всех процентных содержаний крупных зерен в песке.

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

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

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

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

Шаги по загрузке надстройки Data Analysis Toolpak

Список функций, доступных в пакете инструментов анализа данных Excel

пример

Как провести тест ANOVA?

пример

Как найти корреляцию между двумя наборами переменных?

пример

Как найти ранг и процентиль?

пример

Аннотация научной статьи по строительству и архитектуре, автор научной работы — Ершова Н.М.

Похожие темы научных работ по строительству и архитектуре , автор научной работы — Ершова Н.М.