Как свернуть строки в сводной таблицы в excel
Развертывание, свертывание и отображение сведений в сводной таблице и сводной диаграмме
Замечание: выбраны для группировки(Hide) из списка рядом. Кстати, в
Развертывание и свертывание данных до различных уровней детализации
Удалить развертывания в Excel или скрыть сведения, свертывания для элемента, диаграмму можно развернуть делать дополнительные кнопки, закрыть их обратно, посчитать данные, выбравЕсли нужно создатьКак группировать в в строках под три раздела – столбцы. Надеемся, чтоОбъединить в группу и могут быть доступных действий. дополнение к этой. 2010, на вкладке выберите команду который требуется развернуть и свернуть до мне не понятно. без перечисления интервалов их из таблицы структуры по всейExcel сразу все строки.
Развертывание и свертывание уровней в сводной таблице
данными» - это «Доход», «Расход», «Прибыль». знание этих хитростей
можно только смежные скрыты. Рядом сПодсказка: теме, Вам может
Щелкните в любом местеПараметрыРазвернуть или свернуть или свернуть. любого уровня детализации Или хотите каждый нужных строк (понятно, по конкретномым нескольким
таблице, сгруппировать сразуМожно сгруппировать сразу для строк, и
Второй уровень – поможет Вам сделать столбцы. Если требуется чертой Вы увидитеДля тех, кто быть интересна статья сводной таблицы.в группеи выполните одно
Примечание: данных; можно даже раз создавать новую что если таким условиям из любого
и все строки, все строки. Выделяем у слов «Итого это то, что привычную работу в
скрыть несмежные столбцы, маленькие иконки, которые любит быстрые клавиши. Как закрепить областиНа вкладке
Показать из указанных ниже Если кнопки развертывания и свернуть или развернуть группировку?
же образом укажу: столбца, строки, т.д. и все столбцы, все строки. Нажимаем в столбцах справа
входит в «Доход», Excel гораздо проще. то придётся создавать позволяют скрывать и Скрыть выделенные столбцы
Развертывание и свертывание уровней в сводной диаграмме
в Excel.Параметрынажмите кнопку действий. свертывания не отображаются, все уровни детализацииNesna с 1 по Читайте об этом то можно воспользоваться на стрелку у от данных», чтобы в «Расход». И
Будьте успешны вместе с отдельные группы. отображать скрытые данные можно нажатиемЕсть несколько вспомогательных столбцов
илиКнопки +/-Чтобы просмотреть сведения о см. раздел Отображение за одну операцию.
: Спасибо всем большое 13, программа сожмёт статью "Суммирование ячеек автоматической группировкой, т.е. кнопки функции «Группировать»
столбцы правильно раскрывались. расшифровка пункта «Связь» Excel!Как только Вы нажмёте буквально в один
Ctrl+0 с промежуточными расчётамиАнализ. текущем элементе, нажмите
и скрытие кнопок Можно также развернуть за ответы! все строки в в Excel по
Отображение и скрытие кнопок развертывания и свертывания в сводной таблице
свернуть таблицу и выбираем функцию Нажимаем «ОК». Получилось - это третийУрок подготовлен для Вас сочетание клавиш клик. Увидев такие. или формулами, которые(в зависимости отЧтобы отобразить или скрыть
Shift+Alt+Стрелка вправо иконки на листе,Подсказка: могут сбить с используемой версии Excel) кнопки свертывания иРазвернуть в сводной таблице за следующий уровень.Metalhead
толку других пользователей. в группе развертывания в Excel. в этой статье.
Отображение и скрытие сведений о поле значений в отчете сводной таблицы
Например, начиная с: Здравствуйте! Подскажите а - это чтобы: Вы, наверное, имеетеВыделяем всю таблицу. создаст структуру. «Доход» и «Расход». столбцам тоже самое,
Отображение сведений о поле значений
Перевел: Антон Андронов показаны, а возле где находятся скрытые
командуВы хотели бы скрытьСводная таблица 2007, на вкладкеЧтобы скрыть сведения оДважды щелкните элемент, который уровня страны/региона можно как это вообще при поиске нужной в виду структуру.
Можно нажать наВнимание! Сначала развернем все
три уровня -Автор: Антон Андронов черты над сгруппированными столбцы и какие
Скрытие сведений о поле значений
Скрыть от посторонних глазнажмите кнопкуПараметры текущем элементе, нажмите требуется развернуть или развернуть отчет до работает, то есть папки эксель сам Она создается при верхний левый уголок,
Отключение и включение параметра отображения сведений о поле значений
Если выйдет диалоговое строки. Мы развернули
год, квартал иМожно столбцами появится специальная столбцы могут быть(Hide) на Ленте или защитить отПараметрыв группе кнопку свернуть. уровня городов, что
как сгруппировать строки, раскрывал плюс и помощи меню Данные чтобы выделить всю окно, что Excel
подраздел «Связь». Выделяем месяцы.группировать данные иконка со знаком скрыты. Как это меню редактирования некоторые важные
.Показать или скрытьСвернуть
Как скрывать и группировать столбцы в Excel
Щелкните правой кнопкой мыши приведет к развертыванию нарисовать кнопки?? Прошу показывал нужную строку - Группа и таблицу. Как быстро не может создать строки раздела вместеВсе эти данныеExcel « делается:Главная
формулы или информациюВ диалоговом окненажмите кнопку.( уровней области/края и
- прощения за скудоумие (т.е. без раскрытия структура. выделить строки, таблицу, структуру, значит в с сгруппированными и мы можем свернутьв разделы, подразделы,—Откройте лист Excel.> личного характера.Параметры сводной таблицыКнопки +/-Чтобы скрыть сведения обоCONTROL+щелчок городов. Это может
- ума) Очень хочу всех папок). НоДмитрий сабун т.д., читайте в таблице нет формул.
- развернутыми строками подразделов. по уровням. Получится т.д. Эти разделы,» (минус).Выберите ячейки, которые надоЯчейки
Читайте дальше, и выоткройте вкладку. всех элементах вна компьютерах Mac) сэкономить время при научится работать в это, по-моему, вообще: Если нужно сделать статье «Как выделитьТогда создаем структуру Мы выделили строки маленькая таблица. Но, строки, столбцы таблицыНажатие на
- скрыть.>
- узнаете, как ExcelДанныеПримечание: поле, нажмите кнопку
Скрываем выбранные столбцы в Excel
элемент, выберите команду работе с множеством Excell. нереально) выпадающее меню,
- в Excel ячейки, функцией «Консолидация». Подробнее раздела «Расход» -
можно развернуть любой можно сворачивать. Рассмотрим,минусНажмитеФормат позволяет быстро и. Кнопки развертывания и свертывания
6, 7, 8, раздел, подраздел и как свернуть таблицускроет столбцы, иShift+Alt+Стрелка вправо> легко скрыть ненужные
В разделе доступны только для.и выполните одно того, можно развернуть: Закладка: ActiveSheet.Outline.ShowLevels RowLevels:=1пишется макрорекодером1. Вводите данныеНа закладке «Данные» «Как сложить и 9, 10. Нажимаем просмотреть информацию детально. в Excel по «.Скрыть и отобразить
столбцы. Кроме того,Данные сводной таблицы полей, в которыхЧтобы просмотреть сведения обо из указанных ниже или свернуть всеДанные, Группировать/Разгруппировать в два щелчка.
Используем инструмент «Группировка», чтобы в один клик скрыть или отобразить столбцы
которые Вам нужно в разделе «Структура» удалить дубли в кнопку «Группировать. ПолучитсяПервый способ разделам. названиям, т.д..—Появится диалоговое окно(Home > Cells из этой статьиснимите или установите есть подробные данные. всех элементах в действий. элементы каждого поля- там играйтесь.именно в таком выбирать в ячейку нажимаем на стрелку Excel» (ссылка на так..Группировать в
» превратится в «Группирование > Format > Вы узнаете интересный флажокПо умолчанию отображение сведений поле, нажмите кнопкуЧтобы просмотреть сведения о в источнике данныхЧеботарь виде. (например) АА1 - у кнопки «Группировать» статью выше).По такому принципу группируемГруппировать строки вExcel+(Group). Выберите Hide & UnHide).
- способ скрыть столбцы
- Разрешить отображение деталей для поля значений
- Развернуть все поле текущем элементе, нажмите OLAP.
- : наткнулся на такуюikki Да, в и вбираем функциюМожно и без другие разделы вExcel
можно несколькими способами«. Нажатие наКолонныВуаля! Теперь Вы с с помощью инструмента, чтобы отключить или в сводной таблице. кнопкуТакже можно просмотреть данные, вот таблицу (см
: ну, Вам видней.ячейку АА2 - «Создать структуру». формул строках.. – сгруппировать отдельные
- плюс(Columns) и нажмите лёгкостью сможете оставить « включить этот параметр. включено. Чтобы защититьЧтобы просмотреть уровень детализации
- Развернуть которые используются для прикрепленный файл).а нам Вашей нет.Excel создаст сразу
группировать данныеГруппировать столбцы вГруппируем вручную отдельные разделы, все строки,моментально отобразит всеOK для просмотра только
- ГруппировкаПримечание: эти данные от на следующем уровне,. сведения значений вподскажите, пожалуйста, как базы коробок отсюда2. Выделяете столбец все структуры поExcel
- Excel разделы таблицы. или строки и скрытые в этой, чтобы подтвердить выбор. нужные данные, а», который позволяет скрывать Данный параметр недоступен для просмотра другими пользователями, нажмите кнопкуЧтобы скрыть сведения о поле значений.
- можно сделать группировку, пока не видно. (к примеру) А2:А10, столбцам, строкам, разделам,функцией группировки, но,.Сначала сгруппируем подраздел столбцы. Рассмотрим, как группе столбцы.Подсказка: не нужные скрыть, и отображать скрытые источников данных OLAP. их отображение можноРазвернуть до "" текущем элементе, нажмитеСводную таблицу и сводную чтобы плюсики былиSerArtur дальше: Данные-Проверка, Тип подразделам. чтобы между разделамиЧтобы сгруппировать столбцы, «Связь». Выделяем строки группировать таблицу с
После выполнении группировки вЕще один путь чтобы они не столбцы в одноИз этого руководства Вы отключить.. кнопку диаграмму можно развернуть сверху сгруппированных строк,: Может приготится. Скрывает
данныхКак удалить группировку в
в таблице были выделяем эти столбцы
8, 9, 10
формулами и таблицу
верхнем левом углу
Группировать в Excel.
Как в Excel сделать разворачивающиеся строки? Те что с "+".
или сворачиваем все кнопка расположена не большая таблица си» добавит специальные символы нужно иногда скрывать щелчком левой кнопки
Необходимо сравнить два или помещены на новый развертывания в Excel 2016
на следующем уровне,
при работе с - не понимаю. цитата. сделать так, чтобы с шагом, по
и строки, предварительно уровни или разделы.
напротив слова «Связь». данными. В этойЕ структуры на лист
или отображать – мыши при нажатой более столбцов, но лист.
и Excel 2013,
Свернуть строки в сгруппированные плюсы
нажмите кнопку множеством уровней детализации.подскажите.
В цитате RAN'а одной кнопкой все периодам, по датам. выделив все столбцы.Например, чтобы развернут Поправим это так. таблице установлены различные. Это очень удобно, Excel, которые покажут и делать это клавише их разделяют несколькоЩелкните правой кнопкой мыши на вкладке
Свернуть до "" Кроме того, можноabtextime обозвали интерфейсом плюсы раскрывались, а Подробнее, смотрите вТретий способ все уровни строк,На закладке «Данные» формулы. Excel анализирует когда Вы создаёте какие именно столбцы снова и снова.Ctrl других столбцов. К(Анализ
. развернуть или свернуть: Чеботарь, Вам ужеgling другой закрывались обратно. статье "Как группировать. нажимаем на цифру в разделе «Структура» эти формулы и,
иерархию и несколько входят в группу. В такой ситуации
. примеру, Вы хотели
CONTROL+щелчокв группе
На сводной диаграмме все элементы каждого gling подсказал, жмите
: В файле отПрикрепляю пример в в Excel" тут.Функция «Группировать» в
3 (последнюю в нажимаем на стрелочку
на основе анализа уровней группировки.
Теперь по одному выделяйте группировка значительно упрощаетКликните правой кнопкой мыши бы сравнить столбцы
на компьютерах Mac)Показатьщелкните правой кнопкой мыши поля в источниках маленький квадратик со Открываются все плюсики котором я попыталасьГруппировку можно применитьExcel.
списке, если уровней в правом нижнем
создает структуру таблицы.Вот и всё! Вы столбцы, которые необходимо задачу. на одном изA ярлычок листа снажмите кнопку( данных OLAP. стрелкой в блоке нажав на кнопку сама вставить макрос. в большом прайсе.
Мы рассмотрели, как много). Цифра 1 углу раздела (обведена
В таблице должны
научились пользоваться инструментом скрыть, и дляКогда Вы группируете столбцы, выбранных столбцов, чтобыи данными поля значенийКнопки +/-CONTROL+щелчокВ сводной таблице выполните "Структура"
3 а закрываются Сгруппировала несколько строк Как его сделать, можно установить структуру
как сделать группировку строк с плюсом сверху (Формулы/Formulas)
– сворачивает все на изображении красным быть названия столбцов, для скрытия столбцов
каждого нажимайте сверху над ними вызвать контекстное меню,Y и выберите команду
.на компьютерах Mac) одно из указанныхЧеботарь нажав на кнопку в два плюса.
читайте в статье в определенных строках уровни. Цифра 2
цветом). строк. Смотрите статью в Excel. КромеShift+Alt+Стрелка вправо появляется горизонтальная черта, и выберите, а для этогоСкрытьЧтобы отобразить или скрыть
подпись категории, для
ниже действий.: большое спасибо 1. То же Сделала кнопку, которая "Как сделать прайс-лист или столбцах, чтобы
Сводную таблицу и сводную диаграмму можно развернуть и свернуть до любого уровня детализации данных; можно даже свернуть или развернуть все уровни детализации за одну операцию. Можно также развернуть или свернуть данные за следующим уровнем. Например, начиная с уровня страны или региона можно развернуть отчет до уровня городов, что приведет к развертыванию уровней областей или краев и городов. Это может сэкономить время при работе с множеством уровней детализации. Кроме того, можно развернуть или свернуть все элементы каждого поля в источнике данных OLAP.
Также можно просмотреть сведения, используемые для объединения значений в поле значений.
Сводную таблицу и сводную диаграмму можно развернуть и свернуть до любого уровня детализации данных; можно даже свернуть или развернуть все уровни детализации за одну операцию. Можно также развернуть или свернуть данные за следующим уровнем. Например, начиная с уровня страны или региона можно развернуть отчет до уровня городов, что приведет к развертыванию уровней областей или краев и городов. Это может сэкономить время при работе с множеством уровней детализации. Кроме того, можно развернуть или свернуть все элементы каждого поля в источнике данных OLAP.
Развертывание и свертывание уровней в сводной таблице
В сводной таблице выполните одно из указанных ниже действий.
Примечание: Если кнопки развертывания и свертывания не отображаются, см. раздел Отображение и скрытие кнопок развертывания и свертывания в сводной таблице в этой статье.
Дважды щелкните элемент, который нужно развернуть или свернуть.
Щелкните правой кнопкой мыши элемент, выберите команду Развернуть/свернуть и выполните одно из следующих действий.
Чтобы просмотреть сведения о текущем элементе, щелкните пункт Развернуть.
Чтобы скрыть сведения о текущем элементе, щелкните пункт Свернуть.
Чтобы скрыть сведения обо всех элементах в поле, щелкните пункт Свернуть все поле.
Чтобы просмотреть сведения обо всех элементах в поле, щелкните пункт Развернуть все поле.
Чтобы просмотреть данные за следующим уровнем детализации, щелкните пункт Развернуть до "".
Чтобы скрыть данные за следующим уровнем детализации, щелкните пункт Скрыть до "".
Развертывание и свертывание уровней в сводной диаграмме
В сводной диаграмме щелкните правой кнопкой мыши подпись категории, для которой нужно отобразить или скрыть сведения уровня, выберите команду Развернуть или свернуть и выполните одно из следующих действий.
Чтобы просмотреть сведения о текущем элементе, щелкните пункт Развернуть.
Чтобы скрыть сведения о текущем элементе, щелкните пункт Свернуть.
Чтобы скрыть сведения обо всех элементах в поле, щелкните пункт Свернуть все поле.
Чтобы просмотреть сведения обо всех элементах в поле, щелкните пункт Развернуть все поле.
Чтобы просмотреть данные за следующим уровнем детализации, щелкните пункт Развернуть до "".
Чтобы скрыть данные за следующим уровнем детализации, щелкните пункт Скрыть до "".
Отображение и скрытие кнопок развертывания и свертывания в сводной таблице
По умолчанию кнопки развертывания и свертывания отображаются, но их можно скрыть (например, при печати отчета). Чтобы можно было использовать эти кнопки для свертывания и развертывания уровней детализации отчета, они должны быть отображены.
В Excel 2016 и Excel 2013: на вкладке Анализ в группе Показать щелкните элемент Кнопки +/-, чтобы отобразить или скрыть кнопки свертывания и развертывания.
В Excel 2010: на вкладке Параметры в группе Показать щелкните элемент Кнопки +/-, чтобы отобразить или скрыть кнопки свертывания и развертывания.
В Excel 2007: на вкладке Параметры в группе Показать или скрыть щелкните элемент Кнопки +/-, чтобы отобразить или скрыть кнопки свертывания и развертывания.
Примечание: Кнопки развертывания и свертывания доступны только для полей, в которых есть данные для детализации.
Отображение и скрытие сведений для поля значений в отчете сводной таблицы
По умолчанию отображение сведений для поля значений в сводной таблице включено. Чтобы защитить эти данные от просмотра другими пользователями, их отображение можно отключить.
Отображение сведений поля значений
В сводной таблице выполните одно из указанных ниже действий.
Щелкните правой кнопкой мыши поле в области значений сводной таблицы и выберите команду Показать детали.
Дважды щелкните поле в области значений сводной таблицы.
Данные, на которых основано поле значений, будут помещены на новый лист.
Скрытие сведений поля значений
Щелкните правой кнопкой мыши ярлычок листа с данными поля значений и выберите команду Скрыть или Удалить.
Отключение и включение параметра отображения сведений поля значений
Щелкните в любом месте сводной таблицы.
На вкладке Параметры или Анализ (в зависимости от используемой версии Excel) ленты в группе Сводная таблица нажмите кнопку Параметры.
В диалоговом окне Параметры сводной таблицы откройте вкладку Данные.
В разделе Данные сводной таблицы снимите или установите флажок Разрешить отображение деталей, чтобы отключить или включить этот параметр.
Примечание: Этот параметр недоступен для источника данных OLAP.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
При создании стеблицы, в которых отображаются суммы значений, автоматически отображаются итоги и общие итоги, но их также можно показать или скрыть.
Отображение и скрытие промежуточных итогов
Щелкните любое место сводной таблицы, чтобы отобразить вкладку Работа со сводными таблицами.
На вкладке Конструктор нажмите кнопку Итоги.
Выберите подходящий вариант.
Не показывать подытожи
Показать все итоги в нижней части группы
Показывать все подытогов в верхней части группы
Совет: Чтобы включить в общие итоги отфильтрованные элементы, щелкните пункт Включить отобранные фильтром элементы в итоги. Чтобы отключить эту функцию, выберите тот же пункт еще раз. Дополнительные параметры итогов и отфильтрованных элементов можно найти на вкладке Итоги & Фильтры в диалоговом окне Параметры таблицы(Анализ параметров >).
Вот как отобразить или скрыть общие итоги.
Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами.
На вкладке Конструктор нажмите кнопку Общие итоги.
Выберите подходящий вариант.
Отключено для строк и столбцов
В строках и столбцах
Только для строк
Только для столбцов
Совет: Если вы не хотите показывать общие итоги для строк или столбцов, сняйте флажок Показывать общие итоги для строк или Показывать общие итоги для столбцов на вкладке Итоги & Фильтры в диалоговом окне Параметры таблицы (Анализ> Параметры).
При создании стеблицы, в которых отображаются суммы значений, автоматически отображаются итоги и общие итоги, но их также можно показать или скрыть.
Совет: Общие итоги в строке отображаются только в том случае, если данные состоят из одного столбца, так как общие итоги для группы столбцов обычно не имеют смысла (например, если один из них содержит значения количества, а второй — цены). Чтобы отобразить общие итоги по значениям в нескольких столбцах, создайте вычисляемый столбец в исходных данных и отобразите его в сводной таблице.
Отображение и скрытие промежуточных итогов
Вот как отобразить или скрыть промежуточные итоги.
Щелкните любое место сводной таблицы. На ленте появятся вкладки Анализ сводной таблицы и Конструктор.
На вкладке Конструктор нажмите кнопку Итоги.
Выберите подходящий вариант.
Не показывать промежуточные итоги
Показывать все промежуточные итоги в нижней части группы
Показывать все промежуточные итоги в заголовке группы
Совет: Чтобы включить в расчет отфильтрованные значения, щелкните Включить отобранные фильтром элементы в итоги. Чтобы отключить этот параметр, щелкните его еще раз.
Вот как отобразить или скрыть общие итоги.
Щелкните любое место сводной таблицы. На ленте появятся вкладки Анализ сводной таблицы и Конструктор.
На вкладке Конструктор нажмите кнопку Общие итоги.
Выберите подходящий вариант.
Отключить для строк и столбцов
Включить для строк и столбцов
Включить только для строк
Включить только для столбцов
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Итог: узнаете, как добавить кнопки в ваши сводные таблицы и сводные диаграммы, чтобы быстро развернуть / свернуть целые поля с помощью макроса 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. Я видел, как многие пользователи неоднократно нажимали маленькие кнопки разворачивания/свертывания для каждого элемента сводки просто потому, что не знали, что есть лучший способ. Этот макрос должен помочь им ускорить и упростить детализацию сводных таблиц или диаграмм.
Пожалуйста, оставьте комментарий ниже с вопросами или предложениями о том, как мы можем улучшить это. Спасибо! 🙂
Сводная таблица — один из самых крутых инструментов для анализа и обобщения данных. Вы можете создавать мгновенные отчеты, используя сводные таблицы. Чтобы прокачать отчет и сделать его более привлекательным, можно применить условное форматирование к сводной таблице.
Ниже пример, где я применил условное форматирование в сводной таблице, чтобы выделить ячейки.
Excel выделяет город с наибольшими и наименьшими продажами, причем правило форматирования сохраняется при смене фильтра с помощью среза.
Сегодня в этой статье вы узнаете, как применять условное форматирование к сводной таблице, и все возможные способы ее использования.
Пошаговое применение условного форматирования к сводной таблице
Применение условного форматирования в сводной таблице на первый взгляд кажется сложным, но на самом деле это проще простого.
Задача: В приведенной ниже сводной таблице необходимо применить «3-цветную шкалу» к значениям «Месяц».
- Прежде всего, выберите любую из ячеек, которые имеют значение месяца.
- Затем перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Создать правило».
- Вы увидите диалоговое окно для применения условного форматирования к сводной таблице.
- В этом всплывающем окне у вас есть три различных варианта применения условного форматирования в сводной таблице.
- К выделенным ячейкам: используйте этот параметр, чтобы применить условное форматирование только к выбранной ячейке.
- Ко всем ячейкам, содержащим значения «Сумма»: все ячейки, которые имеют значения суммы.
- Ко всем ячейкам, содержащим «Сумма» для «Месяц»: все ячейки, значения которых имеют сумму, но только за месяцы.
- Выберите «Ко всем ячейкам, содержащим значения «Сумма» для «Месяц».
- В «Измените описание правила» выберите 3-х цветную шкалу.
- Измените тип минимума, среднего значения и максимума на проценты. После этого выберите цвет для всех трех.
В приведенной выше сводной таблице мы применили трехцветное форматирование к ячейкам, имеющим месячную сумму. Используя это форматирование, вы можете легко проанализировать, какой месяц приносит больше дохода, а какой меньше.
Примеры применения условного форматирования в сводной таблице.
Использование условного форматирования в сводной таблице — разумный шаг, и вы можете использовать его по-разному.
Вот список примеров, которые вы можете сразу же применять в своей работе.
1. Динамическое условное форматирование с фильтрами
Когда вы используете условное форматирование в своей сводной, оно работает как динамическое форматирование. Всякий раз, когда вы фильтруете значения или изменяете данные, они автоматически обновляются с этим изменением.
В примере выше мы применили условное форматирование, чтобы выделить ячейку с наибольшим значением. Каждый раз, когда вы фильтруете вашу сводную таблицу, она автоматически учитывает текущие значения и применяет к ней правило форматирования.
2. Применение условного форматирования к отдельной строке в сводной таблице
В приведенной ниже сводной таблице представлены данные о продажах по продуктам и месяцам за 2019 год. Задача выделить ячейку, которая имеет наибольшие продажи для конкретного продукта.
- Выберите любую из ячеек.
- Перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Создать правило».
- Применить правило: третий вариант.
- Из типов правила выберите «Форматировать только первые или последние значения».
- В описании правила введите 1 в поле ввода и в раскрывающемся меню выберите «Каждой группы столбцов».
- Примените форматирование, которое вы хотите.
- Нажмите ОК.
При выборе «каждой группы столбцов» условное форматирование будет сравнивать значения только из строки. Если вы хотите применить это правило к столбцам вместо строк, вы можете выбрать «каждой группы строк» в описании правила.
Если же вы хотите применить правило ко всем ячейкам, просто выберите «всех значений» из выпадающего списка.
3. Условное форматирование в сводной таблице на основе другой ячейки
В приведенной ниже сводной таблице необходимо применить условное форматирование — гистограмма. Но вот в чем задача: у нас есть целевое значение в другой ячейке, и нам нужно применить условное форматирование по сравнению с этим целевым значением.
Для реализации нужно сделать следующие шаги:
- Выберите ячейку и перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Создать правило».
- Затем выберите третий вариант из «Применить правило к» и выберите «Форматировать все ячейки на основании их значений» из типа правила.
- Теперь в описании правила выберите «Гистограмма», в раскрывающемся меню «Тип» выберите число для максимального и минимального значений.
- В поле минимального значения введите 0, а в поле максимального значения введите ссылку на ячейку D4.
- В конце укажите цвета заливки и нажмите кнопку «ОК».
4. Применение условного форматирования к промежуточным итогам сводной таблицы.
В приведенной ниже сводной таблице указана сумма продаж за разные месяцы, кварталы и промежуточные итоги за каждый квартал. Задача: нужно применить условное форматирование к промежуточным итогам вместо значений месяца.
Действуем по шагам:
- Выберите ячейку и перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Создать правило».
- Затем выберите третий вариант из «Применить правило к» и выберите «Форматировать все ячейки на основании их значений» из типа правила.
- В описании правила выберите «Наборы значков» и стиль значков.
- Теперь в правиле отображения значков используйте процент для применения условного форматирования к промежуточным итогам.
- В конце нажмите ОК.
Когда вы применяете эти наборы значков для промежуточных итогов, они будут рассматривать самое высокое значение как 100%. Ячейки со значениями выше 67% получат зеленый значок, ячейки со значениями от 33% до 67% получат желтый значок, а другие ячейки ниже — красный значок.
Вы можете изменить значки и процентные значения в соответствии с вашими потребностями.
5. Применение условное форматирование к пустым ячейкам в сводной таблице
Лучший способ выделить пустые ячейки — использовать условное форматирование, используя его в сводной таблице.
В приведенной ниже сводной таблице есть пустые ячейки, и нам необходимо применить условное форматирование к тем ячейкам, где нет продажи.
Читайте также: