Spis treści
Tabele przestawne to jedno z najpotężniejszych narzędzi w programie Excel. Pozwalają analizować i podsumowywać różne zestawienia dużych ilości danych za pomocą zaledwie kilku kliknięć myszką. W tym artykule zapoznamy się z tabelami przestawnymi, zrozumiemy, czym one są, nauczymy się je tworzyć i dostosowywać.
Ten artykuł został napisany przy użyciu programu Excel 2010. Koncepcja tabel przestawnych nie zmieniła się znacząco na przestrzeni lat, ale sposób ich tworzenia jest nieco inny w każdej nowej wersji programu Excel. Jeśli masz wersję programu Excel, a nie 2010, przygotuj się, że zrzuty ekranu w tym artykule będą się różnić od tego, co widzisz na ekranie.
Trochę historii
W pierwszych dniach oprogramowania do arkuszy kalkulacyjnych, piłka zasad Lotus 1-2-3. Jego dominacja była tak kompletna, że wysiłki Microsoftu w celu stworzenia własnego oprogramowania (Excel) jako alternatywy dla Lotusa wydawały się stratą czasu. Teraz szybko do roku 2010! Excel dominuje w arkuszach kalkulacyjnych bardziej niż kod Lotusa w swojej historii, a liczba osób, które nadal używają Lotusa, jest bliska zeru. Jak to mogło się stać? Jaki był powód tak dramatycznego obrotu wydarzeń?
Analitycy identyfikują dwa główne czynniki:
- Po pierwsze, Lotus zdecydował, że ta nowomodna platforma GUI o nazwie Windows to tylko chwilowa moda, która nie potrwa długo. Odmówili zbudowania wersji Lotus 1-2-3 dla systemu Windows (ale tylko przez kilka lat), przewidując, że wersja DOS ich oprogramowania będzie wszystkim, czego kiedykolwiek będą potrzebować konsumenci. Microsoft naturalnie opracował program Excel specjalnie dla systemu Windows.
- Po drugie, firma Microsoft wprowadziła w programie Excel narzędzie o nazwie Tabele przestawne, które nie było dostępne w programie Lotus 1-2-3. Tabele przestawne, dostępne wyłącznie w programie Excel, okazały się tak niezwykle przydatne, że ludzie zwykle trzymali się nowego pakietu oprogramowania Excel, zamiast kontynuować pracę z programem Lotus 1-2-3, który ich nie miał.
Tabele przestawne, nie doceniając ogólnego sukcesu systemu Windows, odegrały marsz śmierci dla Lotus 1-2-3 i zapoczątkowały sukces programu Microsoft Excel.
Co to są tabele przestawne?
Jaki jest więc najlepszy sposób na scharakteryzowanie, czym są tabele przestawne?
W uproszczeniu tabele przestawne to podsumowania niektórych danych, stworzone w celu ułatwienia analizy tych danych. W przeciwieństwie do ręcznie tworzonych sum, tabele przestawne programu Excel są interaktywne. Po utworzeniu możesz je łatwo modyfikować, jeśli nie dają obrazu, na który liczyłeś. Wystarczy kilka kliknięć myszą, aby sumy można odwrócić, tak aby nagłówki kolumn stały się nagłówkami wierszy i odwrotnie. Z tabelami przestawnymi można zrobić wiele rzeczy. Zamiast próbować opisywać słowami wszystkie cechy tabel przestawnych, łatwiej zademonstrować to w praktyce…
Dane analizowane za pomocą tabel przestawnych nie mogą być losowe. Powinny to być surowe, surowe dane, jak jakaś lista. Na przykład może to być lista sprzedaży dokonanych przez firmę w ciągu ostatnich sześciu miesięcy.
Spójrz na dane pokazane na poniższym rysunku:
Zwróć uwagę, że nie są to surowe, surowe dane, jak to już zostało podsumowane. W komórce B3 widzimy 30000 dolarów, co jest prawdopodobnie łącznym wynikiem, który zrobił James Cook w styczniu. Gdzie zatem są oryginalne dane? Skąd wzięła się kwota 30000 dolarów? Gdzie jest oryginalna lista sprzedaży, z której pochodzi ta miesięczna suma? Oczywiste jest, że ktoś wykonał świetną robotę, organizując i sortując wszystkie dane sprzedaży z ostatnich sześciu miesięcy i przekształcając je w tabelę sum, którą widzimy. Jak myślisz, ile czasu to zajęło? Godzina? Godzina dziesiąta?
Faktem jest, że powyższa tabela nie jest tabelą przestawną. Został wykonany ręcznie z surowych danych przechowywanych w innym miejscu, a jego przetworzenie zajęło co najmniej kilka godzin. Taką właśnie tabelę podsumowującą można stworzyć za pomocą tabel przestawnych w zaledwie kilka sekund. Zastanówmy się, jak…
Jeśli wrócimy do oryginalnej listy sprzedaży, wyglądałoby to mniej więcej tak:
Możesz być zaskoczony, że z tej listy transakcji za pomocą tabel przestawnych i w zaledwie kilka sekund możemy stworzyć miesięczny raport sprzedaży w Excelu, który przeanalizowaliśmy powyżej. Tak, możemy to zrobić i nie tylko!
Jak stworzyć tabelę przestawną?
Najpierw upewnij się, że masz jakieś dane źródłowe w arkuszu Excela. Najbardziej typowa jest lista transakcji finansowych. W rzeczywistości może to być lista wszystkiego: dane kontaktowe pracowników, kolekcja płyt CD lub dane o zużyciu paliwa w Twojej firmie.
Tak więc uruchamiamy Excela… i ładujemy taką listę…
Po otwarciu tej listy w programie Excel możemy rozpocząć tworzenie tabeli przestawnej.
Wybierz dowolną komórkę z tej listy:
Następnie na karcie Wprowadzenie (Wstaw) wybierz polecenie Stół obrotowy (Stół obrotowy):
Pojawi się okno dialogowe Utwórz tabelę przestawną (Tworzenie tabeli przestawnej) z dwoma pytaniami do Ciebie:
- Jakich danych użyć do utworzenia nowej tabeli przestawnej?
- Gdzie umieścić tabelę przestawną?
Ponieważ w poprzednim kroku wybraliśmy już jedną z komórek listy, cała lista zostanie automatycznie wybrana do utworzenia tabeli przestawnej. Zwróć uwagę, że możemy wybrać inny zakres, inną tabelę, a nawet zewnętrzne źródło danych, takie jak tabela bazy danych Access lub MS-SQL. Ponadto musimy wybrać, gdzie umieścić nową tabelę przestawną: na nowym arkuszu lub na jednym z istniejących. W tym przykładzie wybierzemy opcję – Nowy arkusz roboczy (do nowego arkusza):
Excel utworzy nowy arkusz i umieści na nim pustą tabelę przestawną:
Gdy tylko klikniemy dowolną komórkę w tabeli przestawnej, pojawi się kolejne okno dialogowe: Lista pól tabeli przestawnej (Pola tabeli przestawnej).
Lista pól w górnej części okna dialogowego to lista wszystkich tytułów z oryginalnej listy. Cztery puste obszary na dole ekranu umożliwiają poinformowanie tabeli przestawnej, w jaki sposób chcesz podsumować dane. Dopóki te obszary są puste, w tabeli też nic nie ma. Wszystko, co musimy zrobić, to przeciągnąć nagłówki z górnego obszaru do pustych obszarów poniżej. Jednocześnie automatycznie generowana jest tabela przestawna, zgodnie z naszymi instrukcjami. Jeśli popełnimy błąd, możemy usunąć nagłówki z dolnego obszaru lub przeciągnąć inne, aby je zastąpić.
Obszar Wartości (Znaczenia) jest prawdopodobnie najważniejszym z czterech. To, który nagłówek zostanie umieszczony w tym obszarze, określa, jakie dane zostaną podsumowane (suma, średnia, maksymalna, minimalna itd.). Są to prawie zawsze wartości liczbowe. Doskonałym kandydatem na miejsce w tym obszarze są dane pod nagłówkiem ilość (Koszt) naszego oryginalnego stołu. Przeciągnij ten tytuł do obszaru Wartości (Wartości):
Należy pamiętać, że tytuł ilość jest teraz oznaczony znacznikiem wyboru, a w okolicy Wartości (Wartości) pojawił się wpis Suma kwoty (pole Kwota Kwota), wskazując, że kolumna ilość podsumował.
Jeśli spojrzymy na samą tabelę przestawną, zobaczymy sumę wszystkich wartości z kolumny ilość oryginalny stół.
Tak więc powstała nasza pierwsza tabela przestawna! Wygodny, ale niezbyt imponujący. Prawdopodobnie chcemy uzyskać więcej informacji o naszych danych, niż obecnie posiadamy.
Przejdźmy do oryginalnych danych i spróbujmy zidentyfikować jedną lub więcej kolumn, których można użyć do podzielenia tej sumy. Na przykład możemy uformować naszą tabelę przestawną w taki sposób, aby łączna kwota sprzedaży była obliczana dla każdego sprzedawcy indywidualnie. Tych. do naszej tabeli przestawnej zostaną dodane wiersze z imieniem i nazwiskiem każdego sprzedawcy w firmie oraz ich łączną kwotą sprzedaży. Aby osiągnąć ten wynik, po prostu przeciągnij tytuł sprzedawca (przedstawiciel handlowy) w regionie Etykiety wierszy (Smyczki):
Robi się ciekawiej! Nasza tabela przestawna zaczyna nabierać kształtu…
Widzisz korzyści? Za pomocą kilku kliknięć stworzyliśmy tabelę, której ręczne utworzenie zajęłoby bardzo dużo czasu.
Co jeszcze możemy zrobić? Cóż, w pewnym sensie nasza tabela przestawna jest gotowa. Stworzyliśmy przydatne podsumowanie oryginalnych danych. Ważne informacje już otrzymane! W dalszej części tego artykułu przyjrzymy się niektórym sposobom tworzenia bardziej złożonych tabel przestawnych, a także nauczymy się je dostosowywać.
Konfiguracja tabeli przestawnej
Najpierw możemy stworzyć dwuwymiarową tabelę przestawną. Zróbmy to za pomocą nagłówka kolumny Metoda płatności (Metoda płatności). Po prostu przeciągnij tytuł Metoda płatności do obszaru Etykiety kolumn (Kolumny):
Otrzymujemy wynik:
Wygląda bardzo fajnie!
Teraz zróbmy trójwymiarowy stół. Jak wyglądałby taki stół? Zobaczmy…
Przeciągnij nagłówek Pakiet (kompleks) do obszaru filtry raportów (Filtry):
Zwróć uwagę, gdzie on jest…
Daje nam to możliwość filtrowania raportu na podstawie „Który kompleks wakacyjny został opłacony”. Na przykład możemy zobaczyć podział według sprzedawców i metod płatności dla wszystkich kompleksów lub za pomocą kilku kliknięć myszką zmienić widok tabeli przestawnej i pokazać ten sam podział tylko dla tych, którzy zamówili kompleks Poszukiwacze słońca.
Jeśli więc dobrze to rozumiesz, naszą tabelę przestawną można nazwać trójwymiarową. Kontynuujmy konfigurację…
Jeśli nagle okaże się, że w tabeli przestawnej powinna wyświetlać się tylko płatność czekiem i kartą kredytową (czyli płatność bezgotówkowa), to możemy wyłączyć wyświetlanie tytułu Gotówka (Gotówka). W tym celu obok Etykiety kolumn kliknij strzałkę w dół i odznacz pole w menu rozwijanym Gotówka:
Zobaczmy teraz, jak wygląda nasza tabela przestawna. Jak widać, kolumna Gotówka zniknął z niej.
Formatowanie tabel przestawnych w programie Excel
Tabele przestawne są oczywiście bardzo potężnym narzędziem, ale jak dotąd wyniki wydają się nieco proste i nudne. Na przykład liczby, które sumujemy, nie wyglądają jak kwoty w dolarach – to tylko liczby. Naprawmy to.
Kuszące jest zrobienie tego, do czego jesteś przyzwyczajony w takiej sytuacji i po prostu zaznaczenie całej tabeli (lub całego arkusza) i użycie standardowych przycisków formatowania liczb na pasku narzędzi, aby ustawić żądany format. Problem z tym podejściem polega na tym, że jeśli kiedykolwiek zmienisz strukturę tabeli przestawnej w przyszłości (co dzieje się z 99% szansą), formatowanie zostanie utracone. Potrzebujemy sposobu, aby była (prawie) trwała.
Najpierw znajdźmy wpis Suma kwoty in Wartości (Wartości) i kliknij na nią. W wyświetlonym menu wybierz element Ustawienia pola wartości (Opcje pola wartości):
Pojawi się okno dialogowe Ustawienia pola wartości (Opcje pola wartości).
Naciśnij przycisk Format liczb (Format liczb), otworzy się okno dialogowe. Formatowanie komórek (format komórki):
Z listy Kategoria (Formaty liczb) wybierz Księgowość (Finansowe) i ustaw liczbę miejsc dziesiętnych na zero. Teraz naciśnij kilka razy OKaby wrócić do naszej tabeli przestawnej.
Jak widać, liczby są sformatowane jako kwoty w dolarach.
Skoro już jesteśmy przy formatowaniu, skonfigurujmy format dla całej tabeli przestawnej. Jest na to kilka sposobów. Używamy tego, który jest prostszy…
Kliknij Narzędzia tabeli przestawnej: projektowanie (Praca z tabelami przestawnymi: Konstruktor):
Następnie rozwiń menu, klikając strzałkę w prawym dolnym rogu sekcji Style tabeli przestawnej (Style tabeli przestawnej), aby zobaczyć bogatą kolekcję stylów wbudowanych:
Wybierz dowolny styl i spójrz na wynik w tabeli przestawnej:
Inne ustawienia tabeli przestawnej w programie Excel
Czasami trzeba filtrować dane według dat. Na przykład na naszej liście transakcji jest wiele, wiele dat. Excel udostępnia narzędzie do grupowania danych według dnia, miesiąca, roku itd. Zobaczmy, jak to się robi.
Najpierw usuń wpis. Metoda płatności z regionu Etykiety kolumn (Kolumny). W tym celu przeciągnij go z powrotem na listę tytułów, a w jego miejsce przesuń tytuł Data rezerwacji (data rezerwacji):
Jak widać, tymczasowo sprawiło to, że nasza tabela przestawna stała się bezużyteczna. Excel utworzył osobną kolumnę dla każdego dnia, w którym dokonano transakcji. W rezultacie otrzymaliśmy bardzo szeroki stół!
Aby to naprawić, kliknij prawym przyciskiem myszy dowolną datę i wybierz z menu kontekstowego GROUP (Grupa):
Pojawi się okno dialogowe grupowania. Wybieramy Miesiące (Miesiące) i kliknij OK:
Voila! Ta tabela jest o wiele bardziej przydatna:
Nawiasem mówiąc, ta tabela jest prawie identyczna z tą pokazaną na początku artykułu, gdzie sumy sprzedaży zostały zestawione ręcznie.
Jest jeszcze jeden bardzo ważny punkt, o którym musisz wiedzieć! Możesz utworzyć nie jeden, ale kilka poziomów nagłówków wierszy (lub kolumn):
… i tak będzie wyglądać …
To samo można zrobić z nagłówkami kolumn (lub nawet filtrami).
Wróćmy do pierwotnej postaci tabeli i zobaczmy, jak wyświetlać średnie zamiast sum.
Aby rozpocząć, kliknij Suma kwoty i z menu, które się pojawi, wybierz Ustawienia pola wartości (Opcje pola wartości):
Lista Podsumuj pole wartości według (Operacja) w oknie dialogowym Ustawienia pola wartości (Opcje pola wartości) wybierz Średni (Przeciętny):
W tym samym czasie, póki tu jesteśmy, zmieńmy się Nazwa niestandardowa (Nazwa niestandardowa) z Średnia kwoty (pole Kwota Kwota) na coś krótszego. Wpisz w tym polu coś takiego Śr:
Naciśnij przycisk OK i zobacz, co się stanie. Zwróć uwagę, że wszystkie wartości zmieniły się z sum na średnie, a nagłówek tabeli (w lewej górnej komórce) zmienił się na Śr:
Jeśli chcesz, możesz natychmiast uzyskać kwotę, średnią i liczbę (sprzedaż) umieszczoną w jednej tabeli przestawnej.
Oto przewodnik krok po kroku, jak to zrobić, zaczynając od pustej tabeli przestawnej:
- Przeciągnij nagłówek sprzedawca (przedstawiciel handlowy) w regionie Etykiety kolumn (Kolumny).
- Przeciągnij tytuł trzy razy ilość (Koszt) do obszaru Wartości (Wartości).
- Dla pierwszego pola ilość zmień tytuł na Kwota produktów: (Kwota), a format liczb w tym polu to Księgowość (Budżetowy). Liczba miejsc po przecinku wynosi zero.
- Drugie pole ilość Nazwa Średniae, ustaw dla niego operację Średni (Średnia) i format liczb w tym polu również zmienią się na Księgowość (Finansowe) bez miejsc po przecinku.
- Dla trzeciego pola ilość ustaw tytuł Liczyć i operacja dla niego – Liczyć (Ilość)
- W Etykiety kolumn Pole (Kolumny) utworzone automatycznie ΣWartości (Σ Wartości) – przeciągnij go do obszaru Etykiety wierszy (Linie)
Oto, na czym skończymy:
Całkowita kwota, średnia wartość i liczba sprzedaży – wszystko w jednej tabeli przestawnej!
Wnioski
Tabele przestawne w programie Microsoft Excel zawierają wiele funkcji i ustawień. W tak małym artykule nie są nawet blisko omówienia ich wszystkich. Aby w pełni opisać wszystkie możliwości tabel przestawnych, wystarczy mała książka lub duża witryna internetowa. Odważni i dociekliwi czytelnicy mogą kontynuować eksplorację tabel przestawnych. Aby to zrobić, po prostu kliknij prawym przyciskiem myszy prawie dowolny element tabeli przestawnej i zobacz, jakie funkcje i ustawienia się otwierają. Na Wstążce znajdziesz dwie zakładki: Narzędzia tabel przestawnych: Opcje (analiza) i Wnętrze (Konstruktor). Nie bój się popełnić błędu, zawsze możesz usunąć tabelę przestawną i zacząć od nowa. Masz szansę, której dawni użytkownicy DOS-a i Lotusa 1-2-3 nigdy nie mieli.