Wyobraźmy sobie gotowy raport, po lekturze którego Szef prosi, aby przygotować to samo zestawienie dla listy TOP 100 produktów. Każdy szanujący się użytkownik Excela zacznie się zastanawiać, jak nie zmieniając raportu odfiltrować te dane i to bez ręcznego wybierania 100 produktów z filtru. Przedstawiam 2 rozwiązania tego wyzwania – przy pomocy zarówno Power Pivot jak i Power Query.

Power Pivot

Przygotowanie danych oraz tabeli

Moje dane tym razem pochodzą z GUS , jest to liczba zawieranych małżeństw w latach 2013-2015.

By wykonać dzisiejsze zadanie tworzę dwie tabele, które połączę modelem danych w narzędziu Power Pivot. Pierwsza to dane w podziale na województwa, druga to lista województw (które będą moim filtrem, więc wybieram kilka z nich, nie wszystkie). Oba zakresy danych formatuję jako tabele.

Sformatowane tabele dodaję do modelu danych oraz łączę je ze sobą.

Klikam w obszarze tabeli > Power Pivot > Tabele > Dodaj do modelu danych , powtarzam tę czynność dla obu tabel.

Następnie w oknie Power Pivot łączymy obie tabele ze sobą Projekt > Relacje > Utwórz relację > Tworzymy relację jeden do wielu pomiędzy województwami z obu tabel >OK

Eksport danych w postaci tabeli przestawnej do Arkusza

Gdy tabele są połączone eksportujemy je w postaci tabeli przestawnej do nowego Arkusza. Okno Power Pivot dla programu Excel > Narzędzia główne > Tabela przestawna > Tabela przestawna

W nowym arkuszu Excel tworzę tabelę przestawną. W polu filtrów umieszczam z zawężoną listą województw. W pole wierszy wybieram również Województwo, w pole wartości umieszczam sumę z Miasto.

Wybieram, już w tabeli przestawnej, filtr raportu, wybieram All, by zobaczyć zawartość filtru (dokładnie województwa z mojej tabeli). Wybieram Zaznacz wiele elementów > Odznaczam puste > OK.

W wyniku tabela przestawna powinna się prezentować następująco (pojawiają się jedynie województwa te które występowały w „małej tabeli”).

Aby raport tabeli przestawnej działał prawidłowo również z nowymi danymi, muszę sprawić by nowe wartości były brane pod uwagę. Klikam prawym przyciskiem myszy na jednym z województw w obszarze raportu tabeli przestawnej >Ustawienia pól > Dołącz nowe elementy do filtru ręcznego.

Power Query

Ładowanie danych z tabeli do zapytania Power Query

Gdy znajduję się w obszarze tabeli (z filtrami województw) z danymi przechodzę do Dane > Pobieranie i przekształcanie > Z tabeli. W tym momencie zostało otwarte okno programu Power Query.

W narzędziach głównych wybieram > Zamknij i załaduj > Zamknij i załaduj do…

Następnie pojawia się poniższe okno „Ładowanie do”, wybieram Utwórz tylko połączenie > OK.

Następnie dodaję do Power Query również drugą tabelę z danymi. Ustawiam się myszą w jej obszarze i powtarzam jak w przypadku wcześniejszej tabeli Dane > Pobieranie i przekształcanie > Z tabeli.

Następnie w oknie Power Query przechodzę do Narzędzia główne > Połącz > Scal zapytania. W tym momencie stworzę połączenie pomiędzy dwiema tabelami.

Zaznaczam kolumny w jednej i drugiej z tabel, przy pomocy których będą się one ze sobą łączyć (zaznaczone na zielono). Wybieram rodzaj sprzężenia > Wewnętrzne.

Przechodzę do narzędzi głównych i stworzone sprzężenie dodam do modelu danych aby później móc się z nim bezpośrednio połączyć . Wybieram > Zamknij i załaduj > Zamknij i załaduj do… Zaznaczam tym razem jak na poniższym rysunku: Utwórz tylko połączenie oraz dodaj dane do modelu danych > Załaduj.

Eksport danych do tabeli przestawnej

Dane pokażę w postaci raportu tabeli przestawnej łącząc się bezpośrednio z modelem danych > Wstawianie > Tabela przestawna > Użyj zewnętrznego źródła danych > Wybierz połącznie > Tabele > Tabele w modelu danych skoroszytu > Otwórz . Dzięki tej operacji mam dostęp do wszystkich danych w moim modelu , zarówno do samego wyniku sprzężenia jak i zaimportowanych tabel.

Plik do pobrania

Pobierz plik filtrowanie danych na podstawie tabeli zewnętrznej i wstaw swoje dane.