Tabele przestawne to jedna z najpotężniejszych funkcji programu Excel. Nawet jeśli jesteś nowicjuszem, możesz przekuć duże ilości danych w przydatne informacje. Tabela przestawna może pomóc w tworzeniu raportów w ciągu kilku minut. Łatwo analizuj czyste dane, a jeśli dane nie są czyste, może pomóc w ich wyczyszczeniu. Nie chcę Cię zanudzać, więc wskoczmy do tego i zbadajmy.
Jak stworzyć tabelę przestawną
To proste. Po prostu wybierz swoje dane. Przejdź do Wstaw. Kliknij tabelę przestawną i gotowe.
Ale poczekaj. Zanim utworzysz tabelę przestawną, upewnij się, że wszystkie kolumny mają nagłówek.
Jeśli dowolny nagłówek kolumny pozostanie pusty, tabela przestawna nie zostanie utworzona i wyświetli komunikat o błędzie.
Wymaganie 1: Wszystkie kolumny powinny mieć nagłówek, aby rozpocząć pracę z tabelami przestawnymi w programie Excel
Powinieneś mieć swoje dane uporządkowane z odpowiednim nagłówkiem. Gdy już to zrobisz, możesz wstawić tabelę przestawną.
Wstaw tabelę przestawną ze wstążki
Aby wstawić tabelę przestawną z menu, wykonaj następujące kroki:
1. Wybierz zakres danych
2. Przejdź do zakładki wstawiania
3. Kliknij ikonę tabeli przestawnej.
4. Pojawi się okno opcji Utwórz tabelę przestawną.
5. Tutaj możesz zobaczyć wybrany zakres danych. Jeśli uważasz, że nie jest to zakres, który chcesz wybrać, bezpośrednio zmień rozmiar zakresu w tym miejscu, zamiast cofać się i ponownie wybierać dane.
6. Następnie możesz wybrać miejsce, w którym chcesz umieścić tabelę przestawną. Polecam korzystanie z nowego arkusza roboczego, ale możesz również użyć obecnego arkusza roboczego. Wystarczy określić lokalizację w polu Lokalizacja.
7. Teraz, jeśli skończysz z ustawieniami, naciśnij przycisk OK. Będziesz mieć swoją tabelę przestawną w nowym arkuszu. Wystarczy wybrać pola do podsumowań. Zobaczymy, jak tworzymy podsumowanie danych za pomocą tabeli przestawnej, ale najpierw wyjaśnijmy podstawy. Z tego samouczka dotyczącego tabeli przestawnej programu Excel dowiesz się więcej, niż się spodziewasz.
Wstaw skrót do tabeli przestawnej (Alt > N > V)
To jest sekwencyjny skrót klawiaturowy służący do otwierania Utwórz Stół obrotowy pole opcji.
Naciśnij przycisk Alt i zwolnij go. Uderzyć n i zwolnij. Uderzyć V i zwolnij. Otworzy się okno opcji Utwórz tabelę przestawną.
Teraz wystarczy wykonać powyższą procedurę, aby utworzyć tabelę przestawną w programie Excel.
Wstaw skrót tabeli przestawnej za pomocą starego skrótu programu Excel (Alt > D > P)
Jedną z rzeczy, które najbardziej lubię w programie Microsoft Excel, jest to, że w każdej nowej wersji programu Excel wprowadzane są nowe funkcje, ale nie odrzucaj starych funkcji (jak MS zrobił z wygraną 8. To było żałosne). Ten pozwala starym użytkownikom normalnie pracować nad nowymi wersjami, tak jak wcześniej pracowali na starszych wersjach.
Jeśli naciśniesz kolejno ALT, D i P na klawiaturze program Excel otworzy kreatora tabeli przestawnej.
Wybierz odpowiednią opcję. Wybrana opcja na powyższym zrzucie ekranu doprowadzi nas do utworzenia tabeli przestawnej, tak jak stworzyliśmy wcześniej.
Naciśnij Enter lub kliknij Dalej, jeśli chcesz sprawdzić wybrany zakres.
Ponownie naciśnij Enter.
Wybierz nowy arkusz roboczy lub miejsce, w którym chcesz, aby Twoja tabela przestawna miała nacisnąć Enter. I gotowe.
Twórz raporty za pomocą tabel przestawnych
Teraz wiesz, jak wstawić tabelę przestawną. Przygotujmy się do tworzenia raportów za pomocą tabel przestawnych w ciągu kilku minut.
Mamy dane do zamówienia stacjonarnego.
Pola kolumn to:
Data zamówienia: Data zamówienia (oczywiście)
Region: Region porządku w kraju
Klient: Imię i nazwisko klienta (co jeszcze może być)
Przedmiot: Zamówiony przedmiot
Jednostka: Liczba sztuk zamówionego towaru
Cena jednostkowa: Koszt jednostkowy
Całkowity: Całkowity koszt zamówienia (jednostka*koszt jednostkowy).
Do tworzenia raportów za pomocą tabel przestawnych użyjemy
Pola tabeli przestawnej: Zawiera listę nazw kolumn w Twoich danych.
Obszary obrotu: Te 4 obszary służą do prezentowania danych w sposób zmanierowany.
FILTRY: Umieść tutaj pola, z których chcesz zastosować filtry w swoim raporcie.
KOLUMNY: Umieść tutaj pola, które chcesz umieścić w swoich kolumnach w raporcie: (Lepiej pokazać niż wyjaśnić)
WYDZIWIANIE: Przeciągnij pola, które chcesz pokazać surowo, jak na powyższym obrazku, pokazałem region w ROWS.
WARTOŚCI: Wybierz pole, aby uzyskać liczbę, sumę, średnią, procent (i wiele innych), które chcesz zobaczyć.
Teraz korzystając z powyższych informacji, przygotowaliśmy ten szybki raport, który pokazuje, z jakiego regionu, ile zamówień zostało złożonych na każdy przedmiot.
Teraz, gdy rozumiesz już swoje dane i pola przestawne (w końcu jesteś sprytny), szybko odpowiedzmy na kilka pytań związanych z tymi danymi, korzystając z tabeli przestawnej.
Q1. Ile jest zamówień?
Tabela przestawna jest początkowo pusta, jak pokazano na poniższym obrazku.
Musisz wybrać pola (nazwy kolumn) w odpowiednich obszarach, aby zobaczyć podsumowanie lub szczegóły tego pola.
Teraz, aby odpowiedzieć na powyższe pytanie, wybiorę element (wybierz dowolną kolumnę, po prostu upewnij się, że nie ma między nią pustej komórki) w polach wartości.
Wybierz pozycję z listy pól i przeciągnij ją do pola Wartość.
Mamy odpowiedź. Tabele przestawne mówią mi, że w sumie są 43 zamówienia. To jest poprawne.
Teraz, kiedy wybrałem Data zamówienia w obszarze Wartości pokazuje 42. Ten znaczy Data zamówienia ma pustą komórkę odkąd wiemy, że łączna liczba zamówień wynosi 43.
Zidentyfikuj nieregularne dane za pomocą tabel przestawnych i wyczyść je.
Tabela przestawna może pomóc w znalezieniu nieprawidłowych informacji w danych.
W większości przypadków nasze dane są przygotowywane przez operatorów wprowadzania danych lub użytkowników, którzy są zwykle nieregularni i wymagają pewnego czyszczenia, aby przygotować dokładny raport i analizę.
Zawsze powinieneś wyczyścić i przygotować swoje dane w sposób manieryczny, zanim przygotujesz jakiekolwiek raporty. Czasami jednak dopiero po sporządzeniu raportu dowiadujemy się, że nasze dane zawierają pewną nieprawidłowość. Chodź, pokażę Ci jak…
P2: Podaj liczbę zamówień z każdego regionu
Teraz odpowiem na to pytanie:
Wybierz Region i przeciągnij go do Wydziwianie Powierzchnia i Przedmiot do Wartości Powierzchnia.
Otrzymamy dane podzielone na regiony. Możemy odpowiedzieć, z jakiego regionu przyszło ile zamówień.
Zgodnie z tabelą przestawną w naszych danych znajdują się łącznie 4 regiony. Ale czekaj, zauważ, że Centralny i Centralny region. Wiemy, że Centrum powinno być Centralny. Wystąpiła nieprawidłowość. Musimy przejść do naszych danych i wyczyścić dane.
Aby wyczyścić dane w polu regionu, odfiltrowujemy nieprawidłową nazwę regionu (Środek) i poprawiamy ją (Środek).
Teraz wróć do swoich danych osiowych.
Kliknij prawym przyciskiem myszy w dowolnym miejscu tabeli przestawnej i kliknij Odśwież.
Twój raport został zaktualizowany.
Teraz widać, że w rzeczywistości są tylko 3 regiony.
Formatowanie raportu przestawnego za pomocą skategoryzowanych wierszy.
Czasami będziesz potrzebować raportów takich jak na powyższym obrazku. Ułatwia to przeglądanie danych w uporządkowany sposób. Możesz łatwo stwierdzić, z jakiego regionu, ile sztuk jest zamawianych. Zobaczmy, jak możesz to zrobić.
Ruszaj się Region oraz Przedmiot do WYDZIWIANIE powierzchnia. Upewnij się, że region znajduje się na górze, a elementy na dole, jak pokazano na obrazku.
Ciągnąć Przedmiot dla Wartość Powierzchnia.
W rezultacie otrzymasz ten raport.
To wystarczy. Ale czasami twój szef chce raportować w formie tabelarycznej bez sum pośrednich. W tym celu musimy sformatować naszą tabelę przestawną.
Usuń sumy częściowe z tabeli przestawnej
Wykonaj następujące kroki:
1. Kliknij w dowolnym miejscu tabeli przestawnej.
2. Przejdź do Projekt Patka.
3. Kliknij sumy częściowe menu.
4. Kliknij Nie pokazuj sum częściowych.
Widać, że nie ma teraz sum częściowych.
W porządku. Ale nadal nie jest w formie tabelarycznej. Regiony i elementy są pokazane w jednej kolumnie. Pokaż je osobno.
Utwórz tabelę przestawną tabelaryczną
Teraz, aby wyświetlić regiony i elementy w różnych kolumnach, wykonaj następujące kroki:
1. Kliknij w dowolnym miejscu w tabeli przestawnej
2. Przejdź do zakładki Projekt
3. Kliknij Układ raportu.
4. Kliknij Pokaż dla opcji Forma tabelaryczna. Wreszcie otrzymasz ten wyrafinowany widok swojego raportu.
Teraz znamy całkowitą liczbę zamówień złożonych na każdy przedmiot z każdego regionu. Jest to pokazane w Hrabstwie kolumn pozycji.
Zmień nazwę kolumny na Zamówienia.
Teraz wygląda lepiej.
P3: Ile sztuk każdego przedmiotu jest zamawianych?
Aby odpowiedzieć na to pytanie potrzebujemy sumy Jednostek. Aby to zrobić, po prostu przenieś pole Jednostki na Wartości. Automatycznie zsumuje liczbę jednostek dla każdej pozycji. Jeśli kolumna w tabeli przestawnej zawiera tylko wartości, w tabeli przestawnej domyślnie wyświetlana jest suma tych wartości. Ale można to zmienić z ustawienia pola wartości. Jak? Pokażę ci to drugie.
Ustawienia pola wartości tabeli przestawnej
P4: średnia cena każdego przedmiotu?
W naszych przykładowych danych cena jednego przedmiotu jest inna dla różnych zamówień. Na przykład zobacz Segregatory.
Chcę poznać średni koszt każdego elementu w tabeli przestawnej. Aby to sprawdzić, przeciągnij koszt jednostkowy do pola wartości. Wyświetli sumę kosztu jednostkowego.
Nie chcemy Suma kosztu jednostkowego, chcemy Średni koszt jednostkowy. Aby to zrobić…
1. Kliknij prawym przyciskiem myszy w dowolnym miejscu sumy w kolumnie Koszt jednostkowy w tabeli przestawnej
2. Kliknij Ustawienia pola wartości
3. Na podstawie dostępnych opcji, Wybierz Przeciętny i naciśnij OK.
Na koniec otrzymasz ten raport przestawny:
Pola obliczeniowe tabeli przestawnej
Jedną z najbardziej użytecznych funkcji tabeli przestawnej są pola obliczeniowe. Pola obliczeniowe to pola, które uzyskuje się przez niektóre operacje na dostępnych kolumnach.
Przyjrzyjmy się, jak wstawiać pola obliczeniowe w tabeli przestawnej na jednym przykładzie:
Na podstawie naszych danych przygotowaliśmy ten raport.
Mamy tutaj Suma jednostek oraz Całkowity koszt. Właśnie przeniosłem kolumnę sumy do pola Wartości, a następnie zmieniłem jej nazwę na Całkowity koszt. Teraz chcę poznać średnią cenę jednostki każdego przedmiotu dla każdego regionu. A to byłoby:
Średnia cena = całkowity koszt / całkowita liczba jednostek
Wstawmy do tabeli przestawnej pole, które pokazuje średnią cenę każdego regionu przedmiotu:
Wykonaj poniższe czynności, aby wstawić pole obliczeniowe do tabeli przestawnej
1. Kliknij w dowolnym miejscu w tabeli przestawnej i przejdź do zakładki Analiza
2. Kliknij Pola, Pozycje i Zestawy w grupie obliczeniowej.
3. Kliknij Pola obliczeniowe.
Zobaczysz to pole wprowadzania dla pola obliczeniowego:
4. W polu wprowadzania nazwy wpisz średni koszt lub cokolwiek chcesz, program Excel nie będzie miał nic przeciwko. W polu wprowadzania formuły napisz i naciśnij OK.
=Ogółem / Jednostki
Możesz wpisać to ręcznie z klawiatury lub możesz dwukrotnie kliknąć nazwy pól wymienione w obszarze Pola, aby wykonać operacje.
5. Masz teraz swoje pole obliczeniowe dodane do tabeli przestawnej. Nazywa się to sumą średniego kosztu, ale nie jest to suma. Excel po prostu uruchamia domyślną funkcję, aby nazwać kolumnę (jak rytuał). Zmień nazwę tej kolumny i ogranicz wyświetlane cyfry dziesiętne.
A tam masz obliczone pole. Możesz uczynić go tak złożonym, jak chcesz. Dla przykładu wziąłem tę prostą przeciętną operację.
Grupowanie w tabeli przestawnej
Masz przygotowany ten raport przestawny.
Teraz chcę, żeby ten raport był dzielony corocznie. Zobacz zrzut ekranu poniżej.
Mamy kolumnę dotyczącą daty zamówienia. Przenieś pole DataZamówienia do Wiersze na górze.
W niczym nie przypomina wymaganego raportu. Musimy mieć daty grupowe rok mądrze.
Teraz, aby zgrupować pole w tabeli przestawnej programu Excel, wykonaj następujące kroki:
1. Kliknij prawym przyciskiem myszy pole, które chcesz pogrupować.
2. Kliknij Grupa. Będziesz mieć to pole opcji do personalizacji. Ponieważ jest to kolumna danych, Excel pokazuje nam odpowiednio grupowanie. Możesz wybrać datę rozpoczęcia i zakończenia.
3. Wybierz na lata i naciśnij OK. Zrobiłeś coroczne grupowanie w tabeli przestawnej programu Excel.
Krajalnice stołu obrotowego
Fragmentatory zostały wprowadzone w programie Excel 2010 jako dodatek. W Excel 2013 i 2016 jest on domyślnie dostępny tak jak filtry.
Krajalnice to nic innego jak filtry. W przeciwieństwie do filtrów, fragmentatory pokazują wszystkie dostępne opcje tuż przed tobą. To sprawia, że pulpit nawigacyjny jest bardziej interaktywny.
Jak dodać fragmentatory w tabeli przestawnej, Excel 2016 i 2013?
Krajalnice stołowe przestawne są łatwe do dodania. Wykonaj następujące kroki:
1. Kliknij w dowolnym miejscu tabeli przestawnej i przejdź do karty Analiza.
2. Kliknij Wstaw fragmentator. Będziesz mieć listę pól dla swoich danych. Wybierz tyle, ile chcesz.
3. W tym przykładzie wybierz Region i naciśnij OK.
Do raportu został dodany fragmentator. Teraz zastosuj filtr jednym kliknięciem.
Wstaw oś czasu w Excelu 2016 i 2013
Jest to jedna z moich ulubionych funkcji tabel przestawnych programu Excel. Ta nowa funkcja działa tylko z datami. Korzystając z tego, możesz wizualnie wybrać przedział czasu, aby filtrować dane.
Aby wstawić kursor osi czasu, wykonaj następujące kroki:
Jak dodać oś czasu w tabeli przestawnej, Excel 2016 i 2013?
1. Kliknij w dowolnym miejscu w tabeli przestawnej i przejdź do zakładki Analiza.
2. Kliknij na Wstaw oś czasu z grupy filtrów. Wszystkie kolumny zawierające wartości czasu w danych źródłowych zostaną wymienione w polu opcji do wyboru. Tutaj mamy tylko jeden. Więc tak…
2. Wybierz opcje i naciśnij Enter lub kliknij OK. Gotowe i masz przed sobą oś czasu tabeli przestawnej.
Możesz wybrać wyświetlanie dzienne, miesięczne, kwartalne lub roczne. Tutaj wybrałem Monthly.
W tym artykule omówiłem najważniejsze i przydatne funkcjonalności Pivot Table. Zbadaliśmy nowe funkcje tabeli przestawnej w programach Excel 2016 i 2013. Dowiedzieliśmy się o klasycznym użyciu tabeli przestawnej, która była uruchamiana w programach Excel 2007, 2010 i starszych. Nadal są przydatne. Jeśli nie znalazłeś tutaj odpowiedzi związanej z tabelą przestawną, zapytaj w sekcji komentarzy.
Istnieje wiele innych funkcji, które nie zostały jeszcze wyjaśnione. Zaawansowaną funkcję tabeli przestawnej poznamy w następnym artykule. Do tego czasu Excel we wszystkim.