Итог: узнайте, как автоматически создавать более описательные и динамические заголовки диаграмм, которые отображают критерии фильтрации с макросами VBA.
Уровень мастерства: Средний
Названия сводных диаграмм по умолчанию — «Итого»
Название диаграммы должно описывать данные, представленные в диаграмме.
При вставке новой сводной диаграммы в качестве заголовка диаграммы обычно используются слова «Итого» или «Заголовок диаграммы». Это означает, что мы всегда должны предпринимать дополнительные шаги, чтобы изменить заголовок диаграммы, чтобы сделать ее более наглядной.
Итак, я написал несколько макросов для автоматизации этого процесса, а также для создания динамических заголовков, в которых перечислены критерии фильтра.
Примечание. Макросы просто выполняют настройку и НЕ нужны для интерактивных субтитров. Подробнее об этом ниже.
Если вы новичок в создании сводных таблиц и сводных диаграмм, ознакомьтесь с моей бесплатной серией из трех частей, посвященной сводным таблицам и сводным панелям.
Макросы заголовка автоматической диаграммы
Следующие макросы создают описательные заголовки диаграмм и динамические субтитры для наших сводных диаграмм. Макросы используют имена (подписи) полей, используемых в сводной диаграмме, для создания заголовков и субтитров.
Скачать файл Excel
Вы можете скачать файл Excel, который содержит макросы ниже. Макросы могут быть добавлены в вашу личную книгу макросов.
Dynamic PivotChart Title Macro.xlsm (266.0 KB)
Макрос № 1: Сводная диаграмма автозаписи
Первый макрос создает простой заголовок диаграммы на основе полей в областях «Значения», «Строки» и «Столбцы» сводной диаграммы (сводной таблицы).
Макрос проходит по каждому PivotField в значениях (DataFields), Rows (RowFields) и столбцах (ColumnFields) PivotChart. Он создает строку текста, которая объединяет каждое имя поля (подпись).
Примечание. Макрос использует свойство Caption поля сводной таблицы, которое представляет собой имя поля, отображаемое в сводной таблице и диаграмме. Заголовок может отличаться от свойства Name. Имя — это имя источника (столбца) из исходных данных.
В тексте используются разделители между значениями и полями строк / столбцов, которые можно изменить в коде. Вот пример структуры заголовка.
[Values Field Caption] by [Rows Field Caption] by [Columns Field Caption]
Вот пример реального названия
Total Revenue by Salesperson by Quarter
Макрос будет включать несколько полей в любой из областей и перечислять их в порядке расположения полей. Вот пример.
[Values Field Caption] by [1st Rows Field Caption] by [2nd Rows Field Caption] by [Columns Field Caption]
Если область имеет несколько полей в области «Значения», то амперсанд & используется для разделения имен полей.
Опять же, это простой макрос, который просто экономит время от необходимости вводить заголовок диаграммы вручную.
Если вы случайно включили и выключили флажок «Заголовок диаграммы», тогда заголовок диаграммы будет сброшен на «Итого». Как правило, вам придется повторно вводить заголовок, но этот макрос также сэкономит время.
Макрос №2. Текстовые поля заголовков динамических сводных диаграмм
Второй макрос создает динамический субтитр для включения полей в области «Фильтры» и их критериев фильтрации. Этот макрос сэкономит вам много времени при ручной настройке.
Как работает макрос
Этот макрос немного более продвинутый. Вот скриншот из макроса в действии.
Сначала он просматривает поля в области «Фильтры» сводной таблицы сводной диаграммы и создает формулу ячейки. Макрос фактически создает строку текста, которая ссылается на ячейки в области фильтров LabelRange (ячейки в сводной таблице, которые содержат поля фильтра и значения фильтра (критерии).
Макрос затем отображает InputBox и предлагает пользователю выбрать пустую ячейку для формулы. Формула вводится в лист.
Когда формула оценивается, она отобразит строку текста, содержащую поля фильтра и их критерии. Вот пример.
Filters: Year: 2014 | Quarter: Qtr1, Qtr2 | Region: East
Текстовое поле (форма) добавляется в сводную диаграмму для субтитров. Текстовое поле связано с ячейкой, содержащей формулу. Это считается динамическим заголовком, поскольку текст в текстовом поле изменится в результате изменения формулы.
Показать несколько элементов фильтра
В этом примере я также использую другую технику для отображения нескольких элементов фильтра в списке через запятую. Изучите этот пост 3 способа отображения (несколько элементов) критериев фильтрации в сводной таблице, чтобы узнать больше.
Когда фильтры применяются к сводной таблице / диаграмме, формула отображает критерии фильтра, как и текстовое поле.
Наконец, макрос форматирует текстовое поле в соответствии с цветом текста заголовка диаграммы. Он также изменяет размеры области графика на диаграмме, чтобы текстовое поле не перекрывало ее.
Макрос выполняет большую работу по созданию динамических субтитров диаграммы. Хорошей новостью является то, что вам не нужно выполнять какие-либо ручные настройки. Просто запустите макрос!
Важно отметить, что макрос выполняет только настройку. После завершения настройки макрос больше не нужен. Формула в ячейке обновляется, когда применяются фильтры, а в связанном текстовом поле на диаграмме просто отображается содержимое ячейки. Это означает, что вам НЕ нужно хранить макросы в файлах, на которых вы запускаете этот макрос. Это хорошо, если вы отправляете файлы другим пользователям и не хотите, чтобы файл содержал макросы.
Макрос №3. Динамические заголовки сводных диаграмм
Третий макрос просто вызывает макрос # 1 и # 2. Вы можете использовать макросы независимо, если хотите создать только заголовок или субтитр. Или вы можете запустить их вместе, чтобы создать оба заголовка одновременно.
Добавить макрокоманды на панель инструментов
В приведенном выше видео я запускаю макросы с панели «Мои макросы». Я создал кнопки, которые вызывают макросы.
Макросы хранятся в моей личной книге макросов. Это означает, что я могу запускать макросы на любой сводной диаграмме в любой открытой книге.
Как только вы это настроите, вам просто нужно выбрать диаграмму, а затем нажать одну из кнопок макроса на пользовательской ленте.
Посмотрите мою серию видео из 4 частей в Персональной книге макросов, чтобы узнать, как ее настроить и создавать собственные ленты и кнопки.
Как вы будете использовать эти макросы?
Вы можете изменить макросы, добавив в них дополнительное форматирование, размещение заголовков и т.д.
Изучите мою надстройку выравнивания диаграмм, чтобы узнать, как быстро перемещать и выравнивать элементы диаграммы.
Пожалуйста, оставьте комментарий ниже с любыми вопросами или предложениями о том, как бы вы изменили эти макросы. Спасибо! 😊