Tse ka Hare
Tlhahiso ea bothata
Re na le mefuta e mengata ea lisele tse nang le data e nang le lisele tse se nang letho:
Mosebetsi ke ho tlosa lisele tse se nang letho, ho siea lisele tse nang le boitsebiso feela.
Mokhoa oa 1. E thata ebile e potlakile
- Ho khetha sebaka sa pele
- Tobetsa konopo F5, konopo e latelang fagahlelwago (E khethehileng). Fesetereng e butsoeng, khetha Lisele tse se nang letho(Ha ho letho) Mme o tobetse OK.
Lisele tsohle tse se nang letho sebakeng li khethiloe.
- Re fana ka taelo ho menu ho tlosa lisele tse khethiloeng: tobetsa ka ho le letona- Hlakola lisele (Hlakola Lisele) e nang le phetoho e eang holimo.
Mokhoa oa 2: Lethathamo la Foromo
Ho nolofatsa, a re boleleng libaka tsa rona tsa ts'ebetso re sebelisa Motsamaisi oa Lebitso (Mookameli oa Lebitso) tab ya moralo (Liforomo) kapa, ho Excel 2003 le ho feta, menu Kenya – Lebitso – Abela (Kenya - Lebitso - Hlalosa)
Bolela mofuta oa B3:B10 Empty, sebaka sa D3:D10 - Ha ho Empty. Mekhahlelo e tlameha ho lekana ka boholo, 'me e ka fumaneha kae kapa kae e amanang le e mong.
Joale khetha sele ea pele ea mofuta oa bobeli (D3) 'me u kenye foromo ena e tšosang ho eona:
=HAEBA(ROW() -ROW(NoEmpty)+1>NOTROWS(YesEmpty)-COUNTBLLANK(YesEmpty);””;INDIRECT(ADDRESS(TLASA KA HO FETISISA((IF(Ha e na letho<>“”),ROW(Lepty);ROW() + MELA(Ho na le Lefeela));(Ho na le Lefeela));(Ho na le Lefeela)) MOTHATA+1);(Ho na le Lefeela)) )))
Phetolelong ea Senyesemane e tla ba:
=HAEBA(ROW()-ROW(Ha ho na letho)+1>MELA(Ha ho letho)-COUNTBLLANK(Ha ho letho),””,INDIRECT(ADDRESS(NNYANE((IF(E se nang letho<>“”),ROW(Empty),ROW() +ROWS(HaveEmpty))),ROW()-ROW)-ROW+HanaEmpty)
Ho feta moo, e tlameha ho kenngoa joalo ka mokhoa o hlophisitsoeng, ke hore, tobetsa ka mor'a ho peista kena (joalo ka tloaelo) le Ctrl + Shift + Kena. Hona joale foromo e ka kopitsoa ho sebelisa autocomplete (hula sefapano se setšo k'honeng e ka tlase e ka letsohong le letona la sele) - 'me re tla fumana mofuta oa mantlha, empa ntle le lisele tse se nang letho:
Mokhoa oa 3. Mosebetsi oa tloaelo ho VBA
Haeba ho na le lipelaelo tsa hore hangata u tla tlameha ho pheta ts'ebetso ea ho tlosa lisele tse se nang letho ho tloha mefuteng, joale ho molemo ho eketsa ts'ebetso ea hau bakeng sa ho tlosa lisele tse se nang letho ho sete e tloaelehileng hang, 'me u e sebelise maemong ohle a latelang.
Ho etsa sena, bula Visual Basic Editor (ALT + F11), kenya module e ncha e se nang letho (menu Kenya – Mojule) ebe u kopitsa mongolo oa tšebetso ena moo:
Function NoBlanks(DataRange As Range) As Variant() Dim N As Long Dim N2 As Long Dim Rng As Range Dim MaxCells As Long Dim Result() As Variant Dim R As Long Dim C As Long MaxCells = Application.WorksheetFunction.Max( _ Application.Caller.Cells.Count) Result.Di Cells1 Result.Data1 Bakeng sa Rng ka 'ngoe Ka DataRange.Lisele Haeba Rng.Value <> vbNullString Joale N = N + 1 Sephetho(N, 1) = Rng.Boleng Bofelo Haeba Next Rng Bakeng sa N1 = N + 2 Ho MaxCells Result(N1, 2) = vbNullString Next N1 If Application.Caller.Rows.B.B Sephetho End If End Function
U se ke ua lebala ho boloka faele le ho khutlela morao ho tloha ho Visual Basic Editor ho ea Excel. Ho sebelisa ts'ebetso ena mohlaleng oa rona:
- Khetha mefuta e lekaneng ea lisele tse se nang letho, mohlala F3:F10.
- E-ea lenaneng Kenya – Mosebetsi (Kenya - Mosebetsi)kapa tobetsa konopo Kenya tshebetso (Kenya Mosebetsi) tab ya moralo (Liforomo) liphetolelong tse ncha tsa Excel. Ka har'a sehlopha Sesebelisoa se hlalositsoe (Mosebelisi o hlalositsoe) khetha mosebetsi oa rona NoBlanks.
- Hlalosa sebaka sa mohloli ka li-voids (B3:B10) e le khang ea mosebetsi ebe u tobetsa Ctrl + Shift + Kenaho kenya tshebetsong joalo ka foromo ya lenane.
:
- Ho hlakola mela eohle e se nang letho tafoleng ka nako e le 'ngoe ka macro e bonolo
- Ho tlosa mela eohle e se nang letho leqepheng la mosebetsi hang-hang u sebelisa tlatsetso ea PLEX
- Ka potlako tlatsa lisele tsohle tse se nang letho
- Li-macros ke eng, moo u ka kenyang khoutu ea macro ho VBA