Kategorie: Pivot
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.
Pivot – Datenmodell mit Zeitintelligenz per DAX-Funktionen
Ohne die Verwendung von Pivot-Tabellen macht die Auswertung vielfältiger Informationen einfach keinen Spaß, ist extrem aufwändig und wird per Formeln höchst rechenintensiv und undynamisch. Seit Office 97 stehen uns Pivot-Tabellen direkt in Excel zur Verfügung. Ein tolles Tool… ich habe da sogar vor meiner Selbstständigkeit meine Diplom-Arbeit mit dem Thema “Deskriptive Statistik mit Microsoft Excel” zu geschrieben. Als Student und Tutor im Bereich Angewandte Sozialforschung war ich von Anfang an vom Potential und den Möglichkeiten der Pivot-Tabellen überzeugt. Vor allem, da es über Excel eine so große Verbreitung genoss.
Anfangs wurden die Pivot-Tabellen erst sehr sparsam genutzt. Sie waren aber auch sehr gut im Menü Daten verborgen. Pivot-Auswertungen erfreuen sich gerade in den letzten Jahren immer größerer Beliebtheit und Verwendung. Aber leider sind sie immer noch nicht bei der Auswertung von Daten in jedem Standardrepertoire eines Excel-Nutzers vorhanden. Und sie haben bis einschließlich Excel 2010 noch ein paar eindeutige Schwächen.
Und dann wurde auf einmal ALLES anders!
Microsoft veröffentlichte für Excel 2010 das Add-In PowerPivot, welches vorher unter dem Codenamen Genesis intern von der SQL-Crew entwickelt und dem Excel-Team vorgestellt wurde. Plötzlich konnten Unmengen von Daten aus externen Datenquellen angezapft und ausgewertet werden. Alte Mängel durch Nutzung des anfälligen Pivot Caches wurden behoben. Und durch eine für Excel neue Speichertechnik und von SQL (MDX) zu Excel migrierte zusätzliche DAX-Formeln (data analysis expressions) erweitern sich seitdem die Auswertungsmöglichkeiten.
Meine ersten YouTube-Videos zu PowerPivot aus Februar 2011 zeigen noch meine ersten Gehversuche. Schnell war klar, dass man auch besser und dynamischer an neue Daten und Datenquellen kommen muss. 2012 wurde dann Data Explorer als Add-In nachgereicht und kurze Zeit später in Power Query umbenannt. Allerdings war das Add-In PowerPivot noch nicht so stabil und führte häufiger zu Abstürzen. Mit Excel 2013 wurde dann endlich PowerPivot fest in Offfice 2013 integriert und damit auch die neue Speichermöglichkeit und Verarbeitung großer Datenmengen. Aus dem PowerPivot-Datenmodell wurde das Excel-Datenmodell. Und auch Power Query ist seit Excel 2016 fester Bestandteil von Excel. Und ja, es gab auch Power View und GeoFlow umbenannt in Power Map bzw. jetzt 3D-Karten.
Meine bei YouTube sehr häufig aufgerufenen und auch von Universitäten genutzten Pivot-Lehrvideos beziehen sich oft noch auf den alten Pivot Cache-Versionen ohne Verwendung des Datenmodells.
Eine neue Videoreihe zu Pivot wird sich auf die neuen Möglichkeiten des Datenmodells unter Verwendung von Power Query stützen. Das so erworbene Wissen lässt sich dann aber auch eine Stufe weiter in Power BI gut verwenden.
Eine neue Demodatei steht im XLSX-Format zur Verfügung:
Um die Beispiele optimal nachzuvollziehen wäre die Verwendung von Excel 2016 oder neue optimal. Aber auch Excel 2010 mit aktivierten Add-Ins PowerPivot und Power Query wäre nutzbar.
Für dieses kostenlos zur Verfügung gestellte Projekt brauche ich eure Unterstützung!
Wenn euch die Tutorials gefallen,
- empfehlt den Kanal bitte weiter!
- abonniert den Kanal!
- lasst einen Daumen nach oben da!
- und schreibt bitte gelegentlich einen Kommentar!
Interaktivität auf dem Kanal ist sehr wichtig, gibt mir Feedback, wie und ob die Videos bei euch ankommen und verbessert zudem das Ranking der Titel.
Und wenn euch einige Tipps einen echten Vorteil für den oder im Job gebracht haben und ihr auch Potential für euer Unternehmen seht, dann
- packt doch einige der Links zu den Videos auf eure firmeninterne Intranetseite! Eine reine Verlinkung auf meine YouTube-Videos, vielleicht noch mit einem Kommentar von euch ist immer erlaubt.
- gebt einen Link zum Kanal an eure Personalabteilung weiter!
- überlegt bitte, wieviel Zeit ihr durch ein wenig Fortbildung und Erweiterung eures Excel-Wissens gewinnen könnt! Als Berater und Trainer bin ich im ganzen deutschsprachigen Raum unterwegs… aktuell online, bald auch hoffenltlich wieder physisch.
Und danke nochmal für den regen und sehr aktiven Austausch auf unseren Excel-Stammtischen, welche auch aktuell online durchgeführt werden. Ich und andere können hier eine Menge voneinander lernen.
Vielen Dank auch an diejenigen, die oben die Schalter 700+ Excel-Stick und Kanal unterstützen gefunden haben. Das Betreiben des Kanals und die Produktion der Titel kosten mich nicht nur Zeit, sondern auch einige Taler. Merci *verbeug*
Im Juni gibt es dann das 10jährige Bestehen des YouTube-Kanals. Hoffentlich mit kleiner Grillparty 😉
Bleibt gesund!
Viel Erfolg und
bis bald
Andreas
Bulgarian Excel Days 2019 in Sofia
Was für ein Feuerwerk!!! Und wie schade, das wir hier in Deutschland ein solch krass gutes Event nicht stemmen können. Oder?
Zwischen dem 20. und 23. November 2019 haben sich in Sofia über 400 Personen zu den Bulgaria Excel Days 2019 zusammengefunden. Neben diversen eintägigen Masterclasses in PowerPivot, Power Query und Financial Controlling gab es noch eine Großveranstaltung in der einem in kurzen Vorträgen die Informationen nur so um die Ohren geflogen sind. Unglaublich wertvoll, spannend vorgetragen und für unsere Verhältnisse dann auch noch günstig.
Mit dabei waren unter anderem
- die Gastgeberin und Excel MVP Boriana Petrova
- aus Australien MVP Liam Bastick – absolut fit in Financial Modelling
- aus den Niederlanden MVP Tony der Jonker, ebenfalls Financial Modelling und Organisator der Excel Experience Days und des Amsterdam Excel Summit
- aus dem schönen Wien Excel MVP und YouTube-Kollegin Leila Gharani
- aus Kanada beehrte und MVP Ken Puls mit krass-guten Themen zu Power Query
- aus Slowenien der brilliante Excel MVP Gašper Kamenšek. Gašper organisiert die aktuell stattfindende Excel Olympics Conference 2019 in Ljubljana
- Excel MVP Frederic Le Guen aus Frankreich mit ein paar wirklich kleveren Tricks
- einige Mitglieder des für Excel verantwortlichen Microsoft Teams
- und so viele mehr…
Glaubt mir bitte: die waren alle wirklich sehr gut und haben neben einer unterhaltsamen und lehrreichen Performance auch viel Lehrmaterialien zur Nachbereitung mitgeliefert.
Ken Puls und Gašper Kamenšek haben mir in den Masterclasses den Horizont erweitert.
Von dem, was ich dort alles gesehen und gelernt habe, kann und werde ich euch nicht alles über den kostenlosen YouTube-Kanal oder die Stammtische zur Verfügung stellen können. Meine Schulungs- und Beratungskunden profitieren natürlich davon!
Das was ich dort gesehen habe, kann ich kaum mit einem Buch oder Videokurs lernen. Die Vorträge liefern mir aber einen Ansatzpunkt, wo ich mit der Fortbildung in Power Query, PowerPivot und Co. weitermachen kann und sollte. Und es gibt natürlich super Kurse, wie z.B. Power Query von Ken Puls und PowerPivot von Rob Collie und Matt Allington.
Wenn Ihr mit Excel arbeitet und von Excel massiv für euren Job profitiert, warum nehmt ihr nicht an solchen internationalen Konferenzen teil? Ihr profitiert nicht nur vom Wissen der Besten der Besten der Besten, ihr habt auch die Möglichkeit des direkten Austauschs und könnt euch als kleine Entschädigung für den Reiseaufwand tolle Städte anschauen. Sofia hat mich echt umgehauen! Also die Stadt!
Ein bisschen Spaß durfte nicht fehlen… Tony, Frederic, ich und Leila.
Einen ähnlich positiven Bericht werde ich aus Ljubljana abliefern dürfen. Ich habe heute einen DAX PowerPivot-Kurs bei MVP Matt Allington absolviert, genieße hier die Gastfreundschaft von Gašper Kamenšek und bin mal wieder erstaunt… ich bin der einzige Deutschsprachige hier…
Sonnige Grüße
Andreas
P.S.: Schaut euch doch mal um… nächstes Jahr gibt es den Amsterdam Excel Summit 2020, den Global Excel Summit 2020 in London https://globalexcelsummit.com , natürlich wieder die Bulgaria Excel Days und die Excel Olympics Conference in Ljubljana.
P.P.S.: Wer es lieber “deutsch” mag, dem empfehle ich mit gutem Gewissen die Excel-Kompetenztage in Fulda und die bestimmt auch wieder stattfindenden Munich Excel Days.
P.P.S.: Hey! Und natürlich bin ich auch noch für euch da! Denkt an die Excel-Stammtische!
Excel-Pivot – Folgen 15 bis 17
Insgesamt wurden meine Excel Pivot-Tutorials (ohne PowerPivot) über 920.000 mal aufgerufen. Aus meiner Tätigkeit als Berater und Trainer weiß ich, dass gerade die Pivot-Auswertungen von größtem Nutzen für sämtliche Listenauswertungen im betrieblichen Controlling, Projekt- und Personalcontrolling und vielen anderen Aufgabengebieten sind.
In den Ergebnissen meiner im Dezember 2015 gestarteten Umfrage https://youtu.be/NQYhO9BOyQY wurde auch verstärkt der Wunsch geäußert, noch mehr Themen aus den Bereichen Excel für das Controlling aufzunehmen, um Controllership und Rechnungswesen im Unternehmen zu unterstützen. Oftmals waren es nur kleine technische Tipps und Denkanstöße, die bei den Zusehern zu neuen Auswertungen und Berichten geführt haben.
Noch kurz zwei Antworten auf die Frage, ob die Videos schon weiterhelfen konnten:
Ich habe sehr viel gelernt und konnte die Lohnberechnung in unserem kleinen Familienbetrieb besser organisieren.
Die Videos helfen oft bei knackigen Problemen der Auswertung und geben Anregungen zu weiteren Möglichkeiten
Größter Beliebtheit mit mehr als 550.000 Videoaufrufen erfreut sich immer noch eine Pivot-Reihe aus dem Jahr 2012. Die Videos basieren auf zwei Übungsdateien, an denen man die demonstrierten Inhalte nachvollziehen kann.
Die ersten Tutorials “Pivot 1-14” sind inklusive Übungsdatei unter dem Link http://thehosblog.com/2013/10/12/pivot_tutorial/ zu finden.
Die weiteren Titel nutzen die Datei: Pivot_Tutorial_15-
15 – Berechnetes Element: http://youtu.be/1SFSjpwSOQc
15a – Berechnetes Element Ergänzung: http://youtu.be/5yaYak5J8Bo
16 – Berechnete Felder: http://youtu.be/KXGdA0I6bKc
16a – Problem mit berechneten Feldern: http://youtu.be/MpU3up0LqJ4
16b – Berechnete Felder und Gesamtergebnis: http://youtu.be/totLHKwOeTw
16c – Problem mit berechneten Feldern3: http://youtu.be/Sedr8xZijJY
17 – PIVOTDATENZUORDNEN: http://youtu.be/21wrdnVyLxM
Danke nochmals für die vielen Unterstützer, die den YouTube-Kanal und diesen Blog im vergangen Jahr finanziell und mit Tipps unterstützt haben.
Beste Grüße und ein spannendes Jahr 2016
Andreas Thehos
Excel – Terminplan in Gantt-Ansicht – Kostenauswertung mit Pivot – Teil 8
In Excel-Gantt-Tutorial # 7 habe ich Kosten für mein Projekt ermittelt.
Die Auswertung mit Pivot bietet sich an.
Hier geht es zur Ausgangsdatei:
Excel626 Terminplan Start
Hier geht es zum Video:
Videolink: http://youtu.be/biXgmFsGyQE
Excel – Summe oberhalb gestapelten Säulen im Pivot-Chart
Die Darstellung von Summen oberhalb von gestapelten Säulen ist ein wenig trickreich. Leider gibt es dazu keinen einfachen Optionsschalter bei der Erstellung von Diagrammen.
Bei Diagrammen aus Pivot-Daten gibt es aber noch einen einfachen zusätzlichen Trick.
Generieren Sie eine zweite Tabelle und erstellen Sie daraus ein Säulendiagramm. Bei diesem Säulendiagramm kann man die Summen oberhalb von Säulen darstellen lassen. Anschließend werden die Diagrammfarben auf “ohne Füllung” gesetzt.
Anschließend wird das eigentliche gestapelte Säulendiagramm auf Basis der ursprünglichen Pivot-Daten erstellt. Hier muss die Hintergrundfarbe der Diagramm- und Zeichnungsflächen unsichtbar geschaltet werden.
Nun sind beide Diagramme so übereinander zu platzieren, dass die Summen der unteren Säulen oberhalb der gestapelten Säulen erscheinen. Dazu muss eventuell noch der Bereich der Zeichnungsfläche angepasst werden.
Mehr dazu erfahren Sie im folgenden Video.
Link zum Video:
Videolink: http://youtu.be/3858JHaspRE
Excel – PivotChart und VBA – Summe oberhalb eines gestapelten Säulendiagramms
Bei einem normalen gestapelten Säulendiagramm kann die Summe schnell oberhalb der Säulen platziert werden. Doch wie sieht es bei einem Pivot-Diagramm aus? Die Spalte Gesamtergebnis lässt sich leider nicht in das Diagramm integrieren, oder?
Ich behelfe mir mit einem berechneten Element, dieses bildet die Summe der sichtbaren also über den Filter definierten Elemente. Anschließend verstellt sich das Layout des Diagramms, auch dies muss korrigiert werden.
Zunächst die Videos – und steht die VBA-Lösung.
Video 1 – Aufbau des Diagramms und des berechneten Felds
Videolink: http://youtu.be/wAI2afucNcI
Video 2 – Intermezzo 1 – Die Makroaufzeichnung berechnetes Element
Videolink: http://youtu.be/j7kwBgZVzZc
Video 3 – Intermezzo 2 – Die Makroaufzeichnung Anpassung Diagramm
Videolink: http://youtu.be/JP3TZPv2cfo
Video 4 – Beschreibung des VBA-Codes
Videolink: http://youtu.be/CKxazIaH6qc
VBA-Code:
Sub Summe_Säulendiagramm()
Dim strFormel As String
Dim strElement As String
Dim i As Integer
Dim intItem As Integer
Dim intAnzahlItems As Integer
On Error GoTo ErrorHandler
strFormel = “=”
intItem = 0
Sheets(“Auswertung”).Range(“B4”).Select
intAnzahlItems = ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Ort”).PivotItems.Count
For i = 2 To intAnzahlItems
If ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Ort”).PivotItems(i).Visible = True Then
intItem = intItem + 1
strElement = ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Ort”).PivotItems(i).Name
If intItem > 1 Then strFormel = strFormel & “+”
strFormel = strFormel & strElement
End If
Next i
ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Ort”) _
.PivotItems(“Summe”).Formula = strFormel
ActiveSheet.ChartObjects(“Diagramm 1”).Activate
With ActiveChart
.FullSeriesCollection(“Summe”).ChartType = xlLine ‘In Excel 2010 SeriesCollection
.FullSeriesCollection(“Summe”).DataLabels.Select ‘In Excel 2010 SeriesCollection
.SetElement (msoElementDataLabelTop)
.PlotArea.Select
.FullSeriesCollection(“Summe”).Format.Line.Visible = msoFalse ‘In Excel 2010 SeriesCollection
End With
Exit Sub
ErrorHandler:
MsgBox “Ein Fehler ist aufgetreten. Wird Summe angezeigt? Haben Sie auch Daten ausgewählt?”, _
vbCritical, “Aktion abgebrochen”
End Sub
Du muss angemeldet sein, um einen Kommentar zu veröffentlichen.