Завантажте зразок робочої книги
Цей підручник покаже, як обчислити "процентиль, якщо", отримавши kго процентиль у ряді цінностей з критеріями.
ПЕРЦЕНЦІЛЬНА функція
Функція PERCENTILE використовується для обчислення kго процентиль значень у діапазоні де k - це відсоткове значення від 0 до 1 включно.
= ПЕРЦЕНТИЛЬНИЙ ($ D $ 2: $ D $ 10,0.75)
Однак це приймає процентиль усього діапазону значень. Натомість, для створення “Процентиля If”, ми будемо використовувати функцію PERCENTILE разом з функцією IF у формулі масиву.
ПЕРЦЕНЦІЙНИЙ ІФ
Поєднавши PERCENTILE та IF у формулі масиву, ми можемо по суті створити функцію “PERCENTLE IF”, яка працює подібно до того, як працює вбудована функція AVERAGEIF. Пройдемося по прикладу.
У нас є список балів, досягнутих учнями з двох різних предметів:
Припустимо, нас попросять знайти 75го процентилі оцінок, досягнутих для кожного предмета, так:
Для цього ми можемо вкласти функцію IF за допомогою предмет оскільки наші критерії всередині PERCENTILE функціонують так:
= ПЕРЦЕНЦІЛЬНИЙ (ЯКЩО (=,),)
= ПЕРЦЕНЦІЙНО (ЯКЩО ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), 0,75)
Під час використання Excel 2022 та попередніх версій необхідно ввести формулу масиву, натиснувши CTRL + SHIFT + ENTER (замість ENTER), повідомляючи Excel, що формула у формулі масиву. Ви дізнаєтесь, що це формула масиву за фігурними дужками, що з’являються навколо формули (див. Верхнє зображення).
Як працює формула?
Функція If оцінює кожну клітинку в нашому діапазоні критеріїв як TRUE або FALSE, створюючи два масиви:
= ПРОЦЕНТИЛЬНО (IF ({TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {0,99; 0,8; 0,93; 0,42; 0,87; 0,63; 0,71; 0,58; 0,73}), 0,75)
Далі, функція IF створює єдиний масив, замінюючи кожне значення на FALSE, якщо його умова не виконується.
= ПРОЦЕНТИЛЬНЕ ({0,81; НЕВІРНО; НЕВІРНО; 0,42; НЕВІРНО; 0,63; НЕВІРНО; 0,58; ЛОЖЕ}, 0,75)
Тепер функція PERCENTILE пропускає значення FALSE і обчислює 75го процентиль решти значень (0,72 - це 75го процентиль).
ПЕРЦЕНТИЛЬНИЙ ЕСЛИ з кількома критеріями
Для обчислення ПЕРЦЕНТИЛЬНОГО ІФ за кількома критеріями (подібно до того, як працює вбудована функція AVERAGEIFS), ви можете просто помножити критерії разом:
= ПЕРЦЕНЦІЛЬНИЙ (ЯКЩО ((=)*(=),),)
= ПЕРЦЕНТИЛЬНИЙ (ЯКЩО (($ D $ 2: $ D $ 10 = $ H2)*($ C $ 2: $ C $ 10 = $ G2), $ E $ 2: $ E $ 10), 0,75)
Інший спосіб включити кілька критеріїв - це вкласти більше формул IF у формулу.
Поради та хитрощі:
- По можливості, завжди посилайтеся на позицію (k) з допоміжної комірки та посилання на блокування (F4), оскільки це полегшить автоматичне заповнення формул.
- Якщо ви використовуєте Excel 2022 або новішу версію, ви можете ввести формулу без CTRL + SHIFT + ENTER.
- Щоб отримати імена студентів, які досягли найкращих оцінок, поєднайте це з ІНДЕКСНИМ МАТЧОМ.
ПЕРЦЕНЦІЙНИЙ ІФ у Google Таблицях
Функція PERCENTILE IF працює точно так само в Google Таблицях, як і в Excel:
