Jahreskalender „Spezial”, nur Dienstag & Freitag und keine Feiertage
Wissensstand: Level 1 ⇒ Einsteiger in PQ, keine/kaum Erfahrung
In einem Forum wurde die Anfrage gestellt, wie mittels VBA ein Jahreskalender erstellt werden kann, wo ausschließlich die Wochentage Dienstag und Freitag enthalten sind. Und als „Sahnehäubchen” sollen auch jene verbliebenen Tage nicht im Kalender erscheinen, wenn sie ein Feiertag sind. Also muss (in Deutschland auf jeden Fall) der Karfreitag aus der Tabelle entfernt werden. Eine Teil-Lösung in VBA lag vor, es haperte aber daran, dass die Feiertage nicht berücksichtigt worden sind und in der Liste geblieben sind.
Ich hatte die Gelegenheit ergriffen und den Fragesteller darauf hingewiesen, dass in VBA die Funktion WorksheetFunction.CountIf durchaus hilfreich sein könnte. Gleichzeitig bot ich ihm an, das Ganze in Power Query zu lösen. Und hier nun der Lösungsweg, der eher auf Transparenz als auf „Eleganz” aufbaut.
Erstellen Sie zu Beginn in einer beliebigen Excel-Mappe eine Liste / Tabelle welche die Feiertage des entsprechenden Jahres enthält. Nutzen Sie wegen der beweglichen Feiertage dazu gerne diesen oder jenen Beitrag aus unserem Blog. In diesem File ist bereits eine Feiertags-Tabelle erstellt, welche automatisch die Daten für das aktuelle Jahr enthält. Einzige Voraussetzung: Das File muss ein Mal im aktuellen Jahr geöffnet und wieder gespeichert worden sein, damit die Formeln neu berechnet werden.
Import der Feiertage
Sie erstellen ein neues, leeres Workbook und aktivieren das Register Daten (bzw. in älteren Versionen Power Query). Nun Daten abrufen | Aus Arbeitsmappe | und im Daten importieren – Dialog wählen Sie das entsprechende Verzeichnis und dort das File mit den Feiertage-Daten, beispielsweise Feiertage_berechnung_xls.xlsx oder jenem Namen, den Sie Ihrer *.xlsx gegeben haben.
Nach dem Klick auf Importieren öffnet sich der Navigator-Dialog. Schauen Sie sich die 3 Varianten Tabelle1 (Tabelle/Liste), Feiertage (Tabellenblatt) und Feiertage1 (Benannter Bereich) durch einen einfachen Klick auf den jeweiligen Eintrag an; im rechten Bereich des Fensters sehen Sie eine Vorschau der zu importierenden Daten:
Auswahl und Vorschau der zu importierenden Daten
Entscheiden Sie sich für eine Möglichkeit. Vorzugsweise sollte dieses Tabelle1 oder Feiertage1 sein; je nachdem, ob Sie nur die kalendarischen Daten nutzen wollen oder auch die Feiertagsbezeichnungen. Der Bequemlichkeit wegen wähle ich Feiertage1. Erweitern Sie die Schaltfläche Laden 
und wählen Sie Laden in…
Im Daten importieren-Dialog markieren Die die Option Nur Verbindung erstellen. Dadurch wird sich rechts ein Seitenfenster öffnen, wo in einem Feld der Name der Abfrage mit dem Vermerk Nur Verbindung aufgezeigt wird. Der Excel-Datenbereich bleibt dadurch leer, obwohl die Abfrage erhalten bleibt.
Der Jahres-Kalender
Alle Tage des Jahres
Aktivieren Sie erforderlichenfalls die Zelle A1. Register Daten und dann Daten abrufen | Aus anderen Quellen | Leere Abfrage und es zeigt sich der Power Query-Editor. Geben Sie hier in das Textfeld in exakt dieser Groß- Kleinschreibung folgende Formel ein:
= List.Dates(#date(2019,1,1), 365, #duration(1,0,0,0))
Der Erfolg zeigt sich sofort, wenn Sie entweder auf das Häkchen links des Textfeldes oder irgendwo in den freien Bereich unterhalb Klicken oder Eingabe betätigen:
Die Formel steht nun in dem Textfeld der leeren Abfrage, darunter das Ergebnis
Nun noch in der Gruppe Konvertieren ein Klick auf Zu Tabelle und bestätigen Sie den folgenden Dialog einfach mit OK. Klicken Sie nun im schmalen Streifen links auf Abfragen und dieser Bereich erweitert sich zum linken Seitenfenster. Markieren Sie nun Abfrage1, F2 und vergeben Sie für diese Abfrage den Namen Datum. Übrigens: Der Name in rechten Seitenfenster wird dabei auch automatisch geändert. 💡
Nur Dienstag und Freitag
Der wohl einfachste Weg zum separieren der beiden Wochentage ist gewiss, vorübergehend eine Hilfsspalte zu erstellen. Markieren Sie die einzige Überschrift Column1 und aktivieren Sie dann das Register Spalte hinzufügen. In der Gruppe Aus Datum und Uhrzeit erweitern Sie die einzig aktive Auswahl Datum und dann Tag | Name des Tags. Ruck zuck schreibt PQ den passenden Wochentag in die zweite Spalte:
Power Query hat den Wochentag in eine neue Spalte geschrieben
Erweitern Sie nun die Spalte Name des Tags, Häkchen bei (Alles auswählen) entfernen und anschließend bei Dienstag und Freitag die Markierung setzen. Sie können jetzt schon einmal eine Zwischenspeicherung vornehmen: Register Datei, Schließen & laden in… und wählen Sie hier die Option Bestehendes Arbeitsblatt. Da Sie zu Beginn die Zelle A1 markiert hatten, wird Ihnen diese Adresse auch vorgeschlagen. Bestätigen Sie mit OK. Und wahrscheinlich sind Sie jetzt gewiss mehr oder weniger irritiert:
serielle Zahl statt eines Datums 🙁
Im Prinzip ist das Ergebnis ja richtig. Nur die Darstellung ist „leicht ungewohnt”. Obwohl in Power Query das „normale” Datum angezeigt wird, sehen Sie hier die serielle Zahl des Datums. Natürlich könnten Sie hier in Excel die Formatierung auf Datum ändern, aber bei der nächsten Aktualisierung wird wieder die Zahl statt des Datums sichtbar sein. Darum öffnen Sie die Abfrage in PQ. Das geht beispielsweise über den Menüpunkt Abfrage | Bearbeiten. Die 1. Spalte ist ja markiert und im Menüband finden Sie in der Gruppe Transformieren bei Datentyp: den Eintrag Beliebig. Ändern Sie diesen Wert auf Datum und anschließend ein Klick auf Schließen & laden. Sie sehen, dass es manchmal nur eine Kleinigkeit ist, die zum Erfolg führt. 😎
Feiertage aus der Liste entfernen
Jetzt geht es noch darum, die Feiertage aus der Liste zu entfernen. Öffnen Sie die Abfrage also wieder zum Bearbeiten und erweitern Sie bei Bedarf das linke Seitenfenster. Kontrollieren Sie noch einmal durch einen Klick auf die Abfrage Feiertage1, ob auch die korrekten Daten des aktuellen Jahres in der Liste stehen. Wählen Sie anschließend wieder im linken Seitenfenster die Abfrage Datum. Im Register Start Klicken Sie nun auf die Schaltfläche Kombinieren und anschließend auf Abfragen zusammenführen. Im Zusammenführen-Dialog wählen Sie im mittleren, schmalen DropDown die Abfrage mit den Feiertagen. Ganz unten bei der Überschrift Join-Art wählen Sie Linker Anti-Join (Zeilen nur in erster). Nun noch jeweils ein Klick in die Spalte mit dem Datum (jeweils Column1) und das Ganze stellt sich so dar:
So werden die Daten korrekt zusammengeführt
Stören Sie sich nicht an der Aussage, dass die Auswahl mit 4 der ersten 105 Zeilen übereinstimmt. Damit ist gemeint, dass 4 Zeilen entfernt werden. Ein Klick auf OK und sie werden sehen, dass nur noch 101 Zeilen in der Abfrage enthalten sind:
Ganz unten in der Statuszeile ist die Anzahl der Spalten und Zeilen vermerkt
Feiertage1 ist markiert, darum Shift und ein Klick in die Überschrift Name des Tags. Nun sind beide Spalten markiert und ein Klick auf Entf wird die beiden Spalten löschen. Was jetzt noch bleibt ist die Aufgabe, die Überschrift entsprechend anzupassen. Das kann beispielsweise Datum sein oder Arbeitstage oder was auch immer sie mögen. Hinweis: Das geht beispielsweise entweder durch einen Klick in die Überschrift und dann F2 oder einen Doppelklick in die Überschrift und dann jeweils gleich den Text schreiben. Ein letztes Mal Schließen & laden und das Ergebnis ist exakt das, was sie haben wollten. Die erzeugte Tabelle im Excel-Arbeitsblatt hat sich automatisch verschlankt und die Anzahl der Zeilen ist auch korrekt angepasst worden.
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag (€ 1,00 – € 2,00) Ihrerseits freuen …