Wykorzystanie tabel przestawnych w raportowaniu wyników

W raportowaniu wyników jednym z warunków konstrukcji efektywnego systemu informacji managerskiej jest stworzenie przejrzystej formy umożliwiającej wielowymiarową analizę. Jednym z zadań osoby odpowiedzialnej za przygotowanie raportów jest takie przetworzenie danych wejściowych (np. budżet), aby mogłyby być porównywane z realizowanymi wynikami. Wyniki te zazwyczaj pobierane są wprost z baz danych systemu transakcyjnego.

Idealnym narzędziem umożliwiającym przygotowanie takich raportów porównujących budżet z wykonaniem są tabele przestawne. Konstrukcję raportu przedstawię na prostym przykładzie, w którym należy dane systemowe (kolumny J-N) porównać z danymi źródłowymi otrzymanymi w postaci tabeli (kolumny B-H).

Zanim przejdziemy do przekształcenia danych źródłowych do formatu użytecznego pod względem analizy danych, zwróćmy uwagę na format zapisu miesięcy. W tabeli źródłowej miesiące określone są w formie cyfr rzymskich, natomiast dane systemowe zawierają jedynie datę zawarcia transakcji.
Dla sprawnego przekształcenia daty transakcji w format rzymski miesiąca możemy wykorzystać własną funkcję napisaną w kodzie VBA. W tym celu z zakładki „Deweloper” wybieramy „Visual Basic” lub po prostu uruchamiamy kombinację klawiszy Alt + F11. W ten sposób przechodzimy do edytora Visual Basic, w którym budujemy funkcję. Funkcja ograniczona jest formułą Sub Function Nazwa_Funkcji (Parametry) End Function. W tzw. ciele funkcji wykorzystujemy procedurę „Select Case” i wielowymiarowych analiz.

Logika funkcji jest taka, że dla każdego parametru m, pod który podstawiamy wartość daty transakcji, obliczany jest numer miesiąca – i. W zależności od wartości parametru i przypisywana jest odpowiednia wartość tekstowa reprezentująca rzymski zapis numeru miesiąca. W ten sposób, podstawiając funkcję „Msc” w każdym wierszu kolumny N, otrzymujemy właściwe wartości.
Kolejnym krokiem jest odpowiednie przetworzenie danych źródłowych. Dobry analityk, konstruując layout do wprowadzania danych, będzie miał na myśli sposób ich wykorzystania dla celów raportowych. Niestety nie każdy myśli jak analityk i często jako dane wejściowe otrzymujemy po prostu tabelę – tak jak na załączonym przykładzie, gdzie dla każdego województwa mamy przypisane miasta, w których funkcjonują przedstawiciele. W kolumnach zostały określone cele miesięczne tych osób na trzeci kwartał roku.
Aby takie dane były użyteczne, należy je przedstawić w formie bazodanowej. Bardzo ważne jest zrozumienie istoty takiego układu a w szczególności pojęcia rekordu i pola. Rekordem jest tak naprawdę wiersz danych opisujący pewien składnik, taki jak umowa, przedstawiciel etc. Składnik ten opisywany jest poprzez dane odpowiadające określonym polom. W naszym przypadku rekordem będzie miesięczny wynik przedstawiciela, opisany poprzez nazwę handlowca, jego przypisanie do miasta i województwa, wartość miesięcznej transakcji oraz miesiąc, którego ta transakcja dotyczy.
Należy zwrócić uwagę na fakt, że nie tworzymy odrębnych pól na wyniki poszczególnych miesięcy a tylko dwa pola – miesiąc i wynik. Dzięki temu powstała tabela nie będzie miała ograniczeń zakresu miesięcy i będzie mogła być wykorzystywana w dalszym procesie raportowania.
W wyniku transformacji otrzymujemy zakres danych taki jak na obrazku poniżej. Zakres ten uzupełniamy o dodatkowe pole „Wykonanie”, które będziemy uzupełniać danymi źródłowymi.

Aby przypisać wyniki, które zebrane są wg zrealizowanych transakcji, możemy wykorzystać funkcję SUMA.WARUNKÓW – jest to rozszerzenie funkcji SUMA.JEŻELI i działa w następujący sposób.

Zaznaczamy kolumnę danych, które chcemy sumować – w tym przypadku kolumnę M, w której mamy dane z systemu dla wartości każdej jednostkowej transakcji. Sumy te przypisujemy odpowiednim handlowcom w odpowiednich miesiącach realizacji. Z tego powodu potrzebujemy uwzględnić dwa warunki: Miesiąc realizacji i Id handlowca. Funkcja przy każdym handlowcu, w danym miesiącu w kolumnie „Wykonanie” obliczy nam sumę transakcji zrealizowanych przez tego handlowca w analizowanym miesiącu.
Tym krokiem kończymy przygotowanie danych i możemy przejść do konstrukcji właściwego raportu w formie tabeli przestawnej.
W tym celu zaznaczamy źródło danych (kolumny od P do V) i z menu „Wstaw” wybieramy Tabelę przestawną. W ten sposób w nowym arkuszu otrzymamy widok jak na poniższym obrazku. Po prawej stronie mamy listę pól tabeli przestawnej, które możemy dowolnie umieszczać jako nagłówki, kolumny i wartości.

Naszym zadaniem jest stworzenie raportu, który w nagłówku zawierałby wartości budżetu, odpowiadające wykonanie oraz odchylenie w postaci procentowej. Używając techniki „przeciągnij – upuść”, przenosimy poszczególne pola do odpowiednich obszarów – tak jak na rysunku poniżej.

W tej postaci raport jest jeszcze mało czytelny i brakuje pola z procentowym wykonaniem. W tym celu z menu „Opcje tabeli przestawnej” wybieramy polecenie „Wstaw pole obliczeniowe”, które wywołuje menu jak na obrazku:

W ten sposób definiujemy pole [%], które obliczane jest jako iloraz wykonania i budżetu. Pole to automatycznie dodaje się do listy wartości tabeli przestawnej. Począwszy od wersji 2007, Excel pozwala na usprawnienie widoku tabeli przestawnej poprzez wykorzystanie zdefiniowanych układów. Dla potrzeb raportowania najlepiej nadaje się układ kompaktowy, który nadaje tabeli bardziej przejrzysty i czytelny obraz.

Wywołując menu „Opcje tabeli przestawnej”, możemy ustawić podstawowe parametry wyświetlania. Najważniejszym z nich wydaje się wyłączenie automatycznego dopasowania szerokości kolumn (inaczej po każdym odświeżeniu kształt raportu zostanie zburzony). Drugim parametrem, który warto wykorzystać, jest zaznaczenie opcji wyświetlania błędnych wartości. Jest to szczególnie ważne w przypadku pól obliczeniowych, w których zwracana jest wartość dzielona przez zero.

Przed otrzymaniem ostatecznego raportu potrzebujemy tylko zdefiniować formaty wyświetlanych wartości i ustawić odpowiednie szerokości kolumn.

W ten sposób otrzymaliśmy w pełni automatyczny raport, który odświeży się zawsze po zasileniu tabeli systemowej danymi. Wartości pobierane są ze skonstruowanej przez nas tabeli w formacie bazodanowym, która łączy dane wejściowe budżetu z danymi z systemu transakcyjnego.
Na uwagę zasługują jeszcze dwie opcje:
Możliwość zwinięcia szczegółów z wykorzystaniem opcji „zwiń pole” – „rozwiń pole”. Dzięki temu dla celów raportowych możemy ukrywać poziom szczegółowości, zachowując jednocześnie możliwość tzw. drążenia danych. Z wykorzystaniem arkusza kalkulacyjnego drążenie danych odbywa się poprzez dwukrotne kliknięcie w dowolną wartość pokazywaną w tabeli przestawnej. Działanie takie powoduje powstanie nowego arkusza z tabelą danych składających się na „drążoną” wartość.
Druga opcja to utworzenie raportu narratywnego. Wystraczy np. pole „Województwo” przenieść do obszaru „filtr” i powstanie raport dla konkrentnego województwa z możliwością wyboru jego nazwy z menu rozwijanego.

Podsumowując dotychczasowe rozważania, możemy zwrócić uwagę na fakt, że nawet proste oprogramowanie typu MS Excel posiada potężne narzędzie raportowe w postaci tabel przestawnych. Jednakże aby móc je w pełni efektywnie zastosować, musimy pamiętać o odpowiednim przygotowaniu i przetworzeniu danych wejściowych. Dzięki tej pracy otrzymamy możliwość wykorzystania narzędzia prezentacji wielowymiarowych analiz.