FAQ — Welche Fragen werden in diesem Artikel beantwortet?

Was ist „MATRIXERSTELLEN“ und warum sollte ich diese Funktion anstelle des Kopierens einer Formel über mehrere Zellen hinweg verwenden?

A: „MATRIXERSTELLEN“ ist eine Funktion von Excel 365, mit der sich aus einer einzigen Formel eine Matrix mit Werten erstellen lässt. Anstatt wie bisher eine Formel in jede Zelle einzufügen und sie über Zeilen und Spalten hinweg zu kopieren, berechnet „MATRIXERSTELLEN” alle Werte in einem einzigen Schritt. Das bedeutet, dass die Logik nur an einer Stelle vorhanden ist. Wenn Sie diese ändern müssen, müssen Sie nur diese eine Formel ändern, und die gesamte Matrix wird aktualisiert. Es gibt nichts zu kopieren, nichts, was kaputtgehen könnte, und nichts, was übersehen werden könnte.

Was bewirkt die Funktion „LET“ eigentlich in einer Formel?

A: Es werden Zwischenwerten oder Berechnungen Namen zugewiesen, sodass diese in derselben Formel wiederverwendet werden können, ohne dass die Berechnungen wiederholt werden müssen. Dieser Vorgang ähnelt der Definition von Variablen in einer Programmiersprache. Anstatt einen komplexen Ausdruck dreimal zu schreiben, schreiben Sie ihn einmal, geben ihm einen Namen und verweisen dann auf diesen Namen. Dadurch werden Formeln kürzer, schneller und für jeden lesbar, der sie öffnet – nicht nur für die Person, die sie erstellt hat.

Warum gibt die Formel für Monate, in denen eine Kategorie keine Ausgaben aufweist, „NA“ statt „0“ zurück?

A: Die Formel gibt bewusst „NA“ statt „0“ zurück und verwendet dabei den Ausdruck WENNFEHLER(monthlyDays = 0, NA(), dailyRate * monthlyDays). Dies ist eine bewusste Designentscheidung. „Null” bedeutet, dass in dieser Kategorie in diesem Monat keine geplanten Ausgaben vorhanden sind. „NA“ bedeutet dagegen, dass diese Kategorie in diesem Monat überhaupt nicht existiert. Die meisten Diagrammtypen lassen NA-Zellen vollständig aus, während Null-Zellen als Datenpunkt bei Null dargestellt werden. Die Verwendung von NA führt zu übersichtlicheren und genaueren Diagrammen.

Woher weiß die Formel, welche Feiertage bei der Berechnung der Arbeitstage ausgeschlossen werden müssen?

A: Der benannte Bereich _DataFestivita verweist auf das spezielle Arbeitsblatt feste_italia_sardegna, das 52 Feiertagsdaten für die Jahre 2024, 2025 und 2026 enthält. Darunter befinden sich italienische Nationalfeiertage sowie der sardische Regionalfeiertag „Sa die de sa Sardigna” am 28. April. Dieser Bereich wird direkt als Parameter für die Feiertage an die Funktion NETTOARBEITSTAGE.INTL übergeben. Das bedeutet, dass bei jeder Berechnung der Arbeitstage in der gesamten Matrix diese Daten automatisch ausgeschlossen werden. Eine manuelle Anpassung ist somit nicht erforderlich.

Was passiert, wenn Daten einer Kategorie fehlen oder falsch sind?

A: Jede Datumsberechnung in der Formel ist in eine IFERROR-Funktion mit einem sicheren Ausweichwert eingebettet. Fehlt das Startdatum einer Aufgabe, wird dieses durch die Funktion IFERROR(taskStart, DATE(2100,1,1)) durch ein Datum weit in der Zukunft ersetzt. Dadurch wird sichergestellt, dass die MAX-Berechnung für Periodenüberschneidungen niemals ein negatives Ergebnis liefert. Wenn kein Aufgabenenddatum angegeben ist, verhält es sich ähnlich: Die Formel IFERROR(taskEnd, DATE(1900,1,1)) wird durch das frühestmögliche Excel-Datum ersetzt. So wird sichergestellt, dass die MIN-Berechnung den Zeitraum auf null reduziert. Die äußere IFERROR-Funktion fängt dann alles ab, was dennoch einen Fehler erzeugt, und gibt stattdessen eine leere Zeichenfolge zurück.

Innerhalb der Formel gibt es die Möglichkeit, ausgehend von einer einzelnen Zelle eine Kostenbasis zu erstellen. Dies geschieht mithilfe der Funktionen MATRIXERSTELLEN, LET und LAMBDA.

In dieser Arbeitsmappe befindet sich eine Formel in einer einzigen Zelle, die gleichzeitig 156 Werte generiert.

Mithilfe dieser Formel wird die monatliche Kostenverteilung für 13 Budgetkategorien über einen Zeitraum von 12 Monaten berechnet. Sie berücksichtigt italienische nationale sowie sardische regionale Feiertage, behandelt Kategorien, die mitten im Monat beginnen oder enden, korrekt und schützt vor fehlenden Daten sowie vor Division durch Null. Für Monate, in denen für eine Kategorie keine geplanten Ausgaben vorliegen, gibt sie „NA” anstelle von Null zurück.

All dies geschieht ohne einen einzigen sichtbaren Fehler. Und das alles erfolgt aus einer einzigen Zelle heraus.

Mithilfe eines Formel-Explorers wird in diesem Artikel die Formel aufgeschlüsselt, wobei bei jedem Schritt der Zwischenwert angezeigt wird. Dadurch wird sichtbar gemacht, was sonst nur mit fundierten Excel-Kenntnissen zu verstehen wäre.

Das Ziel besteht nicht darin, Sie dazu zu ermutigen, Formeln wie diese zu schreiben. Vielmehr soll gezeigt werden, was möglich ist, und es soll erklärt werden, warum sie so konzipiert wurde. Dabei werden die Faktoren hervorgehoben, die zu ihrer Wartbarkeit beitragen, anstatt zu ihrer Anfälligkeit.

Die vollständige Formel:

Dies ist die vollständige Formel aus der Zelle AUTO_PROSPETTO_COSTI!$G$9, genau so, wie sie in der Formelleiste angezeigt wird:

=WENNFEHLER(LET(
    startDates; C9:C21;
    endDates; D9:D21;
    taskAmounts; B9:B21;
    totalDays; SETUP_BASELINE!G9:G21;
    headerDates; 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(DATUM(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
            )
        )
    )
);"")

Auf den ersten Blick mag dies wie ein riesiger Textblock wirken. Am Ende des Artikels wird jedoch jede Zeile Sinn ergeben.

Ein struktureller Überblick über die Verschachtelung der Ebenen

Die Formel besteht aus vier unterschiedlichen Ebenen, von denen jede eine bestimmte Funktion erfüllt. Betrachtet man die Funktion jeder Ebene für sich, lässt sich das Ganze wesentlich leichter nachvollziehen.

Ebene 1 – WENNFEHLER: Das Sicherheitsnetz. Sollte ein Teil der Formel aus irgendeinem Grund einen Fehler verursachen, erkennt WENNFEHLER diesen und gibt stattdessen eine leere Zeichenfolge zurück. Der Benutzer sieht den Fehler somit nie. So bleibt die Arbeitsmappe präsentabel, selbst wenn die Daten unvollständig sind.

Ebene 2 – Äußere LET: Die Vorbereitungsphase. Bevor Berechnungen durchgeführt werden, sammelt LET alle erforderlichen Daten und weist jedem Element einen eindeutigen Namen zu. Es werden fünf benannte Variablen definiert: startDates, endDates, taskAmounts, totalDays und headerDates. In allen nachfolgenden Schritten werden diese Namen anstelle von reinen Zellbezügen verwendet.

Ebene 3 – MATRIXERSTELLEN: Der Matrix-Generator. MATRIXERSTELLEN wird angewiesen, ein Raster mit 13 Zeilen und 12 Spalten zu erstellen. Für jede Position im Raster wird die LAMBDA-Funktion mit der Zeilennummer r und der Spaltennummer c aufgerufen.

Ebene 4 – LAMBDA mit innerem LET: Die Berechnungsengine. Für jede Zeilen-Spalten-Kombination definiert die innere LET-Anweisung zusätzliche benannte Variablen und führt die Berechnung der monatlichen Kosten durch.

Die Besonderheiten von Layer 2: Die Bezeichnungen und warum sie wichtig sind.

Wird im Komponentenbaum die Option startDates im Formel-Explorer markiert, wird der Wert im rechten Fensterbereich angezeigt: eine Spalte mit 13 Datumsangaben im Excel-Seriennummernformat. Dabei handelt es sich um die Startdaten aller 13 Budgetkategorien, die direkt aus den Zellen C9:C21 derselben Tabelle ausgelesen werden.

Das gleiche Muster gilt für alle fünf benannten Variablen.

Besondere Beachtung verdient die Referenz G8# für headerDates. Das Symbol # bedeutet „wo immer diese Formel überläuft“; es handelt sich um eine dynamische Überlaufreferenz. Wenn sich die Projektdauer ändert und zusätzliche Monatsspalten hinzugefügt werden, erweitert sich headerDates automatisch, um diese einzubeziehen. MATRIXERSTELLEN erstellt dann eine breitere Matrix, ohne dass eine manuelle Aktualisierung der Formel erforderlich ist.

Das bedeutet in der Praxis eine einzige Quelle der Wahrheit. Ändern Sie die Projektdaten in „SETUP_BASELINE“, und die Formel passt sich automatisch an.

Ebene 3 im Detail: Wie MATRIXERSTELLEN aufgebaut ist.

Die Funktion MATRIXERSTELLEN erwartet drei Argumente: die Anzahl der Zeilen, die Anzahl der Spalten und eine LAMBDA-Funktion, die für jede Position aufgerufen werden soll.

ZEILEN(startDates) ergibt den Wert 13, da es 13 Budgetkategorien gibt. SPALTEN(headerDates) ergibt den Wert 12, da es 12 Spalten für die Monate gibt.

Die Funktion MATRIXERSTELLEN ruft dann 156 Mal die Funktion LAMBDA(r, c, ...) auf, einmal für jede Kombination aus Zeile und Spalte. Beim ersten Aufruf sind r = 1 und c = 1 (Kategorie 1, Monat 1). Beim letzten Aufruf sind r = 13 und c = 12 (Kategorie 13, Monat 12).

Das Ausgabefenster im Explorer zeigt die vollständige 13 × 12-Matrix an. Die meisten Zellen enthalten einen Geldwert. Viele enthalten #N/A, dies sind die Monate, für die in dieser Kategorie keine Ausgaben geplant sind. Diese #N/A-Werte sind beabsichtigt, wie in den oben stehenden FAQ erläutert.

Ebene 4 im Detail: die Berechnungsengine für jede Zelle.

Die innere LET-Funktion definiert 12 benannte Variablen für jede Zeilen-Spalten-Position. Wenn man diese der Reihe nach liest, wird der gesamte Berechnungsprozess deutlich.

taskStart und taskEnd werden jeweils aus startDates und endDates abgerufen, wobei INDEX mit der aktuellen Zeilennummer r verwendet wird. Für Zeile 1 sind dies das Start- und Enddatum von CAT_01.

headerDate wird mithilfe der Funktion INDEX und der aktuellen Spaltennummer c aus headerDates abgerufen. Für Spalte 1 ist dies die Überschrift des ersten Monats, in dieser Arbeitsmappe also der 1. Februar 2024.

amount und taskTotalDays: der Budgetbetrag und die Gesamtzahl der Arbeitstage für diese Kategorie, die beide zeilenweise abgerufen werden.

dailyRate: WENNFEHLER(amount / taskTotalDays, 0). Das Budget geteilt durch die Gesamtzahl der Arbeitstage ergibt die Kosten pro Arbeitstag. Die WENNFEHLER-Funktion schützt vor einer Division durch Null, falls taskTotalDays den Wert Null hat.

monthStart und monthEnd: der erste bzw. der letzte Tag des aktuellen Monats. monthStart wird als DATUM(JAHR(headerDate), MONAT(headerDate), 1) berechnet, d. h. als der erste Tag des Monats. monthEnd ist MONATSENDE(monthStart, 0), also der letzte Tag desselben Monats. Beide Werte werden in eine WENNFEHLER-Funktion eingeschlossen, die ein Ausweichdatum enthält, falls das Kopfdatum fehlt.

periodStart und periodEnd: Berechnung der Überschneidung

Dies ist der Teil der Formel, der technisch am interessantesten ist.

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

periodStart ist das spätere der beiden Daten, also das Startdatum der Aufgabe oder das Startdatum des Monats. Wenn die Aufgabe beispielsweise am 15. Februar beginnt, der Monat jedoch am 1. Februar startet, ist das Startdatum des Zeitraums der 15. Februar, da die Aufgabe in den ersten beiden Wochen des Monats nicht aktiv war.

periodEnd ist das frühere der beiden Daten, also das Enddatum der Aufgabe oder das Monatsende. Wenn die Aufgabe beispielsweise am 10. März endet, das Monatsende jedoch am 31. März liegt, ist das Periodenende der 10. März, da die Aufgabe in den letzten drei Wochen des Monats nicht aktiv war.

Die WENNFEHLER-Ausweichlösungen sind beabsichtigt. Fehlt taskStart, wird ein weit in der Zukunft liegendes Datum (Jahr 2100) eingesetzt, sodass periodStart nach periodEnd liegt, wodurch isActive auf FALSCH gesetzt wird. Fehlt hingegen taskEnd, wird das früheste Excel-Datum (Jahr 1900) eingesetzt, sodass „periodEnd” vor periodStart liegt und isActive wiederum auf FALSE gesetzt wird.

isActive

isActive; periodStart <= periodEnd

Mit diesem einzelnen Ausdruck wird bestimmt, ob sich diese Kategorie mit diesem Monat überschneidet. Wenn der Beginn des Zeitraums (periodStart) am oder vor dem Ende des Zeitraums (periodEnd) liegt, war die Kategorie zu einem bestimmten Zeitpunkt im Laufe des Monats aktiv. Das Ergebnis ist entweder WAHR (1) oder FALSCH (0).

<

Wie im obigen Screenshot zu sehen ist, wird die Berechnung der Arbeitstage vollständig übersprungen und monthlyDays auf Null gesetzt, wenn isActive auf FALSE gesetzt ist. Dieses boolesche Gatter steuert die gesamte Kostenberechnung.

monthlyDays

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

Wenn isActive den Wert WAHR hat, zählt NETTOARBEITSTAGE.INTL die Arbeitstage zwischen periodStart und periodEnd, wobei Wochenenden und die 52 italienischen und sardischen Feiertage, die in _DataFestivita gespeichert sind, ausgeschlossen werden. Das Ergebnis ist die Anzahl der Arbeitstage, an denen diese Kategorie im laufenden Monat aktiv war.

Wenn isActive FALSCH ist, ist das Ergebnis sofort Null und NETTOARBEITSTAGE.INTL wird nie aufgerufen.

Die WENNFEHLER-Funktion, die die Funktion NETTOARBEITSTAGE.INTL umgibt, fängt einen bestimmten Randfall ab: Wenn periodStart aufgrund eines Rundungs- oder Datumsgenauigkeitsproblems geringfügig nach periodEnd liegt, gibt NETTOARBEITSTAGE.INTL eine negative Zahl zurück. WENNFEHLER erkennt dies und gibt stattdessen Null zurück.

monthlyAmount — das Endergebnis

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

Wenn monthlyDays den Wert Null annimmt, sei es, weil die Kategorie inaktiv war oder weil der Überlappungszeitraum keine Arbeitstage enthielt (beispielsweise ein Zeitraum, der vollständig auf Wochenenden und Feiertage fällt), lautet das Ergebnis „NA“. Andernfalls lautet das Ergebnis dailyRate * monthlyDays, also die Kosten pro Arbeitstag multipliziert mit der Anzahl der berechtigten Arbeitstage im aktuellen Monat.

Die letzte Zeile des inneren LET lautet einfach monthly Amount. Dies weist LET an, diesen Wert zurückzugeben. Er sollte als Ausgabe für diese Zeilen-Spalten-Position zurückgegeben werden.

Warum ist diese Architektur für die Wartung von Bedeutung?

Eine derart komplexe Formel könnte leicht unübersichtlich werden. Dies ist jedoch aus drei konkreten Gründen nicht der Fall.

Erstens gibt es eine Formel und einen Änderungspunkt. Alle 156 Werte in der Matrix stammen aus einer einzigen Formel in Zelle G9. Wenn sich die Verteilungslogik ändern muss, beispielsweise wenn Wochenendtage einbezogen oder ein anderer Feiertagskalender verwendet werden soll, muss die Änderung nur an dieser einen Stelle vorgenommen werden. Die gesamte Matrix wird aktualisiert.

Benannte Variablen und verständliche Logik: Jeder Zwischenwert hat einen aussagekräftigen Namen. isActive, dailyRate, periodStart und monthlyDays sind Beispiele für Begriffe, die ein Projektmanager versteht. Vergleichen Sie dies mit der folgenden Formel, die dieselbe Logik verwendet, jedoch keine Namen enthält: =WENN(MAX(C9,G$8)<=MIN(D9,MONATSENDE(G$8,0)), NETTOARBEITSTAGE.INTL(MAX(C9,G$8), MIN(D9,MONATSENDE(G$8,0)),1,...) * (B9/SETUP_BASELINE!G9),NA(). Korrekt, aber unlesbar.

Abgesicherte Randfälle: Jeder Punkt, an dem ein Fehler auftreten könnte, wie z. B. Division durch Null, fehlende Datumsangaben oder negative Werktagszahlen, wird durch eine spezifische WENNFEHLER-Funktion mit einer spezifischen Ausweichlösung abgesichert. Dies stellt sicher, dass sich die Formel korrekt verhält, anstatt einen sichtbaren Fehler zu erzeugen.

Der Formel-Explorer zeigt Informationen an, die in der Formelleiste nicht enthalten sind.

Die Formelleiste zeigt Ihnen, was in der Formel steht. Der Formel-Explorer zeigt Ihnen, was die Formel in jedem Schritt, für jede Variable und bei jedem Zwischenwert tut.

Ohne den Explorer müssen Sie die gesamte Berechnungskette im Kopf durchspielen, um die Formel zu verstehen. Mit dem Explorer können Sie hingegen eine beliebige Variable im Komponentenbaum auswählen. Daraufhin wird deren tatsächlicher Wert im rechten Fensterbereich angezeigt. So sehen Sie beispielsweise, dass _DataFestivita 52 Zeilen mit Feiertagsdaten ergibt. Außerdem können Sie erkennen, dass isActive für eine bestimmte Zelle auf FALSCH gesetzt ist, da periodStart auf 45.627 und periodEnd auf 45.322 gesetzt ist. Diese beiden Datums-Seriennummern zeigen an, dass sich der aktive Zeitraum dieser Kategorie nicht mit dem aktuellen Monat überschneidet. Zudem sehen Sie, dass NETTOARBEITSTAGE.INTL den Wert -213 zurückgeben würde, wenn WENNFEHLER dies nicht abgefangen hätte, eine negative Zahl, die zu negativen Kosten führen würde, wenn sie unberücksichtigt bliebe.

Dies veranschaulicht den Unterschied zwischen dem Lesen einer Karte und dem Begehen des Geländes. Die Formelleiste ist wie eine Karte. Sie sind der Entdecker.

Über dieses Arbeitsbuch

In diesem Artikel wird eine Arbeitsmappe analysiert, die als Projektkostenbasis und Ausgabenplan für Kunden aus der italienischen Baubranche erstellt wurde. Zu Demonstrationszwecken wurden darin Dummy-Daten verwendet. Der begleitende Artikel behandelt die vollständige Struktur der Arbeitsmappe, die Analyseergebnisse und die Architektur.

Was eine Arbeitsbuchanalyse tatsächlich offenbart und warum nicht alles, was falsch erscheint, auch tatsächlich falsch ist

Dieser Artikel ist Teil der Reihe auf helpme.safeoffice.de, die praktische Anleitungen zu Datenlösungen, Arbeitsmappenmodellierung und Prozessdokumentation für Unternehmen bietet, die Tools benötigen, die effektiv, langlebig und für alle Nutzer leicht verständlich sind.