📌 Формула массива: практика решений. Как выбрать все одинаковые значения в столбце

Всем привет! 👋

О возможностях функции ВПР мы говорили не однократно.

Но функция ВПР() имеет свои ограничения, одно из которых заключается в том, что она находит только одно значение, соответствующее искомому запросу.

Однако, что делать, если в таблице присутствуют несколько одинаковых значений, удовлетворяющих запросу? Как найти все соответствующие значения?

Об этом и поговорим! Цель сегодняшнего обзора научиться получать список значений, удовлетворяющих искомому запросу.

Формула массива: практика решений. Как выбрать все одинаковые значения в столбце

📝 Рассмотрим пример

Задача. Для выбранного менеджера вывести список:

• Сумма по договору
• Номер договора
• Дата закрытия

▶️ Разбираем решение

🔔 Для решения задачи воспользуемся формулой массива:

{ЕСЛИОШИБКА(ЕСЛИ(ДЛСТР($F$1)>0;ИНДЕКС($B$1:$B$40;НАИМЕНЬШИЙ(ЕСЛИ(($A$2:$A$40=$F$1);СТРОКА($A$2:$A$40);"");СТРОКА()-1));"");"")}

Используемая формула имеет следующую логику:

• Функция ДЛСТР($F$1) проверит длину содержимого ячейки F1. Когда длина больше нуля, то выполняется следующая часть формулы, иначе результатом будет пустая ячейка:

Если условие ДЛСТР($F$1) истинно и значение успешно извлечено с помощью функции ИНДЕКС, то формула возвращает это значение. В противном случае, если условие не выполнено или значение не может быть извлечено, формула возвращает пустую строку.

• Внутри вложенной функции ЕСЛИ() происходит проверка условия $A$2:$A$40=$F$1, сравнивая значения столбца A с содержимым ячейки F1:

Функция ЕСЛИ

• Когда условие истинно, то функция СТРОКА() возвращает номер строки для соответствующих совпадающих значений, иначе результатом будет пустая строка:

• Функция НАИМЕНЬШИЙ() находит наименьшее значение в массиве, полученном от предыдущей функции ЕСЛИ. Это позволяет определить наименьший номер строки для совпадающих значений:

Функция НАИМЕНЬШИЙ выполняет поиск наименьшего значения в диапазоне. Затем она возвращает номер строки с наименьшим значением

• Функция ИНДЕКС($B$1:$B$40;...) возвращает значение из столбца B, соответствующее наименьшему номеру строки, найденному предыдущей функцией НАИМЕНЬШИЙ():

Функция ИНДЕКС использует номер строки, возвращенный функцией НАИМЕНЬШИЙ, чтобы извлечь соответствующее значение из диапазона

• "Обертка" ЕСЛИОШИБКА обрабатывает возможные ошибки, которые могут возникнуть при выполнении формулы. Если формула возвращает ошибку, то результатом будет пустая строка, иначе будет возвращено значение из функции ИНДЕКС():

Функция ЕСЛИОШИБКА

🔔 Важно: завершить формулу следует сочетанием клавиш

CTRL+SHIFT+Enter

Аналогично, определяем номер договора и дату закрытия договора изменив диапазоны:

Номер договора

Дата закрытия

▶️ Результат

Таким образом, формула позволяет проверить условие, используя значение ячейки F1, и возвращает соответствующее значение, на основе наименьшего номера строки, соответствующего условию.

Наша команда очень заинтересована в создании качественного и полезного контента. Поэтому, если Вам понравился этот урок, не забудьте поблагодарить автора лайком 👍. Если у Вас есть вопросы или своё решение, не стесняйтесь оставить комментарий 💬. Нужно решение для другой проблемы? Пожалуйста, напишите свой вопрос в комментариях 💬, и мы обязательно рассмотрим его в ближайшей теме обзора. Мы ценим Вашу обратную связь 💝, так как это помогает нам оценить, насколько данный материал был полезен для Вас

⏩ СКАЧАТЬ ПРИМЕР