Изменение формата даты для сгруппированных дат в сводной таблице
Допустим, у вас есть допустимое поле даты в ваших необработанных данных с такими датами, как 1/1/2015. Затем вы создаете сводную таблицу с полем даты в метках строк.
Теперь предположим, что вы хотите отображать результаты по месяцам, поэтому щелкните правой кнопкой мыши «Поле группы» и выберите группировку по месяцам.
Теперь ваши данные отображаются в месяцах в формате Ммм. Как бы вы поменяли формат Мммм? Или что, если вы хотите отобразить его как число (например, Ян отображается как 1)
Как насчет дат, которые имеют отметки времени и группируются по дням. В сводной таблице дата будет отображаться как Д-Ммм. Что, если я хочу ММ / ДД / ГГГГ?
Да, я уже пробовал изменить его, щелкнув правой кнопкой мыши -> настройки поля -> формат числа. Это не сработало.
9 ответов
Начиная с Excel 2016, нет возможности изменить способ, которым Excel автоматически форматирует сгруппированные даты в сводных таблицах.
Обходной путь — создать новое поле / столбец в исходном файле данных с желаемым форматом и использовать его в сводной таблице.
Я выбрал один из заголовков столбца и нажал кнопку «Разгруппировать». Моя проблема заключалась в том, что в сводной таблице были заголовки mmm, и я хотел, чтобы m / d / yyyy соответствовали моим необработанным данным.
Я пробовал две вещи:
Я просто как обычно поменял формат ячеек.
Выделено более одного элемента при группировании (с помощью Control + щелчок по окнам или Command + щелчок по Mac) источник
Ps: Я указал, как выбрать более одного элемента, потому что это не очень интуитивно понятно.
Мне удалось решить эту проблему, манипулируя внутренним OOXML (Office Open XML) документа Excel. Если вы не знаете, как открыть документ Excel (для просмотра документов xml, из которых он состоит внутри), обратитесь к одной из этих ссылок:
«Лучший способ» Используйте расширение Visual Studio (редактор пакетов OOXML) для непосредственного открытия / редактирования файла Excel: http://www.ericwhite.com/blog/open-xml-installation-center/
Или используйте «более простое» расширение для Chrome (перетащите на него файл Excel): https://chrome.google.com/webstore/detail/ooxml-tools/bjmmjfdegplhkefakjkccocjanekbapn?hl=en (не забудьте загрузить новый файл после сохранения в нем изменений)
Или «наименее рекомендованный» способ
Независимо от вашего метода, теперь перейдите к «PivotCache» для вашей сводной таблицы. В моем «распакованном файле excel» путь к файлу: Root> xl> pivotCache> pivotCacheDefinition1.xml
Моя копия содержит около 800 строк xml, и я полагаю, что это может быть намного больше, но сосредоточьтесь только на той части, которую вы хотите переформатировать. (убедитесь, что вы преобразили свой xml, чтобы это не одна длинная сжатая строка, если вы используете Visual Studio Ctrl + K, Ctrl + D)
Найдите атрибут: groupBy = «months»
Мой образец находит
. И все, что вам нужно сделать, это заменить «Янв» на «1» или любое другое строковое представление для каждого месяца!
Сделать еще один шаг вперед, но все же ответить на вопрос о том, как отформатировать группу по столбцу, — отформатировать groupBy = «days», что приведет к
Список можно продолжать и продолжать, если быть точным, то 368 элементов. 365 дней, плюс первый и последний элементы, включая 29 февраля. Если вы пытаетесь отформатировать строку, сгруппированную по дням, это элементы, которые нужно изменить. то есть:
Вы можете изменить их, например, на китайский (или что угодно, вам просто нужно 366 замен элементов) (опять же, оставьте первый и последний элемент в покое).
Так что «1 января» «2 января» может стать
Вы относитесь к ним точно так же, как я показывал ранее для Месяцев. Дни, однако, утомительнее делать вручную, так как вам нужен элемент на каждый день, поэтому я создал функцию C #, которую вы можете изменить, чтобы быстро выплевывать элементы в файл (для копирования и вставки в ваш .xml)
Важно: если вы используете эту функцию, чтобы вывести свои дни, добавьте вручную 29 февраля, если в исходном наборе, который вы вставляете, есть 29 февраля (как это было у меня).
Кажется, есть новая опция «Групповое поле» (по какой-то причине помеченная «7». Нажмите на нее, и вы получите варианты переключателя для Месяца / Дня / Часа и т. Д. Выберите свои предпочтения и нажмите OK. Надеюсь, это сработает для вас тоже!
Будем признательны за то, что можно и чего нельзя делать.
Кажется, есть новая опция «Групповое поле» (по какой-то причине помеченная «7». Нажмите на нее, и вы получите варианты переключателя для Месяца / Дня / Часа и т. Д. Выберите свои предпочтения и нажмите OK. Надеюсь, это сработает для вас тоже!
Я просто переформатировал поле даты и времени, которое было источником данных для сводной таблицы (которое было дд / мм / ггг чч: мм: сс) в текст (ddddd.ttttt), а затем, когда я обновил сводную таблицу, оно появилось в формат даты и времени, который я выбрал.
Ответ Бариса Сари частично верен. Вам нужно разгруппировать даты, что затем позволит вам установить любой формат даты, который вы хотите.
Выберите все даты в сводной таблице, затем щелкните правой кнопкой мыши и выберите «Разгруппировать». Затем щелкните правой кнопкой мыши еще раз по своему выбору и нажмите «Форматировать ячейки». Первая вкладка в открывшемся окне относится к числам.
Это работало в Excel 2016 и 2010.
Если вы выберете столбцы, в которых разбита дата (внутри сводной таблицы — годы, квартал и т. Д.), И перейдите к разгруппировке данных. Это приведет вас к формату даты.
У меня была такая же проблема. Я щелкнул правой кнопкой мыши значение, для которого я хочу изменить представление сводной таблицы, и выбрал опцию «Разгруппировать». Это возвращает формат просмотра регулятора. Я надеюсь это тебе поможет.
Изменение формата даты для сгруппированных дат в сводной таблице
предположим, у вас есть законное поле даты в ваших необработанных данных с датами, такими как 1/1/2015. Затем вы создаете сводную таблицу с полем даты в метках строк.
теперь предположим, что вы хотите показать результаты по месяцам, поэтому щелкните правой кнопкой мыши «поле группы» и выберите, чтобы сгруппировать его по месяцам.
теперь ваши данные отображаются в месяцах в формате Mmm. Как бы вы изменили его формат Mmmm? Или что, если вы хотите отобразить его как число (т. е. Jan отображается как 1)
Как насчет дат, которые имеют метки времени, и вы группируетесь по дням. В сводной таблице дата будет отображаться как D-Mmm. Что делать, если я хочу MM/DD/YYYY?
Да, я уже пробовал изменить его, щелкнув правой кнопкой мыши -> настройки поля — > формат номера. Это не сработало.
7 ответов
У меня была та же проблема. То, что я сделал,-это щелкнуть правой кнопкой мыши по значению, для которого я хочу изменить представление в сводной таблице и выбрать опцию «разгруппировать». Это возвращает формат представления reguler. Надеюсь, это вам поможет.
Если вы выбираете столбцы, в которых дата разбивается (в пределах сводных лет, квартала и т. д.) и идут данные-разгруппироваться. Это приведет вас к формату даты.
начиная с Excel 2016, нет способа изменить способ, которым Excel автоматически форматирует сгруппированные даты в сводных таблицах.
обходным путем является создание нового поля / столбца в исходном файле данных с требуемым форматом и использование его в сводной таблице.
ответ Бариса Сари частично верен. Вам нужно разгруппировать даты, что позволяет вам установить любой формат даты, который вы хотите.
выберите все даты в сводной таблице, затем щелкните правой кнопкой мыши и выберите Ungroup. Затем щелкните правой кнопкой мыши снова на вашем выборе и нажмите Format Cells. Первая вкладка в открывшемся окне относится к номерам.
это работало в Excel 2016 и 2010.
Я просто переформатировал поле даты и времени, которое было источником данных для сводной таблицы (которая была dd/mm/yyy hh:mm:ss) в текст (ddddd.ttttt), а затем, когда я обновил сводную таблицу, она появляется в выбранном мной формате даты и времени.
Я пробовал две вещи:
Я просто изменил формат ячеек, как обычно.
выбрано более одного элемента в группировке (с помощью Control+click on windows или Command+click on mac) источник
ps.: Я указал, как выбрать более одного элемента, потому что это не очень интуитивно.
Я выбрал один из заголовков столбцов и нажал ungroup. Моя проблема заключалась в том, что сводная таблица имела заголовки mmm, и я хотел, чтобы m/d/yyyy соответствовал моим необработанным данным.
Форматирование даты в Excel
Итог: узнаете, как изменить форматирование даты для сгруппированного поля в сводной таблице.
Уровень мастерства: Средний
Изменение формата для дат не работает
Когда мы группируем поле «Дата» в сводной таблице с помощью функции «Группировать», форматирование чисел для поля «День» фиксируется. Он имеет следующий формат «день-месяц» или «d-ммм».
Если мы попытаемся изменить числовой формат поля День/Дата, это не сработает. Ничего не меняется, когда мы заходим в Настройки поля> Числовой формат и меняем числовой формат на пользовательский или формат даты.
Форматирование чисел не работает, потому что элемент сводки — это фактически текст, а НЕ дата.
Когда мы группируем поля, функция группирования создает элемент Дни для каждого дня одного года. Он сохраняет название месяца в именах полей Day, и фактически это группа номеров дней (1-31) для каждого месяца.
На самом деле можно увидеть этот список текстовых элементов в файле pivotCacheDefinition.xml. Чтобы увидеть, что вы можете изменить расширение файла Excel на .zip и перейти к папке PivotCache.
Поскольку это текстовые элементы, представляющие дни года, мы не сможем изменить форматирование ячеек непосредственно в Excel. Однако есть несколько обходных путей.
Решение № 1 — Не используйте группы дат
Первым решением является создание полей (столбцов) в диапазоне исходных данных с различными группами по году, кварталу, месяцу, дням и т.д
Я подробно объясняю это в своей статье «Группировка дат в сводной таблице». Источник данных.
Использование собственных полей из исходных данных для разных групп дат даст вам контроль над форматированием чисел поля в сводной таблице. Вы также можете создать таблицу календаря с группировками, если вы используете Power Pivot.
Автоматическая группировка полей даты
Если вы используете Excel 2016 (Office 365), то поле даты автоматически группируется при добавлении его в сводную таблицу.
Разгруппировать поле даты:
- Выберите ячейку внутри сводной таблицы в одном из полей даты.
- Нажмите кнопку «Разгруппировать» на вкладке «Анализ» ленты.
Автоматическая группировка является настройкой по умолчанию, которую можно изменить. См. мою статью «Даты группирования в сводной таблице». Даты группирования VERSUS приведены в исходных данных, чтобы узнать больше.
Как только поле даты будет разгруппировано, вы можете изменить форматирование поля.
Чтобы изменить форматирование чисел в несгруппированном поле Дата:
- Щелкните правой кнопкой мыши ячейку в поле даты сводной таблицы.
- Выберите настройки поля …
- Нажмите кнопку «Числовой формат».
- Измените форматирование даты в окне «Формат ячеек».
- Нажмите ОК и ОК.
Опять же, это работает только для полей, которые НЕ сгруппированы. Если вы снова сгруппируете поле после изменения форматирования, форматирование элементов в поле «Дни» изменится на «1 января».
Решение №2. Изменение имен элементов сводки с помощью VBA
Если вы действительно хотите использовать функцию Group Field, то мы можем использовать макрос для изменения имен элементов сводки. Создается впечатление, что изменилось форматирование даты, но на самом деле меняется текст в каждом названии элемента сводки.
Следующий макрос перебирает все сводные элементы сгруппированного поля «Дни» и изменяет форматирование чисел на пользовательский формат. По умолчанию я установил «m/d», но вы можете изменить его на любой формат даты для месяца и дня. Просто помните, что элемент НЕ будет содержать год, так как элемент не является фактической датой.
Скачать файл
Загрузите файл Excel, который содержит макрос.
Pivot Table Date Field Group Number Formatting Macro.xlsm (54.2 KB)
Макрос форматирования поля «Дни»
Как работает макрос
Макрос сначала зацикливает элементы сводки в поле Дни, чтобы восстановить имя элемента сводки в его исходное имя по умолчанию. Два разных элемента не могут иметь одинаковое имя. Так что это должно предотвратить любые ошибки при изменении или попытке разных форматов.
Второй цикл меняет каждый элемент сводки на новый формат. Он использует функцию DateValue для изменения названия элемента сводки «1-Jan» на дату. Затем он использует функцию «Формат», чтобы изменить форматирование даты на текст. По умолчанию используется формат «m / d». Это может быть изменено на другой формат с месяцем и днем. Каждый элемент должен быть уникальным, поэтому вы можете использовать месяц и день в названии элемента.
Возможно, вы могли бы разделить этот макрос на два макроса и запускать только первый цикл сброса по мере необходимости. Макрос занимает около 15 секунд для запуска на моем компьютере из-за всех циклов. Но это не тот, который вам придется часто бегать.
Также важно упомянуть, что это выполняется на элементах сводки, а не на сводном кеше. Таким образом, только что названные элементы будут изменены только в сводной таблице, на которой вы запускаете макрос.
Макрос форматирования сгруппированных элементов
Был задан большой вопрос в комментариях об изменении форматирования для сгруппированных чисел. Это та же проблема, что и для групп дат. Имена групп — это текст, а не цифры. Тем не менее, мы можем использовать макрос, чтобы изменить их.
Вот код макроса. Вам просто нужно изменить значение константы sGroupField вверху на имя вашего сгруппированного поля. При необходимости вы также можете изменить формат чисел в sNumberFormat.
Я надеюсь, что информация была полезной. Я бы предложил использовать решение № 1, если вы не хотите использовать функцию группы.
Пожалуйста, оставьте комментарий ниже с любыми вопросами или предложениями о том, как мы можем улучшить это. Спасибо! ��
Как изменить в сводной таблице числовой формат, заданный по умолчанию
Для числовых значений, отображаемых в области значений сводной таблицы, следует использовать подходящий числовой формат. Не следует полагать, будто можно просто перенести числовой формат исходной таблицы в созданную на ее основе сводную таблицу.
Для отображения значений дохода с нулями после десятичной запятой и пробелом в качестве разделителя разрядов используется следующий код:
PT.PivotFields("Сумма по полю Доход").NumberFormat = "# ##0"
Некоторые компании обслуживают клиентов, которые совершают закупки на тысячи или миллионы долларов. Для того чтобы вывести числа в тысячах, используйте запятую или пробел в числовом формате либо добавьте букву К для указания вывода чисел в тысячах.
РТ.PivotFields("Сумма по полю Доход").Number Format = "# ##,К
Сокращение до тысяч диктуется внешними обстоятельствами. Если вы работаете в сравнительно молодой компьютерной фирме, в которой все используют К как разделитель тысяч, то вам повезло, поскольку корпорация Microsoft приветствует использование такого сокращения. Использование таких сокращений осталось и в новой версии пакета Office 2013. Однако если вы работаете в компании со столетним стажем, где для тысяч используется сокращение М, а ММ соответствует миллионам, то вам придется прибегнуть к хитрости. Перед символом М введите обратную косую черту.
PT.PivotFields("Сумма по полю Доход").NumberFormat = "# ##0,\М"
В качестве альтернативы можете поместить М в двойные кавычки. Чтобы ввести двойные кавычки в строке VBA-кода, заключенной в кавычки, следует задать две последовательные кавычки. Чтобы установить числовой формат в десятках миллионов в виде # ##0.0,, "ММ", необходимо ввести следующую строку кода:
Рис. 12.12. После заполнения пустых ячеек и сортировки осталось удалить несколько промежуточных итогов и лишних подписей
Три кавычки в конце строки введены без ошибки. Две кавычки имитируют ввод одной кавычки в записи числового формата, а последняя кавычка завершает строку VBA-кода. На рис. 12.12 показана сводная таблица с заполненными пустыми ячейками, с выводимыми в тысячах величинами и полями Категория оборудования и Регион, отсортированными по убыванию.