Tabele Przestawne Podstawy
Przykłady opisane w tej lekcji dostępne są w
arkuszu Excela: Tabele
Przestawne Podstawy.xlsx tylko ich samodzielne przerobienie daje gwarancję
zapamiętania tej lekcji
Przykłady dla wszystkich lekcji szkolenia Excel
2013: ExcelSzkolenie.pl
Cwiczenia Excel 2013.zip
Ta lekcja może być
obejrzana lub przeczytana poniżej.
Film wygląda najlepiej
jeśli będzie odtwarzany w rozdzielczości 720p HD, rozdzielczość można zmienić
dopiero po uruchomieniu filmu klikając na ikonie trybika
która pojawi się w prawym dolnym
rogu poniższego ekranu. Po kilku sekundach od zmiany obraz wyostrzy się.
Tabele przestawne są jednym z najcenniejszych
narzędzi oferowanych przez Excel, niestety rzadko wykorzystywanym, ponieważ wiele
osób uważa, że ich używanie jest niezwykle trudne. W tej lekcji postaram się
dowieść, że wcale tak nie jest, a tworząc tabele przestawne możemy zaoszczędzić
wiele godzin pracy.
Podstawy używania tabel przestawnych zostaną
omówione na kilku prostych przykładach.
Przykład 1.
(Tabela z Danymi –
arkusz: ‘Tabele Przestawne 1’)
(Przykład 1 wykonany
– arkusz: ‘Tabele Przestawne 2’)
Dysponujemy tabelą o trzech kolumnach zawierających: nazwę produktu, kategorie, do której produkt ten należy, oraz jego sprzedaż z ostatniego roku. Tabela zawiera dane dla 500 produktów.
Zostaliśmy poproszeni o obliczenie, jaka jest
całkowita sprzedaż dla każdej z 10 kategorii produktów, jaka była średnia
sprzedaż na jeden produkt w każdej z kategorii, ile różnych produktów było
sprzedawanych w każdej z kategorii oraz jaka była sprzedaż najlepszego z tych
produktów.
Aby utworzyć tabelę przestawną należy ustawić aktywną komórkę na dowolnej komórce tabeli z danymi i wybrać z karty wstążki ‘WSTAWIANIE’ ikonę ‘Tabela przestawna’ zaznaczoną na poniższym rysunku.
Wyświetlone zostanie poniższe okno. Excel
próbuje sam domyślić się jaki obszar zajmuje nasza tabela z danymi, z reguły
jest to sugestia właściwa.
Domyślnie ustawiona jest opcja wstawiania
tabeli przestawnej w nowym arkuszu, wszystkim początkującym sugeruje przy niej
pozostać. Klikamy OK.
Po kliknięciu OK. na wstążce pojawiają się
‘NARZĘDZIA TABEL PRZESTAWNYCH’ z dwoma nowymi kartami poleceń, ‘ANALIZA’ oraz
‘PROJEKTOWANIE’ (oznaczone 1)
Na karcie ‘ANALIZA’ trzy ostatnie ikony
(oznaczone 2) informują nas o tym jakie elementy tabeli przestawnej mają być
wyświetlane.
Liczbą 3 oznaczona została nowoutworzona tabela
przestawna, w tej chwili nie zawiera żadnych kolumn ani wierszy.
Numerem 4 oznaczyłem listę ‘Pola tabeli
przestawnej’, która posłuży nam do utworzenia naszej tabeli.
I wreszcie cyfrą 5 oznaczone jest polecenie,
które wyświetli okno pozwalające nam zmieniać opcje tabeli. Od kliknięcia tego
polecenia zaczniemy tworzenie naszej tabeli przestawnej.
Istnieje kilka sposobów tworzenia i
modyfikowania tabel przestawnych, przeciąganie pól w widoku klasycznym jest
moim zdaniem najbardziej intuicyjnym i najszybszym sposobem.
Klikamy napis ‘Tabela przestawna’ a
następnie ‘Opcje’.
W oknie ‘Opcje tabeli przestawnej’ na
karcie ‘Wyświetlanie’ należy zaznaczyć ‘Układ klasyczny tabeli przestawnej’,
opcja ta domyślnie jest wyłączona.
Klikamy OK.
Wygląd tabeli zmienił się, powinna
ona teraz wyglądać tak jak jest to pokazane na poniższym rysunku.
Dodatkowo proponuję przenieść ‘Pola
tabeli przestawnej’ oznaczone trzy rysunki wyżej cyfrą 4 bliżej tabeli
przestawnej przeciągając za tytuł w lewo (strzałka w lewo na rysunku poniżej).
Jeżeli nie są widoczne wszystkie pola
należy rozciągnąć okno ‘Pola tabeli przestawnej’ łapiąc za róg i przeciągając w
dół (strzałka w dół na rysunku poniżej).
Ponieważ, dane które mamy
przygotować, mają być obliczone dla każdej z kategorii, pole kategoria
umieścimy w polach wierszy.
Przeciągamy pole tabeli przestawnej ‘kategoria’ do obszaru oznaczonego jako ‘Upuść pola wierszy tutaj’.
Trzykrotnie przenosimy pole
‘sprzedaż’ do obszaru ‘Upuść pola wartości tutaj’.
Przeciągamy pole ‘nazwa produktu’ do
obszaru ‘Upuść pola wartości tutaj’ (opisy pól są widoczny tylko wówczas, gdy
pola te są puste).
Tabela, którą uzyskamy powinna wyglądać tak jak poniżej.
Już w tym momencie znamy odpowiedzi na 2 z 4
postawionych na początku tego przykładu pytań.
W kolumnie B mamy sumę sprzedaży dla wszystkich
kategorii a w kolumnie E ilość produktów po kategoriach.
Sformatujemy teraz kolumny w tabeli.
Podwójny lewy klik na nagłówku pierwszej z
kolumn (komórka B4) otworzy okno ‘Ustawienia pola wartości’.
Klikamy przycisk ‘Format liczby’.
Formatujemy liczby tak jak jest to pokazane
poniżej i klikamy OK, OK.
Uzyskamy tabelę która będzie wyglądać jak
poniżej.
Wciąż mamy jednak 3 sumy sprzedaży (w kolumnach
B, C i D).
Klikamy dwukrotnie na nagłówku drugiej kolumny
(komórka C4).
Tym razem oprócz zmiany formatu liczby
zmieniamy także opcję ‘Podsumuj pole wartości według’ na ‘Średnia’.
Format liczb zmieniamy analogicznie.
Klikamy dwukrotnie na nagłówku trzeciej kolumny
(komórka D4).
Wybieramy ‘Maksimum’, i formatujemy liczby tak
samo jak poprzednio.
Uzyskujemy tabelę przestawną
zawierającą wszystkie poszukiwane przez nas dane.
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ż’, wciskamy ‘Enter’.
Pojawi się powyższy komunikat, jeżeli mimo
wszystko chcielibyśmy taką właśnie nazwę nadać możemy dodać spację na końcu
słowa ‘Sprzedaż’, lub dokonać innej drobnej zmiany.
Zmieniamy wszystkie nagłówki kolumn, następnie zwężamy kolumny uzyskując tabelę taką jak na poniższym rysunku.
Gratulacje! J
Najprawdopodobniej właśnie
przygotowałeś swoją pierwszą tabelę przestawną.
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.
Proponuję dokonać teraz drobnej
zmiany w danych wejściowych (Arkusz: ‘Tabele przestawne 1’). Zmieńmy sprzedaż dla
pierwszego produktu na 2345.
Po powrocie do naszej tabeli
odkrywamy, że nic się nie zmieniło, maksimum w kategorii A nadal wynosi 995.
UWAGA:
Dane w
tabelach przestawnych nie są automatycznie aktualizowane.
Aby tabela przestawna została
zaktualizowana należy kliknąć ją prawym przyciskiem myszy i wybrać polecenie
‘Odśwież’.
Nowe wartości zostały obliczone,
maksimum w kategorii A jest teraz właściwe, zmieniły się też inne wartości dla
kategorii A i Sumy końcowej.
Gdybyśmy chcieli zmienić kolejność pól, np.
kolumnę ‘Ilość’ pokazywać za ‘Sprzedażą’, 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 koniec tego przykładu sformatujemy
jeszcze tabelę przestawną używając jednego z dostępnych ‘Stylów tabeli
przestawnej’.
Nowy styl będzie pokazywany na naszej
tabeli automatycznie podczas przemieszczania kursora myszy nad ikonami stylów.
Na skutek ostatniej operacji uzyskaliśmy ostateczny
wygląd naszej tabeli w tym przykładzie.
Przykład
2.
(Tabela z Danymi –
arkusz: Tabele Przestawne 1)
(Przykład 2 wykonany
– arkusz: Tabele Przestawne 3)
Dodatkowym zadaniem po ukończeniu ‘Przykładu 1’
będzie obliczenie jaki był procentowy rozkład sprzedaży na kategorie.
W tym celu jeszcze raz dodajemy sprzedaż do tabeli przestawnej.
Jeżeli mając ustawioną komórkę w tabeli
przestawnej nie widzimy już Listy pól, możemy ją przywrócić klikając na
polecenie o tej samej nazwie pokazane poniżej.
Przeciągamy pole sprzedaż jeszcze raz do danych
i klikamy dwukrotnie jego nagłówek.
Przechodzimy na kartę ‘Pokazywanie wartości
jako’, w okienku ‘Pokaż wartości jako’ wybieramy ‘% sumy kolumny’ po czym
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.
Kategorie o większym udziale są ważniejsze i
powinny być na górze tabeli.
Klikamy na ikonę strzałki w komórce ‘A4’ i wybieramy
polecenie ‘Więcej opcji sortowania…’.
W oknie ‘Sortowanie (kategoria)’ klikamy
‘Malejąco (od Z do A) według:’ i wybieramy ‘Suma z sprzedaż’.
Pozostała już nam tylko zmiana nagłówka kolumny na ‘Udział’ oraz zmiana formatu
na procentowy bez miejsc dziesiętnych i ćwiczenie to możemy uznać za
zakończone.
Przykład 3.
(Tabela z Danymi –
Arkusz: ‘Tabele Przestawne 1’)
(Przykład 3 wykonany
– Arkusz: ‘Tabele Przestawne 4’)
W przykładzie 3 użyjemy tej samej tabeli z
danymi.
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 10 kategorii produktów, oraz
sumę sprzedaży tych produktów.
Poniżej fragment tabeli na której będziemy
pracować w tym ćwiczeniu.
Aby przygotować taki raport bez użycia tabel
przestawnych najlepiej byłoby:
1. Założyć filtr na tabeli.
2. Posortować tabelę
malejąco po sprzedaży,
3.
Skopiować tabelę z danymi na 10 arkuszy.
4.
Używając filtra pozostawić na każdym z arkuszy tylko
produkty z jednej kategorii i skasować pozostałe,
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 może nawet 10 razy
więcej czasu, a ryzyko pomyłki przy tylu operacjach byłoby znacznie większe niż
przy użyciu tabeli przestawnej.
Aby przygotować raport korzystając z tabeli
przestawnej:
1. Ustawiamy aktywną komórkę w tabeli z danymi
2.
Wybieramy
Wstawianie à Tabela przestawna i
zatwierdzamy klikając OK.
3.
Jeżeli widok klasyczny jest
wyłączony, należy wybrać polecenie Opcje à Wyświetlanie àUkład klasyczny
tabeli przestawnej.
4.
Przenosimy nazwy pól zgodnie z
poniższym rysunkiem.
5.
Zmieniamy format liczb w kolumnie z
danymi. Jeżeli w tabeli przestawnej jest tylko jedna kolumna z danymi jej
nagłówek znajduje się nie bezpośrednio nad kolumną ale na lewo od niej. Aby
zmienić format należy dwukrotnie kliknąć komórkę A3.
6.
Klikamy prawym klawiszem w dowolnym polu
kolumny z danymi i sortujemy ją od największych do najmniejszych.
7.
Klikamy prawym klawiszem na jednej z
nazw produktów i wybieramy polecenie
Filtruj a następnie 10 pierwszych.
Wybieramy ‘Górne’
‘10’ ‘Elementy’ według ‘Suma z sprzedaż’.
Raport jest już gotowy do rozesłania i powinien wyglądać tak jak poniżej.
Interesujące ich kategorie użytkownicy mogą wybierać z pola filtra, komórka B1.
Przykład 4.
(Tabela z Danymi –
Arkusz: ‘Tabele Przestawne 1’)
(Przykład 4 wykonany
– Arkusz: ‘Tabele Przestawne 5’)
W tym przykładzie będziemy
kontynuować pracę na tabeli przestawnej utworzonej w poprzednim przykładzie.
Załóżmy że dane sprzedaży zmieniły
się ale nadal mamy 500 produktów na liście.
Nie trzeba przygotowywać tabeli przestawnej od nowa, wystarczy podmienić stare dane na nowe w arkuszu z danymi, tak aby były dokładnie w tym samym miejscu.
Następnie klikamy prawym klawiszem na tabeli i
wybieramy opcję ‘Odśwież’, tak jak było to już opisywane w przykładzie 1.
Dane zostaną zaktualizowane, a układ
i format tabeli oraz wszystkie wybrane opcje zachowane.
W przypadku gdy wielkość tabeli z
danymi uległa zmianie, procedura jest nieco bardziej skomplikowana.
Dopiszemy teraz jeden produkt na końcu tabeli z
danymi w Arkuszu ‘Tabele Przestawne 1’.
Jeżeli nowy arkusz z danymi ma inną ilość wierszy niż poprzedni należy ustawić aktywną komórkę w tabeli przestawnej i z karty ‘ANALIZA’ wybrać polecenie ‘Zmień źródło danych’.
Pokazany zostanie arkusz z danymi źródłowymi
oraz zakres z którego korzysta tabela przestawna.
Przewijamy widok do produktu który został
dopisany.
Zmieniamy adres ostatniego wiersza z D502 na
D503 i klikamy OK.
Zamiast zaznaczać obszar używając myszki
wygodniej będzie zmienić zakres zamieniając 2 na 3 w polu ‘Tabela/zakres:’.
Dane w tabeli przestawnej zostaną
zaktualizowane.
Tabela przestawna w obecnej postaci może nam
pokazać 10 produktów z najwyższą sprzedażą w wybranej kategorii lub we
wszystkich kategoriach.
Gdybyśmy 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ąć ikonę strzałki przy polu kategoria (komórka B1), po
czym wybrać ‘Zaznacz wiele elementów’.
Zaznaczamy kategorie ‘A’ i ‘C’.
Pole Wszystkie nie tylko potrafi wszystkie
zaznaczyć ale i odznaczyć.
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.
Na liście jest 10 najlepszych produktów wg
sprzedaży łącznie z kategorii ‘A’ i ‘C’.
Niestety nie ma możliwości wyświetlenie listy
wybranych elementów bezpośrednio w komórce B1.
Aby sprawdzić jakie elementy są wybrane należy
kliknąć ikonę filtra.
Przykład 5.
Ćwiczenia
do samodzielnego wykonania.
Biegłość w używaniu tabel przestawnych możemy uzyskać tylko i wyłącznie dzięki ćwiczeniom.
Przed przejściem dalej sugeruje samodzielne przygotowanie poniższych tabel przestawnych wykorzystując dane znajdujące się w arkuszu ‘Tabele Przestawne 6’.
Ćwiczenie
do samodzielnego wykonania 1.
Na podstawie tabeli znajdującej się w arkuszu: ‘Tabele Przestawne 6’, 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 czemu uzyskamy biegłość i będziemy pamiętać tą lekcję także jutro.
Informacje dodatkowe
Przed
przygotowaniem raportu tabeli przestawnej należy odpowiednio przygotować tabelę
z danymi, ponieważ nie z każdej listy danych możemy przygotować tabelę
przestawną.
Tabela
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.
3.
Tabela nie ma pustych wierszy ani kolumn. Excel traktuje pustą kolumnę lub
wiersz jako koniec tabeli.
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.
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 (lekcja ‘Funkcje dla Zaawansowanych’).
7.
Dane liczbowe są liczbami. Często dane liczbowe importowane z wewnętrznych
firmowych systemów informatycznych są tekstem. Aby szybko sprawdzić czy
wszystkie dane są liczbami najłatwiej zaznaczyć kolumny z danymi i na pasku
stanu (patrz lekcja Pasek Stanu) wybrać 'Licznik wartości liczbowych' 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 specjalne 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’.