Как в excel рассчитать дисперсию. Расчет дисперсии в Microsoft Excel

13 комментариев

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

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

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

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

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

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

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

Работа начинается с дизайна таблицы. Правила:

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

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

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

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

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

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



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

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

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

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

Читайте также  Как включить split view на iphone. Режим разделения экрана на iPhone и iPad

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

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

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

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

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

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

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

Оставьте комментарий