Zerknijmy na prosty rejestr transakcji.

Jak widzimy arkusz ma on dość prostą strukturę. Każdy wiersz zawiera dane dotyczące jednego zamówienia. W każdym wierszu mamy takie dane jak data zamówienia, nazwisko handlowca, kraj dostawy, termin płatności, kwota sprzedaży i koszt produkcji. Ogólnie mamy 9 unikalnych sprzedawców i około 1000 transakcji na przestrzeni jednego roku.

Z tego pliku możemy wyciągnąć wiele interesujących m.in. danych:

1. Który sprzedawca wygenerował największy obrót
2. Ile obrotu wygenerował każdy ze sprzedawców
3. Do jakiego państwa najwięcej sprzedajemy (liczba transakcji i suma wartości wszystkich zamówień)
4. W jakim miesiącu był najwyższy obrót
5. Możemy też zrobić dwuwymiarową „macierz” przedstawiającą wysokość obrotów poszczególnych handlowców do poszczególnych państw

… i jeszcze pewnie z kilkanaście innych ciekawych zestawień …

Osoby które nie znają tabel przestawnych spędzają pół dnia na ręcznym lub półautomatycznym generowaniu tego typu zestawień. Gdy nie znałem dobrze Excela (było to dawno temu bo około 2010 roku i nieprawda) sam poświęciłem z godzinę na napisanie prostego makra, które wygenerowało zestawienie jakie bym zrobił za pomocą tabel przestawnych w 30 sekund.

Innym razem na szkoleniu po pokazaniu jak zrobić zestawienie który sprzedawca ile sprzedał usłyszałem od jednego z kursantów, że on dla podobnych danych wykonywał następujące czynności:

1. Włączał filtrowanie
2. Filtrował po nazwisku wybranego sprzedawcy
3. Przechodził do wiersza sumy
4. Zapisywał w innym arkuszu nazwisko sprzedawcy i ową sumę
5. Powrót do kroku nr 2 i wybieranie kolejnego sprzedawcy

Dobrze, że chociaż tutaj jest tylko 9 unikalnych sprzedawców to jeszcze takie pół-automatyczne generowanie raportu dużo czasu nie pochłonie, ale co by było, gdyby owe zestawienie miało milion wierszy i np. 500 różnych sprzedawców?

Poniżej opiszę kilkanaście prostych zestawień dzięki którym poznasz solidne podstawy dotyczące tabel przestawnych.

Pobierz przykładowe pliki do ćwiczeń

Kto ile sprzedał

Stwórzmy pierwszą tabelę przestawną. W tym celu w arkuszu Dane umieść kursor w dowolnej komórce naszej tabeli. Następnie na karcie Wstawianie wybierz polecenie Tabela przestawna. Pojawi się okno dialogowe Tabela przestawna z innej tabeli lub zakresu.W polu Tabela/zakres Excel podaje zakres komórek, które zostaną poddane analizie (W naszym przypadku dane znajdują się w obiekcie Tabela o nazwie Sprzedawcy, gdyby był to zwykły zakres komórek, to w polu Tabela/zakres pojawiłby się zakres komórek: Dane!$A$1:$G$1060). Domyślnie jest wybrana opcja, aby tabela przestawna została utworzona w nowym arkuszu, co jak najbardziej jest dobrym pomysłem, gdyż nasza tabela z danymi zajmuje sporo miejsca, ale nic nie stoi na przeszkodzie, aby tabela przestawna znajdowała się w tym samym arkuszu obok tabeli z danymi.

Po kliknięciu w przycisk OK ukaże na się taki oto widok. Po lewej stronie w zakresie komórek od A3 do C20 mamy tabelę przestawną (na razie jest pusta) a po prawej stronie znajduje się panel Pola tabeli przestawnej. Tutaj taka mała uwaga: ponieważ kursor znajduje się wewnątrz tabeli przestawnej (w moim przypadku jest to komórka A3) to po prawej stronie jest ten panel. Jeśli „odkliknę” poza tabelę przestawną (np. w komórkę H10) to owy panel zniknie. Natomiast jeśli z powrotem kliknę w tabelę przestawną (w moim przypadku jest to dowolna komórka z zakresu A3:C20) to ten panel z powrotem się pojawi.

Jeśli kursor się znajduje wewnątrz tabeli przestawnej a po prawej stronie nie masz panelu Pola tabeli przestawnej to możliwe, że przez przypadek kiedyś wcześniej ktoś go wyłączył. Aby go włączyć należy umieścić kursor wewnątrz tabeli przestawnej a następnie na karcie Analiza tabeli przestawnej w grupie poleceń Pokazywanie włączyć polecenie Lista pól.

W starszych wersjach programu Excel owe polecenie jest ukryte na karcie: Opcje (znajdująca się pod napisem Narzędzia tabel przestawnych) w grupie poleceń Pokazywanie/ukrywanie.

Może trochę się rozgadałem, ale panel Pola tabeli przestawnej jest niezbędny do wykonywania ćwiczeń. Przyjrzyjmy się owemu panelowi. W górnej części znajdują się nazwy kolumn z naszej tabeli danych. W dolnej części mamy takie pola jak:

• Filtry
• Kolumny (w starszych wersjach Excela pole nazywa się Etykiety kolumn)
• Wiersze (Etykiety wierszy)
• Wartości

Do dzieła!

Tak więc z górnej części przeciągnijmy pole Sprzedawca do pola Wiersze znajdujące się w dolnej części panelu. Naszym oczom w tabeli przestawnej zobaczymy wymienione unikalne nazwiska sprzedawców. Następnie przeciągnij pole Sprzedaż do pola Wartości. I tym sposobem stworzyliśmy prostą tabelę przestawną (można też w górnej części panelu „klikać” w „ptaszki” znajdujące się na lewo od nazwy pola, ale czasami Excel owe pole przeniesie nie zawsze tam gdzie chcemy).

A tak wygląda nasza tabela przestawna

Odświeżanie tabel przestawnych

Gdy zmienisz coś w tabeli z danymi, dane w tabeli przestawnej się nie zmienią. Dlaczego? Otóż skoroszyt może zawierać wiele tabel przestawnych. Dodatkowo tabela przestawna może analizować np. milion wierszy. Wtedy przeliczenie takiej tabeli przestawnej mogłoby zająć kilka sekund. Niezbyt komfortowo by się pracowało gdy po wpisaniu danej wartości do komórki trzeba odczekać kilka sekund, aby wprowadzić nową wartość do kolejnej komórki.

Z tego powodu Tabele przestawne należy odświeżać zawsze, gdy coś się zmieni w zakresie z danymi. W ramach eksperymentu możesz przy dowolnym Sprzedawcy w kolumnie Sprzedaż wpisać jakąś bardzo dużą liczbę a następnie sprawdzić, czy dane w tabeli przestawnej się zmieniły.

Aby odświeżyć tabelę przestawną najprościej będzie kliknąć prawym przyciskiem myszy na tabeli przestawnej i wybrać polecenie Odśwież.

Jeśli w skoroszycie istnieje kilka tabel przestawnych, które odwołują się do danych, które uległy zmianie, wtedy najlepiej jest wybrać polecenie Odśwież wszystko znajdujące się na karcie Dane w grupie poleceń Zapytania i połączenia. Również na karcie Analiza tabeli przestawnej w Grupie poleceń Dane znajduje się polecenie Odśwież (należy kliknąć w strzałeczkę i z menu wybrać Odśwież wszystko). A najlepiej jest zapamiętać skrót klawiszowy Ctrl + Alt + F5.

Wskazówka: jeśli zmieniłeś wartość sprzedaży jakiemuś sprzedawcy na jakąś dużą liczbę, aby przetestować odświeżanie tabel przestawnych to zalecam z powrotem wpisać oryginalną wartość (i oczywiście odśwież wszystkie tabele przestawne). Zbyt duża wartość sprzedaży w jednym wierszu może utrudnić wykonywanie kolejnych ćwiczeń.

Do jakich państw eksportujemy?

Załóżmy, że się pomyliliśmy i nie chcieliśmy sprawdzać kto ile sprzedał a jaka jest suma eksportu do poszczególnych państw. Czyli w polu Wiersze powinno się znajdować pole Państwo a nie Sprzedawca. Ale nic trudnego! Wystarczy w panelu Pola tabeli przestawnej w polu Wiersze kliknąć w „strzałeczkę” przy Sprzedawca i z menu rozwijanego wybrać polecenie Usuń (można też z Wierszy pole Sprzedawca przeciągnąć poza panel – tak jakby w głąb arkusza). W drugim kroku należy przeciągnąć pole Państwo do Wierszy.

I powinniśmy zobaczyć taką oto tabelę przestawną:

Dwuwymiarowe tabele przestawne

Załóżmy, że chcemy mieć tabelę przestawną która powie nam nie tylko ile ogólnie dany sprzedawca wygenerował obrotu ale również pokaże jak wygląda sprzedaż danego handlowca w rozbiciu na poszczególne państwa. W tym celu stwórzmy od podstaw nową tabelę przestawną.

Na początek przejdźmy do arkusza Dane. Upewnijmy się, że kursor znajduje się w wewnątrz tabeli z danymi. Następnie na karcie Wstawianie wybierz polecenie Tabela przestawna. Jak pojawi się okno dialogowe Tabela przestawna z innej tabeli lub zakresu to kliknij w przycisk OK (zazwyczaj domyślne ustawienia są optymalne). W nowym arkuszu pojawi się tabela przestawna. No to teraz będzie nieco więcej zabawy. Otóż nasze przeciągnijmy pola:

• Sprzedawca do Wierszy
• Sprzedaż do Wartości
• Państwo do Kolumn

Naszym oczom ukaże się taka oto tabela przestawna:

Jak widzimy ta tabela jest mało czytelna, gdyż jest strasznie dużo kolumn i mało wierszy. Gdybyśmy dokonali transpozycji tabeli przestawnej to dane by były czytelniejsze. Ale nic straconego. W dolnej części panelu Pola tabeli przestawnej wystarczy przeciągnąć Sprzedawcę z Wierszy do Kolumn a następnie Państwo z Kolumn do Wierszy.

Wtedy tabela przestawna będzie czytelniejsza

Wskazówka: „to czego jest więcej” przeciągaj do wierszy (gdyż na ekranie się mieści więcej wierszy niż kolumn) a to czego jest mniej przeciągaj do kolumn.

Sprawdzenie jakie rekordy składają się na daną sumę

Podczas pobieżnej analizy danych widzimy, że sprzedawca Dudek sprzedał bardzo dużo towaru do Dżibuti (takie malutkie państewko w Afryce). Może nas to intrygować jak to możliwe. W tym celu możemy dwukrotnie kliknąć w komórkę B10 (przecięcie kolumny Dudek z wierszem Dżibuti).

Excel utworzy nowy arkusz w którym będą wymienione wszystkie sprzedaże Dudka do Dżibuti.

Państwo i sprzedawca w wierszach

Stwórzmy nową tabelę przestawną. Następnie przeciągnij Państwo do Wierszy. W kolejnym kroku przeciągnij Sprzedawcę również do Wierszy. Na końcu przeciągnij Sprzedaż do Wartości.

Tak oto będzie wyglądała nasza tabela przestawna

Czyli mamy na pierwszym poziomie szczegółowości Państwa a potem jest minusik którym możemy rozwinąć szczegóły pokazujące ile dany sprzedawca do tego państwa wyeksportował.

Tutaj wskazówka. Domyślnie wszystkie państwa są „rozwinięte”. Jeśli chcielibyśmy je zwinąć to kliknij w dowolnego sprzedawcę wewnątrz tabeli przestawnej a następnie na karcie Analiza tabeli przestawnej w grupie poleceń Aktywne pole wybierz polecenie Zwiń pole.

A co jeśli chcielibyśmy mieć odwrotne zestawienie? Tzn. najpierw niech będzie sprzedawca a dopiero rozwijając listę przy sprzedawcy chcielibyśmy zobaczyć ile i do jakich państw dany sprzedawca eksportował? Tutaj sprawa jest banalna. W naszych polach wystarczy w polu Wiersze przesunąć Sprzedawcę „nad Państwo” (albo możemy też Państwo przesunąć pod Sprzedawcę).

Inne sposoby agregacji danych

Gdy przeciągniemy Sprzedawcę do Wierszy a Sprzedaż do Wartości to Excel dla każdego sprzedawcy zsumuje liczby znajdujące się w kolumnie Sprzedaż i wygeneruje tabelę zawierającą dwie kolumny: nazwisko sprzedawcy i suma jego sprzedaży.

Jak widzimy domyślną funkcją agregującą jest suma, co zresztą widać w polu Wartości. Po przeciągnięciu pola Sprzedaż w polu wartości pojawia się pole o nazwie Suma z Sprzedaż.

Jednak czasem byśmy chcieli policzyć np. średnią wartość sprzedaży. W tym celu w polu Wartości przy polu Suma z Sprzedaż należy kliknąć w trójkącik a następnie z menu rozwijanego wybrać polecenie Ustawienia pola wartości.

Pojawi się okno dialogowe Ustawienia pola wartości.

W tym oknie dialogowym możemy wybrać funkcję agregującą:

• Suma – zostaną zsumowane wszystkie wartości
• Liczba – zostają zliczone wszystkie niepuste komórki (odpowiednik funkcji ILE.NIEPUSTYCH)
• Średnia – zostanie policzona średnia arytmetyczna
• Maksimum
• Minimum
• Iloczyn – raczej mało przydatna rzecz – wszystkie liczby zostaną przez siebie przemnożone
• Zliczanie –  zostają zliczone komórki zawierające wartości liczbowe (odpowiednik funkcji ILE.LICZB)
• Odchylenie standardowe – Szacowana wartość odchylenia standardowego populacji, gdzie próbka to podzbiór całej populacji.
• OdchStdc – Odchylenie standardowe dla populacji, którą stanowią wszystkie podsumowywane wartości
• Wariancja – Szacowana wariancja populacji, gdzie próbka to podzbiór całej populacji.
• Wariancja populacji – Wariancja dla populacji, którą stanowią wszystkie podsumowywane wartości.

Po wybraniu odpowiedniej funkcji agregującej kliknij w przycisk OK. Aby było ciekawiej, to możesz do pola Wartości kilkukrotnie przeciągnąć pole Sprzedaż a dla każdego z tych pól wybrać inną funkcję agregującą (jeśli w polu Wartości znajduje się więcej niż jedno pole to wtedy w polu Kolumny pojawia się dodatkowe pole z grecką literą sigma i nazwą Wartości mówiące o tym, że w polu wartości znajduje się kilka pól i każde z nich będzie miało jakby „swoją” kolumnę).

A tak wygląda nasza Tabela przestawna

Oczywiście po utworzeniu tabeli przestawnej warto jest sprawdzić czy nie pojawiły się jakieś głupoty. W tym celu np. dla pierwszego sprzedawcy pomnożę (ręcznie na kalkulatorze) średnią wartość sprzedaży przez Liczbę sprzedaży. I co otrzymamy?

1244,371481 * 135 = 167990,149935 (jest pewna drobna rozbieżność, ale wynika ona z błędów w zaokrągleniach liczb)

Ile procent obrotu przyniósł firmie dany sprzedawca?

Przypomnijmy sobie pierwsze ćwiczenie. Przeciągnęliśmy Sprzedawcę do Wierszy a Sprzedaż do Wartości. Domyślnie Excel zsumował wszystkie wartości sprzedaży w rozbiciu na poszczególnych sprzedawców. Problem jest taki, że widzimy te wartości kwotowo a nie procentowo.

Ale tutaj będzie bułka z masłem. Otóż w polu Wartości przy polu Suma z Sprzedaż wystarczy kliknąć w trójkącik wybrać polecenie Ustawienia pola wartości. Pojawi się znane nam już okno dialogowe Ustawienia pola wartości. Kliknij w tym oknie w kartę Pokazywanie wartości jako a następnie w polu Pokaż wartości jako wybierz opcję % sumy końcowej.

I tak oto będzie wyglądała nasza Tabela przestawna

Filtry

Do tej pory w dolnej części panelu Pola tabeli przestawnej przeciągaliśmy odpowiednie nagłówki tabeli do pól Wiersze, Kolumny i Wartości. Nie korzystaliśmy z pola Filtry. Zmieńmy to!

Utwórz nową tabelę przestawną. Następnie tradycyjnie przeciągnij Sprzedawcę do Wierszy a Sprzedaż do Wartości. Następnie Termin płatności przeciągnij do Filtrów.

W efekcie zobaczymy zwykłą tabelę przestawną, ale nad nią znajdzie się dodatkowy Wiersz: Termin płatności z wybraną wartością (Wszystko). Klikając w strzałeczkę przy (Wszystko) rozwinie się okno Filtrów.

Wybierz dowolną wartość np. Z góry. I naszym oczom ukaże się tabela przestawna z tym, że wartość z kolumny Sprzedaż dla każdego ze Sprzedawców będzie zliczana tylko wtedy, gdy w wierszu Termin płatności znajduje się wartość z góry.

Oczywiście jeśli chcesz, to możesz do pola Filtr przeciągnąć kilka pól (np. Termin płatności i Państwo).

Fragmentatory

Skoro mowa o filtrach to nieco wygodniejszym sposobem filtrowania danych jest korzystanie z Fragmentatorów. Fragmentator to okienko, które działa dokładnie jak filtr, z tym że jest ono zawsze na wierzchu. Aby wstawić dowolny (jeden bądź kilka) fragmentator należy przejść na kartę Analiza tabeli przestawnej i w grupie poleceń Filtrowanie wybrać polecenie Wstaw Fragmentator. Pojawi się okienko dialogowe Wstawianie Fragmentatorów. Wybierz np. Termin płatności i Państwo.

Efekt będzie podobny jak na poniższym rysunku

Nasza tabela przestawna zlicza z tabeli z danymi tylko te wiersze, gdzie w kolumnie Państwo jest Brazylia a w kolumnie Termin płatności znajduje się wartość 14 dni lub 30 dni. Jeśli wstawiasz jakiś fragmentator to nie musisz owego pola przeciągać do Filtrów.

Sortowanie danych, wybieranie 3 najlepszych sprzedawców

W tabeli przestawnej możemy sortować rekordy. Domyślnie są one sortowane względem pierwszej kolumny (czyli w naszym przypadku najczęściej bywało, że po nazwiskach handlowców).

Ale jeśli chcemy sprawdzić kto jest najlepszym handlowcem? Utwórz prostą tabelę przestawną (przeciągnij pola Sprzedawca do Wierszy, Sprzedaż do Wartości)

Następnie w tabeli przestawnej kliknij prawym przyciskiem myszy na dowolnej komórce w kolumnie Suma z Sprzedaż i z menu podręcznego wybierz polecenie Sortuj -> Sortuj od największych do najmniejszych. Możesz również przejść na kartę Dane i tam wybrać polecenie sortowania.

Najlepszy sprzedawca będzie na samej górze tabeli przestawnej.

Czasami, gdy chcemy np. dać premię 3 najlepszym handlowcom, to nie musimy wyświetlać ich wszystkich w tabeli przestawnej. Możemy włączyć filtrowanie. W tym celu kliknij w strzałkę filtrów przy Etykiety wierszy a następnie wybierz polecenie Filtry wartości -> Pierwsze 10…

Pojawi się okno dialogowe Filtr 10 pierwszych wartości (Sprzedawca). W tym oknie dialogowym możemy określić czy chcemy zobaczyć najlepszych (Górne) czy najgorszych (Dolne) handlowców. Dodatkowo możemy określić ilu ich chcemy widzieć (domyślnie jest to 10, ale możemy zmienić np. na 3). W polu według określamy według jakiego kryterium wybieramy handlowców (mamy tam tylko jedną opcję Suma z Sprzedaż, ale jeśli tabela przestawna miałaby więcej kolumn zawierających liczby to tutaj wybieramy czy np. interesują nas sprzedawcy którzy sprzedali za największą kwotę, czy może Ci, co mieli najwięcej transakcji itp.)

Tabele przestawne z datami

Zróbmy proste miesięczne zestawienie sprzedaży. W tym celu przeciągnij Datę sprzedaży do Wierszy a Sprzedaż do Wartości. I tutaj w zależności od pewnych czynników możemy mieć pogrupowane daty w miesiące tak jak u mnie:

Jeśli posiadasz starszą wersję Excela (lub wyłączyłeś pewną opcję w ustawieniach Excela) to możesz mieć wypisane po kolei wszystkie unikalne daty i zsumowaną dzienną sprzedaż dla każdego z dni.

Skoro mamy daty pogrupowane w miesiące to je rozgrupujmy. W tym celu kliknij prawym przyciskiem myszy na dowolną komórkę w kolumnie Etykiety wierszy i wybierz polecenie Rozgrupuj.

Teraz dane w naszej tabeli przestawnej są pogrupowane według dni. Aby pogrupować je z powrotem należy kliknąć na dowolnej dacie prawym przyciskiem myszy i wybrać polecenie Grupuj. Pojawi się okno dialogowe Grupowanie.

Domyślnie w tym oknie jest zaznaczona pozycja Miesiące. Jeśli dane są z okresu dłuższego niż rok czasu należy KONIECZNIE doznaczyć pozycję Lata. Dlaczego? Otóż jeśli byłoby włączone grupowanie tylko według miesięcy to zostałyby zsumowane wszystkie transakcje z wszystkich styczni różnych lat pod jedną pozycją! Akurat w naszym przypadku mamy dane tylko z jednego roku, ale warto jest wyrobić sobie nawyk grupowania według Miesięcy i Lat.

No dobrze, wspominałem wcześniej, że nowsze wersje programu Excel domyślnie grupują daty w tabelach przestawnych. Można jednak tę opcję wyłączyć. W tym celu należy kliknąć na kartę Plik wybrać polecenie Opcje. Pojawi się okno dialogowe Opcje programu Excel w którym należy wybrać kartę Dane. Znajdziesz tam opcję Wyłącz automatyczne grupowanie kolumn Data/godzina w tabelach przestawnych.

Oś czasu

Podobnie jak w przypadku Fragmentatorów dla kolumn z datami można włączyć ciekawą opcję: Oś czasu. W tym celu należy na karcie Analiza tabeli przestawnej wybrać polecenie Wstaw oś czasu znajdujące się w grupie poleceń Filtrowanie. Pojawi się okno dialogowe Wstawianie osi czasu w którym należy wskazać dla jakiej kolumny będziemy wstawiać oś czasu (w naszym przypadku będzie to Data zamówienia).

Oś czasu działa zarówno gdy dane są grupowane jak i niepogrupowane i pozwala na zawężenie wyświetlanego zakresu dat w tabeli przestawnej.

Polecane tabele przestawne

W nowszych wersjach Excela na karcie Wstawianie obok polecenia Tabela przestawna znajduje się polecenie Polecane tabele przestawne. Wystarczy, tylko kliknąć w to polecenie gdy kursor znajduje się w tabeli danych. Excel na podstawie tych danych zaproponuje kilka przykładowych tabel przestawnych (po prawej stronie wyskoczy panel Polecane tabele przestawne). Jest to dość fajny sposób na szybkie stworzenie tabeli przestawnej. W moim przypadku Excel zaproponował 4 różne tabele przestawne, ale na samym dole znajdował się link Pokaż wszystkie 10 wyniki.

Wykres przestawny

Wykres przestawny można wstawić na dwa różne sposoby: możesz najpierw stworzyć tabelę przestawną a następnie utworzyć wykres z tabeli przestawnej. Jest to mój ulubiony sposób.

Możemy również utworzyć wykres przestawny od nowa (efektem ubocznym będzie fakt utworzenia tabeli przestawnej).

W starszych wersjach programu Excel polecenie Wykres przestawny znajdowało się na karcie Wstawianie pod przyciskiem Tabela przestawna.

W nowszych wersjach programu Excel polecenie Wykres przestawny zostało przeniesione jako samodzielny przycisk do grupy poleceń Wykresy na karcie Wstawianie.

Pojawi się okno dialogowe Utwórz wykres przestawny, które jest dość podobne do okna dialogowego Tabela przestawna z innej tabeli lub zakresu gdy wstawialiśmy tabelę przestawną. Zostaw zaznaczone domyślne opcje i kliknij w przycisk OK.

Po prawej stronie znajduje się Panel Pola tabeli przestawnej w którym znane nam pola mają nieco inne nazwy

• Filtry
• Zamiast Kolumny mamy Legenda (seria)
• Zamiast Wiersze mamy Oś (kategoria)
• Wartości

Tak więc tradycyjnie przeciągnij Sprzedawca do Oś (kategoria) / Wiersze a Sprzedaż do Wartości. Zobaczymy wtedy tabelę przestawną oraz wykres przestawny.

Pobieranie danych z innego źródła danych (np. baza programu Access)

Do tej pory importowaliśmy dane tylko z arkusza znajdującego się w bieżącym skoroszycie. Ale możemy dane importować z innych źródeł takich jak np.

• Połączenia (np. z PowerQuery)
• Dane z bazy MSSQL
• Dane z innych plików Excela
• Dane z plików Accessa

Tworzenie tabeli przestawnej z danych z innego źródła nie różni się zbytnio od tworzenia jej gdyby źródło danych było w tym samym skoroszycie. Jedynie na początku jest troszkę więcej klikania, później już jest tak samo.

W pierwszym kroku na karcie Wstawianie należy kliknąć w dolną część polecenia Tabela przestawna a następnie wybrać opcję Z zewnętrznego źródła danych (w starszych wersjach Excela klikamy bezpośrednio w polecenie Tabela przestawna).

Pojawi się okno dialogowe Tabela przestawna z zewnętrznego źródła. Kliknij w przycisk Wybierz połączenie. Następnie pojawi się okno dialogowe Istniejące połączenia.

W tym oknie zieloną ramką zaznaczyłem istniejące połączenie (wcześniej zaimportowałem jakieś dane za pomocą narzędzia PowerQuery i zapisałem je jako połączenie). Moglibyśmy wybrać to połączenie i z danych z tego połączenia utworzyć tabelę przestawną, ale my zajmijmy się importem danych z pliku Accessa. W tym celu kliknij w przycisk Wyszukaj więcej.

Pojawi się standardowe okno do otwierania plików. W dolnej części okna na liście rozwijanej zmień pokazywane typy plików na Wszystkie źródła danych. Przejdź do folderu w którym jest plik dane_z_accessa.accdb i go wskaż. Pojawi się okno dialogowe Wybieranie tabeli. Wybierz odpowiednią tabelę (lub kwerendę). Tutaj drobna uwaga: jeśli w kolumnie typ mamy VIEW (ang. widok) to oznacza, że dany obiekt jest kwerendą, jeśli mamy TABLE to oznacza, że dany obiekt jest tabelą.

Wrócimy do okna dialogowego Tabela przestawna z zewnętrznego źródła w którym należy kliknąć w przycisk OK.

Dalsza część procesu tworzenia tabeli przestawnej przebiega podobnie jakbyśmy zaimportowali dane bezpośrednio z skoroszytu tj. zobaczysz po prawej stronie panel Pola tabeli przestawnej. Dla utrwalenia umiejętności możesz tradycyjnie Sprzedawcę przeciągnąć do Wierszy a Sprzedaż do Wartości.

Pole obliczeniowe

W naszej tabeli z danymi mamy dwie dość ciekawe kolumny zawierające dane liczbowe: Sprzedaż i Koszt produkcji. Załóżmy, że będziemy chcieli policzyć, który z Sprzedawców nie tyle generuje największą sprzedaż co największy Zysk. W tym celu moglibyśmy dodać do tabeli w arkuszu Dane dodatkową kolumnę z prostą formułą:

=[@Sprzedaż]-[@[Koszt produkcji]]

Formuła w wersji gdyby dane były w zwykłym zakresie a nie obiekcie tabela wyglądałaby tak:

=F2-G2

No, ale załóżmy, że dane z których jest generowana tabela przestawna są tylko „tylko do odczytu” (np. jest włączona ochrona arkusza lub też dane do tabeli są pobierane z zewnętrznego źródła np. baza MSSQL).

Tutaj będziemy musieli stworzyć pole obliczeniowe. W pierwszej kolejności utwórz nową tabelę przestawną i przesuń Sprzedawcę do Wierszy.

Następnie na karcie Analiza tabeli przestawnej w grupie poleceń Obliczenia wybierz polecenie Pola, elementy, zestawy -> Pole obliczeniowe (w starszych wersjach programu Excel na karcie Narzędzia tabel przestawnych -> Opcje w grupie poleceń Narzędzia wybierz polecenie Formuły->Pole obliczeniowe).

Pojawi się okno dialogowe Wstawianie pola obliczeniowego. W polu Nazwa zmień wpisaną wartość Pole1 na np. Zysk. W kolei w polu Formuła mamy wpisane = 0. Zmaż zero. Następnie na liście Pola dwukrotnie kliknij w pole o nazwie Sprzedaż. W polu Formułą powinno być teraz = Sprzedaż. Dopisz ręcznie z klawiatury znak minusa a następnie w polu Pole kliknij dwukrotnie w pole Koszt produkcji. Ponieważ nazwa tego pola zawiera spację, to została objęta w apostrofy i finalnie nasza formuła powinna wyglądać tak:

=Sprzedaż -'Koszt produkcji'

Oczywiście całą formułę mógłbyś od razu wpisać „z palca”, ale wtedy istnieje ryzyko popełnienia literówki, dlatego zamiast wpisywać nazwy pól lepiej jest w nie dwa razy kliknąć.

Po kliknięciu w przycisk OK nasze nowo utworzone pole obliczeniowe powinno automatycznie trafić do Wartości.

Uwaga: nasze pole obliczeniowe będzie od teraz dostępne też dla innych tabel przestawnych.

Edycja pola obliczeniowego

Załóżmy, że popełniłeś drobny błąd podczas definiowania pola obliczeniowego. Można wtedy takie pole obliczeniowe albo usunąć, albo wyedytować formułę. W tym celu na karcie Analiza tabeli przestawnej w grupie poleceń Obliczenia wybierz polecenie Pola, elementy, zestawy -> Pole obliczeniowe (w starszych wersjach programu Excel na karcie Narzędzia tabel przestawnych -> Opcje w grupie poleceń Narzędzia wybierz polecenie Formuły->Pole obliczeniowe).

Pojawi się dobrze Tobie znane okno dialogowe Wstawianie pola obliczeniowego. Zamiast wstawiać nowe pole obliczeniowe w polu Nazwa należy kliknąć w „strzałeczkę” i wybrać jedno z wcześniej utworzonych pól obliczeniowych.

Następnie masz dwie opcje do wyboru:

• możesz kliknąć w przycisk Usuń aby usunąć pole obliczeniowe
• albo w polu Formuła zmodyfikować formułę a następnie kliknij w przycisk Modyfikuj, aby zatwierdzić zmianę.

Tabela przestawna z dwóch tabel

Nasza podstawowa tabela z danymi posiada następujące kolumny:

• Nr zamówienia
• Data zamówienia
• Sprzedawca
• Państwo
• Termin płatności
• Sprzedaż
• Koszt produkcji

W arkuszu Dane2 posiadamy też drugą tabelkę pomocniczą o nazwie Kontynenty zawierającą następujące kolumny:

• Państwo
• Kontynent

W obu tabelach elementem wspólnym jest kolumna Państwo. Załóżmy, że chcemy zrobić proste zestawienie: jaka jest sprzedaż ale nie do poszczególnych państw tylko na poszczególne kontynenty (zamiast kontynentów równie dobrze moglibyśmy np. utworzyć podobną tabelkę pomocniczą i przypisać państwa do różnych regionów). Pojawia się pytanie jak stworzyć tabelę przestawną z tych dwóch tabel. Tutaj jest kilka możliwości:

• Możemy się poddać i nie robić tego ćwiczenia 😉
• Możemy skorzystać z PowerQuery i scalić te dwie tabele w jedną a następnie z wynikowej tabeli utworzyć tabelę przestawną.
• Możemy skorzystać z dodatku PowerPivot (może kiedyś w przyszłości o nim wspomnę na blogu)
• Możemy w pierwszej tabeli utworzyć nową kolumnę Kontynent a następnie skorzystać z funkcji WYSZUKAJ.PIONOWO

Omówię ten ostatni sposób. W pierwszej kolejności w Arkuszu Dane w komórce H1 wpisz nazwę kolumny tj. Kontynent. Następnie w komórce H2 wpisz następującą funkcję:

=WYSZUKAJ.PIONOWO([@Państwo];Kontynenty;2;FAŁSZ)

Ponieważ w arkuszu Dane2 dane są w obiekcie Tabela (o nazwie Kontynenty) stąd drugi argument funkcji WYSZUKAJ.PIONOWO wygląda następująco: Kontynenty

Jeśli dane by były w zwykłym zakresie komórek to nasza funkcja wyglądałaby następująco:

=WYSZUKAJ.PIONOWO([@Państwo]; 'Dane2'!$A$2:$B$22;2;FAŁSZ)

(tutaj trzeba pamiętać o „dolarach”) Skoro mamy już w tabeli z danymi zrobioną pomocniczą kolumnę Kontynent to zrobienie tabeli przestawnej to będzie bułka z masłem: klikamy na kartę Wstawienia, wybieramy polecenie Tabela przestawna akceptujemy domyślne opcje a w panelu Pola tabeli przestawnej pole Kontynent przeciągamy do Wierszy, Sprzedaż do Wartości i naszym oczom powinna się pokazać taka oto tabela przestawna:

Uwaga: jeśli wcześniej wykonywałeś kilka ćwiczeń a po dodaniu kolumny Kontynent w arkuszu Dane na liście pól nie ma kolumny Kontynent to czasem trzeba na karcie Dane kliknąć w polecenie Odśwież wszystko. Niestety ale czasami Excel buforuje pewne dane (i błędnie zapamiętał wcześniejszy układ tabeli z danymi) stąd zdarzają się tego typu niespodzianki.