Datenverarbeitung mit Excel

Excel als Datenbank: 4 Tipps für mehr Performance

Excel als Datenbank zu verwenden kann eine bequeme Lösung sein. Wenn die Datei jedoch immer größer wird, fällt die Zeitersparnis weg. Mit der richtigen Technik gleichen Sie den Performance-Verlust wieder aus.

Microsoft Excel

© microsoft

Microsoft Excel

Excel als Datenbank? Warum auch nicht. Denn seit Excel 2007 stehen dem Anwender insgesamt 1.048.576 Zeilen und 16.384 Spalten pro Tabellenblatt zur Verfügung. Das ist ein gewaltiges Raster und eine deutliche Erweiterung der ursprünglichen 65.536 Zeilen und 256 Spalten. Im Vergleich dazu bietet Access nur 256 Spalten an. Dies ist einer der Gründe, warum Excel als Datenbank missbraucht wird. Dass das Programm mit so vielen Daten verhältnismäßig langsam wird, nehmen die Anwender dabei in Kauf. Doch es geht auch schneller.

Dieser Artikel beschreibt vier Techniken, wie Sie große Datenmengen in Excel mit Datenbank-Funktionen, Datenfeldern, SQL und modernen Algorithmen verarbeiten. Diese Techniken benötigen sehr wenig Zeit, da Sie die automatische Berechnungsfunktion umgehen und die ganze Rechenarbeit im Arbeitsspeicher ausführen.

Technik 1: Die Ping-Pong-Technik

Immer wenn Sie Daten in einer Excel-Tabelle weiterverarbeiten, rechnet Excel bei jedereinzelnen Eingabe die beteiligten Zellen und Bereiche neu durch. Das dauert. Daher wird bei der ersten Technik die Berechnung von Excel komplett umgangen. Der Inhalt einer Excel-Tabelle wird dabei zunächst mit einem einzigen Befehl in den Arbeitsspeicher Ihres PCs befördert. Hier findet dann die eigentliche Verarbeitung statt.

Excel Kundentabelle

© Hersteller / Archiv

Als Ausgangssituation dient eine Kundentabelle mit 6000 Datensätzen, die nach Bestellhäufigkeit sortiert ist. Mit der Ping-Pong-Technik filtern Sie blitzschnell nach Kunden, die mehr als fünf Mal bestellt haben.

Dazu wird im Arbeitsspeicher ein Datenfeld aufgebaut, indem die Daten weiterverarbeitet werden. Nach erfolgter Verarbeitung wird der komplette Inhalt der Daten aus dem Arbeitsspeicher in eine Ziel-Tabelle befördert. Auch für diese Aktion benötigen Sie wiederum nur einen einzigen Befehl. Die meisten Arbeiten auf Basis dieser Technik bei sehr großen Datenmengen benötigen weniger als eine Sekunde.

Beim folgenden Beispiel wird der Tabelleninhalt (Kundendaten) der Tabelle tbl_Gesamt in den Arbeitsspeicher gebracht. Danach werden bestimmte Zeilen aus dem Datenbestand gelöscht, und anschließend wird der Rest der verbleibenden Datenmenge in der Tabelle tbl_Ergebnis ausgegeben.

In der Tabelle tbl_Gesamt sollen alle die Kundendaten in die Tabelle tbl_Ergebnis übertragen werden, die eine Bestellhäufigkeit, wie in Zelle I1 angegeben, aufweisen. Den Quellcode (Listing 1 Die Ping-Pong-Technik.docx) und die Beispieldateien (Quelle.xlsx, Ergebnis.xlsx und PerformanceTechniken.xlsm) finden Sie auf der Heft-DVD unter Top-Software/Extras zum Heft/Excel Datenbanktechniken.

Zunächst wird die Startzeit der Prozedur über die Anweisung Debug.Print festgehalten. Danach wird über die Methode ClearContents die Tabelle tbl_Ergebnis geleert. Die Größe des in Verwendung befindlichen Bereichs wird über die Eigenschaft Usedrange für die Spalten sowie für die Zeilen ermittelt.

Danach wird der verwendete Bereich der Tabelle direkt in ein Datenfeld befördert. Die Daten befinden sich jetzt im Arbeitsspeicher. Dort angekommen, wird ein gleichgroßes Datenfeld mit dem Namen VardatZiel angelegt. Über eine Schleife werden die dem Kriterium der Menge entsprechenden Daten aus dem Datenfeld Var-Dat in das Datenfeld VardatZiel geschoben.

Excel Kundentabelle

© Hersteller / Archiv

Alle Kunden, die mehr als fünf Mal bestellt haben, werden aufgelistet.

Im letzten Schritt wird das Datenfeld VardatZiel in die Tabelle tbl_Ergebnis gekippt. Dazu muss die Größe des Datenfeldes in der Tabelle vorreserviert werden. Über die Methode AutoFit werden die Spalten der Tabelle automatisch angepasst. Im Beispieltest benötigt diese Technik für die Reduktion von 6000 Datensätzen (tbl_ Gesamt) in 3.619 Datensätze (tbl_Ergebnis) weniger als eine Sekunde.

Technik 2: SQL einsetzen, um große Datenmengen blitzschnell auszuwerten

Bei dieser Technik kommt die Datenbankabfragesprache SQL zum Einsatz. Dabei können Abfragen gestartet werden, die dann Daten aus der aktiven oder einer noch geschlossenen Arbeitsmappe holen und in einer Zieltabelle ausgeben. Über die Prozedur (Listing 2 Zugriff auf Daten über ein SQL-Statement.docx) lösen Sie die gleiche Aufgabe wie in Technik 1 beschrieben.

Es sollen über den Einsatz einer SQL-Anweisung alle Kundendaten aus der Tabelle tbl_Gesamt in die Tabelle tbl_SQL gespielt werden, die eine Bestellhäufigkeit aufweisen, die in Zelle I1 gefordert wird.

Excel Kundentabelle

© Hersteller / Archiv

Die Gesamttabelle (6000 Datensätze) soll nach Spalte 6 (=F) auf 25 Tabellen verteilt werden. Unser Datenfilter-Makro braucht dafür vier Sekunden.

Zunächst wird sicherheitshalber die Zieltabelle tbl_SQL über die Methode ClearContentsgeleert. Danach wird ein ADO-Objekt (Access Data Sources) erzeugt, über das der Zugriff auf SQL-Befehle möglich wird. Danach wird als Ziel die eigene, aktuell geöffnete Arbeitsmappe angegeben und die Verbindung über die Methode Open eröffnet.

Danach wird das SQL-Statement zusammengesetzt. Hier liegt die eigentliche Intelligenz der Prozedur. Über das Schlüsselwort SELECT werden die Feldnamen (hier die Überschriften der Excel-Tabelle tbl_Gesamt) mit Komma getrennt angegeben.

Dabei wird die Quelltabelle im FROMBefehl angegeben. Über den Begriff WHERE wird eine Bedingung formuliert. Das SQLStatement ORDER BY legt die Sortierung fest, nach der die Daten in der Zieltabelle tbl_SQL abgelegt werden sollen:

strSQL = "SELECT Name, Vorname, Straße, PLZ, Ort, [Anzahl Bestellungen]" & "FROM [tbl_Gesamt$] WHERE [Anzahl Bestellungen] >= 5" & "ORDER BY [Anzahl Bestellungen] DESC"

Excel Kundentabelle

© Hersteller / Archiv

Und hier das Ergebnis: Die 25 Tabellen wurden in einer neuen Mappe automatisch angelegt, und die Daten wurden darin verteilt.

Im Anschluss werden die ermittelten Daten aus dem Speicher über die Methode Copy-FromRecordset in die Zieltabelle befördert. Als Laufzeit benötigt diese Technik etwas länger. Die Reduktion von 6000 Datensätzen auf 3619 Datensätze dauerte aber immer noch weniger als zwei Sekunden.

Tipp: Mit der gleichen Technik können Sie übrigens auch auf geschlossene Arbeitsmappen zugreifen. Dazu muss lediglich eine Zeile angepasst werden:

strConnection = "DRIVER={Microsoft Excel Driver (*.xls)}; DBQ=" & This Workbook.Path & "\NameDerMappe.xls"

Technik 3: Einsatz des Datenfilters von Excel über Makro

Bei dieser Technik wird eine Excel-Arbeitsmappe geöffnet und die erste Tabelle darin komplett verarbeitet. Dabei werden die Daten in der Tabelle auf neue Tabellen auf Basis der Bestellungen (Anzahl 1 bis 25) in der neu erstellten Excel-Arbeitsmappe Ergebnis.xlsx verteilt. Dazu wird der Datenfilter von Excel eingesetzt.

Text-Datei

© Hersteller / Archiv

Aus dieser Textdatei sollen nur die Daten von Kunden aus der Schweiz eingespielt werden.

Als Vorarbeit dazu wird in der Tabelle tbl_DatenVerteilen festgelegt, nach welcher Spalte die Daten auf den Tabellen verteilt werden sollen. Die Quelldateien dazu finden Sie auf der Heft-DVD (Listing 3 Den AutoFilter von Excel mehrfach einsetzen, um Daten zu verteilen.docx). Zunächst einmal werden bei dieser Technik lästige Bremser in Excel temporär ausgeschaltet.

Application.Calculation = xlCalculationManualApplication.ScreenUpdating = FalseApplication.DisplayAlerts = False

Über die Eigenschaft Calculation wird die Berechnung kurzfristig ausgeschaltet, indem dieser Eigenschaft die Konstante xlCalculationManual zugewiesen wird. Die Bildschirmaktualisierung wird über die Eigenschaft ScreenUpdating abgeschaltet, indem ihr der Wert False zugewiesen wird.

Über die Methode GetOpenFileName wird ein Dateiauswahl-Dialog am Bildschirm angezeigt und ausgewertet. Vor der eigentlichen Verarbeitung wird zunächst einmal die Gesamtanzahl der Datensätze festgehalten, um später zu prüfen, ob wirklich alle Datensätze verarbeitet und verteilt wurden.

Danach wird eine unikate Liste aus der angegebenen Spalte (hier Spalte F = Anzahl Bestellungen) ermittelt. Die hier ermittelte Anzahl der unikaten Bestellungen bildet die Grundlage der einzufügenden Excel-Tabellen. Dazu wird die Methode AdvancedFilter eingesetzt.

Bereich.AdvancedFilterAction:=xlFilterCopy, _CriteriaRange:=Bereich,CopyToRange:=tbl_DatenVerteilen.Range("H1"), Unique:=True

In einer Schleife wird diese unikate Liste danach durchlaufen. Innerhalb der Schleife wird nach der jeweiligen Gruppe gefiltert, die dazu gehörenden Daten kopiert und in eine neue Tabelle eingefügt. Am Ende der Prozedur erfolgt noch ein Plausibilitätstest, bei dem die Gesamtanzahl der originären Datensätze mit der Anzahl der verteilten Datensätze abgeglichen wird.

Excel Kundentabelle

© Hersteller / Archiv

Mithilfe des Filesystemobjekts in Excel wurden alle Schweizer-Datensätze aus einer Textdatei extrahiert.

Die Verteilung von 6000 Datensätzen auf 25 Tabellen benötigt weniger als 4 Sekunden. Wenn Sie diese Aufgabe manuell über den Datenfilter von Excel durchführen möchten, dann dauert es pro Tabelle 30 Sekunden (wenn Sie schnell sind) und Sie kommen dabei auf 25 Minuten. Rechnerisch ergibt das eine Verbesserung von 18.750 Prozent!

Technik 4: Einsatz des Filesystemobjekts für den schnellen Datenimport

Bei der letzten, hier vorgestellten Technik wird eine Textdatei in Excel importiert. Dabei werden jedoch nur bestimmte Datensätze aus der Textdatei gezogen und in die Tabelle tbl_Suchen eingespielt. Den dazu gehörenden Quelltext finden Sie in der Datei Listing 4 Öffnen, Filtern und Ausgeben über das FileSystem-Objekt.docx.

Mithilfe der Methode ClearContents wird die Zieltabelle bis auf die Überschrift geleert. Danach wird das Objekt Filesystemobject erstellt, welches Ihnen Befehle zur Datei- und Verzeichnisverarbeitung automatisch zur Verfügung stellt.

Set FSO = CreateObject("Scripting.filesystemobject")

Einer davon ist der Befehl OpenTextFile, über den Sie eine Textdatei (hier Umsatz.txt) öffnen. Über die Methode ReadAll wird der komplette Inhalt in den Arbeitsspeicher über ein Datenfeld mithilfe der Funktion Split eingelesen und aufgeteilt.

Mehr zum Thema

Set FsoDat = FSO.OpenTextFile (This Workbook.Path & "\Umsatz.txt")

VarDat = Split(FsoDat.ReadAll, vbCrLf)

Danach kommt der Befehl Filter zum Einsatz, der das komplette Datenfeld nach dem Suchbegriff filtert und das Ergebnis daraus in das Hilfsdatenfeld VardatZiel befördert. Dieses Datenfeld wird nun Zeile für Zeile in die Tabelle tbl_Suchen ausgeleert und abschließend über die Methode TextToColumns auf die Spalten auf Basis des Trennzeichens (hier Semicolon) verteilt. Für das Herausfiltern von rund 7000 Datensätzen in Textdateien auf 36 Datensätze benötigt diese Technik weniger als eine Sekunde!

Fazit

Mithilfe spezieller Techniken verarbeiten Sie auch große Datenmengen in Excel, ohne Excel lahmzulegen. Trotz aller dieser Techniken ist Excel keine Datenbank und kann auch keine Datenbank ersetzen. Die hier vorgestellten Techniken helfen Ihnen aber trotzdem dabei, wenn Sie weiterhin mit Excel als Lieblingswerkzeug auch bei großen Datenmengen arbeiten wollen.

Mehr zum Thema

Excel Tabelle
Office

So günstig wie jetzt waren die Zinssätze lange nicht mehr. Dennoch sollten Sie genau nachrechnen, bevor Sie sich für einen günstigen Kredit…
Microsoft
Microsoft Office 2010

Wir geben Ihnen wertvolle Tipps zu Microsoft Word und Excel. Mit unseren Tricks sparen Sie viel Zeit und machen sich die Arbeit leichter.
Microsoft Office Workshop für Kopf- & Fußzeilen in Excel.
Anleitung zu Microsoft Office

Wie füge ich Kopf- und Fußzeilen in Excel ein? Die Lösung auf diese oft gestellte Frage, geben wir in dieser Anleitung zu Microsoft Office. So…
Excel - Nettoarbeitstage
Office-Tipp

Wir erklären, wie Sie in Excel etwa für eine Arbeits- oder Urlaubsplanung die Wochenenden herausrechnen und die Nettoarbeitstage berechnen.
Windows 10
Nach Windows 10 Update

KB3086786: Dateien von Word, Excel und Powerpoint, die unter Windows 7 erstellt wurden, können nach dem Update auf Windows 10 Probleme machen.