Gezielt sortieren für YTD-Auswertungen So sortieren Sie in Excel gruppierte Datumswerte in einer PivotTable

PivotTables sind als Werkzeug für funktionelle Berichte ungemein praktisch. Mit wenigen Klicks können Sie z.B. eine umfangreiche Kostenaufstellung übersichtlich nach Monaten oder Quartalen gruppieren. Einziger Wermutstropfen: Die Datumswerte einer gruppierten Liste lassen sich nicht formatieren. Wer statt abgekürzter Monatsnamen (Jan, Feb, …, Dez) ein anderes Datumsformat wünscht – z.B. mit zusätzlicher Jahreszahl –, muss zu dem von Ignatz Schels gezeigten Trick greifen.

Gezielt sortieren für YTD-Auswertungen So sortieren Sie in Excel gruppierte Datumswerte in einer PivotTable

PivotTables sind als Werkzeug für funktionelle Berichte ungemein praktisch. Mit wenigen Klicks können Sie z.B. eine umfangreiche Kostenaufstellung übersichtlich nach Monaten oder Quartalen gruppieren. Einziger Wermutstropfen: Die Datumswerte einer gruppierten Liste lassen sich nicht formatieren. Wer statt abgekürzter Monatsnamen (Jan, Feb, …, Dez) ein anderes Datumsformat wünscht – z.B. mit zusätzlicher Jahreszahl –, muss zu dem von Ignatz Schels gezeigten Trick greifen.

Die Datumsgruppierung in der Excel-PivotTable bietet die Möglichkeit, Datumswerte nach Monaten, Quartalen und Jahren zu gruppieren. Dieses Werkzeug ist für die Praxis sehr nützlich, hat allerdings einen kleinen Schönheitsfehler: Gruppierte Monatsnamen nehmen keine Zahlenformatierung an, das von Excel verwendete abgekürzte Format (Jan, Feb, … Dez) trotzt allen Formatierversuchen. Dieser Tipp zeigt, wie Sie gruppierte Datumswerte in PivotTables mit Hilfe eines Tricks trotzdem passend formatieren können. Auf diese Weise schaffen Sie die Basis für funktionelle Berichte, wie z.B. das in Bild 1 gezeigte kumulierte YTD-Chart.

Eine individuelle Formatierung gruppierter Datumswerte

Bild 1: Eine individuelle Formatierung gruppierter Datumswerte (hier: zusätzliche Jahreszahl) ist in PivotTables nur mit einem Trick möglich.
Bild vergrößern

Eine PivotTable mit Monatsauswertung

Die für das Beispiel verwendete Liste (Bild 2, links) führt die täglich anfallenden Projektkosten auf. Um die Kosten in einer PivotTable nach Monaten zusammenzufassen und aufzusummieren, gehen Sie wie folgt vor:

  1. Setzen Sie den Zellzeiger in die Liste und wählen Sie Einfügen / Tabellen / PivotTable.
  2. Bestätigen Sie den Listenbereich als Quellbereich und schalten Sie unter "Legen Sie fest …" auf die Option Vorhandenes Arbeitsblatt um.
  3. Klicken Sie auf eine freie Zelle neben der Liste (im Beispiel $F$1) und bestätigen Sie mit OK.
  4. Ziehen Sie in der Feldliste der neu erstellten PivotTable das Datumsfeld in den Bereich "Zeilen" und das Projektkostenfeld in den Bereich "Werte".
  5. Markieren Sie das erste Datum in der PivotTable mit der rechten Maustaste und wählen Sie Gruppieren. Markieren Sie Monate und bestätigen Sie mit OK.

Sollten die Projektkosten nicht durchgehend numerisch sein, verwendet das Wertefeld automatisch die Funktion ANZAHL, um die Datensätze zu zählen. Öffnen Sie in diesem Fall die Wertfeldeinstellungen (rechte Maustaste auf das Wertefeld) und schalten Sie auf die Funktion SUMME um.

Achten Sie darauf, dass die Datumsspalte außer der Überschrift ausschließlich Datumswerte enthält. Die PivotTable verweigert die Gruppierung, wenn in der Spalte Leerzeilen, Texte oder Fehlermeldungen enthalten sind. Sie erhalten dann die Fehlermeldung: "Kann den markierten Bereich nicht gruppieren"

Nach Monaten gruppierte Datumswerte in der PivotTable

Bild 2: Nach Monaten gruppierte Datumswerte in der PivotTable
Bild vergrößern

Die abgekürzten Monatsnamen (Jan, Feb, … Dez) lassen sich nicht umformatieren. Der Versuch, dem Feld im Zeilenbereich ein anderes, benutzerdefiniertes Zahlenformat wie MMMM (ausgeschriebener Monat) oder MMM JJ (Monat und abgekürztes Jahr) zuzuweisen, wird zwar akzeptiert, die PivotTable zeigt das Format aber nur für ungruppierte Datumswerte an.

Schritt 1: Passend formatierte Datumswerte bereitstellen

Liste in Tabelle umwandeln

Um das Datumsfeld der PivotTable nach eigenen Wünschen zu formatieren, müssen Sie zunächst die Datenquelle, also die Liste mit den Projektkosten aufbereiten. Am besten wandeln Sie die Liste dazu in eine Tabelle um. Sie bietet die Möglichkeit, zusätzliche Berechnungen auch in verknüpfte Daten (z. B. ODBC-Imports oder SAP-Queries) einzubinden. Setzen Sie dazu den Zellzeiger in die Liste und wählen Sie Einfügen / Tabellen / Tabelle. Bestätigen Sie den Listenbereich und die aktivierte Option "Tabelle hat Überschrift".

Hilfsspalten für formatierte Datumswerte einfügen

  1. In die Tabelle fügen Sie zwei zusätzliche Hilfspalten für die individuell formatierten Datumswerte ein.
  2. Markieren Sie mit der rechten Maustaste die Spalte B und wählen Sie Zellen einfügen. Tragen Sie für die neue Spalte als Überschrift "Monat" ein.
  3. Schreiben Sie in der Zelle B2 die folgende Formel, wobei Sie den Bezug auf [@Datum] mit Klick auf A2 herstellen:
    =TEXT([@Datum];"MMMM")
  4. Fügen Sie eine weitere Hilfsspalte zwischen B und C ein, geben Sie dieser die Überschrift "MMM JJ" und berechnen Sie das Datum mit dieser Formel:
    =TEXT([@Datum];"MMM JJ")

Argumente der verwendeten Formel

TEXT()
=TEXT(Zellbezug;Zahlenformat)

Damit haben Sie die Tabelle als Datenquelle für die PivotTable vorbereitet. Je nach persönlichem Bedarf können Sie weitere Spalten einfügen und das Datum mit der TEXT()-Funktion in beliebigen anderen Formaten berechnen.

Verwenden Sie keine Verknüpfungen auf die Datumsspalte, die mit Zahlenformaten präpariert werden. Das Ergebnis sieht zwar genau so aus, in der PivotTable werden diese Verknüpfungen aber wie das Datum selbst behandelt, d. h. das individuelle Zahlenformat wird bei Gruppierungen nicht akzeptiert.

Um alternative Datumsformate anzuzeigen, fügen Sie entsprechende Hilfsspalten in die Tabelle ein

Bild 3: Um alternative Datumsformate anzuzeigen, fügen Sie entsprechende Hilfsspalten in die Tabelle ein.

Schritt 2: Benutzerdefinierte Sortierlisten erzeugen

Sortierlisten in leerem Tabellenblatt vorbereiten

Bewertungen und Kommentare

Diese Funktion steht nur eingeloggten Nutzern zur Verfügung. Jetzt einloggen
2 Kommentare anzeigen & selbst mitreden!
Gesamt
Bewertungen 6
Kommentare 2

Alle Kommentare (2)

Annette
Weber

Das Ganze geht auch etwas einfacher: Man fügt in die Tabelle eine weitere Spalte ein mit der Formel =DATUM(JAHR(A3);MONAT(A3);1) (mit A3 als Datumsquelle, dann auf die gesamte Spalte erweitern) => So wird jedes Datum auf den Monatsersten gesetzt. Anschließend formatiert man diese Spalte auf ein Format ohne Tagesanzeige, z.B. "Mrz. 01". Diese Spalte kann man dann in der Pivottabelle für eine Gruppierung nach Monaten verwenden, eine eigene Sortierliste ist nicht notwendig.

 

Ignatz
Schels

An sich ein guter Trick, mit =DATUM(JAHR([Datum];MONAT([DATUM];1) den Monatsersten zu berechnen und mit benutzerdefiniertem Zahlenformat zu formatieren. In der Pivot wird mit der Aktualisierung aber das Zahlenformat entfernt, auch bei der Gruppierung nach Monaten verschwindet die individuelle Datumsformatierung und es wird nur der abgekürzte Monat (Jan, Feb, Mar) angezeigt. Die Umwandlung des Datums in Text ist für die Pivot immer zu empfehlen, weil damit die automatische Datumserkennung ausgeschaltet wird.