Poniższy opis pokazuje jak w Smart BI wykorzystać tabelę przestawną, na przykładzie zestawienia obrotów i sald (ZOiS) dla kont syntetycznych.
Pobierz przykładowy arkusz ZOiS_syntetyka.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ć.
W raporcie zestawienie obrotów i sald (ZOiS), w pierwszych dwóch kolumnach znajdują się symbole kont księgowych oraz ich nazwy. Informacje o kontach znajdują się na arkuszu Dane w kolumnach: O (konta księgowe) i T (nazwy kont księgowych).
Można te dane wykorzystać do raportu, ale najpierw należy przygotować je tak, aby nadawały się do raportu Zestawienia obrotów i sald. Każde konto księgowe musi wystąpić raz (nie może się powtarzać), a w danych na zakładce Dane konta się powtarzają.
Do uporządkowania tych danych możesz wykorzystać tabelę przestawną. Aby utworzyć tabelę przestawną w SmartBI:
1. Przejdź na arkusz Dane i zaznacz wszystkie kolumny z danymi.
2.Przejdź na kartę narzędzi Wstawianie (górne menu) i wybierz polecenie Tabela przestawna. Na potrzeby tworzonego raportu ZOiS wybierz w oknie parametrów tabeli przestawnej do wierszy dwa pola:
•Syntetyka,
•Syntetyka: nazwa
Arkusz z tabelą przestawną nazwij roboczy.
Opis arkusza ZOiS i użytych formuł
Na arkuszu o nazwie ZOiS znajduje się tabela z kolumnami raportu:
•Konto,
•Nazwa,
•BO Winien,
•BO Ma,
•Obroty Winien,
•Obroty Ma,
•Obroty Narastająco Winien,
•Obroty Narastająco Ma,
•Saldo Winien,
•Saldo Ma.
Poniżej znajdują się informacje o formułach użytych z poszczególnych kolumnach.
Nazwa
W kolumnie Nazwa znajduje się formuła:
=JEŻELI(B5<>""; roboczy!B4;"")
Formuła ta, pobiera dane z arkusza roboczy (tabela przestawna), ale zanim zawartość komórki będzie przeniesiona z arkusza roboczy (w przykładzie jest to zawartość komórki B4), formuła sprawdza, czy na arkuszu roboczy ta komórka nie jest pusta. Jeśli jest pusta, to w raporcie ZOiS odpowiadająca jej komórka również pozostanie pusta, w przeciwnym razie zawartość komórki (nazwa konta) z arkusza roboczy zostanie przeniesiona do odpowiadającej jej komórki w raporcie ZOiS.
Konto
W kolumnie Konto znajduje się formuła:
=JEŻELI(DŁ(B5)=2;0&B5;B5)
Ta formuła sprawdza w pierwszej kolejności czy długość tekstu wpisanego w komórce (w przykładzie B5), jest równy dwa znaki. Chodzi o to, że w SmartBI konta księgowe, które należą do zespołu zero (mają z przodu zero), są prezentowane bez tego zera.
Formuła jeśli stwierdzi, że długość tekstu wynosi dwa znaki: DŁ(B5)=2, wówczas doda z przodu zero: 0&B5. W przeciwnym przypadku przeniesie do raportu ZOiS tekst z komórki B5. W ten sposób wszystkie konta syntetyczne w raporcie mają trzy znaki.
BO Wn
W kolumnie BO Wn znajduje się formuła:
=JEŻELI(B5<>""; SUMA.WARUNKÓW(Dane!Q:Q; Dane!R:R;B5; Dane!U:U; "Bilans otwarcia"); "")
Formuła wykorzystuje sumę warunków z dwoma warunkami:
•symbol konta wpisany w komórce B5,
•wpisane w kolumnie U na arkuszu Dane opis „Bilans otwarcia”).
Do tej kolumny wartości są pobierane z arkusza Dane.
BO Ma
W kolumnie BO Ma znajduje się formuła:
=JEŻELI(B5<>""; SUMA.WARUNKÓW(Dane!P:P; Dane!R:R;B5; Dane!U:U; "Bilans otwarcia"); "")
Ta formuła różni się od formuły z kolumny BO Wn, tylko tym, że wartości pobierane są z kolumny P, gdy w kolumnie BO Wn z kolumny Q na arkuszu Dane. Reszta jest tak samo.
Obroty Wn
W kolumnie Obroty Wn znajduje się formuła:
=JEŻELI(B5<>""; SUMA.WARUNKÓW(Dane!Q:Q; Dane!R:R;B5; Dane!M:M; $D$1); "")
Formuła wykorzystuje sumę warunków z dwoma warunkami:
•sumowanie wg konta księgowego wpisanego w kolumnie B (w przykładzie jest to komórka B5),
•zablokowana komórka D1, w której prezentowany jest miesiąc. Raport zestawienie obrotów i sald zawsze dotyczy jakiegoś konkretnego okresu (miesiąca), więc informacja o tym okresie jest niezbędna. Na przykład na pierwszym obrazku raportowany miesiąc to listopad, więc dane w kolumnie Obroty Wn dotyczą listopada.
Obroty Ma
W kolumnie Obroty Ma znajduje się formuła:
=JEŻELI(B5<>""; SUMA.WARUNKÓW(Dane!P:P; Dane!R:R;B5; Dane!M:M; $D$1); "")
Tutaj, formuły dla strony Wn i Ma są bardzo podobne jak w kolumnach bilansu otwarcia, a różną się tylko kolumną, z której brane są wartości. Dla Obrotów Wn, wartości są pobierane z kolumny Q arkusza Dane, a dla strony Ma z kolumny P arkusza Dane.
Obroty Narastające Wn
Kolumna Obroty Narastające Wn pobiera wartości wg formuły:
=JEŻELI(B5<>""; SUMA.WARUNKÓW(Dane!Q:Q; Dane!R:R; B5); "")
Wartości są sumowane wg konta księgowego z kolumny B (w przykładzie komórki B5). I podobnie jak wcześniej, formuła w kolumnie Obroty Narastająco Ma, ma formułę różniącą się tylko kolumną z wartościami.
Saldo Wn i Saldo Ma
Ostatnie dwie kolumny Saldo Wn i Saldo Ma, mają formuły odpowiednio:
•=JEŻELI(C5<>""; JEŻELI(I5>J5;I5-J5;0);"")
•=JEŻELI(C5<>""; JEŻELI(J5>I5;J5-I5;0);"")
Powyższe formuły sprawdzają która wartość w obrotach narastająco jest większa (strona Wn, czy strona Ma) i wpisują wartość z wyliczonej wartości.
Komórka miesiąca
Poza tabelą na arkuszu ZOiS w komórce D1 pojawia się informacja o miesiącu którego dotyczy zestawienie, czyli informacja o tym, jakiego miesiąca dotyczą wartości w kolumnach Obroty Wn i Obroty Ma. Formuła w komórce D1 jest następująca:
=JEŻELI(B3=1; "styczeń"; JEŻELI(B3=2; "luty"; JEŻELI(B3=3; "marzec"; JEŻELI(B3=4; "kwiecień"; JEŻELI(B3=5; "maj"; JEŻELI(B3=6; "czerwiec"; JEŻELI(B3=7; "lipiec"; JEŻELI(B3=8; "sierpień"; JEŻELI(B3=9; "wrzesień"; JEŻELI(B3=10; "październik"; JEŻELI(B3=11; "listopad"; JEŻELI(B3=12; "grudzień"; ""))))))))))))
Formuła odwołuje się do kolumny B, która jest traktowana w raporcie jako kolumna techniczna i jest ukryta. W komórce B3, do której odwołuje się formuła z komórki D1, widoczna jest liczba 11. Formuła w komórce B3 jest następująca: =MAX(roboczy! H:H)
Liczba 11 jest efektem ustalenia przez formułę wartości maksymalnej wśród wszystkich wartości na arkuszu roboczy w kolumnie H. Cały mechanizm ustalania tej wartości wymaga kilku kroków. Na obrazku niżej widoczny jest fragment arkusza roboczy.
Do kolumny F na tym arkuszu, pobrane są dane (miesiące) z arkusza Dane za pomocą tabeli przestawnej. Podczas generowania raportu, użytkownik wskazuje okres, za jaki ma być przygotowany raport. Miesiąc zamykający okres, jest miesiącem, który będzie pobierany do kolumn Obroty Wn i Obroty Ma.
Wg ustawień, do arkusza Dane zostaną wczytane miesiące od stycznia do października. Aby raport „wiedział”, który miesiąc jest ostatni, można zastosować formułę maximum (MAX), ale by ona zadziałała poprawnie, nazwy miesięcy powinny być zamienione na ich symbole liczbowe. Zostało to zrobione za pomocą formuły:
=JEŻELI(F4<>""; JEŻELI(F4="styczeń"; 1; JEŻELI(F4="luty"; 2; JEŻELI(F4="marzec"; 3; JEŻELI(F4="kwiecień"; 4;JEŻELI(F4="maj"; 5; JEŻELI(F4="czerwiec"; 6; JEŻELI(F4="lipiec"; 7; JEŻELI(F4="sierpień"; 8; JEŻELI(F4="wrzesień"; 9; JEŻELI(F4="październik"; 10; JEŻELI(F4="listopad"; 11; JEŻELI(F4="grudzień"; 12; "")))))))))))); "")
W kolumnie H na arkuszu roboczy, wylistowane są miesiące w formie liczbowej. Do danych w takiej formie bez problemu można zastosować formułę: =MAX(roboczy! H:H), która wyszuka maksymalną wartość w kolumnie H na arkuszu roboczy. Widzimy tę wartość na raporcie ZOiS w komórce B3. Aby była ona łatwiejsza w interpretacji, zamieniamy liczbę miesiąca na odpowiadającą mu nazwę:
=JEŻELI(B3=1;"styczeń"; JEŻELI(B3=2; "luty"; JEŻELI(B3=3; "marzec"; JEŻELI(B3=4; "kwiecień"; JEŻELI(B3=5; "maj"; JEŻELI(B3=6; "czerwiec"; JEŻELI(B3=7; "lipiec"; JEŻELI(B3=8; "sierpień"; JEŻELI(B3=9; "wrzesień"; JEŻELI(B3=10; "październik"; JEŻELI(B3=11; "listopad"; JEŻELI(B3=12; "grudzień"; ""))))))))))))
Ostatnia część w raporcie ZOiS, to sumowanie, które zostało umieszczone w wierszu trzecim arkusza ZOiS. W poszczególnych komórkach wiersza sumowania, wprowadzona jest formuła sumy częściowej: =SUMY.CZĘŚCIOWE(9;E5:E1701). W raporcie została zastosowana suma częściowa, ponieważ podczas filtrowania danych (np. wg kont księgowych), formuła ta zawsze wyliczy prawidłowe wartości uwzględniając tylko widoczne wartości.
Z uwagi na to, że w tym raporcie są wstawione dwie tabele przestawne, raport po uruchomieniu trzeba zawsze odświeżyć.
