Pivot-Tabellen Wertfeldeinstellungen

Zusammenfassungsfunktion und benutzerdefinierte Berechnungen in einer Pivot Tabelle

Mit Pivot Tabellen lassen sich Daten schnell und einfach zusammenfassen. Dabei werden Zahlenwerte summiert und Textwerte gezählt. Je nachdem, welches Ergebnis die Pivot Tabelle liefern soll, können aber auch andere Zusammenfassungsfunktionen notwendig sein.

So bieten Pivot Tabellen mit den Wertfeldeinstellungen weitaus mehr Möglichkeiten. Ob Mittelwert, Maximum oder Minimum, Standardabweichung… oder aber benutzerdefinierte Berechnungstypen. Welche Möglichkeiten es alle gibt, wird auf dieser Seite ausführlich erläutert.

Pivot-Tabelle-Wertfeldeinstellungen

Der Bereich um die Wertfeldeinstellungen ist etwas umfangreicher und gliedert sich daher in zwei Abschnitte. Im ersten Abschnitt werden die von Excel vordefinierten Zusammenfassungsfunktionen einzeln durchgegangen. Welche das sind, wie diese funktionieren und in welchen Fällen diese sinnvoll eingesetzt werden.

Der zweite Abschnitt umfasst die benutzerdefinierten Berechnungstypen. Auch diese werden im Detail vorgestellt und an Beispielen verdeutlicht.

Standardmäßig ist dort „Keine Berechnung“ ausgewählt. In diesem Fall bleibt in der Pivot Tabelle alles so, wie es ist.

Als nächste Option findet sich „% des Gesamtergebnisses“. Hierbei wird der prozentuale Anteil jedes Datenwertes am Gesamtergebnis berechnet. Eine sehr häufig genutzte Berechnung, um schnell ein Verteilungsverhältnis darzustellen.

Pivot-Tabelle-Wertfeldeinstellungen

Anzeige des Umsatzanteils je Land

An diesem Punkt wird noch einmal die Abhängigkeit der Berechnung, auf den zuvor gewählten Berechnungstyp deutlich. Ist der Berechnungstyp (Reiter: Werte anzeigen als) jetzt beispielsweise auf „Anzahl Zahlen“ ausgewählt, richtet sich die prozentuale Verteilung danach, wie oft der Wert vorkommt. Also das Verteilungsverhältnis nach Anzahl.

Pivot-Tabelle-Wertfeldeinstellungen

Anzeige des Umsatzanteils je Land, unter Berechnungstyp Anzahl

Steht der Berechnungstyp auf  „Summe von“, werden die Zahlenwerte ins Verhältnis zu der Gesamtsumme an Werten gesetzt.

Weiter gibt es die Möglichkeit „% des Spaltenergebnisses“ und darunter „% des Zeilenergebnisses“ zu wählen. Dabei bezieht sich der Prozentwert eines jeden Wertes immer auf das Spaltengesamtergebnis oder das Zeilengesamtergebnis. Je nachdem, wie die Bedingungen in der Pivot Tabelle gesetzt sind und welche Detailstufe angezeigt werden soll.

Pivot-Tabelle-Wertfeldeinstellungen

Prozentuale Verteilung des Umsatzes je Spalte (Land)

Das Nächste ist „% von“. Hiermit lässt sich ermitteln, wie hoch der prozentuale Anteil aller Basiselemente in Bezug auf ein fest bestimmtes Basiselement ist. Dazu muss erst ein Basisfeld gewählt werden, welches immer ein wählbares Feld aus der Feldliste ist und eine Bedingung für die Zusammenfassung von Werten bildet. Dann muss das Basiselement gewählt werden, an dem sich alle anderen Basiselemente ins Verhältnis setzen. Diese Basis entspricht 100%.

Pivot-Tabelle-Wertfeldeinstellungen

Bei der Auswahl Basisfeld: Land und Basiselement: Deutschland, bildet Deutschland die Basis mit 100% und alle anderen Werte werden dazu ins Verhältnis gesetzt

Zwei weitere Berechnungstypen heißen „% des übergeordneten Zeilenergebnisses“ und „% des übergeordneten Spaltenergebnisses“. Diese Berechnung ermöglicht es, den prozentualen Anteil eines Wertes, der in einer Zeile bzw. Spalte steht, im Verhältnis zum übergeordneten Wert zu setzen. Der übergeordnete Wert ist das Teilergebnis, dass beim Hinzufügen einer weiteren Zeilen- bzw. Spaltenebene entsteht. Wenn beispielsweise ein Feld als zweite Ebene in den Zeilenbereich gezogen wird und „% des übergeordneten Zeilenergebnisses“ auswählt wird, erhält man einmal den prozentualen Anteil an der Summe der unteren Ebene und darüber in Fett den Anteil je Gruppe an der Gesamtsumme der oberen Ebene. Auch hierbei ist je nach Anordnung der Bedingungen die Variante für Zeilen oder Spalten zu wählen.

Pivot-Tabelle-Wertfeldeinstellungen

Prozentuale Verteilung innerhalb der unteren Ebene sowie Verteilung der oberen Ebene am Gesamtergebnis

„% des übergeordneten Ergebnisses“ wird verwendet, wenn mehrere Zeilen- bzw. Spaltenebenen hinzugefügt werden. Dann lässt sich über die Auswahl des Basisfeldes bestimmen, welche Basis für die Ermittlung des Prozentsatzes genommen werden soll.

Als nächstes stehen „Differenz von“ und „% Differenz von“ zur Auswahl. Mit diesen Berechnungen wird die Differenz der Werte eines Basisfeldes in Bezug auf das gewählte Basiselement berechnet.

Wenn beispielsweise als Basisfeld „Land“ und als Basiselement „Deutschland“ gewählt wurde, wird die Differenz des Umsatzes der anderen Länder, zu dem von „Deutschland“ dargestellt. Das gleiche gilt für die Prozentwerte, bei der Auswahl „% Differenz von“.

Pivot-Tabelle-Wertfeldeinstellungen

Es wird die Differenz zu dem zuvor gewählten Basiselement ermittelt

„Laufende Summe in“ kumuliert die Werte eines Feldes auf. In der Spalte „Summe von Umsatz“ wird der Umsatz je Land zusammengefasst. Diese Werte werden in der Spalte „laufende Summe in“ Zeile für Zeile kumuliert, also mit jeder Zeile weiter aufsummiert.

Pivot-Tabelle-Wertfeldeinstellungen

Die Werte werden von Zeile zu Zeile kumuliert dargestellt

Das Gleiche geschieht mit „% Laufende Summe in“, nur als Prozentwert. Eine sehr praktische Funktion für eine Analyse nach dem Pareto-Prinzip, wie beispielsweise der ABC-Analyse.

Pivot-Tabelle-Wertfeldeinstellungen

Der prozentuale Anteil wird kumuliert dargestellt

Mit den Berechnungsoptionen „Rangfolge nach Größe (aufsteigend)“ und „Rangfolge nach Größe (absteigend)“ werden Werten in einer Pivot Tabelle sofort ein Rang zugeordnet. Je nachdem, ob aufsteigend oder absteigend gewählt wird, weist Excel einen Rang als fortlaufenden Zahlenwert zu. Gleiche Werte bekommen hierbei den gleichen Rang.

Pivot-Tabelle-Wertfeldeinstellungen

Dem größten Wert wird bei absteigender Sortierung der Rang 1 zugeteilt

Zu guter Letzt die Berechnungsoption „Index“. „Index“ dient zur Gewichtung von Werten, um ihre Bedeutung im Gesamtkontext erfassen zu können. Im Prinzip stellt der Index dar, wie groß der Einfluss einer Veränderung, beispielsweise einer Preiserhöhung, auf den Gesamtumsatz ist.

Zur Verdeutlichung habe ich eine Beispieldaten-Tabelle vorbereitet. Diese umfasst eine Liste von Artikeln mit deren Käuferdaten. Die Käuferdaten bestehen aus Geschlecht, Alter, Städte Kategorie und der Verkaufssumme.

Daraus wird im ersten Schritt eine schnelle Pivot Tabelle erstellt. Dazu wird das „Alter“ in den Zeilen-Bereich, die „Städte Kategorie“ in den Spalten-Bereich und die „Verkäufe“ in den Werte-Bereich gezogen. Die Verkäufe werden als Währung, ohne Dezimalstellen formatiert.

Diese Pivot Tabelle wird jetzt einmal kopiert und unter die andere gesetzt. Anschließend in einer der beiden Pivot Tabellen auf die Wertfeldeinstellungen -> Werte anzeigen als und „Index“ wählen, dann „OK“. Die Werte als Zahl mit 2 Dezimalstellen formatieren.

Beim Blick auf die Städte Kategorie C und deren absoluten Werten, verglichen mit den Indexwerten, ist festzustellen, dass die Altersgruppen 0-17 und 55+ die niedrigsten absoluten Werte, aber die höchsten Indexwerte haben.

Würde jetzt eine Preiserhöhung für die Städte Kategorie C, um beispielsweise 15% durchgesetzt werden, was würde passieren? Absolut betrachtet, würde sich das Umsatzplus logischerweise am stärksten in der Altersgruppe 26-35 auswirken. Aber beim relativen Plus ist das nicht der Fall.

Wird die Erhöhung um 15% für C durchgeführt, erhält man die entsprechend erhöhten Verkäufe sowohl für C, als auch für das Gesamtumsatzergebnis. Wird das Gesamtergebnis jetzt ins Verhältnis zu den Verkäufen vor der Preiserhöhung in Kategorie C gesetzt, ist festzustellen, dass in der Altersgruppe 55+ das relative Umsatzplus mit 8,95% am höchsten ist.

Und genau das zeigt die Berechnungsoption „Index“. Ich hoffe, dass die Berechnungsoption anhand  dieses Beispiels etwas deutlicher geworden ist.

Zur Nachvollziehbarkeit gibt es hier noch einen Video-Ausschnitt zum Thema Index, aus dem Pivot Tabellen Kompakt Kurs.

YouTube

Mit dem Laden des Videos akzeptieren Sie die Datenschutzerklärung von YouTube.
Mehr erfahren

Video laden

Eine weitere Option bietet das Fenster Wertfeldeinstellungen noch. Und zwar ganz unten das Zahlenformat. Hier können die verschiedenen Formate ausgewählt werden, die sonst auch über die Registerkarte Zahl -> Zahlenformate ausgewählt werden können.

9 Kommentare
  1. Ina Bourmer
    Ina Bourmer sagte:

    Wie schaffe ich es, dass nach einer Aktualisierung immer noch die von mir eingestellten Wertfeldeinstellungen vorhanden sind? Ich nutze die Summe uns dann „Werte anzeigen als“ in absteigender Reihenfolge, also die Funktion Rang. Aber nach jeder Aktualisierung steht dort wieder die Summe anstelle des Rangs 🙁

    Antworten
    • Dustin
      Dustin sagte:

      Hallo Ina,

      ich habe deinen Fall einmal versucht nachzuvollziehen. Also in einer Beispiel-Pivot-Tabelle über Wertfeldeinstellungen unter „Werte anzeigen als“ -> „Rangfolge nach Größe (aufsteigend)“, den Summenwerten eine Rangfolge zugeordnet. Beim Aktualisieren der Daten wird bei mir allerdings nichts zurückgesetzt.
      Du kannst dein Beispiel aber gerne per Mail an info@hands-on-excel.com senden. Dann kann ich dir hoffentlich eine Lösung anbieten.

      Viele Grüße

      Antworten
    • Dustin
      Dustin sagte:

      Hallo Fabian,

      in Pivot-Tabellen werden Werte immer nach dem gewählten Attribut im Zeilen- oder Spaltenfeld zusammengefasst.
      Zahlenwerte durch Summieren und Textwerte durch Zählen.
      Möchte man die einzelnen Werte nicht zusammengefasst ausgegeben bekommen, muss ein Attribut gewählt werden, welchem das gewünschte Detail zugeordnet ist. Alternativ könnte man auch das Wertfeld einfach in den Zeilen- oder Spaltenbereich verschieben.

      Ich hoffe Dir mit dieser Antwort weitergeholfen zu haben. Ansonsten bitte das Problem, am besten mit einem Beispiel, etwas konkreter beschreiben.

      Gruß Dustin

      Antworten
      • Nico
        Nico sagte:

        Hallo Dustin,
        Ich habe ein Problem, dass ich meine ich mithilfe von Pivot Tabellen gelöst werden kann.
        Ich habe einen Datensatz in dem ich verschiedene Arbeitsblöcke habe, die bestimmten Übergruppen zugeordnet sind. Beide Bestandteile sind jeweils in einzelnen Spalten mit Überschrift aufgelistet. Ich habe nun verschiedene Zeitpunkte in denen diese Arbeitsblöcke bearbeitet werden sollen. Diese Zeitpunkte sind auch (jeder einzeln) oben in der Überschriftenzeile abgetragen. Insofern der Arbeitsblock in diesem Zeitpunkt bearbeitet wird, kommt in diese Spalte ein Kreuz. Jetzt würde ich daraus gerne eine Tabelle generieren lassen, die mir eine Timeline darstellt. In der ersten Spalte hätte ich gerne die übergeordneten Begriffe der Arbeitsblöcke und daneben die Arbeitsblöcke (in dem jeweiligen Zeitslot) ausgeschrieben. Bisher habe ich es nur geschafft, dass mir meine Tabelle die Summe an Building Blocks pro Schritt ausrechnet. Ich hätte dabei aber lieber die ausgeschriebenen Blöcke. Ist das möglich? Problem ist auch die Zeitslots sind Wörter und keine Datumsangaben.

        Antworten
        • Dustin
          Dustin sagte:

          Hallo Nico,

          ich kann dir anbieten, dass du mir dein bisheriges Ergebnis einmal per E-Mail an info@hands-on-excel.com sendest und ich mir das Problem ansehe. Anhand der Beschreibung kann ich nur in etwa ahnen, was du darstellen möchtest. Ich könnte mir aber durchaus vorstellen, dass es dafür eine Lösung gibt. Die Daten kannst du natürlich gerne durch irgendwelche Beispielwerte ersetzen. Anhand eines Beispiels kann ich dir hoffentlich eine passende Lösung geben.

          Gruß
          Dustin

          Antworten
  2. Jörg Schütz
    Jörg Schütz sagte:

    In meiner Pivot-Auswertung habe ich 2 Spalten mit Werten, die sich aus einer Formel ergeben. Eine Spalte wird als SUMME angezeigt und zeigt die Werte, die 2. Spalte wird als ANZAHL angezeigt und nicht als Wert. Die Werte in der Ursprungstabelle sind als Zahlen formatiert. Was kann ich tun ??
    Besten Dank für Hilfe.
    mfg
    Jörg

    Antworten
    • Dustin
      Dustin sagte:

      Hallo Jörg,

      lässt sich die 2. Spalte nicht in den Wertfeldeinstellungen unter „Wertfeld zusammenfassen nach“ auf Summe umstellen?

      Um dein Problem besser zu verstehen und dir einen Lösungsvorschlag machen zu können, wäre es am einfachsten, du schickst dein Beispiel per Mail an: info@hands-on-excel.com

      Gruß
      Dustin

      Antworten
  3. Jörg Schütz
    Jörg Schütz sagte:

    Hallo Dustin,
    ich habe heute erst gesehen, dass Du Dich schon am gleichen Tage gemeldet hast. Vielen Dank.
    Mein Fehler war, dass ich eine WENNFEHLER(….;““) Abfrage eingebaut habe. Das kann Pivot nicht als Zahl interpretieren und stellt Text vorein.
    Ein Tip für Leidensgenossen : WENNFEHLER(…..;0) Und schon liest Pivot das als Zahl.
    mfg
    Jörg

    Antworten

Hinterlasse einen Kommentar

An der Diskussion beteiligen?
Hinterlasse uns deinen Kommentar!

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.