INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

1/15

ST.IiE

Tabele i wykresy przestawne —

wprowadzenie

Opracowano na podstawie: John Walkenbach, „Excel 2007 PL Biblia” Helion 2007

Spis treści

1

WSTĘP ................................................................................................................................................................................ 1

2

CZYM SĄ TABELE PRZESTAWNE .......................................................................................................................................... 1

3

TABELA PRZESTAWNA NA PRZYKŁADZIE ............................................................................................................................ 2

4

DANE ODPOWIEDNIE DLA TABELI PRZESTAWNEJ ............................................................................................................... 4

5

TERMINOLOGIA TABEL PRZESTAWNYCH ............................................................................................................................ 4

6

TWORZENIE TABELI PRZESTAWNEJ .................................................................................................................................... 5

7

OBLICZENIA TABEL PRZESTAWNYCH ................................................................................................................................ 10

8

TWORZENIE WYKRESÓW PRZESTAWNYCH ....................................................................................................................... 10

9

ZADANIA .......................................................................................................................................................................... 13

1 Wstęp

Tabele przestawne są byd może jednym z najbardziej skomplikowanych narzędzi Excela. Kilka kliknięd myszą
wystarczy do tego, aby tabelę danych przetworzyd na dziesiątki różnych sposobów i uzyskad prawie każdego rodzaju
zestawienie, o którym można pomyśled.

Dlaczego „przestawna"? Czy Czytelnika ciekawi termin „przestawna"? Słowo to będące przymiotnikiem wiąże się
z przemieszczaniem. Jeśli dane potraktuje się jako fizyczny obiekt, tabela przestawna pozwoli przestawiad
zestawienie danych i oglądad je z różnych stron lub perspektyw. Tabela przestawna umożliwia proste
przemieszczanie pól, ich wzajemne zagnieżdżanie, a nawet tworzenie dowolnych grup pozycji. Jeżeli Czytelnik
otrzymałby dziwny obiekt z prośbą o jego identyfikację, prawdopodobnie przyjrzałby mu się z kilku różnych stron,
aby spróbowad go określid. Praca z tabelami przestawnymi przypomina analizę takiego dziwnego obiektu. W tym
przypadku obiektem są dane. Ponieważ tabela przestawna zachęca do eksperymentowania, można ją bez ograniczeo
przetwarzad do momentu uzyskania żądanych wyników. To, co się osiągnie, może byd zaskoczeniem.

2 Czym są tabele przestawne

Tabela przestawna

jest dynamicznym zestawieniem danych. Zestawienie to jest oparte na bazie danych,

umieszczonej w arkuszu (w postaci tabeli) lub w pliku zewnętrznym. Za pomocą tabeli przestawnej przekształcamy
dziesiątki rzędów i kolumn w interesujące prezentacje danych.

Przykładowo, tabela przestawna może udostępniad rozkłady częstości czy wielowymiarowe tabele odwołao
krzyżowych. Możliwe jest również wyświetlenie sum pośrednich na dowolnie wybranym poziomie szczegółowości.
Jednak najważniejszą cechą tabeli przestawnej jest jej interaktywnośd. Informacje w niej zawarte można przestawiad
w dowolny sposób, można wstawiad do niej dodatkowe formuły, które będą służyły nowym obliczeniom, a nawet

INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

2/15

ST.IiE

grupowad poszczególne elementy raportu. Jednak najlepsze w tym wszystkim jest to, że wystarczy kilka kliknięd, aby
tabelę przestawną zamienid na atrakcyjnie wyglądający raport.

Korzystanie z tabeli przestawnej wiąże się jednak z pewnym utrudnieniem. W przeciwieostwie do raportu opartego
na formule, tabela przestawna nie aktualizuje automatycznie danych po dokonaniu zmian. Nie stanowi to jednak
poważnego problemu, ponieważ wystarczy kliknąd ikonę Odśwież, znajdującą się na pasku narzędzi i w tabeli pojawią
się najnowsze dane.

Tabele przestawne pojawiły się w Excelu 97. Niestety wielu użytkowników nie korzysta z nich, ponieważ uważają, że
są zbyt skomplikowane. W przypadku Excela 2007 tabele przestawne w znacznym stopniu ulepszono. W efekcie
tworzenie i przetwarzanie tabele przestawnych jest prostsze niż kiedykolwiek wcześniej.

3 Tabela przestawna na przykładzie

Poniższy przykład pozwoli lepiej zrozumied pojęcie tabeli przestawnej. Rysunek 1prezentuje arkusz z danymi, które
będą podstawą do utworzenia tabeli przestawnej (plik

konta_bankowe.xlsx

).

Rysunek 1 Tabela danych używana dalej do tworzenia tabel przestawnych

Tabela zawiera informacje o nowych kontach z okresu miesiąca, założonych w banku mającym trzy oddziały. Składa
się ona z 712 rekordów, z których każdy reprezentuje nowe konto. Tabela ma następujące kolumny:

Data

Kwota

Typ konta

Otwarte przez

Oddział

Klient

wrz-01

15 000

Rachunek oszczędnościowy Przedstawiciel bankowy I

Klient banku

wrz-01

500

ROR

Przedstawiciel bankowy I

Klient banku

wrz-01

12 438

ROR

Przedstawiciel bankowy I

Klient banku

wrz-01

11 957

ROR

Przedstawiciel bankowy I

Klient banku

wrz-01

5 879

ROR

Przedstawiciel bankowy I

Klient banku

wrz-01

5 000

Lokata terminowa

Przedstawiciel bankowy I

Klient banku

wrz-01

50 000

Fundusz emerytalny

Przedstawiciel bankowy I

Klient banku

wrz-01

4 000

Fundusz emerytalny

Przedstawiciel bankowy I

Klient banku

wrz-01

13 636

Rachunek oszczędnościowy Przedstawiciel bankowy II

Klient banku

wrz-01

5 000

ROR

Przedstawiciel bankowy II

Klient banku

wrz-01

4 623

Fundusz emerytalny

Przedstawiciel bankowy II

Klient banku

wrz-01

15 276

Fundusz emerytalny

Przedstawiciel bankowy II

Klient banku

wrz-01

7 177

Fundusz emerytalny

Kasjer

II

Klient banku

wrz-01

12 000

Rachunek oszczędnościowy Przedstawiciel bankowy III

Klient banku

wrz-01

3 171

ROR

Przedstawiciel bankowy III

Klient banku

wrz-01

5 000

Fundusz emerytalny

Przedstawiciel bankowy III

Klient banku

wrz-01

6 837

Fundusz emerytalny

Przedstawiciel bankowy III

Klient banku

wrz-01

15 759

Rachunek oszczędnościowy Kasjer

III

Klient banku

wrz-01

16 000

Rachunek oszczędnościowy Przedstawiciel bankowy I

Nowy

wrz-01

14 571

Rachunek oszczędnościowy Kasjer

I

Nowy

wrz-01

4 690

ROR

Przedstawiciel bankowy II

Nowy

wrz-01

7 000

Fundusz emerytalny

Przedstawiciel bankowy II

Nowy

wrz-01

8 721

Fundusz emerytalny

Przedstawiciel bankowy III

Nowy

wrz-02

5 524

ROR

Przedstawiciel bankowy I

Klient banku

wrz-02

12 962

ROR

Kasjer

I

Klient banku

wrz-02

7 427

ROR

Przedstawiciel bankowy II

Klient banku

wrz-02

10 000

Rachunek oszczędnościowy Kasjer

II

Klient banku

wrz-02

45 000

Rachunek oszczędnościowy Kasjer

II

Klient banku

wrz-02

14 867

ROR

Kasjer

II

Klient banku

INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

3/15

ST.IiE

data otwarcia konta,

kwota wpłacona przy otwarciu konta,

typ konta (rachunek oszczędnościowy, ROR, fundusz emerytalny, lokata terminowa),

osoba otwierająca konto (kasjer, przedstawiciel bankowy);

oddział, w którym otwarto konto (I, II, III),

typ klienta (nowy lub dotychczasowy).

Baza danych zawiera wiele informacji o poszczególnych kontach bankowych i nie jest czytelna dla odbiorcy. Dlatego
konieczne jest utworzenie raportu, który przedstawi nam dane w bardziej przystępnej i zrozumiałej formie. Raport
będzie odpowiadał na pytania, które najbardziej nas interesują. W naszym przykładzie mogą one brzmied
następująco:

Jaka jest dzienna całkowita wartośd nowych depozytów dla każdego oddziału?

Ile nowych kont otwarto w każdym z oddziałów i do której grupy należą — rachunek oszczędnościowy, ROR,
fundusz emerytalny, lokata terminowa?

Jaki jest przepływ pieniędzy w poszczególnych typach kont?

Jakiego rodzaju konta najczęściej otwierają kasjerzy?

Jak wypada oddział I w porównaniu z pozostałymi dwoma?

W którym oddziale najwięcej kont otwierają nowi klienci?

Odpowiedzi na te pytania można oczywiście uzyskad za pomocą poszczególnych formuł i sortowania danych. Z reguły
jednak znacznie lepszym rozwiązaniem jest tabela przestawna, ponieważ jej utworzenie zabiera nie więcej niż kilka
sekund, a także nie wymaga wpisywania żadnej formuły i pozwala uzyskad ładnie wyglądający raport. Ponadto tabele
przestawne są o wiele mniej podatne na błędy niż formuły (w dalszej części rozdziału przedstawiono kilka tabel
przestawnych, będących odpowiedzią na powyższe pytania).

Rysunek 2 Prosta tabela przestawna

Rysunek 2 przedstawia tabelę przestawną utworzoną na podstawie przykładowych danych. Widad tu liczbę kont
otwartych w banku, podzieloną według ich rodzaju oraz poszczególnych oddziałów, w których zostały założone. Jest
to tylko jedno z wielu zestawieo, jakie można utworzyd na podstawie danych z rysunku 1.

Rysunek 3 Tabela przestawna wykorzystująca filtr raportu

Suma z Kwota

Typ konta

Oddział

Fundusz emerytalny

Lokata terminowa

Rachunek oszczędnościowy ROR

Suma koocowa

I

885 757

68 380

1 359 385

802 403

3 115 925

II

467 414

134 374

1 137 911

392 516

2 132 215

III

336 088

10 000

648 549

292 995

1 287 632

Suma koocowa

1 689 259

212 754

3 145 845

1 487 914

6 535 772

Klient

Klient banku

Suma z Kwota

Oddział

Typ konta

I

II

III

Suma koocowa

Fundusz emerytalny

548 198

286 891

291 728

1 126 817

Lokata terminowa

68 380

125 374

10 000

203 754

Rachunek oszczędnościowy

973 112

845 522

356 079

2 174 713

ROR

505 822

208 375

144 391

858 588

Suma koocowa

2 095 512

1 466 162

802 198

4 363 872

INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

4/15

ST.IiE

Na rysunku 3 widzimy kolejną tabelę przestawną, opartą na tej samej bazie danych. Używa ona pola listy rozwijanej
Filtr raportu dla pozycji Klient (w wierszu 1.). W tym przypadku wyświetlane są jedynie dane tych klientów, którzy już
wcześniej korzystali z usług banku (z pola listy rozwijanej można również wybrad opcję Nowy lub Wszyscy). Warto
zwrócid uwagę na zmianę kierunku wyświetlania danych w tabeli. W przypadku tej tabeli przestawnej oddziały
pojawiają się jako etykiety kolumn, natomiast typy kont jako etykiety wierszy. Dokonana zmiana, która zajęła około 5
sekund, jest kolejnym przykładem elastyczności tabeli przestawnej.

4 Dane odpowiednie dla tabeli przestawnej

Tabela przestawna wymaga, aby dane miały postad prostokątnej bazy danych. Bazę danych przechowuje się
w zakresie arkusza (może to byd tabela lub po prostu zwykły zakres) lub w pliku zewnętrznym. W Excelu można
budowad tabele przestawne na podstawie każdej bazy danych, jednak nie zawsze przyniesie to pożądany efekt.

Ogólnie mówiąc, pola tabeli bazy danych mogą należed do jednego z dwóch typów:

Dane — pole zawiera wartości lub dane, które będą później zestawiane. W przypadku omawianego przykładu
polem takim jest pole Kwota.

Kategorie — pole takie zawiera opis danych. Przykładowe pola kategorii na rysunku 1 to pola: Data, Typ konta,
Otwarte przez, Oddział oraz Klient. Opisują one dane zawarte w polu Kwota.

W jednej tabeli bazy danych może się mieścid dowolna liczba pól danych i pól kategorii. Tabele przestawne z reguły
służą do zestawienia jednego lub większej ilości pól danych. Wartości w polach kategorii umieszczane są w tabeli
w postaci wierszy, kolumn lub filtrów. Istnieją wyjątki od tej zasady. Tabele przestawne mogą się okazad przydatne
także wtedy, gdy baza danych nie zawiera pól danych w postaci liczbowej.

5 Terminologia tabel przestawnych

Jeśli chce się tworzyd tabele przestawne, trzeba najpierw zapoznad się ze słownictwem, które jest ściśle związane
z tym zagadnieniem. Poniższy rysunek pomoże lepiej zrozumied poszczególne pojęcia.

Etykiety kolumn — są to pola będące nagłówkami kolumn w tabeli przestawnej. Jeżeli w tych polach znajduje się
kilka pozycji (nagłówków podrzędnych), to każdej z nich przyporządkowana jest jedna kolumna. Na powyższym
rysunku polem kolumny jest Klient; na to pole składają się dwa nagłówki podrzędne: klient banku oraz nowy
klient. Pola kolumny mogą byd zagnieżdżane.

Suma koocowa — jest to wiersz bądź kolumna, który (która) zawiera sumę wartości wszystkich komórek
znajdujących się w danym wierszu lub kolumnie tabeli przestawnej. Możliwe jest obliczanie sum zbiorczych dla
wierszy, kolumn, dla wierszy i kolumn lub wcale. Tabela na rysunku wyświetla sumy zbiorcze zarówno dla
wierszy, jak i dla kolumn.

Grupa — jest to zbiór pozycji traktowanych jako całośd. Pozycje można grupowad ręcznie lub automatycznie (na
przykład grupuje się daty w miesiące). W tabeli na rysunku nie zdefiniowano żadnych grup.

Pozycja — jest to element pola, znajdujący się w nagłówku rzędu lub kolumny tabeli przestawnej. Na rysunku
pozycjami pola Klient są klient banku i nowy klient. Pole Oddział ma trzy pozycje: I, II i III, natomiast pole Typ
konta ma cztery pozycje: rachunek oszczędnościowy, ROR, fundusz emerytalny oraz lokata terminowa.

Odświeżanie — polega na ponownym przeliczeniu tabeli przestawnej po dokonaniu zmian w danych źródłowych.

Pole wiersza — jest to pole, będące nagłówkiem wiersza w tabeli przestawnej. Każda pozycja takiego pola
zajmuje jeden wiersz. Pola wiersza mogą byd zagnieżdżane. Na rysunku polami wiersza są Oddział oraz Typ
konta.

INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

5/15

ST.IiE

Dane źródłowe — są to dane, na podstawie których zbudowana jest tabela przestawna. Mogą pochodzid
z arkusza albo z zewnętrznej bazy danych.

Suma pośrednia — jest to wiersz lub kolumna, który (która) wyświetla sumę pośrednią zawartości
poszczególnych komórek w wierszach lub kolumnach tabeli przestawnej. Tabela na rysunku wyświetla sumy
pośrednie dla każdego oddziału.

Filtr tabeli — jest to pole, które pojawia się na stronach tabeli przestawnej — można je porównad z przekrojem
trójwymiarowej kostki. W tym samym czasie może byd wyświetlona tylko jedna pozycja filtru pola (albo
wszystkie pozycje). Na rysunku filtrem tabeli jest pole Otwarte przez, które aktualnie wyświetla pozycję
(Wszystkie). W poprzednich wersjach Excela filtr tabeli nosił nazwę pola strony.

Obszar wartości — są to komórki tabeli przestawnej, które zawierają dane zbiorcze. W Excelu można dokonad
podsumowania danych na kilka różnych sposobów, takich jak suma, średnia czy licznik.

Rysunek 4 Przykładowa tabela przestawna

6 Tworzenie tabeli przestawnej

W tym punkcie przedstawimy podstawowe kroki wymagane do utworzenia tabeli przestawnej na podstawie danych
kont bankowych. Jest to proces interaktywny. Zupełnie normalne jest eksperymentowanie z różnymi układami do
momentu znalezienia układu spełniającego oczekiwania.

6.1 Określanie danych

Jeśli dane znajdują się w zakresie arkusza, należy zaznaczyd dowolną jego komórkę i wybrad polecenie
Wstawianie/Tabele/Tabela przestawna. W efekcie zostanie otwarte okno dialogowe pokazane na rysunku 5.

Otwarte przez

(Wszystkie)

Suma z Kwota

Typ konta

Etykiety wierszy

Klient banku

Nowy

Suma koocowa

I

Fundusz emerytalny

548 198

337 559

885 757

Lokata terminowa

68 380

68 380

Rachunek oszczędnościowy

973 112

386 273

1 359 385

ROR

505 822

296 581

802 403

I Suma

2 095 512 1 020 413

3 115 925

II

Fundusz emerytalny

286 891

180 523

467 414

Lokata terminowa

125 374

9 000

134 374

Rachunek oszczędnościowy

845 522

292 389

1 137 911

ROR

208 375

184 141

392 516

II Suma

1 466 162

666 053

2 132 215

III

Fundusz emerytalny

291 728

44 360

336 088

Lokata terminowa

10 000

10 000

Rachunek oszczędnościowy

356 079

292 470

648 549

ROR

144 391

148 604

292 995

III Suma

802 198

485 434

1 287 632

Suma koocowa

4 363 872 2 171 900

6 535 772

INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

6/15

ST.IiE

Rysunek 5 Okno dialogowe tworzenia tabeli przestawnej

Excel próbuje zidentyfikowad zakres na podstawie położenia aktywnej komórki. Jeśli tworzy się tabelę przestawną
przy użyciu zewnętrznego źródła danych, trzeba wybrad odpowiednie polecenie, a następnie kliknąd przycisk Wybierz
połączenie, aby określid źródło danych.

WSKAZÓWKA. Jeśli tabelę przestawną tworzy się na bazie danych arkusza, dobrym pomysłem
będzie zdefiniowanie najpierw dla danych tabeli (za pomocą polecenia Wstawianie/
Tabele/Tabela). Gdy się tak postąpi, a następnie doda do tabeli nowe wiersze danych, Excel
odświeży tabelę przestawną bez konieczności ręcznego określania nowego zakresu danych.

6.2 Określanie lokalizacji tabeli przestawnej

Aby wskazad miejsce docelowe tabeli przestawnej, należy użyd dolnej sekcji okna dialogowego Tworzenie tabeli
przestawnej. Chod domyślne położenie to nowy arkusz, można wybrad dowolny zakres lub arkusz, łącznie z tym,
który przechowuje dane.

Po kliknięciu przycisku OK Excel utworzy pustą tabelę przestawną i wyświetli okienko Lista pól tabeli przestawnej
(Rysunek 6).

WSKAZÓWKA. Okienko Lista pól tabeli przestawnej standardowo jest przytwierdzone
z prawej strony okna Excela. Przeciągając pasek tytułu okienka, można je przenieśd
w dowolne miejsce. Jeśli kliknie się poza obszarem tabeli przestawnej, okienko zostanie
ukryte.

6.3 Określanie układu tabeli przestawnej

W dalszej kolejności należy określid rzeczywisty układ tabeli przestawnej. Można to zrobid, korzystając z jednej
z następujących metod:

Przeciągnięcie nazw pól do jednego z czterech paneli okienka Lista pól tabeli przestawnej.

Kliknięcie nazwy pola prawym przyciskiem myszy i wybranie z menu podręcznego lokalizacji pola.

UWAGA. W poprzednich wersjach Excela można było przeciągad pozycje listy pól
bezpośrednio do odpowiedniego obszaru tabeli przestawnej. Chod jest to nadal możliwe,
domyślnie odpowiednia funkcja jest wyłączona. Aby ją uaktywnid, należy wybrad polecenie

INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

7/15

ST.IiE

Narzędzia tabel przestawnych/Opcje/Tabela przestawna/Opcje, które otwiera okno
dialogowe Opcje tabeli przestawnej. Po uaktywnieniu karty Wyświetlanie należy umieścid
symbol zaznaczenia obok opcji Układ klasyczny tabeli przestawnej (umożliwia przeciąganie
pól w siatce).

Rysunek 6 Za pomocą okienka Lista pól tabeli można utworzyd tabelę przestawną

Poniższe kroki tworzą tabelę przestawną zaprezentowaną wcześniej w punkcie „Tabela przestawna na przykładzie".
W przykładzie pozycje widoczne w górnej części okienka Lista pól tabeli przestawnej zostaną przeciągnięte do paneli
znajdujących się w jego dolnej części. Oto wymagane kroki:

1. Przeciągnąd pole Kwota do panelu Wartości. Gdy to nastąpi, tabela przestawna wyświetli sumę wartości

kolumny Kwota.

2. Przeciągnąd pole Typ konta do panelu Etykiety wierszy. Tabela przestawna pokaże całkowitą wartośd dla

każdego typu konta.

3. Przeciągnąd pole Oddział do panelu Etykiety kolumn. Tabela przestawna pokaże wartośd dla każdego typu konta

z uwzględnieniem poszczególnych oddziałów (Rysunek 7).

6.4 Formatowanie tabeli przestawnej

Warto zauważyd, że tabele przestawne używają formatowania liczbowego Ogólne. W celu zmiany formatu
liczbowego należy zaznaczyd dowolną wartośd i wybrad polecenie Narzędzia tabel przestawnych/Opcje/Aktywne
pole/Ustawienia pola. W efekcie pojawi się okno dialogowe Ustawienia pola wartości. Należy kliknąd przycisk Format
liczby i zmienid format, np. na Liczbowe, bez miejsc dziesiętnych i z separatorem kolejnych tysięcy.

INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

8/15

ST.IiE

Rysunek 7 Po wykonaniu kilku prostych kroków tabela przestawna zaprezentuje zestawienie danych

Dla tabeli przestawnej można zastosowad dowolny z kilku wbudowanych stylów. Aby wybrad styl, należy zaznaczyd
dowolną komórkę w obrębie tabeli przestawnej i użyd polecenia Narzędzia tabel przestawnych/Projektowanie/Style
tabeli przestawnej.

Można również skorzystad z kontrolek grupy Narzędzia tabel przestawnych/Projektowanie/Układ pozwalających
kontrolowad różne elementy tabeli przestawnej. Przykładowo, w razie potrzeby można ukryd sumy koocowe.

Grupa Narzędzia tabel przestawnych/Opcje/Pokazywanie/ukrywanie zawiera dodatkowe opcje mające wpływ na
wygląd tabeli przestawnej. Przykładowo, za pomocą przycisku Nagłówki pól można wyświetlid nagłówki pól.

Jeszcze więcej opcji tabel przestawnych znajduje się w oknie dialogowym Opcje tabeli przestawnej (Rysunek 8). Aby
je otworzyd, należy zastosowad polecenie Narzędzia tabel przestawnych/Opcje/Tabela przestawna/Opcje. Można też
prawym przyciskiem myszy kliknąd dowolną komórkę tabeli przestawnej i z menu podręcznego wybrad pozycję Opcje
tabeli przestawnej.

Rysunek 8 Okno dialogowe "Opcje tabeli przestawnej"

INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

9/15

ST.IiE

6.5 Modyfikowanie tabeli przestawnej

Po utworzeniu tabeli przestawnej z łatwością można ją zmodyfikowad. Przykładowo, za pomocą okienka Lista pól
tabeli przestawnej można dodad informacje podsumowujące. Rysunek 9 prezentuje tabelę przestawną po
przeciągnięciu drugiego pola (Otwarte przez) do sekcji Etykiety wierszy okienka Lista pól tabeli przestawnej. Oto kilka
wskazówek dotyczących możliwych modyfikacji tabeli przestawnej:

W celu usunięcia pola z tabeli przestawnej należy je zaznaczyd w dolnej części okienka Lista pól tabeli
przestawnej, a następnie przeciągnąd poza jego obręb.

Jeśli sekcja posiada więcej niż jedno pole, przeciągając nazwy pól, można zmienid ich kolejnośd. Operacja wpływa
na wygląd tabeli przestawnej.

Aby tymczasowo usunąd pole z tabeli przestawnej, należy usunąd symbol zaznaczenia widoczny obok nazwy pola
w górnej części okienka Lista pól tabeli przestawnej. W efekcie tabela zostanie ponownie wyświetlona bez pola.
Gdy symbol zaznaczenia ponownie umieści się obok nazwy pola, pojawi się ono w sekcji, w której wcześniej się
znajdowało.

Jeżeli do sekcji Filtr raportu doda się pole, na liście rozwijanej pojawią się pozycje pola. Lista umożliwia
filtrowanie wyświetlanych danych przy użyciu jednej lub większej liczby pozycji. Rysunek 10 przedstawia
przykład. Do obszaru Filtr raportu przeciągnięto pole Data. Aktualnie raport pokazuje dane tylko dla jednej daty
wybranej z listy rozwijanej

Rysunek 9 Dwa pola pełnią rolę etykiet wierszy

Rysunek 10 Tabela przestawna jest filtrowana za pomocą daty

INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

10/15

ST.IiE

7 Obliczenia tabel przestawnych

Dane tabel przestawnych najczęściej są podsumowywane za pomocą operacji sumowania. Jednak dane można
przetworzyd za pomocą kilku innych metod podsumowywania. W obszarze wartości tabeli przestawnej należy
zaznaczyd dowolną komórkę, a następnie w celu otwarcia okna dialogowego Ustawienia pola wartości wybrad
polecenie Narzędzia tabel przestawnych/Opcje/Aktywne pole/Ustawienia pola. Okna ma dwie karty —
Podsumowanie według i Pokazywanie wartości jako (Rysunek 11).

Rysunek 11 Okno dialogowe – Ustawienia pola wartości

Przy użyciu karty Podsumowanie według należy wybrad inną funkcję podsumowującą. Do wyboru są takie funkcje jak
Suma, Licznik, Średnia, Maksimum, Minimum, Iloczyn, Licznik num., OdchStd, OdchStdc, Wariancja i Wariancja
populacji. Aby wyświetlid wartości w innej postaci, należy użyd listy rozwijanej znajdującej się w obrębie karty
Pokazywanie wartości jako. Dostępne opcje przedstawiono w poniższej tabeli.

Funkcja

Wynik

Różnica

Wyświetla dane jako wynik odejmowania od wartości Element podstawowy pola Pole
podstawowe.

% z

Pokazuje dane jako procent wartości Element podstawowy pola Pole podstawowe.

% różnicy

Wyświetla dane jako procentowo wyrażony wynik odejmowania od wartości Element
podstawowy pola Pole podstawowe

Suma bieżąca w

Wyświetla wartośd dla kolejnych elementów pola Pole podstawowe jako sumę bieżącą.

% wiersza

Wyświetla dane w każdym wierszu lub kategorii jako procent sumy wiersza lub kategorii.

% kolumny

Wyświetla wszystkie dane każdej kolumny lub serii jako procent sumy kolumny lub serii.

% sumy

Pokazuje dane jako procent sumy koocowej wszystkich danych lub punktów danych raportu.

Indeks

Oblicza dane za pomocą następującej zależności: ((wartośd komórki) x (suma całkowita sum
całkowitych)) / ((całkowita suma wiersza) x (całkowita suma kolumny))

8 Tworzenie wykresów przestawnych

Wykres przestawny jest graficzną reprezentacją podsumowania danych wyświetlanych w tabeli przestawnej. Wykres
przestawny zawsze bazuje na tabeli przestawnej. Chod Excel pozwala na jednoczesne utworzenie tabeli i wykresu
przestawnego, nie można wygenerowad wykresu, gdy nie istnieje tabela przestawna.

Osoby zaznajomione z tworzeniem wykresów Excela nie będą miały problemu z definiowaniem i dostosowywaniem
wykresów przestawnych. W przypadku wykresu przestawnego są dostępne wszystkie opcje wykresów oferowane
przez Excela.

INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

11/15

ST.IiE

Excel zapewnia dwie następujące metody tworzenia wykresu przestawnego:

Zaznaczenie dowolnej komórki istniejącej tabeli przestawnej i wybranie polecenia Narzędzia tabel
przestawnych/Opcje/Narzędzia/Wykres przestawny.

Zastosowanie polecenia Wstawianie/Tabele/Tabela przestawna/Wykres przestawny. Excel utworzy tabelę
i wykres przestawny.

8.1 Przykład wykresu przestawnego

Arkusz

obroty_wg_regionu.xlsx

przedstawia tabelę monitorującą dzienną sprzedaż według regionów (Rysunek

12). Pole Data zawiera daty dla całego roku (z wyłączeniem sobót i niedziel). W polu Region znajduje się nazwa
regionu (Wschód, Południe lub Zachód), natomiast pole Obroty zawiera wartośd sprzedaży.

Rysunek 12 Dane zostaną użyte do utworzenia wykresu przestawnego

Pierwszym krokiem jest utworzenie tabeli przestawnej podsumowującej dane. prezentuje taką tabelę. Pole Data
znajduje się w sekcji Etykiety wierszy. Daty kolejnych dni pogrupowano w miesiące. Pole Region umieszczono
w sekcji Etykiety kolumn, natomiast pole Obroty w sekcji Wartości. Chod tabela przestawna z pewnością jest prostsza
do interpretowania od nieprzetworzonych danych, trendy będzie łatwiej zauważyd na wykresie.

Rysunek 13 Tabela przestawna podsumowuje dane sprzedaży według regionu i miesiąca

Data

Region

Obroty

2006-01-02

Wschód

10 909

2006-01-03

Wschód

11 126

2006-01-04

Wschód

11 224

2006-01-05

Wschód

11 299

2006-01-06

Wschód

11 265

2006-01-09

Wschód

11 328

2006-01-10

Wschód

11 494

2006-01-11

Wschód

11 328

2006-01-12

Wschód

11 598

2006-01-13

Wschód

11 868

2006-01-16

Wschód

11 702

2006-01-17

Wschód

11 846

2006-01-18

Wschód

11 898

2006-01-19

Wschód

11 871

2006-01-20

Wschód

12 053

Suma z Obroty

Etykiety kolumn

Etykiety wierszy

Południe

Wschód Zachód

Suma koocowa

sty

171 897

259 416

99 833

531 146

lut

135 497

255 487

100 333

491 317

mar

147 425

296 958

107 884

552 267

kwi

131 401

248 956

110 628

490 985

maj

132 165

293 192

144 889

570 246

cze

122 156

281 641

133 153

536 950

lip

110 844

263 899

147 484

522 227

sie

107 935

283 917

176 325

568 177

wrz

101 233

252 049

181 518

534 800

paź

104 542

273 592

212 932

591 066

lis

98 041

292 585

232 032

622 658

gru

95 986

288 378

239 514

623 878

Suma koocowa

1 459 122 3 290 070 1 886 525

6 635 717

INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

12/15

ST.IiE

W celu utworzenia wykresu przestawnego należy zaznaczyd dowolną komórkę tabeli przestawnej i wybrad polecenie
Narzędzia tabel przestawnych/Opcje/Narzędzia/Wykres przestawny. Excel otworzy okno dialogowe Wstawianie
wykresu, w którym można wybrad typ wykresu. Na potrzeby przykładu należy użyd standardowego wykresu
liniowego i kliknąd przycisk OK. Excel wygeneruje wykres przestawny i wyświetli Okienko filtru wykresu przestawnego
(Rysunek 14).

Rysunek 14 Wykres przestawny korzysta z danych tabeli przestawnej

Wykres ułatwia zauważenie zwiększającej się wartości sprzedaży dla regionu zachodniego, spadającej sprzedaży dla
regionu południowego i stosunkowo niezmiennej sprzedaży dla regionu wschodniego.

Gdy zaznaczy się wykres przestawny, w obrębie narzędzia Wstążka pojawi się nowa karta Narzędzia wykresów
przestawnych. Polecenia karty są niemal takie same jak w przypadku standardowego wykresu Excela. W związku
z tym wykres przestawny można przetwarzad na dowolny żądany sposób.

Jeżeli zmodyfikuje się tabelę przestawną, zawartośd wykresu przestawnego zostanie automatycznie dostosowana,
aby uwzględniała nowe dane. Rysunek 15 przedstawia wykres przestawny po zmianie grupowania dat.

Rysunek 15 Jeśli zmodyfikuje się tabelę przestawną, zawartośd wykresu przestawnego też ulegnie zmianie

0

100 000

200 000

300 000

400 000

500 000

600 000

700 000

800 000

900 000

Kw.1

Kw.2

Kw.3

Kw.4

Południe

Wschód

Zachód

INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

13/15

ST.IiE

8.2 Dodatkowe informacje na temat wykresów przestawnych

Oto kilka uwag, o których trzeba wiedzied, gdy używa się wykresów przestawnych:

Tabela i wykres przestawny są łączone za pomocą dwukierunkowego łącza. Jeśli zmieni się strukturę tabeli
przestawnej bądź przefiltruje ją, zostanie też zmodyfikowany wykres przestawny. Działa to również w drugą
stronę.

Okienko filtru wykresu przestawnego wyświetlane po zaznaczeniu wykresu przestawnego zawiera identyczne
kontrolki, co nagłówki pól wykresu przestawnego. Kontrolki umożliwiają filtrowanie danych wyświetlanych
w obrębie tabeli i wykresu przestawnego. Jeśli wprowadzi się zmiany na wykresie za pomocą Okienka filtru
wykresu przestawnego, zostaną one również uwzględnione w tabeli przestawnej.

Jeżeli uzna się, że Okienko filtru wykresu przestawnego jest niepotrzebne lub irytujące, wystarczy kliknąd ikonę X
widoczną na pasku tytułu. W efekcie okienko zniknie. W celu przywrócenia okienka należy zastosowad polecenie
Narzędzia wykresów przestawnych/Analiza/Pokazywanie/ukrywanie/Filtr wykresu przestawnego.

Jeśli utworzono wykres przestawny i usunięto powiązaną z nim tabelę przestawną, wykres pozostanie. Formuły
serii wykresu zawierają oryginalne dane przechowywane w tablicach.

Domyślnie wykresy przestawne są osadzane w arkuszu z tabelą przestawną. Aby przenieśd wykres do innego
zwykłego arkusza lub arkusza wykresu, należy wybrad polecenie Narzędzia wykresów przestawnych/
Projektowanie/Lokalizacja/ Przenieś wykres.

Przy użyciu tabeli przestawnej można utworzyd wiele wykresów przestawnych. Wykresy można przetwarzad
i formatowad niezależnie. Jednak wszystkie takie wykresy wyświetlają identyczne dane.

Nie wolno zapomnied o motywach. W celu zmiany motywu skoroszytu należy zastosowad polecenie Układ
strony/Motywy/Motywy. Gdy się tak postąpi, tabela i wykres przestawny uwzględnią nowy motyw.

9 Zadania

Proszę zbudowad tabele przestawne odpowiadające na następujące pytania:

9.1 Jaka jest całkowita dzienna wartość nowego depozytu dla każdego oddziału?

Pole Oddział znajduje się w sekcji Etykiety kolumn.

Pole Data umieszczono w sekcji Etykiety wierszy.

Pole Kwota znajduje się w sekcji Wartości i jest podsumowywane za pomocą funkcji Suma.

Warto zauważyd, że tabela przestawna może też byd sortowana za pomocą dowolnej kolumny. Przykładowo,
kolumnę Suma koocowa można posortowad w kolejności malejącej, aby stwierdzid, który dzieo miesiąca ma
największą wartośd nowych funduszy. W celu wykonania operacji sortowania wystarczy prawym przyciskiem myszy
kliknąd dowolną komórkę kolumny i z menu podręcznego wybrad polecenie Sortuj.

9.2 Ile kont (z uwzględnieniem podziału na typy) otwarto w każdym oddziale?

Pole Typ konta znajduje się w sekcji Etykiety kolumn.

Pole Oddział umieszczono w sekcji Etykiety wierszy.

Pole Kwota znajduje się w sekcji Wartości i jest podsumowywane za pomocą funkcji Licznik.

Suma jest najczęściej wykorzystywaną funkcją podsumowującą w przypadku tabel przestawnych. W przykładzie
zmieniono funkcję podsumowującą na Licznik. W tym celu prawym przyciskiem myszy należy kliknąd dowolną
komórkę obszaru Wartości i z menu podręcznego wybrad pozycję Podsumowanie według/Licznik.

INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

14/15

ST.IiE

9.3 Jak się przedstawia rozkład środków pieniężnych między różnymi kontami?

Przykładowo, na 253 nowych kontach zgromadzono kwotę 5000 złotych lub mniejszą. Tabela przestawna jest
nietypowa, ponieważ używa tylko jednego pola, którym jest pole Kwota.

Pole Kwota (pogrupowane) znajduje się w sekcji Etykiety wierszy.

Pole Kwota umieszczono też w sekcji Wartości i podsumowano za pomocą funkcji Licznik.

Trzeci egzemplarz pola Kwota znajduje się w sekcji Wartości i jest podsumowywany przy użyciu funkcji % sumy.

Gdy na początku dodałem do sekcji Etykiety wierszy pole Kwota, tabela przestawna wyświetliła wiersz dla każdej
unikatowej wartości środków finansowych. Prawym przyciskiem myszy kliknąłem jedną z etykiet wierszy i z menu
wybrałem pozycję Grupuj. W dalszej kolejności użyłem okna dialogowego Grupowanie, aby utworzyd przedziały
środków finansowych zwiększające się o 5000.

Drugi egzemplarz pola Kwota znajdujący się w sekcji Wartości jest podsumowywany za pomocą funkcji Licznik.
Prawym przyciskiem myszy kliknąłem wartośd i z menu wybrałem pozycję Podsumuj dane według/Licznik.

Kolejny egzemplarz pola Kwota dodałem do sekcji Wartości i tak go skonfigurowałem, aby pokazywał wartośd
procentową. Przy użyciu karty Pokazywanie wartości jako okna dialogowego Ustawienia pola wartości zastosowałem
funkcję % sumy. W celu otwarcia tego okna prawym przyciskiem myszy należy kliknąd dowolną komórkę i wybrad
pozycję Podsumuj dane według/Więcej opcji.

9.4 Jakiego typu konta są najczęściej zakładane przez kasjera?

ROR jest najczęściej zakładanym typem konta.

Pole Typ konta znajduje się w sekcji Etykiety wierszy.

Pole Otwarte przez umieszczono w sekcji Filtr raportu.

Pole Kwota znajduje się w sekcji Wartości (podsumowana za pomocą funkcji Suma).

Drugi egzemplarz pola Kwota (podsumowywane przy użyciu funkcji % sumy) jest zlokalizowany w sekcji Wartości.

Tabela przestawna korzysta z pola Otwarte przez jako filtru raportu i pokazuje dane wyłącznie dla kasjera.
Posortowałem dane tak, aby największa wartośd znajdowała się na samej górze. Dodatkowo użyłem formatowania
warunkowego w celu wyświetlenia pasków danych dla wartości procentowych.

9.5 Jak wypada oddział I w porównaniu z dwoma pozostałymi oddziałami?

Tabela przestawna jest nietypowa, ponieważ używa tylko jednego pola, którym jest pole Kwota.

Pole Typ konta znajduje się w sekcji Etykiety wierszy.

Pole Oddział umieszczono w sekcji Etykiety kolumn.

Pole Kwota znajduje się w sekcji Wartości.

Pogrupowałem oddziały II i III, a następnie nadałem im nazwę Inne. Tabela przestawna wyświetla wartośd
z podziałem na typy kont. Dodatkowo utworzyłem wykres przestawny.

9.6 W którym oddziale kasjerzy zakładają nowym klientom najwięcej kont ROR?

W oddziale I kasjerzy założyli nowym klientom 23 rachunki ROR.

Pole Klient znajduje się w sekcji Filtr raportu.

Pole Otwarte przez umieszczono w sekcji Filtr raportu.

Pole Typ konta znajduje się w sekcji Filtr raportu.

Pole Oddział wstawiono w sekcji Etykiety wierszy.

INFORMATYKA EKONOMICZNA — laboratorium

arkusz kalkulacyjny: MS Excel

15/15

ST.IiE

Pole Kwota znajduje się w sekcji Wartości podsumowywanej za pomocą funkcji Licznik.

Tabela przestawna używa trzech filtrów raportu. Pole Klient jest filtrowane w celu pokazania wyłącznie pozycji Nowy.
Pole Otwarte przez jest filtrowane, aby wyświetlid jedynie pozycję Kasjer. Z kolei filtrowanie pola Typ konta ma na
celu pokazanie wyłącznie pozycji ROR.

9.7 Obroty według regionów

Proszę zbudowad wykres przestawny dla skoroszytu

obroty_wg_regionu.xlsx

zgodnie z opisem.