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 programu Excel 2010 (Beta). Koncepcja tabeli przestawnej zmieniła się przez lata niewiele, ale metoda jej tworzenia zmieniła się niemal w każdej iteracji programu Excel. Jeśli używasz wersji programu Excel, która nie jest wersją 2010, oczekuj różnych ekranów od tych, które widzisz w tym artykule.
Mała historia
Na początku programu arkuszy kalkulacyjnych Lotus 1-2-3 rządził grzędami. Jego 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ą się 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 ma. 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.
Zrozumienie 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żesz łatwo go zmienić, jeśli nie oferuje dokładnego wglądu w dane, na które liczyłeś. 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, wcześniej 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 $, co jest najwyraźniej całkowitą sprzedażą James Cook'a w styczniu. Więc gdzie są surowe dane? Jak osiągnęliśmy kwotę 30 000 $? 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 rzeczywiście 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 którym to 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ą zaledwie 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 pytaniem: na jakie dane powinna opierać się nowa tabela 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ż, ż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 lub 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ę również 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. PivotTable jest automatycznie tworzona w celu dopasowania do naszych instrukcji. 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 (sumując, uśredniając, znajdując 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 "Kwota" 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 suma dla każdego. Aby to osiągnąć, wystarczy przeciągnąć pole "Sprzedawca" do pola Etykiety wierszy pudełko:
Terazw końcu rzeczy zaczynają się interesować! Nasza tabela przestawna zaczyna nabierać kształtu ...
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:
Co wygląda tak:
Zaczyna się bardzo fajne!
Zróbmy to trzy-wymiar wymiarowy. 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, 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ę w dół obok Etykiety kolumni 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) i użyć standardowych przycisków formatowania liczb na pasku narzędzi, aby zakoń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 "Suma kwoty" w 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) Rachunkowośći zmniejsz liczbę miejsc dziesiętnych do 0. Kliknij dobrze kilka razy, aby powrócić do tabeli przestawnej ...
Jak widać, numery zostały poprawnie sformatowane jako kwoty w dolarach.
Chociaż zajmujemy się formatowaniem, sformatujmy całą tabelę przestawną. Jest na to kilka sposobów. Użyjmy prostego ...
Kliknij Narzędzia PivotTable / Design patka:
Następnie upuść strzałkę w dolnym prawym rogu ekranu Style tabeli przestawnej listę, aby zobaczyć ogromny zbiór 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 stolik!
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).
Utrzymanie rzeczy znowu proste, zobaczmy, jak knuć uśrednione wartości, a nie zsumowane wartości.
Najpierw kliknij "Suma kwoty" i wybierz Ustawienia pola wartości ... z pojawiającego się menu kontekstowego:
w Podsumuj pole wartości o lista w Ustawienia pola wartości pole, wybierz Średni:
Kiedy tu jesteśmy, zmieńmy to Nazwa niestandardowa, od "Average of Amount" do czegoś trochę bardziej zwięzłego. Wpisz coś w rodzaju "Śr.":
Kliknij dobrzei zobacz, jak to wygląda. Zauważ, że wszystkie wartości zmieniają się od sum zsumowanych do średnich, a tytuł tabeli (od lewej górnej komórki) zmienił się 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):
pole od Etykiety kolumn do Etykiety wierszyOto, na czym kończymy:
Łącznie, przeciętnie i licz na tę samą tabelę przestawną!
Wniosek
Istnieje wiele, wiele innych 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ść, której nigdy nie mieli starsi użytkownicy DOS Lotus 1-2-3.
Jeśli pracujesz w pakiecie Office 2007, możesz zapoznać się z naszym artykułem na temat tworzenia tabeli przestawnej w programie Excel 2007.
Dołączamy 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
