Excel vba свернуть сгруппированные строки
Есть ли способ программно группировать/разгруппировать столбцы или строки в Excel 2010?
- команда "manual" находится в Data > Outline > Group/Ungroup
- в excel 2003, это использовалось для работы: someSheet.columns(i).ShowDetail = True / False , но оно больше не работает в 2010 году для групп (только для сводных таблиц и промежуточных групп)
- Запись макроса не создает никакого кода, который я мог бы использовать
Более точно, вызов myRange.ShowDetail = True в Excel 2010 расширяет свернутую группу, но вызывает ошибку, если группа уже расширена. И свойство ShowDetail возвращает True, независимо от того, была ли группа расширена или нет.
В Excel 2010 свойство ShowDetail всегда возвращает true для группы, независимо от того, было ли оно свернуто или расширено. Вместо этого можно использовать свойство Hidden :
Относительно строк не в сводных таблицах. Не было моего опыта в Excel 2010, что ShowDetail ALWAYS оценивает значение True. Я думал, что это так, но я не понимал, что мне нужно быть в сводной строке, чтобы это свойство работало, как ожидалось. Во-вторых, я не понимал, что итоговая строка по умолчанию находится под группированными строками. Тестирование для свернутого/расширенного режима стало намного яснее, как только я изменил этот параметр, чтобы иметь сводную строку над группируемыми строками (в ленте: данные > контур, показ ящика рамки).
Если моя выбранная ячейка находится в суммарной строке, ShowDetail evalutes возвращает значение True, если отображаются сгруппированные записи, и False, если они не являются. Ключ для меня был в итоговой строке, чтобы увидеть, что поведение работает таким образом. Наличие по умолчанию дочерних/сгруппированных строк действительно меня бросило.
Здесь мой макрос, который динамически расширяет и сворачивает сгруппированные записи, привязанные к суммарной строке, когда я выбираю ячейку в итоговой строке. И это делает мою ячейку в столбце "Полужирный", если раздел расширяется. Этот макрос не запускается, если я выбрал более одной ячейки.
Обратите внимание, что защита рабочего листа предотвращает расширение и свертывание групп ячеек. Мой рабочий лист защищен, поэтому я снимаю листы, чтобы развернуть/свернуть, а затем повторно их воспроизвести. (Возможно, мне удастся просто защитить или защитить только текущий лист, а не все).
Помните, я установил свою итоговую строку выше сгруппированных записей. Если сводная строка ниже сгруппированных записей (по умолчанию), то ссылку на строку смещения следует изменить на -1, например:
Доброго времени суток!
Делаю сводную с огромной структурой отчета и разворачивать мышкой на каждом уровне каждого заголовка очень неудобно.
В отчете уровней более 10-ти и значения уровней не такие юзер френдли, как в примере, поэтому переключаться на нужный уровень, это как сальто с тройным пируэтом в воздухе после каждой фильтрации в отчете.
Очень нужен макрос на кнопку развернуть/свернуть по уровням структуры. Вижу три варианта решения.
1) Выбор уровня из раскрывающего списка
2) Две кнопки (одна раскрывает на 1 уровень, другая сворачивает на 1 уровень)
3) Через счетчик аналогично 2-ому варианту. Не представляю правда как, но это было бы супер мощным решением.
Ниже загрузил файл-пример, там собственно пример таблицы и там же реализован 1 вариант, который работает очень криво.
Также в файле добавил кнопки для других вариантов, чтобы было нагляднее.
Если у вас есть возможность, помочь с решением, буду невероятно признателен.
По решению приоритетнее 2 или 3 вариант. Хотя если реализовать их сложно, то правке кода 1-ого варианта, тоже буду очень рад.
Очень надеюсь на Вашу помощь.
Ниже привожу пример структуры отчета и кода на раскрыть/свернуть через выпадающий список, который представлен в файле.
[vba]
For i = 1 To Number
Доброго времени суток!
Делаю сводную с огромной структурой отчета и разворачивать мышкой на каждом уровне каждого заголовка очень неудобно.
В отчете уровней более 10-ти и значения уровней не такие юзер френдли, как в примере, поэтому переключаться на нужный уровень, это как сальто с тройным пируэтом в воздухе после каждой фильтрации в отчете.
Очень нужен макрос на кнопку развернуть/свернуть по уровням структуры. Вижу три варианта решения.
1) Выбор уровня из раскрывающего списка
2) Две кнопки (одна раскрывает на 1 уровень, другая сворачивает на 1 уровень)
3) Через счетчик аналогично 2-ому варианту. Не представляю правда как, но это было бы супер мощным решением.
Ниже загрузил файл-пример, там собственно пример таблицы и там же реализован 1 вариант, который работает очень криво.
Также в файле добавил кнопки для других вариантов, чтобы было нагляднее.
Если у вас есть возможность, помочь с решением, буду невероятно признателен.
По решению приоритетнее 2 или 3 вариант. Хотя если реализовать их сложно, то правке кода 1-ого варианта, тоже буду очень рад.
Очень надеюсь на Вашу помощь.
Ниже привожу пример структуры отчета и кода на раскрыть/свернуть через выпадающий список, который представлен в файле.
[vba]
For i = 1 To Number
Dmitry_spb
Делаю сводную с огромной структурой отчета и разворачивать мышкой на каждом уровне каждого заголовка очень неудобно.
В отчете уровней более 10-ти и значения уровней не такие юзер френдли, как в примере, поэтому переключаться на нужный уровень, это как сальто с тройным пируэтом в воздухе после каждой фильтрации в отчете.
Очень нужен макрос на кнопку развернуть/свернуть по уровням структуры. Вижу три варианта решения.
1) Выбор уровня из раскрывающего списка
2) Две кнопки (одна раскрывает на 1 уровень, другая сворачивает на 1 уровень)
3) Через счетчик аналогично 2-ому варианту. Не представляю правда как, но это было бы супер мощным решением.
Ниже загрузил файл-пример, там собственно пример таблицы и там же реализован 1 вариант, который работает очень криво.
Также в файле добавил кнопки для других вариантов, чтобы было нагляднее.
Если у вас есть возможность, помочь с решением, буду невероятно признателен.
По решению приоритетнее 2 или 3 вариант. Хотя если реализовать их сложно, то правке кода 1-ого варианта, тоже буду очень рад.
Очень надеюсь на Вашу помощь.
Ниже привожу пример структуры отчета и кода на раскрыть/свернуть через выпадающий список, который представлен в файле.
[vba]
For i = 1 To Number
Автор - Dmitry_spb
Дата добавления - 06.08.2015 в 06:55
Правая кнопка мыши
Правая кнопка мыши
miver
Miver, исходные данные находятся в модели данных pover pivot, поэтому контекстное меню выглядит иначе.
Как Вы можете заметить в контекстном меню отсутствует уровень структуры при Развернуть/Свернуть, аналогично отсутствующей возможности группировки.
При возможности я бы с самого начала загрузил файл аналогичный оригиналу. Но это невозможно, т.к. при использовании модели данных PoverPivot, независимо от кол-ва строк и столбцов внутри, файл будет превышать допустимый размер в 100кб.
В общем, обходными путями честно не получится. В данном случае нужен именно макрос.
Привожу пример контекстного меню в моем случае.
Miver, исходные данные находятся в модели данных pover pivot, поэтому контекстное меню выглядит иначе.
Как Вы можете заметить в контекстном меню отсутствует уровень структуры при Развернуть/Свернуть, аналогично отсутствующей возможности группировки.
При возможности я бы с самого начала загрузил файл аналогичный оригиналу. Но это невозможно, т.к. при использовании модели данных PoverPivot, независимо от кол-ва строк и столбцов внутри, файл будет превышать допустимый размер в 100кб.
В общем, обходными путями честно не получится. В данном случае нужен именно макрос.
Привожу пример контекстного меню в моем случае.
Dmitry_spb
Как Вы можете заметить в контекстном меню отсутствует уровень структуры при Развернуть/Свернуть, аналогично отсутствующей возможности группировки.
При возможности я бы с самого начала загрузил файл аналогичный оригиналу. Но это невозможно, т.к. при использовании модели данных PoverPivot, независимо от кол-ва строк и столбцов внутри, файл будет превышать допустимый размер в 100кб.
В общем, обходными путями честно не получится. В данном случае нужен именно макрос.
Привожу пример контекстного меню в моем случае.
Автор - Dmitry_spb
Дата добавления - 06.08.2015 в 12:57
Скрытие и отображение столбцов в Excel - самая простая задача. И автоматизировать этот процесс даже легко. VBA Hide Column не требует огромного кода для написания. Но прежде чем мы начнем писать код для скрытия столбца в VBA, нам нужно понять несколько вещей и требований. Нам нужно знать имя столбца или последовательность, которую мы хотим скрыть, и диапазон ячейки, которая является необязательной.
Как скрыть столбцы в Excel VBA?
Мы научимся скрывать столбец в VBA с помощью нескольких примеров в Excel.
Вы можете скачать этот шаблон Excel VBA Hide Columns здесь - VBA Скрыть шаблон Excel Columns
Excel VBA Hide Column - Пример № 1
Для этого нам нужны данные в виде нескольких заполненных столбцов. Здесь у нас есть набор данных, в котором мы случайным образом взяли числа от 1 до 40 в разных 4 столбцах A, B, C и D соответственно, и все столбцы окрашены в разные цвета только для лучшего отображения на веб-странице.
Предположим, если мы хотим скрыть столбцы A и B, то сначала нам нужно выбрать оба столбца, как показано ниже.
Теперь щелкните правой кнопкой мыши и выберите « Скрыть», как показано ниже.
Выбранные столбцы будут скрыты после этого. Мы могли видеть только столбцы C и D с данными.
Теперь, если подобная работа может быть выполнена и макросом VBA. Для этого нам понадобится модуль. Перейдите в VBA и выберите модуль из списка меню «Вставка», как показано ниже.
В открывшемся модуле напишите подкатегорию VBA Hide Column, или мы можем выбрать любое другое имя в соответствии с нашим выбором, как показано ниже.
Код:
Сначала выберите столбцы, которые нам нужно скрыть. Здесь мы скрываем столбец C.
Код:
Поскольку мы хотим скрыть весь столбец C, выберите функцию FullColumn, а затем Hidden, как показано ниже.
Код:
Теперь, чтобы это произошло, мы можем выбрать ответ ИСТИНА или ЛОЖЬ. Если мы выберем Hidden как TRUE, тогда это будет скрывающая функция или если FALSE, тогда она будет работать как скрывающая функция. Поскольку мы хотим скрыть выбранные столбцы C из диапазона, мы бы выбрали здесь TRUE.
Код:
Теперь скомпилируйте код и запустите его, нажав кнопку Play, расположенную под строкой меню в VBA. Мы увидим, что столбец C теперь скрыт, теперь он показывает только столбцы A, B и D.
Если мы хотим скрыть более одного столбца вместе. Затем в диапазоне нам нужно выбрать эти столбцы, как показано ниже. Здесь мы выбираем столбцы B и C.
Код:
Теперь снова запустите код.
Здесь мы видим, столбцы B и C теперь скрыты. И только столбцы A и D видны.
Excel VBA Hide Column - Пример № 2
Вместо того, чтобы выбирать столбцы путем упоминания имени, мы также можем скрыть любой столбец по их последовательности. Во многих случаях этот процесс не является предпочтительным, поскольку мы можем легко узнать имя столбца, в котором находится столбец в алфавитном порядке. Этот процесс похож на код, который мы видели в примере № 1, где мы выбрали имя столбца как B и B: C, мы выберем порядковый номер, в котором расположен столбец. Если предположить, что мы хотим скрыть столбец A, используя код примера-1, нам просто нужно поместить порядковый номер, а не выбирать его с помощью Range.
Код:
Теперь запустите этот код, нажав клавишу F5 или нажав кнопку Play. Мы увидим, столбец А, который находится на первой позиции, теперь скрыт.
Excel VBA Hide Column - Пример № 3
Есть еще один способ скрыть столбец, используя код VBA. Это также просто, как код, который мы видели в примере № 1. Для этого мы снова будем использовать те же данные, которые мы использовали в приведенном выше примере. Запишите подкатегорию под любым именем или именем выполняемой функции.
Код:
Выберите любой диапазон столбца, который нам нужно скрыть. Здесь мы выбираем тот же столбец C.
Код:
Чтобы активировать выбор необходимого столбца, выберите функцию « Столбцы», как показано ниже.
Код:
Затем используйте скрытую команду, чтобы активировать функцию скрытия и отображения в VBA.
Код:
И выберите ИСТИНА, чтобы скрыть выбранный столбец, и ЛОЖЬ, чтобы скрыть выбранный скрытый столбец.
Код:
Столбец C, который был нашим столбцом диапазона, теперь скрыт.
Excel VBA Hide Column - Пример № 4
Есть еще один простой способ автоматизировать процесс скрытия столбцов в VBA. Для этого снова напишите подкатегорию в имени VBA Hide Columns.
Код:
Прямо выберите столбец, который нам нужно скрыть. Здесь снова мы выбираем тот же столбец C.
Код:
И выберите скрытую команду как ИСТИНА, чтобы активировать процесс скрытия. Или выберите ЛОЖЬ, чтобы активировать процесс отображения.
Код:
Посмотрим, колонка С снова скрыта.
Плюсы и минусы Excel VBA Скрыть столбец
- Процесс VBA Hide Column очень прост в реализации.
- Мы можем скрыть любой тип столбца Range, будь то в последовательности или в случайных позициях.
- Поскольку процесс VBA Hide Column прост в применении, но в ручном режиме, он также прост, как в коде VBA. Таким образом, это не вносит большой вклад в экономию. Настоятельно рекомендуется только тогда, когда мы работаем над написанием огромного кода.
То, что нужно запомнить
- Мы скрываем и отображаем столбец с помощью некоторого кода VBA. Для отображения, используйте FALSE вместо TRUE для функциональной цели.
- Порядковый номер столбца также позволяет скрыть столбец, кроме выбора имени столбца, которое упоминается в алфавитном порядке.
Рекомендуемые статьи
Это руководство к VBA Hide Columns. Здесь мы обсудим, как скрыть столбцы в Excel, используя код VBA, а также приведем практические примеры и загружаемый шаблон Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
Итог: узнаете, как добавить кнопки в ваши сводные таблицы и сводные диаграммы, чтобы быстро развернуть / свернуть целые поля с помощью макроса VBA.
Уровень мастерства: Средний
Развертывание и свертывание полей занимает много времени
Развертывание и свертывание полей в сводной таблице может быть многоэтапным процессом, который занимает много времени. Это особенно верно для пользователей/потребителей ваших файлов отчетов Excel, которые не знакомы с процессом.
Чтобы развернуть или свернуть поле «ВСЕ», мы обычно нажимаем маленькие кнопки «развернуть» (+) и свернуть (-) для каждого элемента в поле.
Для этого есть ярлыки в контекстном меню и на вкладке «Параметры / Анализ» на ленте.
Мы также можем нажать кнопки «Развернуть поле» и «Свернуть поле» на вкладке «Анализ / параметры» на ленте. Ячейка в области строк или столбцов сводной таблицы должна быть выбрана для того, чтобы эти кнопки работали.
Сочетания клавиш для операций Развернуть/Свернуть:
Свернуть все поле:
Примечание. Для работы сочетаний клавиш должна быть выбрана ячейка в области «Строки или столбцы».
Однако в сводной таблице нет специальных кнопок, чтобы развернуть / свернуть все поле.
Новые кнопки «Развернуть» в сводных диаграммах Excel 2016
В Excel 2016 была добавлена новая функция в сводные диаграммы, которая позволяет нам разворачивать или сворачивать поля в области строк. Вы увидите маленькие кнопки плюс и минус в правом нижнем углу своей сводной диаграммы.
Однако эта функция доступна ТОЛЬКО в Excel 2016 (Office 365).
Итак, я создал несколько макросов, которые будут работать в любой версии Excel, как в области строк, так и в столбцах сводной таблицы или сводной диаграммы.
Мы также можем изменить сводную диаграмму, чтобы она отображала кнопки «Развернуть / Свернуть» и добавить их над сводной таблицей. Вот анимация, которая показывает, как это сделать.
Шаги для изменения сводной диаграммы:
- Удалите кнопки поля Значения и Ось на графике.
- Удалите метки оси, заголовок и легенду.
Измените размер диаграммы, чтобы отображались только кнопки «Развернуть / Свернуть».
График будет скрыт за кнопками. Таким образом, мы просто используем сводную диаграмму для кнопок развернуть / свернуть. Важно отметить, что это будет работать только для полей в области строк сводной таблицы и только в 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. Я видел, как многие пользователи неоднократно нажимали маленькие кнопки разворачивания/свертывания для каждого элемента сводки просто потому, что не знали, что есть лучший способ. Этот макрос должен помочь им ускорить и упростить детализацию сводных таблиц или диаграмм.
Пожалуйста, оставьте комментарий ниже с вопросами или предложениями о том, как мы можем улучшить это. Спасибо! 🙂
Читайте также: