Abonnenten von Microsoft 365 können in Excel die neuen Funktionen NACHZEILE() und NACHSPALTE() für eine schnelle und elegante Berechnung von Spalten- bzw. Zeilenwerten eines Zellbereichs nutzen. Beide Funktionen integrieren die ebenfalls neue LAMBDA-Funktion.
Beide Funktionen haben den gleichen Aufbau. Sie erwarten als Parameter einen Zellbereich bzw. Array sowie eine LAMBDA-Funktion.
=NACHSPALTE(Array;LAMBDA(Spalte))
Grundsätzliche Informationen zur LAMBDA-Funktion finden Sie hier.
Die Funktion NACHSPALTE() führt für jede Spalte des angegebenen Zellbereichs eine Berechnung durch, die Sie mit der LAMBDA-Funktion definieren. Die LAMBDA-Funktion selbst erwartet einen selbst definierten Parameter sowie eine Berechnungsfunktion. Das Ergebnis ist ein Zellbereich mit Werten. Dieser Ergebnisbereich hat eine Zeile und genauso viele Spalten wie der ursprünglich definierte Zellbereich.
Die Funktion NACHZEILE() führt jeweils für alle Zeilen eines Zellbereichs eine Berechnung mit Hilfe einer LAMBDA-Funktion durch. Der Ergebnisbereich umfasst eine Spalte und so viel Zeilen wie der ausgewählte Zellbereich.
Beispiel: Produktmengen
In unserem Beispiel sollen die beiden Funktionen für die Berechnung von statistischen Werten von Produktmengen eingesetzt werden.
Die Tabelle hat den folgenden Aufbau:
Die Gesamtmengen pro Produkt in Zeile 14 wurden mit der Summenfunktion ermittelt und dienen später als Kontrollwerte für die Nutzung der Funktion NACHSPALTE().
Funktion NACHSPALTE()
Sie können die Summe der Mengen pro Produkt durch die folgende Formel ermitteln.:
=NACHSPALTE(B2:E13;LAMBDA(Zellwert;SUMME(Zellwert)))
Wenn Sie die obige Formel in Zelle B17 eingeben, sieht das Ergebnis so aus:
Der Ergebnisse finden Sie im Zellbereich B17:E17. Dieser Bereich ist automatisch als Überlaufbereich angelegt worden, d.h. Sie brauchen die in Zelle B17 eingegebene Formel nicht nach rechts kopieren.
Erläuterung der Formel:
Als erster Parameter der Funktion NACHSPALTE() wird der zu untersuchende Zellbereich definiert. Er umfasst die Zellen im Bereich B2:E13. Der zweite Parameter ist die LAMBDA-Funktion mit der Berechnungsformel. Die LAMBDA-Funktion erwartet zunächst einen einzigen, selbst benannten Parameter. Dieser Parameter heißt in unserem Beispiel Zellwert. Sie können diesen Parameter auch x oder Spalte nennen. Mit dem zweiten Parameter definieren Sie, wie jeder Zellwert in einer Spalte verarbeitet werden soll. In unserem Beispiel sollen alle Werte aufaddiert werden. Daher wird die Funktion SUMME verwendet. Als Parameter der Funktion SUMME dient der zuvor definierte Parameter.
Wenn Sie für jedes Produkt die größte Menge ermitteln wollen, lautet die Formel mit der Funktion nach NACHSPALTE () so:
=NACHSPALTE(B2:E13;LAMBDA(Zellwert;MAX(Zellwert)))
Der einzige Unterschied zur ersten Formel besteht in der Anwendung der Funktion MAX statt SUMME.
Im dritten Beispiel soll für jedes Produkts die Anzahl der Monate ermittelt werden, in denen die Produktmenge größer ist als ein vorgegebener Mindestwert von 200.
Wenn Sie die den Mindestwert in eine beliebige Zelle schreiben und dieser Zelle den Namen Mindestwert geben, sieht die Formel so aus:
=NACHSPALTE(B2:E13;LAMBDA(Zellwert;ZÄHLENWENN(Zellwert;”>=” &Mindestmenge)))
In diesem Beispiel werden für jede Spalte getrennt alle Zellwerte mit der Funktion ZÄHLENWENN überprüft, ob der Wert größer oder gleich 200 ist oder nicht.
Das Tabellenblatt sieht mit allen Ergebnissen so aus:
Im nächsten Beispiel sehen Sie, wie Sie die Funktion NACHSPALTE() einsetzen können, wenn sich die Produktmengen in einer dynamischen Tabelle befinden. Die Tabelle hat den Namen Produktmengen_DT und sieht so aus:
Die Formel für die aufaddierten Mengen pro Produkt lautet folgendermaßen:
=NACHSPALTE(Produktmengen_DT[[Produkt 1]:[Produkt 4]];LAMBDA(Zellwert;SUMME(Zellwert)))
Der Unterschied zur Formel ohne dynamische Tabelle besteht in der Angabe des zu untersuchenden Zellbereichs. Der zu berücksichtigende Zellbereich umfasst die Spalten Produkt1 bis Produkt 4 der dynamischen Tabelle Produktmengen_DT. Der Spaltenbereich als auch die Überschriften der ersten und der letzten Spalte stehen in eckigen Klammern. Achten Sie darauf, dass in diesem Fall die Spalte Monat nicht zum zu untersuchenden Zellbereich gehört.
Das größte Menge pro Produkt wird mit der folgenden Formel ermittelt:
=NACHSPALTE(Produktmengen_DT[[Produkt 1]:[Produkt 4]];LAMBDA(Zellwert;MAX(Zellwert)))
Das Tabellenblatt sieht danach so aus:
Funktion NACHZEILE()
Im Folgenden soll die Funktion NACHZEILE() eingesetzt werden, um zeilenbezogene Werte zu ermitteln:
Zunächst soll pro Monat der maximale Wert ermittelt werden:
Geben Sie in der Zelle F2 die folgende Formel ein:
=NACHZEILE(B2:E13;LAMBDA(Zellwert;MAX(Zellwert)))
Bis auf den Funktionsnamen NACHZEILE () ist die Formel identisch mit der Berechnung der größten Menge pro Produkt.
Danach soll die Anzahl der Produkte, bei denen die Produktmenge größer oder gleich ist als der vorgegebene Mindestwert von 200, berechnet werden.
Geben Sie in der Zelle G2 die folgende Formel ein:
=NACHZEILE(B2:E13;LAMBDA(Zellwert;ZÄHLENWENN(Zellwert;”>=” &Mindestmenge)))
Bis auf den Funktionsnamen NACHZEILE() ist auch in diesem Beispiel die Formel identisch mit der Berechnung der Anzahl der Monate, bei denen der Mindestwert erreicht wurde.
Das Tabellenblatt sieht danach so aus:
Hinweise
- Wenn Sie die Funktion NACHSPALTE() oder NACHZEILE() ohne eine LAMBDA-Funktion einsetzen, erscheint in der Zelle der Fehler #WERT!.
- Wenn Sie in der LAMBDA-Funktion einen Parameter definieren, den Sie bei der Berechnungsfunktion anders schreiben, erscheint der Fehler #NAME?.
Fazit
Mit den neuen Funktionen NACHSPALTE() und NACHZEILE() können Sie durch die vielseitig einsetzbare LAMBDA-Funktion spalten- und zeilenbezogene Berechnungen für einen Zellbereich durchführen.
Super erklärt und gut verständlich dargestellt. Einmal gelesen und verstanden.
Danke – macht Appetit auf *mehr*.
Die 365-Writerentwichlung ist schon rasant.