Bezpłatny fragment - Sześć formuł Excela, które powinien znać każdy księgowy

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,

— JPK_KR z Excelem

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).

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”.

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”.

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).

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,

— Rachunek Przepływów Pieniężnych zasilany JPK_KR i Power Query

Formuły Excela dla księgowych

Projektując raporty finansowo księgowe praktycznie wykorzystywałam wciąż te same formuły, stąd pomysł aby je przedstawić i opowiedzieć o tym, jak bardzo są użyteczne dla potrzeb księgowych. Formuły, które będą omówione w niniejszej publikacji, są wymienione poniżej:

— SUMA. WARUNKÓW — sumowanie wartości wg wskazanych kryteriów,

— SUMY.CZĘŚCIOWE — sumowanie przefiltrowanych danych,

— JEŻELI — analizowanie i wyświetlenie danych wg wskazanych założeń,

— WYSZUKAJ. PIONOWO — proste przeszukiwanie danych,

— FILTRUJ — zaawansowane przeszukiwanie danych,

— JEŻELI. BŁĄD — ukrywanie informacji o błędach.

Wymienione formuły są proste do zrozumienia i łatwe do nauczenia. Nie ma potrzeby angażowania dużej ilości czasu, aby swobodnie się nimi posługiwać. W kolejnych rozdziałach zostały wyczerpująco omówione poszczególne formuły ilustrowane praktycznymi przykładami.

Sumowanie wg określonych warunków

Formuła SUMA. WARUNKÓW, jest w mojej opinii najbardziej księgową formułą. Jej użyteczność księgowa sprawia, że może być wykorzystana niemalże w każdym raporcie finansowo księgowym. Zaletą tej formuły jest to, że można dodawać do siebie wartości spełniające tylko określone warunki. W księgowości często właśnie istnieje potrzeba dodawania wartości wg określonych kryteriów. Tak na przykład dzieje się gdy chcielibyśmy dodawać obroty wg wskazanych kont księgowych. Ponadto, w księgowości mogą dochodzić kolejne warunki, na przykład suma wskazanych kont w danym okresie obrachunkowym, wg określonego działu. Formuła SUMA. WARUNKÓW pozwala na rozbudowywanie jej o kolejne warunki. Chcąc skorzystać z tej formuły, trzeba podać określone parametry:

— zakres, z którego mają być pobierane wartości, które będą do siebie dodawane wg podanych w formule kryteriów,

— zakres kryteriów, wśród których formuła będzie przeszukiwała i wybierała, wg podanego kryterium,

— podane kryterium.

Jeśli potrzebnych jest więcej warunków, wówczas w formule podajemy:

— zakres, z którego mają być pobierane wartości, które będą do siebie dodawane wg podanych w formule kryteriów,

— zakres1 kryteriów, wśród których formuła będzie przeszukiwała i wybierała, wg podanego kryterium1,

— podane kryterium dla zakresu1,

— zakres2 kryteriów, wśród których formuła będzie przeszukiwała i wybierała, wg podanego kryterium2,

— podane kryterium dla zakresu2 …..

Należy jednak pamiętać, że im więcej podamy kryteriów (i zakresów), tym trudniej może być do uzyskania wynik.

Przykład 1:

W pliku JPK_KR znajduje się sekcja ZOiS (zestawienie obrotów i sald), w której zbierane są dane:

— konto księgowe,

— Bilans otwarcia Wn,

— Bilans otwarcia Ma,

— Obroty Wn,

— Obroty Ma,

— Obroty Narastająco Wn,

— Obroty Narastająco Ma,

— Saldo Wn,

— Saldo Ma.