Üldine valem
=COUNTIFS(criteria_range,criteria,values,">"&value)+1
Kokkuvõte
Loendis üksuste järjestamiseks ühe või mitme kriteeriumi abil saate kasutada funktsiooni COUNTIFS. Näidatud näites on valem E5-s järgmine:
=COUNTIFS(groups,C5,scores,">"&D5)+1
kus "rühmad" on nimega vahemik C5: C14 ja "hinded" on nimega vahemik D5: D14. Tulemuseks on iga oma rühma kuuluva inimese auaste.
Märkus. Kuigi ekraanipildil on andmed sorditud rühmade järgi, töötab valem sorteerimata andmetega hästi.
Selgitus
Ehkki Excelis on funktsioon RANK, pole tingimusliku asetuse täitmiseks funktsiooni RANKIF. Funktsiooniga COUNTIFS saate siiski hõlpsasti luua tingimusliku RANK-i.
Funktsioon COUNTIFS võib tingimusliku loendamise läbi viia kahe või enama kriteeriumi abil. Kriteeriumid sisestatakse vahemike / kriteeriumide paaridesse. Sel juhul piirab esimene kriteerium loendamist samale rühmale, kasutades nimetatud vahemikku "rühmad" (C5: C14):
=COUNTIFS(groups,C5) // returns 5
Iseenesest naaseb see rühma A liikmete koguarv, mis on 5.
Teine kriteerium piirdub loendiga ainult tulemustega, mis on suuremad kui praegune skoor D5-st:
=COUNTIFS(groups,C5,scores,">"&D5) // returns zero
Need kaks kriteeriumi töötavad koos ridade loendamiseks, kus rühm on A ja skoor on kõrgem. Loendis oleva eesnime (Hannah) puhul pole A-grupis kõrgemaid punkte, mistõttu COUNTIFS tagastab nulli. Järgmises reas (Edward) on A-grupis kolm punkti, mis on kõrgem kui 79, nii et COUNTIFS tagastab 3. Ja nii edasi.
Õige auastme saamiseks lisame COUNTIFSi tagastatud numbrile lihtsalt 1.
Järjekorra järjekorra muutmine
Järjekorra järjestuse ja järjestuse vastupidiseks muutmiseks (st väikseim väärtus on järjestatud # 1) kasutage lihtsalt operaatorist vähem ():
=COUNTIFS(groups,C5,scores,"<"&D5)+1
D5-st suuremate skooride lugemise asemel loeb see versioon punkte D5-st väiksema väärtuse, muutes paremusjärjestuse.
Duplikaadid
Nagu funktsioon RANK, määrab ka selle lehe valem duplikaatväärtustele sama auastme. Näiteks kui konkreetsele väärtusele määratakse auaste 3 ja järjestatavates andmetes on kaks väärtuse eksemplari, saavad mõlemad eksemplarid 3 ja järgmine määratud aste on 5. Käitumise jäljendamiseks funktsiooni RANK.AVG, mis määraks sellisel juhul keskmise auastme 3,5, saate "parandusteguri" arvutada järgmise valemiga:
=(COUNTIFS(groups,C5)+1-(COUNTIFS(group,C5,scores,">"&D5)+1)-(COUNTIFS(groups,C5,scores,"<"&D5)+1))/2
Selle ülaltoodud valemi tulemuse saab keskmise auastme saamiseks lisada algsele auastmele. Kui väärtusel pole duplikaate, tagastab ülaltoodud kood nulli ja sellel pole mõju.
