Verwyder leë rye en kolomme in data

Leë rye en kolomme kan in baie gevalle 'n pyn in tabelle wees. Standaardfunksies vir sortering, filtering, opsomming, skep van spilpunttabelle, ens. sien leë rye en kolomme as 'n tabelbreuk, sonder om die data wat verder agter hulle geleë is, op te tel. As daar baie sulke gapings is, kan dit baie duur wees om dit met die hand te verwyder, en dit sal nie werk om alles gelyktydig "in grootmaat" met behulp van filter te verwyder nie, want die filter sal ook tydens pouses "strompel".

Kom ons kyk na verskeie maniere om hierdie probleem op te los.

Metode 1. Soek vir leë selle

Dit is dalk nie die gerieflikste nie, maar beslis die maklikste manier is noemenswaardig.

Gestel ons het te doen met so 'n tabel wat baie leë rye en kolomme binne bevat (uitgelig vir duidelikheid):

Gestel ons is seker dat die eerste kolom van ons tabel (kolom B) altyd die naam van 'n stad bevat. Dan sal leë selle in hierdie kolom 'n teken wees van onnodige leë rye. Om hulle almal vinnig te verwyder, doen die volgende:

  1. Kies reeks met stede (B2:B26)
  2. Druk die sleutel F5 en druk dan Kies 'n voorbeeldfoto (Gaan na Spesiale) of kies op die blad Tuis — Soek en kies — Kies 'n groep selle (Tuis — Soek & Kies — Gaan na spesiale).
  3. Kies die opsie in die venster wat oopmaak Leë selle (Spasies) en druk OK – alle leë selle in die eerste kolom van ons tabel moet gekies word.
  4. Kies nou op die blad What is This Command Verwyder – Vee rye uit blad uit (Vee uit — Vee rye uit) of druk die sleutelbordkortpad Ctrl+minus – en ons taak is opgelos.

Natuurlik kan jy op presies dieselfde manier ontslae raak van leë kolomme deur die tabelopskrif as basis te gebruik.

Metode 2: Soek vir leë rye

Soos jy dalk reeds uitgepluis het, sal die vorige metode slegs werk as ons data noodwendig volledig gevulde rye en kolomme bevat, wat vasgehaak kan word wanneer jy na leë selle soek. Maar wat as daar nie sulke vertroue is nie, en die data kan ook leë selle bevat?

Kyk byvoorbeeld na die volgende tabel vir net so 'n geval:

Hier sal die benadering 'n bietjie moeiliker wees:

  1. Voer die funksie in sel A2 in COUNT (COUNTA), wat die aantal gevulde selle in die ry aan die regterkant sal bereken en hierdie formule na die hele tabel sal kopieer:
  2. Kies sel A2 en skakel die filter aan met die opdrag Data – Filter (Data – Filter) of sleutelbordkortpad Ctrl+Skuif+L.
  3. Kom ons filter nulle uit volgens die berekende kolom, dws alle rye waar daar geen data is nie.
  4. Dit bly om die gefiltreerde lyne te kies en dit met die opdrag uit te vee Tuis — Skrap -' Vee rye van blad uit (Tuis — Skrap — Vee rye uit) of sleutelbordkortpad Ctrl+minus.
  5. Ons skakel die filter af en kry ons data sonder leë lyne.

Ongelukkig kan hierdie truuk nie meer met kolomme gedoen word nie – Excel het nog nie geleer hoe om volgens kolomme te filter nie.

Metode 3. Makro om alle leë rye en kolomme op 'n vel te verwyder

Jy kan ook 'n eenvoudige makro gebruik om hierdie taak te outomatiseer. Druk kortpadsleutel alt+F11 of kies op die oortjie ontwikkelaar — Visual Basic (Ontwikkelaar – Visual Basic Editor). As oortjies ontwikkelaar nie sigbaar is nie, kan jy dit deur aktiveer Lêer – Opsies – Lintopstelling (Lêer - Opsies - Pas lint aan).

In die Visual Basic-redigeerdervenster wat oopmaak, kies die kieslysopdrag Voeg in – Module en in die leë module wat verskyn, kopieer en plak die volgende reëls:

   Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки Vir r = 1 na ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 rng Is Niks Dan Stel rng = Rye(r) Else Stel rng = Unie(rng, Rye(r)) Einde As Volgende r Indien Nie rng Is Niks Dan rng.Delete 'удаляем пустые столбцы Stel rng = Niks Vir 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 Stel rng = Union(rng, Columns( r)) End If Next r Indien Nie rng Is Niks Dan rng.Delete End Sub

Maak die redigeerder toe en keer terug na Excel. 

Tik nou kombinasie alt+F8 of knoppie Macros tab ontwikkelaar. Die venster wat oopmaak sal 'n lys van alle makro's wat tans beskikbaar is vir jou om te hardloop, insluitend die makro wat jy sopas geskep het. Vee Leeg uit. Kies dit en klik op die knoppie Run (hardloop) – alle leë rye en kolomme op die blad sal onmiddellik uitgevee word.

Metode 4: Kragnavraag

Nog 'n manier om ons probleem op te los en 'n baie algemene scenario is om leë rye en kolomme in Power Query te verwyder.

Kom ons laai eers ons tabel in die Power Query Query Editor. Jy kan dit omskakel na 'n dinamiese "slim" een met die sleutelbordkortpad Ctrl+T of kies net ons datareeks en gee dit 'n naam (byvoorbeeld data) in die formulebalk, omskakeling na benoemde:

Nou gebruik ons ​​die opdrag Data - Kry data - Van tabel / reeks (Data - Kry Data - Van tabel / reeks) en laai alles in Power Query:

Dan is alles eenvoudig:

  1. Ons vee leë reëls uit met die opdrag Tuis – Verminder reëls – Verwyder reëls – Vee leë reëls uit (Tuis – Verwyder rye – Verwyder leë rye).
  2. Regskliek op die opskrif van die eerste Stad-kolom en kies die Opdrag Ontpivot Other Columns in die kontekskieslys. Ons tabel sal wees, soos dit tegnies korrek genoem word, genormaliseer – omgeskakel in drie kolomme: stad, maand en waarde vanaf die kruising van die stad en maand vanaf die oorspronklike tabel. Die eienaardigheid van hierdie bewerking in Power Query is dat dit leë selle in die brondata oorslaan, wat is wat ons nodig het:
  3. Nou voer ons die omgekeerde bewerking uit - ons verander die resulterende tabel terug in 'n tweedimensionele een om dit terug te keer na sy oorspronklike vorm. Kies die kolom met maande en op die oortjie Transformasie kies 'n span Draai kolom (Transformeer – Spilkolom). In die venster wat oopmaak, as 'n kolom van waardes, kies die laaste (Waarde), en in die gevorderde opsies - die bewerking Moenie saamvoeg nie (Moenie saamvoeg nie):
  4. Dit bly om die resultaat terug te laai na Excel met die opdrag Tuis — Maak toe en laai — Maak toe en laai in... (Tuis — Maak toe en laai — Maak toe en laai na...)

  • Wat is 'n makro, hoe werk dit, waar om die teks van 'n makro te kopieer, hoe om 'n makro te laat loop?
  • Vul alle leë selle in die lys met die waardes van die ouerselle
  • Verwyder alle leë selle uit 'n gegewe reeks
  • Verwyder alle leë rye in 'n werkblad met die PLEX-byvoeging

Lewer Kommentaar