Kategorie: Add-In
Power Query lernen…
… aber wie damit beginnen?
Seit dem Jahr 2013 beschäftige ich mich mit Power Query – zu Beginn kam es als Add-In unter dem Namen Data Explorer auf den Markt.
Und schon gab es lange Gesichter. Nur für Excel 2010 und Excel 2013… und das noch nicht einmal für alle Versionen von Excel 2010. Aktuell laut Download-Seite von Microsoft für die Office-Versionen:
- Microsoft Office 2010 Professional Plus mit Software Assurance
- Microsoft Office 2013
Mit Excel 2016 bzw. Office 365 gehört es dann aber auch in den Excel-Standard über das Register Daten und muss nicht gesondert als Add-In aktiviert werden. Eine Ehre, die bereits Power Pivot in Excel 2013 zuteil wurde (leider auch wieder nicht in allen Versionen).
Power Query wird so regelmäßig erweitert und um neue Funktionen und Importmöglichkeiten ergänzt, dass ein heute erstelltes Skript schon für die nächste Version überarbeitet werden muss. Trotzdem: Es lohnt sich!
So viele Aufgabenstellungen im Bereich Datenimport, Datentransformation und -anreicherung können mit Power Query gelöst werden. Schnittstellen, die früher über Query, VBA oder manuellen Import gelöst wurden und viele manuelle Schritte bzw. SVERWEISe notwendig machten.
Die ersten Schritte in Power Query hatte ich mir selbst erarbeitet, bis ich dann im Mai 2014 Ken Puls (Excel MVP) beim Amsterdam Excel Summit begegnet bin.
Hier ein Bild vom Amsterdam Excel Summit 2015. Ich bin umringt u.a. von Ken Puls, Jon Peltier, Bob Umlas, Henk Vlootman, Tony de Jonker, Roger Govier, Liam Bastick, Charles Williams, Jan-Karel Pieterse und vielen netten anderen Excel-Freunden.
Ken Puls aus Vancouver (Kanada) betreibt die Seite http://www.excelguru.ca und darunter auch seinen großartigen Blog http://www.excelguru.ca/blog/
Ken war dann auch im Folgejahr in Amsterdam und ich konnte noch mehr Input von ihm mitnehmen. Ich habe Möglichkeiten von Power Query entdecken dürfen, die ich mir niemals selbst hätte erarbeiten können. Mehr Infos zu parametergesteuerten Abfragen, zur Einbettung von Funktionen und tiefergehendes Hintergrundwissen. Ken beschreibt in seinem Blog raffinierte Lösungen und geht auf diverse Alltagsszenarien wie parametergesteuerte SQL-Abfragen ein.
Zudem hat Ken Puls zusammen mit Miquel Escobar das Buch M is for (Data) Monkey geschrieben. Dieser Titel hat mich in Techniken eingeführt, die zum Teil schon im Blog standen, zum Teil aber auch bislang völlig neu für mich waren. Eine unglaublich lehrreiche Lektüre, welche ich euch – auch als ebook – ans Herz legen mag. Zudem gibt es hier Zugang zu Online-Ressourcen, über die man auch sonst nicht verfügbare Datenquellen wie Azure anzapfen kann.
Am wichtigsten sind für mich die eigenen Erfahrungen, die ich in der Praxis bei mir oder beim Kunden sammeln kann. Ich lerne ungemein aus den Blogbeiträgen und der Lektüre, aber auch immer wieder beim Austausch an den Excel-Stammtischen.
Ich hoffe, das kommt hier nicht so als Freakshow rüber. Ja, tatsächlich…. wir treffen uns regelmäßig zu Excel-Stammtischen z.B. am 01.12.2016 in Münster, organisiert durch das ITB der FH Münster. Prof. Dr. Johannes Schwanitz und ich haben den Stammtisch dort ins Leben gerufen und alle können von den Erfahrungen und Problemen der anderen Teilnehmer profitieren. Der direkte Wissensaustausch funktioniert doch viel effizienter, als das alleinige googlen und ausprobieren.
Zuletzt… lernt nicht allein. Ihr bleibt allein schneller an Fragen hängen, könnt nicht von den Erfahrungen und Lösungen anderer profitieren und gebt somit viel schneller auf.
- Lernt nicht allein, sucht euch in der Firma einen Sparringspartner
- Tauscht euch mit anderen aus, egal ob in Foren oder live
- Schaut in nützliche Blogs und YouTube-Kanäle
- Experimentiert mit Power Query
- Arbeitet das Buch von Ken durch
- Besucht Seminare zu diesem Thema… seht die Teilnahmegebühr als Investition, nicht als Kosten.
Träumt nicht von Excel!
Euer Andreas
Excel – Power Query – Dateien importieren – csv und txt
Mit Power Query können die Dateien eines Ordners ausgelesen werden. Dabei können in der Abfrage aber auch die Inhalte der Dateien ausgelesen und zu einer Liste zusammengefasst werden.
Bei der Auswahl Ordner / Folder werden die Dateien eines anschließend ausgewählten Verzeichnisses aufgelistet.
Über Extension kann der Dateityp gefiltert werden.
Anschließend auf das Symbol bei Content klicken, um statt der Dateiinformationen die Dateiinhalte auszulesen.
Sollten irrtümlich noch Überschriften mit importiert werden, können diese herausgefiltert werden.
Hier sind die Übungsdateien:
http://1drv.ms/1jRavBS
Hier geht es zum Video:
Videolink: http://youtu.be/auSLYl-BfYQ
Kurzlink dieses Beitrags: http://wp.me/p3egMz-uL
Excel – SVERWEIS für sortierte Listen – sehr schnell
Wie schnell ist der SVERWEIS, wenn man einen Wert in einer unsortierten bzw. einer sortierten Liste sucht? Beim SVERWEIS Typ FALSCH ist der Unterschied deutlich messbar. Mit der Hilfe einer kleinen WENN-Funktion und SVERWEIS Typ WAHR ist der Unterschied sogar erheblich. Die Messungen wurden mit dem Add-In FastExcel durchgeführt.
Die beiden Listen umfassen jeweils über eine Million Datenzeilen. Die linke Liste ist unsortiert, die rechte wurde sortiert.
Der normale SVERWEIS sucht nun Werte in der ersten Spalte der unsortierten Liste. Da die Werte nicht sortiert sind, muss mit dem Bereich_Verweis-Argument FALSCH gearbeitet werden. Ebenso wird mit INDEX und VERGLEICH (Typ 0) gearbeitet.
Formel für Label aus Tabelle 1:
=SVERWEIS($J3;$A$2:$C$1048576;2;FALSCH)
Rechenzeit: 547,95 ms
Formel aus Label aus Tabelle 2:
=INDEX($A:$C;VERGLEICH($O3;$A:$A;0);2)
Rechenzeit: 543,33 ms
Es resultieren die langsamsten Ergebnisse.
Schon schneller wird der Zugriff auf sortierte Listen.
Formel für Label aus Tabelle 3:
=SVERWEIS($J13;$E$2:$G$1048576;2;FALSCH)
Rechenzeit: 156,30 ms
Formel aus Label aus Tabelle 4:
=INDEX($E:$G;VERGLEICH($O13;$E:$E;0);2)
Rechenzeit: 153,04 ms
Mit einer Prüfung, ob bei SVERWEIS Typ WAHR bei der Suche des Suchkriteriums der gleiche Wert wie das Suchkriterium resultiert, kann dann der SVERWEIS Typ WAHR zur Ermittlung des Wertes aus der Liste gewählt werden. SVERWEIS Typ Bereich_VERWEIS = WAHR ist eine wahre Rakete.
Formel aus Label aus Tabelle 5:
=WENN(SVERWEIS($J23;$E$2:$E$1048576;1)<>$J23;"";SVERWEIS($J23;$E$2:$G$1048576;2))
Rechenzeit: 0,316 ms
Noch schneller erwies sich bei sortierten Listen allerdings die Funktion VERWEIS.
Hier die Formel für Label aus Tabelle 6:
=WENN(VERWEIS($J33;$E$2:$E$1048576)<>$J33;"";VERWEIS($J33;$E$2:$E$1048576;$F$2:$F$1048576))
Rechenzeit: 0,294ms
In dieser Aufstellung können die mit FastExcel gemessenen Geschwindigkeiten verglichen werden.
Und noch ein wenig dynamischer funktioniert alles mit INDEX und VERGLEICH Typ 1:
=WENN(INDEX($E:$E;VERGLEICH($J43;$E:$E;1))<>$J43;"";INDEX($F:$F;VERGLEICH($J43;$E:$E;1)))
Hier gibt es beide Übungsdateien aus dem Video:
http://1drv.ms/1gUnwEV
Hier geht es zum Video:
Videolink: http://youtu.be/lhPizOTt6Fc
Excel 2013 – Office-App People Graph
Über Excel 2013 stehen zusätzlich kleine Anwendungen zur Verfügung, die Office-Apps. Diese Apps können mit den Daten von Excel arbeiten und zum Beispiel kleine Informationsgrafiken bereit stellen.
Die Office-App People Graph von Microsoft erzeugt nette kleine Diagramme, die als Grafik gespeichert oder in andere Anwendungen kopiert werden können.
Hier geht es zum Video:
Videolink: http://youtu.be/tLjuqS0W6AQ
Excel – PowerPivot – Spalten für PivotTable ausblenden
Eventuell werden bei der Erstellung der PivotTable zu viel Spalten angezeigt. Jede Spalte lässt sich so verbergen, dass diese zwar in den Tabellen von PowerPivot, jedoch nicht mehr in der Auswertung der PivotTable zu sehen ist.
Einfach eine Spalte mit rechter Maustaste anwählen und Aus Clienttools ausblenden auswählen.
Hier gibt es die Übungsdatei:
Excel_450_Spalten ausblenden
Hier geht es zum Video
Videolink: http://youtu.be/c8Gm6Bw2Fp8
Excel – PowerPivot – Daten aus dem Web per Power Query
Das kostenlose Add-In Power Query (früher Data Explorer) für Excel 2013/2010 ermöglicht die komfortable Anpassung der zu importierenden Daten, Filterung und auch Abfragen per Internet auf dort verfügbare Listen.
Über Power Query – Online Search kann auf Inhalte von Tabellen im Netz zugegriffen werden.
Anschließend einfach einen Suchbegriff eingeben und anschließend mit der Maus über die Trefferliste fahren, um sich die Treffer links anzeigen zu lassen. Erst mit einem Klick wird die Tabelle ins Tabellenblatt geladen.
Der Import der Daten kann noch über Filter & Shape gesteuert werden. So können Spalten abgewählt, Datentypen verändert oder Zeichenketten ausgetauscht werden. Siehe hierzu die Videoreihe zu Data Explorer.
Mit Load to data model wird die Tabelle in das PowerPivot Data Model überführt. Mit einem Ein-/Aus-Schalter lässt sich auch vermeiden, dass die Tabelle vorab in ein Tabellenblatt eingelesen wird.
Hier ist das Video:
Videolink: http://youtu.be/vqIaeevgkZQ
Excel – PowerPivot – Berechnete Felder – Measures
In PivotTables können berechnete Felder die Berechnungen in Pivot-Tabellen ergänzen. Diese reguläre Option ist allerdings in mit PowerPivot erstellten PivotTables inaktiv. Statt dessen werden berechnete Felder wahlweise im Berechnungsbereich von PowerPivot oder im Register PowerPivot unter Berechnete Felder erstellt.
Diese Measures stehen dann in Tabellen, Diagrammen und in Power View (Excel 2013) zur Verfügung. Ebenso sollten die Daten für Power Maps genutzt werden können (noch nicht getestet).
Hier ist schon mal die Übungsdatei
Excel_439_PowerPivot_4
Und hier geht es zum Video
Videolink: http://youtu.be/TxXV-F-otOQ
Excel – PowerPivot – Einführung – Verknüpfung von Tabellen
PowerPivot ist ein sehr mächtiges Add-in für Excel 2010 und neuer. Allerdings steht aktuell das Add-In nur im einzeln erworbenen Excel und über die Office-Versionen Office Professional Plus und Office 365 ProPlus zur Verfügung. Um PowerPivot nutzen zu können, muss das Register PowerPivot sichtbar sein. Prüfen Sie zur Not über DATEI – Optionen – Add-Ins, ob das COM-Add-In installiert und aktiviert ist.
Bei PowerPivot geht es wie bei Pivot um die Auswertung großer Datenmengen. Daten können abgerufen, konsolidiert und über weiterer Funktionen (DAX-Funktionen in englischer Sprache) bearbeitet werden. SVERWEISE zwischen Tabellen, um Informationen für die Pivot-Auswertung zu Ergänzen sind auch nicht mehr unbedingt erforderlich.
Im Beispiel liegen zwei Datenlisten vor. In der linken stehen Vorgänge mit sechsstelligen Vorgangsnummern, in der rechten befinden sich Projekte. Die beiden ersten Nummern der Vorgangsnummern bezeichnen die Projekte. Wenn ich nun eine Auswertung zu Kostenstellen fahren möchte, müsste ich zunächst bei jedem Vorgang identifizieren, zu welchem Projekt der Vorgang gehört. Diese Arbeiten erledige ich mit PowerPivot.
Zunächst werden beide Datenlisten markiert und als Tabelle formatiert. Wahlweise über das Register START und dort Formatvorlagen – Als Tabelle formatieren oder über die Tastenkombination STRG + T.
Bestätigen Sie den Datenbereich. Ihre Tabellen beinhalten Überschriften, dann bestätigen Sie auch dies. Wählen Sie eventuell noch ein ansprechendes Layout.
Die Tabellen sind nun nicht nur optisch aufgehübscht und mit Filtersymbolen versehen. Die Bereiche können auch benannt und so direkt über Formeln angesprochen werden. Wechseln Sie zur Umbenennung der Tabellen in das Register TABELLENTOOLS – Eigenschaften – ENTWURF, dort können Sie die Tabellen einzeln ganz links umbenennen.
Im Beispiel wähle ich tab_Projektdaten und tab_Kostenstellen. Die Namen der Tabellen tauchen dann auch unter FORMELN – Definierte Namen – Namens-Manager auf.
Klicken Sie auf eine der Tabellen. Wechseln Sie nun auf das Register POWERPIVOT – Tabellen und wählen Zu Datenmodell hinzufügen. Wechseln Sie von der PowerPivot-Ansicht auf Excel zurück und führen Sie diesen Schritt für die zweite Tabelle aus.
Es befinden sich nun beide Excel-Tabellen im PowerPivot Data Model. Hier werden die Daten sehr effizient gespeichert. Die Verbindung zwischen den Tabellen ist noch nicht geschaltet.
Ich formatiere zunächst die Zahlen noch so, wie ich sie später in der Auswertung sehen möchte.
Wählen Sie nun Spalte hinzufügen an und wechseln anschließend in die Bearbeitungszeile. Formeln werden hier immer für alle Spalten angegeben. Einige DAX-Funktionen sind entsprechend der normalen Excel-Funktionen zu nutzen. Klammern müssen korrekt geschlossen werden.
Die DAX-Funktion =LEFT([Vorgang];2) gibt die beiden linken Zeichen der Spalte Vorgang zurück.
Diese neue Spalte sollte noch umbenannt werden. CalculatedColumn1 ist ja eher ein Zustand, als ein vernünftiger Name. Klicken Sie wahlweise doppelt auf den Spaltennamen oder mit rechter Maustaste auf den Spaltennamen und dann auf Spalte umbenennen.
Zuletzt müssen die beiden Tabellen noch verknüpft werden. Wechseln Sie dazu auf das Register Entwurf und dann klicken Sie auf die Schaltfläche Beziehung erstellen.
Wählen Sie den Schlüssel aus, über den die beiden Tabellen korrespondieren. Wichtig an dieser Stelle: In einer Tabelle gibt es die Informationen nur einmal, in der anderen mehrfach (n) – hier wird also eine 1:n-Beziehung wie in Access oder anderen Datenbanken erstellt.
Erzeugen Sie nun die PivotTable. Wechseln Sie zurück auf das Register Home und dort auf PivotTable.
Wählen Sie den Ort aus, an dem die PivotTable platziert werden soll. Ich würde fast immer ein Tabellenblatt pro PivotTable bevorzugen. Es sei, Sie wollen mehrere Berichte und Diagramme auf ein Blatt bringen (Dashboard).
Sie können die Felder aus beiden Tabellen zur Erstellung der PivotTable nutzen. Ziehen Sie die Feldnamen der einzelnen Tabellen auf zum Beispiel Zeilen oder Spalten bzw. zur Auswertung in die Werte.
Hier geht es zur Übungsdatei:
Excel_436_PowerPivot_1
Und hier ist Video # 1
Videolink: http://youtu.be/E8uNxCchFR8
Du muss angemeldet sein, um einen Kommentar zu veröffentlichen.