Cyfroteka.pl

klikaj i czytaj online

Cyfro
Czytomierz
00148 009730 11027765 na godz. na dobę w sumie
Excel. Tabele i wykresy przestawne. Przewodnik po tworzeniu dynamicznych arkuszy kalkulacyjnych - książka
Excel. Tabele i wykresy przestawne. Przewodnik po tworzeniu dynamicznych arkuszy kalkulacyjnych - książka
Autor: Liczba stron: 288
Wydawca: Helion Język publikacji: polski
ISBN: 83-246-0558-4 Data wydania:
Lektor:
Kategoria: ebooki >> komputery i informatyka >> aplikacje biurowe >> excel
Porównaj ceny (książka, ebook, audiobook).

Poznaj nowoczesne narzędzie do analizy danych

Excel, jedna z najpopularniejszych aplikacji biurowych, jest najczęściej wykorzystywany do gromadzenia i prezentowania danych. Jednak na tym nie kończą się jego możliwości. Można również użyć go do przeprowadzania złożonych analiz, wyznaczania trendów, prognozowania i obliczeń statystycznych. Te zastosowania mogą okazać się przydatne zarówno przedsiębiorcom, jak i naukowcom, a nawet zwykłym użytkownikom. Excel oferuje wiele narzędzi do analizowania, podsumowywania oraz wizualizacji dużej ilości danych, a wśród nich tabele i wykresy przestawne.

Książka 'Excel. Tabele i wykresy przestawne. Przewodnik po tworzeniu dynamicznych arkuszy kalkulacyjnych' opisuje zasady wykorzystywania tych możliwości programu. Czytając ją i wykonując zawarte w niej przykłady, dowiesz się, jak tworzyć i modyfikować tabele przestawne, przeprowadzać obliczenia na znajdujących się w nich danych oraz prezentować uzyskane wyniki na wykresach. Każde zagadnienie przedstawione jest w postaci zrzutów ekranu i zwięzłych objaśnień. Dzięki temu będziesz mógł niemal natychmiast zastosować je w swojej pracy.

Poznaj bogate możliwości Excela i przekonaj się,
jak bardzo jest przydatny podczas analizowania danych.

Znajdź podobne książki Ostatnio czytane w tej kategorii

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. Koœciuszki 1c 44-100 Gliwice tel. 032 230 98 63 e-mail: helion@helion.pl Excel. Tabele i wykresy przestawne Przewodnik po tworzeniu dynamicznych arkuszy kalkulacyjnych Autor: Paul McFedries T³umaczenie: Piotr Pilch ISBN: 83-246-0558-4 Tytu³ orygina³u: Excel Pivot Tables and Pivot Charts: Your visual blueprint for creating dynamic spreadsheets Format: B5, stron: 296 Poznaj nowoczesne narzêdzie do analizy danych (cid:129) Zbuduj tabele przestawne (cid:129) PrzeprowadŸ obliczenia i analizy (cid:129) Przedstaw dane na wykresach Excel, jedna z najpopularniejszych aplikacji biurowych, jest najczêœciej wykorzystywany do gromadzenia i prezentowania danych. Jednak na tym nie koñcz¹ siê jego mo¿liwoœci. Mo¿na równie¿ u¿yæ go do przeprowadzania z³o¿onych analiz, wyznaczania trendów, prognozowania i obliczeñ statystycznych. Te zastosowania mog¹ okazaæ siê przydatne zarówno przedsiêbiorcom, jak i naukowcom, a nawet zwyk³ym u¿ytkownikom. Excel oferuje wiele narzêdzi do analizowania, podsumowywania oraz wizualizacji du¿ej iloœci danych, a wœród nich tabele i wykresy przestawne. Ksi¹¿ka „Excel. Tabele i wykresy przestawne. Przewodnik po tworzeniu dynamicznych arkuszy kalkulacyjnych” opisuje zasady wykorzystywania tych mo¿liwoœci programu. Czytaj¹c j¹ i wykonuj¹c zawarte w niej przyk³ady, dowiesz siê, jak tworzyæ i modyfikowaæ tabele przestawne, przeprowadzaæ obliczenia na znajduj¹cych siê w nich danych oraz prezentowaæ uzyskane wyniki na wykresach. Ka¿de zagadnienie przedstawione jest w postaci zrzutów ekranu i zwiêz³ych objaœnieñ. Dziêki temu bêdziesz móg³ niemal natychmiast zastosowaæ je w swojej pracy. (cid:129) G³ówne elementy tabel przestawnych (cid:129) Tworzenie i formatowanie tabeli przestawnej (cid:129) Filtrowanie danych w tabeli przestawnej (cid:129) Organizowanie danych tabeli przestawnej za pomoc¹ grup (cid:129) Definiowanie mechanizmów obliczeniowych (cid:129) Prezentowanie danych na wykresach przestawnych (cid:129) Import danych zewnêtrznych do tabeli przestawnej (cid:129) Wykorzystywanie kostek OLAP Poznaj bogate mo¿liwoœci Excela i przekonaj siê, jak bardzo jest przydatny podczas analizowania danych Spis treści Jak korzystać z książki ...................................................... viii 1. Tabele przestawne .......................................................... 2 Analiza danych ........................................................................................................................................................... 2 Tabele przestawne — wprowadzenie ................................................................................................................... 4 Zalety tabel przestawnych ....................................................................................................................................... 6 Przegląd zastosowań tabeli przestawnej ............................................................................................................. 8 Omówienie elementów tabeli przestawnej ....................................................................................................... 10 Wykresy przestawne — wprowadzenie ............................................................................................................. 12 2. Tworzenie tabeli przestawnej ...........................................14 Przygotowanie danych arkusza ............................................................................................................................ 14 Tworzenie listy dla raportu tabeli przestawnej ............................................................................................... 16 Tworzenie prostej tabeli przestawnej przy użyciu listy Excela ................................................................. 18 Dodawanie pól za pomocą kreatora tworzącego tabele przestawne .........................................................22 Ponowne tworzenie istniejącej tabeli przestawnej .........................................................................................24 3. Przetwarzanie tabeli przestawnej .....................................26 Wyświetlanie i ukrywanie paska narzędziowego Tabela przestawna ......................................................26 Zaznaczanie elementów tabeli przestawnej .....................................................................................................28 Usuwanie pola tabeli przestawnej .......................................................................................................................30 Odświeżanie danych tabeli przestawnej ...........................................................................................................32 Wyświetlanie szczegółów dotyczących danych, na których bazuje tabela przestawna .....................34 Tworzenie wykresu na podstawie danych tabeli przestawnej ....................................................................36 Dodawanie wielu pól do obszaru wiersza lub kolumny ...............................................................................40 Dodawanie wielu pól do obszaru danych .........................................................................................................42 Dodawanie wielu pól do obszaru strony ...........................................................................................................44 Publikowanie tabeli przestawnej na stronie internetowej ............................................................................46 Zamiana zawartości tabeli przestawnej na zwykłe dane ..............................................................................50 Drukowanie tabeli przestawnej ............................................................................................................................52 Usuwanie tabeli przestawnej ................................................................................................................................54 4. Modyfikowanie wyglądu tabeli przestawnej ........................56 Przemieszczanie pola do innego obszaru ..........................................................................................................56 Wyświetlanie innej strony .....................................................................................................................................58 Zmiana kolejności pól obszaru .............................................................................................................................60 Sortowanie danych tabeli przestawnej za pomocą funkcji automatycznego sortowania ...................62 Przemieszczanie pozycji wiersza lub kolumny ..............................................................................................64 Pokazywanie jedynie 10 pierwszych pozycji ..................................................................................................66 Ukrywanie pozycji pola wiersza lub kolumny ................................................................................................68 Wyświetlanie ukrytych pozycji pola wiersza lub kolumny ........................................................................70 Wyświetlanie stron jako arkuszy.........................................................................................................................72 Grupowanie wartości liczbowych .......................................................................................................................74 Grupowanie dat i godzin ........................................................................................................................................76 iv Excel_tabele_i_wykresy_przestawniv iv Excel_tabele_i_wykresy_przestawniv iv 2006-10-10 21:44:41 2006-10-10 21:44:41 Grupowanie wartości tekstowych .......................................................................................................................78 Ukrywanie szczegółów grupy ..............................................................................................................................80 Wyświetlanie szczegółów grupy .........................................................................................................................81 Rozgrupowanie wartości ........................................................................................................................................82 Modyfi kowanie układu obszaru strony .............................................................................................................84 5. Dostosowywanie pól tabeli przestawnej .............................86 Zmiana nazwy pola tabeli przestawnej..............................................................................................................86 Zmiana nazwy pozycji tabeli przestawnej .......................................................................................................88 Formatowanie komórki tabeli przestawnej ......................................................................................................90 Formatowanie wartości liczbowych w danych tabeli przestawnej ..........................................................92 Formatowanie dat w danych tabeli przestawnej ...........................................................................................94 Formatowanie warunkowe danych tabeli przestawnej .................................................................................96 Wyświetlanie pozycji pozbawionych danych ..................................................................................................98 Wyłączanie pozycji pola strony .........................................................................................................................100 6. Określanie opcji tabeli przestawnej ................................ 102 Zastosowanie funkcji Autoformatowanie .......................................................................................................102 Zachowywanie formatowania tabeli przestawnej .........................................................................................104 Zmiana nazwy tabeli przestawnej .....................................................................................................................106 Wyłączanie sum końcowych ..............................................................................................................................108 Łączenie etykiet pozycji ......................................................................................................................................110 Określanie łańcuchów dla błędów i pustych komórek ...............................................................................112 Ochrona tabeli przestawnej .................................................................................................................................114 7. Przeprowadzanie obliczeń w tabeli przestawnej ................116 Modyfi kowanie obliczeń podsumowujących tabeli przestawnej ............................................................. 116 Defi niowanie różnicowego obliczenia podsumowującego .........................................................................118 Defi niowanie procentowego obliczenia podsumowującego ......................................................................120 Defi niowanie obliczenia podsumowującego Suma bieżąca w .................................................................122 Defi niowanie obliczenia podsumowującego Indeks ....................................................................................124 Ukrywanie dla pola sum częściowych .............................................................................................................126 Wyświetlanie dla pola wielu sum częściowych ............................................................................................128 Uwzględnianie ukrytych stron w wynikach tabeli przestawnej ..............................................................130 8. Definiowanie niestandardowych obliczeń tabel przestawnych ......................................................132 Niestandardowe obliczenia — wprowadzenie ...............................................................................................132 Ograniczenia niestandardowych obliczeń ......................................................................................................133 Wstawianie niestandardowegopola obliczeniowego .....................................................................................134 Wstawianie niestandardowej pozycji obliczeniowej ....................................................................................136 Modyfi kowanie niestandardowego obliczenia ...............................................................................................138 Zmiana kolejności rozwiązywania pozycji obliczeniowych ......................................................................140 Wyszczególnianie niestandardowych obliczeń .............................................................................................142 Usuwanie niestandardowego obliczenia ..........................................................................................................143 Excel_tabele_i_wykresy_przestawnv v Excel_tabele_i_wykresy_przestawnv v v 2006-10-10 21:44:42 2006-10-10 21:44:42 Spis treści 9. Tworzenie wykresu przestawnego ...................................144 Ograniczenia wykresu przestawnego ...............................................................................................................144 Tworzenie wykresu przestawnego przy użyciu tabeli przestawnej........................................................145 Tworzenie wykresu przestawnegoprzy użyciu listy Excela .....................................................................146 Tworzenie wykresu przestawnego obok tabeli przestawnej .....................................................................150 Przenoszenie wykresu przestawnego do innego arkusza ...........................................................................152 Ukrywanie lub pokazywanie przycisków pól wykresu przestawnego ..................................................154 Zmiana typu wykresu przestawnego ................................................................................................................156 Zmiana kolejności serii wykresu przestawnego ...........................................................................................158 Dodawanie tytułów do wykresu przestawnego ............................................................................................160 Przenoszenie legendy wykresu przestawnego ...............................................................................................162 Wyświetlanie tabeli danych wraz z wykresem przestawnym ..................................................................164 Drukowanie wykresu przestawnego .................................................................................................................166 Usuwanie wykresu przestawnego .....................................................................................................................168 10. Tworzenie bardziej zaawansowanych tabel przestawnych ................................................... 170 Tworzenie tabeli przestawnej przy użyciu wielu skonsolidowanych zakresów .................................170 Tworzenie tabeli przestawnej przy użyciu istniejącej tabeli ....................................................................174 Tworzenie tabeli przestawnej przy użyciu danych zewnętrznych .........................................................176 Defi niowanie serwerowego pola strony ...........................................................................................................180 Ustawianie opcji danych zewnętrznych ..........................................................................................................182 Eksportowanie danych tabeli przestawnejz pliku HTML do Excela ...................................................184 Zmniejszanie rozmiaru skoroszytów tabel przestawnych .........................................................................186 Zastosowanie w formule wartości tabeli przestawnej ................................................................................187 11. Tworzenie tabeli przestawnej przy użyciu kostki OLAP.....188 OLAP .........................................................................................................................................................................188 Defi niowanie źródła danych dla kostki OLAP .............................................................................................190 Tworzenie tabeli przestawnej przy użyciu kostki OLAP ..........................................................................194 Wyświetlanie i ukrywanie szczegółów dotyczących wymiarów i poziomów ....................................196 Ukrywanie poziomów ...........................................................................................................................................198 Wyświetlanie wybranych poziomów i składowych ....................................................................................199 Wyświetlanie wielu pozycji pola strony ........................................................................................................ 200 Dodatek A: Tworzenie formuł tabel przestawnych ................ 202 Formuły — wprowadzenie ................................................................................................................................. 202 Typy formuł ............................................................................................................................................................. 204 Funkcje arkusza — wprowadzenie .................................................................................................................. 206 Typy funkcji ............................................................................................................................................................ 208 Defi niowanie funkcji .............................................................................................................................................210 Defi niowanie formuły ...........................................................................................................................................212 Korzystanie z niestandardowych formatów numerycznych i formatów dat .......................................214 vi Excel_tabele_i_wykresy_przestawnvi vi Excel_tabele_i_wykresy_przestawnvi vi 2006-10-10 21:44:42 2006-10-10 21:44:42 Dodatek B: Zastosowanie narzędzia Microsoft Query w przypadku tabel przestawnych Excela ..............216 Zastosowanie narzędzia Microsoft Query ......................................................................................................216 Defi niowanie źródła danych ...............................................................................................................................218 Uruchamianie narzędzia Microsoft Query ................................................................................................... 222 Okno narzędzia Microsoft Query .................................................................................................................... 223 Uwzględnianie tabeli w kwerendzie ............................................................................................................... 224 Dodawanie pól do kwerendy ............................................................................................................................. 226 Filtrowanie rekordów przy użyciu kryteriów kwerendy ........................................................................... 228 Sortowanie rekordów kwerendy ....................................................................................................................... 230 Zwracanie wyników kwerendy ......................................................................................................................... 232 Dodatek C: Dane zewnętrzne .............................................234 Typy danych zewnętrznych ................................................................................................................................ 234 Importowanie danych ze źródła danych ......................................................................................................... 236 Importowanie danych z tabeli programu Access ......................................................................................... 238 Importowanie danych z tabeli Worda ............................................................................................................. 240 Importowanie danych z pliku tekstowego ...................................................................................................... 242 Importowanie danych ze strony internetowej ............................................................................................... 246 Importowanie danych z pliku XML ................................................................................................................ 248 Odświeżanie zaimportowanych danych ......................................................................................................... 250 Dodatek D: Podstawy języka VBA związane z tabelami przestawnymi ..................................252 Rejestrowanie makra ............................................................................................................................................ 252 Korzystanie z narzędzia Edytor Visual Basic .............................................................................................. 254 Omówienie modelu obiektowego tabeli przestawnej ................................................................................. 256 Dodawanie makra do modułu ........................................................................................................................... 258 Uruchamianie makra ........................................................................................................................................... 260 Ustawianie zabezpieczeń makr ........................................................................................................................ 262 Przypisywanie makra do przycisku paska narzędziowego ...................................................................... 266 Przypisywanie makra do polecenia menu ..................................................................................................... 268 Dodatek E: Słownik terminów związanych z tabelami przestawnymi .................................. 270 Słownik terminów związanych z tabelami przestawnymi ....................................................................... 270 Excel_tabele_i_wykresy_przestawnvii vii Excel_tabele_i_wykresy_przestawnvii vii vii 2006-10-10 21:44:42 2006-10-10 21:44:42 Analiza danych T abele i wykresy przestawne, którym poświęcono niniejszą książkę, stanowią część większej kate- gorii, czyli analizy danych. Oba narzędzia będzie można lepiej wykorzystać, gdy Czytelnik lepiej zrozu- mie, czym jest analiza danych, jakie są jej zalety i jakie inne narzędzia są dostępne. Analiza danych jest zastosowaniem narzędzi i metod w celu zorganizowania, przebadania, wyciągnięcia wniosków, a czasami również określenia przewidywań dotyczących zbioru informacji. Kierownik sprzedaży może posłużyć się analizą danych, aby przestudiować historię sprzedaży produktu, ustalić ogólny trend i pro- gnozy dotyczące sprzedaży w przyszłości. Naukowiec może z niej skorzystać w celu przeanalizowania wy- ników eksperymentów i określenia ich statystycznego znaczenia. Każdy z nas może użyć analizy danych do Dane ustalenia maksymalnej wartości pożyczki hipotecznej, na jaką może sobie pozwolić, lub określenia kwoty, jaką co miesiąc musi odłożyć na sfi nansowanie emerytury lub wykształcenia dzieci. Celem analizy danych jest trochę głębsze ich zrozu- mienie. Z defi nicji „surowe” dane są zwykłym zbiorem faktów, które same przekazują niewiele informacji lub żadnych istotnych. Aby w pewnym stopniu zrozumieć dane, trzeba je przetworzyć w określony znaczący spo- sób. Może to być tak prosta operacja jak wyznaczenie sumy lub średniej dla kolumny liczb bądź tak złożo- na jak rozbudowana analiza regresyjna określająca podstawowy trend zakresu wartości. Obie operacje są przykładami analizy danych. Aby spełnić nawet naj- większe wymagania, Excel oferuje kilka narzędzi — od najprostszych do zaawansowanych. Słowo „dane” w terminie „analiza danych” identyfikuje zbiór liczb, dat i tekstu reprezentujący „surowe” infor- macje, które użytkownik musi przetwarzać. W przypadku Excela dane te znajdują się w arkuszu. Mogą w nim zo- stać umieszczone na dwa sposoby — można je wprowa- dzić ręcznie lub zaimportować z zewnętrznego źródła. Po wstawieniu do arkusza dane można następnie pozo- stawić w postaci zwykłego zakresu lub zamienić na listę ułatwiającą przetwarzanie. Importowanie danych Większość projektów analizy danych dotyczy dużej ilo- ści danych. W tym przypadku najszybszą i najbardziej precyzyjną metodą umieszczenia danych w arkuszu Ex- cela będzie zaimportowanie ich z zewnętrznego źródła. W najprostszym scenariuszu dane można skopiować z pliku tekstowego, tabeli Worda lub arkusza danych programu Access, a następnie wkleić je do arkusza Exce- la. Jednakże przeważnie dane biznesowe i naukowe są przechowywane w dużych bazach danych. Excel oferuje narzędzia pozwalające zaimportować do arkusza żądane dane. Więcej informacji na temat tych narzędzi można znaleźć w dodatku B i C. Wprowadzanie danych W wielu przypadkach analizy danych żądane dane muszą zostać ręcznie wprowadzone do arkusza. Jeśli na przy- kład Czytelnikowi zależy na wyznaczeniu prawdopodob- nej miesięcznej raty pożyczki hipotecznej, musi najpierw określić wartości takie jak aktualna stopa procentowa, kwota pożyczki i okres spłaty. Ręczne wpisywanie danych jest odpowiednie tylko w przypadku niewielkich projek- tów, ponieważ wprowadzanie setek, a nawet tysięcy wartości jest czasochłonne i podatne na błędy. Lista Gdy dane znajdą się w arkuszu, można je pozostawić w postaci zwykłego zakresu i nadal mieć możliwość za- stosowania wielu metod analizy danych. Jeśli jednak za- kres zamieni się na listę, Excel potraktuje dane jak prostą, płaską bazę i umożliwi użycie kilku bazodanowych metod analizy. W rozdziale 2. zamieszczono zadanie „Tworzenie listy dla raportu tabeli przestawnej”, z którym należy się zapoznać. 2 Excel_tabele_i_wykresy_przestawn2 2 Excel_tabele_i_wykresy_przestawn2 2 2006-10-10 21:44:43 2006-10-10 21:44:43 Modele danych W wielu przypadkach analiza danych jest przeprowa- dzana na wartościach arkuszowych. Sprowadza się to do zorganizowania wartości za pomocą modelu danych, czyli zbioru komórek będących arkuszową wersją jakie- goś rzeczywistego zagadnienia lub scenariusza. Model uwzględnia nie tylko „surowe” dane, ale również jedną lub więcej komórek reprezentujących określoną metodę analizy danych. Przykładowo, model amortyzacji po- życzki hipotecznej posiadałby dane takie jak stopa pro- centowa, kwota pożyczki i okres spłaty, a także komórki obliczające wpłaty, kwotę pożyczki i stopę procentową w okresie spłaty. Przy takich obliczeniach korzysta się z formuł i wbudowanych funkcji Excela omówionych w dodatku A zatytułowanym „Tworzenie formuł dla tabel przestawnych”. R o z d z i a ł 1 . T a b e l e p r z e s t a w n e Formuły Formuła jest zestawem symboli i wartości wykonują- cych określonego typu obliczenia i zwracających wynik. Wszystkie formuły Excela posiadają identyczną ogólną strukturę. Za znakiem równości (=) znajduje się jeden lub więcej argumentów, które mogą być wartością, odwołaniem do komórki, zakresem, nazwą zakresu lub funkcji. Argumenty są oddzielone od siebie jed- nym operatorem, czyli symbolem łączącym argumenty w określony sposób (można tu wymienić znak plusa (+) i mnożenia (*)), lub większą ich liczbą. Przykłado- wo, formuła =A1+A2 dodaje wartości zawarte w ko- mórkach A1 i A2. Funkcje Funkcja jest predefiniowaną formułą wbudowaną w Excela. Każda funkcja pobiera jedną lub więcej da- nych wejściowych nazywanych argumentami. Mogą to być wartości lub odwołania do komórek. Funkcja zwraca następnie wynik. Excel oferuje setki funkcji, za pomocą których można między innymi obliczać średnie, określać przyszłą wartość inwestycji, porównywać wartości. Analiza warunkowa Jedną z najczęściej stosowanych metod analizy da- nych jest analiza warunkowa (ang. what-if analysis), w przypadku której definiuje się modele arkuszowe bada- jące hipotetyczne sytuacje. Termin „warunkowa” wynika z faktu, że takie sytuacje zwykle są formułowane przy użyciu pytań. Jak na miesięczną wpłatę wpłynie zwiększe- nie stopy procentowej o 2 ? Jaki będzie poziom sprze- daży, gdy budżet przeznaczony na reklamy zwiększy się o 10 ? Excel oferuje cztery narzędzia analizy warianto- wej — tabele danych, Goal Seek, Solver i scenariusze. Tabela danych Tabela danych jest zakresem komórek, w którym jedna kolumna zawiera serię wartości nazywanych komórka- mi wejściowymi. Zawartość każdej komórki wejściowej można przekazać formule i Excel wyświetli wynik dla każdego przypadku. Przykładowo, tabeli danych można użyć w celu przekazania serii wartości stopy procento- wej formule, która obliczy miesięczną wpłatę związaną z pożyczką zwykłą lub hipoteczną. Goal Seek Z narzędzia Goal Seek programu Excel można sko- rzystać, gdy zamierzamy przetwarzać jeden składnik formuły, nazywany zmienną komórką (ang. changing cell), w taki sposób, że formuła zwraca określony wynik. Przykładowo, w przypadku analizy opłacalności określa się liczbę egzemplarzy produktu, które trzeba sprzedać przy zerowym zysku. Mając formułę obliczającą zysk, za pomocą narzędzia Goal Seek można wyznaczyć próg opłacalności. Solver Z dołączonego do Excela dodatku Solver korzystamy, gdy mamy zamiar przetwarzać wiele składników for- muły nazywanych zmiennymi komórkami (ang. chan- ging cells) w taki sposób, że generuje ona optymalny wynik. Przykładowo, za pomocą dodatku Solver można rozwiązać tak zwany problem transportowy, w przy- padku którego celem jest zminimalizowanie kosztu dostarczenia towaru pochodzącego z kilku fabryk do różnych magazynów rozmieszczonych na terenie całe- go kraju. Scenariusze Scenariusz jest zbiorem wartości wejściowych przeka- zywanych formułom modelu w celu wygenerowania wyniku. Idealne jest zdefiniowanie scenariuszy dla róż- nych sytuacji (na przykład najlepszej, najgorszej itp.). Menedżer scenariuszy Excela zachowa każdy scena- riusz. Później można zastosować dowolny z nich i Excel automatycznie uwzględni w modelu wszystkie wartości wejściowe. Excel_tabele_i_wykresy_przestawn3 3 Excel_tabele_i_wykresy_przestawn3 3 3 2006-10-10 21:44:43 2006-10-10 21:44:43 Tabele przestawne — wprowadzenie L isty i zewnętrzne bazy danych mogą zawierać setki, a nawet tysiące rekordów. Analizowanie tak dużej ilości danych może okazać się koszmarem, gdy nie zastosujemy odpowiedniego typu narzędzi. Aby pomóc użytkownikowi, Excel oferuje narzędzie analizy danych, które ma duże możliwości, a nazywane jest tabelą przestawną. Narzędzie to umożliwia podsu- mowywanie setek rekordów w zwartym tabelarycznym formacie. W celu zobaczenia różnych widoków danych można przetwarzać lub przestawiać układ tabeli. Analiza bazodanowa Czytelnik dowie się z książki wszystkiego, co musi wiedzieć (naprawdę prawie wszystko warte jest po- znania) na temat tabel przestawnych. Między innymi wyjaśniono, jak tworzyć tabele przestawne, edytować, przestawiać, formatować i wykonywać dla nich oblicze- nia. Jeśli poświęcimy trochę czasu na zapoznanie się z podstawami dotyczącymi tabel przestawnych, więcej zyskamy po lekturze pozostałej części książki. Aby zrozumieć tabele przestawne, trzeba uzmysłowić so- bie, w jaki sposób dopasowują się one do innych funkcji analizy bazodanowej Excela. Analiza bazodanowa posiada trzy poziomy złożoności — wyszukiwanie i pobieranie, kry- teria i funkcje listy oraz wiele zmiennych. Podczas przecho- dzenia z jednego poziomu na kolejny stanie się widoczna potrzeba użycia tabel przestawnych. Wiele zmiennych Następny poziom analizy bazodanowej dotyczy poje- dynczego problemu z wieloma zmiennymi. Jeśli firma z poprzedniego przykładu posiada cztery regiony, moż- na zażądać przekazania całkowitych sum niezależnie dla każdego regionu i kolejnych kwartałów. Jedno z roz- wiązań polegałoby na zdefiniowaniu czterech różnych kryteriów i funkcji BD.SUMA(). Jak jednak postąpić w przypadku tuzina lub stu regionów? W idealnej sytuacji skorzystamy z określonej metody zestawiania informacji bazodanowych w tabeli sprzedaży, która dla każdego re- gionu i kwartału posiada odpowiednio wiersz i kolumnę. Właśnie do tego służy tabela przestawna. Przy omawianiu narzędzia Kreator tabel i wykresów przestawnych w ra- mach zadania „Tworzenie prostej tabeli przestawnej przy użyciu listy Excela” zamieszczonego w rozdziale 2. okaże się, że aby zdefiniować własne tabele przestawne, wy- starczy kilka kliknięć myszą. Wyszukiwanie i pobieranie Najprostszy poziom analizy bazodanowej uwzględnia podstawowe wyszukiwanie i pobieranie informacji. Jeśli na przykład mamy do czynienia z bazą danych, która za- wiera listę przedstawicieli handlowych firmy i podlegające im obszary sprzedaży, to aby znaleźć określonego przed- stawiciela oraz wartość sprzedaży w jego obszarze, moż- na posłużyć się formularzem, a nawet funkcją szukania programu Excel. Kryteria i funkcje listy Kolejny poziom złożoności analizy bazodanowej swoim zakresem obejmuje bardziej zaawansowane systemy wy- szukiwania i pobierania, w przypadku których stosuje się kryteria umożliwiające przetwarzanie podzbioru danych. Dla takiego podzbioru można następnie zastosować sumy częściowe i funkcje listy programu Excel (przykładem jest funkcja BD.SUMA() sumująca te komórki listy, które spełniają określone kryteria). Dla przykładu załóżmy, że każdy obszar sprzedaży jest częścią większego regionu i zależy nam na poznaniu całkowitej wartości sprzedaży w rejonie wschodnim. Można w tym celu obliczyć sumy częściowe dla regionu lub zdefiniować kryterium dopa- sowujące wszystkie obszary wschodniego rejonu i użyć funkcji BD.SUMA() do uzyskania całkowitej sumy. Aby otrzymać bardziej konkretne informacje, takie jak całkowita wartość sprzedaży we wschodnim regionie w drugim kwartale, w kryterium wystarczy uwzględnić odpowiednie warunki. 4 Excel_tabele_i_wykresy_przestawn4 4 Excel_tabele_i_wykresy_przestawn4 4 2006-10-10 21:44:44 2006-10-10 21:44:44 R o z d z i a ł 1 . T a b e l e p r z e s t a w n e Zastosowania tabel przestawnych Tabele przestawne ułatwiają analizowanie dużej ilości da- nych przez wykonywanie trzech różnych operacji — gru- powanie danych według kategorii, podsumowywanie ich przy użyciu obliczeń i filtrowanie w celu wyświetlania tyl- ko tych rekordów, które mają być przetwarzane. Grupowanie Tabela przestawna jest po części narzędziem analizy danych o dużych możliwościach, po- nieważ automatycznie grupuje duże ilości danych według mniejszych kategorii, któ- rymi można lepiej zarządzać. Dla przykładu załóżmy, że dysponujemy źródłem 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. Oryginalne dane mogą liczyć tysiące rekordów. Jeśli jednak — korzystając z pola Region — utworzymy tabelę przestawną, będzie ona miała tylko cztery wiersze. Każdy z nich będzie prze- znaczony dla czterech unikatowych wartości pola Region oryginalnych danych. Grupowanie można też zastosować już po utworzeniu tabeli przestawnej. Jeśli na przy- kład dane zawierają pole Państwo, to aby pogrupować wszystkie rekordy posiadające taką samą wartość tego pola, można zdefi- niować odpowiednią tabelę przestawną. Po wykonaniu tej operacji unikatowe wartości pola Państwo można dodatkowo pogrupo- wać w ramach kontynentów — Ameryka Północna, Ameryka Południowa, Europa itd. Aby dowiedzieć się, jak grupować wartości tabeli przestawnej, należy zajrzeć do roz- działu 4. Podsumowywanie Razem z grupowaniem danych według unikatowych wartości jed- nego pola lub większej ich liczby, dla każdej grupy Excel wyświetla też wyniki obliczeń podsumowujących. Domyślnym typem obli- czenia jest suma (wyznaczana przez funkcję Suma). Oznacza to, że dla każdej grupy Excel sumuje wszystkie wartości określonego pola. Jeśli na przykład dane zawierają pola Region i Sprzedaż, ta- bela przestawna mogłaby pogrupować unikatowe wartości pola Region i dla każdej z nich wyświetlić wartość pola Sprzedaż. Ex- cel oferuje inne funkcje podsumowujące, takie jak Licznik, Średnia, Max, Min i OdchStd. Tabela przestawna oferuje jeszcze większe możliwości, wyświet- lając podsumowania dla jednego grupowania podzielonego przez kolejne. Dla przykładu załóżmy, że dane dotyczące sprzedaży za- wierają też pole Produkt. Można zdefiniować tabelę przestawną, która udostępni całkowitą wartość sprzedaży dla każdego produk- tu z podziałem na regiony. Filtrowanie Tabela przestawna umożliwia też przeglądanie tylko podzbioru danych. Przykładowo, grupowania tabeli przestawnej domyślnie pokazują wszystkie unikatowe wartości pola. Jednakże każde grupowanie można tak zmodyfikować, aby ukryć te wartości, których nie zamierzamy przeglądać (należy zapoznać się z zada- niem „Ukrywanie pozycji pola wiersza lub kolumny” z rozdziału 4.). Każda tabela przestawna jest też wyposażona w obszar stro- ny (należy zapoznać się z podrozdziałem „Omówienie elementów tabeli przestawnej” zamieszczonym w dalszej części rozdziału), który pozwala na zastosowanie filtra dla niej całej. Dla przykładu przyjmijmy, że dane dotyczące sprzedaży uwzględniają pole Klient. Przez umieszczenie tego pola w obszarze strony tabeli przestawnej filtrowaniu można poddać raport tabeli przestawnej, aby zawierał jedynie wyniki dla jednego klienta. Ograniczenia tabeli przestawnej w dalszej części rozdziału, objaśniono zastosowaną poni- żej terminologię związaną z tabelami przestawnymi. Tabele przestawne mają określone ograniczenia, z któ- rymi trzeba się zaznajomić. W podrozdziale „Omówie- nie elementów tabeli przestawnej”, który znajduje się (cid:129) Maksymalna liczba pól kolumny wynosi 256 (warto zauważyć, że nie istnieje limit liczby pól wiersza). (cid:129) Maksymalna liczba pól strony wynosi 256. (cid:129) Liczba pól danych nie może przekroczyć 256. (cid:129) Maksymalna liczba pozycji, które mogą pojawić się w polu wiersza, jest równa 32 500 (cid:129) Maksymalna liczba pozycji, które mogą pojawić się w polu kolumny, jest równa 32 500 (cid:129) Maksymalna liczba pozycji, które mogą pojawić się w polu strony, nie może przekroczyć 32 500 (cid:129) Rozmiar i liczba tabel przestawnych jest ograniczona przez ilość pamięci, jaką dysponuje system. (jeśli korzystamy z wersji Excela starszej niż 2003, limit wynosi 8000). (jeśli korzystamy z wersji Excela starszej niż 2003, limit wynosi 8000). (jeśli korzystamy z wersji Excela starszej niż 2003, limit wynosi 8000). Excel_tabele_i_wykresy_przestawn5 5 Excel_tabele_i_wykresy_przestawn5 5 5 2006-10-10 21:44:44 2006-10-10 21:44:44 Zalety tabel przestawnych Jeśli Excel dysponuje tak wieloma bogatymi w możli- wości narzędziami i funkcjami służącymi do analizy danych, dlaczego trzeba zdobywać wiedzę na temat tworzenia i przetwarzania tabel przestawnych? Krótka odpowiedź jest taka, że tabele przestawne są przydat- ną bronią, o którą można poszerzyć posiadany arsenał narzędzi analizy danych. Dłuższa odpowiedź jest nato- miast taka, że tabele przestawne są warte opanowania, ponieważ posiadają nie jedną czy dwie zalety, ale długą ich listę. Tabele przestawne pozwalają zaoszczędzić czas Obecnie ludzie mają o wiele za dużo do zrobienia i mają na to zdecydowanie za mało czasu. Z założenia komputery mają pomóc w poradzeniu sobie z tym problemem, skra- cając czas, który poświęcamy na realizowanie rutynowych zadań, takich jak dodawanie wierszy z wartościami. Nie- które funkcje oferowane przez komputery wywołują od- wrotny efekt. Przykładowo, poczta elektroniczna zajmuje coraz więcej czasu. Ale tabele przestawne nie zaliczają się Łatwość obsługi Być może najważniejszą zaletą tabel przestawnych jest to, że nie wiąże się z nimi zniechęcająca krzywa „uczenia”. Po zrozumieniu podstawowych zagadnień za pomocą narzędzia Kreator tabel i wykresów przestawnych moż- na utworzyć prosty raport tabeli przestawnej. Operacja ta będzie wymagała jedynie dziewięciu kliknięć (należy zapoznać się z podrozdziałem „Omówienie elementów tabeli przestawnej” zamieszczonym w dalszej części roz- działu). Nawet najbardziej złożone tabele przestawne nie są zbyt trudne do zdefiniowania, ponieważ kreator krok po kroku przeprowadzi nas przez cały proces (należy zapoznać się z zadaniem „Tworzenie prostej tabeli prze- stawnej przy użyciu listy Excela” z rozdziału 2.). Szybkość Podczas generowania raportu przeciętna tabela prze- stawna musi wykonać sporą ilość operacji. Musi przeana- lizować setki, a nawet tysiące rekordów, z których każdy może posiadać tuzin lub więcej pól. Ponadto tabela prze- stawna musi wyodrębnić unikatowe wartości z jednego lub większej liczby pól, obliczyć dane podsumowujące dla każdej niepowtarzalnej pozycji i wszystko rozmieścić w arkuszu. Zadziwiające jest to, że z wyjątkiem przypad- ków użycia największych źródeł danych, cały ten proces zwykle zajmuje sekundę lub dwie. Tworzenie i utrzymywanie tabel przestawnych jest proste. Tego typu tabele zadziwiająco szybko wyko- nują duże i złożone obliczenia. Aby uwzględnić nowe dane, można je szybko i łatwo uaktualniać. Ponieważ tabele przestawne są dynamiczne, ich składniki mogą być bez problemu przemieszczane, fi ltrowane i doda- wane. Ze względu na to, że tabele przestawne można w pełnym zakresie dostosowywać, każdy raport można utworzyć w żądany sposób. Ponadto w przypadku ta- bel przestawnych można zastosować większość opcji formatowania, które wykorzystuje się przy zwykłych zakresach i komórkach Excela. do tych funkcji. Zadanie (organizowanie ogromnych ilości danych za pomocą tabel), które ma być wyeliminowane przez tabele przestawne, z założenia jest czasochłonne. Jednak tabele przestawne dzięki swojej łatwości obsłu- gi, szybkości i prostemu uaktualnianiu redukują ten czas do zaledwie ułamka tego, który dotychczas poświęcano. W efekcie można zaoszczędzić dużo czasu. Możliwość aktualizacji Tabele przestawne często są stosowane w sytuacjach, w których oryginalne dane ulegają zmianie. Gdy ma to miejsce, zawartość tabeli przestawnej może stać się nie- aktualna. Jednakże każda taka tabela „zapamiętuje” ory- ginalne dane, na podstawie których utworzono raport. Oznacza to, że gdy tabela przestawna zdezaktualizuje się, nie trzeba będzie od nowa generować raportu. Zamiast tego można wykonać polecenie odświeżające dane, któ- re od razu uaktualni zawartość tabeli o najnowsze dane. Możliwe jest nawet takie skonfigurowanie tabeli prze- stawnej, aby jej dane były automatycznie odświeżane. W celu uzyskania szczegółowych informacji na temat odświeżania tabel przestawnych należy zapoznać się z zadaniem „Odświeżanie danych tabeli przestawnej” za- mieszczonym w rozdziale 3. 6 Excel_tabele_i_wykresy_przestawn6 6 Excel_tabele_i_wykresy_przestawn6 6 2006-10-10 21:44:44 2006-10-10 21:44:44 R o z d z i a ł 1 . T a b e l e p r z e s t a w n e Elastyczność tabel przestawnych Jedną z cech tabeli przestawnej, która sprawia, że jest ona bogatym w możliwości narzędziem analizy danych, jest elastyczność. Przykładowo, po utworzeniu tabeli przestawnej powiązany z nią generowany raport nie jest zamieniany w „posąg”. Możliwe jest przemieszczanie składników tabeli przestawnej z jednego miejsca w dru- gie, filtrowanie wyników, dodawanie i usuwanie danych itp. Kolejnym aspektem elastyczności tabel przestawnych jest ich uniwersalność. Oznacza to, że tabele można two- rzyć nie tylko przy użyciu zakresów i list Excela. Uniwersalność Jeśli tabele przestawne można by było tworzyć tylko przy użyciu zakresu lub listy Excela, nadal byłyby wy- jątkowo przydatne. Jednakże twórcy Excela sprawili, że tabele przestawne są na tyle uniwersalne, że obsługują wiele innych typów danych. Tabele przestawne można tworzyć na bazie tabel programu Access i Word, plików tekstowych, stron internetowych, danych XML i tabel wydajnych systemów bazodanowych, takich jak SQL Server i serwerów OLAP (Online Analytical Processing). W rozdziale 10. omówiono tworzenie zaawansowanych tabel przestawnych, natomiast w rozdziale 11. definio- wanie tabel przy użyciu kostki OLAP. Dynamika Każda tabela przestawna jest dynamicznym obiektem, który można wielokrotnie konfigurować, aby uzyskać żądany typ raportu. Dokładniej mówiąc, większość pól dodawanych do tabeli można też przemieszczać z jed- nego miejsca raportu w inne. Operacja taka jest nazy- wana przestawianiem danych. Powoduje ona, że Excel ponownie konfiguruje tabelę przestawną i jeszcze raz oblicza wyniki. Ponieważ Excel natychmiast generuje uaktualnioną tabelę przestawną, z operacji przestawia- nia można korzystać, gdy tylko zajdzie taka potrzeba. Dzięki tej operacji tabele przestawne stają się jeszcze bardziej przydatne. Aby dowiedzieć się, jak przestawiać dane, należy zapoznać się z zadaniem „Przemieszczanie pola do innego obszaru” z rozdziału 4. Możliwość przetwarzania W szybki i prosty sposób można modyfikować struktu- rę tabeli przestawnej, aby uzyskać interesujące wyniki. Przykładowo, do dowolnego obszaru tabeli zawsze można dodać nowe pola, co zwykle sprowadza się do kilku kliknięć myszą. Z łatwością można usuwać wszel- kie pola, które nie są już potrzebne. Ponadto, jak wcześ- niej wspomniano, możliwe jest grupowanie i filtrowanie wyników tabeli przestawnej, aby przetwarzać jedynie żądane dane. Tabele przestawne dostosowują się do wymagań Choć wiele tworzonych tabel przestawnych będziemy wykorzystywać do własnych celów, prawdopodobnie okaże się, że część z nich będzie zdefiniowana dla innych osób, aby mogły je przeglądać na ekranie, po wydruko- waniu, a nawet udostępnieniu w internecie (należy za- poznać się z zadaniem „Publikowanie tabeli przestawnej na stronie internetowej” zamieszczonym w rozdziale 3.). W sytuacji gdy tabela przestawna ma być udostępniona większej grupie osób, zwykle będziemy dążyć do tego, aby wyglądała jak najlepiej. W związku z tym Excel wy- posaża tabele przestawne w kilka opcji umożliwiających użytkownikowi formatowanie i dostosowywanie ich do własnych wymagań. Dostosowywanie Każda tabela przestawna posiada kilka opcji, za pomo- cą których można dostosowywać jej raport w całości lub jego poszczególne elementy. Przykładowo, można ukrywać pozycje, sortować dane i modyfikować wy- gląd wydruku raportu. Można również dostosowywać obliczenia użyte w raporcie przez modyfikację jednego z wbudowanych obliczeń Excela lub zdefiniowanie nie- standardowych obliczeń. Aby uzyskać dodatkowe in- formacje na temat niestandardowych obliczeń, należy zajrzeć do rozdziału 8. Formatowanie Gdy tabela przestawna podaje żądany wynik, trochę czasu można poświęcić na dostosowanie wyglądu ra- portu, tak aby przeglądanie danych było wygodniejsze. Na szczęście większość komórek tabeli przestawnej za- chowuje się jak zwykłe komórki arkusza Excela. Ozna- cza to, że można je formatować w identyczny sposób — poprzez zmianę czcionki, stosowanie kolorów i ra- mek, używanie formatów liczbowych i daty itp. Więcej informacji na temat dostosowywania pól tabel prze- stawnych można znaleźć w rozdziale 5. Excel_tabele_i_wykresy_przestawn7 7 Excel_tabele_i_wykresy_przestawn7 7 7 2006-10-10 21:44:45 2006-10-10 21:44:45 Przegląd zastosowań tabeli przestawnej W przypadku korzystania z narzędzi Excela służących do analizy danych kluczowe jest orientowanie się, którego z nich użyć i w ja- kich okolicznościach. Jeśli zamierzamy zebrać jeden lub dwa fakty dotyczące danych, jedna lub dwie formu- ły często będą wszystkim, co będzie do tego potrzebne. W przypadku bardziej drobiazgowych wymagań, zwłaszcza gdy trzeba utworzyć arkuszową wersję ja- kiegoś rzeczywistego zagadnienia, niezbędny jest mo- del danych. Jeśli zależy nam na „przepytaniu” danych przez umieszczenie w formule różnych wartości i po- równanie uzyskanych wyników, najlepsza będzie tabela danych. Jeżeli szukamy określonego lub optymalnego wyniku, w przypadku prostych modeli należy zastoso- wać narzędzie Goal Seek, natomiast dla bardziej złożo- nych modeli odpowiedni będzie dodatek Solver. Z tabel przestawnych najlepiej korzystać tylko w nie- których sytuacjach. Przypadki, w których tabela prze- stawna będzie najlepszym narzędziem analizy danych lub przynajmniej wartym rozważenia, można podzielić na trzy kategorie — struktura danych, żądany typ ana- lizy i wymagania (użytkownika i jego przełożonego) dotyczące raportów. Struktura danych Od każdego innego czynnika struktura danych w więk- szym stopniu określa, czy tabela przestawna jest dobrym narzędziem analizy danych. Określonego typu dane po prostu nie mogą być analizowane za pomocą tabeli prze- stawnej, ponieważ w ich przypadku zostałyby wygenero- wane przeważnie bezużyteczne wyniki. Zwykle strukturą danych najlepiej nadającą się do zastosowania z tabelami przestawnymi jest taka, w której dane są przechowywane w formacie tabelarycznym, a ponadto są spójne i powta- rzające się. Takim przykładem jest struktura transakcyjnych baz danych. W celu uzyskania dokładniejszych informacji dotyczących przygotowywania danych pod kątem raportu tabeli przestawnej należy zapoznać się z podrozdziałem „Przygotowanie danych arkusza” zawartym w rozdziale 2. Dane transakcyjne Idealnym typem, w przypadku którego skorzystamy z analizy opartej na tabeli przestawnej, są dane trans- akcyjne powiązane z częstymi spójnymi wymianami informacji. Typowe przykłady danych transakcyjnych to zamówienia klientów, należności do zapłaty, wyniki do- świadczeń, sumy magazynowe, sprzedaż produktów, odpowiedzi na ankiety i harmonogramy produkcji. Dane transakcyjne mają taką samą strukturę w przypadku każ- dego rekordu i są spójne. Ponadto, w przynajmniej jed- nym polu powtarzają się wartości. Wszystko to sprawia, że dane transakcyjne idealnie nadają się do zastosowania tabel przestawnych. Dane tabelaryczne Jeśli dane są przechowywane w formacie tabelarycznym, są dobrym przykładem, na którym można zastosować analizę wykorzystującą tabelę przestawną. Oznacza to, że dane są uporządkowane za pomocą struktury złożo- nej z wierszy i kolumn. Każdy wiersz ma taką samą liczbę kolumn. Jeśli dane są porozrzucane po całym arkuszu i nie można ich uporządkować za pomocą formatu tabelarycz- nego, na ich bazie nie można tworzyć tabeli przestawnej. Spójne i powtarzające się dane Użycie analizy danych opartej na tabeli przestawnej po- winno się rozważyć, gdy dane tabelaryczne zawierają też spójne i powtarzające się wartości. Przez spójność wartości rozumie się to, że każda kolumna zawiera tego samego typu dane zapisane w identycznym formacie. Przykładowo, w pierwszej kolumnie znajdują się wyłącz- nie nazwiska klientów, w drugiej tylko daty zamówień, a w trzeciej kwoty faktur. Powtarzające się wartości ozna- czają, że przynajmniej jedna kolumna przechowuje ogra- niczoną liczbę wartości powtarzających się w rekordach. Przykładowo, kolumna Region może zawierać tylko cztery wartości — Wschód, Zachód, Północ i Południe. Wartości te są powtarzane w setkach lub tysiącach rekordów. 8 Excel_tabele_i_wykresy_przestawn8 8 Excel_tabele_i_wykresy_przestawn8 8 2006-10-10 21:44:45 2006-10-10 21:44:45 R o z d z i a ł 1 . T a b e l e p r z e s t a w n e Żądany typ analizy Decydując, czy na podstawie danych warto utworzyć tabelę przestawną, należy się zastanowić nad typem wy- maganej analizy. Do jakiego celu dążymy? Czego trzeba się dowiedzieć? Jaka tajemnica prawdopodobnie skrywa się w danych? Ogólnie mówiąc, tworzenie tabeli prze- stawnej jest dobrym pomysłem, gdy w ramach analizy danych szukamy jednej lub więcej następujących rzeczy: listy unikatowych wartości pola, podsumowywania dużej ilości danych, relacji między dwoma polami lub większą ich liczbą i trendu danych w jakimś okresie. Unikatowe wartości Mając do czynienia z ogromną ilością danych, można stwierdzić, że jedną z pierwszych rzeczy, którą chciało- by się uzyskać z danych, jest lista unikatowych wartości określonego pola. Przykładowo, w przypadku bazy da- nych zawierającej tysiące zamówień po prostu chciało- by się wiedzieć, którzy klienci je złożyli. Najlepszym roz- wiązaniem umożliwiającym to jest tabela przestawna, ponieważ wyodrębnianie listy niepowtarzalnych war- tości występujących w polu jest jedną z operacji, które wykonuje ona najlepiej. Relacje Jednym z największych problemów, z którymi mamy do czynienia podczas przetwarzania dużego zbioru danych, jest określenie relacji istniejących między dwo- ma polami. Którzy klienci kupują produkty i jakie? Jak wygląda kwestia wadliwych produktów w zależności od fabryki? Tabele przestawne idealnie nadają się do tego typu analizy, ponieważ dokonują podziału wartości jednego pola z uwzględnieniem drugiego. Przykładowo, można wyświetlić całkowitą wartość sprzedaży gene- rowanej przez każdego przedstawiciela handlowego, a następnie dane te podzielić według klientów, państwa, produktu, kategorii itd. Podsumowywanie Analiza danych często oznacza podsumowywanie ich w określony sposób. Może to polegać na sumowaniu danych, zliczaniu, wyznaczaniu średniej, szukaniu mak- symalnej wartości itp. Excel posiada funkcje arkuszowe, sumy częściowe i inne narzędzia przeznaczone do tego typu analizy. Jednak żadne z nich nie nadaje się do pod- sumowywania dużej ilości danych, zwłaszcza gdy zale- ży nam na przeglądnięciu wyników w postaci zwartego raportu. Aby było to możliwe, trzeba utworzyć tabelę przestawną. Trendy Jeśli dane zawierają pole daty lub czasu, interesujące może być stwierdzenie, jak zmienia się w czasie określo- ne pole. Analiza trendu może być wyjątkowo przydat- na. Excel posiada kilka bogatych w możliwości narzędzi ułatwiających identyfikację trendu. Jednakże tabela przestawna będzie znakomitą propozycją, gdy zamie- rzamy podsumować jedno pole i dokonać jego podziału według daty lub czasu. Jak wartość sprzedaży zmienia się w całym roku? Jak liczba wadliwych produktów zmienia się w ciągu dnia lub tygodnia? Wymagania dotyczące raportów Ostatnią kwestią, którą należy rozpatrzyć przy decydo- waniu o tym, czy analizować dane za pomocą tabeli prze- stawnej, jest stwierdzenie, jakie są wymagania dotyczące raportów. Inaczej mówiąc, jaki ma być końcowy rezultat analizy. Jeśli zamierzamy uzyskać raport, który jest ela- styczny i można go z łatwością często modyfikować, na- leży skorzystać z tabeli przestawnej. Elastyczność Jeżeli zależy nam na elastyczności umożliwiającej szyb- kie i proste modyfikowanie raportu, w celu przeprowa- dzenia analizy danych należy zdefiniować tabelę prze- stawną. Jeśli konieczna jest zmiana układu, na przykład z pionowego na poziomy, można przestawić dowolne pole, wykonując myszą operację kliknięcia i przeciągnię- cia. Jeśli trzeba przeglądać podzbiory wyników, można zastosować filtrowanie raportu na podstawie wartości określonego pola. Częste zmiany Jeśli uważamy, że dane będą często modyfikowane, na- leży zdefiniować tabelę przestawną. Aby użyć najnow- szych danych, z łatwością można uaktualniać tabelę przestawną. Dzięki temu raport zawsze będzie dokładny i aktualny. Ponieważ równie prosta jest zmiana struktury tabeli przestawnej (przez dodanie nowego pola, które umieszczono w oryginalnych danych), zawsze można uwzględnić w niej nowe dane. Excel_tabele_i_wykresy_przestawn9 9 Excel_tabele_i_wykresy_przestawn9 9 9 2006-10-10 21:44:45 2006-10-10 21:44:45 Omówienie elementów tabeli przestawnej P o zapoznaniu się z kilkoma kluczowymi zagad- nieniami bardzo szybko będzie można korzystać z tabel przestawnych. Konieczne jest zrozumie- nie elementów tworzących typową tabelę przestawną, a zwłaszcza czterech obszarów — wiersza, kolumny, danych i strony. W obszarach tych umieszcza się pola znajdujące się w oryginalnych danych. Dodatkowo trzeba opanować kilka ważnych terminów związanych z tabelami przestawnymi, które będą stoso- wane w całej książce. Są to takie terminy jak dane źródło- we, bufor tabeli przestawnej i obliczenie podsumowujące. OBSZAR STRONY A W obszarze jest wyświetlana rozwijana lista zawierająca unikatowe wartości pola. Po wybraniu wartości z listy Excel przefi ltruje wyniki tabeli przestawnej, aby uwzględnić tylko te rekordy, które są zgodne z określoną wartością. OBSZAR KOLUMNY B W obszarze w poziomie są wyświetlane unikatowe wartości pochodzące z pola danych źródłowych. OBSZAR WIERSZA C W obszarze w pionie są wyświetlane unikatowe wartości pochodzące z pola danych źródłowych. F E G A B C D POZYCJE POLA G Unikalne wartości pola umieszczonego w określonym obszarze. F PRZYCISK POLA DANYCH Identyfi kuje zarówno obliczenie (na przykład wykonane przez funkcję Suma), jak i pole (na przykład Całkowita kwota faktury) użyte w obszarze danych. E PRZYCISK POLA Identyfi kuje pole zawarte w obszarze. Za pomocą przycisku pola można też przemieścić pole z jednego obszaru do drugiego. D OBSZAR DANYCH W obszarze są umieszczone wyniki obliczenia, które Excel wykonał dla liczbowego pola danych źródłowych. 10 Excel_tabele_i_wykresy_przestawn10 10 Excel_tabele_i_wykresy_przestawn10 10 2006-10-10 21:44:46 2006-10-10 21:44:46 R o z d z i a ł 1 . T a b e l e p r z e s t a w n e Słownik terminów związanych z tabelami przestawnymi Tabele przestawne mają własne terminy, z których wiele może nie być znanych Czytelnikowi, nawet jeśli ma on duże doświadczenie w zakresie obsługi Excela. Aby szyb- ciej opanować tabele przestawne, powinniśmy się zapo- znać nie tylko z wcześniej zamieszczonymi terminami, ale też pojęciami zawartymi w poniższym słowniku. Dane źródłowe Oryginalne dane, na bazie których utworzono tabe- lę przestawną. Danymi źródłowymi mogą być między innymi zakres lub lista Excela, tabela programu Access, tabela Worda, plik tekstowy, strona internetowa, plik XML, dane znajdujące się na serwerze SQL Server lub OLAP. Dane zewnętrzne Dane źródłowe, które pochodzą z pliku lub bazy da- nych, a nie z arkusza Excela. W celu zaimportowania danych zewnętrznych do arkusza Excela można posłu- żyć się programem Microsoft Query (więcej informacji zawarto w dodatku B). Można też użyć innych narzędzi Excela umożliwiających importowanie danych (należy zajrzeć do dodatku C). Bufor tabeli przestawnej Są to dane źródłowe, które Excel przechowuje w pamię- ci w celu zwiększenia wydajności tabel przestawnych. Pole zewnętrzne i wewnętrzne Gdy w obszarze wiersza lub kolumny znajduje się wiele pól (należy zapoznać się z zadaniem „Dodawanie wie- lu pól do obszaru wiersza lub kolumny” z rozdziału 3.), Excel umieszcza je jedno za drugim (dotyczy to obsza- ru wiersza) lub jedno pod drugim (dotyczy to obszaru kolumny). W obu przypadkach pole najbliższe obszaru danych jest nazywane polem wewnętrznym, natomiast to najbardziej oddalone od tego obszaru — polem zewnętrznym. Obszar upuszczania Region tabeli przestawnej, w którym można umieścić pole pochodzące z danych źródłowych lub z innego ob- szaru tabeli. Każdy obszar upuszczania Excel wyróżnia niebieską ramką. Przestawianie Przemieszczenie pola z jednego obszaru upuszczania tabeli przestawnej do drugiego. Etykiety Elementy tabeli przestawnej zlokalizowane w obszarze bez danych. Etykietami są przyciski pola, pozycje pola i rozwijana lista obszaru strony. Dane Wyznaczone wartości umieszczane w obszarze danych. Obliczenie podsumowujące Operacja matematyczna, którą Excel wykonuje dla warto- ści pól liczbowych w celu wygenerowania podsumowania widocznego w obszarze danych. Excel oferuje 11 wbudo- wanych funkcji podsumowujących: Suma, Licznik, Średnia, Max, Min, Iloczyn, Licznik num., OdchStd (próbka), StDevp (populacja), War (prób- ka) i Warc (populacja). Więcej informacji na temat tych funkcji można znaleźć w rozdziale 7. Możliwe jest też tworzenie niestandardowych funkcji podsumowujących (należy zajrzeć do rozdziału 8.). Excel_tabele_i_wykresy_przestawn11 11 Excel_tabele_i_wykresy_przestawn11 11 11 2006-10-10 21:44:46 2006-10-10 21:44:46 Wykresy przestawne — wprowadzenie G dy rozpoczyna się proces tworzenia tabeli prze- stawnej, w rzeczywistości Excel umożliwia zdefi - niowanie tabeli lub wykresu przestawnego. Ogól- nie rzecz biorąc, wykres przestawny jest tym dla tabeli przestawnej, czym zwykły wykres dla zakresu. Oznacza to, że pierwsze jest grafi czną reprezentacją drugiego. W związku z tym wykres przestawny umożliwia wizua- lizację wyników tabeli przestawnej przez wyświetlanie w postaci wykresu wartości obszaru danych. M
Pobierz darmowy fragment (pdf)

Gdzie kupić całą publikację:

Excel. Tabele i wykresy przestawne. Przewodnik po tworzeniu dynamicznych arkuszy kalkulacyjnych
Autor:

Opinie na temat publikacji:


Inne popularne pozycje z tej kategorii:


Czytaj również:


Prowadzisz stronę lub blog? Wstaw link do fragmentu tej książki i współpracuj z Cyfroteką: