Функція FREQUENCY в Excel є потужним інструментом для аналізу даних, що дозволяє визначити, як часто певні значення трапляються в заданих інтервалах. Наприклад, маючи набір даних про вік різних осіб, можна легко з’ясувати, скільки людей потрапляє в кожну вікову групу. Розгляньмо, як можна використовувати цю функцію для обчислення частотного розподілу та, з невеликими змінами, як визначити відсотки частоти.
Призначення функції FREQUENCY
Функція FREQUENCY, що працює з масивами, допомагає обчислити розподіл частот у наборі даних. Щоб використовувати її, вам потрібно надати набір числових даних (це клітинки з вашими вихідними значеннями), перелік граничних значень (категорії, за якими ви сортуєте дані) та підтвердити введення натисканням комбінації клавіш Ctrl+Shift+Enter.
Де можна застосувати цю функцію? Розгляньмо приклад. Уявімо, що ви викладач, і у вашій електронній таблиці є результати тестів ваших учнів. За допомогою функції FREQUENCY ви можете легко підрахувати, скільки студентів отримали оцінки A, B, C, D або F. Результати тестів стануть вашим набором даних, а граничні значення для оцінок будуть визначати категорії.
Функція FREQUENCY обробить список результатів тестів і підрахує, скільки учнів отримало кожну з оцінок, порівнюючи кожен результат з заданим діапазоном значень, які відповідають певній оцінці.
При округленні оцінок до десятих часток відсотка, застосовуватимуться такі діапазони:
F: 0-59.9
D: 60-69.9
C: 70-79.9
B: 80-89.9
A: 90-100
Наприклад, оцінка 79.9 буде зарахована до діапазону C, а оцінка 98.2 — до діапазону A. Excel проаналізує список результатів, класифікує кожну оцінку, підрахує загальну кількість балів у кожному діапазоні та поверне масив із п’яти клітинок, де буде відображено кількість оцінок у кожній категорії.
Для роботи функції FREQUENCY потрібні два масиви: “Data_array” і “Bins_array”. Масиви – це просто списки значень. “Data_array” містить вихідні значення, наприклад, оцінки студентів, які Excel порівнюватиме з пороговими значеннями, зазначеними в “Bins_array”, наприклад, з діапазонами літерних оцінок.
Приклад використання
Розгляньмо на прикладі обчислення частотного розподілу та відсотків частоти для набору з 18 чисел від 0 до 10. Ми визначимо, скільки чисел знаходиться в кожному діапазоні: від 1 до 2, від 2 до 3 і так далі.
У нашому прикладі таблиці ми маємо два стовпці: “Data_array” і “Bins_array”.
Стовпець “Data_array” містить вихідні числа, а стовпець “Bins_array” містить граничні значення діапазонів. Зверніть увагу, що в першій клітинці стовпця “Bins_array” немає значення, це необхідно, щоб врахувати кількість значень в масиві результатів, яка завжди буде на одиницю більшою, ніж у “Bins_array”.
Також додамо третій стовпець “Результати”, куди помістимо обчислені значення.
Спочатку виділіть клітинки, куди ви хочете вставити результати. Перейдіть до вкладки “Формули” та виберіть “Інші функції”. У меню, що з’явиться, наведіть курсор на пункт “Статистичні”, прокрутіть список та виберіть функцію “ЧАСТОТА”.
З’явиться вікно “Аргументи функції”. Клацніть у поле “Data_array” і виділіть клітинки зі значеннями в стовпці “Data_array” (або введіть діапазон клітинок вручну).
Якщо ви отримали повідомлення про помилку, що ви не можете редагувати частину масиву, перевірте, чи ви виділили всі клітинки масиву. Натисніть “ОК”, а потім клавішу Esc.
Для редагування формули масиву або видалення масиву, спочатку потрібно виділити всі клітинки масиву.
Далі клацніть поле “Bins_array” та виберіть клітинки з граничними значеннями в стовпці “Bins_array”.
Натисніть кнопку “ОК”.
Ви помітите, що значення з’явиться лише в першій клітинці стовпця “Результати”, а решта залишаться порожніми.
Щоб побачити інші значення, клацніть у рядку формул і натисніть Ctrl+Shift+Enter.
Тепер у стовпці “Результати” відобразяться всі обчислені значення.
Ви можете бачити, що Excel підрахував чотири значення, які були менші або рівні 1 (виділено червоним), а також підрахував кількість значень для інших діапазонів. Ми додали стовпець “Опис результату”, щоб пояснити логіку обчислення кожного результату.
Обчислення відсотків частоти
Це добре, але як бути, якщо ми хочемо бачити відсотки замість необроблених результатів? Наприклад, який відсоток наших чисел припадає на діапазон від 1 до 2?
Для обчислення відсотків частоти для кожного діапазону, ми змінимо формулу масиву в рядку формул. Виділіть усі клітинки в стовпці “Результати” та додайте в кінці формули в рядку формул наступне:
/COUNT(B3:B20)
Остаточна формула повинна виглядати так:
=FREQUENCY(B3:B20,C3:C20)/COUNT(B3:B20)
Тепер знову натисніть Ctrl+Shift+Enter.
Нова формула ділить кожен елемент масиву “Результати” на загальну кількість значень у стовпці “Data_array”.
Результати не відображаються автоматично у відсотках, але це легко змінити. Перейдіть до вкладки “Основне” і натисніть кнопку “%”.
Тепер значення відображатимуться у відсотках. Наприклад, ви можете побачити, що 17% чисел у стовпці “Data_array” потрапляють у діапазон від 1 до 2.
Найкраще те, що тепер, коли формула вже є в стовпці “Результати”, ви можете змінити будь-які значення в стовпцях “Data_array” та “Bins_array”, і Excel автоматично оновить результати новими значеннями.
Швидкий спосіб введення формул
Якщо ви віддаєте перевагу вводити текст і добре знаєте, як позначати стовпці та клітинки, ви можете оминути пошук у меню “Формули”, просто вводячи функції безпосередньо в рядку формул Excel, а потім натискаючи Ctrl+Shift+Enter.
Для обчислення розподілу частот використовуйте наступний синтаксис:
{=FREQUENCY(Data_array,Bins_array)}
Для обчислення відсотків частоти використовуйте такий синтаксис:
{=FREQUENCY(Data_array,Bins_array)/COUNT(Data_array)}
Пам’ятайте, що це формула масиву, тому вам потрібно натискати Ctrl+Shift+Enter замість просто Enter. Наявність {фігурних дужок} навколо формули вказує на те, що її введено як формулу масиву.