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

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

Anova — расшифровывается как дисперсионный анализ. Anova in excel — это статистический метод, который используется для проверки разницы между двумя или более средствами.

Почему ANOVA используется в Excel?

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

Как найти надстройки Anova в Excel?

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

Excel поставляется с надстройками, называемыми «Анализатор данных», «Солвер» и т. Д. И некоторые из надстроек COM, которые мы можем добавить в Excel, — это Power Pivot, Power Query, Power View и т. Д.

Действия по добавлению надстроек Anova в Excel

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

Чтобы добавить надстройки в Excel, выполните следующие действия.

• В Excel перейдите в меню « Файл» .

• Выберите Option, где в более старой версии мы можем найти опцию, названную «EXCEL OPTION».

• Так что мы получим диалоговое окно параметров Excel, как показано ниже.

• Теперь мы можем видеть надстройки, нажмите на надстройки .

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

Добавление Excel-надстроек

• В Excel в опции « Управление» у нас есть опция надстроек, например надстройки Excel, надстройки COM, Action, пакеты расширения XML, Disabled Items.

Давайте посмотрим, как добавить Excel-add, выполнив следующие шаги:

• Нажмите на активную надстройку, чтобы добавить ее в Excel.
• Сначала мы увидим, как активировать надстройку Excel в Excel, которая показана в первой части.
• Выберите первую надстройку Analysis ToolPack.

• А внизу мы видим выпадающий список диспетчера, в котором выберите надстройки Excel, а затем нажмите кнопку « Перейти» .

• Мы получим окно надстроек, как показано ниже.

• Теперь пакет инструментов анализа содержит набор надстроек, которые покажут нам возможность выбрать его.
• Мы видим, что Пакет инструментов анализа, Пакет инструментов анализа Pak-VBA, Инструменты Валюты Евро, Надстройка Solver.
• Отметьте все надстройки и дайте ОК, чтобы выбранные надстройки отображались в меню данных.

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

• Теперь мы видим, что во вкладке данных «Анализ данных » добавлена ​​группа анализа, выделенная красным цветом.

Как использовать ANOVA в Excel?

ANOVA в Excel очень прост и удобен в использовании. Давайте посмотрим, как работает однофакторный инструмент Excel ANOVA с несколькими различными примерами.

Вы можете скачать этот шаблон ANOVA Excel здесь — Шаблон ANOVA Excel

Excel ANOVA — Пример № 1

В этом примере мы увидим, как применить единый фактор Excel ANOVA, следуя приведенному ниже примеру.

Рассмотрим приведенный ниже пример, который показывает оценки учащихся по каждому предмету.

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

• Сначала перейдите в меню ДАННЫЕ, а затем нажмите на АНАЛИЗ ДАННЫХ .

• Мы получим диалоговое окно анализа.
• На скриншоте ниже мы видим список инструментов анализа, где мы видим инструмент ANOVA — однофакторный.
• Нажмите ANOVA: однофакторный инструмент и затем нажмите OK .

• Таким образом, мы получим диалоговое окно ANOVA: однофакторный, как показано на скриншоте ниже.

• Теперь мы можем видеть диапазон ввода в диалоговом окне.
• Нажмите на поле ввода диапазона, чтобы выбрать диапазон $ B $ 1: $ D $ 7, как показано ниже.

• Как мы можем видеть на скриншоте выше, мы выбрали диапазоны вместе с именем студента, чтобы получить точный результат.
• Теперь выбран диапазон ввода, убедитесь, что установлен флажок Column .

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

• Мы выбрали ячейку диапазона вывода как G1, где будет отображаться вывод.
• Убедитесь, что установлен флажок Метки в первой строке, и нажмите кнопку ОК .

• Мы получим следующий вывод следующим образом.

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

F = между группой / внутри группы

• F-статистика: F-статистика — это не что иное, как значения, которые мы получаем, когда выполняем ANOVA, который используется для значительного определения средних между двумя популяциями.

Значения F всегда используются вместе со значением «P» для проверки значимости результатов, и этого достаточно, чтобы отклонить нулевую гипотезу.

Если мы получим значение F больше, чем значение F crit, то мы можем отклонить нулевую гипотезу, которая означает, что что-то является значимым, но на приведенном выше снимке экрана мы не можем отклонить нулевую гипотезу, поскольку значение F меньше, чем критик F, и оценки учеников не значительный, который выделен и показан на скриншоте ниже.

Если мы работаем с одним фактором Excel ANOVA, убедитесь, что дисперсия 1 меньше, чем дисперсия 2.

На скриншоте выше мы видим, что первая дисперсия (92.266) меньше, чем дисперсия2 (1877.5).

Excel ANOVA — Пример № 2

В этом примере мы увидим, как отклонить нулевую гипотезу, выполнив следующие шаги.

На приведенном выше снимке экрана мы можем видеть три группы A, B, C, и мы собираемся определить, насколько эти группы значительно отличаются, выполнив тест ANOVA.

• Сначала нажмите на меню DATA .
• Нажмите на вкладку анализа данных .
• Выберите Anova Single factor из диалогового окна «Анализ».
• Теперь выберите диапазон ввода, как показано ниже.

• Затем выберите выходной диапазон как G1, чтобы получить выход.

• Убедитесь, что флажки «Столбцы и метки в первом ряду» установлены, а затем нажмите «ОК».

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

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

То, что нужно запомнить

• Инструмент Excel ANOVA будет работать точно, если мы введем правильные данные или в противном случае получим неверные данные.
• Всегда убедитесь, что первое значение дисперсии меньше, чем второе, чтобы мы получили точное значение F.

Рекомендуемые статьи

Это было руководство к ANOVA в Excel. Здесь мы обсудим, как найти надстройки ANOVA и как использовать ANOVA в Excel вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи —

1. Как интерпретировать результаты с помощью теста ANOVA
2. Использование функции автоформатирования в Excel
3. Использование комментариев для печати в Excel
4. ИГЕРРОР с VLOOKUP в Excel

Факторный анализ в Excel: пример

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

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

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

1. Посмотрим, за счет, каких наименований произошел основной рост по итогам второго месяца. Если продажи какого-то товара выросли, положительная дельта – в столбец «Рост». Отрицательная – «Снижение». Формула в Excel для «роста»: =ЕСЛИ((C2-B2)>0;C2-B2;0), где С2-В2 – разница между 2 и 1 месяцем. Формула для «снижения»: =ЕСЛИ(J3=0;B2-C2;0), где J3 – ссылка на ячейку слева («Рост»). Во втором столбце – сумма предыдущего значения и предыдущего роста за вычетом текущего снижения.
2. Рассчитаем процент роста по каждому наименованию товара. Формула: =ЕСЛИ(J3/$I$11=0;-K3/$I$11;J3/$I$11). Где J3/$I$11 – отношение «роста» к итогу за 2 месяц, ;-K3/$I$11 – отношение «снижения» к итогу за 2 месяц.
3. Выделяем область данных для построения диаграммы. Переходим на вкладку «Вставка» — «Гистограмма».
4. Поработаем с подписями и цветами. Уберем накопительный итог через «Формат ряда данных» — «Заливка» («Нет заливки»). С помощью данного инструментария меняем цвет для «снижения» и «роста».

Теперь наглядно видно, продажи какого товара дают основной рост.

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

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

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

Среднеквадратичное (стандартное) отклонение

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

На практике формула стандартного отклонения следующая:

Как и с дисперсией, есть и немного другой вариант расчета. Но с ростом выборки разница исчезает.

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

Ассортимент и объем продаж продукции

Далее необходимо рассчитать изменчивость объема продаж по каждой товарной группе. На рисунке ниже показан итог расчета коэффициента вариации по продукции. Формула расчета в Excel будет иметь следующий вид:

Коэффициент вариации объемов продаж =СТАНДОТКЛОН(B5:G5)/СРЗНАЧ(B5:G5)

Расчет коэффициента вариации продаж товаров

Сейчас необходимо классифицировать товары в группу – «X»,»Y» или «Z». Для этого напишем формулу определяющую класс товара, и воспользуемся встроенной формулой «ЕСЛИ» в Excel. Формула будет иметь следующий вид:

Группа товара =ЕСЛИ(H5 Самая обсуждаемая >10 000 просмотров >100 лайков С примером Видео-урок

Предприниматель, ментор в бизнес-инкубаторе HSE inc. Автор книги-бестселлера «Финансовый анализ предприятия с помощью коэффициентов и моделей». Лауреат государственной премии за комплекс монографий в сфере экономики и управления предприятиями авиационной промышленности на базе информационных технологий. Лауреат премии Всероссийского конкурса на лучшую научную книгу 2013 года от Фонда развития отечественного образования