SEARCH umsebenzi ku-Microsoft Excel

Omunye wabasebenza ngokuthandwa kakhulu phakathi kwabasebenzisi be-Excel umsebenzi SESHA. Imisebenzi yakhe ifaka ukunquma inani lesikhundla sento ekuhlelweni kwedatha okunikeziwe. Kuletha inzuzo enkulu kakhulu uma isetshenziswa ihlanganiswa nabanye abaqhubi. Ake sibheke ukuthi yini eyakha umsebenzi. SESHA, nokuthi singasetshenziswa kanjani ekusebenzeni.

Ukusetshenziswa kwe-opharetha SEARCH

Umsebenzi SESHA kungokwesigaba semisebenzi Izinkomba kanye nokufika. Ibheka into ebekiwe kuhlu oluthile olucacisiwe nezinkinga kuseli elihlukile inombolo yezikhundla zalo kulolu banga. Empeleni, ngisho negama layo libonisa lokhu. Futhi, lo msebenzi, uma usetshenziswa ngokuhlanganiswa nabanye opharetha, ubatshela inombolo yenombolo yento ethile yokucubungula okwalandela kwaleyo datha.

ISyntax yokusebenza SESHA kubukeka kanjena:

= SEARCH (search_value; lookup_array; [match_type])

Manje cabanga ngalunye kulezi zingxabano ezintathu ngokwahlukana.

"Ukufuna inani" - Lesi yisakhi okufanele sitholakale. Kungaba nefomu lombhalo, elezinombolo, futhi futhi kuthathe inani elinengqondo. Inkomba yeseli equkethe noma yimaphi amanani angenhla ingasebenza futhi njengale ngxabano.

Kubukwa u-Array likheli lobubanzi lapho inani losesho litholakala khona. Kuyisikhundla sale nto ku-array lapho opharetha okufanele banqume SESHA.

Uhlobo Lomdlalo kubonisa umdlalo oqondile ongawufuna noma ongalungile. Le ngxabano ingaba nezincazelo ezintathu: "1", "0" futhi "-1". Inani "0" opharetha bafuna kuphela umdlalo oqondile. Uma inani lichaziwe "1", lapho kungekho umdlalo oqondile SESHA ibuyisa into esondele kuyo ngokuhleleka. Uma inani lichaziwe "-1", khona-ke uma kungatholakali umdlalo oqondile, umsebenzi ubuyisela into esondele kakhulu kuyo ngokukhuphuka kwe-oda. Kubalulekile uma ungafuni inani eliqondile, kepha inani elilinganisiwe ukuze ifomethi oyibukayo ihlelwe ngokukhuphuka (uhlobo lokufana "1") noma ukwehla (uhlobo lomdlalo "-1").

Ukuxabana Uhlobo Lomdlalo akufuneki. Kungashiywa uma kungadingeki. Kulokhu, inani layo elizenzakalelayo "1". Faka isicelo sokuphikisana Uhlobo LomdlaloOkokuqala, kunengqondo kuphela lapho kusetshenziswa amanani wokubala, hhayi awombhalo.

Uma kwenzeka SESHA kuzilungiselelo ezibekiwe azikwazi ukuthola into oyifunayo, opharetha ukhombisa iphutha esitokisini "# N / A".

Lapho wenza usesho, opharetha akahlukanisi phakathi kwamarejista amacala. Uma kunemidlalo eminingana eqondile kumalungu afanayo, ke SESHA ikhombisa isikhundla sabo bokuqala esitokisini.

Indlela yoku-1: bonisa indawo yento kuluhlu lwedatha yombhalo

Ake sibheke isibonelo secala elilula uma usebenzisa SESHA Unganquma indawo yento ebekiwe ngokulandelana kwemininingwane yombhalo. Sithola ukuthi lelo gama lihlala kuliphi ibanga lapho amagama womkhiqizo akhiwe khona Ushukela.

1. Khetha iseli lapho umphumela olandelwayo uzokhonjiswa khona. Chofoza isithonjana "Faka umsebenzi" eduze komugqa wamafomula.



2. Ukuqala Umsebenzi Wizards. Isigaba esivulekile "Uhlu oluphelele lwe-alfabhethi" noma Izinkomba kanye nokufika. Uhlu lwabasebenza lapho sifuna igama "FUNA". Ngemuva kokuthola futhi ukugqamisa, chofoza inkinobho "Kulungile" phansi kwewindi.



3. Iwindi le-Operator Argument Lasebenza SESHA. Njengoba ubona, kuleli windi, ngenombolo yezimpikiswano, kunezinkambu ezintathu. Kufanele siwagcwalise.

   Njengoba sidinga ukuthola isikhundla segama Ushukela ebangeni, bese ushayela leli gama enkambeni "Ukufuna inani".

   Ensimini Kubukwa u-Array udinga ukucacisa izixhumanisi zobubanzi uqobo. Ungayishayela ngesandla, kepha kulula ukusetha isikhombisi esigangeni bese ukhetha le nhlu eshidini, ngenkathi ubambe inkinobho yegundane kwesobunxele. Ngemuva kwalokho, ikheli layo lizokhonjiswa ewindini lokuphikisana.

   Ensimini yesithathu Uhlobo Lomdlalo faka inombolo "0", ngoba sizosebenza nedatha yombhalo, futhi ngenxa yalokho sidinga umphumela onembile.

   Ngemuva kokuthi yonke imininingwane ifakiwe, chofoza inkinobho "Kulungile".



4. Uhlelo lwenza ukubala futhi lukhombisa inombolo ye-serial yesikhundla Ushukela kuhlu olukhethiwe esitokisini esaluchaze esinyathelweni sokuqala salo myalo. Inombolo yesikhundla izolingana "4".

Isifundo: I-Excel Feature Wizard

Indlela yesi-2: ukusebenzisa kwe-othomathikhi kohlelo lokusebenzisa opharetha

Ngaphezulu, sihlolisise icala lokuqala lokusebenzisa opharetha SESHAkepha noma ikwazi ukuzenzakalelayo.

1. Ukuze kube lula, engeza ezinye izinkambu ezimbili ezingeziwe kushidi: Isimo futhi "Inombolo". Ensimini Isimo shayela egameni olidingayo. Manje makube njalo Inyama. Ensimini "Inombolo" setha ikhesa bese uya ewindini lezimpikiswano zesisebenzisi ngendlela efanayo naleyo ekhulunywe ngayo ngenhla.



2. Ewindini yokuphikisana komsebenzi, enkundleni "Ukufuna inani" khombisa ikheli leseli lelo gama ebhalwe ngalo Inyama. Emasimini Kubukwa u-Array futhi Uhlobo Lomdlalo chaza idatha efanayo nasendleleni yangaphambilini - ikheli lebanga nenombolo "0" ngokufanele. Ngemuva kwalokho chofoza inkinobho "Kulungile".



3. Ngemuva kokuthi senze isenzo esingenhla, enkundleni "Inombolo" isikhundla segama sizoboniswa Inyama ebangeni elikhethiwe. Kulokhu, lilingana "3".



4. Le ndlela yinhle ukuthi uma sifuna ukuthola isikhundla sanoma yiliphi elinye igama, asizukudinga ukuphinda sikhiphe noma siguqule ifomula ngaso sonke isikhathi. Elula ngokwanele enkundleni Isimo faka igama elisha lokusesha esikhundleni seledlule. Iyacubungula futhi ikhishwe umphumela ngemuva kwalokhu okuzokwenzeka ngokuzenzakalelayo.

Indlela 3: Sebenzisa opharetha we-FIND ngezinkulumo ezinombolo

Manje ake sibheke ukuthi ungasebenzisa kanjani SESHA ukusebenza ngezinkulumo ezinombolo.

Umsebenzi ukuthola izimpahla ngenani lokuthengisa kwama-ruble angama-400 noma okusondele kakhulu kuleli nani ngokukhuphuka kwe-oda.

1. Okokuqala, kudingeka sihlele izinto ezikukholamu "Inani" ngokuhleleka. Khetha le kholomu bese uya kuthebhu "Ikhaya". Chofoza isithonjana Hlunga nesihlungietholakala etheyiphu emabhulokini "Ukuhlela". Kuluhlu olubonakala, khetha "Hlunga kusuka kwesilinganiso esiphezulu kuya kwesincane".



2. Ngemuva kokuthi ukuhlunga sekwenziwe, khetha iseli lapho umphumela uzokhonjiswa khona, bese wethula iwindi lokuphikisana ngendlela efanayo obekuxoxwa ngayo ngendlela yokuqala.

   Ensimini "Ukufuna inani" shayela ngenombolo "400". Ensimini Kubukwa u-Array chaza izixhumanisi zekholomu "Inani". Ensimini Uhlobo Lomdlalo inani elisethiwe "-1", ngoba sesha amanani alinganayo noma amakhulu kunosesho. Ngemuva kokuqeda zonke izilungiselelo, chofoza inkinobho "Kulungile".



3. Umphumela wokucubungula uboniswa kuseli elicacisiwe ngaphambili. Lesi yisikhundla. "3". Ihambelana naye "Izambane". Ngempela, inani lemali elivela ekuthengisweni kwalo mkhiqizo lisondele kakhulu kunombolo 400 ngokukhuphuka kwemali futhi lifinyelela kuma-ruble angama-450.

Ngokufanayo, ungasesha isikhundla esiseduzane "400" ngokuhleleka. Kuphela kuphela kwalokhu udinga ukuhlunga idatha ngokuhleleka okwenyukayo, nasensimini Uhlobo Lomdlalo inani lokuphikisana okusethiwe "1".

Isifundo: Hlunga futhi uhlunge idatha ku-Excel

Indlela 4: sebenzisa ngokuhlangana nabanye opharetha

Kusebenza kakhulu ukusebenzisa lo msebenzi nabanye opharetha njengengxenye yefomula eyinkimbinkimbi. Isikhathi esiningi isetshenziswa ngokuhlangana nomsebenzi INDEX. Le ngxabano ikhombisa okuqukethwe uhla oluchazwe umugqa wayo noma inombolo yekholamu kuseli elicacisiwe. Ngaphezu kwalokho, ukubalwa, maqondana opharetha SESHA, ayenziwa engahlobene nalo lonke ishidi, kodwa kuphela kububanzi. I-syntax yalomsebenzi imi kanje:

= INDEX (uhlu; umugqa_unombolo; ikholomu_unombolo)

Ngaphezu kwalokho, uma uhlu luhlangene-olulodwa, ungasebenzisa eyodwa kuphela yezimpikiswano ezimbili: Inombolo yomugqa noma Inombolo Yekholomu.

Isici seSici seSici INDEX futhi SESHA ilele eqinisweni lokuthi elokugcina lingasetshenziswa njengengxabano yokuqala, okungukuthi, bonisa isikhundla somugqa noma ikholamu.

Ake sibheke ukuthi lokhu kungenziwa kanjani ekusebenzeni kwetafula lonke. Umsebenzi wethu ukubonisa emkhakheni owengeziwe weshidi "Umkhiqizo" igama lomkhiqizo, inani eliphelele lemali evela kulo ama-ruble angama-350 noma eliseduze kakhulu kwaleli nani ekuhlelweni kwe-oda. Le ngxabano icacisiwe endle. "Inani elifanelekile lemali ngeshidi ngalinye".

1. Hlunga izinto kwikholamu "Imali Yengeniso" ekhuphuka. Ukuze wenze lokhu, khetha ikholomu edingekayo futhi, ekuthebhu "Ikhaya"chofoza isithonjana Hlunga nesihlungi, bese kumenyu evela, chofoza entweni "Hlunga kusuka kobuncane kuya kobukhulu".



2. Khetha iseli enkambu "Umkhiqizo" futhi ashaye ucingo Isici Wizard ngendlela ejwayelekile ngokusebenzisa inkinobho "Faka umsebenzi".



3. Ewindini elivulayo Umsebenzi Wizards esigabeni Izinkomba kanye nokufika ngifuna igama INDEX, ikhethe bese uchofoza inkinobho "Kulungile".



4. Okulandelayo, kuvulwa iwindi elinikeza inketho yezinketho zo-opharetha INDEX: nge-array noma ireferensi. Sidinga inketho yokuqala. Ngakho-ke, sishiya kule windi zonke izilungiselelo ezizenzakalelayo bese uchofoza inkinobho "Kulungile".



5. Iwindi lokuphikisana ngomsebenzi liyavula INDEX. Ensimini Array chaza ikheli lebanga lapho opharetha asebenza khona INDEX izofuna igama lomkhiqizo. Kithi, lokhu kuyikholomu "Igama lomkhiqizo".

   Ensimini Inombolo yomugqa umsebenzi owakhiwe uzotholakala SESHA. Kuzodingeka ukuthi kuqhutshwe ngesandla ngokusebenzisa isi-syntax esishiwo ekuqaleni kwesihloko. Qopha ngokushesha igama lomsebenzi - "FUNA" ngaphandle kwezingcaphuno. Ngemuva kwalokho vula ubakaki. Impikiswano yokuqala yalokhu ku-opharetha ukuthi "Ukufuna inani". Itholakala eseshidi enkundleni "Isilinganiso semali engenayo". Chaza izixhumanisi zeseli eziqukethe inombolo 350. Sibeka i-semicolon. Ingxabano yesibili ngukuthi Kubukwa u-Array. SESHA izobheka uhla lapho inani lemali likhona futhi libheke eyodwa esondele kakhulu kuma-ruble angama-350. Ngakho-ke, kulokhu, chaza izixhumanisi zekholomu "Imali Yengeniso". Siphinde sibeka i-semicolon. Ingxabano yesithathu Uhlobo Lomdlalo. Njengoba sizofuna inombolo elingana naleyo inikezwe noma encane kakhulu, sibeka inombolo lapha "1". Sivalela amabakaki.

   Ukuphikisana kwesithathu emsebenzini INDEX Inombolo Yekholomu shiya kungenalutho. Ngemuva kwalokho chofoza inkinobho "Kulungile".



6. Njengoba ukwazi ukubona, umsebenzi INDEX usebenzisa opharetha SESHA esitokisini esichazwe ngaphambilini sibonisa igama Tea. Ngempela, inani elivela ekuthengisweni kwetiyi (ama-ruble angama-300) liseduze kakhulu nokwehla kuya inani lama-ruble angama-350 kuwo wonke amanani asetafuleni elicubungwayo.



7. Uma siguqula inombolo ebaleni "Isilinganiso semali engenayo" kuya kokunye, lapho okuqukethwe insimu kuzolandiswa ngokuzenzakalelayo ngakho "Umkhiqizo".

Isifundo: INDEX umsebenzi e-Excel

Njengoba ukwazi ukubona, opharetha SESHA kuwumsebenzi olula kakhulu wokunquma inombolo yokulandelana yento ethile echazwe kumininingwane yedatha. Kepha izinzuzo zako zanda kakhulu uma zisetshenziswa kumafomula ayinkimbinkimbi.