В контексте работы с данными, содержащими повторяющиеся значения зачастую возникает вопрос:
- Как выполнить поиск по критерию при наличии повторов!?
Один из примеров решения демонстрирующий подход создания списка уникальных значений и поиск по критерию при наличии повторов рассмотрен в данном обзоре 😉.
📢 Файл с примером размещен в конце статьи 🔽
▶️ Пример исходных данных
В качестве исходных данных будем использовать информацию содержащую повторяющиеся значения:
🎯 Задачей данного примера будет создать список уникальных значений и выполнить поиск по критерию при наличии повторов
▶️ Список уникальных значений
Для целей данного примера список уникальных значений будем формировать посредством макроса:
⚙️ Как работает процедура:
- Проверяем, выбрана ли ячейка A2. Если нет, ничего не произойдет.
- Если ячейка A2 выбрана, определяем последнюю заполненную ячейку в столбце A.
- Далее, создаем объект словаря. Этот объект удобен для хранения уникальных значений (он сохраняет каждое значение только один раз).
- После создания словаря перебираем все ячейки в столбце A, начиная со строки 6 и заканчивая последней заполненной ячейкой, которую определили ранее. Если встречаем непустую ячейку, то добавляем значение ячейки в словарь.
- После того, как перебирали все ячейки, преобразуем ключи словаря, в которых хранятся все уникальные значения, в массив.
- В завершении, удаляем все существующие правила проверки в ячейке A2 и заменяем их новым правилом. Это новое правило ограничит ввод в ячейку A2 списком элементов, которые являются уникальными значениями из столбца A, найденного ранее.
🔔 Важно, код должен быть помещен в модуль кода листа, в котором должно выполняться действие.
Такой подход позволит создать выпадающий список уникальных значений в ячейке A2 при клике на эту ячейку:
▶️ Зависимый выпадающий список
Следующим шагом определим возможность выбора значений соответствующих выбранному критерию.
Решать поставленную задачу будем посредством именованных диапазонов (ячеек) и сочетания встроенных функций
🔘 Шаг 1: Определяем именованные диапазоны и ячейки
Чтобы вернуть диапазон, смещенный от искомой ячейки (А2) применим сочетание встроенных функций СМЕЩ(), ПОИСКПОЗ() и СЧЁТЕСЛИ():
⚙️ Как это работает:
- ПОИСКПОЗ(Критерий_Фирма; Фирма; 0) - ищет 'Критерий_Фирма' в диапазоне 'Фирма' и возвращает позицию его первого вхождения. Ноль в конце указывает, что совпадение должно быть точным.
- СЧЁТЕСЛИ(Фирма; Критерий_Фирма) - подсчитывает вхождения 'Критерий_Фирма' в диапазоне 'Фирма'.
- Смещение по строкам определяется положением, найденным функцией ПОИСКПОЗ с учетом минус(-) 1, так как Excel начинает отсчет с 1, а не с 0) и количеством, заданным функцией СЧЁТЕСЛИ. Единица (1) в конце указывает функция СМЕЩ вернет диапазон шириной в 1 столбец.
🔘 Шаг 2: Результирующей формуле назначим имя
🔘 Шаг 3: Назначим проверку данных
Для ячейки В2 задаем тип данных список и укажем источник:= Список_Город
Таким образом, получаем зависимый выпадающий список:
▶️ Поиск по критерию (при наличии повторов)
Завершающим этапом задачи является поиск значений одновременно удовлетворяющих нескольким условиям
📝 Отметим, в рамках рассматриваемого примера и первый и второй диапазоны содержащие критерии поиска имеют повторы.
А потому, объединим данные поискового запроса (в данном случае названия компании и названия города).
Решение в данном случае представлено сочетанием функций ИНДЕКС() и ПОИСКПОЗ()
где ИНН", "Критерий_Фирма", "Критерий_Город", "Фирма" и "Город" являются именованными диапазонами
В данном случае функция ПОИСКПОЗ выполняет поиск в объединенных столбцах компании и города по порядку, возвращая взаимное положение первого совпадения:
Для удобства (аналогично рассмотренному ранее примеру) результирующей формуле назначим имя:
Указав назначенное имя в ячейке C2 получаем доступ к необходимым данным, даже при наличии повторов:
▶️ Дополнения
Чтобы визуально исключить ошибку выбора при смене критерия поиска дополнительно было назначено условное форматирование:
▶️ Тестируем результат
➡️ В заключение отметим, что рассмотренный пример является типовым решением и может быть дополнен и изменен в зависимости от условий конкретной задачи
⏩ Дополнительные рекомендации по работе с выпадающими списками:
🙌 Оставайся с нами на связи и узнавай больше о других методах и приемах работы в Excel:
✅ СКАЧАТЬ ПРИМЕР 🔽
