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.

21 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
  4. Katrin
    Katrin sagte:

    Hallo,
    danke für die übersichtliche Darstellung!
    Ich habe ein Problem zu dem ich noch keine Lösung gefunden habe. Meine Tabelle ist ähnlich aufgebaut wie dein Beispiel „% des übergeordneten Zeilenergebnisses“, also mit mehreren (in meinem Fall 3) Zeilenebenen.
    Die Anzahl der Einträge pro Zeile habe ich, aber ich hätte gerne die Anzahl der Einträge einer bestimmten Kategorie, die aber die unterste Zeilenebene betrifft. Also in deinem Beispiel Die Summe aller Umsätze unter Deutschland, aus allen Monaten zusammen gezählt.
    Hast du dafür eine Idee? Ich könnte das Feld natürlich als Spalte definieren, dann fehlt mir aber die Zeilengliederung – die soll erhalten bleiben. Danke!

    Antworten
    • Dustin
      Dustin sagte:

      Hallo Katrin,

      mir ist leider kein Weg bekannt Gesamtergebnisse lediglich für einzelne Attribute einer untergeordneten Zeilenebene auszugeben.

      Ich sehe hier auch nur die Möglichkeit eines der Felder in den Spaltenbereich zu übernehmen. Siehe hierzu folgendes Beispiel mit einem 3. Zeilenfeld.

      Pivot Beispiel

      Vermutlich entspricht das aber bereits deinem Ansatz.

      Viele Grüße
      Dustin

      Antworten
  5. Phil
    Phil sagte:

    Hallo zusammen,

    beim Erstellen einer Pivot-Tabelle, möchte ich in den Werteinstellungen das Wertfeld von Anzahl auf Summe ändern. Hier kommt aber immer die Fehlermeldung:

    „! Wir können dieses Feld nicht mit ‚Summe‘ zusammenfassen, da dies keine unterstützte Berechnung für Text-Datentypen ist!

    Können Sie mir helfen, damit ich wieder die Summen angezeigt bekomme.
    Danke
    Phil

    Antworten
    • Dustin
      Dustin sagte:

      Hallo Phil,

      schau dir bitte nochmal die Spalte in der Datengrundlage an, in der die Werte stehen. Für mich klingt es danach, dass die Werte als Text und nicht als Zahl formatiert sind. Zahlen, die als Text formatiert sind, können in Pivot Tabellen nicht summiert, sondern lediglich gezählt werden.

      Viele Grüße
      Dustin

      Antworten
  6. Maike
    Maike sagte:

    Hallo,
    ich habe folgendes Problem:
    Ich habe Werte mit den Feldeinstellungen „Mittelwert vo xy“, doch wenn ich in meiner Tabelle mir nur einen Teil der Spalten anzeigen lasse, diese Auswahl dann wieder ändere springt die Feldeinstellung automatisch zu „Summe von xy“ zurück. Wie kann ich das verhindern?
    Vielen Dank im Voraus.
    BG
    Maike

    Antworten
    • Dustin
      Dustin sagte:

      Hallo Maike,

      in einer einfachen Pivot Tabelle wird durch das aus- und wieder einblenden eines Feldes immer wieder die Standard-Aggregation „Summe“ von Excel benutzt.

      Möchtest du grundsätzlich ein Feld für den Mittelwert zur Verfügung haben, musst du die Daten ins Datenmodell laden. In Power Pivot kannst du dann ein sogenanntes Measure in der DAX-Formelsprache erzeugen. In diesem Fall also einfach den Mittelwert der entsprechenden Spalte berechnen. Anschließend ist das Feld in der Feldliste verfügbar und kann in der Pivot Tabelle eingeblendet werden.

      Um die Daten ins Datenmodell aufzunehmen, wählst du bei der Erstellung der Pivot Tabelle (der Schritt, indem der Datenbereich ausgewählt wird) die unterste Checkbox (Dem Datenmodell diese Daten hinzufügen) an. Danach über den Menübandreiter „Power Pivot“ ein neues Measure erstellen.

      Ich hoffe die Antwort hilft dir weiter.

      Viele Grüße
      Dustin

      Antworten
  7. Steffi
    Steffi sagte:

    Hallo,

    wie kann ich doppelte Namen sauber anzeigen z.B. Ömer Toprak, Hasan Toprak, Stefan Toprak.
    Pivot zeigt mich die Namen Gruppiert an und ich kann anschließend die Werte nicht Auf oder absteigend sortieren. Wie komm ich zur Lösung?

    Antworten
    • Dustin
      Dustin sagte:

      Hallo,

      um keine doppelten Namen zu aggregieren, sollte der komplette Name (Vor- und Nachname) in einer Spalte stehen. Anschließend kann über den Filter des Zeilenfeldes die Summe oder Anzahl der Tore auf- oder absteigend sortiert werden.

      Viele Grüße

      Antworten
  8. Stefan
    Stefan sagte:

    Hallo, ich habe mehrere Spalten wo ich die Monate Jan.-Dez. zusammenzähle.
    Was mir fehlt ist die Summenbildung.
    Wenn ich Jan und Feb anklicke soll die Gesamtsumme nur Jan+Feb angezeigt werden. Wie bekomme ich das hin?

    Antworten
    • Dustin
      Dustin sagte:

      Hallo Stefan,

      damit das funktioniert, musst du die Daten in der Grundlagentabelle entpivotieren.

      Die Anordnung der Daten sollte dann so aussehen, wie in der rechten Tabelle.

      Entpivotieren

      Dann stellt die Pivot Tabelle automatisch die Summe als Ergebnis dar.

      Viele Grüße
      Dustin

      Antworten
  9. Chadi Chehab
    Chadi Chehab sagte:

    Hallo Dustin,
    ist es in einer Pivot-Tabelle möglich in den Zellen eigene Änderungen vorzunehmen wie z.B. einen Prognosewert selbst zu schreiben?
    Vielen Dank im Voraus

    Antworten
    • Dustin
      Dustin sagte:

      Hallo,

      in einer Pivot-Tabelle selbst ist es leider nicht möglich, Zellen manuell zu ändern oder Werte wie Prognosen direkt einzutragen. Pivot-Tabellen ziehen ihre Daten aus einer zugrunde liegenden Datenquelle und dienen lediglich zur Analyse und Darstellung dieser Daten.

      Eine Möglichkeit wäre aber die Prognosewerte direkt in der Datenquelle zu ergänzen, sofern möglich, um diese in der Pivot-Tabelle nutzen zu können.

      Viele Grüße
      Dustin

      Antworten

Hinterlasse einen Kommentar

An der Diskussion beteiligen?
Hinterlasse uns deinen Kommentar!

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert