STARTSEITE / FORMELN & FUNKTIONEN

Hinweis: In Artikel 2 finden Sie den Link, mit dem Sie die Arbeitsmappe (EXE-Datei) herunterladen können.

Artikel 2: Die Realität erfassen: `DATI_COSTI_EFFETTIVI`

Was dieses Arbeitsblatt bewirkt

DATI_COSTI_EFFETTIVI – Erfassung der Ist-Kosten – ist das Live-Pendant zu dem in Artikel 1 behandelten Planungsblatt. Während AUTO_PROSPETTO_COSTI die Planung beschreibt, erfasst dieses Blatt die tatsächlich angefallenen Ausgaben. Seine Struktur entspricht nahezu genau der des Basisblatts: Es gibt dieselben Kategorien, dieselben monatlichen Spalten und dieselben Zusammenfassungszeilen. Die Quelle der Zahlen unterscheidet sich jedoch grundlegend. Anstatt die geschätzten Budgets nach Arbeitstagen aufzuteilen, liest dieses Blatt die tatsächlichen Rechnungsdaten direkt aus dem Transaktionsbuch (REGISTRO_ENTRATE_&_USCITE) und fasst sie im gleichen zeitlich gestaffelten Format zusammen.

Diese parallele Struktur ist der Schlüssel zur Leistungsstärke der Arbeitsmappe: Da beide Blätter dasselbe Layout verwenden, lassen sich die Unterschiede zwischen geplanten und tatsächlichen Ausgaben sofort vergleichen. Zudem ist für die Abweichungsanalyse in Blatt 3 keine zusätzliche Umwandlung erforderlich.

Aufbau im Überblick

Das Blatt ist in dieselben zwei Bereiche unterteilt wie Blatt 1:

Spalten A–E (ab Zeile 9): Kategoriebezeichnungen, tatsächliche Gesamtkosten, Startdaten, Enddaten und Dauer werden automatisch aus „SETUP_BASELINE” und dem Hauptbuch übernommen.
Spalten G ff.: Die Ist-Kostenmatrix. Jede Spalte entspricht einem Kalendermonat und jede Zelle zeigt den tatsächlichen Betrag an, der in diesem Monat für diese Kategorie basierend auf den Rechnungen im Hauptbuch ausgegeben wurde.

Die Zeilen 4–7 weisen die gleiche Übersichtsstruktur wie Blatt 1 auf. Sie enthalten die tatsächlichen Monatssummen, die kumulierten Ist-Werte und die kumulierten Ist-Werte als Prozentsatz der gesamten Budgetprognose.

Veranschaulichung der Daten

Die Monatskopfzeile: Ein anderer Ansatz

In Blatt 1 werden in Zeile 8 auf Grundlage des geplanten Start- und Enddatums des Projekts Daten generiert. In Blatt 2 ist der Ansatz etwas anders. Die Zelle G8 ist an das tatsächliche Startdatum gekoppelt.

=MONATSENDE(ACTUALSTARTDATE, 0)

Dies gibt den letzten Tag des Monats zurück, in dem das Datum ACTUALSTARTDATE liegt. Die Zelle H8 erweitert die Reihe dann dynamisch:

=MONATSENDE(
   EDATUM(G8, SEQUENZ(, MONAT(ACTUALENDDATE) - MONAT(ACTUALSTARTDATE))),
   0
)

Während Blatt 1 den ersten Tag jedes Monats ausgibt, gibt Blatt 2 den letzten Tag, also das Monatsenddatum, aus. Dies ist beabsichtigt. Das Hauptbuchblatt (REGISTRO_ENTRATE_&_USCITE) speichert für jede Transaktion eine berechnete Spalte DATA EOM (Monatsenddatum). Da an beiden Stellen das Monatsenddatum verwendet wird, kann die SUMMEWENNS-Suche in der Verteilungsformel genau auf dieses Feld abgeglichen werden. Dadurch werden Unklarheiten vermieden, die durch Transaktionen entstehen könnten, die an verschiedenen Tagen innerhalb desselben Monats erfasst wurden.

Verwendete benannte Bereiche:

ACTUALSTARTDATE → DATI_COSTI_EFFETTIVI!$B$4
ACTUALENDDATE → DATI_COSTI_EFFETTIVI!$B$5

Die Eingabezeilen: Ein `LET`, das Daten aus dem Hauptbuch aggregiert.

Die Zelle A9enthält eine LET-Formel, die die gesamte Eingabetabelle ausfüllt. Sie unterscheidet sich in einem entscheidenden Punkt von der entsprechenden Formel in Blatt 1: Die Kostenspalte (ct) wird nicht aus Setup_Baseline gelesen, sondern live berechnet, indem alle übereinstimmenden Transaktionen aus der Hauptbuchtabelle summiert werden.

=LET(
   cat,    SETUP_BASELINE!B9:B200,
   ct,     SUMMEWENN(tbl_Registro_UE[CATEGORIA], cat, tbl_Registro_UE[DENARO OUT]),
   inizio, SETUP_BASELINE!D9:D200,
   fine,   SETUP_BASELINE!E9:E200,
   durata, SETUP_BASELINE!F9:F200,
   result, WENNNV(HSTAPELN(cat, ct, inizio, fine, durata), ""),
   result
)

Der entscheidende Satz lautet:

ct, SUMMEWENN(tbl_Registro_UE[CATEGORIA], cat, tbl_Registro_UE[DENARO OUT])

SUMMEWENNarbeitet hier im Array-Modus, da cat ein Array ist (die gesamte Spalte B9:B200 aus SETUP_BASELINE). Excel wertet SUMMEWENN einmal für jeden Wert in cat aus und gibt ein Array mit Summen zurück – eine pro Kategorie. Das bedeutet, dass cat die Summe aller ausgehenden Zahlungen enthält, die mit dem jeweiligen Kategorienamen gekennzeichnet sind und direkt aus der strukturierten Tabelle tbl_Registro_UE stammen.

HSTAPELN fasst dann die Kategorienamen, die tatsächlichen Summen, die Daten und die Dauer zu einer einzigen, zusammenhängenden Ausgabe zusammen. Die Formel in B7 bestätigt separat die Gesamtsumme.

=SUMME(B9:B200)

Da der Bereich B9:B200 durch die Ausweitung von A9 gefüllt wird, spiegelt diese Summe dynamisch das Ergebnis der LET-Formel wider.

Die Kernformel für die Verteilung lautet: `SUMMEWENNS` über zwei Dimensionen.

Die Formel in Zelle G9 in Blatt 2 ist im Vergleich zur Berechnung in Blatt 1 sehr einfach, da es sich bei den tatsächlichen Kosten um einzelne Rechnungsvorgänge mit einem bestimmten Datum handelt und keine anteilige Berechnung nach Tagen erforderlich ist. Die Frage lautet also: Welche Rechnungen gehören zu dieser Kategorie und fallen in diesen Monat?

=WENN(
   SUMMEWENNS(
      tbl_Registro_UE[DENARO OUT],
      tbl_Registro_UE[CATEGORIA],    DATI_COSTI_EFFETTIVI!A9:A200,
      tbl_Registro_UE[DATA EOM],     DATI_COSTI_EFFETTIVI!G8:BB8
   ) = 0,
   "",
   SUMMEWENNS(
      tbl_Registro_UE[DENARO OUT],
      tbl_Registro_UE[CATEGORIA],    DATI_COSTI_EFFETTIVI!A9:A200,
      tbl_Registro_UE[DATA EOM],     DATI_COSTI_EFFETTIVI!G8:BB8
   )
)

Dies ist eine zweidimensionale SUMMEWENNS-Funktion, die sich das dynamische Array-Verhalten von Excel zunutze macht. Und so funktioniert sie:

A9:A200 ist ein vertikales Array mit Kategorienamen (einer pro Zeile).
G8:BB8 ist ein horizontales Array mit Monatsenddaten (eine pro Spalte).

Wenn SUMMEWENNS gleichzeitig Arrays für beide Kriterienbereiche erhält, wendet Excel die Berechnung auf beide Dimensionen an und gibt eine Matrix zurück, d. h. eine Zeile pro Kategorie und eine Spalte pro Monat, und das in einer einzigen Formel. Jede Zelle in der Matrix entspricht der Summe aller DENARO OUT-Werte im Hauptbuch, bei denen sowohl die Kategorie als auch das DATA EOM-Datum übereinstimmen.

Der Wrapper WENN(...=0, „“, ...) unterdrückt Nullen, sodass leere Monate optisch leer bleiben, anstatt 0 anzuzeigen.

Die Formelleiste ist sichtbar und zeigt neben den umgebenden Daten Folgendes an

Ergebnis: Spiegelung von Blatt 1

Die drei Zusammenfassungszeilen (4–6) verwenden dasselbe NACHSPALTE/LAMBDA-Muster wie Blatt 1, beziehen sich jedoch auf die tatsächliche Kostenmatrix und nicht auf die geschätzte.

Zeile 6: Tatsächliche Monatssummen

=NACHSPALTE(G9:BB200, LAMBDA(ary, WENN(SUMME(ary)=0, "", SUMME(ary))))

Zeile 5: Kumulierte tatsächliche Ausgaben

=NACHSPALTE(G6:BB6, LAMBDA(col, WENN(col="", "", SUMME(col:G$6))))

Zeile 4: Kumulierte tatsächliche Ausgaben in %

=NACHSPALTE(G5:BB5, LAMBDA(col, WENN(col="", "", col / COSTESTIMATE)))

Bitte beachten Sie, dass in Zeile 4 durch COSTESTIMATE (die budgetierte Gesamtsumme) geteilt wird, nicht durch die tatsächlichen Gesamtausgaben. Dies ist beabsichtigt: Die Darstellung der Ist-Werte als Prozentsatz des ursprünglichen Budgets macht sofort deutlich, ob das Projekt im Vergleich zum Plan über- oder unter den Kosten liegt. So wird nicht nur der interne Fortschritt in Richtung einer möglicherweise unvollständigen tatsächlichen Gesamtsumme angezeigt.

Die auf `DATEDIF` basierende Dauer

Zelle B6 berechnet die tatsächliche Projektdauer dynamisch:

=WENN(B4="", "", WENN(B5="", "", (DATEDIF(B4, B5, "M")) + 1))

DATEDIF ist eine undokumentierte, aber weit verbreitete Excel-Funktion, die die Anzahl der vollen Monate zwischen zwei Daten angibt. Durch das Hinzufügen von 1 wird berücksichtigt, dass ein Projekt, das im Januar beginnt und im Januar endet, eine Dauer von einem Monat hat und nicht null. Die verschachtelten IF-Bedingungen verhindern, dass die Formel einen Fehler ausgibt, wenn eines der beiden Daten leer ist.

Die Beziehung zwischen den Blättern 1 und 2

Es lohnt sich, einen Schritt Abstand zu nehmen, um die zugrunde liegende Entwurfsentscheidung dieser Architektur zu erkennen. Die beiden Blätter sind keine einfachen Kopien voneinander mit unterschiedlichen Nummern. Sie verwenden grundlegend unterschiedliche Berechnungsmodule.:

Blatt	Blatt 1 (`AUTO_PROSPETTO_COSTI`)	Blatt 2 (`DATI_COSTI_EFFETTIVI`)
Quelle	`SETUP_BASELINE` (Schätzungen)	`tbl_Registro_UE` (echte Rechnungen)
Verteilungsmethode	`MATRIXERSTELLEN` + `NETTOARBEITSTAGE.INTL`	Zweidimensional `SUMMEWENNS`
Monatsüberschriften	Erster Tag des Monats	Letzter Tag des Monats (EOM)
Spalte Kosten	Statische Schätzung	`SUMMEWENN` aus dem Hauptbuch
Aktualisierungen, wenn	Änderungen der Start- und Endtermine oder der Budgets	Neue Rechnungen werden in das Hauptbuch aufgenommen

Durch diese Trennung wird sichergestellt, dass die Basisplanung nicht durch Ist-Werte überschrieben wird – ein grundlegendes Prinzip der Projektkostensteuerung. Der Vergleich beider Werte stellt stets einen sauberen und nachvollziehbaren Abgleich dar.

Wichtige Erkenntnisse

Die Tabelle mit den tatsächlichen Kosten (A9) verwendet, ebenso wie Blatt 1, die Funktionen LET/HSTAPELN. Sie ersetzt jedoch die statische Schätzungsspalte durch ein dynamisches SUMMEWENN-Array, das auf die Hauptbuch-Tabelle verweist.
Die Monatsüberschriften verwenden das Datum des Monatsendes, um sich an das Feld DATA EOM im Hauptbuch anzupassen, was eine saubere SUMMEWENN-Zuordnung ermöglicht.
Die Verteilungsmatrix wird durch ein zweidimensionales SUMMEWENNS berechnet: Eine Formel in G9 erzeugt die gesamte Matrix, indem sie gleichzeitig über die Dimensionen Kategorie (Zeile) und Monat (Spalte) übertragen wird.
Die Zusammenfassungszeilen verwenden dasselbe Aggregationsmuster NACHSPALTE/LAMBDA wie Blatt 1. Dadurch wird eine parallele Struktur beibehalten, die direkt in die Abweichungsanalyse einfließt.

Visualisierungen (dieser Artikel)

Abbildung 1: Veranschaulicht wird, wie die 2D-SUMIFS-Funktion die Ist_Kostenmatrix erzeugt, indem ein vertikales Array mit Kategorienamen und ein horizontales Array mit Monatsenddaten gleichzeitig über beide Dimensionen übertragen werden.

Wie die 2D-SUMIFS-Funktion die Ist-Kostenmatrix erstellt.

Abbildung 2: Vergleichstabelle zwischen Blatt 1 und Blatt 2: Nebeneinander-Vergleich von Quelle, Kostenspalte, Verteilungsmodul, Format der Monatsüberschrift und Aktualisierungsauslöser für das Basis- und das Ist-Blatt.

Aus der Serie sind bereits folgende Artikel erschienen:
Vor den Formeln steht die Struktur (Kostenbasis-Ausgabenplan Teil 01)

Eine vollständige, zeitlich gestaffelte Kostenbasis wird mit nur einer einzigen Formel erstellt. (Kostenbasis-Ausgabenplan, Teil 02)

Artikel 2: Die Realität erfassen: DATI_COSTI_EFFETTIVI