Как рассчитать дисперсию в Excel — формула дисперсии выборки и генеральной совокупности

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

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

Что такое дисперсия?

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

Чтобы лучше понять, что вы на самом деле рассчитываете с помощью дисперсии, рассмотрите этот простой пример.

Предположим, в вашем местном зоопарке есть 5 тигров в возрасте 14, 10, 8, 6 и 2 лет.

Чтобы найти дисперсию, выполните следующие простые шаги:

  1. Вычислите среднее (простое среднее) пяти чисел:
    Средняя формула
  2. Из каждого числа вычтите среднее значение, чтобы найти различия. Для наглядности нанесем различия на график:
    Разница в Excel
  3. Сократите каждую разницу.
  4. Вычислите среднее квадратов разностей.

Формула дисперсии

Итак, дисперсия равна 16. Но что на самом деле означает это число?

По правде говоря, дисперсия просто дает вам очень общее представление о дисперсии набора данных. Значение 0 означает отсутствие изменчивости, т. е. все числа в наборе данных одинаковы. Чем больше число, тем больше разбросаны данные.

Этот пример для дисперсия населения (т.е. 5 тигров — это вся интересующая вас группа). Если ваши данные являются выборкой из большей совокупности, вам необходимо рассчитать выборочная дисперсия по несколько иной формуле.

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

В Excel есть 6 встроенных функций для расчета дисперсии: VAR, VAR.S, VARP, VAR.P, VARA и VARPA.

Ваш выбор формулы дисперсии определяется следующими факторами:

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

Функции дисперсии Excel

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

Название Версия Excel Тип данных Текст и логика

БЫЛ
2000 — 2019 Образец игнорируется

ЧЬЯ
2010 — 2019 Образец игнорируется

БЫТЬ
2000 — 2019 Образец Оценка

ПОСЛЕДНИЙ
2000–2019 гг. Население не учитывается

ДА
2010–2019 гг. Население не учитывается

БРОСАТЬ
2000 — 2019 Оценка населения

ВАР.С против. ВАРА и ВАР.П vs. ДЕФОРМАЦИЯ

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

Тип аргумента VAR, VAR.S, VARP, VAR.P VARA и VARPA Логические значения в массивах и ссылках Игнорируется Оценивается
(TRUE=1, FALSE=0) Текстовые представления чисел в массивах и ссылках Игнорируется Оценивается как ноль Логические значения и текстовые представления чисел, вводимые непосредственно в аргументы Оцениваются
(TRUE=1, FALSE=0) Пустые ячейки Игнорируются

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

Выборка представляет собой набор данных, извлеченных из всего населения. А дисперсия, рассчитанная по выборке, называется выборочной дисперсией.

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

Выборочная дисперсия рассчитывается по следующей формуле:
Пример формулы дисперсии

Где:

  • x̄ — среднее (простое среднее) значений выборки.
  • n — размер выборки, т. е. количество значений в выборке.

В Excel есть 3 функции для нахождения выборочной дисперсии: VAR, VAR.S и VARA.

Функция ВАР в Excel

Это самая старая функция Excel для оценки дисперсии на основе выборки. Функция VAR доступна во всех версиях Excel с 2000 по 2019.

ВАР(число1, [number2]…)

Примечание. В Excel 2010 функция VAR была заменена функцией VAR.S, которая обеспечивает повышенную точность. Хотя VAR по-прежнему доступен для обратной совместимости, рекомендуется использовать VAR.S в текущих версиях Excel.

Функция VAR.S в Excel

Это современный аналог функции Excel VAR. Используйте функцию VAR.S, чтобы найти выборочную дисперсию в Excel 2010 и более поздних версиях.

ВАР.С(число1, [number2]…)

Функция ВАРА в Excel

Функция Excel VARA возвращает примерную дисперсию на основе набора чисел, текста и логических значений, как показано на рис. этот стол.

БЫТЬ(значение1, [value2]…)

Пример формулы отклонения в Excel

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

В качестве примера найдем дисперсию выборки, состоящей из 6 элементов (B2:B7). Для этого можно использовать одну из следующих формул:

= ПЕРЕМ(B2:B7)

=ПЕР.С(B2:B7)

=ВАРА(B2:B7)

Как показано на скриншоте, все формулы возвращают один и тот же результат (округленный до 2 знаков после запятой):
Расчет выборочной дисперсии в Excel

Чтобы проверить результат, произведем расчет var вручную:

  1. Найдите среднее значение с помощью функции СРЗНАЧ:
    =СРЕДНЕЕ(B2:B7)

    Среднее значение идет в любую пустую ячейку, скажем, B8.

  2. Вычтите среднее значение из каждого числа в выборке:
    =B2-$B$8

    Различия идут в столбец C, начиная с C2.

  3. Возведите в квадрат каждую разницу и поместите результаты в столбец D, начиная с D2:
    =С2^2
  4. Сложите квадраты разностей и разделите результат на количество элементов в выборке минус 1:
    =СУММ(D2:D7)/(6-1)

Как видите, результат нашего ручного вычисления var точно такой же, как число, возвращаемое встроенными функциями Excel:
Примеры формул дисперсии в Excel

Если ваш набор данных содержит логические и/или текстовые значения, функция VARA вернет другой результат. Причина в том, что VAR и VAR.S игнорируют любые значения, отличные от чисел, в ссылках, в то время как VARA оценивает текстовые значения как нули, TRUE как 1 и FALSE как 0. Поэтому, пожалуйста, тщательно выбирайте функцию дисперсии для своих расчетов в зависимости от того, хотите обработать или игнорировать текст и логические операции.
Использование функций VAR, VAR.S и VARA в Excel

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

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

Дисперсию населения можно найти по следующей формуле:
Формула дисперсии населения

Где:

  • x̄ — среднее значение населения.
  • n — размер совокупности, т. е. общее количество значений в совокупности.

В Excel есть 3 функции для расчета дисперсии генеральной совокупности: VARP, VAR.P и VARPA.

Функция VARP в Excel

Функция Excel VARP возвращает дисперсию генеральной совокупности на основе всего набора чисел. Он доступен во всех версиях Excel с 2000 по 2019.

ВАРП(число1, [number2]…)

Примечание. В Excel 2010 VARP был заменен на VAR.P, но по-прежнему сохранен для обратной совместимости. В текущих версиях Excel рекомендуется использовать ДИСП.П, поскольку нет гарантии, что функция ДИСП будет доступна в будущих версиях Excel.

Функция VAR.P в Excel

Это улучшенная версия функции VARP, доступная в Excel 2010 и более поздних версиях.

ВАР.П(число1, [number2]…)

Функция ДСПСП в Excel

Функция VARPA вычисляет дисперсию генеральной совокупности на основе всего набора чисел, текста и логических значений. Он доступен во всех версиях Excel с 2000 по 2019.

БЫТЬ(значение1, [value2]…)

Формула дисперсии населения в Excel

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

Допустим, у нас есть экзаменационные баллы группы из 10 студентов (B2:B11). Баллы составляют всю совокупность, поэтому мы будем делать дисперсию с этими формулами:

=СКОРБКА(B2:B11)

=ПЕР.П(B2:B11)

=ТОЭ(B2:B11)

И все формулы вернут одинаковый результат:
Расчет дисперсии населения в Excel

Чтобы убедиться, что Excel правильно рассчитал дисперсию, вы можете проверить ее с помощью формулы ручного расчета var, показанной на снимке экрана ниже:
Формула дисперсии населения в Excel

Если кто-то из студентов не сдавал экзамен и вместо количества баллов указано N/A, функция VARPA вернет другой результат. Причина в том, что VARPA оценивает текстовые значения как нули, в то время как VARP и VAR.P игнорируют текстовые и логические значения в ссылках. Посмотри пожалуйста VAR.P vs. БЫЛ НА для получения полной информации.
Функции VARP и VARPA в Excel

Формула дисперсии в Excel — примечания по использованию

Чтобы правильно провести дисперсионный анализ в Excel, следуйте простым правилам:

  • Предоставляйте аргументы в виде значений, массивов или ссылок на ячейки.
  • В Excel 2007 и более поздних версиях можно указать до 255 аргументов, соответствующих выборке или генеральной совокупности; в Excel 2003 и старше — до 30 аргументов.
  • Чтобы оценить только числа в ссылках, игнорируя пустые ячейки, текст и логические значения, используйте функцию VAR или VAR.S для расчета выборочной дисперсии и VARP или VAR.P для нахождения дисперсии генеральной совокупности.
  • Для оценки логических и текстовых значений в ссылках используйте функцию VARA или VARPA.
  • Укажите не менее двух числовых значений для формула выборочной дисперсии и по крайней мере одно числовое значение для формула дисперсии населения в Excel, иначе #DIV/0! возникает ошибка.
  • Аргументы, содержащие текст, который нельзя интерпретировать как числа, приводят к ошибке #ЗНАЧ! ошибки.

Дисперсия по сравнению со стандартным отклонением в Excel

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

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

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

В сочетании со средним значением стандартное отклонение может сказать вам, сколько лет большинству тигров. Например, если среднее значение равно 8, а стандартное отклонение равно 4, возраст большинства тигров в зоопарке составляет от 4 (8 — 4) до 12 лет (8 + 4).
Рассчитать дисперсию и стандартное отклонение в Excel

Microsoft Excel имеет специальные функции для расчета стандартного отклонения выборки и генеральной совокупности. Подробное объяснение всех функций можно найти в этом руководстве: Как рассчитать стандартное отклонение в Excel.

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

Практическая рабочая тетрадь

Вычислить дисперсию в Excel — примеры (файл .xlsx)

Вас также могут заинтересовать

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *