Enye yezinto eziwusizo kakhulu zohlelo lwe-Excel uhlelo lwe-opharetha. Isesha idatha ebangeni ekuxhumaneni kwemigqa ebekiwe nekholamu, ubuyisa umphumela osesitokisini elinqunyelwe ngaphambili. Kepha amathuba walo msebenzi adalulwa uma eyisebenzisa kumafomula ayinkimbinkimbi ngokuhlanganiswa nabanye opharetha. Ake sibheke izinketho ezahlukahlukene zokusetshenziswa kwazo.
Usebenzisa umsebenzi wokusebenza
I-Index opharetha ibhekisa eqenjini lemisebenzi kusuka esigabeni "izixhumanisi kanye nama-arrays". Inezinhlobo ezimbili: ama-arrows kanye nezinkomba.Inketho yokuhlela ine-syntax elandelayo:
= Inkomba (uhlu; inombolo_link; inombolo_ukuna)
Ngasikhathi sinye, izimpikiswano ezimbili zokugcina ezisefomula zingasetshenziswa, zombili kanye nanoma yimuphi omunye wabo uma u-ARAY ungobunye obukhulu. Ngobubanzi obuhlukahlukene abaningi, womabili amanani kufanele asetshenziswe. Kuyadingeka futhi ukubheka ukuthi ngaphansi kwenombolo yomugqa kanye nekholamu akuqondakali ukuthi ayinalo kwizixhumanisi zeshidi, kepha i-oda ngaphakathi kwe-array ebekiwe kakhulu.
I-Syntax yenketho yesethenjwa ibukeka kanjena:
= Index (isixhumanisi; inombolo_link; inombolo_number; [inombolo_name])
Lapha ungasebenzisa futhi ukuphikisana eyodwa kuphela kusuka kokubili: "Inombolo yomugqa" noma "Inombolo yekholomu". Ingxabano "inombolo yendawo" ngokuvamile ngokuzikhethela futhi isebenza kuphela lapho amabanga amaningana ebamba iqhaza ekusebenzeni.
Ngakho-ke, opharetha ufuna idatha ebangeni elisethiwe lapho echaza intambo noma ikholomu. Lesi sici sifana kakhulu namandla aso. Ingalo yomqhubi Kepha ngokungafani nakho kungasesha cishe yonke indawo, hhayi kuphela kukholomu yesobunxele eyedlulele yetafula.
Indlela 1: Usebenzisa Inkomba Yo-Operator ye-Array
Ake, okokuqala, sihlaziya isibonelo esilula kunazo zonke se-algorithm yokusebenzisa inkomba yopharetha ye-array.
Sinetafula le-salary. Kwikholamu yayo yokuqala, amagama abasebenzi ayakhonjiswa, okwesibili - usuku lokukhokha, nangokwesithathu - inani lemali etholwayo. Sidinga ukuhoxisa igama lesisebenzi kulayini wesithathu.
- Khetha iseli lapho kuzokhonjiswa khona umphumela wokucubungula. Chofoza isithonjana esithi "Insert Function", esitholakala ngokushesha ngakwesobunxele sentambo yefomula.
- Inqubo yokusebenzisa i-wizard yemisebenzi yenzeka. Esigabeni "Izikhombo nama-Arroys" aleli thuluzi noma "uhlu olugcwele lwezinhlamvu" ngokubheka igama elithi "Index". Ngemuva kokuthola lo opharetha, siwugqabhisisa bese uchofoza inkinobho ethi "Kulungile", ebekwe ezansi kwewindi.
- Iwindi elincane liyavula, lapho udinga ukukhetha enye yezinhlobo zomsebenzi: "Array" noma "isixhumanisi". Sidinga inketho ethi "Aray". Itholakala kuqala futhi igqanyiswe ngokuzenzakalelayo. Ngakho-ke, singavele sichofoze inkinobho ethi "KULUNGILE".
- Iwindi lokuphikisana livula umsebenzi we-Index. Njengoba kushiwo ngenhla, inezimpikiswano ezintathu, kanye nemikhakha emithathu yokugcwaliswa.
Ensimini ethi "Array", udinga ukucacisa ikheli ledatha yedatha ecutshungulwa. Ungayishayela ngesandla. Kepha ukwenza lula umsebenzi, sizokwenza ngenye indlela. Sibeka isikhombisi emkhakheni ofanele, bese umaye lonke uhla lwe-tabular data eshidini. Ngemuva kwalokho, ikheli lebanga livela ngokushesha enkundleni.
Ensimini "yomugqa", sibeka inombolo "3", kusukela ngesimo, kudingeka sichaze igama lesithathu ohlwini. Ensimini "yenombolo yekholomu", setha inombolo "1", ngoba igama elithi ikholomu lingowokuqala ebangeni elizinikezele.
Ngemuva kwazo zonke izilungiselelo ezichaziwe zenziwa, chofoza inkinobho ethi "Kulungile".
- Umphumela wokucubungula uboniswa esitokisini, esachazwa esigabeni sokuqala sale ncwadi. Yisibongo esiphumela esivela kwesithathu ohlwini ebangeni ledatha ezinikezele.
Siphazamise umsebenzi womsebenzi we-Index ohlelweni oluningi (amakholomu ambalwa nezintambo). Uma uhla lwalunobukhulu obunye, khona-ke idatha egcwalisa iwindi lengxabano kungaba lula kakhulu. Ensimini "i-array" ngendlela efanayo nangaphezulu, sicacisa ikheli layo. Kulokhu, uhla lwedatha luqukethe kuphela amanani ekholomu eyodwa "yegama". Inkundla "yomugqa wenombolo", chaza inani elithi "3", njengoba udinga ukwazi imininingwane kusuka kulayini wesithathu. Inkambu "yenombolo yekholomu" ingashiywa ingenalutho nhlobo, ngoba sinohlu olulodwa olusetshenziswa khona lapho kuphela ikholamu eyodwa kuphela. Chofoza inkinobho ethi "KULUNGILE".
Umphumela uzofana ncamashi nangaphezulu.
Kwakuyisibonelo esilula, ukuze ubone ukuthi lo msebenzi usebenza kanjani, kodwa usebenza, le ndlela isetshenziswa okwamanje, akuvamile ukusetshenziswa.
Isifundo: I-Master of Imisebenzi ku-Asuka
Indlela 2: Isicelo kwinkimbinkimbi nge-opharetha yokusesha
Empeleni, umsebenzi we-Index uvame ukusetshenziswa ndawonye kanye nengxabano yokusesha. I-Bunch Index - Inkampani yokusesha iyithuluzi elinamandla lapho usebenza ku-Excel, elivumelana nezimo ngokuya ngokusebenza kwalo okusondele kakhulu kune-analogue yayo esondele kakhulu - opharetha we-ARP.
Umsebenzi oyinhloko womsebenzi wokusesha ukucacisa inombolo ngokulandelana kwenani elithile ebangeni elizinikezele.
Sesha i-syntax yokufuna okunjalo:
= Ibhodi lesesho (elifiselekayo_dation, ukubukwa okubukiwe_okuthile, [uhlobo_ensation])
- Inani elifunekayo inani, isikhundla salolo uhla esilufunayo;
- I-array ebukiwe yibanga lapho leli nani litholakala khona;
- Uhlobo lwemephu yemephu yipharamitha yokuzikhethela enquma ngqo noma cishe ibheke amanani. Sizobheka amanani anembile, ngakho-ke le ngxabano ayisetshenziswa.
Usebenzisa leli thuluzi, ungangenisa ukwethulwa kwezimpikiswano "Inombolo yomugqa" nenombolo yekholomu "kumsebenzi we-Index.
Ake sibheke ukuthi kungenziwa kanjani esibonelweni esithile. Sisebenza netafula elifanayo okwakuxoxwa ngalo ngenhla. Ngokwehlukana, sinezinkambu ezimbili ezengeziwe - "Igama" nelithi "Inani". Kuyadingeka ukwenza lokho lapho kwethulwa igama lesisebenzi, inani lemali etholwe yiyo iboniswa ngokuzenzakalelayo. Ake sibheke ukuthi lokhu kungenziwa kanjani ekusebenzeni ngokusebenzisa inkomba yokusebenza nokusesha.
- Okokuqala, sifunda ukuthi yimuphi umholo owemukela umsebenzi weParfenov D. F. Faka igama lakhe kwinsimu ehambisanayo.
- Khetha iseli kwinkambu ye- "Inani" lapho umphumela wokugcina uzoboniswa khona. Gijimani inkomba yomsebenzi we-Buscide Window we-array.
Ensimini ethi "ARRAY" Sethula izixhumanisi zekholomu, lapho kutholakala khona amaholo wabasebenzi.
Inkambu ye- "Column" ishiywe ingenalutho, njengoba sisebenzisa isibonelo uhla oluhlanya olulodwa.
Kepha ensimini "yomugqa", sizodinga nje ukuqopha umsebenzi wosesho. Ngomlando wakhe, bambelela kwi-syntax okuxoxwe ngalo ngenhla. Masinyane enkundleni faka igama lomuntu opharetha "inkampani yokucinga" ngaphandle kwezilinganiso. Ngemuva kwalokho vula amabakaki futhi ukhombise izixhumanisi zenani elifunayo. Lezi yizixhumanisi zeseli lapho saqoqa khona igama lomsebenzi weParfenov ngokwahlukana. Sibeka iphuzu ngekhefana futhi sicacisa izixhumanisi zebanga elibukiwe. Kuthu, leli ikheli lekholomu elinamagama abasebenzi. Emva kwalokho, vala ubakaki.
Ngemuva kokuthi wonke amanani enziwe, chofoza inkinobho ethi "Kulungile".
- Umphumela wenombolo yemali etholwa i-Parfenova D. F. Ngemuva kokucutshungulwa kuboniswa enkambu "yenani".
- Manje, uma esensimini ethi "Igama" sizoshintsha okuqukethwe kusuka kuParfenov.
Indlela 3: Iyacubungula amatafula amaningi
Manje ake sibheke ukuthi inkomba ingaphathwa kanjani ngamatafula amaningana. Ngalezi zinhloso, kuzosetshenziswa "inombolo yokuphikisana" eyengeziwe ".
Sinamatafula amathathu. Ithebula ngalinye libonisa inkokhelo yabasebenzi ngenyanga ehlukene. Umsebenzi wethu ukwazi amaholo (ikholomu yesithathu) yesisebenzi sesibili (umugqa wesibili) ngenyanga yesithathu (indawo yesithathu).
- Sikhetha iseli lapho umphumela umphumela futhi ungavula imisebenzi wizard, kepha lapho ukhetha uhlobo lwe-opharetha, khetha umbono wereferensi. Sidinga lokhu ngoba lolu hlobo olusekela umsebenzi ngegciwane elithi "Area Number".
- Iwindi lokuphikisana livuleka. Ensimini yesixhumanisi, kudingeka sicacise amakheli wawo wonke amabanga amathathu. Ukuze wenze lokhu, setha isikhombisi enkundleni bese ukhetha uhla lokuqala ngenkinobho yegundane kwesobunxele. Ngemuva kwalokho faka iphuzu ngekhefana. Lokhu kubaluleke kakhulu, ngoba ngokushesha uya ekukhishweni kwe-array elandelayo, khona-ke ikheli layo lizomane lithathe indawo izixhumanisi zangaphambili. Ngakho-ke, ngemuva kokungena endaweni nge-comma, sabela uhla olulandelayo. Bese uphinde ubeke iphuzu ngekhefana bese wabela uhlu lokugcina. Yonke inkulumo esensimini "yokuxhumanisa" ithatha kubakaki.
Ensimini "yomugqa", khombisa inombolo "2", njengoba sifuna isibongo sesibili ohlwini.
Ensimini "yenombolo yekholomu", chaza inombolo "3", ngoba ikholomu yeholo ingeyesithathu ku-akhawunti etafuleni ngalinye.
Emkhakheni we- "Area Number", sibeka inombolo "3", ngoba sidinga ukuthola idatha etafuleni lesithathu, eliqukethe imininingwane mayelana neholo lenyanga yesithathu.
Ngemuva kwayo yonke imininingwane ifakiwe, chofoza inkinobho ethi "Kulungile".
- Ngemuva kwalokho, imiphumela yokubala iboniswa esitokisini esikhethwe ngaphambilini. Kubonisa inani leholo lesisebenzi sesibili (Safe Vav. M.) ngenyanga yesithathu.
Indlela 4: Ukubalwa kwenani
Ifomu lereferensi alisetshenziswa kaningi njengefomu le-array, kepha lingasetshenziswa hhayi kuphela lapho lisebenza ngamabanga amaningana, kodwa nakwezinye izidingo. Isibonelo, lingasetshenziswa ukubala inani elihambisana nenani lemali.
Lapho ungeza amanani, i-syntax elandelayo:
= Amanani (ikheli_massiva)
Esimweni esingakanani, inani lemali etholwayo yabo bonke abasebenzi ngenyanga ingabalwa kusetshenziswa ifomula elandelayo:
= Amanani (C4: C9)
Kepha ungakuguqula kancane ngokusebenzisa umsebenzi we-Index. Ngemuva kwalokho uzoba nefomu elilandelayo:
= Ama-sums (C4: Index (C4: C9; 6))
Kulokhu, iseli libonisa izixhumanisi ze-array, eziqala ngazo. Kepha kwizixhumanisi zokuphelelwa yisikhathi kwe-array, opharetha uyasetshenziswa. Kulokhu, impikiswano yokuqala yenkomba ye-opharetha ikhombisa ububanzi, kanti okwesibili - esitokisini sayo sokugcina - elesithupha.
Isifundo: Izici eziwusizo zihamba phambili
Njengoba ukwazi ukubona, ukusebenza kwenkomba kungasetshenziswa ekudingisweni ukuxazulula imisebenzi ehlukahlukene. Yize sibheke kude nazo zonke izinketho ezingenzeka zokusetshenziswa kwazo, kodwa kuphela okufunwa kakhulu. Kunezinhlobo ezimbili zalesi sici: ireferensi kanye nama-array. Ingasetshenziswa ngempumelelo kakhulu ngokuhlanganiswa nabanye opharetha. Idalwe ngale ndlela yamafomula izokwazi ukuxazulula imisebenzi enzima kakhulu.