Daya daga cikin mahimman hanyoyi na gudanarwa da kayan aiki shine bincike na ABC. Tare da shi, za ka iya rarraba albarkatu na ƙwarewar, samfurori, abokan ciniki, da dai sauransu. saboda muhimmancin. A daidai wannan lokacin, bisa ga muhimmancin mahimmanci, an sanya kowanne ɗayan da aka tsara a ɗayan sassa uku: A, B, ko C. Excel na da kayayyakin aiki a cikin kayansa wanda zai sa ya fi sauƙi don aiwatar da irin wannan bincike. Bari mu ga yadda za mu yi amfani da su, kuma menene bincike na ABC.
Amfani da bincike na ABC
ABC bincike ne wani nau'i na inganta da kuma saba da yanayin zamani bambancin na Pareto manufa. Dangane da hanyar da ake gudanarwa, dukkanin abubuwan bincike sun kasu zuwa kashi uku don muhimmancin:
- Category A - abubuwan da suke da yawa 80% takamaiman nauyin;
- Category B - abubuwa, wanda duka shine daga 5% har zuwa 15% takamaiman nauyin;
- Category C - sauran abubuwan da suka rage, yawan jimillar abin da yake 5% da kuma rage takamaiman nauyin.
Wasu kamfanoni suna amfani da dabarun da suka ci gaba da rarraba abubuwa ba cikin 3 ba, amma cikin ƙungiyoyi 4 ko 5, amma za mu dogara ga tsarin shirin na ABC.
Hanyar 1: bincike ta hanyar fashewa
A cikin Excel, ana yin bincike na ABC ta amfani da fashewa. Dukkan abubuwa an ware daga mafi girma zuwa mafi ƙanƙanci. Sa'an nan kuma ƙididdigar nauyin kowane nau'i an ƙidaya, bisa kan abin da aka sanya wani nau'i a gare shi. Bari mu yi amfani da wani misali don gano yadda ake amfani da wannan fasahar a aikace.
Muna da tebur tare da jerin kayayyaki da kamfanin ke sayar, da kuma adadin yawan kudaden shiga daga sayarwa don wani lokaci. A kasan teburin, yawan kudaden shiga an taƙaita shi don duk kayan kayan. Ayyukan shine yin amfani da ABC-bincike don raba waɗannan samfurori zuwa kungiyoyi bisa ga muhimmancin su.
- Zaɓi teburin tare da sakonnin bayanai, riƙe da maɓallin linzamin hagu, ba tare da maɓallin kai ba da jere na ƙarshe. Jeka shafin "Bayanan". Danna maballin. "A ware"located a cikin wani akwati na kayayyakin aiki "Tsara da tace" a kan tef.
Hakanan zaka iya yin daban. Zaɓi layin da ke sama a kan teburin, to sai ku motsa zuwa shafin "Gida" kuma danna maballin "Tsara da tace"located a cikin wani akwati na kayayyakin aiki Ana gyara a kan tef. An kunna jerin da muka zaɓa matsayi a ciki. "Yanki na Custom".
- Lokacin da ake yin amfani da duk wani aikin da aka yi a sama, an kaddamar da taga saitin saiti. Mun dubi saitin "Bayanan na yana ƙunshe da maɓallai" an saita alamar. Idan akwai rashinsa, shigar.
A cikin filin "Shafi" saka sunan mahaɗin da bayanan kuɗi ya samu.
A cikin filin "A ware" kana buƙatar sakawa ta hanyar takamaiman ƙayyadaddun tsari. Mun bar saitunan da aka saita - "Darajar".
A cikin filin "Dokar" saita matsayi "Saukowa".
Bayan yin wadannan saituna danna maballin "Ok" a kasan taga.
- Bayan yin wannan aikin, an ware duk abubuwa ta hanyar kudaden shiga daga mafi girma zuwa mafi ƙasƙanci.
- Yanzu muna buƙatar lissafta yawancin kowane abu don jimlar. Mun kirkiro don waɗannan dalilai ƙarin shafi, wanda za mu kira "Share". A cikin sakon farko na wannan shafi sanya alamar "="bayan haka zamu nuna ma'anar tantanin tantanin salula inda yawan kudaden da aka samu daga sayarwa samfurin da aka dace ya samo Next, saita siginar rarraba ("/"). Bayan haka zamu nuna mahimmanci na tantanin halitta, wanda ya ƙunshi adadin tallace-tallace na kaya a duk faɗin aikin.
Ganin gaskiyar cewa za mu kwafi tsarin da aka nuna a wasu sassan kundin "Share" ta amfani da alamar cika, adreshin mahaɗin zuwa mahaɗin da ke dauke da adadin kudaden shiga ga ɗayan ɗin, muna buƙatar gyara. Don yin wannan, sa mahaɗin din cikakke. Zaɓi daidaituwa na tantanin halitta wanda aka ƙayyade a cikin tsari kuma danna maballin F4. Kamar yadda muka gani, alamar dollar ta bayyana a gaban haɗin kai, wanda ke nuna cewa haɗin ya zama cikakke. Ya kamata a lura cewa ƙididdigar adadin kudaden shiga na farko abu a jerin (Mataki na 3) dole ne ya kasance dangi.
Bayan haka, don yin lissafi, danna kan maballin. Shigar.
- Kamar yadda kake gani, yawan kudaden shiga daga samfurin farko da aka jera a cikin jerin suna nunawa a cikin wayar da aka kera. Don yin kwafin wannan tsari a cikin kewayon da ke ƙasa, sanya siginan kwamfuta a kusurwar dama na cell. An canza shi a matsayin alamar cika mai kama da ƙananan giciye. Latsa maɓallin linzamin hagu kuma ja da ƙoshin da aka cika har zuwa ƙarshen shafi.
- Kamar yadda kake gani, dukan shafi yana cike da bayanan da ke kwatanta rabon kudaden shiga daga sayar da kowane samfurin. Amma darajar takamaiman nauyin an nuna shi a cikin mahimman tsari, kuma muna buƙatar canza shi zuwa kashi. Don yin wannan, zaɓi abubuwan da ke ciki na shafi "Share". Sai motsa zuwa shafin "Gida". A rubutun a cikin ƙungiyar saitunan "Lambar" Akwai filin da ke nuna tsarin bayanai. Ta hanyar tsoho, idan ba ku yi wani manipako ba, za'a tsara tsarin a can. "Janar". Mun danna kan gunkin a cikin nau'i na triangle wanda ke tsaye a dama na wannan filin. A cikin jerin jerin da suka buɗe, zaɓi matsayi "Shawarwari".
- Kamar yadda kake gani, dukkanin lambobin shafi sun canza zuwa kashi. Kamar yadda ya kamata, a cikin layi "Jimlar" aka nuna 100%. Yanayin kayan da ake tsammani za'a kasance a cikin shafi daga babba zuwa karami.
- Yanzu ya kamata mu ƙirƙirar wani shafi wanda za'a rarraba rarraba tare da cikakken jimlar. Wato, a kowace jere, za a ƙara nauyin nauyin duk waɗannan kayayyaki waɗanda aka samo a cikin jerin da ke sama zuwa nauyin nauyin mutum na musamman. Ga abu na farko a jerin (Mataki na 3) nauyin ma'auni na musamman da rabon da aka ƙayyade zai daidaita, amma ga duk waɗanda suka biyo baya, za a ƙaddara shareccen abin da aka rigaya a cikin lissafi zuwa alamar mutum.
Don haka, a jere na farko mun canja zuwa shafi "Haɗa Share" lissafin shafi "Share".
- Kusa, saita siginan kwamfuta a cikin tantanin halitta na biyu. "Haɗa Share". A nan dole mu yi amfani da wannan tsari. Mun sanya alamar daidai kuma ninka abinda ke ciki na tantanin halitta "Share" wannan jeri da abun ciki na cell "Haɗa Share" daga layin da ke sama. Dukkan hanyoyi ne dangi, wato, ba zamu yi wani abu tare da su ba. Bayan haka, danna maballin. Shigar don nuna sakamakon ƙarshe.
- Yanzu kuna buƙatar kwafin wannan maƙala zuwa sel na wannan shafi, wanda aka samo a kasa. Don yin wannan, yi amfani da alamar cikawa, wanda muka riga ya sake zama don kwashe ma'anar a shafi "Share". A lokaci guda, kirtani "Jimlar" kamawa bai zama dole ba saboda sakamakon da aka samu a cikin 100% za a nuna a kan abu na ƙarshe daga jerin. Kamar yadda kake gani, duk abubuwan da ke cikin shafinmu sun cika.
- Bayan haka mun ƙirƙiri wani shafi "Rukuni". Muna buƙatar ƙunsar samfurori zuwa cikin jigogi A, B kuma C bisa ga yawan kuɗin da aka nuna. Kamar yadda muka tuna, duk abubuwa suna rarraba cikin kungiyoyi bisa ga tsarin da ake biyowa:
- A - har sai 80%;
- B - wadannan 15%;
- Tare da - sauran 5%.
Sabili da haka, duk kayan kaya, ƙayyadaddun ɓangaren ƙididdigewa wanda ya shiga iyakar zuwa 80%sanya wani jinsi A. Kasuwanci tare da nauyin kaya na musamman 80% har zuwa 95% sanya wani jinsi B. Ƙarin samfurin samfurin tare da darajar ƙarin 95% ƙaddara takamaiman nauyin sanya nau'i C.
- Don tsabta, zaka iya cika waɗannan rukuni a launi daban-daban. Amma wannan zaɓi ne.
Saboda haka, mun karya abubuwa a cikin kungiyoyi bisa ga muhimmancin gaske, ta yin amfani da bincike na ABC. Lokacin yin amfani da wasu hanyoyi, kamar yadda aka ambata a sama, yi amfani da bangare zuwa ƙungiyoyi da yawa, amma ka'idar rabuwa ta kasance kusan canzawa.
Darasi: Raba da kuma tace a Excel
Hanyar 2: ta amfani da mahimman tsari
Tabbas, yin amfani da jingina shine hanyar da ta fi dacewa don gudanar da bincike na ABC a Excel. Amma a wasu lokuta ana buƙatar gudanar da wannan bincike ba tare da rayar da layuka a cikin tebur ba. A wannan yanayin, wani tsari mai mahimmanci zai zo wurin ceto. Alal misali, zamu yi amfani da maɓallin tushe guda ɗaya kamar yadda a cikin farko.
- Ƙara zuwa ɗakin farko wanda ya ƙunshi sunan kayan kaya da kuma kudade daga sayarwa kowane ɗayan su, shafi "Rukuni". Kamar yadda kake gani, a wannan yanayin ba zamu iya ƙara ginshikan tare da lissafi na mutum da tarawa ba.
- Zaɓi sel na farko a shafi. "Rukuni"sannan danna maballin. "Saka aiki"located kusa da dabara bar.
- An yi kunnawa Ma'aikata masu aiki. Matsa zuwa category "Hanyoyin sadarwa da zane-zane". Zaɓi aiki "SELECT". Danna maballin. "Ok".
- An kunna maɓallin maganin aikin. Zaba. Sakamakonsa kamar haka:
= SELECT (Index_number; Value1; Value2; ...)Manufar wannan aiki shine don fitowa daga ɗaya daga cikin lambobin da aka ƙayyade, dangane da lambar index. Yawan adadin zai iya zuwa 254, amma muna buƙatar kawai sunaye guda uku waɗanda suka dace da nau'i na bincike na ABC: A, B, Tare da. Zamu iya shiga cikin filin nan da nan "Value1" Alamar "A"a cikin filin "Value2" - "B"a cikin filin "Value3" - "C".
- Amma tare da gardama "Halin lambar" Dole ne a sake gwadawa sosai, bayan gina wasu ƙarin aiki. Saita siginan kwamfuta a filin "Halin lambar". Kusa, danna kan gunkin da ke da nau'i na alawalin hagu na button "Saka aiki". Jerin ayyukan amfani da kwanan nan ya buɗe. Muna buƙatar aiki MATCH. Tun da ba a cikin jerin ba, danna kan rubutun "Sauran fasali ...".
- Gudun kan taga. Ma'aikata masu aiki. Sa'an nan kuma zuwa jinsin "Hanyoyin sadarwa da zane-zane". Mun sami wuri a can "MATCH"zaɓi shi kuma danna maballin "Ok".
- Ma'aikatar kulawa ta buɗewa ta buɗe MATCH. Sakamakonsa kamar haka:
= MATCH (Abinda aka bincika;Dalilin wannan aikin shine don ƙayyade lambar matsayi na takaddama. Wato, kawai abin da muke buƙatar filin "Halin lambar" ayyuka Zaba.
A cikin filin "Viewed array" Zaka iya sanya bayanin nan da nan:
{0:0,8:0,95}Ya kamata ya zama daidai a cikin takalmin gyare-gyare, kamar nau'in tsari. Ba abu ne mai wuya ba tsammani waɗannan lambobi (0; 0,8; 0,95) ya nuna iyakokin rabon da aka tara tsakanin kungiyoyi.
Field "Tsarin Mapping" ba dole ba kuma a wannan yanayin ba za mu cika shi ba.
A cikin filin "Sakamakon darajar" saita siginan kwamfuta. Sa'an nan kuma, ta hanyar alamar da aka bayyana a cikin nau'i mai maƙalli, mun matsa zuwa Wizard aikin.
- Wannan lokaci a cikin Mai sarrafa aiki motsa zuwa category "Ilmin lissafi". Zaɓi sunan "SUMMESLI" kuma danna maballin "Ok".
- Gidan gwajin aikin ya fara. Kundin. Kamfanin mai ƙayyade yana ƙayyade sel da ke haɗu da yanayin da aka ƙayyade. Harshensa shine:
= SUMMES (range, criterion, range_summing)A cikin filin "Range" shigar da adireshin shafi "Raba". Ga waɗannan dalilai, mun saita siginan kwamfuta a filin, sannan, idan muka danna maɓallin linzamin hagu, zaɓi dukkan sassan jimlar daidai, ban da darajar "Jimlar". Kamar yadda kake gani, an nuna adireshin nan da nan a cikin filin. Bugu da kari, muna buƙatar yin wannan mahada cikakke. Don yin wannan, sa zaɓi kuma latsa maballin F4. Adireshin yana haskaka tare da alamun dollar.
A cikin filin "Criterion" muna buƙatar saita yanayin. Shigar da waɗannan kalmomi:
">"&Nan da nan bayan haka mun shigar da adireshin farkon tantanin halitta na shafi. "Raba". Mun sanya daidaitattun kwance a cikin wannan adireshin cikakke, yana ƙara alamar dollar daga madogarar keyboard a gaban harafin. Tsakanin a tsaye yana dangi, wato, kada a sami alama a gaban lambar.
Bayan haka, kada ku danna maballin "Ok", kuma danna sunan aikin MATCH a cikin tsari.
- Sa'an nan kuma mu koma cikin taga na aiki. MATCH. Kamar yadda kake gani, a filin "Sakamakon darajar" bayanan da aka bayar da mai aiki Kundin. Amma ba haka ba ne. Jeka wannan filin kuma ƙara alama ga bayanan data kasance. "+" ba tare da fadi ba. Sa'an nan kuma mu shigar da adireshin farkon tantanin halitta na shafi. "Raba". Kuma kuma mun sanya daidaitattun kwance na wannan mahada cikakke, kuma a tsaye mun bar dangi.
Kusa, ɗauki duk abinda ke cikin filin "Sakamakon darajar" a cikin sakonni, sa'an nan kuma sanya siginar rarraba ("/"). Bayan haka, sake ta hanyar alamar triangle, je zuwa zabin zaɓi.
- Kamar karshe lokacin gudu Mai sarrafa aiki neman ma'aikacin da ake so a cikin rukunin "Ilmin lissafi". A wannan lokaci, ana kiran aikin da ake so "SUMM". Zaɓi shi kuma danna maballin. "Ok".
- Ma'aikatar kulawa ta buɗewa ta buɗe SUM. Babban manufar shi shine taƙaita bayanai a cikin kwayoyin halitta. Maganar wannan sanarwa yana da sauki:
= SUM (Lamba1; Number2; ...)Don manufofinmu muna buƙatar filin kawai. "Number1". Shigar da haɓakawa na mahaɗin shafi "Raba", banda tantanin salula da ke dauke da cikakkun bayanai. Mun riga mun yi irin wannan aiki a filin. "Range" ayyuka Kundin. Kamar yadda a wannan lokacin, muna yin cikakken haɗin kewayon ta hanyar zabar su, kuma latsa maɓallin F4.
Bayan wannan latsa maɓallin "Ok" a kasan taga.
- Kamar yadda kake gani, ƙaddamar da ayyukan da aka shigar ya haifar da lissafi kuma ya ba da sakamakon a cikin tantanin farko na shafi "Rukuni". An saka abu na farko a rukuni. "A". Cikakken tsari da muka yi amfani da wannan lissafi kamar haka:
= SELECT (MATCH ((SUMMES ($ B $ 2: $ B $ 27; ">" & $ B2) + $ B2) / SUM ($ B $ 2: $ B $ 27); {0: 0.8: 0.95} ); "A"; "B"; "C").Amma, hakika, a kowane hali, haɗin kai a wannan tsari zai zama daban. Sabili da haka, ba za'a iya la'akari da shi ba. Amma, ta yin amfani da jagoran da aka ba a sama, zaka iya sanya haɗin duk kowane tebur kuma yi nasarar amfani da wannan hanyar a kowane hali.
- Duk da haka, wannan ba duka bane. Mun lasafta kawai don jere na farko na tebur. Don ci gaba da cika ma'ajin bayanai "Rukuni", kana buƙatar kwafin wannan maƙasudin zuwa layin da ke ƙasa (ban da layin jeri "Jimlar") ta amfani da alamar cika, kamar yadda muka yi fiye da sau ɗaya. Bayan an shigar da bayanai, za a iya la'akari da bincike na ABC cikakke.
Kamar yadda kake gani, sakamakon da aka samu ta yin amfani da bambance-bambancen tare da yin amfani da wani tsari mai mahimmanci bai bambanta ba daga sakamakon da muka yi ta hanyar rarrabawa. Ana sanya dukkan samfurori guda iri ɗaya, amma layin ba su canza matsayi na farko ba.
Darasi: Maɓallin aiki na Excel
Excel zai iya sauƙaƙe ABC bincike don mai amfani. Ana samun wannan ta hanyar amfani da kayan aiki kamar fashewa. Bayan haka, nauyin ma'auni na musamman, ƙididdiga na tara kuma, a gaskiya, rarraba cikin ƙungiyoyi an ƙidaya. A cikin lokuta inda ba a yarda da canje-canje a matsayin farko na layuka a teburin ba, ba za a iya izinin hanyar ba ta amfani da tsari mai mahimmanci.
