✅ Pamięć podręczna przestawna w programie Excel - co to jest i jak najlepiej z niej korzystać

Spisie treści

Co to jest pamięć podręczna przestawna?
Skutki uboczne Pivot Cache
Udostępnianie pamięci podręcznej Pivot
Tworzenie zduplikowanej pamięci podręcznej Pivot (z tym samym źródłem danych)
Policz liczbę pamięci podręcznych Pivot
Poprawa wydajności podczas pracy z tabelami przestawnymi

Jeśli pracujesz z tabelami przestawnymi programu Excel, pamięć podręczna przestawna to coś, o czym zdecydowanie powinieneś wiedzieć.

Co to jest pamięć podręczna przestawna?

Pamięć podręczna przestawna to coś, co jest automatycznie generowane podczas tworzenia tabeli przestawnej.

Jest to obiekt, który zawiera replikę źródła danych. Chociaż nie możesz go zobaczyć, jest on częścią skoroszytu i jest połączony z tabelą przestawną. Gdy wprowadzasz jakiekolwiek zmiany w tabeli przestawnej, nie używa ona źródła danych, a raczej używa pamięci podręcznej przestawnej.

Powodem generowania pamięci podręcznej przestawnej jest optymalizacja działania tabeli przestawnej. Nawet jeśli masz tysiące wierszy danych, tabela przestawna bardzo szybko je podsumowuje. Możesz przeciągać i upuszczać elementy w polach wierszy/kolumn/wartości/filtrów i natychmiast zaktualizuje wyniki.

Pamięć podręczna przestawna umożliwia szybkie działanie tabeli przestawnej.

Chociaż wydaje Ci się, że jesteś bezpośrednio połączony z danymi źródłowymi, w rzeczywistości podczas wprowadzania zmian w tabeli przestawnej uzyskujesz dostęp do pamięci podręcznej przestawnej (a nie do danych źródłowych).

Jest to również powód, dla którego musisz odświeżyć tabelę przestawną, aby odzwierciedlić wszelkie zmiany wprowadzone w zestawie danych.

Skutki uboczne Pivot Cache

Jedną wadą pamięci podręcznej przestawnej jest to, że zwiększa rozmiar skoroszytu.

Ponieważ jest to replika danych źródłowych, podczas tworzenia tabeli przestawnej kopia tych danych jest przechowywana w pamięci podręcznej przestawnej.

Gdy używasz dużych zestawów danych do tworzenia tabeli przestawnej, rozmiar pliku skoroszytu znacznie się zwiększa.

Udostępnianie pamięci podręcznej Pivot

Począwszy od programu Excel 2007, jeśli masz już tabelę przestawną i utworzysz dodatkową tabelę przestawną przy użyciu tych samych danych źródłowych, program Excel automatycznie udostępnia pamięć podręczną przestawną (co oznacza, że obie tabele przestawne używają tej samej pamięci podręcznej przestawnej). Jest to pomocne, ponieważ pozwala uniknąć duplikacji pamięci podręcznej, co z kolei powoduje mniejsze zużycie pamięci i mniejszy rozmiar pliku.

Ograniczenia udostępnionej pamięci podręcznej Pivot

Chociaż współdzielona pamięć podręczna przestawna poprawia działanie tabeli przestawnej i wykorzystanie pamięci, ma ona następujące ograniczenia:

Gdy odświeżysz jedną tabelę przestawną, wszystkie tabele przestawne połączone z tą samą pamięcią podręczną zostaną odświeżone.
Gdy grupujesz pola w jednej z tabel przestawnych, jest ono stosowane do wszystkich tabel przestawnych przy użyciu tej samej pamięci podręcznej. Na przykład, jeśli pogrupujesz daty według miesięcy, ta zmiana zostanie odzwierciedlona we wszystkich tabelach przestawnych.
Po wstawieniu obliczonego pola/elementu do jednej z tabel przestawnych pojawia się ono we wszystkich tabelach przestawnych, które współużytkują pamięć podręczną przestawną.

Sposobem na obejście tych ograniczeń jest zmuszenie programu Excel do utworzenia oddzielnej pamięci podręcznej przestawnej dla różnych tabel przestawnych (przy użyciu tego samego źródła danych).

Uwaga: Jeśli używasz różnych źródeł danych dla różnych tabel przestawnych, program Excel automatycznie wygeneruje dla nich osobne pamięci podręczne przestawne.

Tworzenie zduplikowanej pamięci podręcznej Pivot (z tym samym źródłem danych)

Oto 3 sposoby tworzenia zduplikowanej pamięci podręcznej przestawnej podczas tworzenia tabel przestawnych z tego samego źródła danych:

#1 Używanie różnych nazw tabel

Kliknij w dowolnym miejscu źródła danych i przejdź do Wstaw -> Tabela (lub możesz użyć skrótu klawiaturowego - Control + T).
W oknie dialogowym Utwórz tabelę kliknij OK. Utworzy tabelę o nazwie Table1.
Po zaznaczeniu dowolnej komórki w tabeli przejdź do Wstaw -> Tabela przestawna.
W oknie dialogowym Utwórz tabelę przestawną zauważysz, że w polu Tabela/zakres znajduje się nazwa tabeli. Kliknij OK.
- Spowoduje to utworzenie pierwszej tabeli przestawnej.
Przejdź do źródła danych (tabeli), zaznacz dowolną komórkę i przejdź do opcji Projektowanie narzędzi tabel -> Narzędzia -> Konwertuj na zakres. Wyświetli się monit z pytaniem, czy chcesz przekonwertować tabelę na normalny zakres. Kliknij Tak. Spowoduje to przekształcenie tabeli w zwykłe dane tabelaryczne.

Teraz powtórz powyższe kroki i po prostu zmień nazwę tabeli (z Table1 na Table2 lub cokolwiek chcesz). Możesz to zmienić, wpisując nazwę w polu poniżej Nazwa tabeli w zakładce Narzędzia tabel Design.

Chociaż obie tabele (Tabela1 i Tabela2) odnoszą się do tego samego źródła danych, ta metoda zapewnia wygenerowanie dwóch oddzielnych pamięci podręcznych przestawnych dla każdej tabeli.

#2 Używanie starego kreatora tabeli przestawnej

Wykonaj te czynności, jeśli chcesz utworzyć dodatkową tabelę przestawną z oddzielną pamięcią podręczną przestawną przy użyciu tego samego źródła danych.

Wybierz dowolną komórkę w danych i naciśnij ALT + D + P.
- Spowoduje to otwarcie Kreatora tabeli przestawnej i wykresu przestawnego.
W kroku 1 z 3 kliknij Dalej.
W kroku 2 z 3 upewnij się, że zakres danych jest poprawny i kliknij Dalej.
Excel wyświetla monit, który zasadniczo mówi, że kliknij Tak, aby utworzyć udostępnioną pamięć podręczną osi i Nie, aby utworzyć oddzielną pamięć podręczną osi.
Kliknij Nie.
W kroku 3 kreatora wybierz, czy chcesz, aby tabela przestawna znajdowała się w nowym arkuszu, czy w tym samym arkuszu, a następnie kliknij Zakończ.

Uwaga: Upewnij się, że dane nie są tabelą programu Excel.

Policz liczbę pamięci podręcznych Pivot

Możesz policzyć liczbę pamięci podręcznych przestawnych, aby uniknąć wielu pamięci podręcznych przestawnych z tego samego źródła danych.

Oto szybki sposób, aby to policzyć:

Naciśnij ALT + F11, aby otworzyć Edytor VB (lub przejdź do zakładki Deweloper -> Visual Basic).
W menu Edytora Visual Basic kliknij Widok i wybierz Okno bezpośrednie (lub naciśnij Ctrl + G). Dzięki temu widoczne będzie okno bezpośrednie.
W oknie bezpośrednim wklej następujący kod i naciśnij Enter:
```
?ActiveWorkbook.PivotCaches.Count
```

Natychmiast pokaże liczbę pamięci podręcznych Pivot w skoroszycie.

Poprawa wydajności podczas pracy z tabelami przestawnymi

Jest kilka rzeczy, które możesz zrobić, aby poprawić wydajność skoroszytów (rozmiar pliku i użycie pamięci) podczas pracy z tabelami przestawnymi:

#1 Usuń dane źródłowe

Możesz usunąć dane źródłowe i używać tylko Pivot Cache. Nadal będziesz mógł robić wszystko, korzystając z pamięci podręcznej przestawnej, ponieważ zawiera ona migawkę oryginalnych danych. Ale ponieważ usunąłeś dane źródłowe, rozmiar pliku skoroszytu ulegnie zmniejszeniu.

Jeśli chcesz odzyskać dane źródłowe, po prostu kliknij dwukrotnie punkt przecięcia sum całkowitych dla tej tabeli przestawnej. Utworzy nowy arkusz roboczy i pokaże wszystkie dane użyte do utworzenia tej tabeli przestawnej.

#2 Nie zapisuj danych w Pivot Cache

Gdy zapisujesz plik z tabelą przestawną i danymi źródłowymi, zapisuje również pamięć podręczną przestawną zawierającą kopię danych źródłowych. Oznacza to, że zapisujesz dane źródłowe w dwóch miejscach: w arkuszu zawierającym dane oraz w pamięci podręcznej osi.

Istnieje możliwość niezapisywania danych w pamięci podręcznej i jej zamykania. Spowoduje to zmniejszenie rozmiaru pliku.

Aby to zrobić:

Zaznacz dowolną komórkę w tabeli przestawnej.
Przejdź do Analiza -> Tabela przestawna -> Opcje.
W oknie dialogowym Opcje tabeli przestawnej przejdź do karty Dane.
Odznacz opcję - Zapisz dane źródłowe z plikiem.
Zaznacz opcję - Odśwież dane podczas otwierania pliku.
- Jeśli nie zaznaczysz tej opcji, po otwarciu skoroszytu programu Excel dane nie zostaną odświeżone i nie będziesz mógł korzystać z funkcji tabeli przestawnej. Aby to zadziałało, musisz ręcznie odświeżyć tabelę przestawną.

Gdy to zrobisz, program Excel nie zapisze danych w pamięci podręcznej osi, ale odświeży je, gdy następnym razem otworzysz skoroszyt programu Excel. Twoje dane mogą znajdować się w tym samym skoroszycie, innym skoroszycie lub w zewnętrznej bazie danych. Gdy otworzysz plik, dane zostaną odświeżone, a pamięć podręczna Pivot zostanie ponownie utworzona.

Chociaż może to prowadzić do zmniejszenia rozmiaru pliku, otwarcie pliku może potrwać nieco dłużej (ponieważ program Excel odtwarza pamięć podręczną).

Zobacz też: Zapisywanie danych źródłowych za pomocą tabeli przestawnej.

Uwaga: Jeśli używasz tej opcji, upewnij się, że źródło danych jest nienaruszone. Jeśli usuniesz dane źródłowe (ze skoroszytu lub dowolnego zewnętrznego źródła danych), nie będzie można odtworzyć pamięci podręcznej przestawnej.

#3 Udostępnianie Pivot Cache dla lepszej wydajności

Jeśli przypadkowo (lub celowo) znajdziesz się w sytuacji, w której masz zduplikowaną pamięć podręczną osi i chcesz usunąć duplikat i udostępnić pamięć podręczną osi, oto kroki, aby to zrobić:

Usuń jedną z tabel przestawnych, dla których chcesz usunąć pamięć podręczną. Aby to zrobić, wybierz tabelę przestawną i przejdź do Strona główna -> Wyczyść -> Wyczyść wszystko.
Teraz po prostu skopiuj tabelę przestawną, którą chcesz zduplikować, i wklej ją (w tym samym arkuszu lub w osobnym arkuszu).
- Zaleca się wklejenie go w osobnych arkuszach roboczych, aby nie nakładał się na drugą tabelę przestawną po jej rozwinięciu. Chociaż czasami kopiuję to obok siebie, aby porównać różne poglądy. Ta kopia wklejania tabeli przestawnej zapewnia udostępnienie pamięci podręcznej przestawnej.