VARIANCE - Excel и Google Таблицы

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

Обзор функции VARIANCE

Функция VARIANCE Вычисляет дисперсию оценки на основе заданной выборки.

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

(Обратите внимание, как появляются входные данные формулы)

Функция VARIANCE Синтаксис и входные данные:

1	= ДИСП (число1; [число2];…)

числа- Значения для получения дисперсии

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

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

Excel предоставляет вам ряд функций для вычисления дисперсии - ДИСПР ..S, ДИСПР.П, ДИСП, ДИСПРА и две старые функции ДИСП и ДИСПР.

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

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

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

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

1	48,49,50,51,52

Среднее значение этого диапазона равно 50 (суммируйте числа и разделите на n, где n - количество значений).

Затем возьмите следующий набор результатов теста:

1	10,25,50,75,90

Среднее значение этого диапазона также 50 - но очевидно, что у нас здесь два очень разных диапазона данных.

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

Дисперсия также используется как базовая точка для ряда более сложных статистических процедур.

Как рассчитывается дисперсия

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

Скажем, у нас есть набор данных, представляющий три игральные карты: 4, 6 и 8.

Чтобы вычислить дисперсию, вы прорабатываете этот процесс:

1) Рассчитайте среднее значение

Сначала мы вычисляем среднее значение. Мы знаем, что наш диапазон данных составляет 4, 6, 8, поэтому среднее значение будет:

1	(6 + 4 + 8) / 3 = 6

Я подтвердил это ниже с помощью функции Excel AVERAGE <>:

1	= СРЕДНИЙ (C4: C6)

2) Вычтите среднее значение из каждого значения в наборе данных

Затем мы вычитаем среднее значение из каждого из наших значений.

Я сделал это по следующей формуле:

1	= C4- $ H $ 4

Среднее значение хранится в H4, поэтому я просто вычитаю его из каждого значения в таблице. Знаки доллара здесь просто «блокируют» ссылку на эту ячейку на H4, поэтому, когда я копирую ее в столбец, она остается неизменной.

Результаты, достижения:

Мы получили:

123	4 - 6 = -26 - 6 = 08 - 6 = 2

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

3) Возвести различия в квадрат

Давайте добавим новый столбец и возведем в квадрат числа в столбце D:

1

= D4 * D4

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

4) Рассчитайте среднее значение квадратов разностей.

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

• Если вы используете данные о населении, вы просто принимаете среднее значение как нормальное (суммируете значения и делите на n)
• Если вы используете образец данных, вы суммируете значения и делите их на n-1

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

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

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

1	= СРЕДНИЙ (E4: E8)

Таким образом, дисперсия нашей популяции составляет 2,666.

Если это было образцы данных (возможно, мы вытащили эти три карты из большего набора), мы вычислим среднее значение следующим образом:

1	Выборочная дисперсия = (4 + 0 + 4) / (3 - 1)

Или:

1	Дисперсия выборки = 8/2 = 4

Зачем делить на n-1 с помощью выборки данных, а не просто n?

Краткий ответ на этот вопрос: «Потому что он дает правильный ответ». Но я полагаю, вам нужно немного большего! Это сложная тема, поэтому я просто дам краткий обзор здесь.

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

Это означает, что если вы просто разделите на n, вы немного недооцените дисперсию генеральной совокупности. Деление на n-1 это немного поправляет.

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

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

Имея всего девять возможных выборок, мы можем вычислить каждую возможную дисперсию выборки, используя оба метода (разделить на n и разделить на n-1), взять среднее из них и посмотреть, какой из них дает нам правильный ответ.

В таблице ниже я все выложил. Каждая строка таблицы представляет собой отдельный образец, а столбцы B и C показывают две карты, выбранные в каждой выборке. Затем я добавил еще два столбца: в одном я вычислил дисперсию этой выборки из двух карточек путем деления на n, а другой - на n - 1.

Взглянем:

Справа от таблицы я показал средние значения столбцов D и E.

Среднее значение столбца D при делении на n дает нам дисперсию 1,333.

Среднее значение столбца E при делении на n-1 дает нам дисперсию 2,666.

Мы уже знаем из нашего предыдущего примера, что дисперсия генеральной совокупности составляет 2,666. Таким образом, деление на n-1 при использовании выборочных данных дает нам более точные оценки.

Функции Excel для расчета дисперсии

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

Здесь у вас есть несколько вариантов:

• п возвращает дисперсию для данных о населении (с использованием метода деления на n)
• S возвращает дисперсию для выборочных данных (делится на n-1)
• VAR это более старая функция, которая работает точно так же, как VAR.S
• ВАРА то же самое, что VAR.S, за исключением того, что он включает текстовые ячейки и логические значения
• ВАРПА то же самое, что и VAR.P, за исключением того, что он включает текстовые ячейки и логические значения

Давайте рассмотрим их по очереди.

Функция Excel VAR.P

VAR.P вычисляет дисперсию данных о населении (используя метод деления на n). Используйте это так:

1	= ДИСПР.П (C4: C6)

Вы определяете только один аргумент в VAR.P: диапазон данных, для которого вы хотите вычислить дисперсию. В нашем случае это значения карт в C4: C6.

Как вы видите выше, VAR.P возвращает 2,666 для нашего набора из трех карт. Это то же самое значение, которое мы вычислили вручную ранее.

Обратите внимание, что VAR.P полностью игнорирует ячейки, содержащие текст или логические (ИСТИНА / ЛОЖЬ) значения. Если вам нужно включить их, используйте вместо этого VARPA.

Функция Excel VAR.S

VAR.S вычисляет дисперсию для выборочных данных (деление на n-1). Вы используете это так:

1	= ДИСПР. S (C4: C6)

Опять же, есть только один аргумент - ваш диапазон данных.

В этом случае VAR.S возвращает 4. То же значение мы получили на шаге 4, когда выполняли ручной расчет выше.

VAR.S полностью игнорирует ячейки, содержащие текст или логические (ИСТИНА / ЛОЖЬ) значения. Если вам нужно включить их, используйте вместо этого VARA.

Функция Excel VAR

VAR полностью эквивалентен VAR.S: он вычисляет дисперсии для выборочных данных (с использованием метода n-1). Вот как это использовать:

1	= ДИСП (C4: C6)

VAR - это «функция совместимости». Это означает, что Microsoft удаляет эту функцию из Excel. На данный момент он все еще доступен для использования, но вместо этого вам следует использовать VAR.S, чтобы ваши электронные таблицы оставались совместимыми с будущими версиями Excel.

Функция Excel VARA

VARA также возвращает дисперсию выборочных данных, но имеет некоторые ключевые отличия от VAR и VAR.S. А именно, он включает в свой расчет логические и текстовые значения:

• ИСТИННЫЕ значения считаются как 1
• Значения FALSE считаются как 0
• Текстовые строки считаются как 0

Вот как это использовать:

1	= ДИСПА (C4: C11)

Мы добавили в таблицу еще пять строк: J, Q, K, TRUE и FALSE. Столбец D показывает, как VARA интерпретирует эти значения.

Поскольку в нашей таблице теперь есть новая партия низких значений, дисперсия увеличилась до 10,268.

Функция Excel VARPA

VARPA вычисляет дисперсию данных о населении. Он похож на VAR.P, за исключением того, что он также включает в расчет логические значения и текстовые строки:

• ИСТИННЫЕ значения считаются как 1
• Значения FALSE считаются как 0
• Текстовые строки считаются как 0

Вы используете это так:

1	= ДИСПР (C4: C12)

Мы добавили в таблицу еще пять строк: J, Q, K, TRUE и FALSE. Столбец D показывает, как VARPA интерпретирует эти значения.

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

Функция VARIANCE в Google Таблицах

Функция CORREL работает в Google Таблицах точно так же, как и в Excel: