AW: Pivot Mittelwert/Anzahl
16.05.2011 14:38:51
fcs
Hallo Squishy,
mit berechnetem Feld ist da nichts zu machen.
Du könntest mit einer Spalte links von der Pivottabelle arbeiten oder die Spalte mit den Formeln soweit rechts anordnen, dass sie nie durch den Pivotbericht überschrieben werden (z.B. Spalte BA)
Bei Anordnung links der Pivottabelle wäre die Formel für die Zeile A7:
=SUMMEWENN($D$6:$AZ$6;"Mittelwert Prämie";D7:AZ7)
Diese Formel dann nach unten kopieren
Andere Automatisierungen können mit VBA-Makros umgesetzt werden.
Nachfolgend ein Beispielmakro für deine Pivottabelle. Im VBA-Editor muss du es unter der Tabelle mit dem Pivottabellenbericht einfügen. Es wird automatisch ausgeführt, wenn die die Pivottabelle aktualisiert wird.
Gruß
Franz
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim Spalte As Long, Spalte1 As Long, Zeile1 As Long, Zeile2 As Long
Dim pvTab As PivotTable, wks As Worksheet
Set wks = ActiveSheet
Set pvTab = Target
With pvTab
'1. Spalte des Datenbereichs der Pivottabelle
Spalte1 = .DataBodyRange.Column
'Spalte in der Zusatzberechnung eingefügt werden soll
Spalte = .DataBodyRange.Column + .DataBodyRange.Columns.Count
'Zeilen des Datenbereichs der Pivottabelle
Zeile1 = .DataBodyRange.Row
Zeile2 = .DataBodyRange.Row + pvTab.DataBodyRange.Rows.Count - 1
End With
Application.ScreenUpdating = False
With wks
'Alle Spalten einblenden
.Columns.Hidden = False
'ggf. noch vorhandene Zusatz-Spalte mit kummulierten Daten löschen
If .Cells(Zeile1, .Columns.Count).End(xlToLeft).Column > Spalte - 1 Then
.Cells(Zeile1, .Columns.Count).End(xlToLeft).EntireColumn.Clear
End If
'Spaltentitel in Zusatzspalte eintragen und formatieren
With .Range(.Cells(Zeile1 - 3, Spalte), .Cells(Zeile1 - 1, Spalte))
.Merge
.WrapText = True
.VerticalAlignment = xlVAlignCenter
.Borders.LineStyle = xlContinuous
.Font.Size = 8
End With
.Cells(Zeile1 - 3, Spalte).Value = "Prämien kummuliert"
'Zelle oberhalb Spaltentitel formatieren
With .Range(.Cells(Zeile1 - 4, Spalte), .Cells(Zeile1 - 4, Spalte))
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Font.Size = 8
End With
'Zusatzspalte formatieren und Formeln eintragen
With .Range(.Cells(Zeile1, Spalte), .Cells(Zeile2, Spalte))
.Borders.LineStyle = xlContinuous
.Font.Size = 8
.NumberFormat = "#,##0.00"
.FormulaR1C1 = "=SUMIF(R" & Zeile1 - 1 & "C" & Spalte1 & ":R" & Zeile1 - 1 _
& "C[-4],""Mittelwert Prämie"",RC" & Spalte1 & ":RC[-4])"
End With
'Breite der Gesamtwert-Spalten einstellen
.Range(.Columns(Spalte - 3), .Columns(Spalte)).EntireColumn.ColumnWidth = 7
'Breite der Daten-Spalten optimieren
.Range(.Columns(pvTab.DataBodyRange.Column), _
.Columns(Spalte - 4)).EntireColumn.AutoFit
'2 der Gesamt-Spalten ausblenden
.Range(.Columns(Spalte - 2), .Columns(Spalte - 1)).EntireColumn.Hidden = True
End With
Application.ScreenUpdating = True
End Sub