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 1048576) 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 PowerPivot ist). In diesem ist die Anzahl der Zeilen (und Spalten) lediglich durch den verfügbaren Arbeitsspeicher begrenzt. (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 Sie die Milllionen Zeilen sicher nicht von Hand eingeben, müssen Sie 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 Ihnen dazu die Verwendung von Power Query ans Herz. Für Excel 2013 ist Power Query ein kostenfreies Excel-AddIn (Downloadlink bei Microsoft), bei Excel 2016 ist die komplette Funktionalität bereits unter dem Reiter “Daten” vorhanden.

    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 Sie das Beispiel nachspielen wollen sollten Sie eine 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 Postleitzahlenspalte als Ganzzahl und unterschlägt die führenden Nullen). Wir verlassen den Dialog  über Bearbeiten, weil wir ja im PowerQuery Abfrageeditor noch ein paar Anpassungen vornehmen wollen.

    Der Abfrageeditor von Power Query ist aufgeteilt in 3 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 (Rechtsklick öffnet das Spaltenmenü), da wir Speicherplatz sparen wollen und den internen Key nicht benötigen.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ü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).

    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.

    Nach dem Laden

    Nach dem Kaffeeholen sind, wenn genug Arbeitsspeicher vorhanden ist, die Daten dann tatsächlich im Datenmodell enthalten und wir können Sie uns in der Power Pivot Oberfläche anschauen. (Falls der Power Pivot Ribbon nicht angezeigt wird, müssen Sie wahrscheinlich ü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 folgenden Artikel zur Einführung in Power Pivot).

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

    Hier wählen Sie 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).

    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.

    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.