Uklanjanje praznih redaka i stupaca u podacima

Prazni redovi i stupci mogu biti problem u tablicama u mnogim slučajevima. Standardne funkcije za sortiranje, filtriranje, sažimanje, kreiranje stožernih tablica itd. percipiraju prazne retke i stupce kao prijelom tablice, bez preuzimanja podataka koji se nalaze dalje iza njih. Ako postoji mnogo takvih praznina, tada njihovo ručno uklanjanje može biti vrlo skupo, a neće uspjeti ukloniti sve odjednom "masovno" pomoću filtriranja, jer će filtar također "posrnuti" na prekidima.

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

Metoda 1. Potražite prazne ćelije

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

Pretpostavimo da imamo posla s takvom tablicom koja sadrži mnogo praznih redaka i stupaca (označeno radi jasnoće):

Pretpostavimo da smo sigurni da prvi stupac naše tablice (stupac B) uvijek sadrži ime grada. Tada će prazne ćelije u ovom stupcu biti znak nepotrebnih praznih redaka. Da biste ih sve brzo uklonili, učinite sljedeće:

  1. Odaberite raspon s gradovima (B2:B26)
  2. Pritisnite tipku F5 i zatim pritisnite Istaknuti (Idi na Posebno) ili odaberite na kartici Početna — Pronađi i odaberi — Odaberite grupu ćelija (Početna — Pronađi i odaberi — Idi na posebno).
  3. U prozoru koji se otvori odaberite opciju Prazne ćelije (praznine) i pritisnite OK – treba označiti sve prazne ćelije u prvom stupcu naše tablice.
  4. Sada odaberite na kartici Naslovna naredba Izbriši – Izbrišite retke s lista (Izbriši — Izbriši retke) ili pritisnite tipkovni prečac Ctrl+minus – i naš zadatak je riješen.

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

Metoda 2: Traženje praznih redaka

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

Pogledajte sljedeću tablicu, na primjer, upravo za takav slučaj:

Ovdje će pristup biti malo složeniji:

  1. Unesite funkciju u ćeliju A2 TOČKA (COUNTA), koji će izračunati broj popunjenih ćelija u retku s desne strane i kopirati ovu formulu u cijelu tablicu:
  2. Odaberite ćeliju A2 i naredbom uključite filter Podaci – Filtar (Podaci — Filtar) ili tipkovni prečac Ctrl+smjena+L.
  3. Isfiltrirajmo nule po izračunatom stupcu, tj. sve retke u kojima nema podataka.
  4. Ostaje odabrati filtrirane retke i izbrisati ih naredbom Početna — Izbriši -' Izbriši retke s lista (Početna — Izbriši — Izbriši retke) ili tipkovni prečac Ctrl+minus.
  5. Isključimo filtar i dobijemo podatke bez praznih redaka.

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

Metoda 3. Makronaredba za uklanjanje svih praznih redaka i stupaca na listu

Također možete koristiti jednostavnu makronaredbu za automatizaciju ovog zadatka. Pritisnite tipkovni prečac drugo+F11 ili odaberite s kartice razvijač — Visual Basic (Razvojni programer — Visual Basic Editor). Ako su kartice razvijač nije vidljiv, možete ga omogućiti putem Datoteka – Opcije – Postavljanje vrpce (Datoteka — Opcije — Prilagodi vrpcu).

U prozoru uređivača Visual Basic koji se otvori odaberite naredbu izbornika Umetak – Modul i u praznom modulu koji se pojavi kopirajte i zalijepite sljedeće retke:

   Sub DeleteEmpty() Dim r As Long, rng As Range 'udalâem pustye stroki For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 Then If rng nije ništa onda postavi rng = redovi(r) Else postavi rng = unija(rng, redovi(r)) Kraj ako je sljedeći r ako nije rng nije ništa onda rng.Izbriši 'udalâem pustye stolbcy postavi rng = ništa 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

Zatvorite uređivač i vratite se u Excel. 

Sada pogodite kombinaciju drugo+F8 ili gumb makronaredbe kartica razvijač. U prozoru koji se otvori bit će popis svih makronaredbi koje su vam trenutno dostupne za pokretanje, uključujući makronaredbu koju ste upravo stvorili. DeleteEmpty. Odaberite ga i kliknite gumb trčanje (trčanje) – svi prazni redovi i stupci na listu bit će trenutno izbrisani.

Metoda 4: Power Query

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

Prvo učitajmo našu tablicu u uređivač upita Power Query. Možete ga pretvoriti u dinamički "pametni" tipkovničkim prečacem Ctrl+T ili samo odaberite naš raspon podataka i dajte mu naziv (npr. Datum) u traci formule, pretvarajući se u named:

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

Onda je sve jednostavno:

  1. Prazne retke brišemo naredbom Početna – Reduce lines – Delete lines – Brisanje praznih redaka (Home – Remove Rows – Ukloni prazne retke).
  2. Kliknite desnom tipkom miša na zaglavlje prvog stupca City i odaberite naredbu Unpivot Other Columns iz kontekstnog izbornika. Naš stol će biti, kako se tehnički ispravno kaže, normalizirani – pretvoreno u tri stupca: grad, mjesec i vrijednost iz sjecišta grada i mjeseca iz izvorne tablice. Posebnost ove operacije u Power Queryju je da preskače prazne ćelije u izvornim podacima, što nam je potrebno:
  3. Sada izvodimo obrnutu operaciju - rezultirajuću tablicu pretvaramo natrag u dvodimenzionalnu kako bismo je vratili u izvorni oblik. Odaberite stupac s mjesecima i na kartici transformacija izabrati tim Zakretni stupac (Transformacija — zaokretni stupac). U prozoru koji se otvori, kao stupac vrijednosti odaberite posljednju (Value), au naprednim opcijama – operaciju Ne spajati (Ne zbrajati):
  4. Preostaje naredbom uploadati rezultat natrag u Excel Početna — Zatvori i učitaj — Zatvori i učitaj u… (Početna — Zatvori&Učitaj — Zatvori&Učitaj u…)

  • Što je makronaredba, kako radi, gdje kopirati tekst makronaredbe, kako pokrenuti makro?
  • Popunjavanje svih praznih ćelija na popisu vrijednostima roditeljskih ćelija
  • Uklanjanje svih praznih ćelija iz zadanog raspona
  • Uklanjanje svih praznih redaka u radnom listu s PLEX dodatkom

Ostavi odgovor