Contents
Samar da matsala
Muna da kewayon sel masu bayanan da suka ƙunshi sel mara komai:
Aikin shine cire sel mara komai, barin sel kawai tare da bayanai.
Hanyar 1. M da sauri
- Zaɓin kewayon asali
- Latsa madannin F5, maballin gaba haskaka (Na musamman). A cikin taga da yake buɗewa, zaɓi Kwayoyin fanko(Blanks) kuma danna OK.
An zaɓi duk sel mara komai a cikin kewayon.
- Muna ba da umarni a cikin menu don share zaɓaɓɓun sel: danna-dama- Share sel (Goge Sel) tare da motsi sama.
Hanyar 2: Tsarin Tsara
Don sauƙaƙe, bari mu sanya sunayen kewayon aikin mu ta amfani da Mai sarrafa suna (Name Manager) tab tsari (Formula) ko, a cikin Excel 2003 da kuma tsofaffi, menu Saka - Suna - Sanya (Saka - Suna - Ƙayyadaddun)
Sunan kewayon B3:B10 Ba komai, kewayon D3:D10 - Babu Babu Komai. Matsakaicin dole ne su kasance daidai girman girman, kuma ana iya kasancewa a ko'ina dangane da juna.
Yanzu zaɓi tantanin halitta na farko na kewayo na biyu (D3) kuma shigar da wannan dabara mai ban tsoro a ciki:
= IF (ROW () -ROW (NoEmpty) + 1> NOTROWS (YesEmpty) - COUNTBLANK (YesEmpty) + ROWS (Akwai fanko))); LINE () ROW (Babu komai)
A cikin harshen Ingilishi zai kasance:
= IF (ROW ()-ROW (Babu komai) + 1> ROW (Ba komai) - COUNTBLANK (Ba komai),””, GASKIYA (ADRESS( KARAMAN ((IF (Ba komai) >>”,ROW (Ba komai),ROW() + ROWS(Basa Bakwai))), ROW()-ROW(Babu Babu)
Haka kuma, dole ne a shigar da shi azaman tsarin tsararru, watau latsa bayan liƙa Shigar (kamar yadda aka saba) kuma Ctrl + Shigar + Shigar. Yanzu ana iya kwafin dabarar ta amfani da autocomplete (jawo giciye baƙar fata a cikin kusurwar dama na tantanin halitta) - kuma za mu sami kewayon asali, amma ba tare da komai ba:
Hanyar 3. Ayyukan al'ada a cikin VBA
Idan akwai tuhuma cewa sau da yawa za ku sake maimaita hanya don cire sel marasa komai daga jeri, to yana da kyau ku ƙara aikin ku don cire sel mara komai a daidaitaccen saiti sau ɗaya, kuma amfani dashi a duk lokuta masu zuwa.
Don yin wannan, buɗe Editan Kayayyakin Kayayyakin Kayayyakin (Visual Basic Editor)ALT + F11), saka sabon tsarin komai (menu Saka - Module) da kwafi rubutun wannan aikin a can:
Aiki NoBlanks(DataRange As Range) As Variant() Dim N As Long Dim N2 As Dogon Dim Rng As Range Dim MaxCells As Dogon Dim Result() As Variant Dim R As Long Dim C As Long MaxCells = Application.WorksheetFunction.Max( _ Application.Caller.Cells.Count, DataRange.Cells.Count) ReDim Result(1 To MaxCells, 1 To 1) For Kowane Rng A DataRange.Cells If Rng.Value <> vbNullString Sai N = N + 1 Result(N, 1) ) = Ƙarshen Rng.Value Idan Rng Na gaba Na N2 = N + 1 Zuwa Sakamakon MaxCells(N2, 1) = vbNullString Na gaba N2 Idan Application.Caller.Rows.Count = 1 Sai NoBlanks = Application.Transpose(Sakamakon) Else NoBlanks = Result Ƙarshe Idan Ƙarshen Aiki
Kar a manta da adana fayil ɗin kuma canza baya daga Editan Kayayyakin Kayayyakin Kayayyakin zuwa Excel. Don amfani da wannan aikin a cikin misalinmu:
- Zaɓi isassun kewayon sel mara komai, misali F3:F10.
- Je zuwa menu Saka - Aiki (Saka - Aiki)ko danna maballin Saka aiki (Saka Aiki) tab tsari (Formula) a cikin sababbin sigogin Excel. A cikin rukuni Ma'anar Mai amfani (An bayyana mai amfani) zabi aikin mu NoBlanks.
- Ƙayyade kewayon tushe tare da voids (B3:B10) azaman hujjar aikin kuma latsa Ctrl + Shigar + Shigardon shigar da aikin azaman tsarin tsararru.
:
- Share duk layuka marasa komai a cikin tebur lokaci guda tare da macro mai sauƙi
- Cire duk layuka marasa komai a cikin takardar aiki lokaci ɗaya ta amfani da ƙarawar PLEX
- Cike da sauri duk sel mara komai
- Menene macros, inda za a saka lambar macro a cikin VBA