Общая формула
=MAX(INDEX(data,0,MATCH(column,header,0)))
Резюме
Чтобы получить максимальное значение в наборе данных, где столбец является переменным, вы можете использовать ИНДЕКС и ПОИСКПОЗ вместе с функцией МАКС. В показанном примере формула в J5:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
где данные (B5: F15) и заголовок (B4: F4) являются именованными диапазонами.
Объяснение
Примечание. Если вы не знакомы с INDEX и MATCH, см .: Как использовать INDEX и MATCH.
В стандартной конфигурации функция ИНДЕКС извлекает значение в заданной строке и столбце. Например, чтобы получить значение в строке 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)))
Работая изнутри, мы сначала используем функцию ПОИСКПОЗ, чтобы получить «индекс» столбца, запрошенного в ячейке J4:
MATCH(J4,header,0) // get column index
С «Зеленым» в J4 функция ПОИСКПОЗ возвращает 3, так как Зеленый - третье значение в названном заголовке диапазона . После того, как ПОИСКПОЗ вернет результат, формулу можно упростить до следующего:
=MAX(INDEX(data,0,3))
Если в качестве номера строки указан ноль, INDEX возвращает все значения в столбце 3 данных именованного диапазона . Результат возвращается в функцию MAX в виде такого массива:
=MAX((83;54;35;17;85;16;70;72;65;93;91))
И МАКС возвращает окончательный результат, 93.
Минимальное значение
Чтобы получить минимальное значение с помощью переменного столбца, вы можете просто заменить функцию MAX на функцию MIN. Формула в J6:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
С ФИЛЬТРОМ
Для решения этой проблемы также можно использовать новую функцию FILTER, поскольку FILTER может фильтровать данные по строкам или по столбцам. Уловка состоит в том, чтобы создать логический фильтр, который исключит другие столбцы. СЧЁТЕСЛИ хорошо работает в этом случае, но он должен быть настроен «в обратном порядке», с J4 в качестве диапазона и заголовком для критериев:
=MAX(FILTER(data,COUNTIF(J4,header)))
После выполнения COUNTIF у нас есть:
=MAX(FILTER(data,(0,0,1,0,0)))
И ФИЛЬТР передает 3-й столбец в МАКС, как и функция ИНДЕКС выше.
Вместо СЧЁТЕСЛИ можно использовать ЕЧИСЛО + ПОИСКПОЗ:
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
Функция ПОИСКПОЗ снова настроена «в обратном порядке», так что мы получаем массив с 5 значениями, который будет служить логическим фильтром. После выполнения ISNUMBER и MATCH у нас есть:
=MAX(FILTER(data,(FALSE,FALSE,TRUE,FALSE,FALSE)))
И ФИЛЬТР снова передает 3-й столбец в МАКС.
