Jak tworzyć tabele przestawne w programie Excel

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 z tego miejsca, 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 lokalizacji.
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 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 wprowadzają nowe funkcje i ale nie odrzucają starych funkcji (tak jak MS z wygraną 8. To było żałosne). Ten pozwala starszemu użytkownikowi normalnie pracować na nowych wersjach, tak jak wcześniej pracował na starszych wersjach.
Jeśli naciśniesz kolejno ALT, D i P na klawiaturze program Excel otworzy się, aby utworzyć 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ę 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).

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ę Przedmiot (wybierz dowolną kolumnę, po prostu upewnij się, że nie ma między nią pustej komórki) w polach wartości.

Wybierz Przedmiot z listy pól i przeciągnij go do pola Wartość.

Mamy odpowiedź. Tabele przestawne mówią mi, że w sumie są 43 zamówienia. To jest poprawne.
Wskazówka dla profesjonalistów: Powinieneś sprawdzić swoje dane, czy są poprawne, czy nie. Jeśli ta liczba nie pasuje do danych, oznacza to, że wybrałeś nieprawidłowy zakres lub pole ma puste komórki.
Informacje: Pole wartości domyślnie zlicza liczbę wpisów w kolumnie, jeśli zawiera tekst i sumuje, jeśli pole zawiera tylko wartości. Możesz to zmienić w ustawieniach pola wartości. Jak? Do zobaczenia później w tym samouczku dotyczącym tabel przestawnych.
Teraz, kiedy wybrałem Data zamówienia w obszarze wartości, to pokazuje 42. To 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 Środkowy 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.
INFORMACJE: Bez względu na to, jakie zmiany wprowadzisz w danych źródłowych, tabela przestawna będzie nadal działać na starych danych dopóki go nie odświeżysz. Program Excel tworzy pamięć podręczną przestawną, a tabela przestawna działa w tej pamięci podręcznej. Po odświeżeniu stara pamięć podręczna jest zmieniana na nowe dane.

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 menu Podsumy.

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 przedmioty 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 formy tabelarycznej opcja. 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 przedmiotów kolumny.
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ć…

Kliknij prawym przyciskiem myszy w dowolnym miejscu sumy w kolumnie Koszt jednostkowy w tabeli przestawnej
Kliknij Ustawienia pola wartości
W oparciu o dostępne opcje, 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ładka Analizuj

2. Kliknij Pola, przedmioty i zestawy w grupie obliczeniowej.

3. Kliknij Pola obliczeniowe.
Zobaczysz to pole wprowadzania dla pola obliczeniowego:

4. W polu wprowadzania nazwy wpisz Średnia Koszt lub cokolwiek chcesz, Excel nie będzie miał nic przeciwko. w formuła pole wprowadzania, napisz i naciśnij ok.