Generisk formel
=MAX(INDEX(data,0,MATCH(column,header,0)))
Resumé
For at hente den maksimale værdi i et datasæt, hvor kolonnen er variabel, kan du bruge INDEX og MATCH sammen med MAX-funktionen. I det viste eksempel er formlen i J5:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
hvor data (B5: F15) og header (B4: F4) er navngivet områder.
Forklaring
Bemærk: Hvis du ikke er ny på INDEX og MATCH, skal du se: Sådan bruges INDEX og MATCH
I en standardkonfiguration henter INDEX-funktionen en værdi i en given række og kolonne. For eksempel for at få værdien i række 2 og kolonne 3 i et givet interval:
=INDEX(range,2,3) // get value at row 2, column 3
INDEX har dog et specielt trick - evnen til at hente hele kolonner og rækker. Syntaksen indebærer at levere nul til "andet" argumentet. Hvis du vil have en hel kolonne, angiver du række som nul. Hvis du vil have en hel række, angiver du kolonne som nul:
=INDEX(data,0,n) // retrieve column n =INDEX(data,n,0) // retrieve row n
I det viste eksempel ønsker vi at finde den maksimale værdi i en given kolonne. Twist er, at søjlen skal være variabel, så den let kan ændres. I F5 er formlen:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
Når vi arbejder indefra og ud, bruger vi først MATCH-funktionen til at få "indekset" for den anmodede kolonne i celle J4:
MATCH(J4,header,0) // get column index
Med "Grøn" i J4 returnerer MATCH-funktionen 3, da Grøn er den tredje værdi i den nævnte rækkehoved . Når MATCH returnerer et resultat, kan formlen forenkles til dette:
=MAX(INDEX(data,0,3))
Med nul angivet som række nummer returnerer INDEX alle værdier i kolonne 3 i de navngivne områdedata . Resultatet returneres til MAX-funktionen i en matrix som denne:
=MAX((83;54;35;17;85;16;70;72;65;93;91))
Og MAX returnerer det endelige resultat, 93.
Minimumsværdi
For at få minimumværdien med en variabel kolonne kan du blot erstatte MAX-funktionen med MIN-funktionen. Formlen i J6 er:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
Med FILTER
Den nye FILTER-funktion kan også bruges til at løse dette problem, da FILTER kan filtrere data efter række eller kolonne. Tricket er at konstruere et logisk filter, der udelukker andre kolonner. COUNTIF fungerer godt i dette tilfælde, men det skal konfigureres "baglæns" med J4 som rækkevidde og overskrift for kriterier:
=MAX(FILTER(data,COUNTIF(J4,header)))
Efter COUNTIF-kørsler har vi:
=MAX(FILTER(data,(0,0,1,0,0)))
Og FILTER leverer 3. kolonne til MAX, samme som INDEX-funktionen ovenfor.
Som et alternativ til COUNTIF kan du bruge ISNUMBER + MATCH i stedet:
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
MATCH-funktionen er igen sat op "baglæns", så vi får en matrix med 5 værdier, der fungerer som det logiske filter. Efter ISNUMBER og MATCH-løb har vi:
=MAX(FILTER(data,(FALSE,FALSE,TRUE,FALSE,FALSE)))
Og FILTER leverer igen 3. kolonne til MAX.
