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

1. Изменение формата номера поля дней не работает
2. Решение № 1 - Не используйте группы дат
3. Автоматическая группировка полей даты
4. Решение №2. Изменение имен элементов сводки с помощью VBA
5. Скачать файл
6. Макрос форматирования поля Изменить дни
7. Как работает макрос
8. Изменить макрос форматирования номеров сгруппированных элементов
9. Окончательный вердикт

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

Уровень умения: Средний

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

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

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

Зачем?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Скачать файл

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

Форматирование номера группы полей в поле даты в сводной таблице Macro.xlsm (54,2 КБ)

Макрос форматирования поля Изменить дни

Sub Change_Days_Field_Formatting () 'Изменить форматирование поля поля Дни' для поля даты Сгруппированной сводной таблицы. 'Источник: https://www.excelcampus.com/pivot-tables/grouped-date-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 ) 'Установить имена обратно в их исходное имя по умолчанию Для каждого pi In pt.PivotFields (sDaysField) .PivotItems' Обходит первый и последний элементы "<01.01.2015" ... If Left (pi.Name, 1) <> "<" And Left (pi.Name, 1) <> ">" Тогда pi.Name = pi.SourceName End If Next pi 'Установить имена в произвольном формате чисел Для каждого pi В 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

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

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

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

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

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

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

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

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

Sub Change_Grouped_Field_Number_Formatting () 'Изменить форматирование номера поля сгруппированных чисел' Источник: https://www.excelcampus.com/pivot-tables/grouped-date-field-formatting/ Dim pt As PivotTable Dim pi As PivotItem Dim sGroup ( ) As String Dim sGroupName As String 'ВАЖНО: Измените следующее имя поля' сгруппированного числа 'в области строк или столбцов. Const sGroupedField As String = "Количество" Const sNumberFormat As String = "$ #, ###" 'Установить ссылку на первую сводную таблицу на листе' Это можно изменить для ссылки на имя сводной таблицы 'Set pt = ActiveSheet.PivotTables ("PivotTable1") Set pt = ActiveSheet.PivotTables (1) 'Установить имена обратно в их исходное имя по умолчанию Для каждого pi In pt.PivotFields (sGroupedField) .PivotItems' Обходит первый и последний элементы "<01.01.2015 "... Если осталось (pi.Name, 1) <>" <"И осталось (pi.Name, 1) <>"> "Тогда pi.Name = pi.SourceName End If Next pi 'Установить имена для Пользовательский формат чисел Для каждого числа в pt.PivotFields (sGroupedField) .PivotItems If Left (pi.Name, 1) = "<" Или Left (pi.Name, 1) = ">" Тогда sGroupName = "<" & Format ( Mid (pi.Name, 2, Len (pi.Name)), sNumberFormat) Остальное sGroup = Split (pi.Name, "-") sGroupName = Format (sGroup (0), sNumberFormat) & "-" & Format (sGroup (1), sNumberFormat) End If If sGroupName <> "" Тогда pi.Name = sGroupName Next pi End Sub

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

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

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

Похожие

Комментарии