Enye yezinto eziwusizo kakhulu wohlelo lwe-Excel wenethiwekhi ye-INDEX. Ibheka imininingwane ebangeni ekuxhumaneni komugqa nekholamu elishiwo, ibuyise umphumela kuseli elakhelwe ngaphambilini. Kepha amathuba aphelele alo msebenzi wembulwa lapho esetshenziswa kumafomula ayinkimbinkimbi ahlanganiswe nabanye abaqhubi. Ake sibheke izinketho ezahlukahlukene zokusebenzisa kwayo.
Isebenzisa umsebenzi we-INDEX
Umsebenzi INDEX kungokwesigaba sezinto ezenziwa kusigaba Izinkomba kanye nokufika. Inezinhlobo ezimbili: zama-arrays nezinkomba.
Inketho yokuhlelwa kabusha ine-syntax elandelayo:
= INDEX (uhlu; umugqa_unombolo; ikholomu_unombolo)
Ngasikhathi sinye, izimpikiswano ezimbili zokugcina kwifomula zingasetshenziswa zombili ngokuhlangene futhi nenye yazo, uma uhlu lusezingeni elilodwa. Ngobubanzi bemidiya eminingi, womabili amanani kufanele asetshenziswe. Kufanele futhi kubhekwe ukuthi uhla nenombolo yekholomu akuqondwa ukuthi iyinombolo ekuxhumaneni kweshidi, kodwa i-oda ngaphakathi kokuhlisiwe okucacisiwe uqobo.
I-syntax yenketho yesethenjwa imi ngalendlela elandelayo:
= INDEX (isixhumanisi; umugqa_unombolo; ikholamu_unombolo; [indawo_inhlaka])
Lapha, ngendlela efanayo, ungasebenzisa ingxabano eyodwa kokukabili: Inombolo yomugqa noma Inombolo Yekholomu. Ukuxabana "Inombolo Yendawo" ngokuvamile kuyakhethwa futhi kusetshenziswa kuphela lapho amabanga athile abandakanyeka ekusebenzeni.
Ngakho-ke, opharetha bafuna imininingwane ekuloba olubekiwe lapho ecacisa umugqa noma ikholamu. Lesi sici sifana kakhulu ne Umhlinzeki we-VLR, kepha ngokungafani nayo, ingasesha cishe yonke indawo, hhayi nje kwikholamu yakwesobunxele yetafula.
Indlela yoku-1: sebenzisa i-opharetha ye-INDEX yama-arrays
Okokuqala, ake sihlaziye opharetha usebenzisa isibonelo esilula INDEX ngokuhlelwa.
Sinethebula lomholo. Ikholamu lokuqala libonisa amagama abasebenzi, okwesibili libonisa usuku lokukhokha, kanti okwesithathu libonisa inani lemali etholwayo. Sidinga ukubonisa igama lesisebenzi kulayini wesithathu.
- Khetha iseli lapho umphumela wokucubungula uzokhonjiswa khona. Chofoza isithonjana "Faka umsebenzi", etholakala ngokushesha ngakwesobunxele sebha yefomula.
- Inqubo yokuqalisa iyaqhubeka Umsebenzi Wizards. Esigabeni Izinkomba kanye nokufika leli thuluzi noma "Uhlu oluphelele lwe-alfabhethi" ngifuna igama INDEX. Ngemuva kokuthola lo opharetha, khetha bese uchofoza inkinobho "Kulungile", etholakala ngaphansi kwewindi.
- Kuvulwa iwindi elincane lapho kufanele ukhethe khona eyodwa yezinhlobo zomsebenzi: Array noma Xhumanisa. Sidinga inketho Array. Itholakala kuqala futhi igqanyiswa ngokuzenzakalelayo. Ngakho-ke, kufanele nje chofoza inkinobho "Kulungile".
- Iwindi lokuphikisana ngomsebenzi liyavula INDEX. Njengoba kushiwo ngenhla, unezimpikiswano ezintathu, futhi ngenxa yalokho, izinkambu ezintathu okufanele ugcwalise ngazo.
Ensimini Array Kufanele uchaze ikheli lobubanzi bedatha elisebenzayo. Ingahanjiswa ngesandla. Kepha ukwenza lula umsebenzi, sizokwenza ngenye indlela. Beka isikhombisi ensimini efanelekile, bese ubiyela lonke uluhlu lwedatha ye-tabular eshidini. Ngemuva kwalokho, ikheli lohlu lizokhonjiswa ngokushesha endle.
Ensimini Inombolo yomugqa faka inombolo "3", ngoba ngesimo sidinga ukuthola igama lesithathu ohlwini. Ensimini Inombolo Yekholomu setha inombolo "1", ngoba ikholomu enamagama ingeyokuqala ebangeni elikhethiwe.
Ngemuva kokuthi wonke amasethingi abekiwe aqediwe, chofoza inkinobho "Kulungile".
- Umphumela wokucubungula uboniswa kuseli elikhonjiswe esigabeni sokuqala salo myalelo. Igama lesibongo elinqunyelwe ukuthi lingokwesithathu ohlwini ohlwini lwedatha olukhethiwe.
Sihlole ukusetshenziswa komsebenzi INDEX kumalungu afanayo amaningi (amakholomu amaningi nemigqa). Ukube uhla belungenxa eyodwa, ukugcwalisa idatha kuwindi lokuphikisana kungaba lula kakhulu. Ensimini Array ngendlela efanayo nale engenhla, sikhombisa ikheli laso. Kulokhu, ibanga ledatha liqukethe amanani kuphela kukholamu eyodwa. "Igama". Ensimini Inombolo yomugqa khombisa inani "3", ngoba udinga ukuthola imininingwane kusuka kumugqa wesithathu. Insimu Inombolo Yekholomu ngokuvamile, ungakushiya kungenalutho, ngoba sinokuhlukahluka kobubanzi lapho kusetshenziswa khona ikholamu eyodwa kuphela. Chofoza inkinobho "Kulungile".
Umphumela uzofana ncamashi nalokhu okungenhla.
Lesi kwakuyisibonelo esilula kakhulu kuwe sokubona ukuthi lo msebenzi usebenza kanjani, kepha uma usebenza, inguqulo efanayo yokusebenzisa kwayo isasetshenziswa kakhulu.
Isifundo: I-Excel Feature Wizard
Indlela yesi-2: sebenzisa ngokuhlangana ne-opharetha SEARCH
Ekusebenzeni, umsebenzi INDEX isetshenziswa kaningi ngengxabano SESHA. Isidlo sasemini INDEX - SESHA Ithuluzi elinamandla lapho usebenza e-Excel, okusebenzayo ekusebenzeni kwalo kuguquke kakhulu kune-analogue esiseduzane - opharetha I-VPR.
Injongo eyinhloko yomsebenzi SESHA kuwuphawu lwenombolo ngokuhleleka kwenani elithile ebangeni elikhethiwe.
ISyntax yokusebenza SESHA enjalo:
= SEARCH (search_value, lookup_array, [match_type])
- Inani elifuniwe - leli inani lesikhundla salo kuluhlu esilufunayo;
- Kubukwa u-Array ububanzi lapho le nombolo itholakala khona;
- Uhlobo Lomdlalo - Lokhu kuyipharamitha ongayikhetha enquma ukuthi angawafuna ngokunembile amanani noma acishe. Sizofuna amanani aqondile, ngakho-ke le ngxabano ayisetshenziswa.
Usebenzisa leli thuluzi ungakwazi ukusebenzisa okokufaka kwezimpikiswano Inombolo yomugqa futhi Inombolo Yekholomu kusebenza INDEX.
Ake sibheke ukuthi lokhu kungenziwa kanjani ngesibonelo esithile. Sisebenza netafula elifanayo, obekuxoxwa ngalo ngenhla. Ngokwehlukana, sinezinkambu ezimbili ezingeziwe - "Igama" futhi "Inani". Kuyadingeka ukuthi uqiniseke ukuthi uma ufaka igama lesisebenzi, inani lemali elitholwayo liboniswa ngokuzenzakalela. Ake sibheke ukuthi lokhu kungenziwa kanjani ekusebenziseni imisebenzi INDEX futhi SESHA.
- Okokuqala, sithola ukuthi amaholo atholakala muphi umsebenzi u-Parfenov D.F. Faka igama lakhe emkhakheni ofanele.
- Khetha iseli enkambu "Inani"lapho kuzovela khona umphumela wokugcina. Vula iwindi lokuphikisana komsebenzi INDEX ngokuhlelwa.
Ensimini Array sifaka izixhumanisi zekholomu lapho kutholakala khona amaholo abasebenzi.
Insimu Inombolo Yekholomu shiya singenalutho, njengoba sisebenzisa uhla olunobukhulu olulodwa njengesibonelo.
Kepha enkundleni Inombolo yomugqa sidinga nje ukubhala umsebenzi SESHA. Ukuyibhala, sinamathela kusi-syntax esixoxwe ngenhla. Masinyane faka igama le-opharetha enkambu "FUNA" ngaphandle kwezingcaphuno. Ngemuva kwalokho vula ngokushesha ubakaki bese ukhombisa izixhumanisi zenani olifunayo. Lezi izixhumanisi zeseli lapho saqopha khona ngokwehlukile igama lesisebenzi uParfenov. Sibeka i-semicolon futhi sibonisa izixhumanisi zobubanzi obubukwayo. Esimweni sethu, leli ikheli lekholamu enamagama abasebenzi. Ngemuva kwalokho, vala ubakaki.
Ngemuva kokuthi wonke amanani afakiwe, chofoza inkinobho "Kulungile".
- Umphumela wenani lemholo D. Parfenov ngemuva kokucutshungulwa kukhonjiswa endle "Inani".
- Manje uma kusensimini "Igama" sizokuguqula okuqukethwe "UParfenov D.F.", ngokwesibonelo, "UPopova M. D.", khona-ke inani lemholo enkambeni lizoshintsha ngokuzenzakalelayo "Inani".
Indlela 3: phatha amatafula amaningi
Manje ake sibheke ukuthi usebenzisa kanjani opharetha INDEX Ungacubungula amatafula amaningi. Ngale njongo kuzosetshenziswa enye impikiswano. "Inombolo Yendawo".
Sinamatafula amathathu. Ithebula ngalinye libonisa amaholo abasebenzi ngenyanga eyodwa. Umsebenzi wethu ukuthola amaholo (ikholamu yesithathu) yesisebenzi sesibili (irowu yesibili) yenyanga yesithathu (isifunda sesithathu).
- Khetha iseli lapho umphumela uzophuma khona futhi ngendlela evamile evulekile Isici Wizard, kepha lapho ukhetha uhlobo loopharetha, khetha umbono oyisethenjwa. Sidinga lokhu ngoba lolu hlobo luxhasa ukusingathwa kwengxabano. "Inombolo Yendawo".
- Iwindi lokuphikisana liyavula. Ensimini Xhumanisa sidinga ukucacisa amakheli awo womathathu amabanga. Ukuze wenze lokhu, setha ikhesa endle bese ukhetha ububanzi bokuqala ngenkinobho yegundane kwesokunxele icindezelwe. Ngemuva kwalokho faka i-semicolon. Lokhu kubaluleke kakhulu, ngoba uma ngokushesha uzokhethwa kulolu hlaka olulandelayo, khona-ke ikheli lakhona lizomane lithathe izixhumanisi zangaphambili. Ngakho-ke, ngemuva kokufaka i-semicolon, khetha ibanga elilandelayo. Bese siphinda sibeka i-semicolon bese ukhetha uhlu lokugcina. Isisho sonke esisensimini Xhumanisa thatha amabakaki.
Ensimini Inombolo yomugqa khombisa inombolo "2", ngoba sifuna igama lesibili lokugcina ohlwini.
Ensimini Inombolo Yekholomu khombisa inombolo "3"njengoba ikholomu yomholo ingeyesithathu ilandelana etafuleni ngalinye.
Ensimini "Inombolo Yendawo" faka inombolo "3", ngoba sidinga ukuthola imininingwane etafuleni lesithathu, eliqukethe imininingwane emiholweni yenyanga yesithathu.
Ngemuva kokuthi yonke imininingwane ifakiwe, chofoza inkinobho "Kulungile".
- Ngemuva kwalokho, imiphumela yesibalo iboniswa kuseli elikhethwe ngaphambilini. Ibonisa inani lomholo wesisebenzi sesibili (V. M. Safronov) ngenyanga yesithathu.
Indlela 4: ukubala inani
Ifomu lereferensi alisetshenziswa kaningi njengefomu lamalungu afanayo, kepha lingasetshenziswa hhayi kuphela lapho lisebenza ngamabanga amaningi, kodwa futhi nakwezinye izidingo. Isibonelo, singasetshenziswa ukubala inani ngokuhlanganiswa ne-opharetha ISUM.
Lapho ungeza inani ISUM ine-syntax elandelayo:
= SUM (arraw_address)
Esimweni sethu esithile, inani lemholo labo bonke abasebenzi ngenyanga lingabalwa kusetshenziswa ifomula elandelayo:
= SUM (C4: C9)
Kepha ungayishintsha kancane usebenzisa umsebenzi INDEX. Ngemuva kwalokho kuzoba nefomu elilandelayo:
= SUM (C4: INDEX (C4: C9; 6))
Kulokhu, izixhumanisi zokuqala zesigcawu zibonisa iseli eqala ngalo. Kepha ekuxhumaniseni okukhombisa ukuphela kwamalungu afanayo, kusetshenziswa opharetha INDEX. Kulokhu, impikiswano yokuqala ka-opharetha INDEX ikhombisa uhla, bese elesibili - esitokisini sokugcina - elesithupha.
Isifundo: Izici Ewusizo ze-Excel
Njengoba ukwazi ukubona, umsebenzi INDEX ingasetshenziswa ku-Excel ukuxazulula imisebenzi ehlukahlukene. Yize sicabangele kude kakhulu nazo zonke izinketho ezingase zisetshenziswe ekusetshenzisweni kwaso, kodwa kuphela ezithandwa kakhulu. Kunezinhlobo ezimbili zalo msebenzi: zokuphawula nezokuhlelwa. Ingasetshenziswa ngempumelelo kakhulu kuhlangene nabanye abaqhubi. Amafomula enziwe ngale ndlela azokwazi ukuxazulula izinkinga eziyinkimbinkimbi kakhulu.
