Kugadzira Pareto Chati muExcel

Gwaro rePareto, rakapihwa zita remuongorori wezvehupfumi wekuItaly, Vilfredo Pareto, rinoti 80% yematambudziko anogona kukonzerwa ne20% yezvikonzero. Nheyo yacho inogona kubatsira zvakanyanya kana ruzivo rwekuchengetedza hupenyu kana iwe uchifanira kusarudza kuti ndeipi yematambudziko mazhinji ekutanga kugadzirisa, kana kana kubviswa kwezvinetso kwakaomeswa nemamiriro ekunze.

Semuenzaniso, uchangobva kukumbirwa kuti utungamire timu iri kunetseka kushanda pane imwe purojekiti kuti ivaratidze nzira kwayo. Unobvunza nhengo dzechikwata kuti ndezvipi zvaive zvipingaidzo zvavo zvikuru mukuzadzikisa zvinangwa nezvinangwa zvavo. Vanoita runyoro rwaunoongorora uye kuona kuti ndezvipi zvikonzero zvikuru zveimwe neimwe yezvinetso zvakasangana neboka, kuyedza kuona zvakabatana.

Zvose zvakaonekwa zvinokonzera matambudziko zvinorongwa maererano nehuwandu hwekuitika kwavo. Uchitarisa nhamba, unoona kuti kushaikwa kwekutaurirana pakati pevaiti veprojekiti uye vanobata mapurojekiti ndicho chikonzero chematambudziko epamusoro makumi maviri nematatu anotarisana nechikwata, nepo dambudziko rechipiri rakakura kuwana zviwanikwa zvinodiwa (makomputa masisitimu, midziyo, nezvimwe, nezvimwe. .). .) zvakakonzera matambudziko gumi nerimwe chete. Mamwe matambudziko ari ega. Zviri pachena kuti nekugadzirisa dambudziko rekutaurirana, chikamu chikuru chezvinetso chinogona kubviswa, uye nekugadzirisa dambudziko rekuwana zviwanikwa, inenge 23% yezvipingamupinyi munzira yeboka inogona kugadziriswa. Kwete chete kuti wafunga kuti ungabatsira sei timu, watoita ongororo yePareto.

Kuita basa rose iri papepa kungangotora nguva yakati kuti. Maitiro acho anogona kukwidziridzwa zvakanyanya uchishandisa iyo Pareto chati muMicrosoft Excel.

Pareto machati musanganiswa wemutsara wechati uye histogram. Iwo akasiyana nekuti anowanzo kuve neakisi imwe yakachinjika (category axis) uye maviri akatwasuka axes. Iyo chati inobatsira pakuisa pamberi uye kuronga data.

Basa rangu nderekukubatsira kugadzirira data yePareto chati uye wobva wagadzira iyo chati pachayo. Kana yako data yakatogadzirirwa iyo Pareto chati, saka unogona kuenderera kune yechipiri chikamu.

Nhasi tichaongorora mamiriro ezvinhu ane dambudziko mukambani inogara ichidzorera vashandi mari. Basa redu nderekutsvaga kuti chii chatinoshandisa zvakanyanya uye kunzwisisa kuti tingaderedza sei mitengo iyi ne80% tichishandisa ongororo yePareto nekukurumidza. Isu tinogona kuziva kuti ndezvipi zvinodhura zvinoverengera 80% yekudzoserwa uye kudzivirira mitengo yakakwira mune ramangwana nekuchinja mutemo kushandisa mitengo yemitengo uye kukurukura mutengo wevashandi.

Chikamu Chekutanga: Gadzirira Dhata yePareto Chati

1. Ronga data rako. Mutafura yedu, pane 6 zvikamu zvemuripo wemari uye huwandu hunorehwa nevashandi.
2. Ronga data uchidzika. Tarisa kuti makoramu akasarudzwa А и Вkuronga nemazvo.
3. Column Sum uwandu (nhamba yemari) inoverengerwa uchishandisa basa SUM (SUM). Mumuenzaniso wedu, kuti uwane huwandu hwese, unofanirwa kuwedzera maseru kubva V3 to V8.

Hotkey: Kuti uverenge huwandu hwehuwandu, sarudza sero B9 uye tigoshingairira Alt+=. Mari yese ichava $12250.

3. 
4. Gadzira chikamu Cumulative Amount (cumulative amount). Ngatitange nekutanga kukosha $ 3750 muchitokisi B3. Kukosha kwega kwega kunobva pakukosha kwesero rakapfuura. Muchitokisi C4 mhando =C3+B4 uye tigoshingairira pinda.
5. Kuti uzadze otomatiki maseru asara mukororamu, tinya kaviri mubato wekuzadza otomatiki.
6. Zvadaro, gadzira chikamu Cumulative % (cumulative percentage). Kuzadza koramu iyi, unogona kushandisa huwandu hwehuwandu uwandu uye kukosha kubva pa column Cumulative Amount. Mubhawa yeformula yesero D3 pinda =C3/$B$9 uye tigoshingairira pinda. Symbol $ inogadzira chinongedzo chakakwana zvekuti kukosha kwesero (cell reference B9) haichinji kana iwe ukakopa fomula pasi.
7. Tinya kaviri chiratidzo chekuzadza otomatiki kuti uzadze koramu nefomula, kana kudzvanya chicherechedzo uye wochikweva pane iyo data column.
8. Iye zvino zvese zvagadzirira kutanga kuvaka iyo Pareto chati!

Chikamu Chechipiri: Kuvaka Pareto Chati muExcel

1. Sarudza iyo data (mumuenzaniso wedu, maseru kubva A2 by D8).
2. dzvanya Alt + F1 pa keyboard kuti ugadzire otomatiki chati kubva pane yakasarudzwa data.
3. Tinya-kurudyi munzvimbo yechati uye kubva pane menyu inooneka, tinya Sarudza data (Sarudza Data). A dialog box ichaonekwa Kusarudza nzvimbo yedata (Sarudza Data Source). Sarudza mutsara Cumulative Amount uye tigoshingairira Bvisa (Bvisa). Zvadaro OK.
4. Dzvanya pagirafu uye shandisa makiyi emiseve pakhibhodi yako kufamba pakati pezvinhu zvayo. Kana mutsara we data wasarudzwa Cumulative %, iyo zvino inopindirana nechikamu axis (horizontal axis), tinya pairi uye sarudza Shandura rudzi rwechati kune dzakatevedzana (Shandura Chart Series Type). Iye zvino iyi nhevedzano yedata yakaoma kuona, asi inogoneka.
5. A dialog box ichaonekwa Shandura rudzi rwechati (Shandura Rudzi rweChati), sarudza mutsara chati.
6. Saka, isu tine histogram uye flat line girafu pamwe neyakachinjika axis. Kuti tiratidze kusunungurwa kwegirafu remutsara, tinoda imwe axis yakatwasuka.
7. Kurudyi tinya pamutsara Cumulative % uye mune menyu inobuda, tinya Data series format (Format Data Series). Bhokisi rebhokisi rezita rimwechete richaonekwa.
8. Muchikamu Row Options (Series Options) sarudza Diki Axis (Secondary Axis) uye tinya bhatani pedyo (Vhara).
9. Iyo muzana axis ichaonekwa, uye chati ichashanduka kuita yakazara-yakazara Pareto chati! Iye zvino isu tinogona kutora mhedziso: iyo yakawanda yemitengo imari yechikoro (Mari yekudzidzisa), zvishandiso (Iron) uye zvekunyora (Zvishandiso zveHofisi).

Iine nhanho-ne-nhanho mirairo yekumisikidza uye kugadzira Pareto chati muExcel iri paruoko, edza mukudzidzira. Nekushandisa Pareto ongororo, unogona kuona matambudziko akanyanya kukosha uye kutora danho rakakosha pakuagadzirisa.