Ukuhlaziywa kwe-ABC ku-Excel

Enye yezindlela ezisemqoka zokuphatha kanye nokuhlelwa kwezinto kuhlaziywe kwe-ABC. Ngalo, ungahlukanisa izinsiza zebhizinisi, izimpahla, amakhasimende, njll. Ngokwezinga elithile elibaluleke kakhulu. Ngasikhathi sinye, ngezinga lokubaluleka, ngalinye leyunithi engenhla labelwa enye yezigaba ezintathu: Uhlelo lwe-A, B noma C. Excel lunezinhlelo zalo zemithwalo ezenza kube lula ukwenza lolu hlobo lokuhlaziywa. Ake sibheke ukuthi singazisebenzisa kanjani, futhi yini ukuhlaziywa kwe-ABC.

Usebenzisa ukuhlaziywa kwe-ABC

Ukuhlaziywa kwe-ABC uhlobo lokuthuthuka nokuguqulwa kwezimo zanamuhla zomgomo we-pareto. Ngokwendlela yokuziphatha kwayo, zonke izinto zokuhlaziya zihlukaniswe izigaba ezintathu ngokuya ngezinga elithile:

• Isigaba A - Izici ezinokuhlanganiswa kwama-80% ama-gravity athile athile;
• Isigaba B - Izici ezihlanganisiwe ezisukela ku-5% kuya ku-15% wokudonsela phansi okuthile;
• Isigaba C - Izinto ezisele, okuhlanganisiwe okuphelele kwawo okungama-5% namandla athile adonsela phansi.

Izinkampani ezihlukile zisebenzisa amasu athuthukile futhi zihlukanise izinto ezingezona ezi-3, kodwa ngamaqembu ama-4 noma ama-5, kepha sizoncika ohlelweni lokuhlaziywa kwe-ABC lwakudala.

Indlela 1: Ukuhlaziywa ngokuhlunga

Ukuhlaziywa kwe-Excel ABC kwenziwa kusetshenziswa ukuhlunga. Zonke izinto zihlelwe ngokunamafutha kube kancane. Lapho-ke isabelo esiqongelelwayo sento ngayinye sibalwa, ngesisekelo sawo esabelwa isigaba esithile. Ake sibheke ukuthi indlela ebekiwe isetshenziswa kanjani ekusebenzeni.

Sinetafula elinohlu lwemikhiqizo oluthengiswa yinkampani, kanye nenani elihambisanayo lemali engenayo ekuthengisweni kwawo isikhathi esithile. Phansi kwetafula, umphumela wemali etholwa njalo kuwo wonke amagama wezimpahla. Kunomsebenzi osebenzisa ukuhlaziywa kwe-ABC, wephule lezi zimpahla zibe ngamaqembu ngokubaluleka kwazo ebhizinisini.

1. Sigqamisa itafula nesikhombisi ngokuvala inkinobho yegundane kwesobunxele, ngaphandle kwe-cap kanye nentambo yokugcina. Iya kuthebhu "yedatha". Sichofoza inkinobho ethi "Hlunga", etholakala ku- "Hort and Hort and Filter" Amathuluzi etheyipini.

   

   Ungenza futhi ngokuhlukile. Sabela leli tafula elingenhla, bese lithuthela kuthebhu "Yasekhaya" bese uqhafaza inkinobho ethi "Hort and Filter" etholakala kwibhokisi lamathuluzi lokuhlela. Uhlu lusebenze lapho isikhundla esithi "Ukuhlunga okwenziwe ngezifiso" kucushiwe.



2. Lapho usebenzisa noma yiziphi izenzo ezingenhla, kuqaliswa kwewindows yezilungiselelo. Sibukeka kangangokuba "idatha yami iqukethe izihloko" ipharamitha yahlelwa kumaki wokuhlola. Endabeni yokungabikho kwayo, faka.

   Ensimini "yekholamu", chaza igama lekholomu lapho imininingwane yemali itholakala khona.

   Ensimini ethi "Hlunga", udinga ukucacisa, lapho kuzohlelwa khona imigomo ethile. Shiya izilungiselelo ezisethiwe - "Amanani".

   Ensimini ethi "Order", bonisa isikhundla esithi "ukwehla".

   Ngemuva komkhiqizo wezilungiselelo ezibekiwe, chofoza inkinobho ethi "Kulungile" ezansi kwewindi.



3. Ngemuva kokwenza isenzo esibekiwe, zonke izinto zahlelwa ngemali engenayo evela kokuncane kuya kokuncane.



4. Manje kufanele sibale ingxenye yezakhi ngayinye yemiphumela ejwayelekile. Dala ikholomu eyengeziwe ngalezi zinhloso, esibiza ngokuthi "Abelana ngesisindo". Esitokisini sokuqala sale kholamu, sibeka uphawu "=", ngemuva kwalokho ucacisa ukuxhumana esitokisini lapho inani lemali engenayo evela ekusetshenzisweni komkhiqizo ofanele. Okulandelayo, setha isibonakaliso sokwahlukaniswa ("/"). Ngemuva kwalokho, sibonisa izixhumanisi zeseli, eziqukethe inani eliphelele lokuthengiswa kwezimpahla kulo lonke leli bhizinisi.

   Njengoba kunikezwe iqiniso lokuthi ifomula elicacisiwe esizokopisha kwamanye amaseli wekholomu "yokwabelana" ngumaki wokugcwalisa, ikheli lesixhumanisi esiqukethe inani lokugcina lemali engenayo ebhizinisini, kudingeka silungise. Ukuze wenze lokhu, yenza ukuxhumana ngokuphelele. Khetha izixhumanisi zeseli elibekiwe kwifomula bese ucindezela ukhiye we-F4. Ngaphambi kwezixhumanisi, njengoba sibona, kwavela isibonakaliso sedola, esibonisa ukuthi isixhumanisi sesiphelele. Kumele kuqashelwe ukuthi ireferensi enanini lemali engenayo yohlu lwezimpahla (umkhiqizo 3) kufanele ihlale isihlobo.

   Ngemuva kwalokho, ukwenza ukubala, cindezela inkinobho ethi Faka.



5. Njengoba sibona, ingxenye yemali engenayo evela kumkhiqizo wokuqala ochazwe ohlwini kwavela esitokisini esiqondiwe. Ukukopisha ifomula ebangeni elingezansi, sibeka isikhombisi ekhoneni elingezansi kwesokudla seseli. Ukuguqulwa kwalo kuMarker wokugcwalisa, ukubukeka kwesiphambano esincane. Sichofoza inkinobho yegundane kwesobunxele bese sihudula umaki wokugcwalisa phansi kuze kube sekupheleni kwekholomu.



6. Njengoba ubona, yonke ikholomu igcwele idatha ebonakala ngengxenye yemali engenayo evela ekusetshenzisweni komkhiqizo ngamunye. Kepha inani lamandla adonsela phansi liboniswa ngefomethi yezinombolo, futhi kudingeka sikuguqule ngamaphesenti. Ukuze wenze lokhu, qokomisa okuqukethwe yikholomu "isisindo esithile". Ngemuva kwalokho sithuthela kuthebhu "Yasekhaya". Etheyipini eqenjini lezilungiselelo zeqembu kukhona inkambu ekhombisa ifomethi yedatha. Ngokuzenzakalelayo, uma ungakhiqizi umthetho owengeziwe, ifomethi ethi "General" kumele ifakwe lapho. Chofoza isithonjana ngendlela yonxantathu otholakala ngakwesokudla kwale nkambu. Uhlu lwamafomethi, khetha isikhundla "samaphesenti".



7. Njengoba sibona, wonke amanani wekholomu aguqulwa abe ngamaphesenti amaphesenti. Njengoba kufanele kube, i-100% ikhonjiswe ngentambo ye- "Total". Isabelo sezimpahla kulindeleke kwikholomu kusuka kokukhudlwana kuya kokuncane.



8. Manje kufanele sidale ikholamu lapho isabelo esiqoqiwe esinemiphumela esikhula sizokhonjiswa khona. Lokho wukuthi, kulayini ngamunye kunesisindo esithile somkhiqizo othile kuzongezwa ingxenye yayo yonke leyo mikhiqizo etholakala ohlwini olungenhla. Kwezimpahla zokuqala ohlwini (Umkhiqizo 3), ingxenye ngayinye kanye nesabelo esiqongelelwe kuzolingana, kepha konke okulandelayo ku-Inkomba yomuntu ngamunye kuzodinga ukwengeza isabelo esinqunyelwe sohlu lwangaphambilini lohlu.

   Ngakho-ke, emugqeni wokuqala sidluliselwa kukholamu "Aqoqiwe Abelana" inkomba yekholomu "ethize".



9. Okulandelayo, setha isikhombisi esitokisini sesibili sekholomu "yesabelo esinqwabelene". Lapha kufanele sisebenzise ifomula. Sibeka isibonakaliso esithi "silingana" futhi sigoqe okuqukethwe kweseli "Ingxenye" ​​yomugqa ofanayo nokuqukethwe kweseli "aqoqiwe isabelo" kusuka entanjeni engenhla. Zonke izinkomba zigcinelwe isihlobo, okungukuthi, asikhiqizi noma yikuphi ukukhwabanisa nabo. Ngemuva kwalokho, yenza ngokuchofoza inkinobho ethi Faka ukubonisa umphumela wokugcina.



10. Manje udinga ukukopisha le formula kumaseli wale kholamu, ebekwe ngezansi. Ukuze senze lokhu, sisebenzisa umaki wokugcwalisa esivele siphendukele kuwo lapho sikopisha ifomula kwikholomu yokwabelana. Ngasikhathi sinye, iString "isiyonke" ayidingekile, ngoba umphumela oqoqiwe u-100% uzoboniswa kumkhiqizo wokugcina ohlwini. Njengoba ubona, zonke izakhi zekholamu yethu ngemuva kwazo zagcwala.



11. Ngemuva kwalokho, dala iqembu "lekholamu". Sizodinga ukuhlanganisa izimpahla ngesigaba A, B no-C ngokokwabelana okucacisiwe okucacisiwe. Njengoba sikhumbula, zonke izinto zisatshalaliswa ngamaqembu ngokuya ngohlelo olulandelayo:
    • A - kuze kube ngu-80%;
    • B - okulandelayo 15%;
    • C - ama-5% asele.

    Ngakho-ke, zonke izimpahla ezaziqoqiwe zesisindo esithile esiphakathi komngcele ofinyelela kuma-80%, sinikeza isigaba A. Izimpahla eziqongelelwe kusuka ku-80% kuya ku-95% abelwe esigabeni B. okusele Iqembu lezimpahla elinenani lama-95% okuqongelela okuqondile okuqongelelwe sinikeza isigaba C.



12. Ukuze kucacelwe, ungagcwalisa amaqembu acacisiwe anemibala ehlukene. Kepha kuyintando.

Ngakho-ke, saphula izakhi ezingeni lokubaluleka, sisebenzisa ukuhlaziywa kwe-ABC. Lapho usebenzisa amanye amasu, njengoba kushiwo ngenhla, kusetshenziselwa ukweqa inani elikhudlwana lamaqembu, kepha umgomo wokuphuka kuleli cala uhlala ucishe washintshiwe.

Isifundo: Ukuhlunga nokuhlunga ku-Excel

Indlela 2: Usebenzisa ifomula eyinkimbinkimbi

Vele, ukusetshenziswa kokuhlunga kuyindlela ejwayelekile kakhulu yokwenza ukuhlaziywa kwe-ABC ku-Efpelle. Kepha kwezinye izikhathi kuyadingeka ukufeza lokhu kuhlaziywa ngaphandle kwemigqa ehleliwe ezindaweni ezisetafuleni lomthombo. Kulokhu, ifomula eliyinkimbinkimbi lizosiza. Isibonelo, sizosebenzisa itafula lomthombo elifanayo njengasesimweni sokuqala.

1. Sifaka etafuleni lomthombo eliqukethe igama lezimpahla kanye nemali engenayo evela ekuthengisweni kwalowo nalowo, iqembu "lelo". Njengoba ukwazi ukubona, kulokhu, angeke sengeze amakholomu ngokubalwa kwezingxenyana zabantu nangobuncumo.



2. Sikhiqiza ukwabiwa kwamaseli okuqala kwikholamu yeqembu, ngemuva kwalokho wenza ngokuchofoza inkinobho ethi "Faka umsebenzi" eduzane nomugqa wefomula.



3. I-Masters activation yemisebenzi. Sithuthela esigabeni "izixhumanisi kanye nama-arrays". Khetha umsebenzi we- "Choice". Sichofoza inkinobho ethi "Kulungile".



4. Iwindi lokuphikisana lomdlalo liyasebenza. I-syntax yethulwe kanjena:

   = Ukukhetha (Inombolo_intex; I-Value1; I-Value2; ...)

   Umsebenzi walesi sici ukuhoxiswa kwenye yamanani acacisiwe, kuya ngenombolo yenkomba. Inani lamanani angafinyelela ku-254, kepha sizodinga amagama amathathu kuphela ahambelana nezigaba zokuhlaziywa kwe-ABC: a, B, C. Singangena ngokushesha "uphawu" ensimini "b "Insimu, insimu" Inani3 "-" C ".



5. Kepha ngengxabano "inombolo ye-Index" kuzodingeka ukuthi ifakwe kahle ngokwakha opharetha abaningana. Faka ikhesa enkombeni ethi "Inombolo Yenombolo Yenombolo". Okulandelayo, chofoza isithonjana ngombono wonxantathu, ngakwesobunxele senkinobho ethi "Incsunction function". Uhlu lwama-opharetha asanda kusetshenziswa luvulwa. Sidinga umsebenzi wokusesha. Njengoba kungekho ohlwini, bese-ke sichofoza kulowo obhaliwe "eminye imisebenzi ...".



6. Iwindi lemisebenzi wizard windows aqalwa futhi. Futhi, iya esigabeni "izixhumanisi kanye nama-arrays". Sithola isikhundla se- "Board Board", sabela phansi futhi sichofoze inkinobho ethi "KULUNGILE".



7. Izimpikiswano eziphikisayo zokusesha ziyavuleka. I-syntax yayo inefomu elilandelayo:

   = Ibhodi Lokucinga (search_name; ukubuka__masive; hlobo_station)

   Inhloso yalo msebenzi ukuchaza inombolo yesikhundla yento ebekiwe. Okusho ukuthi, nje lokho esikudingayo kwe- "Index Number" field kucacisa ukukhetha.

   Ensimini "yohlu lwe-array", ungabuza ngokushesha le nkulumo elandelayo:

   {0: 0.8: 0,95}

   Kufanele kube ngabakaki abangenayo, njengefomula yamalungu afanayo. Akunzima ukuqagela ukuthi lezi zinombolo (0; 0.8; 0.95) zisho imingcele yokwabelana okuqoqiwe phakathi kwamaqembu.

   Inkambu "yohlobo lokuqhathanisa" ayiphoqelekile futhi kulokhu ngeke sikugcwalise.

   In the "Inani Lesibili", setha isikhombisi. Okulandelayo futhi ngesithonjana esichazwe ngenhla ngesimo sonxantathu, sithuthela emjovo.



8. Lesi sikhathi emisebenzini wizard, senza ukufudukela esigabeni "Mathematical". Khetha igama elithi "athula" bese ucindezela inkinobho ethi "Kulungile".



9. Kuzokwethulwa iwindi lokuphikisana nalo. I-opharetha ebekiwe ifingqa amaseli ahlangabezana nesimo esichazayo. I-syntax yayo yile:

   = Buthule (ibanga; ubumbano; ranga_suming)

   In the "Range" field, faka ikheli le- "Revenue Revenue" Column. Ngalezi zinhloso, sibeka isikhombisi endle, bese ngokwenza isiqeshana senkinobho yegundane kwesobunxele, khetha wonke amaseli wekholomu ehambisanayo, ngaphandle kwenani "eliphelele". Njengoba ubona, ikheli livele ngokushesha ensimini. Ngaphezu kwalokho, kudingeka senze lesi sixhumanisi esiphelele. Ukuze wenze lokhu, khiqiza ukwabiwa kwawo bese uchofoza kukhiye we-F4. Ikheli lakhishwa yizimpawu zedola.

   Ensimini ethi "Criterion", kudingeka sisekele isimo. Faka inkulumo elandelayo:

   ">"&

   Kwathi ngokushesha nje lapho sifaka ikheli leseli lokuqala lekholomu "yemali engenayo". Senza izixhumanisi ezivundlile kuleli kheli eliphelele, engeza uphawu lwedola kusuka kukhibhodi phambi kwencwadi. Izixhumanisi eziqonde mpo yeka isihlobo, okungukuthi, akufanele kube khona uphawu phambi kwedijithi.

   Ngemuva kwalokho, asicindezeli inkinobho ethi "KULUNGILE", bese uqhafaza egameni lomsebenzi wokusesha entanjeni yefomula.



10. Ngemuva kwalokho sibuyela ewindini lezimpiko lebhodi lokusesha. Njengoba sibona, enkombeni ye- "Fonceral enencazelo", imininingwane ebekwe ngu-opharetha yathula. Kepha lokho akukhona konke. Iya kule nkambu bese usenezela kwimininingwane etholakalayo ukwengeza uphawu "+" ngaphandle kwezingcaphuno. Ngemuva kwalokho sethula ikheli leseli lokuqala lekholomu "yemali engenayo". Futhi futhi senza izixhumanisi ngokuqondile nge-ABSOLULE, futhi zibheke ngokuqondile isihlobo.

    Okulandelayo, sithatha konke okuqukethwe kwensimu "inani elifunekayo" kubakaki, ngemuva kwalokho sibeka uphawu lokwehlukaniswa ("/"). Ngemuva kwalokho, ngesithonjana sikanxantathu, iya kusebenze umsebenzi wokukhetha.



11. Njengokugcina ngesikhathi sokusebenza komsebenzi wewizadi, sifuna opharetha okudingekayo esigabeni "Mathematical". Kulokhu umsebenzi owuthandayo ubizwa nge- "sums". Sigqagqa kancane bese ucindezela inkinobho ethi "Kulungile".



12. Kuvulwa iwindi lezimpikiswano lomqhubi. Inhloso yayo eyinhloko ukubizwa kwedatha kumaseli. I-syntax yalolu hlelo lokusebenza ilula impela:

    = Amanani (inombolo1; inombolo2; ...)

    Ngezinhloso zethu, kuphela insimu "yenombolo1" izodingeka. Singenisa kuwo izixhumanisi ze- "Revenue" Column, ukuqeda iseli equkethe imiphumela. Sesivele siyenzile ukuhlinzwa okunjalo ku-"Range" emkhakheni womsebenzi. Njengoba ngaleso sikhathi, izixhumanisi zangabuhla ziphelele, zizigqabisisa futhi zicindezela ukhiye we-F4.

    Ngemuva kwalokho, chofoza ukhiye we- "OK" ezansi kwewindi.



13. Njengoba ubona, okuyinkimbinkimbi yemisebenzi efakiwe yenze ukubala futhi kwakhipha umphumela kwiseli lokuqala lekholamu "iqembu". Umkhiqizo wokuqala wabelwa iqembu "A". Ifomula ephelele esetshenziswe yithi ngalesi sibalo imi ngalendlela elandelayo:

    = Ukukhetha (ibhodi lokusesha ((u-aunt ($ b $ 2: $ B $ 27; ">" $ B2) + $ B2) / amanani ($ b $ 27); $ B $ 27); , 95}); "A"; B ";" C ")

    Kepha-ke, esimweni ngasinye, izixhumanisi ezikule formula zizohluka. Ngakho-ke, ngeke kubhekwe njengendawo yonke. Kepha, usebenzisa le ncwajana enikezwe ngenhla, ungafaka izixhumanisi zanoma yiliphi ithebula bese usebenzisa le ndlela kunoma yisiphi isimo kunoma yisiphi isimo.



14. Kodwa-ke, lokhu akubona bonke. Sibale kuphela umugqa wokuqala wetafula. Ukuze ugcwalise ngokuphelele ikholomu ye- "Group" yeqembu, udinga ukukopisha le formula ebangeni elingezansi (ukuqeda iseli lentambo "isiyonke") usebenzisa umaki wokugcwalisa, njengoba sesivele senze okungaphezulu kwesisoma. Ngemuva kokuthi idatha yenziwe, ukuhlaziywa kwe-ABC kungabhekwanziwa kwenziwe.

Njengoba sibona, imiphumela etholwe kusetshenziswa inketho isebenzisa ifomula eyinkimbinkimbi ahlukile kwaleyo miphumela esayenziwa ngokuhlelwa. Zonke izimpahla zinikezwa izigaba ezifanayo, ngasikhathi sinye kuphela lapho imigqa ingazange iguqule isikhundla sayo sokuqala.

Isifundo: I-Master of Imisebenzi ku-Asuka

Uhlelo lwe-Excel luyakwazi ukunciphisa kakhulu ukuhlaziywa kwe-ABC komsebenzisi. Lokhu kutholakala ngokusebenzisa ithuluzi elinjalo njengokuhlelwa. Ngemuva kwalokho, amandla adonsela phansi athile adonsela phansi, isabelo esinqwabelene futhi, empeleni, ukuhlukaniswa kwamaqembu kubalwa. Ezimweni lapho ushintsho esimweni sokuqala semigqa etafuleni aluvunyelwe, ungasebenzisa indlela usebenzisa ifomula eyinkimbinkimbi.