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

Как рассчитать дисперсию в Excel — образец & формула дисперсии популяции

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Имя	Версия Excel	Тип данных	Текст и логика
VAR	2000 — 2019	Образец	Игнорируется
VAR.S	2010 — 2019	Образец	Игнорируется
VARA	2000 — 2019	Образец	Оценено
VARP	2000 — 2019	Население	Игнорируется
VAR.P	2010 — 2019	Население	Игнорируется
VARPA	2000 — 2019	Население	Оценено

VAR.S против VARA и VAR.P против VARPA

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

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

A образец это набор данных, взятых из всей совокупности. А дисперсия, рассчитанная по выборке, называется дисперсия выборки .

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

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

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

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

Функция VAR в Excel

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

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

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

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

Функция VARA в Excel

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

Формула выборочной дисперсии в Excel

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

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

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

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

1. Найдите среднее значение с помощью функции AVERAGE: = СРЕДНЕЕ(B2:B7) Среднее значение попадает в любую пустую ячейку, скажем, B8.
2. Вычтите среднее значение из каждого числа в выборке: =B2-$B$8 Различия переходят в колонку C, начиная с C2.
3. Возведите в квадрат каждую разность и запишите результаты в столбец D, начиная с D2: =C2^2
4. Сложите квадраты разностей и разделите результат на количество предметов в выборке минус 1: =SUM(D2:D7)/(6-1)

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

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

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

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

Дисперсию популяции можно найти с помощью этой формулы:

Смотрите также: Как построить диаграмму рассеяния в Excel

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

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

Функция VARP в Excel

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

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

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

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

Функция VARPA в Excel

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

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

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

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

И все формулы будут возвращать одинаковый результат:

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

Если некоторые студенты не сдавали экзамен и вместо номера балла у них стоит N/A, функция VARPA вернет другой результат. Причина в том, что VARPA оценивает текстовые значения как нули, а VARP и VAR.P игнорируют текстовые и логические значения в ссылках. Подробную информацию смотрите в разделе VAR.P против VARPA.

Смотрите также: Автоответчик вне офиса в Outlook, Gmail и Outlook.com

Формула отклонения в Excel — советы по использованию

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

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

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

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

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

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

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

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

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

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

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

Предыдущий пост Ссылка Excel на другой лист или рабочую книгу (внешняя ссылка)

Michael Brown

Майкл Браун — увлеченный технологический энтузиаст, стремящийся упростить сложные процессы с помощью программных инструментов. Имея более чем десятилетний опыт работы в технологической отрасли, он отточил свои навыки в Microsoft Excel и Outlook, а также в Google Sheets и Docs. Блог Майкла посвящен тому, чтобы делиться своими знаниями и опытом с другими, предоставляя простые советы и учебные пособия для повышения производительности и эффективности. Являетесь ли вы опытным профессионалом или новичком, в блоге Майкла вы найдете ценную информацию и практические советы, которые помогут вам максимально эффективно использовать эти важные программные инструменты.

Как рассчитать коэффициент дисперсии в Excel (3 метода)

В Excel пользователи рассчитывают различные Статистика свойства для демонстрации дисперсии данных. По этой причине пользователи пытаются вычислить Коэффициент вариации в Excel. Вычисление Коэффициент вариации ( АВТОБИОГРАФИЯ ) легко с помощью функции Excel STDEV.P или STDEV. S встроенные функции, а также типичные Статистические формулы .

Допустим, у нас есть набор данных, рассматриваемый как Население ( Установите ) или Образец и мы хотим вычислить Коэффициент вариации ( АВТОБИОГРАФИЯ ).

В этой статье мы демонстрируем типичные Статистика формула, а также STDEV.P и STDEV.S функции для вычисления Коэффициент вариации в Excel.

Скачать рабочую книгу Excel

Расчет коэффициента вариации.xlsx

Что такое коэффициент дисперсии?

В целом Коэффициент вариации ( АВТОБИОГРАФИЯ ) называется соотношением между Стандартное отклонение ( σ ) и среднее или среднее значение ( μ ). Она показывает степень изменчивости по отношению к Среднее или Средний из Население (Установить) или Образец . Итак, есть 2 отдельные формулы для Коэффициент вариации ( АВТОБИОГРАФИЯ ). Это:

�� Коэффициент вариации ( АВТОБИОГРАФИЯ ) для Население или Установите ,

�� Коэффициент вариации ( АВТОБИОГРАФИЯ ) для Образец ,

⏩ Здесь Стандартное отклонение для Население,

⏩ The Стандартное отклонение для Образец ,

3 простых способа вычисления коэффициента вариации в Excel

Если пользователи следуют формуле Статистики для расчета Коэффициент вариации ( АВТОБИОГРАФИЯ ), им сначала нужно найти стандартное отклонение для Население ( σ ) или Образец ( S ) и Среднее или Средний ( μ ). В качестве альтернативы пользователи могут использовать STDEV.P и STDEV.S рассчитать Население и Образец варианты Стандартное отклонение расчет. для подробного расчета следуйте приведенному ниже разделу.

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

Перед расчетом Коэффициент вариации ( АВТОБИОГРАФИЯ ) пользователям необходимо задать данные для поиска компонентов формулы. Как мы уже упоминали ранее, в формуле Формула статистики для Коэффициент вариации ( АВТОБИОГРАФИЯ ) это

Коэффициент вариации для Население ,

Или

Коэффициент вариации для Образец ,

�� Настройка данных

Пользователям необходимо вручную найти Коэффициент вариации ( АВТОБИОГРАФИЯ ) компоненты формулы, такие как Средний ( μ ), Отклонение ( xi-μ ), и Сумма квадратов отклонений ( ∑(xi-μ)2 ), чтобы иметь возможность рассчитать Коэффициент вариации ( АВТОБИОГРАФИЯ ).

⏩ Вычисление среднего значения (μ)

Первый этап расчета Коэффициент вариации это вычислить Средний данных. Используйте СРЕДНЕЕ функция для вычисления Средний или Среднее из заданного набора данных. Используйте приведенную ниже формулу в любой ячейке (т.е, C14 ).

= СРЕДНЕЕ(C5:C13)

⏩ Нахождение отклонения (x _i -μ)

После этого пользователи должны найти Отклонение от среднего значения ( x _i -μ) . Это минусовое значение каждой записи ( x _i ) к Средний ( μ) значение. Введите приведенную ниже формулу в Отклонение (т.е, Колонка D ) клетки.

=C5-$C$14

⏩ Нахождение суммы квадратов отклонений ∑(xi-μ) 2

Сейчас, Квадрат отклонения значения (xi-μ)2 и поместить данные в соседние ячейки (т.е., Колонка E ). Затем просуммируйте квадратные значения в ячейке E14 Просто используйте SUM функция в E14 ячейку, чтобы найти сумму квадратов отклонений.

=SUM(E5:E13)

Сайт SUM функция обеспечивает общее значение Колонка E .

⏩ Вычисление стандартного отклонения (σ или S )

Сайт Стандартное отклонение для Население ( σ ) имеет свою формулу

Стандартное отклонение для Население ( Установите ),

Таким образом, вычисляя Стандартное отклонение должна быть формула, применяемая в G6 клетка.

➤ Вставьте приведенную ниже формулу в G6 ячейку, чтобы найти Стандартное отклонение ( σ ).

Смотрите также: Как выделить дублирующиеся строки в Excel (3 способа)
=SQRT(E14/COUNT(C5:C13))

Сайт SQRT функция дает значение квадратного корня, а COUNT функция возвращает суммарные номера записей.

➤ Нажмите или нажмите Войти для применения формулы и Стандартное отклонение значение появляется в ячейке G6 .

Опять же, используйте Образец версия Стандартное отклонение формула для нахождения Стандартное отклонение . Формула,

Стандартное отклонение для Образец ,

➤ Введите следующую формулу в ячейку H6 для отображения Стандартное отклонение .

=SQRT(E14/(COUNT(C5:C13)-1))

Смотрите также: Как отформатировать номер телефона с кодом страны в Excel (5 способов)

➤ Используйте Войти чтобы применить формулу в H6 клетка.

⏩ Расчет коэффициента вариации (CV)

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

➤ Выполните следующую формулу в ячейке G11 чтобы найти Коэффициент вариации для Население ( Установите ).

=G6/C14

➤ Нажмите кнопку Войти чтобы применить приведенную ниже формулу в ячейке H11 чтобы найти Коэффициент вариации для Образец .

=H6/C14

�� Наконец-то Коэффициент вариации для обоих вариантов отображается в ячейках G11 и H11 как видно из приведенного ниже скриншота.

Читать далее: Как сделать анализ отклонений в Excel (с быстрыми шагами)

Похожие чтения

• Как рассчитать объединенную дисперсию в Excel (с помощью простых шагов)
• Расчет дисперсии портфеля в Excel (3 разумных подхода)
• Как рассчитать процент отклонений в Excel (3 простых способа)

Метод 2: Расчет коэффициента вариации (CV) с помощью функций STDEV.P и AVERAGE

Excel предлагает множество встроенных функций для выполнения различных операций. Статистика расчеты. STDEV.P Функция принимает числа в качестве аргументов.

Как мы уже упоминали ранее, что Коэффициент вариации ( АВТОБИОГРАФИЯ ) представляет собой квант двух компонентов (т.е, Стандартное отклонение ( σ ) и Средний ( μ )). STDEV.P функция находит Стандартное отклонение ( σ ) для Население и СРЕДНЕЕ функция приводит к Средний ( μ ) или Среднее .

Шаг 1: Используйте следующую формулу в ячейке E6 .

=STDEV.P(C5:C13)/СРЕДНЕЕ(C5:C13)

Сайт STDEV.P функция возвращает стандартное отклонение для популяции и СРЕДНЕЕ функция выводит среднее или среднеарифметическое значение.

Шаг 2: Нажмите кнопку Войти чтобы применить формулу. Мгновенно в Excel отобразится значение Коэффициент вариации ( АВТОБИОГРАФИЯ ) в Процент предварительно отформатированную ячейку.

Читать далее: Как рассчитать разброс в Excel (простое руководство)

Метод 3: Использование функций STDEV.S и AVERAGE для Рассчитать коэффициент дисперсии

Альтернатива STDEV.P функция, Excel имеет STDEV.S для выборочных данных для расчета Стандартное отклонение ( σ ). Аналогично STDEV.P функция, STDEV.S принимает числа в качестве аргументов. Типичный Коэффициент отклонения ( АВТОБИОГРАФИЯ ) формула представляет собой соотношение между Стандартное отклонение ( σ ) и Средний ( μ ).

Шаг 1: Используйте следующую формулу в ячейке E6 .

=STDEV.S(C5:C13)/СРЕДНЕЕ(C5:C13)

Шаг 2: Теперь используйте Войти для отображения кнопки Коэффициент отклонения в камере E6 .

Читать далее: Как рассчитать дисперсию с помощью таблицы Pivot Table в Excel (с простыми шагами)

Заключение

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

Предыдущий пост Как автоматически нумеровать ячейки в Excel (10 методов)
Следующий пост Как отфильтровать дубликаты в Excel (7 простых способов)

Hugh West

Хью Уэст — опытный тренер и аналитик Excel с более чем 10-летним опытом работы в отрасли. Он имеет степень бакалавра в области бухгалтерского учета и финансов и степень магистра делового администрирования. Хью страстно любит преподавать и разработал уникальный подход к обучению, которому легко следовать и который легко понять. Его экспертные знания Excel помогли тысячам студентов и специалистов по всему миру улучшить свои навыки и преуспеть в своей карьере. В своем блоге Хью делится своими знаниями со всем миром, предлагая бесплатные учебные пособия по Excel и онлайн-обучение, чтобы помочь отдельным лицам и компаниям полностью раскрыть свой потенциал.

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

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

I. Понимание дисперсии

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

А. Определение дисперсии

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

Б. Важность дисперсии

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

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

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

А. Подготовка ваших данных

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

B. Расчет дисперсии с использованием формул

1. Ввод формулы: в пустой ячейке используйте формулу =VAR.P(.
2. Выбор диапазона данных: выделите диапазон ячеек, содержащих ваши данные.
3. Закрытие формулы: закройте формулу с помощью ) и нажмите Enter.

III. Советы по точному расчету дисперсии

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

А. Очистка данных

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

Б. Понимание функций Excel

Ознакомьтесь с другими функциями Excel, такими как ДИСП.С, которая рассчитывает дисперсию для выборки, а не для всей генеральной совокупности.

IV. Общие проблемы

Даже опытные пользователи Excel сталкиваются с проблемами. Давайте рассмотрим некоторые распространенные проблемы.

А. Работа с недостающими данными

Если ваш набор данных содержит пробелы, используйте функцию ЕСЛИОШИБКА, чтобы управлять недостающими данными, не влияя на расчет дисперсии.

V. Часто задаваемые вопросы (часто задаваемые вопросы)

А. Может ли дисперсия быть отрицательной?

Дисперсия всегда неотрицательна. Отрицательный результат указывает на ошибку в расчете.

Б. Как часто мне следует рассчитывать дисперсию?

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

C. На что указывает высокая дисперсия?

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

D. Есть ли в Excel встроенные функции для расчета выборочной дисперсии?

Да, Excel предлагает функции как генеральной дисперсии (VAR.P), так и выборочной дисперсии (VAR.S).

E. Могут ли значения дисперсии помочь в принятии решений?

Абсолютно! Значения дисперсии помогают понять распределение данных, способствуя принятию более обоснованных решений.

F. Существуют ли упрощенные методы расчета отклонений?

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

VI. Вывод

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

Похожие посты:

1. Как рассчитать стандартное отклонение в Excel
2. Как рассчитать возраст в Excel
3. Как посчитать среднее значение в Excel

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

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

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

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

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

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

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

1. Вычислите среднее (простое среднее) пяти чисел:
   
2. Из каждого числа вычтите среднее значение, чтобы найти различия. Для наглядности нанесем различия на график:
   
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.

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

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

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

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

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

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

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

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

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

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

1. Найдите среднее значение с помощью функции СРЗНАЧ:
   =СРЕДНЕЕ(B2:B7) Среднее значение идет в любую пустую ячейку, скажем, B8.
2. Вычтите среднее значение из каждого числа в выборке:
   =B2-$B$8 Различия идут в столбец C, начиная с C2.
3. Возведите в квадрат каждую разницу и поместите результаты в столбец D, начиная с D2:
   =С2^2
4. Сложите квадраты разностей и разделите результат на количество элементов в выборке минус 1:
   =СУММ(D2:D7)/(6-1)

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

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

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

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

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

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

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

Функция VARP в Excel

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

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

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

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

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

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

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

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

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

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

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

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

Формула дисперсии в 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).

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

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