Uklanjanje praznih redova i kolona u podacima

Prazni redovi i kolone mogu biti problem u tabelama u mnogim slučajevima. Standardne funkcije za sortiranje, filtriranje, sumiranje, kreiranje pivot tabela, itd. percipiraju prazne redove i kolone kao prelom tabele, bez prikupljanja podataka koji se nalaze dalje iza njih. Ako ima mnogo takvih praznina, onda njihovo ručno uklanjanje može biti vrlo skupo, a neće uspjeti ukloniti sve odjednom "na veliko" korištenjem filtriranja, jer će se filter također "posrnuti" u prekidima.

Pogledajmo nekoliko načina za rješavanje ovog problema.

Metoda 1. Potražite prazne ćelije

Ovo možda nije najpogodniji, ali svakako najlakši način je vrijedan spomena.

Pretpostavimo da imamo posla s takvom tablicom koja sadrži mnogo praznih redova i stupaca unutra (naglašeno radi jasnoće):

Pretpostavimo da smo sigurni da prva kolona naše tabele (kolona B) uvek sadrži ime grada. Tada će prazne ćelije u ovoj koloni biti znak nepotrebnih praznih redova. Da biste ih sve brzo uklonili, učinite sljedeće:

  1. Odaberite raspon s gradovima (B2:B26)
  2. Pritisnite tipku F5 a zatim pritisnite Označite (Idi na Specijalno) ili odaberite na kartici Početna — Pronađite i odaberite — Odaberite grupu ćelija (Početna — Find&Select — Idi na posebno).
  3. U prozoru koji se otvori odaberite opciju Prazne ćelije (praznine) i pritisnite OK – treba izabrati sve prazne ćelije u prvoj koloni naše tabele.
  4. Sada odaberite na kartici Početna naredba Izbriši – Izbrišite redove sa lista (Izbriši — Izbriši redove) ili pritisnite prečicu na tastaturi Ctrl+minus – i naš zadatak je riješen.

Naravno, možete se riješiti praznih kolona na potpuno isti način, koristeći zaglavlje tablice kao osnovu.

Metoda 2: Potražite prazne redove

Kao što ste možda već shvatili, prethodna metoda će funkcionirati samo ako naši podaci nužno sadrže potpuno popunjene redove i stupce, na koje se može zakačiti kada tražite prazne ćelije. Ali što ako nema tog povjerenja, a podaci mogu sadržavati i prazne ćelije?

Pogledajte sljedeću tabelu, na primjer, samo za takav slučaj:

Ovdje će pristup biti malo složeniji:

  1. Unesite funkciju u ćeliju A2 COUNT (COUNTA), koji će izračunati broj popunjenih ćelija u redu s desne strane i kopirati ovu formulu dolje u cijelu tablicu:
  2. Odaberite ćeliju A2 i naredbom uključite filter Podaci – Filter (Podaci — Filter) ili prečica na tastaturi Ctrl+smjena+L.
  3. Filtrirajmo nule po izračunatoj koloni, odnosno svim redovima u kojima nema podataka.
  4. Ostaje odabrati filtrirane linije i izbrisati ih naredbom Početna — Izbriši -' Brisanje redova sa lista (Početna — Izbriši — Izbriši redove) ili prečica na tastaturi Ctrl+minus.
  5. Isključujemo filter i dobijamo podatke bez praznih linija.

Nažalost, ovaj trik se više ne može izvesti sa stupcima – Excel još nije naučio kako filtrirati po stupcima.

Metoda 3. Makro za uklanjanje svih praznih redova i kolona na listu

Također možete koristiti jednostavan makro za automatizaciju ovog zadatka. Pritisnite prečicu na tastaturi alt+F11 ili izaberite sa kartice Developer - Visual basic (Programer — Visual Basic Editor). Ako tabs Developer nije vidljiv, možete ga omogućiti putem Datoteka – Opcije – Podešavanje trake (Datoteka — Opcije — Prilagodi traku).

U prozoru uređivača Visual Basica koji se otvori, izaberite komandu menija Umetak – Modul i u prazan modul koji se pojavi kopirajte i zalijepite sljedeće redove:

   Sub DeleteEmpty() Dim r As Long, rng As Range 'udalâem pustye stroke Za r = 1 Za ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 Then If rng nije ništa Tada Postavi rng = Redovi(r) Drugo Postavi rng = Unija(rng, Redovi(r)) Kraj ako je sljedeći r Ako nije rng nije ništa Onda rng.Delete 'udalâem pustye stolbcy Postavi rng = Ništa Za r = 1 Do ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count Ako je Application.CountA(Columns(r)) = 0 Onda ako rng nije ništa onda postavite rng = Columns(r) Drugo Postavite rng = Union(rng, Columns( r)) End If Next r Ako nije rng nije ništa onda rng.Delete End Sub

Zatvorite uređivač i vratite se u Excel. 

Sada pritisnite kombinaciju alt+F8 ili dugme Macros tabulator Developer. U prozoru koji se otvori biće prikazani svi makroi koji su vam trenutno dostupni za pokretanje, uključujući makro koji ste upravo kreirali. DeleteEmpty. Odaberite ga i kliknite na dugme trčanje (trčati) – svi prazni redovi i kolone na listu će se odmah izbrisati.

Metod 4: Power Query

Drugi način rješavanja našeg problema i vrlo čest scenarij je uklanjanje praznih redova i stupaca u Power Queryju.

Prvo, učitajmo našu tabelu u Power Query Query Editor. Možete ga pretvoriti u dinamički „pametni“ pomoću prečice na tipkovnici Ctrl+T ili jednostavno odaberite naš raspon podataka i dajte mu ime (npr. podaci) u traci formule, pretvarajući se u named:

Sada koristimo naredbu Data – Get data – From table/range (Data – Get Data – From table/range) i učitavamo sve u Power Query:

Onda je sve jednostavno:

  1. Prazne redove brišemo naredbom Početna – Smanji redove – Obriši linije – Obriši prazne redove (Home – Ukloni redove – Ukloni prazne redove).
  2. Kliknite desnim tasterom miša na naslov prve kolone Grad i izaberite naredbu Unpivot Other Columns iz kontekstnog menija. Naš sto će biti, kako se tehnički ispravno zove, normalizovan – konvertovano u tri kolone: ​​grad, mesec i vrednost sa preseka grada i mesec iz originalne tabele. Posebnost ove operacije u Power Queryju je da preskače prazne ćelije u izvornim podacima, što nam je potrebno:
  3. Sada izvodimo obrnutu operaciju – rezultujuću tablicu vraćamo u dvodimenzionalnu kako bismo je vratili u prvobitni oblik. Odaberite kolonu s mjesecima i na kartici transformacija izabrati tim Pivot kolona (Transformacija — zaokretna kolona). U prozoru koji se otvori, kao kolonu vrijednosti, odaberite posljednju (Vrijednost), a u naprednim opcijama – operaciju Nemojte agregirati (Nemoj agregirati):
  4. Ostaje da prenesete rezultat nazad u Excel pomoću naredbe Početna — Zatvori i učitaj — Zatvori i učitaj u… (Početna — Zatvori&Učitaj — Zatvori&Učitaj na…)

  • Šta je makro, kako funkcioniše, gde kopirati tekst makroa, kako pokrenuti makro?
  • Popunjavanje svih praznih ćelija na listi vrijednostima roditeljskih ćelija
  • Uklanjanje svih praznih ćelija iz datog raspona
  • Uklanjanje svih praznih redova na radnom listu sa PLEX dodatkom

Ostavite odgovor