Jednoczesne filtrowanie wielu tabel przestawnych

Podczas tworzenia złożonych raportów, a zwłaszcza dashboardów w Microsoft Excel, bardzo często konieczne jest jednoczesne filtrowanie kilku tabel przestawnych na raz. Zobaczmy, jak można to zrealizować.

Metoda 1: Ogólny fragmentator do filtrowania przestawnych w tym samym źródle danych

Jeśli pivoty zbudowane są na podstawie jednej tabeli danych źródłowych, to najprościej jest użyć ich do jednoczesnego ich filtrowania Sekcja to graficzny filtr przycisków połączony ze wszystkimi tabelami przestawnymi jednocześnie.

Aby go dodać, wybierz dowolną komórkę w jednym z podsumowań i na karcie Analiza Wybierz drużynę Wklej plasterek (Analiza — Wstaw fragmentator). W oknie, które się otworzy, zaznacz pola obok kolumn, według których chcesz filtrować dane i kliknij OK:

Utworzony fragmentator domyślnie filtruje tylko element obrotowy, dla którego został utworzony. Jednak za pomocą przycisku Raportuj połączenia (Zgłoś połączenia) Zakładka Plaster (plasterki) do listy przefiltrowanych tabel możemy łatwo dodać inne tabele podsumowujące:

Metoda 2. Ogólny wycinek do filtrowania podsumowań w różnych źródłach

Jeśli twoje czopy zostały zbudowane nie według jednego, ale według różnych tabel danych źródłowych, powyższa metoda nie zadziała, ponieważ w oknie Raportuj połączenia wyświetlane są tylko te podsumowania, które zostały zbudowane z tego samego źródła.

Możesz jednak łatwo obejść to ograniczenie, korzystając z modelu danych (omówiliśmy to szczegółowo w tym artykule). Jeśli załadujemy nasze tabele do Modelu i tam je połączymy, to filtrowanie będzie dotyczyć obu tabel jednocześnie.

Załóżmy, że jako dane wejściowe mamy dwie tabele kosztów sprzedaży i transportu:

Załóżmy, że stoimy przed zadaniem zbudowania własnego podsumowania dla każdego z nich, a następnie przefiltrowania ich jednocześnie według miast o wspólnym przekroju.

Wykonujemy następujące czynności:

1. Przekształcanie naszych oryginalnych tabel w dynamiczne, inteligentne tabele za pomocą skrótu klawiaturowego Ctrl+T lub komendy Strona główna – Formatuj jako tabelę (Strona główna — Formatuj jako tabelę) i nadaj im imiona tablProdaji и zakładkaTransport Zakładka Konstruktor (Projekt).

2. Załaduj kolejno obie tabele do modelu za pomocą przycisku Dodaj do modelu danych na karcie Power Pivot.

Bezpośrednie połączenie tych tabel w modelu nie będzie możliwe, ponieważ chociaż dodatek Power Pivot obsługuje tylko relacje jeden-do-wielu, to znaczy, że jedna z tabel nie ma duplikatów w kolumnie, z którą łączymy się. To samo mamy w obu tabelach w terenie Miasto są powtórzenia. Musimy więc utworzyć kolejną pośrednią tabelę przeglądową z listą unikalnych nazw miast z obu tabel. Najłatwiej to zrobić za pomocą funkcji dodatku Power Query, która jest wbudowana w program Excel od wersji 2016 (a w przypadku programu Excel 2010-2013 jest pobierana bezpłatnie z witryny firmy Microsoft).

3. Po wybraniu dowolnej komórki w „inteligentnej” tabeli ładujemy je pojedynczo w Power Query za pomocą przycisku Ze stołu/zakresu Zakładka Dane (Dane — Z tabeli/zakresu) a następnie w oknie Power Query wybierz wł. Głównym Zespoły Zamknij i załaduj – Zamknij i załaduj (Strona główna — Zamknij i załaduj — Zamknij i załaduj do…) i opcja importu Po prostu stwórz połączenie (Tylko utwórz połączenie):

4. Łączymy oba stoły w jeden za pomocą polecenia Dane – Połącz zapytania – Dodaj (Dane — Połącz zapytania — Dołącz). Kolumny o tych samych nazwach w nagłówku zmieszczą się pod sobą (jak kolumna Miasto), a te, które nie pasują, zostaną umieszczone w różnych kolumnach (ale to nie jest dla nas ważne).

5. Usuń wszystkie kolumny z wyjątkiem kolumny Miastoklikając prawym przyciskiem myszy jego tytuł i wybierając polecenie Usuń inne kolumny (Usuń inne kolumny) a następnie usuń wszystkie zduplikowane nazwy miast, ponownie klikając prawym przyciskiem myszy nagłówek kolumny i wybierając polecenie Usuń duplikaty (Usuń duplikaty):

6. Utworzona lista referencyjna jest przesyłana do modelu danych za pośrednictwem Strona główna — Zamknij i załaduj — Zamknij i załaduj w (Strona główna — Zamknij i załaduj — Zamknij i załaduj do…) i wybierz opcję Po prostu stwórz połączenie (Tylko utwórz połączenie) i najważniejsze! – włącz checkbox Dodaj te dane do modelu danych (Dodaj te dane do modelu danych):

7. Teraz możemy, wracając do okna Power Pivot (zakładka powerpivot - przycisk Zarządzanie), Przełącz na Widok wykresu (widok diagramu) i połącz nasze tabele kosztów sprzedaży i transportu poprzez utworzony pośredni katalog miast (przeciągając pola między tabelami):

8. Teraz możesz utworzyć wszystkie wymagane tabele przestawne dla tworzonego modelu za pomocą przycisku tabela podsumowująca (Stół obrotowy) on Głównym (Dom) w oknie Power Pivot i zaznaczając dowolną komórkę w dowolnym osi na karcie Analiza przycisk dodawania plasterka Wklej plasterek (Analiza — Wstaw fragmentator) i wybierz krojenie w polu listy Miasto w dodanym katalogu:

Teraz, klikając znajomy przycisk Raportuj połączenia on Zakładka na plasterki (Slicer — Zgłoś połączenia) zobaczymy całe nasze podsumowanie, ponieważ są one teraz zbudowane na powiązanych tabelach źródłowych. Pozostaje włączyć brakujące pola wyboru i kliknąć OK – a nasz slicer zacznie jednocześnie filtrować wszystkie wybrane tabele przestawne.