Excel-TippJahresliste mit Werten für jeden Tag erstellen

Ein Praxisbeispiel: Eine Jahresliste mit Verrechnungspreisen für jeden Tag des Jahres ist ein häufiges Anliegen in der Wirtschaftsprüfung. Für jeden einzelnen Tag eines Jahres soll aufgeführt sein, welcher Verrechnungspreis an diesem Tag galt.

Dabei dient Power Query als leistungsfähiges Tool, um vorhandene Listen – in denen jeweils das Datum der letzten Anpassung und der dazugehörige Verrechnungspreis vermerkt sind – mit allen Kalendertagen zu verknüpfen.

Beispiel: Liste mit Änderungsdatum und geändertem Wert

Sie haben eine Tabelle, in der jede Zeile einen Datensatz mit einem Datum (dem Tag, an dem eine Anpassung vorgenommen wurde) und dem dazugehörigen Verrechnungspreis enthält. Der Anspruch des Wirtschaftsprüfers ist es, für jeden Tag im Jahr den gültigen Verrechnungspreis anzuzeigen.

Dabei soll der zuletzt angepasste Preis bis zum aktuellen Tag angewendet werden.

Daten in Power Query importieren

Importieren Sie zunächst Ihre bestehende Liste in Power Query. Markieren Sie eine Zelle in der Liste und aktivieren Sie im Menüband die Befehlsfolge Registerkarte Daten > Befehlsgruppe Daten abrufen und transformieren > Befehl Aus Tabelle/Bereich.

Da es sich bei der Liste um einen normalen Bereich handelt, wird die Liste zunächst in eine intelligente Tabelle umgewandelt.

Es öffnet sich das Dialogfeld Tabelle erstellen. Der Bereich der Liste wird automatisch erkannt. Da die Liste über eine Beschriftung verfügt, muss das Kontrollkästchen Tabelle hat Überschriften aktiviert sein. Bestätigen Sie die Einstellungen durch Klick auf OK.

Die Liste wird daraufhin in den Power-Query-Editor geladen.

Datumsreihe für das Jahr erstellen

Um alle Tage des gewünschten Jahres 2024 darzustellen, müssen Sie eine Datumsreihe in einer anderen Abfrage erstellen.

Erstellen Sie zuerst eine leere Abfrage, indem Sie im Menüband die Befehlsfolge Registerkarte Start > Neue Quelle > Andere Quellen > Leere Abfrage ausführen.

Um alle Tage eines Jahres in einer Liste (Spalte) darzustellen, müssen Sie eine Datumsreihe generieren. Nutzen Sie hierzu die M-Funktion List.Dates.

Erfassen Sie den folgenden Code in der Bearbeitungsleiste, um eine Datumsreihe vom 01.01.2024 bis 31.12.2024 (hier das Beispieljahr 2024) zu erzeugen:

= List.Dates( #date( 2024, 1, 1 ), 366, #duration( 1, 0, 0, 0 ) )

Bestätigen Sie die Eingabe der Formel durch Enter. Daraufhin wird eine entsprechende Datumsreihe als Liste erzeugt.

Wandeln Sie die Liste jetzt in eine Tabelle um, indem Sie im Menüband die Befehlsfolge Listentools > Transformieren > Zu Tabelle ausführen.

Es öffnet sich das Dialogfeld Zu Tabelle. Lassen Sie die Einstellungen unverändert und bestätigen Sie das Dialogfeld durch Klick auf OK.

Zusammenführen der Datumstabelle mit der Wertetabelle

Im nächsten Schritt verknüpfen Sie die Datumstabelle mit der ursprünglichen Liste der Verrechnungspreise. Hierbei kommt die Join-Funktion zum Einsatz. Die Verknüpfung der beiden Tabellen erfolgt dabei über die Spalte Datum.

Wichtig ist, dass beide Spalten über den gleichen Datentyp verfügen; hier der Datentyp Datum.

Falls in einer der Tabellen ein anderer Datentyp verwendet wird (zum Beispiel Datum/Uhrzeit), dann aktivieren Sie die entsprechende Spalte und klicken Sie mit der rechten Maustaste auf die Spaltenbeschriftung.

Wählen Sie im Kontextmenü den Eintrag Datum aus.

Daraufhin wird die Uhrzeit nicht mehr in der Spalte angezeigt.

Beide Datumsspalten in beiden Abfragen verfügen jetzt über den gleichen Datentyp und können somit verknüpft werden.

Wählen Sie jetzt wieder die Abfrage1 und aktivieren Sie im Menüband die Befehlsfolge Start > Kombinieren > Abfragen zusammenführen.

Es öffnet sich das Dialogfeld Zusammenführen.

Klicken Sie bei Abfrage1 (alle Datumswerte) auf die Spalte Column1, sodass die Spalte grün markiert wird. Wählen Sie im Listenfeld darunter die Abfrage Tabelle1 aus und markieren Sie hier die Spalte Datum.

Bei Join-Art wählen Sie Linker äußerer Join aus. Das bedeutet, es werden alle Datensätze aus der Abfrage1 und die übereinstimmenden Daten aus der Tabelle1 übernommen.

Bestätigen Sie Ihre Einstellungen, indem Sie das Dialogfeld durch Klick auf OK schließen.

Es wird eine neue Spalte mit dem Namen Tabelle1 eingefügt. Klicken Sie am rechten Rand der Spaltenbeschriftung auf die zwei Pfeilchen.

Sie bekommen jetzt alle Spalten der Tabelle1 angezeigt.

Da Sie nur den jeweiligen Verrechnungspreis benötigen, deaktivieren Sie das Kontrollkästchen für die Spalte Datum.

Deaktivieren Sie auch das Kontrollkästchen Ursprünglichen Spaltennamen als Präfix verwenden und schließen Sie das Dialogfeld durch Klick auf OK.

Sie bekommen jetzt den Verrechnungspreis für die übereinstimmenden Datumswerte angezeigt. Bei den nicht übereinstimmenden Datumswerten wird der Wert null für eine leere Zelle eingetragen.

Fehlende Einträge ergänzen

Da in der ursprünglichen Liste nicht für jeden Tag ein Wert angegeben ist und die entsprechenden Zellen in der zusammengeführten Tabelle mit „null“ belegt sind, müssen Sie diese Lücken noch füllen.

Die korrekten Werte stehen in der Spalte jeweils über den leeren Zellen – bei dem Tag (Datum), zu dem der Wert ursprünglich aktualisiert wurde. Sie wollen diesen Wert also übernehmen und darunter eintragen, bis wieder ein neuer Wert bei einem Datum eingetragen ist.

Markieren Sie dazu die Spalte Wert und aktivieren Sie anschließend im Menüband die Befehlsfolge Transformieren > Ausfüllen > Nach unten.

Daraufhin werden die Werte (Verrechnungspreise) nach unten in den leeren Zellen ausgefüllt. Bei den leeren Zellen wird somit der Wert vom letzten vorherigen Verrechnungspreis eingetragen. Sie haben jetzt eine Verrechnungspreisliste für jeden Tag des Jahres.

Fast fertig. Benennen Sie jetzt noch die Spalte Column1 in Datum um. Klicken Sie mit der linken Maustaste zweimal auf den Spaltennamen und überschreiben Sie den Spaltentitel.

Jetzt können Sie die fertige Liste in Ihre Excel-Tabelle übertragen. Aktivieren Sie einfach im Menüband die Befehlsfolge Start > Schließen & laden.