Изтеглете примерната работна книга
Този урок ще покаже как да се изчисли „процентил ако“, като се извлече kth персентил в бяс от ценности с критерии.
PERCENTILE функция
Функцията PERCENTILE се използва за изчисляване на kth процентил на стойности в диапазон където к е стойността на процентила между 0 и 1 включително.
= ПРОЦЕНТНО ($ D $ 2: $ D $ 10,0.75)
Това обаче отнема процентила на целия диапазон от стойности. Вместо това, за да създадем „Перцентил Ако“, ще използваме PERCENTILE функцията заедно с IF функцията във формула на масив.
ПЕРЦЕНЦИЛЕН АКО
Комбинирайки PERCENTILE и IF във формула на масив, можем по същество да създадем функция „PERCENTLE IF“, която работи подобно на това как работи вградената функция AVERAGEIF. Нека преминем през пример.
Имаме списък с резултати, постигнати от учениците по два различни предмета:
Предполага се, че сме помолени да намерим 75th процентили резултати, постигнати за всеки предмет, така:
За да постигнем това, можем да вмъкнем 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; FALSE; FALSE; 0,42; FALSE; 0,63; FALSE; 0,58; FALSE}, 0,75)
Сега функцията PERCENTILE пропуска стойностите FALSE и изчислява 75th процентил на останалите стойности (0,72 е 75th перцентил).
ПЕРЦЕНЦИЛЕН АКО с множество критерии
За да изчислите PERCENTILE IF с множество критерии (подобно на това как работи вградената функция 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.
- За да извлечете имената на ученици, постигнали най -добрите оценки, комбинирайте това с INDEX MATCH.
ПЕРЦЕНЦИЛЕН АКО в Google Таблици
Функцията PERCENTILE IF работи абсолютно същото в Google Sheets, както и в Excel:
