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

Artikle 3: Den Kreislauf schließen — CATEGORIE_STIMATE_VS_EFFETTIVE und REGISTRO_ENTRATE_&_USCITE

Übersicht

In den ersten beiden Artikeln wurde erläutert, wie die Arbeitsmappe eine Kostenbasis erstellt und die tatsächlichen Ausgaben überwacht. Dieser abschließende Artikel untersucht, wo diese beiden Prozesse zusammenlaufen. CATEGORIE_STIMATE_VS_EFFETTIVE (Budgetkategorien: Schätzung vs. Ist) ist der Abweichungsbericht, der einen klaren Überblick über die Entwicklung der einzelnen Kategorien im Vergleich zum Budget bietet. REGISTRO_ENTRATE_&_USCITE (Einnahmen- und Ausgabenregister) ist das Rohdaten-Transaktionsbuch, das alle vorgelagerten Prozesse speist. Zusammen vervollständigen diese beiden Berichte die Datenpipeline. Transaktionen werden über das Transaktionsbuch erfasst, fließen durch die Ist-Kostenliste und erscheinen schließlich als Abweichungswerte im Vergleichsbericht.

Blatt 3: CATEGORIE_STIMATE_VS_EFFETTIVE

Der Zweck

Dieses Arbeitsblatt befasst sich mit einer grundlegenden Frage der Projektsteuerung: Welche Budgetkategorien liegen im Plan und welche nicht? Es bietet für jede Kategorie einen dreispaltigen Vergleich aus veranschlagtem Budget, Ist-Kosten und Abweichung. Die Spaltensummen sind jeweils oben aufgeführt.

Das Arbeitsblatt ist bewusst einfach gehalten. Eine zeitliche Aufschlüsselung ist nicht enthalten, da diese Arbeit bereits in den Arbeitsblättern 1 und 2 erledigt wurde. Die Aufgabe dieses Arbeitsblatts besteht darin, die zeitlich gegliederten Daten pro Kategorie in einer einzigen Zeile zusammenzufassen, wodurch Abweichungen sofort sichtbar werden.

Die LET-Formel in Zelle B9

Die Kategoriespalte (Zelle B9) wird durch eine einzige LET-Formel ausgefüllt. Diese Formel ruft die Kategoriedaten ab und erstellt die Vergleichstabelle.

=LET(
   cat,  _TRO_ALL(SETUP_BASELINE!B9:B200),
   bud,  _TRO_ALL(SETUP_BASELINE!C9:C200),
   ct,   _TRO_ALL(DATI_COSTI_EFFETTIVI!B9:B200),
   calc, WENNNV(HSTAPELN(cat, bud, ct), ""),
   calc
)

Es werden drei Arrays definiert:

• cat: die Kategorienamen aus SETUP_BASELINE
• bud: das geschätzte Budget aus SETUP_BASELINE,
• ct: die tatsächlichen Gesamtkosten aus Spalte B von Blatt 2, die wiederum durch die in Artikel 2 beschriebene SUMMEWENN-Formel ermittelt werden.

HSTAPELN fügt diese drei Arrays nebeneinander zusammen, um eine einzige übergreifende Ausgabe zu erstellen, die die Spalten B, C und D abdeckt. WENNNV(..., „”) unterdrückt Zeilen, in denen ein Wert einen Fehler darstellt, typischerweise leere Zeilen am Ende des 200-zeiligen Bereichs.

Die Spalte Abweichung (E9)

Spalte E ist einfach:

=SUMME(C9:C200)  [row 7 total]

Die Abweichung jeder Zeile entspricht der berechneten Differenz zwischen den tatsächlichen Ausgaben in Spalte D und dem Budget in Spalte C. Da die tatsächlichen Ausgaben unter dem Budget liegen, sind in den aktuellen Daten alle Abweichungen positiv – das Projekt läuft noch und es fallen weiterhin Kosten an.

Spalte Gesamtbetrag (Zeile 7)

C7: =SUMME(C9:C200)
D7: =SUMME(D9:D200)
E7: =SUMME(E9:E200)

Diese einfachen SUMMEN-Formeln funktionieren korrekt, obwohl die Bereiche C9:C200, D9:D200 und E9:E200 durch die Ausweitung von B9 gefüllt werden. Excel erkennt ausgeweitete Bereiche innerhalb von SUMME – die Summen werden automatisch aktualisiert, sobald neue Kategorien hinzugefügt werden oder sich die Budgets in SETUP_BASELINE ändern..

Blatt 4: REGISTRO_ENTRATE_&_USCITE

Der Zweck

Das Hauptbuch ist die zentrale Datenquelle für alle finanziellen Vorgänge. Jede geleistete Zahlung, jede eingegangene Rechnung und jeder Einnahmenposten wird hier als Zeile in der strukturierten Tabelle tbl_Registro_UE erfasst. Diese Tabelle bildet die Grundlage für die Ist-Kostensummen in Blatt 2 und somit auch für die Abweichungszahlen in Blatt 3.

Blatt 2 fungiert zudem als eigenständiges Finanzregister mit eigener Saldenverfolgung und einem Übersichtsbereich für jedes Konto.

Die strukturierte Tabelle: tbl_Registro_UE

Die Transaktionsdaten werden nicht als einfacher Bereich, sondern als formale Excel-Tabelle gespeichert. Dies hat mehrere architektonische Auswirkungen:

1. Strukturierte Verweise: Formeln an anderer Stelle in der Arbeitsmappe können auf Spalten anhand ihres Namens (z. B. tbl_Registro_UE[CATEGORIA] oder tbl_Registro_UE[DENARO OUT] ) statt anhand ihrer Adresse verweisen. Dadurch sind die Formeln selbsterklärend und unempfindlich gegenüber einer Neuanordnung der Spalten.
2. Automatische Erweiterung: Wenn der Tabelle eine neue Zeile hinzugefügt wird, beziehen alle abhängigen Formeln (insbesondere die SUMMEWENNS in Blatt 2) die neuen Daten automatisch ein, ohne dass eine manuelle Anpassung des Bereichs erforderlich ist.
3. Konsistente Spaltentypen: Die Tabelle erzwingt Datentypen auf Spaltenebene, wodurch das Risiko gemischter Text-/Zahleneingaben verringert wird. Diese würden die Übereinstimmung von SUMMEWENNS beeinträchtigen.

Die Spalten der Tabelle lauten: INDEX, DATA FATTURA (Rechnungsadatum), DATA EOM (Monatsende), CONTO (Konto), FORNITORE (Lieferant), DESCRIZIONE (Beschreibung), CATEGORIA (Kategorie), DENARO IN (Einnahme), DENARO OUT (Ausgabe), BALANCE (Saldo).

Die Auto-Index-Formel (Spalte A)

Zelle A8 enthält eine dynamische Array-Formel, die alle Zeilen automatisch nummeriert:

=SEQUENZ(ANZAHL(--tbl_Registro_UE[DATA EOM]),, 1, 1)

ANZAHL(--tbl_Registro_UE[DATA EOM]) zählt die Anzahl der nicht leeren Zeilen in der Spalte DATA EOM. Die doppelte Verneinung (--) mehr dazu erfahren Sie hier konvertiert Datumswerte in Zahlen, damit die Funktion ANZAHL sie erkennen kann. SEQUENZ(n, , 1, 1) erzeugt ein vertikales Array von 1 bis n. Das Ergebnis entspricht dabei stets der aktuellen Anzahl von Datenzeilen. Beim Hinzufügen von Zeilen ist keine manuelle Neunummerierung erforderlich.

Die DATA EOM Spalte (Spalte C)

Bei jeder Transaktion wird das Rechnungsdatum (DATA FATTURA) auf den letzten Tag des jeweiligen Kalendermonats normiert:

=WENN($B8="", "", MONATSENDE(B8, 0))

Die Funktion MONATSENDE(Datum, 0) gibt den letzten Tag des Monats zurück, in dem das angegebene Datum liegt. Die WENN-Bedingung gibt einen leeren Wert zurück, wenn kein Rechnungsdatum vorhanden ist. Durch diese Normalisierung wird die Lücke zwischen dem Rohbuch und der Verteilungsmatrix in Blatt 2 geschlossen. Durch die Ausrichtung aller Transaktionen auf ihr Monatsenddatum kann die SUMMEWENNS-Funktion in Blatt 2 mithilfe eines einfachen Gleichheitstests diese der richtigen Monatsspalte zuordnen.

Die Spalte „Laufender Saldo“ (Spalte J)

Die Spalte BALANCE erfasst die kumulierte Nettoposition nach jeder Transaktion. Die Formel lautet:

=TEILERGEBNIS(9, tbl_Registro_UE[[#Headers],[DENARO IN]]:tbl_Registro_UE[[#This Row],[DENARO IN]])
-TEILERGEBNIS(9, tbl_Registro_UE[[#Headers],[DENARO OUT]]:tbl_Registro_UE[[#This Row],[DENARO OUT]])

Hierbei wird die Funktion TEILERGEBNIS mit dem (Summen-)Funktionscode 9 in einem sich ausdehnenden Bereichsmuster verwendet. Der Bereich beginnt an der Spaltenüberschrift ([#Headers]) und endet an der aktuellen Zeile ([#This Row]). Da der Startpunkt fest an der Überschrift liegt und sich der Endpunkt mit jeder Zeile verschiebt, erweitert sich die Summe Zeile für Zeile und es ergibt sich eine kumulierte Gesamtsumme.

Der entscheidende Vorteil der Verwendung von TEILERGEBNIS anstelle von SUMME besteht darin, dass TEILERGEBNIS filterbewusst ist: Wenn die Tabelle gefiltert wird, um nur bestimmte Kategorien oder Datumsbereiche anzuzeigen, berechnet TEILERGEBNIS den Saldo neu, wobei nur die sichtbaren Zeilen berücksichtigt werden. Eine einfache SUMME würde den Filter ignorieren.

Die Formel für den Nettosaldo im Kopfbereich (Zelle J4) verwendet eine einfachere Version.

=H6 - I6

Dabei sind H6 und I6 die Gesamtbeträge der Transaktionen für DENARO IN bzw. DENARO OUT, die sich wie folgt berechnen:

H6: =TEILERGEBNIS(9, tbl_Registro_UE[[#All],[DENARO IN]])
I6: =TEILERGEBNIS(9, tbl_Registro_UE[[#All],[DENARO OUT]])

Das Fenster „Kontostandsübersicht“

Oben rechts auf dem Blatt befindet sich ein übersichtliches Feld, in dem der Nettosaldo für jedes registrierte Konto angezeigt wird (Spalten M–N). Die Kontonamen werden aus der Tabelle SETUP_REGISTRO abgerufen.

M1: =WENN(SETUP_REGISTRO!B2="", "", SETUP_REGISTRO!B2)
M2: =WENN(SETUP_REGISTRO!B3="", "", SETUP_REGISTRO!B3)
...

Der Kontostand jedes Kontos wird berechnet durch:

N1: =WENN(M1="", "",
     SUMMEWENNS(tbl_Registro_UE[[#All],[DENARO IN]],  tbl_Registro_UE[[#All],[CONTO]], M1)
   - SUMMEWENNS(tbl_Registro_UE[[#All],[DENARO OUT]], tbl_Registro_UE[[#All],[CONTO]], M1)
)

Hier filtert die Funktion SUMMEWENNS nach dem Kontonamen in der Spalte CONTO und summiert alle Ein- und Auszahlungen für jedes Konto separat. Anschließend werden diese voneinander abgezogen, um den Saldo zu ermitteln. Die Funktion WENN(M1="", "", ...) sorgt dafür, dass leere Kontopositionen als leer und nicht als Null angezeigt werden.

Die gesamte Datenpipeline

Nachdem alle vier Arbeitsblätter beschrieben wurden, ist es hilfreich, sich den Datenfluss von der Eingabe bis zur Berichterstellung durch die Arbeitsmappe vorzustellen.

SETUP_BASELINE
  │  Projekt Parameter, Kategorie Liste, Arbeitstage
  │
  ├──► AUTO_PROSPETTO_COSTI  (Blatt 1)
  │      MATRIXERSTELLEN × NETTOARBEITSTAGE.INTL → zeitliche Kostenbasis
  │
  └──► DATI_COSTI_EFFETTIVI  (Blatt 2)
         LET/HSTAPELN ruft die Kategorieliste ab
         SUMMEWENNS (2D) gegen tbl_Registro_UE → zeitlich gestaffelte Ist-Werte
              │
              ▼
REGISTRO_ENTRATE_&_USCITE  (Blatt 4)
  tbl_Registro_UE (strukturierte Tabelle)
  MONATSENDE Normalisierung → DATA EOM
  TEILERGEBNIS laufender Saldo
              │
              ▼
CATEGORIE_STIMATE_VS_EFFETTIVE  (Blatt 3)
  LET/HSTAPELN: cat + bud (Blatt 1) vs ct (Blatt 2) → Abweichungsbericht

Jede Änderung im Hauptbuch wird automatisch weitergeleitet. Wenn Sie beispielsweise in Blatt 4 eine neue Rechnung hinzufügen, werden die Ist-Kostensummen in Blatt 2 sofort aktualisiert. Dies hat unmittelbare Auswirkungen auf die Abweichungszahlen in Blatt 3. Ein manuelles Aktualisieren oder Kopieren und Einfügen ist nicht erforderlich.

Wichtige Erkenntnisse

Blatt 3:

• Eine LET/HSTAPELN-Formel fasst den dreispaltigen Vergleich aus SETUP_BASELINE und Blatt 2 zu einem einzigen Ausgabelebensraum zusammen – die gesamte Tabelle wird von einer einzigen Formel gesteuert.
• Die Spaltensummen verwenden die einfache SUMME-Formel auf die ausgabegespeicherten Bereiche und werden automatisch aktualisiert.
• Die Abweichung ergibt sich aus dem Budget abzüglich der Ist-Werte und wird als positive Zahl ausgedrückt, wenn der Wert unter dem Budget liegt.

Blatt 4:

• Die strukturierte Tabelle tbl_Registro_UE verwendet durchgehend benannte Spalten. Dadurch sind alle abhängigen Formeln lesbar und gegenüber strukturellen Änderungen robust.
• Die entscheidende Verknüpfung, die den zweidimensionalen SUMMEWENNS-Abgleich in Blatt 2 ermöglicht, ist die MONATSENDE-Normalisierung in der Spalte DATA EOM.
• Der Laufsaldo verwendet die Funktion „TEILERGEBNIS” in einer erweiterbaren strukturierten Referenz. Dies ergibt eine kumulierte Nettoposition, die Tabellenfilter berücksichtigt.
• Die im Übersichtsbereich angezeigten Salden pro Konto werden mithilfe der Funktion SUMMEWENNS berechnet, nach der Spalte CONTO gefiltert und durch die in der Tabelle SETUP_REGISTRO gespeicherten Kontonamen gesteuert.

Visualisierungen (dieser Artikel)

Abbildung 1: Die gesamte Datenpipeline der Arbeitsmappe. Durchgängiger Datenfluss von SETUP_BASELINE über die Blätter 1, 2 und 4, der im Abweichungsbericht in Blatt 3 zusammenläuft.

Abbildung 2: Erweiterungsbereich von TEILERGEBNIS – Darstellung, wie sich der laufende Saldo aufbaut. Zeilenweise wird dargestellt, wie sich die strukturierte Referenz [#Headers]:[#This Row] bei jeder neuen Transaktion erweitert. Zudem wird erläutert, warum TEILERGEBNIS gegenüber SUMME bevorzugt wird, um Filterbedingungen zu berücksichtigen.

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)

Daten aus einem Kontenbuch fließen in eine zeitlich gegliederte Ist-Kostenmatrix. (Kostenbasis-Ausgabenplan, Teil 03)