Raport RZiS miesiącami i narastająco można zaprojektować w oparciu o zapytanie Obroty kont w miesiącach. W pierwszym kroku, trzeba przygotować środowisko dla raportu.
Pobierz przykładowy arkusz RZiS_miesiacami.xlsx >>
Pobrany arkusz można wczytać do edytowanego raportu poleceniem Otwórz.
Bezpośrednio po wczytaniu raport ma jeszcze dane, które zostały pobrane ze źródeł, z którymi był połączony podczas jego tworzenia. Aby zainicjować pobranie danych do raportu z danych w miejscu wczytania raportu, trzeba raport zamknąć i ponownie go uruchomić.
1.Otwórz predefinowany raport Obroty kont w miesiącach i zapisz go pod nową nazwą RZiS miesiącami i narastająco.
2.Otwórz nowy raport i dodaj w nim arkusz: RZiS.
3.Na arkuszu RZiS przygotuj tabelę jak na obrazku niżej:
4.W kolumnie B i C wprowadź opisy pozycji rachunku zysków i strat, wraz z oznaczeniami tych pozycji wg wariantu RZiS, z którego będziemy korzystać.
W omawianym przykładzie jest to wariant porównawczy.
Ważną kolumną jest kolumna Atrybut umieszczona na końcu tabeli. Dzięki atrybutom będzie można konfigurować RZiS tak, aby salda poszczególnych kont księgowych trafiały we właściwe miejsce w raporcie.
Pomiędzy opisem pozycji, a atrybutami jest umieszczonych 12 kolumn dla poszczególnych miesięcy.
Ponadto, u góry w komórce D1 została umieszczona lista z dwoma elementami: narastająco; miesięcznie. Za pomocą tych dwóch elementów, będzie można wybierać sposób wyświetlania danych w RZiS.
5.Przejdź do projektowania kokpitu, za pomocą którego będzie można wygodnie konfigurować raport. W tym celu należy dodaj drugi arkusz i przygotuj w nim tabelkę.
W kolumnie C i D podłącz listy:
•W kolumnie C (Saldo) podłączamy listę, która będzie składała się z dwóch elementów: Wn i Ma.
•W kolumnie D natomiast, która została nazwana Atrybut, również dołączona jest lista, jednak zanim zostanie dołączona lista, najpierw należy ją przygotować. W pierwszym kroku, zaznacz wszystkie elementy z kolumny P na arkuszu RZiS i skopiuj je. Następnie wklej je w dowolnym miejscu kokpitu, na przykład w kolumnie W (bądź jakiejkolwiek innej), a następnie usuń z tej listy wszystkie pozycje ze słowem SUMA.
6.Tak przygotowaną listę elementów, można wykorzystać do włączenia w kolumnie D na arkuszu kokpit, jako listę rozwijalną.
Aby to zrobić:
•Przejdź na kartę Formuły i wybierz polecenie Menadżer nazw.
•Wybierz przycisk Nowy i wypełnij pola
•W polu nazwa wprowadzamy nazwę, w przykładzie jest to Atrybut, a w polu odwołania wprowadzamy zakres komórek do których zostały przed chwilą skopiowane atrybuty: $W$2:$W$44. Po wypełnieniu pól zatwierdzamy wprowadzone informacje przyciskiem OK.
7.Następnie ustawiamy się w komórce D3 na arkuszu kokpit i przechodzimy na zakładkę Dane, wybieramy polecenie Poprawność danych i w okienku, które zostanie otworzone wypełniamy pola:
•Dozwolone: list,
•Źródło: =Atrybut
Przyciskiem OK zatwierdź wprowadzone informacje. A następnie z komórki D3 przeciągnij listę na dolne komórki, tak aby lista była dostępna w niższych komórkach.
8.Następnie sformatuj kolumny od E do P, do których będą pobierane odpowiednie wartości z arkusza Dane za pomocą formuł Excela. Formatowanie komórek realizujemy poprzez zaznaczenie całego obszaru, w którym mają być sformatowane komórki. Formatowanie uruchamiamy z podręcznego menu, otwieranego spod prawego przycisku i wyborem polecenia Formatowanie komórek. Komórki formatujemy jako księgowe.
9.Przejdź do komórki E3, gdzie wprowadź następującą formułę:
=JEŻELI(B3<>""; JEŻELI(C3="Wn"; SUMA.WARUNKÓW(Dane!Q:Q; Dane!O:O;B3; Dane!M:M;$E$2) - SUMA.WARUNKÓW(Dane!P:P; Dane!O:O;B3; Dane!M:M;$E$2); JEŻELI(C3="Ma"; SUMA.WARUNKÓW(Dane!P:P; Dane!O:O;B3; Dane!M:M;$E$2) - SUMA.WARUNKÓW(Dane!Q:Q; Dane!O:O;B3; Dane!M:M;$E$2); "podaj stronę salda"));"")
Formułę przeciągnij w dół, np. do setnego wiersza. Jeśli będzie potrzeba (przy dużej ilości danych) formułę można przeciągnąć niżej.
Wyjaśnienie formuł zawartych w formule z komórki E3:
•SUMA.WARUNKÓW(Dane!Q:Q; Dane!O:O;B3; Dane!M:M;$E$2)
Formuła SUMA.WARUNKÓW potrzebuje minimalnie trzech parametrów: 1.Zakres z którego mają być pobierane wartości, 2.Zakres, w którym będą warunki wg których będą wybierane i sumowane wartości oraz 3.Konkretny warunek. W prezentowanym przykładzie, zakres z którego mają być pobierane wartości, to kolumna Q na arkuszu Dane, są to Obroty Wn.
Zakres warunków, wg których będą brane i sumowane wartości z kolumny Q, to kolumna O na arkuszu Dane. W tej kolumnie są konta księgowe. Warunek, który będzie brany pod uwagę przy sumowaniu wartości z kolumny Q, będzie wpisany do komórki B3. Ponadto, w formule tej podany jest drugi zakres warunków: kolumna M na arkuszu Dane. W tej kolumnie jest okres – miesiąc. Wartości, które będą sumowane w komórce E3, będą pobierane z kolumny Q na arkuszu Dane, wg konta księgowego wpisanego w komórce B3 (w przykładzie 402*) i wg miesiąca (w przykładzie jest to styczeń – komórka E2).
 |
•SUMA.WARUNKÓW(Dane!P:P; Dane!O:O;B3; Dane!M:M;$E$2)
Ta sama formuła, jaka została omówiona w pkt. 1, z tym że tutaj wartości będą pobierane z kolumny P na arkuszu Dane. W tej kolumnie są Obroty Ma. |
•JEŻELI(C3="Wn"; SUMA.WARUNKÓW(Dane!Q:Q; Dane!O:O;B3; Dane!M:M;$E$2) - SUMA.WARUNKÓW(Dane!P:P; Dane!O:O;B3; Dane!M:M;$E$2)
Formuła JEŻELI sprawdza warunek, który jest podany: JEŻELI C3="Wn". Jeśli w komórce będzie wprowadzone „Wn”, wówczas formuła odejmie do SUMY.WARUNKÓW wyliczonej ze strony Wn (obroty Wn), SUMĘ.WARUNKÓW wyliczoną ze strony Ma (obroty Ma). |
•JEŻELI(C3="Ma"; SUMA.WARUNKÓW(Dane!P:P; Dane!O:O;B3; Dane!M:M;$E$2) - SUMA.WARUNKÓW(Dane!Q:Q; Dane!O:O;B3; Dane!M:M;$E$2)
Podobna formuła jak w punkcie 3, tylko w tym wypadku warunek jest C3="Ma" i od strony Ma odejmowana jest strona Wn, wyliczona za pomocą formuły SUMA.WARUNKÓW. |
•JEŻELI(C3="Wn"; SUMA.WARUNKÓW(Dane!Q:Q; Dane!O:O;B3; Dane!M:M;$E$2) - SUMA.WARUNKÓW(Dane!P:P; Dane!O:O;B3; Dane!M:M;$E$2); JEŻELI(C3="Ma"; SUMA.WARUNKÓW(Dane!P:P; Dane!O:O;B3; Dane!M:M;$E$2) - SUMA.WARUNKÓW(Dane!Q:Q; Dane!O:O;B3; Dane!M:M;$E$2);"podaj stronę salda"))
Ta formuła obsługuje sytuację, gdy w komórce C3 nie będzie wprowadzone ani Wn, ani Ma, czyli komórka będzie pusta. Wówczas pojawi się komunikat: „podaj stronę salda”. |
•=JEŻELI(B3<>""; ;"")
Formuła ta również jest formułą JEŻELI. Warunek jaki jest w tym wypadku sprawdzany, to czy komórka B3 jest różna od pustej. Jeśli jest różna od pustej, to ma być realizowana formuła podana po średniku, a jeśli komórka jest pusta, to pole E3, w której jest wprowadzona cała formuła, ma pozostać pusta. |
10.W przygotowanym kokpicie wprowadź odpowiednie konta księgowe w komórki kolumny B, oraz określ saldo konta, które ma być wyliczone i wskaż atrybut (pozycję w RZiS), do której dana wartość ma być wysłana na arkuszu RZiS.
11.Aby raport RZiS prawidłowo pobierał dane z kokpitu, tutaj także trzeba wprowadzić odpowiednie formuły. W tym miejscu także została wykorzystana formuła SUMA.WARUNKÓW.
W kolumnie „styczeń” wystarczy wpisać prostą formułę: =SUMA.WARUNKÓW(kokpit!E:E; kokpit!D:D;P8)
Formuła pobiera wartości z kolumny E na kokpicie (styczeń), odwołuje się do warunków z kolumny D, również na kokpicie (atrybuty) i sprawdza zgodnie z jakim atrybutem dane mają być umieszczone w raporcie RZiS. W styczniu wartość miesięczna i wartość narastająco są sobie równe, wobec czego nie ma potrzeby bardziej rozbudowywać tej formuły.
W kolumnie „luty” formuła jest już troszkę rozbudowana:
=JEŻELI($D$1="miesięcznie"; SUMA.WARUNKÓW(kokpit!F:F; kokpit!D:D;P8); SUMA.WARUNKÓW(kokpit!F:F; kokpit!D:D;P8)+D8)
Formuła rozpoczyna się warunkiem JEŻELI, który brzmi: $D$1=”miesięcznie”, co oznacza, że jeśli w komórce D1 będzie słowo „miesięcznie”, to będzie uruchomiona formuła: SUMA.WARUNKÓW(kokpit! F:F; kokpit! D:D;P8), czyli pobierana jest wartość z kokpitu w ten sam sposób, jaki został opisany dla kolumny styczeń (powyżej). W przeciwnym razie (w komórce D1 jest inne słowo niż „miesięcznie”), wówczas do wartości miesięcznej za luty dodawana jest wartość ze stycznia. Efekt działania tej formuły jest taki, że gdy w komórce D1 będzie wybrane słowo „miesięcznie”, w pozycjach RZiS dla lutego będą podawane dane z lutego, natomiast jeśli w komórce D1 zostanie zmieniona wartość na „narastająco”, wówczas do wartości z lutego podanej w kokpicie, zostaną dodane wartości ze stycznia (wartość z komórki D8 w arkuszu RZiS).
W kolejnych miesiącach formuła będzie podobna.
Dla marca:
=JEŻELI($D$1="miesięcznie"; SUMA.WARUNKÓW(kokpit!G:G; kokpit!D:D;P8); SUMA.WARUNKÓW(kokpit!G:G; kokpit!D:D;P8)+E8).
Dla kwietnia:
=JEŻELI($D$1="miesięcznie"; SUMA.WARUNKÓW(kokpit!H:H; kokpit!D:D;P8); SUMA.WARUNKÓW(kokpit!H:H; kokpit!D:D;P8)+F8
Dla maja:
=JEŻELI($D$1="miesięcznie"; SUMA.WARUNKÓW(kokpit!I:I; kokpit!D:D;P8); SUMA.WARUNKÓW(kokpit!I:I; kokpit!D:D;P8)+G8)
Itd...
Formuła w kolumnie grudzień:
=JEŻELI($D$1="miesięcznie"; SUMA.WARUNKÓW(kokpit!P:P; kokpit!D:D;P8); SUMA.WARUNKÓW(kokpit!P:P; kokpit!D:D;P8)+N8)
Z uwagi na to, że zgodnie z ustawą o rachunkowości różnice kursowe są prezentowane w RZiS wg salda, formuła z pozycji, w której prezentowane są różnice kursowe powinna być:
=JEŻELI(SUMA.WARUNKÓW(kokpit!E:E; kokpit!D:D;P51)>0; SUMA.WARUNKÓW(kokpit!E:E; kokpit!D:D;P51);0)
Ważne, aby dla konta księgowego różnic kursowych w kokpicie wprowadzić to konto w kontekście salda Wn i Salda Ma.
Formuła najpierw sprawdzi wartość ujemnych różnice kursowych (saldo Wn) i wartość dodatnich różnic kursowych (saldo Ma). To saldo, które ma na kokpicie wartość ujemną będzie pominięte, a to które ma wartość dodatnią, zostanie przekazane do właściwej pozycji w RZiS.
Podobnie powinna być zmodyfikowana formuła dla pozycji zysk i strata ze zbycia niefinansowych aktywów trwałych. W tych pozycjach obowiązują takie same zasady, jak dla pozycji różnic kursowych.