Сводные таблицы в Excel - неоспоримо удобный инструмент, который обеспечивает быструю и эффективную обработку больших объемов данных, а также упрощает анализ и визуализацию информации.
Однако, при работе с данными из различных источников возникает необходимость в постоянном обновлении источника данных, что может быть раздражающим моментом 🤔.
В данном обзоре представлен пример решения, который поможет упростить и автоматизировать процесс обновления источника данных для сводной таблицы.
📢 Файл с примером размещен в конце статьи 🔽
▶️ Пример исходных данных
В качестве источника исходных данных рассмотрим 3 (три) таблицы расположенные на разных листах, каждой из которых, задано соответствующее имя:
На основании одного из источников создаем сводную таблицу:
Также, для целей данного примера размещаем элемент управления ActiveX ComboBox - поле со списком:
📝 Примечание автора: Посредством данного элемента управления будем обновлять источник данных для сводной таблицы.
А также, определим именованные ячейки и диапазоны:
▶️ ComboBox: как установить диапазон данных и связь с ячейкой
Установить диапазон данных для элемента управления и задать связь с ячейкой можно несколькими способами
🔘 Способ 1: Указать диапазон и связь с ячейкой в свойствах
🔘 Способ 2. Использовать метод Worksheet.Activate или событие листа
Пример основного макроса:
Источник данных для ComboBox1 представляет собой именованный диапазон.
Таким образом, при каждом запуске макроса элемент управления будет обновляться данными из указанного диапазона.
🔔 Автоматизировать работу макроса можно несколькими способами:
Рассмотрим два основных:
⏩ Вариант 1. Метод Worksheet.Activate
- позволяет активировать макрос при активации конкретного листа.
Это означает, что когда пользователь выбирает или переключается на данный лист, указанный макрос будет автоматически выполнен.
Например,
В результате, при активации указанного листа элемент управления будет заполнен данными из именованного диапазона:
⏩ Вариант 2. Событие Worksheet.SelectionChange
- запускает макрос при изменении выделенной области на листе:
➡️ В данном случае, макрос выполняется только при клике на определенную ячейку. Далее, элемент управления заполняется данными из именованного диапазона:
Чтобы установить связь с ячейкой применим макрос:
Выбранное значение помещается в именованную ячейку "names" с помощью строки кода: ThisWorkbook.Names("names").RefersToRange.Value = selectedValue
🔔 Макрос назначим на событие элемента управления ComboBox1_Change:
➡️ Таким образом, при выполнении макроса выбранное значение из ComboBox1 будет записано в указанную именованную ячейку, обеспечивая связь между элементом управления ComboBox и указанной ячейкой:
▶️ Как автоматически обновить источник данных для сводной таблицы
Пример макроса:
🔔 Чтобы обеспечить автоматическое обновление источника данных для сводной таблицы на основе значения, содержащегося в указанной ячейке добавим макрос на событие элемента управления ComboBox1_Change:
➡️ Теперь, при каждом изменении значения в ComboBox1 сначала вызывается процедура, которая устанавливает выбранное значение в именованную ячейку. Затем, процедура, которая обновляет источник данных сводной таблицы:
🔷 Подводя итог ...
Рассмотренный прием является типовым решением и позволяет обеспечить автоматическое обновление источника данных для сводной таблицы на основе значения, содержащегося в указанной ячейке. В соответствии с конкретными потребностями и целями проекта код может быть доработан и изменен.
🔵 Ознакомиться с другими приемами VBA Excel вы можете в наших предыдущих обзорах 🔽
🙌 Оставайся с нами на связи и узнавай больше о других методах и приемах работы в Excel:
✅ СКАЧАТЬ ПРИМЕР 🔽
