AW: Macro: (Variable) Jahresperioden in Monatsperioden wandeln
31.10.2024 13:23:02
Yal
Hallo Meike,
ja, es ist mit PQ nicht ganz einfach. Die Handlung der Wert in Zeilen anstatt Spalten ist unüblich:
- Bereich B5:U6 markieren, einen Name vergeben und sofort Menü "Daten", "Aus Tabelle/Bereich". So bekommst Du die Daten in PQ. Die Abfrage heisst "Daten"
- Menü "Tranformieren", "Vertauschen" (=Transponieren)
- Name der Spalten in "Jahr" und "K1" ändern
- Typ von "Jahr" in Datum ändern
- Rechtklick auf "Jahr", "Transformieren", "Jahr", "Jahresende"
- nochmal: "Transformieren", "Monat", "Monatsanfang"
- rechtsklick auf die Abfrage und "Verweis" klicken
- Du hast eine zweite Abfrage, benenne sie "Daten_Modus2" um
- jetzt entsteht eine Fummelei, die nicht ganz PQ-Anfänger Niveau ist. Am besten gebe ich dir den Code. Einfach "Erweiterte Editor" öffnen und Code ersetzen:
let
Quelle = Daten,
Monatswert = (Jahr, Wert)=> List.Generate (() => 1, each _ = 12, each _ + 1, each [Datum = #date(Jahr, _,1), Wert = Wert / 12]),
#"Aufgerufene benutzerdefinierte Funktion" = Table.AddColumn(Quelle, "Monaten", each Monatswert(Date.Year([Jahr]), [K1])),
#"Entfernte Spalten" = Table.RemoveColumns(#"Aufgerufene benutzerdefinierte Funktion",{"Jahr", "K1"}),
#"Erweiterte Monaten" = Table.ExpandListColumn(#"Entfernte Spalten", "Monaten"),
#"Erweiterte Monaten1" = Table.ExpandRecordColumn(#"Erweiterte Monaten", "Monaten", {"Datum", "Wert"}, {"Jahr", "K2"}),
#"Angefügte Abfrage" = Table.Combine({#"Erweiterte Monaten1", Daten}),
#"Ersetzter Wert" = Table.ReplaceValue(#"Angefügte Abfrage",null,0,Replacer.ReplaceValue,{"K1"}),
#"Gruppierte Zeilen" = Table.Group(#"Ersetzter Wert", {"Jahr"}, {{"Kosten Modus 1", each List.Sum([K1]), type any}, {"Kosten Modus 2", each List.Sum([K2]), type number}})
in
#"Gruppierte Zeilen"
Nur soviel: es wird für jede Jahreswert 12 gezwölte Monatswert errechnet samt Datum.
Am Ende werden die beide Abfragen kombiniert und mit endgültige Spaltennamen gruppiert.
Nur diese zweite Abfrage sollte herausgegeben werden (die andere in "Nur Verbindung"). In der Ausgabe, die Spalte "Jahr" als Datum formatieren.
Zugegeben, der VBA-Code ist fast leichter:
Sub Transform()
Dim Daten
Dim Erg
Dim i, j, m
Daten = Worksheets("Sheet1").Range("B5:U6").Value
ReDim Erg(1 To 4, 1 To UBound(Daten, 2) * 12)
For i = 1 To UBound(Daten, 2)
For m = 1 To 12
j = (i - 1) * 12 + m
Erg(3, j) = 0 'default-Monatswert bei Modus 1
If m = 12 Then 'nur bei letztem Monat
Erg(1, j) = DateSerial(Year(Daten(1, i)), 12, 1) 'Jahr übertragen
Erg(3, j) = Daten(2, i) 'Jahreswert übertragen
End If
Erg(2, j) = m 'monat übertragen
Erg(4, j) = Daten(2, i) / 12 'gezwölftelte Jahreswert übertragen
Next
Next
Worksheets("Sheet1").Range("B40").Resize(4, UBound(Erg, 2)) = Erg
End Sub
VG
Yal