Office-Blog

Pivot: So funktioniert die Datumsgruppierung nach Wochen

22.12.2017   Hildegard Hügemann

Im Blogbeitrag vom 7.11.2017 habe ich die automatische Datumsgruppierung ab Excel 2016 und deren Ecken und Kanten erläutert.
Dazu erhielt ich die Frage, ob eine wochenweise Gruppierung ebenfalls möglich sei.
Zwar hält Pivot standardmäßig keine Gruppierungsebene nach Wochen bereit, aber das Manko lässt sich wie folgt beheben:

  • Entweder durch eine spezielle Gruppierung innerhalb der Pivot-Tabelle
  • oder durch eine zusätzliche Spalte in der Datenbasis
Gruppierung nach Wochen und nach Kalenderwochen

Pivot-Berichte mit Gruppierung nach Wochen (links) und nach Kalenderwochen (rechts)

Mit diesem Trick gelingt die Datumsgruppierung nach Wochen

In der Pivot-Tabelle sind per Rechtsklick in die Datumsspalte Gruppierungen nach Jahren, Quartalen, Monaten und Tagen verfügbar. Für Wochen gibt es keinen Eintrag. Der Trick: Die Einstellung nach Tagen wählen und dann aufpeppen. Hier die Schritte im Detail:

  • In den Datenbestand klicken und über Einfügen > PivotTable und OK ein neues Arbeitsblatt für die Pivot-Tabelle erzeugen.
  • In der PivotTable-Feldliste das Feld Datum in die Zeilenbeschriftungen ziehen.
  • Ab Excel 2016 wird per Rechtsklick in die Datumsspalte der Pivot-Tabelle zunächst die Gruppierung aufgehoben.
  • Ein rechter Mausklick in die Datumsspalte der Pivot-Tabelle, Gruppieren wählen.
  •  Im folgenden Dialog bei Nach: Tage und bei Tage anzeigen: 7 einstellen.
  • Den Wochenanfang auf einen Montag setzen über Starten: und Eintragen des Montags vor dem ersten Datum der Datenliste.
Wochenweises Gruppieren: Als Startdatum den Montag vor dem ältesten Datum wählen und die Tage-Gruppierung auf 7 einstellen

Wochenweises Gruppieren: Als Startdatum den Montag vor dem ältesten Datum wählen und die Tage-Gruppierung auf 7 einstellen

Im Ergebnis dessen werden Wochen-Zeiträume angezeigt, ohne dass zuvor eine zusätzliche Berechnung in der Datenbasis erfolgen muss.

So sieht das Ergebnis aus: Eine perfekte Wochengruppierung ohne zusätzliche Spalte in der Datenbasis

So sieht das Ergebnis aus: Eine perfekte Wochengruppierung ohne zusätzliche Spalte in der Datenbasis

Geht auch: Nach Kalenderwochen auswerten

Und was, wenn eine Gruppierung nach ISO-Kalenderwochennummer benötigt wird?
Das lässt sich über eine Zusatzspalte in der Datenbasis lösen werden. Hier die Schritte:

  • In der Datenbasis eine neue Spalte namens KW hinzufügen.
  • Dort folgende Formel eingeben (in Abhängigkeit von der Excel-Version):
    =KALENDERWOCHE(A3;21) …bis Excel 2013
    =ISOKALENDERWOCHE(A3) … ab Excel 2016
  • Die Formel per Doppelklick auf das Ausfüllkästchen nach unten kopieren.
In der Datenbasis eine neue Spalte KW einrichten und den Pivot-Bericht danach gruppieren

In der Datenbasis eine neue Spalte KW einrichten und den Pivot-Bericht danach gruppieren

Anschließend wir die Pivot-Auswertung wie folgt angelegt:

  • Klick in den Datenbestand und Einfügen > PivotTable und OK.
  • In der PivotTable-Feldliste das Feld KW in die Zeilenbeschriftungen ziehen.
  • Die Pivot-Tabelle mit weiteren Feldern und Filtern ausstatten.

Im Ergebnis dessen werden nach ISO berechnete Kalenderwochennummern angezeigt.

Das Ergebnis: Eine Gruppierung nach ISO-Kalenderwoche durch Einrichten einer Zusatzpalte in der Datenbasis

Das Ergebnis: Eine Gruppierung nach ISO-Kalenderwoche durch Einrichten einer Zusatzpalte in der Datenbasis

Tipp: Sind Daten aus mehreren Jahren auszuwerten, empfiehlt sich eine weitere Spalte »ISOJahr« in der Datenbasis.
Über sie ist dann auch ein Gruppieren oder ein Filtern nach Jahr möglich.
Die Formel =JAHR(A3-WOCHENTAG(A3;3)+3) liefert die korrekte Jahresberechnung nach ISO.

Funktioniert auch ohne Zusatzspalte: Kalenderwochenberechnung nach ISO in Pivot

Mein Kollege Dominik Petri zeigt in einem weiteren Blogbeitrag eine Lösung unter Verwendung des Datenmodells.

 

Über den Autor

Hildegard Hügemann

- Dipl.-Informatikerin
- Zertifizierte Office-Trainerin mit den Schwerpunkten Excel, Access, Word
- Entwicklerin von Excel-Tools zur Projekt-, Produktions- und Verkaufssteuerung
- Coach bei der Datenaufbereitung mit Access, Power Query und Power Pivot
- Entwicklerin von Access-Datenbanken für Mittelständler und Behörden
- Bloggerin zu Excel und Access auf www.huegemann-informatik.de
- Autorin für Microsoft Press, dpunkt.verlag und das »Projekt Magazin«

3 Comments so far

Michael KrattPosted on  3:04 pm - Jan 10, 2020

Kleiner Hinweis:
=ISOKALENDERWOCHE(A3) … steht AB EXCEL 2013 und nicht erst „ab Excel 2016“ zur Verfügung!

NettissaPosted on  1:33 pm - Mai 22, 2019

Hallo Larissa,

wahrscheinlich musst du die zuvor ausgewählte (blau hinterlegte) „Monats-Gruppierung“ abwählen. Du kannst erst die Anzahl der Tage ändern, wenn nur noch Tage gruppiert werden (und natürlich grundsätzlich Datumswerte in deinem Set enthalten sind).

Gruß

LarissaPosted on  2:00 pm - Mrz 4, 2019

Hallo,
ich habe versucht in meiner Pivot-Tabelle die Daten nach Kalenderwochen zu sortieren (über Gruppierung). Allerdings kann ich bei „Tage anzeigen“ keine Änderungen vornehmen, also nicht die 1 auf 7 ändern. Können Sie mir sagen, woran das liegen könnte?
Vielen Dank