Spis treści
Być może słyszałeś o prawie Pareto lub o zasadzie 20/80. Pod koniec XIX wieku włoski socjolog i ekonomista Vilfredo Pareto odkrył, że rozkład bogactwa w społeczeństwie jest nierównomierny i podlega pewnej zależności: wraz ze wzrostem bogactwa liczba osób bogatych maleje wykładniczo ze stałym współczynnikiem ( wśród włoskich gospodarstw domowych 19% dochodów przypadało na 80% rodzin). Później pomysł ten rozwinął w swojej książce Richard Kocha, który zaproponował sformułowanie uniwersalnej „Zasady 20/20” (80% wysiłków daje 20% wyniku). W praktyce prawo to zwykle nie wyraża się w tak pięknych liczbach (czytaj „Długi ogon” Chrisa Andersona), ale wyraźnie pokazuje nierównomierny rozkład zasobów, zysków, kosztów itp.
W analizie biznesowej często konstruuje się wykres Pareto, aby przedstawić tę nierówność. Może służyć do wizualnego pokazania, na przykład, które produkty lub klienci przynoszą największe zyski. Zwykle wygląda to tak:
Jego główne cechy:
- Każda niebieska kolumna histogramu przedstawia zysk produktu w jednostkach bezwzględnych i jest wykreślona wzdłuż lewej osi.
- Pomarańczowy wykres przedstawia skumulowany procent zysków (tj. udział zysków na zasadzie skumulowanej).
- Na granicy warunkowej 80%, dla przejrzystości zwykle rysowana jest pozioma linia progowa. Wszystkie dobra na lewo od punktu przecięcia tej linii z wykresem skumulowanego zysku przynoszą nam 80% pieniędzy, wszystkie dobra na prawo – pozostałe 20%.
Zobaczmy, jak samodzielnie zbudować wykres Pareto w programie Microsoft Excel.
Opcja 1. Prosty wykres Pareto oparty na gotowych danych
Jeśli dane źródłowe dotarły do Ciebie w postaci podobnej tabeli (czyli już w formie gotowej):
… wtedy robimy co następuje.
Posortuj tabelę w porządku malejącym według zysku (tab Dane – Sortowanie) i dodaj kolumnę ze wzorem na obliczenie skumulowanego procentu zysku:
Ta formuła dzieli łączny skumulowany zysk od początku listy do bieżącej pozycji przez łączny zysk dla całej tabeli. Dodajemy również kolumnę ze stałą 80%, aby utworzyć linię przerywaną poziomego progu na przyszłym wykresie:
Wybieramy wszystkie dane i budujemy regularny histogram na zakładce Wstaw – Histogram (Wstaw – Wykres kolumnowy). Powinno to wyglądać mniej więcej tak:
Szeregi procentowe na wynikowym wykresie należy przesłać wzdłuż osi pomocniczej (prawej). Aby to zrobić, musisz zaznaczyć wiersze myszą, ale może to być trudne, ponieważ trudno je zobaczyć na tle dużych kolumn zysków. Lepiej więc skorzystać z rozwijanej listy na karcie, aby podświetlić układ or utworzony:
Następnie kliknij prawym przyciskiem myszy wybrany wiersz i wybierz polecenie Formatuj serie danych i w wyświetlonym oknie wybierz opcję Na osi pomocniczej (oś drugorzędna). W rezultacie nasz diagram będzie wyglądał tak:
W przypadku serii skumulowanego udziału w zyskach i progu należy zmienić typ wykresu z kolumn na linie. Aby to zrobić, kliknij każdy z tych wierszy i wybierz polecenie Zmień typ wykresu serii.
Pozostaje tylko wybrać poziomy wiersz Threshold i sformatować go tak, aby wyglądał jak linia odcięcia, a nie dane (tj. Usuń znaczniki, ustaw linię przerywaną na czerwono itp.). Wszystko to można zrobić, klikając prawym przyciskiem myszy wiersz i wybierając polecenie Formatuj serie danych. Teraz diagram przyjmie swoją ostateczną formę:
Zgodnie z nią możemy wywnioskować, że 80% zysku pochodzi z pierwszych 5 towarów, a wszystkie inne dobra na prawo od ziemniaka stanowią tylko 20% zysku.
W programie Excel 2013 możesz to zrobić jeszcze łatwiej — użyj nowego wbudowanego typu wykresu kombi od razu podczas kreślenia:
Opcja 2: Tabela przestawna i wykres Pareto przestawny
Co zrobić, jeśli nie ma gotowych danych do budowy, a jedynie oryginalne surowe informacje? Załóżmy, że na początku mamy tabelę z takimi danymi sprzedażowymi:
Aby zbudować na nim wykres Pareto i dowiedzieć się, które produkty sprzedają się najlepiej, musisz najpierw przeanalizować dane źródłowe. Najłatwiej to zrobić za pomocą tabeli przestawnej. Wybierz dowolną komórkę w tabeli źródłowej i użyj polecenia Wstaw — tabela przestawna (Wstaw — tabela przestawna). W wyświetlonym oknie pośrednim nie zmieniaj niczego i kliknij OK, a następnie w panelu, który pojawi się po prawej stronie, przeciągnij pola danych źródłowych z górnych do dolnych obszarów układu przyszłej tabeli przestawnej:
Wynikiem powinna być tabela podsumowująca z całkowitymi przychodami dla każdego produktu:
Posortuj go w kolejności malejącej przychodów, ustawiając aktywną komórkę na kolumnie Kwota w polu Przychód i za pomocą przycisku sortowania От Я до А (od Z do A) Zakładka Dane.
Teraz musimy dodać obliczoną kolumnę ze skumulowanymi zarobkami odsetkowymi. Aby to zrobić, ponownie przeciągnij pole Dochód do obszaru Wartości w prawym okienku, aby uzyskać zduplikowaną kolumnę w osi. Następnie kliknij prawym przyciskiem myszy sklonowaną kolumnę i wybierz polecenie Dodatkowe obliczenia – % sumy bieżącej w polu (Pokaż dane jako – % sumy bieżącej w). W wyświetlonym oknie wybierz pole Imię, na którym procent przychodów będzie się kumulował od góry do dołu. Dane wyjściowe powinny wyglądać tak, jak w poniższej tabeli:
Jak widać, jest to prawie gotowy stół z pierwszej części artykułu. Do pełnego szczęścia brakuje jedynie kolumny z wartością progową 80% do skonstruowania linii odcięcia w przyszłym diagramie. Taką kolumnę można łatwo dodać za pomocą pola obliczeniowego. Zaznacz dowolną liczbę w podsumowaniu, a następnie kliknij zakładkę Strona główna – Wstaw – Pole obliczeniowe (Strona główna – Wstaw – Pole obliczeniowe). W oknie, które się otworzy, wprowadź nazwę pola i jego formułę (w naszym przypadku stałą):
Po kliknięciu OK do tabeli zostanie dodana trzecia kolumna z wartością 80% we wszystkich komórkach i ostatecznie przybierze wymaganą formę. Następnie możesz użyć polecenia Wykres przestawny (wykres przestawny) Zakładka parametry (Opcje) or Analiza (Analiza) i skonfiguruj wykres w dokładnie taki sam sposób, jak pierwsza opcja:
Wyróżnianie kluczowych produktów
Aby wyróżnić czynniki mające największy wpływ, można wyróżnić kolumny znajdujące się na lewo od punktu przecięcia pomarańczowej krzywej skumulowanych odsetek z poziomą linią odcięcia 80%. Aby to zrobić, będziesz musiał dodać kolejną kolumnę do tabeli z formułą:
Ta formuła wyprowadza 1, jeśli iloczyn znajduje się po lewej stronie punktu przecięcia i 0, jeśli jest po prawej stronie. Następnie musisz wykonać następujące czynności:
- Do wykresu dodajemy nową kolumnę – najłatwiej to zrobić przez proste skopiowanie, czyli podświetlenie kolumny podświetlenie, skopiuj go (Ctrl + C), wybierz schemat i wstaw (Ctrl + V).
- Wybierz dodany wiersz i przełącz go wzdłuż osi pomocniczej, jak opisano powyżej.
- Typ wykresu serii podświetlenie zmiana na kolumny (histogram).
- Usuwamy prześwit boczny we właściwościach wiersza (kliknij prawym przyciskiem myszy na wiersz) Oświetlenie – format rzędów – szczelina boczna), aby kolumny zlały się w jedną całość.
- Usuwamy granice kolumn i sprawiamy, że wypełnienie jest przezroczyste.
W efekcie otrzymujemy tak ładne wyróżnienie najlepszych produktów:
PS
Począwszy od Excel 2016, wykres Pareto został dodany do standardowego zestawu wykresów Excel. Teraz, aby go zbudować, wystarczy wybrać zakres i na zakładce wstawka (Wstawić) wybierz odpowiedni typ:
Jedno kliknięcie – i schemat jest gotowy:
- Jak zbudować raport za pomocą tabeli przestawnej
- Skonfiguruj obliczenia w tabelach przestawnych
- Co nowego w wykresach w programie Excel 2013
- Artykuł w Wikipedii na temat prawa Pareto