Kategorie: YouTube
Power Query und Pivot-Tabelle mit Datenmodell
Ein weiterer Workshop! Doch dieses mal werden wir mit Hilfe von Power Query Daten ins Excel importieren und von dort aus mit dem Datenmodell weiterarbeiten. Mit Hilfe von DAX-Funktionen werden Measures gebildet, die dann in der Pivot-Auswertung zur Verfügung stehen.
Übungsdateien des Videos:
Zunächst wieder die reinen Verkaufsdaten in einer Liste auf einem Tabellenblatt:
Anschließend eine Datei mit einer Kalendertabelle auf einem Tabellenblatt und zwei Tabellen auf dem zweiten Tabellenblatt:
Die folgenden Steuerdaten werden zusätzlich benötigt. Hier greife ich per Webanfrage auf die Tabelle zu. Dafür muss der Link zu diesem Blog-Beitrag kopiert werden:
https://thehosblog.com/2022/03/04/power-query-und-pivot-tabelle-mit-datenmodell/
| Land | Mehrwertsteuer |
| Deutschland | 19% |
| Niederlande | 21% |
| Österreich | 20% |
| Schweiz | 7,7% |
Die über Power Query abgefragten Tabellen werden im Datenmodell oder schon vorab in Power Query miteinander verknüpft. Informationen, die später in Pivot nicht verfügbar sein sollen, werden vorab im Datenmodell ausgeblendet. So benötigt man nicht den Umsatz der ursprünglichen Tabelle, sondern das mit Hilfe der DAX-Funktion berechnete Measure. Auch die Filialen tauchen tausendfach in den Sales-Daten auf. Hier benötige ich lediglich das in der Filialtabelle enthaltene Feld für spätere Filterzwecke.
Mit den ins Datenmodell importierten Daten können nun nach der Erstellung von DAX-Funktionen Auswertungen gefahren werden.
Das Tabellenblatt kann nun kopiert und in Kopie weiter modifiziert werden.
Hier geht es zum YouTube-Video… folgt – wird neu erstellt. Danke an den Hinweis zum Fehler im Video an Olaf!
Videolink: folgt
Pivot-Tabellen und Power Query – Neue Reihe
Du möchtest aus Deinen Daten eine für Deine Zwecke aussagekräftige Auswertung erzeugen? Du musst Daten aus diversen Datenquellen zusammenfügen und die Informationen verdichten?
Dann sind Power Query und Pivot-Tabellen genau die richtigen Werkzeuge für Dich!
Einen kurzen Blick in die neue Reihe findest Du im folgenden Intro-Video:
Videolink: https://youtu.be/sE94pP1KODQ
Doch bevor ich auf die modernen Pivot-Tabellen mit Datenmodell eingehe, möchte ich mit Dir eine klassische Pivot-Tabelle aufbauen. Im folgenden Bild erkennst Du das Endergebnis des Videos.
Und so sahen die Ausgangsdaten aus Datei 1 aus.
Die Technik wird auch noch für Online- und Mac-Nutzer benötigt. Für einige genutzte Funktionen benötigst Du Excel 2019 oder neuer (z. B. die Funktion TEXTVERKETTEN). Die anderen Techniken funktionieren aber schon bei älteren Excel-Versionen.
Die Übungs- und Ergebnisdateien für das folgende YouTube-Video kannst Du hier herunterladen:
Ausgangsdatei für das erste Trainingsvideo:
Ergebnisdatei:
An diesen Pivot-Tabellen – die tolle und mächtige Ergebnisse liefern – gibt es aber einige Schwachstellen, welche ich dann in späteren Videos mit Hilfe der Pivot-Tabellen mit Datenmodell und den DAX-Funktionen (data analysis expressions) beheben werde.
Wenn Du mich auf dieser Reise durch Power Query und Pivot begleiten magst, würde ich Dich bitten, die Videos zu kommentieren, zu bewerten und den Kanal zu abonnieren. Für mich der einzige Ansporn, weiter kostenlosen Content bei YouTube hochzuladen.
Videolink: https://youtu.be/MTcCcrQ3rM0
Ich freue mich auf eine spannende Reise
Andreas
P.S.: Die Reihe steht Dir kostenlos zur Verfügung, Du kannst die Videos auch gerne ins Unternehmen verlinken. Die Übungsdateien dürfen in unveränderter Form unter Nennung der Quelle und des Autors kommerziell genutzt werden.
Umfragen in der YouTube-Community
Hallo,
einige haben schon mitbekommen, dass bei YouTube auf Umfragen im Community-Bereich hingewiesen wird.
Ich möchte mehr über meine Zielgruppe (also auch über Dich) erfahren, um den Kanal noch besser anzupassen bzw. erst einmal auch zu überblicken, wer sich aktiv am Kanalgeschehen beteiligt.
Klicke dazu in meinem Kanal auf den Eintrag COMMUNITY oder folge folgendem Link:
https://www.youtube.com/user/AThehos/community
Dort kannst Du zum einen Fragen beantworten und kommentieren, erfährst aber auch Kleinigkeiten zum Kanalgeschehen.
Nach Beantwortung erhältst Du eine Übersicht, wie die anderen Kanalnutzer geantwortet haben.
Nach ca. ein paar Wochen möchte ich dann Stellung zur Frage und den Antworten geben.
Hier geht’s zum Video:
Videolink: https://youtu.be/seCS–pgeYI
Merci für Deine aktive Teilnahme!
Ich wünsche Dir eine frohe Weihnachtszeit.
Beste Grüße
Andreas
Das Beste kommt zum Schluss!
Ein verrücktes Jahr mit vielen kleineren und größeren Katastrophen, Highlights und spannenden Entwicklungen geht zu Ende. Zeit für ein kleines Resumee.
In 2016 habe ich bislang 99 Videos online gestellt – davon 30 auf meinem neuen Kanal www.youtube.com/atExcel. Das sind knapp 2 Titel pro Woche, wobei wir ja noch ein paar Tage bis Silvester übrig haben. In der Zeit meiner Umzüge und der ganzen Renovierungen(privat und beruflich) hatte ich keine Videos erstellt, so dass sich im Verlauf des Sommers eine schöne „Delle“ im Zugriffsverlauf erkennen lässt. Von knapp 410.000 Zugriffen pro Monat zu Jahresbeginn bin ich aktuell wieder bei knapp 385.000 pro Monat angelangt.
Neu dabei waren einige Videos, die mal nicht Excel mit meiner Stimme im Off zeigten, sondern in denen ich ein paar Statements abgegeben habe. Kleine Smartphone-Videos mit miserablem Ton. Diese Videos wurden von den meisten Zuschauern ganz gut angenommen, so dass ich damit in 2017 weiter machen möchte. Ich denke, auf Premiere-Effekte kann ich dabei verzichten, oder? Insgesamt war es aber bislang das erfolgreichste Jahr meiner bislang 6,5jährigen YouTube-Laufbahn.
Als ewiges Highlight in meinen Videos ist mal wie der der Word-Klassiker „Inhaltsverzeichnis“ mit insgesamt 202.000 Zugriffen im aktuellen Jahr dabei. Vielleicht sollte ich das Produkt wechseln und in Zukunft Word-Videos erstellen! Obwohl… dann lieber OneNote.
Insgesamt freue ich mich natürlich über fast 22.000 Abonnenten und die vielen treuen Stamm-Kommentatoren. Auch hier hat sich wieder gezeigt, dass es sich lohnt, sein Wissen gratis online zu stellen. Ich habe wieder so viele Tipps erhalten, wie man etwas anders und auch besser machen kann.
Mein USB-Stick Excel 700+ hat die 64 GB-Marke gesprengt. Ich stelle den Vertrieb für Privatpersonen erst einmal zum Jahresende ein, bis ich eine kosten- und zeitsparendere Lösung gefunden habe. Vielen Dank an alle, die sich auch über diesen Weg fortbilden.
Und vor allem der Austausch bei den Excel-Stammtischen war großartig. Ich habe in diesem Jahr in Wien, Köln, München, Münster und Frankfurt am Main teilgenommen.
Mein großes Highlight war aber der dritte Amsterdam Excel Summit, bei dem sich viele Excel MVPs im Vorfeld ihres offiziellen Treffens ausgetauscht haben. Auch wegen des Netzwerks lohnt sich die Teilnahme. In 2017 bin ich sicher wieder dabei http://topexcelclass.com/index.php/amsterdam-excel-summit/. Danke an Tony de Jonker und Jan Karel Pieterse für dieses erstklassige Event und das wirklich phantastische Abendprogramm.
Mein Dank für 2016 gilt zudem Johannes Curio für die Organisation des Excel-Stammtischs in Basel und Dr. René Martin für die regelmäßige Ausrichtung in München. Prof. Dr. Johannes Schwanitz und sein Team von der FH Münster haben zwei tolle Treffen in Münster organisiert.
Natürlich allen Lesern des Blogs. Hey… Ihr dürft gerne auch ein Feedback in den Kommentaren hinterlassen. Danke!
Für 2017 habe ich zwei ganz ehrgeizige Ziele:
- Ich will die Schulen, Berufsschulen und Unis erreichen! Ich habe schon viel Feedback von Schülern und Studenten erhalten, die meine Videos im Unterricht gucken „mussten“. Wenn die Videos über YouTube für den Unterricht genutzt werden, dann möchte ich gerne noch ein paar Inhalte dafür besteuern.Warum Excel für die Ausbildung? Weil dort noch viel geleistet werden kann. Ich bin stolz über den erreichten Anteil und das positive Feedback. Aus meiner Erfahrung bei meinen Kunden weiß ich, dass diese Kenntnisse benötigt werden.
- Ich möchte auf YouTube 100.000 Abonnenten erreichen. Wenn nur einige von euch die Videos im Unternehmen oder an der Uni weiter empfehlen – vielleicht über einen firmeninternen Newsletter oder als Tipp zur Einsparung von Fortbildungskosten an die Personalentwicklung, dann wäre ich dort schon einen großen Schritt weiter.Warum gerade 100.000? Ich stehe natürlich auch mit YouTube direkt in Kontakt. Als YouTube-Partner erhalte ich Tipps und direkte Empfehlungen von meiner Partner-Managerin aus London. Die haben einen tolles Team bei YouTube, da wäre ich gerne mehr involviert.
Von daher meine Zielsetzung, ich mache kräftig mit den YouTube-Kanälen und diesem Blog weiter. Wenn es gut war, dann mehr davon! Und zudem möchte ich die Vernetzung von uns Excel-Anwendern voran treiben. Es gibt noch einige weiße Stammtisch-Flecken auf der Landkarte…
Ich wünsche euch allen ein ganz tolles Weihnachtsfest und ein erfolgreiches, glückliches und gesundes Neues Jahr.
Träumt nicht von Excel!
Euer Andreas
P.S.: Fast vergessen. Dieser Blog wurde in 2016 bislang von 133.480 unterschiedlichen Besuchern aufgesucht. Da ich in diesem Jahr noch nicht so viele Artikel veröffentlicht habe, ein schöner Erfolg. Danke!
Excel-Aufgabe – Säulendiagramm auch in der Breite aufbauen
Eine kleine Bastelaufgabe…
Ein Säulendiagramm ist ja schnell erstellt, baut sich aber gerade nach oben auf. Wie sieht es damit aus, wenn man eine Säulenbreite von 5 annimmt und sich das Diagramm erst pro 5 in der Breite aufbauen und dann weiter stapeln soll?
So sieht es noch wie ein normales Säulendiagramm aus.
Teilt man die Zahl durch 5 und bleibt ein Rest, sieht man die oberen Säulenkanten.
Ich habe eine Lösung. Wie sieht euer Ansatz aus?
Hier wird die Aufgabe beschrieben:
Video bei YouTube: http://youtu.be/r6QQSl2J8Vs
Viel Spaß beim Tüfteln… in der Facebook-Gruppe Excel-FAQ ist schon eine tolle Lösung mit einem Balkendiagramm eingegangen.
Bitte probiert zunächst eine eigene Lösung aus. Auch wenn man nicht ganz zum gewünschten Ergebnis kommt, lernt man dabei eine Menge über Excel.
Hier ist meine schludrige Lösung 😉
Videolink zu YouTube: http://youtu.be/AY368IstZIY
Diese schicke Lösung von Josef kommt aus der Facebook-Gruppe Excel-FAQ
Videolink zu YouTube: http://youtu.be/0g3FMaxn_rM
Aktueller Stand und Zukunft…
Zur Sperrung des YouTube-Kanals und Deaktivierung der thehosblog.com-Beiträge – Positives Fazit nach ca. 15 Tagen:
– Erfreuliches Feedback zum Kanal (konnte ich leider nicht komplett beantworten)
– Starker Rückgang der Anfragen zu Gratis-Hilfestellung
– vermehrt geschäftliche Anfragen
– vermehrt Kooperationsanfragen
Komplette Entwicklung des Videokanals in den letzten 50 Monaten:
Entwicklung 2014. Leider war der Effekt bei „nicht gelistet“ nicht wie erwünscht, da sämtliche Links über die Listen und Foren noch funktionierten.
Das Thema Video-Tutorials ist noch nicht abgehakt. Ich informiere mich gerade zu den Bereichen Crowdfunding, E-Commerce, FTP und rechne mit spitzem Bleistift durch…
Office Mix – Interaktive Präsentationen
Office Mix von der Webseite http://mix.office.com steht aktuell als Customer Preview für PowerPoint 2013, Office 2013 und Office 365 zur Verfügung.
Auf der Webseite können auch direkt die technischen Mindestvoraussetzungen eingesehen werden. Die Installation setzt allerdings voraus, dass man sich über ein Microsoft-, Facebook-, Google- oder Unternehmenskonto anmeldet. Es werden nämlich neben dem Add-In noch Dienste online zur Verfügung gestellt. Für diese online-Zugriffe ist eine Authentifizierung erforderlich.
Nach der Installation des Add-Ins steht ein neues Register namens „Mix“ in PowerPoint zur Verfügung.
Die eigentliche PowerPoint-Präsentation kann über dieses Register mit weiteren Objekten wie Film, Ton, Screen Capture, Screen Recording, Umfragen und aufgezeichnetem Whiteboard angereichert werden.
Über die Office-Apps kommen einzelne Umfrageelemente auf jeweils ein Präsentationsblatt. Diese Apps werden dann auch bei mix.office.com ausgewertet.
Zu Testzwecken kann man dann vor dem Upload die Preview starten.
Beim Upload selbst kann zusätzlich noch ein Video für offline-Zwecke erzeugt werden.
Auf der Seite von mix.office.com lassen sich nun die eigenen Präsentationen freigeben, auswerten und löschen.
Unter Manage befinden sich die eigenen Präsentationen.
Mit Analytics kann genau geschaut werden, wer wie lange auf welche Folie geschaut hat. Ebenso lassen sich die Antworten pro Folie und Umfragefeld auswerten. An dieser Stelle sollten auch Datenschutzrechtliche Aspekte berücksichtigt werden.
Eine frei zugängliche Präsentation:
https://mix.office.com/watch/GQRAXHNHHLD9
Hier geht es zum Video:
Videolink: http://youtu.be/zQgTaEsU8Eg
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
Aktuelle Informationen auch auf https://www.facebook.com/AThehos
Excel – Power Query – Monatsanfang und Monatsende
Die Funktionen Date.StartOfMonth und Date.EndOfMonth ermitteln passend zu einer Datumsspalte den jeweiligen Monatsanfang und das Monatsende.
Wichtig bei diesen Funktionen ist die korrekte Groß-/Kleinschreibung.
Befinden sich in der Spalte namens „Datum“ z.B. Verkaufsdaten, kann mit Date.EndOfMonth([Datum]) der jeweilige Monatsende errechnet werden.
Natürlich stehen mir Monatsanfang und Monatsende auch über eine vernünftige Kalendertabelle in PowerPivot zur Verfügung. Aber wer weiß schon, wofür diese Spalten benötigt werden können?
Hier gibt es die Übungsdatei:
Excel Power Query 2-1
Hier geht es zum Video:
Videolink: http://youtu.be/nXVa5O7Y9oI
Excel – Umwandlung von Eingaben zu korrekten Zahlenwerten
Nach der Eingabe von 900 soll die Stundenzahl 9:00 resultieren. Das Datum soll durchgehend ohne Punkte wie bei 050414 eingeben werden und der 05.04.2014 soll resultieren.
Die Funktion TEXT formatiert einen Zahlenwert zu Text. Die /-Zeichen sorgen dafür, dass zusätzlich Sonderzeichen eingefügt werden. Aus 1234 würde mit TEXT(1234;“00/:00″) der Textwert „12:34“. Um den Text zurück in eine Zahl zu verwandeln, kann das Ergebnis mit 1 multipliziert oder mit einem Doppelminus — in eine Zahl gewandelt werden.
Die Funktion WECHSELN wurde zusätzlich eingefügt, um führende Nullen zu entfernen. Nachfolgende Nullwerte werden ignoriert.
Hier gibt es eine Demo-Datei:
Excel 525 – Umwandlung mit Text
Bitte beachten:
Ich biete keinen kostenlosen Excel-Support per E-Mail oder Anruf.
Geschäftliche Anfragen richten Sie bitte per E-Mail an thehos@at-training.de – Vielen Dank.
at Excel-Blog - Andreas Thehos 
Du muss angemeldet sein, um einen Kommentar zu veröffentlichen.