Tabele Przestawne dla Zaawansowanych

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

Przykład 1.

(Arkusz: ‘Tabele Przestawne dla Zaawans 1’ rozwiązanie: ‘Tabele Przestawne dla Zaawans 2’)

Dane w poniższej tabeli przedstawiają sprzedaż w dolarach i sztukach oraz marżę wyrażoną w dolarach dla:

24 miesięcy, 8 krajów, 5 kategorii produktów, 19 segmentów i 30 brandów. Tabela ta ma 6491 linii z danymi.

Jest to uproszczony przykład, którego założenia oparte są na rzeczywistym raporcie funkcjonującym w jednej z korporacji kosmetycznych.

Naszym zadaniem jest przedstawienie tych danych w formie raportu, który będzie elastyczny i czytelny dla użytkowników, niemających dużej wprawy w posługiwaniu się tabelami przestawnymi.

Podobnie jak w lekcji ‘Tabele przestawne podstawy’ zaczniemy od sporządzenia tabeli przestawnej.

Proces ten opisany jest dokładnie w powyżej wspomnianej lekcji.

Po utworzeniu tabeli przeciągamy: Miesiąc, Kraj i Kategoria do prostokąta oznaczonego ‘Upuść pola stron tutaj’

Kolejność pól wierszy i kolumn możemy zmieniać w dowolnym momencie, klikając na pole i przeciągając je w inne miejsce.

\

Po wykonaniu powyższego przeciągamy pola ‘Segment’ i ‘Brand’ do prostokąta oznaczonego ‘Upuść pola wierszy tutaj’.

Po powyżej opisanej operacji nasza tabela przestawna będzie wyglądać tak jak na poniższym rysunku.

Przeciągamy pola ‘Sprzedaż 2007’ i ‘Sztuki 2007’ w miejsce oznaczone ‘Upuść elementy danych tutaj’ w efekcie czego uzyskujemy tabelę wyglądającą tak jak na poniższym rysunku.

Pole ‘Dane’ przeciągamy tak jak pokazuje to niebieska strzałka poniżej do komórki ‘D5’ z napisem ‘Suma’.

Do tej pory przeprowadzaliśmy operacje znane już wcześniej z lekcji ‘Tabele przestawne dla początkujących’ teraz do naszej tabeli dodamy Pole obliczeniowe’

Jeżeli w arkuszu, w którym się znajdujemy jest tabela przestawna i aktywna jest komórka wewnątrz tej tabeli, menu ‘Tabela Przestawna’ będzie widoczne a jego ikony aktywne.

Menu ‘Tabela Przestawna’ możemy także zawsze dodać samodzielnie, więcej informacji na ten temat znajduje się w lekcji: Zmiany w menu i własne ikony.

Aby dodać pole obliczeniowe z Menu Tabela przestawna wybieramy ‘Formuły’ → ‘Pole obliczeniowe...’

(Opcja formuły jest aktywna tylko, jeśli aktywna jest komórka wewnątrz tabeli przestawnej - na poniższym rysunku aktywna jest komórka D8.)

Okno ‘Wstaw pole obliczeniowe’ służy do tworzenia formuł, których wyniki będą wyświetlane w tabeli przestawnej.

W naszym przykładzie wybieramy pole ‘Sprzedaż 2007’ i wciskamy przycisk ‘Wstaw pole’, ręcznie (z klawiatury) wpisujemy znak dzielenia ‘/’, a następnie wybieramy pole ‘Sprzedaż 2006’ i znów wciskamy przycisk ‘Wstaw pole’, po czym dopisujemy na końcu formuły ‘-1’.

Ponieważ zmiana wielkości sprzedaży powinna być wartością procentową, klikamy prawym klawiszem na nagłówku ‘Suma z Pole1’ i wybieramy opcję ‘Ustawienia Pola...’

W oknie ‘Pole tabeli przestawnej’ klikamy przycisk ‘Liczby...’, a w oknie ‘Formatuj komórki, które się wyświetli wybieramy kategorię ‘Procentowe’ z jednym miejscem po przecinku. Klikamy OK w obu oknach.

Ponieważ nagłówek wprowadzony automatycznie przez Excel ‘Suma z Sprzedaż 2007’ nie wygląda zbyt profesjonalnie, stajemy w tej komórce i zmieniamy go na ‘Sprzedaż 2007’.

Pojawi się komunikat o błędzie z informacją, że ‘Ta nazwa pola tabeli przestawnej już istnieje’.

Jeżeli mimo to, taką właśnie nazwę chcemy wprowadzić w nagłówku naszej tabeli przestawnej, najprostrzym rozwiązaniem będzie dodanie spacji na jej końcu.

Dodatkowo formatujemy też nagłówki (wyrównanie akapitu do środka).

Dodajemy Pole2 – Sztuki zmiana, które informuje nas jak zmieniła się sprzedaż w sztukach w porównaniu z rokiem ubiebłym. Wszystkie czynności przeprowadzamy analogicznie do przygotowywania Pola1 z tą tylko różnicą że odnośniki dotyczą sztuk a nie sprzedaży.

Trzecim polem obliczeniowym jakie dodamy będzie marża. W danych źródłowych marża podana jest wartościowo, dzięki czemu wystarczy ją podzielić przez sprzedaż i uzyskujemy marżę procentową.

Używając wcześniej opisanych technik formatujemy pole ‘Marża %’, tak aby wyglądało jak na poniższym rysunku.

Ostatnim polem obliczeniowym będzie zmiana marży w stosunku do ubiegłego roku.

W poniższym przykładzie zamiast jeszcze raz dzielić ‘marżę 2007’ przez ‘sprzedaż 2007’ użyto już wcześniej przygotowane ‘Pole3’, które jest wynikiem tego dzielenia.

Zmiana wielkość marży powinna być pokazana w punktach procentowych a nie procentach. Ponieważ nie ma takiego formatu, musimy go stworzyć samodzielnie, w niestandardowych formatach liczb wprowadzamy „0,0%p.”.

Poniżej przedstawiony jest efekt wyżej opisanych zmian.

Raport przygotowany w formacie tabeli przestawnej ma tą przewagę nad zwykłymi tabelkami że jest bardzo elastyczny. Jeżeli zamiast podziału segmentów na brandy, wolimy sprawdzić w jakich segmentach występują dane brandy wystarczy przeciągnąć pole ‘Segment’ w prawo, tak jak jest to pokazane poniżej.

Wynik powyżej opisanej zmiany znajduje się poniżej.

Oczywiście nie jest to jedyna opcja możemy także np. przeciągnąć pole ‘Kategoria’ do pól wierszy a ‘Brand’ do pól stron, co da nam podział Kategorii na segmenty. Możliwości jest bardzo wiele zachęcam do eksperymentów.

Na koniec pozostaje nam formatowanie tabeli tak, aby była jak najbardziej ‘user friendly’ (także dla użytkowników, którzy nie wiedzą czym jest tabela przestawna).

Proponuje ukryć wiersz 5, w którym nie ma niczego co mogłoby być przydatne dla użytkownika tego raportu, oraz pokolorować filtry i nagłówki kolumn i wierszy na kolor zielony (ponoć uspokaja). A także rozdzielić kolumny zawierające sprzedaż w usd, sprzedaż w sztukach i marżę pionowymi liniami.

Aby zmiany formatowania nie zostały utracone podczas zmiany filtrów, opcja ‘Autoformatowanie tabeli’ w ‘Opcjach tabeli przestawnej’ musi być odznaczona, a zmiany w formatowaniu musimy wprowadzać po wcześniejszym wybraniu opcji ‘Wszystkie’ dla wszystkich filtrów.

Dodatkowo tabela będzie wyglądała lepiej, jeśli wyłączymy linie siatki.

Przykład 2.

(Arkusz: ‘Tabele Przestawne dla Zaawans 3’ rozwiązanie: ‘Tabele Przestawne dla Zaawans 4’)

Poproszono nas o ustalenie ile produktów miało sprzedaż poniżej 100, powyżej 800, oraz w grupach co 50 pomiędzy 100 a 800.

Przygotujmy tabelę przestawną na podstawie danych i przenieśmy dość nietypowo nazwę produktu do danych, a sprzedaż do pola wierszy.

W kolejnym kroku klikamy kolumnę sprzedaż prawym klawiszem myszy, wybieramy polecenie ‘Grupuj i pokaż szczegóły” a potem „Grupuj”.

W okienku Grupowanie wprowadzamy początek i koniec przedziału po którym chcemy grupować, oraz wartość co ile mają być grupowane dane pomiędzy 100 a 800. Klikamy OK.

Wynik pokazuje odpowiada na pytanie postawione w tym ćwiczeniu.

Powyższy wynik możemy wzbogacić o sprzedaż dla każdego z tych przedziałów, udział w sprzedaży i udział w ilości produktów.

Z tak przygotowanej tabeli można wyciągnąć wiele ciekawych wniosków, widać np. od razu że 50 produktów ze sprzedażą poniżej 100, stanowi aż 10% wszystkich oferowanych produktów a generują one mniej niż 1% sprzedaży.

Przykład 3.

(dane arkusz: ‘Tabele Przestawne dla Zaawans 3’, rozwiązanie: ‘Tabele Przestawne dla Zaawans 4’)

W tym przykładzie także zgrupujemy dane ale w nierównych przedziałach, które dla wielu zjawisk rynkowych są znacznie bardziej odpowiednie do analizowania danych. Na przykład dla danych cenowych.

Zaczniemy od przygotowania tabeli przestawnej na podstawie danych znajdujących się w arkuszu ‘Tabele Przestawne dla Zaawans 3’.

Do obszaru ‘pola wierszy’ i obszaru ‘danych’ przenosimy ‘sprzedaż’.

W polu danych wybieramy ustawienia pola, klikamy ‘Opcje >>’ i zmieniamy ‘Pokaż dane jako:’ na ‘% kolumny’.

Zaznaczamy produkty ze sprzedażą poniżej 50, klikamy dowolną komórkę w tym obszarze prawym klawiszem myszy i wybieramy polecenie ‘Grupuj i pokaż szczegóły’ a później ‘Grupuj’.

Do tabeli zostanie dodana kolumna z nagłówkiem ‘Grupuj1’.

Po dwukrotnym kliknięciu lewym klawiszem myszy w komórkę A5 (z tekstem Grupuj1), grupa zostanie zwinięta i kolumnie C pokazany zostanie udział sprzedaży dla całej grupy.

Nazwę grupy możemy zmienić z ‘Grupuj1’ na <50 po prostu wpisując nowy tekst w komórkę.

W analogiczny sposób proponuję zgrupować, ukryć i zmienić nazwy dla pozostałych komórek tabeli.

Wynikiem takiego grupowania będzie tabela taka jak pokazana poniżej.

Grupowanie możliwe jest na wielu poziomach, po zaznaczeniu 2 pierwszych grup i ponownym wyborze polecenia ‘Grupuj’ uzyskamy połączenie tych grup.

Do tabeli zostanie dodana kolejna kolumna i drugi poziom grupowania, tak samo możemy zmienić jego nazwę.

Poprzez podwójne kliknięcie lewym klawiszem myszy zamkniemy tą grupę i uzyskamy dane o udziale w sprzedaży dla produktów o sprzedaży poniżej 200.

Aby rozgrupować grupę (każdego poziomu) klikamy jej nazwę prawym klawiszem i wybieramy polecenia: ‘Grupuj i pokaż szczegóły’, ‘ Rozgrupuj’.

Aby połączyć 2 grupy bez tworzenia kolejnego poziomu grupowania, należy otworzyć istniejące grupy, zaznaczyć wszystkie ich pola, i wybrać polecenie ‘Grupuj’.

W naszym przykładzie połączymy grupy <50 i 50-200. Po pokazaniu szczegółów (otworzeniu) tych grup zaznaczamy komórki B5:B106 i wybieramy ‘Grupuj’.

‘Stare’ grupy przestają istnieć i na ich miejsce powstaje nowa grupa, której nazwę możemy zmienić na <200.

UWAGA

Dane w powyższych przykładach zostały tak przygotowane, że ich grupowanie przebiegało bez problemów.

Grupując inne dane możemy napotkać na poniższy komunikat:

Najbardziej prawdopodobne przyczyny uniemożliwiające grupowanie to:

- jedna z danych które uważaliśmy za dane liczbowe, tak naprawdę jest tekstem. Łatwo jest zamienić tekst z liczbami na liczby przemnażając każdą z komórek przez 1. Po tej operacji należy odświeżyć dane w tabeli przestawnej.

- dla niektórych lub choćby jednego rekordu brakuje danych, puste komórki należy wypełnić zerami i odświeżyć dane w tabeli przestawnej.

- część tabeli jest już zgrupowana, nie można łączyć automatycznego grupowania o równych przedziałach z grupowaniem o nierównych przedziałach.

Przykład 4.

(Arkusz: ‘Tabele Przestawne dla Zaawans 5’)

Często chcielibyśmy szybko wpisać do arkusza formułę, która liczyła by coś na podstawie danych z tabeli przestawnej.

Dla przykładu sprawdzimy czy w kolumnie E udziały sprzedaży na pewno zostały właściwie policzone, samodzielnie obliczmy je na podstawie danych z kolumny C.

Niestety okazuje się, że Excel w sposób dość skomplikowany zapisuje odwołania do tabeli przestawnej.

Formuł nie można kopiować nawet po usunięciu symboli $, bo odwołania do komórek nie są adresami a opisami np. ‘<’ oznacza pierwszy z przedziałów grupowania.

Łatwo można to ominąć wpisując formułę z klawiatury.

Taką formułę można oczywiście bez problemu kopiować. Należy pamiętać że jeżeli tabela zmieni się formuła taka nadal będzie korzystać z tych samych adresów.

Sposób ten jest użyteczny jako możliwość policzenia czegoś szybko a nie jako profesjonalne wykorzystanie tabel przestawnych.

Przykład 5.

(Arkusz: ‘Tabele Przestawne dla Zaawans 6’)

W tym przykładzie będziemy wykorzystywać bazę danych Microsoft Access BazaDanych.mdb, przed rozpoczęciem wykonywania tego przykładu należy zapisać ten plik na swoim komputerze.

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.

W przypadku Excela 2003 ma to tą ogromną zaletę, że zewnętrzne źródło danych (najczęściej baza danych Microsoft Access) nie musi być ograniczone limitem 65536 wierszy, który obowiązuje w Excelu.

Aby przygotować bazę danych korzystającą z zewnętrznego źródła danych w ‘Kreatorze tabel i wykresów przestawnych’ w 1 kroku wybieramy 2 opcję: ‘Zewnętrzne źródło danych’.

W kolejnym kroku klikamy przycisk ‘Pobierz dane...’

W poniżej przedstawionym oknie wybieramy ‘MS Access Database’ i klikamy OK.

W kolejnym oknie wskazujemy bazę danych, z której chcemy korzystać w naszym przykładzie BazaDanych.mdb.

Podczas wybierania bazy danych na ekranie będzie wyświetlany komunikat ‘Łączenie ze źródłem danych...’.

Uruchomiony zostanie ‘Kreator kwerend’, który został już opisany w lekcji ‘Kwerendy’, w której korzystaliśmy z tej samej bazy danych.

Postępujemy dokładnie tak samo jak w lekcji ‘Kwerendy’.

W ostatnim oknie kreatora możemy przejść do programu ‘Microsoft Query’ aby utworzyć bardziej skomplikowaną kwerendę, lub jak w naszym przykładzie pozostawiamy wybraną pierwszą opcję i klikamy przycisk ‘Zakończ’.

(program ‘Microsoft Query’ także został opisany w lekcji ‘Kwerendy’)

Klikamy ‘Dalej >’.

Wybieramy miejsce w którym ma znaleźć się tworzona tabela przestawna i klikamy ‘Zakończ’.

Obsługa tabeli przestawnej która korzysta z zewnętrznego źródła danych 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 tak jak na poniższym rysunku.

Aby edytować kwerendę należy kliknąć dowolną komórkę tabeli prawym klawiszem myszy i wybrać polecenie ‘Kreator tabel przestawnych’.

Klikamy ‘< Wstecz’ w 3 kroku kreatora...

...i ‘Pobierz dane...’ w 2 kroku...

... dochodząc do ‘Kreatora kwerend’.

Kiedy jest uruchomiony ‘Kreator kwerend’ na pasku Start Windows wyświetlana jest ikona ‘Microsoft Query’, jeżeli nie zamierzamy edytować kwerendy w tym programie powinniśmy ją ignorować.

Powyższe przykłady nie wyczerpuje 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.

Szkolenie Excel 2003 Szkolenie Excel 2007