Как при помощи VBA группировать даты по годам в сводных таблицах Excel 2010
Зачастую данные транзакций накапливаются в таблице отчета по одной строке в день. Хотя ежедневные данные могут быть полезными для работников некоторых отраслей, большинству из них больше подходят данные, отсортированные по месяцам, кварталам либо годам.
Хорошая новость заключается в том, что в Excel 2010 группировка данных в сводных таблицах облегчена до предела. Теперь вместо загадочных формул типа =А2+1-ДЕНЬ(А2)
, применяемых для преобразования ежедневных сведений в ежемесячные данные, можно обратиться к возможностям группирования Excel, выбрав произвольный уровень группировки (по месяцам, кварталам и т.д.), кпримером може служиь группировка данных по количеству смс уведомлений своих клиентов отправленных за отчетный период.
Группировка по дате в VBA выполняется довольно нестандартно. Метод .Group может применяться только к единственной ячейке сводной таблицы, а эта ячейка должна включать дату либо надпись поля Дата. Как показано на рис. 12.10, можно выбрать либо заголовок Дата, отображенный в ячейке Р2, либо одну из дат в диапазоне Р3:АРМ3
. Первый метод довольно ненадежный, особенно если сводная таблица будет создаваться в новом столбце. Два других метода более надежны.
Если вы не собираетесь использовать различное число полей строк, можете обозначить заголовок Дата, находящийся в ячейке на пересечении строки 1 и столбца 3, как TableRange2. Эта ячейка может быть выделена с помощью
следующей строки кода:
РТ.TableRange2.Cells(1,3).Select
Если количество полей строк будет изменяться, добавьте комментарий, в котором упоминается, что для изменения числа полей строк следует изменить 3 на иное число. Еще один метод выделения ячейки основан на использовании свойства LabelRange для поля Дата. Следующий код всегда выбирает ячейку, содержащую заголовок поля Дата:
РТ.PivotFields("Дата").LabelRange.Select
Для группировки ежедневных дат в даты по годам следует определить сводную таблицу, в качестве поля строки которой используется поле Дата. Для гарантированного создания сводной таблицы отключите свойство ManualCalculation.
Для поиска подписи даты воспользуйтесь свойством LabelRange. К ячейке, содержащей подпись даты, примените метод .Group. Для аргумента Periods этого метода следует определить массив, включающий семь нулевых значений. Эти семь значений соответствуют секундам, минутам, часам, дням, месяцам, кварталам и годам. Например, для группировки по годам применяется следующий код.
РТ.PivotFields("Дата").LabelRange.Group _ Periods:=(False, False, False, False, False, False, _ False, True)
После выполнения группировки по годам поле по-прежнему называется Дата. В этом заключается отличие от группировки по нескольким полям. Для группировки по месяцам, кварталам и годам используется следующий код.
РТ.PivotFields("Дата").LabelRange.Group _ Periods:=(False, False, False, False, False, True, _ True, True)
После выполнения группировки по месяцам, кварталам и годам в поле Дата отображаются месяцы. В сводной таблице появляется два новых виртуальных поля: Кварталы и Годы.
Для группировки по неделям выбирается период Day (День), а затем используется аргумент By для группировки по периодам, состоящим из семи дней.
РТ.PivotFields("Дата").LabelRange.Group Ву:=7 Periods:=(False, False, False, True, False, , True, True)
Поскольку показанная на рис. 12.10 сводная таблица будет группироваться по годам, используется следующий код.
РТ.PivotFields("Дата").LabelRange.Group _ Periods:=(False, False, False, False, False, False, _ False, True)
На рис. 12.11 показана сводная таблица с датами продаж, сгруппированными по годам.