Samenvatting
Om een lijst met namen weer te geven, gerangschikt op een numerieke waarde, kunt u een set formules gebruiken op basis van LARGE, INDEX, MATCH, met behulp van de TEXT-functie. In het getoonde voorbeeld is de formule in G5:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
En de formule in G10 is:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
waarbij de datum van de klant (B5: B17) (C5: C17) en het bedrag (C5: C17) benoemde bereiken zijn.
Let op: dit zijn matrixformules en moeten worden ingevoerd met control + shift + enter, behalve in Excel 365.
Uitleg
Dit voorbeeld is voor de duidelijkheid in twee delen opgebouwd: (1) een formule om de top 3 bedragen voor elke maand te bepalen en (2) een formule om de klantnaam op te halen voor elk van de top 3 maandbedragen.
Let op: er is geen daadwerkelijke rang in de brongegevens. In plaats daarvan gebruiken we de LARGE-functie om rechtstreeks met bedragen te werken. Een andere benadering zou zijn om rang aan de brongegevens toe te voegen met de RANK-functie en de rangwaarde te gebruiken om klantnamen op te halen.
Deel 1: elke maand top 3 bedragen ophalen
Om de top 3 bedragen voor elke week op te halen, is de formule in G5:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Opmerking: dit is een matrixformule en moet worden ingevoerd met control + shift + enter, behalve in Excel 365.
Werken van binnen naar buiten, gebruiken we eerst de tekst functie tot maand namen te krijgen voor elke datum in de genoemde range datum :
TEXT(date,"mmmm") // get month names
Het aangepaste getalnotatie "mmmm" zal een string terug als "April", "kan", "Juni" voor elke naam in het benoemde bereik datum . Het resultaat is een reeks maandnamen zoals deze:
("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")
De TEXT-functie levert deze array aan de ALS-functie, die is geconfigureerd om datums op een bepaalde maand te filteren door de maandnaam te testen tegen de waarde in G4 (een gemengde verwijzing, zodat de formule naar beneden en naar beneden kan worden gekopieerd):
IF(TEXT(date,"mmmm")=G$4,amount) // filter on month
Alleen bedragen in april overleven en halen IF; alle andere waarden zijn FALSE:
(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Ten slotte gebruikt de functie LARGE de waarde in F5 (ook een gemengde referentie) om de "n-de" grootste waarde te retourneren die overblijft. In cel G5 retourneert GROOTSTE 18.500, de "eerste" grootste waarde. Terwijl de formule naar beneden en over de tabel wordt gekopieerd, retourneert de functie GROOTSTE de bovenste 3 bedragen in elk van de drie maanden.
Nu we de top 3 waarden in elke maand kennen, kunnen we deze informatie gebruiken als een "sleutel" om de klantnaam voor elk op te halen.
Deel 2: clientnamen ophalen
Opmerking: dit is een voorbeeld van het gebruik van INDEX en MATCH met meerdere criteria. Als dit concept nieuw voor u is, volgt hier een eenvoudig voorbeeld.
Om de naam op te halen die is gekoppeld aan de top drie waarden in G5: I7, gebruiken we INDEX en MATCH:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
Opmerking: dit is een matrixformule en moet worden ingevoerd met control + shift + enter, behalve in Excel 365.
Werkend van binnen naar buiten, is de MATCH-functie geconfigureerd om Booleaanse logica als volgt te gebruiken:
MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)
De opzoekwaarde is 1 en de opzoekmatrix is geconstrueerd met deze uitdrukking:
(amount=G5)*(TEXT(date,"mmmm")=G$9)
De expressie die de lookup-array maakt, gebruikt Booleaanse logica om bedragen te "filteren" die (1) niet in april zijn en (2) niet de waarde in G5 (18.500). Het resultaat is een array van 1'en en 0'en als volgt:
(0;0;1;0;0;0;0;0;0;0;0;0;0)
Met een opzoekwaarde van 1 en nul voor het zoektype (om een exacte overeenkomst te forceren) retourneert MATCH 3 rechtstreeks naar de functie INDEX:
=INDEX(client,3) // returns "Janus"
INDEX retourneert de derde waarde in de benoemde bereikclient, "Janus".
Terwijl de formule naar beneden en over de tabel wordt gekopieerd, worden de drie beste klanten in elk van de drie maanden geretourneerd.
