Несмотря на то что большая часть операций подведения итогов и вычисления показателей выполняется с использованием стандартных команд сводных таблиц, иногда возникают ситуации, в которых требуется дополнительная группировка данных. Например, исходные финансовые данные, как правило, фиксируются с указанием даты сделки (рис. 1), в отчете желательно представить их не по каждой дате отдельно (рис. 2а), а сгруппированными по месяцам (рис. 2б). Команда Группировать вкладки Анализ позволяет легко и быстро консолидировать даты сделок в группы, основанные на месяцах или кварталах. Впоследствии вы сможете суммировать данные в этих группах таким же образом, как это выполняется в полях сводной таблицы.
- И введите свои пользовательские даты.
- Выберите вариант «месяц из группы».
Иногда бывает, что вам нужно использовать более одного раза для группировки дат в сводной таблице. Предположим, что в нижней сводной таблице вы хотите группировать даты по кварталам и месяцам. После того, как вы создадите несколько групп для дат в сводной таблице, вы также получите возможность расширения и свертывания.
Скачать заметку в формате или , примеры в формате
Рис. 2. Отчет, сведенный по датам: (а) данные двух лет продаж занимают более 500 столбцов; (б) после группировки по месяцам отчет существенно удобнее
Группировка по неделям
Вы используете его в своей работе или нет? Или вы никогда не слышали об этом. И, пожалуйста, не забудьте поделиться этим советом с друзьями. Одной из самых мощных функций сводных таблиц является их способность группировать данные. Любое поле, добавленное как метка строки или столбца, автоматически группируется по значениям, которые отображаются в этом поле. Например, вы можете использовать сводную таблицу для группировки списка сотрудников по отделам. В этом случае, когда поле отдела добавлено как метка строки, сводная таблица аккуратно разбивает количество сотрудников по подразделениям, а новая строка для каждого отдела появляется в исходных данных.
Группировка полей дат
Для группировки полей дат выделите заголовок поля дат или любую ячейку с датой. Например, на рис. 2а выделите одну из ячеек: В3, В4, С4, D4… На контекстной вкладке ленты Анализ в области Группировать щелкните на кнопке Группировка по полю . Если поле содержит информацию о датах, откроется диалоговое окно Группирование (рис. 3). Обратите внимание, что исходные данные (см. рис. 1) в колонке Дата заказа должны содержать только даты; даже одна текстовая или незаполненная ячейка (пустая) в исходных данных в столбце Дата заказа не позволит сделать группировку по датам. Если перед группированием вы выделили только одну ячейку, то кнопки Группировка по полю и Группировка по выделенному работают одинаково. Различие проявится только если перед группированием вы выделите несколько ячеек.
Группировка по датам
Многие люди останавливаются на этой базовой автоматической группировке, но сводные таблицы также могут группировать данные более сложными способами. Например, вы также можете использовать сводную таблицу для группировки данных по номерам - суммирование данных по возрастному диапазону, ценовому диапазону или любому численному диапазону, который имеет смысл для ваших данных. Чтобы проиллюстрировать, предположим, что у вас есть список результатов голосования, который включает в себя возраст избирателя, и вы хотите суммировать результаты по возрастным группам.
Рис. 3. Окно Группирование
Можно группировать данные по секундам, минутам, часам, дням, месяцам, кварталам и годам. По умолчанию выделен вариант Месяцы . Выберите также Дни и Годы и нажмите Ok . Обратите внимание на некоторые особенности группировки данных в итоговой сводной таблице. Во-первых, поля Месяцы и Годы добавлены в список полей (рис. 4). Не позволяйте себя одурачить - ваш источник данных не изменился и никаких новых полей не содержит. Эти поля теперь являются частью кеша сводной таблицы в памяти (подробнее о кеше см. ). Во-вторых, по умолчанию поля Месяцы и Годы автоматически добавляются в макет сводной таблицы. Вы можете работать с ними, как с обычными полями: перетаскивать в другие области или делать неактивными. В-третьих, включайте данные по Дням , чтобы иметь возможность добавить это поле в сводную таблицу. Если вы в окне Группирование оставите только Месяцы , то поле Даты вы будете видеть в списке, вот только оно эквивалентно месяцам, а поле Месяцы не появится. И наконец, в окне Группирование добавляйте поле Годы . Если это сделать, то вы корректно сможете разделить данные по годам (рис. 5а), если же этого не сделать, то данные двух лет наблюдений объединяться в одном столбце (рис. 5б).
Ваши данные могут выглядеть примерно так. Чтобы начать группировать данные по возрасту, сначала создайте свою сводную таблицу. Затем добавьте поле «Возраст» как метку строки, а поле «Проголосовать» как метка столбца. На этом этапе наша сводная таблица выглядит так. Хотя эта сводная таблица интересна, она не очень полезна, поскольку автоматическая группировка по фактическому возрасту слишком сложна. Это легко сделать, используя функцию группировки, встроенную в сводные таблицы.
В этом примере мы будем группироваться на 10 лет. Вы можете использовать этот же подход для групповых числовых данных многими полезными способами. Вы можете группировать клиентов по общему объему продаж, группе сотрудников по времени в компании, погодные данные группы по температуре - список бесконечен.
Сводные таблицы легко создавать, но трудно контролировать. Функция группировки сводной таблицы по дням недели. Это гигантский швейцарский армейский нож для инструментов. По мере того, как вы приобретаете больше навыков с помощью инструментов, вы выясняете, как лучше всего делать почти все.
Сводные таблицы очень хороши при группировке информации. Вы можете группироваться по годам, месяцам, кварталам и даже день и час. Но если вы хотите сгруппировать что-то вроде дня недели, сначала вам нужно сделать небольшую подготовительную работу в исходных данных.
Рис. 5. Группирование: (а) по дням, месяцам и годам; (б) по дням и месяцам
Группировка полей дат по неделям
Диалоговое окно Группирование предлагает настройки группировки по секундам, минутам, часам, дням, месяцам, кварталам и годам. А что делать, если нужно сгруппировать данные по одной или двум неделям или иным промежуткам времени? Это вполне реально.
Здесь у нас есть необработанные данные для подписки на рассылку электронной почты. Каждая строка показывает дату подписания пользователем. Позвольте быстро создать сводную таблицу, которая суммирует новые подписки по месяцам. Это ускорит процесс позже, когда мы начнем работу с исходными данными. Чтобы начать, давайте добавим поле адреса электронной почты в качестве значения. Это дает нам подсчет всех подписей по электронной почте и говорит, что сводная таблица работает правильно.
Когда поле даты негруппировано, мы получаем новую строку в сводной таблице для каждой строки исходных данных, что не очень полезно. Чтобы получить полезное резюме, нам нужно сгруппировать поле даты. В сводных таблицах можно группировать даты по годам, месяцам и даже кварталам.
Прежде всего следует свериться с календарем, чтобы решить, с какого дня должна начинаться неделя: с воскресенья, понедельника или любого на ваш выбор. Например, первый понедельник в 2014-м году – 6 января. Если вы хотите, чтобы данные за первую неделю также отражались, вам следует выбрать в качестве начала отсчета последний понедельник 2013-го года – 30 декабря. Чтобы сгруппировать даты по неделям выделите заголовок или ячейку с датой. Например, на рис. 2 ячейки В3 или В4. Перейдите на контекстную вкладку ленты Анализ и в разделе Группировать щелкните на кнопке Группировка по полю . В диалоговом окне Группирование (рис. 6) выделите только параметр Дни . В результате станет доступным счетчик количество дней . Чтобы создать недельный отчет, установите значение 7. Установить в поле начиная с требуемую дату (в нашем примере – 30.12.13). Нажмите Ok . В результате сгенерируется отчет, отображающий еженедельные объемы продаж, как показано на левой части рис. 6.
Но как мы группируем по дням недели? Вы можете использовать текст для извлечения частей даты в виде текста или когда вы хотите присоединиться к числу со строкой и сохранить определенный формат номера. Вернемся в нашу сводную таблицу, мы должны обновиться, чтобы забрать новый столбец.
Теперь данные суммируются по дням недели, и мы можем. Теперь мы можем видеть, сколько новых подписчиков было в каждый день недели. Итог: узнайте различия между функцией «Даты группы» в сводной таблице и датой группировки в исходных данных, добавив дополнительные столбцы за год, месяц, квартал и т.д.
Если вы решили выполнить группировку данных по неделям, иные настройки группировки будут недоступны. Вы не сможете сгруппировать текущее или любое другое поле по месяцам или кварталам. Чтобы преодолеть это ограничение вы должны создавать сводные таблицы так, чтобы каждая была основана на своем кеше (см. ). Именно так я и поступал в файле с примерами, чтобы сохранить вид примеров в первозданном виде.
Одна из многих причин, почему сводные таблицы являются удивительными, заключается в том, что они позволяют нам быстро группировать даты для создания сводных отчетов. Что означают даты группировки? Ну, это просто означает, что мы группируем все значения даты в столбце в год, квартал, месяц, день и т.д.
Если у нас есть данные уровня транзакции с столбцом, который содержит дату транзакции, то мы можем захотеть сгруппировать даты, чтобы сообщать данные по годам, кварталам или месяцам. Мы можем создавать дополнительные столбцы в исходных данных с текстовыми или числовыми значениями, используя формулы. Метод, который вы используете здесь, зависит от ваших потребностей и фискального календаря вашей компании. После того, как мы создадим группы в меню группировки, новые поля добавляются в список полей сводной таблицы.
Группирование двух полей дат в одной сводной таблице
При группировке поля дат по месяцам и годам программа Excel переназначает исходное поле дат для отображения месяцев и добавляет новое поле для отображения лет. Новое поле получает имя Годы . Все довольно просто, если у вас в отчете представлено только одно поле дат. Если вам нужно создать отчет с двумя полями дат (например, дата заказа и дата оплаты) и вы пытаетесь сгруппировать оба поля по месяцам и годам, программа Excel сама установит первому сгруппированному полю имя Годы , а второму - имя Годы2 . Чтобы не возникло путаницы, переименуйте поле Годы , прежде чем запустить группирование по второму полу дат.
Они фактически хранятся в сводном кэше в фоновом режиме. Сводные таблицы могут совместно использовать сводный кеш, если они имеют один и тот же диапазон или таблицу исходных данных. Это означает, что все сводные таблицы, разделяющие кеш, также будут разделять группы.
Итак, если вы группируете поле даты в одной сводной таблице, вы можете получить неожиданные результаты в другой сводной таблице. Ваши другие сводные таблицы, которые используют тот же кеш-ключ, также могут измениться. Это может вызвать разочарование и путаницу.
Вы можете отключить кеш, изменив диапазон исходных данных, но это может стать беспорядочным, если у вас много сводных таблиц. У меня также есть функция, которая отображает список всех сводных таблиц в рабочей книге и связанного с ней сводного кеша. Таким образом, главное преимущество использования функции поля сводной таблицы состоит в том, что она выполняется быстро и легко. Если вам нужен быстрый отчет о сводной таблице по годам, кварталам или месяцам, то это простое решение.
Группировка числовых полей
Диалоговое окно Группирование , применяемое для числовых полей, позволяет группировать элементы в одинаковые диапазоны. Это может быть полезно при проведении частотного анализа. Сводная таблица на рис. 7 сформирована необычным образом. Здесь в область СТРОКИ помещено поле Доход , а в область ЗНАЧЕНИЕ – поле Заказчик . Поскольку поле Заказчик является текстовым, сводная таблица автоматически подсчитывает их количество, а не сумму.
Другой вариант - добавить столбцы в исходные данные, которые вычисляют группы. Это означает, что сгруппированные поля будут фактически столбцами в исходных данных и будут просто сохранены в сводном кэше. Это дает нам большую гибкость для внесения изменений в наши группы. Если вы используете функцию группировки сводной таблицы, то разгруппирование или добавление новых группировок может привести к неожиданным результатам для других сводных таблиц, которые разделяют кеш.
Если мы добавим новую дату в исходные данные точки поворота, то сводные таблицы, которые используют эти исходные данные, не обязательно будут затронуты или изменены. Еще одно преимущество группировки дат в исходных данных - это когда ваша компания сообщает о финансовом году, который отличается от календарного года. В этом случае нам может понадобиться использовать формулу для расчета кварталов или финансовых месяцев. Мы можем добавить эту формулу в качестве нового столбца в исходных данных, а затем добавить это поле в сводную таблицу.
Выделите в столбце А любое число, а затем на вкладке Анализ щелкните на кнопке Группировка по полю . В диалоговом окне Группирование выберите параметры группирования (рис. 8). В рассматриваемом случае группирование начинается с 0 и завершается величиной 25 350 при шаге группирования 5000.
Другим преимуществом является то, что мы могли бы повторно использовать столбцы группы дат в других сводных отчетах, формулах или анализе за пределами сводной таблицы. Один большой недостаток здесь - сортировка. Сводная таблица не всегда распознает правильный порядок ваших финансовых месяцев, и нам может потребоваться добавить номер фискального месяца в начало имени месяца.
Таблицы календаря представляют собой таблицы, содержащие список дат за весь период наших данных. Они также содержат дополнительные столбцы для группировок даты. Подход таблицы календаря не добавляет никаких столбцов в исходную таблицу исходных данных и не применяет группировку в сводном кеше.
Разгруппировка. Создав группу, вы можете разгруппировать её с помощью кнопки Разгруппировать , находящейся на вкладке Анализ . Достаточно выделить ячейку со сгруппированными данными и щелкнуть на этой кнопке. Эта команда также доступна в контекстном меню: выделите ячейку со сгруппированными данными и щелкните правой кнопкой мыши.
Список сравнения параметров группировки даты
И немного морщинистой рубашки.
Функция полевой группы столбцов
Добавить столбцы даты в исходные данные. Данные о источнике данных содержат больше столбцов и добавляются в файл. . Выбранный вами метод будет зависеть от ваших потребностей и процесса. Уверен, у вас есть несколько причин, почему и почему вы группируете даты для сводных таблиц.Группируем данные вручную
Итог: узнайте, почему функция группы элементов сводной таблицы отключена, выделена серым цветом или не работает, и несколько быстрых советов по поиску проблемы. Функция группировки даты сводных таблиц - отличный инструмент, который может сэкономить нам много времени.
Группировка текстовых полей
Excel позволяет вручную группировать любые строки и столбцы сводной таблицы. Рассмотрим эту возможность на примере. Предположим, вас вызвал менеджер по продажам. Он хочет выполнить реорганизацию региональных отделений. Он просит предоставить отчет с объемами продаж за последние два года по указанным новым зонам и входящим в них рынкам сбыта. Вы догадываетесь, что эти зоны будут меняться не один раз до принятия окончательного решения, поэтому не хотите вносить изменения в источник данных.
Простое правило для группировки дат в сводных таблицах
Однако функция группировки даты не всегда работает. Давайте посмотрим, почему это происходит. Причина в основном связана с правилом выше, где все ячейки в поле даты не содержат дат. Существует несколько способов поиска ячеек, содержащих текст или ошибки в столбце.
Группы выпадающих меню «Фильтр»
Один быстрый способ определить, есть ли текст или значения ошибок в вашем столбце, это использовать список флажков в раскрывающемся меню фильтра. Как показано на изображении ниже, в раскрывающемся меню фильтра группируются все значения даты в этом столбце по годам, месяцам, дням. Все значения текста и ошибок перечислены в нижней части списка.
Для начала создайте отчет, отображающий доход по рынкам сбыта. Держа нажатой клавишу Ctrl, выделите рынки сбыта, на основе которых будет создана новая зона (рис. 9). Перейдите на вкладку Анализ, и щелкните на кнопке Группировка по выделенному .
Что делать, если Excel не группирует данные в сводном отчете и выдает ошибку?
Мы видим, что этот столбец содержит некоторые даты, которые были введены в неправильном формате. Теперь столбцы будут отфильтрованы только для отображения значений текста и ошибок. Следующий шаг - определить, почему значения не являются датами, и исправить их.
Если раскрывающееся меню фильтра не содержит групп дат за год, месяц или день, то весь столбец, вероятно, является текстовым значением. Это применит фильтр для всех текстовых значений в столбце. Обратите внимание, что этот метод работает только для текста, а не для логических или ошибок.
Excel добавит новое поле с именем Рынок сбыта2 (рис. 10). Выделенные на первом шаге рынки сбыта объединились в Группа1 . Выделите оставшиеся рынки сбыта и повторно щелкните на кнопке Группировка по выделенному .
Переименуйте не очень красиво звучащие новые поля, добавьте промежуточные итоги, отсортируйте по алфавиту строки по полю Зона продаж , и получите отчет, который не стыдно показать менеджеру (рис. 11).
Заметка написана на основе книги Билл Джелен, Майкл Александер. . Глава 4.
Группировка позволяет выделить для анализа определенное подмножество данных сводной таблицы. Например, можно сгруппировать значения даты или времени (поля даты и времени в сводной таблице) в большом и неудобном списке по кварталам и месяцам, как показано в примере ниже.
Примечание: В Excel 2016 появилась новая функция - группировка по времени . Приложение автоматически обнаруживает связи между значениями соответствующих полей и группирует их, когда пользователь добавляет в сводные таблицы строки полей времени. Созданную группу можно перетащить в сводную таблицу для анализа.
Группировка полей
Совет: Группы дат и времени четко обозначены в отчете сводной таблицы, например Апр , Май и Июн для месяцев. Чтобы изменить подпись группы, щелкните ее, нажмите клавишу F2 и введите новое имя.
Дополнительные сведения о группировке см. в разделе в конце этой статьи.
Автоматическая группировка столбцов со значениями даты и времени (группировка по времени)
Примечание: Группировка по времени доступна только в Excel 2016.
Примечания:
Если перетащить поле даты из списка полей в область строк или столбцов, в которой уже есть поле, и расположить его над ним, существующее поле будет удалено из указанной области, а автоматического сворачивания данных не произойдет, и вы сможете увидеть это поле, когда решите их свернуть.
При перетаскивании поля даты, содержащего более тысячи строк, из списка полей в область строк или столбцов в сводной таблице модели данных поле даты удаляется из списка полей, чтобы приложение Excel могло отобразить сводную таблицу, для которой превышено ограничение на миллион записей.
Группировка выделенных элементов
Можно также выделить и сгруппировать определенные элементы:
Выделите в сводной таблице несколько элементов для группировки: щелкните их, удерживая нажатой клавишу CTRL или SHIFT.
Щелкните правой кнопкой мыши выделенные элементы и выберите команду Группировать .
При группировке выбранных элементов создается новое поле на основе поля, элементы которого группируются. Например, при группировке данных по полю Продавец создается новое поле Продавец1 , которое добавляется в раздел полей и с которым можно работать так же, как и с другими полями. В сводной таблице первой созданной группе назначается подпись наподобие Группа1 . Чтобы задать для группы более осмысленное имя, щелкните его, выберите Параметры поля , а затем введите новое значение в поле Пользовательское имя .
Советы:
Чтобы получить более компактную сводную таблицу, можно создать группы для всех остальных несгруппированных элементов в поле.
В полях, упорядоченных по уровням, можно группировать только элементы, имеющие одинаковые следующие уровни. Например, если в поле есть два уровня "Страна" и "Город", нельзя группировать города из разных стран.
Разгруппировка сгруппированных данных
Чтобы отменить группировку, щелкните правой кнопкой мыши любой элемент в сгруппированных данных и выберите команду Разгруппировать .
В случае числовых полей, а также полей даты и времени будут разгруппированы все группы. В случае групп выделенных элементов будут разгруппированы только выделенные элементы. Поле группы не будет удалено из списка полей, пока для него не будут удалены все группы. Предположим, что поле "Город" содержит четыре города: Москва, Санкт-Петербург, Ярославль и Мурманск. Можно сгруппировать их, поместив Москву и Ярославль в группу с именем "Центр", а Санкт-Петербург и Мурманск в группу "Северо-запад". Новое поле "Город2" появляется в области полей и отображается в области "Строки" списка полей.
Как показано здесь, поле "Город2" создано на основе поля "Город" и помещено в область "Строки" для группировки выбранных городов.
Как показано ниже, четыре города упорядочены в новых группах "Центр" и "Северо-запад".
Примечание: При отмене группировки по времени или автоматического свертывания полей первая операция отмены приводит к удалению из областей полей всех вычисляемых полей, в результате чего остается только поле даты. По тому же принципу операция отмены в сводной таблице работала и в предыдущих выпусках приложения. Вторая операция отмены удаляет поле даты из области полей и отменяет все изменения.
Сведения о группировке данных в сводной таблице
При группировке данных в сводной таблице необходимо учитывать перечисленные ниже моменты.