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

Группировка и отмена группировки данных в сводной таблице

​Смотрите также​ Продолжайте выбирать, пока​ Excel!​—​Колонны​ с таблицами, часто​Откройте лист Excel и​ других столбцов. К​ ярлычок любого листа.​Чтобы выполнить одинаковые задачи​ вас уделить пару​ CREATE SESSION CUBE,​ «Город» содержит четыре​

​ подпись наподобие​​ удалено из указанной​ Группировка по времени доступна​должно быть больше​Группировка позволяет выделить для​ все необходимые листы​Урок подготовлен для Вас​» (минус).​(Columns) и нажмите​ используют возможность скрыть​ выделите столбцы, которые​ примеру, Вы хотели​Выберите команду​

Группировка полей

1. ​ на определенных листах,​ секунд и сообщить,​ группировка элементов невозможна.​ города: Москва, Санкт-Петербург,​Группа1​ области, а автоматического​​ только в Excel 2016.​​ или позже значения​

2. ​ анализа определенное подмножество​​ не будут включены​​ командой сайта office-guru.ru​​Нажатие на​​OK​ и отобразить столбцы.​ необходимо скрыть.​

   

   • ​ бы сравнить столбцы​Выделить все листы​ сделайте следующее.​

   • ​ помогла ли она​В уже сгруппированное поле​ Ярославль и Мурманск.​. Чтобы задать для​

     ​ сворачивания данных не​​В области задач​​ в поле​ данных сводной таблицы.​ в группу.​​Источник: https://www.ablebits.com/office-addins-blog/2014/08/06/excel-hide-columns/​​минус​

3. ​, чтобы подтвердить выбор.​​ Существует ещё один​​Подсказка:​A​

   • ​.​Удерживая клавишу CTRL, щелкните​ вам, с помощью​

   • ​ невозможно добавить вычисляемый​ Можно сгруппировать их,​ группы более осмысленное​ произойдет, и вы​

     ​Поля сводной таблицы​по​ Например, можно сгруппировать​​Отпустите клавишу​​Перевел: Антон Андронов​​скроет столбцы, и​​Подсказка:​ инструмент, который отлично​Чтобы выделить несмежные​и​​Примечание:​​ ярлычки листов, которые​​ кнопок внизу страницы.​​ элемент. Нужно сначала​​ поместив Москву и​​ имя, щелкните его,​​ сможете увидеть это​​перетащите поле даты​

​.​​ значения даты или​Ctrl​Автор: Антон Андронов​ «​​Еще один путь​​ справляется с этой​​ столбцы, отметьте их​​Y​​ Если сгруппировать все листы,​​ хотите объединить в​ Для удобства также​ разгруппировать данные, добавить​ Ярославль в группу​ выберите​

Автоматическая группировка столбцов со значениями даты и времени (группировка по времени)

​ поле, когда решите​​ из области «Поля»​В поле​

• ​ времени (поля даты​​. Листы будут сгруппированы.​​Группировка листов в Excel​—​ к этому же​ задачей, – Вы​ щелчком левой кнопки​, а для этого​

  ​ а затем открыть​ группу.​ приводим ссылку на​

  ​ вычисляемый элемент, а​ с именем «Центр»,​Параметры поля​

  ​ их свернуть.​ в область «Строки»​с шагом​ и времени в​Если листы, которые требуется​ значительно упрощает ввод​» превратится в «​ диалоговому окну:​ оцените его по​ мыши при нажатой​ удобнее расположить их​

  ​ любой из них,​Совет:​ оригинал (на английском​ затем повторно сгруппировать​ а Санкт-Петербург и​, а затем введите​При перетаскивании поля даты,​

  ​ или «Столбцы», чтобы​введите указанные ниже​ сводной таблице) в​

  ​ сгруппировать, находятся рядом​ однотипных данных. Группировка​+​

​Данные​​ достоинству! Этот инструмент​

• ​ клавише​ рядом. Кстати, в​ группировка будет отменена.​ Если вы хотите сгруппировать​ языке) .​ их.​ Мурманск в группу​ новое значение в​ содержащего более тысячи​ автоматически сгруппировать значения​ значения.​ большом и неудобном​ друг с другом,​ позволяет работать одновременно​«. Нажатие на​

• ​>​ – «​Ctrl​ дополнение к этой​ Однако если выделены​ листы, расположенные подряд,​You can quickly perform​При наличии одного или​ «Северо-запад». Новое поле​ поле​ строк, из списка​ по времени.​Для числовых полей укажите​ списке по кварталам​

Группировка выделенных элементов

​ то можете воспользоваться​ с несколькими листами,​

1. ​плюс​Группировать​Группировка​.​ теме, Вам может​

2. ​ не все листы​ щелкните ярлычок первого​ tasks on multiple​​ нескольких сгруппированных элементов​​ «Город2» появляется в​

​Пользовательское имя​ полей в область​Список полей сводной​ число, обозначающее интервал​ и месяцам, как​ клавишей​​ например, применять форматирование​​моментально отобразит все​​>​​». Бывает так, что​Кликните правой кнопкой мыши​ быть интересна статья​ книги, между ними​ листа диапазона, а​ worksheets at the​ использовать команду​ области полей и​.​​ строк или столбцов​​ таблицы до группировки​ для каждой группы.​ показано в примере​Shift​​ или вводить необходимую​​ скрытые в этой​Группировать​ на одном листе​​ на одном из​​ Как закрепить области​

​ можно свободно переключаться,​​ затем, удерживая клавишу​

• ​ same time by​Преобразование в формулы​ отображается в области​Советы:​ в сводной таблице​

• ​ по времени​Для полей даты и​ ниже.​вместо​ информацию. В данном​ группе столбцы.​(Data > Group​ есть несколько несмежных​ выбранных столбцов, чтобы​ в Excel.​

Разгруппировка сгруппированных данных

​ не нарушая группировки.​ SHIFT — последнего.​ grouping worksheets together.​(​ «Строки» списка полей.​​ ​​ модели данных поле​

​Список полей сводной таблицы​ времени выберите один​Примечание:​Ctrl​ уроке мы научимся​После выполнении группировки в​ > Group).​ групп столбцов, которые​ вызвать контекстное меню,​Есть несколько вспомогательных столбцов​К началу страницы​Теперь добавим одну формулу​ If you group​Работа со сводными таблицами​Как показано здесь, поле​Чтобы получить более компактную​ даты удаляется из​ до группировки по​ или несколько временных​ в Excel 2016 появилась новая​. Для этого выделите​ группировать листы в​ верхнем левом углу​Подсказка:​ нужно иногда скрывать​ и выберите​ с промежуточными расчётами​После внесения всех нужных​

​ в ячейку B7​ a set of​ >​ «Город2» создано на​ сводную таблицу, можно​ списка полей, чтобы​

​ времени​ интервалов для группировки.​ функция — группировка по​ первый ярлычок, затем,​

​ Excel, разгруппировывать их,​​ появляются маленькие цифры.​Чтобы отменить группировку​ или отображать –​Скрыть​ или формулами, которые​ изменений вы можете​ на листах «Париж»​ worksheets, any changes​Анализ​ основе поля «Город»​ создать группы для​ приложение Excel могло​Excel автоматически добавляет вычисляемые​Можно выбрать дополнительные промежутки​ времени. Приложение автоматически​ удерживая клавишу​ а также применять​ Их можно использовать​ выберите диапазон, содержащий​

Сведения о группировке данных в сводной таблице

​ и делать это​(Hide) из списка​ могут сбить с​ отменить группировку листов.​

• ​ и «Лондон». Если​ you make on​нажмите кнопку​ и помещено в​

• ​ всех остальных несгруппированных​ отобразить сводную таблицу,​ столбцы в сводную​ времени для группировки,​ обнаруживает связи между​Shift​ преимущества группировки на​

• ​ для того, чтобы​ сгруппированные столбцы, и​ снова и снова.​​ доступных действий.​​ толку других пользователей.​​Щелкните ярлычок любого листа​​ два листа сгруппированы,​​ one worksheet are​​Средства OLAP​​ область «Строки» для​​ элементов в поле.​ для которой превышено​ таблицу, используемую для​

• ​ например​ значениями соответствующих полей​, выделите последний ярлычок​

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

См. также

​ made in all​) невозможно. Перед ее​

​ группировки выбранных городов.​В полях, упорядоченных по​

support.office.com

Группировка листов

​ ограничение на миллион​​ группировки значений даты​Месяц​ и группирует их,​ из диапазона группировки.​В Excel Вы можете​ одновременно все группы​Shift+Alt+Стрелка влево​ группировка значительно упрощает​Для тех, кто​ от посторонних глаз​Выберите команду​ одном из них,​ the other worksheets​ вызовом необходимо сначала​Как показано ниже, четыре​ уровням, можно группировать​ записей.​ и времени. Excel​ы или​ когда пользователь добавляет​Когда листы сгруппированы, можно​

​ работать как с​ одинакового уровня. Например,​.​ задачу.​ любит быстрые клавиши.​ или защитить от​Разгруппировать листы​ автоматически выполнятся на​ in the group​ разгруппировать сгруппированные элементы.​ города упорядочены в​ только элементы, имеющие​Можно также выделить и​ также автоматически свернет​Недели​ в сводные таблицы​ перейти к любому​ каждым листом по​ в таблице, показанной​Инструмент «​Когда Вы группируете столбцы,​ Скрыть выделенные столбцы​ редактирования некоторые важные​.​ другом. Например, изменение​ in the exact​Создавать срезы для иерархий​ новых группах «Центр»​

​ одинаковые следующие уровни.​

• ​ сгруппировать определенные элементы:​

• ​ данные таким образом,​

• ​. Сначала сгруппируйте элементы​

• ​ строки полей времени.​

Группировка выделенных листов

​ листу внутри группы.​ отдельности, так и​ ниже, нажатие на​Группировка​ сверху над ними​ можно нажатием​ формулы или информацию​Совет:​

​ формулы на листе​ same location on​ OLAP со сгруппированными​

1. ​ и «Северо-запад».​ Например, если в​Выделите в сводной таблице​ чтобы они отображались​

   

   ​ по неделям, убедившись,​​ Созданную группу можно​ Любые изменения, внесенные​ с несколькими одновременно.​ цифру​» добавит специальные символы​ появляется горизонтальная черта,​

2. ​Ctrl+0​ личного характера.​ Для разгруппирования также можно​ «Париж» в этом​ the worksheets. If​ полями нельзя.​Примечание:​ поле есть два​ несколько элементов для​ в периодах даты​ что выбран только​ перетащить в сводную​ на одном листе,​ Листы могут быть​

   

   ​1​​ структуры на лист​ показывающая, какие столбцы​.​​Читайте дальше, и вы​​ щелкнуть ярлычок любого​

   ​ случае также будет​

​ you want to​​ТолькоExcel 2016 : чтобы отключить​ При отмене группировки по​ уровня «Страна» и​ группировки: щелкните их,​

Отмена группировки выделенных листов

​ и времени максимального​ интервал времени​ таблицу для анализа.​ также появятся на​

1. ​ объединены в группы.​скроет все столбцы,​ Excel, которые покажут​

​ выбраны для группировки​

Группировка всех листов

​Подсказка:​ узнаете, как Excel​

1. ​ листа, не входящего​ учтено на листе​

2. ​ create, for example,​​ группировку по времени​​ времени или автоматического​

   

   ​ «Город», нельзя группировать​​ удерживая нажатой клавишу​ уровня.​Дни​В сводной таблице щелкните​ всех остальных. Однако,​ Любые изменения, внесенные​ которые видны на​ какие именно столбцы​ и могут быть​

   

​Вы можете найти​

Отмена группировки всех листов

​ позволяет быстро и​ в группу.​ «Лондон».​

1. ​ standard tables or​ в сводных таблицах​

2. ​ свертывания полей первая​​ города из разных​​ CTRL или SHIFT.​

   ​Например, если в списке​​. В поле​ правой кнопкой мыши​ если Вы выберите​ на один лист,​

​ этом рисунке, а​

support.office.com

Как скрывать и группировать столбцы в Excel

​ входят в группу.​ скрыты. Рядом с​ команду​ легко скрыть ненужные​К началу страницы​Примечание:​ calculations on a​ (в том числе​ операция отмены приводит​ стран.​Щелкните правой кнопкой мыши​​ полей выше отмечено​​Количество дней​

​ любое числовое поле​ лист, который не​ применяются ко всей​ нажатие на цифру​Теперь по одному выделяйте​ чертой Вы увидите​

• ​Скрыть​ столбцы. Кроме того,​Из этого руководства Вы​ После группировки листов в​ large number of​ в моделях данных)​​ к удалению из​​Чтобы отменить группировку, щелкните​​ выделенные элементы и​​ поле «Дата», Excel​укажите​ или поле даты​ состоит в группе,​ группе.​2​ столбцы, которые необходимо​ маленькие иконки, которые​
• ​(Hide) на Ленте​ из этой статьи​ узнаете и сможете​ названии книги отобразится​ worksheets, grouping can​
• ​ и сводных диаграммах,​ областей полей всех​ правой кнопкой мыши​ выберите команду​ автоматически добавляет год,​7​

​ и времени и​ то все окажутся​Рассмотрим пример, когда сотрудники​скроет столбцы​ скрыть, и для​ позволяют скрывать и​ меню​ Вы узнаете интересный​ научиться скрывать столбцы​ надпись​​ save you a​​ можно внести изменения​ вычисляемых полей, в​ любой элемент в​Группировать​

• ​ квартал и месяц​, а затем щелкните​
• ​ выберите команду​ разгруппированными.​ должны проходить обучение​С​

Скрываем выбранные столбцы в Excel

​ каждого нажимайте​ отображать скрытые данные​Главная​ способ скрыть столбцы​ в Excel 2010-2013.​

1. ​[Группа]​ lot of time.​ в реестр.​

​ результате чего остается​​ сгруппированных данных и​.​ (дату), как показано​Месяцы​Группировать​​Щелкните правой кнопкой мыши​​ каждые три месяца.​

1. ​и​Shift+Alt+Стрелка вправо​ буквально в один​>​ с помощью инструмента​​ Вы увидите, как​​.​ This works best​

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

​ по одному из​​ Создадим группу листов​Е​​.​​ клик. Увидев такие​Ячейки​​ «​​ работает стандартный функционал​​К началу страницы​​ if the worksheets​​ анализа данных листа​​ По тому же​​Разгруппировать​​ создается новое поле​Список полей сводной​Совет:​

​В поля​ листов группы и​ в Excel для​. Это очень удобно,​Замечание:​ иконки на листе,​>​Группировка​

Используем инструмент «Группировка», чтобы в один клик скрыть или отобразить столбцы

​ Excel для скрытия​Совет:​ already have identical​Учебный видеоролик: группировка данных​ принципу операция отмены​.​ на основе поля,​ таблицы после группировки​ Группы дат и времени​начиная с​ из контекстного меню​​ этих сотрудников. При​​ когда Вы создаёте​Объединить в группу​ Вы сразу поймёте,​Формат​», который позволяет скрывать​ столбцов, а также​ Клиентам нравятся советы. Если​ data structures.​ в сводных таблицах​ в сводной таблице​В случае числовых полей,​

​ элементы которого группируются.​ по времени​ четко обозначены в​и​ выберите пункт​ добавлении имен сотрудников​ иерархию и несколько​ можно только смежные​ где находятся скрытые​>​ и отображать скрытые​ научитесь группировать и​ вы можете поделиться​В этой статье​Примечание:​ работала и в​ а также полей​ Например, при группировке​Список полей сводной таблицы​ отчете сводной таблицы,​

1. ​по​
2. ​Разгруппировать листы​ на один из​
3. ​ уровней группировки.​​ столбцы. Если требуется​​ столбцы и какие​
4. ​Скрыть и отобразить​​ столбцы в одно​​ разгруппировывать столбцы при​​ хорошим советом, добавьте​​Группировка выделенных листов​​Мы стараемся как​​ предыдущих выпусках приложения.​

​ даты и времени​​ данных по полю​ после группировки по​ например​​введите в зависимости​​.​​ листов группы, они​​Вот и всё! Вы​​ скрыть несмежные столбцы,​​ столбцы могут быть​(Home > Cells​

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

1. ​Продавец​​ времени​​Апр​ от типа данных​Листы будут разгруппированы. Кроме​ будут добавляться и​ научились пользоваться инструментом​
2. ​ то придётся создавать​ скрыты. Как это​ > Format >​Скрываем выбранные столбцы в​​Группировка​​Если вы больше не​

​Группировка всех листов​​ вас актуальными справочными​ удаляет поле даты​ группы. В случае​создается новое поле​Примечания:​,​

1. ​ указанные ниже значения.​ того, Вы можете​​ на все остальные​​ для скрытия столбцов​ отдельные группы.​ делается:​ Hide & UnHide).​ Excel​».​​ хотите выполнять задания​​Отмена группировки всех листов​
2. ​ материалами на вашем​​ из области полей​​ групп выделенных элементов​Продавец1​​ ​​Май​​Для группировки числовых полей:​​ разгруппировать листы, просто​​ листы.​​ в Excel. Кроме​Как только Вы нажмёте​Откройте лист Excel.​
3. ​Вуаля! Теперь Вы с​Используем инструмент «Группировка», чтобы​Уметь скрывать столбцы в​ на нескольких листах​Ниже описан пример, когда​ языке. Эта страница​ и отменяет все​ будут разгруппированы только​, которое добавляется в​Если перетащить поле даты​и​​ минимальное и максимальное​​ выбрав ярлычок любого​Выберите первый лист, который​ того, Вы научились​ сочетание клавиш​​Выберите ячейки, которые надо​​ лёгкостью сможете оставить​​ в один клик​​ Excel очень полезно.​​ одновременно, отмените группировку.​​ в книге есть​ переведена автоматически, поэтому​ изменения.​ выделенные элементы. Поле​

​ раздел полей и​ из списка полей​Июн​ числа.​ листа, не состоящего​ Вы хотите включить​ группировать и разгруппировывать​Shift+Alt+Стрелка вправо​ скрыть.​ для просмотра только​ скрыть или отобразить​

​ Может быть множество​Удерживая клавишу CTRL, щелкните​

​ несколько листов, каждый​ ее текст может​
​При группировке данных в​
​ группы не будет​

​ с которым можно​

office-guru.ru

Группировка листов в Excel

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

​ сводной таблице необходимо​ удалено из списка​ работать так же,​ или столбцов, в​ изменить подпись группы,​ и времени: начальную​Урок подготовлен для Вас​Нажмите и удерживайте клавишу​ знание этих хитростей​ показаны, а возле​Shift+Alt+Стрелка вправо​

Как сгруппировать листы в Excel

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

1. ​ командой сайта office-guru.ru​Ctrl​ поможет Вам сделать​
2. ​ черты над сгруппированными​​.​​ чтобы они не​
3. ​ или несколько столбцов​ часть таблицы (листа):​К началу страницы​ всех листов одинакова​ нас важно, чтобы​ моменты.​
4. ​ него не будут​​ другими полями. В​​ поле, и расположить​

​ клавишу F2 и​ или время.​Источник: http://www.gcflearnfree.org/office2013/excel2013/10/full​на клавиатуре.​ привычную работу в​​ столбцами появится специальная​​Появится диалоговое окно​​ отвлекали от текущей​​ в таблице? Есть​Необходимо сравнить два или​Вы можете легко сгруппировать​​ и отражает продажи​​ эта статья была​Для источников данных OLAP,​

​ удалены все группы.​ сводной таблице первой​ его над ним,​ введите новое имя.​Значение в поле​Автор/переводчик: Антон Андронов​Выберите следующий лист, затем​ Excel гораздо проще.​ иконка со знаком​Группирование​ задачи.​ простой способ сделать​

Как разгруппировать все листы в Excel

1. ​ более столбцов, но​ все листы книги.​ кофе.​ вам полезна. Просим​ не поддерживающих инструкцию​​ Предположим, что поле​​ созданной группе назначается​
2. ​ существующее поле будет​Примечание:​начиная с​Автор: Антон Андронов​ следующий и т.д.​Будьте успешны вместе с​

​ «​(Group). Выберите​
​Те, кто много работает​
​ это:​

​ их разделяют несколько​

office-guru.ru

​Щелкните правой кнопкой мыши​

Группировка и отмена группировки данных в сводной таблице

В этом курсе:

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

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

Группировка данных

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

В окне Группировка установите флажки Начиная с и Заканчивая и при необходимости измените значения.

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

Нажмите кнопку ОК.

Группировка выделенных элементов

Удерживая нажатой клавишу CTRL, выделите несколько значений.

Щелкните правой кнопкой мыши и выберите команду Группировать.

Присвоение имени группе

На вкладке Анализ нажмите кнопку Параметры поля.

Измените значение в поле Пользовательское имя и нажмите кнопку «ОК».

Разгруппировка сгруппированных данных

Щелкните правой кнопкой мыши любой элемент в группе.

Выберите команду Разгруппировать.

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

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

Microsoft Excel

трюки • приёмы • решения

Как в Excel группировать элементы по дате в сводной таблице

Одна из наиболее полезных функций сводных таблиц — возможность комбинировать элементы в группах. Группировка элементов проста: выделите их и выполните команду Работа со сводными таблицами ► Параметры ► Группировать ► Группа по выделенному.

Но вы можете сделать еще один шаг вперед. Если поле содержит даты, Excel способен создавать группы автоматически. Многие пользователи упускают эту полезную функцию. На рис. 171.1 показана часть таблицы, в которой есть два столбца с данными: Дата и Продажи. Полностью таблица содержит 93156 строк и охватывает период с 8 июля 2006 года по 8 августа 2009 года. Цель состоит в том, чтобы объединить информацию о продажах по месяцам.

Рис. 171.1. Вы можете использовать сводную таблицу для обобщения данных о продажах за месяц

На рис. 171.2 вы можете видеть часть сводной таблицы (в столбцах D:E ), созданную на основе данных. Неудивительно, что она выглядит так же, как и входные данные, поскольку даты не были сгруппированы. Для группировки элементов по месяцам щелкните правой кнопкой мыши на любой ячейке в столбце Дата сводной таблицы и выберите в контекстном меню пункт Группировать. Вы увидите окно Группирование, показанное на рис. 171.3. В списке выберите Месяцы и Годы и убедитесь, что начальная и конечная даты являются правильными. Нажмите кнопку ОК. Элементы Дата в сводной таблицы группируются по годам и по месяцам (как показано на рис. 171.4).

Рис. 171.2. Сводная таблица перед группировкой по месяцам и годам

Рис. 171.3. Используйте окно Группирование, чтобы сгруппировать элементы в сводной таблице

Если вы выберете только Месяцы в окне Группирование, будут объединены месяцы ИЗ разных лет. Например, элемент июнь отобразит продажи за 2008 и 2009 годы. Обратите внимание на то, что окно Группирование содержит и другие элементы, основанные на времени. Например, можно сгруппировать данные по кварталам (рис. 171.5).

Рис. 171.4. Сводная таблица после группировки по месяцам и годам

Рис. 171.5. Сводная таблица после группировки по кварталам и годам

Группировка данных сводной таблицы в Excel 2013

Несмотря на то что большая часть операций подведения итогов и вычисления показателей выполняется с использованием стандартных команд сводных таблиц, иногда возникают ситуации, в которых требуется дополнительная группировка данных. [1] Например, исходные финансовые данные, как правило, фиксируются с указанием даты сделки (рис. 1), в отчете желательно представить их не по каждой дате отдельно (рис. 2а), а сгруппированными по месяцам (рис. 2б). Команда Группировать вкладки Анализ позволяет легко и быстро консолидировать даты сделок в группы, основанные на месяцах или кварталах. Впоследствии вы сможете суммировать данные в этих группах таким же образом, как это выполняется в полях сводной таблицы.

Рис. 1. Исходные данные содержат список сделок с указанием конкретной даты

Скачать заметку в формате Word или pdf, примеры в формате Excel

Рис. 2. Отчет, сведенный по датам: (а) данные двух лет продаж занимают более 500 столбцов; (б) после группировки по месяцам отчет существенно удобнее

Группировка полей дат

Для группировки полей дат выделите заголовок поля дат или любую ячейку с датой. Например, на рис. 2а выделите одну из ячеек: В3, В4, С4, D4… На контекстной вкладке ленты Анализ в области Группировать щелкните на кнопке Группировка по полю. Если поле содержит информацию о датах, откроется диалоговое окно Группирование (рис. 3). Обратите внимание, что исходные данные (см. рис. 1) в колонке Дата заказа должны содержать только даты; даже одна текстовая или незаполненная ячейка (пустая) в исходных данных в столбце Дата заказа не позволит сделать группировку по датам. Если перед группированием вы выделили только одну ячейку, то кнопки Группировка по полю и Группировка по выделенному работают одинаково. Различие проявится только если перед группированием вы выделите несколько ячеек.

Рис. 3. Окно Группирование

Можно группировать данные по секундам, минутам, часам, дням, месяцам, кварталам и годам. По умолчанию выделен вариант Месяцы. Выберите также Дни и Годы и нажмите Ok. Обратите внимание на некоторые особенности группировки данных в итоговой сводной таблице. Во-первых, поля Месяцы и Годы добавлены в список полей (рис. 4). Не позволяйте себя одурачить — ваш источник данных не изменился и никаких новых полей не содержит. Эти поля теперь являются частью кеша сводной таблицы в памяти (подробнее о кеше см. Excel 2013. Создание нескольких сводных таблиц на основе одного источника данных: один кеш или несколько?). Во-вторых, по умолчанию поля Месяцы и Годы автоматически добавляются в макет сводной таблицы. Вы можете работать с ними, как с обычными полями: перетаскивать в другие области или делать неактивными. В-третьих, включайте данные по Дням, чтобы иметь возможность добавить это поле в сводную таблицу. Если вы в окне Группирование оставите только Месяцы, то поле Даты вы будете видеть в списке, вот только оно эквивалентно месяцам, а поле Месяцы не появится. И наконец, в окне Группирование добавляйте поле Годы. Если это сделать, то вы корректно сможете разделить данные по годам (рис. 5а), если же этого не сделать, то данные двух лет наблюдений объединяться в одном столбце (рис. 5б).

Рис. 4. Добавление полей Месяцы и Годы в список полей и макет сводной таблицы

Рис. 5. Группирование: (а) по дням, месяцам и годам; (б) по дням и месяцам

Группировка полей дат по неделям

Диалоговое окно Группирование предлагает настройки группировки по секундам, минутам, часам, дням, месяцам, кварталам и годам. А что делать, если нужно сгруппировать данные по одной или двум неделям или иным промежуткам времени? Это вполне реально.

Прежде всего следует свериться с календарем, чтобы решить, с какого дня должна начинаться неделя: с воскресенья, понедельника или любого на ваш выбор. Например, первый понедельник в 2014-м году – 6 января. Если вы хотите, чтобы данные за первую неделю также отражались, вам следует выбрать в качестве начала отсчета последний понедельник 2013-го года – 30 декабря. Чтобы сгруппировать даты по неделям выделите заголовок или ячейку с датой. Например, на рис. 2 ячейки В3 или В4. Перейдите на контекстную вкладку ленты Анализ и в разделе Группировать щелкните на кнопке Группировка по полю. В диалоговом окне Группирование (рис. 6) выделите только параметр Дни. В результате станет доступным счетчик количество дней. Чтобы создать недельный отчет, установите значение 7. Установить в поле начиная с требуемую дату (в нашем примере – 30.12.13). Нажмите Ok. В результате сгенерируется отчет, отображающий еженедельные объемы продаж, как показано на левой части рис. 6.

Рис. 6. Группировка дат по неделям

Если вы решили выполнить группировку данных по неделям, иные настройки группировки будут недоступны. Вы не сможете сгруппировать текущее или любое другое поле по месяцам или кварталам. Чтобы преодолеть это ограничение вы должны создавать сводные таблицы так, чтобы каждая была основана на своем кеше (см. Excel 2013. Создание нескольких сводных таблиц на основе одного источника данных: один кеш или несколько?). Именно так я и поступал в файле с примерами, чтобы сохранить вид примеров в первозданном виде.

Группирование двух полей дат в одной сводной таблице

При группировке поля дат по месяцам и годам программа Excel переназначает исходное поле дат для отображения месяцев и добавляет новое поле для отображения лет. Новое поле получает имя Годы. Все довольно просто, если у вас в отчете представлено только одно поле дат. Если вам нужно создать отчет с двумя полями дат (например, дата заказа и дата оплаты) и вы пытаетесь сгруппировать оба поля по месяцам и годам, программа Excel сама установит первому сгруппированному полю имя Годы, а второму — имя Годы2. Чтобы не возникло путаницы, переименуйте поле Годы, прежде чем запустить группирование по второму полу дат.

Группировка числовых полей

Диалоговое окно Группирование, применяемое для числовых полей, позволяет группировать элементы в одинаковые диапазоны. Это может быть полезно при проведении частотного анализа. Сводная таблица на рис. 7 сформирована необычным образом. Здесь в область СТРОКИ помещено поле Доход, а в область ЗНАЧЕНИЕ – поле Заказчик. Поскольку поле Заказчик является текстовым, сводная таблица автоматически подсчитывает их количество, а не сумму.

Рис. 7. Сводная таблица, «заготовка» для проведения частотного анализа

Выделите в столбце А любое число, а затем на вкладке Анализ щелкните на кнопке Группировка по полю. В диалоговом окне Группирование выберите параметры группирования (рис. 8). В рассматриваемом случае группирование начинается с 0 и завершается величиной 25 350 при шаге группирования 5000.

Рис. 8. Частотное распределение на основе группировки заказов в группы по $5000 по полю Доход

Разгруппировка. Создав группу, вы можете разгруппировать её с помощью кнопки Разгруппировать, находящейся на вкладке Анализ. Достаточно выделить ячейку со сгруппированными данными и щелкнуть на этой кнопке. Эта команда также доступна в контекстном меню: выделите ячейку со сгруппированными данными и щелкните правой кнопкой мыши.

Группировка текстовых полей

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

Для начала создайте отчет, отображающий доход по рынкам сбыта. Держа нажатой клавишу Ctrl, выделите рынки сбыта, на основе которых будет создана новая зона (рис. 9). Перейдите на вкладку Анализ, и щелкните на кнопке Группировка по выделенному.

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

Excel добавит новое поле с именем Рынок сбыта2 (рис. 10). Выделенные на первом шаге рынки сбыта объединились в Группа1. Выделите оставшиеся рынки сбыта и повторно щелкните на кнопке Группировка по выделенному.

Рис. 10. Первая зона создана

Переименуйте не очень красиво звучащие новые поля, добавьте промежуточные итоги, отсортируйте по алфавиту строки по полю Зона продаж, и получите отчет, который не стыдно показать менеджеру (рис. 11).

Рис. 11. Итоговый отчет по новым зонам продаж

[1] Заметка написана на основе книги Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013. Глава 4.

Сводные таблицы Excel

Диалоговое окно Группирование (Grouping) предлагает настройки группировки по секундам, минутам, часам, дням, месяцам, кварталам и годам. А что делать, если нужно сгруппировать данные по одной или двум неделям? Это вполне реально.

Прежде всего следует свериться с бумажным календарем данного года. Рабочие даты могут начинаться с 4 января 2010 года, причем важно знать, что в этом году 4 января пришлось на понедельник. Вы решаете, с какого дня должна начинаться неделя: с воскресенья, понедельника или любого другого дня. Сверьтесь с календарем. Ближайший понедельник был 4 января 2010 года.

Выделите любой заголовок дат в сводной таблице. Перейдите на контекстную вкладку ленты Параметры (Options) и в разделе Группировать (Group) щелкните на кнопке Группировка по полю (Group Field). В диалоговом окне Группирование (Grouping) отмените выделение всех параметров в списке с шагом (By), за исключением параметра Дни (Days). В результате станет доступным счетчик количество дней (Number of Days). Чтобы создать недельный отчет, увеличьте количество дней с 1 до 7.

И наконец, следует установить флажок начиная с (Starting At) и определить дату. Если вы приняли настройки по умолчанию и начали с 4 января 2010 года, то все недельные периоды в отчете будут начинаться с понедельника и заканчиваться воскресеньем. Эти настройки нас вполне устраивают (рис. 4.6), поэтому оставим все без изменений.

Рис. 4.6. Для получения доступа к счетчику количество дней в списке с шагом выберите значение Дни

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

Рис. 4.7. Отчет, отображающий еженедельные объемы продаж

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

Группирование двух полей дат в один отчет

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

Если вам нужно создать отчет с двумя полями дат и вы пытаетесь сгруппировать оба поля по месяцам и годам, программа Excel сама установит первому сгруппированному полю имя Годы, а второму — имя Годы2. Это неминуемо приведет к конфликту данных. В подобном случае важно правильно переименовать поля.

Группировка данных в сводной таблице

Группировка в сводных таблицах (831,4 KiB, 976 скачиваний)

Основное предназначение сводной таблицы — анализ данных. И для этого она снабжена достаточным довольно обширным функционалом, включая группировку элементов. Для чего она нужна. Предположим, имеется таблица примерно такого вида:

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

ГРУППИРОВКА ДАТЫ И ВРЕМЕНИ
Если необходимо просмотреть суммарную стоимость предложений по кварталам, то пригодиться группировка по датам.

1. Выделить любую ячейку нужного поля из области строк или столбцов и щелкнуть правой кнопкой мыши;
2. Выбрать из контекстного меню пункт Группировать (Group) ;
3. В поле Начиная с (Starting at) ввести начальную дату для группы;
4. В поле по (Ending at) ввести конечную дату для группы;
5. В поле с шагом (By) выбрать диапазон группировки: секунды, минуты, часы, дни, месяцы, кварталы, годы (seconds, minutes, hours, days, months, quarters, years) ;
6. Нажать OK
   

ГРУППИРОВКА ЧИСЛОВЫХ ПОЛЕЙ
Может пригодиться для группировки по занятым местам или по ценам предложений. Например, можно отобрать все предложения от 110 000р до 130 000р с шагом 10 000р. В данном случае получим таблицу, в которой будут интересующие предложения из указанного диапазона, разбитые с нужным шагом. Если какие значения превышают указанную сумму(130 000р), то будет отдельная группа: >130000, если меньше:

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

И вроде бы в ячейках даты/числа и все равно. В данном случае следует проверить — а действительно ли числа это числа, а даты — даты? Потому как бывает и так, что выглядят в ячейках данные как числа или даты, а на деле это просто текст. В большинстве случаев Excel подсвечивает такие ячейки зелеными треугольничками в левом верхнем углу:

В этом случае все просто: находим самую первую ячейку с таким треугольничком и выделяем все нижестоящие ячейки(до конца таблицы(Ctrl+Shift+стрелка вниз)). После чего прокручиваем лист обратно к этой ячейке, нажимаем на значок с воскл.знаком левее ячейки и в раскрывшемся меню выбираем Преобразовать в число(дату).

После этого обязательно необходимо перейти в сводную таблицу и обновить её(выделить любую ячейку сводной таблицы→Правая кнопка мыши→Обновить (Refresh) или вкладка Данные (Data) →Обновить все (Refresh all) →Обновить (Refresh) ). Вполне возможно, что это действие придется повторить еще один-два раза.

Но может быть и так, что зеленых треугольников нет, а группировка не работает. Для начала следует убедиться, что в столбце нет ячеек с явным текстом, а так же ячеек с пробелами. По хорошему — пустые ячейки тоже лучше убрать или заменить нолями. Опять обновить пару раз сводную.

Если после этого группировка все равно отказывается работать — значит где-то еще есть числа/даты, записанные как текст. Но они могут быть не подсвечены зеленым треугольником. Такое поведение часто наблюдается в файлах, выгруженных из 1С или иных программ. Часто побеждают это очень упорным трудом: выделяют ячейку, жмут F2(чтобы войти в режим редактирования ячейки) и Enter. Тогда Excel преобразует дату/число в настоящие дату/число. Но если таких ячеек хотя бы 100 — это уже не на пару минут рутины. Благо, все это можно сделать за пару секунд. Чтобы быстро преобразовать ячейки с датами/числами, записанными как текст в реальные даты/число необходимо:

• скопировать любую пустую ячейку на листе
• выделить все ячейки с датами/числами
• правая кнопка мыши -Специальная вставка (Paste Special) -в окне выбрать Значения (Values) , операция — Сложить (Multiply)
• ОК

Excel автоматом преобразует даты и числа в нормальные данные. Возможно, придется заново задать формат датам — но это уже совершенно не сложно: правая кнопка мыши —Формат ячеек (Format Cells) -Дата (Date) .
Про другие возможности Специальной вставки можно прочитать в этой статье: Как быстро умножить/разделить/сложить/вычесть из множества ячеек одно и то же число?

ГРУППИРОВКА ТЕКСТОВЫХ ПОЛЕЙ ИЛИ ОТДЕЛЬНЫХ ЭЛЕМЕНТОВ

1. Выделить ячейку из области строк или столбцов с одним из элементов поля для группировки;
2. Удерживая CTRL или SHIFT выделить другие элементы (ячейки) этого поля;
3. Щелкнуть правой кнопкой по любой выделенной ячейке и выбрать из контекстного меню пункт Группировать (Group) или на вкладке Параметры (Options) в группе Группировать (Group) нажать кнопку Группа по выделенному (Group Selection);
   
   
4. При необходимости задать свое имя группе

В полях с уровнями можно группировать только элементы, имеющие одинаковые подуровни. Например, если в поле есть два уровня «Страна» и «Город», нельзя сгруппировать города из разных стран.

ПЕРЕИМЕНОВАНИЕ ГРУППЫ ПО УМОЛЧАНИЮ
При группировке элементов Excel задает имена групп по умолчанию, например Группа1 (Group1) для выбранных элементов или Кв-л1 (Qtr1) для квартала 1(если работаем с датами). Задать группе более понятное имя совсем несложно:

1. Выделить имя группы;
2. Нажать клавишу F2;
3. Ввести новое имя группы.

1. Выделить группу элементов, которые требуется разгруппировать;
2. На вкладке Параметры (Options) в группе Группировать (Group) нажать кнопку Разгруппировать (Ungroup) (или щелкнуть правой кнопкой мыши и выбрать из контекстного меню пункт Разгруппировать (Ungroup) ).

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

1. Для источников данных OLAP (Online Analytical Processing), не поддерживающих инструкцию CREATE SESSION CUBE, группировка элементов невозможна.
2. При наличии одного или нескольких сгруппированных элементов использовать команду Преобразование в формулы (Параметры— Сервис— Средства OLAP) невозможно. Перед использованием этой команды необходимо сначала удалить сгруппированные элементы.
3. Для быстрой работы c группами данных надо выделить ячейки в области названий строк или столбцов сводной таблицы, щелкнуть правой кнопкой мыши на любой из выделенных ячеек и выбрать Развернуть/Cвернуть (Expand/Collapse)

Так же см.:
[[Общие сведения о сводных таблицах]]
[[Сводная таблица из нескольких листов]]

Статья помогла? Поделись ссылкой с друзьями!

Поиск по меткам

Можно ли добавить дополнительные «Промежуточные итоги» для сводной таблицы, содержащей большую структуру — столбцов.
Так, чтобы эти дополнительные итоги — показывали итоги по каждой структуре.
Например, есть сводная таблица по месяцам продаж (строки) по Магазинам, Маркам, Цветам товара (столбцы).

Хотелось бы увидеть в столбцах: Общие итоги (+), Итоги по Магазину (+), Итоги по Марке (- не дает, только внутри каждого магазина), Итоги по Цвету (+), Итоги по Магазину-Марке(+), Итоги по Марке-Цвету(- не дает), Итоги по Магазину -Цвету (не дает).

Итого 7 итогов: 4 могу сделать, а 3 не получается ( в одной таблице). Приходится делать надстройку поверх Сводной.

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме — добро пожаловать на Форум