ul. Zygmunta Starego 1/3, 44-100 Gliwice, (32)724-14-29

Tabele przestawne stosowane są do szybkiej analizy danych. Funkcja ta została dodana do MS Excel  od wersji 2007 i usprawniła pracę analityków o kilkaset procent. Aby posłużyć się tabelami przestawnymi musimy mieć odpowiednio przygotowanie dane zawierające nagłówki.

1

W celu utworzenia tabeli przestawnej należy zaznaczyć dowolną komórkę w tabeli danych i z zakładki WSTAWIANIE wybrać Tabela przestawna.

2

Pokaże się nam okno Tworzenia tabeli przestawnej.

6

Dzięki temu, że przed uruchomieniem kreatora tabel została zaznaczona komórka z danymi, Excel domyślnie przyjął zakres danych do analizy. Ważne jest to, że Excel zaznaczy zakres do ostatniego niepustego wiersza, więc jeśli w naszych danych jest jakaś przerwa, to analiza będzie błędna.

Aby uniknąć takich sytuacji warto sformatować nasze dane jako tabelę. W celu sformatowania danych jako tabela należy:

  • Zaznaczyć cały zakres danych używając do tego skrótu Ctrl + Shift + strzałka w bok zaczynając od pierwszej komórki z nagłówkiem. To spowoduje zaznaczenie wszystkich nagłówków.
  • Następnie nie puszczając Ctrl i Shift naciskamy strzałkę w dół co spowoduje zaznaczenie danych do ostatniego niepustego wiersza, a Excel pokaże nam to miejsce.

4

  • Jeśli pod pustym wierszem znajdują się kolejne dane, to przyciskamy strzałkę w dół jeszcze raz, aż do momentu, kiedy kończą się dane.
  • Kiedy mamy już zaznaczoną całą tabelę, przechodzimy do NARZĘDZI GŁÓWNYCH i w sekcji Style wybieramy Formatuj jako tabelę. Można też użyć skrótu klawiszowego Ctrl+t.

5

Kolejnym wyborem jest lokalizacja tabeli przestawnej i dla zachowania czytelności danych warto zostawić opcję „Nowy Arkusz”, która wstawi nowy arkusz z tabelą.

6

Pojawił się nowy arkusz z tabelą przestawną, która jest pusta.

7

Po prawej stronie znajduje się lista pól, która zawiera wszystkie nagłówki z naszej tabeli, oraz plan z jej obszarami.8

Aby w naszym raporcie pojawiły się jakieś dane, wystarczy zaznaczyć dowolne pole. Załóżmy, że chcemy sprawdzić, kto był najlepszym sprzedawcą. W tym wypadku zaznaczamy pole Sprzedawca i pole Kwota, bo po wartości sprzedaży zweryfikujemy, kto był najlepszy.

9

Otrzymany raport wyglądać będzie w taki sposób.

10

Podczas edycji tabeli przestawnej na wstędze uaktywniają się dwie dodatkowe zakładki ANALIZA (OPCJE) i PROJEKTOWANIE. Aby zilustrować wyniki raportu warto użyć Wykresu przestawnego, który uruchamiamy w zakładce ANALIZA (OPCJE).

11

Pojawi się kreator Wstawiania wykresu, w którym wybieramy jaki wykres chcemy wstawić. Domyślnym wykresem jest Kolumnowy grupowany.

12

Po wstawieniu wykresu widać, że najlepszym sprzedawcą był Panecki.

13

Tabele przestawne można bardzo łatwo modyfikować i jeśli chcemy np. sprawdzić w jakim kraju była najlepsza sprzedaż wystarczy odznaczyć Sprzedawcę i zaznaczyć Państwo. Natychmiast otrzymujemy nowy raport, z którego można odczytać wynik.

14

Możemy również dodać podkategorię np. Sprzedawcę poprzez zaznaczenie tego pola.

15

Raport sprzedaży automatycznie sumuje wyświetlane wartości, ale często te sumy utrudniają analizę. W zakładce PROJEKTOWANIE znajduje się przycisk Sumy częściowe, który pozwala na wybór opcji Nie pokazuj sum częściowych. Możemy też pozbyć się Sum końcowych, oraz zmienić Układ raportu.

16

Wracając do naszego raportu można zauważyć, że Dobrowolski ma bardzo niską sprzedaż w Argentynie. Aby szybko zmodyfikować raport i sprawdzić jak sprzedawał Dobrowolski do innych krajów wystarczy zmienić kolejność warunków w obszarach pól łapiąc Sprzedawcę i przeciągając go nad Państwo.

17

Pomijając Irlandię i Niemcy widać, że jego wyniki sprzedaży nie są najlepsze. W celu sprawdzenia historii transakcji do wybranego kraju wystarczy dwukrotnie kliknąć LPM na wybranej transakcji. Po kliknięciu na sprzedaż w Irlandii pojawi się nowy arkusz z tabelą zawierającą wszystkie transakcje Dobrowolskiego w Irlandii.

18

Jeżeli chcielibyśmy wydrukować nasze dane to łapiemy pole Sprzedawca z obszaru Wierszy i przenosimy do obszaru Kolumn. Dzięki temu tabela będzie rozłożona na całym arkuszu.

19

Po dodaniu pola do obszaru Wartości z automatu liczby są sumowane, ale rozwijając menu na polu Suma z …  i wybierając Ustawienia pola wartości, możemy zmienić metodę liczenia i wybrać np. Średnią.

W tym oknie możemy też nadać format liczbowy, wyświetlany we wszystkich komórkach z pola. Wystarczy użyć przycisku Format liczby i wybrać odpowiedni np. walutowy.

20

Przydatnym obszarem są również Filtry. Po dodaniu do tego obszaru Pola z dostawcami pojawia się w lewym górnym rogu rozwijana lista, z której możemy wybrać jedną, lub kilka opcji.

21

Tabele przestawne nie aktualizują się automatycznie, dlatego po zmodyfikowaniu danych źródłowych należy użyć opcji Odśwież z zakładki ANALIZA (OPCJE), lub z menu kontekstowego raportu.

22

Sprawdźmy teraz, w którym miesiącu była najlepsza sprzedaż. W tym celu:

  • odznaczamy pola Państwo i Sprzedawca,
  • zaznaczamy Datę zamówienia,
  • klikając PPM na raporcie w dowolnym polu z datami,
  • z menu kontekstowego wybieramy Grupuj.

23

W wyświetlonym oknie wybieramy początek i koniec grupowania, ale jeśli interesuje nas cały zakres to Excel z automatu go podpowiada. Niżej wybieramy kategorię według, której chcemy grupować dane. Zaznaczamy miesiące i akceptujemy.

24

Otrzymamy raport posortowany według miesięcy.

25

Można też podzielić dane na kilka kategorii. Przechodząc ponownie do konfiguratora grupowania i zaznaczając Kwartał i Miesiąc otrzymujemy taki wygląd raportu.

26

Grupowanie można stosować również dla liczb. W tym celu:

  • odznaczmy sobie Datę zamówienia i zostawmy tylko Kwotę,
  • następnie z listy pól tabeli przestawnej przeciągamy jeszcze raz Kwotę, ale tym razem upuszczamy ją w obszarze Wierszy,
  • w obszarze Wartości zmieniamy Sumę na Licznik.

27

Po wywołaniu menu kontekstowego pola Wierszy i wyborze opcji Grupuj pokaże się inne okno, niż w przypadku grupowania dat. Liczby grupujemy po zakresach, które definiujemy przez początek, koniec i wielkość przedziału. W tym wypadku ustalamy go na 500, więc w pierwszej grupie znajdą się wszystkie transakcje o wartości od 0 do 500 zł.

28

29

Przy grupowaniu liczb wartości osiągające górną granicę przedziału np. 500 będą zaliczane do przedziału 500-1000, a wszystkie do wartości 499,99 znajdą się w pierwszym przedziale.

Grupować można również tekst, jednak należy użyć innej metody. W przypadku kliknięcia na opcję Grupuj pojawi się błąd.

30

Aby grupować tekst należy zaznaczyć komórki, które chcemy wrzucić do jednej grupy np. te ze słowem Piasek. Pozostałe nazwy możemy dodać do innej grupy zaznaczając je i trzymając klawisz Ctrl.

31

Warto wspomnieć o jeszcze jednej, ciekawej funkcjonalności tabel przestawnych. Są to Pola obliczeniowe i pozwalają na stworzenie prostej formuły wyciągającej dane z istniejących kolumn, bez konieczności dodawania nowej kolumny do naszej tabeli z danymi źródłowymi. Załóżmy, że mamy dane z kolumną Sprzdaż i Koszty, ale interesuje nas ile tak naprawdę zarobiliśmy na transakcjach. W tym celu:

  • tworzymy tabelę przestawną
  • w zakładce ANALIZA (OPCJE) wybieramy Pola, elementy i zestawy i przechodzimy do Pola obliczeniowego.
  • Wpisujemy tutaj nazwę kolumny, która zostanie wyświetlona w raporcie,
  • wpisujemy formułę (można klikać dwukrotnie LPM na kategorię z listy, lub zaznaczać ją i przyciskać Wstaw pole).
  • Zatwierdzamy Pole obliczeniowe i otrzymujemy raport z Zyskiem.

32

33

Tags:

1 comment

  1. Ładowanie strony trwa bardzo długo…

Comments are closed.