Obejrzyj wideo - jak zrobić wykres Pareto w programie Excel
Wykres Pareto opiera się na zasadzie Pareto (zwanej również zasadą 80/20), która jest dobrze znaną koncepcją w zarządzaniu projektami.
Zgodnie z tą zasadą ~80% problemów można przypisać około ~20% problemów (lub ~80% twoich wyników może być bezpośrednim wynikiem ~20% twoich wysiłków i tak dalej… ).
Wartość procentowa 80/20 może się różnić, ale chodzi o to, że spośród wszystkich problemów/wysiłków jest kilka, które przynoszą maksymalny wpływ.
Jest to szeroko stosowana koncepcja w zarządzaniu projektami w celu ustalenia priorytetów pracy.
Tworzenie wykresu Pareto w Excelu
W tym samouczku pokażę, jak zrobić:
- Prosty (statyczny) wykres Pareto w programie Excel.
- Dynamiczny (interaktywny) wykres Pareto w programie Excel.
Tworzenie wykresu Pareto w programie Excel jest bardzo łatwe.
Cała sztuczka kryje się w sposobie rozmieszczenia danych w backendzie.
Weźmy przykład Hotelu, dla którego dane reklamacyjne mogą wyglądać tak, jak pokazano poniżej:
UWAGA: Aby utworzyć wykres Pareto w programie Excel, musisz uporządkować dane w kolejności malejącej.
Tworzenie prostego (statycznego) wykresu Pareto w programie Excel
Oto kroki, aby utworzyć wykres Pareto w programie Excel:
- Skonfiguruj swoje dane, jak pokazano poniżej.
- Oblicz skumulowany % w kolumnie C. Użyj następującej formuły: =SUM($B$2:B2)/SUM($B$2:$B$1)
- Wybierz cały zestaw danych (A1:C10), przejdź do Wstaw -> Wykresy -> Kolumna 2-D -> Kolumna grupowana. Spowoduje to wstawienie wykresu kolumnowego z 2 seriami danych (liczba skarg i skumulowany odsetek).
- Kliknij prawym przyciskiem myszy dowolny słupek i wybierz Zmień typ wykresu serii.
- W oknie dialogowym Zmień typ wykresu wybierz Combo w lewym okienku.
- Wprowadź następujące zmiany:
- Liczba skarg: kolumna grupowana.
- Skumulowany %: Linia (zaznacz również pole wyboru Oś pomocnicza).
[Jeśli używasz programu Excel 2010 lub 2007, będzie to dwuetapowe proces. Najpierw zmień typ wykresu na wykres liniowy. Następnie kliknij prawym przyciskiem myszy wykres liniowy i wybierz Formatuj serie danych i wybierz Oś pomocnicza w opcjach serii]
- Twój wykres Pareto w Excelu jest gotowy. Dostosuj wartości osi pionowej i tytuł wykresu.
Jak interpretować ten wykres Pareto w programie Excel?
Ten wykres Pareto podkreśla główne kwestie, na których hotel powinien się skoncentrować, aby uporządkować maksymalną liczbę reklamacji. Na przykład skupienie się na pierwszych 3 problemach automatycznie załatwiłoby ~80% skarg.
Na przykład skupienie się na pierwszych 3 problemach automatycznie załatwiłoby ~80% skarg.
Tworzenie dynamicznego (interaktywnego) wykresu Pareto w Excelu
Teraz, gdy mamy statyczny/prosty wykres Pareto w Excelu, pójdźmy o krok dalej i sprawmy, by był trochę interaktywny.
Coś, jak pokazano poniżej:
W takim przypadku użytkownik może określić % reklamacji, które należy rozpatrzyć (za pomocą paska przewijania programu Excel), a wykres automatycznie podświetli kwestie, którymi należy się zająć.
Pomysł polega na tym, aby mieć 2 różne paski.
Czerwony jest podświetlony, gdy skumulowana wartość procentowa jest zbliżona do wartości docelowej.
Oto kroki, aby zrobić ten interaktywny wykres Pareto w programie Excel:
- W komórce B14 mam wartość docelową połączoną z paskiem przewijania (którego wartość waha się od 0 do 100).
- W komórce B12 użyłem formuły = B14/100. Ponieważ nie możesz określić wartości procentowej na pasku przewijania, po prostu dzielimy wartość paska przewijania (w B14) przez 100, aby uzyskać wartość procentową.
- W komórce B13 wprowadź następującą kombinację funkcji INDEKS, DOPASUJ i JEŻELI.BŁĄD:
=JEŻELI.BŁĄD(INDEKS($C$2:$C$10;JEŻELI.BŁĄD(DOPASOWANIE($B$12;$C$2:$C$10;1);0)+1);1)
Ta formuła zwraca skumulowaną wartość, która pokryłaby wartość docelową. Na przykład, jeśli masz wartość docelową 70%, zwróci 77%, co oznacza, że powinieneś spróbować rozwiązać pierwsze trzy problemy.
- W komórce D2 wprowadź następującą formułę (i przeciągnij lub skopiuj dla wszystkich komórek - D2:D10):
=JEŻELI($B$13>=C2,B2,NA()) - W komórce E2 wprowadź następującą formułę (i przeciągnij lub skopiuj dla wszystkich komórek - E2:E10):
=JEŻELI($B$13<> - Wybierz dane w kolumnie A, C, D i E (naciśnij Control i wybierz za pomocą myszy).
- Przejdź do Wstaw -> Wykresy -> Kolumna 2-D -> Kolumna grupowana. Spowoduje to wstawienie wykresu kolumnowego z 3 seriami danych (skumulowany procent, słupki, które mają zostać podświetlone w celu osiągnięcia celu, oraz pozostałe słupki)
- Kliknij prawym przyciskiem myszy dowolny słupek i wybierz Zmień typ wykresu serii.
- W oknie dialogowym Zmień typ wykresu wybierz Combo w lewym okienku i wprowadź następujące zmiany:
- Skumulowany %: Linia (zaznacz również pole wyboru Oś drugorzędna).
- Podświetlone słupki: Kolumna skupiona.
- Pozostałe słupki: kolumna skupiona.
- Kliknij prawym przyciskiem myszy dowolny z podświetlonych pasków i zmień kolor na czerwony.
Otóż to!
Utworzyłeś interaktywny wykres Pareto w programie Excel.
Teraz, gdy zmienisz cel za pomocą paska przewijania, wykres Pareto zostanie odpowiednio zaktualizowany.
Czy używasz wykresu Pareto w programie Excel?
Chciałbym usłyszeć Twoje przemyślenia na temat tej techniki i sposobu jej użycia. Zostaw swoje ślady w sekcji komentarzy 🙂
- Analiza reklamacji restauracji za pomocą wykresu Pareto.
- Tworzenie wykresu Gantta w programie Excel.
- Tworzenie wykresu Milestone w Excelu.
- Tworzenie histogramu w Excelu.
- Szablon kalkulatora grafiku programu Excel.
- Szablon śledzenia urlopu pracownika.
- Obliczanie średniej ważonej w programie Excel.
- Tworzenie krzywej dzwonowej w programie Excel.
- Zaawansowane wykresy Excel
- Jak dodać oś pomocniczą na wykresach programu Excel.
