Darmowy fragment publikacji:
Tytuł oryginału: Excel® Data Analysis: Your visual blueprint™ for analyzing data, charts, and PivotTables, 4th Edition
Tłumaczenie: Tomasz Walczak z wykorzystaniem fragmentów książki
„Microsoft Excel 2010 PL. Formuły i funkcje. Akademia Excela” w tłumaczeniu Ireneusza Jakóbika
ISBN: 978-83-283-0682-0
Copyright © 2013 by John Wiley Sons, Inc., Indianapolis, Indiana
All Rights Reserved. This translation published under license with the original publisher John Wiley Sons, Inc.
Translation copyright © 2015 by Helion S.A.
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form
or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise without either
the prior written permission of the Publisher.
The Teach Yourself Visually Brand trade dress is a trademark of John Wiley Sons, Inc.
in the United Stated and/or other countries. Used by permission.
Wiley, the Wiley logo, Visual, the Visual logo, Visual Blueprint, Read Less - Learn More and related trade dress are
trademarks or registered trademarks of John Wiley Sons, Inc. and/or its affiliates. Excel is a registered trademark
of Microsoft Corporation in the United States and/ or other countries. All other trademarks are the property of their
respective owners. John Wiley Sons, Inc. is not associated with any product or vendor mentioned in this book.
Wszelkie prawa zastrzeżone. Nieautoryzowane rozpowszechnianie całości lub fragmentu niniejszej publikacji
w jakiejkolwiek postaci jest zabronione. Wykonywanie kopii metodą kserograficzną, fotograficzną, a także kopiowanie
książki na nośniku filmowym, magnetycznym lub innym powoduje naruszenie praw autorskich niniejszej publikacji.
Wszystkie znaki występujące w tekście są zastrzeżonymi znakami firmowymi bądź towarowymi ich właścicieli.
Autor oraz Wydawnictwo HELION dołożyli wszelkich starań, by zawarte w tej książce informacje były kompletne
irzetelne. Nie biorą jednak żadnej odpowiedzialności ani za ich wykorzystanie, ani za związane z tym ewentualne
naruszenie praw patentowych lub autorskich. Autor oraz Wydawnictwo HELION nie ponoszą również żadnej
odpowiedzialności za ewentualne szkody wynikłe z wykorzystania informacji zawartych w książce.
Wydawnictwo HELION
ul. Kościuszki 1c, 44-100 GLIWICE
tel. 32 231 22 19, 32 230 98 63
e-mail: helion@helion.pl
WWW: http://helion.pl (księgarnia internetowa, katalog książek)
Pliki z przykładami omawianymi w książce można znaleźć pod adresem:
ftp://ftp.helion.pl/przyklady/excnie.zip
Drogi Czytelniku!
Jeżeli chcesz ocenić tę książkę, zajrzyj pod adres
http://helion.pl/user/opinie/excnie
Możesz tam wpisać swoje uwagi, spostrzeżenia, recenzję.
Printed in Poland.
• Kup książkę
• Poleć książkę
• Oceń książkę
• Księgarnia internetowa
• Lubię to! » Nasza społeczność
1. Tworzenie formuł na potrzeby analizy danych ................... 2
Wprowadzenie do analizy danych ....................................................................................................... 2
Wprowadzenie do formuł ....................................................................................................................... 4
Rodzaje formuł .......................................................................................................................................... 6
Tworzenie formuł...................................................................................................................................... 8
Dodawanie nazw zakresów do formuł .............................................................................................10
Wskazywanie w formułach zakresów z innych arkuszy .............................................................12
Przenoszenie lub kopiowanie formuł ................................................................................................14
Stosowanie bezwzględnych odwołań do komórek ........................................................................ 16
Tworzenie formuł tablicowych ...........................................................................................................18
Uruchamianie obliczeń iteracyjnych .................................................................................................20
2. Rozwiązywanie problemów z formułami ..........................22
Wartości błędów w Excelu ...................................................................................................................22
Wyświetlanie formuł zamiast wyników ..........................................................................................24
Używanie okna czujki do śledzenia wartości komórki ................................................................26
Przechodzenie przez formułę krok po kroku ................................................................................28
Wyświetlanie tekstu zamiast wartości błędów ..............................................................................30
Sprawdzanie błędów w formułach w arkuszu ...............................................................................32
Dokonywanie inspekcji formuły w celu zlokalizowania błędów ..............................................34
3. Wzbogacanie formuł za pomocą funkcji ............................36
Funkcje Excela .........................................................................................................................................36
Typy funkcji ..............................................................................................................................................38
Dodawanie funkcji do formuły ...........................................................................................................40
Dodawanie liczb z wiersza lub kolumny ........................................................................................42
Tworzenie formuły z autosumowaniem ...........................................................................................44
Zaokrąglanie wartości ............................................................................................................................46
Tworzenie formuł warunkowych ........................................................................................................48
Warunkowe obliczanie sumy ................................................................................................................50
Warunkowe zliczanie elementów ........................................................................................................51
Obliczanie pierwiastka kwadratowego .............................................................................................52
Pobieranie numeru kolumny lub wiersza ........................................................................................53
vi
EXCNIE_ksiazka.indb 6
2015-05-14 11:25:24
Spis treściPoleć książkęKup książkęWyszukiwanie wartości ........................................................................................................................54
Określanie lokalizacji wartości ...........................................................................................................56
Zwracanie wartości komórki za pomocą funkcji INDEKS .......................................................57
Wykonywanie obliczeń na datach i czasie .......................................................................................58
4. Analizowanie danych finansowych .................................. 60
Obliczanie przyszłej wartości .............................................................................................................60
Obliczanie wartości bieżącej ...............................................................................................................62
Określanie wysokości rat pożyczki ....................................................................................................64
Obliczanie części kapitałowej i odsetkowej ....................................................................................66
Obliczanie dopuszczalnego oprocentowania ...................................................................................68
Określanie wewnętrznej stopy zwrotu .............................................................................................70
Obliczanie amortyzacji metodą liniową ...........................................................................................72
Obliczanie amortyzacji metodą równomiernie malejącego salda ............................................74
Obliczanie amortyzacji metodą DDB ..............................................................................................76
Obliczanie amortyzacji metodą sumy cyfr rocznych ...................................................................78
5. Analizowanie danych statystycznych .............................. 80
Obliczanie średniej .................................................................................................................................80
Warunkowe obliczanie średniej ...........................................................................................................81
Wyznaczanie mediany lub wartości modalnej ...............................................................................82
Wyznaczanie pozycji .............................................................................................................................84
Określanie n-tej największej lub najmniejszej wartości .............................................................86
Tworzenie rozkładów częstości dla przedziałów ..........................................................................88
Obliczanie wariancji i odchylenia standardowego ........................................................................90
Obliczanie korelacji ................................................................................................................................92
6. Tworzenie tabel na potrzeby analizy danych .....................94
Tabele ..........................................................................................................................................................94
Wprowadzenie do funkcjonalności tabel .........................................................................................95
Przekształcanie zakresu w tabelę .......................................................................................................96
Zaznaczanie danych w tabeli ...............................................................................................................98
Wstawianie wiersza do tabeli ........................................................................................................... 100
Wstawianie kolumny do tabeli ......................................................................................................... 101
Usuwanie wiersza tabeli .................................................................................................................... 102
EXCNIE_ksiazka.indb 7
vii
2015-05-14 11:25:24
Poleć książkęKup książkęUsuwanie kolumny tabeli .................................................................................................................. 103
Wyznaczanie sum częściowych dla kolumn ................................................................................ 104
7. Sortowanie i filtrowanie danych ................................... 106
Proste sortowanie lub filtrowanie .................................................................................................... 106
Sortowanie wielopoziomowe ............................................................................................................ 108
Sortowanie niestandardowe ...............................................................................................................110
Sortowanie według koloru komórki, koloru czcionki lub ikony komórki .............................111
Stosowanie filtrów szybkich przy złożonym sortowaniu .........................................................112
Wprowadzanie kryteriów wyszukiwania rekordów ...................................................................114
Tworzenie filtrów zaawansowanych ................................................................................................116
Wyświetlanie unikatowych rekordów w wynikach filtrowania ..............................................118
Zliczanie przefiltrowanych rekordów ............................................................................................ 120
8. Techniki analizowania danych .......................................122
Wyróżnianie komórek spełniających określone kryteria ......................................................... 122
Wyróżnianie największej lub najmniejszej wartości z przedziału ....................................... 124
Wyświetlanie powtarzających się wartości .................................................................................. 126
Wyświetlanie komórek o wartościach powyżej lub poniżej średniej ................................... 127
Analizowanie wartości komórek za pomocą pasków danych ................................................. 128
Analizowanie wartości komórek za pomocą skali kolorów.......................................................130
Analizowanie wartości komórek za pomocą zestawów ikon ....................................................132
Tworzenie niestandardowych reguł formatowania warunkowego .........................................134
Wyróżnianie komórek na podstawie formuły ...............................................................................136
Modyfikowanie reguły formatowania warunkowego ..................................................................138
Usuwanie formatowania warunkowego z zakresu ...................................................................... 140
Usuwanie formatowania warunkowego z arkusza ...................................................................... 141
Tworzenie reguł sprawdzania poprawności danych ................................................................. 142
Podsumowywanie danych za pomocą sum częściowych ......................................................... 144
Grupowanie powiązanych danych .................................................................................................. 146
Usuwanie powtarzających się wartości z zakresu lub tabeli................................................... 148
Konsolidacja danych z różnych arkuszy ....................................................................................... 150
viii
EXCNIE_ksiazka.indb 8
2015-05-14 11:25:25
Spis treściPoleć książkęKup książkę9. Narzędzia do analizowania danych ................................154
Tworzenie tabeli danych .................................................................................................................... 154
Konfigurowanie tabeli danych z dwoma wartościami wejściowymi .................................... 156
Pomijanie tabel danych przy obliczeniach w skoroszycie ....................................................... 158
Analizowanie danych z wykorzystaniem funkcji szukania wyniku ......................................160
Analizowanie danych za pomocą scenariuszy ..............................................................................162
Dodatek Solver .......................................................................................................................................166
Wczytywanie dodatku Solver ............................................................................................................168
Optymalizowanie wyniku za pomocą Solvera............................................................................. 170
Dodawanie warunków ograniczających dla Solvera .................................................................. 172
Zapisywanie rozwiązania jako scenariusza .................................................................................. 174
10. Śledzenie trendów i dokonywanie prognoz ......................176
Kreślenie linii trendu .......................................................................................................................... 176
Obliczanie wartości najlepszego dopasowania ............................................................................ 178
Nanoszenie prognozowanych wartości na wykres ......................................................................180
Przedłużanie trendu liniowego..........................................................................................................182
Obliczanie prognozowanych wartości trendu liniowego ...........................................................184
Kreślenie linii trendu wykładniczego .............................................................................................186
Obliczanie wartości w trendzie wykładniczym ...........................................................................188
Kreślenie linii trendu logarytmicznego ..........................................................................................190
Kreślenie linii trendu potęgowego ...................................................................................................192
Kreślenie linii trendu wielomianowego ..........................................................................................194
11. Korzystanie z dodatku Analysis ToolPak ........................196
Wczytywanie dodatku Analysis ToolPak .......................................................................................196
Obliczanie średniej ruchomej ............................................................................................................198
Porównywanie wariancji .................................................................................................................... 200
Obliczanie korelacji ............................................................................................................................. 202
Przeprowadzanie analiz regresji ...................................................................................................... 204
Określanie rangi i percentyla ............................................................................................................ 206
Obliczanie statystyk opisowych ...................................................................................................... 208
Generowanie liczb losowych ............................................................................................................ 210
Tworzenie rozkładu częstości .......................................................................................................... 212
EXCNIE_ksiazka.indb 9
ix
2015-05-14 11:25:25
Poleć książkęKup książkę12. Analizowanie danych za pomocą tabel przestawnych ........214
Wprowadzenie do tabel przestawnych .......................................................................................... 214
Przegląd możliwości tabel przestawnych ..................................................................................... 215
Tworzenie tabeli przestawnej na podstawie tabeli lub zakresu ..............................................216
Tworzenie tabeli przestawnej na podstawie danych zewnętrznych ......................................218
Odświeżanie danych w tabeli przestawnej .................................................................................. 222
Dodawanie wielu pól do obszaru wierszy lub kolumn ............................................................. 224
Dodawanie wielu pól do obszaru danych ..................................................................................... 226
Przenoszenie pola do innego obszaru ............................................................................................ 228
Grupowanie wartości w tabelach przestawnych ......................................................................... 230
Modyfikowanie podsumowań w tabeli przestawnej .................................................................. 232
Obliczenia niestandardowe ............................................................................................................... 234
Dodawanie niestandardowego pola obliczeniowego .................................................................. 236
Dodawanie niestandardowego elementu obliczeniowego ........................................................ 238
13. Wizualizowanie danych za pomocą wykresów ................ 240
Elementy wykresów ............................................................................................................................ 240
Typy wykresów ..................................................................................................................................... 241
Tworzenie wykresu ............................................................................................................................. 242
Wyświetlanie tabeli danych .............................................................................................................. 244
Zmienianie układu i stylu wykresu ................................................................................................ 245
Wybieranie wykresu innego typu ................................................................................................... 246
Dodawanie wykresu przebiegu w czasie do komórki ............................................................... 248
14. Importowanie danych do Excela ................................... 250
Dane zewnętrzne .................................................................................................................................. 250
Importowanie informacji ze źródła danych ................................................................................. 252
Importowanie danych z tabeli Accessa .......................................................................................... 254
Importowanie danych z tabeli edytora Word ............................................................................... 256
Importowanie danych z pliku tekstowego ..................................................................................... 258
Importowanie danych ze stron WWW .......................................................................................... 262
Importowanie danych z pliku XML ............................................................................................... 264
Odświeżanie zaimportowanych danych ........................................................................................ 266
Rozdzielanie tekstu z komórek na kolumny ................................................................................ 268
x
EXCNIE_ksiazka.indb 10
2015-05-14 11:25:25
Spis treściPoleć książkęKup książkę15. Kwerendy źródeł danych ............................................ 270
Wprowadzenie do programu Microsoft Query ........................................................................... 270
Definiowanie źródła danych ............................................................................................................. 272
Uruchamianie programu Microsoft Query .................................................................................. 276
Przegląd okna Microsoft Query ...................................................................................................... 277
Dodawanie tabel do kwerend ........................................................................................................... 278
Dodawanie pól do kwerend ............................................................................................................... 280
Filtrowanie rekordów za pomocą kryteriów kwerendy............................................................. 282
Sortowanie rekordów kwerendy ...................................................................................................... 284
Zwracanie wyników kwerend .......................................................................................................... 286
16. Używanie języka VBA do analizowania danych ................288
Rejestrowanie makr ............................................................................................................................. 288
Otwieranie edytora kodu VBA ........................................................................................................ 290
Model obiektowy Excela .................................................................................................................... 292
Dodawanie makra do modułu .......................................................................................................... 294
Uruchamianie makra .......................................................................................................................... 296
Przypisywanie klawisza skrótu do makr....................................................................................... 298
Przypisywanie makra do paska szybkiego dostępu ................................................................... 300
Przypisywanie makr do wstążki ...................................................................................................... 302
Ustawianie poziomu bezpieczeństwa makr ................................................................................. 304
Cyfrowe podpisywanie makr Excela .............................................................................................. 306
Dodatek A. Używanie skrótów klawiaturowych Excela .......... 308
Skorowidz ......................................................................314
EXCNIE_ksiazka.indb 1
1
2015-05-14 11:25:25
Poleć książkęKup książkęWprowadzenie
do tabel przestawnych
T abele i zewnętrzne bazy danych mogą zawierać ty-
siące rekordów. Analizowanie takich ilości danych
bez odpowiednich narzędzi może być prawdziwym
koszmarem. Właśnie w takich sytuacjach będzie pomoc-
ny Excel ze swoim potężnym narzędziem do analizy
danych o nazwie tabela przestawna. Umożliwia ona pod-
sumowanie setek rekordów w zwięzłym, tabelarycznym
formacie, a następnie manipulowanie układem tabeli
Grupowanie
(przestawianie jej) w celu uzyskania spojrzeń na dane z in-
nej perspektywy.
Tabele przestawne pomagają analizować duże ilości da-
nych za pomocą trzech operacji: grupowania danych we-
dług kategorii, podsumowywania danych z wykorzysta-
niem obliczeń i filtrowania danych w celu wyświetlenia
tylko potrzebnych rekordów.
Tabele przestawne są przydatnym narzędziem do analizowania danych po części dlatego, że automatycznie grupują duże
zbiory danych w mniejsze, łatwiejsze w użyciu kategorie. Załóżmy, że masz źródło danych z polem Region, w którym
każda komórka zawiera jedną z czterech wartości: Wschód, Zachód, Północ i Południe. Pierwotne dane mogą
obejmować tysiące rekordów, jeśli jednak tworzysz tabelę przestawną z polem Region, w wynikowej tabeli mogą wystę-
pować tylko cztery wiersze — po jednym dla każdej z czterech unikatowych wartości tego pola.
Ponadto można tworzyć własne sposoby grupowania po utworzeniu tabeli przestawnej. Jeśli dane obejmują pole Kraj,
możesz w tabeli przestawnej pogrupować wszystkie rekordy o tej samej wartości tego pola. Potem można dodatkowo po-
grupować elementy z poszczególnych krajów według kontynentów: Ameryka Północna, Ameryka Południowa, Europa itd.
Podsumowywanie
Oprócz grupowania danych na podstawie unikatowych wartości jednego lub kilku pól Excel wyświetla ponadto podsumo-
wania dla poszczególnych grup. Domyślnie obliczana jest suma (Excel dodaje do siebie wszystkie wartości z danego pola).
Na przykład jeśli w danych występują pola Region i Sprzedaż, w tabeli przestawnej można pogrupować wartości we-
dług regionów i wyświetlić sumaryczny poziom sprzedaży dla każdego regionu. Excel udostępnia też inne podsumowania:
liczbę elementów, średnią, maksimum, minimum i odchylenie standardowe.
Jeszcze większe możliwości dają podsumowania dla grup z podziałem na podgrupy. Załóżmy, że w danych o sprzedaży znaj-
duje się też pole Produkt. Można wtedy wyświetlić w tabeli przestawnej łączny poziom sprzedaży dla każdego produktu
z podziałem na regiony.
Filtrowanie
Tabele przestawne umożliwiają też wyświetlanie podzbiorów danych. Na przykład przy domyślnym pogrupowaniu tabela
przestawna pokazuje wszystkie unikatowe wartości z grupującego pola. Możesz tak jednak zmodyfikować tabelę, aby ukryć
niepotrzebne wartości. Każda tabela przestawna udostępnia też filtr raportów, który można zastosować do całej tabeli.
Załóżmy, że w danych na temat sprzedaży znajduje się pole Klient. Jeśli wykorzystasz je w filtrze raportu, możesz prze-
filtrować raport i wyświetlić wyniki dla tylko jednego klienta.
214
EXCNIE_ksiazka.indb 214
2015-05-14 11:26:32
Poleć książkęKup książkęPrzegląd możliwości
tabel przestawnych
W arto poznać tabele przestawne, ponieważ mają
one wiele zalet. Na przykład tworzenie ich i za-
rządzanie nimi jest proste, a ponadto niezwykle
szybko przeprowadzają rozbudowane i złożone oblicze-
nia. Możesz też szybko zaktualizować tabelę przestawną
i uwzględnić nowe dane. Ponieważ takie tabele są dyna-
miczne, można łatwo przenosić, filtrować i dodawać kom-
ponenty. Tabele przestawne są w pełni konfigurowalne,
Wprowadzenie do elementów tabel przestawnych
co pozwala opracować każdy raport w pożądany sposób.
Dostępna jest też większość opcji formatowania używa-
nych dla zwykłych zakresów Excela.
Pracę z tabelami przestawnymi można zacząć bardzo
szybko po zapoznaniu się z kilkoma podstawowymi za-
gadnieniami. Musisz zrozumieć elementy typowej tabe-
li przestawnej (zwłaszcza cztery z nich: wiersze, kolum-
ny, dane i filtry), do których dodawane są pola danych.
A Filtr
Wyświetla listę rozwijaną z unikatowymi wartościami
pola. Gdy wybierzesz wartość z tej listy, Excel przefiltruje
wyniki w tabeli przestawnej i wyświetli tylko te rekordy,
które pasują do zaznaczonej wartości.
B Obszar kolumn
Wyświetla w poziomie unikatowe wartości z wybranego
pola z danych.
E Etykiety wierszy
Identyfikują pola z obszaru wierszy. Etykiety wierszy moż-
na też zastosować do filtrowania wartości pól wyświetla-
nych w obszarze wierszy.
F Etykiety kolumn
Identyfikują pola z obszaru kolumn. Etykiety kolumny
można też zastosować do filtrowania wartości pól wy-
świetlanych w obszarze kolumn.
C Obszar wierszy
Wyświetla w pionie unikatowe wartości z wybranego pola
z danych.
G Nagłówek pola danych
Określa obliczenia (na przykład „suma”) i pole (na przy-
kład „wartość faktury”) używane w obszarze danych.
D Obszar danych
Wyświetla wyniki obliczeń, które Excel przeprowadził na
polu liczbowym z danych.
H Elementy pól
Obejmują unikatowe wartości pól dodane do danego
obszaru.
E
H
G
C
B
A
F
D
EXCNIE_ksiazka.indb 215
215
2015-05-14 11:26:32
Rozdział 12. Analizowanie danych za pomocą tabel przestawnychPoleć książkęKup książkęTworzenie tabeli przestawnej
na podstawie tabeli lub zakresu
J eśli analizowane dane znajdują się w zakresie lub
tabeli Excela, można zastosować polecenie Tabela
przestawna. Trzeba tylko wskazać lokalizację danych
źródłowych, a następnie określić, gdzie ma znaleźć się
wygenerowana tabela przestawna.
Excel tworzy pustą tabelę przestawną w nowym arkuszu
lub we wskazanym miejscu. Ponadto Excel wyświetla
panel Pola tabeli przestawnej obejmujący cztery obszary:
FILTRY, KOLUMNY, WIERSZE i WARTOŚCI. Aby za-
kończyć tworzenie tabeli przestawnej, zapełnij niektóre
lub wszystkie z tych obszarów polami danych.
Tworzenie tabeli przestawnej na podstawie tabeli lub zakresu
1 Kliknij komórkę w źródle danych
(może to być zakres lub tabela).
2 Otwórz zakładkę WSTAWIANIE.
3 Wybierz opcję Tabela przestawna.
2
3
1
4
A
5
Pojawi się okno dialogowe Tworzenie
tabeli przestawnej.
4 Zaznacz opcję Nowy arkusz (pole
zmieni się w
A Jeśli chcesz umieścić tabelę
).
przestawną w istniejącym arkuszu,
kliknij opcję Istniejący arkusz (pole
zmieni się w
), a następnie
w polu Lokalizacja wybierz arkusz
i komórkę, gdzie ma znaleźć się
tabela przestawna.
5 Kliknij przycisk OK.
216
EXCNIE_ksiazka.indb 216
2015-05-14 11:26:32
Poleć książkęKup książkęB Excel utworzy pustą tabelę
przestawną.
C Excel wyświetli panel Pola tabeli
przestawnej.
6 Kliknij i przeciągnij pole, a następnie
upuść je w obszarze WIERSZE.
B
D Excel doda unikatowe wartości
pola do obszaru wierszy tabeli
przestawnej.
C
6
7 Kliknij i przeciągnij pole liczbowe,
a następnie upuść je w obszarze
WARTOŚCI.
E Excel zsumuje wartości liczbowe
na podstawie wartości wierszy.
8 W razie potrzeby kliknij i przeciągnij
pola, a następnie upuść je w obszarach
KOLUMNY i FILTRY.
Za każdym razem, gdy upuścisz pole
w jednym z obszarów, Excel zaktua-
lizuje tabelę przestawną i uwzględni
w niej nowe dane.
D
E
8
7
Dodatkowe informacje
Excel udostępnia kilka szybkich technik pomocnych przy tworzeniu tabel przestawnych. Gdy w panelu Pola tabeli
przestawnej zaznaczysz pole tekstowe lub pole z datami (pole
), zostanie ono dodane do
obszaru WIERSZE. Jeśli zaznaczysz pole liczbowe (pole
), Excel doda je do obszaru WARTOŚCI.
Możesz też kliknąć pole prawym przyciskiem myszy, a następnie kliknąć obszar, który chcesz wybrać. Obszar
FILTRY pozwala dodać do tabeli przestawnej pole filtra, które umożliwia wyświetlenie podzbioru danych na pod-
stawie wartości z tego pola. W tym celu kliknij skierowaną w dół strzałkę w polu z filtrem, wybierz z listy rozwijanej
element, który chcesz zastosować jako filtr, a następnie kliknij przycisk OK.
zmieni się w
zmieni się w
EXCNIE_ksiazka.indb 217
217
2015-05-14 11:26:32
Rozdział 12. Analizowanie danych za pomocą tabel przestawnychPoleć książkęKup książkęTworzenie tabeli przestawnej
na podstawie danych zewnętrznych
Z amiast tworzyć tabelę przestawną na podstawie da-
nych z arkusza Excela, można zbudować ją z wy-
korzystaniem zewnętrznego źródła danych. Dzięki
temu można budować raporty dla bardzo dużych zbiorów
danych i relacyjnych systemów bazodanowych.
Analizowane dane mogą być zapisane nie w zakresie albo
tabeli Excela, ale w systemie RDBMS, takim jak Mic-
rosoft Access lub SQL Server. Gdy używasz takich pro-
gramów, możesz skonfigurować tabelę, zapytanie lub inny
obiekt z danymi, z którymi zamierzasz pracować. Następ-
nie możesz utworzyć tabelę przestawną na podstawie ta-
kiego zewnętrznego źródła danych.
Tworzenie tabeli przestawnej na podstawie danych zewnętrznych
1 Wciśnij kombinację Alt+D, a następnie
wciśnij P.
Pojawi się okno dialogowe Kreator
tabel i wykresów przestawnych —
krok 1 z 3.
danych (pole
2 Zaznacz opcję Zewnętrzne źródło
).
3 Zaznacz opcję Tabela przestawna
zmieni się w
(pole
zmieni się w
4 Kliknij przycisk Dalej.
).
2
3
4
Pojawi się okno dialogowe Kreator
tabel i wykresów przestawnych —
krok 2 z 3.
5 Kliknij przycisk Pobierz dane.
5
218
EXCNIE_ksiazka.indb 218
2015-05-14 11:26:32
Poleć książkęKup książkęPojawi się okno dialogowe
Wybierz źródło danych.
6 Kliknij wybrany typ źródła danych.
7 Kliknij przycisk OK.
Pojawi się okno dialogowe
Wybieranie bazy danych.
8 Kliknij katalog zawierający potrzebną
bazę danych.
9 Kliknij wybraną bazę danych.
10 Kliknij przycisk OK.
9
Pojawi się okno dialogowe Kreator
kwerend — wybierz kolumny.
11 Kliknij tabelę lub kolumnę, którą
chcesz wykorzystać jako źródło
danych dla tabeli przestawnej.
11
12 Kliknij przycisk Dodaj.
A Pola wybranej tabeli pojawią się
na liście.
13 Kliknij przycisk Dalej.
7
10
6
8
12
A
13
Dodatkowe informacje
Aby utworzyć źródło danych, otwórz zakładkę DANE, wybierz opcję Pobieranie danych zewnętrznych, kliknij
Z innych źródeł, a następnie wybierz Z programu Microsoft Query. W oknie dialogowym Wybierz źródło danych
kliknij Nowe źródło danych. Usuń zaznaczenie przy polu Używaj Kreatora kwerend do tworzenia/edycji kwerend
(pole
), a następnie kliknij przycisk OK. W oknie dialogowym Utwórz nowe źródło danych
wprowadź nazwę źródła danych, wybierz sterownik bazy danych potrzebny dla danego źródła, a następnie kliknij
przycisk OK.
zmieni się w
EXCNIE_ksiazka.indb 219
cdn.
219
2015-05-14 11:26:32
Rozdział 12. Analizowanie danych za pomocą tabel przestawnychPoleć książkęKup książkęTworzenie tabeli przestawnej
na podstawie danych zewnętrznych (ciąg dalszy)
G dy tworzysz tabelę przestawną na podstawie da-
nych zewnętrznych, musisz wcześniej zdefiniować
odpowiednie źródło danych. Nie musisz bezpo-
średnio korzystać z programu Microsoft Query. Ponadto
gdy budujesz tabelę przestawną opartą na danych ze-
wnętrznych, możesz pominąć okna dialogowe Kreatora
kwerend związane z filtrowaniem i sortowaniem danych,
ponieważ w kontekście tworzenia tabeli przestawnej ope-
racje te zwykle nie są istotne.
Przy tworzeniu tabeli przestawnej na podstawie danych
zewnętrznych nie musisz najpierw importować tych da-
nych do Excela. Dane są wtedy dostępne tylko w nowej
tabeli przestawnej, a nie w skoroszycie.
Tworzenie tabeli przestawnej na podstawie danych zewnętrznych (ciąg dalszy)
Pojawi się okno dialogowe Kreator
kwerend — filtr danych.
14 Kliknij przycisk Dalej.
Pojawi się okno dialogowe Kreator
kwerend — kolejność sortowania.
15 Kliknij przycisk Dalej.
14
15
220
EXCNIE_ksiazka.indb 220
2015-05-14 11:26:32
Poleć książkęKup książkęPojawi się okno dialogowe Kreator kwerend
— koniec.
16 Zaznacz pole Zwróć dane do progra-
zmieni się
mu Microsoft Excel (pole
w
).
17 Kliknij przycisk Zakończ.
Excel wróci do okna dialogowego
Kreator tabel i wykresów przestaw-
nych — krok 2 z 3.
18 Kliknij przycisk Zakończ.
Excel utworzy pustą tabelę
przestawną.
A Pola dostępne w tabeli lub
kwerendzie wybranej w kroku 11.
pojawią się w panelu Pola tabeli
przestawnej.
19 Kliknij pola z panelu Pola tabeli
przestawnej i przeciągnij je do
odpowiednich obszarów.
B Excel wyświetli w tabeli
przestawnej podsumowanie
zewnętrznych danych.
16
B
18
17
A
19
Dodatkowe informacje
Najczęstszym problemem przy korzystaniu z zewnętrznych źródeł danych jest to, że użytkownik często nie ma
kontroli nad zewnętrznym plikiem. Gdy spróbujesz na przykład odświeżyć tabelę przestawną z zewnętrznymi
danymi, Excel może wyświetlić komunikat o błędzie. Jeśli podejrzewasz, że źródłem problemu jest zmiana danych
używanych do logowania się do bazy, kliknij przycisk OK, aby wyświetlić okno dialogowe Logowanie, i poproś
administratora bazy o nową nazwę użytkownika i nowe hasło.
Źródłem problemu może być też przeniesienie lub zmiana nazwy pliku bazy danych. W takiej sytuacji kliknij przy-
cisk OK w oknie z komunikatem błędu, a następnie wybierz opcję Baza danych w oknie dialogowym Logowanie.
Potem za pomocą okna dialogowego Wybierz bazę danych znajdź i wskaż plik z bazą.
EXCNIE_ksiazka.indb 221
221
2015-05-14 11:26:33
Rozdział 12. Analizowanie danych za pomocą tabel przestawnychPoleć książkęKup książkęExcel udostępnia dwie techniki odświeżania tabel prze-
stawnych: ręczną i automatyczną. Ręczne odświeżanie
zwykle przeprowadza się wtedy, gdy wiadomo, że źród-
łowe dane się zmieniły, lub w celu upewnienia się, że naj-
nowsze dane są uwzględnione w raporcie. Automatyczne
odświeżanie Excel przeprowadza za użytkownika.
3
A
2
2
Odświeżanie danych
w tabeli przestawnej
N iezależnie od tego, czy w tabeli przestawnej znajdu-
ją się wyniki finansowe, odpowiedzi do ankiety lub
informacje o zbiorach książek lub płyt DVD, uży-
wane dane prawdopodobnie nie są statyczne. Oznacza to,
że zmieniają się po pojawieniu się nowych wyników, prze-
prowadzeniu kolejnych ankiet lub dodaniu nowych pozycji
do zbiorów. Aby zapewnić aktualność analiz reprezento-
wanych w tabeli przestawnej, należy ją odświeżyć.
Odświeżanie danych w tabeli przestawnej
1
3
4
1
Ręczne odświeżanie danych
1 Kliknij dowolną komórkę w tabeli
przestawnej.
2 Otwórz zakładkę ANALIZA.
3 Wybierz opcję Odśwież.
Możesz też wcisnąć kombinację
Alt+F5.
A Aby zaktualizować wszystkie
tabele przestawne w skoroszycie,
kliknij skierowaną w dół strzałkę
pod przyciskiem Odśwież,
a następnie wybierz opcję
Odśwież wszystko.
W celu zaktualizowania wszystkich ta-
bel przestawnych możesz też wcisnąć
kombinację Ctrl+Alt+F5.
Excel zaktualizuje dane w tabeli
przestawnej.
Automatyczne odświeżanie
danych
1 Kliknij dowolną komórkę w tabeli
przestawnej.
2 Otwórz zakładkę ANALIZA.
3 Kliknij przycisk Tabela przestawna.
4 Wybierz opcję Opcje.
Uwaga: możesz też kliknąć dowolną
komórkę tabeli przestawnej
prawym przyciskiem myszy,
a następnie wybrać opcję
Opcje tabeli przestawnej.
222
EXCNIE_ksiazka.indb 222
2015-05-14 11:26:33
Poleć książkęKup książkęPojawi się okno dialogowe Opcje tabeli
przestawnej.
5 Otwórz zakładkę Dane.
6 Wybierz opcję Odśwież dane podczas
zmieni się
otwierania pliku (pole
w
).
7 Kliknij przycisk OK.
Excel zastosuje wybrane opcje
odświeżania.
6
5
7
Zastosuj to
Jeśli tabela przestawna jest oparta na danych zewnętrznych, możesz przygotować harmonogram pozwalający
automatycznie odświeżać tabelę w określonych odstępach czasu. Kliknij dowolną komórkę tabeli przestawnej,
otwórz zakładkę ANALIZA, kliknij skierowaną w dół strzałkę pod przyciskiem Odśwież, a następnie wybierz opcję
Właściwości połączenia z listy rozwijanej. Zaznacz pole wyboru Odśwież co (
), a następnie użyj
kontrolek do określenia w minutach odstępu między operacjami odświeżania.
Zauważ, że przy automatycznym odświeżaniu warto unikać zbyt częstego aktualizowania danych. W zależności
od lokalizacji danych i ich ilości proces odświeżania może zajmować dużo czasu i spowalniać wykonywanie innych
zadań.
zmieni się w
EXCNIE_ksiazka.indb 223
223
2015-05-14 11:26:33
Rozdział 12. Analizowanie danych za pomocą tabel przestawnychPoleć książkęKup książkęDodawanie wielu pól
do obszaru wierszy lub kolumn
D o każdego obszaru tabeli przestawnej możesz do-
dać wiele pól. Ten przydatny mechanizm pozwala
przeprowadzać dodatkowe analizy dzięki innemu
spojrzeniu na dane. Załóżmy, że analizujesz efekty kampa-
nii sprzedażowej, w ramach której przeprowadzono kilka
akcji promocyjnych z wykorzystaniem kilku technik re-
klamowych. Podstawowa tabela przestawna może pokazy-
wać poziom sprzedaży każdego produktu (pole wiersza)
dla poszczególnych technik reklamowych (pole kolumny).
Można też sprawdzić poziom sprzedaży każdego produk-
tu dla poszczególnych akcji promocyjnych. W tym celu
należy dodać pole promocji do obszaru wierszy.
Dodawanie wielu pól do obszaru wierszy lub kolumn
Dodawanie pola do obszaru
WIERSZE
1 Kliknij komórkę w tabeli przestawnej.
1
2 Kliknij pole wyboru przy polu
tekstowym lub polu z datą, które
chcesz dodać (pole
zmieni się
w
A Excel doda pole do obszaru
).
WIERSZE.
B Excel doda unikatowe wartości
wybranego pola do obszaru
wierszy w tabeli przestawnej.
B
2
A
224
EXCNIE_ksiazka.indb 224
2015-05-14 11:26:33
Poleć książkęKup książkęDodawanie pola do obszaru
WIERSZE lub KOLUMNY
1 Kliknij komórkę w tabeli przestawnej.
2 Na liście w panelu Pola tabeli
przestawnej kliknij pole, które chcesz
dodać, a następnie przeciągnij je do
obszaru WIERSZE lub KOLUMNY.
1
C Excel doda pole do obszaru
WIERSZE lub KOLUMNY.
D Excel doda unikatowe wartości
pola do obszaru wierszy lub
kolumn tabeli przestawnej.
D
D
2
C
Zastosuj to
Po dodaniu drugiego pola do obszaru wierszy lub kolumn można zmienić pozycje pól, aby zmodyfikować wygląd
tabeli przestawnej. Na liście Pola tabeli przestawnej w obszarze WIERSZE lub KOLUMNY kliknij wybrany przycisk
z polem i przenieś go nad lub pod inny przycisk.
W Excelu można umieścić w obszarze wierszy lub kolumn więcej niż dwa pola. W zależności od wymagań zwią-
zanych z przeprowadzaną analizą można wstawić w tych obszarach trzy, cztery pola, a nawet większą ich liczbę.
EXCNIE_ksiazka.indb 225
225
2015-05-14 11:26:33
Rozdział 12. Analizowanie danych za pomocą tabel przestawnychPoleć książkęKup książkęDodawanie wielu pól
do obszaru danych
E xcel umożliwia dodanie wielu pól do obszaru da-
nych tabeli przestawnej. Pozwala to wyświetlić jed-
nocześnie wiele podsumowań, co wzbogaca analizy.
Załóżmy, że analizujesz efekty kampanii sprzedażowej.
Prosta tabela przestawna może pokazywać sumę sprze-
danych sztuk. Ponadto może Cię interesować wartość
Dodawanie wielu pól do obszaru danych
Dodawanie pól do obszaru
danych za pomocą pola wyboru
1 Kliknij komórkę w tabeli przestawnej.
1
sprzedanych towarów. Aby ją uzyskać, można dodać do
obszaru danych pole z wartością sprzedaży. Aby umieś-
cić w obszarze danych wiele pól, należy zastosować panel
Pola tabeli przestawnej lub przeciągnąć pola do odpowied-
niego obszaru.
2 Zaznacz pole wyboru przy polu, które
chcesz dodać do obszaru danych.
A Excel doda reprezentujący pole
przycisk do obszaru WARTOŚCI.
B Excel doda pole do obszaru
danych tabeli przestawnej.
2
B
B
A
226
EXCNIE_ksiazka.indb 226
2015-05-14 11:26:33
Poleć książkęKup książkęDodawanie pól do obszaru
danych za pomocą przeciągania
1 Kliknij komórkę w tabeli przestawnej.
2 W panelu Pola tabeli przestawnej
kliknij pole, które chcesz dodać,
i przeciągnij je do obszaru WARTOŚCI.
1
C Excel doda pole do obszaru
danych tabeli przestawnej.
2
C
C
Dodatkowe informacje
Gdy dodajesz drugie pole do obszaru danych, Excel przenosi etykiety (na przykład Suma z Sztuk lub Suma
z Cena) do obszaru kolumn, co zwiększa czytelność tabeli. Dodatkowo w obszarze KOLUMNY w panelu Pola
tabeli przestawnej pojawia się pole Wartości. Dzięki niemu można przestawiać wartości w raporcie. W Excelu
w obszarze danych można umieścić więcej niż dwa pola. Możesz swobodnie dodać trzecie, czwarte i kolejne pola,
aby wzbogacić analizy.
EXCNIE_ksiazka.indb 227
227
2015-05-14 11:26:33
Rozdział 12. Analizowanie danych za pomocą tabel przestawnychPoleć książkęKup książkęPrzenoszenie pola
do innego obszaru
T abela przestawna to cenne narzędzie do analizy da-
nych, ponieważ potrafi podsumować setki, a nawet
tysiące rekordów i przedstawić je w formie zwię-
złego, zrozumiałego raportu. Jednak tabele przestawne
(w odróżnieniu od większości innych mechanizmów
analizowania danych w Excelu) nie są statycznym zbio-
rem komórek arkusza. Pola tabeli przestawnej można
przenosić między obszarami. To pozwala spojrzeć na
Przenoszenie pól do różnych obszarów
Przenoszenie pól między
obszarami wierszy i kolumn
1 Kliknij komórkę w tabeli przestawnej.
2 Kliknij i przeciągnij przycisk pola
z obszaru KOLUMNY do obszaru
WIERSZE.
A Excel wyświetli wartości z pola
w obszarze wierszy.
Możesz też przeciągnąć przycisk
pola z obszaru WIERSZE do obszaru
KOLUMNY.
1
A
dane z różnych perspektyw, co znacznie wzbogaca ana-
lizy. Przenoszenie pól w tabelach przestawnych to proces
przestawiania danych.
Najczęściej stosowany sposób przestawiania danych to
przenoszenie pól między obszarami wierszy i kolumn.
Możesz też jednak przenieść pole z obszaru wiersza lub
kolumny do obszaru filtrów.
2
228
EXCNIE_ksiazka.indb 228
2015-05-14 11:26:34
Poleć książkęKup książkęPrzenoszenie pól z obszaru
wierszy do obszaru filtrów
1 Kliknij komórkę w tabeli przestawnej.
2 Kliknij i przeciągnij przycisk pola
z obszaru WIERSZE do obszaru FILTRY.
1
A Excel przeniesie przycisk pola do
obszaru filtrów raportu.
B
Możesz też przeciągnąć przycisk pola
z obszaru KOLUMNY do obszaru
FILTRY.
2
Zastosuj to
Możesz przenieść dowolne pole z obszaru wierszy, kolumn i filtrów do obszaru danych tabeli przestawnej. Może
wydawać się to dziwne, ponieważ pola w obszarach wierszy, kolumn i filtrów prawie zawsze zawierają tekst,
a domyślne obliczenia dla obszaru danych to sumowanie. Jak można zsumować wartości tekstowe? Jest to oczy-
wiście niemożliwe. Zamiast tego w Excelu domyślnym podsumowaniem dla wartości tekstowych w tabelach
przestawnych jest zliczanie. Dlatego jeśli na przykład przeciągniesz pole Promocja do obszaru wartości, Excel
utworzy drugie pole danych o nazwie Liczba z Promocja.
EXCNIE_ksiazka.indb 229
229
2015-05-14 11:26:34
Rozdział 12. Analizowanie danych za pomocą tabel przestawnychPoleć książkęKup książkęGrupowanie wartości
w tabelach przestawnych
A by ułatwić sobie pracę z tabelami przestawnymi
o dużej liczbie wierszy lub kolumn, warto pogru-
pować te elementy. Można na przykład pogrupo-
wać miesiące w kwartały, co zmniejszy liczbę elementów
z 12 do 4. Podobnie w raporcie z dziesiątkami państw
można pogrupować je według kontynentów i zmniej-
Grupowanie wartości w tabelach przestawnych
1 Kliknij dowolny element pola
liczbowego, którego wartości chcesz
pogrupować.
1
szyć w ten sposób liczbę pozycji do czterech lub pięciu
(w zależności od analizowanych krajów). Jeśli w obszarze
wierszy lub kolumn występuje pole liczbowe, pojawiają
się czasem setki elementów — po jednym dla każdej war-
tości. Można wtedy ulepszyć raport dzięki utworzeniu
kilku przedziałów liczbowych.
2 Otwórz zakładkę ANALIZA.
3 Kliknij przycisk Grupowanie.
4 Wybierz opcję Grupuj pole.
2
3
4
230
EXCNIE_ksiazka.indb 230
2015-05-14 11:26:34
Poleć książkęKup książkęPojawi się okno dialogowe
Grupowanie.
5 Wprowadź początkową wartość
liczbową.
A Zaznacz te pola wyboru (pole
), aby Excel
pobrał minimalną i maksymalną
wartość pola i umieścił je w polach
tekstowych.
zmieni się w
A
8
5
6
7
6 Wprowadź końcową wartość
liczbową.
7 Podaj wielkość każdej grupy.
8 Kliknij przycisk OK.
B Excel pogrupuje wartości liczbowe.
B
Zastosuj to
Aby pogrupować daty i czas, najpierw kliknij dowolną komórkę pola. Następnie otwórz zakładkę ANALIZA, klik-
nij przycisk Grupowanie i wybierz opcję Grupuj pole. W oknie dialogowym Grupowanie wprowadź początkowe
i końcowe daty lub czas. Na liście Według kliknij wybrany sposób grupowania (na przykład Miesiące lub Kwarta-
ły). Przy grupowaniu wartości tekstowych trzeba utworzyć niestandardowe grupy. Na przykład aby pogrupować
państwa, należy dodać niestandardowe grupy Ameryka Północna, Europa itd. Trzeba zaznaczyć elementy, które
mają znaleźć się w jednej z grup, otworzyć zakładkę ANALIZA, kliknąć przycisk Grupowanie i wybrać opcję Gru-
puj zaznaczenie. Następnie wystarczy kliknąć etykietę grupy, wprowadzić nazwę, wcisnąć Enter i powtórzyć ten
proces dla następnej niestandardowej grupy.
EXCNIE_ksiazka.indb 231
231
2015-05-14 11:26:34
Rozdział 12. Analizowanie danych za pomocą tabel przestawnychPoleć książkęKup książkęModyfikowanie podsumowań
w tabeli przestawnej
J eśli analiza danych wymaga obliczeń innych niż
sumowanie (dla danych liczbowych) lub zliczanie
(dla danych tekstowych), można dla pola danych
zastosować dowolne z dziewięciu innych podsumowań
dostępnych w Excelu. Na przykład Średnia oblicza śred-
nią z wartości z pola liczbowego. Maksimum i Minimum
wyświetlają największą i najmniejszą wartość takiego
pola. Iloczyn mnoży wartości z pola liczbowego, a Licznik
wyświetla łączną liczbę wartości w źródłowym polu.
OdchStd i OdchStdc obliczają odchylenie standardowe dla
próbki i całej populacji. Wariancja i Wariancja populacji
zwracają wariancję z próbki i z całej populacji.
Modyfikowanie podsumowań w tabeli przestawnej
1 Kliknij dowolną komórkę wybranego
pola danych.
1
2 Otwórz zakładkę ANALIZA.
3 Kliknij przycisk Aktywne pole.
4 Wybierz opcję Ustawienia pola.
3
4
2
232
EXCNIE_ksiazka.indb 232
2015-05-14 11:26:36
Poleć książkęKup książkęPojawi się okno dialogowe Ustawienia
pola wartości z otwartą zakładką
Podsumowanie wartości według.
5 Wybierz podsumowanie, które chcesz
zastosować.
6 Kliknij przycisk OK.
A Excel ponownie przeliczy wyniki
w tabeli przestawnej.
B Excel zmieni etykietę w polu
danych, aby odzwierciedlić
zastosowanie nowego
podsumowania.
5
6
B
A
Dodatkowe informacje
Jeśli wyniki w tabeli przestawnej wyglądają na nieprawidłowe, sprawdź, czy Excel nie zastosował podsumowania
Licznik zamiast Suma. Jeśli pole danych zawiera przynajmniej jedną komórkę tekstową lub pustą, Excel domyślnie
zliczy elementy, zamiast obliczyć ich sumę. Gdy dodasz drugie pole do obszaru wierszy lub kolumn, Excel wyświetli
sumę częściową dla każdego elementu z zewnętrznego pola. Aby zmienić obliczenia sum częściowych, kliknij
dowolną komórkę zewnętrznego pola, otwórz zakładkę ANALIZA, kliknij przycisk Aktywne pole, a następnie wy-
bierz opcję Ustawienia pola. Zaznacz pole opcji Niestandardowe (pole
), a następnie wybierz
rodzaj obliczeń dla sum częściowych i kliknij przycisk OK.
zmieni się w
EXCNIE_ksiazka.indb 233
233
2015-05-14 11:26:36
Rozdział 12. Analizowanie danych za pomocą tabel przestawnychPoleć książkęKup książkęObliczenia
niestandardowe
O bliczenia niestandardowe generuje formuła, którą na-
leży zdefiniować, aby uzyskać w tabeli przestaw-
nej wartości niemożliwe do otrzymania za pomocą
źródłowych pól danych i wbudowanych podsumowań
Excela. Obliczenia niestandardowe umożliwiają rozbu-
dowanie analiz danych o wyniki dostosowane do potrzeb
użytkownika.
Załóżmy, że tabela przestawna wyświetla kwartalne wy-
niki sprzedaży uzyskane przez pracowników i chcesz
przyznać 10-procentową premię każdej osobie, która
w dowolnym kwartale sprzedała produkty o wartości
ponad 25 000 złotych. Możesz przygotować obliczenia
niestandardowe, aby sprawdzały, czy poziom sprzedaży
jest wyższy niż 25 000 złotych, i jeśli ten warunek jest
spełniony, mnożyły wynagrodzenie przez 0,1 w celu
otrzymania wysokości premii.
Typy obliczeń niestandardowych
Gdy tworzysz obliczenia niestandardowe w tabeli prze-
stawnej, Excel udostępnia dwie metody: pola obliczeniowe
i elementy obliczeniowe.
Pole obliczeniowe
Jeśli analizy danych wymagają umieszczenia w tabeli
przestawnej pola, które jest niedostępne w źródle danych
i niemożliwe do otrzymania za pomocą wbudowanych
podsumowań z Excela, możesz wstawić niestandardową
formułę, aby uzyskać potrzebne pole. Pole obliczeniowe
to nowe pole danych o wartości wyliczanej przez niestan-
dardową formułę. Pole obliczeniowe można wyświetlać
razem z innymi polami danych lub niezależnie od nich. Pole
obliczeniowe jest tak naprawdę niestandardowym podsu-
mowaniem, dlatego prawie zawsze jest oparte na polach
z danych źródłowych. Więcej informacji na ten temat znaj-
dziesz w podrozdziale „Dodawanie niestandardowego pola
obliczeniowego” w dalszej części rozdziału.
Excel przeprowadza obliczenia niestandardowe na da-
nych źródłowych, aby wygenerować podsumowanie.
W większości sytuacji obliczenia niestandardowe przy-
pominają wbudowane podsumowania tabel przestawnych
Excela, przy czym użytkownik może precyzyjnie zdefi-
niować szczegóły formuły. Ponieważ tworzony jest wzór,
można wykorzystać większość związanych z formułami
możliwości, jakie daje Excel. Zapewnia to bardzo dużą
swobodę przy opracowywaniu obliczeń niestandardo-
wych dostosowanych do potrzeb z zakresu analizy da-
nych. Dzięki umieszczeniu obliczeń w tabeli przestawnej,
a nie w danych źródłowych można w razie potrzeby łatwo
zaktualizować obliczenia i odświeżyć wyniki w raporcie.
Element obliczeniowy
Jeśli przeprowadzane analizy danych wymagają umieszcze-
nia w tabeli przestawnej elementu, który jest niedostępny
w źródle danych i niemożliwy do otrzymania za pomocą
wbudowanych podsumowań z Excela, możesz wstawić
niestandardową formułę, aby uzyskać potrzebny element.
Element obliczeniowy to nowa pozycja w wierszu albo ko-
lumnie, której wartość jest obliczana za pomocą formuły
niestandardowej. W formule elementu obliczeniowego
używane są elementy z danego pola.
234
EXCNIE_ksiazka.indb 234
2015-05-14 11:26:36
Poleć książkęKup książkęOgraniczenia obliczeń niestandardowych
Niezależnie od tego, czy używasz pól, czy elementów ob-
liczeniowych, obliczenia niestandardowe są przydatnym
dodatkiem do zestawu narzędzi analitycznych w tabe-
lach przestawnych. Jednak choć formuły w obliczeniach
niestandardowych wyglądają jak w zwykłych arkuszach,
nie dają wszystkich możliwości dostępnych w standardo-
wych formułach. Istnieje szereg ograniczeń, które Excel
nakłada na formuły niestandardowe. Nie można na przy-
kład stosować odwołań do wartości spoza danych tabeli
przestawnej ani elementów obliczeniowych w pogrupo-
wanej tabeli.
Ogólne ograniczenia
Głównym ograniczeniem związanym z obliczeniami nie-
standardowymi jest to, że nie można stosować odwołań
do wartości spoza danych źródłowych tabeli przestawnej
(wyjątkiem są tu stałe, na przykład liczby):
• W niestandardowych formułach nie można używać
jako operandów odwołań do komórek, adresów za-
kresów i nazw zakresów.
• Nie można stosować funkcji, która wymaga odwo-
łań do komórek, zakresów lub zdefiniowanych nazw.
Można jednak korzystać z wielu funkcji Excela, jeśli
zamiast odwołania do komórki lub nazwy zakresu
podane zostanie pole lub element. Na przykład jeżeli
chcesz utworzyć element obliczeniowy zwracający
średnią dla elementów o nazwach Sty, Lut i Mar, mo-
żesz zastosować następującą formułę:
=ŚREDNIA(Sty, Lut, Mar)
• W niestandardowych formułach nie można używać
jako operandów sum częściowych, sum końcowych
wierszy, sum końcowych kolumn ani sumy końcowej
tabeli przestawnej.
Ograniczenia dotyczące pól obliczeniowych
Jeśli chcesz używać pól obliczeniowych, musisz zrozu-
mieć, jak wskazywać inne pola tabeli przestawnej w obli-
czeniach i jakie ograniczenia obowiązują przy korzystaniu
z odwołań do pól.
odwołania do pól
Excel traktuje odwołania do pól w formułach jako sumy
wartości z tych pól. Na przykład formuła =Sprzedaż
+ 1 nie powoduje dodania 1 do każdej wartości z pola
Sprzedaż i zwrócenia sumy wszystkich uzyskanych
w ten sposób liczb. Excel nie interpretuje tej formuły
jako =Suma z (Sprzedaż + 1). Zamiast tego
formuła dodaje 1 do sumy wartości z pola Sprzedaż.
Excel traktuje więc pokazaną formułę jako =(Suma
z Sprzedaż) + 1.
pRoblemy z odwołaniami do pól
Gdy w obliczeniach niestandardowych używane jest od-
wołanie do innego pola, Excel domyślnie oblicza sumę, co
może prowadzić do problemów. Jest tak, ponieważ sumo-
wanie niektórych rodzajów danych nie ma sensu. Załóżmy
na przykład, że źródło danych ze spisem towarów zawiera
pola Liczba sztuk i Cena jednostkowa. Użyt-
kownik chce obliczyć łączną wartość towarów, dlatego
tworzy pole obliczeniowe z następującą formułą:
=Liczba sztuk * Cena jednostkowa
Niestety ta formuła nie zadziała, ponieważ Excel traktuje
operand Cena jednostkowa jako sumę takich cen.
Oczywiście dodawanie cen do siebie nie ma sensu, formu-
ła zwróci więc nieprawidłowy wynik.
Ograniczenia dotyczące elementów
obliczeniowych
Excel nakłada następujące ograniczenia związane z ele-
mentami obliczeniowymi:
• W przypadku elementu obliczeniowego formuła nie-
standardowa nie może odwoływać się do elementów
z innych pól z wyjątkiem pola, w którym sam element
obliczeniowy się znajduje.
• Nie można tworzyć elementów obliczeniowych w ta-
beli przestawnej, w której przynajmniej jedno pole
jest pogrupowane. Przed utworzeniem elementu
obliczeniowego należy rozgrupować wszystkie pola
tabeli przestawnej.
• Nie można pogrupować pola w tabeli przestawnej
zawierającej choć jeden element obliczeniowy.
•
Elementu obliczeniowego nie można używać jako fil-
tra raportu. Ponadto pola wiersza lub kolumny, które
zawiera obiekt obliczeniowy, nie można przenieść do
obszaru filtrów.
• Nie można wstawić elementu obliczeniowego do
tabeli przestawnej, w której określone pole zostało
użyte więcej niż jeden raz.
• Nie można wstawić elementu obliczeniowego do ta-
beli przestawnej, w której użyto obliczeń podsumo-
wujących typu Średnia, OdchStd, OdchStdc,
Wariancja albo Wariancja populacji.
EXCNIE_ksiazka.indb 235
235
2015-05-14 11:26:36
Rozdział 12. Analizowanie danych za pomocą tabel przestawnychPoleć książkęKup książkęDodawanie niestandardowego
pola obliczeniowego
J eśli w analizach danych potrzebne jest pole tabeli
przestawnej, które nie występuje w polach danych
źródłowych i jest niemożliwe do uzyskania za po-
mocą wbudowanych podsumowań Excela, można wsta-
wić pole obliczeniowe z niestandardową formułą i w ten
sposób otrzymać pożądane wyniki. Niestandardowe pole
obliczeniowe jest oparte na formule bardzo podobnej do
zwykłych formuł Excela. Jednak formuły pola obliczenio-
wego nie należy wstawiać do komórki arkusza. Zamiast
tego Excel udostępnia okno dialogowe, w którym można
wprowadzić nazwę pola obliczeniowego i utworzyć for-
mułę. Następnie Excel zapisuje formułę razem z resztą
danych tabeli przestawnej w jej pamięci podręcznej.
Dodawanie niestandardowego pola obliczeniowego
1 Kliknij dowolną komórkę w obszarze
danych tabeli przestawnej.
2 Otwórz zakładkę ANALIZA.
3 Kliknij przycisk Obliczenia.
4 Wybierz opcję Pola, elementy
i zestawy.
5 Wybierz opcję Pole obliczeniowe.
Pojawi się okno dialogowe
Wstawianie pola obliczeniowego.
6 Wpisz nazwę pola obliczeniowego.
7 Rozpocznij wpisywanie formuły dla
pola obliczeniowego.
2
3
4
5
1
6
7
236
EXCNIE_ksiazka.indb 236
2015-05-14 11:26:36
Poleć książkęKup książkę8 Aby wstawić do formuły pole
w miejscu, w którym znajduje się
kursor, kliknij nazwę tego pola.
9 Kliknij Wstaw pole.
10 Gdy formuła będzie gotowa, kliknij
przycisk Dodaj.
11 Kliknij przycisk OK.
A Excel doda pole obliczeniowe
do obszaru danych tabeli
przestawnej.
B Excel doda pole obliczeniowe do
panelu Pola tabeli przestawnej.
10
8
9
11
A
B
Dodatkowe informacje
Gdy do tabeli przestawnej dodajesz pole obliczeniowe, Excel uwzględnia niestandardowe obliczenia w wierszu
z sumą końcową. Niestety często są one błędne, dlatego nie należy zakładać, że są prawidłowe. Źródłem prob-
lemu jest to, że wynik nie jest sumą wartości z pola obliczeniowego. Zamiast tego Excel stosuje formułę z pola
obliczeniowego do sumy pól użytych w formule. Gdy dodasz opisane w przykładzie pole obliczeniowe, Excel
zastosuje formułę do wartości Suma końcowa pola Suma z Cena w sumie. Nie jest to prawidłowy sposób oblicza-
nia sumy prowizji. Aby rozwiązać problem, powinieneś utworzyć formułę sumującą kwoty prowizji poza tabelą
przestawną.
EXCNIE_ksiazka.indb 237
237
2015-05-14 11:26:36
Rozdział 12. Anali
Pobierz darmowy fragment (pdf)