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.

[grwebform url="https://app.getresponse.com/view_webform_v2.js?u=N3vX&webforms_id=9648602" css="on" center="off" center_margin="0"/]

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.