Сводная таблица Excel [Формат дат]
Почему сводная таблица не воспринимает формат даты ДД. ММ. ГГГГ?
Есть таблица с датами и суммами, мне необходимо сформировать сводную таблицу именно по дням.
При формировании таблицы у меня автоматически заменяются дни на месяца. как это можно исправить.
В исходных данных:
А Дата — В Сумма
01.02.2013 — 10
05.02.2013 — 15
10.02.2013 — 25
01.05.2015 — 2
03.05.2015 — 3
06.06.2018 — 8
09.08.2019 — 15
Изменение формата даты для сгруппированных дат в сводной таблице
Допустим, у вас есть допустимое поле даты в ваших необработанных данных с такими датами, как 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 соответствовал моим необработанным данным.
Как изменить в сводной таблице числовой формат, заданный по умолчанию
Для числовых значений, отображаемых в области значений сводной таблицы, следует использовать подходящий числовой формат. Не следует полагать, будто можно просто перенести числовой формат исходной таблицы в созданную на ее основе сводную таблицу.
Для отображения значений дохода с нулями после десятичной запятой и пробелом в качестве разделителя разрядов используется следующий код:
PT.PivotFields("Сумма по полю Доход").NumberFormat = "# ##0"
Некоторые компании обслуживают клиентов, которые совершают закупки на тысячи или миллионы долларов. Для того чтобы вывести числа в тысячах, используйте запятую или пробел в числовом формате либо добавьте букву К для указания вывода чисел в тысячах.
РТ.PivotFields("Сумма по полю Доход").Number Format = "# ##,К
Сокращение до тысяч диктуется внешними обстоятельствами. Если вы работаете в сравнительно молодой компьютерной фирме, в которой все используют К как разделитель тысяч, то вам повезло, поскольку корпорация Microsoft приветствует использование такого сокращения. Использование таких сокращений осталось и в новой версии пакета Office 2013. Однако если вы работаете в компании со столетним стажем, где для тысяч используется сокращение М, а ММ соответствует миллионам, то вам придется прибегнуть к хитрости. Перед символом М введите обратную косую черту.
PT.PivotFields("Сумма по полю Доход").NumberFormat = "# ##0,\М"
В качестве альтернативы можете поместить М в двойные кавычки. Чтобы ввести двойные кавычки в строке VBA-кода, заключенной в кавычки, следует задать две последовательные кавычки. Чтобы установить числовой формат в десятках миллионов в виде # ##0.0,, "ММ", необходимо ввести следующую строку кода:
Рис. 12.12. После заполнения пустых ячеек и сортировки осталось удалить несколько промежуточных итогов и лишних подписей
Три кавычки в конце строки введены без ошибки. Две кавычки имитируют ввод одной кавычки в записи числового формата, а последняя кавычка завершает строку VBA-кода. На рис. 12.12 показана сводная таблица с заполненными пустыми ячейками, с выводимыми в тысячах величинами и полями Категория оборудования и Регион, отсортированными по убыванию.