Tabele Przestawne dla Ekspertów

Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: Tabele Przestawne dla Ekspertów.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

Przykład 1.

(Arkusz: ‘Tabele Przestawne Ekspert 1’)

Elementy Obliczeniowe w Tabelach Przestawnych

W przykładzie pierwszym lekcji Tabele Przestawne dla Zaawansowanych używając pól obliczeniowych wyliczaliśmy marże procentową na podstawie marży kwotowej i wartości sprzedaży. Gdybyśmy zamiast wyliczać DP% w tabeli przestawnej dodali kolumnę z takim dzieleniem do tabeli źródłowej uzyskalibyśmy podobny rezultat, choć oczywiście wszystkie ewentualne podsumowania w tabeli przestawnej byłyby błędne.

Elementy obliczeniowe tym różnią się od pól obliczeniowych że to raczej przeważnie podsumowania wierszy tabeli źródłowej i mogłyby je zastąpić dodatkowe wiersze dodane w tabeli źródłowej. Podobnie w tym przypadku gdybyśmy zdecydowali się na zmiany w tabeli źródłowej w wielu przypadkach podsumowania na tak zmodyfikowanych danych byłyby błędne.

Skorzystamy ze znanej już nam tabeli źródłowej przedstawiającej sprzedaż kosmetyków w 8 europejskich krajach.

Dodamy nową tabelę przestawną opartą o dane z arkusza ‘Tabele Przestawne dla Zaawans 5’.

Następnie pokażemy w tej tabeli sprzedaż z 2014 roku w podziale na miesiące i kategorie tak jak jest to pokazane na poniższym rysunku, oraz sformatujemy liczby.

W następnym kroku ustawiamy aktywną komórkę na dowolnym z miesięcy i z menu ‘ANALIZA’ wybieramy ‘Pola, elementy i zestawy’ a następnie ‘Element obliczeniowy…’.

W oknie ‘Wstaw element obliczeniowy w „Miesiąc’”’ wprowadzamy nazwę elementu Q1 w polu ‘Nazwa’.

A w polu Formuła wprowadzamy 3 pierwsze miesiące korzystając z okienka ‘Elementy:’ i z klawiatury wpisując plusy.

Potwierdzamy poprzez kliknięcie OK

Czynność tą powtarzamy dla 3 kolejnych kwartałów aż uzyskamy tabelę przestawną w poniższej postaci.

Niestety Sumy końcowe kolumn obliczane przez tabelę przestawną nie biorą pod uwagę tego, że elementy obliczeniowe są sumą miesięcy, na skutek tego ‘Suma końcowa’ jest dwukrotnie wyższa niż powinna być.

Dlatego też rezygnujemy z sumy końcowej kolumn.

A następnie dodajemy element obliczeniowy który ją zastąpi.

W skład elementów obliczeniowych mogą wchodzić także inne elementy obliczeniowe.

Sumę dla roku obliczymy wykorzystując sumy kwartalne.

W wyniku tej operacji uzyskamy tabelę taka jak poniższa.

Ostatnim krokiem będzie przeniesienie elementów obliczeniowychQ1, Q2 i Q3 aby były po miesiącach, które sumują.

Po wybraniu etykiety elementu obliczeniowego, najeżdżamy na krawędź jego obramowania, aż uzyskamy kursor w kształcie strzałki w 4 strony.

Otrzymujemy docelowy wygląd tej tabeli przestawnej.

Elementy obliczeniowe działają poprawnie niezależnie od zmian w układzie tabeli.

Jeśli trafią do nagłówków kolumn w Opcjach tabeli przestawnej należy dodać sumy kolumn i zrezygnować z sumy wierszy.

Kolumna do której dodaliśmy elementy obliczeniowe nie może być wykorzystana w filtrach tabeli przestawnej.

Gdy spróbujemy ją tam przenieść zobaczymy dość zaskakujący komunikat bez treści.

Przekształćmy jeszcze naszą tabelę do poniższej postaci.

Firma rozważa rebranding i połączenie brandów pierwszego, drugiego i części produktów trzeciego brandu w jeden nowy roboczo nazywany Megabrand.

Produkty z 3 brandu planowane do połączenia generują 60% jego sprzedaży.

Chcielibyśmy już teraz przeglądać dane w nowym podziale.

Przygotujemy 2 elementy obliczeniowe: ‘Megabrand’ i ‘Brand 3 okrojony’.

Uzyskujemy tabelę przestawną jak poniżej z niepoprawną wartością dla sumy kolumny.

Po odfiltrowaniu Brandu 1, Brandu 2 i Brandu 3. Suma jest poprawna.

Ostateczny wygląd tabeli.

W tym przypadku, gdybyśmy spróbowali przygotować element obliczeniowy sumujący wszystkie brandy zobaczylibyśmy komunikat z informacją, że formuła może mieć maksymalnie 255 znaków.

Komunikat mówi o Nazwie, ale to pomyłka, chodzi o Formułę.

Można by to obejść tworząc 2 cząstkowe elementy obliczeniowe, a element TOTAL stanowiłby ich sumę, ale rozwiązanie z filtrem uważam za znacznie szybsze.

Jeżeli podczas próby dodania Elementu obliczeniowego zobaczymy poniższy komunikat najprawdopodobniej na tej samej tabeli źródłowej oparta jest już inna tabela przestawna i dlatego Excel nie potrafi utworzyć Elementów obliczeniowych.

Problemy pojawią się również gdy próbujemy grupować na dwa różne sposoby w dwóch tabelach przestawnych opartych na tym samym źródle.

Dlatego często wygodnym rozwiązaniem jest skopiowanie tabeli źródłowej do innego arkusza i oparcie każdej z tabel przestawnych na innym źródle.

Przykład 2.

(Arkusz: ‘Tabele Przestawne Ekspert 2’)

W sekcji ‘NARZĘDZIA TABEL PRZESTAWNYCH’ na karcie ‘PROJEKTOWANIE’ znajdziemy 2 ikony pozwalające nam formatować tabele przestawne.

Polecenie ‘Puste wierze’ wstawi wiersz po każdy elemencie etykiet wierszy.

Elementów tych musi być co najmniej 2.

Podział taki zdecydowanie pozytywnie wpływa na przejrzystość tabeli przestawnej.

Gdy elementów etykiet wierszy jest więcej niż 2, podział zostanie wprowadzony po każdej grupie najniższej kategorii etykiet.

Tabela przestawna może być pokazywana w formie kompaktowej lub konspektu.

Można także wybrać powtarzanie wszystkich etykiet elementów.

Przykład 3.

(Arkusz: ‘Tabele Przestawne Ekspert 2’)

Polecenie ‘NARZĘDZIA TABEL PRZESTAWNYCH’ g karta ‘ANALIZA’ g ‘Pola, elementy i zestawy’ g ‘Lista formuł’ wprowadza w nowym arkuszu listę wszystkich formuł i pól obliczeniowych wraz z formułami wg których są obliczane.

Lista taka może być przydatna szczególnie gdy pól tych jest dużo, lub też gdy nie my je utworzyliśmy.

Przykład 4.

(Arkusz: ‘Tabele Przestawne Ekspert 2’)

Podwójne kliknięcie lewym klawiszem myszy w jedną z danych tabeli przestawnej powoduje dodanie nowego arkusza zawierającego zbiór rekordów które posłużyły do obliczenia tej wartości.

Dla przykładu podwójne kliknięcie w liczbę 19 381 doda…

… nowy arkusz a w nim wszystkie rekordy spełniające filtry dla tego pola, czyli kategoria Colour, Segment Facial make-up oraz kraj wybrany w filtrze: RUSSIA.

Dodane zostały całe rekordy z tabeli źródłowej, mimo że w tabeli przestawnej pokazywana jest tylko sprzedaż dla 2014 roku.

Jeśli zsumujemy sprzedaże 2014 dla wszystkich rekordów musimy otrzymać wartość w którą klikaliśmy.

Funkcjonalność może być użyteczna gdy chcemy sprawdzić z czego wynika jakaś liczba bez modyfikowania tabeli przestawnej.

Często w ten sposób sprawdzane są wyniki które wyglądają ‘podejrzanie’.

Tak wygenerowany arkusz można śmiało skasować nie będzie to miało wpływu na tabelę przestawną czy arkusz z danymi źródłowymi.

Przykład 5.

(Arkusz: ‘Tabele Przestawne Ekspert 3’)

W tym przykładzie będziemy wykorzystywać bazę danych Microsoft Access BazaDanych.mdb znajdujący się w katalogu z ćwiczeniami Excela.

Przed przerobieniem tego przykładu sugeruję zapoznanie się z lekcją ‘Kwerendy’.

Tabelę przestawną możemy utworzyć nie tylko na podstawie danych będących w arkuszu Excela, ale także na podstawie danych zewnętrznych.

Aby przygotować tabelę przestawną korzystającą z zewnętrznego źródła danych po kliknięciu ikony ‘Tabela Przestawna’ na karcie ‘Wstawianie’ w oknie ‘Tworzenie Tabeli przestawnej’ zaznaczamy: ‘Użyj zewnętrznego źródła danych’ i klikamy przycisk ‘Wybierz połączenie…’.

W oknie ‘Istniejące połączenia’ klikamy ‘Wyszukaj więcej’, po czym w oknie ‘Wybieranie źródła danych’, wskazujemy bazę danych.

W kolejnym oknie wybieramy jedną z tabel bazy danych i klikamy OK.

Powrócimy do okna ‘Tworzenie tabeli przestawnej’ w której została dodana nazwa połączenia BazaDanych.

Po kliknięciu OK. w powyższym oknie utworzona zostanie tabela przestawna, którą obsługujemy zupełnie tak samo jak tabelę przestawną utworzoną na podstawie danych znajdujących się w Excelu.

Jeśli otrzymamy od kogoś plik z tabelą przestawną w którym nie możemy znaleźć arkusza z danymi, tabela taka najprawdopodobniej korzysta właśnie z danych zewnętrznych.

Korzystanie z polecenia ‘Tabela przestawna’ z karty ‘Wstawianie’ umożliwia wybranie tylko jednej tabeli.

Gdybyśmy chcieli utworzyć tabelę przestawną, w której będziemy wykorzystywać dane z wielu tabel bazy danych należy wybrać polecenie ‘Z innych źródeł’ znajdujące się na karcie ‘Dane’, po czym kliknąć ikonę opisaną jako ‘Z programu Microsoft Query’.

Tabela przestawna będzie bazować na kwerendzie tworzenie kwerend zostało dokładnie opisane w lekcji kwerendy, aby nie powtarzać dwukrotnie tego samego w tej lekcji jest omówione pobieżnie.

W kolejnym kroku wybieramy MS Access Database.

Wskazujemy lokalizację pliku BazaDanych.mdb

Klikając przycisk oznaczony symbolem ‘>’ kiedy w lewym okienku zaznaczona jest nazwa tabeli możemy dodać wszystkie kolumny tabeli do kwerendy.

W kolejnym kroku mamy możliwość dodania filtrów.

Następny ekran umożliwia sortowanie.

W ostatnim oknie kreatora kwerend wybierzemy pierwszą opcję ‘Zwróć dane do programu Microsoft Office Excel’.

Dzięki drugiej opcji możemy uruchomić program Microsoft Query umożliwiający przeprowadzenie zaawansowane operacje na danych przed ich importem do tabeli przestawnej.

Program Microsoft Query został omówiony w lekcji ‘Kwerendy’.

Po kliknięciu Zakończ w kreatorze kwerend uaktywnione zostanie okno ‘Importowanie danych’.

W oknie tym zaznaczamy opcję ‘Raport tabeli przestawnej’ oraz wskazujemy miejsce w którym ma się znaleźć nowo tworzona tabela przestawna. Klikamy OK.

Obsługa tabeli przestawnej która korzysta z kwerendy niczym nie różni się od obsługi tabeli przestawnej opartej na danych będących w Excelu.

Przeciągnijmy pola tabeli przestawnej do odpowiednich obszarów na przykład tak jak na poniższym rysunku.

Na karcie dane możemy uaktualnić dane w tabeli przestawnej korzystając z przycisku Odśwież wszystko.

Dzięki poleceniu Właściwości mamy możliwość edycji kwerendy z której korzysta tabela przestawna (powrót do edycji kwerendy został opisany w lekcji Kwerendy).

Powyższe przykłady nie wyczerpują wszystkich możliwości tabel przestawnych, ale pozwalają wyrobić umiejętność pracy z nimi, dzięki czemu użytkownik Excela sam może dalej zgłębiać ich możliwości w związku z konkretnymi problemami biznesowymi, jakie zostaną przed nim postawione.