Обща формула
=MAX(INDEX(data,0,MATCH(column,header,0)))
Обобщение
За да извлечете максималната стойност в набор от данни, където колоната е променлива, можете да използвате INDEX и MATCH заедно с функцията MAX. В показания пример формулата в J5 е:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
където данните (B5: F15) и заглавката (B4: F4) са наречени диапазони.
Обяснение
Забележка: Ако сте нови в INDEX и MATCH, вижте: Как да използвате INDEX и MATCH
В стандартна конфигурация функцията INDEX извлича стойност в даден ред и колона. Например, за да получите стойността на ред 2 и колона 3 в даден диапазон:
=INDEX(range,2,3) // get value at row 2, column 3
INDEX обаче има специален трик - възможността за извличане на цели колони и редове. Синтаксисът включва предоставяне на нула за аргумента "други". Ако искате цяла колона, предоставяте ред като нула. Ако искате цял ред, предоставяте колона като нула:
=INDEX(data,0,n) // retrieve column n =INDEX(data,n,0) // retrieve row n
В показания пример искаме да намерим максималната стойност в дадена колона. Обратът е, че колоната трябва да е променлива, за да може лесно да се променя. В F5 формулата е:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
Работейки отвътре навън, първо използваме функцията MATCH, за да получим "индекса" на колоната, поискана в клетка J4:
MATCH(J4,header,0) // get column index
С "Зелено" в J4, функцията MATCH връща 3, тъй като Green е третата стойност в заглавката на посочения диапазон . След като MATCH върне резултат, формулата може да бъде опростена до това:
=MAX(INDEX(data,0,3))
С нула, предоставена като номер на реда, INDEX връща всички стойности в колона 3 от посочените данни за диапазон . Резултатът се връща към функцията MAX в масив като този:
=MAX((83;54;35;17;85;16;70;72;65;93;91))
И MAX връща крайния резултат, 93.
Минимална стойност
За да получите минималната стойност с променлива колона, можете просто да замените функцията MAX с функцията MIN. Формулата в J6 е:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
С ФИЛТЪР
Новата функция FILTER също може да се използва за решаване на този проблем, тъй като FILTER може да филтрира данните по ред или по колона. Номерът е да се създаде логически филтър, който да изключва други колони. В този случай COUNTIF работи добре, но трябва да бъде конфигуриран "назад", като J4 е обхватът и заглавката за критерии:
=MAX(FILTER(data,COUNTIF(J4,header)))
След изпълненията на COUNTIF имаме:
=MAX(FILTER(data,(0,0,1,0,0)))
И FILTER доставя 3-та колона на MAX, същото като функцията INDEX по-горе.
Като алтернатива на COUNTIF можете вместо това да използвате ISNUMBER + MATCH:
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
Функцията MATCH отново се настройва „назад“, така че получаваме масив с 5 стойности, които ще служат като логически филтър. След стартиране на ISNUMBER и MATCH имаме:
=MAX(FILTER(data,(FALSE,FALSE,TRUE,FALSE,FALSE)))
И FILTER отново доставя третата колона на MAX.
