Amfani da Nazarin ABC a Microsoft Excel

Ofaya daga cikin mahimman hanyoyin gudanar da dabaru shine nazarin ABC. Tare da taimakonsa, zaku iya rarrabe albarkatun masana'antar, kaya, abokan ciniki, da sauransu. ta mataki na mahimmanci. A lokaci guda, gwargwadon mahimmancin matakin, ana sanya kowane ɗayan abubuwan da ke sama ɗayan rukuni uku: A, B ko C. Excel yana cikin kayan aikinsa wanda ya sauƙaƙa gudanar da wannan nau'in bincike. Bari mu gano yadda ake amfani da su, kuma menene ke tattare da bincike ABC.

Yin amfani da Nazarin ABC

Binciken ABC wani nau'i ne na haɓaka da daidaitawa ga yanayin zamani na ƙa'idar Pareto. Dangane da hanyar aiwatar da shi, dukkanin abubuwa na binciken sun kasu kashi uku bisa gwargwadon mahimmancin:

• Nau'i A - abubuwanda ke tattare dasu sama da 80% takamaiman nauyi;
• Nau'i B - abubuwanda hadewar su daga 5% a da 15% takamaiman nauyi;
• Nau'i C - sauran abubuwanda suka rage, hade duka wanda shine 5% kuma kasa da takamaiman nauyi.

Wasu kamfanoni suna amfani da ƙarin fasahohi masu zurfi kuma suna fasa abubuwa a cikin rukunin 3 ko 4 ko 5, amma zamu dogara da tsarin bincike na ABC na gargajiya.

Hanyar 1: rarrabawa bincike

A cikin Excel, ana gudanar da bincike na ABC ta amfani da rarrabewa. Duk abubuwa ana rarrabe daga babba zuwa ƙarami. Sannan, ana yin lissafin takamaiman nauyin kowane sashi, dangane da wacce aka sanya nau'in. Bari mu gano, ta amfani da takamaiman misali, yadda ake amfani da wannan dabarar a aikace.

Muna da tebur tare da jerin kayan da kamfanin ke siyarwa, da kuma adadin kuɗin shiga daga siyarwar su na wani ɗan lokaci. A kasan tebur, jimlar kudaden shiga ga duk abubuwan kaya. Aikin, ta amfani da nazarin ABC, shine raba waɗannan samfuran cikin rukuni bisa ga mahimmancinsu ga kasuwancin.

1. Zaɓi tebur tare da siginan bayanai, riƙe maɓallin linzamin kwamfuta na hagu, ban da taken da kuma layi na ƙarshe. Je zuwa shafin "Bayanai". Latsa maballin. "Tace"located a cikin toshe kayan aiki Dadi da kuma Matatarwa a kan tef.

   

   Hakanan zaka iya yi daban. Zaɓi saman da ke saman teburin, sannan matsa zuwa wurin "Gida" kuma danna maballin Dadi da kuma Matatarwalocated a cikin toshe kayan aiki "Gyara" a kan tef. Ana kunna jerin abubuwa wanda muke zaɓi wani matsayi a ciki. Tsarin Kasuwanci.



2. Lokacin amfani da ɗayan ɗayan ayyukan da ke sama, ana buɗe taga taga abubuwa. Mun duba don haka a kusa da siga "My bayanai yana dauke da taken" an saita alamar rajista. Idan babu shi, shigar.

   A fagen Harafi nuna sunan shafi wanda ke ɗauke da bayanan kudaden shiga.

   A fagen "Tace" kuna buƙatar tantance wane takamaiman ma'aunin rarrabewa za'a yi. Mun bar saitunan da aka tsara - - "Dabi'u".

   A fagen "Oda" saita matsayi "Cancanci".

   Bayan yin saitunan da aka ƙayyade, danna kan maɓallin "Ok" a kasan taga.



3. Bayan aiwatar da aikin da aka ƙayyade, dukkanin abubuwan sun tsara ta hanyar kudaden shiga daga mafi girma zuwa ƙarami.



4. Yanzu ya kamata mu lissafa takamaiman nauyi na kowane ɗayan abubuwan gaba ɗaya. Muna ƙirƙirar ƙarin shafi don waɗannan dalilai, waɗanda za mu kira "Takamaiman nauyi". A zangon farko na wannan shafin, sanya alama "=", bayan haka muna nuna hanyar haɗi zuwa tantanin halitta wanda adadin kudaden shiga daga siyarwar samfurin daidai yake. Na gaba, saita alamar rarrabuwa ("/") Bayan haka, nuna ma'anar ayyukan tantanin halitta, wanda ya ƙunshi adadin yawan tallace-tallace na kaya a cikin kasuwancin.

   Ganin cewa zamu kwayar da tsarin da aka kayyade zuwa wasu kwayoyin halitta a cikin shafi "Takamaiman nauyi" ta yin amfani da mai cike da alamar, to muna buƙatar gyara adireshin hanyar haɗi zuwa kashi wanda ke ɗauke da jimlar kudaden shiga ga kamfani. Don yin wannan, sanya mahaɗin cikakkar. Zaɓi daidaitawa na tantanin halitta da aka ƙayyade a cikin dabara kuma latsa maɓallin F4. A gaban masu daidaitawa, kamar yadda muke gani, alamar dala ta bayyana, wanda ke nuna cewa haɗin yanar gizon ya zama cikakke. Ya kamata a lura cewa hanyar haɗin zuwa darajar kudaden shiga na abu na farko a cikin jerin (Samfura 3) dole ne ya kasance dangi.

   Sannan, don yin lissafi, danna maballin Shigar.



5. Kamar yadda kake gani, ragin kudaden shiga daga samfurin farko da aka jera a cikin jerin an nuna shi a cikin kwayar manufa. Don kwafa dabarar zuwa kewayon da ke ƙasa, sanya siginan kwamfuta a cikin kusurwar dama ta sel. Yana canzawa cikin alamar cika wanda yayi kama da ƙaramin giciye. Latsa maɓallin linzamin kwamfuta na hagu ka ja mai alamar mai ƙasa zuwa ƙarshen shafi.



6. Kamar yadda kake gani, shafin gaba daya yana cike da bayanan da yake nuna rabon kudaden shiga daga siyarwar kowane samfuri. Amma takamaiman nauyi an nuna shi a cikin lamba, kuma muna buƙatar canza shi zuwa kashi. Don yin wannan, zaɓi abubuwan da ke cikin shafi "Takamaiman nauyi". Sannan mun matsa zuwa shafin "Gida". A kan kintinkiri a cikin rukunin saiti "Lambar" Akwai filin da yake nuna tsarin bayanai. Ta hanyar tsoho, idan ba ku yi ƙarin jan hankali ba, ya kamata a saita tsarin a wurin "Janar". Mun danna kan gunkin a cikin hanyar alwatika wanda yake gefen dama na wannan filin. A cikin jerin tsare-tsare da ke buɗe, zaɓi matsayin "Sha'awa".



7. Kamar yadda kake gani, an canza duk darajar shafi zuwa darajojin ƙimar. Kamar yadda aka zata, a layi "Gaba ɗaya" nuna 100%. Matsakaicin kayan ana tsammanin kasancewa a cikin shafi daga babba zuwa ƙarami.



8. Yanzu yakamata mu kirkiro wani shafi wanda za'a fara rabawa tare da tarawa duka. Wannan shine, a kowane layi, takamaiman nauyin samfurin musamman zai ƙara takamaiman nauyin duk waɗancan samfuran waɗanda ke cikin jerin abubuwan da ke sama. Don abu na farko a cikin jerin (Samfura 3) takamaiman matakin nauyi da rabon da za'a tara zai zama daidai, amma ga duk wanda zai biyo baya, rabon abubuwan da suka gabata na jerin ya buƙaci a ƙara nuna su ga mai nuna alama.

   Don haka, a cikin jere na farko mun matsa zuwa shafi Rarraba Share Alamar shafi "Takamaiman nauyi".



9. Na gaba, saita siginan kwamfuta zuwa sel na biyu a cikin shafi. Rarraba Share. Anan dole muyi amfani da tsari. Mun sanya alama daidai kuma ƙara abubuwan da ke cikin tantanin halitta "Takamaiman nauyi" layi ɗaya da abin da ke ciki Rarraba Share daga layin da ke sama. Mun bar duk hanyoyin haɗin gwiwa, ma'ana, ba mu sarrafa su. Bayan haka, danna maɓallin Shigar don nuna sakamako na ƙarshe.



10. Yanzu kuna buƙatar kwafin wannan dabinon a cikin sel ɗin wannan shafi, waɗanda suke a ƙasa. Don yin wannan, yi amfani da alamar cikawa, wanda muka riga muka koma lokacin da aka kwafa dabarar a cikin shafi "Takamaiman nauyi". A wannan yanayin, layin "Gaba ɗaya" babu buƙatar kamawa, tunda sakamakon shine ya haifar 100% za a nuna akan abu na ƙarshe daga jerin. Kamar yadda kake gani, dukkanin abubuwan da ke cikin shafinmu sun cika bayan hakan.



11. Bayan haka mun kirkiro shafi "Kungiyoyi". Muna buƙatar tattara samfuran zuwa kashi A, B da C bisa ga tara hannun jari. Kamar yadda muke tunawa, dukkan abubuwa sun kasu kashi-kashi bisa tsarin mai zuwa:
    • A - zuwa 80%;
    • B - mai zuwa 15%;
    • Tare da - saura 5%.

    Saboda haka, ga duk kaya, da tara hannun jari na musamman nauyi wanda aka hada a cikin iyaka har zuwa 80%sanya rukuni A. Kayan aiki tare da takamaiman nauyi na 80% a da 95% sanya rukuni B. Sauran samfurin samfurin tare da ƙimar mafi girma fiye da 95% tara takamaiman nauyin sanya nau'in C.



12. Don tsinkaye, zaku iya cika waɗannan rukunin tare da launuka daban-daban. Amma wannan ba na tilas bane.

Don haka, mun rarrabe abubuwa zuwa kungiyoyi gwargwadon matakin mahimmancin, ta amfani da nazarin ABC. Lokacin amfani da wasu fasahohi, kamar yadda aka ambata a baya, ana amfani da rarrabuwa cikin manyan rukuni, amma ƙa'idar rarrabuwar ta kasance ba ta canzawa.

Darasi: Fitarwa da kuma matattara

Hanyar 2: yi amfani da tsari mai rikitarwa

Tabbas, yin amfani da rarrabawa shine hanya mafi yawancin mutane don aiwatar da bincike na ABC a cikin Excel. Amma a wasu halaye, wajibi ne don gudanar da wannan bincike ba tare da sake shirya layuka a cikin ainihin tebur ba. A wannan yanayin, tsari mai rikitarwa zai zo don ceto. A matsayin misali, zamu yi amfani da tebur ɗaya tushe kamar yadda a farkon yanayin.

1. Ara zuwa ainihin tebur ɗin da ke ɗauke da sunan kayan da aka sayar daga sayarwar kowannensu, shafi "Kungiyoyi". Kamar yadda kake gani, a wannan yanayin ba za mu iya ƙara ginshiƙai tare da lissafin daidaikun mutane da tara hannun jari ba.



2. Zaɓi sel na farko a cikin shafi "Kungiyoyi"sannan kuma danna maballin "Saka aikin"wanda yake kusa da layin tsari.



3. Kunna cigaba Wizards na Aiki. Mun matsa zuwa rukuni Tunani da Arrays. Zaɓi aiki "CHEICE". Latsa maballin "Ok".



4. Ana kunna taga aikin aiki. KYAUTA. An gabatar da bayanin sa kamar haka:

   = Zabi (Index_number; Darajar1; Darajar2; ...)

   Manufar wannan aikin ita ce fitar da ɗayan ƙimar da aka nuna, gwargwadon lambar ma'anar. Yawan ƙimar zai iya kaiwa 254, amma muna buƙatar kawai sunaye uku waɗanda suka dace da nau'ikan nazarin ABC: A, B, Tare da. Nan da nan zamu iya shiga filin "Darajar1" alama ce "A"a fagen "Darajar2" - "B"a fagen "Darajar 3" - "C".



5. Amma tare da hujja Lambar Manuniya Dole ne ku dame shi sosai ta hanyar haɗa fewan ƙarin masu aiki a ciki. Sanya siginan kwamfuta a cikin filin Lambar Manuniya. Bayan haka, danna maballin a cikin nau'i na alwatika zuwa hagu na maɓallin "Saka aikin". Jerin masu aiki da aka yi amfani da su kwanan nan ya buɗe. Muna bukatar aiki Neman. Tunda ba cikin jerin bane, saika latsa alamar "Sauran sifofin ...".



6. Tagan taga ya sake tashi. Wizards na Aiki. Sake sake komawa cikin rukuni Tunani da Arrays. Nemo matsayi a wurin "Neman", zaɓi shi kuma danna maballin "Ok".



7. Wurin Shaida Mai aiki Yana buɗewa Neman. Syntax kamar haka:

   = Binciko (Bincike_value; Dubawa_array; Match_type)

   Dalilin wannan aikin shine ƙayyade matsayin adadin ɓangaren abin da aka ƙayyade. Wannan shine, kawai abin da muke buƙata don filin Lambar Manuniya da ayyuka KYAUTA.

   A fagen Ganin Array Nan take zaka iya bayyana ambaton mai zuwa:

   {0:0,8:0,95}

   Ya kamata ya kasance a cikin ɗayan biyun, a matsayin tsari mai tsari. Zai yi wuya a iya lamuran cewa waɗannan lambobin (0; 0,8; 0,95) nuna iyakokin babban rabo tsakanin kungiyoyi.

   Filin Nau'in Match na tilas ne kuma a wannan yanayin ba zamu cika shi ba.

   A fagen "Neman darajar" saita siginan kwamfuta. Bayan haka kuma ta hanyar hoton da ke sama a cikin nau'in alwatika mu koma zuwa Mayan fasalin.



8. Wannan lokacin a ciki Mayen aiki matsa zuwa rukuni "Ilmin lissafi". Zaɓi suna SUMMS kuma danna maballin "Ok".



9. Farashin muhawara na aiki yana farawa ZAMU CIGABA. Ma'aikacin da aka ƙaddara yana taƙaita ƙwayoyin da ke haɗuwa da takamaiman yanayin. Syntax din sa shine:

   = LITTAFIN (yanki; ra'ayi; sum_range)

   A fagen "Range" shigar da adireshin shafi "Kudade". Don waɗannan dalilai, saita siginan kwamfuta a cikin filin, sannan, riƙe maɓallin linzamin kwamfuta na hagu, zaɓi duk ƙwayoyin a cikin sashin da ya dace, ban da darajar "Gaba ɗaya". Kamar yadda kake gani, an nuna adreshin nan da nan a filin. Bugu da kari, muna bukatar sanya wannan hanyar ta zama cikakkar. Don yin wannan, zaɓi shi kuma danna maɓallin F4. Adireshin ya tsaya tare da alamun dala.

   A fagen "Sharhin" muna buƙatar saita yanayi. Mun shigar da wadannan magana:

   ">"&

   Sannan nan da nan bayan shi mun shigar da adireshin sel na farko na shafi "Kudade". Mun sanya daidaitattun kwance a wannan adireshin ta hanyar ɗaukar alamar dala daga cikin allon gaban wasiƙar. Mun bar madaidaitan daidaitawa dangi, wato, kada a sami wata alama a gaban lambar.

   Bayan haka, kar a danna maballin "Ok", kuma danna sunan aikin Neman a cikin dabarar dabara.



10. Sannan mun koma zuwa yanayin muhawara na aiki Neman. Kamar yadda kake gani, a cikin filin "Neman darajar" ya bayyana bayanan da mai aikin ya saita ZAMU CIGABA. Amma wannan ba duka bane. Je zuwa wannan filin kuma ƙara alamar a bayanan data kasance. "+" ba tare da ambato ba. Sannan mun shigar da adireshin sel na farko na shafi "Kudade". Kuma sake, muna sanya daidaitawar wannan hanyar ta zama cikakkar, kuma mu barsu dangi na tsaye.

    Bayan haka, ɗauki duka abubuwan da ke filin "Neman darajar" a cikin baka, daga baya muka sanya alamar rarraba ("/") Bayan haka, sake ta alamar alwatika, je zuwa taga zaɓi aikin.



11. Kamar lokacin ƙarshe a cikin gudu Mayen aiki neman mai aiki da ake so a rukunin "Ilmin lissafi". Wannan lokacin, ana kiran aikin da ake so SAURARA. Zaɓi shi kuma danna maballin. "Ok".



12. Wurin Shaida Mai aiki Yana buɗewa SAURARA. Babban mahimmancinsa shine taƙaita bayanai a sel. Ginin kalma na wannan bayani abu ne mai sauki:

    = SUM (Lambar1; lamba2; ...)

    Don dalilan mu kawai ana buƙatar filin "Lambar1". Shigar da daidaitawa na kewayon shafi a ciki. "Kudade"ban da tantanin halitta wanda ya ƙunshi jimlar. Mun riga mun yi irin wannan aiki a fagen "Range" da ayyuka ZAMU CIGABA. Kamar yadda muke a wancan lokacin, muna yin daidaitawa na kewayon cikakkar ta zabar su da latsa madannin F4.

    Bayan haka, danna maɓallin "Ok" a kasan taga.



13. Kamar yadda kake gani, hadaddun ayyukan da aka gabatar sun aiwatar da ƙididdigar kuma mayar da sakamakon zuwa sel na farko na shafi "Kungiyoyi". An sanya samfurin farko ne rukuni "A". Cikakken dabara da muka yi amfani da wannan ƙididdigar ita ce kamar haka:

    = SAUKE (Neman (SUMMES ($ B $ 2: $ B $ 27; ">" & $ B2) + $ B2) / SUM ($ B $ 2: $ B $ 27); {0: 0.8: 0.95} ); "A"; "B"; "C")

    Amma, hakika, a kowane yanayi, masu tsarawa a cikin wannan tsari zasu bambanta. Don haka, ba za a iya yin la'akari da shi a zaman duniya ba. Amma, ta amfani da jagorar da aka bayar a sama, zaku iya shigar da daidaitawar kowane tebur kuma ku sami nasarar amfani da wannan hanyar a kowane yanayi.



14. Koyaya, wannan ba duk bane. Mun yi lissafin kawai don layin farko na tebur. Don gaba ɗaya cika wani shafi tare da bayanai "Kungiyoyi", kuna buƙatar kwafa wannan dabara zuwa kewayon da ke ƙasa (ban da tantanin halitta "Gaba ɗaya") amfani da alamar cikewa, kamar yadda muka yi fiye da sau ɗaya. Bayan an shigar da bayanan, ana iya yin nazarin ABC kammalawa.

Kamar yadda kake gani, sakamakon da aka samu ta amfani da zabin ta amfani da hadadden tsari bashi da bambanci kwatankwacin sakamakon da muka aiwatar ta rarrabewa. Duk samfuran suna sanya nau'ikan guda ɗaya, amma layin bai canza matsayin farawa ba.

Darasi: Mayan Maɗaukaki

Excel na iya sauƙaƙe binciken ABC ga mai amfani. Ana samun wannan ta amfani da kayan aiki kamar rarrabawa. Bayan wannan, takamaiman girman nauyi, hannun jari kuma, a zahiri, ana yin lissafin rabo cikin rukuni. A cikin yanayin inda ba a ba da izinin canza asalin farkon layuka a cikin tebur ba, zaku iya amfani da hanyar ta amfani da tsari mai rikitarwa.