Sammanfattning
För att visa en lista med namn, rangordnade efter ett numeriskt värde, kan du använda en uppsättning formler baserade på STOR, INDEX, MATCH, med hjälp av TEXT-funktionen. I exemplet som visas är formeln i G5:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Och formeln i G10 är:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
där klient (B5: B17) datum (C5: C17) och belopp (C5: C17) heter intervall.
Obs! Dessa är matrisformler och måste anges med kontroll + shift + enter, utom i Excel 365.
Förklaring
Detta exempel är upprättat i två delar för tydlighetens skull: (1) en formel för att bestämma de tre bästa beloppen för varje månad och (2) en formel för att hämta klientnamnet för var och en av de tre bästa månadsbeloppen.
Observera att det inte finns någon faktisk rankning i källdata. Istället använder vi den STORA funktionen för att arbeta direkt med belopp. Ett annat tillvägagångssätt skulle vara att lägga till rang i källdata med RANK-funktionen och använda rangvärdet för att hämta klientnamn.
Del 1: Hämta de tre bästa beloppen varje månad
För att hämta de tre bästa beloppen för varje vecka är formeln i G5:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Obs: detta är en matrisformel och måste anges med kontroll + shift + enter, utom i Excel 365.
Att arbeta inifrån och ut, använder vi först TEXT-funktionen för att få månadsnamn för varje datum i intervallet som heter datum :
TEXT(date,"mmmm") // get month names
Det anpassat numeriskt format "mmmm" kommer att returnera en sträng som "April", "kan", "Juni" för varje namn i intervallet som heter dagen . Resultatet är en rad månadsnamn så här:
("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")
TEXT-funktionen levererar denna matris till IF-funktionen, som är konfigurerad för att filtrera datum under en viss månad genom att testa månadens namn mot värdet i G4 (en blandad referens så att formeln kan kopieras ner och över):
IF(TEXT(date,"mmmm")=G$4,amount) // filter on month
Endast belopp i april överlever och klarar IF; alla andra värden är FALSKA:
(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Slutligen använder LARGE-funktionen värdet i F5 (även en blandad referens) för att returnera det "n" största värdet som återstår. I cell G5 returnerar STORA 18 500, det "första" största värdet. Eftersom formeln kopieras ner och över tabellen returnerar STOR-funktionen de tre bästa beloppen under var och en av de tre månaderna.
Nu när vi känner till de tre bästa värdena i varje månad kan vi använda den här informationen som en "nyckel" för att hämta klientnamnet för varje.
Del 2: Hämta klientnamn
Obs: Detta är ett exempel på att använda INDEX och MATCH med flera kriterier. Om detta koncept är nytt för dig, här är ett grundläggande exempel.
För att hämta namnet som är associerat med de tre bästa värdena i G5: I7 använder vi INDEX och MATCH:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
Obs: detta är en matrisformel och måste anges med kontroll + shift + enter, utom i Excel 365.
Arbetar inifrån och ut, MATCH-funktionen är konfigurerad för att använda boolesk logik så här:
MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)
Uppslagsvärdet är 1 och uppslagsmatrisen är konstruerad med det här uttrycket:
(amount=G5)*(TEXT(date,"mmmm")=G$9)
Uttrycket som skapar uppslagsmatrisen använder boolesk logik för att "filtrera ut" mängder som (1) inte är i april och (2) inte värdet i G5 (18 500). Resultatet är en array med 1 och 0 så här:
(0;0;1;0;0;0;0;0;0;0;0;0;0)
Med ett sökvärde 1 och noll för matchningstyp (för att tvinga en exakt matchning) returnerar MATCH 3 direkt till INDEX-funktionen:
=INDEX(client,3) // returns "Janus"
INDEX returnerar det tredje värdet i den namngivna intervallklienten "Janus".
Eftersom formeln kopieras ner och över tabellen returnerar den de tre bästa klienterna under var och en av de tre månaderna.
