Okuqukethwe
Uma ubusebenza ku-Excel isikhathi esingaphezu kosuku lwesibili, cishe usuvele wahlangana noma wasebenzisa izinkomba ze-dollar-sign kumafomula nemisebenzi ye-Excel, isibonelo. $D $2 or F$3 njll. Ekugcineni masithole ukuthi zisho ukuthini ngempela, zisebenza kanjani nokuthi zingaba usizo kuphi kumafayela akho.
Izixhumanisi ezihlobene
Lezi yizinkomba ezijwayelekile ngendlela yenombolo yomugqa wohlamvu lwekholomu ( A1, С5, okungukuthi “umkhumbi wezempi”) otholakala kumafayela amaningi e-Excel. Okuhlukile kwabo ukuthi bayaguquguquka lapho kukopishwa amafomula. Labo. C5, isibonelo, iphenduka ibe С6, С7 njll. lapho ukopisha phansi noma ukuze D5, E5 njll. lapho ukopisha kwesokudla, njll. Ezimweni eziningi, lokhu kujwayelekile futhi akudali izinkinga:
Izixhumanisi ezixutshiwe
Kwesinye isikhathi iqiniso lokuthi isixhumanisi kufomula, lapho sikopishwa, "amaslayidi" ahlobene neseli lokuqala alithandeki. Khona-ke, ukulungisa isixhumanisi, isibonakaliso sedola ($) sisetshenziswa, esikuvumela ukuthi ulungise lokho okuza ngaphambili. Ngakho, isibonelo, isixhumanisi $C5 ngeke ishintshe kuwo wonke amakholomu (isb С ayisoze yaphenduka D, E or F), kodwa ingase idlule imigqa (okungukuthi ingase idlule $C6, $C7 njll.). Ngokunjalo, C$5 – ngeke ihambe emigqeni, kodwa ingakwazi “ukuhamba” kumakholomu. Izixhumanisi ezinjalo zibizwa okuhlanganisiwe:
Izixhumanisi eziphelele
Hhayi-ke, uma ungeza womabili amadola kusixhumanisi ngesikhathi esisodwa ($5) - izophenduka ibe ngokuphelele futhi ngeke ishintshe nganoma iyiphi indlela ngesikhathi sokukopishwa, okungukuthi amadola aqiniswa ngokuqinile kanye nomugqa nekholomu:
Indlela elula neshesha kakhulu yokuguqula ireferensi ehlobene ibe ireferensi ephelele noma exubile ukuyikhetha kufomula bese ucindezela inkinobho ethi F4 izikhathi ezimbalwa. Lo khiye uzungeza zonke izinketho ezine ezingaba khona zokulungisa isixhumanisi seseli: C5 → $5 → $C5 → C$5 futhi konke futhi.
Konke kulula futhi kucacile. Kodwa kukhona oyedwa "kodwa".
Ake sithi sifuna ukwenza ireferensi yeseli С5. Njengoba wayehlale ekhuluma ngaye С5 ngokunganaki noma yisiphi isenzo somsebenzisi. Kuvele kube yinto ehlekisayo - noma ngabe wenza isixhumanisi sibe ngokuphelele (isb $5), kusashintsha kwezinye izimo. Isibonelo: Uma ususa ulayini wesithathu nowesine, uzoshintsha ube $3. Uma ufaka ikholomu kwesokunxele С, khona-ke izoshintsha ibe D. Uma usike iseli С5 bese unamathisele ku F7, khona-ke izoshintsha ibe F7 njalo njalo. Kuthiwani uma ngifuna isixhumanisi esinzima ngempela esizohlala sibhekisela kuso С5 futhi akukho okunye ngaphansi kwanoma yiziphi izimo noma izenzo zabasebenzisi?
Izixhumanisi eziphelele ngempela
Isixazululo siwukusebenzisa umsebenzi INDALO (INDIRECT), ekhiqiza ireferensi yeseli kusuka kuyunithi yezinhlamvu yombhalo.
Uma ufaka ifomula kuseli:
=I-INDIRECT(“C5”)
=I-INDIRECT(«C5»)
khona-ke iyohlala ikhomba iseli elinekheli C5 kungakhathaliseki ukuthi yiziphi izenzo eziqhubekayo zomsebenzisi, ukufaka noma ukususa imigqa, njll. Okuwukuphela kwenkinga encane ukuthi uma iseli eliqondiwe lingenalutho, kusho ukuthi INDALO okuphumayo 0, okungelula ngaso sonke isikhathi. Kodwa-ke, lokhu kungagwenywa kalula ngokusebenzisa ukwakhiwa okuyinkimbinkimbi kancane ngesheke ngokusebenzisa umsebenzi ISBLANK:
=IF(ISNULL(INDIRECT(“C5″))),””, INDIRECT(“C5”))
=IF(ISBLANK(INDIRECT(«C5″));»»;INDIRECT(«C5»))
- Izinkomba zeqembu leshidi le-XNUMXD lapho uhlanganisa idatha evela kumathebula amaningi
- Kungani udinga isitayela sesixhumanisi se-R1C1 nendlela yokusivala
- Ukukopishwa ngqo kwamafomula nge-macro ngesengezo se-PLEX