Общая формула
=COUNTIFS(criteria_range,criteria,values,">"&value)+1
Резюме
Чтобы ранжировать элементы в списке по одному или нескольким критериям, вы можете использовать функцию СЧЁТЕСЛИМН. В показанном примере формула E5 выглядит так:
=COUNTIFS(groups,C5,scores,">"&D5)+1
где «группы» - это именованный диапазон C5: C14, а «scores» - именованный диапазон D5: D14. В результате каждый человек получает рейтинг в своей группе.
Примечание: хотя на снимке экрана данные отсортированы по группам, формула будет работать с несортированными данными.
Объяснение
Хотя в Excel есть функция RANK, функция RANKIF для выполнения условного ранжирования отсутствует. Однако вы можете легко создать условный РАНГ с помощью функции СЧЁТЕСЛИМН.
Функция СЧЁТЕСЛИМН может выполнять условный подсчет с использованием двух или более критериев. Критерии вводятся в парах диапазон / критерий. В этом случае первый критерий ограничивает подсчет одной и той же группой с использованием именованного диапазона «группы» (C5: C14):
=COUNTIFS(groups,C5) // returns 5
Само по себе это вернет общее количество членов группы в группе «A», равное 5.
Второй критерий ограничивает подсчет только баллами, превышающими «текущий балл» из D5:
=COUNTIFS(groups,C5,scores,">"&D5) // returns zero
Эти два критерия работают вместе, чтобы подсчитать строки, в которых группа - A и оценка выше. Для первого имени в списке (Ханна) в группе A нет более высоких оценок, поэтому СЧЁТЕСЛИМН возвращает ноль. В следующей строке (Эдвард) три результата в группе A выше 79, поэтому СЧЁТЕСЛИМН возвращает 3. И так далее.
Чтобы получить правильный ранг, мы просто добавляем 1 к числу, возвращаемому функцией СЧЁТЕСЛИМН.
Изменение порядка ранжирования
Чтобы изменить порядок ранжирования и ранжирование по порядку (т. Е. Наименьшее значение имеет ранг №1), просто используйте оператор «меньше чем» ():
=COUNTIFS(groups,C5,scores,"<"&D5)+1
Вместо подсчета баллов выше, чем D5, эта версия будет подсчитывать баллы меньше, чем значение в D5, эффективно меняя порядок ранжирования.
Дубликаты
Как и функция RANK, формула на этой странице присваивает повторяющимся значениям один и тот же ранг. Например, если определенному значению присвоен ранг 3, и есть два экземпляра значения в ранжируемых данных, оба экземпляра получат ранг 3, а следующий присвоенный ранг будет 5. Чтобы имитировать поведение функции RANK.AVG, которая в таком случае присвоит средний ранг 3,5, вы можете рассчитать «поправочный коэффициент» по следующей формуле:
=(COUNTIFS(groups,C5)+1-(COUNTIFS(group,C5,scores,">"&D5)+1)-(COUNTIFS(groups,C5,scores,"<"&D5)+1))/2
Результат этой формулы выше может быть добавлен к исходному рангу, чтобы получить средний ранг. Если значение не имеет дубликатов, приведенный выше код возвращает ноль и не действует.
