Изменение формата даты для сгруппированных дат в сводной таблице
предположим, у вас есть законное поле даты в ваших необработанных данных с датами, такими как 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 соответствовал моим необработанным данным.
Изменение формата даты для сгруппированных дат в сводной таблице
Допустим, у вас есть допустимое поле даты в ваших необработанных данных с такими датами, как 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.
Если вы выберете столбцы, в которых разбита дата (внутри сводной таблицы — годы, квартал и т. Д.), И перейдите к разгруппировке данных. Это приведет вас к формату даты.
У меня была такая же проблема. Я щелкнул правой кнопкой мыши значение, для которого я хочу изменить представление сводной таблицы, и выбрал опцию «Разгруппировать». Это возвращает формат просмотра регулятора. Я надеюсь это тебе поможет.
Excel: как сортировать сводную таблицу по дате
Часто вам может понадобиться отсортировать строки в сводной таблице в Excel по дате.
К счастью, это легко сделать с помощью параметров сортировки в раскрывающемся меню в столбце « Ярлыки строк» сводной таблицы.
В следующем примере показано, как именно это сделать.
Пример: сортировка сводной таблицы по дате в Excel
Предположим, у нас есть следующий набор данных в Excel, который показывает количество продаж в разные даты:
Перед созданием сводной таблицы для этих данных щелкните одну из ячеек в столбце «Дата» и убедитесь, что Excel распознает ячейку как формат даты :
Затем мы можем выделить диапазон ячеек A1:B10 , затем щелкнуть вкладку « Вставка » на верхней ленте, затем щелкнуть « Сводная таблица» и вставить следующую сводную таблицу, чтобы суммировать общий объем продаж на каждую дату:
Поскольку Excel распознает формат даты, он автоматически сортирует сводную таблицу по дате от самой старой до самой новой даты.
Однако, если мы хотим отсортировать от самого нового к самому старому, мы можем щелкнуть стрелку раскрывающегося списка рядом с метками строк и нажать « Сортировать от нового к самому старому»:
Строки в сводной таблице будут автоматически отсортированы от самых новых к самым старым:
Чтобы выполнить сортировку от самой старой даты к самой новой, просто щелкните стрелку раскрывающегося списка рядом с Метками строк еще раз, а затем нажмите Сортировать от самой старой до самой новой .
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:
Как изменить формат даты в сводной таблице excel
Как правило, даты на оси диаграммы или сводной диаграммы отображаются как » 2014-02-15 «. В некоторых случаях может потребоваться игнорировать год в датах, например» 2/15 «, или оставьте месяц только в таких датах, как» фев «, есть ли у вас идея, как это сделать? В этой статье представлены два метода изменения формата даты на оси диаграммы или сводной диаграммы в Excel.
- Изменить формат даты на оси сводной диаграммы в Excel
- Изменить формат даты на оси нормальной диаграммы в Excel
Изменить формат даты на оси сводной диаграммы в Excel
Предположим, вы создали сводную диаграмму, как показано ниже, и вы можете изменить формат даты на оси этой сводной диаграммы следующим образом: 
1. На сводной диаграмме щелкните правой кнопкой мыши значок Время кнопку подано и выберите Настройки полей из контекстного меню.
Внимание: В Excel 2007 вы не можете найти кнопку поля на сводной диаграмме, но можете щелкнуть Время Подан в Поля осей (категории) раздел Список полей сводной таблицы панель и выберите Сохраненные настройки из раскрывающегося списка (см. снимок экрана ниже). И этот метод также хорошо работает в Excel 2010 и 2013. Смотрите скриншоты выше.
2. В появившемся диалоговом окне «Параметры поля» щелкните значок Формат номера кнопку. 
3. Теперь вы попадаете в диалоговое окно «Формат ячеек», щелкните, чтобы выделить На заказ в Категория поле, а затем введите код формата в поле Тип и нажмите OK кнопка. (Внимание: Если вы хотите показать 2014-01-03 как 1/3, введите м / д в Тип коробка; если вы хотите показать 2014-01-03 как Янв, введите ммм в Тип коробка.) 
4, Нажмите OK в диалоговом окне «Параметры поля». Затем вы увидите, что даты на оси сводной диаграммы сразу меняются на указанный формат. Смотрите ниже снимки экрана:
Изменить формат даты на оси диаграммы в Excel
Например, есть диаграмма, показанная на снимке экрана ниже, и чтобы изменить формат даты по оси нормальной диаграммы в Excel, вы можете сделать следующее: 
1. Щелкните правой кнопкой мыши по оси, где вы измените формат данных, и выберите Ось формата из контекстного меню. 
2. Продолжайте, основываясь на вашей версии Microsoft Excel:
(1) На панели оси формата Excel 2013 разверните Число группы на Параметры оси вкладка, введите м / д or ммм или другие в Код формата и нажмите Добавить кнопку.
(2) В диалоговом окне оси формата Excel 2007 и 2010 щелкните Число в левой строке введите m / d или mmm или другие коды в поле Код формата и нажмите Добавить кнопку.
Excel 2013 и более поздние версии: 
Excel 2007 и 2010: 
Внимание: Если вы введете м / д в Код формата поле, даты на выбранной оси будут изменены на формат 1/1, 2/1,…; если вы войдете ммм в Код формата поле, даты на оси будут изменены на формат января, февраля,….
3. Закройте панель / диалоговое окно «Ось формата». Затем вы увидите, что даты на оси диаграммы сразу меняются на определенный формат.