Jak utworzyć tabelę przestawną w programie Excel: samouczek krok po kroku

Tabela przestawna to jedna z najpotężniejszych — i zastraszających — funkcji programu Microsoft Excel. Potężny, ponieważ może pomóc w podsumowaniu i zrozumieniu dużych zbiorów danych. Onieśmielający, ponieważ nie jesteś ekspertem od Excela, a tabele przestawne zawsze miały opinię skomplikowanych.

Dobra wiadomość: nauka tworzenia tabeli przestawnej w programie Excel jest znacznie łatwiejsza, niż mogłoby się wydawać.

Ale zanim przeprowadzimy Cię przez proces tworzenia tabeli, cofnijmy się o krok i upewnijmy się, że dokładnie rozumiesz, czym jest tabela przestawna i dlaczego możesz jej potrzebować.

Innymi słowy, tabele przestawne wydobywają znaczenie z tej pozornie nieskończonej plątaniny liczb na ekranie. A dokładniej, umożliwia grupowanie danych na różne sposoby, dzięki czemu można łatwiej wyciągnąć pomocne wnioski.

Część „przestawna” tabeli przestawnej wynika z faktu, że możesz obracać (lub przestawiać) dane w tabeli, aby zobaczyć je z innej perspektywy. Aby było jasne, nie dodajesz, nie odejmujesz ani w żaden inny sposób nie zmieniasz danych podczas tworzenia osi. Zamiast tego po prostu reorganizujesz dane, aby móc ujawnić z nich przydatne informacje.

Do czego służą tabele przestawne?

Jeśli nadal czujesz się nieco zdezorientowany, co właściwie robią tabele przestawne, nie martw się. Jest to jedna z tych technologii, które o wiele łatwiej zrozumieć, gdy zobaczysz ją w akcji.

Celem tabel przestawnych jest oferowanie przyjaznych dla użytkownika sposobów szybkiego podsumowywania dużych ilości danych. Można ich używać do lepszego zrozumienia, wyświetlania i szczegółowej analizy danych liczbowych — i mogą pomóc w identyfikowaniu i odpowiadaniu na nieoczekiwane pytania z nimi związane.

Oto siedem hipotetycznych scenariuszy, w których rozwiązaniem może być tabela przestawna:

1. Porównanie sum sprzedaży różnych produktów.

Załóżmy, że masz arkusz, który zawiera miesięczne dane sprzedaży dla trzech różnych produktów — produktu 1, produktu 2 i produktu 3 — i chcesz dowiedzieć się, który z tych trzech przynosi najwięcej pieniędzy. Możesz oczywiście przejrzeć arkusz i ręcznie dodać odpowiednią wartość sprzedaży do bieżącej sumy za każdym razem, gdy pojawi się produkt 1. Następnie możesz zrobić to samo dla produktu 2 i produktu 3, aż uzyskasz sumy dla nich wszystkich. Bułka z masłem, prawda?

Teraz wyobraź sobie, że Twój miesięczny arkusz sprzedaży zawiera tysiące wierszy. Ręczne sortowanie ich wszystkich może zająć całe życie. Korzystając z tabeli przestawnej, możesz automatycznie agregować wszystkie dane dotyczące sprzedaży dla produktu 1, produktu 2 i produktu 3 — i obliczyć ich odpowiednie sumy — w mniej niż minutę.

2. Pokazywanie sprzedaży produktów jako procent całkowitej sprzedaży.

Tabele przestawne w naturalny sposób pokazują sumy każdego wiersza lub kolumny podczas ich tworzenia. Ale to nie jedyna liczba, którą możesz wyprodukować automatycznie.

Załóżmy, że w arkuszu Excela wprowadzono kwartalne dane dotyczące sprzedaży trzech osobnych produktów i przekształciłeś te dane w tabelę przestawną. Tabela automatycznie wyświetli trzy sumy na dole każdej kolumny — po zsumowaniu kwartalnej sprzedaży każdego produktu. Ale co by było, gdybyś chciał dowiedzieć się, jaki procent sprzedaży tych produktów ma w stosunku do całej sprzedaży firmy, a nie tylko do łącznej sprzedaży tych produktów?

Dzięki tabeli przestawnej możesz skonfigurować każdą kolumnę, aby podać procent wszystkich trzech kolumn, a nie tylko sumę kolumny. Jeśli na przykład sprzedaż trzech produktów wyniosła 200 000 USD, a pierwszy produkt przyniósł 45 000 USD, możesz edytować tabelę przestawną, aby zamiast tego powiedzieć, że ten produkt przyczynił się do 22,5% całej sprzedaży firmy.

Aby wyświetlić sprzedaż produktu jako procent całkowitej sprzedaży w tabeli przestawnej, po prostu kliknij prawym przyciskiem myszy komórkę zawierającą sumę sprzedaży i wybierz opcję Pokaż wartości jako > % sumy całkowitej .

3. Łączenie zduplikowanych danych.

W tym scenariuszu właśnie ukończyłeś przeprojektowanie bloga i musiałeś zaktualizować kilka adresów URL. Niestety, twoje oprogramowanie do raportowania blogów nie radziło sobie z tym zbyt dobrze i ostatecznie podzieliło dane „widoku” dla pojedynczych postów między dwa różne adresy URL. Tak więc w arkuszu kalkulacyjnym masz dwa oddzielne wystąpienia każdego indywidualnego posta na blogu. Aby uzyskać dokładne dane, musisz połączyć sumy widoków dla każdego z tych duplikatów.

W tym miejscu do gry wkracza tabela przestawna. Zamiast ręcznie wyszukiwać i łączyć wszystkie dane z duplikatów, możesz podsumować swoje dane (za pomocą tabeli przestawnej) według tytułu posta na blogu i voila: dane widoku z tych zduplikowanych postów będą agregowane automatycznie.

4. Uzyskanie liczby pracowników dla poszczególnych działów.

Tabele przestawne są przydatne do automatycznego obliczania elementów, których nie można łatwo znaleźć w podstawowej tabeli programu Excel. Jedną z tych rzeczy jest liczenie rzędów, które mają ze sobą coś wspólnego.

Jeśli masz na przykład listę pracowników w arkuszu Excel, a obok nazw pracowników znajdują się odpowiednie działy, do których należą, możesz utworzyć tabelę przestawną z tych danych, która pokazuje nazwę każdego działu i liczbę pracowników należące do tych działów. Tabela przestawna skutecznie eliminuje zadanie sortowania arkusza Excel według nazwy działu i ręcznego liczenia każdego wiersza.

5. Dodawanie wartości domyślnych do pustych komórek.

Nie każdy zestaw danych wprowadzony do programu Excel zapełni każdą komórkę. Jeśli czekasz na pojawienie się nowych danych przed wprowadzeniem ich do programu Excel, możesz mieć wiele pustych komórek, które wyglądają na mylące lub wymagają dalszych wyjaśnień podczas wyświetlania tych danych kierownikowi. Tu właśnie wkraczają tabele przestawne.

Możesz łatwo dostosować tabelę przestawną, aby wypełnić puste komórki wartością domyślną, taką jak 0 USD lub TBD (określenie „do ustalenia”). W przypadku dużych tabel danych możliwość szybkiego tagowania tych komórek jest użyteczną funkcją, gdy wiele osób przegląda ten sam arkusz.

Aby automatycznie sformatować puste komórki tabeli przestawnej, kliknij tabelę prawym przyciskiem myszy i kliknij Opcje tabeli przestawnej. W wyświetlonym oknie zaznacz pole oznaczone jako Puste komórki jako i wprowadź, co ma być wyświetlane, gdy komórka nie ma innej wartości.

Jak stworzyć tabelę przestawną

1. Wprowadź swoje dane do zakresu wierszy i kolumn.
2. Sortuj dane według określonego atrybutu.
3. Zaznacz swoje komórki, aby utworzyć tabelę przestawną.
4. Przeciągnij i upuść pole w obszarze „Etykiety wierszy”.
5. Przeciągnij i upuść pole w obszarze „Wartości”.
6. Dostosuj swoje obliczenia.

Teraz, gdy masz już lepsze wyobrażenie o tym, do czego można wykorzystać tabele przestawne, przejdźmy do sedna tego, jak właściwie je utworzyć.

Krok 1. Wprowadź dane do zakresu wierszy i kolumn.

Każda tabela przestawna w programie Excel zaczyna się od podstawowej tabeli programu Excel, w której przechowywane są wszystkie dane. Aby utworzyć tę tabelę, po prostu wprowadź swoje wartości w określonym zestawie wierszy i kolumn. Użyj najwyższego wiersza lub najwyższej kolumny, aby skategoryzować swoje wartości według tego, co reprezentują.

Na przykład, aby utworzyć tabelę Excela z danymi o skuteczności postów na blogu, możesz mieć kolumnę z listą „Najważniejszych stron”, kolumnę z listą „Kliknięć” każdego adresu URL, kolumnę z listą „Wyświetlenia” każdego posta i tak dalej. (Będziemy używać tego przykładu w kolejnych krokach).

Krok 2. Posortuj dane według określonego atrybutu.

Gdy masz już wszystkie dane, które chcesz wprowadzić do arkusza programu Excel, będziesz chciał w jakiś sposób je posortować, aby łatwiej było nimi zarządzać po przekształceniu ich w tabelę przestawną.

Aby posortować dane, kliknij kartę Dane na górnym pasku nawigacyjnym i wybierz ikonę Sortuj pod nią. W wyświetlonym oknie możesz wybrać sortowanie danych według dowolnej kolumny i w dowolnej kolejności.

Na przykład, aby posortować arkusz Excela według „Wyświetleń do daty”, wybierz ten tytuł kolumny w obszarze Kolumna , a następnie wybierz, czy chcesz uporządkować swoje posty od najmniejszego do największego, czy od największego do najmniejszego.

Wybierz OK w prawym dolnym rogu okna sortowania, a pomyślnie zmienisz kolejność każdego wiersza arkusza Excela według liczby wyświetleń, które otrzymał każdy post na blogu.

Krok 3. Zaznacz swoje komórki, aby utworzyć tabelę przestawną.

Po wprowadzeniu danych do arkusza programu Excel i posortowaniu ich według własnych upodobań zaznacz komórki, które chcesz podsumować w tabeli przestawnej. Kliknij Wstaw w górnej części nawigacyjnej i wybierz ikonę tabeli przestawnej . Możesz także kliknąć w dowolnym miejscu w arkuszu, wybrać "Tabela przestawna" i ręcznie wprowadzić zakres komórek, które chcesz uwzględnić w tabeli przestawnej.

Spowoduje to otwarcie okna opcji, w którym oprócz ustawienia zakresu komórek możesz wybrać, czy chcesz uruchomić tę tabelę przestawną w nowym arkuszu, czy zachować ją w istniejącym arkuszu. Jeśli otworzysz nowy arkusz, możesz przejść do niego i od niego odejść na dole skoroszytu programu Excel. Po dokonaniu wyboru kliknij OK.

Możesz też podświetlić komórki, wybrać Polecane tabele przestawne po prawej stronie ikony tabeli przestawnej i otworzyć tabelę przestawną ze wstępnie ustawionymi sugestiami, jak uporządkować każdy wiersz i kolumnę.

Uwaga: Jeśli używasz starszej wersji programu Excel, „Tabele przestawne” mogą znajdować się w obszarze Tabele lub Dane na górnym pasku nawigacyjnym, a nie „Wstaw”. W Arkuszach Google możesz tworzyć tabele przestawne z menu rozwijanego Dane w górnej części nawigacyjnej.

Krok 4. Przeciągnij i upuść pole w obszarze „Etykiety wierszy”.

Po wykonaniu kroku 3 program Excel utworzy dla Ciebie pustą tabelę przestawną. Następnym krokiem jest przeciągnięcie i upuszczenie pola — oznaczonego zgodnie z nazwami kolumn w arkuszu kalkulacyjnym — do obszaru Etykiety wierszy . To określi, jaki unikalny identyfikator — tytuł posta na blogu, nazwa produktu itd. — tabela przestawna uporządkuje dane według.

Załóżmy na przykład, że chcesz uporządkować dane z bloga według tytułu posta. Aby to zrobić, wystarczy kliknąć i przeciągnąć pole „Najpopularniejsze strony” do obszaru „Etykiety wierszy”.

Uwaga: Twoja tabela przestawna może wyglądać inaczej w zależności od wersji programu Excel, z którą pracujesz. Jednak ogólne zasady pozostają takie same.

Krok 5. Przeciągnij i upuść pole do obszaru „Wartości”.

Po ustaleniu, według czego zamierzasz uporządkować dane, następnym krokiem jest dodanie niektórych wartości przez przeciągnięcie pola do obszaru Wartości .

Pozostając przy przykładzie danych z bloga, załóżmy, że chcesz podsumować wyświetlenia postów na blogu według tytułu. Aby to zrobić, po prostu przeciągnij pole „Widoki” do obszaru Wartości.

Krok 6. Dostosuj swoje obliczenia.

Suma określonej wartości zostanie obliczona domyślnie, ale możesz łatwo zmienić ją na coś takiego jak średnia, maksymalna lub minimalna, w zależności od tego, co chcesz obliczyć.

Na komputerze Mac możesz to zrobić, klikając małe i obok wartości w obszarze "Wartości", wybierając żądaną opcję i klikając "OK". Po dokonaniu wyboru tabela przestawna zostanie odpowiednio zaktualizowana.

Jeśli używasz komputera, musisz kliknąć mały odwrócony trójkąt obok wartości i wybrać Ustawienia pola wartości , aby uzyskać dostęp do menu.

Po skategoryzowaniu danych zgodnie z własnymi upodobaniami zapisz swoją pracę i używaj jej tak, jak chcesz.

Głębsze kopanie za pomocą stołów obrotowych

Poznałeś już podstawy tworzenia tabel przestawnych w programie Excel. Dzięki temu zrozumieniu możesz dowiedzieć się, czego potrzebujesz ze swojej tabeli przestawnej i znaleźć rozwiązania, których szukasz.

Na przykład możesz zauważyć, że dane w tabeli przestawnej nie są posortowane w żądany sposób. W takim przypadku funkcja sortowania programu Excel może ci pomóc. Ewentualnie może być konieczne uwzględnienie w raportach danych z innego źródła, w którym to przypadku funkcja WYSZUKAJ.PIONOWO może się przydać.

Uwaga redaktora: Ten post został pierwotnie opublikowany w grudniu 2018 r. i został zaktualizowany w celu zapewnienia kompleksowości.