Raport zestawieniowy w Excelu

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ż];"")