✅ Сводный кеш в Excel - что это такое и как его лучше всего использовать

Содержание

Что такое сводный кеш?
Побочные эффекты сводного кеша
Совместное использование сводного кеша
Создание дубликата сводного кэша (с тем же источником данных)
Подсчитайте количество сводных кешей
Повышение производительности при работе со сводными таблицами

Если вы работаете со сводными таблицами Excel, вам обязательно стоит знать о сводном кэше.

Что такое сводный кеш?

Сводный кеш - это то, что автоматически создается при создании сводной таблицы.

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

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

Pivot Cache обеспечивает быстрое функционирование сводной таблицы.

Хотя вы думаете, что напрямую связаны с исходными данными, на самом деле вы получаете доступ к сводному кешу (а не к исходным данным), когда вносите изменения в сводную таблицу.

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

Побочные эффекты сводного кеша

Одним из недостатков сводного кеша является то, что он увеличивает размер вашей книги.

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

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

Совместное использование сводного кеша

Начиная с Excel 2007 и далее, если у вас уже есть сводная таблица и вы создаете дополнительную сводную таблицу с использованием тех же исходных данных, Excel автоматически использует общий кеш сводной таблицы (что означает, что обе сводные таблицы используют один и тот же сводный кеш). Это полезно, поскольку позволяет избежать дублирования сводного кеша и, в свою очередь, приводит к меньшему использованию памяти и уменьшению размера файла.

Ограничения общего сводного кэша

Хотя общий сводный кеш улучшает работу сводной таблицы и использование памяти, он страдает следующими ограничениями:

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

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

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

Создание дубликата сводного кэша (с тем же источником данных)

Вот 3 способа создать дубликат сводного кеша при создании сводных таблиц из одного и того же источника данных:

# 1 Использование разных имен таблиц

Щелкните в любом месте источника данных и перейдите в Вставить -> Таблица. (или вы можете использовать сочетание клавиш - Control + T).
В диалоговом окне «Создать таблицу» нажмите «ОК». Будет создана таблица с именем Table1..
Выбрав любую ячейку в таблице, перейдите в меню «Вставка» -> «Сводная таблица».
В диалоговом окне «Создание сводной таблицы» вы заметите, что в поле «Таблица / диапазон» указано имя таблицы. Щелкните ОК.
- Это создаст первую сводную таблицу.
Перейдите к источнику данных (таблице), выделите любую ячейку и перейдите в меню Table Tools Design -> Tools -> Convert to Range. Появится запрос с вопросом, хотите ли вы преобразовать таблицу в нормальный диапазон. Щелкните Да. Это преобразует таблицу в обычные табличные данные.

Теперь повторите шаги, описанные выше, и просто измените имя таблицы (с Table1 на Table2 или как хотите). Вы можете изменить его, введя имя в поле под «Имя таблицы» на вкладке «Дизайн» инструментов для таблиц.

Хотя обе таблицы (Table1 и Table2) относятся к одному и тому же источнику данных, этот метод обеспечивает создание двух отдельных сводных кэшей для каждой таблицы.

# 2 Использование старого мастера сводной таблицы

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

Выберите любую ячейку в данных и нажмите ALT + D + P.
- Откроется мастер сводной таблицы и сводной диаграммы.
На шаге 1 из 3 нажмите Далее.
На шаге 2 из 3 убедитесь, что диапазон данных правильный, и нажмите «Далее».
Excel показывает подсказку, в которой, по сути, говорится, что нажмите «Да», чтобы создать общий сводный кеш, и «Нет», чтобы создать отдельный сводный кеш.
Нажмите Нет.
На шаге 3 мастера выберите, хотите ли вы, чтобы сводная таблица была на новом листе или на том же листе, а затем нажмите «Готово».

Примечание. Убедитесь, что это не таблица Excel.

Подсчитайте количество сводных кешей

Вы можете подсчитать количество сводных кешей, чтобы избежать нескольких сводных кешей из одного источника данных.

Вот быстрый способ его посчитать:

Нажмите ALT + F11, чтобы открыть редактор VB (или перейдите на вкладку «Разработчик» -> «Visual Basic»).
В меню редактора Visual Basic нажмите «Просмотр» и выберите «Немедленное окно» (или нажмите Control + G). Это сделает непосредственное окно видимым.
В окне Immediate Window вставьте следующий код и нажмите Enter:
```
? ActiveWorkbook.PivotCaches.Count
```

Он мгновенно покажет количество сводных кешей в книге.

Повышение производительности при работе со сводными таблицами

Есть несколько вещей, которые вы можете сделать, чтобы улучшить производительность книг (размер файла и использование памяти) при работе со сводными таблицами:

# 1 Удалить исходные данные

Вы можете удалить исходные данные и использовать только сводный кэш. Вы по-прежнему сможете делать все, используя сводный кеш, поскольку он содержит снимок исходных данных. Но поскольку вы удалили исходные данные, размер файла вашей книги уменьшится.

Если вы хотите вернуть исходные данные, просто дважды щелкните пересечение общих итогов для этой сводной таблицы. Он создаст новый рабочий лист и покажет все данные, использованные для создания этой сводной таблицы.

# 2 Не сохраняйте данные в сводном кэше

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

Есть возможность не сохранять данные в кеше и закрыть его. Это приведет к уменьшению размера файла.

Сделать это:

Выберите любую ячейку в сводной таблице.
Перейдите в Analyze -> Pivot Table -> Options.
В диалоговом окне «Параметры сводной таблицы» перейдите на вкладку «Данные».
Снимите флажок с опции - Сохранить исходные данные с файлом.
Отметьте опцию - Обновлять данные при открытии файла.
- Если вы не отметите этот параметр, при открытии книги Excel данные не будут обновляться, и вы не сможете использовать функции сводной таблицы. Чтобы это заработало, вам придется вручную обновить сводную таблицу.

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

Хотя это может привести к уменьшению размера файла, открытие файла может занять немного больше времени (поскольку Excel воссоздает кеш).

Смотрите также: Сохранение исходных данных с помощью сводной таблицы.

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

# 3 Совместное использование сводного кэша для повышения производительности

Если случайно (или намеренно) вы попадаете в ситуацию, когда у вас есть дублированный сводный кеш, и вы хотите удалить дубликат и предоставить общий доступ к сводному кешу, вот шаги, чтобы сделать это:

Удалите одну из сводных таблиц, для которой вы хотите удалить кеш. Для этого выберите сводную таблицу и перейдите на главную -> Очистить -> Очистить все.
Теперь просто скопируйте сводную таблицу, которую вы хотите продублировать, и вставьте ее (либо на том же листе, либо на отдельном листе).
- Рекомендуется вставлять ее в отдельные рабочие листы, чтобы она не перекрывалась с другой сводной таблицей при ее расширении. Хотя иногда копирую рядом, чтобы сравнить разные взгляды. Эта копирующая вставка сводной таблицы обеспечивает общий доступ к сводному кешу.