Formuła ogólna
=COUNTIFS(criteria_range,criteria,values,">"&value)+1
Podsumowanie
Aby uszeregować pozycje na liście przy użyciu co najmniej jednego kryterium, można użyć funkcji LICZ.WARUNKI. W pokazanym przykładzie formuła w E5 to:
=COUNTIFS(groups,C5,scores,">"&D5)+1
gdzie „grupy” to nazwany zakres C5: C14, a „wyniki” to nazwany zakres D5: D14. Rezultatem jest ranga dla każdej osoby w swojej grupie.
Uwaga: chociaż dane są posortowane według grup na zrzucie ekranu, formuła będzie działać poprawnie z danymi nieposortowanymi.
Wyjaśnienie
Chociaż program Excel ma funkcję RANK, nie ma funkcji RANKIF do wykonywania rangi warunkowej. Możesz jednak łatwo utworzyć warunkową RANKĘ za pomocą funkcji LICZ.WARUNKI.
Funkcja LICZ.WARUNKI może wykonywać zliczanie warunkowe przy użyciu co najmniej dwóch kryteriów. Kryteria są wprowadzane w parach zakres / kryteria. W tym przypadku pierwsze kryterium ogranicza liczenie do tej samej grupy, używając nazwanego zakresu „grupy” (C5: C14):
=COUNTIFS(groups,C5) // returns 5
Samo to zwróci całkowitą liczbę członków grupy w grupie „A”, czyli 5.
Drugie kryterium ogranicza liczenie tylko do wyników wyższych niż „bieżący wynik” z D5:
=COUNTIFS(groups,C5,scores,">"&D5) // returns zero
Te dwa kryteria współpracują ze sobą, aby policzyć wiersze, w których grupa ma status A, a wynik jest wyższy. Dla pierwszego nazwiska na liście (Hannah) nie ma wyższych wyników w grupie A, więc LICZ.WARUNKI zwraca zero. W następnym wierszu (Edward) są trzy wyniki w grupie A wyższe niż 79, więc COUNTIFS zwraca 3. I tak dalej.
Aby uzyskać odpowiednią rangę, po prostu dodajemy 1 do liczby zwracanej przez COUNTIFS.
Odwrócenie kolejności rang
Aby odwrócić kolejność rang i rangę w kolejności (tj. Najmniejsza wartość zajmuje pozycję nr 1) po prostu użyj operatora mniejszego niż ():
=COUNTIFS(groups,C5,scores,"<"&D5)+1
Zamiast liczyć wyniki większe niż D5, ta wersja będzie liczyć wyniki mniejsze niż wartość w D5, skutecznie odwracając kolejność rang.
Duplikaty
Podobnie jak w przypadku funkcji POZYCJA, formuła na tej stronie przypisze zduplikowanym wartościom tę samą rangę. Na przykład, jeśli określonej wartości zostanie przypisana ranga 3, aw ocenianych danych istnieją dwa wystąpienia tej wartości, obie instancje otrzymają rangę 3, a następną przypisaną rangę będzie 5. Aby naśladować zachowanie funkcji RANK.AVG, która w takim przypadku przypisałaby średnią rangę 3,5, można obliczyć „współczynnik korygujący” za pomocą następującego wzoru:
=(COUNTIFS(groups,C5)+1-(COUNTIFS(group,C5,scores,">"&D5)+1)-(COUNTIFS(groups,C5,scores,"<"&D5)+1))/2
Wynik z powyższego wzoru można dodać do pierwotnej rangi, aby uzyskać średnią rangę. Gdy wartość nie ma duplikatów, powyższy kod zwraca zero i nie ma żadnego efektu.
