STARTSEITE / FORMELN & FUNKTIONEN

Hinweis: Um Ihnen die Nachvollziehung der in den folgenden Artikeln beschriebenen Vorgänge zu ermöglichen, steht Ihnen nachfolgend die Option zur Verfügung, die Arbeitsmappe herunterzuladen.

↧

Datei Cost Baseline Expenditure Rev 06 ZIP — cost_baseline_expenditure_schedule_rev_06.zip

Es wird keine installierte Excel-Version benötigt.

Download

Bitte beachten Sie, dass es sich bei dem Download um eine ZIP-Datei handelt, deren Inhalt eine Datei mit dem Namen „cost_baseline_expenditure_schedule_rev_06.exe” ist. Es kann sein, dass Windows beim Ausführen dieser Datei eine Sicherheitsmeldung anzeigt und Sie daran hindert, die Datei auszuführen. Bitte sprechen Sie in diesem Fall mit Ihrer IT-Abteilung.

Für Fragen stehe ich Ihnen gerne per E-Mail zur Verfügung.

Artikel 1: Die Erstellung der Kostenbasis: `AUTO_PROSPETTO_COSTI`.

Was dieses Blatt leistet

AUTO_PROSPETTO_COSTI ist das Planungszentrum der Arbeitsmappe. Es ist zuständig für die Schätzung der Kosten und den Ausgabenplan. Er dient dazu, eine scheinbar einfache Frage zu beantworten: „Wie viel Geld sollte in jedem Kalendermonat ausgegeben werden, wenn eine Reihe von Budgetkategorien mit jeweils Gesamtkosten, Start- und Enddatum vorliegt?”

Die Antwort lässt sich nicht einfach durch Nachschlagen ermitteln. Das Arbeitsblatt verteilt die Kosten jeder Kategorie auf die Monate, in denen sie aktiv ist, und gewichtet sie entsprechend der Anzahl der Arbeitstage, die in den jeweiligen Kalendermonat fallen. Das Ergebnis ist eine zeitlich aufgeschlüsselte Kostenbasis, also der offizielle Plan, an dem später die tatsächlichen Ausgaben gemessen werden.

Hier finden Sie einen Überblick über die Struktur.

Das Arbeitsblatt ist in zwei Bereiche unterteilt:

Spalten A–F (ab Zeile 8): Die Eingabetabelle. Jede Zeile definiert eine Budgetkategorie einschließlich der geschätzten Kosten, des Startdatums, des Enddatums und der Dauer in Monaten.
Spalten G ff.: Die Ausgabematrix. Jede Spalte steht für einen Kalendermonat und jede Zelle enthält die geschätzten Ausgaben für diese Kategorie in diesem Monat.

In den Zeilen 4 bis 7 über der Datentabelle befinden sich Zusammenfassungszeilen, die die Daten aller Kategorien zusammenfassen und die monatlichen Summen, die kumulierten Ausgaben sowie die kumulierten Ausgaben als Prozentsatz des gesamten Projektbudgets angeben.

Übersicht, in der die Eingabespalten neben der monatlichen Verteilungsmatrix dargestellt sind, wobei die Zusammenfassungszeilen oben zu sehen sind

Der Aufbau: Benannte Bereiche und `SETUP_BASELINE`

Bevor Berechnungen durchgeführt werden, werden alle Master-Projektparameter im zugehörigen Blatt SETUP_BASELINE definiert. Die Arbeitsmappe verwendet benannte Bereiche, um auf diese Werte zu verweisen. Dadurch wird die Verwendung fest codierter Zelladressen vermieden.

Die in diesem Arbeitsblatt verwendeten benannten Bereiche sind:

beannter Bereich	Verweist auf	Zweck
`PROJECTNAME`	`SETUP_BASELINE!$B$2`	Projektname für die Anzeige
`COSTESTIMATE`	`SETUP_BASELINE!$C$5`	Geschätztes Gesamtbudget (Summe aller Kategorien)
`STARTDATE`	`AUTO_PROSPETTO_COSTI!$B$4`	Datum Projektstart
`ENDDATE`	`AUTO_PROSPETTO_COSTI!$B$5`	Datum Projektende
`TOTALCOST`	`AUTO_PROSPETTO_COSTI!$B$7`	Arbeitsblatt mit den Gesamtkosten
`_DataFestivita`	`feste_italia_sardegna!$C:$C`	Feiertagskalender für die Berechnung der Arbeitstage
`DURATA_GGL`	`SETUP_BASELINE!$G$9:$G$200`	Gesamtzahl der Arbeitstage pro Kategorie (im Setup vorab berechnet)

Die Zeilen für die Kategorieeingaben (A9:F200) werden nicht manuell in dieses Blatt eingegeben. Stattdessen enthält Zelle A9 eine LET-Formel, die diese Daten in Echtzeit aus SETUP_BASELINE abruft.

=LET(
   cat,    (SETUP_BASELINE!B9:B200),
   ct,     (SETUP_BASELINE!C9:C200),
   inizio, (SETUP_BASELINE!D9:D200),
   fine,   (SETUP_BASELINE!E9:E200),
   durata, (SETUP_BASELINE!F9:F200),
   result, WENNNV(HSTAPELN(cat, ct, inizio, fine, durata), ""),
   result
)

Die Funktion LET weist jeder Quellspalte einen lesbaren Variablennamen zu (u. B. cat, ct, inizio, fine, durata). Die Funktion HSTAPELN fasst diese zu einem einzigen übergleitenden Array zusammen. WENNNV(..., „“) unterdrückt Fehlermeldungen für leere Zeilen am Ende des Bereichs.
In der Praxis bedeutet dies: Die gesamte Eingabetabelle wird durch eine einzige Formel in A9 gefüllt und Aktualisierungen von `SETUP_BASELINE' werden hier sofort übernommen.

Schritt 1: Erstellen der Kopfzeile für den Monat

In Zeile 8, beginnend mit Spalte G, wird der erste Tag jedes Projektmonats angezeigt. Die Zelle G8 enthält:

=WENN(
   STARTDATE = "",
   "",
   MONATSENDE(
      EDATUM(STARTDATE, SEQUENZ(, MONAT(ACTUALENDDATE) - MONAT(ACTUALSTARTDATE) + 1)),
      -2
   ) + 1
)

Dies ist eine dynamische Array-Formel, die alle Monatsüberschriften in einem einzigen Ausdruck generiert:

SEQUENZ(, n) erzeugt ein horizontales Array von Ganzzahlen von 1 bis n, wobei n die Anzahl der Monate zwischen dem tatsächlichen Start- und Enddatum ist.
EDATUM(STARTDATE, ...) verschiebt das Startdatum um jeweils eine ganze Zahl in der Sequenz – um jeweils einen Monat.
MONATENDE(..., -2) + 1 wandelt jedes verschobene Datum in den ersten Tag des jeweiligen Monats um (Ende des Monats minus 1 Monat, plus 1 Tag).

Das Ergebnis erstreckt sich nach rechts über so viele Spalten, wie das Projekt Monate umfasst. Es ist nicht erforderlich, die Kopfzeile manuell zu verlängern, wenn sich die Projektdauer ändert, da sich die Formel automatisch anpasst.

Schritt 2: Die Kernverteilungsformel befindet sich in Zelle G9.

Dies ist die komplexeste Formel in der Arbeitsmappe. In Zelle G9 werden die geschätzten Ausgaben auf der Grundlage der Arbeitstage für jede Kategorie und jeden Monat gleichzeitig berechnet. Sie ist als Kombination aus MATRIXERSTELLEN und LET geschrieben:

=WENNFEHLER(
  LET(
    startDates,   C9:C21,
    endDates,     D9:D21,
    taskAmounts,  B9:B21,
    totalDays,    SETUP_BASELINE!G9:G21,
    headerDates,  ANCHORARRAY(G8),

    MATRIXERSTELLEN(
      ZEILEN(startDates),
      SPALTEN(headerDates),
      LAMBDA(r, c,
        LET(
          taskStart,     INDEX(startDates, r),
          taskEnd,       INDEX(endDates, r),
          headerDate,    INDEX(headerDates, c),
          amount,        INDEX(taskAmounts, r),
          taskTotalDays, INDEX(totalDays, r),
          dailyRate,     WENNFEHLER(amount / taskTotalDays, 0),

          monthStart,    WENNFEHLER(DATUM(JAHR(headerDate), MONAT(headerDate), 1),
                                 DATUM(2024, c, 1)),
          monthEnd,      WENNFEHLER(MONATSENDE(monthStart, 0),
                                 MONATSENDE(DATE(2024, c, 1), 0)),

          periodStart,   MAX(WENNFEHLER(taskStart, DATUM(2100,1,1)), monthStart),
          periodEnd,     MIN(WENNFEHLER(taskEnd,   DATUM(1900,1,1)), monthEnd),

          isActive,      periodStart <= periodEnd,

          monthlyDays,   WENN(isActive,
                            WENNFEHLER(NETTOARBEITSTAGE.INTL(
                              periodStart, periodEnd, 1, _DataFestivita), 0),
                            0),

          monthlyAmount, WENN(monthlyDays = 0, NA(), dailyRate * monthlyDays),

          monthlyAmount
        )
      )
    )
  ),
"")

Im Detail

Äußeres LET – Benennung der Eingaben: Das äußere LET vergibt aussagekräftige Namen für die fünf Eingabe-Arrays (startDates, endDates, taskAmounts, totalDays) sowie für headerDates, das über ANCHORARRAY(G8) auf die dynamisch ausgebreitete Monatskopfzeile verweist.

MATRIXERSTELLEN(ZEILEN, SPALTEN, LAMBDA(r, c, ...)):
MATRIXERSTELLEN ist der Motor. Er erstellt ein zweidimensionales Array mit einer Zeile für jede Kategorie und einer Spalte für jeden Monat. Dabei wird die LAMBDA-Funktion einmal für jede Zellenposition (r, c) aufgerufen.

Inneres LET – Berechnung pro Zelle: Innerhalb des LAMBDA definiert eine zweite LET-Funktion die zur Berechnung des Werts jeder Zelle benötigten Variablen.

dailyRate = amount / taskTotalDays — Die Gesamtkosten der Kategorie geteilt durch die Gesamtzahl der Arbeitstage im gesamten Projekt. Dies sind die Kosten pro Arbeitstag.
monthStart / monthEnd : der erste und letzte Tag des Monats in der Spalte.
periodStart = MAX(taskStart, monthStart) : der spätere Zeitpunkt aus dem Start der Aufgabe und dem Monatsbeginn.
periodEnd = MIN(taskEnd, monthEnd): der frühere Zeitpunkt aus dem Aufgabenende und dem Monatsende.
isActive – nur dann wahr, wenn periodStart ≤ periodEnd, d. h. wenn sich die Aufgabe überhaupt mit diesem Monat überschneidet.
monthlyDays — NETTOARBEITSTAGE.INTL(periodStart, periodEnd, 1, _DataFestivita) zählt die Arbeitstage in der Überschneidung, wobei Wochenenden und die im Blatt feste_italia_sardegna aufgeführten Feiertage ausgeschlossen werden.
monthlyAmount = dailyRate × monthlyDays.

Ist die Aufgabe in diesem Monat inaktiv, wird NA() zurückgegeben, was als Leerzeichen dargestellt wird. Die äußere Funktion WENNFEHLER(..., „“) fängt alle verbleibenden Fehler ab.

Schritt 3: Summierungszeilen

Zeile 6 – Monatliche Summen:

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

NACHSPALTE wendet die Funktion LAMBDA auf jede Spalte im Datenbereich an und addiert alle Werte in der Spalte „Kategorie“ für den jeweiligen Monat. Leere Monate geben „“ zurück.

Zeile 5 — Kumulierte Ausgaben

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

Dies summiert die monatlichen Summen von Spalte G bis einschließlich der aktuellen Spalte. Der Anker G$6 stellt sicher, dass die laufende Summe immer beim ersten Monat beginnt.

Zeile 4 — Kumulierte Ausgaben in %:

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

Hiermit wird jeder kumulierte Wert durch den benannten Bereich TOTALCOST geteilt, um die im Diagrammblatt verwendeten prozentualen Werte der S-Kurve zu ermitteln.

Warum `NETTOARBEITSTAGE.INTL` wichtig ist

Für die Kontrolle der Projektkosten ist die Verwendung einer Gewichtung nach Arbeitstagen anstelle einer Gewichtung nach Kalendertagen von Bedeutung. So erstreckt sich beispielsweise eine Kategorie vom 29. Januar bis zum 3. Februar über zwei Kalendermonate, doch fast alle Kosten fallen, gemessen an den Arbeitstagen, im Januar an. Eine Aufteilung nach Kalendertagen würde daher zu einer falschen Darstellung der Basislinie führen. Das Modell berücksichtigt jedoch Feiertage korrekt, indem es die Funktion NETTOARBEITSTAGE.INTL in Verbindung mit der Feiertagsliste _DataFestivita verwendet. So weist beispielsweise ein Monat, in dem mehrere italienische Feiertage zusammenfallen, weniger abrechnungsfähige Tage und somit geringere zugewiesene Kosten auf.

Zu den wichtigsten Punkten gehören:

Die gesamte Eingabetabelle wird durch eine einzige LET/HSTAPELN-Formel in A9 gefüllt, die von SETUP_BASELINE gesteuert wird.
Die Kopfzeile für die Monate (G8) ist ein dynamisches Array, das sich automatisch erweitert, wenn sich die Projektdauer ändert.
Die Verteilungsmatrix (ab G9) wird durch eine einzige MATRIXERSTELLEN/LAMBDA/LET-Formel berechnet, die jede Zeilen-Spalten-Kreuzung durchläuft. Dadurch sind keine Hilfsspalten erforderlich.
Die Kostenverteilung basiert auf Arbeitstagen, nicht auf Kalendertagen, und verwendet einen speziellen Feiertagskalender.
Das NACHSPALTE/LAMBDA-Muster in den Zusammenfassungszeilen sorgt dafür, dass die Aggregationsformeln schlank und selbstausweitend bleiben.

Visualisierungen (dieser Artikel)

Abbildung 1: — Formelarchitektur: von SETUP_BASELINE bis zur Verteilungsmatrix.
Zeigt die dreistufige Struktur: Eingaben aus benannten Bereichen, die in die LET/HSTAPELN-Formel in Zelle A9 einfließen, welche wiederum die MAZRIXERSTELLEN-Engine in Zelle G9 speist, die die Ausgabematrix erzeugt; darunter befinden sich die NACHSPALTEN-Zusammenfassungszeilen.

Formel Architektur: von SETUP_BASELINE in die Verteilungsmatrix.

Abbildung 2: Im Inneren der Zelle „MATRIXERSTELLEN“: Zeigt, wie ein einzelner monatlicher Ausgabenwert berechnet wird. Verfolgt die drei Eingaben (Tagesrate, Monatsgrenzen, Überschneidungszeitraum) bis zur Funktion „NETTOARBEITSTAGE.INTL“ und von dort weiter bis zum endgültigen Monatsbetrag.

Innerhalb der Zelle „MATRIXERSTELLEN“