Sammendrag
For å vise en liste med navn, rangert etter en numerisk verdi, kan du bruke et sett med formler basert på LARGE, INDEX, MATCH, med hjelp fra TEXT-funksjonen. I eksemplet vist er formelen i G5:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Og formelen i G10 er:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
der klient (B5: B17) dato (C5: C17) og beløp (C5: C17) er navngitt områder.
Merk: Dette er matriseformler og må legges inn med kontroll + skift + enter, unntatt i Excel 365.
Forklaring
Dette eksemplet er satt opp i to deler for klarhetens skyld: (1) en formel for å bestemme de tre beste beløpene for hver måned og (2) en formel for å hente klientnavnet for hvert av de tre beste månedlige beløpene.
Merk at det ikke er noen faktisk rangering i kildedataene. I stedet bruker vi STOR-funksjonen til å jobbe direkte med beløp. En annen tilnærming vil være å legge til rangering til kildedataene med RANK-funksjonen, og bruke rangverdien til å hente klientnavn.
Del 1: Hent topp 3 beløp hver måned
For å hente de tre beste beløpene for hver uke, er formelen i G5:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Merk: dette er en matriseformel og må angis med kontroll + skift + enter, unntatt i Excel 365.
Når vi arbeider fra innsiden og ut, bruker vi først TEKST-funksjonen til å få månedlige navn for hver dato i den valgte datoen :
TEXT(date,"mmmm") // get month names
Det egendefinerte tallformatet "mmmm" vil returnere en streng som "April", "Mai", "Juni" for hvert navn i den angitte datoen for området . Resultatet er en rekke månedlige navn som dette:
("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")
TEKST-funksjonen leverer denne matrisen til IF-funksjonen, som er konfigurert til å filtrere datoer i en gitt måned ved å teste månedsnavnet mot verdien i G4 (en blandet referanse, slik at formelen kan kopieres ned og over):
IF(TEXT(date,"mmmm")=G$4,amount) // filter on month
Bare beløp i april overlever og gjør det gjennom IF; alle andre verdier er FALSE:
(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Til slutt bruker LARGE-funksjonen verdien i F5 (også en blandet referanse) for å returnere den "nte" største verdien som gjenstår. I celle G5 returnerer LARGE 18 500, den "første" største verdien. Når formelen kopieres nedover og over hele tabellen, returnerer STOR-funksjonen de tre beste beløpene i hver av de tre månedene.
Nå som vi kjenner de tre beste verdiene i hver måned, kan vi bruke denne informasjonen som en "nøkkel" for å hente klientnavnet for hver.
Del 2: Hent klientnavn
Merk: Dette er et eksempel på bruk av INDEX og MATCH med flere kriterier. Hvis dette konseptet er nytt for deg, er her et grunnleggende eksempel.
For å hente navnet som er knyttet til de tre beste verdiene i G5: I7, bruker vi INDEX og MATCH:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
Merk: dette er en matriseformel og må angis med kontroll + skift + enter, unntatt i Excel 365.
Arbeidet fra innsiden og ut er MATCH-funksjonen konfigurert til å bruke boolsk logikk slik:
MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)
Oppslagsverdien er 1, og oppslagsmatrisen er konstruert med dette uttrykket:
(amount=G5)*(TEXT(date,"mmmm")=G$9)
Uttrykket som oppretter oppslagsmatrisen bruker boolsk logikk for å "filtrere ut" beløp som ikke er (1) i april, og (2) ikke verdien i G5 (18 500). Resultatet er en rekke på 1 og 0 slik:
(0;0;1;0;0;0;0;0;0;0;0;0;0)
Med en oppslagsverdi på 1 og null for samsvarstype (for å tvinge en eksakt samsvar) returnerer MATCH 3 direkte til INDEKS-funksjonen:
=INDEX(client,3) // returns "Janus"
INDEX returnerer den tredje verdien i den nevnte rekkeklienten, "Janus".
Når formelen kopieres nedover og over bordet, returnerer den de tre beste klientene i hver av de tre månedene.
