Pivot Summenzeile mit Formel

Forumthread: Pivot Summenzeile mit Formel

09.05.2022 12:32:12

Moritz

Hallo Zusammen,
bei der Zusammenfassung einer Auswertung per Pivot Tabelle ergibt sich mir folgendes Problem:
Hier habe ich eine Spalte die sich aus 2 anderen Zeilen berechnet und einen Prozentwert ausspuckt. Ergebnisse können hier positiv, negativ oder 0 sein.
Für die Summenzeile kann ich nun nur "summe" bzw "mittelwert" etc. wählen. Damit komme ich jedoch nicht auf die richtigen Ergebnisse. Die Summe in dieser Spalte müsste sich neu aus den Summen der beiden anderen Spalten berechnen. In die Pivot Summenzeile kann ich jedoch weder manuell eine Formel einfügen noch sehe ich eine Option um mein Ziel zu ereichen. Ein berechnetes Feld hilft mir hier auch nicht, da Excel hier eine neue Spalte erstellt bei der ich das selbe Problem wieder bei den Summen habe.
Liebe Grüße und vielen Dank im Voraus
Moritz
Hier eine Beispieldatei, Das Problem habe ich in Spalte H nocheinmal näher ausgeführt:
https://www.herber.de/bbs/user/152955.xlsx

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: in Deiner 2013er XL-Version ...

09.05.2022 13:14:09

neopa
AW: Pivot Summenzeile mit Formel

09.05.2022 16:57:04

Yal
AW: Pivot Summenzeile mit Formel

10.05.2022 13:27:25

Moritz
AW: Pivot Summenzeile mit Formel

10.05.2022 14:19:03

Yal

AW: in Deiner 2013er XL-Version ...

09.05.2022 13:14:09

neopa

Hallo Moritz,
... stehen bestimmte Funktionalitäten noch nicht zur Verfügung. Du kannst Dir aber einfach z.B. wie folgt helfen:
Werte den Mittelwert der Marge nicht über Pivot aus, sondern schreibe anstelle dessen folgende Formel in D4:
=WENNFEHLER(1-(C4/B4);"") und kopiere diese durch Doppelklick nach unten.
Alternativ: Lade Dir (Kostenfrei) von https://www.microsoft.com/de-ch/download/details.aspx?id=39379 Das Add-In PowerQuery (PQ) für Deine XL-Version erstelle damit das von Dir gewünschte. Diese Funktionalität kannst Du sicher noch oft gut einsetzen. In neueren XL-Versionen ist sie automatisch schon in XL integriert.
Mehr zu PQ sieh mal hier https://excelhero.de/power-query/power-query-ganz-einfach-erklaert/
Gruß Werner
.. , - ...

AW: Pivot Summenzeile mit Formel

09.05.2022 16:57:04

Yal

Hallo Moritz,
Du brauchst ein berechnetes Feld. Diese Felder werden nach der Pivot-Aggregation ausgewertet:
_ eine Zelle im Pivot markieren,
_ Menü "Pivottabel-Tools", "Analysieren", "Felder, Element und Gruppen", "Berechnetes Feld"
_ Name: "Marge2"
_ Formel: "=1-(COS/Umsatz) "
_ Hinzufügen, ok.
_ dann Marge2 in den Wert-Felder verschieben (Drag&drop)
_ lick auf "Summe von Marge2", "Wertfeldeinstellungen...", "Mittelwert" und Zahlenformal Prozent
_ Feld "Art + Beschr." wegnehmen, um die Pivotwirkung sichtbar zu machen.
Mittelwert von Marge2 zeigt 15,68%
Eigentlich die Berechnung der Marge aus der Quelle komplett wegnehmen. Dann kann dein berechnetes Feld "Marge" heissen anstatt "Marge2".
VG
Yal

AW: Pivot Summenzeile mit Formel

10.05.2022 13:27:25

Moritz

Hallo Werner, hallo Yal,
vielen Dank für eure sehr zeitnahen Antworten!
Die Formel in die Spalte daneben schreiben funktioniert bei mir leider nicht, da die Originalpivot größer als das Beispiel ist und die Margenspalten mitten in der Pivot angeordnet sein müssen. Power Query hab ich zwar schon, jedoch noch keine Erfahrungen..
Auch wenn ich nicht ganz verstehe wieso Excel beim Mittelwert in Yal's Beispiel nicht den Mittelwert wie vorher auswirft, sondern diesen jetzt korrekt berechnet, aber es klappt!!
Vielen Dank euch beiden!! :)
Liebe Grüße
Moritz

AW: Pivot Summenzeile mit Formel

10.05.2022 14:19:03

Yal

Hallo Moritz,
es gilt den Spruch: Die Summe der Produkt ist nicht gleich den Produkt der Summe.
Wenn Du zuerst die prozentuelle Marge rechnet, ist es ein Produkt. Du machst anschliessend den Mittelwert, das ist -mehr oder weniger- eine Summe: Produkt vor Summe.
Wenn Du die prozetuelle Marge als Pivot-Formel einträgst, dann werden zuerst die Summen durch den Pivot errechnet, dann die Marge. Also Summe vor Produkt.
Es ist mit Rabatt leichter zu verstehen: 4% auf Art.1, 5% auf Art.2 und 6% auf Art.3 ergeben nicht 15% Rabatt, sondern je nach Artikelpreis, irgendwas zwischen 4 und 6%: es müssen die Preise und die Rabatte als Wert summiert, dann der prozentuelle Rabatt berechnet.
VG
Yal

Infobox / Tutorial

Schritt-für-Schritt-Anleitung

Pivot Tabelle erstellen: Starte Excel und wähle die Daten aus, die du in deiner Pivot Tabelle verwenden möchtest. Gehe zu „Einfügen“ und klicke auf „PivotTable“. Wähle den gewünschten Speicherort aus.
Felder hinzufügen: Ziehe die benötigten Felder in die Bereiche „Zeilen“ und „Werte“ deiner Pivot Tabelle.
Berechnetes Feld einfügen: Um ein berechnetes Feld zu erstellen, klicke auf die Pivot Tabelle und gehe zu „Pivottabel-Tools“ > „Analysieren“ > „Felder, Elemente und Gruppen“ und wähle „Berechnetes Feld“. Gib dem Feld einen Namen, z.B. „Marge“, und füge die Formel ein (z.B. =1-(COS/Umsatz)).
Wertfeldeinstellungen anpassen: Ziehe das neue berechnete Feld in den Wertebereich. Klicke mit der rechten Maustaste darauf, wähle „Wertfeldeinstellungen...“ und setze es auf „Mittelwert“ oder „Summe“, je nach Bedarf.
Summenzeile aktivieren: Gehe zu „PivotTable-Analyse“ und aktiviere die Summenzeile, um die Gesamtwerte deiner Pivot Tabelle anzuzeigen.
Formel für Summenzeile anpassen: Nutze die Pivot Funktion Excel, um die Summen der Zeilen korrekt darzustellen. Beachte, dass die Reihenfolge der Berechnungen wichtig ist; Summen sollten vor der Berechnung von Prozenten erfolgen.

Häufige Fehler und Lösungen

Fehler: Summen werden nicht korrekt angezeigt: Stelle sicher, dass die Daten in der Pivot Tabelle in der richtigen Reihenfolge verarbeitet werden. Überprüfe die Einstellungen deines berechneten Feldes.
Fehler: Formel in Pivot einfügen funktioniert nicht: Du kannst keine Formeln direkt in die Pivot Tabelle einfügen. Verwende stattdessen berechnete Felder.
Problem mit der Excel-Version: In älteren Versionen von Excel sind möglicherweise nicht alle Funktionen verfügbar, die du brauchst. Überlege, das Add-In PowerQuery zu installieren, um zusätzliche Möglichkeiten zu erhalten.

Alternative Methoden

Power Query: Nutze Power Query, um Zeilen zu summieren und die gewünschten Werte zu berechnen. Lade deine Daten in Power Query und führe die benötigten Berechnungen dort durch, bevor du sie in deine Pivot Tabelle überträgst.
Formeln außerhalb der Pivot Tabelle: Wenn die Pivot Tabelle zu komplex ist, kannst du auch die notwendigen Berechnungen außerhalb der Pivot Tabelle vornehmen und die Ergebnisse dann in deine Excel Tabelle einfügen.

Praktische Beispiele

Beispiel 1: Angenommen, du hast eine Pivot Tabelle mit Umsatzdaten. Du möchtest die Marge berechnen. Füge ein berechnetes Feld „Marge“ mit der Formel =1-(C Umsatz / B Umsatz) ein, um die prozentuale Marge darzustellen.
Beispiel 2: Wenn du die Summe der Werte aus zwei Spalten benötigst, erstelle eine neue Spalte in deiner Datenquelle und summiere die Werte dort, bevor du die Pivot Tabelle aktualisierst.

Tipps für Profis

Summenzeile fixieren: Wenn du die Excel Summenzeile fixieren möchtest, kannst du dies über die Optionen in den Pivot Table Einstellungen tun. Das sorgt dafür, dass die Summen immer sichtbar bleiben, während du durch die Pivot Tabelle scrollst.
Verwende benannte Bereiche: Wenn du häufig mit bestimmten Daten arbeitest, erstelle benannte Bereiche für deine Daten. Das erleichtert die Verwaltung und Referenzierung in Formeln.
Nutze die Filterfunktion: Filtere deine Daten in der Pivot Tabelle, um spezifische Informationen schneller zu finden. Dies kann dir helfen, die Berechnungen präziser und effizienter zu gestalten.

FAQ: Häufige Fragen

1. Wie kann ich die Summenzeile in einer Pivot Tabelle fixieren?
Um die Summenzeile zu fixieren, gehe zu den PivotTable-Optionen und aktiviere die Summenzeile unter den Layout-Optionen.

2. Kann ich in einer Pivot Tabelle eine Formel einfügen?
Du kannst keine Formeln direkt in die Pivot Tabelle einfügen. Verwende stattdessen berechnete Felder, um die gewünschten Berechnungen durchzuführen.

3. Was ist der Unterschied zwischen der Berechnung des Mittelwerts und der Summe?
Der Mittelwert ist das Ergebnis der Summierung aller Werte geteilt durch die Anzahl der Werte, während die Summe einfach die Gesamtsumme aller Werte darstellt. In der Pivot Tabelle kann die Reihenfolge der Berechnung entscheidend sein.

Beliebteste Forumthreads (12 Monate)

Forumthread: Pivot Summenzeile mit Formel

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Pivot Summenzeile mit Formel in Excel

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread