Sažetak
Za prikaz popisa imena rangiranih numeričkom vrijednošću možete upotrijebiti skup formula na temelju LARGE, INDEX, MATCH, uz pomoć funkcije TEXT. U prikazanom primjeru formula u G5 je:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
A formula u G10 je:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
gdje su datumi klijenta (B5: B17) (C5: C17) i iznos (C5: C17) imenovani rasponi.
Napomena: ovo su formule polja i moraju se unijeti sa control + shift + enter, osim u programu Excel 365.
Obrazloženje
Ovaj je primjer za jasnoću postavljen u dva dijela: (1) formula za određivanje prva 3 iznosa za svaki mjesec i (2) formula za dohvaćanje imena klijenta za svaki od prva 3 mjesečna iznosa.
Imajte na umu da u izvornim podacima nema stvarnog ranga. Umjesto toga, koristimo funkciju LARGE za izravan rad s iznosima. Drugi bi pristup bio dodavanje ranga izvornim podacima pomoću funkcije RANK i korištenje vrijednosti ranga za dohvaćanje imena klijenata.
1. dio: dohvatite najviše 3 iznosa svakog mjeseca
Da biste preuzeli 3 glavna iznosa za svaki tjedan, formula u G5 je:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Napomena: ovo je formula niza i mora se unijeti sa control + shift + enter, osim u programu Excel 365.
Radeći iznutra prema van, prvo koristimo funkciju TEXT da bismo dobili nazive mjeseci za svaki datum u imenovanom datumu raspona :
TEXT(date,"mmmm") // get month names
Prilagođeni format broja "mmmm" vratit će niz poput "travnja", "svibnja", "lipnja" za svako ime u imenovanom datumu raspona . Rezultat je niz imena mjeseci poput ovog:
("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")
Funkcija TEXT isporučuje ovaj niz funkciji IF koja je konfigurirana za filtriranje datuma u danom mjesecu testiranjem naziva mjeseca prema vrijednosti u G4 (mješovita referenca, tako da se formula može kopirati prema dolje i unatrag):
IF(TEXT(date,"mmmm")=G$4,amount) // filter on month
Preživljavaju se samo iznosi u travnju i prolaze kroz IF; sve ostale vrijednosti su LAŽNE:
(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Konačno, funkcija LARGE koristi vrijednost u F5 (također mješovita referenca) za vraćanje "n-te" najveće vrijednosti koja ostaje. U ćeliji G5, LARGE vraća 18.500, "1." najveću vrijednost. Kako se formula kopira prema dolje i preko tablice, funkcija LARGE vraća prva 3 iznosa u svakom od tri mjeseca.
Sad kad znamo najviše 3 vrijednosti u svakom mjesecu, te podatke možemo koristiti kao "ključ" za dohvaćanje imena klijenta za svaki.
Dio 2: dohvaćanje imena klijenata
Napomena: Ovo je primjer upotrebe INDEX-a i MATCH-a s više kriterija. Ako vam je ovaj koncept nov, evo osnovnog primjera.
Da bismo dohvatili ime povezano s gornje tri vrijednosti u G5: I7, koristimo INDEX i MATCH:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
Napomena: ovo je formula niza i mora se unijeti sa control + shift + enter, osim u programu Excel 365.
Funkcionirajući iznutra prema van, funkcija MATCH konfigurirana je za uporabu logičke logike poput ove:
MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)
Vrijednost pretraživanja je 1, a niz pretraživanja konstruiran je s ovim izrazom:
(amount=G5)*(TEXT(date,"mmmm")=G$9)
Izraz koji stvara niz pretraživanja koristi logičku logiku za "filtriranje" iznosa koji (1) nisu u travnju i (2) nisu vrijednost u G5 (18.500). Rezultat je niz od 1 i 0 kao što je ovaj:
(0;0;1;0;0;0;0;0;0;0;0;0;0)
S traženom vrijednošću 1 i nulom za vrstu podudaranja (kako bi se forsiralo točno podudaranje) MATCH vraća 3 izravno u funkciju INDEX:
=INDEX(client,3) // returns "Janus"
INDEX vraća treću vrijednost u imenovanom klijentu raspona, "Janus".
Kako se formula kopira prema dolje i preko tablice, vraća 3 najbolja klijenta u svakom od tri mjeseca.
