📌 Проблема выпадающих списков (Часть 3): как создать и настроить выпадающий список уникальных значений на основании данных сводной таблицы

Продолжая тему предыдущего обзора рассмотрим, как источник данных полученный из данных сводной таблицы можно использовать для создания выпадающего списка.

📢 Файл с примером размещен в конце статьи 🔽

➡️ В данном примере е рассмотрено, как автоматизировать этот процесс, создания выпадающего списка уникальных значений на основании данных сводной таблицы

▶️ Постановка задачи и пример исходных данных

В целях демонстрационного примера рассмотрим задачу создания выпадающего списка уникальных значений на основании данных сводной таблицы:

Продолжая тему предыдущего обзора рассмотрим, как источник данных полученный из данных сводной таблицы можно использовать для создания выпадающего списка.-4

Выпадающий список будем формировать в столбце В (начиная с ячейки В6)

🔔 Пример исходных данных для создания сводной таблицы см. в предыдущем обзоре:

📌 Проблема выпадающих списков (Часть 2): как создать источник данных (уникальных значений) для выпадающего списка

Excel на ИЗИ: ✅ Приемы эффективной работы в Microsoft Excel 23 апреля

▶️ Создаем именованный диапазон

Именованные диапазоны - эффективное и удобное решение, которое позволяет присвоить понятные имена определенным областям ячеек, что облегчает работу с таблицами и формулами.

Использование именованных диапазонов в формулах делает их более читаемыми и удобными для сопровождения, поскольку имена предоставляют информацию о содержании данных в диапазоне.

Кроме того, именованные диапазоны могут использоваться в макросах, делая код более гибкими и легко адаптируемыми к изменениям в структуре данных.

Автоматически создать именованный диапазон и изменять его в зависимости от установленного критерия можно посредством следующего макроса:

Создание именованного диапазона на основе указанных данных

⚙️ Как это работает:

используя имя листа указанное в ячейке C1 определяем лист с данными
устанавливаем последнюю заполненную строку в столбце (эта информация сохраняется в переменной lastRow)
используя метод Add создаем именованный диапазон, который охватывает данные по столбцу (A) от ячейки A2 до последней заполненной определенной

➡️ В результате, назначенное имя будет отражено в диспетчере имен:

🔔 При изменении критерия именованный диапазон также будет изменен:

Изменение именованного диапазона при изменении критерия

▶️ Создаем выпадающий список

Теперь, созданный именованный диапазон может быть использован для удобного доступа к данным:

Макрос выполняет задачу создания выпадающего списка на листе "Лист2" на основе данных из определенного именованного диапазона.

🔘 Определение диапазона для выпадающего списка:

получаем имя листа, на котором будет создан выпадающий список
определяем последнюю заполненную строку в столбце (B)
устанавливаем диапазон в столбце (B), который охватывает ячейки от B6 до последней заполненной

🔘 Установка источника данных для выпадающего списка:

удаляем предыдущую настройку проверки данных в диапазоне
добавляем новую настройку проверки данных типа "список" (xlValidateList), где источник данных для выпадающего списка устанавливается равным именованному диапазону
устанавливаем параметры выпадающего списка.

➡️ В результате будет создан выпадающий список на основе данных из диапазона сводной таблицы:

▶️ Автоматизируем решение

В целях автоматизации объединим оба рассмотренных решения

Создаем выпадающий список на основе данных из определенного именованного диапазона.

🔔 Для большего удобства можно реализовать макрос назначив его на событие листа:

Автоматическое создание выпадающего списка при изменении данных в целевой ячейке

Макрос проверяет, было ли изменено значение в указанной ячейке:

в случае изменений проверяем, является ли значение пустым
если B3 не пусто, то очищаем данные в диапазоне и вызывает макрос для создания выпадающего списка. Если B3 пусто, то очищаем данные в диапазоне.

📝 Примечание автора: Код следует поместить в модуль листа, на котором выполняется действие.

Теперь, при каждом изменении значения в ячейке B3, данные в диапазоне будут очищены, а затем выполнится макрос устанавливающий выпадающий список

🔷 Подводя итог

Представленное решение является типовым примером, который позволяет автоматизировать процесс создания выпадающего списка уникальных значений на основе данных сводной таблицы. В зависимости от условий задачи может быть доработано и изменено.