Создание сводных таблиц с двумя видами группирования дат

Рубрика: 7. Полезняшки Excel

Если вы создавали несколько сводных таблиц из одного и того же источника данных, то, возможно, замечали, что группирование поля в одной сводной таблице влияет на остальные таблицы. В частности, все остальные сводные таблицы начинают использовать такой же вариант группирования. Иногда именно это и требуется, но так бывает не всегда. Например, вам может понадобиться сделать два самостоятельных отчета в виде сводных таблиц: в одном данные обобщаются по месяцам, в другом по неделям. [1]

%d1%80%d0%b8%d1%81-1-%d0%b7%d0%b0%d0%b4%d0%b0%d0%bd%d0%b8%d0%b5-%d0%b8%d0%bc%d0%b5%d0%bd%d0%b8-%d0%b4%d0%b8%d0%b0%d0%bf%d0%b0%d0%b7%d0%be%d0%bd%d1%83

Рис. 1. Задание имени диапазону

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

Причина, по которой группирование данных в одной сводной таблице затрагивает все другие, заключается в том, что все сводные таблицы работают с одним и тем же кэшем. К сожалению, напрямую приказать сводной таблице использовать другой кэш нельзя (как обойти это ограничение см. Создание нескольких сводных таблиц на основе одного источника данных: один кеш или несколько?). Однако Excel можно перехитрить – следует просто присвоить исходным данным множественные имена диапазона.

Например, если исходный диапазон называется Таблица1, то присвойте тому же диапазону второе имя Таблица2. Проще всего сделать это в поле Имя, расположенном слева от строки формул (рис. 1). Выделите диапазон, введите название в поле Имя и нажмите Enter. Затем, не снимая выделения, там же введите другое имя и снова нажмите Enter. Excel отобразит только первое имя, но в системе сохранится и второе. Вы можете в этом убедиться, выполнив команду Формулы –> Определенные имена –> Диспетчер имен (рис. 2).

%d1%80%d0%b8%d1%81-2-%d0%b4%d0%b8%d0%b0%d0%bf%d0%b0%d0%b7%d0%be%d0%bd-%d1%81-%d0%b4%d0%b2%d1%83%d0%bc%d1%8f-%d0%b8%d0%bc%d0%b5%d0%bd%d0%b0%d0%bc%d0%b8

Рис. 2. Диапазон с двумя именами

Создавая первую сводную таблицу, укажите Таблица1 в поле Таблица или диапазон диалогового окна Создание сводной таблицы (рис. 3). Создавая вторую сводную таблицу, укажите в поле Таблица или диапазон имя Таблица2. Каждая из этих таблиц будет использовать отдельный кэш, и вы сможете создавать группы в одной сводной таблице, не затрагивая при этом другую.

%d1%80%d0%b8%d1%81-3-%d0%b8%d1%81%d0%bf%d0%be%d0%bb%d1%8c%d0%b7%d0%be%d0%b2%d0%b0%d0%bd%d0%b8%d0%b5-%d0%bd%d0%b0%d0%b7%d0%b2%d0%b0%d0%bd%d0%b8%d1%8f-%d0%b8%d0%bc%d0%b5%d0%bd%d0%be%d0%b2%d0%b0%d0%bd

Рис. 3. Использование названия именованного диапазона в поле Таблица или диапазон

В результате Excel создаст два кэша для двух сводных таблиц. В дальнейшем вы сможете задать различные виды группирования для различных сводных таблиц (рис. 4).

%d1%80%d0%b8%d1%81-4-%d0%b4%d0%b2%d0%b5-%d1%81%d0%b2%d0%be%d0%b4%d0%bd%d1%8b%d0%b5-%d1%82%d0%b0%d0%b1%d0%bb%d0%b8%d1%86%d1%8b-%d1%81%d0%be%d0%b7%d0%b4%d0%b0%d0%bd%d0%bd%d1%8b%d0%b5-%d0%b8%d0%b7

Рис. 4. Две сводные таблицы (с разными вариантами группирования), созданные из одного источника данных. Информация в одной сводной таблице сгруппирована по месяцам, в другой — по неделям.

Этот прием можно использовать и с имеющимися сводными таблицами. Убедитесь, что присвоили источнику данных новое имя. Потом выделите сводную таблицу и выполните команду Работа со сводными таблицами –> Анализ –> Данные –> Изменить источник данных. В диалоговом окне Изменить источник данных сводной таблицы укажите новое имя, которое вы дали диапазону.

[1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 235, 236.

Комментарии: 2 комментария

Как вариант, можно создать дополнительный столбец, например «Дата заказа_2» в котором значения будут равны значениям столбца А. В сводной таблице по полю «Дата заказа» сделать группировку по месяцам, а по полю «Дата заказа_2» — по неделям. В этом случае не нужно делать второй, идентичный диапазон.

А правильным будет создание таблицы дат и связь с первой таблицей по ключевому полю «дата»


Прокомментировать