Vispārēja formula
=COUNTIFS(criteria_range,criteria,values,">"&value)+1
Kopsavilkums
Lai sarindotu vienumus sarakstā, izmantojot vienu vai vairākus kritērijus, varat izmantot funkciju COUNTIFS. Parādītajā piemērā formula E5 ir:
=COUNTIFS(groups,C5,scores,">"&D5)+1
kur "grupas" ir nosauktais diapazons C5: C14 un "rādītāji" ir nosauktais diapazons D5: D14. Rezultāts ir rangs katrai personai savā grupā.
Piezīme: lai gan ekrānuzņēmumā dati tiek sakārtoti pēc grupas, formula labi darbosies ar nešķirotiem datiem.
Paskaidrojums
Lai gan programmai Excel ir funkcija RANK, nosacītās pakāpes veikšanai nav funkcijas RANKIF. Tomēr jūs varat viegli izveidot nosacītu RANK ar funkciju COUNTIFS.
Funkcija COUNTIFS var veikt nosacītu skaitīšanu, izmantojot divus vai vairākus kritērijus. Kritēriji tiek ievadīti diapazonu / kritēriju pāros. Šajā gadījumā pirmais kritērijs ierobežo skaitīšanu vienai un tai pašai grupai, izmantojot nosaukto diapazonu "grupas" (C5: C14):
=COUNTIFS(groups,C5) // returns 5
Pats par sevi tas atgriezīs kopējos grupas dalībniekus grupā A, kas ir 5.
Otrais kritērijs ierobežo skaitīšanu tikai ar punktiem, kas ir lielāki par D5 pašreizējo punktu:
=COUNTIFS(groups,C5,scores,">"&D5) // returns zero
Abi kritēriji darbojas kopā, lai saskaitītu rindas, kur grupa ir A un rezultāts ir augstāks. Pirmajam vārdam sarakstā (Hannah) A grupā nav augstāku punktu skaita, tāpēc COUNTIFS atgriež nulli. Nākamajā rindā (Edvards) A grupā ir trīs rādītāji, kas ir augstāki par 79, tāpēc COUNTIFS atgriež 3. Un tā tālāk.
Lai iegūtu atbilstošu rangu, mēs vienkārši pievienojam 1 skaitlim, ko atgriezis COUNTIFS.
Rindu secības maiņa
Lai mainītu ranga secību un rangu pēc kārtas (ti, mazākā vērtība ir ierindota Nr. 1), izmantojiet mazāku nekā operators ():
=COUNTIFS(groups,C5,scores,"<"&D5)+1
Tā vietā, lai skaitītu vērtējumus, kas ir lielāki par D5, šī versija skaitīs mazākus rezultātus nekā vērtība D5, faktiski mainot rangu secību.
Dublikāti
Tāpat kā funkcija RANK, arī šajā lapā esošā formula dublikātu vērtībām piešķir tādu pašu rangu. Piemēram, ja konkrētai vērtībai tiek piešķirts rangs 3 un rangā esošajos datos ir divi vērtības gadījumi, abi gadījumi saņems rangu 3 un nākamais piešķirtais rangs būs 5. Lai atdarinātu uzvedību no funkcijas RANK.AVG, kas šādā gadījumā piešķirtu vidējo pakāpi 3,5, jūs varat aprēķināt "korekcijas koeficientu" ar šādu formulu:
=(COUNTIFS(groups,C5)+1-(COUNTIFS(group,C5,scores,">"&D5)+1)-(COUNTIFS(groups,C5,scores,"<"&D5)+1))/2
Rezultātu no šīs iepriekš minētās formulas var pievienot sākotnējam rangam, lai iegūtu vidējo rangu. Ja vērtībai nav dublikātu, iepriekš minētais kods atgriež nulli un tam nav ietekmes.
