Warsztat techniczny controllera, czyli 5 najważniejszych funkcji oraz 15 skrótów, które warto znać
Controller finansowy to w obecnych czasach osoba, która powinna cechować się wykształceniem kierunkowym, wiedzą teoretyczną z zakresu finansów, znajomością języka obcego. Jednakże powinien to być również specjalista posiadający solidny warsztat techniczny, czyli zaawansowaną wiedzę w obszarze Excela czy wizualizacji danych. Świat finansów przenika się coraz mocniej z nowoczesnymi technologiami, co sprawia, że dobry controller nie może poprzestać na wiedzy teoretycznej. Wiele przedsiębiorstw stawia na optymalizację procesów, do czego niezbędna jest dobra wiedza techniczna. Niniejszy artykuł w odpowiedzi na te potrzeby skupia się na 5 funkcjach, 15 skrótach klawiaturowych oraz 2 wykresach, które każdy controller finansowy powinien znać, by za ich pomocą optymalizować procesy, jednocześnie skracając nakład czasu potrzebny do wykonania zadania.
5 najważniejszych funkcji w pracy kontrolera finansowego – funkcja UNIQUE
Pierwszą z funkcji, którą warto poznać i stosować jest funkcja UNIQUE (w polskiej wersji językowej UNIKATOWE). Funkcja ta została wprowadzona w wersji Excela z 2021 roku. Została ona podobnie jak inne funkcje (np. XLOOKUP, o której później) wdrożona, by odpowiedzieć na potrzeby użytkowników.
Spójrzmy na wykres 1. Jest to krótki raport przedstawiający produkty sprzedane w ostatnim tygodniu przez pewną firmę produkcyjną. Każdy produkt ma swoją serię lub wiele serii. Nas interesuje, ile unikatowych produktów zostało sprzedanych w badanym okresie. Możemy oczywiście usunąć duplikaty wchodząc w kartę Dane, a następnie klikając „usuń duplikaty”. Jest to jednak rozwiązanie niekorzystne, o tyle, że wiersze zostaną usunięte i de facto przy większej ilości wierszy (np. 1000) nie wiemy co zostało usunięte, nie mamy kontroli nad danymi. W tym miejscu wchodzi do gry funkcja UNIQUE, która daje nam większą kontrolę i pozwala na dwa różne sposoby przefiltrować duplikaty.
Pierwszy sposób filtrowania za pomocą UNIQUE to utworzenie listy unikatowych wartości, z zastrzeżeniem, że jeśli pojawi się duplikat, tak jak w naszym przykładzie produkt Y150, to Excel usunie drugą i kolejną wartość, Y150 natomiast pozostawi na liście ten produkt. Widać to w środkowej tabeli z wykresu 1. Funkcja nie usunęła za to duplikatów wartości, jak 200 w kolumnie Series czy „Z” z kolumny Product. Listę wartości tworzymy wpisując formułę zgodnie z powyższym wykresem – należy wybrać dwukrotnie „false”. Pierwsze false odnosi się do tego, że szukamy duplikatów w wierszach, nie kolumnach. Drugie definiuje, że chcemy widzieć na liście wartość, która została zduplikowana.
Zamieniając ostatni argument na „true” osiągamy kształt tabeli po prawej stronie wykresu nr 1, czyli otrzymujemy listę z całkowitym pominięciem duplikatów, w tym wypadku produktu Y150. Warto dodać, że funkcji UNIQUE można użyć z pominięciem ostatnich dwóch argumentów. Wtedy niejako domyślnie Excel odczytuje ostatnie dwa argumenty jako false i otrzymujemy kształt danych zbieżny ze środkową tabelą. Warto jednak znać rolę ostatnich dwóch argumentów.
5 najważniejszych funkcji w pracy kontrolera finansowego – funkcja XLOOKUP
Druga i według autora w wielu sytuacjach najważniejsza funkcja to XLOOKUP (w polskiej wersji X.WYSZUKAJ). Przy bardziej skomplikowanych modelach jest ona niezbędna i tak naprawdę zawsze na którymś etapie budowy pliku jest ona potrzebna. Wielu użytkowników nazwa może zmylić. Nie jest to funkcja VLOOKUP (wyszukaj.pionowo), znana ze wszystkich dotychczasowych wersji Excela. XLOOKUP to funkcja dodana podobnie jak UNIQUE w Excelu z 2021 roku, która stanowi odpowiedź na podstawowy mankament funkcji VLOOKUP, czyli konieczność wpisywania numeru kolumny z docelowymi danymi oraz utrzymywania kolumn tabeli w określonej kolejności. XLOOKUP całkowicie pomija ten problem i daje użytkownikowi możliwość prostego i szybkiego zmapowania danych.
Na wykresie 4 znajduje się przykładowa lista pracowników firmy wraz z podanym indywidualnym numerem pracownika, biurem, krajem i mailem. W tabeli po prawej stronie mamy z kolei imię i nazwisko kilku pracowników oraz wynagrodzenie. Cel zadania to przypisać numer ID (wiersze zaznaczone na zielono).
W naszym przypadku formuła będzie wyglądała tak, jak na wykresie nr 5. Podobnie jak w przypadku UNIQUE, możemy zakończyć wpisywanie formuły na trzecim argumencie, ponieważ Excel domyślnie przypisuje wartości dla argumentów 3-5. Przechodząc do budowy – rozpoczynamy od zaznaczenia komórki z danymi, których będziemy szukać w zestawie danych. Kolejne dwa argumenty to wybór kolumny, która powinna zawierać wartość „Danuta Makowska” oraz kolumny z ID przypisanym do tej osoby. W tym miejscu widać przewagę XLOOKUP nad VLOOKUP. By zrobić to samo za pomocą drugiej z funkcji, kolumna „Imię i nazwisko” musiałaby znaleźć się po lewej stronie kolumny „EMP ID”. Co więcej, należałoby podać, że chcemy przypisać wartość z drugiej w takim wypadku kolumny. Ostatnie trzy argumenty określają:
- jaki komunikat ma się pojawić w przypadku nieznalezienia takiej osoby w zestawie danych,
- czy chcemy szukać dokładnie takiej wartości czy przybliżonej,
- czy chcemy szukać zaczynając od ostatniego czy pierwszego wiersza.
W naszym przypadku w zupełności wystarczy standardowa (domyślna) konfiguracja z trzema argumentami lub pięcioma, w kształcie podanym na wykresie 5.
5 najważniejszych funkcji w pracy kontrolera finansowego – funkcja SUMIFS
Trzecia z funkcji, którą kontroler finansowy powinien znać i umieć stosować to formuła sumifs (w polskiej wersji SUMA.WARUNKÓW). Jest to funkcja bardzo istotna w pracy kontrolera, z tego względu, że pozwala sumować kwoty przy podaniu co najmniej dwóch warunków. Przy dużych zbiorach danych (np. sprzedażowych) jest ona nieoceniona do analiz biznesowych. W kolumnie obok kolejny zestaw danych – przedstawia on sprzedaż, jaką zanotowali pracownicy o wskazanym ID, pracując w określonym kraju i biurze w różnych miesiącach między styczniem a majem 2023 roku. Załóżmy, że chcemy z tego zestawu danych wyciągnąć sprzedaż pracowników reprezentujących biuro w Rzeszowie w styczniu 2023.
By obliczyć wskazaną wartość należy w naszym przypadku określić 5 argumentów funkcji. Pierwszy argument to zawsze kolumna, z której wartości będą sumowane, czyli kolumna F – Sprzedaż (warto zaznaczać całą kolumnę, a nie tylko określone wiersze, gdyż bazując wyłącznie na wskazanych wierszach, np. od 3 do 50 możemy pominąć dane dodane na dole tabeli w kolejnych m-cach). Drugi argument to kolumna, w której wyszukujemy wartości stanowiącej pierwszy warunek. Szukamy więc w kolumnie D – Biuro wartości „Rzeszow”. Ostatnie dwa argumenty definiuje na tej samej zasadzie, co pierwszy warunek. Interesuje nas kolumna E z miesiącem sprzedaży oraz warunek 01/2023, czyli sprzedaż w styczniu. Jak widać na wykresie 6 sprzedaż łączna przy wskazanych warunkach to 13.000 zł.
5 najważniejszych funkcji w pracy kontrolera finansowego – funkcja FILTER
Przedostatnia z funkcji to funkcja FILTER (w polskiej wersji Excela FILTRUJ). Jest to przede wszystkim funkcja do wyciągania tabeli danych przy uwzględnieniu wskazanych filtrów. Czemu jest ona istotna? Głównie dlatego, że jest dynamiczna, a co więcej odpowiada na potrzeby wynikające ze słabości zwykłego filtrowania tabeli (które możemy znaleźć w karcie „dane” i sekcji „sort & filter” lub włączyć poprzez skrót ctrl + shift + L). W przypadku zwykłego filtrowania nie widzimy wierszy, które zostały zafiltrowane. Nie stanowi to dla nas problemu, jeśli w zakładce mamy jedynie jedną tabelę z danymi. Co jednak, jeśli w danej zakładce mamy 5 tabel, a liczba wierszy nie jest stała. W takim wypadku albo musimy zrezygnować z tabel, albo z filtrowania. Jest na to rozwiązanie – funkcja FILTER. Spójrzmy na poniższy przykład.
Tabela 1 zawiera listę pracowników wraz z podanym mailem, nr ID, krajem oraz biurem, do którego te osoby są przypisane. Pierwsze z zadań to wyfiltrowanie tylko tych pracowników, którzy są przypisani do biura w Pradze.
Zadanie to można wykonać na dwa różne sposoby. Jeden o wiele prostszy i bardziej intuicyjny, a drugi trudniejszy, ale dający pogląd na możliwości i działanie funkcji FILTER. W pierwszym przypadku wystarczyłoby wybrać zakres danych, czyli kolumny od B do H, następnie zaznaczyć kolumnę H i dodać warunek, że Excel ma sprawdzić tą kolumnę pod kątem wartości z komórki K3. Klikamy klawisz enter i otrzymamy taką tabelę, jak widoczną po prawej stronie. Natomiast można to samo zadanie wykonać, odwołując się zarówno do kraju, jak i biura. W tym wypadku oczywiste jest, że Praga jest w Czechach, natomiast można by filtrować np. po Pradze oraz roku rozpoczęcia pracy, gdybyśmy takie dane mieli. W sytuacji,, gdy mamy więcej niż jeden warunek kluczowe do zapamiętania jest to, czy nawias oddzielamy poprzez „*” czy „+”. W przypadku „*” dajemy znać Excelowi, że chcemy, by oba warunki były spełnione. Przykładowo, filtrując jedynie po Czechach otrzymalibyśmy 4 wiersze (Praga oraz Ostrava).
W kolejnym przykładzie chcemy uzyskać listę pracowników (oraz dane ich dotyczące) tylko jeśli są w strukturze przypisani do Polski lub Czech. W takim wypadku ponownie zaznaczamy kolumny od B do H oraz określamy warunki (identycznie jak w zadaniu 1), lecz łączymy je za pomocą plusa, co znaczy, że ma zostać wyfiltrowany każdy wiersz, który dotyczy Czech lub Polski.
Funkcja FILTER daje możliwość dynamicznego filtrowania zarówno z jednym, jak i większą liczbą warunków. Co więcej, nie ukrywa ona wierszy, dzięki czemu praca na wielu tabelach w jednej zakładce jest nie tylko możliwa, ale również nieutrudniona przez aspekty techniczne.
5 najważniejszych funkcji w pracy kontrolera finansowego – funkcja INDEX & MATCH
Ostatnia funkcja, a właściwie połączenie dwóch funkcji – INDEX & MATCH to alternatywa dla połączenia funkcji VLOOKUP oraz HLOOKUP. Nie jest to jednak najistotniejsze. Istotny jest fakt, że pozwala ona przygotować raport w oparciu o dwa wymiary. Poniżej zamieszczono przykładowy rachunek zysków i strat w latach 2018-2022. W takim wypadku można by się pokusić o zwykłe odwołanie do danej komórki. Jednakże co, jeśli mówimy o RZiS na 500 wierszy Excela oraz zakładce zawierającej wiele danych. W takim przypadku zdecydowanie lepiej skorzystać z funkcji INDEX & MATCH. Dla celów prezentacji zadanie to wyciągnięcie wartości przychodów, zysku brutto oraz zysku netto z pierwotnej tabeli.
Używając funkcji INDEX & MATCH warto pamiętać, że jest to, jak określono powyżej, połączenie dwóch funkcji, które mają podać pozycje komórki z daną wartością. Formuła zawiera 7 argumentów. Pierwszy z nich to zakres danych. Jest to oczywiście cała tabela (RZiS), od komórki B2 do H11. Cały zakres blokujemy za pomocą klawisza F4. Dalej przechodzimy do argumentów dotyczących wiersza. Z tego powodu zaznaczamy komórkę B14, czyli Revenue. Chcemy ustalić, gdzie znajduje się w pierwotnej tabeli wiersz „Revenue”. Również i tu blokujemy, lecz tylko kolumnę. Dalej wybieramy zakres B3-B11, bo w tym przedziale znajduje się wiersz, którego szukamy. Kolejny argument = 0 oznacza, że szukamy komórki o dokładnie takiej (nie przybliżonej) wartości. Ostatnie argumenty dotyczą warunku z kolumny, czyli roku 2021 (blokujemy wiersz). Szukamy go w komórkach od B2 do H2. Blokujemy cały zakres. Ponownie interesuje nas dopasowanie dokładne. Finalnie po kliknięciu ENTER oraz przekopiowaniu formuły w dół i w prawo otrzymujemy dynamiczną formułę.
Poniższa formuła jest z pewnością najtrudniejsza do opanowania ze wszystkich wskazanych i wygląda na skomplikowaną. Jednakże po kilku zastosowaniach i błędach staje się ona prosta, a jednocześnie bardzo ważna podczas przygotowywania raportów.
15 najważniejszych skrótów klawiaturowych w pracy kontrolera finansowego
Powyższa lista 15 skrótów jest oczywiście subiektywną listą autora. Niemniej każdy controller finansowy przynajmniej z częścią z nich powinien się zgodzić, a obowiązkowo większość lub wszystkie powinien znać i stosować, by skrócić czas pracy na pliku Excela do minimum.
Podsumowanie
W niniejszym artykule wskazano 5 subiektywnie wybranych, najważniejszych funkcji w Excelu oraz 15 skrótów klawiaturowych, które controller finansowy powinien znać. Wymóg znajomości wynika przede wszystkim z dynamiki rozwoju stanowiska oraz biznesu, a także nacisku na optymalizacje procesów, zwłaszcza w dużych korporacjach. Opanowanie powyższych skrótów i funkcji nie zamyka tematu i nie daje gwarancji sukcesu, lecz z pewnością do niego przybliża.
