✅ Обобщен кеш в Excel - какво е това и как да го използвате най -добре

Съдържание

Какво е Pivot Cache?
Странични ефекти на Pivot Cache
Споделяне на Pivot Cache
Създаване на дублиран осев кеш (със същия източник на данни)
Пребройте броя на обобщените кешове
Подобряване на производителността при работа с обобщени таблици

Ако работите с Excel Pivot Tables, Pivot Cache е нещо, за което определено трябва да знаете.

Какво е Pivot Cache?

Pivot Cache е нещо, което автоматично се генерира, когато създавате обобщена таблица.

Това е обект, който съдържа реплика на източника на данни. Въпреки че не можете да го видите, той е част от работната книга и е свързан с обобщената таблица. Когато правите промени в обобщената таблица, тя не използва източника на данни, а използва Pivot Cache.

Причината, поради която се генерира кеш кеш, е да се оптимизира функционирането на обобщената таблица. Дори когато имате хиляди редове с данни, обобщената таблица е много бърза в обобщаването й. Можете да плъзгате и пускате елементи в редовете/колоните/стойностите/филтрите и той незабавно ще актуализира резултатите.

Pivot Cache позволява това бързо функциониране на обобщена таблица.

Докато смятате, че сте директно свързани с изходните данни, в действителност имате достъп до осевия кеш (а не към изходните данни), когато правите промени в обобщената таблица.

Това е и причината, поради която трябва да опресните обобщената таблица, за да отразява всички промени, направени в набора от данни.

Странични ефекти на Pivot Cache

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

Тъй като това е реплика на изходните данни, когато създавате обобщена таблица, копие на тези данни се съхранява в Pivot Cache.

Когато използвате големи набори от данни за създаване на обобщена таблица, размерът на файла на работната книга се увеличава значително.

Споделяне на Pivot Cache

От Excel 2007 нататък, ако вече имате обобщена таблица и създавате допълнителна обобщена таблица, използвайки същите изходни данни, Excel автоматично споделя пивот кеша (което означава, че и двете обобщени таблици използват един и същ осев кеш). Това е полезно, тъй като избягва дублирането на кешовия кеш и от своя страна води до по -малко използване на паметта и намаляване на размера на файла.

Ограничения на споделения пивот кеш

Докато споделеният кеш кеш подобрява функционирането на осевата таблица и използването на паметта, той страда от следните ограничения:

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

Начинът да заобиколите тези ограничения е да принудите Excel да създаде отделен обобщен кеш за различни обобщени таблици (докато използвате същия източник на данни).

Забележка: Ако използвате различни източници на данни за различни обобщени таблици, Excel автоматично ще генерира отделни обобщени кешове за него.

Създаване на дублиран осев кеш (със същия източник на данни)

Ето 3 начина за създаване на дублиращ се осев кеш, докато създавате обобщени таблици от същия източник на данни:

#1 Използване на различни имена на таблици

Щракнете навсякъде в източника на данни и отидете на Вмъкване -> Таблица (или можете да използвате клавишната комбинация - Control + T).
В диалоговия прозорец Създаване на таблица щракнете върху OK. Той ще създаде таблица с името Table1.
С всяка клетка, избрана в таблицата, отидете на Вмъкване -> Осева таблица.
В диалоговия прозорец Създаване на обобщена таблица ще забележите, че в полето Таблица/Обхват има името на таблицата. Щракнете върху OK.
- Това ще създаде първата обобщена таблица.
Отидете на източника на данни (таблица), изберете произволна клетка и отидете на Дизайн на инструменти за таблици -> Инструменти -> Конвертиране в диапазон. Той ще покаже подкана с въпрос дали искате да преобразувате таблицата в нормален диапазон. Щракнете върху Да. Това ще преобразува таблицата в обикновени таблични данни.

Сега повторете стъпките по -горе и просто променете името на таблицата (от Table1 на Table2 или каквото искате). Можете да го промените, като въведете името в полето под Име на таблица в раздела Инструменти за таблици.

Въпреки че и двете таблици (Таблица 1 и Таблица 2) се отнасят до един и същ източник на данни, този метод гарантира, че за всяка таблица се генерират два отделни сводни кеша.

#2 Използване на съветника за старата обобщена таблица

Използвайте тези стъпки, когато искате да създадете допълнителна обобщена таблица с отделен осев кеш, докато използвате същия източник на данни.

Изберете всяка клетка в данните и натиснете ALT + D + P.
- Това ще отвори съветника за обобщена таблица и обобщена диаграма.
В стъпка 1 от 3 щракнете върху Напред.
В стъпка 2 от 3 се уверете, че диапазонът от данни е правилен и щракнете върху Напред.
Excel показва подкана, която по същество казва щракнете върху Да, за да създадете споделен осев кеш, и Не, за да създадете отделен осев кеш.
Щракнете върху No.
В стъпка 3 на съветника изберете дали искате обобщената таблица в нов работен лист или същия работен лист и след това щракнете върху Готово.

Забележка: Уверете се, че данните не са таблица на Excel.

Пребройте броя на обобщените кешове

Може да искате да преброите броя на обобщените кешове, само за да избегнете множество осеви кешове от един и същ източник на данни.

Ето бърз начин да го преброите:

Натиснете ALT + F11, за да отворите VB Editor (или отидете в раздел Developer -> Visual Basic).
В менюто на редактора на Visual Basic щракнете върху Изглед и изберете Незабавен прозорец (или натиснете Control + G). Това ще направи незабавния прозорец видим.
В непосредствения прозорец поставете следния код и натиснете Enter:
```
? ActiveWorkbook.PivotCaches.Count
```

Той незабавно ще покаже броя на обобщените кешове в работната книга.

Подобряване на производителността при работа с обобщени таблици

Има няколко неща, които можете да направите, за да подобрите производителността на работните книги (размер на файла и използване на паметта), докато работите с обобщени таблици:

#1 Изтрийте изходните данни

Можете да изтриете изходните данни и да използвате само Pivot Cache. Все пак ще можете да правите всичко с помощта на обобщения кеш, тъй като той съдържа моментна снимка на оригиналните данни. Но тъй като сте изтрили изходните данни, размерът на файла на вашата работна книга ще намалее.

В случай, че искате да върнете изходните данни, просто щракнете двукратно върху пресечната точка на общите суми за тази обобщена таблица. Той ще създаде нов работен лист и ще покаже всички данни, използвани за създаване на тази обобщена таблица.

#2 Не запазвайте данните в Pivot Cache

Когато запишете файл с обобщена таблица и изходни данни, той също така записва кешовия кеш, който има копие на изходните данни. Това означава, че запазвате изходните данни на две места: в работния лист, който съдържа данните, и в обобщения кеш.

Има опция да не записвате данните в кеша и да ги затваряте. Това ще доведе до по -малък размер на файла.

Да го направя:

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

Когато направите това, Excel няма да запише данните в основния кеш, но ще го опресни, когато отворите работната книга на Excel следващия път. Вашите данни могат да бъдат в същата работна книга, друга работна книга или външна база данни. Когато отворите файла, той опреснява данните и Pivot Cache се пресъздава.

Въпреки че това може да доведе до по -малък размер на файла, отварянето на файла може да отнеме малко повече време (тъй като Excel пресъздава кеша).

Вижте също: Запазване на изходни данни с обобщена таблица.

Забележка: Ако използвате тази опция, уверете се, че източникът на данни е непокътнат. Ако изтриете изходните данни (от работната книга или друг външен източник на данни), няма да можете да пресъздадете сводния кеш.

#3 Споделяне на Pivot Cache за по -добра производителност

Ако случайно (или умишлено) попаднете в ситуация, в която имате дублиран кеш кеш и искате да изтриете дубликата и да го споделите, ето стъпките за това:

Изтрийте една от обобщените таблици, за които искате да изтриете кеша. За да направите това, изберете осевата таблица и отидете на Начало -> Изчистване -> Изчистване на всички.
Сега просто копирайте обобщената таблица, която искате да дублирате, и я поставете (или в същия работен лист, или в отделен работен лист).
- Препоръчително е да го поставите в отделни работни листове, така че да не се припокрива с другата обобщена таблица, когато я разширите. Въпреки че понякога го копирам един до друг, за да сравня различни гледни точки. Това поставяне на копие на обобщената таблица гарантира, че кешовият кеш е споделен.