Загальна формула
=COUNTIFS(criteria_range,criteria,values,">"&value)+1
Резюме
Для ранжирування елементів у списку за одним або кількома критеріями можна скористатися функцією COUNTIFS. У наведеному прикладі формула в E5 має вигляд:
=COUNTIFS(groups,C5,scores,">"&D5)+1
де "групи" - іменований діапазон C5: C14, а "бали" - іменований діапазон D5: D14. Результатом є ранг для кожної людини у своїй групі.
Примітка: хоча на знімку екрана дані сортуються за групами, формула буде добре працювати з невідсортованими даними.
Пояснення
Хоча Excel має функцію RANK, функція RANKIF не виконує умовного рангу. Однак ви можете легко створити умовний RANK за допомогою функції COUNTIFS.
Функція COUNTIFS може виконувати умовний відлік за допомогою двох або більше критеріїв. Критерії вводяться в парах діапазон / критерій. У цьому випадку перший критерій обмежує підрахунок до тієї самої групи, використовуючи названий діапазон "групи" (C5: C14):
=COUNTIFS(groups,C5) // returns 5
Це саме по собі поверне загальну кількість членів групи в групі "А", тобто 5.
Другий критерій обмежує підрахунок лише балами, більшими за "поточний бал" з D5:
=COUNTIFS(groups,C5,scores,">"&D5) // returns zero
Два критерії працюють разом, щоб підрахувати рядки, де група - А, і оцінка вища. Для першого імені у списку (Ханна) у групі А немає вищих балів, тому COUNTIFS повертає нуль. У наступному рядку (Едвард) у групі А є три бали вище 79, тому COUNTIFS повертає 3. І так далі.
Щоб отримати належний ранг, ми просто додаємо 1 до числа, яке повертає COUNTIFS.
Зміна порядку ранжування
Щоб змінити порядок ранжування та ранжування за порядком (тобто найменше значення класифікується як 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
Результат наведеної вище формули можна додати до початкового рангу, щоб отримати середній ранг. Коли значення не має дублікатів, наведений вище код повертає нуль і не має ефекту.
