Odstranjevanje praznih vrstic in stolpcev v podatkih

Prazne vrstice in stolpci so v mnogih primerih lahko težava v tabelah. Standardne funkcije za razvrščanje, filtriranje, povzemanje, ustvarjanje vrtilnih tabel itd. zaznavajo prazne vrstice in stolpce kot prelom tabele, ne da bi pobrali podatke, ki se nahajajo dlje za njimi. Če je takšnih vrzeli veliko, je lahko ročno odstranjevanje zelo drago in ne bo delovalo, da bi odstranili vse naenkrat "v razsutem stanju" s filtriranjem, ker se bo filter tudi "spotaknil" ob prelomih.

Oglejmo si več načinov za rešitev te težave.

Metoda 1. Iskanje praznih celic

To morda ni najbolj priročen, vsekakor pa je najlažji način vreden omembe.

Recimo, da imamo opravka s takšno tabelo, ki vsebuje veliko praznih vrstic in stolpcev (označeno zaradi jasnosti):

Recimo, da smo prepričani, da prvi stolpec naše tabele (stolpec B) vedno vsebuje ime mesta. Potem bodo prazne celice v tem stolpcu znak nepotrebnih praznih vrstic. Če želite vse hitro odstraniti, naredite naslednje:

  1. Izberite obseg z mesti (B2:B26)
  2. Pritisnite tipko F5 in nato pritisnite Označite (Pojdi na posebno) ali izberite na zavihku Domov — Najdi in izberi — Izberite skupino celic (Domov — Najdi&Izberi — Pojdi na posebno).
  3. V oknu, ki se odpre, izberite možnost Prazne celice (Prazne) in pritisnite OK – vse prazne celice v prvem stolpcu naše tabele morajo biti označene.
  4. Zdaj izberite na zavihku Domov Ukaz Izbriši – izbrišite vrstice iz lista (Izbriši — Izbriši vrstice) ali pritisnite bližnjico na tipkovnici Ctrl+minus – in naša naloga je rešena.

Seveda se lahko na povsem enak način znebite praznih stolpcev, pri čemer uporabite glavo tabele kot osnovo.

2. način: Iskanje praznih vrstic

Kot ste morda že ugotovili, bo prejšnja metoda delovala le, če naši podatki nujno vsebujejo popolnoma izpolnjene vrstice in stolpce, ki jih je mogoče zaskočiti pri iskanju praznih celic. Kaj pa, če tega zaupanja ni in podatki lahko vsebujejo tudi prazne celice?

Za takšen primer si na primer oglejte naslednjo tabelo:

Tukaj bo pristop nekoliko bolj zapleten:

  1. V celico A2 vnesite funkcijo COUNT (COUNTA), ki bo izračunal število zapolnjenih celic v vrstici na desni in kopiral to formulo navzdol v celotno tabelo:
  2. Izberite celico A2 in z ukazom vklopite filter Podatki – Filter (Podatki — Filter) ali bližnjico na tipkovnici Ctrl+Shift+L.
  3. Izločimo ničle po izračunanem stolpcu, torej vse vrstice, kjer ni podatkov.
  4. Ostaja še izbrati filtrirane vrstice in jih izbrisati z ukazom Domov — Izbriši -' Izbriši vrstice iz lista (Domov — Izbriši — Izbriši vrstice) ali bližnjico na tipkovnici Ctrl+minus.
  5. Filter izklopimo in podatke dobimo brez praznih vrstic.

Na žalost tega trika ni več mogoče izvesti s stolpci – Excel se še ni naučil filtriranja po stolpcih.

3. način. Makro za odstranitev vseh praznih vrstic in stolpcev na listu

Za avtomatizacijo te naloge lahko uporabite tudi preprost makro. Pritisnite bližnjico na tipkovnici druga+F11 ali izberite na zavihku razvijalec - Visual basic (Razvijalec — urejevalnik Visual Basic). Če zavihki razvijalec ni viden, ga lahko omogočite prek Datoteka – Možnosti – Nastavitev traku (Datoteka — Možnosti — Prilagodi trak).

V oknu urejevalnika Visual Basic, ki se odpre, izberite menijski ukaz Vstavi – Modul in v prazen modul, ki se prikaže, kopirajte in prilepite naslednje vrstice:

   Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 Then If rng ni nič, potem nastavite rng = vrstice(r) Else nastavite rng = zveza(rng, vrstice(r)) End If Next r Če ni rng ni nič potem rng.Delete 'удаляем пустые столбцы Nastavite rng = nič za r = 1 Za ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 Then If rng Is Nothing Then Set rng = Columns(r) Else Set rng = Union(rng, Columns( r)) End If Next r If Not rng Is Nothing Then rng.Delete End Sub

Zaprite urejevalnik in se vrnite v Excel. 

Zdaj pritisnite kombinacijo druga+F8 ali gumb Makri tab razvijalec. V oknu, ki se odpre, so navedeni vsi makri, ki so trenutno na voljo za zagon, vključno z makrom, ki ste ga pravkar ustvarili. DeleteEmpty. Izberite ga in kliknite gumb Run (teči) – vse prazne vrstice in stolpci na listu bodo takoj izbrisani.

4. način: Power Query

Drug način za rešitev naše težave in zelo pogost scenarij je odstranitev praznih vrstic in stolpcev v Power Query.

Najprej naložimo našo tabelo v urejevalnik poizvedb Power Query. Lahko ga pretvorite v dinamičnega »pametnega« z bližnjico na tipkovnici Ctrl+T ali preprosto izberete naš obseg podatkov in ga poimenujete (npr. datum) v vrstici s formulami, pretvorba v named:

Sedaj uporabimo ukaz Data – Get data – From table/range (Podatki – Get Data – From table/range) in vse naložimo v Power Query:

Potem je vse preprosto:

  1. Prazne vrstice brišemo z ukazom Domov – Zmanjšaj vrstice – Brisanje vrstic – Brisanje praznih vrstic (Domov – Odstrani vrstice – Odstrani prazne vrstice).
  2. Z desno miškino tipko kliknite naslov prvega stolpca Mesto in v kontekstnem meniju izberite ukaz Odvrni druge stolpce. Naša miza bo, kot se tehnično pravilno imenuje, normalizirano – pretvorjen v tri stolpce: mesto, mesec in vrednost iz presečišča mesta in meseca iz prvotne tabele. Posebnost te operacije v Power Queryju je, da preskoči prazne celice v izvornih podatkih, kar potrebujemo:
  3. Zdaj izvedemo obratno operacijo - nastalo tabelo spremenimo nazaj v dvodimenzionalno, da jo vrnemo v prvotno obliko. Izberite stolpec z meseci in na zavihku Preoblikovanje izberite ekipo Vrtilni stolpec (Pretvorba — vrtilni stolpec). V oknu, ki se odpre, kot stolpec vrednosti izberite zadnjo (Vrednost), v naprednih možnostih pa operacijo Ne združevati (Ne seštevaj):
  4. Ostaja še naložiti rezultat nazaj v Excel z ukazom Domov — Zapri in naloži — Zapri in naloži v… (Domov — Zapri&Naloži — Zapri&Naloži na…)

  • Kaj je makro, kako deluje, kam kopirati besedilo makra, kako zagnati makro?
  • Polnjenje vseh praznih celic na seznamu z vrednostmi nadrejenih celic
  • Odstranjevanje vseh praznih celic iz danega obsega
  • Odstranjevanje vseh praznih vrstic na delovnem listu z dodatkom PLEX

Pustite Odgovori