Wer braucht in Excel mehr als eine Million Zeilen? Für viele Anwendungsfälle genügen doch die verfügbare Menge von 1 Million Zeilen vollkommen (genauer gesagt 1.048.576). Wer Excel allerdings für Datenauswertungen einsetzt, ist in vielen Fällen heute schon Daten-Multimillionär. Zum Glück gibt es in Excel Power Pivot!
Das Excel Datenmodell
Seit Excel in der Version 2013 gibt es das Excel Datenmodell. Das Datenmodell ist die interne Komponente, die die Basis für Excel Power Pivot ist. In diesem Datenmodell ist die Anzahl der Zeilen (und Spalten) lediglich durch den verfügbaren Arbeitsspeicher begrenzt.
Hinweis: Wer Office noch in der 32-bit Version einsetzt ist durch die Einschränkung des Arbeitsspeichers auf max. 2 GB pro geöffnetem Excel noch etwas mehr begrenzt, aber für ein paar Millionen Zeilen genügt das auch.
Beispieldaten in Excel laden
Da Sie die Millionen Zeilen sicher nicht von Hand eingeben wollen, müssen Sie die Daten aus einer vorhandenen Datenquelle laden. Beispielsweise aus eine oder mehrerer Dateien oder aber eine Datenbank. Auch wenn es in Excel noch andere Möglichkeiten gibt, legen wir Ihnen dazu die Verwendung von Power Query wärmstens ans Herz. Wenn Sie nicht wissen, was Power Query ist, dann lesen Sie unseren Einstiegsartikel zu Power Query.
Eine Auswertung über Adressen und Straßen in Deutschland
Als Beispiel verwende ich eine aus der Openstreetmap Datenbank extrahierte CSV Datei, in der alle in OpenStreetmap erfassten Hausnummern in Deutschland mit Straße und Postleitzahl verzeichnet sind. Die Datei ist ca 150 MB groß (Lizenzbedingungen für Open-Streetmap sind hier zu finden). Gefunden habe ich die Datei in diesem Forumsbeitrag auf OpenStreetmap.
Damit stehen uns über 9 Millionen Zeilen für das Beispiel zur Verfügung. Das heißt, wenn Sie das Beispiel nachspielen wollen, sollten Sie die 64-Bit Version von Excel einsetzen.
Los geht's
Wir starten mit der Auswahl des Typs (Textdatei) der zu importierenden Daten. Im Dateiauswahldialog wählen wir die (vorher entpackte) Datei aus.
Im folgenden Dialog ändern wir das Encoding auf den für diese Datei korrekten Wert: UTF-8 (dann werden auch Umlaute korrekt dargestellt) und legen außerdem fest, dass die Datentypen nicht ermittelt werden sollen (ansonsten erkennt Excel die Postleitzahl als Ganzzahl und unterschlägt die führenden Nullen). Wir verlassen den Dialog über Bearbeiten, weil wir ja im Power Query Abfrageeditor noch ein paar Anpassungen vornehmen wollen.
Der Abfrageeditor von Power Query ist aufgeteilt in drei Bereiche: Oben die Ribbon, Links die Vorschau auf die (bearbeiteten) Daten und rechts die Liste mit den (Bearbeitungs-) Schritten, mit denen die Rohdaten transformiert werden.
Unsere erste Aktion besteht gleich darin den automatisch (unnötigerweise) angelegten Schritt Geänderter Typ zu entfernen.
Genauso entfernen wir die erste Spalte, da wir Speicherplatz sparen wollen und den internen Key nicht benötigen. Bitte aber die Spalten nicht Löschen, sondern die Spalten auswählen (siehe auch den Artikel, warum du in Power Query niemals Spalten löschen sollst).
Im nächsten Schritt teilen wir Power Query mit, dass es die erste Zeile als Überschrift verwenden soll...
...was uns erneut einen automatisch angelegten Geänderter Typ Schritt beschert, den wir dann auch gleich wieder entfernen.
Da die Geodaten (Länge/Breite) im amerikanischen Format vorliegen (Punkt als Dezimaltrenner), ändern wir über einen Rechtsklick auf die Spalte den Typ Mit Gebietsschema...
...auf den in diesem Fall korrekten Wert English (USA) . Das ganze machen wir für beide Spalten (lat und lon).
Daten in Excel laden
Nachdem wir mit den in der Vorschau angezeigten Daten jetzt zufrieden sind wählen wir (wichtig!) über das Submenü am Button Schließen & laden den Eintrag Schließen & laden in... aus. Wenn wir hier nur Schließen & laden wählen, würde Excel versuchen die Daten in das aktuelle Arbeitsblatt zu laden, was wir nicht wollen.
Im nachfolgenden Dialog wählen wir Nur Verbindung erstellen, da wird die Daten gerade nicht in ein Excel-Arbeitsblatt laden wollen. Das würde nicht funktionieren, da Excel-Arbeitsblätter nicht mehr als eine Million Zeilen zulassen. Uns liegen aber ca. 12 Millionen Datensätze vor. Anschließend setzen wir den Haken bei Dem Datenmodell die Daten hinzufügen:
Nach dem Laden
Das Laden wird etwas dauern. Nach dem Kaffeeholen sind, wenn genug Arbeitsspeicher vorhanden ist, die Daten dann tatsächlich im Datenmodell enthalten und wir können die Daten in der Power Pivot Oberfläche anschauen.
Hinweis: Falls der Power Pivot Ribbon nicht angezeigt wird, müssen Sie vermutlich das AddIn aktivieren über Datei > Optionen > Add-Ins > Verwalten: COM-Add-Ins > Los... das Addin Microsoft Power Pivot for Excel aktivieren.
In Power Pivot können Sie die eingelesenen Daten ansehen, filtern, erweitern und noch vieles mehr. Zum Start empfehle ich Ihnen unseren Artikel Einführung in Power Pivot oder unseren neuen Online Kurs.
Zu guter Letzt fügen wir dann noch eine Pivot-Tabelle auf die Daten in unser Arbeitsblatt hinzu.
Hier wählen Siest du Das Datenmodell dieser Arbeitsmappe verwenden, um in der Pivot-Tabelle Zugriff auf alle Tabellen des Datenmodells zu bekommen. (Ja, man kann mehrere Tabellen ins Datenmodell importieren und diese dort auch in Beziehung zueinander setzen).
Schon am Ende?
Für den geneigten Leser noch ein paar Ideen zum Weiterüben.
- PLZ-Informationen als zusätzliche Tabelle ins Datenmodell hinzufügen
- Anzeige der Daten in Excel PowerMap (Blogbeitrag zu diesem Thema folgt), zum Beispiel die Anzahl der Hausnummern pro PLZ. Damit lässt sich dann so ein Karte erzeugen, das die Bevölkerungsverteilung in Deutschland erahnen lässt.
Starthilfe zur Analyse deiner Datenmengen
Hier finden Sie noch mehr Informationen, was Power BI und Excel Power Pivot ist, wie sich Power Query einreiht und wie Sie sich zwischen diesen Werkzeugen entscheiden:
Und dann gibt es noch unserenOnline-Kurs, der dir eine Einführung in Power Pivot gibt.
Kommentar hinterlassen