Wykres Pareto w Excelu

Dynamiczny wykres Pareto w Excelu

Posted on Posted in wykresy

Wykres Pareto w Excelu to kolejny, z wykresów, który może znacząco poprawić czytelność prezentowanych przez nas danych. Zazwyczaj wykonuje się go, za pomocą wykresu słupkowego i liniowego. W dzisiejszym wpisie, chciałbym podzielić się z Wami sposobem na stworzenie dynamicznego i dwu kolorowego wykresu Pareto, jakiego być może jeszcze nie widzieliście. Inspiracją do zbudowania takiego wykresu był wpis Andy'ego Kriebela, który wykonał analogiczny wykres w programie Tableau. Gotowy plik z wykresem do pobrania tutaj.

 

Teoria

Nazwa wykresu pochodzi od nazwiska włoskiego ekonomisty Vilfredo Pareto (1848-1923), który odkrył ciekawą zależność zwaną później zasadą 20/80.  Otóż, w 1887 roku we Włoszech zaobserwował on, że  80% bogactwa całego kraju było własnością 20% ludności. Zasada zauważona przez Pareto, zaczęła być później dostrzegana w innych dziedzinach praktyki gospodarczej. 54 lata później, niejaki Joseph Juran badając stan "jakości" ponownie doszedł do wniosku, że 80% problemów jest powodowanych przez 20% przyczyn, potwierdzając tym samym tezę swojego poprzednika. W praktyce sama zasada 20/80 nie zawsze rozkłada się tak równomiernie. Niemniej jednak, jej zastosowanie może przydać się w kilku aspektach działalności przedsiębiorstwa np. w celu wyeliminowania najczęściej występujących przyczyny niezadowolenia z usług firmy.

 

Jak już wcześniej wspominałem wykres Pareto zazwyczaj składa się dwóch wykresów: kolumnowego, czasami w formie histogramu oraz wykresu liniowego. Na wykresie kolumnowym, przedstawiona jest w kolejności malejącej, liczba wystąpień danego zjawiska, natomiast na wykresie liniowym, skumulowany udział procentowy każdego z nich.

 

Wykres Pareto w Excelu

 

Przejdźmy zatem do kroków potrzebnych do stworzenia dynamicznego i dwukolorowego wykresu Pareto.

 

Zapisz

Zapisz

Zapisz

Budowa wykresu

W celu zbudowania wykresu, posłużymy się dość obszernym zakresem danych zaczerpniętym ze strony CNBC. Artykuł zamieszczony na stronie serwisu, porusza temat transferu zysów i majątku zagranicę przez największe amerykańskie spółki, celem uniknięcia płacenia podatków w USA. Dane jakie będą nam potrzebne do zbudowania wykresu, to nazwa spółki oraz wartość transferowanej kwoty offshore. Na bazie tych dwóch kolumn, utworzymy pozostałe 4 kolumny, pomocne przy budowie wykresu:

 

Tabela z danymi

 

-  "Skum. wartość offshore"  - wartości z kolumny "Środki offshore" sumowane narastająco

-  "Skum. % offshore"(Y) - % udział każdej z wartości z kolumny "Skum. wartość offshore" w łącznej sumie wartości "Skum. wartość offshore"

- "Pomoc. Skum. % offshore " - to kolumna za pomocą, której zostanie zbudowany mniejszy z kolorowych obszarów

- "Ile spółek"(X) - kolumna numerująca poszczególne spółki

Za pomocą tak przygotowanej tabeli tworzymy pierwszą część wykresu, dzięki której uzyskamy właściwą oś poziomą X. W tym celu wstawiamy wykres punktowy z prostymi liniami i jako jego dane podajemy wartości z kolumn X i Y.

 

Wykres punktowy z prostymi liniami

 

W celu uzyskania efektu wypełnienia pod wykresem, będziemy musieli zbudować jeszcze dwie tabele. Pomysł na to jak to zrobić, przedstawił już kiedyś Jon Peltier w jednym ze swoich wpisów na blogu. Metoda uzyskania efektu wypełnienia pod wykresem, zakłada przeskalowanie wartości z kolumny (X) w skali od 0-1000. W tym miejscu odstąpię od szczegółowego tłumaczenia wszystkich kroków związanych z budową tabeli, gdyż na prostym przykładzie zostało to już wyjaśnione w artykule Jon'a. Przejdźmy zatem dalszej części budowy wykresu. W kolejnym kroku zaznaczamy dane z kolumny "obszar duży", klikamy na utworzony wykres i za pomocą przycisku Wklej-->wklej specjalnie wklejamy nasze dane do wykresu. Ważne jest, aby parametry wklejenia danych był takie same jak poniżej.

 

Pierwsza część wykresu

 

Efekt dodania kolejnej serii danych powinien wyglądać następująco.

 

Druga seria danych na wykresie

 

Klikamy na tak przygotowany wykres i za pomocą ikonki z karty Projektowanie -->Typ zmieniamy rodzaj czerwonego wykresu na wykres warstwowy przedstawiony na pomocniczej osi danych.

 

Zmiana typu i osi wykresu

 

Ten sam proces powtarzamy dla "małego obszaru" danych, który zabarwi zaznaczoną część naszego wykresu. Efekt powinien wyglądać jak poniżej.

 

Druga seria danych

 

Wykres wygląda już prawie tak jak tego chcieliśmy. W kolejnym kroku dodajemy do wykresu pomocniczą poziomą oś danych. Następnie zmieniamy sposób jej wyświetlania na oś daty i usuwamy pomocniczą oś pionową a w dalszej kolejności pomocniczą oś poziomą.

 

Pomocnicza oś danych

 

Wykres powinien wyglądać jak poniżej.

 

Uzupełnione tło pod wykresem punktowym

 

Jak widać na pierwszy rzut oka, obszar pod linią nie jest jeszcze idealnie dopasowany. Aby to zmienić należy ustawić skalę osi poziomej na przedział od 0-286 (łączny przedział analizowanych wierszy). Rozwiązanie to nie jest do końca idealne, gdyż nie pozwala na wyświetlenie szerszej skali legendy. W celu określenia szerszego zakresu skali, należy dodać do tabel, na których zbudowany jest wykres, pomocnicze wiersze w ilości adekwatnej do zamierzonej skali.

 

Zakres osi poziomej

 

Wykres jest już prawie gotowy. Na tym etapie dodamy jeszcze do niego punkt na niebieskiej linii, z którego poprowadzimy pionowe i poziome słupki błędu oraz dwa dodatkowe punkty, które posłużą nam do wyświetlenia współrzędnych wybranego punktu (etykieta pionowa i pozioma).

 

Dodatkowe elementy wykresu

 

Po kilku zabiegach kosmetycznych z ukrywaniem znaczników i formatowaniem obszaru wykresu, gotowy wykres Pareto prezentuje się jak poniżej.

 

Dynamiczny wykres Pareto w Excelu

 

 

Zapisz

Zapisz

2 thoughts on “Dynamiczny wykres Pareto w Excelu

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *