Les bases de la fonction LAMBDA

Fonction pour créer des fonctions Excel permettant de réaliser d’extraordinaires formules.

Pourquoi le terme LAMBDA ?

C’est la 11eme lettre de l’alphabet grec (λ).

La bonne réponse se trouve plus du côté des mathématiques et de l’informatique mais pourquoi ne pas inventer une origine qui colle bien à cette fonction.
Dans la langue française, lambda signifie aussi « quelconque », comme dans l’expression « un individu lambda » donc une fonction quelconque qui n’a pas de nom ni de fonction spécifique.
Tout comme une page vierge, l’utilisateur pourra l’utiliser pour créer quelque chose de nouveau en lui donnant un nouveau nom et une formule.

À quoi ça sert ?

À créer de nouvelles fonctions de calculs dans Excel pour éviter de refaire des formules complexes. À masquer à l’utilisateur leurs complexités.

Avec le VBA (programmation), on peut créer ses propres fonctions de calcul (UDF User Defined Function) mais elles vont poser des problèmes d’utilisations :

• Soient elles se trouvent dans le fichier ou elles sont utilisées mais implique un enregistrement en xls ou xlsm du fichier avec tous les problèmes que cela implique (alertes virus inquiétantes, macro désactivé, blocage du fichier…).
• Soient elles sont externes au fichier mais la fonction sera en erreur (#NOM / #NAME) si le fichier est ouvert sur un autre poste informatique (sauf d’avoir à l’avance fourni le code et avoir correctement configuré Excel pour l’utiliser).
• Elles ne sont pas compatibles avec la version Web d’Excel.

Avec les fonctions LAMBDA, on n’a pas ces désagréments.
Bien que plus limitées, elles ont l’avantage d’être accessibles à toutes personnes maitrisant les fonctions dynamiques d’Excel sans faire de programmation.

Dans les formules Excel via la fonction Si on peut réaliser des structures conditionnelles mais il était impossible de faire des boucles. Maintenant c’est possible car les fonctions LAMBDA peuvent être récursive c’est à dire s’appeler elle-même.

La syntaxe

=LAMBDA(Parametre1 [ ; Parametre2 ; ... Parametre253] ; Formule/Calculation)

• Parametre1 à 253 : Noms des valeurs utilisés par la formule précisée par l’argument Formule (exemple : Age, Date, Nom).
  • Obligatoire pour le 1 (contrairement à ce qui est indiqué dans l’aide).
  • Facultatifs pour les autres.
• Formule (obligatoire) : Formule renvoyant le résultat.

Contraintes pour les noms utilisés par les arguments Paramètres !

• Pas de nom en double.
• Caractères non autorisés :  . ; , : espace + – * / = ) ( ] [ } { |  @ _ # & $ £ % ! < > °
• Caractères autorisés a à z, 0 à 9, \ à ù ç é ² ¤ µ § ? €
• Pas de chiffre au début ou à la fin du nom.
• Selon certaines sources, il pourrait y avoir de problèmes si le nom correspond à une macro Excel4. Les quelques tests que j’ai faits n’ont pas été concluant pour confirmer cette information. 

Disponibilité de cette fonction

• Windows : 16.0.14729
• Mac : 16.56 (Build 211211)
• Web : introduit le 18 mars 2022
• iOS : 2.56 (Build 211207)
• Android : 16.0.14729

Exemple 1 : Exemple d'illustration de son fonctionnement

On commence par un exemple très simple, on va créer une fonction pour calculer la surface d’un parallélogramme (S = Base x Hauteur).

=LAMBDA(Base ; Hauteur ; Base * Hauteur)

Comment tester sa fonction LAMBDA ?

Avant de nommer sa fonction on va la tester. On ajoute à la fin de la formule les valeurs des paramètres entre parenthèses.

=LAMBDA(Base ; Hauteur ; Base*Hauteur)( 5 ; 10)
=LAMBDA(Base ; Hauteur ; Base*Hauteur)(A4 ; 10)

Comment lui donner un nom afin de l'utiliser facilement ?

Il suffit de définir un nom Excel dont la valeur est la fonction LAMBDA.

Je vous conseille :

• D’écrire le nom en partie en minuscule (ex : MaFonction) pour distinguer vos fonctions des fonctions standards d’Excel ou d’utiliser un préfix (ex : _MAFONCTION).
• De documenter votre fonction, d’expliquer à quoi sert votre fonction, ce que sont ses arguments, qui la créée dans la zone Commentaire. Cette information apparaîtra dans une info-bulle au moment de la sélection de la fonction.

Comment utiliser cette nouvelle fonction ?

Comme une fonction standard d’Excel.

Saisie directe

Les noms des arguments apparaissent.

Utilisation de l’assistant fonction d’Excel

Les noms des arguments n’apparaissent pas.

Petite synthèse

Les avantages

1. Comme vous venez de voir, créer une nouvelle fonction avec la fonction LAMBDA est très simple.
2. Terminé les formules monstrueuses dans la barre de formule. Vous pouvez appeler de manière compacte des fonctions très complexes, appelant d’autre fonctions basées elles même sur d’autres fonctions LAMBDA ou non LAMBDA.
3. En cas de modification de la formule, terminé de chercher partout dans le classeur et de devoir répéter plusieurs fois la modification maintenant c’est centralisé dans le Gestionnaire de nom.
4. Ces fonctions sont liées au classeur et fonctionneront même en mode web.

Les inconvénients

1. Pas de rétrocompatibilité avec les anciennes versions.
2. Fonctions liées au classeur donc 2 fonctions de même nom de 2 classeurs ne feront pas forcément les mêmes calcules.
3. Risque de confusion des utilisateurs du classeur ne sachant plus ce qui est fonction standard et fonction personnelle.
4. Pas de description de la fonction et des arguments dans l’assistant fonction.

Quelques exemples

Exemple 2 : Paramètres optionnels 1

On peut anticiper ce que fera la formule de notre fonction dans le cas où un des paramètres n’a pas été précisé via la fonction ISOMITTED.

On va créer une fonction correspondant à une fonction SOMME.SI.ENS faisant la somme des QT du tableau en fonction d’une ville et du sexe.
Si le sexe n’est pas précisé, il faudra prendre en compte tous les sexes.

=LAMBDA(Ville;Sexe;SOMME(FILTRE(C1:C6;(A1:A6=Ville)*(SI(ISOMITTED(Sexe);VRAI;B1:B6=Sexe)))))("Dijon";) '=> 1101 

Ici il y a plusieurs astuces.

• La fonction FILTRE ne porte que sur la colonne C mais les clauses du filtre portent sur les colonnes B et C (beaucoup d’utilisateurs pensent que c’est obligatoire de travailler sur la plage A2:C6).
• La valeur VRAI pour inclure les hommes et les femmes (l’argument Inclure de la fonction FILTRE doit simplement renvoyer autant de valeurs VRAI ou FAUX que de lignes à trier).

Exemple 3 : Paramètres optionnels 2

On veut récupérer des informations d’internet concernant les propriétaires d’une adresse IP (voir l’article Exploiter des données d’Internet avec la fonction SERVICEWEB).

'InfosIp : 
=LAMBDA(Ip;Info;SI(OU(ISOMITTED(Info);Info="");"Info non précisée";FILTRE.XML(SERVICEWEB("https://ipapi.co/"& Ip &"/xml/");"root/"&Info)))

ISOMITTED teste si l'argument a été saisie pas sa valeur qui peut être vide ou 0 d'où le test supplémentaire Info = "".

Utilisation en mode récursif

Ça va se compliquer car la notion même de récursivité ne nous est pas naturelle.

C'est quoi la récursivité ?

C’est le fait que la fonction va faire le calcul en exécutant une copie d’elle-même qui va elle-même exécuté une copie ainsi de suite.
Imaginer le reflet d’un miroir en face d’un autre miroir, les deux miroirs se réfléchissant à "l’infinie".
Le but étant de découper un gros problème en une succession de plus petits.

C’est la seule fonction d’Excel qui prend en charge la récursivité nous permettant de faire des boucles dans les formules Excel comme en programmation.

Simulons ce fonctionnement

Un peu de vocabulaire : On parle d’appel de fonction (ou itération) c’est à dire d’une exécution de la fonction (une boucle).

1. Appel1 : On valide la fonction (c’est l’Appel1),
   la fonction va s’appeler elle-même (c’est l’Appel2) et se met en attente du résultat de ce 2eme appel.
2. Appel2 : Ce nouvel appel (Appel2) va générer lui-même un nouvel appel (Appel3) et se met en attente ainsi de suite.

On parle de pile d’appels.

Via une condition on va arrêter cet enchainement (ex : à l’Appel 4) et retourner finir d’exécuter l’appel précédent (l’Appel3) ainsi de suite.
Sans cet arrêt Excel partirait en théorie sur une boucle infinie mais en pratique il bloque au moment où il arrive à sa limite.
Cette limite est liée au nombre de paramètres utilisés (à la date ou j’écris cet article : Environ 5400 avec 1 paramètre, 4100 avec 2, 3300 avec 3…).

Structures classiques d'une fonction LAMBDA imbriquées

' MaFonction :
=LAMBDA(Paramètre1 ; Paramètre2 ; ...
        SI (Test_logique ;
        MaFonction ;
        Valeur))

Ou la fonction SI inversée :

' MaFonction :
=LAMBDA(Paramètre1 ; Paramètre2 ; ...
        SI (Test_logique ;
        Valeur ;
        MaFonction))

Exemple 4 : Exemple d'illustration de son fonctionnement

On donne une lettre de départ et un nombre, Excel génère une chaîne représentant la suite alphabétique à partir de cette lettre et de la longueur Nombre.

=ReptLettres("a";4) ' => "abcd" 

Décomposons sa structure

Pour simplifier la formule et illustrer comme faire interagir ces fonctions, on va déjà créer la fonction LettreSuivante permettant d’obtenir le caractère suivant.

' LettreSuivante :
=LAMBDA(Lettre;CAR(CODE(Lettre)+1))

Logique : On récupère le code du caractère, on ajoute 1 et on récupère le caractère correspondant.

La fonction principale :

' ReptLettres : 
=LAMBDA(Lettre;Nbr;SI(Nbr<2;Lettre;Lettre & ReptLettres(LettreSuivante(Lettre);Nbr-1)))

La fonction ReptLettres contient un appel à elle-même.
La clause d’arrêt est Nbr<2.
Chaque appel se fait avec une valeur Nbr diminuée de 1 (Nbr-1) jusqu’à arriver à la valeur de la clause d’arrêt (Nbr<2).

Tentative laborieuse d’illustrer la logique des appels de cette fonction

Exemple 5 : Calcul de factorielles

Rappel : La factorielle de 4 (4!) c’est 4x3x2x1.
Refaisons la fonction standard d’Excel FACT.

' Factorielle :
=LAMBDA(Val;SI(Val<3;2;Val*Factorielle(Val-1)))
=Factorielle(4) ' =>  24 

Autre tentative d’illustrer autrement cette récursivité :

Factorielle(4) = 4 * Factorielle(3)     => Val * Factorielle(Val-1)
               = 4 * 3 * Factorielle(2) => Val * Factorielle(Val-1)
               = 4 * 3 * 2              => SI Val<3 alors 2

Fonction « Compilé » présentant la chaine de calculs :

On a arrêté la boucle à 2, la multiplication par 1 étant sans objet.

Comment tester une LAMBDA récursive ?

Chose assez complexe à réaliser !
L’astuce des parenthèses ne fonctionnera pas dans ce cas.

=LAMBDA(Val;SI(Val<3;2;Val*Factorielle(Val-1)))(4) ' => #NOM? 

Excel ne trouve pas la fonction Factorielle (sauf si vous ne l’avez pas définie en réalisant l’exemple précédent !).

On va ruser en utilisant la fonction LET qui permet de définir des noms locaux dans une fonction. On va réaliser cette formule par étape :

=LET(Factorielle ; LAMBDA(Val;SI(Val<3;2;Val*Factorielle(Val-1))) ; Factorielle(4)) ' => #NOM? 

Ça ne fonctionne pas !  Le nom Factorielle ne peut pas elle aussi "s’auto-définir" !
Avant d’aller plus loin dans la solution, on va déjà observer une « étrange » propriété des fonctions LAMBDA.

Considérons ces 2 fonctions :

' Fct :
 =LAMBDA(Val;Val+1)
'Valeur10 :
=LAMBDA(NomFct;NomFct(10))

• La fonction Fct n’a rien de particulier, elle ajoute 1 à la valeur du paramètre.
• La fonction Valeur10 est étrange, le paramètre (NomFct) n’est pas utilisé en tant que valeur de l’argument de la fonction mais est utilisé comme le nom d’une fonction !

Valeur10(Fct) ' = 11 !!! 

La fonction qui est appliqué à 10 est donc paramétrable ! Il faut donc se rappeler qu’un paramètre peut véhiculer le nom d’une fonction.

Retour à notre problème, on va ajouter à notre fonction un paramètre supplémentaire pour nous permettre de remplacer ce nom gênant par un autre nom (Fct, Moi, Me par exemple).

=LET(Factorielle ; LAMBDA(Fct;Val;SI(Val<3;2;Val*Fct(Val-1))) ; Factorielle(4)) ' => #VALEUR 

La fonction ayant maintenant 2 paramètres il nous faut donc ajouter ce 2eme paramètres partout où il est utilisé.

=LET(Factorielle ; LAMBDA(Fct;Val;SI(Val<3;2;Val*Fct(Fct;Val-1))) ; Factorielle(Factorielle;4)) ' => 24 

Comparons les 2 syntaxes :

=                LAMBDA(    Val;SI(Val<3;2;Val*Factorielle(    Val-1)))
=LET(Factorielle;LAMBDA(Moi;Val;SI(Val<3;2;Val*Moi        (Moi;Val-1)));Factorielle(Factorielle;4))

Il suffira de reproduire ce schéma pour tester vos LAMBDA récursive, facile ! 😊

Comment importer ou exporter une fonction LAMBDA

Une méthode très simple mais ce sera toutes les fonctions !

1. Créer une feuille vierge dans le classeur contenant les fonctions,
2. Déplacer la feuille dans le classeur cible,
3. Supprimer cette feuille.

D'autres utilisations de la fonction LAMBDA

La fonction LAMBDA est liée à une série de nouvelles fonctions d’Excel (MAP, SCAN, REDUCE, BYCOL, BYROW, MAKEARRAY) qui seront le sujet d’autres articles.

Merci pour votre attention bienveillante.