DATUM

Zeitachse für PivotTabelle einfügen (analog Slicer/Datenschnitt)

Roman Pivot , , , , , , , ,

Analog der im Beitrag "Slicer/Datenschnitt einfügen" präsentierten Methode, PivotTabellen übergreifende "Filter" und Schaltflächen einzufügen, stelle ich Dir hier die "Zeitachse" vor. Es besteht also auch die Möglichkeit, einen Datumsfilter Pivot-übergreifend zu erstellen. Voraussetzung dazu ist, dass Deine Datenquelle ein Datum aufweist. Als Beispiel habe ich Dir hier eine mögliche Datenquelle:

Sobald Du eine Pivot-Tabelle erstellt hast, kannst Du via der Menüleiste unter "PivotTable-Tools" → "Analysieren" eine Zeitachse einfügen:

zeitachseeinfuegen

Nun erhältst Du unmittelbar neben der Pivot ein neues Objekt: Die Zeitachse.

zeitachsefull

Wie erwähnt, dient die Zeitachse als Filter für Deine Daten. Einerseits kannst Du eine Selektion mit den Reglern auf der linken und rechten Seite der Daten machen. In der rechten oberen Ecke ist zudem eine Auswahl aufklappbar (siehe Bild unten). Darin kann - je nach Datenquelle - die Granularität des Filters eingestellt werden. Selektierst Du beispielsweise Quartale oder Jahre, wird die Zeitachse entsprechend angepasst.

zeitachseselektion

Mehrere PivotTabellen verknüpfen

Im anfangs verlinkten Beitrag "Slicer/Datenschnitt einfügen" habe ich aufgezeigt, dass man die Filter mehrerer PivotTabellen verknüpfen kann. Dies gilt auch für Zeitachsen. Selektiere hierzu Deine Zeitachse, gehe auf "Zeitachsentools" in der Menüleiste → "Berichtsverbindungen". Voraussetzung ist, dass Du mehrere PivotTabellen mit der gleichen Datenquelle hast! Nachdem die Verbindung zu mehreren PivotTabellen hergestellt wurde, kann der Filter auf der Zeitachse einmal gestellt werden, die Tabellen reagieren dann sofort auf die getätigte Selektion.

Automatische Datumsaggregation in Pivots (Excel 2016)

Roman Pivot , , , , , , , , , , , ,

Der Fokus in diesem Beitrag liegt auf einer Änderung für Pivot-Tabellen in Excel 2016 gegenüber älteren Versionen - und zwar werden Datumsformate neu automatisch aggregiert. Schauen wir uns zuerst einen Ausschnitt der Datenbasis an, bevor wir zum Vergleich kommen:

umsatzliste

Es werden also pro Tag die Umsätze pro Produkt aufgeführt. In älteren Excel-Versionen wurden diese Daten als einzelne Tagen in den Spalten ausgewiesen:

pivotdatenalt

Excel 2016 geht nun einen Schritt weiter und führt die Daten automatisch zu Jahren zusammen (in den Grafiken oben wurden der Übersicht halber weniger Einträge gezeigt):

pivotdatumsaggregationneu

Zudem bietet sich die Möglichkeit, die Pivot-Tabelle weiter aufzuklappen - es werden Quartale oder darin auch die jeweiligen Monate angezeigt, wenn man auf das "+" klickt.

Dies ist sicherlich ein tolles Feature. Allerdings muss beachtet werden, dass die zusätzlichen Dimensionen die Zeilen der effektiven Werte nach unten verschieben. Falls Du also in früheren Versionen mittels Formeln Bezug auf die Pivot-Tabelle genommen hattest, musst Du die Verschiebung entsprechend berücksichtigen. Falls Du nach wie vor das alte Format beibehalten möchtest, kannst Du in der Feldliste unter "SPALTEN" die automatisch generierten Felder "Years" und "Quarters" via Drag-And-Drop entfernen (oder in der Auswahlliste oben den Haken entfernen):

pivottablefelderaggregation

Einen eingefärbten Kalender erstellen

Roman Allgemein, Formeln , , , , , , , , , , , , , ,

Wer kennt das nicht: Es beginnt ein neues Kalenderjahr und sämtliche Dateien wie Personaleinsatzplanung, Ferienpläne, Projektübersichten, Terminlisten, etc. müssen für das neue Jahr aufgesetzt werden. In diesem Blog zeige ich Dir, wie Du dies mithilfe von vereinzelten Formeln sowie bedingten Formatierungen ganz schnell hinbringst!

Du kannst Dir dafür gerne direkt diese Datei (Farbiger_Kalender.xlsx) herunterladen. Alternativ kannst Du nachfolgendes Bild selbst nachkonstruieren (Beschreibungen dazu folgen unmittelbar):

In der Zelle "B1" gibst Du das gewünschte Jahr ein, in "B2" den entsprechenden Monat. Unterhalb wird Dir mittels der Formel "DATUM" die Kombination aus Tag (fortlaufende Nummer im Bereich  "D3:D33") sowie dem Monat und dem Jahr in "B2" respektive "B1" erstellt. Daraus können wir in der Spalte "E" mittels der Formel "TEXT" den jeweiligen Wochentag ermitteln (im Beitrag Dynamische Titel (Formel "TEXT") haben wir bereits angeschaut, wie man den Wochentag von einem beliebigen Datum auslesen kann).

Hier zusammengefasst noch einmal die bereits erwähnten Formeln:

[In der Zelle "B3" und dann entsprechend angewendet auf "B3:B33"]
=DATUM($B$1;C3;D3)
[In der Zelle "C3" und dann entsprechend angewendet auf "C3:C33"]
=MONAT(1&$B$2)
[In der Zelle "E3" und dann entsprechend angewendet auf "E3:E33"]
=TEXT(B3;"TTTT")

Die bedingte Formatierung zur farblichen Hervorhebung von Samstagen und Sonntagen

Das einzige, was jetzt noch fehlt, ist die Markierung von den Wochenenden. Hierzu verwenden wir bedingte Formatierungen. Falls Du die oben bereitgestellte Datei verwendest, klicke beispielsweise die Zelle "E3" an und gehe dann via "Start" in der Menüleiste auf "Bedingte Formatierung" und dort auf "Regeln verwalten...". Auf den Bereich "$D$3:$K$33" wenden wir zwei Formeln an. Falls der jeweilige Wert in der Spalte "E" ein Samstag respektive Sonntag ist, werden die Zellen von "D" bis "K" grau eingefärbt. Weitere Informationen findest Du, wenn Du auf "Regel bearbeiten" klickst.

Wie erstellt man man so eine Formel selbst? Falls Du das gerne nachkonstruieren möchtest, gehst Du wie folgt vor: Selektiere zum Beispiel die Zelle "E3" und gehe dann via "Start" → "Bedingte Formatierung" auf "Neue Regel...". Nimm dann die Einstellungen in der Grafik unten vor. Diese sind: Wähle einerseits "Formel zur Ermittlung der zu formatierenden Zellen verwenden" als Regeltyp aus und gebe dann folgende Formel ein:

=$E3="Samstag"

Danach kannst Du via dem Button "Formatieren..." dein gewünschtes Format auswählen.

Formatierungsregel

Wichtig zu erwähnen ist an dieser Stelle, dass der Bezug unbedingt "$E3" sein muss. Wir setzen dadurch die Spalte "E" absolut. Dies bedeutet, dass die Regel in beispielsweise der Zelle "K3" ebenfalls überprüft, ob eben in dieser Zelle "E3" "Samstag" steht. Die Zeilennummer ist nicht fixiert, was bedeutet, dass in der Zelle "K6" zu "E6" referenziert wird. Nach dem Erstellen dieser Regel können wir via "Start" → "Bedingte Formatierung" → "Regeln verwalten..." bei "Wird angewendet auf" den Bereich =$D$3:$K$33 eintragen. Diese Schritte wiederholst Du dann für Sonntag, indem Du eine weitere Regel anlegst.

Falls Du weniger oder mehr Spalten eingefärbt haben möchtest (da Du beispielsweise mehrere Mitarbeitende oder Projekte erfassen möchtest), kannst Du einfach Deine Regeln verwalten und den Bereich von =$D$3:$K$33 auf =$X$3:$X$33 erweitern!

... und nun kannst Du in den Zellen "B1" und "B2" andere Monate und Jahre eingeben und hast direkt die Wochenende eingefärbt!

Wie kann ich das nun verwenden?

Diese Frage ist natürlich abhängig davon, was Du genau für eine Anwendung dafür hast. Falls Du nicht alle Informationen von diesem Tabellenblatt beibehalten möchtest, kannst Du entweder vereinzelte Zellen/Spalten ausblenden oder wie folgt vorgehen: Der einfachste Weg, damit Du diese Datei verwenden kannst, ist, den gewünschten Monat auszuwählen und dann mit einem Rechtsklick auf das Tabellenblatt via "Verschieben oder kopieren..." eine Kopie zu erstellen. Wenn Du nun den Bereich "B3:E33" auswählst und kopierst und im Anschluss darüber nur "Werte" einfügst, bleiben die Formatierungen erhalten! Du kannst die übrigen Spalten "A" bis "C" sowie die Zeilen eins und zwei löschen. Danach wiederholst Du den Vorgang für weitere Monate/Jahre. Zum Beispiel für den Monat Februar wirst Du zusätzliche vorige Zeilen unterhalb haben, diese kannst Du natürlich ebenfalls entfernen.

P.S.: Falls Du eine englische Version von Excel verwendest, müsstest Du selbstverständlich die Monate in "B2" auf Englisch eingeben, die Formel "TEXT" leicht anpassen ("TTTT" wird zu "DDDD") und die bedingte Formatierung von "Samstag" auf "Saturday" und von "Sonntag" auf "Sunday" ändern.