Bezpłatny fragment - Excel tylko dla księgowych

Wstęp

Do tej pory przedstawiałam sposoby projektowania raportów, zasilanych danymi z plików JPK, w których wykorzystywane były określone, wcześniej już zaprojektowane wzory raportów. Innymi słowy, do konkretnych pozycji wskazanego wzoru raportu, należało dołączyć źródła danych, które znajdowały się w plikach JPK. Te metody opisałam z książce wydanej w 2019 roku, przez Wydawnictwo C.H. Beck, do której nabycia zachęcam, osoby zainteresowane projektowaniem raportów w oparciu o dane plikach JPK_KR.

Mam nadzieję, że każdy znajdzie w tej pozycji coś dla siebie, zarówno Użytkownicy początkujący, jak Ci zaawansowani.

Jednak Excel to mocne narzędzie i można z jego pomocą tworzyć wiele ciekawych analiz, raportów, czy zestawień.

Niniejsza książeczka, jest materiałem uzupełniającym bezpłatne szkolenie, które udostępniam każdemu chętnemu, kto napisze na adres: wirtuozksiegowosci@gmail.com wyrażając chęć poznania zagadnienia.

Prezentowane szkolenie przedstawia bardzo ciekawe, a mało jeszcze znane narzędzie w Excelu, jakim jest Power Query. W materiałach tych, podane są instrukcje krok po kroku, jak wykonać przykładowy raport, co w połączeniu z prezentacją na szkoleniu, powinno być przyjazną instrukcją dla każdego księgowego, nawet tego, który widzi Power Query po raz pierwszy.

Moje szkolenia są przeznaczone specjalnie dla księgowych, ponieważ są ukierunkowane na konkretne zastosowania funkcjonalności Excela w pracy księgowego. Poza tym, raporty i zestawienia jakie prezentuję na szkoleniach, są zasilane z plików JPK. Obecnie, dla każdego księgowego znajomość zawartości Jednolitych Plików Kontrolnych, to jedno z podstawowych wyzwań, ponieważ organy skarbowe podczas kontroli, będą prosiły właśnie o te pliki, jako dane do skontrolowania.

Także swobodne zarządzanie danymi w plikach JPK, to na pewno bardzo pożądana dziś umiejętność.

Uwaga: szkolenie i materiały opracowane są dla wersji Excela 2016 i wyższej.

Co jest wewnątrz pliku JPK_KR

Plik JPK_KR można nazwać inaczej elektroniczną księgą rachunkową. Schemat główny budowy tego pliku został przedstawiony na rys. 1 (pobrane ze strony Ministerstwa Finansów).

Zgodnie z Ustawą o Rachunkowości, w księgach rachunkowych znajdują się m.in.:

— Zestawienie Obrotów i Sald (ZOiS),

— Dziennik,

— Zapisy.

Ponadto, w pliku tym są także nagłówek, dane podatnika oraz bloki kontrolne. Wszystkie pliki JPK mają format XML. Sama struktura JPK_KR, w swojej surowej postaci niezbyt nadaje się do bezpośredniego zasilania raportów Excela, ponieważ zbudowana jest ona z rozdzielnych bloków, które bez formuł czy innych funkcjonalności Excela, są bardzo trudne do połączenia i logicznego wydobycia z nich danych.

Jednak same formuły Excela też nie zawsze wystarczają, czasami trzeba pewne informacje wprowadzić już wcześniej do samych danych, na etapie ewidencji księgowej w programie komputerowym. Ważne też jest to, aby dane, które trafiają do pliku JPK_KR były uporządkowane, transparentne i z tzw. wizją. A co najważniejsze, by były poprawne.

Trzeba tutaj jasno powiedzieć, że im bardziej uporządkowaną księgowość prowadzi księgowy, tym bardziej przydatne są dane w plikach JPK do wykorzystania ich podczas tworzenia raportów.

Pobranie danych

Pierwszą czynnością, jaką należy wykonać przygotowując jakikolwiek raport, jest pobranie danych. Ale nie tylko to. Dobrze jest, dla uporządkowania środowiska pracy, wcześniej przygotować sobie na dysku odpowiednie miejsce, w którym będzie przechowywany raport, jego źródła danych oraz dane archiwalne.

W tym celu zakładamy katalog na dysku, który można dowolnie nazwać, np. Raporty. Wewnątrz katalogu zakładamy podkatalog Archiwum, w którym będą przechowywane dane archiwalne, a także tworzymy czysty arkusz Excela. W kolejnym kroku, należy przygotować dane z programu finansowo — księgowego, które będą źródłem dla raportu. W tym celu, generujemy plik JPK_KR, a następnie zmieniamy jego nazwę. W przykładzie jest to nazwa Dane. Nazwa pliku JPK_KR z danymi powinna być prosta i krótka. I jak to zostało wcześniej napisane, zakładamy czysty plik Excela, dla potrzeb projektowanego raportu, dla którego wprowadzamy nazwę taką, aby wiadome było, jaki zawiera raport. W przykładzie plik ten został nazwany Monitoring kosztów. Katalog przygotowany do pracy, powinien wyglądać mniej więcej tak, jak na rys. 2

Teraz można rozpocząć pracę nad raportem. Dwukrotnie klikając na nowo założonym pliku Monitoring kosztów. xlsx, otwieramy skoroszyt Excela i przechodzimy do karty Dane (rys. 3).

Z karty Dane (u góry), wybieramy polecenie Nowe zapytanie, a następne polecenie: Z pliku i drugie polecenie: z pliku XML. (rys. 3). W kolejnym kroku, ujrzymy okno przeglądania zawartości naszego dysku, gdzie trzeba wyszukać i wskazać założony wcześniej katalog Raporty, a następnie zaznaczyć plik z danymi: Dane. xml i wprowadzić je do arkusza przyciskiem Importuj (rys. 4).

Po wykonaniu tych czynności ujrzymy okno Nawigatora, takie jak na rys. 5.

W oknie Nawigatora, widoczna jest cała struktura pliku JPK_KR. Jednak na potrzeby tworzonego raportu, nie są potrzebne wszystkie obszary tego pliku, a tylko niektóre, więc zaznaczamy tylko te, które chcemy wykorzystać i są to: Dziennik oraz KontoZapis. By móc zaznaczyć więcej niż jedną opcję, wpierw trzeba zaznaczyć pole Wybierz wiele elementów, które znajduje się w górnej części okna.

Trzeba tutaj podkreślić, że w tym oknie zaznaczamy tylko te elementy, które są niezbędne do tworzonego raportu. To, co zostało wskazane w oknie Nawigatora na rys. 5, dotyczy raportu Monitoring kosztów oraz innych, podobnych merytorycznie raportów.

Kolejnym krokiem jest wyedytowanie wybranych obszarów z pliku JPK_KR, za pomocą przycisku Edytuj, który znajduje się w prawym dolnym rogu okna Nawigatora (rys. 5).

Wstępne porządkowanie danych

Edycja danych, spowoduje pobranie i wyświetlenie wszystkich danych z zaznaczonych wcześniej obszarów i przedstawienie ich w formie tabel. Wybrane przez nas dwa obszary, są także dwiema tabelami, których nazwy są widoczne po lewej stronie okna (rys. 6).

W pierwszej kolejności porządkujemy dane, czyli np. usuwamy zbędne kolumny. Kolumny można usuwać na dwa sposoby:

— poprzez zaznaczenie kolumn, które mają być usunięte i wybranie polecenia: Usuń,

— poprzez zaznaczenie kolumn, które mają pozostać i wybranie polecenia: Usuń inne kolumny.

W obu przypadkach polecenie usuwania wybieramy z menu podręcznego, które otwieramy poprzez ustawienie się na nagłówku tabeli i kliknięcie na nim prawym przyciskiem myszy. To, z którego polecenia skorzystamy jest zależne od tego, jaką czynność będzie łatwiej wykonać. Jeśli chcemy pozostawić np. trzy kolumny z piętnastu, wówczas łatwiej będzie zaznaczyć te trzy kolumny i wybrać polecenie Usuń inne kolumny.

Zaimportowane dane znajdują się obecnie w dwóch osobnych tabelach, pomiędzy którymi można się przełączać klikając po lewej stronie okna na ich nazwy: Dziennik lub KontoZapis.

Na potrzeby tworzonego raportu, pozostawiamy następujące kolumny:

w tabeli Dziennik:

— NrZapisuDziennika,

— OpisDziennika,

— NrDowoduKsiegowego,

— RodzajDowodu,

— DataOperacji,

— DataDowodu,

— DataKsiegowania.

W tabeli KontoZapis:

— NrZapisu,

— KodKontaWinien,

— KwotaWinien,

— OpisZapisuWinien,

— KodKontaMa,

— KwotaMa,

— OpisZapisuMa,

— KwotaWinienWaluta,

— KodWalutyWinien,

— KwotaWalutyMa,

— KodWalutyMa.

Dla samego raportu Monitoring kosztów, można by pozostawić dużo mniej kolumn, ale pozostawiając więcej kolumn, tworzymy sobie fundament, na bazie którego będzie można projektować więcej niż jeden podobnych raportów.

Po usunięciu zbędnych z punktu widzenia tworzonego raportu, kolumn, kolejną bardzo ważną kwestią jest, zamiana kwot z formatu tekstowego, na format waluty. Aby to było możliwe, najpierw trzeba w kolumnach KwotaWinien i KwotaMa zamienić kropki na przecinki.

W pierwotnej wersji danych z pliku JPK_KR, znakiem rozdzielającym wartości stałych od dziesiętnych są kropki, a w takiej postaci nie jest możliwa zmiana typu danych z tekstowych na walutowe. Aby dokonać tej zamiany, należy wybrać u góry zakładkę Przekształć, a następnie polecenie: Zmienianie wartości. W okienku, jakie zostanie wyświetlone w górnym polu wpisujemy kropkę, a w dolnym, przecinek i zatwierdzamy przyciskiem OK (rys. 7).

Po zamianie kropek na przecinki, kolumny KwotaWinien i KwotaMa będą już prawidłowo przygotowane, aby można było zamienić ich zawartość z typu tekstowego na walutowy. Ta zamiana będzie nam bardzo potrzebne później, podczas budowania tabel przestawnych.

Aby wykonać tę zmianę, ponownie zaznaczamy obie kolumny KwotaWinien i KwotaMa i wybieramy u góry polecenie: Przekształć — Typ danych. Następnie zamieniamy tekst na waluty.

Zanim załadujemy tabele do arkuszy, warto zwrócić uwagę na prawą część okna edytora zapytań (rys. 9), w którym znajduje się rejestrator wykonanych kroków zapytania. Jest to bardzo przydatna funkcjonalność, posiadająca wiele zalet, ale dla początkujących Użytkowników (i nie tylko), przydatna będzie możliwość usuwania kroków, gdy stwierdzimy, że chcemy się wycofać z jakiegoś nieopacznie wykonanego kroku.

Może się zdarzyć, że okienko rejestratora kroków jest niewidoczne, wówczas trzeba je włączyć poprzez wybranie u góry zakładki Widok, a następnie polecenia Ustawienia zapytania (rys. 9)