Диалоговое окно Группирование (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.7.
Если вы решили выполнить группировку данных по неделям, иные настройки группировки будут недоступны. Вы не сможете сгруппировать текущее или любое другое поле по месяцам или кварталам.
Группирование двух полей дат в один отчет
При группировке поля дат по месяцам и годам программа Excel переназначает исходное поле дат для отображения месяцев и добавляет новое поле для отображения лет. Новое поле получает имя Годы. Все довольно просто, если у вас в отчете представлено только одно поле дат.
Если вам нужно создать отчет с двумя полями дат и вы пытаетесь сгруппировать оба поля по месяцам и годам, программа Excel сама установит первому сгруппированному полю имя Годы, а второму - имя Годы2. Это неминуемо приведет к конфликту данных. В подобном случае важно правильно переименовать поля.
Группировка позволяет выделить для анализа определенное подмножество данных сводной таблицы. Например, можно сгруппировать значения даты или времени (поля даты и времени в сводной таблице) в большом и неудобном списке по кварталам и месяцам, как показано в примере ниже.
Примечание: В Excel 2016 появилась новая функция - группировка по времени . Приложение автоматически обнаруживает связи между значениями соответствующих полей и группирует их, когда пользователь добавляет в сводные таблицы строки полей времени. Созданную группу можно перетащить в сводную таблицу для анализа.
Группировка полей
Совет: Группы дат и времени четко обозначены в отчете сводной таблицы, например Апр , Май и Июн для месяцев. Чтобы изменить подпись группы, щелкните ее, нажмите клавишу F2 и введите новое имя.
Дополнительные сведения о группировке см. в разделе в конце этой статьи.
Автоматическая группировка столбцов со значениями даты и времени (группировка по времени)
Примечание: Группировка по времени доступна только в Excel 2016.
Примечания:
Если перетащить поле даты из списка полей в область строк или столбцов, в которой уже есть поле, и расположить его над ним, существующее поле будет удалено из указанной области, а автоматического сворачивания данных не произойдет, и вы сможете увидеть это поле, когда решите их свернуть.
При перетаскивании поля даты, содержащего более тысячи строк, из списка полей в область строк или столбцов в сводной таблице модели данных поле даты удаляется из списка полей, чтобы приложение Excel могло отобразить сводную таблицу, для которой превышено ограничение на миллион записей.
Группировка выделенных элементов
Можно также выделить и сгруппировать определенные элементы:
Выделите в сводной таблице несколько элементов для группировки: щелкните их, удерживая нажатой клавишу CTRL или SHIFT.
Щелкните правой кнопкой мыши выделенные элементы и выберите команду Группировать .
При группировке выбранных элементов создается новое поле на основе поля, элементы которого группируются. Например, при группировке данных по полю Продавец создается новое поле Продавец1 , которое добавляется в раздел полей и с которым можно работать так же, как и с другими полями. В сводной таблице первой созданной группе назначается подпись наподобие Группа1 . Чтобы задать для группы более осмысленное имя, щелкните его, выберите Параметры поля , а затем введите новое значение в поле Пользовательское имя .
Советы:
Чтобы получить более компактную сводную таблицу, можно создать группы для всех остальных несгруппированных элементов в поле.
В полях, упорядоченных по уровням, можно группировать только элементы, имеющие одинаковые следующие уровни. Например, если в поле есть два уровня "Страна" и "Город", нельзя группировать города из разных стран.
Разгруппировка сгруппированных данных
Чтобы отменить группировку, щелкните правой кнопкой мыши любой элемент в сгруппированных данных и выберите команду Разгруппировать .
В случае числовых полей, а также полей даты и времени будут разгруппированы все группы. В случае групп выделенных элементов будут разгруппированы только выделенные элементы. Поле группы не будет удалено из списка полей, пока для него не будут удалены все группы. Предположим, что поле "Город" содержит четыре города: Москва, Санкт-Петербург, Ярославль и Мурманск. Можно сгруппировать их, поместив Москву и Ярославль в группу с именем "Центр", а Санкт-Петербург и Мурманск в группу "Северо-запад". Новое поле "Город2" появляется в области полей и отображается в области "Строки" списка полей.
Как показано здесь, поле "Город2" создано на основе поля "Город" и помещено в область "Строки" для группировки выбранных городов.
Как показано ниже, четыре города упорядочены в новых группах "Центр" и "Северо-запад".
Примечание: При отмене группировки по времени или автоматического свертывания полей первая операция отмены приводит к удалению из областей полей всех вычисляемых полей, в результате чего остается только поле даты. По тому же принципу операция отмены в сводной таблице работала и в предыдущих выпусках приложения. Вторая операция отмены удаляет поле даты из области полей и отменяет все изменения.
Сведения о группировке данных в сводной таблице
При группировке данных в сводной таблице необходимо учитывать перечисленные ниже моменты.
Helen Bradley рассказывает, как группировать данные в сводной таблице Excel для их дальнейшего обобщения и анализа.
Работая в Excel со сводной таблицей, Вы можете прийти к выводу, что многие данные должны быть обобщены ещё больше. Это может быть сделано с помощью группировки данных, и цель этой статьи – показать Вам, как это делается.
Группируем по значению
Представьте себе такую ситуацию: Вы анализируете подборку счетов, собранных в сводной таблице. Вы можете обобщать счета, объединяя их в группы по 5 – 10 или даже 100 счетов. Давайте рассмотрим пример, где идентификатор строки OrderID – это числовое поле. В данный момент каждому счёту соответствует отдельная строка, и результат получается довольно громоздким. Мы можем сгруппировать эти данные, чтобы упростить таблицу.
В этой сводной таблице каждый идентификатор OrderID представлен в отдельной строке. Такие данные можно считать обобщёнными, но нам этого недостаточно.
Для этого кликните правой кнопкой мыши по одному из OrderID данной сводной таблицы и в появившемся меню выберите Group (Группировать). Так как OrderID это числовое поле, то диалоговое окно уже будет подготовлено для численной группировки с полями Starting At (Начиная с), Ending At (По) и By (С шагом). В данной ситуации Excel предлагает Вам группировать элементы по 10 штук.
По умолчанию Excel предлагает в качестве значения Starting At (Начиная с) первый элемент OrderID : 10248. Тем не менее, Вы можете настроить точнее или создать другую группировку, установив собственное значение параметра Starting At (Начиная с). Чтобы начать с элемента 10240 , введите это значение в поле Starting At (Начиная с) – Вы можете установить любое стартовое значение, даже такое, которого нет в данных.
Мы настраиваем параметры группировки данных – они будут сгруппированы по полю OrderID по десять последовательно пронумерованных заказов.
Итак, мы настроили значение Starting At (Начиная с), оставили значение Ending At (По) таким, как предложил Excel, и оставили значение By (С шагом) равным 10 , поскольку оно нам подходит. Вы можете делать группы меньше или больше, изменяя это значение – например: 5, 20, 50 или 100. Теперь жмём ОК . Данные будут сгруппированы по значению OrderID в более удобные для обработки группы.
Вот так выглядят данные, сгруппированные по OrderID . Как видите, они значительно более обобщены, чем ранее.
Группируем по дате
Если данные, с которыми Вы работаете, являются датами, то Вы сможете использовать похожий приём. Чтобы сгруппировать информацию по датам, кликните правой кнопкой мыши по дате в столбце или строке Вашей сводной таблицы и выберите Group (Группировать). Вы можете выбрать шаг группировки – Seconds (Секунды), Minutes (Минуты), Hours (Часы), Days (Дни), Months (Месяцы), Quarters (Кварталы) или Years (Годы), и установить начальный и конечный момент времени. Для группировки по годам, кварталам и месяцам интервал зафиксирован равным 1 , а для группировки по дням Вы можете установить собственный интервал, например, равный 7 дням, чтобы сгруппировать данные по неделям. Для этого выберите Days (Дни) как шаг группировки, установите количество дней равным 7 , укажите для параметра Starting At (Начиная с) дату, которая выпадает на начало недели, и нажмите ОК . Информация будет сгруппирована по неделям.
Большой объём данных будет сгруппирован по неделям (по 7 дней), и первым днем выступит воскресенье – день недели, соответствующий дате, указанной в параметре Starting At (Начиная с).
Группируем данные вручную
Не всегда набор записей можно разделить по группам с такой же лёгкостью, как последовательность чисел или дат. Иногда группа становится группой просто потому, что Вы так решили. В этом случае можно создать свою собственную группировку. Откройте лист со сводной таблицей, кликните по первому элементу данных, который нужно включить в группу и далее, с нажатой клавишей Ctrl , последовательно щелкните по всем элементам, которые также нужно включить в эту группу. Затем на вкладке PivotTable / Options (Работа со сводными таблицами / Анализ) нажмите Group Selection (Группировка по выделенному) и элементы будут добавлены в новую группу. Таким же образом Вы можете выделить другие элементы и создать еще одну группу.
Выделив несколько полей, Вы можете объединить их в группы так, как будет удобно.
При помощи этого инструмента Вы можете группировать данные в небольшие подборки, с которыми удобно работать. Так как по умолчанию группы названы именами Group 1 (Группа 1) и Group 2 (Группа 2), Вам придётся самостоятельно переименовать их. Для этого кликните ячейку, содержащую имя группы, и в строке формул введите более подходящее имя для группы.
После того, как группы созданы, Вы вправе переименовывать их, как угодно.
Вы можете развернуть или свернуть группу, нажав символы плюс или минус (+/-) слева от имени группы.
Исправляем ошибки
Иногда, пытаясь сгруппировать выделенные данные, Вы получите сообщение об ошибке, уведомляющее, что выбранные данные не могут быть объединены в группу. Есть целый ряд причин, почему это может произойти. Одна из причин: если Вы пытаетесь создать собственную группировку, то должны выделить два или более элемента для создания группы – нельзя создать группу из одной записи.
Если у Вас есть пустые ячейки в тех полях, где должны быть даты или числа, то это тоже станет причиной сообщения об ошибке. Ошибка появится и в том случае, если вместо даты или числа в ячейке содержится текстовое значение. В каждом случае, проверьте исходные данные и исправьте ошибку, а затем обновите сводную таблицу и попытайтесь снова.
Детализация групп
Одним из преимуществ, которые даёт группировка данных, является то, что Вы можете извлечь подмножество сгруппированных данных на новый лист. Чтобы сделать это, выберите группу, и дважды щелкните по ячейке с интересующим Вас итоговым значением. Данные, из которых было собрано это итоговое значение, будут извлечены на новый лист текущей рабочей книги Excel.
Двойной щелчок мышью по любому значению в сводной таблице создаёт новый лист, содержащий все данные, относящиеся к этому значению.
Группировка и несколько диапазонов консолидации
Вы можете создавать группы в сводной таблице, которая собрана из нескольких консолидированных диапазонов , например, из данных на разных листах рабочей книги. Для этого в Excel 2007 и 2010 на Панель быстрого доступа необходимо добавить команду (Мастер сводных таблиц и диаграмм).
Чтобы сделать это, кликните выпадающее меню Панели быстрого доступа, выберите пункт More Commands (Другие команды), в открывшемся диалоговом окне установите группу All Commands (Все команды) и в списке команд найдите PivotTable and PivotChart Wizard (Мастер сводных таблиц и диаграмм). Нажмите кнопку Add (Добавить), чтобы добавить кнопку на Панель быстрого доступа.
Теперь Вы можете создать сводную таблицу из нескольких диапазонов Excel с единообразной компоновкой данных. Вам понадобятся данные с одинаковым количеством столбцов, одинаковыми заголовками столбцов, при этом каждая таблица может иметь разное количество строк.
Эта команда исключена из меню Excel 2010, но Вы можете добавить её на Панель быстрого доступа самостоятельно.
Создайте новый лист в Вашей рабочей книге Excel, кликните только что добавленную кнопку PivotTable and PivotChart Wizard (Мастер сводных таблиц и диаграмм), выберите пункты Multiple Consolidation Ranges (В нескольких диапазонах консолидации) и PivotTable (Сводная таблица), а затем нажмите Next (Далее). Выберите I will create the page fields (Создать поля страницы) и снова нажмите Next (Далее). Теперь Вам нужно указать каждый из диапазонов. Зайдите на первый лист, выберите все данные, включая заголовки таблицы, и нажмите Add (Добавить). Повторите шаги, чтобы добавить информацию со следующего листа и так далее, пока не добавите данные со всех листов, которые планировали использовать в сводной таблице.
Мастер сводных таблиц и диаграмм позволяет объединять сразу несколько диапазонов в одной сводной таблице.
В первым делом нужно выбрать, какие диапазоны данных будут использованы.
Далее укажите количество полей, которые нужно добавить в сводную таблицу – вероятнее всего, Вам понадобится одно или два. Теперь выберите один из диапазонов в поле немного выше и введите для него имя группы. Затем выберите следующий диапазон и проделайте для него те же самые шаги. И так далее для всех диапазонов. Если имя группы уже существует, Вы можете не вводить его, а выбрать из выпадающего списка.
Так, например, диапазон, содержащий даты, может быть частью группы, которая разделяет месяцы по кварталам, а также частью группы, которая разделяет месяцы по сезонам: Winter (Зима), Spring (Весна), Summer (Лето) и Fall (Осень). Группировки могут быть по любому признаку, который Вы посчитаете подходящим для своих данных. Нажмите Next (Далее), кликните по ячейке, в которой должен будет находиться верхний левый угол сводной таблицы и нажмите Finish (Готово).
В Мастере сводных таблиц и диаграмм Вы можете связать диапазоны со страницами, как показано на рисунке выше – Quarters (Кварталы) и Seasons (Сезоны).
Сводная таблица будет сформирована автоматически, группы будут созданы как поля Report Filter (Фильтры) в списке полей сводной таблицы, но Вы, если пожелаете, можете перетащить эти поля в область Row Labels (Строки) или Column Labels (Колонны).
На рисунке выше показано: мы переместили страницу Page 2 (Сезоны) из области Report Filter (Фильтры) в область Row Labels (Строки).
Группируете ли Вы данные в уже готовой сводной таблице или Вы делаете это вместе с созданием сводной таблицы из нескольких диапазонов – результат будет одинаковый, и это станет ещё одним полезным инструментом в Вашем арсенале для обобщения данных в сводных таблицах Excel.
Сводные таблицы Excel обладают инструментом группировки полей с различными типами данных: даты, числа и текст. К примеру, вы можете сгруппировать даты заказов по годам или месяцам, посмотреть количество протестированных учеников, набравших проходной бал. Вручную выбрать текстовые элементы в поле сводной таблицы и группировать их. Это позволит увидеть промежуточные итоги для определенного набора пунктов в сводной таблице.
В сегодняшнем посте мы рассмотрим группировку в .
Группировка по датам
Для группировки по датам, щелкните правой кнопкой мыши на поле с датами в сводной таблице. В выпадающем меню, выберите Группировать.
Группирование, выберите один или несколько пунктов из поля с шагом. Для ограничения стартовой и конечной даты, вы можете изменить их в полях Начиная с и По. ЖмемОК для закрытия диалогового окна.
Группировка по неделям
Для группировки дат по неделям, щелкните правой кнопкой мыши на поле с датами. В выпадающем меню, выберите Группировать. В появившемся диалоговом окне Группирование, выберитепункт Дни в поле С шагом и укажите количество дней 7. Если необходимо, можно изменить стартовую дату на другую. Жмем ОК .
Группировка числовых значений
Для начала необходимо создать сводную таблицу, и поместить в поле строк данные, которые вы хотите сгруппировать.
Обратите внимание, что данные по численности населения находятся в поле строк.
Выделите поле, которое вы хотите сгруппировать, и перейдите по вкладке Работа со сводными таблицами -> Группировать -> Группировка по полю.
В появившемся диалоговом окне Группирование , настройте диапазон группировки и шаг. В нашем случае, мы выставили диапазон от 0 до 12000 с шагом 1000.
В поле Значения поместите данные, которыевы хотите свести по сгруппированным данным. В нашем примере, я хочу увидеть количество регионов в каждом диапазоне численности.
Получившаяся сводная таблица показывает частотное распределение численности населения регионов России.
Группировка текстовых полей
Пункт Группировать будет недоступен из выпадающего меню, если вы щелкните правой кнопкой мыши по текстовому полю. Их необходимо группировать вручную.
Выберите поле, которое вы хотите сгруппировать. На ленте перейдите по вкладке Работа со сводными таблицами -> Группировать -> Группа по выделенному.
Чтобы переименовать название группы, щелкните по полю левой кнопкой мыши и в строке формул введите свое название.
Одна из наиболее полезных функций сводных таблиц - возможность комбинировать элементы в группах. Группировка элементов проста: выделите их и выполните команду Работа со сводными таблицами Параметры Группировать Группа по выделенному
.
Но вы можете сделать еще один шаг вперед. Если поле содержит даты, Excel способен создавать группы автоматически. Многие пользователи упускают эту полезную функцию. На рис. 171.1 показана часть таблицы, в которой есть два столбца с данными: Дата
и Продажи
. Полностью таблица содержит 93156 строк и охватывает период с 8 июля 2006 года по 8 августа 2009 года. Цель состоит в том, чтобы объединить информацию о продажах по месяцам.
На рис. 171.2 вы можете видеть часть сводной таблицы (в столбцах D:E), созданную на основе данных. Неудивительно, что она выглядит так же, как и входные данные, поскольку даты не были сгруппированы. Для группировки элементов по месяцам щелкните правой кнопкой мыши на любой ячейке в столбце Дата сводной таблицы и выберите в контекстном меню пункт Группировать . Вы увидите окно Группирование , показанное на рис. 171.3. В списке выберите Месяцы и Годы и убедитесь, что начальная и конечная даты являются правильными. Нажмите кнопку ОК . Элементы Дата в сводной таблицы группируются по годам и по месяцам (как показано на рис. 171.4).
Если вы выберете только Месяцы в окне Группирование, будут объединены месяцы ИЗ разных лет. Например, элемент июнь отобразит продажи за 2008 и 2009 годы. Обратите внимание на то, что окно Группирование содержит и другие элементы, основанные на времени. Например, можно сгруппировать данные по кварталам (рис. 171.5).