Ten praktyczny samouczek wyjaśnia, czym jest wykres przestawny oraz jak go tworzyć i dostosowywać. Zobaczymy również różnicę między wykresem przestawnym a tabelą:
Wykresy są uważane za jeden z najlepszych sposobów prezentacji raportu. Pomagają nam zrozumieć i analizować dane w prostszy sposób. Wykresy przestawne w Excelu dają nam wizualną reprezentację danych na różne sposoby.
W tym samouczku poznamy wszystkie szczegóły potrzebne do pracy z wykresami przestawnymi w programie Excel. Tworzenie wykresów różnych typów, formatowanie ich układu, dodawanie filtrów, dodawanie niestandardowych formuł i używanie formatu jednego wykresu do innego wykresu należącego do różnych tabel przestawnych.
Co to jest wykres przestawny w programie Excel?
Wykres przestawny w programie Excel to wizualna reprezentacja danych. Daje on duży obraz surowych danych. Pozwala analizować dane za pomocą różnych typów wykresów i układów. Jest uważany za najlepszy wykres podczas prezentacji biznesowej, która obejmuje ogromne dane.
Wykres przestawny a tabela
Tabela przestawna zapewnia nam sposób na podsumowanie dużych danych w macierzy przypominającej siatkę. Możesz wybrać pola, których chcesz użyć w tabeli dla wierszy i kolumn. Wykres przestawny zapewnia nam graficzną reprezentację tabeli przestawnej. Możesz wybierać spośród wielu układów i typów wykresów.
Ten wykres również podsumowuje dane. Możesz utworzyć zarówno tabelę przestawną, jak i wykres dla źródła danych i obsługiwać je jednocześnie. Oznacza to, że zmiany wprowadzone w tabeli przestawnej zostaną odzwierciedlone na wykresie i odwrotnie.
Źródło danych
Poniżej znajduje się przykładowe źródło danych, które zostanie wykorzystane w tym samouczku. Kliknij na link, aby pobrać Sample_Data Pivot Chart.
| Identyfikator zamówienia | Data zamówienia | Nazwa produktu | Region | Miasto | Ilość | Cena całkowita |
|---|---|---|---|---|---|---|
| 1 | 03-01-2020 | Zwykłe ciasteczka | Północ | Nowy Jork | 33 | 444.66 |
| 2 | 04-02-2012 | Cukrowe ciasteczka | Południe | Lima | 432 | 346.33 |
| 3 | 05-04-2018 | Wafle | Wschód | Boston | 33 | 32.54 |
| 4 | 06-05-2019 | Czekolada | Zachód | Oak Land | 245 | 543.43 |
| 5 | 07-07-2020 | Lody | Północ | Chicago | 324 | 223.56 |
| 7 | 09-09-2020 | Zwykłe ciasteczka | Wschód | Waszyngton | 32 | 34.4 |
| 8 | 10-11-2020 | Cukrowe ciasteczka | Zachód | Seattle | 12 | 56.54 |
| 9 | 11-12-2017 | Wafle | Północ | Toronto | 323 | 878.54 |
| 10 | 12-14-2020 | Czekolada | Południe | Lima | 232 | 864.74 |
| 11 | 01-15-2020 | Lody | Wschód | Boston | 445 | 457.54 |
| 13 | 03-18-2018 | Słone ciasteczka | Północ | Nowy Jork | 5454 | 34546 |
| 14 | 04-18-2017 | Ciasteczka serowe | Południe | Lima | 5653 | 3456.34 |
| 15 | 05-19-2016 | Słone ciasteczka | Wschód | Waszyngton | 4 | 74.4 |
| 16 | 06-20-2015 | Ciasteczka serowe | Zachód | Oak Land | 545 | 876.67 |
Tworzenie wykresu przestawnego
Istnieją 2 sposoby na utworzenie wykresu przestawnego w programie Excel.
#1) Utwórz ze źródła danych
Możemy utworzyć wykres bezpośrednio z arkusza danych bez tabeli przestawnej.
Aby to osiągnąć, wykonaj poniższe kroki.
#1) Wybierz dowolną komórkę w tabeli.
#2) Przejdź do Wstaw -> Wykres przestawny
#3) Możesz utworzyć nowy arkusz lub wskazać zakres tabeli, w którym chcesz umieścić wykres w sekcji Istniejący arkusz.
#4) Kliknij OK
Spowoduje to utworzenie pustego wykresu przestawnego i powiązanej z nim tabeli przestawnej. Możesz dodać żądane pola, aby wygenerować raport i wykres.
#2) Utwórz z tabeli przestawnej
Jeśli utworzyłeś już tabelę przestawną, możesz użyć jej do wygenerowania wykresu przestawnego. Utworzyliśmy przykładową tabelę przestawną, jak pokazano poniżej.
Aby utworzyć wykres.
#1) Wybierz dowolną komórkę w tabeli przestawnej.
#2) Przejdź do Wstaw-> Wykres przestawny
#3) Zostanie wyświetlona lista dostępnych wykresów, wybierz żądany wykres.
#4) Kliknij przycisk OK.
Spowoduje to wygenerowanie wykresu z danymi pobranymi z tabeli przestawnej. Przykład wykresu przestawnego pokazano poniżej.
Uwaga: Alternatywnie można użyć skrótu klawiszowego F11. Kliknij tabelę przestawną i naciśnij klawisz F11 na klawiaturze.
Dostosowywanie wykresu
Wykres można dostosować za pomocą przycisku + i ikony farby znajdującej się po prawej stronie wykresu.
Przycisk + - pomaga dodawać lub usuwać elementy wykresu, takie jak tytuły, linie siatki, legendy itp. oraz decydować o ich położeniu.
Możesz dodać tytuł wykresu, wspomnieć o tytułach osi itp. Dodaliśmy tytuł wykresu i tytuł osi jako przykład.
Styl wykresu - można zmienić styl i kolor wykresu, klikając ikonę pędzla.
W sekcji kolorów można również zmienić kolor wykresu.
Zalecane wykresy
Excel udostępnia nam rekomendowane wykresy przestawne, które pozwalają szybko wybrać typ wykresu przestawnego spełniający wymagania biznesowe.
#1) Wybierz tabelę źródła danych.
#2) Przejdź do Wstaw -> Zalecane wykresy .
#3) Kliknij opcję Polecane wykresy.
#4) Kliknij odpowiedni wykres.
#5) Kliknij OK
Wynikowa tabela przestawna i wykres zostaną utworzone w nowym arkuszu i można je dalej dostosowywać zgodnie z potrzebami.
Pola wykresu przestawnego
Ma 4 pola, jak pokazano poniżej.
1. Filtry: Pola w tej sekcji dają nam możliwość dodawania filtrów raportów.
2. Legendy (Seria) Pola w tej sekcji reprezentują nagłówki kolumn w tabeli przestawnej.
3. Oś (Kategorie): Pola te są wyświetlane na pasku osi na wykresie.
4. Wartości: Służy do wyświetlania podsumowanych wartości liczbowych.
Narzędzia wykresów przestawnych
Analizuj: Dostępne są różne opcje, które sprawiają, że wykres jest bardziej przyjazny dla użytkownika.
Nazwa wykresu: Jest to nazwa wykresu używana podczas pisania kodu VBA, a także w panelu wyboru. Jest dostępna w programie Excel 2010 i nowszych.
Opcje: Zostanie wyświetlone okno dialogowe PivotTable Options, w którym można ustawić układ & Format, ustawić wyświetlanie/ukrywanie sumy całkowitej, ustawić opcje sortowania, opcje wyświetlania itp.
Aktywne pole: Możesz zmienić nazwę kolumny w tabeli, na przykład , Suma całkowita do kwoty końcowej itp. i to samo zostanie zaktualizowane w tabeli i na wykresie.
Rozwiń pole: Służy do automatycznego rozwijania wszystkich wartości.
Jeśli masz wiele pól, takich jak Lata, Kwartały i Daty, zamiast rozwijać je pojedynczo, możesz kliknąć Rozwiń pole.
Zwiń pole: Jest to przeciwieństwo opcji Rozwiń pole. Spowoduje to zwinięcie rozwiniętych pól i wyświetlenie zwartego wykresu.
Rozwiń przykład
Przykład upadku
Uwaga: Przypuśćmy, że w sekcji Rows znajduje się tylko jedno pole, wówczas kliknięcie przycisku Expand Field spowoduje wyświetlenie okna dialogowego ze wszystkimi polami, w którym można wybrać żądane pole. Wybrane pole zostanie dodane do sekcji Rows, a wykres zostanie automatycznie zaktualizowany.
Insert Slicer
Do wykresu można wstawić fragmentator, podobnie jak w przypadku tabeli przestawnej.
Aby zintegrować slicer z wykresem, wykonaj poniższe kroki.
- Kliknij wykres przestawny.
- Przejdź do Zakładka Analyze -> Insert Slicer .
- W oknie dialogowym Wybierz pola należy utworzyć plasterki.
- Kliknij OK
Spowoduje to wstawienie okna slicera, jak pokazano poniżej. Widzieliśmy, jak używać slicera w naszym poprzednim samouczku.
Wstaw oś czasu
Oś czasu można wstawić do wykresu podobnie jak tabelę przestawną.
Aby zintegrować Oś czasu z wykresem, wykonaj poniższe kroki.
- Kliknij wykres przestawny.
- Przejdź do Karta Analizuj -> Wstaw oś czasu.
- Wybierz wymagane pole daty.
- Kliknij OK
Spowoduje to wstawienie osi czasu, jak pokazano poniżej. Widzieliśmy, jak korzystać z osi czasu w naszym poprzednim samouczku.
Wynik oparty na osi czasu jest aktualizowany zarówno w tabeli przestawnej, jak i na wykresie.
Połączenie filtra
Możesz połączyć slicer lub oś czasu do wielu wykresów przestawnych. Na przykład, Utworzyliśmy 2 tabele przestawne i 1 slicer. Stosujemy slicer do obu wykresów.
- Kliknij wykres przestawny, z którym slicer nie jest aktualnie połączony.
- Przejdź do Analyze -> Filter Connection
- Wybierz krajalnicę, którą chcesz podłączyć.
- Kliknij przycisk Ok
Teraz możesz obsługiwać oba wykresy za pomocą jednego slicera.
Obliczenia
Jeśli chcesz dodać niestandardowe formuły, możesz to zrobić za pomocą pola obliczeniowego.
Przykład:
#1) Wybierz wykres przestawny, do którego chcesz dodać niestandardowe formuły.
#2) Przejdź do Analizuj -> Pola -> Pozycje -> Zestawy
#3) Wybierz opcję Pola obliczone.
#4) W polu Nazwa wprowadź żądaną nazwę.
#5) Jeśli udzielasz 10% rabatu na łączną sumę, możesz dodać formułę, jak pokazano poniżej.
#6) Tabela przestawna, pola przestawne i wykres zostaną odpowiednio zaktualizowane.
Odświeżanie
Za każdym razem, gdy zmienisz wartości w źródle danych, kliknij w dowolnym miejscu wykresu przestawnego, a następnie kliknij prawym przyciskiem myszy i wybierz opcję Odśwież lub przejdź do opcji Analizuj -> Odśwież. Odświeżenie tabeli przestawnej spowoduje również odświeżenie wykresu.
Zmiana źródła danych
Za każdym razem, gdy dodasz więcej wierszy do źródła danych, wykres nie uwzględni dodanych wierszy, ponieważ zdefiniowaliśmy zakres podczas tworzenia wykresu.
Aby uwzględnić nowe wiersze:
- Kliknij w dowolnym miejscu wykresu przestawnego.
- Przejdź do Analizuj -> Zmień źródło danych
- Pojawi się okno dialogowe Change PivotTable Data Source, w którym można wprowadzić nowy zakres danych.
- Kliknij przycisk Ok
Upewnij się, że wykonałeś powyższe kroki dla wszystkich wykresów indywidualnie.
Wyczyść
Za pomocą opcji Wyczyść można wyczyścić cały wykres przestawny. Będzie to pusty wykres i tabela.
- Kliknij wykres przestawny
- Analizuj -> Wyczyść -> Wyczyść wszystko
Można również wyczyścić wszystkie zastosowane filtry poprzez Analizuj -> Wyczyść-> Wyczyść filtry
Wykres ruchu
Po utworzeniu wykresu można przenieść go do wybranej lokalizacji.
Wykonaj poniższe czynności:
- Kliknij wykres przestawny.
- Przejdź do Analyze -> Move Chart
- Wybierz żądaną opcję z okna dialogowego:
- Nowy arkusz: Arkusz zostanie automatycznie utworzony, a wykres zostanie wyświetlony.
- Obiekt w: Możesz wybrać jeden z dostępnych arkuszy, a wykres zostanie przeniesiony do wybranego arkusza.
Lista terenowa: Panel Pola wykresu przestawnego można wyświetlać/ukrywać.
Przyciski terenowe: Na wykresie można wyświetlać/ukrywać pole legendy, pole osi, pole wartości, filtr raportu itp.
Projekt
W tej zakładce dostępnych jest kilka opcji projektowania wykresu.
Dodaj element wykresu: Daje nam to te same opcje, które otrzymaliśmy po kliknięciu przycisku + obok wykresu przestawnego. Pomagają nam one dodawać elementy do wykresu, takie jak tytuł, pasek błędów itp.
Szybki układ: Można zmienić domyślny układ i wybrać jeden z dostępnych predefiniowanych układów. Na przykład, przenieśliśmy układ regionu na górę zamiast na prawą stronę.
Zmiana kolorów: Wybierz różne kolory dla wykresu.
Styl wykresu: Wybierz styl wykresu spośród dostępnych wykresów.
Przełącz wiersz/kolumnę: Możesz łatwo przełączać wiersze i kolumny za pomocą jednego kliknięcia, a tabela przestawna i wykres zostaną automatycznie zaktualizowane.
Przed przełączeniem wiersza/kolumny
Po przełączeniu wiersza/kolumny
Wybierz Dane: Załóżmy, że poświęciłeś dużo czasu na sformatowanie wykresu przestawnego zgodnie ze standardami firmy i wszystkie wykresy powinny mieć ten sam format. Wtedy ta opcja jest przydatna. Nie można bezpośrednio skopiować wykresu przestawnego i zmienić źródła danych. Należy wykonać kilka kroków.
#1) Wybierz żądany wykres przestawny i skopiuj obszar wykresu.
#2) Otwórz nowy skoroszyt. Plik -> Nowy skoroszyt
#3) Po wklejeniu skopiowanego wykresu można zauważyć, że na pasku menu widnieje napis Narzędzia wykresów, a nie Narzędzia wykresów przestawnych.
#4) Teraz wybierz obszar wykresu i kliknij opcję Wytnij.
#5) Przejdź do skoroszytu, w którym chcesz użyć tego wykresu.
#6) Uwaga: Tabela przestawna powinna być już utworzona.
#7) Wklej wykres z kroku 4.
#8) Przejdź do sekcji Projektowanie w obszarze Narzędzia wykresów i kliknij kartę Wybierz dane.
#9) Kliknij dowolną komórkę w tabeli przestawnej.
Wykres przestawny zostanie utworzony z danymi obecnymi w nowej tabeli przestawnej, ale format pozostanie taki sam jak poprzednio. Możesz zmodyfikować oś i legendę zgodnie z potrzebami nowej tabeli.
Wynikowy wykres dla nowej tabeli przestawnej jest pokazany poniżej.
Zmień typ wykresu: Domyślny typ wykresu kolumnowego można zmienić na żądany typ, jak pokazano poniżej.
Wykres zostanie zaktualizowany automatycznie na podstawie dokonanego wyboru.
Wykres kołowy
Wykres słupkowy
Format
Są one zasadniczo używane do niestandardowego formatowania tekstu znajdującego się wewnątrz wykresu.
Aktualny wybór: Spowoduje to wyświetlenie wszystkich elementów obecnych w tabeli i wybranie tego, w którym chcesz zmienić styl formatu. Na przykład, Wybierzemy Tytuł wykresu i zmienimy jego styl.
#1) Wybierz Tytuł wykresu z listy rozwijanej.
#2) Kliknij opcję Wybór formatu.
#3) Formatuj tytuł wykresu otworzy się w prawym panelu.
#4) Wybierz kolor, styl, obramowanie itp. według własnego uznania.
Po kilku podstawowych formatowaniach tytuł wykresu będzie wyglądał jak poniżej.
Resetuj, aby dopasować styl: Spowoduje to zresetowanie wszystkich zmian i nadanie domyślnego stylu.
Kształty wstawek: Możesz wstawiać kształty, takie jak linie, strzałki, a także pole tekstowe dla lepszego wyjaśnienia.
Styl kształtu: Możesz wybrać różne style dla obszaru roboczego. Wybierz obszar, dla którego chcesz zmienić styl i kliknij na styl.
Po zastosowaniu stylów do całego wykresu, kolumna i wiersze są pokazane poniżej.
Zorganizuj: Jeśli istnieje wiele wykresów przestawnych i nakładają się one na siebie w tych opcjach.
Bring Forward
- Wybierz wykres, który chcesz wyświetlić.
- Kliknij opcję Przenieś do przodu, aby przenieść wykres o jeden krok do przodu.
Bring To Front: Ta opcja spowoduje umieszczenie wykresu powyżej wszystkich innych wykresów.
Wyślij wstecz
- Wybierz wykres, który chcesz odesłać.
- Kliknij opcję Wyślij wstecz, aby wysłać wykres o jeden poziom wstecz.
Wyślij do tyłu: Służy do wysyłania wybranego wykresu z powrotem do wszystkich innych wykresów.
Okienko wyboru
Możesz zdecydować o widoczności wykresu za pomocą panelu wyboru. Na tej stronie wyświetlane są wszystkie dostępne wykresy i slicery i możesz kliknąć ikonę oka, aby zdecydować, czy dany element powinien być widoczny w arkuszu, czy nie.
Rozmiar: Służy do dostosowywania wysokości wykresu przestawnego, szerokości, wysokości skali, szerokości skali itp.
Często zadawane pytania
P #1) Jak utworzyć wykres przestawny w programie Excel?
Odpowiedź: Istnieją 2 sposoby tworzenia wykresów przestawnych.
#1) Utwórz ze źródła danych
- Wybierz dowolną komórkę w tabeli źródła danych.
- Przejdź do Wstaw -> Wykres przestawny
- Wybierz zakres.
Spowoduje to utworzenie pustej tabeli przestawnej i wykresu przestawnego.
#2) Utwórz z tabeli przestawnej
Jeśli masz już tabelę przestawną:
- Wybierz dowolną komórkę w tabeli przestawnej.
- Przejdź do Wstaw -> Wykres przestawny
- Zostanie wyświetlona lista dostępnych wykresów, wybierz żądany wykres.
Spowoduje to utworzenie wykresu z danymi odpowiednimi dla tabeli przestawnej.
P #2) Dlaczego używamy wykresu przestawnego w Excelu?
Odpowiedź:
Korzystanie z wykresów przestawnych ma wiele zalet:
- Zapewnia skuteczny i łatwy sposób przedstawiania danych w sposób graficzny.
- Dane można łatwo podsumować, przeciągając żądane pola do dowolnej z 4 dostępnych sekcji tabeli.
- Zapewnia skuteczny sposób zmiany surowych danych w zorganizowany format poprzez manipulowanie nimi za pomocą łatwego filtrowania, wyrównywania, dostosowywania, obliczeń itp.
P #3) Jak sformatować wykres przestawny?
Odpowiedź: Wykres można formatować przy użyciu różnych opcji dostępnych w sekcji Narzędzia wykresu przestawnego. Umożliwia ona dodawanie nowych pól, zmianę koloru, czcionki, tła itp. w celu nadania wykresowi bardziej interaktywnego i reprezentacyjnego wyglądu. Kliknij w dowolnym miejscu wykresu przestawnego, aby otworzyć sekcję Narzędzia.
P #4) Czy mogę dodać slicer do wykresów przestawnych?
Odpowiedź: Tak, do wykresów przestawnych można dodawać plasterki i osie czasu. Pomoże nam to w jednoczesnym filtrowaniu zarówno wykresu, jak i powiązanej tabeli przestawnej.
- Kliknij wykres przestawny.
- Przejdź do Zakładka Analyze -> Insert Slicer .
- W oknie dialogowym Wybierz pola chcesz utworzyć slicery.
- Kliknij OK
Następnie można dodać połączenie Filtr, aby połączyć jeden slicer z wieloma wykresami.
Wnioski
W tym samouczku dowiedzieliśmy się o wykresach przestawnych w programie Excel. Jest to wizualna reprezentacja tabeli przestawnej lub źródła danych. Pomaga nam przeglądać dane podsumowujące w formacie graficznym z różnymi typami wykresów.
Dostępnych jest wiele opcji filtrowania, formatowania, dostosowywania wykresów i dodawania różnych układów według własnego uznania. Wykres przestawny w programie Excel jest przydatny, gdy mamy do czynienia z ogromną ilością danych. Jest bardzo przydatny podczas prezentacji biznesowej z filtrowaniem jednym kliknięciem, filtrowaniem według czasu, niestandardowymi obliczeniami itp.