Как создать сводные таблицы на основании единого кэша в Excel.

Актуально для Excel ранних версий

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

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

Начиная с Excel 2007 Microsoft распознает, когда вы создаете сводную таблицу из того же источника, что и существующая сводная таблица. Это снижает случаи создания избыточного кэша.

Кроме уменьшения размера файла есть и другие преимущества единого кэш:

- Вы можете обновить одну сводную таблицу – и сразу обновятся остальные.

- При добавлении вычисляемого поля в одну сводную таблицу оно станет доступно и в других сводных,

- При добавлении вычисляемого элемента в одну сводную таблицу, он появится и в других,

- Любая группировка или разгруппировка появится во всех сводных таблицах с общим кэшем

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

1. Шаг 1 объявляет объект под названием WS. Он создает контейнер памяти для каждого рабочего листа. Также объявляем объект под названием PT, который содержит каждую сводную таблицу. 

2. Шаг 2 запускаем цикл, говоря Excel, что мы хотим охватить все рабочие листы в этой книге. Обратите внимание, что мы используем ThisWorkbook вместо ActiveWorkbook. 

3. Шаг 3 Цикл проходит через все сводные таблицы на каждом листе, а затем меняет CachIndex на тот, который используется Сводной таблицей1. После того, как все сводные таблицы на листе пройдены, макрос переходит к следующему листу. После того, как все листы были пройдены - макрос заканчивается. 

Для реализации этого макроса, вы можете скопировать и вставить его в стандартный модуль:

1. Активируйте редактор Visual Basic, нажав ALT + F11. 
2. Щелкните правой кнопкой мыши имя проекта / рабочей книги в окне проекта. 
3. Выберите Insert➜Module. 
4. Введите или вставьте код во вновь созданном модуле.