Esinye sezici eziwusizo kakhulu ze-Excel ngumqhubi we-INDEX. Ifuna idatha ebangeni ekuhlanganiseni komugqa ocacisiwe nekholomu, ibuyisa umphumela esitokisini esanduliwe. Kodwa amandla okusebenza alo msebenzi abonakalisiwe uma esetshenziselwa amafomula ayinkimbinkimbi ngokuhlanganiswa namanye ama-opharetha. Ake sibheke izinketho ezihlukahlukene zesicelo sazo.
Ukusebenzisa umsebenzi we-INDEX
I-Operator INDEX kungokwaqembu lemisebenzi evela kusigaba "Izixhumanisi nezigaba". Inezinhlobonhlobo ezimbili: izigubhu kanye nezinkomba.
Ukuhluka kwezingxenye zesikhala kunesitatimende esilandelayo:
= INDEX (i-array; umugqa_inombolo; ikholomu_nomboro)
Kulesi simo, izingxabano ezimbili zokugcina kule formula zingasetshenziswa zombili ndawonye kanye nanoma iyiphi yazo, uma uhlu luyingxenye eyodwa. Emkhakheni wezinhlobonhlobo, zombili izindinganiso kufanele zisetshenziswe. Kumele kuqashelwe ukuthi inombolo yomugqa nekholomu ayiyona inamba ezixhumanisini zeshidi, kodwa umyalelo ngaphakathi kwesigaba esibekiwe ngokwawo.
I-syntax ye-variant reference ibukeka kanje:
= INDEX (isixhumanisi; umugqa_inombolo; ikholomu_inombolo; [indawo_nombolo])
Lapha ungasebenzisa eyodwa kuphela yezimpikiswano ezimbili ngendlela efanayo: "Inombolo yomugqa" noma "Inombolo yekholomu". Ukuphikisana "Inombolo yendawo" ngokuvamile kuvame ukukhetha futhi isebenza kuphela lapho kubhekwa ama-multiple ranges ekusebenzeni.
Ngakho-ke, opharetha usesho idatha ebangeni elicacisiwe uma echaza umugqa noma ikholomu. Lo msebenzi ufana kakhulu namakhono ayo umsebenzisi we-vpr, kodwa ngokungafani nakho kungacwaninga cishe yonke indawo, hhayi nje kukholomu yesokunxele yetafula.
Indlela 1: Sebenzisa opharetha be-INDEX ngezinhlaka
Okokuqala, ake sihlaziye, sisebenzise isibonelo esilula, i-algorithm yokusebenzisa opharetha INDEX for arrays.
Sinetafula lemiholo. Ekholomu yokuqala, amagama abasebenzi aboniswa, okwesibili - usuku lokukhokha, futhi okwesithathu - inani lemali engenayo. Kudingeka sibonise igama lomsebenzi kumgca wesithathu.
- Khetha iseli lapho umphumela wokucubungula uzoboniswa khona. Chofoza kusithonjana "Faka umsebenzi"okuyinto itholakala ngokushesha ngakwesokunxele ibha yamakhemikhali.
- Inqubo yokuqalisa ivela. Amakhosi omsebenzi. Esikhathini "Izixhumanisi nezigaba" le nsimbi noma "Uhlu oluphelele lwe-alfabhethi" bheka igama INDEX. Ngemva kokuthola lo opharetha, ukhethe bese uchofoza inkinobho. "Kulungile"elitholakala phansi kwefasitela.
- Iwindi elincane livuleka kulokho okudingayo ukukhetha eyodwa yezinhlobo zokusebenza: "Uhlu" noma "Xhumanisa". Okukhethwa kukho esikudingayo "Uhlu". Itholakala kuqala futhi ikhethwe ngokuzenzakalelayo. Ngakho-ke, sidinga nje ukucindezela inkinobho "Kulungile".
- Iwindi lempikiswano yokusebenza ivula. INDEX. Njengoba kukhulunywe ngenhla, kunezimpikiswano ezintathu, futhi, ngokufanele, amasimu amathathu okugcwalisa.
Ensimini "Uhlu" Kufanele ucacise ikheli lohlu lwedatha olucutshungulwayo. Ingaqhutshwa ngesandla. Kodwa ukuze sikwazi ukwenza umsebenzi, sizoqhubeka ngokuhlukile. Faka i-cursor ensimini efanele, bese ujikeleza lonke uhlu lwemininingwane yamathebhu kushidi. Ngemuva kwalokhu, ikheli lebanga liboniswa masinyane ensimini.
Ensimini "Inombolo yomugqa" ufake inombolo "3", ngoba ngesimo esidinga ukucacisa igama lesithathu kuhlu. Ensimini "Inombolo yekholomu" setha inombolo "1"kusukela ikholomu ngamagama yilokuqala ebangeni elikhethiwe.
Ngemuva kokuthi zonke izilungiselelo ezicacisiwe zenziwe, sichofoza inkinobho "Kulungile".
- Umphumela wokucubungula uboniswa esitokisini esacacisiwe esigabeni sokuqala sale mfundiso. Yilona igama lokugcina elitholwe yilokho okwesithathu kuhlu ohlwini lobubanzi olukhethiwe.
Sihlaziye ukusetshenziswa komsebenzi. INDEX ku-multidimensional array (amakholomu ambalwa nemigqa). Uma ububanzi buyi-one-dimensional, ukugcwalisa idatha ewindini lempikiswano kuzoba lula nakakhulu. Ensimini "Uhlu" ngendlela efanayo ngenhla, sichaza ikheli layo. Kulesi simo, ibanga ledatha liqukethe amanani kuphela kukholomu eyodwa. "Igama". Ensimini "Inombolo yomugqa" ucacise inani "3", ngoba udinga ukwazi idatha kusuka kumugqa wesithathu. Inkambu "Inombolo yekholomu" ngokuvamile, ungashiya kungenalutho, ngoba sinebanga elilodwa elilodwa lapho ikholomu eyodwa kuphela isetshenziswa khona. Sicindezela inkinobho "Kulungile".
Umphumela uzoba ngokufanayo naphezulu.
Kwakuyisibonelo esilula kakhulu ukuthi ubone ukuthi lo msebenzi usebenza kanjani, kodwa ngokwenza le ndlela yokusebenzisa ayisasetshenziswa kakhulu.
Isifundo: I-Excel umsebenzi wizard
Indlela 2: sebenzisa ngokubambisana nomsebenzisi we-MATCH
Ngokwenza, umsebenzi INDEX okuvame ukusetshenziselwa ukuphikisana MATCH. Bunch INDEX - MATCH iyithuluzi elinamandla lapho isebenza ku-Excel, eguquguqukayo kakhulu ekusebenzeni kwayo kune-analogue esiseduze kakhulu yi-opharetha I-Vpr.
Umsebenzi oyinhloko womsebenzi MATCH iyinkomba yenombolo ngokulandelana kwenani elithile ebangeni elikhethiwe.
I-syntax ye-Operator MATCH okufana nalokhu:
= MATCH (inani lokusesha, ukulandelana kokulingana, [match_type])
- Inani elifunwa - yileli xabiso isikhundla sakhe ebangeni esiyilindele;
- Ibukeka buhle - yilo ibanga lapho leli xabiso litholakala khona;
- Uhlobo lokumephu - Lena ipharamitha yokuzikhethela enquma ukuthi ifuna ngokunembile noma cishe ukucinga amanani. Sizobheka izindinganiso eziqondile, ngakho-ke le mpikiswano ayisetshenzisiwe.
Ngalesi thuluzi ungazenzelela ukufakwa kwezimpikiswano. "Inombolo yomugqa" futhi "Inombolo yekholomu" emsebenzini INDEX.
Ake sibone ukuthi lokhu kungenziwa kanjani ngesibonelo esithile. Sisebenza sonke ngetafula elifanayo, okuxoxwa ngenhla. Ngokwehlukana, sinamasimu amabili engeziwe - "Igama" futhi "Inani". Kubalulekile ukwenza ukuthi uma ufaka igama lesisebenzi, inani lemali elitholayo liboniswa ngokuzenzakalelayo. Ake sibone ukuthi lokhu kungasetshenziswa kanjani ngokusebenzisa imisebenzi INDEX futhi MATCH.
- Okokuqala, sizokuthola ukuthi yiluphi uhlobo lomsebenzi wezeholo u-Parfenov DF owamukelayo. Sifaka igama lakhe endaweni efanele.
- Khetha iseli emkhakheni "Inani"lapho umphumela wokugcina uzoboniswa khona. Qalisa iwindi lempikiswano yokusebenza INDEX for arrays.
Ensimini "Uhlu" sifaka izixhumanisi zekholomu lapho izibalo zemiholo yabasebenzi zitholakala khona.
Inkambu "Inombolo yekholomu" sishiya singenalutho, ngoba sisebenzisa ibanga elilodwa lesisodwa njengesibonelo.
Kodwa ensimini "Inombolo yomugqa" sidinga nje ukubhala umsebenzi MATCH. Ukuze sibhale, silandela i-syntax echazwe ngenhla. Masinyane ensimini faka igama lomqhubi "QHUBA" ngaphandle izingcaphuno. Bese uvula ngokushesha ibhakha bese ucacisa izixhumanisi zenani elifunayo. Lezi yizixhumanisi zeseli lapho sibhala khona ngokweqile igama lesisebenzi sikaParfenov. Sibeka i-semicolon bese sicacisa izixhumanisi ebangeni elibukwe. Esikhathini sethu, leli yikheli lekholomu ngamagama abasebenzi. Ngemuva kwalokho, vala isibakaki.
Ngemuva kokuthi wonke amanani afakiwe, chofoza inkinobho "Kulungile".
- Umphumela wenani lemali oyikhokhela i-Parfenova DF ngemuva kokucubungula iboniswa ensimini "Inani".
- Manje uma insimu "Igama" sishintsha okuqukethwe "Parfenov D.F."ku, isibonelo, "Popova M.D."khona-ke inani lentela ensimini lizoshintsha ngokuzenzakalelayo. "Inani".
Indlela 3: ukucubungula amatafula amaningi
Manje ake sibone ukuthi kusetshenziswa kanjani opharetha INDEX Ungakwazi ukuphatha amatafula amaningi. Ingxabano eyengeziwe izosetshenziselwa le njongo. "Inombolo yendawo".
Sinamatafula amathathu. Ithebula ngalinye libonisa inkokhelo yabasebenzi inyanga ethile. Umsebenzi wethu ukuthola ukuthola inkokhelo (ikholomu yesithathu) yesisebenzi sesibili (umugqa wesibili) ngenyanga yesithathu (isifunda sesithathu).
- Khetha iseli lapho umphumela uzoboniswa khona futhi ngendlela evamile evulekile Umsebenzi weWebhu, kodwa uma ukhetha uhlobo lomsebenzisi, khetha ukubuka kokubhekisela. Sidinga lokhu ngoba lolu hlobo lusekela umsebenzi ngxabano "Inombolo yendawo".
- Iwindi lempikiswano ivuleka. Ensimini "Xhumanisa" sidinga ukucacisa amakheli azo zonke izigaba ezintathu. Ukwenza lokhu, setha isikhombisi ensimini bese ukhetha ibanga lokuqala nenki yegundane lesokunxele eligcinwe phansi. Bese sibeka i-semicolon. Lokhu kubaluleke kakhulu, ngoba uma uhamba ngokushesha ekukhethweni kwesigaba esilandelayo, ikheli lalo lizobe lishintsha izixhumanisi zangaphambilini. Ngakho, emva kokwethulwa kwe-semicolon, khetha uhla olulandelayo. Khona-ke sibeka i-semicolon bese ukhetha uhlu lokugcina. Yonke inkulumo esensimini "Xhumanisa" thatha abazali.
Ensimini "Inombolo yomugqa" ucacise inombolo "2", njengoba sifuna igama lesibili kuhlu.
Ensimini "Inombolo yekholomu" ucacise inombolo "3", njengoba ikholomu yesilinganiso yiyesithathu kuthebula ngalinye.
Ensimini "Inombolo yendawo" ufake inombolo "3", njengoba sidinga ukuthola idatha etafuleni lesithathu, eliqukethe ulwazi ngezinkokhelo zenyanga yesithathu.
Ngemuva kokuthi yonke idatha ifakiwe, chofoza inkinobho "Kulungile".
- Emva kwalokho, imiphumela yokubala iboniswa kuseli elikhethiwe. Ibonisa inani lomholo wesisebenzi sesibili (V. Safronov) ngenyanga yesithathu.
Indlela 4: Isibalo sokubalwa
Ifomu lokubhekisela akulona livame ukusetshenziswa njengefomu elihle, kodwa lingasetshenziswa hhayi kuphela lapho isebenza ngezigaba eziningana, kodwa nakwezinye izidingo. Isibonelo, ingasetshenziswa ukubala inani ngokuhlanganiswa nomqhubi SUM.
Uma ungeze imali SUM ine-syntax elandelayo:
= I-SUM (ikheli le-array)
Esimweni sethu, inani lemali engenayo yabasebenzi bonke enyangeni lingabalwa ngokusebenzisa ifomula elandelayo:
= SUM (C4: C9)
Kodwa ungayishintsha kancane ngokusebenzisa lo msebenzi INDEX. Bese kuzobukeka kanje:
= SUM (C4: INDEX (C4: C9; 6))
Kulesi simo, izixhumanisi zokuqala komdwebo zibonisa iseli okuqala ngalo. Kodwa kuzixhumanisi zokucacisa ukuphela kohlu, opharetha isetshenziswa. INDEX. Kulokhu, ingxabano yokuqala yomqhubi INDEX ibonisa ububanzi, kanti okwesibini kuya esitokisini sayo sokugcina yisithupha.
Isifundo: Izici eziwusizo ze-Excel
Njengoba ungabona, umsebenzi INDEX ingasetshenziswa ku-Excel yokuxazulula imisebenzi ehlukahlukene. Nakuba sibheke kude nakho konke okukhethwa kukho ukusetshenziswa kwayo, kodwa kuphela okudingekayo kakhulu. Kunezinhlobo ezimbili zalokhu msebenzi: ukubhekisela kanye nezigaba. Ngempumelelo kakhulu ingasetshenziswa ngokuhlanganiswa namanye ama-opharetha. Amafomula adalwe ngale ndlela azokwazi ukuxazulula imisebenzi eyinkimbinkimbi kakhulu.
