Bevor wir beginnen: Was leistet diese Arbeitsmappe eigentlich?

Eine Tabellenkalkulation, die selbstständig denkt

Die meisten Excel-Arbeitsmappen sind passiv. Man gibt Zahlen ein und erhält Zahlen als Ergebnis. Die Formeln sind Diener. Sie sitzen still in ihren Zellen. Sie warten darauf, dass man ihnen sagt, was sie berechnen sollen.

Die Arbeitsmappe, die wir uns nun ansehen werden, ist anders aufgebaut. Sie wartet nicht darauf, dass man ihr Anweisungen gibt. Wenn Sie dem Einrichtungsblatt eine neue Budgetkategorie hinzufügen, wird die Kostenbasis automatisch für jeden Monat des Projekts aktualisiert. Wenn Sie eine Rechnung im Hauptbuch erfassen, werden die Ist-Kostensummen aktualisiert und die zeitliche Verteilung neu berechnet. Auch die Abweichungszahlen im Zusammenfassungsbericht passen sich an – und das alles, ohne dass Sie eine weitere Zelle berühren müssen. Ändern Sie das Projektenddatum, wird die Kopfzeile des Monats entsprechend erweitert oder verkürzt. Im wahrsten Sinne des Wortes ist die Arbeitsmappe also lebendig.

In dieser vierteiligen Artikelserie (Kostenbasis-Ausgabenplan 01–04) wird genau erklärt, wie das funktioniert.

Was daraus werden könnte:

Eine gut konzipierte Arbeitsmappe muss nicht bei der Kostenverfolgung haltmachen. Stellen Sie sich vor, dieselbe Struktur würde um ein Projektmanagement-Blatt mit Aufgaben, Verantwortlichen, Abhängigkeiten und Fristen ergänzt, das wiederum in ein Gantt-Diagramm einfließt. Dieses aktualisiert sich bei jeder Terminänderung automatisch. Wenn Sie darüber hinaus ein Kanban-Board hinzufügen, treiben die Aufgabendaten, die den Zeitplan steuern, nun eine Live-Workflow-Ansicht an. Diese zeigt, was geplant ist, was gerade bearbeitet wird und was bereits abgeschlossen ist. Fügen Sie ein Blatt hinzu, das die Zahlen überwacht, den geplanten Fortschritt mit den tatsächlichen Ausgaben vergleicht und Kategorien kennzeichnet, die das Budget schneller aufbrauchen, als es der Zeitplan zulässt. Dieses Blatt kann auch Aufgaben aufzeigen, die Aufmerksamkeit erfordern, bevor sie zu Problemen werden. An diesem Punkt ist die Tabelle nicht mehr nur eine Aufzeichnung dessen, was geschehen ist. Sie wird zu einem System, das Ihnen sagt, was als Nächstes zu tun ist. Die folgenden Artikel erklären, wie Sie die Grundlage für genau diese Art von Tool schaffen können.

Wozu dient die Arbeitsmappe:

„Cost_baseline_expenditure_schedule_rev_06” ist ein Instrument zur Projektkostensteuerung, das für die Bedürfnisse eines italienischen Unternehmens programmiert wurde. Es wurde für ein bis 2024 laufendes Projekt erarbeitet und seitdem weiterentwickelt. Der Vorteil gegenüber käuflicher Software ist, dass die Arbeitsmappe zeitnah vollständig an die individuellen Bedürfnisse angepasst werden kann.

Im Projektmanagement ist die Kostenbasis mehr als nur eine Gesamtzahl: Es handelt sich um einen monatlichen Ausgabenplan, der aufzeigt, wie viel Geld bis zu einem bestimmten Zeitpunkt im Projekt ausgegeben worden sein sollte. Ohne Kostenbasis lässt sich die Leistung nicht messen. Sie wissen beispielsweise vielleicht, dass Sie 500.000 € ausgegeben haben. Ohne jedoch zu wissen, ob Sie bis zu diesem Zeitpunkt 400.000 € oder 600.000 € hätten ausgeben sollen, ist diese Zahl bedeutungslos.

Diese Arbeitsmappe erstellt die Kostenbasis automatisch, vergleicht sie kontinuierlich mit den tatsächlichen Ausgaben aus einem Transaktionsbuch und hebt Abweichungen hervor. All dies wird durch eine Handvoll Formeln gesteuert, die die meisten Excel-Anwender niemals selbst geschrieben hätten.

Im Folgenden finden Sie eine Liste der Themen, die Sie beim Lesen dieser Artikelserie kennenlernen werden:

Die Artikel richten sich an fortgeschrittene Excel-Anwender:innen, die mit Funktionen wie SVERWEIS, SUMMEWENNS und verschachtelten WENN-Anweisungen vertraut sind, aber möglicherweise noch nicht intensiv mit der neueren dynamischen Array-Engine von Excel oder LET-basierten Formelmustern gearbeitet haben. Wenn Ihnen das bekannt vorkommt, wird diese Artikelserie Sie in vielerlei Hinsicht herausfordern.

Sie werden sehen: LET wird ernsthaft eingesetzt. Und zwar nicht nur, um die Wiederholung eines Ausdrucks zu vermeiden. Sondern auch als echtes Strukturierungswerkzeug. Es ermöglicht, komplexe, mehrstufige Berechnungen in benannte, lesbare Schritte zu zerlegen. Diese können unabhängig voneinander verstanden werden. Die Arbeitsmappe nutzt LET, um ganze Eingabetabellen aus einem anderen Blatt zu übernehmen, Zwischenarrays zu benennen und eine selbsterklärende Formellogik aufzubauen. Andernfalls wäre dies ein einziger, unlesbarer, verschachtelter Ausdruck, der sich über Hunderte von Zeichen erstreckt.

Sie werden feststellen, dass MATRIXERSTELLEN und LAMBDA anstelle eines Rasters aus einzelnen Formeln verwendet werden. Die gesamte Kostenverteilungsmatrix, die dreizehn Kategorien über elf Monate umfasst, wird durch eine einzige Formel in einer Zelle erzeugt. Das konzeptionelle Kernstück dieser Reihe besteht darin, zu verstehen, wie MATRIXERSTELLEN einen zweidimensionalen Raum durchläuft und wie das darin enthaltene LAMBDA jede (Zeilen-, Spalten-)Koordinate erhält, um einen einzelnen Zellwert zu berechnen.

Sie werden auch sehen, dass dynamische Arrays nicht nur als Ausgabe, sondern auch als Infrastruktur genutzt werden. So wird die Kopfzeile für die Monate mithilfe der Funktionen SEQUENZ und EDATUM automatisch erweitert. Die Eingabetabelle wird durch eine einzige LET/HSTAPELN-Formel generiert. Benannte Bereiche fungieren als Schnittstelle zwischen den Arbeitsblättern und sorgen dafür, dass die Formellogik von den physischen Zelladressen entkoppelt bleibt. Dies sind keine Tricks, sondern architektonische Entscheidungen, die die Arbeitsmappe wartbar machen.

Sie sehen eine zweidimensionale SUMMEWENNS-Formel, die in einem einzigen Ausdruck eine vollständige Matrix erzeugt. Wenn Sie Arrays als beide Kriterien in einer SUMIFS-Formel übergeben, durchsucht die Arbeitsmappe gleichzeitig Zeilen und Spalten. So wird ein herkömmliches Raster aus einzelnen Suchformeln in eine einzige, elegante Formel umgewandelt.

Außerdem werden Sie feststellen, dass NETTOARBEITSTAGE.INTL nicht nur für die Terminplanung, sondern auch für die Kostenverteilung verwendet wird. Die Arbeitsmappe verteilt die Kosten nicht nach Kalendertagen, sondern gewichtet die Zuordnung für jeden Monat anhand der Anzahl der darin enthaltenen Arbeitstage. Dabei wird ein spezieller Feiertagskalender für Sardinien verwendet. Diese subtile, aber wichtige Entscheidung hat konkrete Auswirkungen auf die Genauigkeit der Basislinie.

Wie passen die vier Arbeitsblätter zusammen?

Die Arbeitsmappe sorgt für eine klare Trennung der Aufgabenbereiche zwischen den einzelnen Arbeitsblättern. Bevor wir uns mit den Details der Formeln befassen, ist es hilfreich, die Gesamtstruktur im Blick zu behalten.

SETUP_BASELINE ist die zentrale Quelle für Projektparameter. Hier werden alle Kategorienamen, Budgetzahlen und Start- sowie Enddaten gespeichert. Die anderen Blätter speichern diese Daten nicht, sondern verweisen darauf. Das bedeutet, dass Änderungen nur an einer Stelle vorgenommen werden müssen, woraufhin sich der Rest der Arbeitsmappe entsprechend anpasst.

AUTO_PROSPETTO_COSTI bildet die Kostenbasis. Mithilfe der Daten aus SETUP_BASELINE wird das Budget für jede Kategorie berechnet und anschließend auf die Monate verteilt, in denen die Kategorie aktiv ist. Dabei wird eine Gewichtung nach Arbeitstagen vorgenommen. Das Ergebnis ist der offizielle Ausgabenplan, also das, was geschehen soll.

DATI_COSTI_EFFETTIVI ist der Tracker für die Ist-Kosten. Seine Struktur entspricht genau der des Basisblatts, doch seine Zahlen stammen aus dem Transaktionsbuch und nicht aus Schätzungen. Das Ergebnis zeigt, was tatsächlich geschehen ist.

REGISTRO_ENTRATE_&_USCITE ist das Transaktionsbuch, das als Rohprotokoll aller Rechnungen und Zahlungen dient. Jeder Eintrag fließt automatisch in die Istkostenübersicht ein.

CATEGORIE_STIMATE_VS_EFFETTIVE liefert schließlich das letzte Puzzleteil. Es fasst das geschätzte Gesamtbudget und die tatsächlichen Gesamtkosten für jede Kategorie zusammen, berechnet die Abweichung und stellt den Vergleich in einer übersichtlichen Tabelle dar.

Die Daten fließen stets in eine Richtung: von der Setup-Baseline und dem Journal zu den beiden Berechnungsblättern und schließlich in den Abweichungsbericht. Ein Rückfluss ist nicht möglich. Diese Einwegarchitektur macht die Arbeitsmappe zuverlässig. Es gibt keine zirkulären Abhängigkeiten, kein Risiko, versehentlich eine Formel mit einem Wert zu überschreiben, und es besteht keine Unklarheit darüber, welcher Wert maßgeblich ist.

Bitte achten Sie beim Lesen auf Folgendes:

Jeder Artikel behandelt ein Arbeitsblatt ausführlich und führt Sie durch die Formeln in der Reihenfolge, in der Sie ihnen begegnen würden, wenn Sie die Arbeitsmappe selbst öffnen würden. Beachten Sie beim Lesen bitte folgende Punkte:

Der erste Punkt betrifft die Rolle benannter Bereiche.: Fast jede Formel in dieser Arbeitsmappe bezieht sich auf benannte Bereiche statt auf Zelladressen. Dabei tauchen die Namen COSTESTIMATE, STARTDATE, _DataFestivita und ACTUALSTARTDATE wiederholt auf. Achten Sie darauf, worauf jeder einzelne Name verweist und warum. Benannte Bereiche dienen nicht nur der Optik, sondern sie sind auch das Bindeglied, das es Formeln auf einem Blatt ermöglicht, auf Änderungen auf einem anderen Blatt zu reagieren.

Die Differenz zwischen Soll- und Istwerten. Die beiden wichtigsten Berechnungsblätter weisen denselben Aufbau auf, erfüllen jedoch gegensätzliche Funktionen. Das „Sollwert“-Blatt verteilt ein festgelegtes Budget anhand eines Arbeitstagmodells über einen bestimmten Zeitraum. Das „Istwerte“-Blatt fasst vergangene Transaktionen mithilfe einer Nachschlagelogik zusammen. Wenn Sie diesen Unterschied verstehen, können Sie die Logik der Formeln in beiden Fällen besser nachvollziehen.

Die Kosten der Automatisierung. Die Formeln in dieser Arbeitsmappe sind nicht einfach. So enthält beispielsweise eine Zelle in der Verteilungsmatrix eine Kombination aus MATRIXERSTELLEN, LAMBDA und LET, die sich bei einer zur besseren Lesbarkeit angepassten Formatierung über fünfundzwanzig Zeilen erstreckt. Diese Komplexität ist der Preis der Automatisierung. In den Artikeln wird jedoch dargelegt, dass es sich lohnt, diesen Preis zu zahlen. Bedenken Sie beim Lesen jedoch die Alternativen: ein Raster aus einzeln eingegebenen WENN-Anweisungen, ein VBA-Makro oder eine Pivot-Tabelle, die manuell aktualisiert werden muss. Der Formelansatz ist schwieriger zu schreiben, aber einfacher zu handhaben.

Die strukturierte Tabelle bildet die Grundlage. Das Transaktionsbuch wird als formale Excel-Tabelle (tbl_Registro_UE) und nicht als einfacher Bereich gespeichert. Das ist kein Zufall. Es ist die Syntax der strukturierten Referenz – tbl_Registro_UE[CATEGORIA], tbl_Registro_UE[DENARO OUT] –, die die SUMMENWENNS-Formeln im Ist-Kostenblatt lesbar und robust macht. Wenn Sie eine strukturierte Referenz in einer Formel sehen, betrachten Sie diese also als bewusste Designentscheidung und nicht nur als Namenskonvention.

Nachstehend finden Sie einen Hinweis zu den Versionen.

Diese Arbeitsmappe enthält mehrere Formeln, die Funktionen verwenden, die in früheren Excel-Versionen nicht verfügbar waren. Dabei handelt es sich um die folgenden Funktionen: LET, LAMBDA, MATRIXERSTELLEN, NACHSPALTE, HSTAPELN und SEQUENZ. Diese Funktionen sind nur in Excel 365 (bzw. Excel 2021 und höher) verfügbar. Wenn Sie eine ältere Version verwenden, stehen Ihnen einige dieser Formeln nicht zur Verfügung. Die hier beschriebene Logik lässt sich jedoch auf alternative Ansätze anwenden, bei denen Hilfsspalten und als Array eingegebene ältere Formeln zum Einsatz kommen. Auf diese alternativen Ansätze wird an den entsprechenden Stellen hingewiesen.

Sind Sie bereit?

Die folgenden drei Artikel führen Sie vom komplexesten zum einfachsten Blatt, entsprechend dem natürlichen Datenfluss durch die Arbeitsmappe. Beginnen Sie mit AUTO_PROSPETTO_COSTI, um zu verstehen, wie die Basisberechnung aufgebaut ist. Alternativ können Sie mit REGISTRO_ENTRATE_&_USCITE beginnen, wenn Sie lieber mit den Rohdaten starten und sich dann vorarbeiten möchten. Beide Ansätze führen zum gleichen Ergebnis.

Unabhängig von der Reihenfolge erwartet Sie eine mit großer Sorgfalt erstellte Arbeitsmappe, die nicht nur die richtigen Zahlen liefert, sondern auch auf transparente und pflegbare Weise generiert, sodass Sie davon lernen können.

Lesen Sie den nächsten Teil dieser Serie: Eine vollständige, zeitlich gestaffelte Kostenbasis wird mit nur einer einzigen Formel erstellt. (Kostenbasis-Ausgabenplan, Teil 02)