Обща формула
=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
Двата критерия работят заедно, за да преброят редове, където групата е A и резултатът е по-висок. За първото име в списъка (Хана) няма по-високи резултати в група А, така че 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
Резултатът от тази формула по-горе може да се добави към първоначалния ранг, за да се получи среден ранг. Когато дадена стойност няма дубликати, горният код връща нула и няма ефект.
