Как развернуть и свернуть все поля сводной таблицы
Итог: узнаете, как добавить кнопки в ваши сводные таблицы и сводные диаграммы, чтобы быстро развернуть / свернуть целые поля с помощью макроса VBA.
Развертывание и свертывание полей занимает много времени
Развертывание и свертывание полей в сводной таблице может быть многоэтапным процессом, который занимает много времени. Это особенно верно для пользователей/потребителей ваших файлов отчетов Excel, которые не знакомы с процессом.
Чтобы развернуть или свернуть поле «ВСЕ», мы обычно нажимаем маленькие кнопки «развернуть» (+) и свернуть (-) для каждого элемента в поле.
Для этого есть ярлыки в контекстном меню и на вкладке «Параметры / Анализ» на ленте.
Мы также можем нажать кнопки «Развернуть поле» и «Свернуть поле» на вкладке «Анализ / параметры» на ленте. Ячейка в области строк или столбцов сводной таблицы должна быть выбрана для того, чтобы эти кнопки работали.
Примечание. Для работы сочетаний клавиш должна быть выбрана ячейка в области «Строки или столбцы».
Однако в сводной таблице нет специальных кнопок, чтобы развернуть / свернуть все поле.
Новые кнопки «Развернуть» в сводных диаграммах Excel 2016
В Excel 2016 была добавлена новая функция в сводные диаграммы, которая позволяет нам разворачивать или сворачивать поля в области строк. Вы увидите маленькие кнопки плюс и минус в правом нижнем углу своей сводной диаграммы.
Однако эта функция доступна ТОЛЬКО в Excel 2016 (Office 365).
Итак, я создал несколько макросов, которые будут работать в любой версии Excel, как в области строк, так и в столбцах сводной таблицы или сводной диаграммы.
Мы также можем изменить сводную диаграмму, чтобы она отображала кнопки «Развернуть / Свернуть» и добавить их над сводной таблицей. Вот анимация, которая показывает, как это сделать.
Измените размер диаграммы, чтобы отображались только кнопки «Развернуть / Свернуть».
График будет скрыт за кнопками. Таким образом, мы просто используем сводную диаграмму для кнопок развернуть / свернуть. Важно отметить, что это будет работать только для полей в области строк сводной таблицы и только в Excel 2016 (Office 365).
Развернуть/Свернуть — макросы
Этот набор макросов развернет или свернет СЛЕДУЮЩЕЕ поле в области строк или столбцов. Это позволяет детализировать поля в строке или области столбцов.
Хорошим примером является случай, когда в сводной таблице есть поля в области строк для года, квартала, месяца, дня. Возможно, мы захотим сравнить итоги за год, а затем перейти к детализации по кварталам или месяцам.
Если сводная таблица в настоящее время свернута по годам, макрос «Expand_Entire_RowField» развернет ВСЕ элементы года, чтобы отобразить кварталы для каждого года.
Запуск макроса «Collapse_Entire_RowField» приведет к сворачиванию элементов Year обратно.
Назначить макросы фигурам или кнопкам
Мы можем назначить эти макросы для фигур или кнопок на листе.
Кнопки можно непрерывно нажимать, чтобы проходить вверх или вниз по полям. Это позволяет пользователям ваших отчетов быстро расширять или сворачивать целые поля.
Формы можно вставлять в сводную диаграмму, чтобы имитировать функциональность кнопок развертывания / свертывания в Excel 2016.
Макро-код VBA
Существует четыре различных макроса для развертывания/свертывания в областях строк и столбцов. Макросы используют For Next Loops, чтобы сделать довольно агрессивный цикл по полям и элементам сводки.
Макрос Expand находит поле в наивысшей позиции, которое свернуто, проверяя свойство ShowDetail каждого PivotItem. Если он НЕ находит расширенный элемент, он расширяет все поле. Если все элементы развернуты, то он переходит к полю в следующей позиции вниз.
Макрос Collapse делает противоположное. Он начинается с самой нижней позиции поля и работает в обратном направлении, пока не найдет опорный элемент, который НЕ свернут. Если он найдет развернутый элемент, он свернет все поле. В противном случае он перемещается вверх в поле в следующей позиции в области строк и повторяет процесс.
Скачать файл
Вы можете скачать файл, содержащий макросы для областей строк и столбцов.
Что можно улучшить?
Пожалуйста, оставьте комментарий ниже с вопросами или предложениями о том, как мы можем улучшить это. Спасибо! 🙂
Объединение столбцов в Microsoft Excel. Как объединить столбцы в excel без потери данных
Однако это не все. Хотя столбики и были объединены, но если значения, располагающиеся первоначально, будут изменены или удалены, переменится и результат, поскольку была применена формула, а ее результат связан с полученными данными.
