Uklanjanje praznih ćelija iz raspona

Formulacija problema

Imamo raspon ćelija s podacima koji sadrže prazne ćelije:

 

Zadatak je ukloniti prazne ćelije, ostavljajući samo ćelije sa informacijama.

Metoda 1. Grubo i brzo

  1. Odabir originalnog raspona
  2. Pritisnite tipku F5, dugme sledeće Označite (Poseban). U prozoru koji se otvori odaberite Prazne ćelije(praznine) i kliknite OK.

    Uklanjanje praznih ćelija iz raspona

    Sve prazne ćelije u rasponu su odabrane.

  3. U meniju dajemo naredbu za brisanje odabranih ćelija: desnim klikom- Izbrišite ćelije (Izbriši ćelije) sa pomakom prema gore.

Metoda 2: Formula niza

Da pojednostavimo, nazovimo naše radne opsege pomoću Ime Manager (Upravitelj imena) tabulator formula (Formule) ili, u programu Excel 2003 i starijim, meni Umetnuti – Ime – Dodijeliti (Umetnuti — Naziv — Definirati)

 

Imenujte raspon B3:B10 HaveEmpty, raspon D3:D10 – NoneEmpty. Opsegovi moraju biti striktno iste veličine i mogu se nalaziti bilo gdje u odnosu jedan na drugi.

Sada odaberite prvu ćeliju drugog raspona (D3) i unesite ovu zastrašujuću formulu u nju:

=IF(ROW() -ROW(NeEmpty)+1>NOTROWS(YesEmpty)-COUNTBLANK(YesEmpty);””;INDIREKTNO(ADRESA(NAJNIŽA((IF(Prazno<>””,RED(Prazan);ROW()) + ROWS(Ima praznih))); LINE()-ROW(Nema praznih)+1); KOLONA(Ima praznih); 4)))

U engleskoj verziji to će biti:

=IF(ROW()-ROW(NeEmpty)+1>ROWS(Empty)-COUNTBLANK(Empty),””,INDIREKTNO(ADRESA(MALA((IF(Prazno<>””,RED(Prazan),ROW() +ROWS(HaveEmpty))),ROW()-ROW(NoEmpty)+1),COLUMN(HaveEmpty),4)))

Štaviše, mora se unijeti kao formula niza, tj. pritisnuti nakon lijepljenja ući (kao i obično) i Ctrl + Shift + Enter. Sada se formula može kopirati pomoću automatskog dovršavanja (prevucite crni križ u donji desni kut ćelije) - i dobit ćemo originalni raspon, ali bez praznih ćelija:

 

Metoda 3. Prilagođena funkcija u VBA

Ako postoji sumnja da ćete često morati ponavljati proceduru uklanjanja praznih ćelija iz raspona, onda je bolje jednom dodati vlastitu funkciju za uklanjanje praznih ćelija u standardni set i koristiti je u svim narednim slučajevima.

Da biste to učinili, otvorite Visual Basic Editor (ALT + F11), umetnite novi prazan modul (meni Umetak – Modul) i tamo kopirajte tekst ove funkcije:

Funkcija NoBlanks(DataRange As Range) Kao Variant() Dim N As Long Dim N2 Kao Long Dim Rng As Range Dim MaxCells Kao Long Dim Result() Kao varijanta Dim R As Long Dim C As Long MaxCells = Application.WorksheetFunction.Max( _ Application.Caller.Cells.Count, DataRange.Cells.Count) ReDim Result(1 do MaxCells, 1 do 1) Za svaki Rng u DataRange.Cells Ako Rng.Value <> vbNullString Tada je N = N + 1 Rezultat(N, 1 ) = Rng.Value End Ako je sljedeći Rng Za N2 = N + 1 Do MaxCells Rezultat(N2, 1) = vbNullString Sljedeći N2 Ako Application.Caller.Rows.Count = 1 Tada je NoBlanks = Application.Transpose(Result) Else NoBlanks = Rezultat End If End Funkcija  

Ne zaboravite da sačuvate datoteku i vratite se sa Visual Basic Editora na Excel. Da biste koristili ovu funkciju u našem primjeru:

  1. Odaberite dovoljan raspon praznih ćelija, na primjer F3:F10.
  2. Idite na meni Umetak – Funkcija (Insert — Funkcija)ili kliknite na dugme Funkcija umetanja (funkcija umetanja) tabulator formula (Formule) u novijim verzijama Excela. U kategoriji Definisano od strane korisnika (Definisano od strane korisnika) izaberite našu funkciju NoBlanks.
  3. Navedite raspon izvora s prazninama (B3:B10) kao argument funkcije i pritisnite Ctrl + Shift + Enterda unesete funkciju kao formulu niza.

:

  • Brisanje svih praznih redova u tabeli odjednom jednostavnim makroom
  • Uklanjanje svih praznih redova na radnom listu odjednom pomoću PLEX dodatka
  • Brzo popunite sve prazne ćelije
  • Šta su makroi, gde ubaciti makro kod u VBA

 

Ostavite odgovor