Excel jenseits einer Millionen Zeilen

    Wer braucht mehr als eine Millionen Zeilen?

    Für viele Anwendungsfälle genügen die pro Excel Arbeitsblatt verfügbaren Million (genauer gesagt 1.048.576) Zeilen ja vollkommen. Wer Excel allerdings für Datenauswertungen einsetzt, ist in vielen Fällen heute schon Daten-Multimillionär.

    Das Excel Datenmodell

    Zum Glück gibt es in Excel dafür seit der Version 2013 das Excel Datenmodell (das ist die interne Komponente die die Basis für Power Pivot ist). In diesem 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.

    Daten in Excel laden

    Da du die Milllionen Zeilen sicher nicht von Hand eingeben willst, musst du die Daten aus einer vorhandenen Datenquelle (meist eine bzw. mehrere Dateien oder aber eine Datenbank) laden. 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 (ca 150 MB, Lizenzbedingungen für Open-Streetmap sind hier zu finden), in der alle in OpenStreetmap erfassten Hausnummern in Deutschland mit Straße, PLZ und verzeichnet sind (gefunden habe ich die Datei in diesem Forumsbeitrag auf OpenStreetmap).
    Das sind über 9 Millionen Zeilen, d.h. 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.

    Text/CSV laden via Power Query

    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 Postleitzahlenspalte 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.Datenvoransicht in Power Query

    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 Abfrageeditor in Power QueryUnsere 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…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).Power Query 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ürden, würde Excel versuchen die Daten in das aktuelle Arbeitsblatt zu laden, was wir ja nicht wollen (und was hier auch nicht funktionieren würde).

    Power Query: Schließen und Laden In

    Im nachfolgenden Dialog wählen wir Nur Verbindung erstellen, da wird die Daten ja gerade nicht in ein Arbeitsblatt laden wollen (würde nicht passen, im Arbeitsblatt gehen ja nur 1 Mio Zeilen, wir haben aber ca. 12 Mio) und setzen die Haken bei Dem Datenmodell die Daten hinzufügen.

    Power Query Ergebnisse nur ins Datenmodell laden

    Nach dem Laden

    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. (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.)Das Datenmodell aus Excel heraus öffnen

    In Power Pivot kannst du die eingelesenen Daten ansehen, filtern, erweitern und noch vieles mehr (zum Start empfehle ich folgenden Artikel zur 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.Pivot Tabelle hinzufügen

    Hier wählst du Das Datenmodell dieser Arbeitsmappe verwenden, um in der Pivottabelle 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 gefällig?

    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.

    // My delegate func for WP-TopBar calls