Tworzenie raportów sprzedażowych za pomocą tabel przestawnych

Samo tworzenie tabel przestawnych jest bardzo proste. Z karty wstawianie wybieramy opcję tabela przestawna, a następnie Excel automatycznie zaznacza obszar tabeli, który mamy w arkuszu. Naszą tabelę możemy umieścić we wskazanej przez nas lokalizacji w pliku albo automatycznie utworzyć nowy arkusz z tabelą przestawną.

Ważna uwaga: pamiętaj aby w Twoim zbiorze danych każda kolumna miała tytuł, w przeciwnym wypadku tabela przestawna nie będzie mogła się utworzyć.

Dodatkowo, możemy wybrać dane, które nie znajdują się w arkuszu Excel tylko np. w bazie danych Acces lub Sql - w tym celu wystarczy, że wybierzemy opcje „użyj zewnętrznego źródła danych” i interesujące nas połączenie.

Kiedy już utworzyliśmy naszą tabelę w nowym arkuszu, możemy już swobodnie wykonywać operacje na naszej tabeli przestawnej.

Załóżmy że na naszych danych chcielibyśmy utworzyć raport dotyczący sprzedaży produktów przez naszych pracowników. Przeciągamy kolumnę sprzedawca do wierszy, a wartość i ilość do wartości i gotowe. Dodatkowo chcielibyśmy zweryfikować, jak taka sprzedaż prezentowała się w maju 2019 roku. Należy teraz przenieść kolumny rok i miesiąc do pola filtru, a następnie wybrać odpowiednie wartości i gotowe.

   Zobacz jak szybko i łatwo udało się przygotować takie zestawienie, nie używając nawet żadnych skomplikowanych formuł. Zatrzymajmy się na chwilę w tym miejscu. Chciałbym pokazać jak można modyfikować wartość w tabeli przestawnej. Na naszej liście pól klikamy prawym przyciskiem na np. ilość - widzimy ze można wybrać jak chcemy przedstawić daną zmienną np. suma, liczba itp. W ten sposób możemy modyfikować sposób przedstawiania naszej wartości w zależności od wymaganych przez nas potrzeb. Tabela może wskazać nam np. wartość maksymalną lub minimalną, czy po prostu zliczyć rekordy i przedstawić wartość jako liczba.

Kiedy jesteśmy już przy temacie liczb i ich formatowania, dodamy teraz pole obliczeniowe do naszej tabeli. Z zagadki analizy wybieramy pole obliczeniowe. Tutaj wykonujemy dwie czynności, nazywamy nasze pole obliczeniowe, a także wprowadzamy działanie, które ma ono wykonywać. W naszym przypadku chcemy obliczyć średnią wartość sprzedaży dla każdego z naszych sprzedawców. Otóż, wartość dzielimy przez ilość i otrzymujemy spodziewany przez nas rezultat. Obliczeń jakie możemy tu wykonać jest nieskończenie wiele i tylko od Ciebie i Twoich potrzeb zależy co konkretnie należy tutaj policzyć.

Jeżeli chcemy aby dane które pobraliśmy z tabeli przestawnej, nie były w niej zagnieżdżone, należy wyłączyć funkcje „weź dane z tabeli”, dzięki temu będziemy mogli kopiować i przeciągać wartości z tabel przestawnych w inne komórki.

Ważnym elementem w pracy z tabelami przestawnymi jest umiejętność grupowania.
W naszym przykładzie pogrupujemy tabele przestawną po wartościach naszej sprzedaży. W tym celu najeżdżamy myszką na tabelę przestawną, klikamy prawy przycisk myszy i z listy która nam się pokazała wybieramy grupuj. Możemy wybrać ustawienie, które poda nam Excel albo ustawić je ręcznie samemu. Wybieramy wartość początkową, końcową i rozpiętość naszych przedziałów.

Musimy pamiętać, że nie ma możliwości grupowania po nieregularnych przedziałach. W takim przypadku, musimy robić to ręcznie i grupować za każdym razem interesujący nas przedział.

Dużym ułatwieniem w pracy z tabelami przestawnymi jest umiejętność ustawienia dynamicznego zakresu danych, dzięki któremu możemy swobodnie dopisywać kolejne rekordy do naszych danych, a tabela przestawna automatycznie - przy jej odświeżaniu - je pobierze.

Formuła, która pozwala nam na taki zabieg ma następującą postać:

Dużą zaletą tej funkcji jest uniwersalność, dla każdego zbioru danych jest ona taka sama. Jedyne o czym musimy pamiętać, to zmieniająca się liczba kolumn oraz inna nazwa arkusza w którym znajdują się nasze dane. Jako, że nasza tabela posiada 6 kolumn, to w funkcji na ostatniej pozycji widzimy 6. Analogicznie jeśli np. mieliśmy zbiór składający się z 15 kolumn, a nasz arkusz nazywałby się Arkusz2 to funkcja wyglądałaby następująco:

PRZESUNIĘCIE(Arksuz2$A$1;0;0;ILE.NIEPUSTYCH(Arkusz2$A:$A);15)

Na tym przykładzie jeszcze dokładniej można zrozumieć, jak należy zmieniać wartości funkcji w odniesieniu do zbioru danych który posiadamy.

Następnie z zakładki formuły wybieramy menedżer nazw, wybieramy nowy, dowolnie ją nazywamy i klikamy ok. Następnie kopiujemy wcześniej przygotowaną przez nas funkcję i wklejamy ją w miejsce odwołania naszego menedżera nazw. Zatwierdzamy i przechodzimy do naszej tabeli przestawnej, gdzie zmieniamy obszar danych. Z zakładki analiza wybieramy zmień źródło danych i wpisujemy nazwę stworzonej przez nas formuły, w tym przypadku „Tabela”. Zatwierdzamy i gotowe. W ten sposób stworzyliśmy dynamiczny zakres danych.

Polecamy także:

• Wyszukaj.pionowo – jak znajdować dane w tabeli?
• Funkcje tekstowe – jak wykonywać operacje na wyrazach?