En introduisant la fonction LAMBDA, Microsoft initie un véritable changement de paradigme dans l’utilisation d’Excel. Je vous l’annonce depuis un moment maintenant, le VBA n’est pas l’avenir d’Excel et ma position sur les macros. Comment parvenir à changer les esprits ? En offrant une alternative pertinente à chacune des motivations d’utilisation du VBA.
Un nouveau pas vient d’être franchi : Vous permettre de développer vos propres fonctions SANS VBA ! Oui, la fonction LAMBDA est une fonction permettant d’en écrire d’autres !
Bienvenue dans le nouveau monde d’Excel : un monde où les limites tombent les unes après les autres !
À ce jour, seuls les détenteurs d’un abonnement à Excel 365 ont accès à la fonction LAMBDA (Ce n’est même accessible que pour les membres du programme Office Insider qui permet d’accéder aux fonctions en avant-première, avec les risques de bugs qui vont avec…).
La fonction LAMBDA : Tout sauf quelconque
Son nom pourrait laisser penser qu’elle est quelconque… en réalité, elle est véritablement révolutionnaire dans le monde d’Excel ! Vous pensez que j’exagère ? Regardons de plus près !
Votre première fonction LAMBDA
Cette fonction est d’un nouveau genre. Elle permet de créer d’autres fonctions qui pourront être appelées par d’autres formules.
Hmmm… C’est intéressant comme idée : une fonction pour écrire des fonctions. Mais comment cela fonctionne-t-il ? Comment l’utilise-t-on ? Comment définit-on les paramètres de cette fonction ? De combien de paramètres peut-on disposer au maximum ?
Les paramètres de votre fonction LAMBDA
Lorsqu’on pense aux fonctions Excel, on pense à SI(), RECHERCHEV(), SOMME(), etc. Chacune d’entre elles possède des paramètres d’appel et vous savez également quel type de résultat vous aurez en retour.
Par exemple, la fonction SOMME() prend jusqu’à 255 paramètres et chacun de ces paramètres peut être un nombre, une référence à une cellule ou une référence à une plage de cellules.
Alors, de combien de paramètres avez-vous besoin pour votre fonction LAMBDA ?
Prenons un exemple : Vous êtes un étudiant ou un enseignant et souhaitez calculer la moyenne des notes pondérées par des coefficients.
Ici, la formule cible est
=SOMMEPROD( [Coefficient]; [Note] ) / SOMME( [Coefficient] )
Il n’y a donc que deux paramètres : la colonne des coefficients et la colonne des notes. Avec ces deux informations, il est possible d’écrire la formule.
Pour ceux qui ne connaissent pas la fonction SOMMEPROD( Plage1; Plage2), elle permet de réaliser le calcul :
(1er élément de la Plage1 x 1er élément de la plage 2) + (2e Plage1 x 2e Plage2) + etc
Écrire une fonction LAMBDA
Par nature, Excel ne peut deviner le nombre de paramètres dont vous aurez besoin dans votre future fonction… Il va donc TOUJOURS considérer que le dernier paramètre d’appel (de la fonction LAMBDA) correspondra à votre formule de calcul. Les premiers éléments étant les variables dont vous aurez besoin pour votre formule…
Ce n’est pas très clair ? Voyons de plus près avec le calcul de la moyenne pondérée des notes.
Commençons par un retour sur les bancs de l’école avec un bel énoncé :
Soit c l’ensemble de mes coefficients et n l’ensemble de mes notes pour calculer la moyenne pondérée. Veuillez trouver la formule pour calculer la moyenne pondérée. Vous avez 2 minutes !
En même temps, je vous ai donné la réponse quelques lignes plus haut. Il « suffit » de placer des variables à la place des plages.
La formule devient donc « SOMMEPROD(c;n)/SOMME(c)« .
Est-ce terminé ? Pas tout de suite… encore un peu de patience.
Une fonction LAMBDA s’écrit de la manière suivante :
=LAMBDA(ma_première_variable; ma_seconde_variables; …, ma_nième_variable; mon_calcul_qui_utilise_les_variables)
Afin de vous y retrouver, je vous recommande d’utiliser des noms de variables qui vous parlent. Les plus mathématiciens d’entre vous utiliseront peut-être les fameux x, y, z comme variables. Ici, je choisis d’utiliser les premières lettres de « Coefficient » et « Notes » pour nommer les variables dont nous avons besoin.
La fonction LAMBDA définitive s’écrit donc ainsi :
=LAMBDA(c; n; SOMMEPROD(c;n)/SOMME(c) )
Comment invoquer une fonction LAMBDA ? Doit-on simplement écrire cette fonction dans une cellule ?
Malheureusement, si vous inscrivez cette fonction juste ainsi, vous recevrez un joli message d’erreur de la part d’Excel : #CALC!
En effet, vous venez d’écrire une fonction … que vous n’avez pas appelée ! C’est comme si vous écriviez « =SI() ». Excel vous retournerait la même erreur !
Pour que cela fonctionne dans une cellule, il faut renseigner les valeurs à attribuer aux paramètres d’appel, en l’occurrence, les deux paramètres c et n avec les valeurs des deux plages de valeurs.
=LAMBDA(c; n; SOMMEPROD(c;n)/SOMME(c))(Notes[Coefficient]; Notes[Note])
À ce stade, je sens naître en vous une pointe de scepticisme… Vous auriez pu écrire la fonction directement au lieu de passer par l’appel à la fonction LAMBDA…
Sauf que ce n’est pas terminé… cette étape essentielle permet de vérifier que votre fonction est bien écrite et retourne le résultat attendu. Maintenant, vous allez pouvoir inscrire votre fonction LAMBDA dans votre classeur Excel pour pouvoir être réutilisée.
Donner un NOM à votre fonction
Finalement, lorsqu’on y pense, le meilleur endroit pour donner un nom à votre fonction LAMBDA, c’est justement dans le gestionnaire de noms !
Ensuite, vous aurez à donner un nom à votre fonction :
Ici, chacun des points de l’illustration a son importance.
- Le nom de votre fonction
C’est celle qui sera présentée dans l’assistance à la saisie. Je vous invite à nommer de manière explicite votre fonction afin d’immédiatement identifier son objectif . - Le commentaire
Ce que vous écrirez ici apparaîtra dans l’infobulle de votre fonction. Ici, pas de mise en forme possible (retour chariot, gras, italique, etc.). Si vous disposez de nombreux paramètres de saisie, ma recommandation : Explicitez chacun d’entre eux de manière concise. - Fait référence à
C’est ici que vous saisissez votre fonction LAMBDA, et uniquement la définition de la fonction, sans aucune référence à des cellules ou des plages de votre classeur.
Et voilà ! Votre première fonction LAMBDA est écrite !
Utiliser votre fonction LAMBDA nouvellement créée
Dès le moment où vous avez cliqué sur OK sur la fenêtre précédente, Excel a désormais connaissance de la nouvelle fonction. Voyez par vous-même :
Vous retrouvez ici le texte saisi précédemment dans le champ « Commentaire ». Vous comprendrez qu’une description trop longue rendrait la lecture difficile.
Toutefois, contrairement aux fonctions Excel, ici, pas d’assistance contextuelle pour chacun des paramètres de la fonction.
Alors que normalement, vous disposez d’une assistance pour la liste des paramètres obligatoires ou optionnels (les paramètres optionnels se présentent entre crochets) :
Vous devrez donc connaître (ou bien décrire dans le commentaire) la liste des paramètres à renseigner.
Désormais, vous pouvez utiliser cette formule pour calculer la moyenne pondérée :
L’accès aux fonctions de niveau supérieur : les fonctions LAMBDA récursives
Je dois vous avertir : Vous entrez en zone dangereuse. L’utilisation de la récursivité est une notion qui n’est pas destinée aux débutants. Cette notion est habituellement enseignée à celles et ceux qui apprennent la programmation.
En effet, une fonction récursive est une fonction qui s’appelle elle-même dans la formule qu’elle représente. Quand on y pense, cela peut dans un premier temps être très troublant.
Dans l’utilisation courante d’Excel, cette forme d’utilisation vous rappelle peut-être l’erreur dite de « référence circulaire » ?
Le plus simple, c’est de comprendre à l’aide d’un l’exemple.
Votre première fonction LAMBDA récursive
Les fonctions récursives sont utiles lorsqu’on doit répéter une opération en boucle jusqu’à ce qu’une certaine condition soit vraie. Cela reste encore trop théorique ? C’est normal.
Prenez le tableau de valeurs suivant :
| Relevés | Valeurs |
|---|---|
| Relevé 1 | 1 |
| Relevé 2 | 43 |
| Relevé 3 | 5 |
| Relevé 4 | 65 |
| Relevé 5 | 76 |
| Relevé 6 | 678 |
| Relevé 7 | 789 |
| Relevé 8 | 43 |
Maintenant, imaginez que vous souhaitiez ajouter une colonne présentant la somme cumulée des valeurs des relevés précédents.
Cela représenterait les calculs suivants :
| Relevés | Somme cumulée |
|---|---|
| Relevé 1 | Valeur 1 |
| Relevé 2 | Valeur 1 + Valeur 2 |
| Relevé 3 | Valeur 1 + Valeur 2 + Valeur 3 |
| … | |
| Relevé 8 | Valeur 1 + Valeur 2 + Valeur 3 + Valeur 4 + Valeur 5 + Valeur 6 +Valeur 7 + Valeur 8 |
Un des raisonnements que l’on peut tenir est :
Je souhaite additionner la valeur du relevé actuel avec les valeurs précédentes jusqu’à arriver à la ligne des en-tête.
C’est exactement de cette manière que notre fonction LAMBDA sera construite.
Utiliser un nom de fonction qui n’existe pas encore… Étrange n’est-ce pas ?
Comme nous l’avons vu précédemment avec votre première fonction LAMBDA, elle n’existera que lorsque vous aurez créé un NOM avec le gestionnaire de nom.
Pour les fonctions récursives, vous aurez la même procédure à suivre. Toutefois, vous devrez connaître le nom de votre fonction avant qu’elle n’existe puisque vous allez l’utiliser dans votre formule.
Vous aurez également à déterminer le critère de sortie de la récursivité. Sinon… vous resterez enfermé à jamais dans la fonction ! :).
Déterminer les paramètres de votre fonction
Ici, j’ai identifié 3 paramètres :
- L’identifiant du relevé actuel
- La valeur du relevé actuel
- La valeur de l’en-tête des relevés du tableau
Pour construire ma fonction récursive, je raisonne donc ainsi :
Ajouter les valeurs du relevé actuel au total précédent jusqu’à ce que la valeur de la colonne « Relevés » soit celle de l’en-tête de mon tableau.
Écrire une fonction récursive pas à pas
Maintenant que les paramètres sont identifiés, il faut nommer à l’aide de variables (une chaîne de caractères quelconque qui représentera votre paramètre dans la formule finale).
Dans mon cas, je vais utiliser les variables suivantes :
| Paramètre | Nom de la variable |
|---|---|
| Identifiant du relevé actuel | r |
| Valeur du relevé actuel | v |
| Valeur de l’en-tête | arret |
J’ai choisi ensuite de nommer ma fonction « SommeCumulee ».
L’idée maintenant est de procéder comme suit :
- Tester si la ligne courante est celle de l’en-tête, si oui, on renvoie la valeur sinon on renvoie zéro. C’est notre critère de sortie de la récursivité.
- Ensuite, on décale la ligne de référence à la ligne précédente puis on appelle la fonction SommeCumulee avec les nouveaux paramètres actualisée. La récursivité est maintenant démarrée.
- C’est tout !
Cela donne la fonction LAMBDA suivante (j’ai ajouté des espaces et sauts de lignes pour plus de lisibilité) :
=LAMBDA(r; v; arret;
SI(r=arret ; 0 ; SommeCumulee( DECALER(r; -1; 0); DECALER(v; -1; 0); arret) + v)
La fonction DECALER() permet de se déplacer d’une cellule à l’autre. Ici, le deuxième paramètre étant le nombre de déplacements en ligne à réaliser. La valeur -1 indique qu’on se dirige vers la ligne juste au-dessus.
Il faut bien évidemment déplacer également la cellule contenant les valeurs, d’où l’utilisation de DECALER() à deux reprises.
Ainsi, la fonction va être appelée en boucle jusqu’à ce que la cellule corresponde à la cellule d’arrêt.
On se rend alors dans le gestion des noms et complétons ainsi :
Utiliser la fonction récursive dans un tableau
Maintenant que votre fonction est écrite, il ne reste qu’à l’utiliser !
Comme précédemment, vous n’aurez que l’assistance décrite dans la zone « Commentaire » de votre NOM.
Grâce à cette fonction, vous pourriez même imaginer l’utiliser pour cumuler les valeurs à partir du relevé 2 ou 4 ! Eh oui, ce n’est qu’un paramètre à changer !
Attention à deux choses tout de même :
- Si vous ne fixez pas le 3e paramètre, il sera adapté par Excel à chaque ligne.
- La formule va s’appliquer à toutes les cellules de la 3e colonne. Sauf que pour les cellules qui sont avant la cellule d’arrêt… il n’y a plus rien pour les arrêter qu’une erreur (l’appel à la fonction DECALER vers la ligne 0 par exemple).
A ces deux réserves près, vous pourrez faire des sommes cumulées à votre goût !
Vous n’aurez alors qu’à effacer la formule dans les cellules où la formule n’est pas applicable.
Un exemple en vidéo : La fonction Fibonacci
Quoi de mieux qu’un exemple en vidéo ? Je vous propose de découvrir la fonction LAMBDA à travers une vidéo.
Dans un premier temps, je vous présente une simple fonction avec trois paramètres (a, b, c) et ensuite, une fonction récursive, et pas des moindre puisqu’il s’agit de la fonction Fibonacci !
Je vous rappelle que la suite de Fibonacci se définit ainsi :
F0 = 0 / F1 = 1 / Fn = Fn-1 + Fn-2
Les fonctions LAMBDA ont-elles des limites ? Et ensuite ?
Actuellement, comme de nombreuses fonctions EXCEL, la fonction LAMBDA ne peut recevoir « que » 255 paramètres. Donc, 254 variables + 1 pour la fonction à définir.
Comme je l’ai mentionné à plusieurs reprises, dans la version actuellement à disposition, il n’y a pas d’assistance à l’utilisation de la fonction comme les autres fonctions d’Excel. Vous devrez donc bien expliquer dans le champ Commentaire la liste des paramètres et vous en souvenir lors de son utilisation.
Pourquoi est-ce une révolution ?
Jusqu’à présent, vous n’aviez pas de choix pour créer vos fonctions personnalisées : vous deviez apprendre le VBA et créer des macros.
Le détournement des macros réalisé par les pirates informatiques pour transmettre des virus à l’aide des fichiers Excel, Word ou PowerPoint fait maintenant trop de dégâts.
Microsoft le sait mais n’avait rien d’autre à proposer aux utilisateurs avancés d’Excel pour la création de fonction personnalisée. C’est maintenant chose faite !
Lorsqu’on compile les dernières avancées majeures d’Excel, on note Power Query, la possibilité d’interagir avec Excel avec du code JavaScript et maintenant les fonctions LAMBDA ! Le mouvement est très clair (à mes yeux) : offrir toutes les options pour sortir des macros !
Vous n’êtes pas d’accord ? Vous souhaitez en discuter ? Cet article vous a plu (ou pas !), faites-le moi savoir !
La zone de commentaires est à vous ! A tout de suite !
C’est effectivement plus simple. Tellement pressé de parler de la fonction LAMBDA qu’on en oublie ses bases…
Bonjour, je viens de tomber sur ce sujet.
J’ai juste une petite remarque, on peut optimiser la fonction avec un seul test si >=0
=LAMBDA(x;SI(x<0;NA();SI(x<2;x;Fibonacci(x-1)+Fibonacci(x-2))))
J’ai de nombreux débats avec d’autres spécialistes… et nous ne sommes pas toujours d’accord (c’est l’intérêt d’un débat en même temps). VBA reste un outil puissant… et encore pour quelques années. Par contre, je pense qu’il y a des investissements plus intéressants à faire lorsqu’on est au début de son parcours.
Mince, moi qui venait d’apprendre VBA ! Super intéressant en tout cas et très bien expliqué. Merci !
Avec plaisir ! Restez connecté pour continuer à recevoir des nouvelles ! 🙂
Merci encore pour cet excellent tuto ! Bravo pour la pédagogie !
Avec plaisir ! 🙂
Encore un tuto très clair (merci !) qui me laisse voir et comprendre toute l’étendue d’un outil que je manipule depuis toujours… et à 5% de son potentiel ! Tous vos articles tutos sont très bien expliqués et me font découvrir à chaque fois une fonctionnalité nouvelle. Merci encore