Okuqukethwe
Uma uthi nhlá (ikakhulukazi lapho ufunda usizo), umsebenzi INDALO (INDIRECT) kubukeka kulula futhi kungadingeki. Ingqikithi yawo wukuguqula umbhalo obukeka njengesixhumanisi ube isixhumanisi esigcwele. Labo. uma sidinga ukubhekisela kuseli A1, khona-ke singakwazi ukwenza isixhumanisi esiqondile (faka uphawu olulinganayo ku-D1, chofoza ku-A1 bese ucindezela u-Enter), noma singasebenzisa INDALO ngenjongo efanayo:
Sicela uqaphele ukuthi i-agumenti yomsebenzi - ireferensi ku-A1 - ifakwe kuzimpawu zokucaphuna, ukuze, empeleni, kube umbhalo lapha.
“Awu, KULUNGILE,” usho. “Futhi yini inzuzo?”
Kodwa ungahluleli ngokubona kokuqala - kuyakhohlisa. Lesi sici singakusiza ezimweni eziningi.
Isibonelo 1. I-Transpose
Okwakudala kohlobo: udinga ukuvula idaya eqondile
groove ukuya evundlile (transpose). Yiqiniso, ungasebenzisa ukufaka okukhethekile noma umsebenzi I-TRANSP (TRANSPOSE) ngefomula yamalungu afanayo, kodwa ungadlula ngeyethu INDALO:
I-logic ilula: ukuthola ikheli leseli elilandelayo, sinamathisela uhlamvu "A" ngohlamvu olukhethekile "&" kanye nenombolo yekholomu yeseli yamanje, esinikezwa umsebenzi. IKHOLOMU (IKHOLAMU).
Inqubo ye-reverse yenziwa kangcono ngokuhlukile kancane. Kusukela kulesi sikhathi sidinga ukwakha isixhumanisi kumaseli B2, C2, D2, njll., kulula kakhulu ukusebenzisa imodi yokuxhumanisa i-R1C1 esikhundleni "sempi yasolwandle" yakudala. Kule modi, amaseli ethu azohluka kuphela enombolweni yekholomu: B2=R1C2, C2=R1C3, D2=R1C4 njll
Yilapho i-agumenti yesibili yomsebenzi ongakhetha ingena khona. INDALO. Uma ilingana AMANGA (AMANGA), bese ungasetha ikheli lesixhumanisi kumodi ye-R1C1. Ngakho-ke singakwazi ukuguqula kalula ububanzi obuvundlile sibubuyisele mpo:
Isibonelo 2. Isamba ngesikhawu
Sesiyihlaziyile kakade indlela eyodwa yokufingqa efasiteleni (ububanzi) bosayizi othile eshidini sisebenzisa umsebenzi. UKULAHLWA (OFFSET). Inkinga efanayo ingaxazululwa ngokusebenzisa INDALO. Uma sidinga ukufingqa idatha kusukela esikhathini esithile sobubanzi, khona-ke singayinamathisela kusuka ezicucu bese siyiguqula ibe isixhumanisi esigcwele, esingasifaka ngaphakathi komsebenzi. ISUM (SUM):
Isibonelo 3. Uhlu lokudonsela phansi kwetafula elihlakaniphile
Kwesinye isikhathi iMicrosoft Excel ayiphathi amagama namakholomu ethebula elihlakaniphile njengezixhumanisi ezigcwele. Ngakho-ke, ngokwesibonelo, lapho uzama ukwakha uhlu lokudonsela phansi (ithebhu Idatha - Ukuqinisekiswa Kwedatha) ngokusekelwe kukholomu Abasebenzi kusuka ku-smart table Abantu sizothola iphutha:
Uma "sisonga" isixhumanisi nomsebenzi wethu INDALO, khona-ke i-Excel izokwamukela kalula futhi uhlu lwethu lokudonsela phansi luzobuyekezwa ngamandla lapho sengeza abasebenzi abasha ekugcineni kwethebula elihlakaniphile:
Isibonelo 4. Izixhumanisi Ezinganqamuki
Njengoba wazi, i-Excel ilungisa ngokuzenzakalelayo amakheli ayireferensi kumafomula lapho ifaka noma isusa amakholomu emigqa eshidini. Ezimweni eziningi, lokhu kulungile futhi kulula, kodwa hhayi njalo. Ake sithi sidinga ukudlulisa amagama asuka kunkomba yesisebenzi aye embikweni:
Uma ufaka izixhumanisi ezivamile (faka =B2 kuseli yokuqala eluhlaza bese uyikopisha phansi), lapho ususa, isibonelo, i-Dasha, sizothola i-#LINK! iphutha esitokisini esiluhlaza elihambisana naye. (#REF!). Esimeni sokusebenzisa umsebenzi ukudala izixhumanisi INDALO ngeke kube khona inkinga enjalo.
Isibonelo sesi-5: Ukuqoqa idatha kumashidi amaningi
Ake sithi sinamaphepha ama-5 anemibiko yohlobo olufanayo evela kubasebenzi abahlukene (uMikhail, u-Elena, u-Ivan, uSergey, uDmitry):
Ake sicabange ukuthi umumo, ubukhulu, indawo kanye nokulandelana kwezimpahla nezinyanga kuwo wonke amathebula kuyefana - izinombolo kuphela ezihlukene.
Ungakwazi ukuqoqa idatha kuwo wonke amashidi (ungawafinyi, kodwa uwabeke ngaphansi kwelinye “enqwabeni”) ngefomula eyodwa nje:
Njengoba ubona, umbono uyafana: sinamathisela isixhumanisi esitokisini esifunayo seshidi elinikeziwe, futhi INDALO iliguqule libe “bukhoma”. Ukuze kube lula, ngaphezu kwetafula, ngengeze izinhlamvu zamakholomu (B, C, D), futhi ngakwesokudla - izinombolo zomugqa okudingeka zithathwe eshidini ngalinye.
Izibhamu
Uma usebenzisa INDALO (INDIRECT) udinga ukukhumbula ngobuthakathaka bayo:
- Uma uxhuma kwelinye ifayela (ngokunamathisela igama lefayela kubakaki abayisikwele, igama leshidi, nekheli leseli), lisebenza kuphela ngenkathi ifayela lokuqala livuliwe. Uma siyivala, sithola iphutha #LINK!
- I-INDIRECT ayikwazi ukubhekisa kububanzi obuqanjwe ngokuguqukayo. Ku-static - akunankinga.
- I-INDIRECT iwumsebenzi oguquguqukayo noma “oguquguqukayo,” okungukuthi ibalwa kabusha nganoma yiluphi ushintsho kunoma iyiphi iseli yeshidi, futhi hhayi nje ukuthonya amaseli, njengasemisebenzini evamile. Lokhu kunomphumela omubi ekusebenzeni futhi kungcono ukungathwalwa ngamatafula amakhulu angu-INDIRECT.
- Ungadala kanjani ububanzi obuguqukayo ngosayizi ozenzakalelayo
- Ukufingqa ngewindi lebanga eshidini elinomsebenzi we-OFFSET