Aus einer Liste von Verträgen möchte ich erfahren, in welchem Monat die Rechnungen fällig sind. Die Rechnungshäufigkeit ist je nach Vertrag unterschiedlich und reicht von monatlich (12x) über vierteljährlich (4x) bis jährlich (1x). Auch die Startdaten sind flexibel.
Dateneingabe: Vertragsdaten
Gewünschte Ausgabe:Rechnungen pro Monat
Ansatz
WasIch findeIch muss lediglich einen Zwischendatensatz erstellen, der alle in diesem Jahr zu versendenden Rechnungen enthält. So würden diese Rechnungsdaten aussehen:alle Rechnungen. Nachdem ich diese Rechnungsdaten erstellt habe, kann ich eine Pivot-Tabelle rund um den Rechnungsmonat erstellen.
Frage
Meine Frage ist zunächst: Ist mein Ansatz der richtige? Und wenn ja, wie würde ich eine solche Zwischendatenquelle automatisch generieren?
Antwort1
Sie sollten in der Lage sein, die Tabelle „Rechnungen pro Monat“ mit einfachen EXCEL-Funktionen zu erstellen.
- MONAT(Datum) ... gibt die Monatsnummer 1..12 zurück
- MONAT(Rechnungsmonat) - MONAT(Vertragsdatum) ... Anzahl der Monate seit Startdatum
- (MOD(obenstehendes, 12/Frequenz) = 0) ... Wahr, wenn es sich um einen Rechnungsmonat handelt
Der einzige Vorbehalt besteht darin, dass das obige Verfahren fehlschlägt, sobald Sie mehrere Kalenderjahre verwenden. Um die Monate seit dem Startdatum korrekt zu zählen, müssen Sie auch einerster Rechnungsmonat:
- MONAT(Rechnungsmonat) + 12*(JAHR(Rechnungsdatum)-JAHR(Vertragsdatum)) - (MONAT(Vertragsdatum) +erster Rechnungsmonat #)