In PivotTables können Sie Zusammenfassungsfunktionen in Wertfeldern verwenden, um Werte aus den zugrunde liegenden Quelldaten zu kombinieren. Wenn mit Zusammenfassungsfunktionen und benutzerdefinierten Berechnungen nicht die gewünschten Ergebnisse erzielt werden, können Sie eigene Formeln in berechneten Feldern und berechneten Elementen erstellen. So können Sie beispielsweise ein berechnetes Element mit der Formel für die Verkaufsprovision hinzufügen, die für jede Region unterschiedlich sein kann. Die PivotTable enthält dann automatisch die Provision in den Teil- und Gesamtergebnissen.
Eine weitere Möglichkeit zum Berechnen ist die Verwendung von Measures in Power Pivot, die Sie mithilfe einer DAX-Formel (Data Analysis Expressions) erstellen. Weitere Informationen finden Sie unter Erstellen eines Measures in Power Pivot.
PivotTables bieten Möglichkeiten zum Berechnen von Daten. Erfahren Sie mehr über die verfügbaren Berechnungsmethoden, Auswirkungen des Quelldatentyps auf Berechnungen und wie Formeln in PivotTables und PivotCharts verwendet werden.
Verfügbare Berechnungsmethoden
Zum Berechnen von Werten in einer PivotTable können Sie eine oder alle der folgenden Berechnungsmethoden verwenden:
Zusammenfassungsfunktionen in Wertfeldern Die Daten im Wertebereich fassen die zugrunde liegenden Quelldaten in der PivotTable zusammen. Beispielsweise werden mit den folgenden Quelldaten:
die folgenden PivotTables und PivotCharts erstellt. Wenn Sie ein PivotChart aus den Daten in einer PivotTable erstellen, spiegeln die Werte in diesem PivotChart die Berechnungen im zugeordneten PivotTable-Bericht wider.
In der PivotTable stellt das Spaltenfeld Monat die Elemente März und April bereit. Das Zeilenfeld Region stellt die Elemente Nord, Süd, Ost und West bereit. Der Wert am Schnittpunkt der Spalte April mit der Zeile Nord ist der Gesamtumsatzerlös aus den Datensätzen in den Quelldaten, bei denen für Monat der Wert April und für Region der Wert Nord angegeben ist.
In einem PivotChart kann das Feld Region ein Kategoriefeld sein, das Nord, Süd, Ost und West als Kategorien enthält. Das Feld Monat kann ein Reihenfeld sein, das die Elemente März, April und Mai als Reihe in der Legende darstellt. EinWertfeld mit dem Namen Summe von Umsatz kann Datenpunkte enthalten, die den Gesamterlös in jeder Region für jeden Monat darstellen. Beispielsweise kann ein Datenpunkt durch seine Position auf der vertikalen Achse (Größenachse) den Gesamtumsatz für April in der Region Nord darstellen.
Zum Berechnen der Wertfelder stehen die folgenden Zusammenfassungsfunktionen für alle Arten von Quelldaten mit Ausnahme von OLAP-Quelldaten (Online Analytical Processing) zur Verfügung.
Funktion Ergebnis Summe Die Summe der Werte. Dies ist die Standardfunktion für numerische Daten. Anzahl Die Anzahl der Datenwerte. Die Zusammenfassungsfunktion "Anzahl" funktioniert genauso wie die Funktion ANZAHL2. "Anzahl" ist die Standardfunktion für andere Daten als Zahlen. Mittelwert Der Mittelwert der Werte. Max Der größte Wert. Min Der kleinste Wert. Produkt Das Produkt der Werte. Anzahl Zahlen Die Anzahl der Datenwerte, die Zahlen sind. Die Zusammenfassungsfunktion "Anzahl Zahlen" funktioniert genauso wie die Funktion ANZAHL. Standardabweichung (Stichprobe) Eine Schätzung der Standardabweichung einer Grundgesamtheit, wobei die Stichprobe eine Teilmenge der Grundgesamtheit ist. Standardabweichung (Grundgesamtheit) Die Standardabweichung einer Grundgesamtheit, wobei die Grundgesamtheit aus allen zusammenzufassenden Daten besteht. Varianz (Stichprobe) Eine Schätzung der Varianz einer Grundgesamtheit, wobei die Stichprobe eine Teilmenge der Grundgesamtheit ist. Varianz (Grundgesamtheit) Die Varianz einer Grundgesamtheit, wobei die Grundgesamtheit aus allen zusammenzufassenden Daten besteht.
Benutzerdefinierte Berechnungen Eine benutzerdefinierte Berechnung zeigt Werte basierend auf anderen Elementen oder Zellen im Datenbereich an. Sie können z. B. Werte im Datenfeld Summe von Umsatz als Prozentwerte des Umsatzes von März oder als eine laufende Summe der Elemente im Feld Monat anzeigen.
Die folgenden Funktionen stehen für benutzerdefinierte Berechnungen in Wertfeldern zur Verfügung.Funktion Ergebnis Keine Berechnung Zeigt den in das Feld eingegebene Wert an. % des Gesamtergebnisses Zeigt Werte als Prozentwerte des Gesamtergebnisses aller Werte oder Datenpunkte im Bericht an. % des Spaltengesamtergebnisses Zeigt alle Werte in jeder Spalte oder Reihe als Prozentwerte des Gesamtergebnisses für die Spalte oder Reihe an. % des Zeilengesamtergebnisses Zeigt den Wert in jeder Zeile oder Kategorie als Prozentwert des Gesamtergebnisses für die Zeile oder Kategorie an. % von Zeigt Werte als Prozentwerte des Werts vom Basiselement im Basisfeld an. % des Vorgängerzeilen-Gesamtergebnisses Berechnet Werte wie folgt:
(Wert für das Element) / (Wert für das übergeordnete Element in Zeilen)% des Vorgängerspalten-Gesamtergebnisses Berechnet Werte wie folgt:
(Wert für das Element) / (Wert für das übergeordnete Element in Spalten)% des Vorgängergesamtergebnisses Berechnet Werte wie folgt:
(Wert für das Element) / (Wert für das übergeordnete Element des ausgewählten Basisfelds)Differenz von Zeigt Werte als Differenz von dem Wert des Basiselements im Basisfeld an. % Differenz von Zeigt Werte als prozentuale Differenz von dem Wert des Basiselements im Basisfeld an. Ergebnis in Zeigt den Wert für aufeinander folgende Elemente im Basisfeld als fortlaufendes Ergebnis an. % Ergebnis in Berechnet den Wert für aufeinander folgende Elemente im Basisfeld, die als fortlaufendes Ergebnis als Prozentwert angezeigt werden. Rangfolge nach Größe (aufsteigend) Zeigt den Rang der ausgewählten Werte in einem bestimmten Feld an, wobei das kleinste Element im Feld als 1 und jeder größere Wert mit einem höheren Rangfolgewert aufgelistet wird Rangfolge nach Größe (absteigend) Zeigt den Rang der ausgewählten Werte in einem bestimmten Feld an, wobei das größte Element im Feld als 1 und jeder kleinere Wert mit einem höheren Rangfolgewert aufgelistet wird Index Berechnet Werte wie folgt:
((Wert in Zelle) x (Gesamtsumme der Gesamtsummen)) / ((Zeilengesamtsumme) x (Spaltengesamtsumme))
- Formeln Wenn Zusammenfassungsfunktionen und benutzerdefinierte Berechnungen nicht die gewünschten Ergebnisse liefern, können Sie eigene Formeln in berechneten Feldern und berechneten Elementen erstellen. So können Sie beispielsweise ein berechnetes Element mit der Formel für die Verkaufsprovision hinzufügen, die für jede Region unterschiedlich sein kann. Der Bericht enthält dann automatisch die Provision in den Teil- und Gesamtergebnissen.
Auswirkungen des Quelldatentyps auf Berechnungen
Die in einem Bericht verfügbaren Berechnungen und Optionen hängen davon ab, ob die Quelldaten aus einer OLAP-Datenbank oder einer anderen Datenquelle stammen.
-
Auf OLAP-Quelldaten basierende Berechnungen Für PivotTables, die aus OLAP-Cubes erstellt werden, werden die zusammengefassten Werte auf dem OLAP-Server vorberechnen, bevor Excel die Ergebnisse anzeigt. Sie können nicht ändern, wie diese im Voraus berechneten Werte in der PivotTable berechnet werden. Sie können beispielsweise nicht die Zusammenfassungsfunktion ändern, die zum Berechnen von Datenfeldern oder Teilergebnissen verwendet wird, oder berechnete Felder oder berechnete Elemente hinzufügen.
Außerdem werden vom OLAP-Server ggf. bereitgestellte berechnete Felder – ebenfalls als berechnete Elemente (Member) bezeichnet – in der Feldliste der PivotTable angezeigt. Es werden auch alle berechneten Felder und berechneten Elemente angezeigt, die von Makros erstellt werden, die in Visual Basic for Applications (VBA) geschrieben und in der Arbeitsmappe gespeichert sind, doch können Sie diese Felder oder Elemente nicht ändern. Wenn Sie weitere Berechnungsarten benötigen, wenden Sie sich an den OLAP-Datenbankadministrator.
Bei OLAP-Quelldaten können Sie die Werte für ausgeblendete Elemente einbeziehen oder ausschließen, wenn Sie Teilergebnisse und Gesamtergebnisse berechnen. - Berechnungen basierend auf Nicht-OLAP-Quelldaten In PivotTables, die auf anderen Typen externer Daten oder auf Arbeitsblattdaten basieren, verwendet Excel die Sum-Zusammenfassungsfunktion, um Wertefelder zu berechnen, die numerische Daten enthalten, und die Zusammenfassungsfunktion Zählen, um Datenfelder zu berechnen, die Text enthalten. Sie können eine andere Zusammenfassungsfunktion wie z. B. "Mittelwert", "Max" oder "Min" verwenden, um die Daten weiter zu analysieren und anzupassen. Sie können auch eigene Formeln erstellen, die Elemente des Berichts oder andere Arbeitsblattdaten verwenden, indem Sie ein berechnetes Feld oder ein berechnetes Element in einem Feld erstellen.
Verwenden von Formeln in PivotTables
Formeln können nur in Berichten erstellt werden, die nicht auf OLAP-Quelldaten basieren. Sie können keine Formeln in Berichten verwenden, die auf einer OLAP-Datenbank basieren. Wenn Sie Formeln in PivotTables verwenden, sollten Sie die folgenden Regeln für die Formelsyntax und das Verhalten von Formeln kennen:
PivotTable-Formelelemente In Formeln, die Sie für berechnete Felder und berechnete Elemente erstellen, können Sie Operatoren und Ausdrücke wie in anderen Arbeitsblattformeln verwenden. Sie können Konstanten verwenden und sich auf Daten aus dem Bericht beziehen, aber Sie können keine Zellbezüge oder definierten Namen verwenden. Sie können keine Arbeitsblattfunktionen verwenden, die Zellbezüge oder definierte Namen als Argumente erfordern, und Sie können keine Matrixfunktionen verwenden.
Feld- und Elementnamen Excel verwendet Feld- und Elementnamen, um diese Elemente eines Berichts in Ihren Formeln zu identifizieren. Im folgenden Beispiel wird für die Daten im Bereich C3: C9 der Feldname Milchprodukte verwendet. Ein berechnetes Element im Feld Typ, das den Umsatz für ein neues Produkt basierend auf dem Umsatz für Milchprodukte schätzt, kann z. B. eine Formel wie =Milchprodukte * 115% verwenden.
Hinweis
In einem PivotChart werden die Feldnamen in der PivotTable-Feldliste angezeigt, und Elementnamen sind in den einzelnen Dropdownlisten der Felder zu sehen. Verwechseln Sie diese Namen nicht mit denjenigen, die in den Diagrammtipps zu sehen sind, da diese Namen von Reihen und Datenpunkten darstellen.
Formeln arbeiten mit Summen, nicht mit einzelnen Datensätzen Formeln für berechnete Felder arbeiten mit der Summe der zugrunde liegenden Daten für alle Felder in der Formel. Beispielsweise wird mit der Formel für ein berechnetes Feld =Umsatz * 1,2 die Umsatzsumme für jeden Typ und jede Region mit 1,2 multipliziert und nicht jeder einzelne Umsatz mit 1.2 multipliziert und anschließend die multiplizierten Werte addiert.
Formeln für berechnete Elemente verwenden die einzelnen Datensätze. Beispielsweise werden mit der Formel für ein berechnetes Element =Milchprodukte * 115% jeder einzelne Umsatzwert für Milchprodukte mit 115 % multipliziert, und anschließend werden die multiplizierten Werte im Bereich "Werte" zusammengefasst.Leerzeichen, Zahlen und Symbole in Namen In einem Namen, der mehr als ein Feld enthält, können die Felder in beliebiger Reihenfolge enthalten sein. Im oben gezeigten Beispiel können die Zellen C6:D6 sowohl "April Nord" als auch "Nord April" sein. Schließen Sie Namen, die mehr als ein Wort, Zahlen oder Symbole enthalten, in einfache Anführungszeichen ein.
Summen Formeln können nicht auf Summen verweisen (z. B. Summe März, Summe April und Gesamtergebnis im Beispiel).
Feldnamen in Elementverweisen Sie können den Feldnamen in einen Verweis auf ein Element einschließen. Der Elementname muss in rechteckige Klammern gesetzt werden, z. B. Region[Nord]. Verwenden Sie dieses Format, um #NAME zu vermeiden? fehler, wenn zwei Elemente in zwei unterschiedlichen Feldern in einem Bericht denselben Namen haben. Wenn ein Bericht beispielsweise ein Element namens Fleisch im Feld Typ und ein anderes Element namens Fleisch im Feld Kategorie enthält, können Sie verhindern, dass #NAME? fehler, indem auf die Elemente als Type[Meat] und Category[Meat] verwiesen wird.
Verweisen auf Elemente nach Position Sie können auf ein Element anhand seiner Position im Bericht verweisen, die aktuell sortiert und angezeigt wird. Typ[1] ist Milchprodukte und Typ[2] ist Meeresfrüchte. Das Element, auf das in dieser Form verwiesen wird, kann sich ändern, sobald sich die Positionen von Elementen ändern oder andere Elemente angezeigt bzw. ausgeblendet werden. Ausgeblendete Elemente werden in diesem Index nicht gezählt.
Sie können relative Positionen zum Verweisen auf Elemente verwenden. Die Positionen werden relativ zu dem berechneten Element bestimmt, das die Formel enthält. Wenn Süd die aktuelle Region ist, dann ist Region[-1] die Region Nord. Wenn Nord die aktuelle Region ist, dann ist Region[+1] die Region Süd. Ein berechnetes Element kann z. B. die Formel =Region[-1] * 3% verwenden. Ein berechnetes Element kann z. B. die Formel =Region[-1] * 3% verwenden. Wenn die von Ihnen angegebene Position vor dem ersten oder nach dem letzten Element im Feld liegt, führt die Formel zu einem Fehler des Typs #BEZUG!.
Verwenden von Formeln in PivotCharts
Zum Verwenden von Formeln in einem PivotChart erstellen Sie die Formeln in der zugeordneten PivotTable, wo Sie die einzelnen Werte sehen können, aus denen sich Ihre Daten zusammensetzen. Anschließend können Sie die Ergebnisse im PivotChart grafisch darstellen.
Das folgende PivotChart zeigt z. B. die Umsätze für jeden Verkäufer pro Region:
Wenn Sie sehen möchten, wie um 10 Prozent gesteigerte Umsätze aussehen, können Sie ein berechnetes Feld in der zugeordneten PivotTable erstellen, das eine Formel wie =Umsatz * 110% verwendet.
Das Ergebnis wird sofort im PivotChart dargestellt, wie es im folgenden Diagramm zu sehen ist:
Zum Anzeigen eines separaten Datenpunkts für Umsätze in der Region "Nord" abzüglich der Transportkosten von 8 Prozent können Sie ein berechnetes Element im Feld "Region" mit einer Formel wie =Nord – (Nord * 8%) erstellen.
Das daraufhin angezeigte Diagramm würde wie folgt aussehen:
Allerdings würde ein berechnetes Element, das im Feld "Verkäufer" erstellt wird, als eine in der Legende dargestellte Reihe und im Diagramm als Datenpunkte in jeder Kategorie angezeigt werden.
Benötigen Sie weitere Hilfe?
Sie können jederzeit einen Experten in der Excel Tech Community fragen oder Unterstützung in Communitys erhalten.