213

Excel – tabele przestawne

11. Tabele przestawne

11.1. Wprowadzenie

Arkusz kalkulacyjny jest zwykle postrzegany, jako narzędzie do wykonywania prostych lub bardziej zaawansowanych obliczeń, z wykorzystaniem wbudowanych funkcji lub własnych formuł, tworzonych przez użytkownika. Arkusz może jednak w znakomity sposób pełnić rolę bazy danych.

W poprzednich rozdziałach zostały przedstawione przykłady prezentacji danych za pomocą konsolidacji i sum pośrednich. Tabele przestawne to jeszcze jedno narzędzie umożliwiające obsługę i zarządzanie danymi.

Tabele przestawne łączą w sobie wszystkie zalety konsolidacji i sum pośrednich, są jednak bardziej elastyczne. Pozwalają, bowiem z dużych baz danych wybrać i wyświetlić informacje potrzebne do prezentacji analizowanego zjawiska, dowolnie zmieniać układ danych i poziom wyświetlania szczegółów.

Najistotniejszą cechą tabel przestawnych jest ich trójwymiarowość. W zwykłych tabelach wartości wyrażają jedynie zależności między elementami ułożonymi w kolumny i wiersze; w tabelach przestawnych istnieje możliwość odnajdowania i prezentowania również innych powiązań.

Modyfikowanie układu tabeli odbywa się w prosty sposób – przez zwykłe przeciąganie odpowiednich pól myszką. W ten sposób można szybko przestawiać poszczególne elementy np. zamieniać dane ułożone w wierszach w kolumny i odwrotnie, można dodawać nowe pola lub usuwać zbędne. Pole Strona daje możliwość filtrowania danych, co jest dodatkowym atutem przy prowadzaniu różnych analiz.

11.2. Tworzenie tabeli przestawnej

Tabela danych musi być opatrzona odpowiednimi nagłówkami kolumn, które będą przyciskami w tabeli przestawnej. Tworząc tabelę danych należy unikać scalania komórek w wierszu nagłówkowym oraz wprowadzania sum do komórek - wyliczenia pojawią się po odpowiednim zdefiniowaniu tabeli przestawnej.

Konstrukcja wiersza nagłówkowego, widoczna na rys 11.1 uniemożliwiłaby utworzenie tabeli przestawnej, dlatego tabelę należy skonstruować wg rys 11.2.

Rysunek 11.1. Nieprawidłowy wiersz nagłówkowy (wprowadzone scalanie komórek)

Ćwiczenie 11.1

Przygotować bazę danych przedstawioną na rysunku 11.2.

Zapisać tabelę w pliku o nazwie Tabela przestawna.xls

W komórce F2 należy wprowadzić formułę obliczającą Sprzedaż wartość =D2*E2, zatwierdzić i skopiować do zakresu F3:F31. W komórce I2 należy obliczyć Skup wartość, korzystając z formuły =G2*H2 i po zatwierdzeniu skopiować do zakresu I3:I31

Rysunek 11.2. Tabela danych

Ćwiczenie 11.2

Przeprowadzić analizę sprzedaży wszystkich walut w poszczególnych dniach.

1. Uaktywnić dowolną komórkę w tabeli danych i wybrać z menu Dane funkcję Raport tabeli przestawnej i wykresu przestawnego. W efekcie pojawi się pierwsze z trzech okien dialogowych. Krok pierwszy to wybór źródła danych oraz rodzaju raportu. Użytkownik ma do wyboru:
2. 1. Listę lub bazę danych Microsoft Excel, przygotowaną w bieżącym skoroszycie lub innym skoroszycie zapisanym na dysku,

b.       Zewnętrzne źródło danych, czyli bazę przygotowaną za pomocą innego programu np. Dbase, Fox-Pro, Access,

3. 3. Konsolidację wielu zakresów, czyli połączenie wielu zakresów, a nawet wykorzystanie wcześniej utworzonych innych tabel przestawnych.

Ponieważ zaznaczona była komórka w tabeli danych, Excel domyślnie zaznacza pierwszą opcję. Domyślnie również została zaznaczona opcja dotycząca rodzaju raportu – Tabela przestawna.

Należy pozostawić ustawienia widoczne na rys 11.3 i wybrać przycisk Dalej.

Rysunek 11.3 Okno dialogowe Kreator tabel i wykresów przestawnych – krok 1

2. Drugie okno umożliwia zaznaczenie obszaru danych na podstawie, których będzie tworzona tabela przestawna. Automatycznie zaznaczana jest cała tabela.

Rysunek 11.4.

Okno dialogowe Kreator tabel i wykresów przestawnych – krok 2

3. 
   
   Po wybraniu przycisku Dalej nastąpi przejście do trzeciego ekranu, w którym należy zdecydować, gdzie zostanie umieszczona tabela przestawna: w nowym lub w istniejącym arkuszu z tabelą danych. Należy uaktywnić opcję Nowy arkusz.

Rysunek 11.5. Okno dialogowe Kreator tabel i wykresów przestawnych – krok 3

4. Jednak najważniejsze decyzje, związane z określeniem struktury tabeli przestawnej, podejmuje użytkownik po wybraniu przycisku Układ... w oknie dialogowym Kreatora – krok 3 z 3 (rys. 11.6)

Rysunek 11.6. Okno dialogowe Kreator tabel i wykresów przestawnych – Układ

Przyciski po prawej stronie okna reprezentują wszystkie pola bazy danych. Przeciągając je myszką do odpowiednich miejsc schematu tabeli, można tworzyć dowolne zestawienia i raporty. Do każdego obszaru można przeciągnąć dowolną liczbę przycisków; dopuszczalne są również obszary bez przycisków. Wyjątkiem jest obszar Dane, do którego należy przenieść przynajmniej jeden przycisk reprezentujący wybrane wartości, inaczej tabela straci sens.

Pole umieszczone w obszarze Kolumna stanie się nagłówkiem kolumn tabeli przestawnej, pole przeniesione w obszar Wiersz będzie tytułem dla wierszy tabeli, wartości umieszczone w obszarze Dane pojawią się na przecięciu odpowiednich kolumn i wierszy.

5. Przycisk reprezentujący pole Waluta przeciągnąć do zakresu z nagłówkiem Kolumna.
6. Przycisk Data przeciągnąć do zakresu opatrzonego etykietą Wiersz.
7. Przycisk Sprzedaż wartość przeciągnąć do zakresu Dane.
8. Rysunek 11.6 prezentuje okno dialogowe Układ z określoną strukturą tabeli przestawnej.

W przypadku, gdy do zakresu Dane zostanie przeniesione pole zawierające wartości liczbowe, Excel automatycznie zastosuje funkcję SUMA. Jeżeli do zakresu Dane zostanie przeniesione pole zawierające wartości tekstowe, zostanie użyta funkcja LICZNIK. Użytkownik może zmienić sugerowaną przez program funkcję, klikając dwukrotnie na nazwie pola, w tym przypadku jest to pole Suma: Sprzedaż wartość. Pojawi się okno dialogowe, które umożliwia zmianę automatycznie ustawionej funkcji na inną, zależną od charakteru raportu, jaki użytkownik tworzy (rys 11.7).

Rysunek 11.7. Okno dialogowe Pole tabeli przestawnej

9.       Należy pozostawić funkcję SUMA i zatwierdzić przyciskiem OK.

10.    W oknie Układ zatwierdzić budowę tabeli przestawnej OK, a w oknie Kreatora wybrać przycisk Zakończ.

W arkuszu o nazwie Arkusz4 pojawi się gotowe zestawienie, pozwalające na prezentację wartości sprzedaży walut w obu kantorach razem w poszczególnych dniach wraz z podsumowaniem wartości w wierszach i kolumnach (rys. 11.8).

Rysunek 11.8. Tabela przestawna do analizy sprzedaży i pasek narzędzi Tabela przestawna

11.3. Modyfikacja tabeli przestawnej

Sposób prezentowania danych w tabeli można w każdej chwili zmienić, modyfikując jej układ:

-          bezpośrednio w arkuszu przemieszczając przyciski pól,

-          za pomocą Kreatora tabel przestawnych lub

-          korzystając z paska narzędzi Tabela przestawna.

Ćwiczenie 11.3

Zmienić układ tabeli przestawnej w taki sposób, aby w wierszach znalazły się waluty, a w kolumnach kolejne dni. Zastosować metodę polegającą na przemieszczaniu przycisków pól bezpośrednio w arkuszu.

1.       Uchwycić przycisk i przemieścić w dół i w lewo, w obszar kolumny. W czasie przemieszczania przycisku, obok kursora pojawia się ikona przedstawiająca miniaturę tabeli z zaznaczonym niebieskim kolorem: wierszem, kolumną lub obszarem wartości. Przycisk waluty przemieszczamy do momentu, gdy zaznaczona będzie kolumna.

2.       Uchwycić przycisk i przemieszczać w prawo i w górę, do momentu, gdy w ikonie tabeli zaznaczony będzie wiersz.

3.       W efekcie powinna pojawić się tabela przestawna o zmienionej orientacji.
(rys. 11.9)

Rysunek 11.9. Zmiana orientacji tabeli

Ćwiczenie 11.4.

Dołączyć do tabeli dane dotyczące skupu oraz wyświetlanie wartości z podziałem na kantory.

1.      

Z paska narzędzi Tabela przestawna przeciągnąć przycisk Skup wartość w obszar wartości tabeli lub na przycisk Suma: Sprzedaż wartość.

Rysunek 11.10. Pasek narzędzi Tabela przestawna z przyciskami reprezentującymi wszystkie pola bazy danych

Ikona tabeli obok kursora powinna tym razem mieć niebieski obszar wartości.

2.       Możliwość wyświetlania wartości z podziałem na kantory można uzyskać dołączając nowe pole do tabeli przestawnej – pole Kantor w obszar Strony.

3.       Kliknięcie prawym przyciskiem myszy w obszarze tabeli przestawnej wywoła menu kontekstowe, z którego należy wybrać funkcję Kreator...

4.       Pojawi się okno dialogowe Kreator tabel przestawnych krok 3 z 3, w którym należy wybrać przycisk Układ...

5.       W oknie dialogowym Układ przycisk Kantor przeciągnąć w obszar Strona i zatwierdzić zmiany OK i dalej Zakończ.

6.       Efektem jest zmodyfikowana tabela przestawna, która ułatwia analizę danych skupu i sprzedaży oraz umożliwia zastosowanie filtra (wybór kantoru).

Rysunek 11.11. Tabela z dodanym polem strony i wartością skupu

Usunięcie pola z tabeli przestawnej można uzyskać ściągając myszką z tabeli odpowiadający mu przycisk. Jeśli jednak przycisk pola jest niewidoczny - na rys. 11.11 Skup wartość, Sprzedaż wartość nie są reprezentowane przez przyciski - należy skorzystać z okna Kreatora i ściągnąć odpowiedni przycisk z diagramu tabeli.

Liczby w tabeli są sformatowane z zastosowaniem formatu ogólnego, który jak widać na rys. 11.11 nie wpływa korzystnie na czytelność prezentowanych wartości.

Liczby w tabeli można formatować na dwa sposoby: korzystając z funkcji Autoformatowanie z menu Format lub tworząc własny format liczbowy.

Ćwiczenie 11.5

Zastosować własny format prezentujący liczby z dokładnością do dwóch miejsc po przecinku. Ponieważ w zakresie Dane znajdują się dwa pola Sprzedaż wartość i Skup wartość formatowanie liczb należy przeprowadzić dla każdego z nich oddzielnie.

1. 
   
   Zaznaczyć dowolną komórkę należącą do pola Sprzedaż wartość np. komórkę o adresie C5, kliknąć na niej prawym przyciskiem myszy i wybrać z menu podręcznego funkcję Ustawienia pola...
2. W oknie dialogowym Pole tabeli przestawnej kliknąć przycisk Liczby

Rysunek 11.12. Menu podręczne tabeli przestawnej

3. 
   W oknie Formatuj komórki wybrać kategorię Liczbowe i ustawić 2 miejsca dziesiętne. Zatwierdzić przyciskiem OK. W ten sposób wszystkie wartości liczbowe pola Sprzedaż wartość przyjmą ustalony format.

Rysunek 11.13. Formatowanie licz

4. Zaznaczyć dowolną komórkę należącą do pola Skup wartość np. komórkę o adresie C6 i wykonać jeszcze raz działania opisane powyżej. Wszystkie wartości z pola Skup wartość przyjmą ustalony, jednolity format liczbowy.

Po sformatowaniu liczb w sposób opisany w ćwiczeniu 11.5, Excel zachowa ten sam format w przypadku zmian struktury tabeli przestawnej. Jeśli liczby zostaną sformatowane w tradycyjny sposób z menu Format-Komórki, po przekształceniu tabeli zostanie ponownie wprowadzony format ogólny i formatowanie trzeba będzie przeprowadzać ponownie.

Ćwiczenie 11.6

Poprawnie skonstruowana tabela przestawna daje możliwość wyświetlania różnego poziomu dokładności informacji, co ułatwia wszelkiego rodzaju analizy i porównania.

Wyświetlić w tabeli, dane dotyczące skupu i sprzedaży marki niemieckiej w kantorze A w poniedziałek i piątek (2001-05-04 i 2001-05-08).

1.       Rozwinąć listę Kantor i zaznaczyć Kantor A (rys. 11.14).

Przycisk listy rozwijalnej

Rysunek 11.14. Lista wyboru kantoru

2.      



W podobny sposób rozwinąć listę Waluta, wyłączyć wszystkie oprócz marki niemieckiej oraz listę Data i pozostawić włączone 2001-05-04 i 2001-05-08.

Rysunek 11.15. Listy wyboru Waluty i Daty

3.       W efekcie powinna pojawić się tabela przestawna przedstawiająca tylko oczekiwane wartości.

Rysunek 11.16. Tabela przestawna z ograniczonym wyświetlaniem informacji

Ćwiczenie 11.7

Wyłączyć wyświetlanie sumowania wierszy i kolumn. W tym zestawieniu te wartości są zbędne.

1.       Zaznaczyć dowolną komórkę tabeli. Kliknąć na niej prawym przyciskiem myszy.

2.       Z menu podręcznego wybrać Opcje tabeli...

3.      

W oknie dialogowym Opcje tabeli przestawnej wyłączyć opcje Sumy całkowite kolumn oraz Sumy całkowite wierszy. Zatwierdzić OK.

...