Pamięć podręczna tabeli przestawnej to jeden z najważniejszych jej elementów, który należy dobrze poznać i zrozumieć. Choć działania pamięci podręcznej nie widać wprost, to ma ona bezpośrednie przełożenie na działanie tabel przestawnych oraz wielkość pliku. Szczególnie ważne jest to w sytuacji, gdy pracujemy na plikach zawierających wiele tabel przestawnych. Praca z pamięcią podręczną tabeli przestawnej to jeden z elementów optymalizacji działania pliku Excel.
Spis Treści
Czym jest pamięć podręczna tabeli przestawnej?
Pamięć podręczna tabeli przestawnej (Pivot Cache) to kopia danych źródłowych tabeli przestawnej przechowywana w pamięci programu Excel. Kopia ta tworzona jest automatycznie podczas tworzenia pierwszej tabeli przestawnej opartej na tym źródle danych. Stanowi przejście pomiędzy danymi źródłowymi a wynikami prezentowanymi w tabeli przestawnej. Jeżeli modyfikujemy tabelę przestawną to korzysta ona nie z danych źródłowych a właśnie z pamięci podręcznej i dopóki jej nie odświeżymy nie zobaczymy w tabeli nowych danych.
Cel funkcjonowania pamięci podręcznej w tabeli przestawnej.
Wykorzystanie pamięci podręcznej daje nam wiele korzyści oraz dodatkowych możliwości niż bezpośrednia praca na danych źródłowych. Jako kopia danych źródłowych dane przechowywane są jako zoptymalizowana tabela. W niej wszelkie obliczenia wykonywany są wielokrotnie szybciej niż bezpośrednio na arkuszu danych. Do tego wszystkie tabele przestawne oparte na tym samym źródle danych korzystają z tej samej pamięci podręcznej, więc wszystkie obliczenia są dostępne dla wszystkich tabel. Ponadto pamięć podręczna tabeli przestawnej nie aktualizuje się automatycznie przy zmianie danych źródłowych. W przeciwnym razie przy każdej zmianie komórki musielibyśmy czekać na odświeżenie tabel przestawnych, które musiałyby przeliczyć na nowo wszystkie wartości.
Jeżeli korzystasz z modeli danych w Power Pivot lub BI Desktop to możesz porównać działanie pamięci podręcznej do takiego jednotabelowego modelu danych. Możemy dodawać nowe miary (pola obliczeniowe), wszystkie obliczenia są robione w pamięci, a ich wyniki dostępne są natychmiast. Dlatego wyświetlenie ogromnej ilości wyników w tabeli przestawnej trwa krótką chwilę. Jeśli trwa dłużej to jest to po prostu czas wypełniania komórek wartościami i formatami a nie samego wyliczania wartości. W odróżnieniu od modelu danych nie możemy zajrzeć do pamięci podręcznej tabeli przestawnej.
Zalety i wady działania pamięci podręcznej tabeli przestawnej
Jak chyba wszystko na świecie tak i pamięć podręczna tabeli przestawnej ma swoje zalety i wady. Niektóre z nich zależą od tego jak korzystamy z tabel przestawnych.
- Pamięć podręczna zwiększa rozmiar pliku – w przypadku prostej tabeli i dużego źródła danych zwiększy nam się znacznie rozmiar pliku. Jednak w sytuacji, gdy korzystamy z wielu tabel działających na tej samej pamięci podręcznej nie będzie to tak zauważalne.
- Przyspiesza działanie tabel przestawnych – wszystkie obliczenia w pamięci podręcznej są dostępne dla wszystkich tabel, które z niej korzystają i są obliczane w momencie odświeżania źródła danych. Wyniki dla każdego układu wierszy i kolumn są już określone, dlatego modyfikacje tabel przestawnych są tak szybkie. Dodatkowo obliczenia zrobione są jednorazowo w pamięci, a nie odrębnie dla każdej tabeli przestawnej.
- Pozwala na tworzenie dodatkowych pól obliczeniowych – jeśli chcemy zobaczyć różnicę pomiędzy dwoma wartościami np. przychodem i kosztem to nie musimy liczyć tego w danych źródłowych. Taką wartość możemy wyliczyć w pamięci podręcznej i prezentować gotową wartość na dowolnym poziomie agregacji
- Wiele tabel przestawnych może korzystać z tej samej pamięci podręcznej – dzięki temu utworzone pola obliczeniowe czy grupowania dostępne są we wszystkich tabelach
- Odświeżając jedną tabelę przestawną odświeżasz automatycznie wszystkie działające na tej samej pamięci podręcznej
- Możemy decydować czy w polach filtrów chcemy dalej widzieć elementy które już nie występują w danych źródłowych – dzięki temu nasze filtry nie psują się, gdy w danych nie ma aktualnie wartości, na które ustawiony był filtr.
- Pamięć podręczna tabeli przestawnej nie odświeża się automatycznie przy zmianie wartości w źródle danych – jak już pisałem unikamy wtedy ciągłych przeliczeń przy zmianie każdej komórki
- Możemy wyłączyć zapisywanie pamięci podręcznej w pliku i ustawić automatyczne załadowanie jej przy otwieraniu pliku – zmniejszy to rozmiar pliku, ale wydłuży czas jego otwierania
- Jeśli źródłem jest zakres komórek to należy się zastanowić czy jego rozmiar będzie się zmieniał – pamięć podręczna nie rozszerzy automatycznie zakresu komórek, jeśli na końcu danych dokleimy nowe wartości. W takich sytuacjach lepiej dane zdefiniować jako tabelę, która elastycznie dostosuje się do danych, a do pamięci podręcznej zawsze załaduje się w całości.
Jak utworzyć tabele przestawne korzystające z tej samej pamięci podręcznej
Sposób 1 – wybór identycznego źródła danych dla nowej tabeli przestawnej
Nasze źródło danych tabeli przestawnej to zazwyczaj zdefiniowana tabela danych lub zakres danych w arkuszu. Aby dwie tabele korzystały z tej samej pamięci podręcznej muszą mieć identyczne źródło danych. Excel sam weryfikuje czy wybrany zakres jest identyczny jak, któryś z już istniejących. Jeśli tak to nowa tabela przestawna działa na wykorzystywanej już pamięci podręcznej. Wtedy nie jest tworzona kolejna kopia danych w pamięci, a wszystkie obliczenia, pola obliczeniowe i grupowania są dostępne dla nowej tabeli.
Gdy naszym źródłem jest zdefiniowana tabela przestawna to ustawienie identycznego źródła dla nowej tabeli jest proste. Wystarczy jako źródło podać nazwę tabeli lub zaznaczyć całą tabelę. Dopóki nie zaznaczymy całej tabeli to nie pojawi się jej nazwa, więc wiemy, kiedy wybraliśmy poprawny zakres komórek.
W przypadku gdy źródłem danych jest zakres komórek musimy dokładnie zwracać uwagę na to które komórki zaznaczamy. Jeśli zaznaczymy o jeden wiersz za dużo lub pominiemy jakąś kolumnę z danymi to zakres ten nie będzie zgodny z dotychczas wykorzystywanym. W ten sposób powstanie odrębna pamięć podręczna, więc rozmiar pliku się zwiększy a tabele przestawne będzie trzeba odświeżać osobno. Nawet jeśli w danych zmieni się wartość komórki wykorzystywanej w obu tabelach to nie zaktualizuje się to w nich automatycznie.
Tworzenie nowych tabel przestawnych przez zaznaczanie za każdym razem na nowo zakresu danych jak widać nie jest wygodne. Nie dość, że wymaga trochę klikania, to łatwo o delikatną zmianę zakresu danych przez co utworzymy odrębną pamięć podręczną.
Sposób 2 – kopiowanie istniejących tabel przestawnych
Najprostszym i najszybszym sposobem na tworzenie tabel opartych o identyczne źródło jest po prostu ich kopiowanie. Nie dość, że zabezpieczamy się przed tworzeniem niepotrzebnych pamięci podręcznych, to często nowa tabela w części jest już gotowa, bo chcemy do niej tylko dorzucić dodatkowe pole, zmienić filtr, zmienić strukturę albo zwiększyć/zmniejszyć szczegółowość danych. Zachowane są też formatowania.
Jak sprawdzić, ile pamięci podręcznych jest utworzonych w skoroszycie?
Sposób 1 – ręcznie sprawdzić źródła wszystkich tabel przestawnych i wykresów przestawnych
Przeglądając źródła danych do tabel przestawnych i wykresów przestawnych możemy określić, ile występuje unikatowych zakresów danych. Problem w tym, że musimy tam zajrzeć do każdej tabeli/wykresu co będzie trudne, gdy mamy ich dziesiątki. Szybkie porównanie dużych zakresów też nie jest proste.
Sposób 2 – jedno polecenie w VBA
Dzięki VBA możemy szybko dowiedzieć się, ile jest pamięci podręcznych w danym skoroszycie. Wystarczy do tego jedno polecenie. Najpierw otwieramy Edytor Visual Basic za pomocą skrótu Alt+F11, a następnie w oknie Immediate które otwieramy skrótem Ctrl+G wklejamy tekst „? ActiveWorkbook.PivotCaches.Count” i wciskamy Enter. W linijce poniżej otrzymamy liczbę używanych pamięci podręcznych.
Kiedy nie chcemy współdzielić pamięci podręcznej w kilku tabelach przestawnych?
Są sytuacje, kiedy wykorzystujemy identyczne źródło danych, ale nie chcemy, aby tabele korzystały z tej samej pamięci podręcznej. Poniżej najczęstsze powody:
- Utworzyliśmy pola obliczeniowe i elementy obliczeniowe, które nie mają być widoczne we wszystkich tabelach przestawnych
- Nie chcemy grupować danych w ten sam sposób w każdej tabeli
- Nie chcemy odświeżać wszystkich tabel jednocześnie – jest to szczególnie przydatne, gdy zmieniliśmy coś w źródle danych (np. sposób wyliczenia jakiejś wartości) i chcemy to porównać z wcześniejszymi wartościami. Zamiast robić wtedy kopię danych czy pliku, wystarczy korzystać z odrębnych pamięci podręcznych.
Jak rozdzielić współdzieloną pamięć podręczną tabel przestawnych?
Sposób 1 – użycie kreatora tabel przestawnych do utworzenia nowej tabeli
Jest duże prawdopodobieństwo, że nie słyszałeś/aś o kreatorze tabel przestawnych. Jest to narzędzie od dawna ukryte w Excelu, zastąpione przez wygodniejsze rozwiązania. W tej sytuacji ma jednak znaczącą dla nas korzyść, pozwala łatwo rozdzielić pamięć podręczną tabel przestawnych.
Kreator tabel przestawnych możemy uruchomić przyciskiem, jeśli dodamy go sobie do paska szybkiego dostępu lub wstążki.
Następnie po jego uruchomieniu wybieramy, że źródło danych mamy Excelu i chcemy utworzyć tabele przestawną.
Następnie określamy zakres danych.
Gdy kliniemy „Dalej” a nasze źródło pokrywa się z już wykorzystywanem źródłem pojawi się poniższy komunikat. W tym miejscu możemy zadecydować czy tabele mają korzystać z tej samej pamięci, czy mają to być niezależne tabele. Chcąc rozdzielić pamięć podręczną wybieramy opcję „Nie”.
Na koniec wybieramy, gdzie chcemy umieścić tabelę przestawną.
Sposób 2 – Użycie kreatora tabel przestawnych do zmiany zakresu istniejącej tabeli
W tym przypadku mamy już tabelę z określonym źródłem i chcemy ją odpiąć od dotychczasowej pamięci podręcznej. Klikamy na dowolną komórkę w tabeli przestawnej a następnie na przycisk kreatora tabel przestawnych. Otwiera nam się okno z ostatnim etapem tworzenia tabel.
Klikamy Wstecz i zmieniamy zakres danych np. zmniejszając go o jeden wiersz. Klikamy Dalej a następnie Zakończ.
W ten sposób mamy odrębne pamięci, ale nie są one identyczne. Dlatego musimy powtórzyć powyższą czynność, tyle że teraz zmienić zakres z powrotem na właściwy. Dzięki temu mamy tabele przestawne działające na identycznym zakresie, ale korzystające z odrębnych pamięci podręcznych.
Sposób 3 – Gdy źródłem danych jest zdefiniowana tabela
W tym przypadku w jednej tabeli możemy mieć jako źródło nazwę tabeli, w drugiej zakres komórek. Aby to zrobić możemy po utworzeniu pierwszej tabeli na zakresie, przekształcić zakres na tabelę. Na niej postawić drugą tabelę przestawną. Jeśli mamy już tabelę, której nie chcemy przekształcać na zakres (bo może to popsuć formuły, połączenia itp.) to możemy postawić drugą tabelę przestawną na zakresie komórek w którym jest zdefiniowana tabela ale zakres musimy wpisać ręcznie. W przeciwnym razie zakres podmienia się na nazwę tabeli.
Jak zmniejszyć wielkość pliku z tabelami przestawnymi
Sposób 1 – usuń dane źródłowe
Tabela przestawna ma swoją pamięć podręczną, czyli kopię danych źródłowych przechowywaną w bardzo optymalny sposób. Dane źródłowe do jej działania nie są potrzebne dopóki nie musimy ich aktualizować. Dlatego jeśli chcesz udostępniać raport opierający się na ogromnej ilości wierszy z danymi to może lepiej te dane usunąć. Czytelnicy raportu nadal mogą przeglądać szczegółowe dane wklikując się w interesujące ich wartości w tabeli przestawnej. W ten sposób zobaczą wszystkie szczegóły składające się na tę wartość w takim układzie jak wyglądałoby to w danych źródłowych
Sposób 2 – Nie zapisuj danych w pamięci podręcznej tabeli przestawnej
Możemy zadziałać odwrotnie. Zamiast pozbywać się danych źródłowych to możemy wyłączyć zapisywanie danych w pamięci podręcznej oraz włączyć odświeżanie pliku podczas jego otwierania. Plik będzie mniejszy, ale będzie się otwierał trochę dłużej. Pozostają nam tu nienaruszone dane źródłowe. Opcje te zmieniamy wchodząc w Opcje tabeli przestawnej do zakładki Dane.
Podsumowanie
Działanie pamięci podręcznej tabeli przestawnej to zagadnienie, z którym każdy zaawansowany użytkownik Excela powinien się zapoznać. Pozwala to lepiej zrozumieć, jak działa cały program oraz odejść od postrzegania Excela jako interfejsu graficznego do obliczeń. Excel to niesłychanie skomplikowany program, w którym działa ogromna liczba różnych rozwiązań. Ich znajomość poszerza nasze możliwości w przygotowywaniu raportów oraz analiz. Pamięć podręczna tabeli przestawnej to jeden z przykładów, gdzie wiele osób ma błędne wyobrażenie tego jak Excel pracuje z danymi.
