В продолжение темы предыдущего обзора, отметим, что самый простой способ создать список уникальных значений - это построить сводную таблицу. Далее, на основании полученных данных установить проверку, указав тип данных Список, а в качестве источника - нужный диапазон.
Но, и в этом подходе есть свои "подводные камни", например, обновление данных и изменение диапазона сводной таблицы при добавлении новых значений или изменении условий.
В данной части обзора рассмотрен пример, позволяющий унифицировать решение и создать список уникальных значений в зависимости от выбранного критерия.
Представленный подход может использоваться как самостоятельно, так и дополнительно при разработке приложений.
📢 Файл с примером размещен в конце статьи 🔽
▶️ Постановка задачи
Пример исходных данных:
В зависимости от предустановленного критерия создать список уникальных значений:
Реализацию решения представить посредством создания сводной таблицы в редакторе VBA Excel.
▶️ Создаем сводную таблицу (VBA EXCEL)
Для удобства решения определим имя нового листа и имя сводной таблицы в ячейках C1 и D1 соответственно
Представленный макрос создает новый лист в книге на котором, на основе указанных данных, размещает сводную таблицу:
⚙️ Последовательность действий:
- определение переменных
- определение листа с исходными данными и последней заполненной строки
- создание нового листа для сводной таблицы и присвоение ему соответствующего имени
- определение диапазона данных для сводной таблицы (до последней заполненной строки на листе с данными)
- создание кэша сводной таблицы и самой сводной таблицы на новом листе
➡️ В результате будет создан новый лист (с указанным в ячейке C1 именем) и пустая сводная таблица (с указанным в ячейке D1 именем):
Представленное решение автоматизирует процесс создания нового листа и сводной таблицы на основе указанных данных. Но, какая же сводная таблица без данных 😉
▶️ Настраиваем поля сводной таблицы
Чтобы настроить поля существующей сводной таблицы применим макрос:
⚙️ Как это работает:
- извлекаем имя поля сводной таблицы из ячейки B3 и применяем его к указанной сводной таблице.
- чтобы избежать ошибки проходим по всем полям сводной таблицы и скрываем их
- настраиваем сводную таблицу, устанавливая требуемые параметры. Например, отображение строк, столбцов и форматирование данных в режиме "Табличный" (дополнительно можно установить размер столбца)
➡️ В результате получаем заполненную сводную таблицу представляющую список уникальных значений:
▶️ Унифицируем решение
Чтобы решение было более унифицировано объединим 2 (два) рассмотренных выше этапа в один.
🔘 Шаг 1: Внесем изменения в рассмотренный выше макрос посредством которого настраивали пустую сводную таблицу
В данном случае макрос создает новую сводную таблицу на основе указанных данных и настраивает ее в соответствии с заданными параметрами.
📝 Примечание автора:
🔘 Шаг 2: Учитываем условие проверки существования листа и сводной таблицы
Для проверки существования листа с определенным именем можно использовать функцию, которая проверяет, существует ли лист с заданным именем в рабочей книге:
Функция принимает имя листа в качестве аргумента и возвращает True, если лист существует, и False в противном случае.
Теперь, можно использовать эту функцию в макросе для проверки существования листа с определенным именем и выполнения соответствующих действий:
🔘 Шаг 3: Тестируем результат
Макрос проверяет, существует ли рабочий лист с именем, указанным в ячейке, далее:
- если рабочий лист существует, вызывается макрос для настройки полей сводной таблицы:
- если рабочий лист не существует, вызывается макрос для создания и настройки новой сводной таблицы.
🔔 Теперь полученный источник данных (список уникальных значений) можно использовать для создания выпадающего списка.
➡️ Но, о том, как автоматизировать этот процесс, рассмотрим в следующем обзоре 😉.
➡️ Узнать больше о создании выпадающих списков Excel 🔽
🙌 Оставайся с нами на связи и узнавай больше о других методах и приемах работы в Excel:
✅ СКАЧАТЬ ПРИМЕР 🔽