Raport zestawieniowy w Excelu
Ten artykuł ma na celu pokazanie, jak krok po kroku przygotować w Excelu przejrzysty raport zestawieniowy, który pozwoli skutecznie porównać sprzedaż faktyczną z planowaną oraz przeanalizować dynamikę zmian rok do roku.
Naszą pracę rozpoczniemy od poznania danych źródłowych. Są one podzielone na dwa arkusze: Faktyczny (Rysunek 1) i Plan (Rysunek 2).
Dane z faktycznej sprzedaży znajdują się w tabeli tSprzedaż, natomiast planowane wyniki sprzedażowe znajdują się w standardowym zakresie komórek. Jednym z powodów, dla którego sprzedaż faktyczna znajduje się w tabeli Excela, jest to, że są to dane sprzedażowe z kilku lat. Układ danych w tabeli Excela ułatwia też m.in. analizę danych i odwoływanie się do nich z innego arkusza.
Przygotowanie raportu
Na podstawie zgromadzonych danych będziemy chcieli stworzyć prosty raport porównawczy, w którym będziemy mogli wybierać miesiąc, który chcemy analizować oraz chcemy, żeby rok automatycznie był ustawiany na aktualny. Wstępny szablon raport pokazuje Rysunek 3.
Mamy już wypisane wszystkie miasta, w których dokonujemy sprzedaży. Następnie potrzebujemy obliczyć aktualny rok, posługując się następującą formułą:
=ROK(DZIŚ())
Wyciąga ona rok z dzisiejszej daty zwracanej przez funkcję DZIŚ. Data zwracana przez tę funkcję będzie się przeliczać za każdym razem, gdy zmienimy wartość w komórkach arkusza, a także przy każdym uruchomieniu pliku.
W następnym kroku dodamy listę rozwijaną w komórce C4 (Rysunek 3). Żeby podkreślić, że jest ona wyjątkowa, został jej ustawiony inny kolor wypełnienia od sąsiadujących komórek. Gdy zaznaczymy interesującą nas komórkę, na karcie Dane klikamy w polecenie „Poprawność danych”. Otworzy to okno „Sprawdzanie poprawności danych”, gdzie na zakładce Ustawienia w polu „Dozwolone” wybieramy opcję „Lista” (Rysunek 4). Następnie możemy wstawić w pole „Źródło” zakres komórek, w którym znajduje się lista miesięcy. My taką listę mamy na arkuszu Plan (Rysunek 2) w nagłówkach kolumn. Wystarczy, że po kliknięciu w pole „Źródło” zaznaczymy myszką odpowiedni zakres. Excel wstawi zakres zaznaczonych komórek (=Plan!$B$2:$M$2), a nam pozostaje jedynie zatwierdzenie utworzenia listy rozwijanej przez kliknięcie przycisku OK.
Dzięki temu, po zaznaczeniu komórki C4, obok niej pojawi się ikona rozwijanej listy, za pomocą której możemy wybrać dowolny miesiąc z wskazanego zakresu (Rysunek 5).
Obliczenia
Po ustaleniu warunków do obliczeń możemy odszukać powiązane z nimi wartości sprzedaży. Najpierw wyciągniemy wartości z aktualnego roku. Żeby to zrobić, potrzebujemy połączyć wszystkie warunki wynikające z naszego raportu (czyli rok, miesiąc oraz nazwa miasta), a następnie odszukać odpowiadający im wiersz w tabeli tSprzedaż. Użyjemy do tego następującej formuły (Rysunek 6):
=X.WYSZUKAJ($C$3&$C$4&B8;tSprzedaż[Rok]&tSprzedaż[Miesiąc]&tSprzedaż[Miasto];tSprzedaż[Sprzedaż];"")
Wykorzystałeś swój limit bezpłatnych treści
Pozostałe 68% artykułu dostępne jest dla zalogowanych użytkowników portalu. Zaloguj się, wybierz plan abonamentowy albo kup dostęp do artykułu/dokumentu.
Zaloguj się
