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

Развертывание, свертывание и отображение сведений в сводной таблице или сводной диаграмме

Сводную таблицу и сводную диаграмму можно развернуть и свернуть до любого уровня детализации данных; можно даже свернуть или развернуть все уровни детализации за одну операцию. Можно также развернуть или свернуть данные за следующим уровнем. Например, начиная с уровня страны или региона можно развернуть отчет до уровня городов, что приведет к развертыванию уровней областей или краев и городов. Это может сэкономить время при работе с множеством уровней детализации. Кроме того, можно развернуть или свернуть все элементы каждого поля в источнике данных OLAP.

Также можно просмотреть сведения, используемые для объединения значений в поле значений.

Сводную таблицу и сводную диаграмму можно развернуть и свернуть до любого уровня детализации данных; можно даже свернуть или развернуть все уровни детализации за одну операцию. Можно также развернуть или свернуть данные за следующим уровнем. Например, начиная с уровня страны или региона можно развернуть отчет до уровня городов, что приведет к развертыванию уровней областей или краев и городов. Это может сэкономить время при работе с множеством уровней детализации. Кроме того, можно развернуть или свернуть все элементы каждого поля в источнике данных OLAP.

Развертывание и свертывание уровней в сводной таблице

В сводной таблице выполните одно из указанных ниже действий.

Нажмите кнопку развертывания или свертывания для элемента, который нужно развернуть или свернуть.

Примечание: Если кнопки развертывания и свертывания не отображаются, см. раздел Отображение и скрытие кнопок развертывания и свертывания в сводной таблице в этой статье.

Дважды щелкните элемент, который нужно развернуть или свернуть.

Щелкните правой кнопкой мыши элемент, выберите команду Развернуть/свернуть и выполните одно из следующих действий.

Чтобы просмотреть сведения о текущем элементе, щелкните пункт Развернуть.

Чтобы скрыть сведения о текущем элементе, щелкните пункт Свернуть.

Чтобы скрыть сведения обо всех элементах в поле, щелкните пункт Свернуть все поле.

Чтобы просмотреть сведения обо всех элементах в поле, щелкните пункт Развернуть все поле.

Чтобы просмотреть данные за следующим уровнем детализации, щелкните пункт Развернуть до " ".

Чтобы скрыть данные за следующим уровнем детализации, щелкните пункт Скрыть до " ".

Развертывание и свертывание уровней в сводной диаграмме

В сводной диаграмме щелкните правой кнопкой мыши подпись категории, для которой нужно отобразить или скрыть сведения уровня, выберите команду Развернуть или свернуть и выполните одно из следующих действий.

Чтобы просмотреть сведения о текущем элементе, щелкните пункт Развернуть.

Чтобы скрыть сведения о текущем элементе, щелкните пункт Свернуть.

Чтобы скрыть сведения обо всех элементах в поле, щелкните пункт Свернуть все поле.

Чтобы просмотреть сведения обо всех элементах в поле, щелкните пункт Развернуть все поле.

Чтобы просмотреть данные за следующим уровнем детализации, щелкните пункт Развернуть до " ".

Чтобы скрыть данные за следующим уровнем детализации, щелкните пункт Скрыть до " ".

Отображение и скрытие кнопок развертывания и свертывания в сводной таблице

По умолчанию кнопки развертывания и свертывания отображаются, но их можно скрыть (например, при печати отчета). Чтобы можно было использовать эти кнопки для свертывания и развертывания уровней детализации отчета, они должны быть отображены.

В Excel 2016 и Excel 2013: на вкладке Анализ в группе Показать щелкните элемент Кнопки +/-, чтобы отобразить или скрыть кнопки свертывания и развертывания.

В Excel 2010: на вкладке Параметры в группе Показать щелкните элемент Кнопки +/-, чтобы отобразить или скрыть кнопки свертывания и развертывания.

В Excel 2007: на вкладке Параметры в группе Показать или скрыть щелкните элемент Кнопки +/-, чтобы отобразить или скрыть кнопки свертывания и развертывания.

Примечание: Кнопки развертывания и свертывания доступны только для полей, в которых есть данные для детализации.

Отображение и скрытие сведений для поля значений в отчете сводной таблицы

По умолчанию отображение сведений для поля значений в сводной таблице включено. Чтобы защитить эти данные от просмотра другими пользователями, их отображение можно отключить.

Отображение сведений поля значений

В сводной таблице выполните одно из указанных ниже действий.

Щелкните правой кнопкой мыши поле в области значений сводной таблицы и выберите команду Показать детали.

Дважды щелкните поле в области значений сводной таблицы.

Данные, на которых основано поле значений, будут помещены на новый лист.

Скрытие сведений поля значений

Щелкните правой кнопкой мыши ярлычок листа с данными поля значений и выберите команду Скрыть или Удалить.

Отключение и включение параметра отображения сведений поля значений

Щелкните в любом месте сводной таблицы.

На вкладке Параметры или Анализ (в зависимости от используемой версии Excel) ленты в группе Сводная таблица нажмите кнопку Параметры.

В диалоговом окне Параметры сводной таблицы откройте вкладку Данные.

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

Примечание: Этот параметр недоступен для источника данных OLAP.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

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

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

1. Развертывание и свертывание полей занимает много времени
2. Новые кнопки «Развернуть» в сводных диаграммах Excel 2016
3. Развернуть/Свернуть — макросы
4. Назначить макросы фигурам или кнопкам
5. Макро-код VBA
6. Скачать файл
7. Что можно улучшить?

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

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

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

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

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

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

Развернуть все поле:

Свернуть все поле:

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

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

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

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

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

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

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

Шаги для изменения сводной диаграммы:

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

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

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

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

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

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

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

Запуск макроса «Collapse_Entire_RowField» приведет к сворачиванию элементов Year обратно.

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

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

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

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

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

Макро-код VBA

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

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

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

Скачать файл

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

Что можно улучшить?

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

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