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.

Power Query in Excel 2016: Text/CSV laden

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.

Dialog mit Datenvorschau

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.

Der Power Query Abfrageeditor

Unsere erste Aktion besteht gleich darin den automatisch (unnötigerweise) angelegten Schritt Geänderter Typ zu entfernen.

Datentypen anpassen im Power Query Editor

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... Power Query Transformation: Erste Zeile als Überschriften verwenden

...was uns erneut einen automatisch angelegten Geänderter Typ Schritt beschert, den wir dann auch gleich wieder entfernen.

Datentypen anpassen

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

Fomat anpassen mit Gebietsschema in Power Query

...auf den in diesem Fall korrekten Wert English (USA) . Das ganze machen wir für beide Spalten (lat und lon).

Dialog zur Eingabe des Gebietsschemas

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.

Power Query: Schließen und Laden In

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:

Power Query Ergebnisse nur ins Datenmodell laden

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.

Power Pivot Ribbon in Excel 2016

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.

Das Datenmodell Fenster von Power Pivot

Zu guter Letzt fügen wir dann noch eine Pivot-Tabelle auf die Daten in unser Arbeitsblatt hinzu.

Power Pivot Tabelle in Excel 2016 hinzufügen

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

Excel Dialog für neue Pivot Tabellen

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.

Power Map Visualisierung in Excel

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.