Fjerning av tomme rader og kolonner i data

Tomme rader og kolonner kan i mange tilfeller være en smerte i tabeller. Standardfunksjoner for sortering, filtrering, oppsummering, opprettelse av pivottabeller osv. oppfatter tomme rader og kolonner som et tabellskifte, uten å plukke opp dataene som ligger lenger bak dem. Hvis det er mange slike hull, kan det være svært kostbart å fjerne dem manuelt, og det vil ikke fungere å fjerne alt på en gang "i bulk" ved hjelp av filtrering, fordi filteret også vil "snuble" ved pauser.

La oss se på flere måter å løse dette problemet på.

Metode 1. Søk etter tomme celler

Dette er kanskje ikke den mest praktiske, men definitivt den enkleste måten er verdt å nevne.

Anta at vi har å gjøre med en slik tabell som inneholder mange tomme rader og kolonner inni (uthevet for klarhetens skyld):

Anta at vi er sikre på at den første kolonnen i tabellen vår (kolonne B) alltid inneholder navnet på en by. Da vil tomme celler i denne kolonnen være et tegn på unødvendige tomme rader. Gjør følgende for å fjerne dem alle raskt:

  1. Velg område med byer (B2:B26)
  2. Trykk på tasten F5 og trykk deretter på Uthev (Gå til Spesial) eller velg på fanen Hjem — Finn og velg — Velg en gruppe celler (Hjem — Finn og velg — Gå til spesial).
  3. Velg alternativet i vinduet som åpnes Tomme celler (Blanker) og trykk OK – alle tomme celler i den første kolonnen i tabellen bør velges.
  4. Velg nå på fanen Hjemprodukt Kommando Slett – Slett rader fra ark (Slett – Slett rader) eller trykk på hurtigtasten Ctrl+minus – og vår oppgave er løst.

Selvfølgelig kan du bli kvitt tomme kolonner på nøyaktig samme måte, ved å bruke tabelloverskriften som grunnlag.

Metode 2: Søk etter tomme rader

Som du kanskje allerede har skjønt, vil den forrige metoden bare fungere hvis dataene våre nødvendigvis inneholder fullt utfylte rader og kolonner, som kan kobles til når du søker etter tomme celler. Men hva hvis det ikke er slik tillit, og dataene kan inneholde tomme celler også?

Ta en titt på følgende tabell, for eksempel for et slikt tilfelle:

Her vil tilnærmingen være litt vanskeligere:

  1. Skriv inn funksjonen i celle A2 COUNT (COUNTA), som vil beregne antall fylte celler i raden til høyre og kopiere denne formelen ned til hele tabellen:
  2. Velg celle A2 og slå på filteret med kommandoen Data – Filter (Data – Filter) eller hurtigtast Ctrl+Skift+L.
  3. La oss filtrere ut nuller etter den beregnede kolonnen, altså alle rader der det ikke er data.
  4. Det gjenstår å velge de filtrerte linjene og slette dem med kommandoen Hjem — Slett -' Slett rader fra ark (Hjem — Slett — Slett rader) eller hurtigtast Ctrl+minus.
  5. Vi slår av filteret og får dataene våre uten tomme linjer.

Dessverre kan dette trikset ikke lenger gjøres med kolonner - Excel har ennå ikke lært hvordan man filtrerer etter kolonner.

Metode 3. Makro for å fjerne alle tomme rader og kolonner på et ark

Du kan også bruke en enkel makro for å automatisere denne oppgaven. Trykk hurtigtast andre+F11 eller velg fra fanen utvikler — Visual Basic (Utvikler – Visual Basic Editor). Hvis faner utvikler ikke er synlig, kan du aktivere den gjennom Fil – Alternativer – Båndoppsett (Fil — Alternativer — Tilpass bånd).

Velg menykommandoen i Visual Basic-redigeringsvinduet som åpnes Sett inn – modul og i den tomme modulen som vises, kopier og lim inn 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 'удаляем пустые столбцы Sett rng = Nothing For r = 1 To 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

Lukk redigeringsprogrammet og gå tilbake til Excel. 

Nå treffer kombinasjon andre+F8 eller knappen Makroer tab utvikler. Vinduet som åpnes vil vise alle makroer som er tilgjengelige for deg for øyeblikket, inkludert makroen du nettopp opprettet. SlettTom. Velg den og klikk på knappen Kjør (løpe) – alle tomme rader og kolonner på arket slettes umiddelbart.

Metode 4: Power Query

En annen måte å løse problemet på og et veldig vanlig scenario er å fjerne tomme rader og kolonner i Power Query.

La oss først laste inn tabellen i Power Query Query Editor. Du kan konvertere den til en dynamisk "smart" med hurtigtasten Ctrl+T eller bare velge dataområdet vårt og gi det et navn (for eksempel Data) i formellinjen, konverterer til navngitt:

Nå bruker vi kommandoen Data – Hent data – Fra tabell / område (Data – Hent data – Fra tabell / område) og laster alt inn i Power Query:

Da er alt enkelt:

  1. Vi sletter tomme linjer med kommandoen Hjem – Reduser linjer – Slett linjer – Slett tomme linjer (Hjem – Fjern rader – Fjern tomme rader).
  2. Høyreklikk på overskriften til den første bykolonnen og velg kommandoen Unpivot Other Columns fra hurtigmenyen. Bordet vårt blir, som det teknisk korrekt heter, normalisert – konvertert til tre kolonner: by, måned og verdi fra skjæringspunktet mellom byen og måned fra den opprinnelige tabellen. Det særegne med denne operasjonen i Power Query er at den hopper over tomme celler i kildedataene, som er det vi trenger:
  3. Nå utfører vi den omvendte operasjonen - vi gjør den resulterende tabellen tilbake til en todimensjonal for å returnere den til sin opprinnelige form. Velg kolonnen med måneder og på fanen Transformation velge et lag Pivotkolonne (Transform – Pivot Column). I vinduet som åpnes, som en kolonne med verdier, velg den siste (Verdi), og i de avanserte alternativene – operasjonen Ikke aggregere (Ikke samle):
  4. Det gjenstår å laste opp resultatet tilbake til Excel med kommandoen Hjem — Lukk og last — Lukk og last inn... (Hjem — Lukk&last — Lukk&last til...)

  • Hva er en makro, hvordan fungerer den, hvor kopierer man teksten til en makro, hvordan kjører man en makro?
  • Fyller alle tomme celler i listen med verdiene til de overordnede cellene
  • Fjerner alle tomme celler fra et gitt område
  • Fjerne alle tomme rader i et regneark med PLEX-tillegget

Legg igjen en kommentar