Fragmentatory w Excelu są najprostszym sposobem na filtrowanie danych na dashboardach przygotowanych za pomocą tabel i wykresów przestawnych. Niestety ich rozwój zatrzymał się w 2010 roku (nie licząc osi czasu i wyboru wielokrotnego) i próżno w najnowszym Excelu szukać dla nich nowych opcji. Korzystając z tego, co jest, da się czasem wyczarować coś niestandardowego, np. fragmentator hierarchiczny.
Jakie zalety ma filtr hierarchiczny?
- Wyszukiwanie za pomocą drzewa rozwijanego ułatwia przeglądanie danych.
- Możemy dokonywać niezależnych wyborów na różnych poziomach hierarchii, np. „dolnośląskie” i „Żyrardów”
Możemy użyć wyszukiwarki, aby dodawać kolejne wyszukiwane elementy na każdym poziomie (np. znajdź „Warszawa” > utwórz filtr > znajdź „Kraków” > Dodaj bieżące zaznaczenie do filtru.
Zwykła tabela przestawna vs Tabela przestawna na modelu danych z Power Pivot
W przykładzie dodamy hierarchię na półkę filtr tabeli przestawnej. Nie da się tego osiągnąć w zwykłej tabeli przestawnej, gdyż pola, które trafiają na półkę Filtry, działają w standardowy sposób.
Odpowiedzią będzie dodanie danych przez model danych edytowany w Power Pivot. Jak pisałem w artykule o nowościach w Excelu 2019, Microsoft zdecydował, że Power Pivot znajdzie się w każdej wersji Office 365 (tj. stacjonarnej wersji Excela, ale kupowanej w modelu subskrypcyjnym – ja sam mam taką wersję), więc Power Pivot będzie miała rosnąca liczba użytkowników. Ci, którzy mają Excela instalowanego tradycyjnie (tzw. stand-alone) znajdą Power Pivot pod warunkiem posiadania wersji Professional.
Krok 1: Dodaj dane do modelu danych
Istnieje kilka sposobów na dodanie danych do modelu danych. Najlepszym rozwiązaniem jest utworzenie zapytania w Power Query i wybranie opcji Utwórz tylko połączenie i zaznaczenie checkboxa Dodaj te dane do modelu danych (więcej o tych opcjach w artykule o wstawianiu tabeli przestawnej na zapytaniach Power Query).
Krok 2: Zbuduj hierarchię w Power Pivot
Nawet bez budowy modelu danych (rozumianego jako kombinacja tabel, relacji ich łączących i miar), możemy skorzystać z opcji budowy hierarchii. Ikonę znajdziesz w oknie Power Pivot > widok diagramu > w prawym górnym rogu tabeli.
Krok 3: Umieść hierarchię na półce filtry w tabeli przestawnej
Krok 4: Wstaw fragmentator na wszystkich polach hierarchii
Wszystkie pola, które są w hierarchii, muszą mieć swój odpowiednik na fragmentatorze. Excel umożliwia wstawienie takiego fragmentatora jednym kliknięciem.
Krok 5: Podłącz fragmentator do wszystkich tabel i wykresów przestawnych na dashboardzie
Najważniejszym ustawieniem fragmentatora jest polecenie Połączenia raportu. Dzięki niemu można fragmentator podłączyć do innych tabel opartych o to samo źródło (w naszym przypadku o model danych).
Krok 6: Ukryj fragmentatory
Jeśli fragmentatory nie są potrzebne, możesz je spokojnie ukryć: Narzędzia główne > Znajdź i zaznacz > Okienko zaznaczenia. Ich ukrycie nie zmienia ich działania.
Krok 7: Przetestuj działanie hierarchicznego fragmentatora
Wybór na filtrze tabeli przestawnej powoduje automatyczne zaznaczenie tego wyboru na fragmentatorach, co wpływa na połączone z nimi tabele i wykresy. To zachowanie omawiałem już w artykule o fragmentatorze działającym jak filtr tekstowy.
Przetestuj fragmentator hierarchiczny Excel
Tu możesz pobrać plik Excel z gotowym filtrem hierarchicznym i sprawdzić jego działanie.
Znasz inne ciekawe triki na tabelach przestawnych?
Podziel się w komentarzu.
