Як обчислити Z-Score за допомогою Microsoft Excel

| | 0 Comments| 12:10 AM
Categories:

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

Що таке Z-Score і що виконують функції AVERAGE, STDEV.S і STDEV.P?

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

=(DataPoint-AVERAGE(DataSet))/STDEV(DataSet)

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

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

Перше статистичне значення, яке вам потрібно, — це «середнє», і функція Excel «СРЕДНІЙ» обчислює це значення. Він просто складає всі значення в діапазоні комірок і ділить цю суму на кількість комірок, що містять числові значення (він ігнорує порожні клітинки).

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

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

  Як вимкнути показ сповіщень для веб-сайтів

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

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

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

Для нашого прикладу ми маємо два стовпці («Значення» та «Z-Score») і три «допоміжні» клітинки для зберігання результатів функцій «СРЕДНЄ», «STDEV.S» і «STDEV.P». Стовпець «Значення» містить десять випадкових чисел з центром навколо 500, а стовпець «Z-Score» – це місце, де ми обчислюємо Z-Score, використовуючи результати, збережені в клітинках «допоміжних».

Спочатку обчислимо середнє значення за допомогою функції «СЕРЕДНЄ». Виберіть комірку, в якій ви будете зберігати результат функції «СРЕДНІЙ».

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

=AVERAGE(E2:E13)

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

У вікні «Аргументи функції» виберіть усі клітинки в стовпці «Значення» як вхідні дані для поля «Число1». Вам не потрібно турбуватися про поле «Число2».

Тепер натисніть «ОК».

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

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

=STDEV.S(E3:E12)

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

  Де ви можете транслювати улюблені різдвяні пропозиції

У вікні «Аргументи функції» виберіть усі клітинки в стовпці «Значення» як вхідні дані для поля «Число1». Вам також не потрібно турбуватися про поле «Number2».

Тепер натисніть «ОК».

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

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

=STDEV.P(E3:E12)

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

У вікні «Аргументи функції» виберіть усі клітинки в стовпці «Значення» як вхідні дані для поля «Число1». Знову ж таки, вам не доведеться турбуватися про поле «Number2».

Тепер натисніть «ОК».

Тепер, коли ми розрахували середнє значення та стандартне відхилення наших даних, у нас є все, що потрібно для обчислення Z-Score. Ми можемо використовувати просту формулу, яка посилається на клітинки, що містять результати функцій «СРЕДНЄ» та «STDEV.S» або «STDEV.P».

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

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

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

Крім того, ви можете скористатися наведеними нижче кроками, щоб ввести формулу замість введення:

Клацніть клітинку F3 і введіть =(
Виберіть клітинку E3. (Ви можете натиснути клавішу зі стрілкою вліво один раз або скористатися мишею)
Введіть знак мінус –
Виберіть клітинку G3, а потім натисніть F4, щоб додати символи «$», щоб зробити «абсолютне» посилання на клітинку (вона буде циклічно переключатися між «G3» > «$G$3″ > «G$3» > «$G3» > «G3» ” якщо ви продовжуєте натискати F4)
Тип)/
Виберіть клітинку H3 (або I3, якщо ви використовуєте «STDEV.P») і натисніть F4, щоб додати два символи «$».
Натисніть Enter

  Як увімкнути або вимкнути переклад у Chrome

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

Давайте обчислимо Z-показники решти значень. Виділіть весь стовпець «Z-Score», починаючи з клітинки, що містить формулу.

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

Тепер формула була «заповнена» для всіх клітинок, і кожна з них завжди посилатиметься на правильні клітинки «СРЕДНЄ» та «STDEV.S» або «STDEV.P» через символи «$». Якщо ви отримуєте помилки, поверніться назад і переконайтеся, що символи «$» включені у введену формулу.

Розрахунок Z-Score без використання клітинок-помічників

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

Ось один із використанням функції «STDEV.S»:

=(Value-AVERAGE(Values))/STDEV.S(Values)

І один із використанням функції “STEV.P”:

=(Value-AVERAGE(Values))/STDEV.P(Values)

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

Якщо у вас великий набір даних, може бути ефективніше використовувати допоміжні клітинки, оскільки він не обчислює кожен раз результат функцій «СРЕДНІЙ» і «STDEV.S» або «STDEV.P», заощаджуючи ресурси процесора та прискорення часу, необхідного для обчислення результатів.

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