Odstranjevanje praznih celic iz obsega

Formulacija problema

Imamo vrsto celic s podatki, ki vsebujejo prazne celice:

 

Naloga je odstraniti prazne celice in pustiti samo celice z informacijami.

Metoda 1. Grobo in hitro

  1. Izbira izvirnega obsega
  2. Pritisnite tipko F5, naslednji gumb Označite (Poseben). V oknu, ki se odpre, izberite Prazne celice(Prazne) in kliknite OK.

    Odstranjevanje praznih celic iz obsega

    Izbrane so vse prazne celice v obsegu.

  3. V meniju damo ukaz za brisanje izbranih celic: desni klik - Izbriši celice (Izbriši celice) s premikom navzgor.

2. način: matrična formula

Za poenostavitev poimenujmo naša delovna območja z uporabo Upravitelj imen (Upravitelj imen) tab Formula (Formule) ali v Excelu 2003 in starejših meni Vstavi – Ime – Dodeli (Vstavi — Ime — Določi)

 

Poimenujte obseg B3:B10 HaveEmpty, obseg D3:D10 – NoneEmpty. Območja morajo biti popolnoma enake velikosti in se lahko nahajajo kjerkoli drug glede na drugega.

Zdaj izberite prvo celico drugega obsega (D3) in vanjo vnesite to strašljivo formulo:

=IF(ROW() -ROW(NoEmpty)+1>NOTROWS(YesEmpty)-COUNTBLANK(YesEmpty);””;INDIRECT(ADDRESS(LOWEST((IF(Empty<>“”,ROW(Empty);ROW() + ROWS(Prazne so))); LINE()-ROW(Ni praznih)+1); COLUMN(Prazne so); 4)))

V angleški različici bo to:

=IF(ROW()-ROW(NoEmpty)+1>ROWS(Empty)-COUNTBLANK(Empty),””,INDIRECT(ADDRESS(SMALL((IF(Empty<>“”,ROW(Empty),ROW() +ROWS(ImamPrazno))),ROW()-ROW(BrezPraznega)+1),COLUMN(ImamPrazno),4)))

Poleg tega ga je treba vnesti kot matrično formulo, tj. pritisniti po lepljenju Vnesite (kot ponavadi) in Ctrl + Shift + Enter. Zdaj lahko formulo kopirate s samodokončanjem (povlecite črni križ v spodnjem desnem kotu celice) - in dobili bomo izvirni obseg, vendar brez praznih celic:

 

3. način. Funkcija po meri v VBA

Če obstaja sum, da boste morali pogosto ponavljati postopek odstranjevanja praznih celic iz obsegov, potem je bolje, da dodate lastno funkcijo za odstranjevanje praznih celic v standardni nabor enkrat in jo uporabite v vseh naslednjih primerih.

Če želite to narediti, odprite urejevalnik Visual Basic (ALT + F11), vstavite nov prazen modul (meni Vstavi – Modul) in kopirajte besedilo te funkcije tja:

Funkcija 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) Za vsak Rng v 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 If Application.Caller.Rows.Count = 1 Then NoBlanks = Application.Transpose(Result) Else NoBlanks = Rezultat Funkcija End If End

Ne pozabite shraniti datoteke in preklopiti nazaj z urejevalnika Visual Basic na Excel. Za uporabo te funkcije v našem primeru:

  1. Izberite zadosten obseg praznih celic, na primer F3:F10.
  2. Pojdite v meni Vstavi – funkcija (Vstavi — funkcija)ali kliknite na gumb Vstavi funkcijo (Vstavi funkcijo) tab Formula (Formule) v novejših različicah Excela. V kategoriji Uporabnik definiran (Uporabnik definiran) izberite našo funkcijo Noblanks.
  3. Določite izvorni obseg s prazninami (B3:B10) kot argument funkcije in pritisnite Ctrl + Shift + Enterza vnos funkcije kot matrične formule.

:

  • Brisanje vseh praznih vrstic v tabeli hkrati s preprostim makrom
  • Odstranjevanje vseh praznih vrstic na delovnem listu hkrati z uporabo dodatka PLEX
  • Hitro zapolni vse prazne celice
  • Kaj so makri, kam v VBA vstaviti kodo makra

 

Pustite Odgovori