Okuqukethwe
Ukwakhiwa kwenkinga
Sinebanga lamaseli anedatha equkethe amaseli angenalutho:
Umsebenzi wukukhipha amaseli angenalutho, kusale amaseli kuphela anolwazi.
Indlela 1. Emahhadlahhadla futhi iyashesha
- Ukukhetha ububanzi bangempela
- Cindezela inkinobho F5, inkinobho elandelayo Qinisekisa (Okukhethekile). Ewindini elivulayo, khetha Amaseli angenalutho(Akuzi lutho) bese uchofoza OK.
Wonke amaseli angenalutho kububanzi akhethiwe.
- Sinikeza umyalo kumenyu ukususa amaseli akhethiwe: chofoza kwesokudla- Susa amaseli (Susa Amaseli) ngeshifti ephezulu.
Indlela 2: I-Array Formula
Ukwenza lula, masiqambe ububanzi bethu bokusebenza sisebenzisa Umphathi wegama (Umphathi Wegama) ithebhu formula (Amafomula) noma, ku-Excel 2003 nangaphezulu, imenyu Faka – Igama – Yabela (Faka - Igama - Chaza)
Yisho ububanzi B3:B10 Awunalutho, ibanga D3:D10 - AkukhoOkungenalutho. Ububanzi kufanele bulingane ngokulinganayo, futhi bungenziwa noma kuphi ngokuhlobene.
Manje khetha iseli lokuqala lohlu lwesibili (D3) bese ufaka le fomula ethusayo kulo:
=IF(UMUGQA() -UROW(Akunalutho)+1>NOTROWS(YesEmpty)-COUNTBLLANK(YesEmpty);””;INDIRECT(IKHELI(ELIPHANSI KAKHULU((IF(Empty<>“”),UMUGQA(Akunalutho);UROW() + IMIGWA(Akunalutho))); UMUGQA()-UROW(Akunalutho)+1); IKHOLOMU(Akunalutho); 4)))
Enguqulweni yesiNgisi kuzoba:
=IF(UROW()-UROW(Akunalutho)+1>IMIGWA(Ayinalutho)-COUNTBLLANKI(Ayinalutho),"",IINDRECT(IKHELI(ELINCINCI((IF(Alinalutho<>“),UMUGQA(Awunalutho),UROW() +IMIGQA(Yiba Nalutho))),UMGQWU()-UROW(Akunalutho)+1),IKHOLOMU(Yiba Nalutho),4)))
Ngaphezu kwalokho, kufanele ifakwe njengefomula yamalungu afanayo, okungukuthi cindezela ngemva kokunamathisela Faka (njengenjwayelo) futhi Ctrl + Shift + Faka. Manje ifomula ingakopishwa phansi kusetshenziswa ukuqedela ngokuzenzakalela (hudula isiphambano esimnyama ekhoneni elingezansi kwesokudla seseli) - futhi sizothola ububanzi bangempela, kodwa ngaphandle kwamaseli angenalutho:
Indlela 3. Umsebenzi wangokwezifiso ku-VBA
Uma kukhona izinsolo zokuthi ngokuvamile kuzodingeka ukuthi uphinde inqubo yokukhipha amaseli angenalutho kububanzi, ngakho-ke kungcono ukwengeza umsebenzi wakho wokukhipha amaseli angenalutho kusethi evamile kanye, futhi uyisebenzise kuzo zonke izimo ezilandelayo.
Ukuze wenze lokhu, vula i-Visual Basic Editor (I-ALT + F11), faka imojuli entsha engenalutho (imenyu Faka – Imojula) bese ukopisha umbhalo walo msebenzi lapho:
Umsebenzi NoBlanks(DataRange As Range) Njengokuhluka() Dim N As Long Dim N2 As Long Dim Rng NjengoRange Dim MaxCells Njengomphumela Omnyama Omude() Njengokwehluka Dim R Njengoba Kungu-Dim C Omude NjengoMaxCells Amade = Application.WorksheetFunction.Max( _ Isicelo.Caller.Cells.Count, DataRange.Cells.Count) ReDim Result(1 To MaxCells, 1 To 1) Ku-Rng Ngayinye Ku-DataRange.Amaseli Uma Rng.Value <> vbNullString Bese kuba N = N + 1 Umphumela(N, 1 ) = Rng.Value End Uma Okulandelayo Rng For N2 = N + 1 To MaxCells Result(N2, 1) = vbNullString Next N2 If Application.Caller.Rows.Count = 1 Bese NoBlanks = Application.Transpose(Result) Else NoBlanks = Umphumela Qeda Uma Uqeda Umsebenzi
Ungakhohlwa ukulondoloza ifayela bese ubuyela emuva usuka ku-Visual Basic Editor uye ku-Excel. Ukusebenzisa lo msebenzi esibonelweni sethu:
- Khetha ububanzi obanele bamaseli angenalutho, isibonelo F3:F10.
- Iya kumenyu Faka – Umsebenzi (Faka - Umsebenzi)noma chofoza inkinobho Faka umsebenzi (Faka Umsebenzi) ithebhu formula (Amafomula) ezinguqulweni ezintsha ze-Excel. Esigabeni Ukuchazwa Komsebenzisi (Umsebenzisi Uchaziwe) khetha umsebenzi wethu NoBlanks.
- Cacisa ububanzi bomthombo ngama-voids (B3:B10) njengokuphikisana komsebenzi bese ucindezela Ctrl + Shift + Fakaukufaka umsebenzi njengefomula yamalungu afanayo.
:
- Isusa yonke imigqa engenalutho etafuleni ngesikhathi esisodwa nge-macro elula
- Ukukhipha yonke imigqa engenalutho ekhasini lokusebenza ngesikhathi esisodwa kusetshenziswa isengezo se-PLEX
- Gcwalisa ngokushesha wonke amaseli angenalutho
- Ayini ama-macros, lapho ungafaka khona ikhodi ye-macro ku-VBA