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

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

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

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

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

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

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

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

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

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

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

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

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

Значимый параметр залит желтым цветом. Так как Р-Значение между группами больше 1, критерий Фишера нельзя считать значимым. Следовательно, поведение в конфликтной ситуации не зависит от уровня образования.



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

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

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

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

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

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

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

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

Задача. Группе мужчин и женщин предъявляли звук разной громкости: 1 – 10 дБ, 2 – 30 дБ, 3 – 50 дБ. Время ответа фиксировали в миллисекундах. Необходимо определить, влияет ли пол на реакцию; влияет ли громкость на реакцию.

Исходная таблица.
  1. Переходим на вкладку «Данные» - «Анализ данных» Выбираем из списка «Двухфакторный дисперсионный анализ без повторений».
    2. Двухфакторный дисперсионный анализ без повторений.
  2. Заполняем поля. В диапазон должны войти только числовые значения.
    3. Параметры анализа.
  3. Результат анализа выводится на новый лист (как было задано).
Результат.

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

Скачать пример факторного и дисперсионного анализа

скачать факторный анализ отклонений
скачать пример 2

Для фактора «Громкость»: 3,16 < 6,94. Следовательно, данный фактор не влияет на время ответа.

Для примера также прилагаем факторный анализ отклонений в маржинальном доходе.


en ru