Praca z tabelami przestawnymi w programie Microsoft Excel

Tabele przestawne są jedną z najpotężniejszych funkcji programu Microsoft Excel. Pozwalają one analizować i analizować duże ilości danych za pomocą zaledwie kilku kliknięć myszą. W tym artykule poznajemy tabele przestawne, poznajemy je i uczymy się ich tworzenia i dostosowywania.

Uwaga: Ten artykuł został napisany przy użyciu Excel 2010 (Beta). Koncepcja tabeli przestawnej zmieniła się przez lata niewiele, ale metoda jej tworzenia zmieniła się w niemal każdej iteracji programu Excel. Jeśli używasz wersji programu Excel, która nie jest 2010, oczekuj różnych ekranów od tych, które widzisz w tym artykule.

Mała historia

W początkowych dniach programów arkuszy kalkulacyjnych Lotus 1-2-3 rządził grzędami. Jej dominacja była tak kompletna, że ​​ludzie uważali, że marnowanie czasu jest dla Microsoftu kłopotliwe w rozwijaniu własnego oprogramowania arkusza kalkulacyjnego (Excel) w celu konkurowania z Lotus. Flash-forward to 2010, a przewaga Excela na rynku arkuszy kalkulacyjnych jest większa niż kiedykolwiek w historii Lotus, podczas gdy liczba użytkowników nadal korzystających z Lotus 1-2-3 zbliża się do zera. Jak to się stało? Co spowodowało tak dramatyczny zwrot fortuny?

Analitycy branżowi sprowadzają to do dwóch czynników: Po pierwsze, Lotus zdecydował, że ta nowa platforma GUI o nazwie "Windows" była przejściową modą, która nigdy nie wystartuje. Odmówili utworzenia wersji Lotus 1-2-3 dla systemu Windows (na kilka lat), przewidując, że ich wersja oprogramowania DOS była wszystkim, czego kiedykolwiek potrzebował. Microsoft oczywiście opracował Excel wyłącznie dla Windows. Po drugie, Microsoft opracował funkcję programu Excel, której Lotus nie zapewniał w wersjach 1-2-3, a mianowicie Tabele przestawne.  Funkcja tabel przestawnych, wyłącznie dla programu Excel, została uznana za niezwykle przydatną, ponieważ ludzie chcieli nauczyć się nowego pakietu oprogramowania (Excel), zamiast trzymać się programu (1-2-3), który go nie posiadał. Ta jedna cecha, wraz z błędną oceną sukcesu systemu Windows, była zwiastunem dla Lotus 1-2-3 i początkiem sukcesu programu Microsoft Excel.

Omówienie tabel przestawnych

Czym dokładnie jest tabela przestawna?

Mówiąc prościej, tabela przestawna jest podsumowaniem niektórych danych, utworzonych w celu umożliwienia łatwej analizy tych danych. Ale w przeciwieństwie do ręcznie utworzonego podsumowania, Excel PivotTables są interaktywne. Po utworzeniu można go łatwo zmienić, jeśli nie oferuje dokładnego wglądu w dane, na które liczył. Za pomocą kilku kliknięć podsumowanie może być "obrócone" - obrócone w taki sposób, że nagłówki kolumn stają się nagłówkami wierszy i odwrotnie. Jest jeszcze wiele do zrobienia. Zamiast próbować opisać wszystkie funkcje tabel przestawnych, po prostu je zademonstrujemy ...

Dane analizowane za pomocą tabeli przestawnej nie mogą być sprawiedliwe każdy dane - to musi być surowy dane, które wcześniej były nieprzetworzone (niezebrane) - zazwyczaj lista pewnego rodzaju. Przykładem może być lista transakcji sprzedaży w firmie z ostatnich sześciu miesięcy.

Sprawdź dane pokazane poniżej:

Zauważ, że tak nie surowe dane. W rzeczywistości jest to już jakieś podsumowanie. W komórce B3 widzimy 30 000 USD, co jest najwyraźniej całkowitą sprzedażą James Cook w styczniu. Więc gdzie są surowe dane? Jak osiągnęliśmy kwotę 30 000 USD? Gdzie jest oryginalna lista transakcji sprzedaży, z których pochodzi ta liczba? Jest jasne, że gdzieś, ktoś musiał zadać sobie trud złożenia wszystkich transakcji sprzedaży w ciągu ostatnich sześciu miesięcy w podsumowaniu, które widzimy powyżej. Jak myślisz, ile czasu to zajęło? Godzina? Dziesięć?

Najprawdopodobniej tak. Widzisz, powyższy arkusz jest w rzeczywistości nie tabelę przestawną. Został stworzony ręcznie z surowych danych przechowywanych w innym miejscu, a kompilacja zajęła kilka godzin. Jednak to jest właśnie tego rodzaju podsumowanie mógłby tworzone przy użyciu tabel przestawnych, w takim przypadku zajęłoby to zaledwie kilka sekund. Zobaczmy, jak ...

Jeśli mamy wyśledzić oryginalną listę transakcji sprzedaży, może wyglądać mniej więcej tak:

Możesz być zaskoczony tym, że korzystając z funkcji tabeli przestawnej w programie Excel, możemy utworzyć miesięczne podsumowanie sprzedaży podobne do powyższego w ciągu kilku sekund, za pomocą kilku kliknięć myszą. Możemy to zrobić - i dużo więcej!

Jak utworzyć tabelę przestawną

Najpierw upewnij się, że masz jakieś surowe dane w arkuszu kalkulacyjnym w programie Excel. Lista transakcji finansowych jest typowa, ale może to być lista niemal wszystkiego: dane kontaktowe pracownika, kolekcja płyt CD lub dane o zużyciu paliwa dla floty samochodów Twojej firmy.

Więc uruchamiamy program Excel ... i ładujemy taką listę ...

Po otwarciu listy w programie Excel możemy rozpocząć tworzenie tabeli przestawnej.

Kliknij dowolną pojedynczą komórkę na liście:

Następnie z Wstawić Kliknij kartę Stół obrotowy Ikona:

The Utwórz tabelę przestawną pojawi się okno z pytaniami: Jakie dane powinna zawierać nowa tablica przestawna i gdzie należy ją utworzyć? Ponieważ kliknęliśmy już komórkę na liście (w powyższym kroku), cała lista otaczająca tę komórkę jest już dla nas wybrana ($ A 1: $ G 88 na Płatności arkusz, w tym przykładzie). Zauważmy, że możemy wybrać listę w dowolnym regionie dowolnego innego arkusza roboczego lub nawet zewnętrzne źródło danych, takie jak tabela bazy danych programu Access, a nawet tabela bazy danych MS-SQL Server. Musimy również wybrać, czy chcemy, aby nasza nowa tabela przestawna była tworzona na Nowy arkusz roboczy lub na istniejący jeden. W tym przykładzie wybierzemy Nowy jeden:

Nowy arkusz zostanie utworzony dla nas, a pusta tabela przestawna zostanie utworzona w tym arkuszu:

Pojawia się także inne okno: Lista pól tabeli przestawnej.  Ta lista pól będzie wyświetlana po kliknięciu dowolnej komórki w tabeli przestawnej (powyżej):

Lista pól w górnej części pola jest w rzeczywistości zbiorem nagłówków kolumn z oryginalnego arkusza danych nieprzetworzonych. Cztery puste pola w dolnej części ekranu pozwalają nam wybrać sposób, w jaki chcemy, aby nasza tabela przestawna podsumowywała nieprzetworzone dane. Jak dotąd w tych polach nie ma nic, więc tabela przestawna jest pusta. Wszystko, co musimy zrobić, to przeciągnąć pola z powyższej listy i upuścić je w dolnych polach. Następnie zostanie automatycznie utworzona tabela przestawna, zgodnie z naszymi instrukcjami. Jeśli zrobimy to źle, musimy tylko przeciągnąć pola z powrotem do miejsca, skąd przybyli i / lub przeciągnąć Nowy pola w dół, aby je zastąpić.

The Wartości box jest prawdopodobnie najważniejszym z czterech. Pole, które jest przeciągane do tego pola, reprezentuje dane, które należy podsumować w pewien sposób (przez zsumowanie, uśrednienie, znalezienie maksimum, minimum itd.). To prawie zawsze liczbowy dane. Idealnym kandydatem do tego pola w naszych przykładowych danych jest pole / kolumna "Kwota". Przeciągnijmy to pole do Wartości pudełko:

Zauważ, że (a) pole "Kwota" na liście pól jest teraz zaznaczone, a "Suma kwoty" została dodana do Wartości pole, wskazujące, że suma została zsumowana.

Jeśli przeanalizujemy samą tabelę przestawną, rzeczywiście znajdziemy sumę wszystkich wartości "Ilość" z arkusza danych nieprzetworzonych:

Stworzyliśmy naszą pierwszą tabelę przestawną! Poręczny, ale niezbyt imponujący. Prawdopodobnie potrzebujemy trochę więcej wglądu w nasze dane.

Odnosząc się do naszych przykładowych danych, musimy zidentyfikować jeden lub więcej nagłówków kolumn, które moglibyśmy wykorzystać do podziału tej sumy. Na przykład możemy zdecydować, że chcielibyśmy zobaczyć podsumowanie naszych danych, gdzie mamy wiersz nagłówka dla każdego z różnych sprzedawców w naszej firmie i dla każdego z nich łącznie. Aby to osiągnąć, wystarczy przeciągnąć pole "Sprzedawca" do pola Etykiety wierszy pudełko:

Teraz, w końcu rzeczy zaczynają się interesować! Nasza tabela przestawna zaczyna przybierać postać ... .

Za pomocą kilku kliknięć stworzyliśmy tabelę, której wykonanie zajęłoby dużo czasu.

Więc co jeszcze możemy zrobić? W pewnym sensie nasza tabela przestawna jest kompletna. Stworzyliśmy użyteczne podsumowanie naszych danych źródłowych. Ważne rzeczy już się nauczyły! W dalszej części artykułu przeanalizujemy sposoby tworzenia bardziej złożonych tabel przestawnych oraz sposoby dostosowywania tych tabel przestawnych.

Najpierw możemy stworzyć dwa-tabela wymiarowa. Zróbmy to, używając "Metody płatności" jako nagłówka kolumny. Po prostu przeciągnij nagłówek "Metoda płatności" na Etykiety kolumn pudełko:

Który wygląda tak:

Zaczyna się bardzo fajne!

Zróbmy to trzy-tabela wymiarowa. Jak może wyglądać taki stół? Więc, zobaczmy…

Przeciągnij kolumnę "Pakiet" / nagłówek do Filtr raportu pudełko:

Zawiadomienie, gdzie kończy się ... .

Pozwala nam to filtrować nasz raport na podstawie zakupu "pakietu wakacyjnego". Na przykład możemy zobaczyć podział sprzedawcy na metodę płatności dla wszystko pakiety lub, za pomocą kilku kliknięć, zmień go tak, aby wyświetlał ten sam podział dla pakietu "Sunseekers":

Jeśli więc myślisz o tym we właściwy sposób, nasza tabela przestawna jest teraz trójwymiarowa. Dostosujmy ...

Jeśli się okaże, powiedzmy, że chcemy tylko zobaczyć czek i karta kredytowa transakcje (tj. bez transakcji gotówkowych), wówczas możemy odznaczyć pozycję "Gotówka" z nagłówków kolumn. Kliknij strzałkę menu obok Etykiety kolumn, i odznacz "Gotówka":

Zobaczmy, jak to wygląda ... Jak widać, "Cash" już nie ma.

Formatowanie

Jest to oczywiście bardzo potężny system, ale do tej pory wyniki wyglądają bardzo prosto i nudno. Na początek liczby, które sumujemy, nie wyglądają jak kwoty w dolarach - tylko zwykłe stare liczby. Naprawmy to.

Może to być pokusa, by zrobić to, do czego jesteśmy przyzwyczajeni w takich okolicznościach i po prostu wybrać całą tabelę (lub cały arkusz roboczy) i użyć standardowych przycisków formatowania liczb na pasku narzędzi, aby ukończyć formatowanie. Problem z tym podejściem polega na tym, że jeśli kiedykolwiek zmienisz strukturę tabeli przestawnej w przyszłości (co jest prawdopodobne 99%), to te formaty liczb zostaną utracone. Potrzebujemy sposobu, który uczyni je (pół-) stałymi.

Najpierw zlokalizujemy wpis "Sum of Amount" w pliku Wartości i kliknij na niego. Pojawi się menu. Wybieramy Ustawienia pola wartości ... z menu:

The Ustawienia pola wartości pojawi się okno.

Kliknij Format liczbowy przycisk i standard Formatuj komórki pojawia się:

Od Kategoria lista, wybierz (powiedz) Księgowość, i zmniejsz liczbę miejsc dziesiętnych do 0. Kliknij dobrze kilka razy, aby wrócić do tabeli przestawnej ...

Jak widać, numery zostały poprawnie sformatowane jako kwoty w dolarach.

Chociaż zajmujemy się formatowaniem, sformatujmy całą tabelę przestawną. Można to zrobić na kilka sposobów. Użyjmy prostego ...

Kliknij Narzędzia PivotTable / Design patka:

Następnie opuść strzałkę w dolnym prawym rogu ekranu Style tabeli przestawnej listę, aby zobaczyć ogromną kolekcję wbudowanych stylów:

Wybierz dowolny odwołujący się i spójrz na wynik w tabeli przestawnej:

Inne opcje

Możemy również pracować z datami. Zwykle istnieje wiele, wiele dat na liście transakcji, takich jak ta, z którą zaczęliśmy. Ale program Excel zapewnia opcję grupowania elementów danych razem według dnia, tygodnia, miesiąca, roku itd. Zobaczmy, jak to się robi.

Najpierw usuńmy kolumnę "Metoda płatności" z Etykiety kolumn (po prostu przeciągnij go z powrotem na listę pól) i zastąp go kolumną "Data rezerwacji":

Jak widać, powoduje to, że nasza tabela przestawna staje się bezużyteczna, dając nam jedną kolumnę dla każdej daty transakcji - bardzo szeroki zakres!

Aby to naprawić, kliknij prawym przyciskiem myszy dowolną datę i wybierz Grupa… z menu kontekstowego:

Pojawi się pole grupowania. Wybieramy Miesięcy i kliknij OK:

Voila! ZA dużo bardziej przydatny stół:

(Nawiasem mówiąc, ta tabela jest praktycznie identyczna z tą pokazaną na początku tego artykułu - oryginalne podsumowanie sprzedaży, które zostało utworzone ręcznie.)

Kolejną fajną rzeczą, o której należy pamiętać, jest to, że możesz mieć więcej niż jeden zestaw nagłówków wierszy (lub nagłówków kolumn):

... który wygląda tak ... .

Możesz zrobić coś podobnego z nagłówkami kolumn (lub nawet filtrami raportów).

Utrzymując ponownie prostotę, zobaczmy, jak kreślić uśrednione wartości, a nie zsumowane wartości.

Najpierw kliknij "Suma kwoty" i wybierz Ustawienia pola wartości ... z wyświetlonego menu kontekstowego:

w Podsumuj pole wartości o lista w Ustawienia pola wartości pole, wybierz Średni:

Kiedy tu jesteśmy, zmieńmy Nazwa niestandardowa, od "Average of Amount" do czegoś trochę bardziej zwięzłego. Wpisz coś w rodzaju "Śr.":

Kliknij dobrze, i zobacz, jak to wygląda. Zauważ, że wszystkie wartości zmieniają się od sum zsumowanych do średnich, a tytuł tabeli (od lewej do lewej komórki) został zmieniony na "Śr.":

Jeśli nam się podoba, możemy nawet mieć sumy, średnie i liczby (wszystkie = liczba sprzedaży) na tej samej tabeli przestawnej!

Oto kroki, aby uzyskać coś podobnego na miejscu (zaczynając od pustej tabeli przestawnej):

1. Przeciągnij "Sprzedawca" do Etykiety kolumn
2. Przeciągnij pole "Ilość" w dół do Wartości pole trzy razy
3. W pierwszym polu "Kwota" zmień jego niestandardową nazwę na "Łącznie" i ustaw numer na Księgowość (0 miejsc po przecinku)
4. W drugim polu "Kwota" zmień jego nazwę niestandardową na "Średnia", której funkcją jest Średni i jest to format liczbowy Księgowość (0 miejsc po przecinku)
5. W trzecim polu "Kwota" zmień jego nazwę na "Liczba" i jego funkcję na Liczyć
6. Przeciągnij automatycznie utworzone pole od Etykiety kolumn do Etykiety wierszy

Oto, na czym kończymy:

Łącznie, średnia i licz na tę samą tabelę przestawną!

Wniosek

Istnieje wiele, wiele więcej funkcji i opcji tabel przestawnych utworzonych przez program Microsoft Excel - o wiele za dużo, aby można je było wyświetlić w takim artykule. Aby w pełni pokryć potencjał tabel przestawnych, wymagana jest mała książka (lub duża witryna). Odważni i / lub geekyjni czytelnicy mogą dość łatwo odkrywać tabele przestawne: wystarczy kliknąć prawym przyciskiem myszy na prawie wszystko i zobaczyć, jakie opcje stają się dostępne. Istnieją również dwie zakładki wstążki: Narzędzia / opcje tabeli przestawnej i Projekt.  Nie ma znaczenia, czy popełnisz błąd - łatwo usunąć tabelę przestawną i zacząć od nowa - możliwość, że dawni użytkownicy DOS Lotus 1-2-3 nigdy nie mieli.

Jeśli pracujesz w pakiecie Office 2007, możesz zapoznać się z naszym artykułem na temat tworzenia tabeli przestawnej w programie Excel 2007.

Dodaliśmy skoroszyt programu Excel, który można pobrać, aby ćwiczyć swoje umiejętności w tabeli przestawnej. Powinien działać ze wszystkimi wersjami programu Excel od 97 roku.

Pobierz nasz skoroszyt ćwiczeń Excel