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

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

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

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

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

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

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

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

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

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

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

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

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