Генеричка формула
=COUNTIFS(criteria_range,criteria,values,">"&value)+1
Резиме
Да бисте рангирали ставке на листи помоћу једног или више критеријума, можете да користите функцију ЦОУНТИФС. У приказаном примеру, формула у Е5 је:
=COUNTIFS(groups,C5,scores,">"&D5)+1
где је „групе“ именовани опсег Ц5: Ц14, а „резултати“ именовани опсег Д5: Д14. Резултат је ранг сваке особе у својој групи.
Напомена: иако су подаци на снимку екрана сортирани по групама, формула ће добро функционисати са несортираним подацима.
Објашњење
Иако Екцел има функцију РАНК, не постоји функција РАНКИФ која би извршила условни ранг. Међутим, лако можете створити условни РАНК помоћу функције ЦОУНТИФС.
Функција ЦОУНТИФС може извршити условно бројање користећи два или више критеријума. Критеријуми се уносе у парове опсег / критеријум. У овом случају, први критеријум ограничава бројање на исту групу, користећи именовани опсег „групе“ (Ц5: Ц14):
=COUNTIFS(groups,C5) // returns 5
Само по себи, ово ће вратити укупно чланова групе у групи "А", што је 5.
Други критеријум ограничава бројање на само оцене веће од „тренутних резултата“ из Д5:
=COUNTIFS(groups,C5,scores,">"&D5) // returns zero
Два критеријума заједно раде како би се бројали редови где је група А и оцена је већа. За прво име на листи (Ханнах) нема већих резултата у групи А, па ЦОУНТИФС враћа нулу. У следећем реду (Едвард), у групи А су три резултата виша од 79, па ЦОУНТИФС враћа 3. И тако даље.
Да бисмо добили одговарајући ранг, једноставно додамо 1 броју који је вратио ЦОУНТИФС.
Обртање редоследа ранга
Да бисте поништили поредак и поредак по редоследу (тј. Најмања вредност је рангирана на 1. месту), само користите оператор мање од ():
=COUNTIFS(groups,C5,scores,"<"&D5)+1
Уместо да броји оцене веће од Д5, ова верзија рачунаће резултате мање од вредности у Д5, ефективно преокрећујући редослед ранга.
Дупликати
Попут функције РАНК, формула на овој страници ће доделити дупликате вредности истог ранга. На пример, ако се одређеној вредности додели ранг 3, а постоје две инстанце вредности у подацима који се рангирају, обе инстанце ће добити ранг 3, а следећи ранг који ће бити додељен 5. За опонашање понашања функције РАНК.АВГ, која би у таквом случају доделила просечни ранг од 3,5, можете израчунати „фактор корекције“ помоћу следеће формуле:
=(COUNTIFS(groups,C5)+1-(COUNTIFS(group,C5,scores,">"&D5)+1)-(COUNTIFS(groups,C5,scores,"<"&D5)+1))/2
Резултат из горње формуле може се додати првобитном рангу да би се добио просечан ранг. Када вредност нема дупликате, горњи код враћа нулу и нема ефекта.
