Вопрос или проблема
У меня есть ситуация, когда я хочу найти ближайшее значение в массиве, а затем использовать его для поиска другого значения.
Вот мой пример.
В ячейке H4 пользователи могут ввести желаемое значение (в минутах [мин]), которое они хотят для проведения конкретного теста. В этом случае выбран Тест 1 (ячейка I4). Я хочу получить ближайшее значение в B4:B9 (столбец мин массива), которое равно или меньше значения в ячейке H4. Моя формула на данный момент возвращает правильное число (5; ячейка J4), но я хотел бы расширить свою формулу, чтобы вернуть значение для var1 (столбец C), которое связано со значением 5.
Мой результат должен быть 113, так как 113 связано с 5 минутами для Теста 1.
Как я могу продолжить свою формулу в ячейке J4 (см. ниже), чтобы вернуть желаемое число для var1 на основе ближайшего значения для минут?
=IF(I4="","",INDEX(A4:C9,MATCH(H4,IF(A4:A9=I4,B4:B9),1),2))
Самая простая версия, которая будет работать со всеми версиями Excel:
=SUMPRODUCT((A2:A7=F3)*(B2:B7=G3)*C2:C7)
Вы также можете использовать:
=SUMIFS(C2:C7, A2:A7, F3, B2:B7, G3)
Или:
=FILTER(C2:C7,(A2:A7=F3)*(G3=B2:B7))
Или, если вы ДЕЙСТВИТЕЛЬНО хотите сделать это с INDEX/MATCH:
=INDEX(C2:C7,MATCH(1,(A2:A7=F3)*(B2:B7=G3)))
Ответ или решение
Чтобы использовать два выражения INDEX и MATCH для возврата конкретного значения в Excel, необходимо разбить вашу задачу на несколько шагов. Рассмотрим вашу ситуацию, в которой вы хотите найти ближайшее значение в массиве и затем использовать это значение для поиска другого значения.
Задача
На основе вашего описания, вы хотите выполнить следующие действия:
- В ячейке H4 пользователь вводит желаемое значение в минутах.
- В ячейке I4 выбирается конкретный тест (например, Test 1).
- Вам нужно найти ближайшее значение из столбца B (время в минутах), которое меньше или равно значению из H4.
- Затем используйте это значение, чтобы вернуть соответствующее значение из столбца C (var1).
Работа с INDEX и MATCH
Для того чтобы достичь вашей цели, вам нужно использовать две функции MATCH для поиска нужных данных.
Формула
Вы можете использовать следующую формулу в ячейке J4:
=IF(I4="", "", INDEX(C4:C9, MATCH(MAX(IF(A4:A9=I4, B4:B9, 0)), B4:B9, 0)))Объяснение Формулы:
-
IF(I4="", "", …): Эта часть проверяет, выбрано ли какое-либо значение в ячейке I4. Если нет, то возвращается пустая строка.
-
IF(A4:A9=I4, B4:B9, 0): Здесь проверяется соответствие значений в столбце A (тесты) с выбранным тестом из I4. Если совпадение найдено, возвращаются значения из столбца B, иначе возвращается 0.
-
MAX(…): Мы используем функцию MAX, чтобы найти максимальное значение времени (минут), которое меньше или равно введенному значению из H4, только для тех строк, которые соответствуют выбранному тесту.
-
MATCH(…, B4:B9, 0): Этот вызов MATCH ищет индекс найденного максимального значения среди значений в диапазоне B4:B9.
-
INDEX(C4:C9, …): Затем функция INDEX использует найденный индекс для извлечения соответствующего значения из диапазона C4:C9.
Важно!
Массивные Формулы: Чтобы правильно использовать данную формулу с массивами, вам может понадобиться задать её как массивную форму, нажав Ctrl+Shift+Enter, если вы используете более ранние версии Excel, которые не поддерживают динамические массивы.
Пример
Предположим, у вас есть следующие данные:
| A (Тесты) | B (Минуты) | C (var1) |
|---|---|---|
| Test 1 | 5 | 113 |
| Test 1 | 10 | 120 |
| Test 2 | 15 | 150 |
| Test 1 | 20 | 140 |
| Test 2 | 25 | 160 |
| Test 1 | 30 | 130 |
Если в H4 вы введете 8, и в I4 выберете "Test 1", то формула в J4 вернет 113, так как 5 — ближайшее значение, которое меньше или равно 8 для "Test 1".
Заключение
Данная формула позволяет эффективно использовать функции INDEX и MATCH для поиска значений в Excel с учётом заданных условий. Настройте диапазоны в формуле, чтобы они соответствовали вашим данным, и вы сможете быстро находить нужные вам значения на основе вводимых данных.