Formula generică
=MAX(INDEX(data,0,MATCH(column,header,0)))
rezumat
Pentru a prelua valoarea maximă într-un set de date, unde coloana este variabilă, puteți utiliza INDEX și MATCH împreună cu funcția MAX. În exemplul prezentat, formula din J5 este:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
unde datele (B5: F15) și antetul (B4: F4) sunt denumite intervale.
Explicaţie
Notă: Dacă nu sunteți nou în INDEX și MATCH, consultați: Cum se utilizează INDEX și MATCH
Într-o configurație standard, funcția INDEX recuperează o valoare la un rând și o coloană date. De exemplu, pentru a obține valoarea la rândul 2 și coloana 3 într-un interval dat:
=INDEX(range,2,3) // get value at row 2, column 3
Cu toate acestea, INDEX are un truc special - capacitatea de a recupera coloane și rânduri întregi. Sintaxa implică furnizarea zero pentru argumentul „celălalt”. Dacă doriți o coloană întreagă, furnizați rândul ca zero. Dacă doriți un rând întreg, furnizați coloana ca zero:
=INDEX(data,0,n) // retrieve column n =INDEX(data,n,0) // retrieve row n
În exemplul prezentat, dorim să găsim valoarea maximă într-o coloană dată. Răsucirea este că coloana trebuie să fie variabilă, astfel încât să poată fi schimbată cu ușurință. În F5, formula este:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
Lucrând din interior spre exterior, folosim mai întâi funcția MATCH pentru a obține „indexul” coloanei solicitate în celula J4:
MATCH(J4,header,0) // get column index
Cu „Verde” în J4, funcția MATCH returnează 3, deoarece Verde este a treia valoare din antetul de interval numit . După ce MATCH returnează un rezultat, formula poate fi simplificată în acest sens:
=MAX(INDEX(data,0,3))
Cu zero furnizat ca număr de rând, INDEX returnează toate valorile din coloana 3 a datelor intervalului denumit . Rezultatul este returnat funcției MAX într-o matrice ca aceasta:
=MAX((83;54;35;17;85;16;70;72;65;93;91))
Și MAX returnează rezultatul final, 93.
Valoare minimă
Pentru a obține valoarea minimă cu o coloană variabilă, puteți înlocui pur și simplu funcția MAX cu funcția MIN. Formula din J6 este:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
Cu FILTRU
Noua funcție FILTER poate fi, de asemenea, utilizată pentru a rezolva această problemă, deoarece FILTER poate filtra datele pe rând sau pe coloană. Trucul constă în construirea unui filtru logic care să excludă alte coloane. COUNTIF funcționează bine în acest caz, dar trebuie configurat „înapoi”, cu J4 ca interval și antet pentru criterii:
=MAX(FILTER(data,COUNTIF(J4,header)))
După ce COUNTIF rulează, avem:
=MAX(FILTER(data,(0,0,1,0,0)))
Și FILTER livrează a 3-a coloană la MAX, la fel ca funcția INDEX de mai sus.
Ca alternativă la COUNTIF, puteți utiliza în schimb ISNUMBER + MATCH:
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
Funcția MATCH este din nou configurată „înapoi”, astfel încât să obținem o matrice cu 5 valori care să servească drept filtru logic. După executarea ISNUMBER și MATCH, avem:
=MAX(FILTER(data,(FALSE,FALSE,TRUE,FALSE,FALSE)))
Și FILTER trimite din nou a treia coloană la MAX.
