Excel-TippJahresbudget mit Power Query auf Monate aufteilen
Es liegen die Planzahlen für ein Jahr vor und Sie müssen diese gleichmäßig auf die Monate aufteilen. Kein Problem mit Power Query. Mit wenigen Klicks erzeugen Sie für jeden Monat eine einzelne Zeile, ohne dass Sie „Kopierorgien“ oder umständliche Formeln einsetzen müssen.
Die folgende Abbildung soll als Beispiel dienen. Sie sehen in der Liste Jahresbudgets für einzelne Abteilungen, die gleichmäßig auf die Monate aufgeteilt werden sollen. Für jeden Monat soll eine Zeile mit einem Zwölftel des Jahresumsatzes erstellt werden.
Laden Sie die Daten als Erstes in Power Query hoch.
Klicken Sie auf eine Zelle in der Tabelle und aktivieren Sie im Menüband die Befehlsfolge Registerkarte Daten > Befehlsgruppe Daten abrufen und transformieren > Befehl Aus Tabelle/Bereich.
Die Daten werden in den Power-Query-Editor hochgeladen.
Neue Spalte mit den einzelnen Monaten für jede Abteilung erzeugen
Fügen Sie als Nächstes eine benutzerdefinierte Spalte hinzu, indem Sie im Menüband die Befehlsfolge Spalte hinzufügen > Benutzerdefinierte Spalte ausführen.
Es öffnet sich das Dialogfeld Benutzerdefinierte Spalte. Vergeben Sie bei Neuer Spaltenname den Namen Monat und erfassen Sie unter Benutzerdefinierte Spaltenformel die Formel
={1..12}
Mit dieser Formel erstellen Sie eine Liste von 1 bis 12. 1 stellt den Anfang und die 12 das Ende der Liste. Bestätigen Sie Ihre Einstellungen, indem Sie das Dialogfeld durch Klick auf OK schließen.
Hinweis: Wollen Sie andere Listen erstellen, dann ändern Sie diese beiden Parameter einfach entsprechend ab.
Es wird nun in jeder Zeile in der neuen Spalte eine Liste von 1 bis 12 hinterlegt. Sie können diese Liste einsehen, wenn Sie auf eine Zelle in der neuen Spalte mit der linken Maustaste klicken. Sie bekommen die Liste unten links dargestellt.
Klicken Sie jetzt in der benutzerdefinierten Spalte oben rechts auf das Symbol mit den zwei Pfeilen, um die hinterlegte Liste auszuklappen. Sie bekommen daraufhin zwei Befehle angezeigt.
Wählen Sie hier den Eintrag Auf neue Zeilen ausweiten aus.
Für jeden Eintrag in der Liste wird jetzt eine entsprechende Zeile in der Abfrage erstellt. Die entsprechenden Inhalte in den anderen Spalten werden entsprechend dupliziert.
Jahresbudget auf Monate verteilen
Jetzt können Sie für jeden einzelnen Monat den entsprechenden Wert berechnen.
Fügen Sie erneut eine benutzerdefinierte Spalte hinzu, indem Sie im Menüband die Befehlsfolge Spalte hinzufügen > Benutzerdefinierte Spalte ausführen.
Es öffnet sich das Dialogfeld Benutzerdefinierte Spalte. Erfassen Sie unter Neuer Spaltenname den Namen Monatsbudget.
Da in der Spalte Budget sich der Jahreswert befindet, können Sie nun den entsprechenden Monatswert berechnen, indem Sie den Wert der Spalte Budget durch 12 dividieren.
Erfassen Sie daher unter Benutzerdefinierte Spaltenformel die folgende Formel:
=[Budget]/12
Bestätigen Sie Ihre Einstellungen, indem Sie das Dialogfeld durch Klick auf OK schließen.
Sie erhalten nun in jeder Zeile das entsprechende Monatsbudget in der neuen Spalte dargestellt.
Jetzt können Sie die Spalte Budget mit den Jahreswerten entfernen. Klicken Sie hierzu einfach mit der rechten Maustaste auf den Spaltennamen und wählen Sie im Kontextmenü den Eintrag Entfernen aus.
Des Weiteren können Sie das Zahlenformat der Spalte Monatsbudget in Dezimalzahl anpassen. Klicken Sie hierzu am linken Rand der Spaltenbeschriftung mit der linken Maustaste auf das Formatsymbol und wählen Sie in der Liste den Eintrag Dezimalzahl aus.
Hinweis: Entsprechend können Sie auch das Format „Währung“ wählen. In diesem Fall werden die Zahlen der Spalte „Monatsbudget“ mit zwei Nachkommastellen angezeigt. Power Query führt die Berechnungen mit Währungen immer mit vier hinterlegten Nachkommastellen aus, um Rundungsfehler zu vermeiden.
Jetzt können Sie die aufbereiteten Daten wieder nach Excel laden, indem Sie im Menüband die Befehlsfolge Start > Schließen & laden klicken.
Sie erhalten daraufhin die aufbereiteten Monatsbudgets in einer separaten Tabelle in Excel dargestellt. Die Zahlenwerte in Spalte C der folgenden Abbildung können Sie noch in das Währungsformat übertragen.
