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

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

Когда вы создаете переменную объекта PivotTable, вы создаете также все свойства сводной таблицы — такие как имя, местоположение, индекс кэша и т. д. В этом макросе цикл проходит по каждой сводной таблице и извлекает нужные свойства на новый лист. 

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

2. Шаг 2 создает новый рабочий лист и добавляет заголовки столбцов в диапазоне А1:F1. Обратите внимание, что мы можем добавить заголовки столбцов, используя простой массив, который содержит наши заголовки. Этот новый рабочий лист остается активным. 

3. Шаг 3 помещает курсор в ячейку A2 активного листа. Отсюда начнет работать цикл макроса (это точка привязки). На протяжении всего макроса, мы используем свойство offset. Свойство offset позволяет передвигать курсор на определенное количество строк столбцов от точки привязки. Например, Range (A2).Offset (0,1) будет перемещать курсор на один столбец. Если мы хотим, переместить курсор на одну строку вниз, мы вводим Range(A2).Offset(1, 0). 

В макросе, используется Offset MyCell.Offset (0,4) он будет перемещать курсор на четыре столбца вправо от анкерной ячейки. После того, как курсор будет на месте, будут введены данные. 

4. Шаг 4 начинает цикл, говоря Excel, что мы хотим оценить все рабочие листы в этой книге. 

5. Шаг 5 перебирает все сводные таблицы в каждом листе. Для каждой сводной таблицы он находит, извлекает соответствующее свойство и заполняет таблицу на основании положения курсора из шага 3. Мы используем шесть свойств: Name, Parent.Range, TableRange2.Address, CacheIndex, PivotCache.SourceData и PivotCache. RecordCount.

- Свойство Name возвращает имя сводной таблицы

- Свойство Parent.Range дает нам лист, где постоянно находится сводная таблица

- Свойство TableRange2.Address возвращает диапазон, где находится сводная таблица

- Свойство CacheIndex возвращает индекс кэша для сводной таблицы

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

- Свойство PivotCache.SourceData сообщает нам, какой диапазон будет использоваться, когда мы будем обновлять сводную таблицу. 

- Свойство PivotCache.Recordcount позволяет посчитать количество записей исходных данных. 

6. Каждый раз, когда макрос находит новую сводную таблицу, он перемещает MyCell и курсор вниз на 1 строку, то есть начинает новую строку для каждой сводной таблицы. 

7. Шаг 7 говорит Excel повторить цикл для всех листов. После того, как все листы были оценены, макрос переходит к последнему шагу. 

8. Шаг 8 форматирует размеров столбцов, чтобы подогнать их под размер данных. 

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

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