Загрузите образец книги
В этом руководстве будет показано, как вычислить «процентиль, если», получив kth процентиль в ряду значений с критериями.
ПРОЦЕНТИЛЬ, функция
Функция ПРОЦЕНТИЛЬ используется для вычисления kth процентиль значений в диапазоне, где k - значение процентиля от 0 до 1 включительно.
= ПРОЦЕНТИЛЬ (2 долл. США: 10,0,75 долл. США)
Однако здесь берется процентиль всего диапазона значений. Вместо этого, чтобы создать «Процентиль если», мы будем использовать функцию ПРОЦЕНТИЛЬ вместе с функцией ЕСЛИ в формуле массива.
ПРОЦЕНТИЛЬ, ЕСЛИ
Комбинируя ПРОЦЕНТИЛЬ и ЕСЛИ в формуле массива, мы можем создать функцию «ПРОЦЕНТИЛЬ ЕСЛИ», которая работает аналогично встроенной функции СРЗНАЧЕСЛИ. Давайте рассмотрим пример.
У нас есть список результатов, полученных студентами по двум разным предметам:
Предположим, нас просят найти 75th процентили баллов по каждому предмету, например:
Для этого мы можем вложить функцию ЕСЛИ с тема в качестве наших критериев внутри функции ПРОЦЕНТИЛЬ, например:
= ПРОЦЕНТИЛЬ (ЕСЛИ (=,),)
= ПРОЦЕНТИЛЬ (ЕСЛИ ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10); 0,75)
При использовании Excel 2022 и более ранних версий необходимо ввести формулу массива, нажав CTRL + SHIFT + ВВОД (вместо того ВХОДИТЬ), сообщая Excel, что формула в формуле массива. Вы узнаете, что это формула массива, по фигурным скобкам, заключенным вокруг формулы (см. Верхнее изображение).
Как работает формула?
Функция If оценивает каждую ячейку в нашем диапазоне критериев как ИСТИНА или ЛОЖЬ, создавая два массива:
= ПРОЦЕНТИЛЬ (ЕСЛИ ({ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ}; {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)
Теперь функция ПРОЦЕНТИЛЬ пропускает значения ЛОЖЬ и вычисляет 75th процентиль остальных значений (0,72 - 75th процентиль).
ПРОЦЕНТИЛЬ, ЕСЛИ с несколькими критериями
Чтобы вычислить ПРОЦЕНТИЛЬ ЕСЛИ с несколькими критериями (аналогично тому, как работает встроенная функция СРЗНАЧЕСЛИМН), вы можете просто умножить критерии вместе:
= ПРОЦЕНТИЛЬ (ЕСЛИ ((=) * (=),),)
= ПРОЦЕНТИЛЬ (ЕСЛИ (($ 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 Таблицах
Функция ПРОЦЕНТИЛЬ ЕСЛИ работает в Google Таблицах точно так же, как и в Excel:
