Tabele Przestawne dla Zaawansowanych

Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: Tabele Przestawne dla Zaawansowanych.xlsx tylko ich samodzielne przerobienie daje gwarancję zapamiętania tej lekcji

Przykłady dla wszystkich lekcji szkolenia Excel 2007: ExcelSzkolenie.pl Cwiczenia Excel 2007.zip

Przykład 1.

(dane arkusz: ‘Tabele Przestawne 1’, rozwiązanie arkusz: ‘Tabele Przestawne 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.

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, przeciągając je w inne miejsce.

Przeciągamy pola ‘Segment’ i ‘Brand’ do prostokąta oznaczonego ‘Upuść pola wierszy tutaj’.

Przeciągamy pola ‘Sprzedaż 2011’ i ‘Sztuki 2011’ w miejsce oznaczone ‘Upuść elementy danych tutaj’

W efekcie czego uzyskujemy tabelę wyglądającą tak jak na poniższym rysunku.

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, na wstążce będą widoczne dwie dodatkowe karty: ‘Opcje’ i ‘Projektowanie’.

Aby dodać pole obliczeniowe z karty ‘Opcje’ 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 A5.)

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ż 2011’ i wciskamy przycisk ‘Wstaw pole’, ręcznie (z klawiatury) wpisujemy znak dzielenia ‘/’, a następnie wybieramy pole ‘Sprzedaż 2010’ i znów wciskamy przycisk ‘Wstaw pole’, po czym dopisujemy na końcu formuły ‘-1’.

Do tabeli przestawnej zostanie dodana kolumna z nagłówkiem ‘Suma z Pole1’.

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 wartości...’

Po czym klikamy na ‘Format liczby’.

i formatujemy liczbę jako procent z jednym miejscem po przecinku.

Zmienimy teraz nagłówek wpisując w komórkę E6 ‘Zmiana sprzedaży’.

Analogicznie sformatujemy kolumny ze sprzedażą (bez miejsc dziesiętnych i z odstępem co 3 liczby) oraz nadamy im nazwy Sprzedaż 2011 i Sztuki 2011.

Ponieważ nazwy ‘Sprzedaż 2011’ i ‘Sztuki 2011’ już istnieją w tabeli z danymi wyświetlony zostanie poniższy komunikat.

Wystarczy na końcu każdej z tych nazw dodać spację aby Excel zaakceptował tą nazwę.

Rozszerzamy wiersz 6 tak aby był 2 razy szerszy od zwykłego wiersza i wybieramy dla niego opcję ‘Zawijaj tekst’. Dzięki czemu będziemy mogli zwęzić kolumny C, D i E. Dodatkowo przenosimy kolumnę ze zmianą sprzedaży w lewe, aby znajdowała się przy Sprzedaży 2011.

Po tych zmianach tabela przestawna (a dokładniej jej górna część) będzie wyglądać tak jak na poniższym rysunku.

Dodajemy teraz drugie pole obliczeniowe z informacją o tym jak zmieniła się sprzedaż w sztukach w porównaniu z rokiem ubiegłym. Wszystkie czynności przeprowadzamy analogicznie do przygotowywania Pola1 z tą tylko różnicą że odnośniki dotyczą sztuk a nie sprzedaży.

Po sformatowaniu tabela będzie wyglądała tak jak poniżej.

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ą. Tym razem proponuję zmienić nazwę pola z automatycznie nadawanej ‘Pole3’ na ‘Marża%’. Okaże się to pomocne w dalszej części tej lekcji.

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.

Zamiast jeszcze raz dzielić ‘marżę 2011’ przez ‘sprzedaż 2011’ użyjemy już wcześniej przygotowanego pola Marża%, które jest wynikiem tego dzielenia.

Jak widać wcześniej utworzone pola obliczeniowe mogą bez problemów posłużyć do tworzenia bardziej skąplikowanych pól obliczeniowych.

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.’.

Dodatkowo ukrywamy 5 wiersz i formatujemy filtry i wiersz nagłówków tak jak jest to pokazane poniżej.

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. Wszystkie formuły obliczeniowe działają poprawnie niezależnie od użytej kombinacji filtrów.

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.

Przykład 2.

(Arkusz: ‘Tabele Przestawne 3’ rozwiązanie: ‘Tabele Przestawne 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 i wybieramy polecenie ‘Grupuj…’.

Formatowanie tabeli przestawnej jest takie samo jak format który został wybrany dla poprzedniej tabeli przestawnej którą przygotowywaliśmy. Format tabeli jaki widzisz podczas przygotowywania tego ćwiczenia może więc odbiegać od pokazanego na poniższym i kolejnych rysunkach.

W okienku Grupowanie wprowadzamy początek i koniec przedziału, według którego 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.

(Arkusz: ‘Tabele Przestawne 4’)

Kontynuując pracę nad tabelą przestawną z poprzedniego przykładu chciałbym przedstawić dość wygodny trick. 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 tabale zmieni się formuła taka nadal będzie korzystać z tych samych adresów.

Jeżeli formuła nie zawiera adresowania absolutnego, możemy ją też wprowadzić pod/nad tabelą a później skopiować tak aby objęła dane w tabeli.

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

Przykład 4.

(dane arkusz: ‘Tabele Przestawne 5’, rozwiązanie: ‘Tabele Przestawne 6’)

W tym przykładzie także zgrupujemy dane, ale w przedziałach o różnej wielkości.

Nierówne przedziały dla wielu zjawisk ekonomicznych i społecznych są znacznie bardziej odpowiednie do przeprowadzania analiz, np. dla danych cenowych, gdzie sprzedaż produktów z segmentów value i mass wymaga podziału na wiele małych przedziałów, a ceny produkty premium są znacznie bardziej zróżnicowane.

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ż’.

Jeżeli w tym samym arkuszu te dane były już grupowane w innej tabeli przestawnej Excel może także w tej tabeli zgrupować je w ten sam sposób. W takim przypadku klikamy oznaczenia grup prawym klawiszem i wybieramy polecenie ‘Rozgrupuj…’. Połączenie to działa w obie strony, zmiany wprowadzone w grupowaniu w tej tabeli będą także wprowadzane w pierwszej tabeli przestawnej. Najprostszym rozwiązaniem jest skopiowanie arkusza z danymi i użycie innego dla każdej z tabel.

Po uzyskaniu tabeli przestawnej wyglądającej tak jak na poniższym rysunku, klikamy prawym klawiszem myszy w polu danych i zmieniamy w oknie ‘Ustawienia pola wartości’ na karcie ‘Podsumowanie według na ‘Suma’.

A na karcie ‘Pokazywanie wartości jako’ wybieramy ‘% kolumny’.

Zaznaczamy produkty ze sprzedażą poniżej 50 (zakres A5:A29), klikamy dowolną komórkę w tym obszarze prawym klawiszem myszy i wybieramy polecenie ‘Grupuj…’.

Do tabeli zostanie dodana kolumna z nagłówkiem ‘sprzedaż2’ a jej pierwszym elementem będzie: ‘Grupuj1’.

Po dwukrotnym kliknięciu lewym klawiszem myszy w komórkę A5 (na tekście ‘Grupuj1’) lub pojedynczym w symbol ‘-‘ w tej komórce, 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ę A5.

W analogiczny sposób proponuję zgrupować, ukryć i zmienić nazwy dla pozostałych komórek tabeli np. w takim podziale jak zaproponowany poniżej.

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, zmiana nazwy grupy polega na wpisaniu nowej nazwy bezpośrednio w komórkę A5.

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ę (dowolnego poziomu) klikamy jej nazwę prawym klawiszem i wybieramy polecenie: ‘ 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:

1. Jedna lub więcej 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.

2. Dla niektórych lub choćby jednego rekordu brakuje danych. Rozwiązanie: puste komórki należy wypełnić zerami i odświeżyć dane w tabeli przestawnej.

3. 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 5.

(Arkusz: ‘Tabele Przestawne dla Zaawans 7’)

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.

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 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).

Podczas pracy z kwerendami na pasku Start wyświetlana jest ikona Microsoft Query, jeżeli nie zamierzamy edytować kwerendy w tym programie możemy ją ignorować.

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.