Всем привет! 👋
О возможностях функции ВПР мы говорили не однократно.
Но функция ВПР() имеет свои ограничения, одно из которых заключается в том, что она находит только одно значение, соответствующее искомому запросу.
Однако, что делать, если в таблице присутствуют несколько одинаковых значений, удовлетворяющих запросу? Как найти все соответствующие значения?
Об этом и поговорим! Цель сегодняшнего обзора научиться получать список значений, удовлетворяющих искомому запросу.
📝 Рассмотрим пример
Задача. Для выбранного менеджера вывести список:
- Сумма по договору
- Номер договора
- Дата закрытия
▶️ Разбираем решение
🔔 Для решения задачи воспользуемся формулой массива:
Используемая формула имеет следующую логику:
- Функция ДЛСТР($F$1) проверит длину содержимого ячейки F1. Когда длина больше нуля, то выполняется следующая часть формулы, иначе результатом будет пустая ячейка:
- Внутри вложенной функции ЕСЛИ() происходит проверка условия $A$2:$A$40=$F$1, сравнивая значения столбца A с содержимым ячейки F1:
- Когда условие истинно, то функция СТРОКА() возвращает номер строки для соответствующих совпадающих значений, иначе результатом будет пустая строка:
- Функция НАИМЕНЬШИЙ() находит наименьшее значение в массиве, полученном от предыдущей функции ЕСЛИ. Это позволяет определить наименьший номер строки для совпадающих значений:
- Функция ИНДЕКС($B$1:$B$40;...) возвращает значение из столбца B, соответствующее наименьшему номеру строки, найденному предыдущей функцией НАИМЕНЬШИЙ():
- "Обертка" ЕСЛИОШИБКА обрабатывает возможные ошибки, которые могут возникнуть при выполнении формулы. Если формула возвращает ошибку, то результатом будет пустая строка, иначе будет возвращено значение из функции ИНДЕКС():
🔔 Важно: завершить формулу следует сочетанием клавиш
Аналогично, определяем номер договора и дату закрытия договора изменив диапазоны:
▶️ Результат
Таким образом, формула позволяет проверить условие, используя значение ячейки F1, и возвращает соответствующее значение, на основе наименьшего номера строки, соответствующего условию.