Resumé
For at få vist en liste med navne, der er rangeret efter en numerisk værdi, kan du bruge et sæt formler baseret på STOR, INDEKS, MATCH med hjælp fra TEKST-funktionen. I det viste eksempel er formlen i G5:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Og formlen i G10 er:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
hvor klient (B5: B17) dato (C5: C17) og beløb (C5: C17) er navngivet områder.
Bemærk: disse er matrixformler og skal indtastes med kontrol + skift + enter, undtagen i Excel 365.
Forklaring
Dette eksempel er opsat i to dele for klarhedens skyld: (1) en formel til at bestemme de øverste 3-beløb for hver måned og (2) en formel til at hente klientnavnet for hver af de 3 øverste månedlige beløb.
Bemærk, at der ikke er nogen faktisk rang i kildedataene. I stedet bruger vi STOR funktion til at arbejde direkte med beløb. En anden tilgang ville være at tilføje rang til kildedataene med RANK-funktionen og bruge rangværdien til at hente klientnavne.
Del 1: Hent top 3-beløb hver måned
For at hente de top 3-beløb for hver uge er formlen i G5:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Bemærk: dette er en matrixformel og skal indtastes med kontrol + skift + enter, undtagen i Excel 365.
Arbejde indefra og ud, vi først bruge TEKST funktionen for at få månedsnavne for hver dato i det navngivne område dato :
TEXT(date,"mmmm") // get month names
Den specielt talformat "mmmm" vil returnere en streng som "April", "Maj", "Juni" for hvert navn i det navngivne område dato . Resultatet er en række månedsnavne som denne:
("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")
TEKST-funktionen leverer dette array til IF-funktionen, som er konfigureret til at filtrere datoer i en given måned ved at teste månedsnavnet mod værdien i G4 (en blandet reference, så formlen kan kopieres ned og på tværs):
IF(TEXT(date,"mmmm")=G$4,amount) // filter on month
Kun beløb i april overlever og gør det gennem IF; alle andre værdier er FALSE:
(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Endelig bruger LARGE-funktionen værdien i F5 (også en blandet reference) til at returnere den "n" største værdi, der er tilbage. I celle G5 returnerer STOR 18.500, den "1." største værdi. Da formlen er kopieret ned og på tværs af tabellen, returnerer LARGE-funktionen de øverste 3 beløb i hver af de tre måneder.
Nu hvor vi kender de 3 bedste værdier i hver måned, kan vi bruge disse oplysninger som en "nøgle" til at hente klientnavnet for hver.
Del 2: Hent klientnavne
Bemærk: Dette er et eksempel på brug af INDEX og MATCH med flere kriterier. Hvis dette koncept er nyt for dig, er her et grundlæggende eksempel.
For at hente navnet tilknyttet de tre øverste værdier i G5: I7 bruger vi INDEX og MATCH:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
Bemærk: dette er en matrixformel og skal indtastes med kontrol + skift + enter, undtagen i Excel 365.
Når man arbejder indefra og ud, er MATCH-funktionen konfigureret til at bruge boolsk logik som denne:
MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)
Opslagsværdien er 1, og opslagsarrayet er konstrueret med dette udtryk:
(amount=G5)*(TEXT(date,"mmmm")=G$9)
Udtrykket, der opretter opslagsarray, bruger boolsk logik til at "filtrere" beløb, der er (1) ikke i april, og (2) ikke værdien i G5 (18.500). Resultatet er en matrix på 1 og 0 som denne:
(0;0;1;0;0;0;0;0;0;0;0;0;0)
Med en opslagsværdi på 1 og nul for matchtype (for at tvinge et nøjagtigt match) returnerer MATCH 3 direkte til INDEX-funktionen:
=INDEX(client,3) // returns "Janus"
INDEX returnerer den tredje værdi i den navngivne rækkevidde-klient, "Janus".
Da formlen kopieres ned og på tværs af tabellen, returnerer den top 3-klienterne i hver af de tre måneder.
