Wenn Sie Microsoft 365 abonniert haben, können Sie in Excel diverse neue dynamische Array-Funktionen nutzen, die als einen Parameter eine LAMBDA-Funktion benötigen. Mit diesen Funktionen können Sie bestimmte Berechnungen eleganter durchführen als mit herkömmlichen Funktionen. Eine dieser neuen Funktionen ist die SCAN-Funktion, mit der Sie u.a. kumulierte Werte ermitteln können.

Aufbau der Funktionen SCAN()

Die neue Funktion führt, ausgehend von einem Startwert, für jede Zelle eines angegeben Zellbereichs eine Berechnung durch. Die Berechnung erfolgt durch eine LAMBDA-Funktion. Die Berechnungsergebnisse für jede Zelle werden in einem sog. Überlaufbereich ausgegeben. Der erzeugte Überlaufbereich hat genauso viel Zeilen und Spalten wie der zu untersuchende oder zu scannende Zellbereich.

Die Funktion erwartet als Parameter einen Start- oder Initialwert, einen zu untersuchenden Zellbereich bzw. Array sowie eine LAMBDA-Funktion.

=SCAN(Startwert;Array;LAMBDA-Funktion(Parameter1;Parameter2;Berechnung))

Die LAMDBA-Funktion erwartet zwei Parameter. Der erste Parameter dient zur internen Speicherung der Zwischenergebnisse nach Prüfung einer jeden Zelle. Er wird Akkumulator genannt. Der zweite Parameter steht für den aktuellen Zellwert aus dem ausgewählten Zellbereich. Er wird Iterator genannt. Alle Zellwerte im Zellbereich werden nacheinander wie in einer Schleife durchlaufen.

Grundsätzliche Informationen zur LAMBDA-Funktion finden Sie hier.

Der Einsatz der Funktion SCAN kann am besten an typischen Beispielen erläutert werden.

Beispiel 1: Kumulierte Monatserlöse für ein Jahr

Mit der Funktion SCAN() können Sie elegant kumulierte Monatserlöse für ein Jahr ermitteln. Die Tabelle mit den Monatserlösen sieht so aus:

Um die kumulierten Monatserlöse zu ermitteln, brauchen Sie in der Zelle C2 nur die folgende Formel mit der Funktion SCAN() eingeben:

=SCAN(0;B2:B13;LAMBDA(Zwischenwert;Zellwert;Zwischenwert+Zellwert))

Die Tabelle mit den kumulierten Monatserlösen sieht so aus:

Der Ergebnisse finden Sie im Zellbereich C2:C13. Dieser Bereich ist automatisch als Überlaufbereich angelegt worden, d.h. Sie brauchen die in Zelle C2 eingegebene Formel nicht nach unten kopieren.

Erläuterung der Funktion:

Der erste Parameter der SCAN-Funktion ist der Startwert 0. Am Anfang des Jahres beträgt der Umsatz noch 0 €. Der zweite Parameter kennzeichnet den Bereich mit den 12 Monatsumsatzwerten. Der dritte Parameter ist die notwendige LAMBDA-Funktion für die Berechnung der 12 Zwischenergebnisse. Diese LAMBDA-Funktion benötigt bei Einsatz der SCAN-Funktion genau zwei Parameter: Der erste Parameter steht für jeden einen einzelnen Zwischenwert. Der zweite Parameter wird für jeden zu prüfenden Zellwert benötigt. Im dritten Parameter wird die Berechnungsformel definiert. In unserem Beispiel soll nacheinander jeder Monatsumsatzwert zu dem aufgelaufenen Zwischenwert hinzuaddiert werden.

Beispiel 2: Entwicklung einer mehrjährigen Investition

Im zweiten Beispiel soll ermittelt werden, wie sich das eingesetzte Kapital in einem bestimmten Investitionszeitraum erhöht, wenn für jedes Jahr unterschiedliche Zinssätze bzw. Renditen erwartet werden. Das Anfangskapital beträgt 1.000 €. Die Tabelle mit den Zinssätzen und dem Endkapital nach jedem Jahr sieht so aus:

Die Formel für die Ermittlung der einzelnen Endkapitalwerte sieht so aus:

=SCAN(Q1;Q4:Q7;LAMBDA(x;y;x*(1+y)))

Im Unterscheid zum ersten Beispiel beträgt hier der Startwert 1.000 €. Er wird aus der Zelle Q1 übernommen. Die Parameter der LAMBDA-Funktion haben hier die Bezeichnung x und y. Die Namen der Parameter können Sie frei wählen. Sie dürfen nicht mit einer Zahl beginnen und keinen Zellbezug darstellen. Ein Parameter mit dem Namen A1 zum Beispiel ist nicht zulässig. Die Berechnung des Endkapitalwerts für jedes Jahr erfolgt durch die Formel x*(1+y).

Beispiel 3: Kumulierte Monatserlöse für zwei Jahre

In diesem Beispiel sollen die kumulierten Umsatzwerte für zwei Jahre ermittelt werden. Beachten Sie, dass zu Beginn des zweiten Jahres der kumulierte Jahresumsatz wieder auf den Wert 0 zurückgestellt werden soll. Diese Art von Berechnungen wird auch YTD (Year-To-Date) genannt.

Die Tabelle mit den Monatswerten und den kumulierten Werten sieht so aus:

Die verwendete Formel lautet folgendermaßen:

=SCAN(0;G2:G25;LAMBDA(Zwischenwert;Zellwert;WENN(MONAT(BEREICH.VERSCHIEBEN(Zellwert;0;-1))=1;Zellwert;Zwischenwert+Zellwert)))

Die SCAN-Funktion hat den Startwert 0 und untersucht den Zellbereich G2:G25. Um den kumulierten Monatswert zu ermitteln, überprüft die LAMDBA-Formel, ob es sich bei dem aktuellen Monat um einen Januar handelt, bei dem der aufgelaufene Wert auf 0 zurückgesetzt werden muss. Dazu wird die WENN-Funktion eingesetzt. Um den Monat zu ermitteln, muss ausgehend vom Umsatzwert des aktuellen Monats der Monat selbst ermittelt werden. Der Monat steht in der gleichen Zeile eine Spalte links vom Umsatzwert. Um diesen Wert zu ermitteln, wird die Funktion BEREICH.VERSCHIEBEN eingesetzt. Der erste Parameter kennzeichnet den aktuellen Zellwert. Der zweite Parameter dieser Funktion bezeichnet die Verschiebung der Zeilen nach oben und unten aus Sicht des aktuellen Zellwertes. Der Wert 0 signalisiert, dass keine Verschiebung der Zeile stattfindet. Der dritte Parameter –1 steht für eine Verschiebung um eine Spalte nach links. Wenn der Monatswert des zurückgegebenen Zellwerts gleich 1 ist, handelt sich bei der aktuellen Zeile um einen Januarwert. In diesem Fall soll die LAMBDA-Funktion den Wert des Januars zurückgeben. Deshalb erscheint an dieser Stelle in der WENN-Formel der Parameter Zellwert. Ansonsten wird der aktuelle Zellwert zum aufgelaufenen Zwischenwert hinzuaddiert.

Fazit

Mit der Funktion SCAN() können Sie für einen Zellbereich schnell Zwischenwerte nach einem bestimmten Formel ermitteln.

Hinweise

• Wenn Sie in der LAMDBA-Formel vor der Berechnungsfunktion statt zwei nur einen oder mehr als zwei Parameter eingeben, erscheint in der Zelle der folgende Fehler: #WERT!
• Wenn Sie in der LAMBDA-Funktion bei der Berechnung einen Parameter verwenden, den Sie vorher nicht definiert haben, erscheint in allen Zellen des Überlaufbereichs der Fehlerwert #NAME!.