Как в excel сделать группировку по месяцам?

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

Несмотря на то что большая часть операций подведения итогов и вычисления показателей выполняется с использованием стандартных команд сводных таблиц, иногда возникают ситуации, в которых требуется дополнительная группировка данных. [1] Например, исходные финансовые данные, как правило, фиксируются с указанием даты сделки (рис. 1), в отчете желательно представить их не по каждой дате отдельно (рис. 2а), а сгруппированными по месяцам (рис. 2б). Команда Группировать вкладки Анализ позволяет легко и быстро консолидировать даты сделок в группы, основанные на месяцах или кварталах. Впоследствии вы сможете суммировать данные в этих группах таким же образом, как это выполняется в полях сводной таблицы.

Рис. 1. Исходные данные содержат список сделок с указанием конкретной даты

Скачать заметку в формате Word или pdf, примеры в формате Excel

Рис. 2. Отчет, сведенный по датам: (а) данные двух лет продаж занимают более 500 столбцов; (б) после группировки по месяцам отчет существенно удобнее

Группировка полей дат

Для группировки полей дат выделите заголовок поля дат или любую ячейку с датой. Например, на рис. 2а выделите одну из ячеек: В3, В4, С4, D4… На контекстной вкладке ленты Анализ в области Группировать щелкните на кнопке Группировка по полю. Если поле содержит информацию о датах, откроется диалоговое окно Группирование (рис. 3). Обратите внимание, что исходные данные (см. рис. 1) в колонке Дата заказа должны содержать только даты; даже одна текстовая или незаполненная ячейка (пустая) в исходных данных в столбце Дата заказа не позволит сделать группировку по датам. Если перед группированием вы выделили только одну ячейку, то кнопки Группировка по полю и Группировка по выделенному работают одинаково. Различие проявится только если перед группированием вы выделите несколько ячеек.

Рис. 3. Окно Группирование

Можно группировать данные по секундам, минутам, часам, дням, месяцам, кварталам и годам. По умолчанию выделен вариант Месяцы. Выберите также Дни и Годы и нажмите Ok. Обратите внимание на некоторые особенности группировки данных в итоговой сводной таблице. Во-первых, поля Месяцы и Годы добавлены в список полей (рис. 4). Не позволяйте себя одурачить — ваш источник данных не изменился и никаких новых полей не содержит. Эти поля теперь являются частью кеша сводной таблицы в памяти (подробнее о кеше см. Excel 2013. Создание нескольких сводных таблиц на основе одного источника данных: один кеш или несколько?). Во-вторых, по умолчанию поля Месяцы и Годы автоматически добавляются в макет сводной таблицы. Вы можете работать с ними, как с обычными полями: перетаскивать в другие области или делать неактивными. В-третьих, включайте данные по Дням, чтобы иметь возможность добавить это поле в сводную таблицу. Если вы в окне Группирование оставите только Месяцы, то поле Даты вы будете видеть в списке, вот только оно эквивалентно месяцам, а поле Месяцы не появится. И наконец, в окне Группирование добавляйте поле Годы. Если это сделать, то вы корректно сможете разделить данные по годам (рис. 5а), если же этого не сделать, то данные двух лет наблюдений объединяться в одном столбце (рис. 5б).

Рис. 4. Добавление полей Месяцы и Годы в список полей и макет сводной таблицы

Рис. 5. Группирование: (а) по дням, месяцам и годам; (б) по дням и месяцам

Группировка полей дат по неделям

Диалоговое окно Группирование предлагает настройки группировки по секундам, минутам, часам, дням, месяцам, кварталам и годам. А что делать, если нужно сгруппировать данные по одной или двум неделям или иным промежуткам времени? Это вполне реально.

Прежде всего следует свериться с календарем, чтобы решить, с какого дня должна начинаться неделя: с воскресенья, понедельника или любого на ваш выбор. Например, первый понедельник в 2014-м году – 6 января. Если вы хотите, чтобы данные за первую неделю также отражались, вам следует выбрать в качестве начала отсчета последний понедельник 2013-го года – 30 декабря. Чтобы сгруппировать даты по неделям выделите заголовок или ячейку с датой. Например, на рис. 2 ячейки В3 или В4. Перейдите на контекстную вкладку ленты Анализ и в разделе Группировать щелкните на кнопке Группировка по полю. В диалоговом окне Группирование (рис. 6) выделите только параметр Дни. В результате станет доступным счетчик количество дней. Чтобы создать недельный отчет, установите значение 7. Установить в поле начиная с требуемую дату (в нашем примере – 30.12.13). Нажмите Ok. В результате сгенерируется отчет, отображающий еженедельные объемы продаж, как показано на левой части рис. 6.

Рис. 6. Группировка дат по неделям

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

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

При группировке поля дат по месяцам и годам программа Excel переназначает исходное поле дат для отображения месяцев и добавляет новое поле для отображения лет. Новое поле получает имя Годы. Все довольно просто, если у вас в отчете представлено только одно поле дат. Если вам нужно создать отчет с двумя полями дат (например, дата заказа и дата оплаты) и вы пытаетесь сгруппировать оба поля по месяцам и годам, программа Excel сама установит первому сгруппированному полю имя Годы, а второму — имя Годы2. Чтобы не возникло путаницы, переименуйте поле Годы, прежде чем запустить группирование по второму полу дат.

Группировка числовых полей

Диалоговое окно Группирование, применяемое для числовых полей, позволяет группировать элементы в одинаковые диапазоны. Это может быть полезно при проведении частотного анализа. Сводная таблица на рис. 7 сформирована необычным образом. Здесь в область СТРОКИ помещено поле Доход, а в область ЗНАЧЕНИЕ – поле Заказчик. Поскольку поле Заказчик является текстовым, сводная таблица автоматически подсчитывает их количество, а не сумму.

Рис. 7. Сводная таблица, «заготовка» для проведения частотного анализа

Выделите в столбце А любое число, а затем на вкладке Анализ щелкните на кнопке Группировка по полю. В диалоговом окне Группирование выберите параметры группирования (рис. 8). В рассматриваемом случае группирование начинается с 0 и завершается величиной 25 350 при шаге группирования 5000.

Рис. 8. Частотное распределение на основе группировки заказов в группы по $5000 по полю Доход

Разгруппировка. Создав группу, вы можете разгруппировать её с помощью кнопки Разгруппировать, находящейся на вкладке Анализ. Достаточно выделить ячейку со сгруппированными данными и щелкнуть на этой кнопке. Эта команда также доступна в контекстном меню: выделите ячейку со сгруппированными данными и щелкните правой кнопкой мыши.

Группировка текстовых полей

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

Читать еще:  Как сделать ссылку на ячейку из другого документа в excel?

Для начала создайте отчет, отображающий доход по рынкам сбыта. Держа нажатой клавишу Ctrl, выделите рынки сбыта, на основе которых будет создана новая зона (рис. 9). Перейдите на вкладку Анализ, и щелкните на кнопке Группировка по выделенному.

Рис. 9. Используйте клавишу Ctrl для выделения несмежных ячеек, с тем чтобы сгруппировать их в новую зону

Excel добавит новое поле с именем Рынок сбыта2 (рис. 10). Выделенные на первом шаге рынки сбыта объединились в Группа1. Выделите оставшиеся рынки сбыта и повторно щелкните на кнопке Группировка по выделенному.

Рис. 10. Первая зона создана

Переименуйте не очень красиво звучащие новые поля, добавьте промежуточные итоги, отсортируйте по алфавиту строки по полю Зона продаж, и получите отчет, который не стыдно показать менеджеру (рис. 11).

Рис. 11. Итоговый отчет по новым зонам продаж

[1] Заметка написана на основе книги Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013. Глава 4.

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

Группировка в сводных таблицах (831,4 KiB, 1 018 скачиваний)

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

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

ГРУППИРОВКА ДАТЫ И ВРЕМЕНИ
Если необходимо просмотреть суммарную стоимость предложений по кварталам, то пригодиться группировка по датам.

  1. Выделить любую ячейку нужного поля из области строк или столбцов и щелкнуть правой кнопкой мыши;
  2. Выбрать из контекстного меню пункт Группировать (Group) ;
  3. В поле Начиная с (Starting at) ввести начальную дату для группы;
  4. В поле по (Ending at) ввести конечную дату для группы;
  5. В поле с шагом (By) выбрать диапазон группировки: секунды, минуты, часы, дни, месяцы, кварталы, годы (seconds, minutes, hours, days, months, quarters, years) ;
  6. Нажать OK

ГРУППИРОВКА ЧИСЛОВЫХ ПОЛЕЙ
Может пригодиться для группировки по занятым местам или по ценам предложений. Например, можно отобрать все предложения от 110 000р до 130 000р с шагом 10 000р. В данном случае получим таблицу, в которой будут интересующие предложения из указанного диапазона, разбитые с нужным шагом. Если какие значения превышают указанную сумму(130 000р), то будет отдельная группа: >130000, если меньше:

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

И вроде бы в ячейках даты/числа и все равно. В данном случае следует проверить — а действительно ли числа это числа, а даты — даты? Потому как бывает и так, что выглядят в ячейках данные как числа или даты, а на деле это просто текст. В большинстве случаев Excel подсвечивает такие ячейки зелеными треугольничками в левом верхнем углу:

В этом случае все просто: находим самую первую ячейку с таким треугольничком и выделяем все нижестоящие ячейки(до конца таблицы(Ctrl+Shift+стрелка вниз)). После чего прокручиваем лист обратно к этой ячейке, нажимаем на значок с воскл.знаком левее ячейки и в раскрывшемся меню выбираем Преобразовать в число(дату).

После этого обязательно необходимо перейти в сводную таблицу и обновить её(выделить любую ячейку сводной таблицы→Правая кнопка мыши→Обновить (Refresh) или вкладка Данные (Data) →Обновить все (Refresh all) →Обновить (Refresh) ). Вполне возможно, что это действие придется повторить еще один-два раза.

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

Если после этого группировка все равно отказывается работать — значит где-то еще есть числа/даты, записанные как текст. Но они могут быть не подсвечены зеленым треугольником. Такое поведение часто наблюдается в файлах, выгруженных из 1С или иных программ. Часто побеждают это очень упорным трудом: выделяют ячейку, жмут F2(чтобы войти в режим редактирования ячейки) и Enter. Тогда Excel преобразует дату/число в настоящие дату/число. Но если таких ячеек хотя бы 100 — это уже не на пару минут рутины. Благо, все это можно сделать за пару секунд. Чтобы быстро преобразовать ячейки с датами/числами, записанными как текст в реальные даты/число необходимо:

  • скопировать любую пустую ячейку на листе
  • выделить все ячейки с датами/числами
  • правая кнопка мыши -Специальная вставка (Paste Special) -в окне выбрать Значения (Values) , операция — Сложить (Multiply)
  • ОК

Excel автоматом преобразует даты и числа в нормальные данные. Возможно, придется заново задать формат датам — но это уже совершенно не сложно: правая кнопка мыши —Формат ячеек (Format Cells) -Дата (Date) .
Про другие возможности Специальной вставки можно прочитать в этой статье: Как быстро умножить/разделить/сложить/вычесть из множества ячеек одно и то же число?

ГРУППИРОВКА ТЕКСТОВЫХ ПОЛЕЙ ИЛИ ОТДЕЛЬНЫХ ЭЛЕМЕНТОВ

  1. Выделить ячейку из области строк или столбцов с одним из элементов поля для группировки;
  2. Удерживая CTRL или SHIFT выделить другие элементы (ячейки) этого поля;
  3. Щелкнуть правой кнопкой по любой выделенной ячейке и выбрать из контекстного меню пункт Группировать (Group) или на вкладке Параметры (Options) в группе Группировать (Group) нажать кнопку Группа по выделенному (Group Selection);

  4. При необходимости задать свое имя группе

В полях с уровнями можно группировать только элементы, имеющие одинаковые подуровни. Например, если в поле есть два уровня «Страна» и «Город», нельзя сгруппировать города из разных стран.

ПЕРЕИМЕНОВАНИЕ ГРУППЫ ПО УМОЛЧАНИЮ
При группировке элементов Excel задает имена групп по умолчанию, например Группа1 (Group1) для выбранных элементов или Кв-л1 (Qtr1) для квартала 1(если работаем с датами). Задать группе более понятное имя совсем несложно:

    1. Выделить имя группы;
    2. Нажать клавишу F2;
    3. Ввести новое имя группы.

  1. Выделить группу элементов, которые требуется разгруппировать;
  2. На вкладке Параметры (Options) в группе Группировать (Group) нажать кнопку Разгруппировать (Ungroup) (или щелкнуть правой кнопкой мыши и выбрать из контекстного меню пункт Разгруппировать (Ungroup) ).

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

  1. Для источников данных OLAP (Online Analytical Processing), не поддерживающих инструкцию CREATE SESSION CUBE, группировка элементов невозможна.
  2. При наличии одного или нескольких сгруппированных элементов использовать команду Преобразование в формулы (ПараметрыСервисСредства OLAP) невозможно. Перед использованием этой команды необходимо сначала удалить сгруппированные элементы.
  3. Для быстрой работы c группами данных надо выделить ячейки в области названий строк или столбцов сводной таблицы, щелкнуть правой кнопкой мыши на любой из выделенных ячеек и выбрать Развернуть/Cвернуть (Expand/Collapse)

Так же см.:
[[Общие сведения о сводных таблицах]]
[[Сводная таблица из нескольких листов]]

Читать еще:  Как сделать фильтр по алфавиту в excel?

Статья помогла? Поделись ссылкой с друзьями!

Поиск по меткам

Можно ли добавить дополнительные «Промежуточные итоги» для сводной таблицы, содержащей большую структуру — столбцов.
Так, чтобы эти дополнительные итоги — показывали итоги по каждой структуре.
Например, есть сводная таблица по месяцам продаж (строки) по Магазинам, Маркам, Цветам товара (столбцы).

Хотелось бы увидеть в столбцах: Общие итоги (+), Итоги по Магазину (+), Итоги по Марке (- не дает, только внутри каждого магазина), Итоги по Цвету (+), Итоги по Магазину-Марке(+), Итоги по Марке-Цвету(- не дает), Итоги по Магазину -Цвету (не дает).

Итого 7 итогов: 4 могу сделать, а 3 не получается ( в одной таблице). Приходится делать надстройку поверх Сводной.

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме — добро пожаловать на Форум

Как настроить группировку строк и столбцов в Excel

Редакторы пакета Microsoft Office обладают различными полезными функциями. И Excel не является исключением. Одной из таких функций является группировка данных.

Назначение

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

  • настроить отображение пунктов и подпунктов в документе;
  • оптимизировать пространство документа для упрощения его редактирования;
  • скрывать временно ненужные элементы.

Возможность удобна для реализации списков и упрощения внесения данных в какие-то таблицы.

Настройки группировки

Вопреки визуальному восприятию искать данную возможность нужно не в разделе «разметка». Расположенная там группировка позволяет переносить вложения документа вместе с ячейками. Оптимальное применение этого инструмента – связка ячейки и картинки. Необходимая пользователям группировка находится по следующему пути:

    1. Вкладка «Данные».
    2. Плитка «Структура»
    3. Кнопка «Группировать».

Фактические опции группировки в указанном разделе имеют следующий вид:

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

Кстати, кнопка «Применить стили» позволяет изменять стиль отображения. Удобно, если нужно распределить данные, придав им не только групповое, но и визуальное различие.

Создание группировки

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

  1. Выделяем в документе необходимый параметр.
  2. Открыть «данные» и на плитке «Структура» нажать «Группировать».
  3. Теперь потребуется выбрать одну из необходимых опций.

Группировка по строкам

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

Группировка по столбцам

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

Примечание! Для удобства структуры, не нужно выделять крайний отображаемый элемент. В показанном примере в общую единицу собраны месяца, а вот квартал сохраняется вне разметки. Адреса строк сохраняются вне зависимости от свёрнутости или развернутости структуры.

Вложенные группы

Представляют собой обычные группы. В этом случае структура собирается по принципу снизу-вверх. То есть, сначала объединяются меньшие компоненты, а потом большие. Называют такую структуру многоуровневой группировкой. Следует визуально выделить общий параметр, который находится на верхнем уровне, а потом определить его структурные единицы. В конкретном примере это имеет вид:

Порядок добавления единиц не важен. Главное, чтобы сохранялась развёртка верхней единицы. В конкретном примере порядок создания структуры имел вид: группировка месяцев, после которой они были свёрнуты и сгруппированы уже кварталы. Таким образом, удалось добиться условной единицы «полугодие».

Примечание! В квартал входит 3 месяца, а в полугодие 6. При составлении таблицы для примера — это правило было нарушено. Здесь в квартал входит 4 месяца, а в полугодие 8, что является фактическим нарушением принятых норм.

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

Удаление группировки

В примере выше допущено несколько грубых ошибок. Поэтому требуется удалить группировки и исправить ошибки. Удаляются эти структурные компоненты следующим образом:

  1. Выделить необходимый диапазон.
  2. Нажать разгруппировать.
  3. Выбрать «колонны» или «строки».
  4. Повторить необходимое количество раз.

Теперь можно внести необходимые исправления и повторить процедуру объединения компонентов для отображения.

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

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

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

Автоматический. Если элементы являются числами или датами, можете определить способ группировки в специальном диалоговом окне Группировка. Для этого выделите любой элемент в заголовках строк или столбцов и выберите команду Работа со сводными таблицамиПараметрыГруппироватьГруппировка по выделенному. Также можете щелкнуть правой кнопкой и выбрать в контекстном меню пункт Группировать. В любом случае будет открыто диалоговое окно Группировка.

Пример ручной группировки

На рисунке показана сводная таблица, созданная из списка сотрудников, находящегося в столбцах А:С. В этих столбцах содержатся поля заголовков Работник, Регион и Пол. Сводная таблица, содержащаяся в столбцах Е:Н, отображает список работников в каждом из регионов.

Наша задача – создать две группы регионов: западный (города Москва, Калуга и Тверь) и восточный (города Казань, Тула и Пермь). Для создания первой группы, удерживая клавишу , выделите города Москва, Калуга и Тверь.

После этого щелкните правой кнопкой и выберите в контекстном меню пункт Группировать. Ту же операцию повторите и для второй группы. На рисунке показаны результаты такой группировки.

Можно создать любое количество групп, и даже объединить их в более общие группы.

Просмотр сгруппированных данных

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

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

Примеры автоматической группировки

Когда некоторое поле содержит числа, даты или время, Excel может автоматически создавать группы. Два примера, приведенные в настоящем разделе, демонстрируют автоматическую группировку.

Группировка по дате

На рисунке показана часть обычной таблицы, содержащей два поля: Дата и Продажи. Эта таблица содержит 730 строк и охватывает диапазон дат от 1 января 2009 года до 31 декабря 2010 года. Требуется обобщить данные о продажах по месяцам.

Читать еще:  Как сделать таблицу табель учета рабочего времени в excel?

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

Для группировки элементов по месяцам выделите любую дату и выберите команду Работа со сводными таблицамиПараметрыГруппироватьГруппировка по полю или щелкните правой кнопкой на поле и выберите в контекстном меню команду Группировать. Откроется диалоговое окно, показанное на рисунке.

В поле с шагом выделите элементы Месяцы и Годы; при этом проверьте правильность начальной и конечной дат. Щелкните на кнопке ОК. Элементы Дата в сводной таблице будут сгруппированы по годам и месяцам, после чего таблица примет вид, показанный на рисунке.

Примечание

Если в диалоговом окне Группирование выбрать только элемент Месяцы, одинаковые месяцы разных лет будут объединены. К примеру, в строке Январь будут сведены данные о продажах за два января 2005 и 2006 года.

На рисунке ниже показано еще одно представление тех же данных, однако в этом случае группировка выполнена по годам и кварталам.

Группировка по времени

На рисунке в столбцах А и В рабочего листа показаны данные, считанные с некоторого датчика на протяжении суток с интервалом в одну минуту. Всего данная таблица содержит 1440 строк. В сводной таблице эти данные обобщены по часам.

Для создания сводной таблицы, показанной на рисунке, были использованы следующие настройки.

• В области значений содержится три экземпляра поля Чтение. Использовано диалоговое окно Параметры поля значений для обобщения первого экземпляра поля по среднему, второго – по минимальному и третьего – по максимальному значению.
• Поле Время помещено в область Названия строк; при этом в диалоговом окне Группирование выполнена группировка по часам.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ссылка на основную публикацию
Adblock
detector