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.

AES2015.png

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.

  1. Lernt nicht allein, sucht euch in der Firma einen Sparringspartner
  2. Tauscht euch mit anderen aus, egal ob in Foren oder live
  3. Schaut in nützliche Blogs und YouTube-Kanäle
  4. Experimentiert mit Power Query
  5. Arbeitet das Buch von Ken durch
  6. 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.

537a

Anschließend auf das Symbol bei Content klicken, um statt der Dateiinformationen die Dateiinhalte auszulesen.

537b

Sollten irrtümlich noch Überschriften mit importiert werden, können diese herausgefiltert werden.

537c

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.

533a

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.

533b

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.

533c

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.

533d

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.

533e

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.

533x

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

Aktuelle Informationen auch auf https://www.facebook.com/AThehos

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

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.

448a

Über Power Query – Online Search kann auf Inhalte von Tabellen im Netz zugegriffen werden.

448b

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.

448c

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.

448d

Hier ist das Video:

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

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.

436a

Bestätigen Sie den Datenbereich. Ihre Tabellen beinhalten Überschriften, dann bestätigen Sie auch dies. Wählen Sie eventuell noch ein ansprechendes Layout.

436b

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.

436c

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.

436d

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.

436f

Ich formatiere zunächst die Zahlen noch so, wie ich sie später in der Auswertung sehen möchte.

436g

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.

436h

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.

437k

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.

436l

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.

436m

Erzeugen Sie nun die PivotTable. Wechseln Sie zurück auf das Register Home und dort auf PivotTable.

436q

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).

436r

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.

437s

Hier geht es zur Übungsdatei:
Excel_436_PowerPivot_1

Und hier ist Video # 1

Excel – Stabdiagramm für Häufigkeitsverteilung – Liniendiagramm

Ein Stabdiagramm kann aus einem Liniendiagramm heraus erzeugt werden. Dafür können von jedem Datenpunkt der Linie heraus Verbindungslinien zur X-Achse gesetzt werden.

So soll das Diagramm nachher aussehen.

433

Erzeugen Sie zunächst ein Liniendiagramm.
Erstellen Sie anschließend die Bezugslinien wie im Bild angezeigt. Bei Excel 2010 befindet sich dieser Menüpunkt ganz rechts im zweiten Register der Diagrammtools.

E433a

Entfernen Sie zuletzt die Farbe der Linie. Es verbleiben die Stäbe.

433b

Die Übungsdatei:
Excel_433_Stabdiagramm

Hier geht es zum Video:

Excel – gruppiertes und gestapeltes Säulendiagramm

Aus den vorliegenden Daten soll ein gruppiertes und zugleich gestapeltes Säulendiagramm erzeugt werden.
Zusätzlich sind die Werte pro Säule oben aufzustellen und die Jahreszahl unter den Gruppen vernünftig anzuordnen.

E430_a

So sollen die Daten grafisch umgesetzt werden…

E430

Die Daten werden dafür anders organisiert, zudem gibt es drei Hilfsspalten für die Beschriftung der Säulen und der X-Achse.

E430b

Die Spalten G und I werden genutzt, um die Beschriftungen über den XY-Chart Labeler einzubringen. Spalte G ist für die Jahreszahlen aus Spalte A in den Gruppen der X-Achse verantwortlich. Ich hatte in einer vorherigen Version die Jahreszahlen mit Spalte F (Werte Europa) verbunden. Allerdings hatten sich die Labels verschoben, sobald sich die Werte änderten. Spalte G sollte solide auf Null bleiben.
Die Nullwerte in Spalte I werden genutzt, um die Beschriftung aus Spalte H einzufügen. Ändern sich nun die Werte der Europasäule oder der vier Länder, verschieben sich die Labels automatisch mit.

Die fertige Datei zum Download:
E430_Gestapelt_und_Gegenüber

Hier geht es zum YouTube-Video XY Chart Labeler http://youtu.be/rNKzwWTkN-c
Rob Bovey passt es seit Jahren an aktuelle Office-Versionen an. Es gibt auch eine Version für den Mac. Ich nutzte es selbst für die Versionen 2003, 2007, 2010 und 2013.

Hier geht es zum Video Excel # 430: