Як виділити дані на основі процентного рейтингу в Excel

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

Інший спосіб поглянути на це — сказати, що бали дитини входять у перші 10 відсотків усіх дітей, які пройшли тест.

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

У Excel ви можете легко отримати ключові процентильні ранги за допомогою функції ПЕРЦЕНТИЛЬ. Для цієї функції потрібні два аргументи: діапазон даних і процентний бал, який ви хочете побачити.

У показаному прикладі значення в клітинці D7 є результатом наступної формули, яка витягує 75-й процентиль на основі даних у діапазоні B3:B20:

=ПЕРЦЕНТИЛЬ($B$3:$B$20,0,75)

Ця формула говорить вам, що будь-який співробітник з доходом понад 52 651 дол. США входить до 75 відсотків найефективніших.

Значення в клітинці D16 є результатом наступної формули, яка витягує 25-й процентиль на основі даних у діапазоні B3:B20:

=ПЕРЦЕНТИЛЬ($B$3:$B$20,0,25)

Ця формула говорить вам, що будь-який співробітник з доходом нижче 24 656 доларів США входить до 25 відсотків найефективніших.

Використовуючи ці маркери процентиля, цей приклад застосовує умовне форматування, так що будь-яке значення в 75-му процентилі буде пофарбовано в зелений колір, а будь-яке значення в 25-му процентилі буде пофарбовано в червоний колір.

Щоб створити це основне правило форматування, виконайте такі дії:

Виберіть клітинки даних у вашому цільовому діапазоні (клітинки B3:B20 у цьому прикладі), натисніть вкладку «Головна» на стрічці Excel, а потім виберіть «Умовне форматування»→ «Нове правило».

Відкриється діалогове вікно Нове правило форматування.

У вікні зі списком у верхній частині діалогового вікна виберіть параметр Використовувати формулу для визначення клітинок для форматування.

Цей вибір оцінює значення на основі заданої вами формули. Якщо певне значення має значення TRUE, до цієї комірки застосовується умовне форматування.

У полі введення формули введіть формулу, показану на цьому кроці.

У цій формулі ви оцінюєте, чи знаходяться дані в цільовій клітинці (B3) у межах 25-го процентиля. Якщо так, буде застосовано умовне форматування.

=B3<>

Зауважте, що у формулі ви виключаєте абсолютні еталонні символи долара ($) для цільової клітинки (B3). Якщо клацнути клітинку B3 замість того, щоб ввести посилання на клітинку, Excel автоматично зробить посилання на клітинку абсолютним. Важливо, щоб ви не включали символи долара абсолютного посилання в цільову клітинку, оскільки вам потрібен Excel, щоб застосувати це правило форматування на основі власного значення кожної клітинки.

Натисніть кнопку Формат.

Це відкриє діалогове вікно Формат клітинок, де у вас є повний набір параметрів для форматування шрифту, межі та заливки для цільової комірки. Після того, як ви завершили вибір параметрів форматування, натисніть кнопку OK, щоб підтвердити зміни та повернутися до діалогового вікна Нове правило форматування.

Повернувшись у діалогове вікно Нове правило форматування, натисніть кнопку OK, щоб підтвердити правило форматування.

На цьому етапі ви повинні опинитися в діалоговому вікні «Диспетчер правил умовного форматування».

Натисніть кнопку Нове правило.

Відкриється діалогове вікно Нове правило форматування.

У вікні зі списком у верхній частині діалогового вікна виберіть параметр Використовувати формулу для визначення клітинок для форматування. Цей вибір оцінює значення на основі заданої вами формули. Якщо певне значення має значення TRUE, то до цієї комірки застосовується умовне форматування.

У полі введення формули введіть формулу, показану тут.

У цій формулі ви оцінюєте, чи знаходяться дані в цільовій клітинці (B3) у межах 75-го процентиля. Якщо так, буде застосовано умовне форматування.

=B3>=ПЕРЦЕНТИЛЬ($B$3:$B$20,0,75)

Натисніть кнопку Формат.

Це відкриє діалогове вікно Формат клітинок, де у вас є повний набір параметрів для форматування шрифту, межі та заливки для цільової комірки. Після того, як ви завершили вибір параметрів форматування, натисніть кнопку OK, щоб підтвердити зміни та повернутися до діалогового вікна Нове правило форматування.

У діалоговому вікні Нове правило форматування натисніть кнопку OK, щоб підтвердити правило форматування.

Якщо вам потрібно відредагувати правило умовного форматування, просто помістіть курсор у будь-яку з клітинок даних у межах вашого форматованого діапазону, а потім перейдіть на вкладку «Головна» та виберіть «Умовне форматування»→ «Керувати правилами». Відкриється діалогове вікно Диспетчер правил умовного форматування. Натисніть правило, яке потрібно редагувати, а потім натисніть кнопку Редагувати правило.