Excel

Maksimālā vērtība ar mainīgo kolonnu

Max Value With Variable Column

Excel formula: Maksimālā vērtība ar mainīgo kolonnuVispārīgā formula | _+_ | Kopsavilkums

Lai izgūtu maksimālo vērtību datu kopā, kur atrodas sleja mainīgais , tu vari izmantot INDEKSS un MATCH kopā ar MAX funkcija . Parādītajā piemērā J5 formula ir šāda:





= MAX ( INDEX (data,0, MATCH (column,header,0)))

kur dati (B5: F15) un galvene (B4: F4) ir nosaukti diapazoni .

kā izveidot funkciju Excel
Skaidrojums

Piezīme. Ja esat jauns INDEX un MATCH, skatiet: Kā lietot INDEX un MATCH





Standarta konfigurācijā funkcija INDEX izgūst vērtību noteiktā rindā un kolonnā. Piemēram, lai iegūtu vērtību 2. rindā un 3. slejā noteiktā diapazonā:

 
= MAX ( INDEX (data,0, MATCH (J4,header,0)))

Tomēr, INDEKSS ir īpašs triks - spēja atgūt vesels kolonnas un rindas. Sintakse ietver nulles norādīšanu argumentam “cits”. Ja vēlaties visu kolonnu, rindu norādiet kā nulli. Ja vēlaties visu rindu, kolonnu norādiet kā nulli:



 
= INDEX (range,2,3) // get value at row 2, column 3

Parādītajā piemērā mēs vēlamies atrast maksimālo vērtību noteiktā kolonnā. Griezums ir tāds, ka kolonnai jābūt mainīgai, lai to varētu viegli mainīt. F5 formula ir šāda:

 
= INDEX (data,0,n) // retrieve column n = INDEX (data,n,0) // retrieve row n

Strādājot no iekšpuses uz āru, mēs vispirms izmantojam MATCH funkcija lai iegūtu šūnā J4 pieprasītās kolonnas “indeksu”:

 
= MAX ( INDEX (data,0, MATCH (J4,header,0)))

Ja J4 ir “zaļš”, funkcija MATCH atgriež 3, jo zaļā krāsa ir trešā vērtība nosaukts diapazons galvene . Kad MATCH atgriež rezultātu, formulu var vienkāršot šādi:

 
 MATCH (J4,header,0) // get column index

Ja rindas numurs ir norādīts ar nulli, INDEX atgriež visas vērtības nosauktā diapazona 3. slejā dati. Rezultāts tiek atgriezts pie MAX funkcija gadā masīvs kā šis:

 
= MAX ( INDEX (data,0,3))

Un MAX atgriež gala rezultātu, 93.

Minimālā vērtība

Lai iegūtu minimums vērtību ar mainīgo kolonnu, jūs varat vienkārši aizstāt funkciju MAX ar funkciju MIN. J6 formula ir šāda:

 
= MAX ({8354351785167072659391})

Ar FILTRU

Jauno FILTRA funkcija 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ā tas darbojas labi, taču tas ir jākonfigurē “atpakaļ”, ar J4 kā diapazons , un galvene priekš kritērijiem :

 
= MIN ( INDEX (data,0, MATCH (J4,header,0)))

Pēc COUNTIF palaišanas mums ir:

 
= MAX ( FILTER (data, COUNTIF (J4,header)))

FILTER piegādā trešo kolonnu līdz MAX, tāpat kā iepriekš minēto funkciju INDEX.

kā izveidot datu diagrammu

Kā alternatīvu COUNTIF varat izmantot ISNUMBER + MATCH.

 
= MAX ( FILTER (data,{0,0,1,0,0}))

Funkcija MATCH atkal tiek 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 izpildes mums ir:

 
= MAX ( FILTER (data, ISNUMBER ( MATCH (header,J4,0))))

Un FILTER atkal piegādā 3. kolonnu līdz MAX.

Pielikumi Fails maksimālā vērtība ar mainīgo kolonnu.xlsx Autors Deivs Bruns


^