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

Что делает макрос: Если вы работаете со сводными таблицами, у вас есть необходимость анализировать один набор данных несколькими способами. В большинстве случаев этот процесс требует создания отдельных сводных таблиц из одного источника данных.
Проблема заключается в том, что каждый раз, когда вы создаете сводную таблицу, вы храните снимок источника данных в кэш. Каждый кэш, который создается увеличивает использование памяти и размер файла. Побочным эффектом этого будет то, что ваша электронная таблица раздувается, для этого создаём PivotTables.
Начиная с Excel 2007, Microsoft построен в автоматическом сводном кэш-алгоритме обмена, который распознает, когда вы создаете сводную таблицу из того же источника, что и существующая сводная таблица. Это снижает случаи создания избыточных кэш. В дополнение к уменьшению размера файла, есть и другие преимущества для обмена кэш:

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

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

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

Код макроса

Sub EdiniiKeshSvodnihTablic()
'Шаг 1: Объявляем переменные
Dim ws As Worksheet
Dim pt As PivotTable
'Шаг 2: Запускаем цикл через все листы книги
For Each ws In ThisWorkbook.Worksheets
'Шаг 3: Запускаем цикл через все сводные таблицы
For Each pt In ws.PivotTables
pt.CacheIndex = _
Sheets("Проданных единиц").PivotTables("PivotTable1").CacheIndex
Next pt
Next ws
End Sub

Как это код работает

  1. Шаг 1 объявляет объект под названием WS. Он создает контейнер памяти для каждого рабочего листа. Также объявляем объект под названием PT, который содержит каждую сводную таблицу.
  2. Шаг 2 начинается цикл, говоря Excel, что мы хотим оценить все
    рабочие листы в этой книге. Обратите внимание, что мы используем ThisWorkbook вместо ActiveWorkbook.
  3. Шаг 3 Цикл проходит через все сводные таблицы в каждом листе, а затем устанавливает CachIndex к той, которая используется PivotTable1. После того, как все PivotTables просмотрели, переходим к следующему листу. После того, как все листы были оценены, макрос заканчивается.

Как использовать

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

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