Cuprins
Poate ați auzit de Legea Pareto sau de Principiul 20/80. La sfârșitul secolului al XIX-lea, sociologul și economistul italian Vilfredo Pareto a descoperit că distribuția bogăției în societate este inegală și supusă unei anumite dependențe: odată cu creșterea averii, numărul oamenilor bogați scade exponențial cu un coeficient constant ( în rândul gospodăriilor italiene, 19% din venit era în 80% din familii). Mai târziu, această idee a fost dezvoltată în cartea sa de Richard Koch, care a propus formularea „Principiului 20/20” universal (80% din eforturi dau 20% din rezultat). În practică, această lege nu este de obicei exprimată în numere atât de frumoase (a se citi „Coada lungă” de Chris Anderson), dar arată clar distribuția neuniformă a resurselor, profiturilor, costurilor etc.
În analiza de afaceri, o diagramă Pareto este adesea construită pentru a reprezenta această neuniformitate. Poate fi folosit pentru a arăta vizual, de exemplu, care produse sau clienți aduc cel mai mare profit. De obicei arată așa:
Principalele sale caracteristici:
- Fiecare coloană albastră a histogramei reprezintă profitul pentru produs în unități absolute și este reprezentată de-a lungul axei din stânga.
- Graficul portocaliu reprezintă procentul cumulat al profiturilor (adică ponderea profitului pe bază cumulativă).
- Pe o margine condiționată de 80%, o linie orizontală de prag este de obicei trasată pentru claritate. Toate bunurile din stânga punctului de intersecție al acestei linii cu graficul profitului acumulat ne aduc 80% din bani, toate bunurile în dreapta – restul de 20%.
Să vedem cum să construiți singur o diagramă Pareto în Microsoft Excel.
Opțiunea 1. O diagramă Pareto simplă bazată pe date gata făcute
Dacă datele sursă v-au venit sub forma unui tabel similar (adică, deja în formă finalizată):
… apoi facem următoarele.
Sortați tabelul în ordinea descrescătoare a profitului (tab Date – Sortare) și adăugați o coloană cu formula de calcul a procentului acumulat din profit:
Această formulă împarte profitul total acumulat de la începutul listei până la articolul curent la profitul total pentru întregul tabel. Adăugăm, de asemenea, o coloană cu o constantă de 80% pentru a crea o linie punctată cu prag orizontal în diagrama viitoare:
Selectăm toate datele și construim o histogramă obișnuită pe filă Inserare – Histogramă (Inserare – Diagramă coloane). Ar trebui să arate cam așa:
Seria procentuală din diagrama rezultată trebuie trimisă de-a lungul axei secundare (dreapta). Pentru a face acest lucru, trebuie să selectați rândurile cu mouse-ul, dar acest lucru poate fi dificil, deoarece sunt greu de văzut pe fundalul coloanelor mari de profit. Prin urmare, este mai bine să utilizați lista derulantă din filă pentru a evidenția Aspect or Format:
Apoi faceți clic dreapta pe rândul selectat și selectați comanda Formatați seria de date iar în fereastra care apare, selectați opțiunea Pe axa secundară (Axa secundară). Ca rezultat, diagrama noastră va arăta astfel:
Pentru seria Cota de profit cumulat și Prag, trebuie să schimbați tipul de diagramă din coloane în linii. Pentru a face acest lucru, faceți clic pe fiecare dintre aceste rânduri și selectați comanda Schimbați tipul graficului în serie.
Tot ce rămâne este să selectați rândul orizontal Threshold și să îl formatați astfel încât să arate ca o linie de tăiere mai degrabă decât date (adică, eliminați marcajele, faceți linia întreruptă roșie etc.). Toate acestea se pot face făcând clic dreapta pe rând și selectând comanda Formatați seria de date. Acum diagrama va lua forma finală:
Potrivit acesteia, putem concluziona că 80% din profit este adus de primele 5 mărfuri, iar toate celelalte bunuri din dreapta cartofului reprezintă doar 20% din profit.
În Excel 2013, o puteți face și mai ușor - utilizați noul tip de diagramă combinată încorporat imediat când trasați:
Opțiunea 2: PivotTable și Pivot Pareto Chart
Ce să faci dacă nu există date gata făcute pentru construcție, ci doar informațiile brute originale? Să presupunem că la început avem un tabel cu date de vânzări ca acesta:
Pentru a construi o diagramă Pareto pe acesta și pentru a afla ce produse se vând cel mai bine, va trebui mai întâi să analizați datele sursă. Cel mai simplu mod de a face acest lucru este cu un tabel pivot. Selectați orice celulă din tabelul sursă și utilizați comanda Insert – Pivot Table (Insert – Pivot Table). În fereastra intermediară care apare, nu modificați nimic și faceți clic OK, apoi în panoul care apare în partea dreaptă, trageți câmpurile de date sursă din zonele de sus în partea de jos a aspectului viitorului tabel pivot:
Rezultatul ar trebui să fie un tabel rezumat cu veniturile totale pentru fiecare produs:
Sortați-l în ordinea descrescătoare a veniturilor setând celula activă pe coloană Sumă în câmpul Venituri și folosind butonul de sortare От Я до А (De la Z la A) fila Date.
Acum trebuie să adăugăm o coloană calculată cu câștigurile acumulate din dobânzi. Pentru a face acest lucru, trageți din nou câmpul Venituri spre zona Valori în panoul din dreapta pentru a obține o coloană duplicată în pivot. Apoi faceți clic dreapta pe coloana clonată și alegeți comanda Calcule suplimentare – % din totalul curent în câmp (Afișați datele ca – % totalul curent). În fereastra care apare, selectați câmpul Nume si Prenume, pe care procentul din venit se va acumula de sus în jos. Ieșirea ar trebui să arate ca acest tabel:
După cum puteți vedea, acesta este aproape un tabel gata făcut din prima parte a articolului. Pentru fericirea deplină îi lipsește doar o coloană cu o valoare de prag de 80% pentru construirea unei linii de tăiere într-o diagramă viitoare. O astfel de coloană poate fi adăugată cu ușurință folosind un câmp calculat. Evidențiați orice număr din rezumat și apoi faceți clic pe filă Acasă – Inserare – Câmp calculat (Acasă – Inserare – Câmp calculat). În fereastra care se deschide, introduceți numele câmpului și formula acestuia (în cazul nostru, o constantă):
După ce faceți clic pe OK o a treia coloană va fi adăugată la tabel cu o valoare de 80% în toate celulele și va lua în sfârșit forma necesară. Apoi puteți folosi comanda Diagrama pivot (Diagrama pivot) fila parametrii (Opțiuni) or Analiză (Analiză) și configurați graficul exact în același mod ca prima opțiune:
Evidențierea produselor cheie
Pentru a evidenția cei mai influenți factori, adică pot fi evidențiate coloane situate în stânga punctului de intersecție al curbei portocalii de dobândă acumulată cu linia de tăiere orizontală de 80%. Pentru a face acest lucru, va trebui să adăugați o altă coloană la tabel cu formula:
Această formulă dă 1 dacă produsul este la stânga punctului de intersecție și 0 dacă este la dreapta. Apoi, trebuie să faceți următoarele:
- Adăugăm o nouă coloană în diagramă – cel mai simplu mod de a face acest lucru este prin simpla copiere, adică evidențiați coloana lumina de fundal, copiaza-l (Ctrl + C), selectați diagrama și introduceți (Ctrl + V).
- Selectați rândul adăugat și comutați-l de-a lungul axei secundare, așa cum este descris mai sus.
- Tip de diagramă de serie lumina de fundal schimbare în coloane (histogramă).
- Înlăturăm spațiul lateral din proprietățile rândului (clic dreapta pe rând Iluminare – Format rând – Spațiu lateral) astfel încât coloanele să se îmbine într-un singur întreg.
- Îndepărtăm marginile coloanelor și facem umplutura translucidă.
Drept urmare, obținem un evidenție atât de frumos al celor mai bune produse:
PS
Începând cu Excel 2016, diagrama Pareto a fost adăugată la setul standard de diagrame Excel. Acum, pentru a-l construi, trebuie doar să selectați intervalul și pe filă Insera (Introduce) alege tipul potrivit:
Un clic - și diagrama este gata:
- Cum să creați un raport folosind un tabel pivot
- Configurați calcule în PivotTables
- Ce este nou în diagrame în Excel 2013
- Articol Wikipedia despre legea lui Pareto