Tabele przestawne - Excel

Autor: Elżbieta Jaworska

W świecie dużych baz danych potrzebne są narzędzia, które ogromne ilości danych przedstawią w przejrzystej formie, zilustrują graficznie, pozwolą wyciągnąć ogólne wnioski. Arkusze pokazują zwykle zależności w dwóch wymiarach (np. kwoty wydatków w rozbiciu na poszczególne kategorie budżetu). Tabela przestawna Excela umożliwia oglądanie tych samych danych pod różnymi kątami - jakby w kilku wymiarach.

Posługiwanie się tabelą przestawną polega na przeciąganiu kolumn danych do wierszy, wierszy do kolumn itd., aby uwidocznić wszelkie możliwe zależności między danymi. Łatwo nią manipulować za pomocą zwykłego przeciągania myszą - stąd pochodzi nazwa: tabela przestawna.

Tworzenie tabeli przestawnej

Musimy przygotować bazę danych dla tabeli przestawnej, czyli wpisać dane w kolumny opatrzone etykietami (w tabeli przestawnej będą to przyciski). Należy unikać wprowadzania sum do komórek tabeli - wszystkie operacje na danych najlepiej przeprowadzać w samej tabeli. Załóżmy, że chcemy ocenić efekty pracy dwóch akwizytorów. Przykładową bazę danych przedstawia rysunek 1.

Rys. 1. Przykładowa baza danych, czyli lista tabeli przestawnej

Zaznaczamy obszar, z którego powstanie tabela przestawna. Nie wolno ominąć etykiet! W menu Dane wybieramy polecenie Raport tabeli przestawnej (w Excelu 2000 - Raport tabeli przestawnej i wykresu przestawnego), pojawi się pierwszy z czterech ekranów Kreatora tabel przestawnych. W wersji Excel 2000 Kreator "robi 3 kroki", w rzeczywistości jednak krok 3. jest podwójny - pod klawiszem Układ ukryto dostęp do kolejnego ekranu, który jest odpowiednikiem ekranu 3. z Excela 97.

Wyświetla się pierwszy ekran Kreatora. Automatycznie uaktywniana jest opcja Lista Lub Baza Danych Microsoft Excel (są też inne możliwości: import danych z zewnętrznych źródeł, z wielu skonsolidowanych zakresów lub z innej tabeli przestawnej). Zakres danych z tabeli źródłowej możemy zmienić w drugim oknie Kreatora, wpisując nowy (adresy bezwzględne!), zaznaczając zakres na arkuszu lub używając przycisku Przeglądaj.

Rys. 2. Ekran 3. Kreatora tabel przestawnych

Najważniejszy etap tworzenia tabeli przestawnej to krok trzeci (rys. 2), czyli okno ustawiania układu tabeli (w Excelu 2000 wciskamy przycisk Układ na ekranie 3.). Tu podejmowane są decyzje o budowie tabeli przestawnej, czyli o przekazywanych przez nią informacjach. Przyciski po prawej stronie okna reprezentują wszystkie pola bazy danych. Przeciągając je myszką, możemy budować dowolne zestawienia. Wartości pola Wiersza będą tytułami rubryk poziomych tabeli, wartości pola Kolumny -- pionowych. Wartości pola Danych wyświetlą się na przecięciu odpowiednich wierszy i kolumn. Pole Strony jest filtrem tabeli.

Ustawienia można w każdej chwili zmienić, przeciągając przycisk na inne miejsce (okno w lewym górnym rogu pokazuje sposób manipulowania przyciskami) lub poza aktywne pola. Do każdego obszaru można przeciągnąć dowolną liczbę przycisków. Dopuszczalne są także obszary bez przycisków, z wyjątkiem obszaru Dane - tu musi się znaleźć przynajmniej jedno pole, inaczej tabela traci sens.

W naszej przykładowej tabeli umieścimy Produkt w polu Strony, Akwizytora - w obszarze Wiersz, Kwartał - w Kolumnie. Przycisk Wartość przeciągnijmy do obszaru Dane, napis zmieni się wtedy na Suma: Wartość.

Ostatnie okno kreatora pozwala wybrać miejsce umieszczenia tabeli. Jeśli wybierzemy Istniejący arkusz, klikamy w zakładkę wybranego arkusza, a potem w komórkę, w której ustawimy lewy górny narożnik tabeli. W tym oknie można też sformatować tabelę. Najczęściej wystarczy sprawdzić, czy aktywne są interesujące nas opcje, np. Sumy całkowite kolumn, Sumy całkowite wierszy, Autoformatowanie tabeli. Naszą tabelę przestawną pokazuje rys. 3.

Rys. 3. Tabela przestawna ilustrująca opisany przykład

Podstawowe definicje
Lista	baza danych, na podstawie której tworzona jest tabela przestawna. Każda kolumna ma w nagłówku etykietę danej kategorii.
Pole	każda kolumna danych na liście Excela.
Rekord	każdy wiersz danych na liście Excela.
Dane źródłowe	dane tworzące listę lub listy, na których oparta jest tabela przestawna.
Przyciski tabeli przestawnej	etykiety wierszy i kolumn, które można przeciągać na inne pozycje.
Funkcje podsumowujące	obliczenia wykonywane przez Excela na danych tabeli przestawnej (najczęściej sumowanie cząstkowe i sumowanie sum cząstkowych w sumę łączną).
Pole stron	filtr dla tabeli przestawnej. Rubryki mogą być rozbite według określonych instrukcji na oddzielne grupy, tzw. strony, wyświetlane pojedynczo.
Grupowanie	podział kategorii danych i zmiana tych podziałów.

Możemy teraz ocenić efekty pracy akwizytorów w poszczególnych kwartałach (dolny wiersz pokazuje sumaryczne wyniki) i w całym roku (ostatnia kolumna tabeli). Pole Wartość z listy tabeli przestawnej nie jest pokazane w formie przycisku. Zapis Suma: Wartość oznacza, że w tabeli występują sumowane wartości odpowiednich pozycji kolumny Wartość źródłowej bazy danych. Gdybyśmy chcieli porównać wyniki uzyskane dla konkretnego artykułu, wystarczy rozwinąć listę wartości pola Strony.

Manipulowanie tabelą przestawną

Strukturę tabeli przestawnej można zmieniać: bezpośrednio w arkuszu, za pomocą Kreatora tabel przestawnych lub za pomocą paska narzędzi Tabela przestawna.

Najbardziej widowiskowe są zmiany bezpośrednio w arkuszu roboczym, przeciąganiu przycisków towarzyszą bowiem ilustracje graficzne. Na przykład, gdy ciągniemy nagłówek kolumny trochę w dół i w lewo, obok kursora pojawia się niebieski miniaturowy kontur tabeli (w Excelu 2000 - szkic tabeli z zaznaczonym na niebiesko paskiem symbolizującym przesuwane pole). Gdy zmieni orientację z poziomej na pionową, zwalniamy uchwyt - tabela ma teraz inny wygląd (rys. 4). Przeciąganiu jakiegoś przycisku w kierunku pola Strony towarzyszy obrazek schodków.

Rys. 4. Tabela przestawna z rys. 3 w bardziej przejrzystej formie

Zmiana struktury tabeli za pomocą Kreatora przebiega tak jak tworzenie tabeli. Wystarczy zaznaczyć dowolną komórkę w tabeli przestawnej, wydać polecenie Dane > Raport tabel przestawnych - wyświetli się od razu trzecie okno dialogowe Kreatora. Zbędne przyciski przeciągamy poza obręb tabeli - znikają, a na ich miejsce przeciągamy nowe.

Pasek narzędziowy Tabela przestawna to podręczny zbiór opcji Kreatora. W wersji Excel 2000 pasek tabeli jest rozbudowany - gdy uaktywnimy jakąś komórkę, pokazuje wszystkie pola źródłowej bazy danych (rys. 3). To dobry sposób na wprowadzenie dodatkowego pola do tabeli przestawnej bezpośrednio w arkuszu. Na przykład, gdybyśmy chcieli porównać wyniki sprzedaży z dwóch ostatnich lat, w pole danych przeciągniemy przycisk Sprzedaż.

Różne modyfikacje tabeli przestawnej

Sposoby prezentowania danych w tabeli przestawnej:
Różnica	w obszarze danych pokazana jest różnica wybranego Pola Odniesienia i Pozycji Odniesienia (pole i pozycję wybiera się z list wyświetlonych po wybraniu tego sposobu).
% Z	w obszarze danych pojawiają się wartości procentowe w odniesieniu do wybranego Pola Odniesienia i Pozycji Odniesienia.
% Różnicy	analogicznie jak w Różnicy, tylko wyrażone procentowo w stosunku do wybranych danych bazowych.
Suma Bieżąca W	suma wskazanych pól danych.
% Wiersza	procent sumy wszystkich wartości w danym wierszu.
% Kolumny	jw. w odniesieniu do kolumny.
% Sumy	dane jako procent sumy wszystkich wartości w całej tabeli przestawnej.
Indeks	dane przeliczane ze wzoru: ((wartość w komórce) x (suma całości))/((suma w danym wierszu) x (suma w danej kolumnie)).

Zmiana danych w bazie źródłowej - musimy odświeżyć tabelę przestawną (sama nie zareaguje). Wystarczy uaktywnić jakąś komórkę tabeli i wydać polecenie Dane > Odśwież dane (lub wcisnąć ikonę ! w pasku narzędziowym).

Usuwanie pól - przeciągamy przycisk poza obszar tabeli. Gdy kursor zmieni się w przekreślony poziomy pasek, zwalniamy przycisk i pole znika.

Grupowanie i rozgrupowywanie pól - zaznaczamy grupę komórek z jednej kategorii i klikamy w ikonę Grupuj w pasku narzędzi lub w menu Dane > Grupy i konspekt > Grupuj. W Excelu 2000 wyświetla się wtedy okno dialogowe Grupowanie, w którym można ustawić zakres grupy. Polecenie Rozgrupuj przywraca tabeli poprzednią postać.

Ukrywanie szczegółów - po zaznaczeniu przycisku pola należy kliknąć w ikonę Ukryj Szczegóły w pasku narzędziowym (chowają się również sumy cząstkowe i całkowite dla tego pola). Ikona Pokaż szczegóły wykonuje operację odwrotną.

Dołączanie dalszych pól do analizy - za pomocą Kreatora, a w Excelu 2000 - bezpośrednio z paska narzędzi.

Zmiana formatu liczb - zaznaczamy komórki i klikamy PPM, z podręcznego menu wybieramy polecenie Formatuj komórki. Komórki z danymi przestawione na nowe miejsca tracą wcześniejszy format, ale po powrocie odzyskują go.

Sortowanie pól - alfabetyczne lub numeryczne, w porządku rosnącym lub malejącym - za pomocą ikony Sortuj.

Zmiana nazw pól - są 2 sposoby. Można wprowadzić zmiany w bazie źródłowej, wrócić do Kreatora i powtórzyć procedurę tworzenia tabeli, wprowadzając w kroku 2. ponownie zakres z danymi. Można też modyfikować bezpośrednio tabelę (oryginalna baza danych nie zmieni się). Trzeba uaktywnić przycisk pola i przeedytować jego nazwę w pasku formuły albo w komórce z przyciskiem, a potem Odświeżyć dane.

Usunięcie tabeli - zaznaczyć całą tabelę, w menu Edycja wybrać polecenie Wyczyść i Wszystko.

Kopiowanie - podobnie jak komórki, zaznaczamy zakres i przesuwamy w inne miejsce.

Obliczanie pola danych - sumowanie nie jest jedyną możliwą operacją. Gdy dwukrotnie klikniemy przycisk umieszczony w polu Dane, pojawi się okienko dialogowe Pole tabel przestawnych (można też zaznaczyć komórkę z wybranego pola danych, kliknąć PPM i w podręcznym menu wydać polecenie Pole, w Excelu 2000 - Ustawienie pola). W obszarze Podsumowanie według wybiera się jedną z jedenastu operacji matematycznych lub statystycznych (patrz ramka). Na przykład, chcemy sprawdzić, jakie maksymalne miesięczne obroty osiągnęli obaj panowie - naciskamy Max, najgorsze wyniki - Min. Przycisk Opcje pozwala wybrać sposób prezentacji danych.

Możliwe operacje tabeli przestawnej
Suma	funkcja sumująca domyślnie używana dla wszystkich pól numerycznych.
Licznik	podaje liczbę rekordów.
Średnia	oblicza średnią arytmetyczną odpowiednich wartości.
Max	podaje największą wartość spośród odpowiednich danych.
Min	najmniejsza wartość spośród odpowiednich danych.
Iloczyn	mnożenie wartości.
Licznik Num.	liczba rekordów zawierających dane liczbowe.
OdchStd	oblicza odchylenie standardowe próbki z populacji danych.
OdchStdc	odchylenie standardowe całej populacji danych.
War	wariancja próbki z populacji danych.
Warc	wariancja całej populacji danych.

Z ciekawostek warto jeszcze wspomnieć o tym, że gdy dwukrotnie klikniemy w komórkę tabeli przestawnej, w bieżącym skoroszycie pojawi się nowy arkusz z danymi dotyczącymi wybranej komórki.

Co nowego w Excelu 2000?

Prawdziwą nowością jest tworzenie wykresów przestawnych dla już istniejącej tabeli lub równolegle z nią. Wykresy te nie mogą istnieć samodzielnie. Każda zmiana w tabeli jest natychmiast odzwierciedlana na wykresie.

Dla konkretnej tabeli wystarczy w pasku narzędziowym Tabela przestawna uruchomić Kreator wykresów. Excel w osobnym arkuszu umieści standardowy, skumulowany wykres kolumnowy, w którym pola wierszy tabeli staną się polami kategorii na osi X, pola kolumn - seriami danych opisanymi w legendzie, pola stron zachowają rolę filtrów, wartości pola danych odłożone będą na osi Y.

Rys. 5. Wykres przestawny sprzężony z tabelą

Jeśli podzielimy ekran na 2 okna i w jednym umieścimy wykres przestawny, a w drugim tabelę (rys. 5), możemy sprawdzić ścisłe związki tabeli z wykresem. Na przykład, jeśli w tabeli przestawnej zamienimy miejscami pola Produkt i Kwartał, otrzymamy wykres, w którym na osi X znajdą się kwartały, a produkty staną się seriami danych wyjaśnionymi w legendzie (rys. 6).

Rys. 6. Każda zmiana w tabeli natychmiast generuje zmianę wykresu

Myślę, że te informacje wystarczą, aby każdy mógł stworzyć własną tabelę przestawną i wykres. Bardziej dociekliwych odsyłam do odpowiednich podręczników.

Polecana literatura

• Laura Maery Gold, Dan Post, "Excel 97 nie tylko dla orłów", tłumaczenie: Władysław Szymczyk, Intersoftland 1997

• Stefan Uss, "Excel 97 PL. Pierwsza pomoc", Komputerowa Oficyna Wydawnicza HELP, Warszawa 1997

• Stefan Uss, "Excel 2000 PL. Pierwsza pomoc", Komputerowa Oficyna Wydawnicza HELP, Warszawa 1999

Tabela przestawna

Niezwykle silnym narzędziem przetwarzania całych zespołów danych w Excelu jest tabela przestawna. Z kolei wykresy pozwalają na syntetyczne i poglądowe przedstawienie danych. Oto przykłady.

Tabela przestawna

Chcemy uzyskać szybko informację jakie imiona wybierają najczęściej rodzice dla swoich dzieci. Zajmiemy się najpierw dziewczynkami. Posortujemy oseski według płci i imienia 1. Następnie, po włączeniu autofiltru, wybierzemy do dalszej obróbki tylko dziewczynki. Już przegląd tabeli z danymi daje pewne informacje - możemy sprawdzić, że imię Anna nadano tylko jednej dziewczynce, a imię Hanna wogóle nie występuje. Jakie imiona przodują? Pełną informację uzyskamy dzięki tabeli przestawnej. Stajemy kursorem w obrębie danych i z menu Dane wybieramy Raport tabeli przestawnej. Teraz z pomocą przychodzi kreator (w angielskiej wersji nazywany czarodziejem). W pierwszym kroku (Gdzie znajdują się dane, które chcesz analizować?) wybieramy opcję Lista lub baza danych Microsoft Excel i przechodzimy dalej. Drugi krok (Gdzie znajdują się dane, które chcesz wykorzystać?) zazwyczaj nie sprawia żadnych trudności gdyż Excel rozpoznaje obszar danych podobnie jak podczas sortowania. Gdyby jednak tak się nie stało należy przejść do arkusza i przeciągnąć kursorem zaznaczając cały obszar danych. Zostanie on wpisany do okienka Zakres. Przycisk Dalej przenosi nas do trzeciego kroku. Tutaj konstruujemy tabelę: przeciągamy nazwy kolumn (pól) znajdujące się po prawej stronie w odpowiednie miejsca narysowanej tabeli - Imię 1 do miejsca oznaczonego WIERSZ oraz jakąś kolumnę, która jest całkowicie wypełniona danymi (np. Nazwisko) do środka tabeli oznaczonego DANE (porównaj obrazek poniżej). W ostatnim - czwartym kroku najlepiej wybrać nowy arkusz jako miejsce umieszczenia tabeli i kliknąć przycisk Zakończ.
Teraz wystarczy posortować utworzoną właśnie tabelę według kolumny suma zawierającej liczebność występowania imion i mamy tabelę taką jak obok - widzimy, że zdecydowanie najpopularniejszym imieniem dla dziewczynek jest Weronika! Pora wykonać to samo dla chłopców, wracając najpierw do pełnego zestawu danych.

Teraz utworzymy jeszcze jedną tabelę przestawną pokzującą rozkład wzrostu osesków. Robiliśmy to poprzednio wykorzystując funkcję tablicową CZĘSTOŚĆ. Zastosowanie tabeli przestawnej jest rozwiązaniem bardziej elastycznym gdyż możemy wziąć do obróbki dodatkową zmienną - płeć i uzyskać rozkłady wzrostu chłopców i dziewczynek. Pierwsze kroki tworzena nowej tabeli przestawnej są identyczne jak poprzednio, dopiero w trzecim kroku, gdy przystępujemy do układania tabeli trzeba przeciągnąć do wiersza Wzrost i do kolumny Płeć. W polu dane można - jak poprzednio - umieścić Nazwisko. Po zakończeniu procedury otrzymamy następującą tabelę
Arkusz wykonał za nas wiele pracy - mamy nie tylko rozkład wzrostu wszystkich osesków ale dodatkowo rozkłady dla dziewcząt (c) i chłopców (s).

 

Wykresy

Teraz wystarczy zaznaczyć prawie całą tabelę (bez pierwszego i ostatniego wiersza i wywołać kreatora wykresów. Jeśli się trochę postaramy to wynik może wyglądać następująco:

---