Nciphisa imigqa namakholomu ku-Excel Worksheet

Nciphisa ukufinyelela ezindaweni ezingasetshenziswanga zespredishithi.

Iphepha ngalinye lomsebenzi ku-Excel lingaba nemigqa engaphezu kuka-1 000,000 kanye namakholomu angu-16,000 wolwazi, kodwa ngokuvamile akudingeki ukuthi yonke igumbi liyadingeka. Ngenhlanhla, ungakwazi ukukhawulela inani lamakholomu nemigqa ekhonjiswa kuspredishithi.

Umkhawulo Ukuphenya Ngokunciphisa Inani Lemigqa Namakholomu ku-Excel

Ngokuyinhloko, sisebenzisa ambalwa kakhulu kunani eliphezulu lemigqa namakholomu futhi ngezinye izikhathi kungase kube inzuzo ukukhawulela ukufinyelela ezindaweni ezingasetshenzisiwe zephepha lokusebenzela.

Isibonelo, ukugwema izinguquko zengozi kumininingwane ethile, ngezinye izikhathi kuyasiza ukuyibeka endaweni yesikhwama sokusebenzela lapho ingenakufinyelelwa khona.

Noma, uma abasebenzisi abanolwazi oluncane kudingeka bafinyelele ephepheni lakho lokusebenzela, ukunciphisa lapho bangaya khona kungabenza bangalahleki emigqeni engenalutho namakholomu ahlala ngaphandle kwendawo yedatha.

Nciphisa imigqa yeshidi lomsebenzi

Kungakhathaliseki ukuthi kungani, unganciphisa okwesikhashana inani lemigqa namakholomu kufinyeleleka ngokunciphisa ububanzi bemigqa namakholomu asetshenzisiwe endaweni ye- Scroll Area yephepha lokusebenzela.

Kodwa-ke, phawula ukuthi ukuguqula indawo ye- Scroll kuyinkinga yesikhashana njengoba isethwe kabusha njalo lapho ibhuku lokusebenzela livaliwe futhi livuliwe .

Ngaphezu kwalokho, ububanzi obufakiwe kufanele bube nemibandela- ayikho imingcele ekubonisweni kwamasethri ohlwini.

Isibonelo

Izinyathelo ezingezansi zisetshenziselwa ukuguqula izakhiwo zephepha lokusebenzela ukuze unciphise inani lemigqa engama-30 nenombolo yamakholomu kuya ku-26 njengoba kuboniswe esithombeni ngenhla.

1. Vula ifayela le-Excel elingenalutho.
2. Chofoza ngakwesokudla kuthebhu leshidi ngezansi ngakwesokudla kwesikrini seShidi 1 .
3. Chofoza Buka i-Code kwimenyu ukuvula iwindi le- Visual Basic for Applications (VBA) .
4. Thola iwindi leZakhiwo zeSpredishithi ekhoneni elingezansi elingakwesokunxele sewindi le-VBA umhleli.
5. Thola indawo ye- Scroll Area ohlwini lwendawo yokusebenza, njengokuboniswa esithombeni ngenhla.
6. Chofoza ebhokisini elingenalutho ngakwesokudla kwelebula le- Scroll Area .
7. Thayipha ububanzi be- a1: z30 ebhokisini.
8. Londoloza ishidi lokusebenzela.
9. Vala iwindi lomhleli we-VBA bese ubuyela iphepha lokusebenzela.
10. Hlola ishidi lokusebenzela. Akufanele ukwazi:
    • Skrolela ngezansi irowu 30 noma ngakwesokudla kwekholomu Z;
    • Chofoza kuseli ngakwesokunene noma ngezansi kwe-cell Z30 ekhasini lokusebenzela.

Qaphela: Isithombe sibonisa ububanzi obufakiwe njengo-$ A $ 1: $ Z $ 30. Lapho incwadi yokugcina igcinwa, umhleli we-VBA uneza izimpawu ze dollar ($) ukwenza izinkomba zeseli zibekwe ngokuphelele .

Susa izikhawulo zokuhambisa

Njengoba kukhonjisiwe, imingcele yomqulu ihlala kuphela uma ibhuku lokusebenzela lihlala livulekile. Indlela elula yokususa noma yimiphi imingcele yokupheqa ukugcina, ukuvala nokuvula kabusha ibhuku lokusebenzela.

Ngaphandle kwalokho, sebenzisa izinyathelo ezimbili kuya kwezine ngenhla ukuze ufinyelele Impahla yeSpredishithi ewindini lomhleli we-VBA bese ususa uhlu olubekwe ohlwini lweSpring Area Property.

Nciphisa imigqa namakholomu ngaphandle kwe-VBA

Indlela ehlukile futhi enomphela yokuvimbela indawo yomsebenzi weshidi lokusebenzela ukufihla imigqa namakholomu angasetshenzisiwe.

Lezi yizinyathelo zokufihla imigqa namakholomu ngaphandle kwebanga A1: Z30:

1. Chofoza kumugqa oholayo irowu 31 ukukhetha wonke umugqa.
2. Cindezela bese ubamba okhiye be- Shift no- Ctrl kukhibhodi.
3. Cindezela bese ukhulula ukhiye we- Down Arrow ekhibhodi ukuze ukhethe yonke imigqa kusuka emgqeni 31 kuya phansi kwephepha lokusebenzela.
4. Chofoza ngakwesokudla ezihlokweni zomugqa ukuvula imenyu yesimo.
5. Khetha Fihla kwimenyu ukufihla amakholomu akhethiwe.
6. Chofoza kukholomu ebeka ikholomu AA bese uphinda isinyathelo 2-5 ngenhla ukuze ufihle wonke amakholomu ngemuva kwekholomu Z.
7. Londoloza ibhuku kanye namakholomu kanye nemigqa ngaphandle kwebanga A1 kuya ku-Z30 lizohlala lifihlekile.

Hlanganisa Imigqa Efihliwe Namakholomu

Uma ibhuku lokusebenzela londolozwa ukugcina imigqa namakholomu kufihlwe uma kuvula futhi, izinyathelo ezilandelayo zizokhipha imigqa namakholomu kusuka esibonelweni esingenhla:

1. Chofoza kusihloko segqa irowu 30 - noma umugqa wokugcina obonakalayo kushidi lokusebenzela - ukhethe lonke umugqa.
2. Chofoza ithebhu yasekhaya yebhande .
3. Chofoza Uhlobo > Fihla & Hlanganisa > Hlanganisa imigqa ebhobheni ukuze ubuyisele imigqa efihliwe.
4. Chofoza kusihlokweni sekholomu yekholomu AA - noma ikholomu ebonakalayo yokugcina - uphinde uphinde usebenzise izinyathelo ezintathu ngenhla ukuze uhlanganise wonke amakholomu.