In diesem Tipp stellen wir Ihnen mit REDUCE eine weitere neue Excel-Funktion vor, mit der Sie bestimmte Berechnungen eleganter durchführen als mit herkömmlichen Funktionen. Die Funktion REDUCE() können Sie u.a. als Alternative zu den Funktion ZÄHLWENWENN und SUMMEWENN einsetzen.

Aufbau der Funktionen REDUCE()

Die Funktion REDUCE() reduziert die Berechnungen, die Sie mit den Zellen eines Zellbereichs durchführen, auf einen einzigen Wert. Die Berechnungen für jede Zelle erfolgen durch eine LAMBDA-Funktion. Diese Art der Berechnung ist das Besondere an dieser Funktion und unterscheidet Sie von anderen Funktionen wie z. B: SUMME und MITTELWERT. Bei den Berechnungen können Sie einen Start- oder Initialwert vorgeben.

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

=REDUCE(Startwert;Array;LAMBDA(Parameter1;Parameter2;Berechnung))

Die LAMDBA-Funktion erwartet genau 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 REDUCE() kann am besten an einigen Beispielen erläutert werden.

Beispiel 1: Anzahl der Werte ermitteln, die einen bestimmten Betrag überschreiten

Im ersten Beispiel soll die Anzahl der Monate ermittelt werden, in denen der Umsatz größer oder gleich 1.000 € ist.

Die Tabelle sieht so aus:

Wenn Sie in der Zelle B16 den Mindestumsatz von 1.000 € eintragen, ermitteln Sie die Anzahl Monate mit der folgenden Formel in Zelle B17:

=REDUCE(0;B2:B13;LAMBDA(x;y;WENN(y>=B16;x+1;x)))

Ergebnis: Es gibt 6 Monate, in denen der Mindestumsatz erreicht wurde.

Erläuterung der Funktion:

Der erste Parameter der REDUCE-Funktion ist der Startwert 0. Am Anfang des Jahres gibt es noch keinen Monat mit einem Umsatz, der größer oder gleich 1.000 € ist. 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 REDUCE-Funktion genau zwei Parameter: Der erste Parameter x steht für jeden einen einzelnen Zwischenwert. Der zweite Parameter y wird für jeden zu prüfenden Umsatzwert im angegebenen Zellbereich benötigt. Im dritten Parameter wird die Berechnungsformel definiert. In unserem Beispiel soll der Zwischenwert jeweils um 1 erhöht werden, wenn der Umsatz des Monats den Mindestumsatz, der in Zelle B16 steht, erreicht. Falls die Bedingung nicht erfüllt ist, bleibt der Zwischenwert x unverändert.

Beispiel 2: Summe der Umsätze, die einen bestimmten Betrag überschreiten

Sie können die Funktion REDUCE() auch als Ersatz für die Funktionen SUMEMWENN bzw. SUMMEWENNS nehmen.

Im zweiten Beispiel soll die Summe der Monatsumsätze errechnet werden, in denen der Mindestumsatz von 1.000 € erreicht wurde.

Die Formel lautet folgendermaßen:

=REDUCE(0;B2:B13;LAMBDA(x;y;WENN(y>=B16;x+y;x)))

Ergebnis: Die Summe der Umsätze größer oder gleich 1.0000 € beträgt 7.030 €.

Der einzige Unterschied zur Formel im ersten Beispiel ist der dritte Parameter der LAMBDA-Funktion. Falls die Bedingung in der WENN-Funktion erfüllt ist, wird der jeweilige Monatsumsatz, dargestellt durch den Parameter y, auf den bereits aufgelaufenen Umsatz hinzuaddiert. Dafür steht der Ausdruck x+y. Ansonsten wird der Zwischenwert, dargestellt durch den Parameter x, nicht erhöht wird.

Beispiel 3: Betrag, der letztmalig größer als ein Mindestwert ist

Im dritten Beispiel soll der letzte Umsatzbetrag ermittelt werden, der größer oder gleich dem Mindestumsatz ist.

Die Formel lautet folgendermaßen:

=REDUCE(0;B2:B13;LAMBDA(x;y;WENN(y>=B16;y;x)))

Ergebnis: Der letzte Betrag lautet 1.060 €

Beispiel 4: Monat, in dem letztmalig ein Mindestwert erreicht wurde

Im nächsten Beispiel soll nicht der letzte Umsatzbetrag, sondern der zugehörige Monat ermittelt werden, in dem letztmalig der Mindestumsatz erreicht wurde.

Mit folgender Formel können Sie den Monat aus Spalte A bestimmen:

=REDUCE(0;B2:B13;LAMBDA(x;y;WENN(y>=B16;BEREICH.VERSCHIEBEN(y;0;-1);x)))

Ergebnis: Im Monat November wurde der Mindestumsatz letztmalig erreicht.

Im dritten Parameter der LAMBDA-Funktion wird die Funktion BEREICH.VERSCHIEBEN verwendet. Wenn der Mindestumsatz erreicht wurde, wird ausgehend von der aktuellen Zelle mit dem Monatswert (Bezug: y) der Wert, der in der gleichen Zelle (2. Parameter mit dem Wert=0) eine Spalte links steht (3. Parameter mit dem Wert = -1), genommen. Falls die Bedingung nicht erfüllt ist, bleibt es bei dem letzten Monat, der den Wert erreicht hat. Dafür steht der Parameter x.

Im Folgenden sehen Sie die Tabelle mit den Umsatzwerten und den Berechnungsergebnissen, die alle mit der Funktion REDUCE erzeugt wurden:

Beispiel 5: Endkapital einer mehrjährigen Investition

In diesem Beispiel soll ermittelt werden, wie hoch das Endkapital am Ende eines Investitionszeitraums ist, 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 Endkapitalwerts sieht so aus:

= REDUCE(B1;B4:B7;LAMBDA(Startwert;Zellwert;Startwert*(1+Zellwert)))

Ergebnis: Der Endkapitalwert beträgt 1.100,58 €

Im Unterscheid zu den vorhergegangenen Beispielen beträgt hier der Startwert 1.000 €. Er wird aus der Zelle B1 übernommen. Die Parameter der LAMBDA-Funktion haben hier die Bezeichnung Startwert und Zellwert. 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 Startwert*(1+Zellwert).

Das Ergebnis sieht so aus:

Das gleiche Endkapital errechnet sich auch durch folgende Kontrollformel:
=ZW2(B11;B4:B7)

Beispiel 6: Mehrmaliges Ersetzen eines Ausdrucks in einem Text

Die Funktion REDUCE() können Sie einsetzen, um in einem Text nach bestimmten Ausdrücken zu suchen und durch einen bestimmten Ausdruck zu ersetzen. In dem folgenden Beispiel soll die Schreibweise von Straßen vereinheitlicht werden.

Das Tabellenblatt sieht mit den Straßennamen sieht so aus:

Wenn im Straßennamen in Spalte A der Ausdruck strasse oder straße erscheint, soll er durch str. ersetzt werden: Die Formel für die erste Straße in Zelle B1 dazu lautet:

=REDUCE(A2;$D$2:$D$3;LAMBDA(x;y;WECHSELN(x;y;$E$2)))

Der Zellbereich D2:D3 mit den zu ersetzenden Ausdrücken sowie die Zelle E2 mit dem Ersatzausdruck werden absolut gesetzt, damit die Formel später nach unten kopiert werden können. Die Berechnung in der LAMDA-Funktion erfolgt durch die Funktion WECHSELN, die nacheinander prüft, ob im Straßennamen der Ausdruck strasse oder straße erscheint. Falls dies der Fall ist, wird der Ausdruck str. dafür in den Straßennamen eingesetzt.

Wenn Sie die Formel aus B2 nach unten kopieren, sieht das Tabellenblatt danach so aus:

Fazit

Mit der Funktion REDUCE() können Sie zunächst Berechnungen für Werte in einem Zellbereich durchführen und danach auf einen einzelnen Wert reduzieren. Wenn Sie die Zwischenwerte im Tabellenblatt sehen möchten, können Sie die Funktion SCAN() verwenden.

Hinweise

• Die Funktion REDUCE() kann nur einen Zellbereich untersuchen. Wenn Sie mehr als einen Zellbereich eingeben, erscheint der folgende Fehler: Sie haben zu viele Argumente für die Funktion angegeben.
• 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!