Загальна формула
=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, оскільки Зелений - це третє значення в названому заголовку діапазону . Після того, як 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)))
З ФІЛЬТРОМ
Нова функція ФІЛЬТР також може бути використана для вирішення цієї проблеми, оскільки ФІЛЬТР може фільтрувати дані за рядком або за стовпцем. Фокус полягає у створенні логічного фільтра, який виключатиме інші стовпці. В цьому випадку COUNTIF працює добре, але його потрібно налаштувати "назад", діапазон J4 і заголовок для критеріїв:
=MAX(FILTER(data,COUNTIF(J4,header)))
Після запуску COUNTIF ми маємо:
=MAX(FILTER(data,(0,0,1,0,0)))
І ФІЛЬТР доставляє 3-й стовпець у МАКС, так само, як функція 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 знову доставляє 3-й стовпець до MAX.
