Fonction lambda récursive dans Excel avec des exemples

Le but de ce tutoriel est d'expliquer le concept d'une fonction récursive et de démontrer l'approche générique de la création de lambdas récursifs dans Excel. Nous explorerons tous les aspects en profondeur pour vous permettre de suivre et de vous reproduire facilement dans vos feuilles de travail.

Avec l'introduction de la fonction lambda, les calculs récursifs dans Excel sont devenus disponibles pour tous, pas seulement les programmeurs VBA. Pour le dire simplement, vous pouvez maintenant construire des formules qui se comportent comme un langage de programmation et vous permettre de réaliser autant, avec si peu, si vite :)

Veuillez garder à l'esprit que les exemples discutés dans ce tutoriel impliquent que vous connaissez déjà la syntaxe et les utilisations de base de Lambda. Sinon, il va raisonner de commencer par l'essentiel: comment écrire et utiliser Lambda dans Excel.

• Fonctions lambda récursives
• Lambda récursif pour supprimer les caractères indésirables
• Comment écrire du lambda récursif dans Excel
• Comment utiliser un lambda récursif
• Comprendre la récursivité
• Exemples de lambda récursive
  • Exemple 1. Retirez les caractères indésirables et coupez les espaces supplémentaires
  • Exemple 2. Remplacez plusieurs caractères par le même caractère
  • Exemple 3. Remplacez plusieurs valeurs par d'autres valeurs à la fois
• Fonctions Recursive Lambda vs VBA définies par l'utilisateur

Fonction de lambda récursive

Pour vous assurer que tout le monde est sur la même longueur d'onde, déterminons d'abord ce qu'est une fonction récursive.

En informatique, la récursivité est une méthode de résolution d'un problème dans lequel une fonction s'appelle directement ou indirectement. Une telle fonction est appelée récursive . Fondamentalement, une fonction récursive fonctionne par itération et trouve une solution à un problème plus important en résolvant des instances plus petites du même problème.

Actuellement, Lambda est la seule fonction Excel qui prend en charge la récursivité, vous permettant de créer des solutions compactes et élégantes pour des problèmes complexes sans codage.

Dans VBA, la récursivité est généralement effectuée en utilisant un pour… Suivant ou faire… pendant la boucle. Lambda s'appuie généralement sur la fonction IF pour tester une condition booléenne et se réapparaître si la condition est vraie ou fausse.

Voici la structure d'une fonction lambda récursive sa forme la plus simple:

= Lambda (x, y,…, 'Déclarer les paramètres if (logical_test, ' tester la condition mylambda (), 'recursse si la condition évalue à la vraie valeur_if_false) ' sortir si la condition évalue à false )

Le point clé est d'empêcher les appels récursifs de continuer pour toujours. Pour cela, vous devez fournir le cas de fin (également appelé cas d'arrêt , ou cas de base ). Si aucun point de sortie n'est fourni, une formule continuera à itération jusqu'à ce que votre ordinateur se bloque, je plaisante bien sûr, il lancera un #Num! erreur.

Par rapport aux fonctions non certes, les lambdas récursifs sont plus difficiles à écrire, à tester et à déboguer. Il ressemble au bon vieux poulet et à l'énigme des œufs - pour qu'une fonction fonctionne correctement, elle doit s'appeler; Pour s'appeler, la fonction doit fonctionner correctement :)

Exemple de lambda récursif pour supprimer les caractères indésirables

Lors de l'importation de données provenant de sources externes, les caractères de déchets peuvent souvent se faufiler et vous devez trouver un moyen de nettoyer vos données d'une manière ou d'une autre.

La fonction Remplacer toutes les fonctionnalités peut supprimer toutes les occurrences d'un caractère donné en les remplaçant par rien, mais il ne peut traiter qu'avec un seul caractère à la fois.

Beaucoup plus rapide et plus pratique sera de répertorier tous les caractères indésirables dans une cellule et de les éliminer en un seul coup à l'aide d'une formule. Un lambda récursif est exactement ce dont vous avez besoin:

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

Notre fonction LAMBDA personnalisée est nommée Removechars et nécessite deux paramètres d'entrée:

• Données - Une cellule ou une gamme de cellules à nettoyer.
• Chars - les caractères indésirables à supprimer. Peut être fourni sous la forme d'une chaîne de texte ou d'une référence cellulaire. Dans une cellule, les caractères doivent être répertoriés sans espaces, sauf si vous souhaitez également éradiquer les espaces.

À un niveau élevé, voici ce que fait la fonction:

La fonction Removechars passe par la liste d'exclusion ( Chars ) et purge un caractère à la fois. Avant chaque appel récursif, la fonction IF évalue les caractères restants. Si la chaîne n'est pas vide (chars ""), la fonction s'appelle. Dès que le dernier caractère a été géré, le processus d'itération se termine - la formule renvoie les données de sa forme actuelle et sort.

La logique inverse fonctionnera également: si la chaîne de chars est vide (chars = ""), renvoyez les données actuelles et sorties; Sinon, appelez la fonction Removechars :

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

Quelle que soit l'approche que vous choisissez, le résultat sera exactement le même:

Conseil. La même tâche peut être facilement accomplie avec des expressions régulières. Pour plus de détails, veuillez consulter Excel Regex pour supprimer les caractères spéciaux.

Comment écrire du lambda récursif dans Excel

Je voudrais commencer par un avertissement :) Il n'y a pas de moyen documenté de construire des lambdas récursifs dans Excel, ce qui est explicable étant donné que la fonction est neuve. Je vais partager mon chemin, qui peut vous être utile ou non.

Créez la formule principale

Généralement, vous commencez par écrire les formules de base qui imitent le comportement souhaité de votre fonction lambda. Dans notre cas, l'objectif ultime est de remplacer des personnages spécifiques par rien, et Excel a déjà un instrument idéal pour cela - la fonction de substitut:

Substitut (texte, old_text, new_text, [instance_num])

Pour substituer, nous devons fournir:

• Texte - Le texte dans lequel remplacer les caractères. Dans notre cas, c'est une chaîne de texte dans A2.
• Old_text - le caractère à remplacer. Nous devons vérifier chaque personnage en D2, et il va de soi pour commencer le plus à gauche. La fonction de gauche peut facilement le récupérer pour nous:

  LEFT(D2, 1)

• New_text - le caractère pour remplacer Old_text par. De toute évidence, c'est une chaîne vide ("").
• Instance_num est facultatif et n'est pas nécessaire dans notre cas, il est donc omis.

En conséquence, notre formule principale prend cette forme:

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

Parce que le substitut ne peut effectuer qu'un seul remplacement à la fois, il doit être exécuté autant de fois qu'il y a de caractères sur la liste d'exclusion en D2. La question est - comment pouvons-nous le forcer à gérer le personnage suivant? Et voici la réponse:

À chaque itération, nous retirerons un personnage de la gauche, c'est-à-dire le personnage qui a déjà été examiné. La bonne fonction en combinaison avec LEN peut facilement le faire:

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

Veuillez prêter attention que chaque substitut ultérieur utilise le résultat du substitut précédent comme argument texte , c'est-à-dire qu'il fait le remplacement non pas dans la chaîne d'origine (A2), mais dans la chaîne renvoyée par les fonctions de substitut précédentes (B2):

Convertissez la formule centrale en une fonction lambda

Si vous vous en souvenez, notre fonction personnalisée est censée être nommée RemoveChars , et il aura 2 paramètres: données et caractères .

Votre travail consiste à instruire la fonction sur la façon de calculer chaque paramètre:

• Données - La chaîne dans laquelle remplacer les caractères. Il est fourni par la formule de substitution.
• Chars - les caractères à supprimer. Il est fourni par la bonne formule.

Ce que vous faites, c'est simplement placer les deux formules discutées ci-dessus à l'intérieur de la fonction Removechars les séparant avec des virgules ou tout le caractère utilisé pour séparer les arguments d'une fonction dans votre Excel (déterminé par le séparateur de liste définis dans des paramètres régionaux ).

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

En gardant à l'esprit que Lambda fonctionne sur les paramètres et non sur les références cellulaires, l'étape suivante consiste à changer A2 en données et D2 en Chars :

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

La fonction Removechars est effectuée. Malheureusement, il n'y a aucun moyen de le tester à ce stade, et nous ne pouvons compter que sur les résultats des tests précédents et faire du débogage plus tard si nécessaire.

Faire en sorte que la fonction lambda s'appelle récursivement

C'est la partie clé qui transforme une "formule théorique" en solution de travail.

Comme pour tout Lambda personnalisé, vous commencez par déclarer les paramètres:

=LAMBDA(data, chars,

Ensuite, vous évaluez une certaine condition et selon le résultat, invoquez la récursivité ou la sortie. L'établissement d'un point de sortie est la considération cruciale. Si vous ne faites pas cela, votre formule ne fonctionnera pas correctement car elle ne sortira jamais de la boucle.

Dans notre cas, la fonction IF vérifie si la liste des caractères n'est pas vide (Chars ""). Si vrai ( Chars n'est pas vide), nous appelons la fonction Removechars. Si false ( Chars est vide), nous renvoyons les données de son formulaire et sortant actuels.

Ceci est l'approche générique:

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

Et c'est la vraie formule dans sa forme complète:

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

Alternativement, vous pouvez vérifier si Chars est vierge (chars = ""). Si vrai, renvoyez les données et sortez; Si False Call remonte.

Le concept:

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

La formule complète:

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

Nommez votre fonction définie par Lambda

Nommer les lambdas récursifs n'est pas différent de nommer leurs homologues non recursives:

1. Appuyez sur le raccourci Ctrl 3 pour ouvrir le gestionnaire de noms, puis cliquez sur Nouveau .
2. Dans la boîte de dialogue du nouveau nom , procédez comme suit:
   • Dans la zone de nom , saisissez le nom de la fonction: Removechars .
   • Laissez la portée définie au classeur .
   • Dans la boîte , collez votre formule Lambda en vous assurant qu'elle commence par un signe d'égalité.
   • Facultativement, entrez la description des paramètres dans la zone de commentaires pour une référence supplémentaire.
   • Cliquez sur OK pour enregistrer votre nouvelle fonction.

     

Comment utiliser une lambda récursive dans Excel

C'est la partie la plus simple :) Une fois que votre fonction lambda a obtenu un nom, vous pouvez l'utiliser comme toute autre fonction native.

Du point de vue de l'utilisateur final, la syntaxe de notre fonction personnalisée est aussi simple que celle-ci:

Removechars (données, chars)

Par exemple, pour nettoyer les données dans A2: A10 ( données ), nous tapons les caractères indésirables en D2 ( Chars ), puis entrons la formule ci-dessous dans B2:

=RemoveChars(A2:A10, D2)

Comme vous le savez probablement, dans Excel 356, chaque formule est une formule de tableau dynamique par nature. Ainsi, faisant saisir la formule dans une seule cellule (B2), nous obtenons immédiatement tous les résultats (ce comportement est appelé déversement).

Si vous préférez le comportement traditionnel de la "Formule - One Cell", utilisez une référence de cellule pour les données (A2) et verrouillez l'adresse de cellule de Chars ($ d 2 $) avec le signe $ pour l'empêcher de changer lors de la copie de la formule:

=RemoveChars(A2, $D$2)

La formule ci-dessus va à B2, puis vous le faites glisser à travers B10:

Au lieu de répertorier les caractères à réaliser dans une cellule, vous pouvez les fournir directement à la formule en tant que chaîne de texte:

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

Note. Étant donné que la fonction de substitut utilisée dans la formule centrale est sensible à la casse , notre fonction personnalisée traite les lettres majuscules et minuscules en tant que caractères différents. Si vous souhaitez supprimer un certain caractère, dites "x", quel que soit le cas de lettre, incluez à la fois "x" et "x" dans la chaîne Chars .

Comprendre la récursivité

L'indice pour comprendre les lambdas récursifs est de savoir exactement ce qui se passe avec chaque itération . Dans notre exemple, il y a deux choses de ce type:

• Le résultat du substitut précédent devient le nouveau paramètre de données pour l'appel suivant de Removechars , comme si nous utilisons des fonctions de substitut imbriquées.
• La chaîne Chars est réduite par un caractère. Vous pouvez le considérer comme une sorte de compte à rebours. Une fois que la chaîne Chars devient vide, le processus d'itération s'arrête et la formule renvoie les données dans sa forme actuelle comme résultat final.

Le tableau ci-dessous peut vous aider à mieux visualiser le processus de récursivité:

Plus d'exemples de fonction lambda récursive

Dans les exemples ci-dessous, nous examinerons comment vous pouvez étendre la fonction Lambda existante avec de nouvelles fonctionnalités pour l'ajuster pour vos besoins.

Exemple 1. Retirez les caractères indésirables et coupez les espaces supplémentaires

Outre divers caractères non pertinents, vos données peuvent également contenir des espaces excessifs. Pour vous en débarrasser, vous pouvez nist les removechars à l'intérieur de la garniture comme vous le feriez pour n'importe quelle fonction intégrée:

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

Pour voir l'effet, veuillez comparer les résultats dans les colonnes B et D. Dans ce dernier cas, non seulement les caractères indésirables sont supprimés, mais aussi tous les espaces de tête et de fin, tandis que les espaces intérieurs sont réduits à un seul caractère d'espace entre les mots:

Si vous ne voulez pas vous soucier de nicher à chaque fois, vous pouvez le faire comme une configuration unique à l'intérieur de la Lambda elle-même:

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

Notre fonction améliorée est nommée RemoveTrim et elle fonctionne comme un charme:

=RemoveTrim(A2:A10, D2)

Exemple 2. Remplacez plusieurs caractères par le même caractère

Dans certains scénarios, il est logique de remplacer quelques personnages différents par un autre personnage que vous spécifiez. En fait, c'est ce que fait réellement notre fonction Removechars - remplace les caractères spécifiés par une chaîne vide (""). Cependant, le caractère de remplacement est codé en dur alors que nous voulons le définir directement dans la formule. Pour le faire, nous avons juste besoin d'ajouter un paramètre de plus, disons new_char , à la fonction.

Ainsi, notre nouvelle fonction, le nommons Remplacechars , aura la syntaxe suivante:

Remplacechars (données, chars, new_char)

Pour transformer les Removechars en remplacements , il y a 3 petites modifications à faire:

• Définissez le paramètre 3 ^RD - NEW_CHAR .
• Remplacez la chaîne vide codée en dur ("") par new_char .
• Passez New_Char à la fonction RemplaceChars comme l'argument 3 ^RD .

Dans le résultat, nous obtenons un autre lambda utile pour remplacer plusieurs caractères récursifs:

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

Par exemple, si votre fournisseur modifie soudainement ses formats IDS ou SKUS, vous pouvez remplacer tous les caractères inappropriés (E1) par celui approprié (E2) en utilisant cette formule:

=ReplaceChars(A2:A6, E1, E2)

Exemple 3. Remplacez plusieurs valeurs par d'autres valeurs à la fois

Cet exemple est une extension logique de celle qui avant. Cette fois, nous remplacerons des mots (ou des chaînes) entiers plutôt que des caractères uniques, et chaque mot aura sa propre valeur de remplacement.

Étant donné que les valeurs anciennes et nouvelles seront placées dans des cellules séparées (comme indiqué dans la capture d'écran ci-dessous), la bonne fonction que nous avons utilisée dans les exemples précédents ne fonctionnera pas. Pour traverser les paires anciennes / nouvelles, nous devons comprendre autre chose. Hmm, il semble y avoir une fonction dans Excel pour déplacer un nombre spécifié de lignes et de colonnes à partir d'une cellule donnée. Oui, c'est compensé!

Avec la méthode principale établie, il n'est pas grave d'écrire la fonction de remplacement :

Rempaceall (données, ancienne, nouveau)

Pour les données , nous utilisons la fonction de substitut sous sa forme de base simplement pour remplacer l'ancienne valeur par la nouvelle:

SUBSTITUTE(data, old, new)

Pour obtenir l' ancienne valeur, nous allons commencer par la cellule la plus haute de l' ancienne liste et déplacer 1 ligne vers le bas à chaque interaction:

OFFSET(old, 1, 0)

Pour obtenir la nouvelle valeur, nous ferons exactement la même chose mais, bien sûr, sur la nouvelle liste:

OFFSET(new, 1, 0)

Enfin, implémentez la stratégie de sortie déjà familière avec l'aide de IF, et votre nouveau puissant Lambda récursif est prêt à l'emploi (n'oubliez pas de le nommer dans le gestionnaire de nom :)

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

Avec les données source dans A2: A10, les anciennes valeurs de la colonne D à partir de D2, et les nouvelles valeurs de la colonne E commençant dans E2, vous pouvez effectuer plusieurs remplacements avec cette formule simple:

=ReplaceAll(A2:A10, D2, E2)

En conséquence, une seule formule en B2 remplace toutes les abréviations dans A2: A10 par les noms complets correspondants:

Lambdas récursive contre les fonctions définies par l'utilisateur VBA

Les utilisateurs avancés d'Excel avec un arrière-plan de programmation peuvent être curieux de voir comment une fonction Lambda récursive est en corrélation avec un code VBA comparable. Eh bien, jetons un coup d'œil.

Lambda récursif pour supprimer plusieurs caractères

Si vous comprenez, il s'agit d'un pseudocode non fonctionnel. Nous l'avons mis dans un éditeur VBA pour représenter l'algorithme sous la forme familière pour mieux comprendre ce qui se passe :)

= Lambda (données, chars, 'Declare Paramètres if (Chars "" , ' Vérifiez si les caractères ne sont pas des removechars vides (substitut (données, gauche (Chars, 1), "" ), 'If True, appelez les remises de remous (Chars, Len (Chars) -1)), Data ' Si false, retourne les données et sortie )))

Fonction définie par l'utilisateur pour supprimer plusieurs caractères récursifs

Et c'est ainsi qu'une fonction analogue définie par l'utilisateur peut être écrite en VBA:

Fonction RemoveCharsRecursive (données sous forme de chaîne , chars comme chaîne ) if ( "" chars) puis data = remplacer (data, gauche (char, 1), "" ) chars = droite (char, len (chars) - 1 ) removecharsrecursive = data

Fonction définie par l'utilisateur pour supprimer plusieurs caractères

Une fonction similaire peut également être écrite en utilisant une méthode non réécursive. Dans ce cas, nous écrivons Removechars comme une fonction distincte et l'appelons à l'intérieur de la fonction RemoveCharsNonRecursive lorsque la chaîne de chars n'est pas vide.

Fonction RemoveCharsNonRecursive (données sous forme de chaîne , chars comme chaîne ) tandis que "" chars data = removechars (data, chars) chars = droite (chars, fonction de la fonction de data (chars) - 1) wend removecharsNonReCursive = data end function re: "data as string , chars as string ) removechars = remplacer (data, gauche (chars, 1), " ) end )

La même tâche peut être accomplie d'une manière différente. Vous pouvez parcourir les caractères d'exclusion de 1 à Len (Chars) et remplacer les Chars trouvés dans les données par une chaîne vide. La fonction médiane est utilisée pour extraire chaque caractère unique de la chaîne Chars un par un.

Fonction RemoveCharsNonRecursive2 (données comme chaîne , chars comme chaîne ) pour index = 1 à len (chars) data = remplacter (data, mid (chars, index, 1), "" ) removecharsonRecursive2 = data end function

Quel est l'avantage d'utiliser les Lambdas par rapport aux fonctions définies par l'utilisateur VBA? D'abord et avant tout, ils ne nécessitent pas d'enregistrement des classeurs en tant que fichiers .xlsm macro-compatibles et vous permettent de vous permettre d'activer les macros à chaque ouverture.

J'espère que ce tutoriel vous a aidé à comprendre à quoi ressemble une Lambda récursive dans Excel. Je vous remercie d'avoir lu et j'espère vous voir sur notre blog la semaine prochaine!

Pratiquer des classeurs à télécharger

Exemples de Lambda récursifs (fichier .xlsx) Fonctions définies par l'utilisateur VBA (fichier .xlsm)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!