✅ Зведений кеш в Excel - що це таке і як його найкраще використовувати

Зміст

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

Якщо ви працюєте з зведеними таблицями Excel, зведений кеш - це те, про що ви неодмінно повинні знати.

Що таке зведений кеш?

Зведений кеш - це те, що автоматично генерується під час створення зведеної таблиці.

Це об’єкт, що містить копію джерела даних. Хоча ви його не бачите, він є частиною робочої книги та під’єднаний до зведеної таблиці. Коли ви вносите будь -які зміни у зведену таблицю, вона не використовує джерело даних, а використовує зведений кеш.

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

Зведений кеш забезпечує швидке функціонування зведеної таблиці.

Хоча вам здається, що ви безпосередньо пов’язані з вихідними даними, насправді ви отримуєте доступ до зведеного кешу (а не вихідних даних) під час внесення змін до зведеної таблиці.

Це також причина, чому вам потрібно оновити зведену таблицю, щоб відобразити будь -які зміни, внесені у набір даних.

Побічні ефекти зворотного кешу

Одним недоліком зведеного кешу є те, що він збільшує розмір вашої книги.

Оскільки це репліка вихідних даних, під час створення зведеної таблиці копія цих даних зберігається у зведеному кеші.

Коли ви використовуєте великі набори даних для створення зведеної таблиці, розмір файлу книги значно збільшується.

Спільний доступ до зведеного кешу

Починаючи з Excel 2007 і далі, якщо у вас вже є зведена таблиця, і ви створюєте додаткову зведену таблицю, використовуючи ті ж вихідні дані, Excel автоматично надає спільний доступ до зведеної кеш -пам'яті (що означає, що обидві зведеної таблиці використовують один і той же зведений кеш). Це корисно, оскільки дозволяє уникнути дублювання зведеного кешу, що, у свою чергу, призводить до зменшення використання пам’яті та зменшення розміру файлу.

Обмеження спільного кешованого кеша

Хоча загальний кешований кеш покращує функціонування зведеної таблиці та використання пам’яті, вона зазнає таких обмежень:

Під час оновлення однієї зведеної таблиці оновлюються всі зведені таблиці, пов’язані з одним кешем.
Коли ви групуєте поля в одній із зведених таблиць, вона застосовується до всіх зведених таблиць, використовуючи один і той же зведений кеш. Наприклад, якщо ви згрупуєте дати за місяцями, ця зміна відобразиться у всіх зведених таблицях.
Коли ви вставляєте обчислюване поле/елемент в одну з зведеної таблиці, вона відображається у всіх зведених таблицях, які мають спільний доступ до зведеної кеш -пам'яті.

Шляхом обходу цих обмежень є змусити Excel створити окремий зведений кеш для різних зведених таблиць (при використанні одного і того ж джерела даних).

Примітка: Якщо ви використовуєте різні джерела даних для різних зведених таблиць, Excel автоматично генеруватиме для них окремі зведені кеші.

Створення дубліката зведеного кешу (з тим самим джерелом даних)

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

#1 Використання різних назв таблиць

Клацніть будь -де у джерелі даних і перейдіть до пункту Вставка -> Таблиця (або ви можете скористатися комбінацією клавіш - Control + T).
У діалоговому вікні Створення таблиці натисніть кнопку ОК. Він створить таблицю з назвою Table1.
З будь -якою коміркою, вибраною в таблиці, перейдіть до пункту Вставка -> Зведена таблиця.
У діалоговому вікні Створення зведеної таблиці можна помітити, що в полі Таблиця/Діапазон є назва таблиці. Натисніть OK.
- Це створить першу зведену таблицю.
Перейдіть до джерела даних (таблиці), виберіть будь -яку клітинку та перейдіть до Конструктор таблиць -> Інструменти -> Перетворити на діапазон. Він покаже запит, чи потрібно перетворити таблицю на нормальний діапазон. Натисніть Так. Це перетворить таблицю на звичайні табличні дані.

Тепер повторіть наведені вище кроки та просто змініть назву таблиці (з таблиці1 на таблицю2 або будь -яку іншу). Ви можете змінити його, ввівши назву у поле під назвою Таблиця на вкладці Дизайн інструментів для таблиць.

Хоча обидві таблиці (Таблиця 1 і Таблиця 2) посилаються на одне і те ж джерело даних, цей метод гарантує, що для кожної таблиці генеруються два окремих зведених кеші.

#2 Використання Майстра старої зведеної таблиці

Виконайте ці дії, якщо потрібно створити додаткову зведену таблицю з окремим зведеним кешем під час використання того самого джерела даних.

Виберіть будь -яку клітинку в даних і натисніть ALT + D + P.
- Це відкриє майстер зведеної таблиці та зведеної діаграми.
На кроці 1 з 3 натисніть Далі.
На кроці 2 з 3 переконайтеся, що діапазон даних правильний, і натисніть Далі.
Excel показує підказку, яка по суті говорить: натисніть на Так, щоб створити спільний зведений кеш, та Ні, щоб створити окремий зведений кеш.
Натисніть Ні.
На кроці 3 Майстра виберіть, чи хочете ви зведену таблицю на новому аркуші чи на тому самому аркуші, а потім натисніть кнопку Готово.

Примітка: Переконайтеся, що дані не є таблицею Excel.

Порахуйте кількість зведених кешів

Ви можете підрахувати кількість зведених кешів, щоб уникнути кількох зведених кешів з одного джерела даних.

Ось швидкий спосіб порахувати це:

Натисніть ALT + F11, щоб відкрити редактор VB (або перейдіть на вкладку Розробник -> Visual Basic).
У меню редактора Visual Basic натисніть «Перегляд» та виберіть «Негайне вікно» (або натисніть Control + G). Це зробить вікно негайного перегляду видимим.
У вікні негайного вставте наступний код і натисніть клавішу Enter:
```
? ActiveWorkbook.PivotCaches.Count
```

Він миттєво покаже кількість зведених кешів у книзі.

Підвищення продуктивності під час роботи зі зведеними таблицями

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

#1 Видаліть вихідні дані

Ви можете видалити вихідні дані та використовувати лише зведений кеш. Ви все одно зможете робити все, використовуючи зведений кеш, оскільки він містить знімок вихідних даних. Але оскільки ви видалили вихідні дані, розмір файлу вашої книги зменшиться.

Якщо ви хочете повернути вихідні дані, просто двічі клацніть на перетині загальних підсумків для зведеної таблиці. Він створить новий аркуш і покаже всі дані, використані для створення зведеної таблиці.

#2 Не зберігайте дані в зведеному кеші

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

Існує можливість не зберігати дані в кеші та закривати їх. Це призведе до зменшення розміру файлу.

Зробити це:

Виберіть будь -яку клітинку у зведеній таблиці.
Перейдіть до Аналіз -> Зведена таблиця -> Параметри.
У діалоговому вікні Параметри зведеної таблиці перейдіть на вкладку Дані.
Зніміть прапорець біля опції - Зберегти вихідні дані за допомогою файлу.
Установіть прапорець - Оновити дані під час відкриття файлу.
- Якщо ви не встановите цей прапорець, під час відкриття книги Excel програма не оновить дані, і ви не зможете користуватися функціями зведеної таблиці. Щоб змусити його працювати, вам доведеться вручну оновити зведену таблицю.

Коли ви це зробите, Excel не збереже дані у зведеному кеші, але оновить їх, коли ви наступного разу відкриєте книгу Excel. Ваші дані можуть бути в тій самій книзі, іншій книзі або у зовнішній базі даних. Коли ви відкриваєте файл, він оновлює дані, а зведений кеш відтворюється.

Хоча це може призвести до зменшення розміру файлу, відкриття файлу може зайняти трохи більше часу (оскільки Excel відновлює кеш).

Дивитися також: Збереження вихідних даних за допомогою зведеної таблиці.

Примітка: Якщо ви використовуєте цю опцію, переконайтеся, що джерело даних недоторкане. Якщо ви видалите вихідні дані (з книги або будь -якого зовнішнього джерела даних), ви не зможете відтворити зведений кеш.

#3 Спільне використання кешового кеша для кращої продуктивності

Якщо випадково (або навмисно) ви потрапили в ситуацію, коли у вас є дублікат зведеного кеша, і ви хочете видалити дублікат і поділитися зведеним кешем, виконайте наведені нижче дії.

Видаліть одну з зведених таблиць, для якої потрібно видалити кеш. Для цього виберіть зведену таблицю та перейдіть до Домашня сторінка -> Очистити -> Очистити все.
Тепер просто скопіюйте зведену таблицю, яку потрібно дублювати, і вставте її (або на той самий аркуш, або на окремий аркуш).
- Рекомендується вставляти його в окремі аркуші, щоб він не збігався з іншою зведеною таблицею під час її розгортання. Хоча, іноді я копіюю його поруч, щоб порівняти різні погляди. Це вставлення копії зведеної таблиці гарантує, що зведений кеш є спільним.