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

Przykład 1.

Pola Obliczeniowe w Tabelach Przestawnych

(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ż 2014’ i ‘Sztuki 2014’ 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ż 2014’ i wciskamy przycisk ‘Wstaw pole’, ręcznie (z klawiatury) wpisujemy znak dzielenia ‘/’, a następnie wybieramy pole ‘Sprzedaż 2013’ 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ą, zmieniamy jego format na procenty a opis na ‘Zmiana Sprzedaży’

Zmieńmy też od razu opisy kolumn C i D na Sprzedaż 2014 i Sztuki 2014 (ponieważ takie pola już istnieją w tabeli z danymi konieczna jest spacja na końcu nazwy).

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 lewo, aby znajdowała się przy Sprzedaży 2014.

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.

Nazwę pola możemy wprowadzić już na tym etapie w polu ‘Nazwa:’.

Po sformatowaniu liczb na procenty w kolumnie ‘Zmiany Ilości’ tabela przestawna 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ą.

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żę 2014’ przez ‘sprzedaż 2014’ 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 skomplikowanych 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.

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.

Elementy obliczeniowe zostały omówione w przykładzie 5.

Przykład 2.

(Arkusz: ‘Tabele Przestawne 3’)

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 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 3’)

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 domyślnie 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.

Aby wyłączyć generowanie funkcji WEŹDANETABELI, wybieramy ANALIZA " Tabela przestawna " Generuj funkcję WeźDaneTabeli.

Teraz już możemy wpisywać formuły jak do każdego innego zakresu.

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.

Innym przykładem może być policzenie Średniej sprzedaży na produkt w każdym z przedziałów.

Dodawanie formuł korzystających z danych w tabeli i będących poza nią jest użyteczne jako możliwość policzenia czegoś szybko a nie jako profesjonalne wykorzystanie tabel przestawnych.

Przykład 4.

(dane arkusz: ‘Tabele Przestawne 4’)

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

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

(Jeżeli w tym samym pliku 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 na których ćwiczymy.)

Dla sumy ze sprzedaży na karcie ‘Pokazywanie wartości jako’ wybieramy ‘% sumy 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.

Nie można łączyć automatycznego grupowania o równych przedziałach z grupowaniem o nierównych przedziałach.

Więcej praktycznych przykładów w lekcji: ‘Tabele Przestawne dla Ekspertów’.