Kategorie: Pivot

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.

A1ExcelDays

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.

A1Ken

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!

A1Auto

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 https://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 – 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

Excel – Pivot – Ein Überblick

Pivot – übersetzt aus dem Englischen bedeutet es soviel wie Dreh- oder Angelpunkt. Es gibt Monitore mit Pivot-Gelenk – diese können seitlich vom Breitformat ins Hochformat gedreht werden. Roboter mit Pivot-Gelenken arbeiten in Werkshallen und animierte Pivot-Figuren liefern sich spannungsgeladene Action in witzigen Kurzfilmen.

Mit den PivotTables und PivotCharts aus Microsoft Excel bietet uns Excel eines der nützlichsten und mächtigsten Werkzeuge zur Auswertung großer Datenbestände. PivotTables (Pivot-Tabellen) sind in ihren Grundzügen leicht und intuitiv zu bedienen. Die meisten Fragestellungen, die sich aus einem bestehenden Datenmaterial ergeben, können mit Pivot oder den neuen Werkzeugen aus PowerPivot, Power Maps, Power Query und Power View beantwortet werden. Natürlich gibt es auch hier beliebig komplexe Situationen, die erst mit ausreichender Erfahrung umgesetzt werden können.

In einem ersten Video werden ein paar grundlegende Möglichkeiten von Microsoft Excel Pivot demonstriert.
Das Video wurde in 2012 mit Excel 2010 aufgezeichnet. Viel Spaß damit.

Hier geht es zum Video:

Videolink: http://youtu.be/DuMuna_TIPc

Excel – Power Pivot – HasOneValue und Values

In meinen Daten befinden sich Werte, die entweder in Euro EUR oder in US-Dollar USD gemeldet wurden. Mit berechneten Elementen der regulären PivotTables hätte ich dies in Pivot umrechnen können. Natürlich ginge auch eine Hilfsspalte neben den Daten.

Die Werte sollen addiert werden können.

540a

In dieser Tabelle ist aufgeführt, welches Land in welcher Währung gemeldet hat.

540b

Und natürlich gibt es für spätere Auswertungen noch eine Kalendertabelle.

540c

Alle drei Tabellen werden ins Power Pivot Data Model geladen. Hier die Diagrammsicht.

540d

In der Faktentabelle Umsatz wird nun ein berechnetes Feld mit dem Namen Betrag errechnet. Betrag rechnet hier aber Äpfel und Birnen zusammen – vielmehr Euro und Dollar. Der Betrag in Höhe von 162 Millionen ist zu hoch. Bereinigt müssten eigentlich rund 153 Mio. resultieren.

540e

Betrag:=
SUM(Umsatz[Wert])/
IF(HASONEVALUE(Countries[Gruppe]);
IF(VALUES(Countries[Gruppe])="USD";1,36;1)
;1)

Es wird die Summe der Umsätze gebildet. Dabei wird aber geschaut, ob einer der Datensätze der Gruppe USD zugeordnet werden kann. Falls ja, wird durch den EUR-USD-Wechselkurs 1,36 geteilt. Alternativ wird der Wert 1 genommen. Für den Fall, dass es das Feld Countries[Gruppe] nicht gibt, wird auch der Wert 1 genommen.

Da sich das Feld Land in der Dimensionstabelle mit der Währungszuordnung befindet, kann dieses Feld Land genommen werden. Die Zuordnung zu einer Währung erfolgt hier automatisch.
Kopfrechenaufgabe: Hier müssten die korrekten 153 Mio. resultieren.

540f

Wird hingegen Land aus der Faktentabelle genommen, ist die zusätzliche Gruppierung nach Währungsgruppe notwendig.
Die Summe liegt bei 153 Mio. Euro.

540k

Ohne diese Gruppe wird nicht korrekt umgerechnet. Die Summe beträgt rund 162 Mio. Einheiten aus EUR und USD gemischt.

540j

Hier geht es zum Verzeichnis mit den Übungsdateien:
http://1drv.ms/TRQlgF

Hier geht es zum Video:

Videolink: http://youtu.be/xH9ETKA3UYY