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