Как рассчитать дисперсию в Excel (с шагами и примерами)

Microsoft Excel предоставляет широкий спектр инструментов и функций для анализа данных и статистической оценки. Одной из распространенных функций является дисперсия, которую можно вычислить с помощью команд VAR.P и VAR.S в зависимости от типа измеряемых данных.

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

Что представляет собой дисперсия в статистике?

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

Типы дисперсии

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

• Дисперсия населения: Дисперсия населения измеряет всю совокупность и использует командную функцию VAR.P() в Excel для расчета дисперсии, игнорируя текстовые и логические данные.

• Выборочная дисперсия. Выборочная дисперсия представляет собой вариацию выборки генеральной совокупности и использует командную функцию VAR.S() в Excel для расчета дисперсии без учета текстовых и логических данных.

• Измерения дисперсии для логических и текстовых входных данных. Вы можете измерить выборочную дисперсию и дисперсию для генеральной совокупности, в которой вы также хотите оценить текстовые и логические аргументы. Excel использует VARA() для дисперсии выборки и VARPA() для дисперсии генеральной совокупности, где у вас есть логические и текстовые входные данные, такие как «Истина = 1, Ложь = 0» или «Ввод = ноль».

Как рассчитать дисперсию в Excel для населения

В приведенных ниже шагах показано, как рассчитать дисперсию в Excel при оценке всей совокупности:

1. Введите и упорядочите данные

Импортируйте свои данные на пустой лист Excel и организуйте их в соответствии с вашими потребностями в оценке.

Например, предположим, что профессор вводит классные оценки за задание. Класс представляет все население, и профессор организует столбец A для имен учащихся и столбец B для оценок. Исследователь данных, изучающий алгоритмы машинного обучения, может использовать различные входные и организационные методы для анализа дисперсии, поскольку их совокупность данных может быть большой.

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

2. Создайте отдельную ячейку для формулы

Используйте отдельный столбец и ячейку для ввода функциональной команды. В примере с экзаменационными баллами профессора они могут использовать ячейку в столбце C или D для перечисления расчета дисперсии. После выбора ячейки, которую вы хотите использовать для командной функции, пометьте столбец для вашего расчета «Дисперсия» для пояснения.

3. Введите функцию VAR.P

В столбце, который вы называете «Дисперсия», выберите ячейку и введите командную функцию для дисперсии населения. Введите имена ячеек, используя синтаксис =VAR.P(ячейка:ячейка).

В качестве примера предположим, что результаты предыдущих экзаменов отображаются в ячейках с B2 по B207. Если профессор вводит формулу в ячейку C2, синтаксис приводит к =VAR.P(B2:B207).

4. Оцените результаты

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

Как рассчитать выборочную дисперсию в Excel

Используйте следующие шаги для расчета выборочной дисперсии в Excel:

1. Выберите выборку населения

Для использования функции VAR.S требуется меньшая выборка из совокупности.

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

2. Расположите данные в электронной таблице

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

3. Выберите отдельную ячейку для формулы

Под новым столбцом выберите ячейку для командной функции.

В выборке из 150 баллов учителя выбирают ячейку в столбце D, чтобы ввести формулу. Назовите новый столбец «Дисперсия», чтобы отличить функцию от остальных ваших данных.

4. Войдите в функцию VAR.S.

Используя синтаксис =VAR.S(ячейка:ячейка), введите командную функцию для ячеек, которые вы оцениваете.

Например, предположим, что вы отслеживаете дневную температуру в течение одного года и хотите получить дисперсию за два конкретных месяца. Если данные за один из месяцев находятся в ячейках с B2 по B32, а данные за другой месяц находятся в ячейках с C2 по C33, вы должны ввести формулу команды как =VAR.S(B2:B32;C2:C33)**.

5. Оцените выборочную дисперсию

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

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

Важность расчетов дисперсии

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

• Измерение частоты ошибок: дисперсия важна для понимания частоты ошибок при оценке статистических данных. Коэффициент ошибки показывает, насколько вероятно, что нулевая гипотеза неверна.

• Выявление причин вариации. Расчет вариации также важен для понимания того, что вызывает большие вариации в наборе данных. Например, определение того, что такие факторы, как возраст и пол, влияют на рост взрослого человека, может помочь организовать выборочные данные для дополнительного анализа.

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

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

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

Примеры

Рассмотрим следующие примеры расчета дисперсии с использованием команд функции VAR.P и VAR.S:

Пример дисперсии населения

Учитель хочет рассчитать дисперсию экзаменационных баллов своего класса. Класс представляет все население, поэтому учитель использует функцию VAR.P в своей электронной таблице Excel. Вводя имена учащихся в столбец A, 9 баллов в столбец B и формулу в столбец C, она вычисляет дисперсию следующим образом:

A B C Студент 1 95 =VAR.P(B2:B10) = 58,09876543 Студент 2 88 Студент 3 73 Студент 4 79 Студент 5 84 Студент 6 98 Студент 7 92 Студент 8 86 Студент 9 80

Образец примера отклонения

Ученый-эколог хочет оценить изменения рН воды за последние шесть месяцев. Поскольку данные о населении содержат примерно 182 дня значений pH, ученый берет образец одного значения для каждого из месяцев, в которые они отслеживали качество воды.

Ученый вводит свои данные в электронную таблицу и использует функцию VAR.S для расчета дисперсии выборочных данных. С месяцами в столбце A, данными pH в столбце B и функцией дисперсии в столбце C расчет приводит к следующему результату:

Проба Месяц Отклонение рН Март 5,5 =VAR.S(B2:B7) = 0,224 Апрель 6,1 Май 6,8 Июнь 6,6 Июль 5,9 Август 6,3

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