Generisk formel
=MAX(INDEX(data,0,MATCH(column,header,0)))
Sammanfattning
För att hämta maxvärdet i en uppsättning data, där kolumnen är variabel, kan du använda INDEX och MATCH tillsammans med MAX-funktionen. I exemplet som visas är formeln i J5:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
där data (B5: F15) och rubrik (B4: F4) heter intervall.
Förklaring
Obs: Om du inte är ny på INDEX och MATCH, se: Hur man använder INDEX och MATCH
I en standardkonfiguration hämtar INDEX-funktionen ett värde i en viss rad och kolumn. Till exempel för att få värdet vid rad 2 och kolumn 3 inom ett visst intervall:
=INDEX(range,2,3) // get value at row 2, column 3
INDEX har dock ett speciellt trick - möjligheten att hämta hela kolumner och rader. Syntaxen innebär att man anger noll för argumentet "annat". Om du vill ha en hel kolumn anger du raden som noll. Om du vill ha en hel rad anger du kolumn som noll:
=INDEX(data,0,n) // retrieve column n =INDEX(data,n,0) // retrieve row n
I det visade exemplet vill vi hitta det maximala värdet i en viss kolumn. Vridningen är att kolumnen måste vara variabel så att den lätt kan ändras. I F5 är formeln:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
Arbetar inifrån och ut, använder vi först MATCH-funktionen för att få "index" för den begärda kolumnen i cell J4:
MATCH(J4,header,0) // get column index
Med "grönt" i J4 återgår MATCH funktionen 3, eftersom Green är det tredje värdet i det namngivna området huvudet . Efter att MATCH returnerar ett resultat kan formeln förenklas till detta:
=MAX(INDEX(data,0,3))
Med noll som radnummer returnerar INDEX alla värden i kolumn 3 i namngivna områdesdata . Resultatet returneras till MAX-funktionen i en sådan matris:
=MAX((83;54;35;17;85;16;70;72;65;93;91))
Och MAX returnerar slutresultatet, 93.
Minsta värde
För att få minimivärdet med en variabelkolumn kan du helt enkelt ersätta MAX-funktionen med MIN-funktionen. Formeln i J6 är:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
Med FILTER
Den nya FILTER-funktionen kan också användas för att lösa detta problem, eftersom FILTER kan filtrera data efter rad eller kolumn. Tricket är att konstruera ett logiskt filter som utesluter andra kolumner. COUNTIF fungerar bra i det här fallet, men det måste konfigureras "bakåt", med J4 som intervall och rubrik för kriterier:
=MAX(FILTER(data,COUNTIF(J4,header)))
Efter COUNTIF-körningar har vi:
=MAX(FILTER(data,(0,0,1,0,0)))
Och FILTER levererar den tredje kolumnen till MAX, samma som INDEX-funktionen ovan.
Som ett alternativ till COUNTIF kan du istället använda ISNUMBER + MATCH:
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
MATCH-funktionen är återigen inställd "bakåt", så att vi får en matris med 5 värden som fungerar som det logiska filtret. Efter ISNUMBER och MATCH-körning har vi:
=MAX(FILTER(data,(FALSE,FALSE,TRUE,FALSE,FALSE)))
Och FILTER levererar igen den tredje kolumnen till MAX.
