Bendroji formulė
=COUNTIFS(criteria_range,criteria,values,">"&value)+1
Santrauka
Norėdami reitinguoti elementus sąraše pagal vieną ar daugiau kriterijų, galite naudoti funkciją COUNTIFS. Parodytame pavyzdyje formulė E5 yra:
=COUNTIFS(groups,C5,scores,">"&D5)+1
kur „grupės“ yra pavadintas diapazonas C5: C14, o „balai“ - pavadintas diapazonas D5: D14. Rezultatas - kiekvieno savo grupės asmens rangas.
Pastaba: nors ekrano kopijoje duomenys yra rūšiuojami pagal grupes, formulė puikiai veiks su nerūšiuotais duomenimis.
Paaiškinimas
Nors „Excel“ turi funkciją RANK, sąlyginei rangai atlikti nėra funkcijos RANKIF. Tačiau naudodamiesi funkcija COUNTIFS, galite lengvai sukurti sąlyginį RANK.
Funkcija COUNTIFS gali atlikti sąlyginį skaičiavimą naudodama du ar daugiau kriterijų. Kriterijai įrašomi į intervalų / kriterijų poras. Tokiu atveju pagal pirmuosius kriterijus skaičiavimas ribojamas tai pačiai grupei, naudojant nurodytas diapazono „grupes“ (C5: C14):
=COUNTIFS(groups,C5) // returns 5
Tai savaime grąžins „A“ grupės, kuri yra 5, grupės narius.
Pagal antrąjį kriterijų skaičius ribojamas tik tuo atveju, jei balai yra didesni už „dabartinį balą“ iš D5:
=COUNTIFS(groups,C5,scores,">"&D5) // returns zero
Šie du kriterijai kartu skaičiuoja eiles, kuriose grupė yra A, o rezultatas didesnis. Vardui sąraše (Hannah) A grupėje nėra aukštesnių balų, todėl COUNTIFS grąžina nulį. Kitoje eilutėje (Edwardas) A grupėje yra trys balai, didesni nei 79, todėl COUNTIFS pateikia 3. Ir pan.
Norėdami gauti tinkamą reitingą, paprasčiausiai pridedame 1 prie skaičiaus, kurį grąžino COUNTIFS.
Grąžinimo eilės tvarka
Norėdami pakeisti rango eiliškumą ir eiliškumą (ty mažiausia vertė yra reitinguojama Nr. 1), tiesiog naudokite mažesnį nei operatorius ():
=COUNTIFS(groups,C5,scores,"<"&D5)+1
Užuot skaičiavusi didesnius nei D5 balus, ši versija skaičiuos mažesnius balus nei vertė D5, veiksmingai pakeisdama rango eiliškumą.
Dublikatai
Kaip ir funkcija RANK, taip ir šiame puslapyje pateikta formulė pasikartojančioms reikšmėms priskiria tą patį rangą. Pvz., Jei konkrečiai vertei priskiriamas 3 reitingas ir reitinguojamuose duomenyse yra du vertės atvejai, abu atvejai gaus 3 reitingą, o kitas priskirtas reitingas bus 5. Norėdami imituoti elgesį funkcijos RANK.AVG, kuri tokiu atveju priskirtų vidutinį 3,5 reitingą, galite apskaičiuoti „korekcijos koeficientą“ naudodami tokią formulę:
=(COUNTIFS(groups,C5)+1-(COUNTIFS(group,C5,scores,">"&D5)+1)-(COUNTIFS(groups,C5,scores,"<"&D5)+1))/2
Šios aukščiau pateiktos formulės rezultatas gali būti pridėtas prie pradinio rango, kad gautų vidutinį reitingą. Kai reikšmė neturi dublikatų, aukščiau pateiktas kodas grąžina nulį ir neturi jokio poveikio.
