Generisk formel
=MAX(INDEX(data,0,MATCH(column,header,0)))
Sammendrag
For å hente ut maksverdien i et datasett, der kolonnen er variabel, kan du bruke INDEX og MATCH sammen med MAX-funksjonen. I eksemplet vist er formelen i J5:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
der data (B5: F15) og overskrift (B4: F4) er navngitte områder.
Forklaring
Merk: Hvis du ikke er kjent med INDEX og MATCH, kan du se: Hvordan bruke INDEX og MATCH
I en standardkonfigurasjon henter INDEX-funksjonen en verdi i en gitt rad og kolonne. For eksempel for å få verdien i rad 2 og kolonne 3 i et gitt område:
=INDEX(range,2,3) // get value at row 2, column 3
INDEX har imidlertid et spesielt triks - muligheten til å hente hele kolonner og rader. Syntaksen innebærer å oppgi null for argumentet "annet". Hvis du vil ha en hel kolonne, oppgir du rad som null. Hvis du vil ha en hel rad, oppgir du kolonne som null:
=INDEX(data,0,n) // retrieve column n =INDEX(data,n,0) // retrieve row n
I eksemplet som vises vil vi finne maksimumsverdien i en gitt kolonne. Vridningen er at kolonnen må være variabel, slik at den enkelt kan endres. I F5 er formelen:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
Når vi arbeider fra innsiden og ut, bruker vi først MATCH-funksjonen for å få "indeksen" til kolonnen som er forespurt i celle J4:
MATCH(J4,header,0) // get column index
Med "Green" i J4 returnerer MATCH-funksjonen 3, siden Green er den tredje verdien i det nevnte områdetittelen . Etter at MATCH har returnert et resultat, kan formelen forenkles til dette:
=MAX(INDEX(data,0,3))
Med null angitt som radnummer, returnerer INDEX alle verdiene i kolonne 3 i de nevnte områdedataene . Resultatet returneres til MAX-funksjonen i en matrise som dette:
=MAX((83;54;35;17;85;16;70;72;65;93;91))
Og MAX returnerer det endelige resultatet, 93.
Minimum verdi
For å få minimumsverdien med en variabel kolonne, kan du ganske enkelt erstatte MAX-funksjonen med MIN-funksjonen. Formelen i J6 er:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
Med FILTER
Den nye FILTER-funksjonen kan også brukes til å løse dette problemet, siden FILTER kan filtrere data etter rad eller kolonne. Trikset er å lage et logisk filter som ekskluderer andre kolonner. COUNTIF fungerer bra i dette tilfellet, men det må konfigureres "bakover", med J4 som område, og overskrift for kriterier:
=MAX(FILTER(data,COUNTIF(J4,header)))
Etter at COUNTIF har kjørt, har vi:
=MAX(FILTER(data,(0,0,1,0,0)))
Og FILTER leverer 3. kolonne til MAX, samme som INDEX-funksjonen ovenfor.
Som et alternativ til COUNTIF kan du bruke ISNUMBER + MATCH i stedet:
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
MATCH-funksjonen er igjen satt opp "bakover", slik at vi får en matrise med 5 verdier som vil tjene som det logiske filteret. Etter ISNUMBER og MATCH har vi:
=MAX(FILTER(data,(FALSE,FALSE,TRUE,FALSE,FALSE)))
Og FILTER leverer igjen 3. kolonne til MAX.
