Wer tief in den Auswertungsmöglichkeiten von Pivot Tabellen rumstochert, stößt im Wertfeldeinstellungen Fenster irgendwann auf die Möglichkeit die Werte nach “Index” anzeigen zu lassen. Etwa 2 Sekunden nachdem man diese Variante ausprobiert stellt sich unweigerlich folgende Frage: “Was zum #@%!? macht Excel da?”
Eine berechtigte Frage!
Dem gehen wir im heutigen Beitrag auf den Grund!
Wenn man Index verstehen will, dann geht das am besten anhand eines Beispiels.
Doch zuerst, wie zeigt man Werte als “Index” überhaupt an?
Wie man Werte in einer Pivot Tabelle als “Index” anzeigt
Wir erstellen eine Pivot Tabelle anhand erfundenen Daten. Diese fassen wir zusammen mit einfachen Summen Werten. Eine Pivot Tabelle wie wir sie täglich sehen.
Die Index Anzeige aktivieren wir folgendermassen: auf irgendeinen Wert in der Pivot Tabelle ein rechts-klick machen und die Option “Wertfeldeinstellungen…” auslesen. Das Wertfeldeinstellungen Fenster öffnet sich. Im zweiten Reiter “Werte anzeigen als” finden, dann “Index” an unterster Stelle im Dropdown Menü auswählen.
Wenn wir OK drücken sehen wir neu folgende Werte in der Pivot Tabelle:
Zurück zu unserem Beispiel!
Pivot Tabellen Index Erklärt
Es hilft wenn wir uns das Ganze mit einer Geschäftsfrage erläutern.
Deshalb hier erst nochmals die Tabelle:
Die Geschäftsleitung ist sich am Überlegen ob man “Schneeketten” oder “Tagfahrlicht” gänzlich aus dem Sortiment streichen soll.
In absoluten Zahlen haben diese Produkte in der Gesamtergebnis Spalte beide den gleich tiefen Wert. Betrachtet man die Frage nur aus dem Blickwinkel “Gesamtumsatz”, so würde es Sinn machen eines dieser beiden Produkte zu streichen.
Schauen wir nun aber auch noch auf die Länder in denen es verkauft wird, so sehen wir das Schneeketten und Tagfahrlichter überproportional zum Umsatz der Schweiz beitragen. Würde eines dieser beiden Produkte aus dem Sortiment genommen, würde der Umsatz in der Schweiz also stark einbrechen.
In der Pivot Tabelle mit Summen ist dies nicht sofort offensichtlich.
Anders in derjenigen mit der Index Anzeige!
Mit Index sehen wir den Einfluss jedes Wertes sowohl in der jeweiligen Spalte wie auch in der Zeile. Je höher der Index Wert, desto stärker die Gewichtung des Wertes auf das gesamt Spalten Ergebnisses und auch auf das gesamt Zeilen Ergebnisses!
Die Produktsichtweise (ergänzt durch Index):
Wenn wir Schneeketten nun anschauen, sehen wir dass der höchste Index Wert auf dem Produkt bei der Schweiz liegt (2.73). Dies bedeutet, das Produkt ist am wichtigsten für den Schweizer Markt. Und dies obwohl in absoluten Werten, Deutschland (7) gleich viel verkauft hat wie die Schweiz (7). In der Schweiz würde das Wegfallen dieses Produktes zu einem bedeutenderen Umsatzverlust führen.
Genau gleich verhält es sich bei Tagfahrlichter. Auch hier wurde wieder gleich viel verkauft in Deutschland (8) wie in der Schweiz (8). Aber der Index zeigt wieder den Einfluss auf die Gesamtumsatzwerte.
“Schneeketten” und “Tagfahrlicht” haben beide einen hohen Index Wert für die Schweiz. Dies bedeutet es sind die wichtigsten Produkte die dort verkauft werden.
Der höchste Index Wert in einer Zeile gibt in diesem Beispiel Ausschluss darüber für welches Land ein Produkt besonders wichtig ist.
Ein weiteres Beispiel: Obwohl in Frankreich weniger als halb so viele Sitzheizungen verkauft werden wie in Deutschland (7 vs. 15), ist das Produkt für die französischen Verkäufer viel wichtiger als für die deutschen Kollegen (1.33 vs. 1.14).
Pivot Tabellen Index Berechnung erklärt
Würde Index eine Einfache Gewichtung berechnen, würden wir nur das Gesamtergebnis als 1.00 haben, in der Tabelle oben, sehen wir aber das sowohl Spalten, Zeilen, wie auch das Gesamtergebnis alle 1.00 sind.
Wie also lautet die Formel welch in der Pivot Tabelle benutzt wird?
Dies ist die Formel zum Berechnen eines gewichteten Durchschnittes. Ein gewichteter Durchschnitt ist hilfreich um fest zu stellen wie stark sich ein Ergebnis auf zwei Verschiedene Kategorien in einer Matrix auswirken.
Das Beispiel könnt Ihr Euch hier in einer Datei anschauen: