Excel jenseits einer Millionen Zeilen

    Wer braucht in Excel mehr als eine Millionen 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 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 du die Milllionen Zeilen sicher nicht von Hand eingeben willst, musst du 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 dir dazu die Verwendung von Power Query ans Herz. Wenn du nicht weißt, was Power Query ist, dann lies 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 du das Beispiel nachspielen willst, solltest du 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, musst du 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 kannst du die eingelesenen Daten ansehen, filtern, erweitern und noch vieles mehr. Zum Start empfehle ich dir 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ählst 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 findest du noch mehr Informationen, was Power BI und Power Pivot ist, wie sich Power Query einreiht und wie du dich zwischen diesen Werkzeugen entscheidest:

    Und dann gibt es noch unserenOnline-Kurs, der dir eine Einführung in Power Pivot gibt.

    Kategorien: Artikel, Grundlagen, Power Query
    Andreas Moosbrugger

    Andreas Moosbrugger ist Experte für Power BI, Power Pivot und Power Query. Als Softwareentwickler und Berater bei der mogular GmbH analysiert er Softwaresysteme, konzipiert sie neu und integriert dabei Geschäftsprozesse, Organisation und IT. Bevor er 2011 mit Holger Gubbels die mogular GmbH gegründet hat, war er bei Daimler und AEB tätig. Dem Diplom-Ingenieur liegt es am Herzen, das Wissen zur kostenlosen BI-Software verfügbar zu machen, damit jeder auch ohne teure Programme seine Daten effektiv analysieren kann. Deshalb bloggt er hier auf PowerPivotInsights. Sie erreichen ihn unter am@mogular.com

    One Comment

    Antwort schreiben

    Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.