Generieke formule
=MAX(INDEX(data,0,MATCH(column,header,0)))
Samenvatting
Om de maximale waarde in een set gegevens op te halen, waarbij de kolom variabel is, kunt u INDEX en MATCH samen met de MAX-functie gebruiken. In het getoonde voorbeeld is de formule in J5:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
waarbij gegevens (B5: F15) en koptekst (B4: F4) benoemde bereiken zijn.
Uitleg
Opmerking: als u nieuw bent bij INDEX en MATCH, zie: Hoe INDEX en MATCH te gebruiken
In een standaardconfiguratie haalt de functie INDEX een waarde op in een bepaalde rij en kolom. Om bijvoorbeeld de waarde in rij 2 en kolom 3 in een bepaald bereik te krijgen:
=INDEX(range,2,3) // get value at row 2, column 3
INDEX heeft echter een speciale truc: de mogelijkheid om hele kolommen en rijen op te halen. De syntaxis omvat het leveren van nul voor het "andere" argument. Als je een hele kolom wilt, geef je rij op als nul. Als u een hele rij wilt, geeft u de kolom op als nul:
=INDEX(data,0,n) // retrieve column n =INDEX(data,n,0) // retrieve row n
In het getoonde voorbeeld willen we de maximale waarde in een bepaalde kolom vinden. De twist is dat de kolom variabel moet zijn, zodat deze gemakkelijk kan worden gewijzigd. In F5 is de formule:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
We werken van binnen naar buiten en gebruiken eerst de MATCH-functie om de "index" te krijgen van de kolom die wordt gevraagd in cel J4:
MATCH(J4,header,0) // get column index
Met "Groen" in J4 retourneert de MATCH-functie 3, aangezien Groen de derde waarde is in de benoemde bereikkop . Nadat MATCH een resultaat heeft geretourneerd, kan de formule hiertoe worden vereenvoudigd:
=MAX(INDEX(data,0,3))
Met nul opgegeven als het rijnummer, retourneert INDEX alle waarden in kolom 3 van de genoemde bereikgegevens . Het resultaat wordt geretourneerd naar de MAX-functie in een array als deze:
=MAX((83;54;35;17;85;16;70;72;65;93;91))
En MAX retourneert het eindresultaat, 93.
Minimale waarde
Om de minimumwaarde te krijgen met een variabele kolom, kunt u eenvoudig de MAX-functie vervangen door de MIN-functie. De formule in J6 is:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
Met FILTER
De nieuwe FILTER-functie kan ook worden gebruikt om dit probleem op te lossen, aangezien FILTER gegevens per rij of per kolom kan filteren. De truc is om een logisch filter te construeren dat andere kolommen uitsluit. AANTAL.ALS werkt goed in dit geval, maar het moet "achterstevoren" worden geconfigureerd, met J4 als het bereik en de koptekst voor criteria:
=MAX(FILTER(data,COUNTIF(J4,header)))
Nadat COUNTIF is uitgevoerd, hebben we:
=MAX(FILTER(data,(0,0,1,0,0)))
En FILTER levert de 3e kolom aan MAX, hetzelfde als de INDEX-functie hierboven.
Als alternatief voor AANTAL.ALS kunt u in plaats daarvan ISNUMBER + WEDSTRIJD gebruiken:
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
De MATCH-functie wordt weer "achterstevoren" ingesteld, zodat we een array krijgen met 5 waarden die als logisch filter zullen dienen. Na ISNUMBER- en MATCH-run hebben we:
=MAX(FILTER(data,(FALSE,FALSE,TRUE,FALSE,FALSE)))
En FILTER levert opnieuw de 3e kolom aan MAX.
