Ngingayisusa kanjani yonke imigqa engenalutho ku-Excel ngokuzenzakalelayo

Ikhono lokusebenza nohlelo lwe-Excel luwusizo ngempela kubameleli bemisebenzi eminingi. Kulesi sihloko, sizocubungula izinto eziningi - ikakhulukazi, kungani ukususa imigqa ku-Excel usebenzisa uhlelo “lokukhetha amaseli angenalutho -> susa umugqa” akuwona umqondo omuhle kakhulu. Sizobuye sihlaziye Izindlela ezi-3 ezisheshayo nezithembekile zokususa olayini abangenalutho ngakho, ukuze ingalimazi ukwaziswa kwamanye amaseli nganoma iyiphi indlela. Zonke izixazululo zisebenza ku-Excel 2019, 2016, 2013 nangaphambili.

Izindlela ezi-3 ezisheshayo nezithembekile zokususa olayini abangenalutho 

Njengoba ubuka lesi sihloko njengamanje, kunengqondo ukucabanga ukuthi, njengabanye abaningi, kufanele ubhekane njalo amaspredishithi e-excel anosayizi omkhulu. Amathuba okuthi uhlangabezane nezimo lapho imigqa engenalutho, ukuvimbela iningi lamathuluzi akhelwe ngaphakathi ekuboneni kahle ububanzi bedatha. Ngakho, isikhathi ngasinye, kufanele ucacise imingcele ngesandla - ngaphandle kwalokho uzothola umphumela ongalungile, futhi kuzokuthatha isikhathi esingaphezu kwehora elilodwa ukuthola nokulungisa amaphutha anjalo. 

Kungase kube nezizathu ezihlukahlukene zokuthi kungani kuvele imigqa engenalutho - isibonelo, uthole ifayela le-Excel komunye umuntu, noma ifayela lathunyelwa lisuka kusizindalwazi esithile, noma usule imininingwane emigqeni engadingeki ngephutha. Ngaphansi kwanoma yiziphi izimo, uma udinga susa imigqa engenalutho futhi ngenxa yalokho ukuba netafula elihlanzekile nelihle, udinga ukwenza uchungechunge lwezinyathelo ezilula. Ake sibheke izimo ezimbalwa eziqondile ezihlokweni ezilandelayo:

• Kungani ungakhiphi imigqa engenalutho ngokukhetha amaseli angenalutho.
• Indlela yokuqeda yonke imigqa engenalutho uma kukhona ikholomu yokhiye.
• Indlela yokuqeda yonke imigqa engenalutho uma ingekho ikholomu yokhiye.
• Lisetshenziswa kanjani ithuluzi le-Delete Empty Lines, nokuthi kungani kuyindlela elula kakhulu, futhi okubaluleke kakhulu, indlela eshesha kakhulu.

Ungasusi imigqa engenalutho ngokukhetha amaseli angenalutho  

Ku-inthanethi, ungathola iseluleko esilandelayo:

• Khetha wonke amaseli aqukethe ulwazi, kusukela ku-1 kuya ekugcineni.
• Cindezela inkinobho F5 - Ngenxa yalokho, ibhokisi lengxoxo "Inguquko".
• Ngaphakathi kwewindi elivulayo, chofoza "Qinisekisa".
• Ewindini "Ukukhetha iqembu lamaseli» khetha inketho «amaseli angenalutho", bese"OK".
• Chofoza kwesokudla kunoma iyiphi iseli ekhethiwe, bese ukhetha “Susa...".
• Ngaphakathi kwewindi elivulekayoIsusa amaseli» chofoza inketho «yezinhlamvu". 

Ngeshwa, lena akuyona indlela engcono kakhulu - isebenzise kuphela amatafula amancane lapho imigqa iboniswa khona esikrinini, noma okungcono nakakhulu - ungayisebenzisi nhlobo.

Lokhu kungenxa yokuthi uma kukhona iseli eyodwa kuphela engenalutho kulayini oqukethe ulwazi olubalulekile, wonke umugqa uyasuswa. 

Ake sibheke isibonelo. Ngaphambi kwethu kunetafula lamakhasimende, kuwo kuphela 6 ithemu. Sifuna susa imigqa yesithathu neyesihlanu, njengoba bengenalutho.

Sebenzisa le ndlela engenhla futhi uzothola okulandelayo:

Ulayini 4 (Rojer) nawo awukho, ngoba kukholamu "Umthombo wethrafikhi".  iseli D4 alinalutho.

Njengoba unetafula elincane, uzokwazi ukulithola ukuntuleka kwedatha, kodwa kumathebula amakhulu aqukethe izinkulungwane zemigqa, ungasusa amarowu amaningi adingekayo ngaphandle kokwazi. Uma unenhlanhla uzoqaphela ukuthi ayikho emahoreni ambalwa, buyisela ifayela lakho kusuka Isipelebese uyakwenza futhi. Kodwa kuthiwani uma uphelelwe inhlanhla noma ungenayo ikhophi yasenqolobaneni? 

Ake sibheke Izindlela Ezi-3 Ezisheshayo Nezethembekile Zokukhipha Imigqa Engenalutho kusuka kumaspredishithi akho e-Excel. Futhi uma ufuna ukonga isikhathi sakho - hamba ngqo ku Indlela yesi-3.

Susa yonke imigqa engenalutho uma kukhona ikholomu yokhiye

The indlela Isebenza inqobo nje uma unekholomu ezokusiza ukuthi uthole ukuthi iyunithi yezinhlamvu ayinalutho noma cha (okubizwa kanjalo ikholomu yokhiye). Isibonelo, lokhu kungaba inombolo ye-oda, noma i-ID yekhasimende, noma into efanayo.

Kudingeka sihambe ukulandelana kwentambo okungashintshiwe, ngakho-ke, ukumane uhlele ngale kholomu futhi uhambise yonke imigqa engenalutho ekupheleni kwethebula ngeke kusebenze. Manje ngidinga ukukwenza.

1. Khetha ithebula ngokuphelele, kusukela kumugqa wokuqala kuya kumugqa wokugcina (ukwenza lokhu, ungabamba kanyekanye Ikhaya le-Ctrl, Ngaphezu kwalokho - Ctrl + Shift + End).

2. Misa I-Autofilter: iya kuthebhu "Idatha” bese uchofoza inkinobho ethi “Hlunga".

3. Okulandelayo, udinga ukusebenzisa isihlungi kukholamu ethi “Sika #” (“Inombolo Yekhasimende.”): chofoza umcibisholo odonsela phansi othi “Isihlungi esizenzakalelayo” egameni lekholomu, susa ukumaka (khetha konke), skrolela phansi uze ufike ekugcineni (empeleni uhlu lude kakhulu), bese khetha ibhokisi elithi “Akunalutho”… Chofoza OK.

4. Hlanganisa yonke imigqa ehlungiwe: kulokhu ungabamba ngesikhathi esifanayo Ikhaya le-Ctrl, bese kuba inkinobho yomcibisholo oya phansi ukuze ubuyele emugqeni wokuqala futhi, bese ubamba Ctrl + Shift + End.

5. Chofoza kwesokudla kunoma iyiphi iseli ekhethiwe bese uchofoza ku-“Susa umugqa»noma vele ucindezele Ctrl + – (uphawu lokususa).

6. Chofoza inkinobho OK lapho ephendula umbuzoSusa lonke igama lencwadi?»

7. Ngemuva kwalokho udinga ukusula isihlungi esisetshenzisiwe: ukwenza lokhu, iya ku- “Idatha” bese uchofoza inkinobho ethi “Clean".

8. Umsebenzi omuhle! Yonke imigqa engenalutho ayisekho, futhi umugqa wesithathu (Rojer) usekhona (ukuze uqhathanise, ungabheka inguqulo yangaphambilini).

Susa yonke imigqa engenalutho uma ingekho ikholomu yokhiye    

Sebenzisa lokhu indlelauma unetafula emsebenzini wakho elinenani elikhulu lamaseli angenalutho abekwe kumakholomu ahlukahlukene, futhi ufuna ukususa ngqo leyo migqa ayinalutho ngokuphelele.

ikholomu yokhiye, engasetshenziswa ukuthola ukuthi iyunithi yezinhlamvu ayinalutho noma cha, ayikho esibonelweni sethu. Okufanele ngikwenze? Thina ngokwethu dala ikholomu eyengeziwe:

1. Dala ikholomu “Izikhala” (“Amaseli angenalutho”) ekugcineni kwethebula lakho, bese ubhala kuseli lokuqala lale kholomu. ifomula: = COUNTBLLANK (A2: C2).

Lokhu formula ibala amaseli angenalutho kulokho okunqunyiwe uhla, lapho i-A2 iyiseli lokuqala, i-C1 iyiseli lokugcina.

2. Kopisha ifomula kuwo wonke amaseli kukholamu.

3. Manje sesinayo ikholomu yokhiye. Bese usebenzisa lemifanekiso kukholamu ethi “Akuzi lutho” (imiyalo enemininingwane ngenhla) ukuze kuboniswe imigqa enenani eliphakeme (3). “3” kusho okulandelayo: wonke amaseli kumugqa akanalutho.

4. Bese ukhetha konke imigqa ehlungiwe futhi ususe ngokuphelele Ayinaluthousebenzisa imiyalelo echazwe ngaphambilini.

Ngakho-ke umugqa ongenalutho (umugqa wesi-5) isusiwe, kanye nemigqa enolwazi oludingekayo hlala endaweni.

5. Okulandelayo, susa ikholomu eyengeziwe. Ngeke kusadingeka. Noma ungabeka enye lemifanekiso futhi ubonise imigqa lapho kuneseli eyodwa noma amaningi angenalutho.

Ukuze wenze lokhu, susa ukumaka i-0", bese uchofoza"OK".

Ukusebenzisa ithuluzi elithi Susa Imigqa Engenalutho kuyindlela elula neshesha kakhulu  

Indlela eshesha kakhulu nengenasici yokususa imigqa engenalutho iyithuluziSusa imigqa engenalutho”, kufakwe kukhithi I-Ultimate Suite ye-Excel.

Phakathi kokunye okuwusizo imisebenzi iqukethe eziningana Tinsita, okuvumela ukuchofoza okukodwa ukuhambisa amakholomu ngokuhudula; susa wonke amaseli angenalutho, imigqa namakholomu, kanye nokuhlunga ngenani elikhethiwe, bala amaphesenti, sebenzisa noma yikuphi ukusebenza kwezibalo okuyisisekelo kububanzi, kopisha amakheli eseli ebhodini lokunamathisela nokunye okuningi.

Uyisusa kanjani imigqa engenalutho ngezinyathelo ezi-4 ezilula

Ukusebenzisa i-Ultimate Suite, ngaphezu kwalokho efakwe kuhlelo lwe-Excel, yilokho okudingayo do:

1. Chofoza kunoma yikuphi iseli etafuleni.
2. Chofoza ithebhu Amathuluzi Amandla > Guqula iqembu.
3. Cindezela Susa imigqa engenalutho > Imigqa engenalutho.

4. Chofoza inkinobho OKukuze uqinisekise ukuthi ufuna ngempela ukususa imigqa engenalutho.

Yilokho kuphela! Ukuchofoza okumbalwa nje futhi uzothola itafula elihlanzekile, yonke imigqa engenalutho ayisekho, futhi ukuhleleka kwemigqa akuhlanekezelwe!