Работа с датами в сводной таблице в excel

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

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

Одна строка в этой базе данных — одна продажа, т.е. полностью описывает одну сделку (что, когда и по какой цене).

Пример 1. Группировка чисел

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

Теперь щелкаем правой кнопкой мыши по цене (область строк) и выбираем из контекстного меню команду Группировать (Group). В Excel 2000-2003 эта команда находится в разделе Группа и структура (Group and Show Detail) контекстного меню:

В итоге появится наш основной инструмент — диалоговое окно группировки данных в сводной таблице:

В окне можно задать начальное и конечное значение интересующего нас интервала и шаг изменения. Особое внимание хочу обратить на флажки слева от слов «начиная с» и «по» — эти флажки относятся к слову «Авто» и означают, что Excel будет сам выбирать интервал (дизайнеру окна напинать бы за такой user-friendly интерфейс сами знаете что). Для определенности введем начальное значение равное нулю, конечное 270 (самый дорогой наш товар стоит 263,5$) и зададим шаг 30, т.е. окно должно выглядеть так:

После нажатия на ОК Excel сгруппирует товары с ценами в заданном интервале и посчитает суммарную выручку по каждому интервалу:

Теперь ясно видно, что (в данном примере) наибольшую выручку нам дают товары с ценой до 60 долларов. «Хочешь быть богатым — работай для бедных!» как говорил Рональд Макдональд.

Пример 2. Группировка дат

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

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

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

После нажатия на ОК Excel сгруппирует даты с шагом месяцы и кварталы и мы получим вот такой красивый отчет:

Пример 3. Произвольная группировка

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

… и сгруппировать товары (предварительно перетащив их мышью в сводной таблице в соседние строки) в одну кучку:

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

Ссылки по теме

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

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

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

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

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

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

Уровень мастерства: Средний

Изменение формата для дат не работает

Когда мы группируем поле «Дата» в сводной таблице с помощью функции «Группировать», форматирование чисел для поля «День» фиксируется. Он имеет следующий формат «день-месяц» или «d-ммм».

Если мы попытаемся изменить числовой формат поля День/Дата, это не сработает. Ничего не меняется, когда мы заходим в Настройки поля> Числовой формат и меняем числовой формат на пользовательский или формат даты.

Почему?

Форматирование чисел не работает, потому что элемент сводки
— это фактически текст, а НЕ дата.

Когда мы группируем поля, функция группирования создает
элемент Дни для каждого дня одного года. Он сохраняет название месяца в именах
полей Day, и фактически это группа номеров дней (1-31) для каждого месяца.

На самом деле можно увидеть этот список текстовых элементов в файле pivotCacheDefinition.xml. Чтобы увидеть, что вы можете изменить расширение файла Excel на .zip и перейти к папке PivotCache.

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

Решение № 1 — Не используйте группы дат

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

Я подробно объясняю это в своей статье «Группировка дат в сводной таблице». Источник данных.

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

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

Автоматическая группировка полей даты

Если вы используете Excel 2016 (Office 365), то поле даты
автоматически группируется при добавлении его в сводную таблицу.

Разгруппировать поле даты:

1. Выберите ячейку внутри сводной таблицы в одном
   из полей даты.
2. Нажмите кнопку «Разгруппировать» на вкладке
   «Анализ» ленты.

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

Как только поле даты будет разгруппировано, вы можете изменить форматирование поля.

Чтобы изменить форматирование чисел в несгруппированном поле
Дата:

1. Щелкните правой кнопкой мыши ячейку в поле даты
   сводной таблицы.
2. Выберите настройки поля …
3. Нажмите кнопку «Числовой формат».
4. Измените форматирование даты в окне «Формат
   ячеек».
5. Нажмите ОК и ОК.

Опять же, это работает только для полей, которые НЕ
сгруппированы. Если вы снова сгруппируете поле после изменения форматирования,
форматирование элементов в поле «Дни» изменится на «1 января».

Решение №2. Изменение имен элементов сводки с помощью VBA

Если вы действительно хотите использовать функцию Group Field, то мы можем использовать макрос для изменения имен элементов сводки. Создается впечатление, что изменилось форматирование даты, но на самом деле меняется текст в каждом названии элемента сводки.

Следующий макрос перебирает все сводные элементы сгруппированного поля «Дни» и изменяет форматирование чисел на пользовательский формат. По умолчанию я установил «m/d», но вы можете изменить его на любой формат даты для месяца и дня. Просто помните, что элемент НЕ будет содержать год, так как элемент не является фактической датой.

Скачать файл

Загрузите
файл Excel, который содержит макрос.

Pivot Table Date Field Group Number Formatting Macro.xlsm (54.2 KB)

Макрос форматирования поля «Дни»

Sub Change_Days_Field_Formatting()
' Измените форматирование чисел в поле Дни
' для поля даты сгруппированной сводной таблицы.

Dim pt As PivotTable
Dim pi As PivotItem


' ВАЖНО: Измените следующее имя
' Сгруппированное поле Дни. Обычно это дни или название
' вашего поля даты.

Const sDaysField As String = "Days"

  ' Установить ссылку на первую сводную таблицу на листе
  ' Это может быть изменено для ссылки на имя сводной таблицы
  'Set pt = ActiveSheet.PivotTables("PivotTable1")
  
  Set pt = ActiveSheet.PivotTables(1)

  ' Установите имена обратно к их имени источника по умолчанию
  For Each pi In pt.PivotFields(sDaysField).PivotItems
    ' Обходит первый и последний пункты "<01.01.2015" ...
    If Left(pi.Name, 1) <> "<" And Left(pi.Name, 1) <> ">" Then
      pi.Name = pi.SourceName
    End If
  Next pi

  ' Установите имена в произвольном формате чисел
  For Each pi In pt.PivotFields(sDaysField).PivotItems
    If Left(pi.Name, 1) <> "<" And Left(pi.Name, 1) <> ">" Then
      ' Измените приведенный ниже формат «m / d» на пользовательский числовой формат.
      ' 2020 год используется для високосного года.
      pi.Name = Format(DateValue(pi.SourceName & "-2020"), "m/d")
    End If
  Next pi

End Sub

Как работает макрос

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

Второй цикл меняет каждый элемент сводки на новый формат. Он
использует функцию DateValue для изменения названия элемента сводки «1-Jan» на
дату. Затем он использует функцию «Формат», чтобы изменить форматирование даты
на текст. По умолчанию используется формат «m / d». Это может быть изменено на
другой формат с месяцем и днем. Каждый элемент должен быть уникальным, поэтому
вы можете использовать месяц и день в названии элемента.

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

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

Макрос форматирования сгруппированных элементов

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

Вот код макроса. Вам просто нужно изменить значение
константы sGroupField вверху на имя вашего сгруппированного поля. При
необходимости вы также можете изменить формат чисел в sNumberFormat.

Sub Change_Grouped_Field_Number_Formatting()
' Изменить форматирование номера поля сгруппированных номеров

Dim pt As PivotTable
Dim pi As PivotItem
Dim sGroup() As String
Dim sGroupName As String


' ВАЖНО: Измените следующее имя
' поле сгруппированного номера в области строк или столбцов.

Const sGroupedField As String = "Quantity"
Const sNumberFormat As String = "$#,###"

  ' Установить ссылку на первую сводную таблицу на листе
  ' Это может быть изменено для ссылки на имя сводной таблицы
  'Set pt = ActiveSheet.PivotTables("PivotTable1")
  
  Set pt = ActiveSheet.PivotTables(1)

  ' Установите имена обратно к их имени источника по умолчанию
  For Each pi In pt.PivotFields(sGroupedField).PivotItems
    ' Обходит первый и последний пункты "<01.01.2015" ...
    If Left(pi.Name, 1) <> "<" And Left(pi.Name, 1) <> ">" Then
      pi.Name = pi.SourceName
    End If
  Next pi

  ' Установите имена в произвольном формате чисел
  For Each pi In pt.PivotFields(sGroupedField).PivotItems
  
    If Left(pi.Name, 1) = "<" Or Left(pi.Name, 1) = ">" Then
      sGroupName = "<" & Format(Mid(pi.Name, 2, Len(pi.Name)), sNumberFormat)
    Else
      sGroup = Split(pi.Name, "-")
      sGroupName = Format(sGroup(0), sNumberFormat) & " - " & Format(sGroup(1), sNumberFormat)
    End If
    
    If sGroupName <> "" Then pi.Name = sGroupName
      
  Next pi

End Sub

Окончательный вердикт

Я надеюсь, что информация была полезной. Я бы предложил использовать решение № 1, если вы не хотите использовать функцию группы.

Пожалуйста, оставьте комментарий ниже с любыми вопросами или
предложениями о том, как мы можем улучшить это. Спасибо! 🙂

Если в Excel данные в сводной таблице содержат дату, и пытались ли вы сгруппировать данные по месяцам, кварталам или годам? Теперь из этого руководства вы узнаете, как группировать дату по месяцам/годам/кварталам в сводной таблице в Excel.

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

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

Простой демонстрация

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

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

1 . Выберите нужный диапазон данных и нажмите Вставить > Сводную таблицу . См. Снимок экрана:

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

3 . Нажмите OK , чтобы создать сводную таблицу. На панели Список полей сводной таблицы перетащите поле в нужные области и убедитесь, что метка даты находится в списке меток строк. (В Excel 2016 и более поздних версиях дата автоматически сгруппируется по годам и кварталам), см. Снимок экрана:

4 . Затем перейдите к сводной таблице, щелкните правой кнопкой мыши в любом месте под заголовком «Ярлыки строк» ​​и выберите Группа . См. Снимок экрана:

5 . В диалоговом окне Группировка выберите критерии группировки из списка По . Здесь я выбираю Месяц и Годы . См. Снимок экрана:

Советы . В этом диалоговом окне Группировка вы также можете выбрать Месяцы , Кварталы , Годы отдельно для группировки даты по месяцам, кварталам или годам по мере необходимости.

6 . Нажмите OK , чтобы применить функцию. Теперь вы можете видеть, что данные сгруппированы по месяцам и годам, как показано на следующем снимке экрана:

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

С помощью описанного выше метода вы можно быстро сгруппировать дату по месяцу, году, кварталу, но иногда может потребоваться сгруппировать дату по определенной дате, например, финансовому году, полугодию, номеру недели и т. д. Обычная функция группы не поддерживает работу с этим..

Если у вас есть Kutools for Excel , с его функцией Сводная таблица Special Time Grouping , вы можете быстро сгруппировать дату по финансовому году. , полгода, номер недели, день недели, полчаса или определенные минуты по мере необходимости.

Советы: Чтобы применить это Специальное группирование по времени в сводной таблице . Во-первых, вы должны загрузить Kutools for Excel , а затем быстро и легко применить эту функцию.

После установки Kutools for Excel , сделайте следующее:

1 . Нажмите Kutools Plus > Сводная таблица > Специальная временная группировка сводной таблицы , см. Снимок экрана:

2 . В диалоговом окне Группировка по особому времени сводной таблицы выполните следующие операции:

• (1.) Сначала выберите диапазон данных, который вы хотите создать сводную таблицу, сгруппированную по определенной дате;
• (2.) В списке Group By выберите нужный вариант, в данном случае , Я выбираю Полгода ;
• (3.) Затем выберите столбец даты, по которому вы хотите сгруппировать;
• ( 4.) Наконец, выберите место для вывода сводной таблицы, вы можете поместить сводную таблицу на новый рабочий лист или в ячейку текущего рабочего листа, как вам нужно.

3 . После завершения настройки нажмите кнопку Ok , и справа от диапазона данных будет добавлен новый вспомогательный столбец Half year .

4 . Затем щелкните любую ячейку в сводной таблице, чтобы активировать панель Поля сводной таблицы , перетащите поле Half Year в Строки раздел, а затем перетащите другие поля в нужные разделы по мере необходимости. Теперь вы увидите, что сводная таблица создана, а даты сгруппированы по полугодиям каждого года. См. Снимок экрана:

Нажмите, чтобы загрузить Kutools for Excel и бесплатная пробная версия сейчас!

Простая демонстрация

Пользователи создают сводные таблицы для анализа, суммирования и представления большого объема данных. Такой инструмент Excel позволяет произвести фильтрацию и группировку информации, изобразить ее в различных разрезах (подготовить отчет).

Исходный материал – таблица с несколькими десятками и сотнями строк, несколько таблиц в одной книге, несколько файлов. Напомним порядок создания: «Вставка» – «Таблицы» – «Сводная таблица».

А в данной статье мы рассмотрим, как работать со сводными таблицами в Excel.

Как сделать сводную таблицу из нескольких файлов

Первый этап – выгрузить информацию в программу Excel и привести ее в соответствие с таблицами Excel. Если наши данные находятся в Worde, мы переносим их в Excel и делаем таблицу по всем правилам Excel (даем заголовки столбцам, убираем пустые строки и т.п.).

Дальнейшая работа по созданию сводной таблицы из нескольких файлов будет зависеть от типа данных. Если информация однотипная (табличек несколько, но заголовки одинаковые), то Мастер сводных таблиц – в помощь.

Мы просто создаем сводный отчет на основе данных в нескольких диапазонах консолидации.

Гораздо сложнее сделать сводную таблицу на основе разных по структуре исходных таблиц. Например, таких:

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

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

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

1. В ячейке-мишени (там, куда будет переноситься таблица) ставим курсор. Пишем = — переходим на лист с переносимыми данными – выделяем первую ячейку столбца, который копируем. Ввод. «Размножаем» формулу, протягивая вниз за правый нижний угол ячейки.



2. По такому же принципу переносим другие данные. В результате из двух таблиц получаем одну общую.



3. Теперь создадим сводный отчет. Вставка – сводная таблица – указываем диапазон и место – ОК.

Открывается заготовка Сводного отчета со Списком полей, которые можно отобразить.

Покажем, к примеру, количество проданного товара.

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



Детализация информации в сводных таблицах

Из отчета (см.выше) мы видим, что продано ВСЕГО 30 видеокарт. Чтобы узнать, какие данные были использованы для получения этого значения, щелкаем два раза мышкой по цифре «30». Получаем детальный отчет:

Как обновить данные в сводной таблице Excel?

Если мы изменим какой-либо параметр в исходной таблице либо добавим новую запись, в сводном отчете эта информация не отобразится. Такое положение вещей нас не устраивает.

Обновление данных:

Курсор должен стоять в любой ячейке сводного отчета.

Либо:

Правая кнопка мыши – обновить.

Чтобы настроить автоматическое обновление сводной таблицы при изменении данных, делаем по инструкции:

1. Курсор стоит в любом месте отчета. Работа со сводными таблицами – Параметры – Сводная таблица.



2. Параметры.



3. В открывшемся диалоге – Данные – Обновить при открытии файла – ОК.

Изменение структуры отчета

Добавим в сводную таблицу новые поля:

1. На листе с исходными данными вставляем столбец «Продажи». Здесь мы отразим, какую выручку получит магазин от реализации товара. Воспользуемся формулой – цена за 1 * количество проданных единиц.



2. Переходим на лист с отчетом. Работа со сводными таблицами – параметры – изменить источник данных. Расширяем диапазон информации, которая должна войти в сводную таблицу.

Если бы мы добавили столбцы внутри исходной таблицы, достаточно было обновить сводную таблицу.

После изменения диапазона в сводке появилось поле «Продажи».

Как добавить в сводную таблицу вычисляемое поле?

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

Это виртуальный столбец, создаваемый в результате вычислений. В нем могут отображаться средние значения, проценты, расхождения. То есть результаты различных формул. Данные вычисляемого поля взаимодействуют с данными сводной таблицы.

Инструкция по добавлению пользовательского поля:

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



2. Работа со сводными таблицами – Параметры – Формулы – Вычисляемое поле.



3. В открывшемся меню вводим название поля. Ставим курсор в строку «Формула». Инструмент «Вычисляемое поле» не реагирует на диапазоны. Поэтому выделять ячейки в сводной таблице не имеет смысла. Из предполагаемого списка выбираем категории, которые нужны в расчете. Выбрали – «Добавить поле». Дописываем формулу нужными арифметическими действиями.



4. Жмем ОК. Появились Остатки.

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

Для примера посчитаем расходы на товар в разные годы. Сколько было затрачено средств в 2012, 2013, 2014 и 2015. Группировка по дате в сводной таблице Excel выполняется следующим образом. Для примера сделаем простую сводную по дате поставки и сумме.

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

В открывшемся диалоге задаем параметры группировки. Начальная и конечная дата диапазона выводятся автоматически. Выбираем шаг – «Годы».

Получаем суммы заказов по годам.

Скачать пример работы

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