Tabele Przestawne Podstawy
Przykłady opisane w tej lekcji dostępne są w arkuszu Excela:
Tabele Przestawne
Podstawy.xls, tylko ich samodzielne przerobienie daje gwarancję zapamiętania
tej lekcji.
Przykłady dla wszystkich lekcji szkolenia Excel 2003: ExcelSzkolenie.pl
Cwiczenia Excel 2003.zip
Podstawy używania tabel przestawnych zostaną omówione na
kilku prostych przykładach.
Przykład 1.
(Arkusz: ‘Tabele Przestawne Podstawy
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.
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.
Klikamy prawym klawiszem na tabeli przestawnej i wybieramy
polecenie ‘Opcje tabeli...’
W okienku ‘Opcje tabeli przestawnej’ wyłączamy opcje
‘Autoformatowanie tabeli’ i klikamy ‘OK’.
Inne często używane opcje na tym ekranie to
włączanie/wyłączanie sum całkowitych wierszy i kolumn.
Raport jest już gotowy do rozesłania.
Kiedy następnym razem będziemy przygotowywać taki sam raport
nie ma konieczności wykonywania wszystkich operacji od nowa, wystarczy
podmienić stare dane na nowe w arkuszu z danymi, kliknąć prawym klawiszem na
tabeli i wybrać opcje ‘Odśwież dane’.
Jeżeli nowy arkusz z danymi ma więcej wierszy niż poprzedni
(w naszym przykładzie dodano nowe produkty) należy kliknąć lewym klawiszem na
tabeli, wybrać ‘Kreator...” przejść ‘< Wstecz’ do 2 z 3 kroków kreatora i
zmienić zakres z którego mają być ciągnięte dane.
Gdybyśmy natomiast chcieli wybrać kilka kategorii na raz i sprawdzić na przykład jakie było 10 najlepszych produktów w kategorii A i C łącznie, należy kliknąć pole kategorii (komórka B1) prawym klawiszem i wybrać ‘Ustawienia pola…’
W oknie, które się otworzy, w okienku ‘Ukryj elementy’ zaznaczamy wszystkie kategorie oprócz A i C (konieczne jest przewinięcie w dół suwaka zaznaczonego czerwoną strzałką).
Po wybraniu klikamy ‘OK.’.
Po czym stwierdzamy że na liście 10 najlepszych produktów nic się nie zmieniło.
Aby wyświetlone zostały najlepsze produkty z kategorii A i
C, musimy jeszcze wybrać opcję ‘Wszystkie’ – co tak naprawdę w tym przypadku
oznacza tylko A i C, ponieważ pozostałe kategorie ukryliśmy.
W komórce B1 wyświetli się opis ‘(Wiele elementów)’, który
oznacza, że został wybrany więcej niż jeden element ale nie wszystkie elementy.
Gdyby ktoś chciał sprawdzić co zostało wybrane musi
wyświetlić okno z kategoriami tak jak to widać na powyższym rysunku.
Niestety nie ma możliwości wyświetlenie listy wybranych
elementów bezpośrednio w komórce B1.
Przykład 2.
(Arkusz: ‘Tabele Przestawne Podstawy
W przykładzie 2 użyjemy tej samej tabeli z danymi.
Tym razem będziemy chcieli obliczyć, jaka jest całkowita
sprzedaż dla każdej z kategorii, jaka była średnia sprzedaż na jeden produkt,
ile produktów było sprzedawanych w każdej z kategorii oraz jaka była sprzedaż
najlepszego z tych produktów. Dodatkowym zadaniem po ukończeniu powyższego
etapu będzie obliczenie jaki był procentowy rozkład sprzedaży na kategorie.
Moglibyśmy przygotować tabelę z tego zadania odpowiednio
zmieniając tabelę przygotowaną w przykładzie 1, jednak dla przećwiczenia
sposobu przygotowania tabel przestawnych proponuje przygotować tabelę od
początku.
Pierwsze kroki są dokładnie takie same jak w przykładzie
pierwszym:
1. Zaznaczyć całą tabele (najłatwiej jest to zrobić
zaznaczając którąkolwiek komórkę tabeli i wciskając Ctrl+Shift+8)
2. Wybrać z Menu: Dane → Raport tabeli przestawnej i
wykresu przestawnego...
3. W okienku, które się pojawi (krok 1 z 3), właściwe opcje
powinny być już zaznaczone, klikamy tylko przycisk ‘Dalej >’ lub od razu
wybieramy ‘Zakończ’
W nowym arkuszu, który zostanie utworzony przenosimy pola
tabeli przestawnej tak jak jest to pokazane na poniższym rysunku, pole sprzedaż
przenosimy trzykrotnie do obszaru opisanego jako ‘Upuść elementy danych tutaj’.
Tabela, którą uzyskamy powinna wyglądać tak jak poniżej. Teraz
przeniesiemy pole ‘Dane’ tak jak jest to pokazane na poniższym rysunku.
Uzyskamy tabele przestawną taką jak poniżej.
Sformatujemy teraz kolumny w tabeli. Klikamy nagłówek
kolumny prawym klawiszem i wybieramy opcję ‘Ustawienia pola...’
Klikamy przycisk ‘Liczby...’
Formatujemy liczby z każdej z kolumn tak jak poniżej.
Uzyskamy tabelę która będzie wyglądać jak poniżej.
Wciąż mamy jednak 3 sumy sprzedaży
Klikamy nagłówek ‘Suma z sprzedaż2’ prawym klawiszem i wybieramy
pole ‘Ustawienia pola’.
Zmieniamy opcję ‘Podsumowanie według:’ na ‘Średnia’.
A w ‘Suma z sprzedaz3’ na ‘Maksimum’.
Ponieważ nagłówek ‘Suma z sprzedaż’ nie brzmi zbyt dobrze
proponuję zmienić ten opis na ‘Sprzedaż’.
Stajemy w komórce z nagłówkiem i wpisujemy tam ‘Sprzedaż’ i
wciskamy ‘Enter’.
Pojawi się komunikat, że taka nazwa pola już istnieje w
oryginalnej tabeli skąd czerpaliśmy dane, jeżeli mimo wszystko chcielibyśmy
taką właśnie nadać możemy dostawić spację na końcu słowa Sprzedaż, lub dokonać
innej drobnej zmiany.
Zmieniamy wszystkie nagłówki kolumn uzyskując tabelę taką
jak na poniższym rysunku.
W ten sposób obliczyliśmy wszystkie dane, których
poszukiwaliśmy w pierwszym etapie tego przykładu.
Po nabraniu wprawy wszystkie opisane powyżej operacje nie
powinny zająć dłużej niż 1 minutę.
Gdybyśmy chcieli zrobić to samo bez wykorzystania tabel
przestawnych zajęłoby to, co najmniej kilkanaście razy więcej.
Dodatkowo, jeśli dane się zmienią wystarczy kliknąć tabelę
prawym przyciskiem i wybrać ‘Odśwież dane’. W przypadku wykorzystania innych
sposobów obliczeń przeliczenie całości od nowa nie byłoby tak szybkie.
Gdybyśmy chcieli zmienić kolejność pól wystarczy złapać za
‘bok’ nagłówka kolumny i przeciągnąć go we właściwe miejsce. To gdzie pojawi
się przeciągana kolumna pokazuje się na bieżąco podczas przeciągania i jest
oznaczone grubą linią.
Na skutek ostatniej operacji uzyskaliśmy ostateczny wygląd
naszej tabeli w pierwszym etapie przykładu.
W drugim etapie tego zadania mieliśmy obliczyć procentowy
rozkład sprzedaży po kategoriach. W tym celu jeszcze raz dodajemy sprzedaż do
tabeli przestawnej.
Klikamy nowoutworzone pole prawym klawiszem myszy i
wybieramy opcję ‘Ustawienia pola…’ po czym klikamy przycisk ‘Opcje’
Z Menu ‘Pokaż dane jako:’ wybieramy ‘% kolumny’ i klikamy
‘OK.’.
W ten sposób uzyskamy tabelę, która powinna wyglądać tak jak
ta poniżej.
Możemy jeszcze posortować kategorie według udziału ich
sprzedaży w sprzedaży całkowitej.
Klikamy prawym klawiszem myszy na polu kategoria i wybieramy
polecenie ‘Ustawienia pola…’.
W oknie ‘Pole tabeli przestawnej’ klikamy przycisk
‘Zaawansowane…’.
W oknie ‘Zaawansowane opcje pola tabeli przestawnej’ wybieramy ‘Opcje Autosortowania’
‘Malejące’ a z Menu ‘Używane pole:’ wybieramy ‘Suma z sprzedaż’ w której to
kolumnie znajduje się obliczony przez nas udział. Po czym wybieramy OK. w tym
oknie i oknie ‘Pole tabeli przestawnej’.
Pozostała już nam tylko zmiana nagłówka kolumny na Udział i
ćwiczenie to możemy uznać za zakończone.
Biegłość w używaniu tabel przestawnych możemy uzyskać tylko
i wyłącznie dzięki ćwiczeniom.
Przed przejściem do lekcji ‘Tabele przestawne dla zaawansowanych’
sugeruje samodzielne przygotowanie poniższych tabel przestawnych wykorzystując
dane znajdujące się w arkuszu: ‘Tabele Przestawne Podstawy
Ćwiczenie do
samodzielnego wykonania 1.
Na podstawie tabeli znajdującej się w arkuszu ćwiczeń nr 26,
przygotuj tabelę przestawną taką jak poniższa.
Czas wykonania ćwiczenia włącznie z formatowaniem, po
uzyskaniu wprawy nie powinien przekroczyć 1 minuty.
Ćwiczenie do
samodzielnego wykonania 2.
Przekształć tabelę przestawną z poprzedniego ćwiczenia do
poniższej postaci:
Czas wykonania ćwiczenia po uzyskaniu wprawy nie powinien
przekroczyć 2 sekund.
Ćwiczenie do
samodzielnego wykonania 3.
Przekształć tabelę przestawną z poprzedniego ćwiczenia do poniższej
postaci, ustaw kolumny dokładnie w tej samej kolejności:
Czas wykonania ćwiczenia włącznie z formatowaniem, po
uzyskaniu wprawy nie powinien przekroczyć 1 minuty.
Ćwiczenie do
samodzielnego wykonania 4.
Przekształć tabelę przestawną z poprzedniego ćwiczenia do
poniższej postaci:
Ćwiczenie do
samodzielnego wykonania 5.
Przekształć tabelę przestawną z poprzedniego ćwiczenia do
poniższej postaci:
Ćwiczenie do
samodzielnego wykonania 6.
Przekształć tabelę przestawną z poprzedniego ćwiczenia do
poniższej postaci:
Po wykonaniu wszystkich 6 ćwiczeń proponuje... ...skasować arkusz w którym je zrobiliśmy
i wykonać je jeszcze raz od początku, dzięki temu uzyskamy niezwykle cenne
doświadczenie.
Przykład 3.
Przed przygotowaniem
raportu tabeli przestawnej należy odpowiednio przygotować tabelę z danymi,
ponieważ nie z każdej listy danych możemy przygotować tabelę przestawną.
Lista
musi spełniać następujące warunki:
1. Każda
kolumna ma nagłówek, który mieści się w 1 wierszu, każdy nagłówek kolumny jest
inny.
2.
Wszystkie dane są w jednej tabeli. Teoretycznie w Excelu 2003 jest możliwe
przygotowanie tabeli przestawnej z kilku tabel ale zdecydowanie tego nie polecam,
szczególnie użytkownikom z małym doświadczeniem.
3. Tabela
nie ma pustych wierszy ani kolumn. Excel traktuje pustą kolumnę lub wiersz jako
koniec tabeli, dane poniżej pustego wiersza / na prawo od pustej kolumny nie
zostaną wzięte pod uwagę.
4. Pusta
komórka to nie to samo co zero! Puste komórki są pomijane przy wielu
obliczeniach, np średnia będzie inna w zależności od tego czy w komórce jest
zero czy jest ona pusta, w przypadku pustej komórki nie zostanie ona wzięta pod
uwagę przy obliczeniach.
5. W
tabeli z danymi nie ma scalonych komórek, scalone komórki nie są traktowane
jako opis do wszystkich komórek ale jedynie jako opis do pierwszego
wiersza/kolumny w którym się znajdują.
6. W
każdej komórce jest tylko jedna dana. Dla przykładu umieszczenie imienia i
nazwiska w jednej komórce uniemożliwi sortowanie po nazwiskach, jeżeli mamy
takie dane, łatwo możemy je rozdzielić używając odpowiedniej kombinacji funkcji
tekstowych (patrz lekcja Funkcje Podstawy).
7. Dane
liczbowe są liczbami. Często dane liczbowe importowane z wewnętrznych firmowych
systemów informatycznych są tekstem. Aby łatwo sprawdzić czy wszystkie dane są
liczbami najłatwiej zaznaczyć kolumny z danymi i na pasku stanu (patrz lekcja
Pasek Stanu) wybrać 'Licznik num.' a później 'Licznik' jeżeli ilości się
różnią, niestety część danych to tekst.
Najprostszą
metodą zamiany liczb na tekst będzie:
-
wpisanie w dowolną komórkę poza tabelą jedynki,
-
skopiowanie komórki z jedynką
-
zaznaczenie obszaru na którym powinny być liczby
- wklejenie
specjalnie tak jak na rysunku poniżej.
Wszystkie
teksty zamienią się na liczby.
Więcej informacji o możliwościach tabel przestawnych w
lekcji ‘Tabele przestawne dla zaawansowanych”
Szkolenie Excel 2003 Szkolenie Excel 2007