Tabele i wykresy przestawne od A do Z - dynamiczna analiza dużych zbiorów danych - ebook

Wstęp

Tabela przestawna jest rozbudowanym i interakcyjnym narzędziem do szybkiego podsumowania dużych ilości danych, tworzenia analiz w interesującym nas układzie oraz o określonym stopniu szczegółowości. Dlatego, zanim zaczniesz budować skomplikowane formuły, spróbuj wykorzystać możliwości tego narzędzia. Książka wprowadza w podstawy przygotowywania tabel przestawnych, a także porusza bardzo ważny temat jakości danych. Kluczem do tworzenia raportów przestawnych jest bowiem poprawne przygotowanie danych źródłowych. Autor objaśnia podstawowe zasady, do których warto się stosować w trakcie opracowywania danych źródłowych do raportu. Jeśli zna się odpowiednie sposoby, dane można szybko przystosować do wymagań tabel przestawnych.

Ponieważ najlepiej uczyć się na praktycznych przykładach, książka zawiera instrukcje, jak utworzyć tabelę przestawną do oceny kosztów w podziale na poszczególne MPK, przeanalizować dane sprzedażowe czy skonsolidować dane z wielu arkuszy. Dowiesz się również, jak zwizualizować analizowane dane z użyciem raportów przestawnych.

W wielu przypadkach sposób tworzenia tabel przestawnych bywa uciążliwy. Dodatkowo wielu użytkowników nie do końca rozumie zasadę ich działania i budowy, więc unikają używania tej funkcji Excela. Jeśli również nie czujesz się zbyt pewnie w temacie tabel przestawnych, książka pokazuje także, jak wykorzystać do ich tworzenia makra VBA, które automatycznie będą sterować i zarządzać tabelami przestawnymi.1. Tabele przestawne – podstawy

Kluczem do bezproblemowego zbudowania raportów przestawnych jest poprawne przygotowanie danych źródłowych. Oto kilka podstawowych zasad, do których warto się stosować w trakcie opracowywania danych źródłowych do raportu:

- Należy wyraźnie zaznaczyć wiersz nagłówkowy, który powinien być pierwszym wierszem listy z danymi.
- Nie łączymy tych samych kategorii danych w jednej kolumnie (np. data i miejsce urodzenia), gdyż znacznie ogranicza to możliwość zbudowania elastycznego raportu oraz modyfikacji jego układu.
- Nie stosujemy scaleń komórek wewnątrz listy, a także staramy się wyeliminować puste zakresy komórek, a w szczególności wiersze.
- Usuwamy komórki zawierające podsumowania liczb z danej kolumny listy.
- Jeśli planujemy grupować dane okresami w gotowym raporcie przestawnym, upewnijmy się, że daty w liście źródłowej są wprowadzone w odpowiednim formacie rozpoznawalnym dla Excela (poprawnie rozpoznane daty są wyrównane do prawej krawędzi komórki).

Rysunek 1.1. przedstawia poprawnie sporządzoną listę źródłową, na której podstawie bez problemu zbudujemy raport przestawny.

Rysunek 1.1. Przykładowa lista źródłowa

1.1. Tworzenie tabeli przestawnej krok po kroku

Do zbudowania od podstaw raportu przestawnego wykorzystamy dane z rysunku 1.1.

Wykonaj następujące czynności:

1. Kliknij dowolną komórkę w obrębie listy.

2. Otwórz kartę Wstawianie i w grupie poleceń Tabele wybierz Tabela przestawna.

3. Wyświetlone zostanie okno dialogowe z oznaczonym obszarem arkusza zawierającym całą tabelę źródłową. Jeśli chcesz zmienić zakres danych pobieranych do tabeli przestawnej, to określ go tutaj.

4. U dołu okna wskaż lokalizację tabeli (nowy czy bieżący arkusz) i zatwierdź ustawienia, klikając przycisk OK.

W arkuszu pojawi się szkielet tabeli przestawnej widoczny na rysunku 1.2.

Rysunek 1.2. Szkielet tabeli przestawnej

Z prawej strony ekranu pojawi się okno Lista pól tabeli przestawnej. Będzie się uaktywniać zawsze, gdy klikniemy jedną z komórek należących do obszaru raportu przestawnego.

Okno to służy do rozplanowania pól w raporcie. Zwróćmy uwagę, że w oknie Lista pól tabeli przestawnej znajdują się nazwy odpowiadające nagłówkom poszczególnych kolumn listy danych.

Pola można też przenosić bezpośrednio na szkielet raportu, co jest wygodniejsze i bardziej intuicyjne.

Aby taką możliwość zyskać:

1. Kliknij raport prawym przyciskiem myszy i z menu podręcznego wybierz polecenie Opcje tabeli przestawnej.

2. W wyświetlonym oknie dialogowym przejdź do zakładki Wyświetlanie.

3. Uaktywnij opcję Układ klasyczny tabeli przestawnej (przeciąganie pól w siatce) i zatwierdź ustawienia przyciskiem OK. Uzyskasz efekt jak na rysunku 1.3.

Rysunek 1.3. Klasyczny układ tabeli przestawnej

Możemy teraz przystąpić do wygodnego rozplanowania pól w raporcie. Przyjmijmy, że chcemy utworzyć tabelę przestawną prezentującą wydatki poszczególnych działów na określone towary:

W tym celu:

1. Chwytamy za pomocą myszy pole Dział i przeciągamy je nad obszar arkusza opisany jako Upuść pola wierszy tutaj.

2. Następnie przycisk Towar przesuwamy nad obszar arkusza opisany jako Upuść pola kolumn tutaj.

3. Przycisk Wartość przeciągnij nad obszar arkusza opisany jako Upuść pola wartości tutaj.

Po wykonaniu tych działań powinniśmy uzyskać tabelę przestawną jak na rysunku 1.4.

Rysunek 1.4. Prosta tabela przestawna

1.2. Modyfikacja układu raportu przestawnego

Dzięki możliwości przeciągania pól w obszarze raportu z łatwością dostosujemy go do naszych potrzeb. W obecnej postaci tabela przedstawia kwotowe wartości zamówień poszczególnych działów. Nas interesują jednak inne informacje. Chcemy się dowiedzieć, ile artykułów biurowych zamawiała konkretna osoba. Dzięki tabeli przestawnej taki raport powstanie w kilka sekund.

Wykonaj następujące czynności:

1. W okienku Lista pól tabeli przestawnej wyłącz zaznaczenie pola Wartość. Do pola wartości przeciągnij pole Liczba.

2. W analogiczny sposób wyłącz pole Dział i przenieś do Etykiet wierszy pole Zamawiający.

Rysunek 1.5. Raport został zmodyfikowany

Kilka ruchów myszą i nowy raport jest gotowy, co jest przedstawione na rysunku 1.5.

1.3. Selekcja danych w raporcie

Dzięki przeciąganiu pól z jednego obszaru raportu do drugiego wyświetlimy dane z określonej kategorii, jednakże w całości. W wielu sytuacjach potrzebny jest jedynie ich fragment. Spróbujemy teraz ograniczyć zakres wyświetlanych informacji jedynie do tych potrzebnych. Zasada działania selekcji danych jest bardzo zbliżona do filtrowania.

Rysunek 1.6. Filtrowanie raportu

Wykonaj następujące czynności:

1. W oknie Lista pól tabeli przestawnej przeciągnij pola Dział oraz Kategoria do obszaru o nazwie Filtr raportu.

2. Tuż nad raportem pojawiły się 2 nowe pola z rozwijanymi listami. Rozwiń górną listę i wskaż dział Administracja.

3. Teraz rozwiń dolną listę i określ kategorię produktów, np. Biurowe.

Zakres danych w raporcie został zawężony. Prezentuje wyniki dotyczące pracowników działu administracyjnego oraz jedynie produktów biurowych. Aktualny widok arkusza przedstawia rysunek 1.6.

1.4. Grupowanie danych w tabeli przestawnej

Dane łączymy w grupy wówczas, gdy jest ich zbyt wiele wyświetlanych w polu kolumn lub wierszy. W grupy można łączyć wszystkie rodzaje danych (tekst, liczby, daty i czas). Najbardziej użyteczne wydaje się grupowanie dat pozwalające uzyskiwać najróżniejsze raporty okresowe. Przyjmijmy, że naszym zadaniem jest teraz uzyskanie raportu, który będzie wyświetlał zamówienia z podziałem na miesiące i kwartały. Zwróćmy uwagę, że w tabeli źródłowej dysponujemy jedynie polem Data. Excelowi to jednak w zupełności wystarczy.

Darmowy fragment