Yleinen kaava
=MAX(INDEX(data,0,MATCH(column,header,0)))
Yhteenveto
Jos haluat noutaa enimmäisarvon tietojoukosta, jossa sarake on muuttuja, voit käyttää INDEX- ja MATCH-toimintoja yhdessä MAX-toiminnon kanssa. Esitetyssä esimerkissä kaava kohdassa J5 on:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
missä tiedot (B5: F15) ja otsikko (B4: F4) on nimetty alueiksi.
Selitys
Huomautus: Jos olet uusi hakemisto INDEX ja MATCH, katso: INDEXin ja MATCHin käyttäminen
Vakiokokoonpanossa INDEX-funktio hakee arvon tietyltä riviltä ja sarakkeelta. Esimerkiksi saadaksesi arvon riviltä 2 ja sarakkeelta 3 tietyllä alueella:
=INDEX(range,2,3) // get value at row 2, column 3
INDEXillä on kuitenkin erityinen temppu - kyky hakea kokonaisia sarakkeita ja rivejä. Syntaksi sisältää nollan toimittamisen "muu" -argumentille. Jos haluat kokonaisen sarakkeen, annat rivin nollana. Jos haluat kokonaisen rivin, lisää sarake nollaksi:
=INDEX(data,0,n) // retrieve column n =INDEX(data,n,0) // retrieve row n
Esitetyssä esimerkissä haluamme löytää maksimiarvon tietystä sarakkeesta. Kierre on, että sarakkeen on oltava vaihteleva, jotta sitä voidaan helposti muuttaa. Kohdassa F5 kaava on:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
Työskentelemällä sisältä ulospäin, käytämme ensin MATCH-funktiota saadaksesi soluun J4 vaaditun sarakkeen "indeksin":
MATCH(J4,header,0) // get column index
Kun J4: ssä on "vihreä", MATCH-funktio palauttaa arvon 3, koska vihreä on nimettyjen alueiden otsikkojen kolmas arvo . Kun MATCH palauttaa tuloksen, kaavaa voidaan yksinkertaistaa seuraavasti:
=MAX(INDEX(data,0,3))
Kun rivinumeroksi annetaan nolla, INDEX palauttaa kaikki nimetyn alueen tietojen sarakkeen 3 arvot . Tulos palautetaan MAX-funktioon näin:
=MAX((83;54;35;17;85;16;70;72;65;93;91))
Ja MAX palauttaa lopullisen tuloksen, 93.
Vähimmäisarvo
Saadaksesi vähimmäisarvon muuttuvalla sarakkeella, voit yksinkertaisesti korvata MAX-toiminnon MIN-toiminnolla. J6: n kaava on:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
Suodattimen kanssa
Uutta FILTER-toimintoa voidaan käyttää myös tämän ongelman ratkaisemiseen, koska FILTER voi suodattaa tietoja riveittäin tai sarakkeittain. Temppu on rakentaa looginen suodatin, joka sulkee pois muut sarakkeet. COUNTIF toimii tässä tapauksessa hyvin, mutta se on määritettävä "taaksepäin", J4 on alue ja otsikko ehdoille:
=MAX(FILTER(data,COUNTIF(J4,header)))
Kun COUNTIF on suoritettu, meillä on:
=MAX(FILTER(data,(0,0,1,0,0)))
Ja FILTER toimittaa kolmannen sarakkeen MAX: lle, sama kuin yllä oleva INDEX-toiminto.
Vaihtoehtona COUNTIF: lle voit käyttää sen sijaan ISNUMBER + MATCH:
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
MATCH-toiminto on jälleen asetettu "taaksepäin", niin että saamme matriisi, jossa on 5 arvoa, joka toimii loogisena suodattimena. ISNUMBER- ja MATCH-ajon jälkeen meillä on:
=MAX(FILTER(data,(FALSE,FALSE,TRUE,FALSE,FALSE)))
Ja FILTER toimittaa jälleen kolmannen sarakkeen MAX: lle.
