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.
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.
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.
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.
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"
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.
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.
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.
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.
