ВАРІАНТ - Excel та Google Таблиці

Цей підручник демонструє, як користуватися Функція Excel VARIANCE в Excel для оцінки дисперсії на основі даної вибірки.

Огляд функції VARIANCE

Функція VARIANCE Обчислює оціночну дисперсію на основі даної вибірки.

Щоб скористатися функцією робочого аркуша Excel VARIANCE, виберіть клітинку та введіть:

(Зверніть увагу, як виглядають вхідні дані формул)

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

1	= VAR (число1, [число2],…)

цифри- Значення для отримання дисперсії

Як розрахувати дисперсію в Excel

Дисперсія вказує на те, наскільки значення в наборі даних розкинулися від середнього значення. З математичної точки зору, дисперсія - це середнє значення квадратної різниці кожного балу від середнього значення (але незабаром ми до цього дійдемо).

Excel надає вам ряд функцій для розрахунку дисперсії - VAR.S, VAR.P, VARA, VARPA та дві старі функції - VAR та VARP.

Перш ніж ми перейдемо до цих функцій і навчимося ними користуватися, давайте поговоримо про дисперсію та спосіб її обчислення.

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

При аналізі даних загальним першим кроком є ​​обчислення середнього значення. Звичайно, це корисна статистика для розрахунку, але вона не дає вам повного уявлення про те, що відбувається з вашими даними.

Візьміть наступний набір даних, який може бути групою результатів тестів, набраних із 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, тому я просто віднімаю це від кожного значення в таблиці. Знаки долара тут просто "заблокують" посилання на цю клітинку для Н4, так що коли я копіюю його у стовпці, він залишається незмінним.

Результати:

У нас є:

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
• VARA є таким же, як і VAR.S, за винятком того, що містить текстові клітинки та логічні значення
• VARPA є таким же, як і VAR.P, за винятком того, що він включає текстові комірки та логічні значення

Давайте розглянемо ці питання один за одним.

Функція Excel VAR.P

VAR.P обчислює дисперсію для даних про населення (використовуючи метод поділу на n). Використовуйте його так:

1	= VAR.P (C4: C6)

У VAR.P ви визначаєте лише один аргумент: діапазон даних, для якого потрібно обчислити дисперсію. У нашому випадку це значення карт у C4: C6.

Як ви бачите вище, VAR.P повертає 2.666 за наш набір з трьох карт. Це те саме значення, яке ми розраховували вручну раніше.

Зауважте, що VAR.P повністю ігнорує клітинки, що містять текст або булеві значення (TRUE/FALSE). Якщо вам потрібно включити їх, використовуйте VARPA.

Функція Excel VAR.S

VAR.S обчислює дисперсію для вибіркових даних (ділення на n-1). Ви використовуєте його так:

1	= VAR.S (C4: C6)

Знову ж таки, є лише один аргумент - ваш діапазон даних.

У цьому випадку VAR.S повертає 4. Ми отримали таку саму цифру на кроці 4, коли ми зробили ручний розрахунок вище.

VAR.S повністю ігнорує клітинки, що містять текст або булеві значення (TRUE/FALSE). Якщо вам потрібно включити їх, використовуйте VARA.

Функція Excel VAR

VAR повністю еквівалентний VAR.S: він обчислює дисперсії для вибіркових даних (використовуючи метод n-1). Ось як його використовувати:

1	= VAR (C4: C6)

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

Функція Excel VARA

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

• Значення TRUE зараховуються як 1
• Значення FALSE зараховуються як 0
• Текстові рядки зараховуються як 0

Ось як ви ним користуєтесь:

1	= VARA (C4: C11)

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

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

Функція Excel VARPA

VARPA обчислює дисперсію даних населення. Він подібний до VAR.P, за винятком того, що він також включає в обчислення булеві значення та текстові рядки:

• Значення TRUE зараховуються як 1
• Значення FALSE зараховуються як 0
• Текстові рядки зараховуються як 0

Ви використовуєте його так:

1	= VARPA (C4: C12)

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

В результаті додавання цієї групи менших значень до даних дисперсія збільшилася до 8,984.

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

Функція CORREL працює точно так само в Google Таблицях, як і в Excel: