10 Tipps um große Datenmengen in Excel zu verwalten
Der Umgang mit Massendaten bereitet Anwendern von Tabellenkalkulationsprogrammen immer wieder Probleme. Oft ist die Lösung ganz einfach und nur wenige Mausklicks entfernt.
Im Sommer 2021 machte Excel Schlagzeilen: Mitarbeiter von Gesundheitsbehörden hatten mithilfe der Tabellenkalkulation die Kontaktnachverfolgung positiver Corona-Tests verwaltet. Es wurde nicht bedacht, dass die möglichen Einträge auf einem Excel-Sheet begrenzt sind. Dadurch kam es zu einem gravie...
Im Sommer 2021 machte Excel Schlagzeilen: Mitarbeiter von Gesundheitsbehörden hatten mithilfe der Tabellenkalkulation die Kontaktnachverfolgung positiver Corona-Tests verwaltet. Es wurde nicht bedacht, dass die möglichen Einträge auf einem Excel-Sheet begrenzt sind. Dadurch kam es zu einem gravierenden Fehler; Tausende Infizierte wurden zu spät über das Testergebnis informiert.
Mit dem nötigen Anwenderwissen wäre das Problem nicht aufgetreten, denn schließlich verfügen Excel-Arbeitsblätter seit der Version 2007 über 1.048.576 Zeilen und 16.384 Spalten! Zudem können Arbeitsmappen um weitere Blätter ergänzt werden. Mit den folgenden Tipps gelingt der Umgang mit umfangreichen Datenbeständen.
Tipp 1: Belegte Zellen zählen
Sie wollen wissen, wie viele Zeilen einer Excel-Spalte mit Inhalten gefüllt sind? Setzen Sie ANZAHL2 ein.
Beispiele: Für den Bereich der Zelle A3 bis an das Spaltenende erhalten Sie das Ergebnis mit dem Code =ANZAHL2(A3:A1048576), für die komplette Zeile 3 nehmen Sie =ANZAHL2(3:3).
Um zu ermitteln, wie viele Zeilen frei sind, erweitern Sie die Formeln, etwa so: =1048574-ANZAHL2(A3:A1048576). Oder Sie integrieren einen Warnhinweis, der erscheint, wenn zum Beispiel nur noch zehn Zeilen oder weniger frei sind: =WENN(1048 574ANZAHL2(A3:A1048576)<11;“Es sind nur noch “& 1048574-ANZAHL2(A3:A1048576)&“ Zeilen frei!“;1048574-ANZAHL2(A3:A1048576)).
Alternative: Leere Zeilen/Spalten kann man auch so zählen: =ANZAHLLEEREZELLEN (A3:A1048576) bzw. =ANZAHLLEEREZELLEN(3:3).
Tipp 2: Informationen verteilen
Der Platz eines Arbeitsblattes reicht nicht aus, und Sie können keine weiteren Datensätze erfassen? Dann ist das Auslagern von Daten auf andere Arbeitsblätter bzw. in andere Arbeitsmappen eine einfache Lösung.
Über [Umschalt]+[F11] legen Sie ein neues Blatt an. Da Sie ANZAHL2 (vgl. Tipp 1) arbeitsblatt- und arbeitsmappenübergreifend einsetzen können, behalten Sie dennoch den Überblick über belegte und leere Zellen. Die Funktion ist in der Lage, bis zu 255 Bereiche zu überprüfen. Mit =ANZAHL2 (A3:A1048576;Tabelle2!A3:A1048576) zählen Sie zusätzlich die Einträge der Spalte A aus Tabelle2 derselben Datei.
=ANZAHL2(A3:A 1048576;Tabelle2!A3:A1048576;[Test.xlsx] Tabelle1!$A$3:$A$1048576) holt zusätzlich Informationen aus einer Datei Test.xlsx. Wichtig ist, dass hinter dem Tabellennamen ein Ausrufezeichen steht und der Dateiname in eckige Klammern gesetzt wird.
Tipp 3: So erkennen und entfernen Sie Duplikate in Ihren Listen
Mehrfacherfassungen von Datensätzen in Listen sind eine häufige Fehlerquelle. Sie wollen diese aufspüren und löschen? Besonders einfach: In die gewünschte Spalte und dann auf Daten/Duplikate entfernen klicken.
Es kommt zu einer Nachfrage, wenn sich in Datenlisten weitere Einträge in der Zeile befinden. Dann werden auf Ihre Anweisung hin nur die Dubletten gelöscht, in denen sich in allen Spalten der Zeile identische Einträge befinden.
Achtung: Sie werden zwar informiert, wie viele Datensätze gelöscht wurden, aber nicht welche!
Ähnlich arbeitet der Spezialfilter: Cursor in die Datenliste setzen und Daten/Filter/ Erweitert wählen. Haken Sie das Kontrollkästchen Keine Duplikate ab, und bestätigen Sie über OK. Die überflüssigen Einträge verschwinden umgehend. Damit die Einträge nicht ohne Ihr Einverständnis gelöscht werden, können Sie unter Aktion die Option Liste an eine andere Stelle kopieren aktivieren.
Dann sind die gelöschten Datensätze zur Not noch in der Ursprungsliste vorhanden. Der Nachteil: Das Datenvolumen wird unnötig aufgebläht. Wenn man über Start/Bedingte Formatierung/Regeln zum Hervorheben von Zellen/Doppelte Werte Mehrfacherfassungen anzeigt, wird die Arbeitsmappe unter Umständen sehr langsam.
Je nach Datenumfang müssen Sie zudem möglicherweise weit nach unten scrollen, um Doppelnennungen aufzuspüren. Völlig anderer Ansatz: Nicht ganz unkompliziert, jedoch sehr effektiv suchen Sie Duplikate mithilfe von Formeln. Ganz wichtig ist es, die Liste zunächst nach allen Spalten zu sortieren (Tipp 5). Rechts neben der Liste verknüpfen Sie die Zellinhalte (etwa aus den Spalten D bis F) miteinander und vergleichen den Eintrag mit der Folgezeile, eingebettet in eine WENN-Funktion: =WENN( (VERKETTEN(A2;B2;C2))=(VERKETTEN(A3;B 3;C3));“Mehrfach“;““).
Kopieren Sie die Formel in die nachfolgenden Zeilen. Anschließend können Sie die „Mehrfach“-Einträge mit der Filterfunktion anzeigen lassen (Tipp 5). Aber auch diese Alternative geht zu Lasten der Geschwindigkeit.
Tipp 4: Erfassen über Datenmaske
Eingaben über Dialoge sind in der Regel bequemer durchzuführen und gleichzeitig weniger fehleranfällig als Tabelleneinträge. Möchten Sie die Vorteile einer Maske nutzen, um etwa bequem durch die Datensätze zu blättern ohne zu verrutschen oder davon zu profitieren, dass Formeln automatisch gesperrt sind, stellen Sie die Maske in den neueren Excel-Versionen zum Beispiel über Datei/Optionen/Symbolleiste für den Schnellzugriff zur Verfügung.
Unter Befehle auswählen aktivieren Sie Nicht im Menüband enthaltene Befehle, markieren in der zugehörigen Liste Maske, klicken auf Hinzufügen und bestätigen über OK. Alternativ können Sie das Symbol auch im Menüband (Datei/Optionen/Menüband anpassen) unterbringen. Noch einfacher: Setzen Sie den Cursor in die Datenliste und drücken zum Aufruf der Maske [ALT]+[N]+[M].
Tipp 5: Sortieren und Filtern
Sortieren und Filtern aus dem gleichnamigen Bereich im Menü Daten sorgen für mehr Überblick in umfangreichen Datenbeständen. Beide Funktionen lassen sich nahezu intuitiv bedienen. Werden die Filterkriterien jedoch komplexer, benötigen Sie Spezialfilter (Schaltfläche Erweitert), deren Einsatz an verschiedene Voraussetzungen geknüpft sind.
Neben der eigentlichen Datenliste (Quelle) wird ein Kriterienbereich bestehend aus Überschrift (!) und Suchkriterien (!) benötigt, in dem die Bedingungen zur Selektierung definiert werden. Der Kriterienbereich soll nicht unmittelbar an die Quelldaten grenzen und steht im Idealfall oberhalb der Tabelle. Die eigentlichen Bedingungen werden unterhalb der Überschriften erfasst, gearbeitet wird mit Vergleichsoperatoren, zum Beispiel größer (>), kleiner (<), gleich (=).
Die Abbildung unten zeigt eine Kundenliste, aus der alle Kunden des Verkaufgebiets Süd mit einem Umsatz von mehr als 100.000 Euro und guter Zahlungsmoral herausgefiltert werden, die mindestens seit dem 31.12.2015 als Stammkunden geführt werden. Die gefilterten Informationen werden mithilfe des Fensters Spezialfilter an den sog. Zielbereich übergeben, bei dem auf ein ausreichendes Platzangebot zu achten ist. Für komplexere Abfragen gibt es Datenbankfunktionen.
Tipp 6: Datenbankfunktionen
Datenbankfunktionen (Formeln/Funktion einfügen/Kategorie auswählen/Alle Funktionen: Datenbank) werten Datenlisten aus und ermöglichen komplexere Abfragen als Filterfunktionen (Tipp 5). Alle Datenbankfunktionen arbeiten mit den Argumenten Datenbank, Datenbankfeld und Suchkriterien.
Datenbank entspricht dem Tabellenbereich mit der Datenliste. Als Datenbankfeld wird eine Spaltenüberschrift verwendet, deren Spalte numerische Angaben (!) enthält. Damit Datenbankfunktionen korrekt arbeiten, wird, wie bei den Spezialfiltern, ein Kriterienbereich benötigt, den Sie ober- oder unterhalb der Datenbank anordnen. Dieser Bereich entspricht in der Regel den Suchkriterien.
Die obere Abbildung zeigt ein Beispiel, das mit DBANZAHL die Kundenzahl ermittelt, die im Verkaufsgebiet Süd einen Umsatz von über 130.000 Euro getätig haben und mindestens seit 2015 Kunde sind oder im Gebiet Nord über 100.000 Euro Umsatz liegen und dort seit 2017 als Kunde geführt werden.
Bitte beachten Sie: Als Datenbankfeld könnte alternativ zu A4 C4, D4 oder F4 genannt werden, nicht jedoch B4 oder E4 bzw. G4, da diese ausschließlich Texteinträge enthalten.
Tipp 7: Kategorisieren und Gruppieren
Für eine gute Übersicht in umfangreichen Datenlisten sorgt auch die Gruppe Gliederung aus dem Menü Daten. Mit Teilergebnissen können Sie Gruppen nach ganz bestimmten Kriterien verdichten; wie genau hängt von Ihrem Datenbestand ab. In der unteren rechten Abbildung sehen Sie eine Umsatzliste, deren Umsätze nach Verkaufsgebieten addiert wurden.
Wichtig: Um Teilergebnisse zu bilden, sortieren Sie die Datenliste zunächst nach dem gewünschten Gliederungskriterium (hier Verkaufsgebiete). Setzen Sie den Cursor in die Datenliste, und klicken Sie auf die Schaltfläche Teilergebnisse. Im folgenden Fenster geben Sie unter Gruppieren nach: das Gliederungskriterium an (Verkaufsgebiete). Unter Verwendung von: entscheiden Sie sich für Summe, wenn Sie Werte addieren möchten. Alternativ können Sie sich u. a. die Anzahl der Einträge oder einen Mittelwert anzeigen lassen.
Im Feld Teilergebnisse addieren zu: bestimmen Sie, welche Spalte ausgewertet werden soll (hier Umsatz). Mit OK bestätigen, fertig. Um explizit die Teilergebnisse zu zeigen, gibt es im linken Bildschirmbereich Schaltflächen, die es ermöglichen, einzelne Ebenen auszublenden.
Tipp: Pivot-Tabellen aus dem Menü Einfügen sind interaktive Tabellen, mit denen sich umfangreiche Datenmengen ebenfalls schnell zusammenfassen lassen. Das heißt, die Daten können unter verschiedenen Gesichtspunkten umgeordnet, verdichtet, auswertet und betrachtet werden. Die genauen Arbeitsschritte richten sich nach dem Datenbestand. Wer ein wenig probiert, dem sollte sich die Funktion rasch erschließen.
Tipp 8: Datenimport
Liegen Daten bereits in anderen Anwendungen in Textform vor und sollen mit Excel weiterverarbeitet werden, können diese über Datei/Öffnen, Dateityp/Textdateien in Tabellen zur Verfügung gestellt werden. Über Öffnen erhalten Sie den ersten von drei Schritten des Textkonvertierungsassistenten.
Da die Vorgehensweise vom jeweiligen Datenbestand abhängt, müssen Sie vielleicht ein wenig probieren, bis ein gutes Ergebnis vorliegt. Ist das Resultat über Getrennt nicht akzeptabel, versuchen Sie es mit Feste Breite. Über Weiter geht es zum zweiten Schritt des Assistenten, der in Abhängigkeit der im Vorfeld gewählten Option (Getrennt oder Feste Breite) Angaben verlangt. Bei Getrennt definieren Sie das Trennzeichen über das zugehörige Kontrollkästchen.
Bei Feste Breite bestimmen Sie eine Umbruchlinie durch einen Klick auf die gewünschte Position. Selten müssen Sie den dritten Schritt des Assistenten aufrufen. Zeigt die Vorschau ein gutes Ergebnis, klicken Sie auf Fertigstellen.
Tipp 9: Transponieren
Sie stellen nachträglich fest, dass die Angaben in den Zeilen besser in Spalten aufgehoben wären oder umgekehrt? Markieren Sie die komplette Liste einschließlich Überschriften, und kopieren Sie diese. Setzen Sie die Eingabemarkierung in einen Bereich, zu dem es keine Überschneidungen mit der Datenliste gibt. Im Kontextmenü klicken Sie unter Einfügen auf Transponieren.
Tipp 10: Datei arbeitet schleppend
Ist die Arbeitsmappe langsam, kann das an der Bedingten Formatierung liegen (Tipp 3). Dann sollte die Funktion nach Möglichkeit abgeschaltet werden. Formeln können ebenfalls Grund für Geschwindigkeitseinbußen sein. Diese lassen sich unter Umständen gegen Werte austauschen: Inhalte kopieren und im Kontextmenü unter Einfügen die Option Werte auswählen.
