Thola umsebenzi ku-Microsoft Excel

Omunye wabasebenzisi abafunwa kakhulu phakathi kwabasebenzisi be-Excel ngumsebenzi MATCH. Umsebenti wayo ukucacisa inombolo yenombolo yesici kunombolo yedatha enikeziwe. Kuletha inzuzo enkulu kakhulu uma isetshenziselwa ngokuhlanganyela namanye opharetha. Ake sibone ukuthi umsebenzi ungubani MATCHnokuthi ingasetshenziswa kanjani ekusebenzeni.

ISICELO SOMSEBENZI WOMSEBENZI

I-Operator MATCH kungokwesigaba semisebenzi "Izixhumanisi nezigaba". Ifuna isici esicacisiwe kwisigaba esicacisiwe futhi sibhekene nenombolo yaso yokubeka kulesi sigaba kuseli elihlukile. Empeleni, ngisho negama layo libonisa lokhu. Futhi, uma isetshenziselwa ngokubambisana namanye ama-operators, lo msebenzi uwazisa ngenombolo yesikhundla sento ethize yokucubungula kwalokhu kwedatha.

I-syntax ye-Operator MATCH kubukeka kanje:

= MATCH (inani lokusesha; ukulandelana kokulinganisa; [match_type])

Manje cabanga ngalunye lwezimpikiswano ezintathu ngokuhlukile.

"Inani elifunwa" - Lesi yisici okufanele sitholakale. Kungaba nombhalo, ifomu lezinombolo, futhi futhi kuthathe inani elinengqondo. Le mpikiswano ingabuye ibe yinkomba egciniwe equkethe noma yimaphi amanani angenhla.

"Uhlu olubukiwe" ikheli lebanga lapho inani likhona khona. Isikhundla salesi sici kulolu hlu lapho opharetha kufanele achaze khona. MATCH.

"Uhlobo Lokumaka" ibonisa umdlalo oqondile wokufuna noma ongalungile. Le mpikiswano ingaba namanani amathathu: "1", "0" futhi "-1". Uma "0" Umqhubi ubheka kuphela umdlalo oqondile. Uma inani liyi "1", uma kungekho mdlalo oqondile MATCH unikeza isici esiseduze kuso ngokuhlehlisa. Uma inani liyi "-1", ke uma kungatholakali umdlalo oqondile, umsebenzi ubuyisela isici esiseduze nayo ekukhuphukeni. Kubalulekile uma ungafuni inani eliqondile, kodwa okulinganayo, ukuze uhlu olubukayo luzolethwa ngokunyuka kwe-oda (uhlobo lokufanisa "1") noma wehla (uhlobo lwemephu "-1").

Ukuphikisana "Uhlobo Lokumaka" akudingeki. Kungase kungabanjwanga uma kungadingeki. Kulesi simo, inani layo elizenzakalelayo liyi "1". Faka isicelo sokuphikisana "Uhlobo Lokumaka"Okokuqala, kunengqondo kuphela lapho inani lamanani lucutshungulwa, hhayi amanani ombhalo.

Uma kwenzeka MATCH nezilungiselelo ezicacisiwe azikwazi ukuthola into oyifunayo, opharetha ubonisa iphutha esitokisini "# N / A".

Uma uqhuba ucingo, opharetha akahlukanisi phakathi kwamarejistreli abalingiswa. Uma kunemifino eminingana eqondile ohlwini, MATCH ibonisa isimo seyokuqala esitokisini.

Indlela 1: Khombisa indawo yento esebangeni lwedatha yombhalo

Ake sibheke isibonelo secala elilula, uma usebenzisa MATCH Ungakwazi ukunquma indawo yezinto ezicacisiwe kuhlu lwemininingwane yombhalo. Thola ukuthi yikuphi isikhundla ebangeni lapho amagama wezimpahla, igama "Ushukela".

1. Khetha iseli lapho umphumela ohanjisiwe uzoboniswa khona. Chofoza kusithonjana "Faka umsebenzi" eduze kwe-bar bar.



2. Qalisa Amakhosi omsebenzi. Vula isigaba "Uhlu oluphelele lwe-alfabhethi" noma "Izixhumanisi nezigaba". Kuhlu lwabo opharetha sifuna igama "QHUBA". Ukuthola nokuyikhetha, chofoza inkinobho. "Kulungile" phansi kwefasitela.



3. Iwindi lempikiswano ye-Operator ivuliwe. MATCH. Njengoba ungabona, kule festile ngenani lenombolo yezimpikiswano kunezinkambu ezintathu. Kufanele sikugcwalise.

   Njengoba kudingeka sithole isikhundla segama "Ushukela" ebangeni, bese uqhuba leli gama ensimini "Inani elifunwa".

   Ensimini "Uhlu olubukiwe" udinga ukucacisa izixhumanisi zobubanzi ngokwayo. Kungenziwa ukuqhutshwa ngesandla, kodwa kulula ukubeka isikhombisi endle bese ukhetha lokhu okushiwo eshadini ngenkathi ugoqa inkinobho yegundane lesokunxele. Emva kwalokho, ikheli layo liboniswa efasiteleni lezingxoxo.

   Ensimini yesithathu "Uhlobo Lokumaka" ufake inombolo "0", ngoba sizosebenza ngedatha yombhalo, ngakho-ke sidinga umphumela oqondile.

   Ngemuva kokuthi yonke idatha isethiwe, chofoza inkinobho. "Kulungile".



4. Uhlelo lwenza ukubala futhi lubonise isikhundla se-ordinal "Ushukela" ohlwini olukhethiwe esitokisini esacacise esiteji sokuqala sale myalelo. Inombolo yesikhundla izolingana "4".

Isifundo: I-Excel umsebenzi wizard

Indlela yesi-2: Yenza okuzenzakalelayo ukusetshenziswa komsebenzisi we-MATCH

Ngenhla, sesicubungule icala lokuqala lokusebenzisa opharetha MATCH, kodwa ngisho kungenziwa okuzenzakalelayo.

1. Ukuze kube lula, sengeza amasimu amabili engeziwe eshidi: "Beka iphuzu" futhi "Inombolo". Ensimini "Beka iphuzu" sishayela egameni elidinga ukutholakala. Ake kube manje "Inyama". Ensimini "Inombolo" setha isikhombisi bese uya efasiteleni lama-opharetha opharetha ngendlela efanayo esayikhuluma ngayo ngenhla.



2. Ebhokisini lokuphikisana lomsebenzi ensimini "Inani elifunwa" ucacise ikheli leseli lapho igama langena khona "Inyama". Emasimini "Uhlu olubukiwe" futhi "Uhlobo Lokumaka" sibonisa idatha efanayo njengendlela yangaphambilini - ikheli lobubanzi nenombolo "0" ngokulandelana. Emva kwalokho, chofoza inkinobho "Kulungile".



3. Ngemuva kokuthi senze izenzo ezingenhla, ensimini "Inombolo" isikhundla segama siboniswa "Inyama" ebangeni elikhethiwe. Kulesi simo, kunjalo "3".



4. Le ndlela inhle ngoba uma sifuna ukwazi isikhundla senye igama, ngakho-ke ngeke sidinge ukuthayipha kabusha noma ukushintsha ifomula isikhathi ngasinye. Kuvele nje ensimini "Beka iphuzu" faka igama elisha lokusesha esikhundleni salowo owedlule. Ukucubungula nokudilizwa komphumela ngemuva kwalokhu kuzokwenzeka ngokuzenzekelayo.

Indlela 3: Sebenzisa umqhubi we-MATCH ngezinkulumo zezinombolo

Manje ake sibheke ukuthi ungayisebenzisa kanjani MATCH ukusebenza nezinkulumo zezinombolo.

Umsebenzi ukuthola umkhiqizo owufanele ama-ruble angu-400 noma oseduze nalesi samba ngokukhuphuka.

1. Okokuqala, sidinga ukuhlunga izinto kukholomu "Inani" wehla. Khetha le kholomu bese uya kuthebhu "Ikhaya". Chofoza kusithonjana "Hlunga futhi uhlunge"esitholakala ku-tape ku-block Ukuhlela. Ohlu oluvela, khetha into "Hlunga kusuka phezulu kuze kube ngaphansi".



2. Ngemuva kokuthi ukuhlelwa kwenziwe, khetha iseli lapho umphumela uzoboniswa khona, futhi uqalise iwindi lempikiswano ngendlela efanayo echazwe ngendlela yokuqala.

   Ensimini "Inani elifunwa" sishayela ngenombolo "400". Ensimini "Uhlu olubukiwe" ucacise izixhumanisi zekholomu "Inani". Ensimini "Uhlobo Lokumaka" setha inani "-1"njengoba sifuna inani elilinganayo noma elikhulu kusuka kulowo oyifunayo. Ngemva kokwenza zonke izilungiselelo chofoza inkinobho "Kulungile".



3. Umphumela wokucubungula uboniswa kuseli elichazwe ngaphambili. Lesi yisimo "3". Ihambisana "Amazambane". Ngempela, inani lemali evela ekuthengisweni kwalo mkhiqizo liseduze nenombolo 400 ekukhuphukeni futhi lilinganiselwa kuma-ruble angu-450.

Ngokufanayo, ungasesha isikhundla esiseduze "400" wehla. Kuphela lokhu udinga ukuhlunga idatha ngokunyuka kwe-oda, nasensimini "Uhlobo Lokumaka" ukusebenza izimpikiswano zibeke inani "1".

Isifundo: Hlunga bese uhlunga idatha ku-Excel

Indlela 4: sebenzisa ngokuhlanganisana namanye opharetha

Lo msebenzi usebenza kahle kakhulu ukusebenzisa namanye ama-opharetha njengengxenye yefomula eyinkimbinkimbi. Ngokuvamile kuvame ukusetshenziswa ngokubambisana nomsebenzi INDEX. Le mpikiswano isuka esitokisini esicacisiwe okuqukethwe kobubanzi obucacisiwe ngenombolo yomugqa noma ikholomu yayo. Ngaphezu kwalokho, ukubalwa, njengokuphathelene nomqhubi MATCH, lwenziwa hhayi elihlobene neshidi lonke, kodwa kuphela ngaphakathi kwebanga. I-syntax yalo msebenzi ilandelayo:

= INDEX (i-array; umugqa_inombolo; ikholomu_nomboro)

Ngaphezu kwalokho, uma uhlu luyingxenye eyodwa, ingabe eyodwa kuphela yezimpikiswano ezimbili ingasetshenziswa: "Inombolo yomugqa" noma "Inombolo yekholomu".

Faka isikhwama semisebenzi INDEX futhi MATCH kungukuthi lokhu kungasetshenziswa njengengxabano yokuqala, okungukuthi, ukukhombisa isikhundla sogqa noma ikholomu.

Ake sibheke ukuthi lokhu kungenziwa kanjani ngokusebenza, usebenzisa yonke ithebula elifanayo. Umsebenzi wethu ukuletha ishidi elengeziwe "Umkhiqizo" igama lezimpahla, inani lemali engenayo elilingana nama-ruble angu-350 noma eliseduze nalesi xabiso ngokuhlehla. Le mpikiswano ichazwe ensimini. "Isamba semali yeshidi ngalinye".

1. Hlunga izinto kukholomu "Inani lemali engenayo" ukhuphuka. Ukuze wenze lokhu, khetha ikholomu edingekayo futhi, ekubeni kuthebhu "Ikhaya", chofoza kusithonjana "Hlunga futhi uhlunge"bese kwimenyu ebonakalayo chofoza into "Hlunga kusuka kokungenani kuya phezulu".



2. Khetha iseli emkhakheni "Umkhiqizo" futhi shayela Umsebenzi weWebhu ngendlela evamile ngokusebenzisa inkinobho "Faka umsebenzi".



3. Ewindini evula Amakhosi omsebenzi esigabeni "Izixhumanisi nezigaba" bheka igama INDEXukhethe bese uchofoza inkinobho "Kulungile".



4. Okulandelayo, iwindi livula ukuthi linikeza ukukhetha kokukhethwa kolayini. INDEX: for array noma for reference. Sidinga ukhetho lokuqala. Ngakho-ke, sisuka kule iwindi zonke izilungiselelo ezizenzakalelayo bese uchofoza inkinobho "Kulungile".



5. Iwindi lempikiswano yokusebenza ivula. INDEX. Ensimini "Uhlu" ucacise ikheli lebanga lapho opharetha khona INDEX uzosesha igama lomkhiqizo. Esimweni sethu, lena ikholomu. "Igama lomkhiqizo".

   Ensimini "Inombolo yomugqa" Umsebenzi owakhiwe uzotholakala MATCH. Kuzodingeka iqhutshwe ngesandla usebenzisa i-syntax okubhekiswe kuyo ekuqaleni kwalesi sihloko. Masinyane bhala igama lomsebenzi - "QHUBA" ngaphandle izingcaphuno. Bese uvula ibakaki. Ukuphikisana kokuqala kwalokhu opharetha kuy "Inani elifunwa". Itholakala kushidi emkhakheni. "Isamba semali engenayo". Cacisa izixhumanisi zeseli equkethe inombolo 350. Sibeka i-semicolon. Iphikisana yesibili liyi "Uhlu olubukiwe". MATCH uzobuka ububanzi obunani lemali engenayo futhi ubheke ama-ruble angu-350 aseduzane. Ngakho-ke, kulokhu, sichaza izixhumanisi zekholomu "Inani lemali engenayo". Futhi sibeka i-semicolon. Ingxabano yesithathu ikhona "Uhlobo Lokumaka". Njengoba sizofuna inombolo elingana neyinikeziwe noma eyiseduze, sibeka inombolo lapha. "1". Vala amabakaki.

   Ukuphikisana komsebenzi wesithathu INDEX "Inombolo yekholomu" shiya kungenalutho. Emva kwalokho, chofoza inkinobho "Kulungile".



6. Njengoba ungabona, umsebenzi INDEX ngosizo lomsebenzisi MATCH esitokisini esicacisiwe ngaphambili sibonisa igama "Tea". Ngempela, inani lokudayiswa kweti (ama-ruble angu-300) lisondelene kakhulu nokuhlehlisa inani lama-ruble angu-350 kuwo wonke amanani asetafuleni asetshenziswe.



7. Uma sishintsha inombolo ensimini "Isamba semali engenayo" komunye, okuqukethwe komkhakha kuzokwenziwa kabusha ngokuzenzakalelayo. "Umkhiqizo".

Isifundo: Umsebenzi we-Excel ku-Excel

Njengoba ungabona, opharetha MATCH yinto elula kakhulu yokunquma inombolo yokulandelana kwento ecacisiwe kuhlu lwamanani. Kodwa inzuzo yalo iyanda kakhulu uma isetshenziselwa amafomula anzima.