Sześć formuł Excela, które powinien znać każdy księgowy - Magdalena Chomuszko

Co znajdziesz w tej publikacji

Excel jest bardzo rozbudowaną aplikacją, wykorzystywaną szeroko w biznesie. Poznanie całego spektrum funkcjonalności Excela jest prawie niemożliwe. Jednak Excel ma wiele zalet, a jedną z nich jest to, że można z niego swobodnie korzystać tylko w ramach wycinka jego szerokich możliwości. Innymi słowy, wystarczy poznać kilka formuł i umieć je zastosować, aby można było uznać, że mamy użyteczne narzędzie rzetelnie wspierające nas w naszej pracy.

Tak właśnie można korzystać z Excela w pracy księgowego. Księgowy ma bardzo duży zakres zadań, które musi realizować z racji swoich obowiązków i Excel mógłby go wesprzeć w wielu tych zadaniach. Poznanie Excela na poziomie zaawansowanym może być bardzo czasochłonne, więc wielu księgowych odkłada "na jutro" poznanie tego narzędzia. Ale fakt jest taki, że można dobrze poznać kilka formuł Excela i to już wystarczy, by możliwe było projektowanie nawet zaawansowanych raportów księgowych.

W oparciu o moje doświadczenie zawodowe wybrałam sześć formuł Excela, które w mojej opinii są bardzo użyteczne, a których znajomość może wesprzeć znacząco pracę księgowego. W swojej praktyce zawodowej przygotowałam dziesiątki raportów finansowo - księgowych, w których właśnie wykorzystywałam formuły, za pomocą których można:

- bardzo precyzyjnie dodawać do siebie tylko wybrane wartości,

- można wyszukiwać elementy spełniające określone kryteria,

- można prezentować sumę tylko wyfiltrowanych kwot.

Pragnę również dodać, że wszelkie omawiane w tej publikacji przykłady są prezentowane w oparciu o dane w pliku JPK_KR. Moją intencją jest skupienie się tylko na obszarach finansowo księgowych, a korzystanie z danych w pliku JPK_KR spełniają ten warunek. W niniejszej pozycji korzystam z danych w pliku JPK_KR(1), ale zapewniam, że umiejętności uzyskane po zapoznaniu się z niniejszą książeczką i przykładami w niej zawartymi na pewno będzie można wykorzystać także dla danych z nowego pliku JPK_KR_PD, który ma zacząć obowiązywać od 1.01.2025.

Ponadto, do publikacji dołączony jest plik Excela ze wszystkimi omawianymi tutaj przykładami. Plik ten można pobrać i wykorzystać w celach edukacyjnych, lub praktycznych.

Przygotowanie danych dla raportów księgowych

Na wstępie parę słów wyjaśnienia czym są pliki JPK dla tych, którzy dopiero w temat wchodzą i mogą nie wiedzieć dlaczego jakieś pliki mają być doskonałym źródłem danych dla raportów księgowych. Otóż pliki JPK (Jednolity Plik Kontrolny) to ustandaryzowane dane pobierane z programów komputerowych wykorzystywanych w biznesie. Służą one do różnorodnych rozliczeń podatków. Ich olbrzymią zaletą jest to, że mają tę samą strukturę bez względu na to, z jakiego programu zostały wygenerowane. Można je łatwo otworzyć w Excelu, zawierają wszystkie kluczowe dane potrzebne by zaprojektować większość raportów księgowych. Pliki JPK wciąż są postrzegane jako przykry obowiązek wynikający z przepisów, służący kontroli i nie wszyscy widzą w nich olbrzymi potencjał jako źródło danych dla raportów, analiz, czy zestawień księgowych.

Księgowi powinni mieć właśnie wiedzę o tym, że dane w plikach JPK doskonale nadają się jako źródła dla raportów finansowo - księgowych. Szczególnie JPK_KR posiada niemalże wszystkie niezbędne dane, by zasilać raporty dla księgowości. Obecnie, w księgowości wykorzystywany jest JPK_KR(1) obowiązujący od lipca 2016 roku, jako plik JPK do kontroli na żądanie. Od 1.01.2025 wejdzie w życie nowy plik JPK_KR_PD (księgi rachunkowe i podatek dochodowy), który zawarty jest w strukturze JPK_CIT i będzie on jeszcze lepszym źródłem danych dla raportów księgowych niż ten obecnie obowiązujący. W tej publikacji wykorzystywane będą dane w pliku JPK_KR(1), jednak sposoby użycie ich do projektowania raportów finansowo - księgowych jest takie samo jak użycie danych z nowego pliku JPK_KR_PD.

Metody pobierania danych z pliku JPK_KR(1) zostały opisane w dwóch moich wcześniejszych publikacjach:

- Excel tylko dla księgowych[1],

- JPK_KR z Excelem[2]

Zachęcam do zapoznania się z tymi pozycjami. Wiedza zawarta w tych książkach, będzie cały czas aktualna, ponieważ wejście w życie nowego JPK_KR_PD nie spowoduje dezaktualizacji struktury JPK_KR(1). Wynika to z tego, że obie te struktury (nowa i obecnie obowiązująca) różnią się miedzy sobą schemą i można przyjąć, że przez wiele lat będą obowiązywały obie struktury JPK_KR równolegle, więc warto znać je obie.

Na potrzeby niniejszej publikacji zostanie zaprezentowana najprostsza metoda otworzenia danych z JPK_KR w Excelu. Pliki JPK_KR mają format XML, a to jest format bardzo dobrze rozumiany przez Excel. Aby szybko otworzyć w Excelu plik JPK_KR, wystarczy przeciągnąć myszą plik JPK w obszar otwartego arkusza Excela i następnie upuścić go w dowolnym miejscu tego arkusza (rys. 1).

Rysunek 1 Otwieranie pliku JPK w Excelu

Po przeciągnięciu pliku JPK_KR w obszar Excela, pojawia się komunikat "Otwieranie pliku XML" (rys. 2), który zamykamy korzystając z przycisku "OK".

Rysunek 2 Otwieranie pliku JPK w Excelu

Kolejne okienko, jakie zostanie otworzone to okienko "Microsoft Excel" (rys. 3), również zamykamy przyciskiem "OK". W tym okienku można także zaznaczyć checkbox "W przyszłości nie pokazuje tego komunikatu".

Rysunek 3 Otwieranie pliku JPK w Excelu

Sens komunikatu, jaki jest wyświetlony na rys. 3 został wyjaśniony w wyżej wymienionych moich publikacjach. Zrozumienie tego komunikatu nie jest istotne z punktu widzenia tematów omawianych w niniejszej publikacji.

Po wykonaniu powyższych kroków zobaczymy w arkuszu Excela uporządkowane dane z pliku JPK_KR (rys. 4).

Rysunek 4 Dane z pliku JPK otworzone w Exelu

Na tak zorganizowanych danych można już projektować nawet zaawansowane raporty finansowo - księgowe. Jednak trzeba w tym miejscu powiedzieć, że takie otworzenie pliku jest użyteczne tylko dla plików o małych i średnich rozmiarach, czyli plików nie przekraczających 200 GB. Pliki XML o większych rozmiarach powinny być otwierane za pomocą funkcjonalności Power Query Excela. Wykorzystanie Power Query do obróbki danych z dużych plików JPK zostało opisane w moich dwóch innych pozycjach:

- Excel tylko dla księgowych[3],

- Rachunek Przepływów Pieniężnych zasilany JPK_KR i Power Query[4]