TABELE I WYKRESY
PRZESTAWNE OD A DO Z
– DYNAMICZNA ANALIZA
DUŻYCH ZBIORÓW
DANYCH

z a a w a n s o w a n y

Krzysztof Chojnacki

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

Kup książkę

Autor:

Krzysztof Chojnacki

Kierownik grupy wydawniczej:

Agnieszka Konopacka-Kuramochi

Wydawca:

Weronika Wota

Redaktor prowadzący:

Rafał Janus

Korekta:

Zespół

Skład i łamanie:

Norbert Bogajczyk

Projekt okładki:

Piotr Fedorczyk

Druk: Miller

ISBN: 978-83-269-4510-6

Copyright by Wydawnictwo Wiedza i Praktyka sp. z o.o.

Warszawa 2016

Wydawnictwo Wiedza i Praktyka sp. z o.o.

03-918 Warszawa, ul. Łotewska 9a

tel. [phone], faks 22 617 60 10

NIP: 526-19-92-256

Numer KRS: 0000098264 – Sąd Rejonowy dla m.st. Warszawy, Sąd Gospodarczy

XIII Wydział Gospodarczy Rejestrowy. Wysokość kapitału zakładowego: 200.000 zł

„Tabele i wykresy przestawne od A do Z – dynamiczna analiza dużych zbiorów danych” wraz z przy-

sługującymi Czytelnikom innymi elementami dostępnymi w subskrypcji (e-letter, strona WWW

i inne) chronione są prawem autorskim. Przedruk materiałów opublikowanych w książce „Tabele

i wykresy przestawne od A do Z – dynamiczna analiza dużych zbiorów danych” oraz w innych

dostępnych elementach subskrypcji – bez zgody wydawcy – jest zabroniony. Zakaz nie dotyczy cyto-

wania publikacji z powołaniem się na źródło.

Publikacja „Tabele i wykresy przestawne od A do Z – dynamiczna analiza dużych zbiorów danych”

została przygotowana z zachowaniem najwyższej staranności i wykorzystaniem wysokich kwalifika-

cji, wiedzy i doświadczenia autorów oraz konsultantów. Zaproponowane w publikacji „Tabele i wy-

kresy przestawne od A do Z – dynamiczna analiza dużych zbiorów danych” oraz w innych dostęp-

nych elementach subskrypcji wskazówki, porady i interpretacje nie mają charakteru porady prawnej.

Ich zastosowanie w konkretnym przypadku może wymagać dodatkowych, pogłębionych konsultacji.

Publikowane rozwiązania nie mogą być traktowane jako oficjalne stanowisko organów i urzędów

państwowych. W związku z powyższym redakcja nie może ponosić odpowiedzialności prawnej za

zastosowanie zawartych w publikacji „Tabele i wykresy przestawne od A do Z – dynamiczna analiza

dużych zbiorów danych” lub w innych dostępnych elementach subskrypcji wskazówek, przykładów,

informacji itp. do konkretnych przypadków.

Kup książkę

3

Spis treści

Wstęp .......................................................................................................................................... 5
1. Tabele przestawne – podstawy ....................................................................................... 7

1.1. Tworzenie tabeli przestawnej krok po kroku .......................................................... 8
1.2. Modyfikacja układu raportu przestawnego .......................................................... 10
1.3. Selekcja danych w raporcie ...................................................................................... 11
1.4. Grupowanie danych w tabeli przestawnej ............................................................. 11
1.5. Tworzenie różnych wariantów raportów ............................................................... 13
1.6. Własne obliczenia w raportach ............................................................................... 13

2. Poprawianie układu danych źródłowych, aby zbudować tabelę przestawną ...... 15

2.1. Problemy z budowaniem tabel przestawnych ....................................................... 15
2.2. Budowa formuł zmieniających układ danych ....................................................... 16
2.3. Zamiana formuł na wartości ................................................................................... 20

3. Tabele przestawne – triki .............................................................................................. 22

3.1. Grupowanie danych w tabelach przestawnych ..................................................... 22
3.2. Prezentacja danych w ujęciu procentowym .......................................................... 25
3.3. Aktualizowanie tabeli przestawnej o nowy zakres danych ................................. 27
3.4. Wyświetlanie 10 największych i 10 najmniejszych wartości ............................... 29

4. Ukrywanie danych w tabeli przestawnej .................................................................... 30

4.1. Ukrywanie wierszy w raporcie ................................................................................ 30
4.2. Samodzielne przygotowanie skoroszytu ................................................................ 32
4.3. Inny sposób ukrywania danych w tabelach przestawnych ................................. 35
4.4. Filtrowanie za pomocą makra ................................................................................ 36

5. Tabele i wykresy przestawne ......................................................................................... 38

5.1. Analiza kosztów z wykorzystaniem tabeli przestawnej ....................................... 38
5.2. Dostosowywanie pól tabeli przestawnej ................................................................ 42
5.3. Modyfikacja tabeli przestawnej – pokaż strony filtru raportu ........................... 45
5.4. Obliczenia w tabeli przestawnej ............................................................................. 46
5.5. Grupowanie danych w tabeli przestawnej ............................................................. 48
5.6. Wykresy przestawne ................................................................................................. 50

6. Wizualizacja wyników sprzedażowych za pomocą tabeli przestawnej ................ 53

6.1. Tworzenie tabeli przestawnej z danych sprzedażowych ...................................... 55
6.2. Wizualizacja wyników sprzedaży firmy ............................................................... 56
6.3. Format wyświetlanych danych w polu Wartości .................................................. 57
6.4. Układ raportu tabeli przestawnej ........................................................................... 59
6.5. Style tabeli przestawnej ............................................................................................ 61
6.6. Sumy cząstkowe i końcowe ..................................................................................... 62

Kup książkę

4

52 najlepsze triki w Excelu, czyli jak szybciej wykonać obliczenia

6.7. Wyróżnianie elementu w tabeli przestawnej ......................................................... 63
6.8. Zmiana układu wyświetlania pól w obszarze Wartości tabeli przestawnej ...... 63
6.9. Stworzenie możliwości wyboru miesięcy – filtr tabeli przestawnej ................... 65

7. Analiza dużych zbiorów danych sprzedażowych ..................................................... 68

7.1. Opis przypadku ......................................................................................................... 68
7.2. Struktura danych wykorzystanych do tabeli przestawnej (2) ............................. 69
7.3. Tworzenie tabeli przestawnej .................................................................................. 69
7.4. Modyfikacja pól tabeli przestawnej ........................................................................ 72
7.5. Zastosowanie filtru raportu tabeli przestawnej (2) .............................................. 74
7.6. Wyświetlanie stron filtru raportu jako arkuszy .................................................... 76

8. Konsolidacja danych w jeden raport ........................................................................... 78

8.1. Przygotowanie danych cząstkowych ...................................................................... 78
8.2. Tworzenie raportu przestawnego ........................................................................... 79

9. Automatyczne generowanie dużej liczby raportów

przy użyciu tabeli przestawnej ..................................................................................... 83
9.1. Raport zamówienia jako tabela przestawna .......................................................... 84
9.2. Procentowy udział wartości każdego produktu w całości

wartości zamówienia ............................................................................................ 86

9.3. Automatyczne wygenerowanie raportu dla każdego z zamówień ..................... 88

10. Tworzenie tabel przestawnych za pomocą makr ...................................................... 90

10.1. Tworzenie kodu procedury ................................................................................... 91
10.2. Układ pól tabeli przestawnej ................................................................................. 93
10.3. Formatowanie tabeli ............................................................................................... 96
10.4. Wartości procentowe w tabelach .......................................................................... 96
10.5. Ukrywanie i odkrywanie wybranych informacji ............................................... 99
10.6. Używanie makr we wszystkich skoroszytach ...................................................... 99

Kup książkę

5

Wstęp

Wstęp

Tabela przestawna jest rozbudowanym i interakcyjnym narzędziem do szyb-

kiego 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 wy-

magań tabel przestawnych.

Ponieważ najlepiej uczyć się na praktycznych przykładach, książka zawie-

ra instrukcje, jak utworzyć tabelę przestawną do oceny kosztów w podziale

na poszczególne MPK, przeanalizować dane sprzedażowe czy skonsolido-

wać dane z wielu arkuszy. Dowiesz się również, jak zwizualizować analizo-

wane 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 czu-

jesz 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.

Kup książkę

Kup książkę

7

1. Tabele przestawne – podstawy

Kluczem do bezproblemowego zbudowania raportów przestawnych jest po-

prawne przygotowanie danych źródłowych. Oto kilka podstawowych zasad,

do których warto się stosować w trakcie opracowywania danych źródło-

wych do raportu:

• Należy wyraźnie zaznaczyć wiersz nagłówkowy, który powinien być pierw-

szym wierszem listy z danymi.

• Nie łączymy tych samych kategorii danych w jednej kolumnie (np. data

i miejsce urodzenia), gdyż znacznie ogranicza to możliwość zbudowa-

nia elastycznego raportu oraz modyfikacji jego układu.

• Nie stosujemy scaleń komórek wewnątrz listy, a także staramy się wyeli-

minować 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 przestaw-

nym, upewnijmy się, że daty w liście źródłowej są wprowadzone w odpo-

wiednim 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

Kup książkę

8

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

1.1. Tworzenie tabeli przestawnej krok po kroku

Do zbudowania od podstaw raportu przestawnego wykorzystamy dane z ry-

sunku 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 prze-

stawna.

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 za-

twierdź 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 ob-

szaru 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.

Kup książkę