Pivot-Tabellen sind in Excel eine wirklich feine Angelegenheit. Leider haben viele Leute völlig zu Unrecht einen Heidenrespekt davor (falls auch du zu ihnen gehören solltest, empfehle ich dir diesen Einsteiger-Artikel).
Ein besonders praktisches Feature möchte ich dir heute vorstellen. Wenn die Ausgangstabelle nämlich viele verschiedene Datumswerte enthält und man seine Pivot-Tabelle z.B. nach Monaten oder Quartalen verdichten möchte, geht das mit wenigen Klicks.
Und zwar so:
Die Ausgangstabelle
Ich habe meinem Beispiel die Besucherstatistiken für ein paar fiktive Websites zugrunde gelegt (ich bitte um Nachsicht bei der phantasielosen Namenswahl). Die Beispieldatei kannst du dir bei Bedarf hier herunterladen. Für jeden einzelnen Tag wird also aufgelistet, wieviele Menschen die jeweilige Seite besucht haben:
Beispiel: Besucherstatistik
Mit wenigen Klicks erstellen wir daraus eine Pivot-Tabelle.
1. Tabelle markieren und Pivot-Tabelle einfügen:
Pivot-Tabelle einfügen
Pivot-Tabelle in neuem Blatt
2. Felder dazufügen
Dazu ziehen wir mit der Maus einfach die einzelnen Felder oben aus der Feldliste nach unten in den jeweiligen Bereich: „Datum“ in Zeilen, „Website“ in Spalten und „Nutzer“ in Werte.
Felder dazufügen
Felder dazufügen (Fortsetzung)
Das Ergebnis ist schon nicht schlecht, aber wie man unschwer erkennen kann, ist die Pivot-Tabelle aufgrund der vielen einzelnen Datumsangaben ziemlich lang und daher nicht gerade übersichtlich:
Pivot-Tabelle im Rohzustand
Das wollen wir im nächsten Schritt ändern.
Werte gruppieren
In diesem Schritt werden wir mit minimalem Aufwand die Einträge nach Monaten, Quartalen oder Jahren verdichten. Dazu müssen wir die aktive Zelle zunächst irgendwo in die Pivot-Tabelle stellen, so dass die Pivottable-Tools im Menüband angezeigt werden:
Pivottable-Tools
Nun klicken wir auf einen beliebigen Datumswert (welcher davon, ist völlig egal), so dass in der Registerkarte „Analysieren“ die Werkzeuggruppe „Gruppieren“ aktiviert wird:
Datumsfeld markieren
Ein Klick auf die Schaltfläche „Gruppenfeld“ öffnet ein Dialogfenster, in dem ich nun die gewünschten Einstellungen für meine Gruppierung vornehmen kann. Ich wähle hier z.B. die Werte „Monate“ und „Jahre“:
Gewünschte Gruppierung auswählen
Und schon habe ich mein eine wunderbar verdichtete Pivot-Tabelle erstellt:
Verdichtete Pivot-Tabelle
Zum Schluss noch etwas Kosmetik und unsere Auswertung kann sich sehen lassen:
Noch etwas Kosmetik
War doch gar nicht so schwer, oder?
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo Martin,
ich habe ein Problem mit einer Pivot-Tabelle. Ich habe eine Datentabelle, die mehrere Ebenen zur Gruppierung beinhalten (Ebene 01-10). In der Pivat-Tabelle sollen aber nur die Ebenen angezeigt werden, die auch vorhanden sind – bei mir werden diese mit „leer“ angezeigt.
Vielleicht das ganze an einem Beispiel, ist leider etwas schwer zu erklären:
Beispiel der Datentabelle: Name, Zahl1, Zahl2, Ebene 1 (Planet – Erde), Ebene 2 (Kontinent – Amerika, Europa), Ebene 3 (Nordamerika, Südamerika, Deutschland), Ebene 4 (in Deutschland – Bundesländer – Bayern, in Amerika reicht Ebene 3).
Wie kann ich die Pivot-Tabelle einstellen das „leer“e Ebenen unter der „höheren“ Ebene verdichtet werden?
Irgendwo muss es doch diese Einstellung geben, im Excel kann man doch alles irgendwo einstellen, nur gefunden habe ich die Einstellung bisher leider noch nicht.
Vielleicht kannst du mir einen Tipp dazu geben.
Gruß,
Nicole
Hallo Martin,
ich würde gerne die Datenquelle für den Zugriff über PIVOTDATENZUORDNEN dynamisch gestalten.
Leider funktioniert in Office 2010 die an anderer Stelle vorgeschlagene Lösung den Bezug als TEXT umzuwandeln nicht.
Beispiel: =PIVOTDATENZUORDNEN(Text($A$2;““);PIVOT!$A$1;“AS“;D$5;“FG“;$A13;“Sparte“;“Neu“;“Artikel“;“Sonder“)
Hast Du eine andere Lösung?
MfG
Heiko
Hallo Heiko,
ich habe Dein Beispiel gerade ausprobiert und bei mir funktioniert die Lösung mit TEXT sowohl unter Excel 2007 als auch 2013 (2010 kann ich momentan nicht testen). Daher kann ich nicht genau sagen, wo das Problem liegt.
Wichtig ist in der Textfunktion nur, dass zweimal die doppelten Anführungszeichen verwendet werden. Ansonsten habe ich leider keinen anderen Tipp…
Schöne Grüße,
Martin
Hallo, das ist schon einmal klasse.
Ich muss eine YTD-Auswertung machen, sprich den Stand zum aktuellen Monat mit den aktuellen Ständen der jeweiligen vorherigen Jahre vergleichen. pro Monat habe ich die Daten, auch der Vorjahre, kämpfe jetzt aber damit jeweils die Daten der Vorjahre bis zum jeweiligen Vorjahres- bzw. Vorvorjahresmonat abzugrenzen um mir dann direkt die Auswertung zu diesen zeigen zu lassen.
Ich habe es jetzt versuct, indem ich pro Jahr eine Gruppe über die Monate gebildet habe – ich wollte mir jetzt einfach die Teilergebniss je Gruppe einblenden und bis zu dem jeweiligen Monat – in diesem Fall August-die Daten filtern. Ist zwar immer noch umständlich, aber schon einmal besser, als alles jeden Monat neu zu berechnen. Gibt es da einen Trick?
Dankeschön.
Hallo SRö,
wenn die Quelldaten nicht schon eine YTD-Information enthalten, wüsste ich momentan auch keinen besseren Weg, als über eine manuelle Gruppierung der Monate zu arbeiten.
Schöne Grüße,
Martin
Hallo Martin,
ich suche zu Pivot-Chart etwas. Hier besonders Kreisdiagramm. Und da speziell Daten der 2. oder n. Spalte darstellen.
Habe EXCEL 2016 und bekomme immer nur die erste Spalte angezeigt, egal, was ich markiere.
Hallo Gerhard,
in einem Kreisdiagramm lässt sich grundsätzlich nur ein Datenreihe darstellen. Für mehrere Datenreihen brauchst Du mehrere Kreisdiagramme. Einzige Ausnahme: Der Diagrammtyp „Kreis aus Kreis“ stellt zwei Kreise dar. Dabei werden die Elemente eines Kreissegments nochmal in einem zweiten Kreis aufgesplittet. Trotzdem ist die Basis immer nur eine Datenreihe.
Schöne Grüße,
Martin
Sorry, Martin. Das ist klar.
Ich habe das Problem, dass es mir nicht gelingt, in Pivot die zweite oder n-te Datenreihe in das Diagramm zu bekommen.
Auch mit der Markierung der 2. Datenreihe (wie das in einer normalen Tabelle geht) wird die erste angezeigt.. was mache ich nicht richtig?
Danke für einen Tipp.
Hallo Gerhard,
in Pivot-Diagrammen musst die entsprechenden Filter innerhalb des Diagramms verwenden. Den Datenbereich an sich kannst Du nicht verändern, der erstreckt sich immer über die ganze Pivot-Tabelle. Aber über den Filter kannst Du festlegen, welche Datenreihe angezeigt wird.
Schöne Grüße,
Martin
Hallo Martin, ich bin ebenfalls begeisterte Excel-Anhängerin 🙂 Jedoch ohne jegliche Ausbildung
Ich habe bei der Gruppierung von Daten in Pivot das Problem, dass die Gruppierungen automatisch in alle Pivots, die in einer Datei enthalten sind und die gleiche Quelle haben, übernommen werden.
Gibt es eine Möglichkeit das auszuschalten?
Viele Grüße
Nicole
Hallo Nicole,
ja, die Möglichkeit gibt es. Sie ist aber nicht mit ein zwei Sätzen erklärt. Das Stichwort hierzu lautet Pivot-Cache. Nur soviel in Kürze:
Wenn eine Pivot-Tabelle erstellt wird, legt Excel im Hintergrund eine Kopie der Quelltabelle an, den sogenannten Pivot-Cache (sprich: „Käsch“). Die Pivot-Tabelle greift aus Performancegründen dann auf diesen Cache zu und nicht auf die eigentlichen Quelldaten. Mehrere Pivot-Tabellen, die auf der gleichen Quelltabelle basieren, teilen sich einen Pivot-Cache. Und genau daher kommt das von dir beschriebene Problem mit den Gruppierungen.
Um das zu vermeiden, muss man bei die Pivot-Tabellen bei der Erstellung dazu zwingen, jeweils einen eigenen Pivot-Cache zu verwenden. Aber wie gesagt, da braucht es ein paar Sätze mehr zur Erklärung. Daher hast du jetzt drei Möglichkeiten:
1. Googeln
2. Warten, bis ich mal einen Artikel darüber schreibe
3. Mein Buch „Excel Pivot-Tabellen für Dummies“ kaufen 🙂
Schöne Grüße,
Martin
Hallo Martin,
wir arbeiten auf Windows 365.
Ich habe bei dem Versuch, Datumsfelder zu gruppieren, das Problem, dass ich das Auswahlfeld über die rechte Maustaste gar nicht erst angezeigt bekomme. Angeblich „kann der markierte Bereich nicht gruppiert werden“, obwohl ich bereits alles Erdenkliche getan habe, um ihn als einen von Datumsfeldern zu formatieren. Z.B das Datum in der zugrunde gelegten Datenbasis mit 1 multipliziert, dort bereits als TT.MM.JJ formatiert, gleiches in der Pivot Tabelle auch noch mal. Ergebnis: Siehe oben.
Auch in der Befehlszeile der PivotTable-Tools ist der Befehl „Feld gruppieren“ nicht anwählbar.
Was kann ich noch tun?
Gruß
Rolf
Hallo Rolf,
ich vermute einmal ganz stark, dass in der Datumsspalte deiner Quelltabelle ungültige Datumswerte oder leere Zellen enthalten sind. Nur wenn jede Zelle ein gültiges Datum enthält, ist eine Gruppierung möglich.
Vielleicht liegt es ja daran.
Schöne Grüße,
Martin
Volltreffer, funktioniert, super!
Vielen Dank.
Gruß Rolf
Danke!!! 🙂 Und frohes gesundes neues Jahr!
Danke Ira, das wünsche ich dir auch!
Super hilfreich, vielen Dank!
Gern geschehen!
Schöne Grüße,
Martin
Hallo Martin, vielen Dank für diesen Vorschlag. Leider kann er meine Frage nicht beantworten. Wahrscheinlich findet sich in den Tiefen des Netzes auch irgendwo eine Antwort, ich habe sie jedoch nicht gefunden. Ich habe eine Excel-PIVOT-Tabelle, in der viele Datenzeilen und eine Spalte mit Datumsangaben enthalten sind. In der Orginaltabelle „Tabelle 1“ besteht die Möglichkeit, die Datumsdaten im Autofilter bereits auf Jahre/ gefiltert zu sehen (also der Autofilter oben). In der Pivot in der „Tabelle 2“ ist das irgendwie nicht möglich. Hier stehen die Daten auf Tagesebene. Was muss eingestellt oder getan werden, damit auch in der Pivot oben im Autofilter gleich die Gruppierung auf Jahre (und dann durch Selektion auf Tagesebene aufklappbar). Ich möchte nicht die Daten in den Zeilen zu Jahren gruppieren, sondern über den Autofilter die Sicht generieren. Ich hoffe, dass geht? Vielen Dank und Grüße, Frank
Hallo Frank,
du kannst in der Pivot-Tabelle das Datumsfeld gruppieren. Ich glaube, ab Excel 2013 (oder 2016?) erfolgt diese Gruppierung bei Datumsfeldern automatisch, sobald man das Feld im Zeilenbereich in einer Pivot-Tabelle verwendet. Also ziehe es mal temporär vom Filter- in den Zeilenbereich. Wenn die Gruppierung automatisch erfolgt, sollten jetzt weitere Felder für Jahr, Quartal etc. entstehen. Falls nicht, dann gehe über die PivotTable-Tools, Register Analysieren. Dort gibt es die Schaltfläche „Feld gruppieren“, wo man die gewünschte Gruppierung vornehmen kann. Danach kannst du das gewünschte Datumsfeld wieder in den Filter ziehen.
Schöne Grüße,
Martin
Hallo Martin,
das Gruppieren nach Datum funktionier bei mir in Excel 2013 lediglich, wenn dem Datenmodell nur eine Tabelle zugrunde liegt.
Sobald das der Pivot zugrunde liegende Datenmodell aus mehreren (dem Datenmodell über Datenverknüpfung hinzugefügten) Tabellen besteht, ist das Gruppieren nach Datum leider nicht aktiv.
Gibt es eine Möglichkeit, auch in dieser Konstellation eine Gruppierung nach Datum hinzubekommen?
Vorab schon mal: Es ist mir nicht möglich, das Datum in beiden Tabellen abzubilden, denn die Tab1 Umsatzzahlen (pro Datum) und die Tab2 die Verkäuferstammdaten enthalten (Verknüpfung läuft über Verkäufer ID).
Beste Grüße
Yvonne
Hallo Yvonne,
sobald das Datenmodell verwendet wird (mit mehreren Tabellen), ist eine Gruppierung tatsächlich nicht möglich, da Excel die Daten im Hintergrund anders verwaltet, als in einer normalen Pivot-Tabelle. Es gibt Lösungen dafür in Power Pivot, aber das würde hier bei weitem den Rahmen sprengen.
Tut mir leid, wenn ich hier keine Lösung anbieten kann.
Schöne Grüße,
Martin
Hallo Martin,
ich habe folgende Frage, ist es möglich alle Ebenen gleichermaßen zu sortieren? Vielleicht an einem Beispiel:
Ich habe eine Pivot-Tabelle erstellt (Spaltenbeschriftung = Datum; Werte = Summe, also der Umsatz; Zeilenbeschriftung = der jeweilige Mitarbeiter, seine Kunden und die Produkte die der Kunde bestellt hat) Nun kann ich die Mitarbeiter und die Kunden nach Umsatz absteigend sortieren, die Produkte aber nur immer einzeln unter dem jeweiligen Kunden, sobald ich die Artikel eines anderen Kunden absteigend sortieren möchte verhaut es mir die andere Sortierung wieder. Gibt es eine Möglichkeit alle Umsätze (der ganzen Tabelle) absteigend zu sortieren? Bin dankbar für jeden Tipp.
Liebe Grüße
Celina
Hallo Celina,
wenn ich dich richtig verstanden habe, dann sollen die Umsätze absteigend nach den Mitarbeitern, dann innerhalb der Mitarbeiter absteigend nach den Kunden und schließlich innerhalb der Kunden absteigend nach Produkt sortiert werden?
Das geht schon. Du musst nur der Reihe nach beginnen, zuerst mit den Mitarbeitern, dann mit den Kunden und danach mit den Produkten. Und zwar jedesmal über einen Rechtsklick auf die Spaltenüberschrift, dann auf „Sortieren“ und dann auf „Weitere Sortieroptionen…“
Und dort markierst du jedes Mal „Absteigend (Z bis A) nach:“ und wählst im Dropdown das Feld mit dem Umsatz aus.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank, mit der neusten Excel-Version hat das auch gleich super geklappt, ich habe mit einer älteren gearbeitet, da wollte Excel mir das nicht übernehmen.
Nun habe ich auch gesehen, dass man die Spalte mit dem Gesamtergebnis ganz einfach ein- und ausblenden kann. Kann man auch andere Spalten ein- und ausblenden lassen, wie z.B. Abweichungen in € und %? Ich kann das zwar manuell berechnen lassen, jedoch überimmt es mir dann für die beiden Spalten das Layout nicht, gibt es dort eine Möglichkeit das Layout auch auf diese beiden Spalten zu übertragen?
vielen Dank für deine Hilfe!
Liebe Grüße
Celina
Hallo Celina,
ein- und ausblenden lassen sich immer nur die Ergebniszeilen oder -spalten.
Die Abweichungen können auch innerhalb der Pivot-Tabelle berechnet werden. Dazu ziehst du das jeweilige Feld nochmal in den Wertebereich. In den Wertfeldeinstellungen kannst du im Register „Werte anzeigen als“ dann aus einer ganzen Reihe von Berechnungsoptionen auswählen, z.B. % des Gesamtergebnisses, Differenz von….
Hier musst du einfach ein wenig probieren.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für den Artikel. Ich steige mit meiner Problematik an Punkt 2. “Felder dazufügen“ deines Artikels ein. Wenn ich in einem Pivot eine Datumsspalte aus der Datenquelle in der Berichtsfilter packe werden mir in diesem die Daten im Format dd.mm.yyyy alle einzeln angezeigt. Bei einer entsprechend Anzahl an Daten wird dies sehr schnell unübersichtlich. Hier mochte ich die “gruppierten” Datumsfilter verwenden. Jedoch habe ich im Internet vergeblich nach einer Lösung gesucht. Es werden lediglich die Filter der Pivot Spalten, wie in deinem Artikel, angesprochen.
Um es eventuell einfacher auszudrücken. Ich mochte nicht die Filter an den Spalten und Zeilen im Pivot, sondern die “Datümer” im Berichtsfilter verdichten.
Ich bitte um Hilfe.
LG
Marcel
Hallo Marcel,
der Trick ist, das Datumsfeld erst im Zeilenbereich zu verwenden. Dort findet die Gruppierung entweder automatisch statt oder kann von Hand vorgenommen werden. Und danach ziehst du das Feld in den Filterbereich und kannst dort auf die gruppierten Werte zugreifen.
Schöne Grüße,
Martin
Hallo Martin,
erst einmal, ein großes Lob für deine Homepage – da steckt sicher einiges an Arbeit drin!
Darf ich dich trotzdem um deine Unterstützung bitten?
In meiner Projektkosten – Tabelle habe ich pro Arbeitspaket ein Startdatum (H2) und ein Enddatum (G2), woraus sich die „Durchlaufzeit in Arbietstagen“ (mit dieser Formel: =WENN(((H2-G2)<=1);1;NETTOARBEITSTAGE(G2;H2)) ) ergibt. Aus den "Kosten pro Arbeitspaket" dividiert durch die "Druchlaufzeit in Arbeitstagen" bekomme ich die Kosten pro Tag.
Nun zu meiner Frage: Wie komme ich über eine Pivot und einem daraus resultierenden Diagramm zur Aussage, in welchem Monat welche Kosten anfallen?
Vielen Dank für deine Hilfe und schöne Grüße,
Anna
Hallo Anna,
dankeschön für das nette Feedback.
Eine Auswertung auf Monatsbasis wird schwer möglich sein, denn wenn ich deine Beschreibung richtig interpretiere, dann können die Arbeitspakete vermutlich auch monatsübergreifend sein. Und somit wären auch die Kosten verschiedenen Monaten zuzurechnen. Das ließe sich über Pivot nur darstellen, wenn die Datentabelle pro Arbeitspaket und Monatsanteil eine eigene Zeile enthalten würde. Ansonsten sieht es eher schlecht aus.
Schöne Grüße,
Martin
hallo Martin,
tolle Tipps lieferst du hier, danke! Mit meine XLS-Problem hab ich schon Stunden getüfftelt, aber finde keine elegante Lösung. Ich arbeite mit Scrum und habe eine einfache Elementtabelle mit Aufwänden pro Planungselement (Stories), zugeordnet zu Zeitperioden (Sprints), schon erledigt oder gerade aktuell oder geplant, kategorisiert in übergeordnete Planungselemente (Features). Die Features sind der Fremdschlüssel der Stories (Datenmodell/Power Pivot) und tragen auch eine Aufwandschätzung. Nun suche ich ein eigentlich einfachen Report: Features von oben nach unten und von links nach rechts die zugehörigen Stories. Spalten: 1) Aufwand Features, 2)…n): schon gemacht, n+1): Summe 2)…n), n+2….Ende: noch geplante Stories pro Sprint, Ende: Gesamtsumme aller Stories pro Feature (= Zeilen). Diese Gesamtsumme kann von der Zahl auf dem Feature abweichen. Daraus möchte ich noch einige Spalten rechnen z.B. Pro Feature = Zeile den Wert auf dem Feature minus Summe aller Stories. Die Anzahl Sprints =Spalten wird laufend grösser, es ist also eine langsam wachsende Datenmenge. Bisher habe ich drei Pivots identischer Zeilenstruktur: einmal nur Features, einmal alle Stories in den Sprints als Spalten inkl. Gesamtsummenspalte, ein Pivot mit einem Datenschnitt für YTD bzw. aktueller Stand. Aktuell hab ich einen Report gebaut mit fixem Bezug auf das Feature-Pivot und die Rechnungsspalten. Die Gesamtsumme und die Summenspalte des Datenschnitts muss ich immer reinkopieren, das müsste doch eleganter gehen. Gruppieren geht im Datenmodell bekanntlich nicht (so einfach). Vielleicht hast du mir einen einfachen Tipp. Herzlichen Dank schon mal!
Hallo Peter,
wie du dir sicher vorstellen kannst, ist es sehr schwierig, dein Szenario von außen im Detail nachzuvollziehen. Am Ende der Beschreibung sprichst du von einem fixen Bezug auf das Feature-Pivot. Vielleicht ist das ein Punkt, wo man ansetzen kann. Statt eines Zellbezugs, der sich in einer Pivot-Tabelle vermutlich ständig ändert, könntest du mit der Funktion PIVOTDATENZUORDNEN dynamisch auf die jeweiligen Elemente der Pivot-Tabelle zugreifen, auch wenn diese sich in der Position ändern.
Ich hatte darüber mal einen Artikel geschrieben: Flexibler Zugriff auf Pivot-Tabellen
Vielleicht hilft das ja ein wenig weiter.
Schöne Grüße
Martin