Vispārēja formula
=MAX(INDEX(data,0,MATCH(column,header,0)))
Kopsavilkums
Lai izgūtu maksimālo vērtību datu kopā, kur kolonna ir mainīga, varat izmantot INDEX un MATCH kopā ar funkciju MAX. Parādītajā piemērā formula J5 ir:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
kur dati (B5: F15) un galvene (B4: F4) ir nosaukti diapazoni.
Paskaidrojums
Piezīme: Ja esat jauns INDEX un MATCH lietotājs, skatiet sadaļu: Kā lietot INDEX un MATCH
Standarta konfigurācijā funkcija INDEX izgūst vērtību dotajā rindā un kolonnā. Piemēram, lai iegūtu vērtību 2. rindā un 3. slejā noteiktā diapazonā:
=INDEX(range,2,3) // get value at row 2, column 3
Tomēr INDEX ir īpašs triks - iespēja izgūt veselas kolonnas un rindas. Sintakse ietver nulles piegādi argumentam "cits". Ja vēlaties visu kolonnu, rindu norādāt kā nulli. Ja vēlaties visu rindu, kolonnu norādiet kā nulli:
=INDEX(data,0,n) // retrieve column n =INDEX(data,n,0) // retrieve row n
Parādītajā piemērā mēs vēlamies atrast maksimālo vērtību dotajā kolonnā. Pagrieziens ir tāds, ka kolonnai jābūt mainīgai, lai to varētu viegli mainīt. F5 formulā ir:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
Strādājot no iekšpuses uz āru, mēs vispirms izmantojam funkciju MATCH, lai iegūtu šūnas J4 pieprasītās kolonnas "indeksu":
MATCH(J4,header,0) // get column index
Ar "Green" J4 funkcija MATCH atgriež 3, jo Green ir trešā vērtība nosauktajā diapazona galvenē . Pēc tam, kad MATCH atgriež rezultātu, formulu var vienkāršot šādi:
=MAX(INDEX(data,0,3))
Ja kā rindas numuru ir norādīta nulle, INDEX atgriež visas vērtības nosaukto diapazona datu 3. slejā . Rezultāts tiek atgriezts MAX funkcijā šādā masīvā:
=MAX((83;54;35;17;85;16;70;72;65;93;91))
Un MAX atgriež gala rezultātu, 93.
Minimālā vērtība
Lai iegūtu minimālo vērtību ar mainīgu kolonnu, varat vienkārši aizstāt funkciju MAX ar MIN funkciju. J6 formula ir:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
Ar FILTER
Jauno FILTER funkciju var izmantot arī šīs problēmas risināšanai, jo FILTER var filtrēt datus pēc rindas vai kolonnas. Triks ir izveidot loģisku filtru, kas izslēgs citas kolonnas. COUNTIF šajā gadījumā darbojas labi, taču tas ir jākonfigurē "atpakaļ", ar diapazonu J4 un kritēriju galveni :
=MAX(FILTER(data,COUNTIF(J4,header)))
Pēc COUNTIF palaišanas mums ir:
=MAX(FILTER(data,(0,0,1,0,0)))
FILTER piegādā 3. kolonnu MAX, tāpat kā funkcija INDEX iepriekš.
Kā alternatīvu COUNTIF varat tā vietā izmantot ISNUMBER + MATCH:
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
Funkcija MATCH atkal ir iestatīta "atpakaļ", lai mēs iegūtu masīvu ar 5 vērtībām, kas kalpos kā loģiskais filtrs. Pēc ISNUMBER un MATCH skrējiena mums ir:
=MAX(FILTER(data,(FALSE,FALSE,TRUE,FALSE,FALSE)))
FILTER atkal piegādā 3. kolonnu MAX.
