Як розрахувати Z-бал за допомогою Microsoft Excel

Z-Score - це статистичне значення, яке показує, скільки стандартних відхилень має певне значення від середнього значення всього набору даних. Ви можете використовувати формули AVERAGE і STDEV.S або STDEV.P, щоб обчислити середнє та стандартне відхилення ваших даних, а потім використовувати ці результати для визначення Z-балів кожного значення.

Що таке Z-Score і що роблять функції AVERAGE, STDEV.S і STDEV.P?

Z-Score є простим способом порівняння значень з двох різних наборів даних. Вона визначається як число стандартних відхилень від середньої точки даних. Загальна формула виглядає так:

= (DataPoint-AVERAGE (DataSet)) / STDEV (набір даних)

Ось приклад, який допоможе уточнити. Скажімо, ви хотіли порівняти результати тестування двох студентів-алгебр, які викладають різні вчителі. Ви знаєте, що перший студент отримав 95% на заключному іспиті в одному класі, а студент іншого класу набрав 87%.

На перший погляд, ступінь 95% є більш вражаючим, але що, якщо викладач другого класу здав більш складний іспит? Ви можете обчислити Z-бал за кожною оцінкою кожного студента на основі середніх балів у кожному класі та стандартного відхилення балів у кожному класі. Порівняння Z-балів двох студентів може виявити, що студент з 87% балів зробив краще в порівнянні з рештою свого класу, ніж студент з 98% балів зробив у порівнянні з рештою свого класу..

Перше статистичне значення, яке вам потрібно, це "середнє" і функція Excel "AVERAGE" обчислює це значення. Він просто додає всі значення в діапазоні комірок і ділить цю суму на кількість комірок, що містять числові значення (вона ігнорує порожні клітинки).

Іншою статистичною величиною, яка нам потрібна, є "стандартне відхилення", а Excel має дві різні функції для розрахунку стандартного відхилення дещо по-різному.

Попередні версії Excel мали лише функцію «STDEV», яка обчислювала стандартне відхилення, розглядаючи дані як «зразок» населення. Excel 2010 розбив це на дві функції, які обчислюють стандартне відхилення:

• STDEV.S: Ця функція ідентична попередній функції "STDEV". Він обчислює стандартне відхилення, розглядаючи дані як «вибірки» населення. Вибірка населення може бути чимось на зразок конкретних комарів, зібраних для дослідницького проекту, або автомобілів, які були відкладені та використані для тестування на аварію.
• STDEV.P: Ця функція обчислює стандартне відхилення при обробці даних як усього населення. Ціле населення було б щось подібне до всіх комарів на Землі або в кожному автомобілі у виробничому циклі конкретної моделі.

Вибір, який ви обираєте, базується на наборі даних. Різниця зазвичай невелика, але результат функції “STDEV.P” завжди буде меншим, ніж результат функції “STDEV.S” для того ж самого набору даних. Це більш консервативний підхід до припущення, що дані є більш різноманітними.

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

Для нашого прикладу ми маємо дві колонки ("Значення" і "Z-Score") і три "помічники" для зберігання результатів функцій "AVERAGE", "STDEV.S" і "STDEV.P". У стовпці "Значення" міститься десять випадкових чисел, розташованих по центру, і стовпець "Z-Score", де ми обчислимо Z-бал, використовуючи результати, збережені в клітинах-помічниках..

По-перше, будемо обчислювати середнє значення за допомогою функції "AVERAGE". Виберіть осередок, в якій буде збережено результат функції “AVERAGE”.

Введіть наступну формулу і натисніть введіть -або - використовуйте меню "Формули".

= СЕРЕДНЯ (E2: E13)

Щоб отримати доступ до функції через меню "Формули", виберіть спадне меню "Додаткові функції", виберіть параметр "Статистичний", а потім натисніть кнопку "СЕРЕДНІЙ".

У вікні Аргументи функцій оберіть усі клітинки у стовпці “Значення” як вхідне поле для поля “Номер1”. Вам не потрібно турбуватися про поле "Number2".

Тепер натисніть "OK".

Далі потрібно обчислити стандартне відхилення значень за допомогою функції "STDEV.S" або "STDEV.P". У цьому прикладі ми покажемо, як обчислити обидва значення, починаючи з "STDEV.S.". Виберіть клітинку, в якій буде збережено результат.

Щоб розрахувати стандартне відхилення за допомогою функції "STDEV.S", введіть цю формулу і натисніть клавішу Enter (або відкрийте меню через меню "Формули").

= STDEV.S (E3: E12)

Щоб отримати доступ до функції за допомогою меню "Формули", виберіть спадне меню "Додаткові функції", виберіть параметр "Статистичний", прокрутіть униз, а потім натисніть команду "STDEV.S".

У вікні Аргументи функцій оберіть усі клітинки у стовпці “Значення” як вхідне поле для поля “Номер1”. Вам також не потрібно турбуватися про поле "Number2".

Тепер натисніть "OK".

Далі будемо обчислювати стандартне відхилення за допомогою функції «STDEV.P». Виберіть комірку, в якій буде збережено результат.

Щоб розрахувати стандартне відхилення за допомогою функції "STDEV.P", введіть цю формулу і натисніть клавішу Enter (або відкрийте меню через меню "Формули").

= STDEV.P (E3: E12)

Щоб отримати доступ до функції через меню "Формули", виберіть спадне меню "Інші функції", виберіть параметр "Статистичний", прокрутіть униз трохи вниз, а потім натисніть формулу "STDEV.P".

У вікні Аргументи функцій оберіть усі клітинки у стовпці “Значення” як вхідне поле для поля “Номер1”. Знову ж таки, вам не доведеться турбуватися про поле "Number2".

Тепер натисніть "OK".

Тепер, коли ми розрахували середнє і стандартне відхилення наших даних, ми маємо все необхідне для розрахунку Z-балів. Ми можемо використовувати просту формулу, яка посилається на клітинки, що містять результати функцій "AVERAGE" та "STDEV.S" або "STDEV.P".

Виберіть першу клітинку у стовпці "Z-Score". Для цього прикладу ми використаємо результат функції "STDEV.S", але ви також можете скористатися результатом з "STDEV.P".

Введіть таку формулу та натисніть Enter:

= (E3- $ G $ 3) / $ H $ 3

Крім того, можна вводити формулу, не вводячи:

1. Натисніть клітинку F3 і введіть тип = (
2. Виберіть клітинку E3. (Ви можете натиснути кнопку клавіша зі стрілкою вліво один раз або використовуйте мишу)
3. Введіть знак мінус -
4. Виберіть клітинку G3, а потім натисніть F4 щоб додати символи "$", щоб зробити "абсолютне" посилання на клітинку (вона буде крутитися через "G3">$G$3 ">" G$3 ">"$G3 ">" G3 ", якщо ви продовжуєте натискати F4)
5. Тип ) /
6. Виберіть осередок H3 (або I3, якщо використовується «STDEV.P») і натисніть F4 , щоб додати два символи "$".
7. Натисніть Enter

Z-Score розрахований для першого значення. Це стандартне відхилення 0,15945 нижче середнього. Щоб перевірити результати, можна помножити стандартне відхилення на цей результат (6.271629 * -0.15945) і перевірити, що результат дорівнює різниці між значенням і середнім значенням (499-500). Обидва результати рівні, тому значення має сенс.

Розрахуємо Z-бали решти значень. Виділіть весь стовпець "Z-Score", починаючи з клітинки, що містить формулу.

Натисніть комбінацію клавіш Ctrl + D, яка копіює формулу у верхній комірці вниз через всі інші виділені комірки.

Тепер формула "заповнена" всіма клітинами, і кожен з них завжди посилається на правильні клітинки "AVERAGE" і "STDEV.S" або "STDEV.P" через символи "$". Якщо ви отримуєте помилки, поверніться назад і переконайтеся, що символи "$" включені до введеної формули.

Розрахунок Z-Score без використання осередків 'Helper'

Клітини-помічники зберігають результат, подібний до тих, що зберігають результати функцій «СЕРЕДНЬО», «STDEV.S» і «STDEV.P». Вони можуть бути корисними, але не завжди потрібні. Ви можете пропустити їх взагалі, обчислюючи Z-Score, використовуючи наступні узагальнені формули.

Нижче наведено функцію "STDEV.S":

= (Значення-AVERAGE (значення)) / STDEV.S (значення)

І один, використовуючи функцію "STEV.P":

= (Value-AVERAGE (значення)) / STDEV.P (значення)

При введенні діапазонів клітин для "Значень" у функціях, не забудьте додати абсолютні посилання ("$" за допомогою F4), так що, коли ви "заповнюєте", ви не обчислюєте середнє або стандартне відхилення іншого діапазону клітин у кожній формулі.

Якщо у вас є великий набір даних, ефективніше використовувати клітинки-помічники, оскільки він не обчислює результат функцій "AVERAGE", "STDEV.S" або "STDEV.P", зберігаючи ресурси процесора і прискорення часу обчислення результатів.

Крім того, "$ G $ 3" ​​займає менше байтів для зберігання і менше завантаження оперативної пам'яті, ніж "AVERAGE ($ E $ 3: $ E $ 12)". Це важливо, оскільки стандартна 32-розрядна версія Excel обмежена 2 Гб оперативної пам'яті (64-розрядна версія не має жодних обмежень щодо кількості оперативної пам'яті).