Fragmentator dla roku fiskalnego! Rok fiskalny zwykle nie pokrywa się z tym kalendarzowym

Z lekcji dowiesz się:

• Jak stworzyć tabelę przestawną?

• Co to jest Fragmentator?

• Jak filtrować dane po roku fiskalnym, a nie kalendarzowym?

Założony w przykładzie rok fiskalny zaczyna się we wrześniu. Oto przykładowe dane zawarte na rys. 1.

(Rys. 1) Przykładowe dane

W pierwszym kroku należy stworzyć tabelę przestawną na podstawie danych sprzedażowych z rys. 1. W tym celu zaznacz pojedynczą komórkę w obszarze tabeli i wybierz polecenie Tabela przestawna z karty Wstawianie (rys. 2).

(Rys. 2) Polecenie Tabela przestawna z karty Wstawianie

Otworzy się okno Tworzenia tabeli przestawnej, gdzie należy wybrać miejsce wstawienia raportu w formie tabeli przestawnej. Wybierz Istniejący arkusz oraz wskaż konkretną komórkę, gdzie ma zostać wstawiona tabela przestawna. Tak ustawione parametry zatwierdź przyciskiem OK (rys. 3).

(Rys. 3) Okno Tworzenia tabeli przestawnej

Twoim celem jest podsumowanie sprzedaży po latach, czyli przeciągnij pole Data do obszaru etykiet wierszy i pole Przychód do obszaru podsumowań wartości. Dane te podziel dodatkowo pod kątem województw, czyli przeciągnij pole Województwa do obszaru etykiet kolumn (jak na rys. 4).

(Rys. 4) Tworzenie tabeli przestawnej

Otrzymasz tabelę przestawną przedstawioną na rys. 5, będącą podsumowaniem wartości sprzedaży po latach.

(Rys. 5) Tabela przestawna

Teraz zajmij się edycją tabeli przestawnej i dopasowaniem wyświetlania danych do Twoich potrzeb. W pierwszej kolejności zmień pokazywanie dat, mianowicie nie potrzebujesz każdego dnia miesiąca, wystarczą miesiące i lata. W tym celu na dowolną z dat kliknij prawym przyciskiem myszki i z podręcznego menu wybierz polecenie Grupuj (rys. 6).

(Rys. 6) Polecenie Grupuj w podręcznym menu

Otworzy się okno Grupowania, gdzie w polu Według zaznacz Miesiące, a następnie przytrzymując klawisz Ctrl zaznacz Lata. Tak ustawione parametry grupowania zatwierdź przyciskiem OK (rys. 7).

(Rys. 7) Parametry grupowania

Otrzymasz tabelę przestawną z pogrupowanymi danymi według miesięcy i lat przedstawioną na rys. 8.

(Rys. 8) Tabela przestawna z pogrupowanymi danymi

W tabeli masz sumy końcowe, które nie będą jednak potrzebne. Aby je wyłączyć rozwiń polecenie Sumy końcowe (punkt 2 na rys. 9) z karty Projektowanie (punkt 1), a następnie wybierz polecenie Wyłącz dla wierszy i kolumn (punkt 3).

(Rys. 9) Ścieżka dostępu do polecenia Wyłącz dla wierszy i kolumn

Kolejnym etapem jest formatowanie liczb, czyli zaznacz dowolną komórkę z przychodem i kliknij prawym przyciskiem myszy, a następnie z podręcznego menu wybierz polecenie Format liczby (rys. 10).

(Rys. 10) Polecenie Format liczby

Otworzy się okno Formatowania komórek, gdzie w polu Kategoria wybierz Walutowe i w polu Miejsca dziesiętne ustaw wartość 0. Tak ustawione parametry formatowania komórek zatwierdź przyciskiem OK (rys. 11).

(Rys. 11) Formatowanie komórek – walutowe

Otrzymasz tabelę przestawną z formatowaniem walutowym przychodu przedstawioną na rys. 12.

(Rys. 12) Tabela przestawna z przychodem sformatowanym jako waluta

Twoim celem jest możliwość filtrowania wyników po latach fiskalnych, a nie kalendarzowych. Gdyby jednak chodziło o rok kalendarzowy, to w bardzo łatwy sposób można przefiltrować te dane, mianowicie kliknij na ikonkę z trójkątem obok nazwy kolumny Lata, następnie w podręcznym menu zaznacz checkbox przy roku, który Cię interesuje np. 2017 (rys. 13).

(Rys. 13) Filtrowanie danych po roku 2017

Aby mieć możliwość filtrowania danych po roku fiskalnym, musisz dołożyć dodatkową kolumnę w danych wejściowych, a później dołożyć dodatkową opcję w tabeli przestawnej. Utwórz nową kolumnę o nazwie Rok Fis. obok tabeli z danymi wejściowymi. Aby otrzymać dane w tej kolumnie musisz sprawdzić czy aktualny miesiąc, to ten rozpoczynający nowy rok fiskalny. Możesz to zrobić przy użyciu funkcji JEŻELI. Należy sprawdzić, czy miesiąc z aktualnej daty jest większy bądź równy 9 (wrzesień) – pierwszy argument funkcji JEŻELI, czyli test_logiczny. Miesiąc z daty wyciągasz za pomocą funkcji MIESIĄC. Zapis testu logicznego (pierwszego argumentu) funkcji JEŻELI powinien wyglądać następująco: MIESIĄC([@Data])>=9

Jeśli warunek z testu jest spełniony, to chcesz, aby funkcja JEŻELI zwróciła wartość w stylu 2017/18 (drugi argument – wartość_jeżeli_prawda). Rok np. 2017 wyciągasz z danych za pomocą funkcji ROK (zapis ROK([@Data])) ale należy go połączyć ze znakiem slash (/) oraz tekstem 18 (druga część zapisu). Łączysz te dane za pomocą znaku ampersand (& - Shift+7). Znak slash zapisz w podwójnym cudzysłowie, aby Excel potraktował go jako tekst.

Następnie za pomocą znaku ampersand połącz znak slash z ostatnią częścią (18), którą zwróci funkcja TEKST.

Pierwszym argumentem funkcji TEKST jest wartość, czyli kolumna z której trzeba wyciągnąć dane (kolumna Data).

Drugi argument to format_tekst, czyli sposób zapisu roku jaki chcesz uzyskać – np. „rr”. Taki zapis spowoduje, że funkcja TEKST zwróci tylko 2 ostatnie cyfry roku. Zapis funkcji TEKST powinien wyglądać następująco: TEKST([@Data];"rr").

Natomiast zapis drugiego argumentu funkcji JEŻELI powinien wyglądać następująco: ROK([@Data])&"/"&TEKST([@Data];"rr")

Jeśli podejrzysz wynik funkcji TEKST w trybie edycji komórki za pomocą klawisza F9 zobaczysz tekst 17 (rys. 14).

(Rys. 14) Podgląd wyniku funkcji TEKST

Z podglądu wyników funkcji wyjdź za pomocą skrótu klawiszowego Ctrl+Z. Aby otrzymać wartość o jeden większą od aktualnego roku wyciągniętego z funkcji ROK - do tekstu otrzymanego z funkcji TEKST – dodaj wartość 1. Zapis całego drugiego argumentu funkcji JEŻELI wygląda następująco:

ROK([@Data])&"/"&TEKST([@Data];"rr")+1

W zapisie funkcji JEŻELI możesz podejrzeć wynik całego drugiego argumentu za pomocą klawisza F9. Otrzymana wtedy wartość to 2017/18 (rys. 15).

(Rys. 15) Podgląd wyniku drugiego argumentu funkcji JEŻELI

Trzeci argument funkcji JEŻELI (wartość_jeżeli_fałsz), to rok o jeden mniejszy niż aktualny, łamany przez wartość z funkcji TEKST, ale bez dodania wartości 1. Podsumowując - możesz skopiować zapis drugiego argumentu funkcji JEŻELI i wprowadzić w nim małe zmiany. Mianowicie od roku uzyskanego z funkcji ROK odejmij wartość 1, natomiast do tekstu uzyskanego z funkcji TEKST - nie dodawaj żadnej wartości. Zapis trzeciego argumentu funkcji JEŻELI (wartość_jeżeli_fałsz) wygląda następująco:

ROK([@Data])-1&"/"&TEKST([@Data];"rr")

Cały zapis formuły powinien wyglądać:

=JEŻELI(MIESIĄC([@Data])>=9; ROK([@Data])&"/"&TEKST([@Data];"rr")+1; ROK([@Data])-1&"/"&TEKST([@Data];"rr"))

Tak przygotowaną formułę zatwierdź przyciskiem Enter. Otrzymane dane w kolumnie Rok Fis. przedstawia rys. 16.

(Rys. 16) Dane w kolumnie Rok Fis.

W kolumnie Rok Fis. znajdują się dane dotyczące lat fiskalnych, czyli przełomów poszczególnych lat (2016/17, 2017/18 itd.).

Po wprowadzeniu dodatkowych danych do tabeli, na podstawie której stworzyłeś tabelę przestawną, musisz ją odświeżyć (nie ma w danych kolumny Rok Fis.). W tym celu kliknij prawym przyciskiem myszy w dowolnym punkcie tabeli przestawnej i z podręcznego menu wybierz polecenie Odśwież (rys. 17).

(Rys. 17) Odświeżanie tabeli przestawnej

Otrzymałeś zaktualizowane dane. Następnie dodaj Fragmentator, czyli wizualny filtr. W tym celu wybierz polecenie Wstaw fragmentator z karty Analiza tabeli przestawnej (rys. 18).

(Rys. 18) Polecenie Wstaw fragmentator

Otworzy się okno Wstawiania fragmentatora, w którym wybierz zakres danych, jakiego ma dotyczyć fragmentator (zaznacz checkbox przy Rok Fis.). Wybrane dane do fragmentatora zatwierdź przyciskiem OK (rys. 19).

(Rys. 19) Okno Wstawiania fragmentatora

Otrzymasz fragmentator z rozpisanymi latami fiskalnymi przedstawiony na rys. 20.

(Rys. 20) Fragmentator z latami fiskalnymi

Możesz edytować wygląd fragmentatora w karcie Fragmentator, w grupie poleceń Przyciski. W polu Kolumny wpisz wartość 2 (zaznaczone zieloną strzałką na rys. 21).

(Rys. 21) Zmiana wyglądu fragmentatora

Możesz również ręcznie zmienić wielkość fragmentatora, czyli dopasować jego obszar do ilości przycisków. Otrzymasz wtedy fragmentator przedstawiony na rys. 22.

(Rys. 22) Fragmentator po zmianach estetycznych

Teraz możesz wybrać na fragmentatorze dowolny rok fiskalny, co spowoduje, że tabela przestawna wyświetli dane z tego roku (czyli np. od 09.2018 do 08.2019) – rys. 23.

(Rys. 23) Dane w tabeli przestawnej z roku fiskalnego 2018/19

Standardowo we fragmentatorach możesz zaznaczyć kilka wartości jednocześnie, wystarczy, że przytrzymasz klawisz Ctrl w czasie zaznaczania poszczególnych lat. Otrzymasz podsumowanie wartości w tabeli przestawnej dla zaznaczonych lat fiskalnych (rys. 24).

(Rys. 24) Podsumowanie wartości przychodu w tabeli przestawnej dla dwóch lat fiskalnych

Polecamy także:

• Warunkowe wypełnianie danych w dół!
• Prognozowanie sprzedaży uwzględniające sezonowość!