Ziel dieses Tutorials ist es, das Konzept einer rekursiven Funktion zu erkl?ren und den generischen Ansatz zur Erstellung rekursiver Lambdas in Excel zu demonstrieren. Wir werden jeden Aspekt eingehend untersuchen, um es Ihnen leicht zu machen, in Ihren Arbeitsbl?ttern zu folgen und zu reproduzieren.

Mit der Einführung der Lambda -Funktion stehen rekursive Berechnungen in Excel für jeden, nicht nur VBA -Programmierer, zur Verfügung. Einfach ausgedrückt, k?nnen Sie jetzt Formeln konstruieren, die sich wie eine Programmiersprache verhalten, und es Ihnen erm?glichen, so viel zu erreichen, mit so wenig, so schnell :)

Bitte beachten Sie, dass die in diesem Tutorial diskutierten Beispiele implizieren, dass Sie die Syntax und die grundlegenden Verwendungen von Lambda bereits kennen. Wenn nicht, liegt es auf dem Grund, mit dem Wesentlichen zu beginnen: wie man Lambda in Excel schreibt und verwendet.

Rekursive Lambda -Funktion

Um sicherzustellen, dass jeder auf derselben Seite ist, stellen wir zun?chst fest, was eine rekursive Funktion ist.

In der Informatik ist Rekursion eine Methode zur L?sung eines Problems, bei dem sich eine Funktion direkt oder indirekt aufruft. Eine solche Funktion wird als rekursiv bezeichnet. Grunds?tzlich funktioniert eine rekursive Funktion nach Iteration und findet eine L?sung für ein gr??eres Problem, indem kleinere Instanzen desselben Problems gel?st werden.

Derzeit ist Lambda die einzige Excel -Funktion, die Rekursion unterstützt und es Ihnen erm?glicht, kompakte und elegante L?sungen für komplexe Probleme ohne Codierung zu erstellen.

In VBA wird die Rekursion im Allgemeinen mit einem für… als n?chstes durchgeführt oder … w?hrend der Schleife. Lambda stützt sich in der Regel auf die IF -Funktion, um einen Booleschen Zustand zu testen und wiederzuverfolgen, ob die Bedingung entweder wahr oder falsch ist.

Hier ist die Struktur einer rekursiven Lambda -Funktion, die ihre einfachste Form hat:

= Lambda (x, y,…, 'deklariere Parameter if (logical_test, test die Bedingung mylambda (), ' Wiederholung, wenn die Bedingung auf true value_if_false bewertet wird , wenn die Bedingung an False bewertet wird )

Der entscheidende Punkt besteht darin, rekursive Anrufe davon abzuhalten, für immer fortzufahren. Zu diesem Zweck sollten Sie den Endfall angeben (auch als Stopp -Fall oder Basisfall bezeichnet). Wenn kein Ausstiegspunkt bereitgestellt wird, wird eine Formel wieder iteriert, bis Ihr Computer abstürzt. Natürlich wird ein Scherz ein #Num werfen! Fehler.

Im Vergleich zu nicht rekursiven Funktionen sind rekursive Lambdas schwieriger zu schreiben, zu testen und zu debuggen. Es ?hnelt dem guten alten Hühnchen- und Eier -R?tsel - damit eine Funktion richtig funktioniert, muss es sich selbst nennen; Um sich selbst anzurufen, muss die Funktion richtig funktionieren :)

Beispiel für rekursive Lambda, um unerwünschte Zeichen zu entfernen

Beim Importieren von Daten aus externen Quellen k?nnen sich Müllzeichen h?ufig einschleichen, und Sie müssen einen Weg finden, Ihre Daten irgendwie zu reinigen.

Das Ersetzen aller Merkmale kann alle Vorkommen eines bestimmten Zeichens entfernen, indem sie sie durch nichts ersetzt, aber es kann jeweils nur mit einem Charakter umgehen.

Viel schneller und bequemer wird es sein, alle unerwünschten Charaktere in einer Zelle aufzulisten und sie mit einer Formel auf einen Sturz zu beseitigen. Ein rekursives Lambda ist genau das, was Sie brauchen:

=LAMBDA(data, chars, IF(chars"", RemoveChars(SUBSTITUTE(data, LEFT(chars, 1), ""), RIGHT(chars, LEN(chars) -1)), data))

Unsere benutzerdefinierte Lambda -Funktion hei?t Removechars und ben?tigt zwei Eingabeparameter:

• Daten - eine Zelle oder ein Bereich von Zellen, die gereinigt werden sollen.
• Zeichen - die unerwünschten Zeichen, die sie entfernen sollten. Kann in Form einer Textzeichenfolge oder einer Zellreferenz bereitgestellt werden. In einer Zelle sollten die Zeichen ohne Leerzeichen aufgeführt werden, es sei denn, Sie m?chten auch R?ume ausrotten.

Auf einer hohen Ebene ist hier die Funktion:

Die Funktion von Removechars f?hrt durch die Ausschlussliste ( CHARS ) und reinigt jeweils einen Charakter. Vor jedem rekursiven Aufruf bewertet die IF -Funktion die verbleibenden Zeichen . Wenn die Zeichenfolge nicht leer ist (Zeichen ""), ruft sich die Funktion selbst auf. Sobald der letzte Charakter behandelt wurde, beendet der Iterationsprozess - die Formel gibt Daten in ihrer aktuellen Form zurück und beendet.

Die umgekehrte Logik funktioniert auch: Wenn die Zeichenfolge für Zeichen leer ist (chars = ""), geben Sie die vorliegenden Daten zurück und beenden Sie. Andernfalls rufen Sie die Funktion removechars an:

=LAMBDA(data, chars, IF(chars="", data, RemoveChars(SUBSTITUTE(data, LEFT(chars, 1), ""), RIGHT(chars, LEN(chars) -1))))

Welchen Ansatz Sie w?hlen, der Sie w?hlen, das Ergebnis ist genau das gleiche:

Tipp. Die gleiche Aufgabe kann leicht mit regul?ren Ausdrücken erledigt werden. Weitere Informationen finden Sie in Excel Regex, um Sonderzeichen zu entfernen.

Wie man rekursive Lambda in Excel schreibt

Ich m?chte mit einem Haftungsausschluss beginnen :) Es gibt keine dokumentierte M?glichkeit, rekursive Lambdas in Excel aufzubauen, was erkl?ren kann, da die Funktion brandneu ist. Ich werde meinen Weg teilen, was für Sie hilfreich sein kann oder nicht.

Erstellen Sie die Kernformel

Im Allgemeinen beginnen Sie mit dem Schreiben der Kernformel (en), die das gewünschte Verhalten Ihrer Lambda -Funktion nachahmen. In unserem Fall ist es das ultimative Ziel, bestimmte Zeichen durch nichts zu ersetzen, und Excel hat dafür bereits ein ideales Instrument - die Ersatzfunktion:

Ersatz (Text, old_text, new_text, [Instance_num])

Um zu ersetzen, müssen wir liefern:

• Text - Der Text, in dem Zeichen ersetzen k?nnen. In unserem Fall ist es eine Textzeichenfolge in A2.
• Old_text - das zu ersetzende Zeichen. Wir müssen jeden einzelnen Charakter in D2 überprüfen, und es liegt an der Stelle, mit den linken links zu beginnen. Die linke Funktion kann sie leicht für uns holen:

  LEFT(D2, 1)

• NEW_TEXT - Das Zeichen, das old_text ersetzt. Offensichtlich ist es eine leere Zeichenfolge ("").
• Instance_num ist optional und wird in unserem Fall nicht ben?tigt, sodass es weggelassen wird.

Als Ergebnis nimmt unsere Kernformel diese Form an:

=SUBSTITUTE(A2, LEFT(D2, 1), "")

Da Ersatz nur einen Ersatz gleichzeitig durchführen kann, muss er so oft ausgeführt werden, da es in der Ausschlussliste in D2 Zeichen gibt. Die Frage ist: Wie zwingen wir es, mit dem n?chsten Charakter umzugehen? Und hier ist die Antwort:

Mit jeder Iteration werden wir einen Charakter von links entfernen, dh der Charakter, der bereits betrachtet wurde. Die richtige Funktion in Kombination mit Len kann das leicht tun:

=RIGHT(D2, LEN(D2) -1)

Bitte achten Sie darauf, dass jeder nachfolgende Ersatz das Ergebnis aus dem vorherigen Ersatz als Textargument verwendet, dh den Ersatz nicht in der ursprünglichen Zeichenfolge (A2), sondern in der von den vorherigen Ersatzfunktionen zurückgegebenen Zeichenfolge (B2):

Konvertieren Sie die Kernformel in eine Lambda -Funktion

Wie Sie erinnern, soll unsere benutzerdefinierte Funktion als Removechars hei?t und 2 Parameter: Daten und Zeichen .

Ihre Aufgabe ist es, die Funktion zu unterweisen, wie jeder Parameter berechnet wird:

• Daten - Die Zeichenfolge, in der Zeichen ersetzen k?nnen. Es wird durch die Ersatzformel bereitgestellt.
• Zeichen - die Zeichen zu entfernen. Es wird durch die richtige Formel bereitgestellt.

Was Sie tun, ist einfach die beiden oben diskutierten Formeln in der Funktion removechars zu platzieren, die sie mit Kommas trennen oder was auch immer das Zeichen verwendet wird, um die Argumente einer Funktion in Ihrem Excel zu trennen (bestimmt durch den in regionalen Einstellungen festgelegten Listentrennzeichen ).

RemoveChars(SUBSTITUTE(A2, LEFT(D2, 1), ""), RIGHT(D2, LEN(D2) -1))

Beachten Sie, dass Lambda mit Parametern und nicht auf Zellreferenzen arbeitet, der n?chste Schritt besteht darin, A2 in Daten und D2 in Zeichen zu ?ndern:

RemoveChars(SUBSTITUTE(data, LEFT(chars, 1), ""), RIGHT(chars, LEN(chars) -1))

Die Funktion removechars erfolgt. Leider gibt es an dieser Stelle keine M?glichkeit, es zu testen, und wir k?nnen uns nur auf die Ergebnisse der vorherigen Tests verlassen und sp?ter bei Bedarf debuggen.

Lassen Sie die Lambda -Funktion sich rekursiv anrufen

Dies ist der Schlüsselteil, der eine "theoretische Formel" in eine funktionierende L?sung verwandelt.

Wie bei jedem benutzerdefinierten Lambda deklarieren Sie die Parameter:

=LAMBDA(data, chars,

Als n?chstes bewerten Sie eine bestimmte Bedingung und rufen je nach Ergebnis entweder die Rekursion oder den Ausgang auf. Die Festlegung eines Ausstiegspunktes ist die entscheidende überlegung. Wenn Sie das nicht tun, funktioniert Ihre Formel nicht richtig, da sie niemals aus der Schleife herauskommt.

In unserem Fall prüft die IF -Funktion, ob die Chars -Liste nicht leer ist (Zeichen ""). Wenn wahr ( Chars nicht leer sind), nennen wir die Removechars -Funktion. Wenn false ( chars leer ist), geben wir Daten zurück.

Dies ist der generische Ansatz:

=LAMBDA(data, chars, IF(chars"", RemoveChars(…), data))

Und dies ist die wirkliche Formel in ihrer vollst?ndigen Form:

=LAMBDA(data, chars, IF(chars"", RemoveChars(SUBSTITUTE(data, LEFT(chars, 1), ""), RIGHT(chars, LEN(chars) -1)), data))

Alternativ k?nnen Sie überprüfen, ob Zeichen leer sind (chars = ""). Wenn wahr, geben Sie Daten zurück und beenden Sie; Wenn falsche Anruf removechars.

Das Konzept:

=LAMBDA(data, chars, IF(chars="", data, RemoveChars(…)))

Die vollst?ndige Formel:

=LAMBDA(data, chars, IF(chars="", data, RemoveChars(SUBSTITUTE(data, LEFT(chars, 1), ""), RIGHT(chars, LEN(chars) -1))))

Nennen Sie Ihre lambda-definierte Funktion

Die Benennung rekursiver Lambdas unterscheidet sich nicht von der Benennung ihrer nicht rekursiven Kollegen:

1. Drücken Sie die Strg 3 -Verknüpfung, um den Namensmanager zu ?ffnen, und klicken Sie dann auf Neu .
2. Führen Sie im Dialogfeld Neues Name Folgendes Folgendes aus:
   • Geben Sie im Feld Name den Namen der Funktion ein: renovechars .
   • Lassen Sie den Umfang der Arbeitsmappe .
   • Fügen Sie in der Box Ihre Lambda -Formel ein, um sicherzustellen, dass sie mit einem Gleichstellungszeichen beginnt.
   • Geben Sie optional die Beschreibung der Parameter in das Kommentarfeld ein, um weitere Referenz zu erhalten.
   • Klicken Sie auf OK , um Ihre neue Funktion zu speichern.

     

Wie man eine rekursive Lambda in Excel verwendet

Es ist der einfachste Teil :) Sobald Ihre Lambda -Funktion einen Namen erh?lt, k?nnen Sie sie genau wie jede andere native Funktion verwenden.

Aus Sicht der Endbenutzer ist die Syntax unserer benutzerdefinierten Funktion so einfach wie folgt:

Removechars (Daten, Zeichen)

Um die Daten in A2: A10 ( Daten ) zu reinigen, geben wir die unerwünschten Zeichen in D2 ( Zeichen ) ein und geben dann die folgende Formel in B2 ein:

=RemoveChars(A2:A10, D2)

Wie Sie wahrscheinlich wissen, ist in Excel 356 jede Formel von Natur aus eine dynamische Array -Formel. Wenn die Formel in nur einer Zelle (B2) eingegeben wird, erhalten wir sofort alle Ergebnisse (dieses Verhalten wird als Verschütten bezeichnet).

Wenn Sie das herk?mmliche Verhalten von "One Formel - One Cell" bevorzugen, verwenden Sie eine Zellreferenz für Daten (A2) und sperren Sie die Zeichen -Zelladresse ($ D $ 2) mit dem $ Zeichen, um zu verhindern, dass sie sich beim Kopieren der Formel nach unten ?ndert:

=RemoveChars(A2, $D$2)

Die obige Formel geht zu B2, und dann ziehen Sie sie durch B10:

Anstatt die angesagten Zeichen in einer Zelle aufzulisten, k?nnen Sie sie direkt als Textzeichenfolge an die Formel angeben:

=RemoveChars(A2:A10, "_^*/&%")

Notiz. Da die in der Kernformel verwendete Ersatzfunktion f?lschlicherweise sensitiv ist, behandelt unsere benutzerdefinierte Funktion Gro?- und Kleinbuchstaben als unterschiedliche Zeichen. Wenn Sie ein bestimmtes Zeichen entfernen m?chten, sagen Sie "x", unabh?ngig vom Buchstabenfall, dann geben Sie sowohl "x" als auch "x" in die Zeichenfolge auf.

Rekursion verstehen

Der Hinweis auf das Verst?ndnis rekursiver Lambdas besteht darin, genau zu wissen, was mit jeder Iteration passiert. In unserem Beispiel gibt es zwei solche Dinge:

• Das Ergebnis aus dem vorherigen Ersatz wird zum neuen Datenparameter für den n?chsten Anruf von Removechars , als würden wir verschachtelte Ersatzfunktionen verwendet.
• Die Zeichenfolge wird durch ein Zeichen reduziert. Sie k?nnen es als eine Art Countdown betrachten. Sobald die Zeichenfolge leer wird, stoppt der Iterationsprozess und die Formel gibt Daten in ihrer vorliegenden Form als Endergebnis zurück.

Die folgende Tabelle kann Ihnen helfen, den Rekursionsprozess besser zu visualisieren:

Weitere Beispiele für eine rekursive Lambda -Funktion

In den folgenden Beispielen werden wir untersuchen, wie Sie die vorhandene Lambda -Funktion mit neuen Funktionen erweitern k?nnen, um sie für Ihre Anforderungen anzupassen.

Beispiel 1. Entfernen Sie unerwünschte Zeichen und schneiden Sie zus?tzliche R?ume ab

Neben verschiedenen irrelevanten Zeichen k?nnen Ihre Daten auch überm??ige R?ume enthalten. Um sie loszuwerden, k?nnen Sie Removechars innerhalb von Trim nisten, so wie Sie es in eingebauten Funktionen tun würden:

=TRIM(RemoveChars(A2:A10, F2))

Um den Effekt anzuzeigen, vergleichen Sie die Ergebnisse in den Spalten B und D. Im letzteren Fall werden nicht nur unerwünschte Zeichen entfernt, sondern auch alle führenden und hinteren R?ume, w?hrend innere R?ume zwischen den W?rtern auf einen einzelnen Raumzeichen reduziert werden:

Wenn Sie sich nicht jedes Mal darum kümmern m?chten, k?nnen Sie es als einmalige Setup in der Lambda selbst tun:

=LAMBDA(data, chars, TRIM(IF(chars"", RemoveTrim(SUBSTITUTE(data, LEFT(chars, 1), ""), RIGHT(chars, LEN(chars) -1)), data)))

Unsere verbesserte Funktion hei?t Removetrim und funktioniert wie ein Zauber:

=RemoveTrim(A2:A10, D2)

Beispiel 2. Ersetzen Sie mehrere Zeichen durch das gleiche Zeichen

In bestimmten Szenarien ist es sinnvoll, einige verschiedene Zeichen durch einen anderen Charakter zu ersetzen, den Sie angeben. Tats?chlich ist es das, was unsere Removechars -Funktion tats?chlich tut - ersetzt die angegebenen Zeichen durch eine leere Zeichenfolge (""). Der Ersatzcharakter ist jedoch festcodiert, w?hrend wir ihn direkt in der Formel definieren m?chten. Um es erledigt zu haben, müssen wir der Funktion nur noch einen Parameter hinzufügen, beispielsweise New_Char .

Also, unsere neue Funktion, nennen wir es ersatztechar , wird die folgende Syntax haben:

Ersatztechare (Daten, Zeichen, new_char)

Um Removechars in Austauschcoden zu verwandeln, müssen 3 kleine ?nderungen vorgenommen werden:

• Definieren Sie den 3 ^RD -Parameter - new_char .
• Ersetzen Sie die festcodierte leere Zeichenfolge ("") durch New_Char .
• übergeben Sie New_Char an die Funktion von AustauschChars als 3 ^-RD -Argument.

Im Ergebnis erhalten wir ein weiteres nützliches Lambda, um mehrere Zeichen rekursiv zu ersetzen:

=LAMBDA(data, chars, new_char, IF(chars"", ReplaceChars(SUBSTITUTE(data, LEFT(chars), new_char), RIGHT(chars, LEN(chars)-1), new_char), data))

Wenn Ihr Lieferant beispielsweise pl?tzlich seine IDs oder SKUS -Formate ?ndert, k?nnen Sie alle unangemessenen Zeichen (E1) durch die entsprechende (E2) durch diese Formel ersetzen:

=ReplaceChars(A2:A6, E1, E2)

Beispiel 3. Ersetzen Sie mehrere Werte durch andere Werte gleichzeitig

Dieses Beispiel ist eine logische Erweiterung des vor ihm. Dieses Mal ersetzen wir ganze W?rter (oder Zeichenfolgen) und nicht einzelne Zeichen, und jedes Wort hat seinen eigenen Ersatzwert.

Da die alten und neuen Werte in separate Zellen platziert werden (wie im folgenden Screenshot gezeigt), funktioniert die richtige Funktion, die wir in den vorherigen Beispielen verwendet haben, nicht. Um die alten/neuen Paare zu durchlaufen, müssen wir etwas anderes herausfinden. Hmm, es scheint eine Funktion in Excel zu geben, um eine bestimmte Anzahl von Zeilen und Spalten aus einer bestimmten Zelle zu verschieben. Ja, das ist ausgefallen!

Mit der festgelegten Hauptmethode ist es keine gro?e Sache, die Ersatz -Funktion zu schreiben:

Ersetzen (Daten, alt, neu)

Für Daten verwenden wir die Ersatzfunktion in ihrer Grundform einfach, um den alten Wert durch das neue zu ersetzen:

SUBSTITUTE(data, old, new)

Um den alten Wert zu erhalten, beginnen wir mit der obersten Zelle in der alten Liste und verschieben mit jeder Interaktion 1 Zeile nach unten:

OFFSET(old, 1, 0)

Um den neuen Wert zu erhalten, werden wir genau das Gleiche tun, aber natürlich auf der neuen Liste:

OFFSET(new, 1, 0)

Implementieren Sie schlie?lich die bereits bekannte Ausstiegsstrategie mit Hilfe von if und Ihr neues leistungsstarkes rekursives Lambda ist bereit für die Verwendung (vergessen Sie einfach nicht, sie im Namensmanager zu benennen :)

=LAMBDA(data, old, new, IF(old"", ReplaceAll(SUBSTITUTE(data, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0) ), data))

Mit den Quelldaten in A2: A10, den alten Werten in Spalte D, beginnend in D2, und die neuen Werte in Spalte E, beginnend in E2, k?nnen Sie durch diese einfache Formel mehrere Ersatzteile durchführen:

=ReplaceAll(A2:A10, D2, E2)

Als Ergebnis ersetzt eine einzelne Formel in B2 alle Abkürzungen in A2: A10 durch die entsprechenden vollst?ndigen Namen:

Rekursive Lambdas vs. VBA-benutzerdefinierte Funktionen

Erweiterte Excel -Benutzer mit einem Programmierhintergrund k?nnen neugierig sein, wie eine rekursive Lambda -Funktion mit einem vergleichbaren VBA -Code korreliert. Lassen Sie uns einen Blick darauf werfen.

Rekursives Lambda, um mehrere Zeichen zu entfernen

Wie Sie verstehen, ist dies nicht funktionierende Pseudocode. Wir haben es in einen VBA -Herausgeber aufgenommen, um den Algorithmus in der vertrauten Form darzustellen, um besser zu verstehen, was los ist :)

= Lambda (Daten, Zeichen, "Parameter deklarieren , wenn (chars " "", " prüfen , ob Zeichen nicht leer ist.

Benutzerdefinierte Funktion zum Entfernen mehrerer Zeichen rekursiv

Auf diese Weise kann eine analoge benutzerdefinierte Funktion in VBA geschrieben werden:

Function RemoveCharsRecursive(data As String , chars As String ) If ( "" chars) Then data = Replace(data, Left(chars, 1), "" ) chars = Right(chars, Len(chars) - 1) RemoveCharsRecursive = RemoveCharsRecursive(data, chars) Else RemoveCharsRecursive = data End If End Function

Benutzerdefinierte Funktion zum Entfernen mehrerer Zeichen nicht rekursiv

Eine ?hnliche Funktion kann auch unter Verwendung einer nicht rekursiven Methode geschrieben werden. In diesem Fall schreiben wir Removechars als separate Funktion und rufen sie innerhalb der RemoveCarsnonRecursive -Funktion auf, wenn die Zeichenfolge nicht leer ist.

Funktion removecharsnonRecursive (Daten als String , Zeichen als Zeichenfolge ) , w?hrend "" > chars data = renovechars (Daten , Zeichen ) chars = recht

Die gleiche Aufgabe kann anders ausgeführt werden. Sie k?nnen die Ausschlusszeichen von 1 nach Len (Zeichen) durchsetzen und die in Daten gefundenen Zeichen durch eine leere Zeichenfolge ersetzen. Die mittlere Funktion wird verwendet, um jedes einzelne Zeichen aus der Zeichenfolge von der Zeichen zu extrahieren.

Funktion removecharsnonRecursive2 (Daten als Zeichenfolge , Zeichen als Zeichenfolge ) für Index = 1 bis Len (chars) data = ersetzen (Daten, Mid (chars, Index, 1 ), "" ) Next RemovecharsnonRecursive2 = Datenendfunktion

Was ist der Vorteil der Verwendung von Lambdas im Vergleich zu VBA-benutzerdefinierten Funktionen? In erster Linie müssen keine Arbeitsmappen als makro-f?hige .xlsm-Dateien gespeichert werden und speichern Sie die Mühe, Makros bei jeder ?ffnung zu aktivieren.

Hoffentlich hat Ihnen dieses Tutorial geholfen, einen Einblick in die rekursive Lambda in Excel zu bekommen. Ich danke Ihnen für das Lesen und hoffe, Sie n?chste Woche in unserem Blog zu sehen!

üben Sie Arbeitsmappen zum Download

Rekursive Lambda-Beispiele (.xlsx-Datei) VBA-benutzerdefinierte Funktionen (.xlsm-Datei)

Das obige ist der detaillierte Inhalt vonRekursive Lambda -Funktion in Excel mit Beispielen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!