Znamy już zalety wielu standardowych narzędzi arkuszowych, a także potrafimy wiele osiągnąć funkcjami.

 

Dopiero jednak tabela przestawna jest narzędziem, które wprowadza nas w świat profesjonalnej analizy danych.

 

Dlaczego?

 

Umożliwia ona bowiem analizę danych:

przy czym, stworzenie tej tabeli jest proste, wręcz banalne i każdy to potrafi.

 

Wyprzedzając nieco temat poboru danych, opisany szczegółowo w następnych odcinkach przyjrzyjmy się tabeli (dostępna w pliku Tabela Przestawna.xls):

 

Rys.1. Szeroka tabela danych, dot. sprzedaży

 

Powtórzę, pozwólcie Państwo: wyprzedzamy tu temat poboru danych – przyjmujemy tu bowiem założenie, że takie dane już posiadamy, choć – skądinąd – nie jest to takie proste.

Zaznaczmy ten obszar myszą.

 

Tu dygresja: Aby zaznaczyć szybko ciągły obszar danych, nie trzeba koniecznie zaznaczać go myszą – może to być długotrwałe. Spróbujmy ustawić aktywną komórkę  w lewym-górnym rogu obszaru danych i 1) nacisnąć [Ctrl+Shift+8] – zaznacza ciągły obszar danych, czyli tu: tabelę danych lub 2) trzymając naciśnięty [Shift] nacisnąć [Ctrl+KursorWLewo], a potem [Ctrl+KursorWDół] – w obu przypadkach tabela jest zaznaczona, niezależnie od jej rozmiaru.

 

Z menu Dane wybieramy opcję Tabela przestawna lub wykres przestawny

 

Rys.2. Uruchomienie kreatora tabeli przestawnej w menu Excela

 

Kolejne kroki to:

 

Rys. 3. Określenie naszego zamiaru

 

Gdy w tym momencie (co polecam!) naciśniemy przycisk Zakończ, kreator stworzy nam gotową tabelę, widoczną na Rys.5.

Gdy naciśniemy Dalej, kreator zapyta o zakres danych dla tabeli.

 

Rys.4. Podanie zakresu tabeli przestawnej

 

Osiągamy efekt, widoczny na Rys.5. (bez dorysowanych elips i strzałek). Następnie chwytamy myszą kolejne pola i przeciągamy na odpowiednie obszary tabeli, tak jak pokazano to na Rys.5. Uwaga! Elementy danych (tu: pole WART_NET) polecam przeciągać jako ostatnie.

 

Rys.5. Wybór pól tabeli przestawnej

 

Osiągamy efekt – gotową, na razie prostą, tabelę przestawną (bez dorysowanych strzałek). W tym miejscu polecam poświęcić chwilę na lepsze poznanie tej, najważniejszej funkcjonalności tabeli przestawnej, jaką jest możliwość przeciągania pól jak się chce i gdzie się chce. Mamy do dyspozycji obszar stron tabeli (tu: A1:E1) – na nim umieszczamy pola z informacją nadrzędną – tu grupy klientów GR_ODB. Możemy przeglądać wszystkie grupy naraz lub wybrać z listy (strzałka w komórce B1) żądaną grupę klientów.

Po lewej (tu: A5:A20) mamy OBSZAR WIERSZY tabeli - służy on do prezentacji listy zawartości żądanego pola, tu użyliśmy go do pogrupowania sprzedaży według klientów

OBSZAR KOLUMN (tu: B4:D4) służy do prezentacji i grupowania danych według kryterium używanego rzadziej i mniej głęboko, zwykle umieszcza się tu wymiar czasu.

OBSZAR DANYCH (tu: B5:D20), jak sama nazwa wskazuje, służy do prezentacji danych zagregowanych, tu i domyślnie dla danych numerycznych, funkcją SUMA – tu: prezentuje sumy sprzedaży według klientów dla poszczególnych dat. Proste, nieprawdaż?

 

Rys.6. Tabela przestawna

 

Gdy wykonamy kolejny krok, tj. przeniesiemy pola miasto (na lewo od pola NAZWA) i nr_fakt (na prawo od pola NAZWA, gdy pojawi się charakterystyczna, szara linia między obszarami lub polami), tak, jak to pokazane na Rys.6., otrzymamy prawie gotową tabelę, grupującą klientów według miast, ale prezentującą również wszystkie faktury poszczególnych klientów - pełna strukturalizacja informacji. Szybko, łatwo, myszką – przeciągamy i mamy gotowy efekt!

 

Jeszcze tylko musimy wykonać kilka zabiegów kosmetycznych. Usuwamy podsumowania pozycji – według mnie, zaciemnia to obraz tabeli.

 

Rys.7. Niepotrzebne podsumowania (opcja domyślna kreatora)

 

Dwukrotnie klikamy na polu MIASTO i, gdy wyświetli się okno pola tabeli przestawnej, ustawiamy opcję Sumy częściowe na pozycji Brak. Postępujemy analogicznie również dla pola NAZWA oraz, ewentualnie, wszystkich innych pól, gdy takie sumy częściowe będą nam przeszkadzać.

 

Przeszkadza nam jeszcze pasek narzędzi tabeli przestawnej – możemy go albo odsunąć, albo umieścić obok innych pasków narzędzi, albo zamknąć. Zawsze można do niego wrócić, klikając prawym przyciskiem myszy na paskach narzędzi i uaktywniając pasek Tabela Przestawna.

 

Nasza tabela przestawna wygląda już dobrze, ale … gdzie są sumy zbiorcze dla klienta czy dla miasta? Otóż równie ważną możliwością tabeli przestawnej jest możliwość prezentacji danych na różnym stopniu ich agregacji. Klikajmy (dwukrotnie) do woli! Proponuję kolejno: Na firmie BRONEX, zwijając i rozwijając jej faktury, dalej: na mieście OPOLE, zwijając i rozwijając firmy opolskie i można tak robić dla każdego pola agregującego. Można grupować również dane według żądanego poziomu agregacji: należy w tym celu ustawić się np. na polu NAZWA i z paska narzędzi tabeli przestawnej wybrać  (Ukryj szczegóły). Proszę się przyjrzeć również pozostałym narzędziom na tym pasku. Wszystkie są opisane „dymkami”.

 

Pozostaje nam jeszcze sformatować dane numeryczne: Klikamy dwukrotnie pole agregujące Suma: WART_NET (tu: w komórce A3) i w oknie „Pole…” wybieramy Liczby, dalej (na liście) Liczbowe, wybieramy użycie separatora tysięcznego i potwierdzamy nasze zmiany.

Nasza tabela jest coraz ładniejsza, a w czym nam potrafi pomóc merytorycznie? Może nam wszystko odpowiednio posortować, tworząc ranking naszych klientów, a nawet ranking miast według wielkości sprzedaży! Dwukrotnie klikamy na polu MIASTO, w oknie pola wybieramy opcję Zaawansowane, dalej Opcje Autosortowania ustawiamy na Malejąco, a na liście Używane pole wybieramy Suma:WART_NET i potwierdzamy. Powtórzmy te kroki dla pola NAZWA i mamy gotowy, strukturalny ranking naszej sprzedaży według miast, a w ramach miast, poszczególnych klientów. Oczywiście powinniśmy ukryć szczegóły, dotyczące faktur – patrz wyżej.

 

Oto imponujący efekt naszej pracy:

 

Rys.8. Gotowa tabela przestawna

 

Ale nawet to jeszcze nie koniec!

 

Kliknijmy narzędzie rysowania wykresu przestawnego  !

W arkuszu obok powstał wykres, zachowujący wszystkie cechy tabeli przestawnej, dane są prezentowane hierarchicznie i można je również zwijać i rozwijać!

 

Rys.9. Wykres przestawny

 

Proszę zwrócić uwagę na zwinięte dane dla Warszawy – ustawiamy się na warszawa i ukrywamy szczegóły.

 

Podsumowanie:

Oto kolejne, potężne narzędzie analityczne w naszych rękach. Niedługo dowiemy się, jak takie dane (bo kluczem tu jest jakość danych) możemy łatwo otrzymywać.

 

Test umiejętności:

  1. Sporządzić tabelę przestawną dla danych tabeli faktura w pliku DaneHandel.xls
  2. Sporządzić tabelę przestawną na danych, użytych w tym przykładzie – terminy płatności (pole TERMIN) w rozbiciu na fakturę i firmę (tylko po lewej stronie tabeli umieszczamy od lewej: TERMIN, FAKTURA i FIRMA) – czyli kalendarz płatności.
  3. Wykonać tabelę przestawną, a następnie wykres przestawny, pokazujący wyłącznie firmy wrocławskie w strukturze: Grupa klientów (GR_ODB), Klient (NAZWA).