Poznawanie nowych narzędzi powoduje, że często zastanawiam się, czy daną funkcjonalność da się utworzyć w programie Excel. Tym razem szukałem sposobu na ustawienie filtra typu Ten miesiąc lub Ten rok (charakterystycznego dla tabel przestawnych) do zdefiniowania filtru globalnego dla raportu.

Gdzie znaleźć filtr czasowy tabeli przestawnej w stylu Ten miesiąc, Ten rok itp.?

Kiedy wstawimy tabelę przestawną i wrzucimy pole rozpoznawane przez Excel jako data do tabeli przestawnej, otrzymamy dodatkowe opcje filtra tabeli przestawnej. Polecenie to działa zarówno dla zwykłej tabeli przestawnej, jak i tej utworzonej na modelu danych, edytowanym przez Power Pivot.

Gdzie można znaleźć globalny fragmentator czasowy (ang. slicer) tego typu?

Podobny filtr czasowy możemy znaleźć w aplikacji analitycznej Power BI Desktop, gdzie w ustawieniach fragmentatora czasowego znajdziemy filtr Względnie, który pozwala wybrać aktualny (oraz zeszły lub przyszły) okres, np. aktualny rok (tłumaczony w momencie pisania artykułu ze słowa „This” Year na „To” Rok ?).

Jak utworzyć w Excelu globalny filtr czasowy?

Rozwiązanie, do którego zmierzamy, będzie działać tylko dla standardowego kalendarza gregoriańskiego (czyli takiego, który wisi na ścianie; nie znajdzie tu zastosowanie kalendarz niestandardowy) w Excelu >= 2013 (2013, 2016, 365 i kolejnych). Funkcjonalność, którą opisuję, jest powiązana z filtrowaniem przez tabele przestawne w Excelu fragmentatora, którą poprzednio opisywałem.

Co ciekawe, technika ta nie działa dla daty w przypadku standardowego fragmentatora (slicera). Po wstawieniu tabeli przestawnej, która ma filtrować fragmentator, który z kolei będzie filtrował kolejne tabele przestawne, mechanizm filtrowania nie działa prawidłowo.

Inaczej się ma sytuacja, gdy wstawimy fragmentator w postaci osi czasu (ang. timeline). Musimy tylko pamiętać, aby w połączeniach raportu ustawić filtrowanie wielu tabel.

Wówczas niespodziewanie mechanizm zaczyna działać – tabela przestawna staje się filtrem tabeli „slicera”, który filtruje raport. Poniżej przykład zastosowania fragmentatora dla tabeli przestawnej pokazującej wynik za dany rok w odniesieniu do planu z wykorzystaniem licznika Excel.

Kiedy przyda się filtr czasowy Od początku roku (YTD)?

Jedną z opcji filtru czasowego jest Od początku roku. Opcja ta pokazuje wyniki w odniesieniu do aktualnej daty systemowej. Kiedy wybrać wynik Ten rok a kiedy Od początku roku? Jeśli mamy w bazie danych lub tabeli źródłowej daty przyszłe (np. prognozy lub plany), wtedy opcja ta może okazać się zbawienna.

Ograniczenia tego rozwiązania

Niestety filtr nie odświeża się sam przy aktualizacji raportu i trzeba go przeładować – zdjąć i założyć ponownie. Dużo lepiej działałaby zatem kalkulacja w stylu Year-To-Date lub Month-To-Date, która dostępna jest w modelu danych Power Pivot i Power BI: TOTALYTD i TOTALMTD.

Pobierz plik Excel z filtrem czasowym

Tu możesz pobrać plik Excel z licznikiem i filtrem czasowym i sprawdzić jego działanie na Twoim komputerze.