Jak stworzyć wykres Pareto w Excelu?

Z lekcji dowiesz się:

• Czym różni się wykres Pareto w Excelu 2010 i 2016?

• Do jakich działań warto użyć tego wykresu?

• Dlaczego w Excelu 2010 mamy większą kontrolę nad diagramem?

Excel 2016

Widoczny na rys. 1 wykres Pareto przedstawia podsumowanie sprzedaży - od województwa, gdzie sprzedaż była najwyższa do tego z najniższą sprzedażą.

Rys. 1. Wykres Pareto

Ponadto na wykresie mamy linię obrazującą procentowy udział sprzedaży danego województwa względem całej sprzedaży. Od Excela 2016 taki wykres można stworzyć w bardzo łatwy sposób. Przede wszystkim musimy mieć dane, na których podstawie ma powstać wykres. Przykładowe dane zostały przedstawione na rys. 2.

Rys. 2. Przykładowe dane sprzedażowe

W celu stworzenia wykresu Pareto musimy zaznaczyć dane, następnie z karty Wstawianie (punkt 1 na rys. 3) rozwijamy ikonkę wykresu Histogram (punkt 2) i wybieramy wykres Pareto (punkt 3).

Rys. 3. Ścieżka dostępu do wykresu Pareto

Po kliknięciu powyższego polecenia Excel automatycznie wstawi wykres przedstawiony na rys. 4.

Rys. 4. Automatycznie stworzony wykres

Dane na wykresie zostają automatycznie ułożone od największej sprzedaży do najmniejszej, nie musimy mieć nawet posortowanych danych wejściowych w tabeli. Przez to, że wykres tworzy się automatycznie, nie mamy na niego dużego wpływu (na jego wygląd). Nie można więc np. przypisać wartości na wykresie (Tytuł wykresu) do jakiejś komórki w danych. Po wpisaniu znaku = (równa się), Excel nie pozwala wpisać odwołania do komórki, możemy tylko ręcznie wpisać nazwę.

Excel 2010

Z tego względu pokażemy jak wykonać taki wykres w Excelu 2010, na który będziemy mieli dużo większy wpływ. Przykładowe dane dla tego wykresu zostały przedstawione na rys. 5.

Rys. 5. Przykładowe dane (Excel 2010)

Istotne jest, że w Excelu 2010 chcąc stworzyć wykres Pareto musimy mieć posortowane dane od największej wartości do najmniejszej oraz musimy sami obliczyć skumulowaną wartość sprzedaży, pisząc odpowiednią formułę podsumowującą dane (rys. 6).

Rys. 6. Obliczenie skumulowanej wartości sprzedaży

Tak przygotowane dane możemy zaznaczyć i rozwinąć polecenie Kolumnowy (punkt 2 na rys. 7) z karty Wstawianie (punkt 1), a następnie wybrać ikonkę interesującego nas typu wykresu kolumnowego (punkt 3).

Rys. 7. Ścieżka dostępu do wykresu kolumnowego

Otrzymamy wykres złożony z 2 elementów, czyli Obrotu i % skumulowanego. Niestety tego drugiego elementu nie widać i ciężko byłoby go zaznaczyć ręcznie, dlatego na karcie Formatowanie mamy w lewym rogu listę elementów zaznaczonego wykresu, z której możemy wybrać odpowiedni element (rys. 8).

Rys. 8. Lista elementów wykresu

Z powyższej listy możemy wybrać serię danych „% skumulowany”. Kiedy mamy zaznaczoną tę konkretną serię to w karcie Wstawianie (punkt 1 na rys. 9) możemy zmienić jej typ wykresu na liniowy, czyli rozwijamy polecenie Liniowy (punkt 2), a następnie wybieramy typ Liniowy ze znacznikami (punkt 3). W Excelu 2010 musimy zmieniać w każdej serii typ wykresu, natomiast w Excelu 2013 można wybrać typ wykresu kombi, gdzie dla każdej serii danych możemy ustawić inny typ wykresu.

Rys. 9. Zmiana typu wykresu na liniowy ze znacznikami

Otrzymamy wykres liniowy ze znacznikami dla serii danych % skumulowany przedstawiony na rys. 10.

Rys. nr 10. Wykres liniowy ze znacznikami dla serii danych % skumulowany

Możemy zauważyć, że teraz serię tą widać, ale jest ona na poziomie zera, ponieważ procent w porównaniu do milionów zł obrotu jest znikomy. Teraz wystarczy przy zaznaczonej serii skorzystać ze skrótu klawiszowego Ctrl+1, aby otworzyło się okno Formatowania serii danych. W oknie tym zaznaczamy w polu Kreśl serię na checkbox przy opcji Oś pomocnicza (rys. 11).

Rys. 11. Uruchomienie osi pomocniczej dla serii danych % skumulowany

Ponadto możemy usunąć legendę, w tym celu zaznaczamy ją i usuwamy za pomocą klawisza Delete. Otrzymamy wykres przedstawiony na rys. 12.

Rys. 12. Wykres po dodaniu osi pomocniczej i usunięciu legendy

Teraz możemy zająć się dopracowaniem wyglądu wykresu, aby miał wymagane elementy. Pierwszym elementem, jaki powinniśmy dodać jest Tytuł wykresu. W tym celu rozwijamy polecenie Tytuł wykresu (punkt 2 na rys. 13) z karty Układ (punkt 1), a następnie wybieramy polecenie Nad wykresem (punkt 3).

Rys. 13. Dodawanie tytułu wykresu

Pojawia się pole tekstowe Tytuł wykresu, w którym można zrobić odwołanie do konkretnej komórki. Zaznaczamy pole Tytuł wykresu, a następnie w pasku formuły wpisujemy znak = a, następnie klikamy na komórkę z tytułem danych (=Pareto_k!$A$1). Po zatwierdzeniu tej formuły przyciskiem Enter otrzymamy tytuł wykresu przedstawiony na rys. 14.

Rys. 14. Wykres po wstawieniu tytułu

Tytuł wykresu jest połączony z komórką z tytułem danych, dzięki temu, kiedy zmienimy tytuł danych, automatycznie zmieni się tytuł.

Zajmiemy się teraz modyfikacją osi pomocniczej.

Zaznaczamy tę oś, następnie otwieramy okno Formatowania osi za pomocą skrótu klawiszowego Ctrl+1. Wprowadzimy zmiany w zakładce Opcje osi. Musimy zmienić wartość Maksimum z automatycznej na Stałą, gdzie wpisujemy wartość 1. Następnie ustawiamy jednostkę przejścia na 0,2 (czyli 20%) – rys. 15.

Rys. 15. Opcje osi

Teraz przechodzimy na zakładkę Liczby, gdzie w polu Miejsca dziesiętne ustawiamy wartość zero, ponieważ miejsce po przecinku na wykresie jest nam niepotrzebne (rys. 16).

Rys. 16. Miejsca dziesiętne na osi pomocniczej

Oś pomocnicza po wprowadzonych zmianach została przedstawiona na rys. 17.

Rys. 17. Zmiany osi pomocniczej na wykresie

Na tym etapie zajmiemy się formatowanie drugiej serii danych. Zaznaczamy serię Obrót, następnie w oknie Formatowania serii danych zmieniamy Szerokość przerwy, co sprawi, że nasze kolumny się poszerzą (rys. 18).

Rys. 18. Zmiana szerokości kolumn serii danych Obrót

Następnie zajmiemy się zmianami osi głównej, ponieważ wartości są w milionach i mają dużą ilość zer. W oknie Formatowania osi przechodzimy na zakładkę Liczby, gdzie w polu Kod formatu wpisujemy 0 dwie spacje "M zł". Dwie spacje w kodzie oznaczają dwukrotne podzielenie wartości przez tysiąc. Wpisany kod zatwierdzamy przyciskiem Dodaj (zaznaczone strzałką na rys. 19).

Rys. 19. Zmieniona oś serii danych

Możemy jeszcze dołożyć etykiety do naszych serii danych, aby wyświetlały się dokładniejsze wartości. W tym celu rozwijamy polecenie Etykiety danych (punkt 2 na rys. 21) z karty Układ (punkt 1), następnie wybieramy polecenie Podstawa wewnętrzna (punkt 3).

Rys. 20. Wstawianie etykiet danych na wykresie

Otrzymamy wykres ze wstawionymi etykietami przedstawiony na rys. 21.

Rys. 21. Wykres z etykietami danych

Łatwo zauważyć, że wstawione etykiety są stanowczo za duże, zachodzą na siebie i ciężko je odczytać. Z tego względu musimy je zaznaczyć i w oknie Formatowania etykiet danych przejść na zakładkę Wyrównanie (punkt 1 na rys. 22). Najpierw zmieniamy Kierunek tekstu, czyli wybieramy z listy rozwijanej polecenie Obróć cały tekst o 270 ^o (punkt 2).

Rys. 22. Obrót tekstu etykiety danych o 270^o

Na wykresie znajdują się pełne liczby (z dużą ilością zer). Możemy je - analogicznie jak dla osi X - sformatować walutowo. W tym celu przechodzimy na zakładkę Liczby i w polu Kod formatu wpisujemy kod 0,0 dwie spacje "M zł". Wpisany kod musimy zatwierdzić przyciskiem Dodaj (jak na rys. 19). Otrzymamy wykres ze sformatowanymi etykietami danych przedstawiony na rys. 23.

Rys. 23. Sformatowane etykiety danych

Tym razem w kodzie zostawiliśmy jedno miejsce po przecinku (0,0) i do takich właśnie wartości Excel zaokrąglił etykiety danych.

Podsumowując - wykonaliśmy wykres Pareto w Excelu 2010, dzięki czemu mamy większą kontrolę nad jego elementami. Możemy na wykresie odwoływać się do komórek z danych, formatować osie i serie danych, i dodawać etykiety tych danych.

Dodatkowo możemy również zmienić jednostkę główną na osi X, żeby nie było tyle różnych wartości. W tym calu zaznaczamy oś X i w oknie Formatowania osi w zakładce Opcje osi zmieniamy Jednostkę główną na Stałą i wpisujemy skok o 2,0E7 (rys. 24).

Rys. 24. Ustawienie jednostki głównej na osi X

Otrzymamy końcowy wykres przedstawiony na rys. 25.

Rys. 25. Końcowy wykres

Polecamy także:

• Jak filtrować wybrane kolumny w Excelu?
• Jak tworzyć miniwykresy w komórkach Excela?

Adam Kopeć