Darmowy fragment publikacji:
IDZ DO
IDZ DO
PRZYK£ADOWY ROZDZIA£
PRZYK£ADOWY ROZDZIA£
SPIS TREŒCI
SPIS TREŒCI
KATALOG KSI¥¯EK
KATALOG KSI¥¯EK
KATALOG ONLINE
KATALOG ONLINE
ZAMÓW DRUKOWANY KATALOG
ZAMÓW DRUKOWANY KATALOG
TWÓJ KOSZYK
TWÓJ KOSZYK
DODAJ DO KOSZYKA
DODAJ DO KOSZYKA
CENNIK I INFORMACJE
CENNIK I INFORMACJE
ZAMÓW INFORMACJE
ZAMÓW INFORMACJE
O NOWOŒCIACH
O NOWOŒCIACH
ZAMÓW CENNIK
ZAMÓW CENNIK
CZYTELNIA
CZYTELNIA
FRAGMENTY KSI¥¯EK ONLINE
FRAGMENTY KSI¥¯EK ONLINE
Wydawnictwo Helion
ul. Chopina 6
44-100 Gliwice
tel. (32)230-98-63
e-mail: helion@helion.pl
Excel. Profesjonalna analiza
i prezentacja danych
Autor: Jinjer Simon
T³umaczenie: £ukasz Suma
ISBN: 83-246-0174-0
Tytu³ orygina³u: Excel Data Analysis: Your visual
blueprint TM for analyzing data, charts, and PivotTables
Format: B5, stron: 304
Poznaj nowoczesne techniki analizy danych oraz tworzenia wykresów
(cid:127) Zastosuj formu³y i kwerendy
(cid:129) Stwórz tabele i wykresy przestawne
(cid:129) Zoptymalizuj pracê, pisz¹c w³asne makra w Visual Basicu
Arkusz kalkulacyjny Excel najczêœciej wykorzystywany jest do gromadzenia
i prezentowania danych. Rzadziej stosowany jest w charakterze narzêdzia
analitycznego, mimo i¿ ma do tego doskona³e predyspozycje. Odpowiednie po³¹czenie
formu³, kwerend i dodatkowych narzêdzi sprawia, ¿e Excel staje siê niezast¹pionym
narzêdziem ka¿dego, kto dokonuje analiz finansowych i numerycznych. Wykorzystanie
tych narzêdzi oraz mo¿liwoœci Excela w zakresie budowania wykresów umo¿liwia
przygotowanie precyzyjnych i doskonale wygl¹daj¹cych zestawieñ.
Dziêki ksi¹¿ce „Excel. Profesjonalna analiza i prezentacja danych” poznasz tajniki
przeprowadzania z³o¿onych analiz i tworzenia wykresów za pomoc¹ Excela.
Czytaj¹c j¹, nauczysz siê wykorzystywaæ formatowanie warunkowe, tworzyæ z³o¿one
formu³y i stosowaæ kwerendy do filtrowania danych w arkuszu. Dowiesz siê,
jak zautomatyzowaæ pracê za pomoc¹ makr i jak tworzyæ aplikacje w VBA
(Visual Basic for Applications), dziêki którym znacznie przyspieszysz najczêœciej
wykonywane zadania. Przeczytasz równie¿ o dodatkach do Excela, które mog¹ okazaæ
siê przydatne podczas pracy nad analizami.
(cid:129) Wprowadzanie danych
(cid:129) Metody zaznaczania komórek
(cid:129) Sortowanie i tworzenie niestandardowych porz¹dków sortowania
(cid:129) Tworzenie w³asnych filtrów wyœwietlania
(cid:129) Stosowanie formu³
(cid:129) Wykorzystanie narzêdzia Solver
(cid:129) Importowanie i przetwarzanie danych ze Ÿróde³ zewnêtrznych
(cid:129) Tworzenie wykresów
(cid:129) Korzystanie z tabel i wykresów przestawnych
(cid:129) Rejestrowanie i uruchamianie makr
(cid:129) Definiowanie w³asnych formularzy i okien dialogowych
(cid:129) Praca z dodatkiem Analiza Danych
Naucz siê efektywnie korzystaæ z Excela i VBA
Spis treści
Jak używać tej książki ...................................................... viii
1 Podstawowe informacje o programie Excel ......................... 2
Możliwości analizowania danych w Excelu ................................................................................... 2
Typy danych Excela ................................................................................................................................. 4
Wyszukiwanie wartości w arkuszu kalkulacyjnym ...................................................................... 6
Zaznaczanie zakresu komórek ............................................................................................................. 7
Nazywanie zakresu .................................................................................................................................. 8
Tworzenie zakresów etykiet ...............................................................................................................10
Modyfi kowanie nazwanych zakresów .............................................................................................12
Kopiowanie i wklejanie zakresu komórek ......................................................................................14
Tworzenie własnych formatów liczbowych .................................................................................. 16
Zastosowanie opcji Autoformatowanie w arkuszu kalkulacyjnym ........................................18
Tworzenie nazwanego stylu ................................................................................................................20
Tworzenie szablonu użytkownika .....................................................................................................22
Ochrona arkuszy ....................................................................................................................................24
2 Porządkowanie danych w arkuszu ...................................26
Tworzenie listy danych ........................................................................................................................26
Dodawanie serii wartości do listy .....................................................................................................28
Sortowanie listy danych .......................................................................................................................30
Tworzenie niestandardowych porządków sortowania ................................................................32
Konsolidowanie danych .......................................................................................................................36
Tworzenie konspektów ........................................................................................................................40
3 Przetwarzanie danych w arkuszu kalkulacyjnym ...............42
Stosowanie formatowania warunkowego ........................................................................................42
Podsumowywanie danych przy użyciu sum częściowych ........................................................46
Filtrowanie listy danych .......................................................................................................................48
Tworzenie niestandardowego fi ltru ..................................................................................................50
Tworzenie fi ltru zaawansowanego ....................................................................................................52
Tworzenie scenariuszy .........................................................................................................................54
Sprawdzanie poprawności danych ...................................................................................................56
iv
__wstepniaki.indd Sec1:iv
__wstepniaki.indd Sec1:iv
2006-02-14 20:53:30
2006-02-14 20:53:30
4 Tworzenie formuł .........................................................58
Sumowanie zawartości komórek za pomocą przycisku Autosumowanie .............................58
Dodawanie funkcji przy użyciu okna dialogowego Wstawianie funkcji ...............................60
Edytowanie formuł ................................................................................................................................62
Szacowanie formuły ..............................................................................................................................64
Używanie narzędzia Solver do otrzymywania określonych wartości argumentów ...........66
Tworzenie formuły warunkowej ........................................................................................................68
Korzystanie z tabeli danych do rozwiązywania formuł .............................................................70
Śledzenie błędów wykonywania formuł .........................................................................................72
Wyszukiwanie wartości w określonym wierszu lub kolumnie ................................................74
Określanie położenia wartości ...........................................................................................................76
Zwracanie wartości z komórki o określonym położeniu na liście danych ...........................78
Pozycjonowanie wartości na liście danych .....................................................................................80
Tworzenie tabeli spłaty kredytu ........................................................................................................82
5
Przetwarzanie informacji pochodzących
z zewnętrznych źródeł danych .......................................84
Łączenie danych pochodzących z innych programów Windows ...........................................84
Importowanie rozdzielanego pliku tekstowego .............................................................................86
Dzielenie kolumny danych na wiele kolumn .................................................................................90
Tworzenie kwerendy sieci Web .........................................................................................................92
Importowanie tabeli bazy danych .....................................................................................................94
Korzystanie z kwerend do selektywnego pobierania informacji
z zewnętrznych baz danych .............................................................................................................96
Obliczanie średniej z wartości przechowywanych w zakresie bazy danych .................... 102
Zapisywanie arkuszy kalkulacyjnych w formie stron internetowych ................................. 104
6 Sporządzanie wykresów danych .................................. 106
Podstawowe informacje o wykresach ............................................................................................ 106
Zmiana typu wykresu .........................................................................................................................110
Rozsuwanie wycinków wykresu kołowego ..................................................................................111
Tworzenie niestandardowych typów wykresów ........................................................................112
Dodawanie linii trendu do wykresu ...............................................................................................114
Dodawanie nowych wartości i zmienianie danych dla wykresu ...........................................116
Tworzenie kombinacji wykresów ...................................................................................................118
__wstepniaki.indd Sec1:v
__wstepniaki.indd Sec1:v
v
2006-02-14 20:53:30
2006-02-14 20:53:30
Spis treści
7 Korzystanie z raportów tabel przestawnych ...................120
Podstawowe informacje o tabelach przestawnych .................................................................... 120
Przegląd pól raportu tabeli przestawnej ....................................................................................... 121
Tworzenie raportu tabeli przestawnej z listy danych .............................................................. 122
Zmiana układu tabeli przestawnej ................................................................................................. 126
Filtrowanie pola ................................................................................................................................... 128
Zmiana typu obliczeń pola danych .................................................................................................130
Grupowanie elementów danych tabeli przestawnej ..................................................................132
Wprowadzanie dodatkowego obliczenia do obszaru danych ..................................................134
Dodawanie pola obliczeniowego ......................................................................................................136
Dodawanie elementu obliczeniowego ............................................................................................138
Pobieranie wartości z raportu tabeli przestawnej ...................................................................... 140
8 Tworzenie wykresów przestawnych ..............................142
Sporządzanie wykresu przestawnego z informacji pobranych z zewnętrznej bazy ....... 142
Zmiana układu wykresu przestawnego ........................................................................................ 146
Zmiana typu wykresu przestawnego ............................................................................................ 148
Filtrowanie pola wykresu przestawnego ...................................................................................... 150
Grupowanie elementów pola wykresu przestawnego .............................................................. 152
Dodawanie tabeli danych do wykresu przestawnego .............................................................. 154
9 Automatyzacja zadań przy użyciu makr ........................156
Wprowadzenie w zagadnienia makr .............................................................................................. 156
Rejestrowanie makra ...........................................................................................................................164
Uruchamianie makra ..........................................................................................................................166
Uruchamianie edytora Visual Basic ..............................................................................................168
Tworzenie makra za pomocą edytora Visual Basic .................................................................. 170
Modyfi kacja zarejestrowanego makra .......................................................................................... 172
Ustawianie opcji zabezpieczeń makra .......................................................................................... 174
Tworzenie własnej funkcji ............................................................................................................... 178
Deklarowanie zmiennej ......................................................................................................................180
Wykonywanie czynności określoną ilość razy ............................................................................182
Wywoływanie okna komunikatu .....................................................................................................184
Pobieranie przez makro danych od użytkownika .......................................................................186
Przypisywanie makra do polecenia menu ....................................................................................188
Uruchamianie makra przy otwieraniu skoroszytu ....................................................................190
Używanie funkcji Excela w kodzie makra ...................................................................................192
vi
__wstepniaki.indd Sec1:vi
__wstepniaki.indd Sec1:vi
2006-02-14 20:53:30
2006-02-14 20:53:30
10 Używanie formularzy i okien dialogowych ....................194
Wprowadzenie w zagadnienia formularzy ...................................................................................194
Pasek narzędzi .....................................................................................................................................195
Formularze .............................................................................................................................................195
Podstawowe informacje na temat okien dialogowych VBA ....................................................196
Dodawanie kontrolek formularza do arkusza kalkulacyjnego ................................................198
Określanie wartości dla kontrolki formularza ............................................................................ 200
Dostosowywanie działania kontrolek formularza za pomocą makr .................................... 202
Tworzenie własnego okna dialogowego ....................................................................................... 204
Wywoływanie własnego okna dialogowego w kodzie makra ................................................ 206
Pobieranie danych z niestandardowego okna dialogowego .................................................... 208
Sprawdzanie poprawności danych wprowadzanych do okna dialogowego ....................... 212
11 Używanie dodatku Analiza danych ..............................214
Instalowanie dodatków do programu Excel ................................................................................ 214
Używanie narzędzia Kreator sum warunkowych ......................................................................216
Przeprowadzanie analiz wartości wariancji dla listy danych (ANOVA) ............................ 220
Znajdowanie korelacji pomiędzy dwoma zestawami danych ................................................ 222
Określanie kowariancji pomiędzy dwoma zbiorami danych ................................................. 224
Tworzenie statystyk opisowych ...................................................................................................... 226
Porównywanie wariancji w dwóch grupach danych ................................................................ 228
Obliczanie średniej ruchomej .......................................................................................................... 230
Określanie rangi i percentylu .......................................................................................................... 232
Dodatek A Klawisze skrótów programu Excel .................................234
Dodatek B Krótki przegląd funkcji oferowanych
przez program Excel .................................................. 240
Dodatek C Krótki przegląd zdarzeń programu Excel i języka VBA .....256
Dodatek D Podstawowe informacje o formułach programu Excel ...... 270
Dodatek E Co znajduje się na płycie CD-ROM ..................................274
Skorowidz ................................................................................276
__wstepniaki.indd Sec1:vii
__wstepniaki.indd Sec1:vii
vii
2006-02-14 20:53:31
2006-02-14 20:53:31
Podstawowe informacje
o tabelach przestawnych
K orzystając z tabeli przestawnej, możesz w szybki
sposób tworzyć dynamiczne podsumowania list
informacji utworzonych w Excelu lub pobranych
z zewnętrznych baz danych. Raporty tabeli przestawnej
pozwalają Ci na opracowywanie tabelarycznych zesta-
wień czy też podsumowań rekordów danych na dwa lub
więcej sposobów przez łączenie wartości pochodzących
z różnych pól. Możesz w ten sposób analizować na przy-
kład kwoty zamówień wypracowanych przez sprzedaw-
ców w poszczególnych województwach, odszukiwać
całkowite wartości sprzedaży przypadające na każdego
Filtrowanie i grupowanie danych
z handlowców i szeregować ich według osiągniętych przez
nich wyników. Wszystkie te działania możesz przeprowa-
dzić dzięki prostym zmianom położenia pól w raporcie
i stosowaniu odpowiednich kryteriów fi ltrowania.
Tabele przestawne są dynamiczne, ponieważ możesz modyfi -
kować ich wygląd i treść dosłownie w mgnieniu oka oraz two-
rzyć połączenia z oryginalnymi danymi w celu odświeżenia
zawartości raportu po zmianie zawartości tabel źródłowych.
Do opracowania raportu tabeli przestawnej możesz użyć in-
formacji z dowolnej bazy danych, do której Excel jest w sta-
nie uzyskać dostęp. Więcej informacji o pobieraniu wartości
z zewnętrznych źródeł danych znajdziesz w rozdziale 5.
Tabele przestawne okazują się najbardziej użyteczne w przy-
padku konieczności analizowania długich list danych, ponie-
waż oferują możliwość szybkiego filtrowania widocznych in-
formacji w celu pokazania tylko niektórych wartości, a także
ich grupowania w celu ukrywania lub odsłaniania określo-
nych fragmentów informacji. Żeby skonstruować tabelę
przestawną, powinieneś przeciągnąć odpowiednie pola
— a w zasadzie etykiety danych — we właściwe miejsca ra-
portu. Pole spełnia tu tak naprawdę rolę filtra Twoich danych,
za pomocą którego możesz wybrać, jakie wartości mają
zostać wyświetlone w tabeli dla określonego pola. Każde
z pól posiada opcję Wszystkie pozwalającą Ci na pokazanie
wszystkich możliwych dla niego wartości. Jeśli na przykład
wybierzesz tę opcję w przypadku pola wierszy Miesiące, Ex-
cel wyświetli wszystkie wartości dla każdego miesiąca. Jeżeli
zechcesz pokazać jedynie określone wartości, będziesz je
mógł wybrać pojedynczo w polach kolumn i wierszy. Pole
strony umożliwia Ci wybranie tylko jednej wartości i w ta-
beli pojawią się wyłącznie wartości z rekordów spełniają-
cych kryterium filtrowania określone za jej pomocą. Więcej
informacji na temat filtrowania znajdziesz w podrozdziale
„Filtrowanie pola”.
Po skonstruowaniu swojej tabeli danych będziesz mógł sko-
rzystać z polecenia Grupuj... w celu połączenia elementów
w jedną wartość lub przedstawienia określonego związku
występującego pomiędzy danymi. Więcej informacji na
temat możliwości grupowania znajdziesz w podrozdziale
„Grupowanie elementów danych tabeli przestawnej”.
Ograniczenia tabel przestawnych
Aby Twoje tabele przestawne działały właściwie, powinieneś
stosować się do następujących reguł i wskazówek:
(cid:129) każdej z kolumn Twojej listy danych powinieneś nadać
etykietę, ponieważ program będzie z nich korzystał
do utworzenia nazw pól raportu tabeli przestawnej;
(cid:129) powinieneś usunąć ze swojej listy wszelkie sumy tworzo-
ne automatycznie, gdyż raport tabeli przestawnej utwo-
rzy komplet potrzebnych podsumowań w oparciu o pola
tabeli;
(cid:129) Excel utworzy tabelę przestawną z całej listy, a więc tak-
że ze wszystkich wartości znajdujących się w ukrytych
komórkach. Jeśli nie chcesz, by ukryte dane znalazły się
w raporcie, będziesz musiał przefiltrować swoją listę
i wynik tego filtrowania umieścić w oddzielnym arkuszu
kalkulacyjnym przy użyciu filtru zaawansowanego. Więcej
informacji na ten temat znajdziesz w rozdziale 3.;
(cid:129) program umożliwia Ci tworzenie raportów tabeli prze-
stawnej zawierających w sumie maksymalnie 8000 ele-
mentów. W obszarze strony możesz umieścić co najwyżej
256 pól, dokładnie tę samą liczbę pól może też przyjąć ob-
szar danych. Pojemność pozostałych obszarów jest ogra-
niczana jedynie przez limity całkowitych rozmiarów tabeli.
Typy danych tabel przestawnych
Niezależnie od rodzaju źródła danych będziesz musiał spowodować, by pola danych źródłowych należały do jednego
z następujących typów:
TYP
OPIS
Określa wartość tekstową opisującą dane. Z wartości takich jak kategoria skorzystasz do utworzenia etykiet
dla pól wierszy, kolumn i strony.
Określa wartość do podsumowania. Wartości danych są przeważnie liczbami, możesz też jednak zestawiać wartości tekstowe.
Kategoria
Dane
120
z__Excel.indb 120
z__Excel.indb 120
2006-02-14 20:56:36
2006-02-14 20:56:36
Przegląd pól raportu
tabeli przestawnej
P ola danych obecnych w Twoim arkuszu możesz
dodać do raportu tabeli przestawnej przez prze-
ciągnięcie ich do jednego z czterech obszarów:
obszaru pól stron, wierszy, kolumn oraz danych. Każde
z pól możesz umieścić w dowolnym obszarze, pamię-
tać jednak musisz, że wybranie miejsca wstawienia
poszczególnych pól będzie miało zasadniczy wpływ na
układ raportu.
Jednym z ciekawszych udogodnień oferowanych przez
raport tabeli przestawnej jest to, że Excel automatycz-
nie wstawia wiersz i kolumnę zawierającą podsumowa-
nia odpowiednich kolumn i wierszy tabeli.
A
Pola stron
Opcjonalny obszar,
który umożliwia
filtrowanie wartości
widocznych w raporcie.
Na stronie wyświetlone
zostaną tylko
wartości z rekordów
spełniających określone
w tym polu warunki.
B
Pola kolumn
Zawierają wartości
danych źródłowych
umieszczone
w kolumnach.
C
Elementy
Definiują podkategorie
dla wierszy lub kolumn
wyświetlających
odpowiednie wartości
w obszarze danych.
D
Podsumy
Wiersze lub kolumny
podsumowujące
wartości
z odpowiednich
komórek zawierających
dane szczegółowe.
A
B
C
R
o
z
d
z
i
a
ł
7
.
K
o
r
z
y
s
t
a
n
i
e
z
r
a
p
o
r
t
ó
w
t
a
b
e
l
p
r
z
e
s
t
a
w
n
y
c
h
H
G
D
E
E
Suma końcowa
Wiersz lub kolumna
zawierające sumy
obliczone
dla wszystkich
danych widocznych
w obszarze danych
raportu.
121
2006-02-14 20:56:36
2006-02-14 20:56:36
F
H
Pola danych
Określają
wartości danych
źródłowych, które
są podsumowywane
w obszarze danych
raportu tabeli
przestawnej.
G
Pola wierszy
Określają wartości
danych źródłowych,
które mają się znaleźć
w wierszach tabeli.
F
Obszar danych
Wyświetla informacje
o podsumowaniu
dokonanym przy
użyciu wybranej
funkcji zestawienia
lub formuły
użytkownika.
z__Excel.indb 121
z__Excel.indb 121
Tworzenie raportu tabeli
przestawnej z listy danych
D zięki możliwościom tworzenia tabelarycznych ze-
stawień podsumowujących informacje tabele prze-
stawne stanowią doskonałe narzędzie do szybkie-
go analizowania wartości pochodzących z opracowanych
w Excelu list danych lub zewnętrznych baz danych.
Korzystając z raportu tabeli przestawnej, możesz dyna-
micznie dostosowywać zestawienie i wybierać kolumny
listy, które mają być podsumowywane.
Raport tabeli przestawnej możesz opracować przy uży-
ciu narzędzia Kreator tabel i wykresów przestawnych, które
umożliwi Ci skorzystanie z listy danych znajdującej się
w bieżącym arkuszu kalkulacyjnym lub innym dostęp-
nym skoroszycie programu Excel. Jeśli przed uruchomie-
niem kreatora zaznaczysz komórkę wchodzącą w skład
listy, program automatycznie wstawi odpowiednie odwo-
łanie w pole Zakres widoczne w drugim kroku działania
narzędzia. Będziesz tu też oczywiście miał możliwość
ręcznego wprowadzenia odniesień do stosownych zakre-
sów defi niujących listę.
Jeżeli Twoja lista danych nie znajduje się w bieżącym
arkuszu, w polu zakresu będziesz musiał określić jej do-
kładne położenie, włączając w to informacje zarówno
o przechowującym ją arkuszu, jak i skoroszycie, w któ-
rym jest on umieszczony. Osiągniesz to, wpisując nazwy
skoroszytu i arkusza w znakach apostrofu, a nazwę pliku
skoroszytu umieszczając dodatkowo w nawiasach kwa-
dratowych. Chcąc na przykład skorzystać z danych za-
wartych na liście znajdującej się w arkuszu Arkusz1 sko-
roszytu ListaExcela.xls, będziesz musiał podać następujące
odwołanie do zakresu:
‚[ListaExcela.xls]Arkusz1‘$A$1:$G$100
Korzystając z kreatora, będziesz też musiał określić miej-
sce, w którym ma się znaleźć utworzony raport tabeli
przestawnej. Może to być bieżący arkusz kalkulacyjny
lub nowy arkusz dodany do bieżącego skoroszytu. Jeśli
zdecydujesz się na umieszczenie raportu w istniejącym
arkuszu, będziesz też musiał podać pierwszą komórkę dla
tabeli przestawnej. Powinieneś wybrać komórkę, w której
nie znajdują się żadne wartości. Jeśli jednak będzie ona
zawierała jakieś dane, Excel umożliwi Ci zastąpienie jej
wartościami z tabeli.
W podrozdziale tym przedstawiono sposób tworzenia
raportu tabeli przestawnej z listy danych opracowanej
w Excelu, możesz jednak skorzystać z tej samej proce-
dury przy sporządzaniu raportu z informacji pobranych
z zewnętrznej bazy danych. Więcej informacji na temat
tworzenia wykresów przestawnych i raportów tabeli
przestawnej z wykorzystaniem danych pochodzących
z zewnętrznych źródeł znajdziesz w rozdziale 8.
Tworzenie raportu tabeli przestawnej z listy danych
1
2
3
4
5
Zaznacz komórkę należącą
do listy danych.
Z menu programu wybierz
polecenie Dane/Raport
tabeli przestawnej i wykresu
przestawnego....
1
Na ekranie pojawi się okno
dialogowe Kreator tabel
i wykresów przestawnych.
Wybierz opcję Lista lub baza
danych programu Microsoft
Office Excel.
Wybierz opcję Tabela przestawna.
Kliknij przycisk Dalej .
3
4
2
5
122
z__Excel.indb 122
z__Excel.indb 122
2006-02-14 20:56:37
2006-02-14 20:56:37
7
6
8
9
R
o
z
d
z
i
a
ł
7
.
K
o
r
z
y
s
t
a
n
i
e
z
r
a
p
o
r
t
ó
w
t
a
b
e
l
p
r
z
e
s
t
a
w
n
y
c
h
Na ekranie pojawi się okno drugiego
kroku kreatora.
Sprawdź, czy w polu Zakres:
wprowadzone zostało odwołanie
do odpowiedniego zakresu komórek
określających położenie listy.
Jeśli zakres widoczny w tym polu
jest niewłaściwy, kliknij przycisk Zwiń
dialog i wybierz odpowiedni obszar
bezpośrednio w arkuszu.
Wybierz przycisk Dalej .
Na ekranie pojawi się okno
trzeciego kroku kreatora.
Aby umieścić raport w nowym
arkuszu kalkulacyjnym, wybierz
pole opcji Nowy arkusz.
• Możesz tu również skorzystać
z opcji Istniejący arkusz
i wybrać odpowiednią komórkę,
jeśli chcesz, by raport tabeli
przestawnej pojawił się
w bieżącym arkuszu.
Wybierz przycisk Układ....
6
7
8
9
Wskazówka
Korzystając z narzędzia Kreator tabel i wykresów przestawnych, możesz też tworzyć raporty używające
danych pochodzących z wielu różnych źródeł jednocześnie. W zależności od opcji, którą wybierzesz
w pierwszym kroku działania kreatora, w oknie drugiego kroku wyświetlone zostaną inne opcje. Więcej
informacji na ten temat znajdziesz w rozdziale 8. W zamieszczonej poniżej tabeli zebrano krótkie opisy
różnych typów źródeł danych wykorzystywanych przy tworzeniu tabel przestawnych:
TYP ŹRÓDŁA DANYCH OPIS
Lista programu Excel
Baza danych
Zakresy konsolidacji
Tabela przestawna
lub wykres przestawny
Jej rozmiar ograniczony jest do 65 635 rekordów lub wierszy danych. Każdy rekord może
mieć do 256 pól lub kolumn. Pierwszy wiersz listy danych musi zawierać etykiety kolumn
Po zaznaczeniu opcji Zewnętrzne źródło danych w pierwszym kroku działania kreatora,
w drugim kroku będziesz mógł wybrać zewnętrzną bazę danych, z której mają być pobrane
informacje wykorzystane później w charakterze pól danych. Więcej informacji na temat
możliwości importowania wartości z baz danych znajdziesz w rozdziale 5.
Opcję Wiele zakresów konsolidacji powinieneś wybrać, gdy zechcesz skorzystać
z danych zawartych w wielu arkuszach kalkulacyjnych. W drugim kroku kreatora będziesz
następnie mógł określić każdy z łączonych zakresów z osobna. Więcej informacji na temat
konsolidowania danych znajdziesz w rozdziale 2.
Z opcji Inny raport tabeli przestawnej lub wykresu przestawnego powinieneś skorzystać,
gdy będziesz chciał użyć tych samych danych, które zebrane są w istniejącej już tabeli
przestawnej lub są wyświetlane na wykresie przestawnym. Excel skorzysta z tych samych
danych źródłowych, nie tworząc dodatkowej ich kopii dla każdego z raportów, co pozwoli
na dużą oszczędność pamięci i stały dostęp do aktualnych danych
z__Excel.indb 123
z__Excel.indb 123
cdn.
123
2006-02-14 20:56:37
2006-02-14 20:56:37
Tworzenie raportu tabeli
przestawnej z listy danych (ciąg dalszy)
R aport tabeli przestawnej buduje się poprzez przecią-
ganie odpowiednich nazw kolumn i pól do stosow-
nych obszarów tabeli aż do uzyskania wymaganego
układu. Nazwy kolumn i pola pojawiają się na diagramie
w formie przycisków pól, których możesz używać w do-
wolnych kombinacjach. Jeśli określony przycisk pola nie
będzie widoczny w raporcie, odpowiadające mu wartości
danych nie zostaną wyświetlone w tabeli przestawnej.
Wszystkie pola, które przeciągniesz do obszaru pól wier-
szy tabeli przestawnej, pojawią się w raporcie w posta-
ci osobnych wierszy danych, wszystkie zaś pola, które
umieścisz w obszarze pól kolumn, zostaną wyświetlone
jako kolumny. Wszystkie pola, które przeciągniesz do ob-
szaru pól danych, zostaną podsumowane i wyświetlone
w głównej części tabeli. Gdy dane są wartościami nume-
rycznymi, do podsumowania danych standardowo uży-
wana jest funkcja Suma, zaś funkcja Licznik stosowana
jest w sytuacji, gdy są to dane tekstowe. Jeśli zechcesz
zmienić sposób podsumowywania wartości, będziesz
mógł wybrać inną funkcję z oferowanych przez program
Excel. Więcej informacji na temat możliwości modyfi -
kowania obliczeń w tabelach przestawnych znajdziesz
w podrozdziale „Zmiana typu obliczeń pola danych”.
W każdym z obszarów raportu tabeli przestawnej możesz
umieścić większą ilość pól danych. Jeśli na przykład do ob-
szaru pól wierszy zechcesz wstawić pole Miesiąc, a następ-
nie dodać tam pole Sprzedawca, Excel wyświetli wartości
sprzedaży w każdym miesiącu podzielone na podkategorie
każdego ze sprzedawców. Możesz tu też określić wartość,
która ma się znaleźć w odpowiednim polu każdego rekor-
du podsumowanego w tabeli, za pomocą przeciągnięcia
tego pola do obszaru pola strony i wybrania dla niego od-
powiedniej wartości. Jeśli na przykład umieścisz w polu
stron pole Region i zaznaczysz jeden z regionów, w tabeli
będą widoczne tylko dane pochodzące z tego właśnie wy-
branego regionu kraju. Więcej informacji na temat elemen-
tów raportu tabeli przestawnej znajdziesz w podrozdziale
„Podstawowe informacje o tabelach przestawnych”.
Po opracowaniu tabeli przestawnej będziesz mógł w pro-
sty i szybki sposób zmienić jej układ przez przeciąganie
poszczególnych elementów tabeli pomiędzy jej obszara-
mi. Więcej informacji na ten temat znajdziesz w podroz-
dziale „Zmiana układu tabeli przestawnej”.
Tworzenie raportu tabeli przestawnej z listy danych (ciąg dalszy)
Na ekranie pojawi się okno dialogowe Kreator
tabel i wykresów przestawnych — układ.
Wybierz jeden z przycisków pola widocznych
w prawej części okna.
Przeciągnij zaznaczony przycisk do obszaru
pól wierszy tabeli przestawnej.
10
11
11
10
12
13
Powtórz kroki 10. i 11. dla pól kolumn i danych.
Wybierz przycisk OK.
124
z__Excel.indb 124
z__Excel.indb 124
13
2006-02-14 20:56:38
2006-02-14 20:56:38
Na ekranie ponownie pojawi się
okno trzeciego kroku kreatora.
(cid:129) Możesz w nim wybrać
przycisk Opcje...
w celu określenia szeregu
ustawień związanych
z formatowaniem i opcjami
danych Twojej tabeli
przestawnej.
14
Kliknij przycisk Zakończ.
Excel utworzy zdefiniowaną
tabelę przestawną i umieści
ją w określonym przez
Ciebie miejscu.
W oknie programu pojawi
się pasek narzędzi Tabela
przestawna oferujący Ci szereg
możliwości zmiany wyglądu
i sposobu działania wstawionego
właśnie raportu.
14
R
o
z
d
z
i
a
ł
7
.
K
o
r
z
y
s
t
a
n
i
e
z
r
a
p
o
r
t
ó
w
t
a
b
e
l
p
r
z
e
s
t
a
w
n
y
c
h
Wskazówka
W czasie tworzenia i przeglądania tabeli przestawnej Excel wyświetla pasek narzędziowy Tabela prze-
stawna. W poniższej tabeli zamieszczono opisy najważniejszych przycisków, które się na nim znajdują:
PRZYCISK OPIS
Umożliwia wyświetlenie okna dialogowego Autoformatowanie. Więcej informacji na temat tego okna
znajdziesz w rozdziale 1.
Tworzy wykres przestawny przy użyciu domyślnych ustawień
Powoduje ukrycie elementów wchodzących w skład grupy. Więcej informacji na ten temat znajdziesz
w podrozdziale „Grupowanie elementów danych tabeli przestawnej”
Powoduje pokazanie wszystkich ukrytych elementów znajdujących się w raporcie tabeli przestawnej
Umożliwia zaktualizowanie zawartości tabeli przestawnej za pomocą bieżących wartości danych
Pozwala na ukrywanie i wyświetlanie okna Lista pól tabeli przestawnej
Z widocznego w lewym górnym rogu tabeli przestawnej pola stron możesz skorzystać w celu przefiltro-
wania wartości pochodzących z dużych list danych. Pole to umożliwia podzielenie raportu na oddzielne
strony i wyświetlanie jedynie tych danych, które spełniają określone kryteria, choć możesz też wybrać
tu opcję prezentowania wszystkich wartości. Więcej informacji na temat działania pola stron znajdziesz
w podrozdziale „Filtrowanie pola”.
Tabela przestawna umożliwia Ci bezproblemowe modyfikacje układu raportu, nie pozwala jednak na
dodawanie lub usuwanie jakichkolwiek wierszy czy kolumn ani na zmianę zawartości którejkolwiek
z komórek wchodzących w jego skład. Aby zmienić wartości widoczne w komórkach tabeli przestawnej,
będziesz musiał wprowadzić odpowiednie modyfikacje w danych źródłowych.
z__Excel.indb 125
z__Excel.indb 125
125
2006-02-14 20:56:38
2006-02-14 20:56:38
Zmiana układu
tabeli przestawnej
T abele przestawne sprawdzają się doskonale w przy-
padku przeprowadzania analiz danych, ponieważ
pozwalają Ci na szybkie dodawanie, przemieszcza-
nie i usuwanie pól z raportu, dzięki czemu możliwe jest
wprowadzanie natychmiastowych zmian wyświetlanych
w nim wartości. Chcąc zmienić układ tabeli, powinieneś
przeciągnąć wybrane pola do odpowiednich miejsc. Mo-
żesz w ten sposób na przykład przesunąć pola z obszaru
pól wierszy do obszaru pól kolumn, a nawet wstawić do
nich dodatkowe pola. Układ tabeli możesz również zmo-
dyfi kować poprzez wprowadzenie większej ilości nowych
pól do obszarów pól wierszy lub kolumn.
Dodawanie nowych pól to tabeli przestawnej umożliwia
Ci okno Lista pól tabeli przestawnej. W oknie tym widocz-
na jest lista wszystkich dostępnych pól tabeli, przy czym
pola używane przez nią obecnie wyświetlane są za pomo-
cą pogrubionej czcionki. Jeśli przeciągniesz nowe pole do
obszaru, w którym znajduje się już jakieś inne, wówczas
dodane pole spowoduje utworzenie podkategorii pola
funkcjonującego wcześniej w tabeli.
Zmiana układu tabeli przestawnej
W ramach tabeli przestawnej możesz przesuwać dowol-
ne pole z każdego obszaru do któregokolwiek innego.
Przemieszczenie pola nie powoduje zmian w warunkach
fi ltrowania odpowiadających mu danych. Jeśli zatem na
przykład przeglądałeś dane jedynie dla miesięcy Styczeń
i Luty, a następnie przesunąłeś pole Miesiąc z obszaru pól
wierszy do obszaru pól kolumn, w kolumnach nadal będą
widoczne tylko wartości zebrane w miesiącach Styczeń
i Luty. Więcej informacji na temat fi ltrowania tabeli prze-
stawnej znajdziesz w podrozdziale „Filtrowanie pola”.
W czasie, gdy przesuwasz lub dodajesz nowe pole do ra-
portu tabeli przestawnej, Excel wyświetla linię wska-
zującą miejsce, w którym wstawi przemieszczane dane.
Jeśli przeciągniesz pole do obszaru pól wierszy, program
wstawi nowe pole z lewej strony wyświetlanej linii.
Jeżeli zaś będziesz przeciągał je do obszaru pól kolumn,
dodane pole zostanie wstawione pod widoczną w tabeli
linią. Więcej informacji na temat elementów raportu ta-
beli przestawnej znajdziesz w podrozdziale „Podstawowe
informacje o tabelach przestawnych”.
Dodawanie pola
1
Wybierz określone pole z listy
widocznej w oknie Lista pól tabeli
przestawnej.
(cid:129) W razie potrzeby możesz
skorzystać z przycisku Wyświetl
listę pól do otwarcia okna Lista pól
tabeli przestawnej.
2
1
2
Przeciągnij pole do odpowiedniego
obszaru tabeli przestawnej.
(cid:129) Linia przerywana będzie
wskazywać miejsce, w którym
znajdzie się nowe pole.
(cid:129) Wybrane pole pojawi się
w odpowiednim obszarze
raportu tabeli przestawnej.
126
z__Excel.indb 126
z__Excel.indb 126
2006-02-14 20:56:39
2006-02-14 20:56:39
Przesuwanie pola
1
2
Wybierz określone pole
w raporcie tabeli przestawnej.
Przeciągnij wybrane pole
do nowego obszaru.
(cid:129) Przerywana linia będzie
wskazywać miejsce, w którym
znajdzie się nowe pole.
(cid:129) Tabela przestawna zostanie
zaktualizowana, a dane
związane z przesuniętym
polem wyświetlane będą w
odpowiednim obszarze raportu.
1
2
R
o
z
d
z
i
a
ł
7
.
K
o
r
z
y
s
t
a
n
i
e
z
r
a
p
o
r
t
ó
w
t
a
b
e
l
p
r
z
e
s
t
a
w
n
y
c
h
Wskazówka
Układ tabeli przestawnej możesz również zmienić,
korzystając z narzędzia Kreator tabel i wykresów
przestawnych. W tym celu powinieneś zaznaczyć
dowolną komórkę wchodzącą w skład raportu
i wybrać z menu polecenie Dane/Raport tabeli
przestawnej i wykresu przestawnego.... Spowoduje
to wyświetlenie na ekranie okna trzeciego kroku
kreatora. Więcej informacji na temat działania
narzędzia znajdziesz w podrozdziale „Tworzenie
raportu tabeli przestawnej z listy danych”.
Jeśli w obszarze pól kolumn Twojej tabeli prze-
stawnej znajduje się wiele pól danych, możesz
zmienić ich kolejność w raporcie. Najprostszym
sposobem zrobienia tego będzie oczywiście
przeciągnięcie wybranego pola w odpowiednie
miejsce, możesz też jednak skorzystać z poleceń
dostępnych na pasku narzędzi Tabela przestawna.
W tym celu zaznacz właściwą kolumnę i wybierz
polecenie Tabela przestawna/Kolejność, aby zo-
baczyć listę dostępnych opcji menu dotyczących
porządku pól w obszarze, a następnie wybierz
pozycję, na której ma się znaleźć zaznaczona ko-
lumna. Wybranie polecenia Przenieś na początek
spowoduje na przykład umieszczenie kolumny na
pierwszej pozycji, licząc od lewej strony raportu
tabeli przestawnej.
Z tabeli przestawnej możesz w każdej chwili usu-
nąć dowolne pole. Aby to zrobić, kliknij wybrane
pole i przeciągnij je z powrotem do obszaru okna
listy pól tabeli przestawnej. Podczas przeciągania
zaznaczonego pola kursor myszki zmieni się ze
strzałki w symbol przycisku, a czerwony znak X
będzie wskazywał, że pole jest usuwane z tabeli
przestawnej. Pamiętaj, że pole zostanie usunięte
jedynie z raportu, jednak cały czas będzie się znaj-
dowało na liście pól i w każdej chwili będziesz je
mógł swobodnie z powrotem dodać do tabeli
przestawnej.
z__Excel.indb 127
z__Excel.indb 127
127
2006-02-14 20:56:39
2006-02-14 20:56:39
Filtrowanie
pola
F iltrowanie każdego pola tabeli przestawnej, dzięki
któremu będziesz mógł przeglądać tylko dane po-
chodzące z rekordów spełniających określone kry-
teria, pozwoli Ci na zaoszczędzenie sporej ilości czasu
i wysiłku podczas analizowania danych. Standardowo po
dodaniu pola do raportu w jego wierszach lub kolumnach
wyświetlane są wszystkie elementy. Pola możesz jednak
fi ltrować w celu pokazania jedynie wybranych elemen-
tów czy też kombinacji elementów. Jeśli na przykład ob-
szar pól wierszy zawiera pole Miasto, masz możliwość
przefi ltrowania tego pola w taki sposób, aby widoczne
były jedynie wartości otrzymane dla określonych miast,
powiedzmy dla Krakowa i dla Warszawy. Pamiętaj, że
musisz wybrać przynajmniej jeden element dla każdego
pola. Jeśli spróbujesz zamknąć okienko listy, nie wybraw-
szy z niej żadnej pozycji, na ekranie pojawi się odpowied-
ni komunikat błędu.
Elementy dostępne dla każdego z pól tworzone są w opar-
ciu o dane, z których zbudowałeś raport tabeli przestawnej.
Jeśli opracowałeś go, korzystając z utworzonej w Exce-
Filtrowanie pola
1
Kliknij przycisk strzałki widoczny
z prawej strony pola, które chcesz
przefiltrować.
lu listy danych, elementami listy są etykiety pochodzące
z odpowiedniej kolumny listy. Jeśli nie możesz znaleźć ja-
kiegoś elementu wśród pozycji listy dla danego pola, powi-
nieneś sprawdzić oryginalne wartości swoich danych. Jeśli
utworzyłeś swój raport, używając danych z innego arkusza
kalkulacyjnego Excela, musisz przejrzeć ten arkusz w po-
szukiwaniu odpowiedniej etykiety. Jeżeli zaś skorzystałeś
z zewnętrznej bazy danych, będziesz mógł sprawdzić dane
po zaimportowaniu ich do nowego arkusza kalkulacyjnego.
Więcej informacji na temat możliwości pobierania wartości
z zewnętrznych źródeł danych znajdziesz w rozdziale 5.
Jeśli dodasz jakieś pole do znajdującego się w lewym gór-
nym rogu raportu tabeli przestawnej pola stron, również
tam będziesz mógł wybrać wyświetlanie wszystkich da-
nych dla elementów lub tylko dla elementów zaznaczo-
nych. Jeśli zatem wybierzesz tam jeden element, w rapor-
cie widoczne będą tylko wartości związane z tym jednym
elementem. Więcej informacji na temat obszaru pola
stron oraz pozostałych części raportu tabeli przestawnej
znajdziesz w podrozdziale „Podstawowe informacje o ta-
belach przestawnych”.
1
Na ekranie pojawi się okienko listy
wszystkich elementów dostępnych
dla wybranego pola.
Kliknij pole opcji (Pokaż wszystko)
w celu usunięcia zaznaczenia
ze wszystkich pól opcji
(znak (cid:59) zmieni się na (cid:133)).
2
2
128
z__Excel.indb 128
z__Excel.indb 128
2006-02-14 20:56:40
2006-02-14 20:56:40
Excel usunie znaki zaznaczenia ze
wszystkich pól widocznych na liście.
Wybierz osobno każdy element,
który ma się pojawić w raporcie
tabeli przestawnej (znak (cid:133) zmieni
się na (cid:59)).
Kliknij przycisk OK.
3
4
4
(cid:129) Tabela danych zostanie
automatycznie zaktualizowana
i będzie wyświetlać tylko dane
dla wybranych elementów
przefiltrowanego pola.
3
R
o
z
d
z
i
a
ł
7
.
K
o
r
z
y
s
t
a
n
i
e
z
r
a
p
o
r
t
ó
w
t
a
b
e
l
p
r
z
e
s
t
a
w
n
y
c
h
Wskazówka
Jeśli lista dostępnych elementów jest dłuższa niż
wymiary wyświetlającego ją okienka, po jego pra-
wej stronie pojawi się pasek przewijania umożli-
wiający dostęp do pozycji niewidocznych w da-
nej chwili. W celu wygodniejszego przeglądania
elementów możesz również powiększyć okien-
ko przez przeciągnięcie jego rogu lub którejś
z krawędzi.
Jeżeli zmienisz rozmiary okienka i klikniesz w nim
przycisk OK, Excel zapamięta jego ustawienia, co
ułatwi Ci kolejne wybieranie elementów po po-
nownym otwarciu okna listy.
Jeśli zdecydujesz się przefiltrować wiele pól, war-
tości widoczne w obszarze pól danych będą się
automatycznie aktualizować po każdej dokonanej
zmianie. W raporcie tabeli przestawnej widocz-
ne będą wówczas tylko rekordy, które spełniają
wszystkie określone w polach kryteria filtrowa-
nia. Jeżeli na przykład przefiltrujesz pole obszaru
wierszy w taki sposób, by wyświetlane były tyl-
ko wartości dla miesięcy Styczeń i Luty, zaś pole
obszaru kolumn tak, by pokazywane były dane
jedynie dla województw Śląskie i Podkarpackie,
w obszarze pól danych pojawią się wartości tyl-
ko z tych rekordów, które spełniają wszystkie
określone warunki. Zapamiętaj, że zastosowanie
filtrów nie powoduje usunięcia z raportu tabe-
li przestawnych jakichkolwiek danych, a jedynie
ukrycie niektórych wartości. Jeśli w okienkach list
pól wybierzesz inne elementy do wyświetlenia,
Twój raport tabeli przestawnej zostanie natych-
miast zaktualizowany.
z__Excel.indb 129
z__Excel.indb 129
129
2006-02-14 20:56:41
2006-02-14 20:56:41
Zmiana typu
obliczeń pola danych
W czasie, gdy tworzysz raport tabeli przestawnej,
Excel przeprowadza pewne standardowe obli-
czenia, podsumowując wartości, które pojawiają
się w obszarze pól danych. Jeśli na przykład pewna komór-
ka zawiera całkowitą wartość sprzedaży w województwie
małopolskim osiągniętą w styczniu, program określa tę
wartość poprzez zsumowanie wszystkich danych na temat
odpowiednich sprzedaży odnalezionych w oryginalnym
zbiorze informacji. Jeżeli będą to wartości sprzedaży czte-
rech pracowników działu handlowego, Excel podsumuje te
dane i wstawi je do wynikowej komórki.
Sposób tworzenia podsumowania możesz zmienić, rezyg-
nując ze standardowego działania sumowania na rzecz
jakiejś innej funkcji. Excel pozwala Ci tu wybrać jedną
z jedenastu różnych funkcji, wśród których znajdują się
takie operacje jak wyznaczanie ilości wystąpień danej
w zbiorze czy wyszukiwanie największej wartości. Jeśli na
przykład wybierzesz funkcję Licznik, w tabeli przestawnej
pojawią się wartości określające liczby wystąpień danych
liczbowych w oryginalnym zbiorze informacji. Jeśli zatem
w województwie małopolskim dokonano czterech sprzedaży
w określonym miesiącu, w odpowiedniej komórce pojawi
się liczba 4, nie zaś sumaryczna wartość sprzedaży.
Zmiana typu obliczeń pola danych
1
2
Kliknij prawym przyciskiem
myszki pole danych.
Na ekranie pojawi się menu
kontekstowe.
Wybierz pozycję
Ustawienia pola...
1
Zmiana funkcji podsumowującej jest natychmiast od-
zwierciedlana w wartościach widocznych w obszarze
pól danych raportu. Jeśli zatem wybraną funkcją zostanie
Licznik, a polem znajdującym się w obszarze danych bę-
dzie na przykład Wartość, nazwą widoczną w polu danych
będzie Licznik z Wartość.
Wybraną funkcję możesz również dostosowywać do swo-
ich potrzeb przez ustawienie odpowiednich opcji w oknie
dialogowym Pole tabeli przestawnej. Możesz na przykład za
jego pomocą określić, jakim procentem całkowitej sumy
jest suma wartości obliczona dla danej komórki, używając
w tym celu kombinacji funkcji z możliwością wyświetla-
nia informacji w postaci procentu określonej wartości.
Niektóre z niestandardowych typów obliczeń wymagają
podania dodatkowych informacji dotyczących sposobu
przeliczania danych. Będziesz zatem musiał zdefi niować
tu zarówno pole wykorzystywane w obliczeniach, jak
i wartość używaną w nich dla tego pola. Na przykład
w celu określenia różnicy pomiędzy wartościami sprze-
daży osiągniętymi w danymi miesiącu i miesiącu po-
przednim powinieneś w polu listy Pokaż dane jako wybrać
pozycję Różnica, w polu Pole podstawowe — opcję Miesiąc,
zaś w polu Element podstawowy — pozycję (poprzedni).
Na ekranie pojawi się okno dialogowe
Pole tabeli przestawnej.
Wybierz nową funkcję
podsumowującą.
Kliknij przycisk Opcje .
3
3
4
130
z__Excel.indb 130
z__Excel.indb 130
2
4
2006-02-14 20:56:41
2006-02-14 20:56:41
Okno dialogowe Pole tabeli przestawnej
zostanie powiększone.
Wybierz którąś z niestandardowych
opcji wyświetlania w polu Pokaż dane jako.
Kliknij przycisk OK.
5
6
Wartości widoczne w tabeli
danych zostaną automatycznie
zaktualizowane zgodnie z nowym
wybranym sposobem obliczania
danych raportu.
6
5
R
o
z
d
z
i
a
ł
7
.
K
o
r
z
y
s
t
a
n
i
e
z
r
a
p
o
r
t
ó
w
t
a
b
e
l
p
r
z
e
s
t
a
w
n
y
c
h
Wskazówka
Pole listy Pokaż dane jako udostępnia Ci szeroką gamę niestandardowych obliczeń związanych ze sposo-
bem prezentowania informacji. Zamieszczona poniżej tabela zawiera zestawienie wszystkich tych opcji
wraz z ich krótkimi opisami:
OPCJA
Różnica
z
różnicy
Suma bieżąca w Pokazuje bieżącą sumę w każdej komórce
OPIS
Oblicza różnicę pomiędzy dwoma komórkami tabeli przestawnej
Określa, jakim procentem wybranej liczby jest wartość obliczona dla danej komórki
Oblicza procentową różnicę pomiędzy wartościami dwóch komórek tabeli
wiersza
kolumny
sumy
Indeks
Określa, jakim procentem sumy danych znajdujących się w wierszu jest wartość obliczona
dla danej komórki
Określa, jakim procentem sumy danych przechowywanych w kolumnie jest wartość obliczona
dla danej komórki
Określa, jakim procentem sumy końcowej jest wartość obliczona dla danej komórki
Określa wartość indeksu dla obliczonej danej
z__Excel.indb 131
z__Excel.indb 131
131
2006-02-14 20:56:42
2006-02-14 20:56:42
Grupowanie elementów
danych tabeli przestawnej
P odczas przeprowadzania analiz danych z wyko-
rzystaniem tabeli przestawnej pomocna okaże się
z pewnością możliwość łączenia danych w jedną
wartość w celu pokazania specyfi cznych związków między
rekordami. Excel oferuje Ci polecenie grupowania, które
jest szczególnie użyteczne przy porządkowaniu i przedsta-
wianiu wyników analiz, zwłaszcza gdy masz do czynienia
z dużymi zestawami danych. Korzystając z niego, możesz
na przykład pogrupować miesiące, aby pokazać wyniki
sprzedaży osiągnięte w poszczególnych kwartałach. Gru-
powanie umożliwi Ci zwijanie i rozwijanie wierszy danych
szczegółowych w dowolnej chwili, dzięki czemu możliwe
będzie prezentowanie wartości sprzedaży w każdym mie-
siącu z osobna lub wyświetlenie jedynie informacji pod-
sumowujących dane grupy, a więc całkowitych wielkości
sprzedaży w kwartałach.
Gdy podzielisz pole na grupy, Excel doda nowe, odpowia-
dające im elementy do listy fi ltrowania pola. Po zgrupowa-
niu odpowiednich wierszy czy kolumn raportu będziesz
mógł usunąć wprowadzoną przez program nazwę grupy
i zastąpić ją własną, lepiej oddającą istotę rzeczy, a także
zastosować dla pola wszelkie dostępne formatowania, jakie
tylko sobie zażyczysz.
Grupowanie elementów danych tabeli przestawnej
Nie możesz, oczywiście, grupować elementów należą-
cych do różnych pól. Mając na przykład w obszarze
wierszy pola Miesiąc i Sprzedawca, nie będziesz zatem
mógł włączyć elementów pola Sprzedawca do grupy
utworzonej z wierszy pola Miesiąc, choć grupę samych
miesięcy będziesz, oczywiście, mógł utworzyć. Bę-
dziesz też w stanie połączyć w grupę elementy należące
do tego samego pola, ale ze sobą bezpośrednio niesąsia-
dujące w raporcie tabeli przestawnej. Więcej szczegó-
łów na temat zaznaczania nieprzylegających do siebie
zakresów arkusza znajdziesz w rozdziale 1., zaś infor-
macje o obszarach i polach raportu tabeli przestawnej
zamieszczono w podrozdziale „Podstawowe informacje
o tabelach przestawnych”.
Samo utworzenie grup nie spowoduje jeszcze automa-
tycznego obliczenia dla nich odpowiednich sum częś-
ciowych. Żeby je zobaczyć, musisz zwinąć wybrane
grupy — Excel połączy wówczas stosowne dane po-
chodzące z grupy wierszy lub kolumn i wstawi pola
sum. Sumy te możesz też uzyskać dzięki przefi ltrowa-
niu pola w taki sposób, by widoczna była jedynie wy-
brana grupa. Więcej informacji na temat możliwości
fi ltrowania tabel przestawnych znajdziesz w podroz-
dziale „Filtrowanie pola”.
Grupowanie elementów
1
Wybierz elementy, które mają
zostać zgrupowane w tabeli
przestawnej.
Wybierz z menu polecenie
Dane/Grupy i konspekt/Grupuj....
2
1
2
(cid:129) Excel utworzy grupę
(cid:129) Możesz zmienić nazwę
z zaznaczonych elementów.
nowej grupy przez wybranie
przechowującej ją komórki
i wpisanie nowej etykiety.
Grupa pojawi się w raporcie
tabeli przestawnej.
Możesz powtarzać kroki 1. i 2.
w celu utworzenia kolejnych grup.
132
z__Excel.indb 132
z__Excel.indb 132
2006-02-14 20:56:42
2006-02-14 20:56:42
Ukrywanie elementów grupy
i tworzenie sum częściowych
1
2
1.Wybierz grupę.
2.Wybierz z menu polecenie
Dane/Grupy i konspekt/Ukryj
szczegóły.
1
Excel ukryje wszystkie elementy
należące do wybranej grupy,
a w tabeli widoczne będą
jedynie wartości sum
wygenerowanych dla tej grupy.
2
R
o
z
d
z
i
a
ł
7
.
K
o
r
z
y
s
t
a
n
i
e
z
r
a
p
o
r
t
ó
w
t
a
b
e
l
p
r
z
e
s
t
a
w
n
y
c
h
Wskazówka
Jeśli w polu przechowywana jest seria dat, mo-
żesz skorzystać z okna dialogowego Grupowanie
w celu określenia sposobu grupowania elemen-
tów zbioru. Używając tego okna, możesz za po-
mocą jednego kroku utworzyć wiele grup danych.
Mając na przykład daty z pięciu kolejnych miesięcy,
będziesz mógł zgrupować dane według miesięcy,
a Excel oprócz utworzenia pięciu grup danych
nada im również automatycznie odpowiednie na-
zwy. Oznacza to, że wszystkie daty styczniowe zo-
staną umieszczone w jednej grupie, która otrzyma
nazwę Sty.
Każdą grupę istniejącą w tabeli przestawnej mo-
żesz w dowolnej chwili usunąć. Aby to zrobić,
powinieneś zaznaczyć grupę i wybrać z menu
polecenie Dane/Grupy
i konspekt/Rozgrupuj....
Po usunięciu grupa przestaje funkcjonować w ta-
beli przestawnej i — by móc z niej znów skorzy-
stać — będziesz musiał ponownie ją utworzyć.
Gdy zechcesz ukryć grupę, powinieneś kliknąć od-
powiednie pole i w jego menu filtrowania usunąć
zaznaczenie z pola opcji widocznego obok nazwy
grupy. Jeśli wybierzesz tu poszczególne elementy
wchodzące w skład grupy, wyświetlone w tabeli
przestawnej będą tylko one, nie zaś grupa, w któ-
rej się znajdują.
z__Excel.indb 133
z__Excel.indb 133
133
2006-02-14 20:56:43
2006-02-14 20:56:43
Wprowadzanie dodatkowego
obliczenia do obszaru danych
J ednoczesne przedstawianie w tabeli przestawnej wy-
ników różnego rodzaju obliczeń możliwe jest dzięki
wprowadzeniu dodatkowych pól do jej obszaru danych.
Być może będziesz chciał analizować dane dotyczące
sprzedaży, porównując zarówno same wartości sprze-
daży, jak i ich procentowy udział w całkowitym wyniku
fi nansowym fi rmy. W tym celu możesz dodawać różne
pola do obszaru danych, lecz możesz również umieszczać
tam kopie pól już obecnych w raporcie i stosować dla nich
inne sposoby obliczania wartości podsumowania.
Excel automatycznie nadaje nazwy każdemu z pól obsza-
ru danych, tworząc je z połączenia nazwy pola i rodzaju
zastosowanego obliczenia. Jeśli na przykład dodasz do
obszaru danych pole Wartość, pierwsze jego wystąpienie
otrzyma nazwę Suma z Wartość, jeżeli zaś dodasz je po
raz drugi, wprowadzone pole otrzyma nazwę Suma z War-
tość2. Nazwa pola ma duże znaczenie, ponieważ dostarcza
Ci informacji umożliwiającej poprawne zinterpretowa-
nie wyników prezentowanych przez tabelę przestawną.
Z tego też powodu powinieneś starannie wybierać nazwy
wprowadzane w oknie dialogowym Pole tabeli przestawnej,
aby opis wyników przeprowadzanych przez Ciebie analiz
był jasny i czytelny dla każdego użytkownika tabeli.
Każde pole widoczne w obszarze danych używa pewne-
go rodzaju obliczeń podsumowujących, przy czym jako
standardowy typ obliczeń wykorzystywana jest tu funk-
cja Suma. Możesz jednak, oczywiście, zmienić sposób
podsumowywania danych, korzystając z okna dialogowe-
go Pole tabeli przestawnej i wybrać dowolną z dostępnych
w nim funkcji. Możesz również zdefi niować własne nie-
standardowe metody obliczeń. Więcej informacji na te-
mat możliwości dostosowywania obliczeń przeprowadza-
nych w obszarze pól danych znajdziesz w podrozdziale
„Zmiana typu obliczeń pola danych”.
Tabela przestawna oferuje Ci możliwość zmiany sposo-
bu obliczania wartości dla każdego z pól danych osobno.
Dzięki temu możesz na przykład w pierwszym polu su-
mować dane o sprzedaży, a w drugim określać procentowy
wkład podsumowanej wartości w stosunku do pozosta-
łych danych obliczonych w kolumnie. Więcej informacji
na ten temat znajdziesz w podrozdziale „Zmiana typu
obliczeń pola danych”.
Wprowadzanie dodatkowego obliczenia do obszaru danych
1
2
Z listy widocznej w oknie Lista pól tabeli
przestawnej wybierz pole do dodania.
(cid:129) Jeśli okno to nie jest widoczne,
możesz je wyświetlić, korzystając
z przycisku Wyświetl listę pól.
Przeciągnij wybrane pole do obszaru
danych raportu tabeli przestawnej
i zwolnij przycisk myszki.
(cid:129) Wybrane pole pojawi się jako drugi
wiersz w obszarze danych.
3
Kliknij nowe pole danych prawym
przyciskiem myszki i z menu
kontekstowego wybierz polecenie
Ustawienia pola....
134
z__Excel.indb 134
z__Excel.indb 134
2
1
3
2006-02-14 20:56:43
2006-02-14 20:56:43
Na ekranie pojawi się okno
dialogowe Pole tabeli przestawnej.
Wybierz nową funkcję
podsumowującą i określ sposób
wyświetlania wartości.
4
Uwaga: więcej informacji na ten temat
znajdziesz w podrozdziale
„Zmiana typu obliczeń pola
danych”.
5
6
Wprowadź nazwę
dla tworzonego pola danych.
Kliknij przycisk OK.
5
4
4
(cid:129) Tabela przestawna zostanie
zaktualizowana w taki
sposób, by wyświetlać wyniki
wprowadzonych obliczeń.
6
R
o
z
d
z
i
a
ł
7
.
K
o
r
z
y
s
t
a
n
i
e
z
r
a
p
o
r
t
ó
w
t
a
b
e
l
p
r
z
e
s
t
a
w
n
y
c
h
Wskazówka
Gdy wprowadzisz do obszaru danych dwa lub
większą ilość pól, Excel utworzy osobne pola
danych w obszarze pól wierszy. Program umieści
nowe pole w osobnym wierszu znajdującym się
pod każdym wystąpieniem wartości pierwszego
pola. Excel umożliwia dodanie do 256 pól do
obszaru danych. Nowe pola możesz też wpro-
wadzać do obszarów pól wierszy i kolumn tabeli
przestawnej, dlatego przydatna z pewnością oka-
że się możliwość filtrowania pól w celu ukrycia
niektórych wyników analizy. Więcej informacji
na ten temat znajdziesz w podrozdziale „Filtro-
wanie pola”.
Opisane tu działanie umożliwia Ci opracowanie
jednej tabeli przestawnej zawierającej jednocześ-
nie wyniki wszystkich zdefiniowanych obliczeń
i przefiltrowanej w taki sposób, by widoczne
były tylko te dane, które są naprawdę istotne dla
użytkownika. Określony sprzedawca może na
przykład być zainteresowany przeglądaniem jedy-
nie całkowitych wartości sprzedaży osiągniętych
przez siebie w zestawieniu z sumarycznymi wyni-
kami wypracowanymi przez całą firmę.
Excel pozwala Ci na zmianę kolejności danych wy-
świetlanych w raporcie tabeli przestawnej, dzięki
oferowanym możliwościom sortowania. Żeby
z nich skorzystać, powinieneś wybrać z menu
polecenie Dane/Sortuj.... Używając okna dialogo-
wego Sortowanie, możesz uporządkować raport
tabeli przestawnej według wartości należących
do dowolnego pola. Więcej informacji na temat
sortowania znajdziesz w rozdziale 2.
z__Excel.indb 135
z__Excel.indb 135
135
2006-02-14 20:56:44
2006-02-14 20:56:44
Dodawanie pola
obliczeniowego
W ykonywane przez Ciebie analizy bardzo często
wymagają przeprowadzenia szczególnego ro-
dzaju obliczeń. Jeśli chcesz, by w Twojej tabeli
przestawnej znalazło się pole zawierające wyniki obli-
czeń opierających się na danych pochodzących z innych
pól lub też na jakichś niestandardowych wartościach, po-
winieneś opracować pole obliczeniowe. Pole obliczenio-
we to po prostu dodatkowy wiersz lub kolumna, w której
znajdują się utworzone przez Ciebie formuły. Możesz na
przykład sporządzić pole obliczeniowe w celu określenia
wartości prowizji płaconej dla każdego z rodzajów sprze-
daży. Twoja formuła będzie korzystała z danych zawar-
tych w polu Wartość, a jej działanie będzie polegało na
mnożeniu ich przez odpowiedni współczynnik, powinna
zatem mieć mniej więcej taką postać:
=-(Wartość * 0,10)
Tworząc formułę przy użyciu okna dialogowego Wsta-
wianie pola obliczeniowego, możesz korzystać ze wszystkich
oferowanych przez program Excel opcji dotyczących for-
muł standardowych. Możesz więc używać tu wszelkich
wbudowanych funkcji czy operatorów matematycznych,
takich jak *, + i (). Korzystając z wbudowanych funkcji,
musisz im dostarczać jako argumentów stałych wartości
Dodawanie pola obliczeniowego
1
2
3
4
Zaznacz dowolną komórkę należącą
do obszaru danych raportu tabeli
przestawnej.
Wybierz z menu polecenie
Wstaw/Pole obliczeniowe....
Na ekranie pojawi się okno dialogowe
Wstawianie pola obliczeniowego.
W polu tekstowym Nazwa:
wprowadź etykietę
dla tworzonego pola.
W polu tekstowym Formuła:
wpisz formułę, która ma obliczać
wartości dla pola.
Uwaga: więcej informacji na temat
tworzenia formuł znajdziesz
w rozdziale 4.
(cid:129) W formule możesz umieszczać
pola widoczne w dolnej części
okna. Możesz wpisać ich nazwy
ręcznie lub skorzystać z listy
i przycisku Wstaw pole.
5
Kliknij przycisk Dodaj.
136
z__Excel.indb 136
z__Excel.indb 136
lub danych pochodzących z dowolnych pól raportu tabeli
przestawnej, które są widoczne w polu listy Pola. Nie mo-
żesz używać tu bezpośrednich odwołań do komórek ani
zakresów nazwanych.
W jednym raporcie tabeli przestawnej możesz utworzyć
wiele pól obliczeniowych. Każde dodane
Pobierz darmowy fragment (pdf)
