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.
Przejdźmy zatem do kroków potrzebnych do stworzenia dynamicznego i dwukolorowego wykresu Pareto.
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:
- "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.
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.
Efekt dodania kolejnej serii danych powinien wyglądać następująco.
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.
Ten sam proces powtarzamy dla "małego obszaru" danych, który zabarwi zaznaczoną część naszego wykresu. Efekt powinien wyglądać jak poniżej.
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ą.
Wykres powinien wyglądać jak poniżej.
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.
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).
Po kilku zabiegach kosmetycznych z ukrywaniem znaczników i formatowaniem obszaru wykresu, gotowy wykres Pareto prezentuje się jak poniżej.
2 thoughts on “Dynamiczny wykres Pareto w Excelu”
Bardzo ciekawa prezentacja. Profesjonalna i ciekawie napisana.
Dziękuję.