Как развернуть и свернуть все поля сводной таблицы

Результат: узнаете, как добавить клавиши в ваши сводные таблицы и сводные диаграммы, чтоб стремительно развернуть / свернуть целые поля при помощи макроса VBA.

Уровень мастерства: Средний

Развертывание и свертывание полей занимает много времени

Развертывание и свертывание полей в сводной таблице быть может многоэтапным действием, который занимает много времени. Это в особенности правильно для юзеров/потребителей ваших файлов отчетов Excel, которые не знакомы с действием.

Чтоб развернуть либо свернуть поле «ВСЕ», мы обычно жмем мелкие клавиши «развернуть» (+) и свернуть (-) для всякого элемента в поле.

Для этого есть ярлычки в контекстном меню и на вкладке «Характеристики / Анализ» на ленте.

Мы также можем надавить клавиши «Развернуть поле» и «Свернуть поле» на вкладке «Анализ / характеристики» на ленте. Ячейка в области строк либо столбцов сводной таблицы обязана быть выбрана для того, чтоб эти клавиши работали.

Сочетания кнопок для операций Развернуть/Свернуть:

Развернуть все поле: alt=»Как развернуть и свернуть все поля сводной таблицы» width=»300″ height=»28″ />

Свернуть все поле: alt=»Как развернуть и свернуть все поля сводной таблицы» width=»300″ height=»26″ />

Примечание. Для работы сочетаний кнопок обязана быть выбрана ячейка в области «Строчки либо столбцы».

Но в сводной таблице нет особых клавиш, чтоб развернуть / свернуть все поле.

Новейшие клавиши «Развернуть» в сводных диаграммах Excel 2016

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

Но эта функция доступна ТОЛЬКО в Excel 2016 (Office 365).

Итак, я сделал несколько макросов, которые будут работать в хоть какой версии Excel, как в области строк, так и в столбцах сводной таблицы либо сводной диаграммы.

Мы также можем поменять сводную диаграмму, чтоб она показывала клавиши «Развернуть / Свернуть» и добавить их над сводной таблицей. Вот анимация, которая указывает, как это создать.

Шаги для конфигурации сводной диаграммы:

1. Удалите клавиши поля Значения и Ось на графике.
2. Удалите метки оси, заголовок и легенду.

Измените размер диаграммы, чтоб показывались лишь клавиши «Развернуть / Свернуть».

График будет укрыт за клавишами. Таковым образом, мы просто используем сводную диаграмму для клавиш развернуть / свернуть. Принципиально отметить, что это будет работать лишь для полей в области строк сводной таблицы и лишь в Excel 2016 (Office 365).

Развернуть/Свернуть — макросы

Этот набор макросов развернет либо свернет СЛЕДУЮЩЕЕ поле в области строк либо столбцов. Это дозволяет детализировать поля в строке либо области столбцов.

Неплохим примером является вариант, когда в сводной таблице есть поля в области строк для года, квартала, месяца, денька. Может быть, мы захотим сопоставить итоги за год, а потом перейти к детализации по кварталам либо месяцам.

Если сводная таблица в истинное время свернута по годам, макрос «Expand_Entire_RowField» развернет ВСЕ элементы года, чтоб показать кварталы для всякого года.

Пуск макроса «Collapse_Entire_RowField» приведет к сворачиванию частей Year назад.

Вы сможете скачать файл, содержащий макросы ниже.

Expand Collapse Entire Fields Macro.xlsm (68.3 KB)

Назначить макросы фигурам либо кнопочкам

Мы можем назначить эти макросы для фигур либо клавиш на листе.

Клавиши можно безпрерывно жать, чтоб проходить ввысь либо вниз по полям. Это дозволяет юзерам ваших отчетов стремительно расширять либо сворачивать целые поля.

Формы можно вставлять в сводную диаграмму, чтоб имитировать функциональность клавиш развертывания / свертывания в Excel 2016.

Макро-код VBA

Существует четыре разных макроса для развертывания/свертывания в областях строк и столбцов. Макросы употребляют For Next Loops, чтоб создать достаточно брутальный цикл по полям и элементам сводки.

Макрос Expand находит поле в наивысшей позиции, которое свернуто, проверяя свойство ShowDetail всякого PivotItem. Если он НЕ находит расширенный элемент, он расширяет все поле. Если все элементы развернуты, то он перебегает к полю в последующей позиции вниз.

Макрос Collapse делает обратное. Он начинается с самой нижней позиции поля и работает в оборотном направлении, пока не отыщет опорный элемент, который НЕ свернут. Если он отыщет развернутый элемент, он свернет все поле. В неприятном случае он {перемещается} ввысь в поле в последующей позиции в области строк и повторяет процесс.

Скачать файл

Вы сможете скачать файл, содержащий макросы для областей строк и столбцов.

Expand Collapse Entire Fields Macro.xlsm (68.3 KB)

Что можно сделать лучше?

Клавиши «Развернуть/свернуть макрос» должны стать неплохим дополнением к хоть какому файлу отчета, который вы отправляете юзерам, не являющимся ниндзя Excel. Я лицезрел, как почти все юзеры не один раз надавливали мелкие клавиши разворачивания/свертывания для всякого элемента сводки просто поэтому, что не знали, что есть наилучший метод. Этот макрос должен посодействовать им убыстрить и упростить детализацию сводных таблиц либо диаграмм.

Пожалуйста, оставьте комментарий ниже с вопросцами либо предложениями о том, как мы можем сделать лучше это. Спасибо! 🙂

Как протянуть столбец в Excel

Разглядим несколько методов протягивания (копирования) формул в строчках и столбцах программки Excel.

1-ый метод: протянуть ячейку в столбце либо строке.

Чтоб протянуть (распространить) формулу из одной ячейки в несколько ячеек столбца либо строчки следует выполнить ряд действий: 1. Записать функцию (формулу) в ячейку и надавить ENTER. 2. Навести курсор на нижний правый угол ячейки таковым образом, чтоб он заполучил форму узкого темного крестика. 3. Надавить и задерживать левую клавишу мыши.

Для резвого наполнения столбца формулой либо значением из ячейки довольно выполнить последующие деяния: 1. Записать функцию (формулу) в ячейку и надавить «ENTER». 2. Навести курсор на нижний правый угол ячейки таковым образом, чтоб он заполучил форму узкого темного крестика.

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

3-ий метод: протянуть формулу без конфигурации (смещения) начальных ячеек.

Для протягивания функции без ее конфигурации следует выполнить те же операции, что в первом и во 2-м случает. Лишь перед протягиванием необходимо зафиксировать адреса неизменяемых ячеек. Зафиксировать адреса ячеек можно добавив перед значением адреса столбца либо перед значением адреса строчки символ «$»бакса.

4-ый метод: протянуть формулу с помощью копирования.

Подступает для работы под фильтрами. Производится последующим образом: 1. Записать функцию (формулу) в ячейку и надавить ENTER. 2. Копировать значение ячейки с помощью выпадающего контекстного меню правой клавишей мыши либо сочетанием кнопок «ctrl»+»c».

Как закрепить строчку и столбец в Excel при прокрутке

Работа с многостраничными документами в Экселе осложняется тем, что применяемые ячейки нередко удалены от заглавий табличных блоков на существенное расстояние. Повсевременно прокручивать лист от заглавий к необходимым данным нерационально, длительно и неловко. Убрать этот недочет поможет функция фиксации. Для закрепления строчки в Excel требуется минимум времени, в итоге шапка в таблице остается в поле зрения всегда, независимо от того, как далековато вниз опустились ячейки с информацией. По вертикали можно создать статичным столбец при прокрутке.

Метод по фиксации строчных данных

1. Установить курсор в любом месте на листе документа.
2. Переход на вкладку «Вид», щелкнуть по значку «Закрепить области». Возникает меню, предлагающее несколько методов.
3. Избрать действие, касающееся лишь верхней строчки.

Метод по фиксации вертикальных блоков при прокрутке

1. Установив курсор в любом месте листа, перейти на вкладку «Вид» к значку «Закрепить области».
2. Избрать в показавшемся меню действий метод, касающийся лишь первого столбца.

Аналогично варианту с шапкой по горизонтали реализуется функция придания неподвижности нескольким вертикальным блокам.

Как создать недвижными строчки и столбцы вкупе

1. Курсор поставить в ячейку, которая соприкасается своими границами и со столбцом, и со строчкой, подлежащих фиксированию.
2. Переход на вкладку «Вид», выбор деяния и одноименного параметра фиксации «Закрепить области».

Как снять фиксацию

Открепление фиксации пригодиться для вас в ходе допущения ошибки, или неверного деяния в прошлых перечнях, в прочем, порядок действий идентичен: