Office-Blog

Große Datenbestände auswerten mit PowerPivot

23.07.2013   Volker Heck

Welche Excel-Nutzer brauchen PowerPivot? Dieser Frage gehe ich derzeit in einem Unternehmen nach und schaue dabei Anwendern bei der Arbeit über die Schulter. Zwei Dinge  beeindrucken mich dabei:

a) wie leicht den meisten der Einstieg in PowerPivot fällt und

b) wie groß die Zeitersparnis im Vergleich zum konventionellen Arbeiten mit Excel ist.

Was machen Nutzer mit PowerPivot typischerweise?

Sie werten wiederholt eine große Anzahl von Datensätzen (>100.000 Zeilen) in Pivot-Tabellen aus  und filtern diese nach unterschiedlichen Kriterien. Mit den üblichen Excel-Befehlen entstehen dabei lange Wartezeiten. Teilweise waren Zeiten zwischen 5 und 15 Minuten zu beobachten.

Mitarbeiter, die Monat für Monat eine größere Anzahl von Reports erzeugen, sparen übers Jahr betrachtet mit PowerPivot nicht nur Stunden, sondern mehrere Tage an Arbeitszeit ein.

Der Test an einem konkreten Beispiel

Wie das geht, möchte ich hier kurz beschreiben. Aus verständlichen Gründen kann ich keine echten Daten verwenden, aber ich habe eine typische Tabelle nachgebaut. Sie enthält 1,5 Millionen Zeilen mit Handelsinformationen aus verschiedenen Handelssystemen. Werte in der Zukunft basieren auf Hochrechnungen.

Die Registerkarte PowerPivot

Die Daten SQL Server importieren und dann analysieren

In der oben gezeigten Registerkarte PowerPivot befindet sich links die Schaltfläche PowerPivot-Fenster. Ein Klick auf diese öffnet die Umgebung zum Erstellen von Tabellen und Berechnungen in PowerPivot.

Die PowerPivot-Befehle zum Abrufen externer Daten

In der Befehlsgruppe Externe Daten abrufen wähle ich Aus Datenbank – Aus SQL Server und importiere so die gesamte Tabelle mit ihren 1.500.000 Zeilen. Das dauert auf meinem Laptop etwa 30 Sekunden.

Eine erste Auswertung

Um die Gewinne nach Jahren aufzuteilen, öffne ich direkt aus dem PowerPivot-Fenster heraus eine Pivot-Tabelle (im folgenden Bild die Schaltfläche ganz rechts).

Eine Pivot-Tabelle aus PowerPivot heraus erstellen

Je nach gewählter Option wird im aktuellen oder einem neuen Arbeitsblatt eine ganz normale Pivot-Tabelle  angelegt. Der Unterschied zu „reinem“ Excel: Die auszuwertenden Daten werden nicht in Excel, sondern in PowerPivot gespeichert.

Zum Auswerten der Gewinne nach Jahr ziehe ich rechts in der PowerPivot-Feldliste den Gewinn in den Bereich Werte und das Jahr in den Bereich Zeilenbezeichnung. Das Ergebnis ist sofort da.

PowerPivot-Auswertung 1

Die Zahlen formatieren

Um den Gewinn passend zu formatieren, wechsle ich wieder ins PowerPivot-Fenster, markiere die Spalte Gewinn und wähle die Formatierungsoption Tausendertrennzeichen.

Die Formatierungsbefehle  Die Schaltfläche Aktualisieren

Was ändert sich in der Pivot Tabelle? Eine gelbe Schaltfläche in der Feldliste weist auf die notwendige Aktualisierung hin. Nach deren Anklicken sind die Änderungen in der Pivot-Tabelle sichtbar.

Die Analyse verfeinern

Nun möchte ich ermitteln, welche Gewinne jeweils hinter einem Handelsbuch stehen. Nichts leichter als das. In der Feldliste verschiebe ich das Jahr auf die Spalten und das Handelsbuch auf die Zeilen. Voilà!

PowerPivot-Auswertung 2

Handelsbücher in Gruppen zusammenfassen: Import einer Excel-Datei

Um die Auswertungen übersichtlicher zu gestalten, sind die Handelsbücher in Gruppen zusammengefasst. Es wäre schön, wenn ich diese Gruppen auch in der Auswertung verwenden könnte.

In der Datenbank, aus der die Daten stammen, gibt es eine solche Gruppierung allerdings nicht. Doch glücklicherweise hatte jemand vor mir die Zuordnung der Handelsbücher zu Gruppen in einer Excel-Datei dokumentiert.

Jetzt kommt neben der hohen Geschwindigkeit die zweitwichtigste Eigenschaft von PowerPivot zum Tragen: Diese Excel-Datei kann ich ebenfalls nach PowerPivot importieren und dort mit der anderen Tabelle, die ursprünglich aus einer Datenbank kommt, verbinden.

Das Importieren der Daten aus Excel erfolgt ähnlich wie der Import aus einer Datenbank. Über Aus anderen Quellen wähle ich die Excel-Datei aus.

Excel-Import über Aus anderen Quellen

Übrigens, so sieht die Excel-Datei mit der Zuordnung der Handelsbücher zu Gruppen aus.

Die Gruppenzuordnung in Excel

Vor einer Analyse der Daten, sollte ich die Verbindung zwischen beiden Tabellen definieren. Über die Schaltfläche Diagrammansicht lasse ich mir die Beziehungen anzeigen.

Die Diagrammansicht aufrufen

Zunächst besteht keine Verbindung zwischen den beiden Tabellen. Mit Drag & Drop stelle ich die Verbindung über die Spalte Handelsbuch einfach her.

Die Tabellen verbinden

In der Feldliste ist zu sehen, dass nun die Felder aus zwei Tabellen (Handelsdaten und Zuordnung) für die Analyse zur Verfügung stehen.

Die beiden Tabellen stammen ursprünglich aus völlig unterschiedlichen Quellen. Dennoch kann ich sie nun gemeinsam auswerten.

PowerPivot-Auswertung 3

Und nächsten Monat?

Wenn ich diesen Bericht nächsten Monat erneut mit den dann aktuellen Zahlen erstelle, kann ich die gerade erstellte Excel-Datei wiederverwenden.

Im PowerPivot-Fenster gibt es die Schaltfläche Aktualisieren. Ein Klick darauf bewirkt, dass PowerPivot die Daten aus der Datenbank erneut einliest.

Nun noch ein Klick auf Aktualisieren in der Pivot Tabelle, und diese enthält die aktuellen Daten.

Zwischenfazit und Vorschau

Die hier beschriebenen Schritte zeigen, wie einfach sich mit Hilfe von PowerPivot auch große Datenmengen schnell analysieren lassen.

Durch die Kombination von Tabellen aus unterschiedlichen Quellen erschließen sich neue Möglichkeiten.

Im zweiten Teil zeige ich weitere Stärken von PowerPivot und füge der Auswertung Berechnungen hinzu.

 

Über den Autor

Volker Heck

Das könnte Sie auch interessieren

3 Comments so far

MoritzPosted on  4:02 pm - Mai 18, 2017

Hallo! Ich habe jeden Monat eine neue Datei, nicht eine bestehende die immer größer wird. Wie kann ich die jeweils neue Datei am besten an die bestehenden Daten anknüpfen?
Danke!
Moritz

matthiasPosted on  2:51 pm - Mai 22, 2015

die maximale Zeitenanzahl von Excel 2010 beträgt 1.048.576 wie um himmelswillen kann man dann 1,5 Mio Zeilen Auswerten.

    svenPosted on  1:10 pm - Sep 28, 2015

    Die 1,5 Zeilen sind Datensätze in einer SQL-Datentabelle. Dort gibt keine Beschränkung auf die Zahl möglicher Zeiten in Excel. In Pivot-Tabellen werden diese Datensätze i.d.R. verdichtet/gruppiert dargestellt. Problematisch wird es dann, wenn eine Pivot-Tabelle so konzipiert wird, das jeder Datensatz einzeln in dieser erscheint, was nicht (immer) die Notwendigkeit in Pivot-Tabellen, ist.

Schreibe eine Antwort