Getagged: Excel 2013

2016-12-03

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

2014-05-27

Excel 2013 – Großschreibung in Registernamen ändern

Warum eigentlich werden in Office 2013 die Registernamen groß geschrieben? START statt Start, EINFÜGEN statt Einfügen.
Mit einem kleinen Trick im Namen des Registers kann dies korrigiert werden.

Ziel:

Um die Registernamen ändern zu können mit rechter Maustaste auf das Menüband klicken, anschließend Menüband anpassen.

Oder auf Datei – Optionen – Menüband anpassen.
Dort auf der rechten Seite den entsprechenden Eintrag auswählen und auf Umbenennen klicken.
Im anschließenden Eingabefeld ein Leerzeichen vor den Registernamen setzen. Aus “Start” wird dann ” Start”.

Hier geht es zum Video:
Videolink: http://youtu.be/b-q1cKhIBNg

2013-05-23

Excel 2013 – PowerView – Daten auf Karte darstellen

Sobald mir Daten mit geografischen Informationen vorliegen, können diese nicht nur mit Pivot oder PowerPivot ausgewertet werden. Diese geografischen Informationen können auch mit PowerView in Form von Kreisdiagrammen und der Hilfe von BING-Karten visualiert werden.

Über die Angabe von Städtenamen, Bundesländernamen oder Ländern sucht PowerView passende Orte und platziert Mini-Diagramme direkt dort auf der Karte. Es können auch Daten über die Angabe von Längen- und Breiteninformationen platziert werden.

In der folgenden Liste (noch keine Tabelle) befinden sich drei Städtenamen in Spalte B. Sollte diese Tabelle weder als Tabelle formatiert worden sein, noch ins PowerPivot Data Modell übernommen worden sein, kann man bei einfachen Listen auch einfach in die Liste klicken (1) und dann über Einfügen (2) auf PowerView klicken. Hinweis: Ich würde es vorab bevorzugen, wenn die Daten schon vorher als Tabelle formatiert (z.B. mit STRG + T) und über das Register PowerPivot ins Datenmodell übernommen werden.

Es öffnet sich das Fenster PowerView. Die Daten werden als Tabelle dargestellt. Die Spalten befinden sich in der Sortierung der Spaltenüberschriften. Zunächst werden alle Daten übernommen. Ein Vorteil gegenüber der ursprünglichen Liste ist noch nicht zu erkennen.

Die Liste wird dann ins PowerPivot Data Model übernommen, was man durch einen Klick auf PowerPivot – Verwalten prüfen kann.

Die Auswahl der Einträge sollte reduziert werden. Zudem lässt sich die Anordnung der Felder durch Verschieben ändern. Im Register Entwurf steht die Schaltfläche Karte zur Verfügung.

Die ausgewählten Daten werden im Kartenausschnitt dargestellt.

Link zum YouTube-Video:
Videolink: http://youtu.be/UKRr1ubgbHo

2013-05-17

Excel – Data Explorer Preview – CSV importieren und matchen

Mehrere csv-Dateien (comma separated value-files) werden monatlich aktualisiert und müssen anschließend gemeinsam ausgewertet werden. Das kostenlose Add-In “Data Explorer” Preview bietet die Möglichkeit, csv-Dateien zu importieren. Diese über den Data Explorer importierten Dateien können dann anschließend kombiniert werden.

Vorab ist aber beim Import der csv-Datei zu beachten, dass die Felder mit identischen Datentypen und der gleichen Spaltenzahl importiert werden.

Spätere Aktualisierungen der csv-Dateien führen über den Schalter Refresh zum erneuten Import und der Aktualisierung der kompletten Tabelle.

Hier bekommt man einen kleinen Einblick in den Aufbau der csv-Dateien. Die Werte sind jeweils mit einem Semikolon von einander getrennt.

Im Register Data Explorer findet man unter From File den Punkt From CSV.

Anschließend wählt man in einem Datei-Explorer die Datei aus (ohne Bild) und klickt auf Öffnen. Der Import würde hier in nur eine Spalte erfolgen.

Mit einem rechten Mausklick auf Column1 kann man einstellen, dass es ein Trennzeichen (Split) gibt. Parallel kann man zudem mitverfolgen, wie die einzelnen Schritte des Imports (Steps) aufgebaut werden. Wenn man rechts auf einen Step klickt, wird in der oberen Befehlszeile der Befehl inklusive Parametern angezeigt.

Hier soll bei einem Trennzeichen (Delimiter) in mehrere Spalten getrennt werden. Ich wähle das Semikolon als Trennzeichen.

Anschließend klicke ich mit rechts auf den Namen der Abfrage und dann auf Rename. Unter dem Namen dieser Abfrage identifiziere ich später die Tabelle.

Der Datentyp des Datumsfeldes wird noch auf Date verändert.

Anschließend importiere ich die erste csv-Datei mit dem Schalter Done. Für die zweite Tabelle gehe ich exakt genau nach diesem Muster vor, wähle aber dabei natürlich einen anderen Query-Namen.

Wenn beide Tabellen geladen sind, kann ich im Register Data Explorer oder im Register Query auf Append klicken.

Die beiden Tabellen stehen zur Auswahl.

Ich kann die Combine-Abfrage noch umbenennen.

Sollten später neuere csv-Dateien unter gleichem Namen im Zielverzeichnis liegen, kann ich über einen Klick auf den Schalter Refresh die Daten erneut einladen.

Zum YouTube-Video:
Videolink: http://youtu.be/SmMlU92j3Do

2013-05-15

Excel 2013 – PowerView – Visualisierung von Daten

Ein im Zeitverlauf animiertes Blasendiagramm ist in wenigen Augenblicken erzeugt. Die Daten liegen im Data Model oder über intelligente Tabellen vor? Excel 2013 ist installiert? Perfekt… Im Register Einfügen finden wir die kleine Gruppe Bericht, dort steht einsam und allein PowerView.

Ein animiertes Diagramm in weniger als 60 Sekunden!

Warum nicht? Excel mausert sich immer mehr zu einem Tool, große Datenbestände anzuzapfen und auszuwerten. Die Datenhaltung würde ich natürlich einer Datenbank überlassen.

Das Handling innerhalb von PowerView erstreckt sich über zwei Register. PowerView selbst und daneben das Register Entwurf. Meine ersten Versuche mit PowerView waren gescheitert, da ich zum einen meine Daten nicht als Intelligente Tabelle (Strg + T) formatiert hatte, und später dann auch nicht auf Entwurf geklickt hatte.

Im Video sind meine (fast) ersten Gehversuche mit PowerView dargestellt. Und wenn das meine ersten Versuche sind… was wartet noch auf mich, wenn ich PowerView genauer kennengelernt habe?

Übungsdatei zum Download:
E369_PowerView

Video bei YouTube:
Videolink: http://youtu.be/k3YEeVHgTQc

2013-05-06

Excel 2013 – Tabellenblätter vergleichen – identische Formeln

Ausgehend von einem Master-Tabellenblatt werden jeden Monat Kopien dieses Tabellenblatts erstellt und mit Werten gefüllt. Die Tabellenblätter werden sich im Verlauf von einander unterscheiden. Doch muss auch darauf geachtet werden, dass die Formeln auf allen Blättern identisch sind. Es darf keine Formel verändert werden und nicht durch die Eingabe eines Zahlenwerts überschrieben werden.

Normalerweise würde man solche Tabellenblätter schützen. Zellschutz für alle zu beschreibenden Zellen deaktivieren und dann einen kompletten Blattschutz, der gegen weitere Veränderungen der Blatt- und Formelstruktur schützt. Doch was, wenn die Blätter nicht geschützt werden (können)? Oder man sich die Blätter aus mehreren Arbeitsmappen zusammenkopieren muss?

Folgende Formel kopiere ich in den Datenbereich A1:D9 eines Tabellenblattes. Hier stellvertretend die Formel für Zelle A1:

=WENN(ISTFORMEL(Original!A1);WENN(WENNNV(FORMELTEXT(Original!A1)=FORMELTEXT(INDIREKT($G$1&"!Z"&ZEILE()&"S"&SPALTE();FALSCH));FALSCH);"";"Z"&ZEILE()&"S"&SPALTE());"")

Die Formel prüft, ob in Zelle D1 des Tabellenblatts Original eine Formel steht. Falls ja, wird dieser Formeltext mit dem Text der entsprechenden Zelle eines anderen Blatts verglichen. Das Tabellenblatt soll aber variabel bleiben und wird hier in Zelle G18 z.B. über Datenüberprüfung angeboten.

Da entweder eine Formel im anderen Blatt steht, oder eine Zahl, habe ich WENNNV gewählt. Die Funktion FORMELTEXT für eine Zelle mit reinem Zahleninhalt ergibt #NV. Im Falle, dass beim Vergleich mit einer falschen Formel oder einem Zahlenwert verglichen wird, wird die Koordinate dieser Zelle in R1C1-Schreibweise angeboten.
Sollte z.B. die Zelle D5 abweichen, liefert "Z"&ZEILE()&"S"&SPALTE()) das Ergebnis Z5S4.

Datei zum Download:
Blattvergleich

Zum YouTube-Video:
Videolink: http://youtu.be/nlQpgUaLz3M

2013-05-05

Excel 2013 – FORMELTEXT, ISTFORMEL und WENNNV

Identifizieren, ob eine Zelle eine Formel enthält und wie diese lautet. Zwei in Excel 2013 eingeführte Funktionen unterstützen Sie dabei.

=FORMELTEXT(Zellbezug) =ISTFORMEL(Zellbezug)

Mit der Funktion =FORMELTEXT(A1) lässt sich die Formel der Zelle A1 anzeigen.
Steht dort allerdings keine Formel, zeigt das Formelergebnis #NV an.

Mit der Funktion =ISTFORMEL(A1) kann eine Zelle dahin gehend überprüft werden, ob überhaupt eine Formel in der Zelle A1 ist. Das Ergebnis lautet hier WAHR oder FALSCH.

Folglich kann mit =WENN(ISTFORMEL(A1);FORMELTEXT(A1);A1) geprüft werden, ob sich eine Formel in einer Zelle befindet. Falls WAHR, wird diese angezeigt, Falls FALSCH, wird der Zellwert angezeigt.

Statt =WENN(ISTFORMEL(A1);FORMELTEXT(A1);A1) kann aber auch die ebenfalls neue Funktion =WENNNV(FORMELTEXT(A1);A1) genutzt werden. Ähnlich der mit Excel 2010 eingeführten Funktion WENNFEHLER wird bei WENNNV geprüft, ob bei einer Formel oder Funktion der Wer #NV herauskommt. Falls nicht, wird einfach die Funktion ausgeführt. Falls doch, wird das Alternativergebnis ausgeführt.
Die Funktionen lassen sich auch dafür nutzen, Zellen mit Formeln optisch über die Bedingte Formatierung hervorzuheben. Zum Beispiel können Sie die Stellen hervorheben, wo Formeln stehen sollten aber nun manuelle Eingaben zu finden sind.

Die Funktion =ISTFORMEL(B2)=FALSCH liefert das Ergebnis FALSCH, falls sich eine Formel in Zelle B2 befindet, da hier das Ergebnis des Vergleichs gilt. Ist eine Formel in Zelle B2, kommt bei =ISTFORMEL(B2) das Ergebnis WAHR heraus und letztendlich bei =ISTFORMEL(B2)=FALSCH ein =WAHR=FALSCH, wodurch hier ein FALSCH resultiert. Entsprechend bei einer Formel in Zelle B2 ein WAHR.

Das WAHR als Formelergebnis ist bei der Bedingten Formatierung notwendig, um die Formatierung auszulösen.

Vorab wurde der Bereich B2:B5 markiert und über das Register START in der Gruppe FORMATVORLAGEN der Schalter BEDINGTE FORMATIERUNG betätigt. Wählen Sie hier Neue Regel und anschließend den untersten Eintrag Formel zur Ermittlung der zu formatierenden Zellen verwenden. B2 ist bei der Markierung die hervorgehobene Zelle, in der auch Zelleingaben stattfinden würden. Die Zelle B2 wird stellvertretend für die drei anderen Zellen formatiert. Die jeweiligen Zellen beziehen die Formel entsprechen auf sich. Zum Beispiel =ISTFORMEL(B5)=FALSCH in Zelle B5.

Die Abbildung zeigt durch die Hervorhebung, dass in Zelle B3 keine Formel vorliegt.

Spannender wird es, wenn es mehrere Tabellenblätter gibt und dort auf die Zelle genau überprüft werden soll, ob in den einzelnen Zellen der jeweiligen Blätter dieselben Formeln vorliegen, wobei die manuellen Eingaben von Werten abweichen dürfen. Also =FORMELTEXT(Blatt1!A1)=FORMELTEXT(BLATT2!A1) bzw. =FORMELTEXT(A1)=FORMELTEXT(BLATT2!A1), wenn sich der Vergleich auf Blatt1 befindet.

Im nächsten Beitrag werde ich mehrere Monatsblätter mit einem Referenzblatt vergleichen…

Bei früheren Excel-Versionen kann man über =ZELLE.ZUORDNEN(48;Zellbezug) ermitteln, ob sich eine Formel in der Zelle befindet. Mehr dazu in Excel # 244 bei YouTube.

Hier gibt es das YouTube-Video:
Videolink: http://youtu.be/tnQ4DHkUjqM

2013-04-27

Excel 2013 – Mehrere Intelligente Tabellen über Pivot auswerten

Sobald die Daten nicht in einer Liste, sondern über mehrere Listen verteilt sind, müssen diese Tabellen verknüpft werden. Für Microsoft Access oder Microsoft PowerPivot kein Problem, dort können Tabellen schon immer auf einfachste Weise mit einander verknüpft werden.

Nun lassen sich Listen auch ohne vorherige Verknüpfung oder umständliche SVERWEISe in einer gemeinsamen PivotTable auswerten. Vorab müssen die Listen in Intelligente Tabellen umgewandelt werden. Der Eintrag steht über das Register START in zur Verfügung. Optional kann man auch STRG + T drücken. Bei der anschließenden Erstellung der PivotTable in Excel 2013 ist auf den Haken zu achten, der die Tabelle in das Datenmodell überführt. Sollte der Haken nicht gesetzt werden, lassen sich aber auch noch nachträglich weitere Tabellen ergänzen – dies führt allerdings zu einer neuen PivotTable.

Die so neu geschaffenen Konstrukte sind leider nicht mit älteren Excel-Versionen kompatibel. Ein Blick in Datei – Informationen – Auf Probleme überprüfen – Kompatibilität prüfen zeigt folgendes Bild.

Hier geht es zum Video

Videolink: https://youtu.be/utUR1rXnvlY