Wojciech Gardziński
Cykl: Excel w biznesie
Podtytuł: Od danych systemów transakcyjnych do
informacji biznesowej
Budowanie uniwersalnych arkuszy analiz danych
Czyli: Jak analizować dane funkcjami Excela?
1. Wstęp
Cykl, które właśnie rozpoczynamy ma na celu pokazanie, jak prawidłowo wykorzystywać Excela - to najpowszechniejsze narzędzie analityczne do wspierania controllingu i innych procedur biznesowych.
Umiejętności posługiwania się Excelem wśród księgowych, ekonomistów, controllerów i analityków biznesowych są coraz większe. To fakt, który jawi się wyraźnie, chociażby poprzez rosnący poziom uczestników szkoleń Excela.
Jednak znajomość pewnych, kluczowych dla tych zastosowań, funkcji arkuszowych, sposobów importu i dostępu do danych (jak np. wykorzystanie programu Ms Query), zaawansowanych metod analizy (jak np. tabel przestawnych, a zwłaszcza wykorzystania do nich kostek OLAPowych) czy wreszcie prawidłowego wiązania skoroszytów w większe modele, jest ciągle niewystarczająca.
W niniejszym cyklu chcemy dokonać przeglądu metod analizy danych i tworzenia uniwersalnych, parametryzowanych raportów dla celów analityka, czyli pracownika zaspokajającego potrzeby informacyjne zarządu.
Chcemy np. pokazać, jak sporządzić Rachunek Wyników lub raport z wykonania budżetu, aby po otrzymaniu kolejnej obrotówki lub kartoteki transakcji, nie trzeba było wykonywać tych samych, żmudnych i podatnych na pomyłki czynności.
Chcemy odpowiedzieć na pytania, które być może nurtują niejednego analityka,
na przykład:
Jak ominąć niektóre
niedoskonałości funkcji arkuszowych np, funkcji Suma.Jeżeli()
Jak parametryzować
formuły, zawierające odwołania do innych arkuszy lub skoroszytów?
Jak parametryzować
odwołania do tablic o stałej strukturze (np. do planu
budżetu)?
Jak najłatwiej
uzyskać dane zawarte w systemach transakcyjnych (F-K, Sprzedaż, Magazyn itp.) i
jeśli nawet samemu ich nie pobierać, to przynajmniej, czego wymagać od
firmowych informatyków?
Jakie są najlepsze
metody do zbiorczej a jakie do pogłębionej (np.
strukturalnej) analizy danych?
Jakie dane są
„lepsze” z punktu widzenia celów naszej analizy?
2. Funkcja Suma.Jeżeli() – siła raportów zbiorczych
Dla celów pierwszego artykułu załóżmy, że już mamy w arkuszach Excela kartotekę transakcji pobraną z systemu f-k za kolejne 3 miesiące. (Rys.1)
Rys. 1 Transakcje z systemu f-k importowane do Excela
Jak na podstawie tych danych można najprościej sporządzić uniwersalny raport wykonania budżetu?
Zacznijmy od najprostszego przypadku: Mając dane kartoteki transakcji sporządźmy raport o wykonaniu budżetu dla poszczególnych pozycji.
Policzmy na początek wartość kosztów na koncie 405.
Wynik podaje funkcja Suma.Jeżeli() o trzech argumentach:
1 – zakres komórek zawierających kryteria wyboru elementów do sumowania – to kolumna C, w której podane są dla każdego wiersza wartości konta Wn – „KOWN”. Najlepiej podać go w najogólniejszej formie: C:C, co oznacza całą kolumnę.
2 – wartość kryterium, dla której chcemy otrzymać sumę. Np. KOWN = 405* (gwiazdka na końcu oznacza, że interesują nas wszystkie wiersze z kontem 405 (zaczynające się od znaków 405).
3 – zakres komórek, które chcemy sumować, dla danego kryterium (w tym wypadku - konta 405). to kolumna E zawierająca kwoty WN dla każdego kontaWN.
=Suma.Jeżeli(C:C;”405*”;E:E)
Formuła byłaby w takiej postaci, gdybyśmy ją pisali w tym samym arkuszu, w którym znajdują się dane.
Nie jest to praktyka zalecana. Dane powinniśmy oddzielać od raportów.
Arkusz z danymi nazywa się Styczeń. Utwórzmy więc arkusz raportu (Shift+F11, dwukrotne kliknięcie na nazwie arkusza i wpisanie nazwy „raportKonta”) - rys.2.
Jeśli, poprzez pokazywanie wartości kolejnych argumentów myszką, w dowolnej komórce utworzymy powyższą formułę, otrzymamy jej następującą postać:
=Suma.Jeżeli(styczeń!C:C;"405*";styczeń!E:E).
Najprostsze jest oczywiście sparametryzowanie 2 argumentu: wystarczy po pierwszym średniku pokazać myszką pole z wpisaną wartością konta oraz wcisnąć odpowiednią liczbę razy klawisz F4:
=Suma.Jeżeli(styczeń!C:C;$A11;styczeń!E:E)
Parametryzacja argumentów 1 i 3-ciego wymaga użycia funkcji Adr.Pośr(). Funkcja ta umożliwia podanie adresów styczeń!C:C i styczeń!E:E w formie łańcuchów tekstowych.
Adr.Pośr(„styczeń!C:C”) i styczeń!C:C - to te same adresy podane w różnych formach.
Łańcuch „styczeń!C:C” jako argument funkcji Adr.Pośr() można łatwo sparametryzować przy pomocy zmiennej tekstowej, czyli zawartości komórki.
Jeśli np. w komórce B4 umieścimy napis styczeń, formuła z komórki B11 przybierze postać:
=Suma.Jeżeli(Adr.Pośr(B$4&”!C:C”;$A11;
Adr.Pośr(B$4&”!E:E”))
Dzięki odpowiedniemu zredagowaniu odwołań do komórek B4 i A11 (znak dolara $ oznacza „zamrożenie” kolumny lub wiersza przy kopiowaniu formuły), możemy skopiować formułę na całe zestawienie kosztów dla czterech miesięcy (Rys. 2).
Rys.2 Zestawienie kosztów dla kolejnych miesięcy
Drugi raport jest w pewnym sensie jeszcze ciekawszy: Analizując zawartość kolumny H arkusza z rys. 1 zauważmy, że miejsca powstawania kosztów (MPK) mają strukturę hierarchiczną. MPK_01 (czyli pierwszy MPK) ma działy od 1 do 6 (MPK_01_1 do MPK_01_6).
To nam pozwala zrobić zestawienie w trzech wymiarach (Rys.3).
Pierwszy wymiar – to czas: w komórce B4 ustawiamy interesujący nas miesiąc.
Drugi wymiar – Piony organizacyjne – opisane w 5 wierszu: np. 01 - pierwszy człon symbolu MPK: MPK_01
Trzeci wymiar: Działy w ramach pionów – opisane w 1 kolumnie: ostatni człon symbolu MPK, np. 2: MPK_01_2
Odpowiednio łącząc ze sobą te łańcuchy otrzymujemy raport o kosztach według struktury organizacyjnej. Tzn. otrzymalibyśmy, gdyby nie... pewna, delikatnie mówiąc, nieścisłość.
Otóż raport podlicza dla każdego MPK’u nie koszty, lecz całą kartotekę księgowań!
Rys.3 Zestawienie kosztów dla tabeli struktury organizacyjnej
Żeby był on cokolwiek wart, powinniśmy podać mu drugi warunek sumowania, mianowicie, żeby sumować tylko rekordy dla kont z kolumny C zaczynających się na „4” (patrz rys.1).
Tymczasem jest to niemożliwe. Funkcja Suma.Jeżeli() dopuszcza podanie warunku tylko dla jednego pola. Albo więc zrezygnujemy z niej na rzecz niewygodnej i bardzo
źle się parametryzującej funkcji BD.Suma() (życzymy
powodzenia!), albo ... zastosujemy
jeden z następujących trików:
Pierwszy trik jest mniej elegancki, ale dość skuteczny:
Musimy postarać się o arkusz zawierający tylko transakcje kosztowe.
Żądamy np. od dostawcy danych, żeby nam dostarczył wyłącznie takie dane.
Możemy również umieścić wyfiltrowane Autofiltrem transakcje w innym arkuszu.
Drugi trik jest prosty i elegancki:
Tworzymy dodatkowe pole (nazwijmy je polem „kombi”), które łączy pola kryteriów w pełny warunek, który zamierzamy później wykorzystać w funkcji Suma.Jeżeli(). Nie ma żadnych ograniczeń w komplikowaniu pola kombi, decydują nasze potrzeby informacyjne i pomysłowość w zestawianiu fragmentów różnych pól.
Np. w kolumnie I piszemy formułę:
=$H2&$C2
... i przepisujemy ją w kolumnie dla całego zakresu danych.
Otrzymujemy następujący arkusz danych stycznia Styczeń
Rys. 4 Zmodyfikowany arkusz
danych stycznia z polem kombi.
Ponieważ symbol MPKu (np. MPK_01_4) ma stałą długość – 8 znaków, umieszczamy go na początku, aby nie komplikować formuły wyrównywaniem długości pierwszego członu.
Takie pole może już być polem kryteriów zawierającym wszystkie warunki niezbędne do obliczenia kosztów w układzie pionów, działów w przekrojach kolejnych miesięcy.
Nadejście kolejnego miesiąca nie będzie się wiązało z żadną dodatkową pracochłonnością; wystarczy dodać nowy arkusz nazwany imieniem miesiąca i raport dalej będzie działał. Odpowiednie zabiegi podczas pobierania danych zapewnią dołączenie formuł pola kombi do importowanych danych. Ale to już temat następnych odcinków.
3. Funkcje Indeks() i Podaj.Pozycję() – podstawa raportów korzystających z planów
Pokazaliśmy raporty utworzone na podstawie podsumowań większej liczby danych (z wstęgi danych), teraz rozważmy przypadek raportu z arkusza o ściśle określonej liczbie danych (z formularza danych).
Przypuśćmy, że mamy do dyspozycji plan budżetu dla firmy w postaci, jak na rys. 5.
Rys. 5 Plan budżetu dla firmy z rozbiciem na miesiące
W tej tabeli każda liczba jest przedmiotem zainteresowania, tych liczb jest tyle, ile pozycji planu razy liczba miesięcy, na które budżet obowiązuje.
Przypuśćmy, że w którymś z raportów potrzebna jest nam wartość zużycia energii elektrycznej (konto 401.300) - w styczniu – komórka B4, wartość 12 864,00.
Odwołanie się do tej wartości przez podanie adresu komórki C4 jest najgorszym z pomysłów, a jest jeszcze kilka, których nie zalecamy.
Jednym z uniwersalnych i eleganckich sposobów jest użycie kombinacji funkcji Indeks() i Podaj.Pozycję().
Jeżeli znamy pozycję liczby 12 864,00, możemy użyć funkcji Indeks().
W tabeli A2:M11 liczba ta znajduje się w 3 wierszu i w 2-giej kolumnie.
Odwołanie do tej liczby wygląda tak:
=Indeks($A$2:$M$11;3;2)
Wynik w komórce: 12 864,00
Należy teraz wyznaczyć numer wiersza i kolumny, w której znajduje się interesująca liczba.
Zauważmy, że liczba ta znajduje się w tym samym wierszu, co konto 401.300.
Do wyznaczenia wiersza użyjmy funkcji Podaj.Pozycję().
=Podaj.Pozycję("401.300";$A$2:$A$11;0)
Wynik w komórce: 3
Analogicznie wyznaczamy numer kolumny, w której znajduje się
napis „luty”:
=Podaj.Pozycję("styczeń";$A$2:$M$2;0)
Wynik w komórce: 2
Wyniki obliczeń możemy teraz podstawić do formuły
odszukującej liczbę.
Teraz jesteśmy gotowi do sporządzenia arkusza konfrontującego plan z wykonaniem, dla kolejnych miesięcy.
Rys. 6 Raport z wykonania budżetu dla firmy z rozbiciem na miesiące
Raport taki pozwala podłączać kolejne dane o wykonaniu i kopiować kolejne fragmenty tabeli dla kolejnych miesięcy w prawo. Wszystkie wyniki są natychmiast prawidłowe.
4. Wnioski
Jak pokazaliśmy, w Excelu jest możliwe sporządzanie raportów w taki sposób, że stwarzają one wrażenie, jakby były podłączone do danych prawie on-line. Wystarczy mieć dane przygotowane w odpowiedni sposób, aby uzyskać natychmiast wyniki, tak, jak sobie wcześniej zaplanowaliśmy. Unikamy sytuacji, że dopiero po zamknięciu miesiąca, zweryfikowaniu danych, żmudnej ich obróbce, przeklejeniu do odpowiednich komórek uzyskujemy interesujące nas wyniki.
Wystarczy dane w jakikolwiek sposób uzyskać, a raport uaktualni się „sam”.
Do tego jednak trzeba przestrzegać pewnych reguł:
1) Unikać mieszania danych i raportów w jednym arkuszu a nawet skoroszycie.
2) Zapewnić sobie dane transakcyjne w odpowiedniej formie, łatwej do dalszej obróbki.
3) Stosować
wstępną obróbkę danych – można stosować np.
„kombinowanie” pól w większe agregaty – do dalszej analizy np.
funkcją Suma.Jeżeli. Inne sposoby
wstępnej obróbki danych przed ich analizą poznamy w dalszych odcinkach.
4) Parametryzować funkcje arkuszowe w taki sposób, aby formuły działały z dala od danych z dowolnego okresu i w dowolnym miejscu arkusza, niezależnie od tego, gdzie znajduje się źródło danych.