Fjernelse af tomme rækker og kolonner i data

Tomme rækker og kolonner kan være en smerte i tabeller i mange tilfælde. Standardfunktioner til sortering, filtrering, opsummering, oprettelse af pivottabeller osv. opfatter tomme rækker og kolonner som et tabelbrud, uden at samle de data, der er placeret længere bag dem. Hvis der er mange sådanne huller, kan det være meget dyrt at fjerne dem manuelt, og det vil ikke fungere at fjerne det hele på én gang "i bulk" ved hjælp af filtrering, fordi filteret også "snubler" ved pauser.

Lad os se på flere måder at løse dette problem på.

Metode 1. Søg efter tomme celler

Dette er måske ikke den mest bekvemme, men absolut den nemmeste måde er værd at nævne.

Antag, at vi har at gøre med en sådan tabel, der indeholder mange tomme rækker og kolonner indeni (fremhævet for klarhedens skyld):

Antag, at vi er sikre på, at den første kolonne i vores tabel (kolonne B) altid indeholder navnet på en by. Så vil tomme celler i denne kolonne være et tegn på unødvendige tomme rækker. Gør følgende for hurtigt at fjerne dem alle:

  1. Vælg rækkevidde med byer (B2:B26)
  2. Tryk på tasten F5 og tryk derefter på Fremhæv (Gå til Special) eller vælg på fanen Hjem — Find og vælg — Vælg en gruppe celler (Hjem — Find&Vælg — Gå til special).
  3. Vælg indstillingen i det vindue, der åbnes Tomme celler (Blank) og tryk OK – alle tomme celler i den første kolonne i vores tabel skal vælges.
  4. Vælg nu på fanen Home Kommando Slet – Slet rækker fra ark (Slet - Slet rækker) eller tryk på tastaturgenvejen Ctrl+minus – og vores opgave er løst.

Du kan selvfølgelig slippe af med tomme kolonner på nøjagtig samme måde ved at bruge tabeloverskriften som grundlag.

Metode 2: Søg efter tomme rækker

Som du måske allerede har regnet ud, vil den tidligere metode kun fungere, hvis vores data nødvendigvis indeholder fuldt udfyldte rækker og kolonner, som kan kobles på, når du søger efter tomme celler. Men hvad hvis der ikke er en sådan tillid, og dataene kan også indeholde tomme celler?

Tag et kig på følgende tabel, for eksempel for netop et sådant tilfælde:

Her vil fremgangsmåden være lidt vanskeligere:

  1. Indtast funktionen i celle A2 COUNT (COUNTA), som vil beregne antallet af udfyldte celler i rækken til højre og kopiere denne formel ned til hele tabellen:
  2. Vælg celle A2 og tænd for filteret med kommandoen Data – Filter (Data — Filter) eller tastaturgenvej Ctrl+Flytte+L.
  3. Lad os filtrere nuller fra den beregnede kolonne, altså alle rækker, hvor der ikke er data.
  4. Det er tilbage at vælge de filtrerede linjer og slette dem med kommandoen Hjem — Slet -' Slet rækker fra ark (Hjem — Slet — Slet rækker) eller tastaturgenvej Ctrl+minus.
  5. Vi slukker filteret og får vores data uden tomme linjer.

Desværre kan dette trick ikke længere gøres med kolonner - Excel har endnu ikke lært at filtrere efter kolonner.

Metode 3. Makro for at fjerne alle tomme rækker og kolonner på et ark

Du kan også bruge en simpel makro til at automatisere denne opgave. Tryk på tastaturgenvej andre+F11 eller vælg fra fanen developer — Visual Basic (Udvikler – Visual Basic Editor). Hvis faner developer ikke er synlig, kan du aktivere den igennem Fil – Indstillinger – Opsætning af bånd (Fil — Indstillinger — Tilpas bånd).

I Visual Basic-editorvinduet, der åbnes, skal du vælge menukommandoen Indsæt – Modul og i det tomme modul, der vises, skal du kopiere og indsætte følgende linjer:

   Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки For r = 1 Til ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows Then If) = 0 rng Is Nothing Then Set rng = Rows(r) Else Set rng = Union(rng, Rows(r)) End If Next r If Not rng Is Nothing Then rng.Delete 'удаляем пустые столбцы Indstil rng = Intet For r = 1 Til 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

Luk editoren og vend tilbage til Excel. 

Hit kombination andre+F8 eller knap Makroer fanen developer. Vinduet, der åbnes, viser alle makroer, der i øjeblikket er tilgængelige for dig, inklusive den makro, du lige har oprettet. Slet Tom. Vælg det, og klik på knappen Kør (løb) – alle tomme rækker og kolonner på arket slettes øjeblikkeligt.

Metode 4: Power Query

En anden måde at løse vores problem på og et meget almindeligt scenarie er at fjerne tomme rækker og kolonner i Power Query.

Lad os først indlæse vores tabel i Power Query Query Editor. Du kan konvertere den til en dynamisk "smart" med tastaturgenvejen Ctrl+T eller blot vælge vores dataområde og give den et navn (f.eks. data) i formellinjen, der konverteres til navngivet:

Nu bruger vi kommandoen Data – Hent data – Fra tabel / område (Data – Hent Data – Fra tabel / område) og indlæser alt i Power Query:

Så er alt simpelt:

  1. Vi sletter tomme linjer med kommandoen Hjem – Reducer linjer – Slet linjer – Slet tomme linjer (Hjem – Fjern rækker – Fjern tomme rækker).
  2. Højreklik på overskriften til den første bykolonne, og vælg kommandoen Fjern pivot andre kolonner i kontekstmenuen. Vores bord bliver, som det teknisk korrekt hedder, normaliseret – konverteret til tre kolonner: by, måned og værdi fra skæringspunktet mellem byen og måned fra den oprindelige tabel. Det særlige ved denne operation i Power Query er, at den springer tomme celler over i kildedataene, hvilket er hvad vi har brug for:
  3. Nu udfører vi den omvendte operation - vi vender den resulterende tabel tilbage til en todimensionel for at returnere den til sin oprindelige form. Vælg kolonnen med måneder og på fanen Transformation vælge et hold Pivot kolonne (Transformer – pivotkolonne). I vinduet, der åbnes, skal du som en kolonne med værdier vælge den sidste (Værdi), og i de avancerede muligheder - handlingen Må ikke aggregeres (Sæt ikke sammen):
  4. Det er tilbage at uploade resultatet tilbage til Excel med kommandoen Hjem — Luk og indlæs — Luk og indlæs... (Hjem — Luk&indlæs — Luk&indlæs til...)

  • Hvad er en makro, hvordan fungerer den, hvor kopierer man teksten til en makro, hvordan kører man en makro?
  • Udfylder alle tomme celler i listen med værdierne for de overordnede celler
  • Fjerner alle tomme celler fra et givet område
  • Fjernelse af alle tomme rækker i et regneark med PLEX-tilføjelsen

Giv en kommentar