Tabela przestawna programu Excel to jedno z najpotężniejszych narzędzi, które mogą pomóc w efektywnym podsumowywaniu i analizowaniu dużych zestawów danych.
Tabela przestawna to jedno z najpotężniejszych i najbardziej użytecznych narzędzi do podsumowywania danych w programie Excel, które umożliwia szybkie podsumowywanie, sortowanie, reorganizowanie, analizowanie, grupowanie i liczenie dużych zestawów danych.
Tabela przestawna umożliwia obracanie (lub przestawianie) danych przechowywanych w tabeli, aby wyświetlić je z różnych perspektyw i mieć jasne zrozumienie dużych zestawów danych.
Ten samouczek zawiera szczegółowe instrukcje dotyczące tworzenia i używania tabel przestawnych w programie Excel.
Uporządkuj swoje dane
Aby utworzyć tabelę przestawną, dane powinny mieć strukturę tabeli lub bazy danych. Musisz więc uporządkować swoje dane w wiersze i kolumny. Aby przekonwertować zakres danych do tabeli, zaznacz wszystkie dane, przejdź do zakładki „Wstaw” i kliknij „Tabela”. W oknie dialogowym Utwórz tabelę kliknij „OK”, aby przekonwertować zestaw danych na tabelę.
Używanie tabeli Excel jako źródłowego zestawu danych do tworzenia tabeli przestawnej sprawia, że Twoja tabela przestawna jest dynamiczna. Gdy dodasz lub usuniesz wpisy w tabeli programu Excel, dane w osi zostaną zaktualizowane.
Załóżmy, że masz duży zestaw danych, jak pokazano poniżej, składa się on z ponad 500 rekordów i 7 pól. Data, region, typ sprzedawcy, firma, ilość, dochód i zysk.
Wstaw tabelę przestawną
Najpierw zaznacz wszystkie komórki zawierające dane, przejdź do zakładki „Wstaw” i kliknij „Wykres przestawny”. Następnie wybierz opcję „Wykres przestawny i tabela przestawna” z menu rozwijanego.
Otworzy się okno dialogowe Utwórz tabelę przestawną. Excel automatycznie zidentyfikuje i wypełni prawidłowy zakres w polu „Tabela/zakres”, w przeciwnym razie wybierze prawidłową tabelę lub zakres komórek. Następnie określ lokalizację docelową tabeli przestawnej programu Excel, może to być „Nowy arkusz roboczy” lub „Istniejący arkusz roboczy” i kliknij „OK”.
Jeśli wybierzesz „Nowy arkusz roboczy”, nowy arkusz z pustą tabelą przestawną i wykresem przestawnym zostanie utworzony w osobnym arkuszu.
Zbuduj swój stół przestawny
W nowym arkuszu zobaczysz pustą tabelę przestawną po lewej stronie okna programu Excel oraz okienko „Pola tabeli przestawnej” po prawej stronie okna programu Excel, w którym znajdziesz wszystkie opcje konfiguracji tabeli przestawnej.
Okienko Pola tabeli przestawnej jest podzielone na dwie sekcje poziome: sekcję Pola (u góry okienka) i sekcję Układ (na dole okienka)
- ten Sekcja terenowa wyświetla wszystkie pola (kolumny) dodane do tabeli. Te nazwy pól to wszystkie nazwy kolumn z tabeli źródłowej.
- ten Sekcja układu ma 4 obszary, tj. Filtry, Kolumny, Wiersze i Wartości, za pomocą których można aranżować i zmieniać kolejność pól.
Dodaj pola do tabeli przestawnej
Aby zbudować tabelę przestawną, przeciągnij i upuść pola z sekcji Pole do obszarów sekcji Układ. Możesz także przeciągać pola między obszarami.
Dodaj wiersze
Zaczniemy od dodania pola „Firma” do sekcji Wiersze. Zwykle pola nieliczbowe są dodawane do obszaru Wiersz Układu. Po prostu przeciągnij i upuść pole „Firma” w obszarze „Wiersz”.
Wszystkie nazwy firm z kolumny „Firma” w tabeli źródłowej zostaną dodane jako wiersze w tabeli przestawnej i zostaną posortowane w porządku rosnącym, ale możesz kliknąć przycisk rozwijany w komórce Etykiety wierszy, aby zmienić kolejność.
Dodaj wartości
Dodałeś wiersz, teraz dodajmy wartość do tej tabeli, aby stała się tabelą jednowymiarową. Możesz utworzyć jednowymiarową tabelę przestawną, dodając do obszarów tylko etykiety wierszy lub kolumn oraz odpowiadające im wartości. Obszar wartości to miejsce, w którym przechowywane są obliczenia/wartości.
Na powyższym przykładowym zrzucie ekranu mamy rząd firm, ale chcemy poznać łączny przychód każdej firmy. Aby to uzyskać, po prostu przeciągnij i upuść pole „Przychody” do pola „Wartość”.
Jeśli chcesz usunąć jakiekolwiek pola z sekcji Obszary, po prostu usuń zaznaczenie pola obok pola w sekcji Pola.
Teraz mamy jednowymiarową tabelę firm (etykiety wierszy) wraz z sumą przychodów.
Dodaj kolumnę
Stół dwuwymiarowy
Wiersze i kolumny razem stworzą dwuwymiarową tabelę i wypełnią komórki trzecim wymiarem wartości. Załóżmy, że chcesz utworzyć tabelę przestawną, wymieniając nazwy firm w wierszach i używając kolumn do wyświetlania dat i wypełniając komórki całkowitymi przychodami.
Po dodaniu pola „Data” do obszaru „Kolumna”, program Excel automatycznie dodaje „Kwartalne” i „Lata” do pól kolumny, aby obliczyć i lepiej podsumować dane.
Teraz mamy dwuwymiarową tabelę z trzema wymiarami wartości.
Dodaj filtry
Jeśli chcesz odfiltrować dane według „Region”, możesz przeciągnąć i upuścić pole „Region” do obszaru Filtr.
Spowoduje to dodanie rozwijanego menu nad tabelą przestawną z wybranym „Polem filtra”. Dzięki temu możesz odfiltrować przychody firm za każdy rok według regionu.
Domyślnie zaznaczone są wszystkie regiony, odznacz je i wybierz tylko region, według którego chcesz filtrować dane. Jeśli chcesz filtrować tabelę według wielu wpisów, zaznacz pole wyboru obok „Wybierz wiele elementów” u dołu listy rozwijanej. I wybierz wiele regionów.
Wynik:
Sortowanie
Jeśli chcesz posortować wartość tabeli w kolejności rosnącej lub malejącej, kliknij prawym przyciskiem myszy dowolną komórkę w kolumnie Suma przychodów, a następnie rozwiń „Sortuj” i wybierz kolejność.
Wynik:
Grupowanie
Załóżmy, że w tabeli przestawnej masz dane uporządkowane według miesięcy, ale nie chcesz wyświetlać ich co miesiąc, zamiast tego chcesz uporządkować dane na kwartały finansowe. Możesz to zrobić w swojej tabeli przestawnej.
Najpierw wybierz kolumny i kliknij je prawym przyciskiem myszy. Następnie wybierz opcję „Grupuj” z menu.
W oknie Grupowanie wybierz „Kwartały” i „Lata”, ponieważ chcemy, aby były one podzielone na kwartały finansowe każdego roku. Następnie kliknij „OK”.
Teraz Twoje dane są uporządkowane w kwartały finansowe każdego roku.
Ustawienia pola wartości
Domyślnie tabela przestawna podsumowuje wartości liczbowe za pomocą funkcji Sum. Możesz jednak zmienić typ obliczeń używany w obszarze Wartości.
Aby zmienić funkcję podsumowania, kliknij prawym przyciskiem myszy dowolne dane w tabeli, kliknij opcję „Podsumuj wartości według” i wybierz odpowiednią opcję.
Możesz też kliknąć strzałkę w dół obok „Suma ..” w obszarze wartości w sekcji pola i wybrać „Ustawienia pola wartości”.
W „Ustawieniach pola wartości” wybierz funkcję do podsumowania danych. Następnie kliknij „OK”. W naszym przykładzie wybieramy „Policz”, aby policzyć liczbę zysków.
Wynik:
Tabele przestawne programu Excel umożliwiają również wyświetlanie wartości na różne sposoby, na przykład wyświetlanie sum całkowitych jako procentów lub sum kolumn jako procentów lub sum wierszy jako procentów lub wartości kolejności od najmniejszej do największej i odwrotnie, wiele więcej.
Aby wyświetlić wartości jako wartości procentowe, kliknij prawym przyciskiem myszy w dowolnym miejscu tabeli, a następnie kliknij „Pokaż wartości jako” i wybierz opcję.
Gdy wybierzemy „% sumy kolumn”, wynik będzie taki,
Odśwież tabelę przestawną
Chociaż raport tabeli przestawnej jest dynamiczny, podczas wprowadzania zmian w tabeli źródłowej program Excel nie odświeża automatycznie danych w tabeli przestawnej. Aby zaktualizować dane, należy go ręcznie „odświeżyć”.
Kliknij w dowolnym miejscu w tabeli przestawnej i przejdź do zakładki „Analizuj”, kliknij przycisk „Odśwież” w grupie Dane. Aby odświeżyć bieżącą tabelę przestawną w arkuszu, kliknij opcję „Odśwież”. Jeśli chcesz odświeżyć wszystkie tabele przestawne w skoroszycie, kliknij „Odśwież wszystko”.
Możesz też kliknąć tabelę prawym przyciskiem myszy i wybrać opcję „Odśwież”.
Otóż to. Mamy nadzieję, że ten artykuł zawiera szczegółowy przegląd tabel przestawnych programu Excel i pomoże w ich stworzeniu.