(C) 2009 - 2020 by Mourad Louha · Alle Rechte vorbehalten

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, dessen 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 dessen Wert – was auch eine Formel sein kann. Es folgt wieder die Bezeichnung der nächsten Variable und dessen Wert. Als letzte Angabe in der Funktion LET erfolgt die Gesamtberechnung. Im Funktionsassistenten sieht das dann zum Beispiel so aus:

Die Funktion LET

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, dessen 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.

Ausgangstabelle

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.

Daten filtern mit der Funktion FILTER

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“)).

Daten filtern mit der Funktion FILTER und Überschriften behalten

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.

Daten einer intelligenten Tabelle filtern mit der Funktion FILTER

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.

Einfaches dynamisches Array

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 dessen Verwendung adressieren. Die Formel =A1:B7 lässt sich somit auch ersetzen durch =INDEX(A1:A7;1;0):INDEX(B1:B7;7;0).

Dynamisches Array mit der Funktion INDEX

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).

INDEX und ANZAHL2 kombinieren

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“))

INDEX, ANZAHL2 und FILTER kombinieren

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.

Vereinfachung mit LET

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 :-)

Mourad Louha

Über den Autor · Mourad Louha

Mourad ist seit 2005 als unabhängiger Softwareentwickler tätig und spezialisiert auf Excel VBA-Anwendungen. Er lebt in Aachen, gelegen am Dreiländereck Belgien, Deutschland und Niederlande. In seiner Freizeit engagiert er sich in Online Communities rund um Office, schreibt beim Excel Ticker Artikel zu Office & Co. und betreibt weitere Community Projekte, wie sein Lieblingsprojekt zum Excel Formel Übersetzer. Für sein außergewöhnliches Engagement wurde er von Microsoft über viele Jahre hinweg als Microsoft Most Valuable Professional (MVP) ausgezeichnet.

 
Comments

No comments yet.

Leave a Reply