Z-оцінка – це статистичний показник, який відображає, на скільки стандартних відхилень конкретне значення відхиляється від середнього значення у межах заданого набору даних. Для розрахунку середнього значення та стандартного відхилення використовуються функції AVERAGE та STDEV.S або STDEV.P. Ці обчислення є основою для визначення Z-оцінки кожного окремого значення.
Суть Z-оцінки та функції AVERAGE, STDEV.S і STDEV.P
Z-оцінка дозволяє легко порівнювати дані з різних наборів, показуючи, наскільки далеко конкретна точка даних відхиляється від середнього значення. Формула для розрахунку виглядає так:
=(Значення-AVERAGE(НабірДаних))/STDEV(НабірДаних)
Розглянемо приклад. Припустимо, ви хочете порівняти результати іспитів з алгебри двох учнів, які навчалися у різних викладачів. Перший учень отримав 95%, а другий – 87%. На перший погляд, 95% виглядає краще, але що, якщо іспит другого учня був складнішим? Обчислення Z-оцінки для кожного учня, враховуючи середні бали та стандартне відхилення у кожному класі, дозволить виявити, хто з учнів показав кращий результат відносно свого класу. Учень з 87% може виявитися успішнішим у порівнянні зі своїм класом, ніж учень з 95% у своєму.
Для обчислення Z-оцінки нам потрібне середнє значення. Excel надає функцію “AVERAGE”, яка обчислює середнє арифметичне, сумуючи всі значення в діапазоні комірок і ділячи на їх кількість. Пусті клітинки ігноруються.
Іншим важливим показником є стандартне відхилення. Excel пропонує дві функції для його обчислення:
- STDEV.S: Ця функція розглядає дані як “вибірку” з більшої сукупності. Наприклад, це можуть бути дані про комарів, зібраних для дослідження, або автомобілі, використані в краш-тестах.
- STDEV.P: Ця функція розглядає дані як повну “сукупність”. Прикладом може бути інформація про всіх комарів на Землі або всі автомобілі певної моделі, випущені протягом виробничого циклу.
Вибір функції залежить від характеру даних. Хоча різниця між результатами цих функцій часто невелика, результат STDEV.P завжди буде меншим за STDEV.S для одного й того ж набору даних. Для консервативного підходу краще вважати, що дані мають більшу мінливість, використовуючи STDEV.S.
Приклад розрахунку Z-оцінки
Розглянемо приклад з двома стовпцями: “Значення” та “Z-оцінка”, а також трьома допоміжними комірками для зберігання результатів функцій “AVERAGE”, “STDEV.S” та “STDEV.P”. У стовпці “Значення” розміщені десять випадкових чисел близько 500. Стовпець “Z-оцінка” буде заповнений Z-оцінками, розрахованими на основі даних у допоміжних комірках.
Спочатку обчислимо середнє значення, вибравши комірку для результату функції “AVERAGE”.
Введіть формулу:
=AVERAGE(E2:E13)
або скористайтеся меню “Формули”, вибравши “Інші функції”, “Статистичні” та “AVERAGE”.
У вікні “Аргументи функції” виберіть усі клітинки зі стовпця “Значення” як вхідні дані для поля “Число1”. Поле “Число2” можна пропустити.
Натисніть “OK”.
Далі обчислимо стандартне відхилення за допомогою “STDEV.S”. Виберіть клітинку для результату.
Введіть формулу:
=STDEV.S(E3:E12)
або скористайтеся меню “Формули”, “Інші функції”, “Статистичні” та “STDEV.S”.
Виберіть клітинки стовпця “Значення” як вхідні дані для поля “Число1” у вікні “Аргументи функції”.
Натисніть “OK”.
Аналогічно розрахуємо стандартне відхилення за допомогою “STDEV.P”, вибравши відповідну комірку.
Введіть формулу:
=STDEV.P(E3:E12)
або скористайтеся меню “Формули”, “Інші функції”, “Статистичні” та “STDEV.P”.
Виберіть клітинки стовпця “Значення” як вхідні дані для поля “Число1”.
Натисніть “OK”.
Тепер, маючи середнє значення та стандартне відхилення, можемо обчислити Z-оцінку. Для цього використовуємо формулу, яка посилається на комірки з результатами “AVERAGE” та “STDEV.S” або “STDEV.P”.
Виберіть першу комірку у стовпці “Z-оцінка”. У цьому прикладі будемо використовувати “STDEV.S”, але можна застосувати й результат “STDEV.P”.
Введіть таку формулу та натисніть Enter:
=(E3-$G$3)/$H$3
Або виконайте такі кроки:
- Клацніть F3 та введіть “=”.
- Виберіть E3.
- Введіть “-“.
- Виберіть G3 та натисніть F4, щоб зробити посилання абсолютним (“$G$3”).
- Введіть “/”.
- Виберіть H3 (або I3, якщо використовуєте “STDEV.P”) та натисніть F4, щоб зробити посилання абсолютним.
- Натисніть Enter.
Z-оцінка для першого значення розрахована і становить -0,15945. Це означає, що значення на 0,15945 стандартних відхилень нижче середнього. Для перевірки, можна помножити стандартне відхилення на цю оцінку (6.271629 * -0.15945) і переконатися, що результат є різницею між значенням та середнім (499-500), що є вірно.
Щоб обчислити Z-оцінки для решти значень, виділіть увесь стовпець “Z-оцінка”, починаючи з комірки з формулою.
Натисніть Ctrl+D, щоб скопіювати формулу на всі виділені клітинки.
Формула застосована для всіх комірок, посилаючись на правильні значення “AVERAGE” та “STDEV.S” або “STDEV.P” через “$”. Якщо виникають помилки, переконайтеся, що символи “$” правильно вказані у формулі.
Обчислення Z-оцінки без допоміжних комірок
Допоміжні комірки, які зберігають проміжні результати, можуть бути корисними, але не завжди потрібні. Можна обчислити Z-оцінку, не використовуючи їх, застосувавши такі формули:
З використанням “STDEV.S”:
=(Значення-AVERAGE(Значення))/STDEV.S(Значення)
З використанням “STDEV.P”:
=(Значення-AVERAGE(Значення))/STDEV.P(Значення)
При введенні діапазонів комірок для “значення” у функціях, обов’язково використовуйте абсолютні посилання (“$” за допомогою F4), щоб уникнути обчислення середнього або стандартного відхилення для іншого діапазону комірок при “заповненні”.
Якщо у вас великий набір даних, використання допоміжних комірок може бути ефективнішим, оскільки це дозволяє не обчислювати результати функцій “AVERAGE” та “STDEV.S” або “STDEV.P” щоразу, економлячи ресурси процесора та прискорюючи обчислення. Також, “$G$3” займає менше місця для зберігання та оперативної пам’яті, ніж “AVERAGE($E$3:$E$12)”. Це важливо, оскільки 32-розрядна версія Excel обмежена 2 ГБ оперативної пам’яті (64-розрядна версія не має обмежень щодо обсягу оперативної пам’яті).