Fehlerhafte Dateien, das ist Alltag in der Excel- & Datenwelt. Dazu gehören auch fehlende Werte.
Entweder man möchte diese Einträge bereinigen, ergänzen oder einfach nur wissen, welche Zeilen leere Zellen aufweisen. Welche manuellen und automatisierten Wege es hierfür gibt, zeige ich dir in diesem Beitrag!
Leere Zellen manuell entfernen 👷🏻♂️
Bedingte Formatierung
Markiere den Bereich, auf welchen die Formatierung angewendet werden soll.
Zellen der Tabelle markiert
Gehe in der Registerkarte Start im Bereich Formatvorlagen auf Bedingte Formatierung -> Neue Regel….
Regel für bedingte Formatierung erstellen
Menü zur Erstellung einer neuen Formatierungsregel
CHECK
Tausche A2 gegen die erste Zelle aus, welche formatiert werden soll. Die bedingte Formatierung wendet diese Regel dann auf alle anderen Zellen ebenso an.
Vorschau & Button für Formatierung
Fertige Formatierungsregel mit Formel & Formatierung
Leere Zellen mit angewandter Formatierungsregel in rot
Inhalte auswählen
Markiere (wie bei Methode 1) den gewünschten Bereich, aus dem du leere Zellen entfernen möchtest. Drücke anschließend STRG + G oder gehe in der Registerkarte Start im Bereich Bearbeiten auf Suchen und Auswählen -> Gehe zu.
Option Gehe zu…
Menü der Option Gehe zu…
Im nächsten Menü Inhalte auswählen wählst du die Option Leerzellen aus. Bestätige mit OK.
Menü zur Auswahl aller Leerzellen
ACHTUNG
Beim Löschen der Zellen können Daten verloren gehen, da oftmals die ganze Zeile der Leerzelle gelöscht wird!
Daten filtern
Aktiviere für deine Daten die Filter-Funktion. Hierfür gehst du in der Registerkarte Start unter Bearbeiten in das Menü Suchen und Auswählen, hier klickst du auf Filtern.
Einfügen eines Filters für deine Daten
Filtermenü mit aktiver Auswahl von leeren Einträgen
ACHTUNG
Beim Löschen der Zellen können Daten verloren gehen, da oftmals die ganze Zeile der Leerzelle gelöscht wird!
Auf leere Zellen gefilterte Tabelle
Das Entfernen von Duplikaten automatisieren ♻
FILTER-Funktion
Die FILTER-Funktion gibt dir einen vorgegebenen Datenbereich (Matrix) mit festgelegten Kriterien gefiltert zurück.
INFO
Diese Methode funktioniert erst mit Excel 2021 / 365 oder früher!
Argumente der FILTER-Funktion
(optionale Argumente weggelassen)
=FILTER(
verkäufe_filter_formel[#Alle];
verkäufe_filter_formel[[#Alle];[KundenID]] <> “”)
INFO
[#Alle] wird durch die intelligente Tabelle automatisch generiert, wenn man die Kopfzeile mit einbezieht.
Die Funktion besteht aus drei Teilen:
1. Matrix
Die Matrix ist der Bereich mit Daten, welchen du abbilden & filtern möchtest.
2. einschließen
Hier kommen die Filterkriterien rein. Das sieht in der Grundform so aus: [Bereich] = [Kriterium]
Bei mehreren Kriterien unterscheiden wir zwischen UND & ODER. UND wird mit einem Stern (*), ODER mit einem Plus (+) ausgewertet:
([Bereich_1] = [Kriterium_1]) * ([Bereich_2] = [Kriterium_2])
Für unseren Fall der leeren Zellen:
verkäufe_filter_formel[KundenID] <> “”
[KundenID] ist die Spalte, welche gefiltert wird, die doppelten Anführungszeichen stellen Leer dar.
3. wenn_leer
Wenn die Formel keine Zeile ausgeben kann (weil kein Kriterium zutrifft), dann kann hier der Output definiert werden.
Beispiel: “KEINE TREFFER”
Als Endergebnis erhältst du die Tabelle als dynamisches Array mit allen Zeilen, welche einen Inhalt in der Spalte KundenID haben, also keine leere Zelle sind.
Ausgegebene Liste (Array) durch FILTER-Formel
PivotTable
Suche in der Registerkarte Einfügen unter Tabellen den PivotTable Button und klicke auf das Icon. Wähle nun den Datenbereich aus.
PivotTable einfügen durch Benutzeroberfläche
Daten der PivotTable ohne Bezug auf intelligente Tabelle
Alternativ kannst du in eine intelligente Tabelle klicken und in der Registerkarte Tabellenentwurf unter Tools die Funktion Mit PivotTable zusammenfassen nutzen. Diese wählt automatisch die intelligente Tabelle als Bereich aus.
PivotTable einfügen mit Bezug auf intelligente Tabelle
Daten der PivotTable mit Bezug auf intelligente Tabelle
Ziehe die Spalten, aus denen die leeren Einträge entfernt werden sollen, in die Zeilen des PivotTable.
Spalte für Liste ohne Leerzellen als Zeilen des PivotTable
Filtermenü zum Entfernen von Leerzellen
Damit hast du leere Zellen erfolgreich entfernt! 🙂
Power Query
Mit Power Query kannst du dir ebenso eine Liste ohne Zeilen mit leeren Einträgen ausgeben lassen, auch im Tabellenformat. Lade hierfür die Daten in Power Query.
Geladene Tabelle in Power Query
Anschließend wählst du die gewünschte Spalte aus, mit Rechtsklick auf andere Spalten entfernen bleibt die gewünschte Spalte übrig. Du kannst natürlich auch alle Spalten drin lassen und nur die gewünschte Spalte filtern, sofern du alle Daten behalten möchtest.
Relevante Spalte durch Entfernen aller anderen Spalten auswählen
Spalte mit null-Wert (leer)
Menüpunkt zum Entfernen leerer Zellen
Diese Option erzeugt automatisch den erforderlichen Code, um sowohl null (Zelle komplett leer) als auch “” (Leerzeichen) zu entfernen.
Durch Leere entfernen erzeugter Power Query Code
Abschließend kannst du die Werte zur besseren Übersichtlichkeit sortieren.
Eindeutige Liste auf- oder absteigend sortieren
Nun kannst du die Abfrage schließen & in ein Blatt laden.
Laden der Abfrage in Arbeitsmappe
Wähle aus, in welcher Form du die Liste haben möchtest. Entweder als intelligente Tabelle oder als PivotTable. Meine Empfehlung wäre eine Tabelle, für ein PivotTable gibt es eine einfachere Lösung.
Optionen zum Importieren der Daten in die Arbeitsmappe
Query Abfrage als intelligente Tabelle
Fazit 💡
In diesem Beitrag hast du sechs Methoden gelernt, um leere Zellen / Zeilen aus deinen Daten zu entfernen.
Es gibt keine bessere oder schlechtere Möglichkeit, jede dieser Methoden hat ihre eigenen Anwendungsfälle. Daher lohnt es sich, jede Vorgehensweise zu üben und sich einzuprägen.
Viel Spaß beim Ausprobieren! 🙂
0 Kommentare