Inhalte
Derzeit verfügt Microsoft Excel über fast fünfhundert Arbeitsblattfunktionen, die über die Schaltfläche des Funktionsassistentenfensters verfügbar sind fx in der Bearbeitungsleiste. Das ist ein sehr ordentliches Set, aber dennoch stößt fast jeder Benutzer früher oder später auf eine Situation, in der diese Liste nicht die Funktion enthält, die er benötigt – einfach weil sie nicht in Excel ist.
Bisher konnte dieses Problem nur mit Makros gelöst werden, also mit dem Schreiben einer eigenen benutzerdefinierten Funktion (UDF = User Defined Function) in Visual Basic, was entsprechende Programmierkenntnisse erfordert und teilweise gar nicht so einfach ist. Mit den neuesten Office 365-Updates hat sich die Situation jedoch zum Besseren verändert – Excel wurde um eine spezielle „Wrapper“-Funktion erweitert LAMBDA. Mit seiner Hilfe ist die Aufgabe, eigene Funktionen zu erstellen, nun einfach und schön gelöst.
Betrachten wir das Prinzip seiner Verwendung im folgenden Beispiel.
Wie Sie höchstwahrscheinlich wissen, verfügt Excel über mehrere Datumsanalysefunktionen, mit denen Sie die Nummer des Tages, des Monats, der Woche und des Jahres für ein bestimmtes Datum bestimmen können. Aber aus irgendeinem Grund gibt es keine Funktion, die die Zahl des Quartals bestimmt, was auch oft benötigt wird, oder? Lassen Sie uns diesen Mangel beheben und mit erstellen LAMBDA eigene neue Funktion, um dieses Problem zu lösen.
Schritt 1. Schreiben Sie die Formel
Beginnen wir mit der Tatsache, dass wir auf die übliche Weise manuell eine Formel in eine Blattzelle schreiben, die berechnet, was wir brauchen. Bei der Quartalszahl geht das zum Beispiel so:
Schritt 2. Einpacken in LAMBDA und Testen
Jetzt ist es an der Zeit, die neue LAMBDA-Funktion anzuwenden und unsere Formel darin zu verpacken. Die Funktionssyntax lautet wie folgt:
=LAMBDA(Variable1; Variable2; … VariableN ; Ausdruck)
wobei die Namen einer oder mehrerer Variablen zuerst aufgeführt werden und das letzte Argument immer eine Formel oder ein berechneter Ausdruck ist, der sie verwendet. Variablennamen sollten nicht wie Zelladressen aussehen und keine Punkte enthalten.
In unserem Fall gibt es nur eine Variable – das Datum, für das wir die Quartalszahl berechnen. Nennen wir die Variable dafür, sagen wir, d. Dann verpacken wir unsere Formel in eine Funktion LAMBDA und die Adresse der ursprünglichen Zelle A2 durch einen fiktiven Variablennamen ersetzen, erhalten wir:
Bitte beachten Sie, dass nach einer solchen Transformation unsere Formel (tatsächlich richtig!) einen Fehler produzierte, da jetzt das ursprüngliche Datum aus Zelle A2 nicht dorthin übertragen wird. Zum Testen und Vertrauen können Sie Argumente übergeben, indem Sie sie nach der Funktion hinzufügen LAMBDA in Klammern:
Schritt 3. Erstellen Sie einen Namen
Nun zum einfachen und unterhaltsamen Teil. Wir öffnen Name Manager Tab Formel (Formeln – Name Manager) und erstellen Sie mit der Schaltfläche einen neuen Namen Erstellen (Schaffen). Denken Sie sich einen Namen für unsere zukünftige Funktion aus und geben Sie ihn ein (z. B. Nomkwartala) und im Feld Link (Referenz) Kopieren Sie sorgfältig aus der Formelleiste und fügen Sie unsere Funktion ein LAMBDA, nur ohne das letzte Argument (A2):
Alles. Nach dem Anklicken OK Die erstellte Funktion kann in jeder Zelle auf jedem Blatt dieser Arbeitsmappe verwendet werden:
Verwendung in anderen Büchern
LAMBDA und dynamische Arrays
Benutzerdefinierte Funktionen, die mit einer Funktion erstellt wurden LAMBDA die Arbeit mit neuen dynamischen Arrays und deren Funktionen erfolgreich unterstützen (FILTER, UNIK, GRADE) 2020 zu Microsoft Excel hinzugefügt.
Angenommen, wir möchten eine neue benutzerdefinierte Funktion erstellen, die zwei Listen vergleicht und die Differenz zwischen ihnen zurückgibt – jene Elemente aus der ersten Liste, die nicht in der zweiten enthalten sind. Lebenswerk, nicht wahr? Früher nutzten sie dafür entweder Funktionen a la VPR (SVERWEIS), PivotTables oder Power Query-Abfragen. Jetzt können Sie mit einer Formel tun:
In der englischen Version wird es sein:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Hier die Funktion COUNTIF zählt die Anzahl der Vorkommen jedes Elements der ersten Liste in der zweiten und dann die Funktion FILTER wählt nur diejenigen aus, die diese Vorkommnisse nicht hatten. Durch das Einwickeln dieser Struktur LAMBDA und Erstellen eines darauf basierenden benannten Bereichs mit einem Namen, z. B. VERTEILUNG SUCHEN – wir erhalten eine praktische Funktion, die das Ergebnis des Vergleichs zweier Listen in Form eines dynamischen Arrays zurückgibt:
Wenn die Quelldaten keine gewöhnlichen, sondern „intelligente“ Tabellen sind, kommt unsere Funktion auch problemlos zurecht:
Ein weiteres Beispiel ist das dynamische Aufteilen von Text durch Konvertieren in XML und anschließendes Parsen Zelle für Zelle mit der Funktion FILTER.XML, die wir kürzlich analysiert haben. Um diese komplexe Formel nicht jedes Mal manuell zu reproduzieren, wird es einfacher sein, sie in LAMBDA zu verpacken und einen darauf basierenden Dynamikbereich zu erstellen, dh eine neue kompakte und praktische Funktion, die sie beispielsweise RAZDTEXT nennt:
Das erste Argument dieser Funktion ist die Zelle mit dem Quelltext und das zweite – das Trennzeichen, und das Ergebnis wird in Form eines horizontalen dynamischen Arrays zurückgegeben. Der Funktionscode lautet wie folgt:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“
Die Liste der Beispiele ist endlos – überall dort, wo Sie oft die gleichen langen und umständlichen Formeln eingeben müssen, erleichtert die LAMBDA-Funktion das Leben spürbar.
Rekursive Aufzählung von Zeichen
Alle vorherigen Beispiele haben nur eine, die offensichtlichste Seite der LAMBDA-Funktion gezeigt – ihre Verwendung als „Wrapper“, um lange Formeln darin einzuhüllen und ihre Eingabe zu vereinfachen. Tatsächlich hat LAMBDA eine andere, viel tiefere Seite, die es zu einer fast vollwertigen Programmiersprache macht.
Tatsache ist, dass ein grundlegend wichtiges Merkmal von LAMBDA-Funktionen die Fähigkeit ist, sie in zu implementieren Rekursion – Berechnungslogik, wenn sich die Funktion während der Berechnung selbst aufruft. Aus Gewohnheit mag es gruselig klingen, aber beim Programmieren ist Rekursion eine gängige Sache. Sogar in Makros in Visual Basic können Sie es implementieren, und wie Sie sehen, ist es jetzt auch in Excel angekommen. Versuchen wir, diese Technik anhand eines praktischen Beispiels zu verstehen.
Angenommen, wir möchten eine benutzerdefinierte Funktion erstellen, die alle angegebenen Zeichen aus dem Quelltext entfernt. Die Nützlichkeit einer solchen Funktion, denke ich, braucht man nicht zu beweisen – es wäre doch sehr bequem, mit ihrer Hilfe verunreinigte Eingabedaten zu löschen, oder?
Im Vergleich zu den vorherigen, nicht rekursiven Beispielen warten jedoch zwei Schwierigkeiten auf uns.
- Wir müssen uns einen Namen für unsere Funktion einfallen lassen, bevor wir mit dem Schreiben ihres Codes beginnen, da dieser Name darin bereits verwendet wird, um die Funktion selbst aufzurufen.
- Das Eingeben einer solchen rekursiven Funktion in eine Zelle und das Debuggen durch Angabe von Argumenten in Klammern nach LAMBDA (wie wir es zuvor getan haben) funktioniert nicht. Sie müssen eine Funktion sofort „von Grund auf neu“ erstellen Name Manager (Namensmanager).
Nennen wir unsere Funktion beispielsweise CLEAN und möchten, dass sie zwei Argumente hat – den zu bereinigenden Text und die Liste der ausgeschlossenen Zeichen als Textstring:
Lassen Sie uns wie zuvor auf der Registerkarte erstellen Formel в Name Manager benannten Bereich, nennen Sie ihn CLEAR und geben Sie ihn in das Feld ein Abdeckung folgende Konstruktion:
=LAMBDA(t;d;IF(d=““;t;CLEAR(SUBSTITUTE(t;LEFT(d);““);MID(d;2;255))))
Hier ist die Variable t der zu löschende Originaltext und d die Liste der zu löschenden Zeichen.
Das ganze funktioniert so:
Iteration 1
Das Fragment SUBSTITUTE(t;LEFT(d);””) ersetzt, wie Sie sich denken können, das erste Zeichen vom linken Zeichen aus der zu löschenden Menge d im Quelltext t durch einen leeren Textstring, entfernt also das „ EIN". Als Zwischenergebnis erhalten wir:
Vsh zkz n 125 Rubel.
Iteration 2
Dann ruft sich die Funktion selbst auf und erhält als Eingabe (das erste Argument) das, was nach der Reinigung im vorherigen Schritt übrig geblieben ist, und das zweite Argument ist die Zeichenfolge der ausgeschlossenen Zeichen, beginnend nicht mit dem ersten, sondern mit dem zweiten Zeichen, dh „BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ”, ohne das vorangestellte “A” – das erledigt die MID-Funktion. Wie zuvor nimmt die Funktion das erste Zeichen von links der verbleibenden (B) und ersetzt es in dem ihr übergebenen Text (Zkz n 125 Rubel) durch einen leeren String – wir erhalten als Zwischenergebnis:
125 Ru.
Iteration 3
Die Funktion ruft sich selbst erneut auf und erhält als erstes Argument, was von dem Text übrig ist, der bei der vorherigen Iteration gelöscht werden soll (Bsh zkz n 125 ru.), und als zweites Argument die Menge der ausgeschlossenen Zeichen, die um ein weiteres Zeichen abgeschnitten werden links, also „VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.“, ohne den Anfangsbuchstaben „B“. Dann nimmt es wieder das erste Zeichen von links (B) aus dieser Menge und entfernt es aus dem Text – wir erhalten:
sh zkz n 125 ru.
Und so weiter – ich hoffe, Sie verstehen es. Bei jeder Iteration wird die Liste der zu entfernenden Zeichen auf der linken Seite abgeschnitten, und wir suchen nach dem nächsten Zeichen aus dem Satz und ersetzen es durch eine Leerstelle.
Wenn alle Zeichen aufgebraucht sind, müssen wir die Schleife verlassen – diese Rolle wird nur von der Funktion übernommen IF (WENN), in der unser Design verpackt ist. Wenn keine Zeichen mehr zu löschen sind (d=““), dann soll die Funktion sich nicht mehr selbst aufrufen, sondern einfach den zu löschenden Text (Variable t) in seiner endgültigen Form zurückgeben.
Rekursive Iteration von Zellen
Auf ähnliche Weise können Sie eine rekursive Aufzählung von Zellen in einem bestimmten Bereich implementieren. Angenommen, wir möchten eine Lambda-Funktion mit dem Namen erstellen ERSATZLISTE zum vollständigen Ersetzen von Fragmenten im Ausgangstext gemäß einer vorgegebenen Referenzliste. Das Ergebnis sollte so aussehen:
Diese. bei unserer Funktion ERSATZLISTE Es wird drei Argumente geben:
- Zelle mit zu verarbeitendem Text (Quelladresse)
- die erste Zelle einer Spalte mit zu suchenden Werten aus der Suche
- die erste Zelle der Spalte mit Ersatzwerten aus der Suche
Die Funktion soll im Verzeichnis von oben nach unten gehen und nacheinander alle Optionen aus der linken Spalte ersetzen Finden zu den entsprechenden Optionen aus der rechten Spalte Ersatz. Sie können dies mit der folgenden rekursiven Lambda-Funktion implementieren:
Das Herunterschalten bei jeder Iteration wird durch eine Standard-Excel-Funktion implementiert VERFÜGUNG (OFFSET), die in diesem Fall drei Argumente hat – den ursprünglichen Bereich, die Zeilenverschiebung (1) und die Spaltenverschiebung (0).
Nun, sobald wir das Ende des Verzeichnisses erreichen (n = „“), müssen wir die Rekursion beenden – wir hören auf, uns selbst aufzurufen und zeigen an, was sich nach all den Ersetzungen in der Quelltextvariablen t angesammelt hat.
Das ist alles. Keine kniffligen Makros oder Power Query-Abfragen – die ganze Aufgabe wird von einer Funktion gelöst.
- So verwenden Sie die neuen dynamischen Array-Funktionen von Excel: FILTER, SORT, UNIC
- Ersetzen und Bereinigen von Text mit der SUBSTITUTE-Funktion
- Erstellen von Makros und benutzerdefinierten Funktionen (UDFs) in VBA