Inhalte
Leere Zeilen und Spalten können in Tabellen in vielen Fällen lästig sein. Standardfunktionen zum Sortieren, Filtern, Zusammenfassen, Erstellen von Pivot-Tabellen etc. nehmen leere Zeilen und Spalten als Tabellenumbruch wahr, ohne die dahinter liegenden Daten aufzunehmen. Wenn es viele solcher Lücken gibt, kann das manuelle Entfernen sehr kostspielig sein, und es wird nicht funktionieren, alle auf einmal „in großen Mengen“ durch Filtern zu entfernen, da der Filter auch bei Unterbrechungen „stolpert“.
Sehen wir uns verschiedene Möglichkeiten an, um dieses Problem zu lösen.
Methode 1. Suchen Sie nach leeren Zellen
Dies ist vielleicht nicht der bequemste, aber definitiv der einfachste Weg, der erwähnenswert ist.
Angenommen, wir haben es mit einer solchen Tabelle zu tun, die viele leere Zeilen und Spalten enthält (zur Verdeutlichung hervorgehoben):
Angenommen, wir sind sicher, dass die erste Spalte unserer Tabelle (Spalte B) immer den Namen einer Stadt enthält. Dann sind leere Zellen in dieser Spalte ein Zeichen für unnötige leere Zeilen. Um sie schnell alle zu entfernen, gehen Sie wie folgt vor:
- Auswahlbereich mit Städten (B2:B26)
- Drücken Sie die Taste F5 und dann drücken Hervorheben (Gehe zu Spezial) oder auf der Registerkarte auswählen Start – Suchen und Auswählen – Wählen Sie eine Gruppe von Zellen aus (Startseite — Suchen&Auswählen — Gehe zu Spezial).
- Wählen Sie im sich öffnenden Fenster die Option aus Leere Zellen (Leerzeichen) und drücken Sie OK – Alle leeren Zellen in der ersten Spalte unserer Tabelle sollten ausgewählt werden.
- Wählen Sie nun auf der Registerkarte aus Startseite Befehl Löschen – Zeilen aus dem Blatt löschen (Löschen — Zeilen löschen) oder drücken Sie die Tastenkombination Ctrl+minus – und unsere Aufgabe ist gelöst.
Leere Spalten können Sie natürlich genauso loswerden, indem Sie den Tabellenkopf als Grundlage verwenden.
Methode 2: Suche nach leeren Zeilen
Wie Sie vielleicht schon herausgefunden haben, funktioniert die vorherige Methode nur, wenn unsere Daten unbedingt vollständig gefüllte Zeilen und Spalten enthalten, die bei der Suche nach leeren Zellen angehängt werden können. Aber was ist, wenn es kein solches Vertrauen gibt und die Daten möglicherweise auch leere Zellen enthalten?
Sehen Sie sich für einen solchen Fall beispielsweise die folgende Tabelle an:
Hier wird der Ansatz etwas kniffliger:
- Geben Sie in Zelle A2 die Funktion ein ANZAHL (COUNTA), wodurch die Anzahl der gefüllten Zellen in der Zeile rechts berechnet und diese Formel in die gesamte Tabelle kopiert wird:
- Wählen Sie die Zelle A2 aus und schalten Sie den Filter mit dem Befehl ein Daten – Filtern (Daten — Filter) oder Tastenkürzel Ctrl+Shift+L.
- Lassen Sie uns Nullen durch die berechnete Spalte herausfiltern, dh alle Zeilen, in denen es keine Daten gibt.
- Es bleibt, die gefilterten Zeilen auszuwählen und mit dem Befehl zu löschen Home — Löschen -' Zeilen aus dem Blatt löschen (Start — Löschen — Zeilen löschen) oder Tastenkürzel Ctrl+minus.
- Wir schalten den Filter aus und erhalten unsere Daten ohne Leerzeilen.
Mit Spalten geht dieser Trick leider nicht mehr – Excel hat noch nicht gelernt, nach Spalten zu filtern.
Methode 3. Makro zum Entfernen aller leeren Zeilen und Spalten auf einem Blatt
Sie können diese Aufgabe auch mit einem einfachen Makro automatisieren. Tastenkürzel drücken Andere+F11 oder wählen Sie auf der Registerkarte aus Entwickler – Visual Basic (Entwickler – Visual Basic-Editor). Wenn Registerkarten Entwickler nicht sichtbar ist, können Sie es durch aktivieren Datei – Optionen – Menüband einrichten (Datei – Optionen – Menüband anpassen).
Wählen Sie im sich öffnenden Fenster des Visual Basic-Editors den Menübefehl Einfügen – Modul Kopieren Sie im angezeigten leeren Modul die folgenden Zeilen und fügen Sie sie ein:
Sub DeleteEmpty() Dim r As Long, rng As Range Für r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 Then If 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 'удаляем пустые столбцы 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)) End If Next r If Not rng Is Nothing Then rng.Delete End Sub
Schließen Sie den Editor und kehren Sie zu Excel zurück.
Jetzt Kombination drücken Andere+F8 oder Taste Makros Tab Entwickler. Das sich öffnende Fenster listet alle Makros auf, die Ihnen derzeit zur Ausführung zur Verfügung stehen, einschließlich des Makros, das Sie gerade erstellt haben. LöschenLeer. Wählen Sie es aus und klicken Sie auf die Schaltfläche Führen Sie (Lauf) – Alle leeren Zeilen und Spalten auf dem Blatt werden sofort gelöscht.
Methode 4: Power-Abfrage
Eine andere Möglichkeit zur Lösung unseres Problems und ein sehr häufiges Szenario besteht darin, leere Zeilen und Spalten in Power Query zu entfernen.
Lassen Sie uns zunächst unsere Tabelle in den Power Query-Abfrage-Editor laden. Sie können ihn mit der Tastenkombination Strg+T in einen dynamischen „intelligenten“ umwandeln oder einfach unseren Datenbereich auswählen und ihm einen Namen geben (z Datum) in der Bearbeitungsleiste, Umwandlung in benannt:
Nun verwenden wir den Befehl Data – Get data – From table / range (Data – Get Data – From table / range) und laden alles in Power Query:
Dann ist alles einfach:
- Leere Zeilen löschen wir mit dem Befehl PosXNUMX – Zeilen reduzieren – Zeilen löschen – Leerzeilen löschen (PosXNUMX – Zeilen entfernen – Leerzeilen entfernen).
- Klicken Sie mit der rechten Maustaste auf die Überschrift der ersten City-Spalte und wählen Sie im Kontextmenü den Befehl Unpivot Other Columns. Unser Tisch wird, wie es technisch richtig heißt, normalisierte – umgewandelt in drei Spalten: Ort, Monat und Wert aus dem Schnittpunkt von Ort und Monat aus der Originaltabelle. Die Besonderheit dieser Operation in Power Query besteht darin, dass leere Zellen in den Quelldaten übersprungen werden, was wir brauchen:
- Jetzt führen wir die umgekehrte Operation durch – wir verwandeln die resultierende Tabelle wieder in eine zweidimensionale, um sie in ihre ursprüngliche Form zurückzubringen. Wählen Sie die Spalte mit den Monaten und auf der Registerkarte Transformation wähle ein Team Pivot-Spalte (Transformieren – Pivot-Spalte). Wählen Sie im sich öffnenden Fenster als Spalte mit Werten den letzten (Wert) und in den erweiterten Optionen – die Operation Nicht aggregieren (nicht aggregieren):
- Es bleibt, das Ergebnis mit dem Befehl wieder in Excel hochzuladen Home — Schließen und laden — Schließen und laden in… (Home — Schließen&Laden — Schließen&Laden nach…)
- Was ist ein Makro, wie funktioniert es, wohin kopiert man den Text eines Makros, wie führt man ein Makro aus?
- Füllen Sie alle leeren Zellen in der Liste mit den Werten der übergeordneten Zellen
- Entfernen aller leeren Zellen aus einem bestimmten Bereich
- Entfernen aller leeren Zeilen in einem Arbeitsblatt mit dem PLEX-Add-on