Inhoud
Lege rijen en kolommen kunnen in veel gevallen lastig zijn in tabellen. Standaardfuncties voor sorteren, filteren, samenvatten, het maken van draaitabellen, etc. zien lege rijen en kolommen als een tabelonderbreking, zonder de gegevens die er verder achter liggen op te pikken. Als er veel van dergelijke gaten zijn, kan het handmatig verwijderen ervan erg duur zijn, en het zal niet werken om alles in één keer "in bulk" te verwijderen met behulp van filtering, omdat het filter ook "struikelt" bij pauzes.
Laten we eens kijken naar verschillende manieren om dit probleem op te lossen.
Methode 1. Zoeken naar lege cellen
Dit is misschien niet de handigste, maar zeker de gemakkelijkste manier is het vermelden waard.
Stel dat we te maken hebben met zo'n tabel met daarin veel lege rijen en kolommen (gemarkeerd voor de duidelijkheid):
Stel dat we zeker weten dat de eerste kolom van onze tabel (kolom B) altijd de naam van een stad bevat. Lege cellen in deze kolom zijn dan een teken van onnodige lege rijen. Ga als volgt te werk om ze allemaal snel te verwijderen:
- Selecteer bereik met steden (B2:B26)
- Druk op de toets F5 en druk vervolgens op Markeer (Ga naar Speciaal) of selecteer op het tabblad Home — Zoek en selecteer — Selecteer een groep cellen (Home — Zoek&Selecteer — Ga naar speciaal).
- Selecteer in het geopende venster de optie lege cellen (Spaties) en druk op OK - alle lege cellen in de eerste kolom van onze tabel moeten worden geselecteerd.
- Selecteer nu op het tabblad Home commando Verwijderen - Verwijder rijen van blad (Verwijderen — Verwijder rijen) of druk op de sneltoets Ctrl+minus – en onze taak is opgelost.
Natuurlijk kunt u op precies dezelfde manier lege kolommen verwijderen, met de tabelkop als basis.
Methode 2: Zoeken naar lege rijen
Zoals je misschien al hebt ontdekt, werkt de vorige methode alleen als onze gegevens noodzakelijkerwijs volledig gevulde rijen en kolommen bevatten, die kunnen worden aangehaakt bij het zoeken naar lege cellen. Maar wat als er geen vertrouwen is en de gegevens ook lege cellen kunnen bevatten?
Kijk bijvoorbeeld eens naar de volgende tabel voor zo'n geval:
Hier zal de aanpak een beetje lastiger zijn:
- Voer in cel A2 de functie in COUNT (AANTALA), die het aantal gevulde cellen in de rij aan de rechterkant berekent en deze formule naar de hele tabel kopieert:
- Selecteer cel A2 en zet het filter aan met het commando Gegevens – Filter (Gegevens — Filter) of sneltoets Ctrl+Shift+L.
- Laten we nullen filteren op de berekende kolom, dwz alle rijen waar geen gegevens zijn.
- Het blijft om de gefilterde regels te selecteren en ze te verwijderen met de opdracht Home — Verwijderen -' Rijen van blad verwijderen (Home — Verwijder — Verwijder rijen) of sneltoets Ctrl+minus.
- We zetten het filter uit en krijgen onze gegevens zonder lege regels.
Helaas kan deze truc niet meer worden gedaan met kolommen - Excel heeft nog niet geleerd hoe te filteren op kolommen.
Methode 3. Macro om alle lege rijen en kolommen op een blad te verwijderen
U kunt ook een eenvoudige macro gebruiken om deze taak te automatiseren. Druk op sneltoets anders+F11 of kies uit het tabblad ontwikkelaar — Visueel Basis (Ontwikkelaar — Visual Basic Editor). Als tabbladen ontwikkelaar niet zichtbaar is, kunt u deze inschakelen via Bestand – Opties – Lint instellen (Bestand — Opties — Lint aanpassen).
Selecteer in het venster van de Visual Basic-editor dat wordt geopend de menuopdracht Invoegen – Module en in de lege module die verschijnt, kopieer en plak je de volgende regels:
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 Is Nothing Dan Set rng = Rows(r) Anders Stel rng = Union(rng, Rows(r)) End If Next r If Not rng Is Nothing Dan rng.Delete 'удаляем пустые столбцы Set 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)) Einde als volgende r Als niet rng is niets Dan rng.Verwijder End Sub
Sluit de editor en keer terug naar Excel.
Nu hit combinatie anders+F8 of knop Macro's tab ontwikkelaar. Het venster dat wordt geopend, toont alle macro's die momenteel voor u beschikbaar zijn om uit te voeren, inclusief de macro die u zojuist hebt gemaakt. VerwijderenLeeg. Selecteer het en klik op de knop lopen (rennen) - alle lege rijen en kolommen op het blad worden onmiddellijk verwijderd.
Methode 4: Power-query
Een andere manier om ons probleem op te lossen en een veel voorkomend scenario is om lege rijen en kolommen in Power Query te verwijderen.
Laten we eerst onze tabel in de Power Query Query Editor laden. Je kunt het converteren naar een dynamische "slimme" met de sneltoets Ctrl + T of gewoon ons gegevensbereik selecteren en het een naam geven (bijvoorbeeld Data) in de formulebalk, converteren naar genoemd:
Nu gebruiken we het commando Data – Get data – From table / range (Data – Get Data – From table / range) en laden alles in Power Query:
Dan is alles eenvoudig:
- Lege regels verwijderen we met het commando Home – Regels verkleinen – Regels verwijderen – Lege regels verwijderen (Home – Rijen verwijderen – Lege rijen verwijderen).
- Klik met de rechtermuisknop op de kop van de eerste kolom Stad en selecteer de opdracht Andere kolommen draaien in het contextmenu. Onze tafel wordt, zoals het technisch correct wordt genoemd, genormaliseerde – omgezet in drie kolommen: stad, maand en waarde van het snijpunt van de stad en maand van de originele tabel. Het bijzondere van deze bewerking in Power Query is dat het lege cellen in de brongegevens overslaat, wat we nodig hebben:
- Nu voeren we de omgekeerde bewerking uit - we veranderen de resulterende tabel terug in een tweedimensionale om deze terug te brengen naar zijn oorspronkelijke vorm. Selecteer de kolom met maanden en op het tabblad Transformatie kies een team Draaikolom (Transformeren — Draaikolom). Selecteer in het geopende venster als een kolom met waarden de laatste (Waarde) en in de geavanceerde opties - de bewerking Niet aggregeren (Niet aggregeren):
- Het blijft om het resultaat terug naar Excel te uploaden met de opdracht Home — Sluiten en laden — Sluiten en laden in… (Home — Sluiten&Laden — Sluiten&Laden naar…)
- Wat is een macro, hoe werkt het, waar kopieer je de tekst van een macro, hoe voer je een macro uit?
- Alle lege cellen in de lijst vullen met de waarden van de bovenliggende cellen
- Alle lege cellen uit een bepaald bereik verwijderen
- Alle lege rijen in een werkblad verwijderen met de PLEX-add-on