25. Tabele przestawne
(Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: Cwiczenia.xls, zachęcam do ich samodzielnego wykonania.)
Podstawy używania tabel przestawnych zostaną omówione na kilku prostych przykładach.
Przykład 1.
Załóżmy, że zostaliśmy poproszeni o przygotowanie raportu przedstawiającego sprzedaż z ostatniego roku 10 najlepszych produktów (tzw. TOP 10) w każdej z 12 kategorii produktów, oraz sumę sprzedaży tych produktów. Dysponujemy raportem w formacie tabeli o trzech kolumnach zawierających: nazwę produktu, kategorie, do której należy, oraz jego sprzedaż z ostatniego roku.
Fragment tabeli omawianej w ćwiczeniu.
nazwa produktu
kategoria
sprzedaż
produkt 1
A
925
produkt 2
617
produkt 3
82
produkt 4
347
produkt 5
917
produkt 6
995
produkt 7
122
produkt 8
372
produkt 9
571
produkt 10
367
produkt 11
471
produkt 12
861
produkt 13
B
829
produkt 14
731
Aby przygotować taki raport bez użycia tabel przestawnych najlepiej byłoby:
1. Skopiować tabelę z danymi na 12 arkuszy.
2. Na każdej z 12 tabel założyć filt,
3. Używając filtra pozostawić na każdym arkuszy tylko produkty z jednej kategorii i skasować pozostałe,
4. Posortować każdą z 12 tabel malejąco po sprzedaży,
5. Skasować produkty spoza TOP 10
6. Nadać arkuszom nazwy odpowiednio do kategorii
Wykonanie takiego zadania bez tabel przestawnych jest, więc jak widać możliwe, jednak zajęłoby około 10 więcej czasu, a ryzyko pomyłki przy tylu operacjach byłoby znacznie większe niż przy użyciu tabel.
Aby przygotować raport korzystając z tabeli przestawnej należy:
1. Sprawdzić czy wszystkie kolumny w naszej tabeli mają nagłówki z różniącymi się nazwami.
2. Zaznaczyć całą tabele (najłatwiej jest to zrobić zaznaczając którąkolwiek komórkę tabeli i wciskając Ctrl+Shift+8)
3. Wybrać z Menu: Dane → Raport tabeli przestawnej i wykresu przestawnego...
4. W okienku, które się pojawi (krok 1 z 3), właściwe opcje powinny być już zaznaczone, klikamy tylko przycisk ‘Dalej >’
5. W kolejnym okienku (krok 2 z 3) pokazany jest zakres gdzie znajduje się tabela, z której dane będą wykorzystywane do naszej tabeli przestawnej, jeżeli zakres się zgadza klikamy przycisk ‘Dalej >’
6. W ostatnim z okienek kreatora wybieramy gdzie ma się znaleźć nowo tworzona tabela przestawna, zdecydowanie wygodniejszym rozwiązaniem jest umieszczenie jej w nowym arkuszu. Po dokonanym wyborze klikamy przycisk ‘Zakończ’
Ponieważ na wszystkich 3 ekranach kreatora najkorzystniejsze opcje są wybrane automatycznie, w większości przypadków można wybrać przycisk ‘Zakończ’ już na pierwszym ekranie.
Jeżeli w pliku, którego używamy jest już tabela przestawna korzystająca z tych samych danych pojawi się poniższy komunikat.
Polecam wybranie opcji ‘Tak’.
Po wybraniu ‘Zakończ’ (lub ‘Tak’) Excel utworzy nowy arkusz z tabelą przestawną, będzie on wyglądał podobnie do poniższego.
Kolejnym krokiem będzie przeciągnięcie z Listy pól tabeli przestawnej pola ‘nazwa produktu w miejsce oznaczone napisem ‘Upuść pola wierszy tutaj’
W efekcie nasza tabela będzie wyglądała tak jak poniżej.
Teraz powinniśmy przenieść pole kategoria w miejsce oznaczone ‘Upuść pola stron tutaj’, a pole sprzedaż w miejsce oznaczone ‘Upuść elementy danych tutaj’.
Po przeciągnięciu nasza tabela będzie wyglądać tak jak poniżej, teraz możemy już zamknąć okienka ‘Tabela przestawna’ i ‘Lista pól tabeli przestawnej’ nie będą już nam potrzebne.
Jeśli zajdzie taka potrzeba okienka ‘Tabela przestawna’ i ‘Pokaż pasek narzędzi Tabela przestawna’ możemy przywrócić klikając prawym klawiszem na tabeli przestawnej i wybierając odpowiednie opcje.
Mamy już tabelę ze sprzedażą po produktach i możemy dokonać wyboru kategorii (w komórce B1)
Teraz zajmiemy się tym, aby tabela pokazywała tylko 10 produktów z najwyższą sprzedażą.
Klikamy prawym klawiszem myszki na dowolnej z nazw produktów i wybieramy opcje: ‘Ustawienia pola...’.
W okienku ‘Pole tabeli przestawnej’ klikamy przycisk ‘Zaawansowane...’
W okienku ‘Zaawansowane opcje pola tabeli przestawnej’ ustawiamy opcje tak jak jest to pokazane poniżej i klikamy dwukrotnie ‘OK’.
Po ostatniej operacji nasza tabela powinna wyglądać tak jak poniżej.
Na koniec sformatujemy nieco naszą tabelę, była bardziej czytelna.
Proponuje:
- dodać kolumnę przed kolumną A
- ukryć linie siatki (Narzędzia → Opcje → Widok → Linie siatki)
- ukryć wiersz 3 ze zbędnym nagłówkiem ‘Suma z sprzedaż’.
- dodać 3 wiersze na początku arkusza
- w komórce B4 wpisać tytuł raportu np. „Sprzedaż TOP 10 po kategoriach”
- wybrać jedną z kategorii np. A
Po tych operacjach nasz raport będzie wyglądał tak jak poniżej, pozostaje jedynie sformatować tabelę przestawną.
Tabele przestawne przy każdej zmianie ustawień formatują się same, więc aby nasze formatowanie nie zostało utracone przy pierwszej zmianie kategorii należy wyłączyć autoformatowanie.
Formatujemy kolumnę ze sprzedażą np. bez miejsc dziesiętnych i wyśrodkowane.
...
Ad_maiorem_Dei_Gloriam