Tworzenie wykresu Pareto w programie Excel (statyczny i dynamiczny)

Obejrzyj wideo – jak utworzyć Wykres Pareto w programie Excel

Wykres Pareto opiera się na zasadzie Pareto (znanej również jako zasada 80/20), która jest dobrze znaną koncepcją w zarządzaniu projektami.

80 / 20 procent wartości może się różnić, ale idea jest taka, że spośród wszystkich problemów / wysiłków jest kilka, które dają maksymalny wpływ.

Jest to szeroko stosowana koncepcja w zarządzaniu projektami w celu ustalenia priorytetów pracy.

Tworzenie wykresu Pareto w programie Excel

W tym samouczku pokażę, jak utworzyć:

• 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 jest ukryta w jak organizujesz dane w zapleczu.

Weźmy przykład hotelu, w przypadku którego dane dotyczące skarg mogą wyglądać tak, jak pokazano poniżej:

UWAGA: Aby utworzyć wykres Pareto w programie Excel, musisz uporządkować dane w porządku malejącym.

Pobierz szablon wykresu Pareto programu Excel

Tworzenie prostego (statycznego) wykresu Pareto w programie Excel

Oto kroki tworzenia wykresu Pareto w programie Excel:

1. Skonfiguruj dane, jak pokazano poniżej.
2. Oblicz skumulowany% w kolumnie C. Użyj następującego wzoru: = SUMA (2 B $: B2) / SUMA (B $ 2 $: 1 $ B $ )
3. Wybierz cały zestaw danych (A1: C10), przejdź do Wstaw – > Wykresy – > Kolumna 2-W – > Kolumna grupowana. Spowoduje to wstawienie wykresu kolumnowego z 2 seriami danych (liczba skarg i skumulowany odsetek).
4. Kliknij prawym przyciskiem dowolny słupek i wybierz opcję Zmień Typ wykresu szeregowego.
5. W oknie dialogowym Zmień typ wykresu wybierz opcję Combo w lewym panelu.
6. Wprowadź następujące zmiany:
   • Liczba skarg: kolumna zgrupowana.
   • Skumulowany%: wiersz (zaznacz także pole wyboru Oś pomocnicza).
7. Twój wykres Pareto w programie Excel jest gotowy. Dostosuj wartości osi pionowej i tytuł wykresu.

Jak zinterpretować ten wykres Pareto w programie Excel

To Pareto Wykres przedstawia główne kwestie, na których hotel powinien się skupić, aby załatwić maksymalną liczbę reklamacji. Na przykład ukierunkowanie na pierwsze 3 problemy automatycznie załatwiło ~ 80% skarg.

Na przykład skierowanie uwagi na pierwsze 3 problemy automatycznie załatwiło ~ 80% skarg.

Tworzenie dynamicznego (interaktywnego) wykresu Pareto w programie Excel

Teraz, gdy mamy statyczny / prosty wykres Pareto w programie Excel, przejdźmy o krok dalej i uczyńmy go nieco interaktywnym.

Coś, jak pokazano poniżej:

W tym przypadku użytkownik może określić% skarg, którymi należy się zająć (używając przewijania programu Excel bar), a wykres automatycznie podkreśli problemy, którym należy się przyjrzeć.

Pomysł polega na tym, aby mieć 2 różne słupki.

Czerwony jest podświetlony, gdy skumulowany procent wartość jest zbliżona do wartości docelowej.

Oto kroki, aby utworzyć ten interaktywny wykres Pareto w programie Excel:

1. W komórce B14 mam połączoną wartość docelową do paska przewijania (którego wartość waha się od 0 do 100).
2. W komórce B12 użyłem formuły = B14 / 100. Ponieważ nie można określić wartości procentowej na pasku przewijania, po prostu dzielimy wartość paska przewijania (w B14) przez 100, aby uzyskać wartość procentową.
3. W komórce B13 wprowadź następującą kombinację funkcji INDEKS, PODAJ.POZYCJĘ i JEŻELI BŁĄD:
   = JEŻELI BŁĄD (INDEKS (2 C $: 10 C $ 10, JEŻELI BŁĄD (DOPASOWANIE (12 $ B $, 2 $ C $ 10,1 ), 0) +1), 1)
   Ta formuła zwraca łączną wartość, która pokryłaby wartość docelową. Na przykład, jeśli masz wartość docelową 70%, zwróci to 77%, co oznacza, że powinieneś spróbować rozwiązać pierwsze trzy problemy.

1. W komórce D2 wprowadź następującą formułę (i przeciągnij lub skopiuj dla całej komórki – D2: D10):
   = JEŻELI (13 $ B $ > = C2, B2, NA ())
2. W komórce E2 wprowadź następującą formułę (i przeciągnij lub skopiuj dla całej komórki – E2: E10):
   = JEŻELI ( $ B $ 13 < C2, B2, NA ())
3. Wybierz dane w kolumnie A, C, D & E (naciśnij Control i wybierz myszą).
4. Idź do Wstaw – > Wykresy – > Kolumna 2-W – > Kolumna grupowana.Spowoduje to wstawienie wykresu kolumnowego z 3 seriami danych (skumulowany procent, słupki, które mają być podświetlone w celu spełnienia celu i pozostałe wszystkie pozostałe słupki)
5. Kliknij prawym przyciskiem myszy dowolny słupek i wybierz opcję Zmień wykres serii Wpisz.
6. W oknie dialogowym Zmień typ wykresu wybierz opcję Combo w lewym okienku i wprowadź następujące zmiany:
   • Skumulowany%: Linia (zaznacz także pole wyboru Oś pomocnicza).
   • Wyróżnione słupki: kolumna zgrupowana.
   • Pozostałe słupki: kolumna zgrupowana.
7. Kliknij prawym przyciskiem myszy dowolny z podświetlonych słupków i zmień kolor na czerwony.

To wszystko!

Utworzyłeś interaktywny wykres Pareto w programie Excel.

Teraz, gdy zmienisz celu za pomocą paska przewijania, wykres Pareto zostanie odpowiednio zaktualizowany.

Pobierz szablon wykresu Pareto programu Excel

Czy używasz wykresu Pareto w programie Excel?

Chciałbym usłyszeć Twoje przemyślenia na temat tej techniki i tego, jak użyłem go. Zostaw swoje ślady w sekcji komentarzy 🙂

Powiązane samouczki dotyczące zarządzania projektami i tworzenia wykresów:

• Analiza skarg restauracji za pomocą wykresu Pareto.
• Tworzenie wykresu Gantta Wykres w programie Excel.
• Tworzenie wykresu punktów kontrolnych w programie Excel.
• Tworzenie histogramu w programie Excel.
• Szablon kalkulatora arkusza czasu pracy w programie Excel.
• Szablon śledzenia odejść pracowników.
• Obliczanie średniej ważonej w programie Excel.
• Tworzenie krzywej dzwonowej w programie Excel.
• Zaawansowane wykresy w programie Excel
• Jak aby dodać oś pomocniczą do wykresów programu Excel.