Tshem tawm cov hlwb khoob ntawm qhov ntau

Formulation ntawm qhov teeb meem

Peb muaj ntau lub hlwb nrog cov ntaub ntawv uas muaj cov hlwb khoob:

 

Lub luag haujlwm yog tshem tawm cov hlwb khoob, tsuas yog cov hlwb nrog cov ntaub ntawv.

Txoj Kev 1. Rough thiab ceev

  1. Xaiv tus thawj ntau yam
  2. Nias tus yuam sij F5, khawm tom ntej highlight (Tshwj xeeb). Hauv qhov rai uas qhib, xaiv Cov hlwb khoob(Vim li cas) thiab nias OK.

    Tshem tawm cov hlwb khoob ntawm qhov ntau

    Txhua lub hlwb khoob hauv qhov ntau yog xaiv.

  3. Peb muab ib tug hais kom ua nyob rau hauv cov ntawv qhia zaub mov kom tshem tawm cov cell xaiv: right-click- Rho tawm cov hlwb (Delete Cells) nrog upward hloov.

Txoj Kev 2: Array Formula

Txhawm rau ua kom yooj yim, cia peb lub npe peb cov haujlwm ua haujlwm siv Lub Npe Tus Thawj Tswj (Npe Manager) tab mis (Formulas) lossis, hauv Excel 2003 thiab laus dua, cov ntawv qhia zaub mov Ntxig – Lub Npe – Muab (Insert — Name — Define)

 

Lub npe B3: B10 Muaj Empty, ntau yam D3: D10 - NoneEmpty. Ranges yuav tsum nruj me ntsis sib npaug, thiab tuaj yeem nyob txhua qhov chaw sib txheeb.

Tam sim no xaiv thawj lub xov tooj ntawm qhov thib ob (D3) thiab nkag mus rau cov qauv txaus ntshai no rau hauv nws:

=IF(ROW() -ROW(NoEmpty)+1>NOTROWS(YesEmpty)-COUNTBLANK(YesEmpty);"";INDIRECT(ADDRESS(LOWEST((IF(Empty<>""),ROW(Empty);ROW())) + ROWS(Muaj qhov khoob))); LINE()-ROW(Tsis muaj qhov khoob)+1); COLUMN(Muaj khoob); 4)))

Hauv Askiv version nws yuav yog:

=IF(ROW()-ROW(NoEmpty)+1>ROWS(Empty)-COUNTBLANK(Empty),"",INDIRECT(ADDRESS(ME((IF(Epty<>""),ROW(Empty),)),ROW()) +ROWS(HaveEmpty))),ROW()-ROW(NoEmpty)+1),COLUMN(HaveEmpty),4)))

Ntxiv mus, nws yuav tsum tau nkag mus ua ib qho array formula, piv txwv li nias tom qab pasting Sau (raws li qub) thiab Ctrl + ua haujlwm + nkag. Tam sim no cov qauv tuaj yeem luam tawm siv autocomplete (txuas tus ntoo khaub lig dub nyob rau sab xis sab xis ntawm lub xov tooj ntawm tes) - thiab peb yuav tau txais cov khoom qub, tab sis tsis muaj lub hlwb khoob:

 

Txoj Kev 3. Kev cai ua haujlwm hauv VBA

Yog tias muaj kev xav tias koj yuav tsum tau rov ua cov txheej txheem kom tshem tawm cov khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khoob khawm

Txhawm rau ua qhov no, qhib Visual Basic Editor (ALT + F11), ntxig ib qho tshiab khoob module (menu Ntxig - Module) thiab luam cov ntawv ntawm cov haujlwm no muaj:

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, DataRange.Cells.Count) ReDim Result(1 To MaxCells, 1 To 1) For each Rng In DataRange.Cells If Rng.Value <> vbNullString Then N = N + 1 Result(N, 1 ) = Rng.Value End If Next Rng For N2 = N + 1 To MaxCells Result(N2, 1) = vbNullString Next N2 Yog Application.Caller.Rows.Count = 1 Ces NoBlanks = Application.Transpose(Result) Lwm NoBlanks = Result Xaus Yog Kawg Function

Tsis txhob hnov ​​​​qab khaws cov ntaub ntawv thiab hloov rov qab los ntawm Visual Basic Editor rau Excel. Txhawm rau siv cov haujlwm no hauv peb qhov piv txwv:

  1. Xaiv qhov ntau txaus ntawm cov hlwb khoob, piv txwv li F3: F10.
  2. Mus rau cov ntawv qhia zaub mov Ntxig - Function (Insert — Function)los yog nyem rau ntawm lub pob Ntxig muaj nuj nqi (Insert Function) tab mis (Formulas) nyob rau hauv newer versions ntawm Excel. Hauv qeb Neeg Siv Li (User Defined) xaiv peb txoj haujlwm NoBlanks.
  3. Qhia meej qhov chaw nrog voids (B3: B10) raws li kev ua haujlwm sib cav thiab nias Ctrl + ua haujlwm + nkagnkag mus rau qhov muaj nuj nqi raws li tus qauv array.

:

  • Rho tawm tag nrho cov kab khoob hauv ib lub rooj ib zaug nrog cov macro yooj yim
  • Tshem tawm tag nrho cov kab khoob hauv ib daim ntawv ua haujlwm ib zaug siv PLEX add-on
  • Quick sau tag nrho cov hlwb khoob
  • Dab tsi yog macro, qhov twg los ntxig macro code hauv VBA

 

Sau ntawv cia Ncua