Поиск значений на основе выбранного критерия, пожалуй, одна из распространенных задач с которой пользователь Excel сталкивается на практике.
В данном обзоре мы рассмотрим 3 (три) подхода решения позволяющие создать список значений на основе выбранного критерия.
📢 Файл с примером размещен в конце статьи 🔽
▶️ Исходные данные
Для целей данного примера в качестве исходных данных рассмотрим следующие:
Для удобства дальнейшей работы назначим именованные диапазоны:
🔔 Обращаем внимание! В данном примере список уникальных значений по столбцу "Менеджер" сформирован с использованием сочетания функций СОРТ и УНИК.
Данный подход позволил не только сформировать список уникальных значений, но и отсортировать его.
📝 Примечание автора: если ваша версия Excel не содержит данных функций набор данных для выпадающего списка можно создать, используя один из описанных ниже подходов:
▶️ Цели и задачи
В качестве критерия будем использовать данные ячейки С2 (выбор осуществляется из выпадающего списка) которой присвоим имя "Критерий":
🎯 Задача: сформировать создать список значений (перечень проданных товаров) на основе выбранного критерия (Менеджера)
▶️ Способ 1. Используем сочетание функций
Для решения поставленной задачи будем использовать сочетание встроенных функций Excel
Одной из встроенных функций позволяющей выполнить левосторонний поиск является ИНДЕКС:
В контексте рассматриваемого примера в качестве аргументов для данной функции достаточно будет указать массив и номер строки.
Так как массив определен ранее как именованный диапазон "Товар", то остается определить только номер строки.
Для поиска номера строки можно воспользоваться сочетанием функций НАИМЕНЬШИЙ+СТРОКА+ЧСТРОК:
Данный подход позволит определить номера строк, в которых находятся соответствующие значения:
⚙️ Краткое описание:
🔘 ЕСЛИ(Менеджер=Критерий; ...): условие проверяет, соответствует ли значение в столбце "Менеджер" критерию, заданному в ячейке "Критерий".
- Если условие выполняется, формула продолжает работу, иначе возвращает ЛОЖЬ.
- Часть формулы СТРОКА(Товар)-1 возвращает номер строки в столбце "Товар". -1 вычитается для того, чтобы получить корректный номер строки, с которой начинается поиск (напомним, назначенный именованный диапазон начинается со второй строки)
🔘 НАИМЕНЬШИЙ(ЕСЛИ(...);ЧСТРОК($B$5:B5)): находит минимальное значение в массиве, где ЧСТРОК($B$5:B5) определяет позицию критерия в указанном массиве.
В результате формула определяющая список значений на основе выбранного критерия будет иметь вид:
🔔 Обращаем внимание! Чтобы учесть обработку возможных ошибок в результирующую формулу включена функция ЕСЛИОШИБКА. Теперь, если при выполнении формулы возникает ошибка, то возвращается пустая строка.
📝 Примечание автора: Разместив формулу в ячейке В5 завершите ее сочетанием клавиш Ctrl + Shift + Enter и распространите ее вниз. Список значений на основе выбранного критерия готов! 😉
▶️ Способ 2. Пишем макрос
В ряде случаев применения сочетаний функций может быть несколько утомительно и не всегда оправдано.
А потому, в качестве альтернативы, рассмотрим процедуру события Worksheet_Change, которая автоматически выполняется при изменении ячейки на листе:
⚙️ Краткое описание:
- Объявляем необходимые переменные
- Задаем ячейку на текущем листе в качестве ячейки критерия. В тот момент, когда в эту ячейку вносятся какие-либо изменения, код предпринимает соответствующие действия.
- Если выбранная ячейка критерия не пустая, проверяем наличие соответствующих критериев.
- В случае если соответствующий критерий не найден или выбранный критерий пуст, очищаем содержимое столбца "B".
- Если критерии найдены, после предварительной очистки содержимого столбца "B" добавляем соответствующие товары в ячейку в столбце "B" текущего листа.
🔔 Немаловажной частью кода является Application. Screenupdating = False, который останавливает визуальное обновление экрана в Excel.
Любые действия, выполняемые кодом, не видны пользователю до тех пор, пока для параметра Application.ScreenUpdating не будет установлено значение True.
Это служит двум целям: ускоряет выполнение кода, а изменения, внесенные кодом (например, очистить содержимое), не отображаются пользователю шаг за шагом, что улучшает общее взаимодействие с пользователем.
🔔 Обращаем внимание, в макросе используется вспомогательная функция FindAllValues, которая и выполняет левосторонний поиск и возвращает список ячеек с соответствующими критериям:
Теперь при изменении изменение ячейки с критерием на текущем листе макрос осуществляет поиск соответствующих значений и добавляет их на текущем листе (начиная с ячейки B5).
Данный подход будет полезен, когда необходимо динамически обновлять список товаров на основе выбранного критерия.
▶️ Способ 3. Создаем сводную таблицу
Сводные таблицы — это еще один способ позволяющий с легкостью справиться с поставленной задачей который под силу даже начинающему пользователю.
Поместив критерий выбора в поле фильтра сводной таблицы получим аналогичный список значений на основе выбранного критерия:
Альтернативным решением будет совместное использование сводной таблицы и срезов:
Данный подход также обеспечит быстрый результат на основе выбранного критерия.
🙌 Оставайся с нами на связи и узнавай больше о других методах и приемах работы в Excel:
✅ СКАЧАТЬ ПРИМЕР 🔽
