Как сгруппировать даты по месяцам в excel

Как группировать даты в сводных таблицах в Excel (по годам, месяцам, неделям)

Возможность быстро группировать даты в сводных таблицах Excel может быть весьма полезной.

Он помогает анализировать данные, получая различные представления по датам, неделям, месяцам, кварталам и годам.

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

Точно так же, если у вас есть данные колл-центра, вы можете сгруппировать их по минутам или часам.

Посмотреть видео — Группировка дат в сводных таблицах (группировка по месяцам / годам)

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

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

В нем есть данные о продажах по дате, магазинам и регионам (восток, запад, север и юг). Данные охватывают более 300 строк и 4 столбца.

Вот простая сводка сводной таблицы, созданная с использованием этих данных:

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

Загрузите данные и следуйте инструкциям.

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

В приведенном выше наборе данных указаны даты за два года (2014 и 2015 годы).

Вот шаги, чтобы сгруппировать эти даты по годам:

Выберите любую ячейку в столбце «Дата» сводной таблицы.
Перейдите в Инструменты сводной таблицы -> Анализировать -> Группа -> Выбор группы.
В диалоговом окне «Группировка» выберите «Годы».
- При группировании дат вы можете выбрать несколько вариантов. По умолчанию опция «Месяцы» уже выбрана. Вы можете выбрать дополнительную опцию вместе с Месяцем. Чтобы отменить выбор месяца, просто щелкните по нему.
- Он выбирает дату "Начало" и "Дата окончания" на основе исходных данных. Если хотите, можете их изменить.
Это резюмирует сводную таблицу по годам.

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

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

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

Вот как вы можете сгруппировать их по кварталам:
- Выберите любую ячейку в столбце «Дата» сводной таблицы.
- Перейдите в Инструменты сводной таблицы -> Анализировать -> Группа -> Выбор группы.
- В диалоговом окне «Группировка» выберите «Кварталы» и отмените выбор любых других выбранных опций.
- Щелкните ОК.
Это подытожит сводную таблицу по кварталам.

Проблема с этой сводной таблицей заключается в том, что она объединяет квартальную стоимость продаж за 2014 и 2015 годы. Следовательно, для каждого квартала стоимость продаж представляет собой сумму значений продаж в 1 квартале 2014 и 2015 годов.

В реальной жизни вы, скорее всего, будете анализировать эти кварталы для каждого года отдельно. Сделать это:
- Выберите любую ячейку в столбце «Дата» сводной таблицы.
- Перейдите в Инструменты сводной таблицы -> Анализировать -> Группа -> Выбор группы.
- В диалоговом окне «Группировка» выберите «Кварталы» и «Годы». Вы можете выбрать более одного варианта, просто щелкнув его.
- Щелкните ОК.
Это суммирует данные по годам, а затем по годам по кварталам. Что-то вроде того, что показано ниже:

Примечание. Я использую макет табличной формы на снимке выше.

Когда вы группируете даты более чем по одной временной группе, происходит кое-что интересное. Если вы посмотрите на список полей, вы заметите, что новое поле было добавлено автоматически. В данном случае это годы.

Обратите внимание, что это новое появившееся поле не является частью источника данных. Это поле было создано в сводном кэше для быстрой группировки и суммирования данных. Когда вы разгруппируете данные, это поле исчезнет.

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

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

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

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

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

Вот как это сделать:
- Выберите любую ячейку в столбце «Дата» сводной таблицы.
- Перейдите в Инструменты сводной таблицы -> Анализировать -> Группа -> Выбор группы.
- В диалоговом окне «Группировка» выберите «Месяцы» и «Годы». Вы можете выбрать более одного варианта, просто щелкнув его.
- Щелкните ОК.
Это сгруппирует поле даты и суммирует данные, как показано ниже:

Опять же, это приведет к добавлению нового поля Годы в поля сводной таблицы. Вы можете просто перетащить поле лет в область столбцов, чтобы получить годы в столбцах, а месяцы — в строках. Вы получите что-то, как показано ниже:

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

При анализе таких данных, как продажи в магазинах или посещаемость веб-сайтов, имеет смысл анализировать их еженедельно.

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

Вот как можно группировать даты по неделям:
- Выберите любую ячейку в столбце «Дата» сводной таблицы.
- Перейдите в Инструменты сводной таблицы -> Анализировать -> Группа -> Выбор группы.
- В диалоговом окне «Группировка» выберите «Дни» и отмените выбор любых других выбранных опций. Как только вы это сделаете, вы заметите, что становится доступной опция «Количество дней» (внизу справа).
  - Встроенной возможности группировки по неделям нет. Вам нужно сгруппировать по дням и указать количество дней, которые будут использоваться при группировке.
  - Обратите внимание, что для того, чтобы это работало, вам нужно выбрать параметр «Только дни».
  - В таком случае вы можете начать дату 30 декабря 2013 г. или 6 января 2014 г. (оба понедельника).
  Это сгруппирует даты по неделям, как показано ниже:
  
  Точно так же вы можете группировать даты, указав любое другое количество дней. Например, вместо еженедельных дат можно группировать даты с интервалом в две недели.
  - Когда вы группируете даты с помощью этого метода, вы не можете сгруппировать их с помощью каких-либо других параметров (например, месяцев, кварталов или лет).
  - Вычисляемое поле / элемент не будет работать при группировке с использованием дней.
  Группировка по секундам / часам / минутам в сводной таблице
  
  Если вы работаете с большими объемами данных (например, данными колл-центра), вы можете сгруппировать их по секундам, минутам или часам.
  
  Вы можете использовать тот же процесс для группировки данных по секундам, минутам или часам.
  
  Предположим, у вас есть данные колл-центра, как показано ниже:
  
  В приведенных выше данных дата записывается вместе со временем. В этом случае для менеджера центра обработки вызовов имеет смысл проанализировать, как номера разрешения вызовов меняются за час.
  
  Вот как сгруппировать дни по часам:
  - Создайте сводную таблицу с датой в области строк и решенным в области значений.
  - Выберите любую ячейку в столбце «Дата» сводной таблицы.
  - Перейдите в Инструменты сводной таблицы -> Анализировать -> Группа -> Выбор группы.
  - В диалоговом окне Группировка выберите Часы.
  - Щелкните ОК.
  Это сгруппирует данные по часам, и вы получите что-то, как показано ниже:
  
  Вы можете видеть, что метки строк здесь — 09, 10 и так далее… которые являются часами в сутках. 09 означает 9 утра, а 18 — 6 вечера. Используя эту сводную таблицу, вы можете легко определить, что большинство вызовов разрешается в течение 1-2 часов дня.
  
  Точно так же вы можете сгруппировать даты по секундам и минутам.
  
  Как разгруппировать даты в сводной таблице в 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).
  
  Группировка в сводной таблице Excel
  
  Часто возникает необходимость выполнить группировку в сводной таблице по заголовкам строки или столбца. Для численных значений Excel может сделать это автоматически (в том числе для дат и времени). Далее это продемонстрировано на примерах.
  
  Пример 1: Группировка в сводной таблице по дате
  
  Предположим, мы создали сводную таблицу (как на картинке ниже), которая показывает данные продаж для каждого дня первого квартала 2016 года.
  
  Если нужно выполнить группировку данных о продажах по месяцам, то это можно сделать вот так:
  1. Кликните правой кнопкой мыши по левому столбцу сводной таблицы (столбец с датами) и выберите команду Группировать (Group). Появится диалоговое окно Группирование (Grouping) для дат.
  2. Выберите Месяцы (Month) и нажмите ОК. Данные таблицы будут сгруппированы по месяцам, как показано в сводной таблице ниже.
  Пример 2: Группировка в сводной таблице по диапазону
  
  Предположим, мы создали сводную таблицу (как на картинке ниже), которая группирует список из 150 детей по возрасту. Группы делятся по возрастам от 5 до 16 лет.
  
  Если нужно пойти еще дальше и объединить возрастные группы в категории 5-8 лет, 9-12 лет и 13-16 лет, то можно поступить вот так:
  1. Кликните правой кнопкой мыши по левому столбцу сводной таблицы (столбец с возрастами) и выберите команду Группировать (Group). Появится диалоговое окно Группирование (Grouping) для чисел.Excel автоматически заполнит поля Начиная с (Starting At) и По (Ending At) минимальным и максимальным значениями из наших исходных данных (в нашем примере это 5 и 16).
  2. Мы хотим объединить возрастные группы в категории по 4 года, следовательно, в поле С шагом (By) вводим значение 4. Жмём ОК.Таким образом возрастные группы будут объединены в категории начиная с 5-8 лет и далее с шагом по 4 года. В итоге получится вот такая таблица:
  Как разгруппировать сводную таблицу
  
  Чтобы разгруппировать значения в сводной таблице, нужно:
  - Кликнуть правой кнопкой мыши по левому столбцу сводной таблицы (столбец, содержащий сгруппированные значения);
  - В появившемся меню нажать Разгруппировать (Ungroup).
  Распространённые ошибки при группировке в сводной таблице
  
  Ошибка при группировке в сводной таблице: Выделенные объекты нельзя объединить в группу (Cannot group that selection).
  
  Иногда при попытке выполнить группировку в сводной таблице оказывается, что команда Группировать (Group) в меню не активна, или появляется окно с сообщением об ошибке Выделенные объекты нельзя объединить в группу (Cannot group that selection). Это происходит чаще всего потому, что в столбце данных в исходной таблице содержатся нечисловые значения или ошибки. Чтобы исправить это, нужно вместо нечисловых значений вставить числа или даты.
  
  После этого кликните правой кнопкой мыши по сводной таблице и нажмите Обновить (Refresh). Данные в сводной таблице будут обновлены, и теперь группировка строк или столбцов должна быть доступна.
  
  Как сгруппировать даты по месяцам в excel
  
  Как сгруппировать дату по месяцу, году, полугодию или другим конкретным датам в сводной таблице?
  
  Если в Excel данные в сводной таблице содержат дату, и пытались ли вы сгруппировать данные по месяцам, кварталам или годам? Теперь в этом руководстве рассказывается, как сгруппировать дату по месяцам / годам / кварталам в сводной таблице в Excel.
  
  Группировать дату по месяцам, кварталам или годам в сводной таблице
  
  В сводной таблице есть функция группировки, вы можете применить ее для группировки данных по мере необходимости.
  
  1. Выберите нужный диапазон данных и нажмите Вставить > PivotTable. Смотрите скриншот:
  
  2. В Создать сводную таблицу в диалоговом окне выберите нужный вариант в Выберите, где вы хотите разместить отчет сводной таблицы раздел. Смотрите скриншот:
  
  3. Нажмите OK для создания сводной таблицы. И в Список полей сводной таблицы панели, перетащите поле в нужные области и убедитесь, что метка даты находится в списке меток строк. (В Excel 2016 и более поздних версиях дата автоматически сгруппируется по годам и кварталам), см. Снимок экрана:
  
  4. Затем перейдите к сводной таблице, щелкните правой кнопкой мыши в любом месте под заголовком «Ярлыки строк» и выберите группы. Смотрите скриншот:
  
  5. В группирование диалоговом окне выберите критерии группировки из By список. Здесь я выбираю Месяц и Лет. Смотрите скриншот:
  
  Советы : В этом группирование диалоговом окне вы также можете выбрать Месяцев, помещение, Лет отдельно для группировки даты по месяцам, кварталам или годам, как вам нужно.
  
  6. Нажмите OK чтобы применить функцию. Теперь вы можете видеть, что данные сгруппированы по месяцам и годам, как показано на следующем снимке экрана:
  
  Группировать дату по полугодию, номеру недели или другим конкретным датам в сводной таблице
  
  С помощью описанного выше метода вы можете быстро сгруппировать дату по месяцу, году, кварталу, но иногда вам может потребоваться сгруппировать дату по определенной дате, например финансовому году, полугодию, номеру недели и т. Д. Обычная функция Group не поддерживает эту проблему.
  
  Если у вас есть Kutools for Excel, С его Группировка по особому времени сводной таблицы функция, вы можете быстро сгруппировать дату по финансовому году, полугодию, номеру недели, дню недели, получасу или конкретным минутам по мере необходимости.
  
  Советы: Чтобы применить это Группировка по особому времени сводной таблицы функция, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.
  
  После установки Kutools for Excel, пожалуйста, сделайте так:
  
  1. Нажмите Кутулс Плюс > Сводная таблица > Группировка по особому времени сводной таблицы, см. снимок экрана:
  
  2. В Группировка по особому времени сводной таблицы диалоговом окне выполните следующие действия:
  - (1.) Сначала выберите диапазон данных, в котором вы хотите создать сводную таблицу, сгруппированную по определенной дате;
  - (2.) Из Группа по список, выберите нужный вариант, в данном случае я выбираю Полгода;
  - (3.) Затем выберите столбец даты, по которому вы хотите сгруппировать;
  - (4.) Наконец, выберите место для вывода сводной таблицы, вы можете поместить сводную таблицу на новый рабочий лист или в ячейку текущего рабочего листа, как вам нужно.
  3. После завершения настроек нажмите Ok кнопка и новый Полгода Вспомогательный столбец добавляется справа от диапазона данных.
  
  4. Затем щелкните любую ячейку в сводной таблице, чтобы активировать Поля сводной таблицы панель, перетащите Полгода поле к Ряды раздел, а затем перетащите другие поля в нужные разделы по мере необходимости. Теперь вы увидите, что сводная таблица создана, а даты сгруппированы по полугодиям каждого года. Смотрите скриншот:
  
  Простая демонстрация
  
  Группировать дату по финансовому году, полугодию, номеру недели или другим конкретным датам в сводной таблице
  
  Обычно вы можете быстро сгруппировать сводную таблицу по месяцам, годам, кварталам, но иногда вам может потребоваться сгруппировать данные на основе финансового года, полугодия и других конкретных дат. В этом случае, Kutools for Excel‘s Группировка по особому времени сводной таблицы Утилита расширяет исходную функцию группировки и поддерживает больше группировок дат. Нажмите, чтобы загрузить Kutools for Excel!
  Похожие публикации: