Kategorie: Pivot

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