Bereichsvariable in der Funktion LET verwenden

Im Abonnement stellt Microsoft Office verschiedene Updatekanäle zur Verfügung. Je nach Kanal sind neue Features früher oder später enthalten. So wählen zum Beispiel Unternehmen eher den halbjährlichen Kanal, da Feature-Updates dort nur halbjährlich erfolgen und z.B. das umfangreiche Testen dieser erlauben. Der monatliche Kanal liefert hingegegen die neuen Features aus, sobald diese verfügbar sind.

Für die ganz Neugierigen gibt es noch den Beta-Kanal oder früher Insider Fast genannt, wo Features in einer Art Betaphase verfügbar gemacht werden. Jeder kann am Office-Insider-Programm teilnehmen. Features, die im Betakanal verfügbar sind, können auch Änderungen unterworfen sein. Zum Beispiel kann sich die Übersetzung einer neuen Excel-Funktion durchaus während der Betaphase ändern.

Die Funktion LET ist eine solche Funktion, die derzeit nur im Betakanal verfügbar ist. LET hieß übrigens eine Zeit lang SEI in der deutschen Übersetzung. Durchaus möglich, dass Sie dann in Blogs oder Forum Verweise auf den vorherigen Namen finden.

Eines meiner Lieblingsforen ist mittlerweile das Clever-Excel-Forum, wo ich unter dem Nickname maninweb aktiv bin. Vor einiger Zeit enstand dort eine Diskussion zu der Funktion LET, wo der User LCohen eine Merkwürdigkeit feststellte. Ich vermutete zunächst einen Bug – schnell stellte sich aber heraus, dass es ein Feature ist.

So ist es möglich innerhalb der Funktion LET auch eine Variable zu Definition einer Bereichsgröße zu verwenden. Das nachfolgend aufgeführte Beispiel erläutert dies anhand einer Datentabelle, die dynamisch Werte filtert.

Kurzer Überblick über die Funktion LET

Die Funktion LET ermöglicht, innerhalb der Funktion Variablen zu deklarieren und zu verwenden. Angenommen, wir haben die zwei Variablen X und Y, deren Summe wir berechnen wollen. Die Formel in der Zelle bei der Verwendung von LET lautet dann =LET(X;10;Y;2;X+Y)

Hierbei erwartet LET eine bestimmte Reihefolge der Argumente. Zunächst ist die Bezeichnung der Variable anzugeben und anschließend deren Wert – was auch eine Formel sein kann. Es folgt wieder die Bezeichnung der nächsten Variable und deren Wert. Als letzte Angabe in der Funktion LET erfolgt die Gesamtberechnung. Im Funktionsassistenten sieht das dann zum Beispiel so aus:

Selbstverständlich kann LET mehr als nur 2 Variablen, bis zu 126 sind möglich. Laut dem Excel-Team kann LET auch deutlich zur Beschleunigung von Berechnungen beitragen. Ein einfaches Beispiel dazu: angenommen sie haben eine sehr komplexe Formel, deren Ergebnis auf Null geprüft werden soll. Wenn Ja, dann soll ein Text erscheinen, ansonsten das Ergebnis der Formel. Normalerweise würden Sie WENN(Komplexe Formel = 0; „Nicht vorhanden“; Komplexe Formel) schreiben. Die Formel würde somit zweimal berechnet. Mit LET würde dies nur einmal passieren.

=LET(X;Komplexe Formel;WENN(X=0;“Nicht vorhanden“;X))

Filtern von Daten mit der Funktion FILTER

Schauen wir uns folgende Tabelle an. In den Spalten A und B befindet sich eine Liste von Städten und Ländern – eine recht einfache Tabelle.

Die Daten sollen nun so gefiltert werden, dass nur die Städte in Deutschland ermittelt werden. Da wir eine moderne Excel-Version verwenden, steht uns hierzu die Funktion FILTER zur Verfügung, die uns ein dynamisches Array zurückliefert.

Die Formel =FILTER(A1:B7;B1:B7=“Deutschland“) erwartet die Ausgangsmatrix im ersten Argument und das Filterkriterium im zweiten Argument. Ein optionales drittes Argument, das hier weggelassen wurde, ermöglich die Angabe eines Texts, falls keine Daten gefunden wurden. Wichtig ist, dass der Ausgangsbereich und der Kriterienbereich dieselbe Höhe haben.

Besser wäre es vielleicht, wenn die Überschriften mit im dynamischen Array ausgegeben würden. Gesagt, getan, wir ergänzen die Formel um ein zweites Kriterium, welches dem ersten hinzugefügt wird. Die Formel lautet dann =FILTER(A1:B7;(B1:B7=“Deutschland“)+(B1:B7=“Land“)).

Wie in der Formel zu sehen, ist der Bereich statisch, geht also von Zeile 1 bis Zeile 7. Würde ich neue Daten der Ausgangstabelle hinzufügen, müsste ich die Formel anpassen. Bringen wir also etwas mehr Dynamik rein.

Dynamisieren der FILTER-Formel über eine intelligente Tabelle

Die einfachste Möglichkeit, die FILTER-Formel zu dynamisieren, ist aus der Datentabelle eine intelligente Tabelle zu machen. Das geht recht einfach: Datentabelle markieren, Strg+T drücken, den Dialog bestätigen und die Formel anpassen.

In diesem Fall habe ich noch den Standardnamen der intelligenten Tabelle von Tabelle1 auf Städte angepasst, was über den kontextsensitiven Reiter Tabellenentwurf aus dem Menüband geht.

Nun gibt es aber durchaus Fälle, wo eine intelligente Tabelle nicht verwendet werden kann, aus welchen Gründen auch immer. Für einen solchen Fall müssen wir uns überlegen, wie wir die Formel auf eine andere Art dynamisieren können.

Dynamisieren der FILTER-Formel über Formeln

Wenn ich in D1 die Formel =A1:B7 angebe, wird der gesamte Inhalt der Tabelle in D1:E7 gespiegelt, sofern die Excel-Version dynamische Arrays kennt. Wir würden jedoch eine Formel benötigen, die die Höhe des Bereichs variabel hält.

Die Excel-Funktion INDEX ist eine sehr vielfältige Funktion und ermöglicht, sowohl einzelne Zellen als auch einen Teilbereich eines Bereichs zu adressieren.

Beispielsweise ermittelt INDEX(A1:A7;3;1) den Wert Redmond an dritter Position aus Spalte A in A1:A7. Setze ich jedoch statt 3 den Wert für die Angabe der Zeilennummer auf Null, liefert mir INDEX als dynamisches Array alle Werte aus der Spalte A inklusive der Überschrift zurück – also Stadt, Aachen, bis hin zu Madrid.

Ein Bereich in der Schreibweise Startzelle:Endzelle lässt sich auf Grund der Eigenschaften von INDEX ebenfalls durch deren Verwendung adressieren. Die Formel =A1:B7 lässt sich somit auch ersetzen durch =INDEX(A1:A7;1;0):INDEX(B1:B7;7;0).

Wenn ich nun die gesamte Spalte A adressiere, anstatt dies bis auf Zeile 7 zu begrenzen, liefert mir die Formel INDEX(A:A;1;0):INDEX(B:B;7;0) dasselbe Ergebnis. Es verbliebe dann nur den Wert 7 rechnerisch zu ermitteln.

Hier kommt dann die Excel-Funktion ANZAHL2 ins Spiel, die innerhalb eines Bereichs die Anzahl der belegten Zellen ermittelt. Zu beachten ist, dass die Funktion generell davon ausgeht, dass keine leeren Zellen im Bereich enthalten sind. Es sollten somit keine Lücken vorhanden sein, was hier der Fall ist. Nebenbei bemerkt, ANZAHL2 zählt leere Formelergebnisse sehr wohl mit, nur leere Zellen nicht. Ersetzen wir also die Zahl 7 durch ANZAHL2(B:B).

Es verbleibt somit dies in die FILTER-Formel einzubauen. Daraus ergbit sich ein recht komplexes Konstrukt, das wie folgt aussieht und dreimal die Teilformel INDEX(B:B;ANZAHL2(B:B);0) verwendet:

=FILTER(A1:INDEX(B:B;ANZAHL2(B:B);0);(B1:INDEX(B:B;ANZAHL2(B:B);0)=“Deutschland“)+(B1:INDEX(B:B;ANZAHL2(B:B);0)=“Land“))

Vereinfachung der FILTER-Formel mit LET

Wenn wir die Funktion LET verwenden, lässt sich INDEX(B:B;ANZAHL2(B:B);0) in die Variable X auslagern, was die Formel deutlich vereinfacht.

Das heißt aber auch, dass ein Bereichsende in LET als Variable verwendet werden kann. Das geht dann auch für die Anfangzelle im Bereich. Ein Konstrukt, wie LET(X;A1;Y;B7;X:Y) ist durchaus möglich.

Fazit

Die Funktion LET eröffnet vielfältige neue Möglichkeiten in Excel. Nicht nur, dass Teilformeln als Variablen abgelegt und in Berechnungen wiederverwendet werden können, sondern auch die Lesbarkeit von Formeln kann gesteigert werden. Zudem kann sich die Verwendung von LET auf die Geschwindigkeit auswirken, da Formel z.B. nicht mehr mehrfach berechnet werden müssten oder der Umweg über Hilfsspalten eingespart werden kann.

Happy Exceling :-)