Факторный и дисперсионный анализ в Excel с автоматизацией подсчетов

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

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

Условно цель дисперсионного метода можно сформулировать так: вычленить из общей вариативности параметра 3 частные вариативности:

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

В программе Microsoft Excel дисперсионный анализ можно выполнить с помощью инструмента «Анализ данных» (вкладка «Данные» – «Анализ»). Это надстройка табличного процессора. Если надстройка недоступна, нужно открыть «Параметры Excel» и включить настройку для анализа.

Работа начинается с оформления таблицы. Правила:

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

Рассмотрим дисперсионный анализ в Excel на примере.

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

Внесем данные в таблицу Excel:

Таблица с исходными данными.

  1. Открываем диалоговое окно нашего аналитического инструмента. В раскрывшемся списке выбираем «Однофакторный дисперсионный анализ» и нажимаем ОК.Инструмент Анализ данных.
  2. В поле «Входной интервал» ввести ссылку на диапазон ячеек, содержащихся во всех столбцах таблицы.Диапазон ячеек.
  3. «Группирование» назначить по столбцам.
  4. «Параметры вывода» – новый рабочий лист. Если нужно указать выходной диапазон на имеющемся листе, то переключатель ставим в положение «Выходной интервал» и ссылаемся на левую верхнюю ячейку диапазона для выводимых данных. Размеры определятся автоматически.
  5. Результаты анализа выводятся на отдельный лист (в нашем примере).

Результаты анализа.

Значимый параметр залит желтым цветом. Так как Р-Значение между группами больше 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. Другими словами, мы должны получить «заметно различающиеся» средние выборок при различных уровнях фактора:

regress-155.png

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

Дисперсионный анализ: основные понятия

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

Минимальное число классов градации (групп) – два. Классы градации могут быть качественными либо количественными.

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

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

Как формулируются, принимаются и отвергаются гипотезы при дисперсионном анализе? При дисперсионном анализе определяют удельный вес суммарного воздействия одного или нескольких факторов. Существенность влияния фактора определяется путём проверки гипотез:

  • H0: μ1 = μ2 = … = μa, где a – число классов градации – все классы градации имеют одно значение средних,
  • H1: не все μi равны – не все классы градации имеют одно значение средних.

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

Ещё некоторые понятия дисперсионного анализа. Статистическим комплексом в дисперсионном анализе называется таблица эмпирических данных. Если во всех классах градаций одинаковое число вариантов, то статистический комплекс называется однородным (гомогенным), если число вариантов разное – разнородным (гетерогенным).

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

Теоретические сведения

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

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

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

Статистические данные представляют собой данные, полученные в результате обследования большого числ объектов или явлений.

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

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

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

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

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

Инструкция

Введите кол-во групп и нажмите “Ok”.

Далее, заполните колонки данными групп.

Данные вводятся по одному числу на строку (без пробелов, пропусков и т.д.).

Вводятся только цифры. Дробные числа вводятся со знаком «.» (точка). После заполнения, нажмите на кнопку «Ввести».

Гипотезы:

Н0: Статистически значимых различий между результатами групп нет

Н1: Различия между результатами групп статистически значимы

Количество групп (по умолч. 3, макс 7):

Задача

Сделанопо 5 измерений случайной величины 100task.ru на каждом из четырех уровней фактора 100task.ru.

Методомдисперсионного анализа проверить гипотезу о том, что фактор 100task.ru не влияет на математическое ожидание величины 100task.ru. Сделать вывод.

Решение

Если не находите примера, аналогичного вашему, если сами не успеваете выполнить работу, если впереди экзамен по предмету и нужна помощь – свяжитесь со мной:

ВКонтакте
WhatsApp
Telegram

Я буду работать с вами, над вашей проблемой, пока она не решится.

Вычислим суммы:

Находим 100task.ru  и 100task.ru поформулам:

100task.ru

100task.ru

100task.ru

где 100task.ru -число наблюдений,  100task.ru -числоуровней фактора.  Для данной задачи 100task.ru.

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

100task.ru

100task.ru

100task.ru

100task.ru

Сравним выборочные дисперсии по критерию Фишера:

100task.ru

Задавая уровень  значимости критерия 100task.ru,по таблице критических точек F-распределения Фишера-Снедекора находим: 100task.ru 

Здесь

100task.ru

Так как 100task.ru   -факторы не оказывают воздействие на математическое ожидание величины 100task.ru.

Если не находите примера, аналогичного вашему, если сами не успеваете выполнить работу, если впереди экзамен по предмету и нужна помощь – свяжитесь со мной:

ВКонтакте
WhatsApp
Telegram

Я буду работать с вами, над вашей проблемой, пока она не решится.

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

Условия применения дисперсионного анализа ANOVA

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

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

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

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

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

SST=SSA+SSE

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

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

где, regress-159.png – общее среднее:

regress-158.png

Обратите внимание, что квадраты отклонений имеют размерность дисперсии , т.е. меры изменчивости. Теперь очевидно, что левая часть выражения представляет собой общую изменчивость (разброс) каждого из наблюдений относительно общего среднего. Эта общая изменчивость (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)).

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

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

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

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

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

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

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

Формат ряда данных.

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

Литература

  1. В.П.Боровиков. STATISTICA. Искусство анализа данных на компьютере, 2-е издание, ПИТЕР, 2003, 700 с. 

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...