Dzięki nowej wersji 2016 Microsoft Excel zapewnia swoim entuzjastycznym użytkownikom ponad 15 odmian wykresów. Ale wykres przestawny w programie Excel pozostaje stałym faworytem wśród studentów, użytkowników domowych i profesjonalistów biznesowych. To jest ta pojedyncza mapa, która może spakować sporo informacji bez przytłaczania czytelnika. Ponadto można go zbudować na tabeli przestawnej i połączyć z niemal każdym innym rodzajem wykresu, co czyni go niezwykle elastycznym narzędziem do analizy danych!

W tym poście pokazujemy, jak zbudować swój pierwszy wykres przestawny w 10 łatwych krokach.

Krok 1: Przygotuj dane w formacie tabelarycznym

Pierwszym krokiem jest utworzenie tabelarycznych danych surowych w parzystych wierszach i kolumnach. Najlepiej jest pracować z tym wykresem na tabeli zawierającej co najmniej 2-3 kolumny, aby wykres przestawny zawierał wystarczającą liczbę pól kolumn, aby wyświetlać znaczące statystyki. Wykres przestawny w programie Excel działa również z wartościami zerowymi; ale w przypadku wykresu zrównoważonego upewnij się, że nie ma wartości pustych w kluczowych kolumnach. Upewnij się także, że Twoje dane są czyste, bez zduplikowanych wartości.

W naszym przykładzie utworzymy wykres przestawny z danymi od wydawcy średniego szczebla, aby uchwycić statystyki sprzedaży w popularnych czasopismach.

Tabela będzie zawierać 3 kolumny pól: nazwę czasopisma, miesiąc publikacji i całkowitą liczbę sprzedaży (w setkach). Kombinacja (Dziennik + miesiąc) jednoznacznie identyfikuje wiersz w tabeli. Ponadto zadbano o to, aby żaden wiersz nie zawierał wartości null. Dane tabeli zostały również posortowane według miesiąca, ale nie jest to konieczne.

  • Przygotuj podobne dane w nowym arkuszu w programie Excel i nazwij zawierający go arkusz „surowymi danymi”.

Polecane kursy

  • Szkolenie online na temat makra VBA
  • Szkolenie online na temat VBA i makr
  • Szkolenie podstawowe Excel 2007

Krok 2: Utwórz tabelę przestawną dla danych

Tabela przestawna idealnie przechowuje dane, które zasilają wykres przestawny w programie Excel. Uwaga - w tym momencie możesz bezpośrednio utworzyć wykres przestawny odnoszący się do przygotowanych surowych danych. Excel niejawnie utworzy tabelę przestawną i wykres przestawny. Może to jednak przytłoczyć nowych użytkowników, ponieważ wynikowy wykres będzie miał zbyt wiele parametrów do zarządzania.

Aby ułatwić zrozumienie, utworzymy jawnie tabelę przestawną, a następnie użyjemy jej do wprowadzenia wykresu przestawnego w programie Excel. Dzięki temu będziesz w stanie łatwo zrozumieć kroki pośrednie i lepiej zarządzać parametrami danych, które są wprowadzane do wykresu. Gdy zostaniesz ekspertem, możesz utworzyć wykres przestawny bezpośrednio z surowych danych.

Aby utworzyć tabelę przestawną,

  • Kliknij kartę Wstaw na głównej wstążce
  • Kliknij przycisk rozwijany tabeli przestawnej i wybierz tabelę przestawną
  • Tutaj możesz wprowadzić dane za pomocą jednej z następujących opcji:
    • Ręcznie wybierz zakres komórek, które składają się na surowe dane. Komórki zostaną podświetlone, a ich pozycje w tabeli zostaną przechwycone (jak pokazano na powyższym obrazku). LUB,
    • Użyj zewnętrznego źródła danych (takiego jak połączenie z bazą danych Microsoft Access lub inne połączenie ze źródłem danych).

Wybierzemy pierwszą opcję, ręcznie wybierając komórki w tabeli utworzonej w kroku 1. Pamiętaj o dołączeniu wszystkich wierszy i kolumn utworzonych danych tabeli.

  • Zaznacz pole wyboru „Nowy arkusz roboczy”, aby wymagana tabela przestawna została utworzona w nowym arkuszu programu Excel, dla lepszej przejrzystości. Excel utworzy tabelę przestawną w nowym arkuszu. Nazwij tę „tabelę przestawną”.

Krok 3: Zapoznaj się z parametrami tabeli przestawnej

W tym kroku nie ma żadnych wyraźnych działań. Jest to jednak marker pomagający zrozumieć różne parametry tabeli przestawnej.

Po utworzeniu tabeli przestawnej kliknij dowolne miejsce w tabeli, aby wyświetlić 2 karty:

  1. Rzeczywista tabela przestawna wyświetlana po lewej stronie arkusza roboczego. Podczas tworzenia tabeli wyświetlany jest tylko pusty szablon. Po zdefiniowaniu i dostosowaniu parametrów tabeli, tabela jest zapełniana wartościami z dostarczonych surowych danych.
  2. Lista pól zawierająca kolumny, które chcesz wypełnić w tabeli. Jest to dalej podzielone na:
    1. Pola tabeli przestawnej: domyślnie będą zawierały wszystkie pola kolumn dostępne w wybranych surowych danych.
    2. Etykiety wierszy i etykiety kolumn : To określi pola danych surowych (wiersze i kolumny), które są wyświetlane w tabeli przestawnej. (Tak więc pole kolumny może zasilać statystyki tabeli, wybierając w punkcie (a) powyżej, ale pozostanie ukryte, dopóki nie zostanie wyraźnie zaznaczone w etykiecie kolumny).
    3. Filtr raportu : służy do ustawienia wszelkich ograniczeń w celu odfiltrowania wierszy z surowych danych.
    4. Wartości: To określi dane statystyczne obliczone na podstawie tabeli przestawnej.

Razem ten ekran oznacza widok „Narzędzia tabeli przestawnej”. Za każdym razem, gdy chcesz zmienić lub odświeżyć tabelę przestawną, zwróć ten widok. W kolejnych krokach nauczymy się dostosowywać utworzoną tabelę przestawną, aby ujawniała dane liczbowe dotyczące naszych surowych danych.

Krok 4: Wybierz pola kolumny mające zastosowanie do tabeli przestawnej

Domyślnie wszystkie pola kolumn wybrane w surowych danych są dostępne do użycia w tabeli przestawnej. Należy je jednak wyraźnie wybrać, aby zastosować je do nowo utworzonej tabeli przestawnej. Uprościmy to, sprawdzając wszystkie kolumny naszych surowych danych w zakładce „Lista pól tabeli przestawnej”.

Krok 5: Dostosuj pola tabeli przestawnej

Kolejność wybierania pól kolumn dla tabeli przestawnej określa kolejność ich dodawania do tabeli. To z kolei kontroluje etykiety wierszy i etykiety kolumn i wpływa na ostateczne wartości obliczone w tabeli przestawnej. Tak więc, zgodnie z zamówieniem wybranym w kroku 4 (1) Dziennik 2) Miesiąc i 3) Sprzedaż), tabela wyświetli całkowitą sprzedaż dla każdego dziennika, pogrupowaną według miesięcy.

  • Teraz odznaczmy wszystkie pola i wypróbujmy to poprawione zamówienie: 1) Miesiąc 2) Dziennik 3) Sprzedaż. Spowoduje to wyświetlenie całkowitej sprzedaży dla każdego miesiąca, pogrupowanej według dziennika.

Jak widać, kolejność zaznaczania ma znaczący wpływ na sposób renderowania tabeli przestawnej. W naszym przykładzie zachowamy zamówienie zarejestrowane w kroku 4.

Krok 6: Dostosuj wartości tabeli przestawnej

Domyślnie MS Excel używa funkcji SUMA do obliczania wartości renderowanych w tabeli.

  • Rozważmy na przykład, że dodajesz tylko 2 pola kolumn w następującej kolejności: 1) Miesiąc 2) Sprzedaż. Spowoduje to automatyczne wyświetlenie całkowitej sumy sprzedaży dla każdego miesiąca.

Teraz rozważ, że chcesz wyświetlić łączną liczbę publikacji sprzedawanych co miesiąc we wszystkich czasopismach. Możesz to wyrenderować za pomocą funkcji COUNT w następujący sposób:

  • Kliknij kartę „Wartości” w widoku arkusza tabeli przestawnej. Domyślnie jest to ustawione na SUMĘ.
  • Kliknij menu rozwijane i wybierz „Ustawienia pola wartości…” i wybierz funkcję LICZBA w wyświetlonym polu listy.
  • Tabela obliczy całkowitą liczbę sprzedaży każdego miesiąca (wskazując liczbę sprzedanych publikacji), jak podano poniżej.

Krok 7: Utwórz wykres, aby rzutować tabelę przestawną

Teraz, gdy mamy w pełni funkcjonalną tabelę przestawną, każdy typ wykresu, który wyświetla tę tabelę, służy jako przerywany wykres przestawny w programie Excel. W naszym przykładzie utworzymy podstawowy wykres 3D-kolumny, aby rzutować naszą tabelę przestawną.

Dla tego,

  • Kliknij tabelę przestawną utworzoną w kroku 5, a następnie kliknij kartę Wstawianie.
  • Kliknij menu rozwijane Kolumna i wybierz Kolumna 3D.

Spowoduje to automatyczne rzutowanie twojego wykresu na wykres kolumnowy.

Kilka rzeczy do odnotowania tutaj:

  • Nazwa wykresu oparta jest na kolumnie pola Wartość. Oto suma SPRZEDAŻY.
  • Wykres przyjmuje kolumny pól tabeli przestawnej. Tutaj kolumny 1) Dziennik 2) Miesiąc i 3) Sprzedaż zostały wybrane do utworzenia wyświetlanego wykresu. Łącznie parametry te rejestrują prognozę sprzedaży w ujęciu dziennym w rozbiciu na miesiące.

I voila, stworzyłeś swój pierwszy (przerywany) wykres oparty na tabeli przestawnej! Dobra robota! jot

Uwaga: możesz zmieniać wykresy tworzone w tabeli przestawnej, eksperymentując z różnymi typami wykresów udostępnianymi przez program Excel w obszarze Wstaw kartę - Wykresy. W każdym przypadku wykres jest rzutem danych tabeli przestawnej, a zatem służy jako wykres przestawny w programie Excel. W dalszych krokach nauczysz się tworzyć i dostosowywać wykres przestawny bezpośrednio na podstawie surowych danych wejściowych.

Krok 8: Utwórz wykres przestawny

Teraz, gdy znasz już tabelę przestawną i rzutowane na niej wykresy, pomińmy kroki 2-7 i zobaczymy, jak możemy bezpośrednio rzutować nasze surowe dane na wykres przestawny. Tak, bez tabeli, bez przerywanego wykresu, tworzymy wykres przestawny na podstawie nieprzetworzonych danych, prosto z kroku 1! Masz teraz wiedzę, aby zrozumieć i odpowiednio dostosować jego parametry, więc wykres przestawny jest czysty i odpowiedni.

Aby utworzyć wykres przestawny,

  • Wybierz komórki zawierające surowe dane, utworzone w kroku 1.
  • Kliknij kartę Wstaw, a następnie menu rozwijane „Tabela przestawna”. Wybierz „PivotChart”.
  • Utwórz tabelę przestawną tak, jak utworzyłeś tabelę przestawną w kroku 2, przechodząc do ustawień domyślnych i klikając OK.
  • Zostanie utworzony wykres przestawny, jak pokazano poniżej. Nazwij ten arkusz jako „Wykres przestawny”.

Kilka rzeczy do zapamiętania:

  • Widok, który otwiera się po kliknięciu obszaru wykresu przestawnego, to widok „Narzędzia wykresu przestawnego”. Jest to bardzo dobrze znany widok, ponieważ łączy w sobie sumę kroków 2-7. Po kliknięciu komórek zawierających tabelę przestawną widok ten staje się dostępny. (Uwaga: ten widok pozwala dodatkowo dostosować preferencje tabeli przestawnej, ale w naszym przykładzie przejdziemy do ustawień domyślnych).
  • Domyślnie program Excel renderuje wykres kolumnowy, podobnie jak to, co wyraźnie utworzyliśmy w kroku 7. Możesz to łatwo zmienić za pomocą przycisku „Zmień typ wykresu” na karcie Projekt (jak zaznaczono na powyższym obrazku).

W kolejnych krokach dostosujemy tę tabelę przestawną w programie Excel, aby renderować końcowy wykres danych, którego pragniemy. jot

Krok 9: Sparametryzuj wykres przestawny

Ok, więc szybko podsumujemy kroki zastosowane na wcześniejszym wykresie do naszego nowo utworzonego wykresu przestawnego.

  • Kliknij komórki zawierające tabelę przestawną, aby uzyskać dostęp do widoku „Lista pól tabeli przestawnej” po prawej stronie.
  • Sprawdź kolejno dziennik, miesiąc i sprzedaż. Spowoduje to wyświetlenie danych sprzedaży według dzienników w podziale na miesiące.
  • Zachowaj pole wartości do domyślnego ustawienia: Suma SPRZEDAŻY.

A wykres przestawny jest teraz gotowy!

Krok 10: Dostosuj wykres przestawny w programie Excel

W tym ostatnim kroku zastosujemy 3 dostosowania do naszej utworzonej tabeli przestawnej w programie Excel, dzięki czemu zrozumiesz zakres zmian, które możesz wprowadzić jednym kliknięciem myszy.

Dostosuj obszar wykresu:

  • Domyślny wykres przestawny jest renderowany z niebieskimi liniami kolumn. Ale co, jeśli chcesz bardziej dramatycznego pokazu? Jest to możliwe przy użyciu różnych „stylów wykresów” dostępnych na wstążce „Narzędzia wykresów przestawnych”.
  • Poznaj style wykresów dostępne w systemie i wybierz opcję ze schematem kolorów i wyświetlaczem, które odpowiadają Twoim preferencjom.
  • I…. wyświetlacz wykresu przestawnego zmienia się natychmiast, zgodnie z nowym kolorem, kolorowy i efektowny! jot

Dostosuj typ wykresu - 1:

Domyślny wykres kolumnowy spełnia większość potrzeb wykresu. Ale co jeśli twoje dane nie najlepiej pasują do tego typu wykresu? Na przykład nasze dane przewidują sprzedaż dla 8 czasopism w ciągu 3 miesięcy, łącznie 24 słupki kolumn. Być może jest to bardziej czytelne, gdy są wyświetlane jako poziome linie zamiast kolumn? Możesz to przetestować jednym kliknięciem myszy, jak pokazano poniżej.

  • Kliknij „Zmień typ wykresu”, znajdujący się najdalej z lewej strony przycisk dostępny w widoku „PivotChart Tools”.
  • Wyświetlone pole listy pokazuje typy wykresów obsługiwane w twojej wersji Microsoft Excel. Niezależnie od wybranego typu wykresu bieżące ustawienia będą kontynuowane (takie jak pola danych wejściowych, pole wartości itp.)
  • Wybierz typ wykresu „Słupkowy” i kliknij OK. Zmienione wyświetlanie wykresu pokazano poniżej.

Jak widać, jest to trochę bardziej czytelne. jot

Dostosuj typ wykresu - 2:

W naszym ostatnim przykładzie rozważmy, czy chcesz zbadać całkowity udział w rynku każdej publikacji. Nie obchodzi Cię już podział według miesiąca, a jedynie łączna sprzedaż.

  • Pierwszym krokiem jest odznaczenie wszystkich pól kolumn wykresu i ponowne wybranie tylko 2 kolumn w kolejności: 1) Dziennik i 2) Sprzedaż. Wynikowa tabela wygląda następująco.

  • Następnie wykresy słupkowe lub kolumnowe nie są najlepiej dostosowane do tych danych. Więc kliknij przycisk polecenia „Zmień typ wykresu”, przewiń wyświetlane pole listy i wybierz wykres kołowy. Wynikowa tabela przestawna w programie Excel wygląda następująco:

Jak widać, jest to dokładniejsza reprezentacja danych niż wykresy słupkowe i liniowe. Możesz także zapoznać się z poleceniami „Układy wykresów” i „Style wykresów”, aby zmienić sposób wyświetlania zgodnie z własnymi wymaganiami.

Nota końcowa…

Wykres przestawny w programie Excel jest zasadniczo każdym wykresem renderowanym z tabeli przestawnej, ze zdefiniowanymi kolumnami pól i wartościami. W tym poście nauczyłeś się drobniejszych niuansów tego wykresu, więc bez względu na wybrany typ wykresu możesz skutecznie renderować wykres, aby pasował do twoich danych.

Poszukaj dalszych postów na temat różnych typów wykresów dostępnych w programie Excel, aby uzyskać zaawansowaną wiedzę.

Polecany artykuł

Jest to przewodnik po wykresie przestawnym w programie Excel, który pozostaje stałym faworytem wśród studentów, użytkowników domowych i profesjonalistów biznesowych. Są to następujące linki zewnętrzne związane z wykresem przestawnym w programie Excel.

  1. 8 niesamowitych i przydatnych funkcji skoroszytu programu Excel 2016
  2. SPSS vs EXCEL
  3. Excel vs Tableau
  4. 25 najlepszych przydatnych zaawansowanych formuł i funkcji programu Excel

Kategoria:

Porady Excel