Créez vos propres fonctions personnalisées sans VBA dans Excel (nouvelle fonction LAMBDA)

Dans ce tutoriel, je vais vous montrer comment créer vos propres fonctions personnalisées dans Excel sans connaître la programmation, grâce à la puissante fonction LAMBDA.

Cette nouvelle fonction révolutionnaire vous permettra de créer des outils sur mesure, adaptés à vos besoins quotidiens, qui vous feront gagner un temps précieux dans votre travail.

À la fin de cette vidéo, vous saurez transformer des formules complexes en fonctions simples à utiliser, et vous pourrez les réutiliser n'importe où dans votre classeur avec un nom facile à retenir.

C'est comme si vous créiez vos propres extensions Excel, sans écrire une seule ligne de code VBA !

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous avons des données de ventes d'une boutique de vêtements.

Ce tableau comprend plusieurs colonnes qui nous donnent des informations sur chaque transaction : la date de la vente, le nom du vendeur, la catégorie du produit vendu, le nom du produit, son prix unitaire, la quantité vendue, le montant total de la vente, ainsi que la région dans laquelle la vente a été effectuée.

2. Comprendre la fonction LAMBDA : principes fondamentaux

   2.1. Qu'est-ce que la fonction LAMBDA et pourquoi l'utiliser ?

La fonction LAMBDA est une fonctionnalité relativement récente d'Excel qui nous permet de créer nos propres fonctions personnalisées directement dans notre classeur, sans avoir besoin de connaissances en programmation VBA. Comme nous l’avons vu un peu plus tôt, c'est comme si nous avions la possibilité de créer nos propres extensions sur mesure pour Excel, adaptées exactement à nos besoins.

Avant l'introduction de LAMBDA, si nous voulions créer une fonction personnalisée dans Excel, nous devions passer par le VBA, ce qui nécessitait des connaissances en programmation et pouvait être intimidant pour beaucoup d'utilisateurs. Avec LAMBDA, n'importe qui maîtrisant les formules Excel de base peut désormais créer ses propres fonctions.

Les avantages de la fonction LAMBDA sont nombreux.

• Tout d'abord, elle nous permet de donner un nom convivial et facile à retenir à nos formules complexes. Par exemple, au lieu de taper une longue formule de filtrage à chaque fois que nous voulons trouver les meilleurs vendeurs, nous pouvons simplement appeler notre fonction personnalisée « TopVendeurs ».
• Ensuite, nous pouvons réutiliser ces fonctions n'importe où dans notre classeur, ce qui nous fait gagner beaucoup de temps et réduit les risques d'erreur. Si nous devons modifier la logique de notre fonction, nous n'avons qu'à la modifier à un seul endroit, et tous les appels à cette fonction seront automatiquement mis à jour.
• Enfin, LAMBDA nous permet de simplifier des calculs complexes ou répétitifs, rendant nos feuilles de calcul plus propres, plus lisibles et plus faciles à maintenir.

   2.2. Structure de base d'une fonction LAMBDA

Maintenant, examinons comment est structurée une fonction LAMBDA dans Excel. La syntaxe de base est la suivante :

=LAMBDA(paramètre1; paramètre2; ...; calcul)

Dans cette structure :

• Les paramètres (paramètre1, paramètre2, etc.) sont les variables d'entrée que nous allons utiliser dans notre fonction, comme les plages de cellules, les valeurs numériques, ou les chaînes de texte.
• Le calcul est la formule qui utilise ces paramètres pour produire un résultat.

Pour illustrer cette structure, prenons un exemple très simple : créer une fonction qui calcule la TVA (20%) sur un montant.

=LAMBDA(montant; montant*1,2)

Dans cet exemple, « montant » est notre paramètre d'entrée, et « montant*0,2 » est notre calcul.

Maintenant, pour enregistrer cette fonction et pouvoir l'utiliser dans notre classeur, nous devons créer un nom défini, car si nous l’utilisons directement dans une cellule Excel nous renverrai une erreur #CALC! :

Pour cela, nous nous rendons dans l'onglet « Formules » du ruban, puis nous cliquons sur « Gestionnaire de noms ».

Nous pouvons également utiliser le raccourci clavier [Ctrl]+[F3] pour ouvrir directement le gestionnaire de noms.

Dans la fenêtre du gestionnaire de noms, nous cliquons sur le bouton « Nouveau » pour créer un nouveau nom, et dans la boîte de dialogue « Nouveau nom » qui s'affiche, nous remplissons les champs suivants :

• Dans le champ « Nom », nous saisissons « montantTTC » (sans espaces, car les noms ne peuvent pas contenir d'espaces).
• Dans le champ « Commentaire », nous pouvons ajouter une description comme « Calcul du montant TTC au taux de 20% ».
• Et enfin, dans le champ « Fait référence à », nous collons notre formule LAMBDA : =LAMBDA(montant; montant*1,2)

Nous cliquons sur « OK » pour valider la création de notre nom défini.

Maintenant, nous pouvons utiliser notre fonction personnalisée n'importe où dans notre classeur en tapant simplement :

=montantTTC(38)

Et Excel nous renvoie le résultat : 45,6.

C'est aussi simple que cela ! Nous venons de créer notre première fonction personnalisée avec LAMBDA. Bien que cet exemple soit très basique, il nous montre le potentiel de cette fonctionnalité.

Dans les sections suivantes, nous allons explorer des exemples plus complexes et plus utiles pour notre analyse de données de vente.

3. Création de fonctions avancées avec LAMBDA

   3.1. Identifier les meilleurs vendeurs

Maintenant que nous avons compris les bases de LAMBDA, passons à un exemple plus concret et utile pour notre analyse de données de vente.

Nous allons créer une fonction qui nous permettra d'identifier les meilleurs vendeurs en fonction du montant total de leurs ventes.

Pour cela, nous allons utiliser les fonctions FILTRE et RANG, combinées avec LAMBDA.

Tout d'abord, rappelons ce que font ces fonctions :

• FILTRE : renvoie un tableau filtré en fonction de critères spécifiés.
• RANG : détermine le rang d'une valeur dans une liste de valeurs.

Voici comment nous allons procéder pour créer notre fonction "TopVendeurs" :

Pour commencer, nous saissons la formule suivante dans une cellule vide :

=LAMBDA(plageVendeurs; plageVentes; nombreTop;FILTRE(plageVendeurs;RANG(plageVentes; plageVentes; 0) <= nombreTop;"Aucun résultat"))

Expliquons cette formule en détail :

• plageVendeurs : la plage de cellules contenant les noms des vendeurs (dans notre exemple, la colonne B).
• plageVentes : la plage de cellules contenant les montants des ventes (dans notre exemple, la colonne G).
• nombreTop : le nombre de meilleurs vendeurs que nous voulons identifier.
• La fonction FILTRE va filtrer la plage de vendeurs en ne conservant que ceux dont le rang des ventes est inférieur ou égal à nombreTop.
• RANG(plageVentes; plageVentes; 0) calcule le rang de chaque vente par rapport à l'ensemble des ventes, en ordre décroissant (le paramètre 0 indique l'ordre décroissant, donc les valeurs les plus élevées ont les rangs les plus bas).

Maintenant, enregistrons cette formule comme un nom défini, comme nous l'avons fait précédemment.

Nous allons dans l'onglet « Formules », puis « Gestionnaire de noms », et nous cliquons sur « Nouveau ».

Nous donnons le nom « TopVendeurs » à notre fonction, et nous collons notre formule LAMBDA dans le champ « Fait référence à ».

Puis, nous pouvons appeler cette fonction sur la feuille de calcul :

=topVendeur(B9:B23;G9:G23;3)

Cette formule nous renverra un tableau avec les noms des trois vendeurs ayant réalisé les montants de vente les plus élevés.

   3.2. Calcul de ventes par période

Passons maintenant à une autre fonction très utile pour notre analyse : le calcul des ventes sur une période spécifique. Cette fonction nous permettra de répondre à des questions comme « Quel a été le montant total des ventes en mars 2023 ? ».

Pour cela, nous allons combiner les fonctions FILTRE, SOMME et les opérateurs de comparaison avec LAMBDA.

Nous créons notre formule LAMBDA :

=LAMBDA(plageDate; plageMontant; dateDebut; dateFin;SOMME(FILTRE(plageMontant;(plageDate >= dateDebut) * (plageDate <= dateFin);0)))

Dans cette formule :

• plageDate : la plage contenant les dates de vente (colonne A dans notre exemple).
• plageMontant : la plage contenant les montants des ventes (colonne G).
• dateDebut : la date de début de la période que nous voulons analyser.
• dateFin : la date de fin de la période.
• La fonction FILTRE sélectionne les montants dont la date correspondante est comprise entre dateDebut et dateFin.
• L'expression (plageDate >= dateDebut) * (plageDate <= dateFin) est un moyen astucieux de combiner deux conditions avec un ET logique. En multipliant les résultats des deux comparaisons (qui donnent 1 pour VRAI et 0 pour FAUX), nous obtenons 1 uniquement si les deux conditions sont vraies.
• La fonction SOMME additionne tous les montants filtrés pour nous donner le total des ventes sur la période.

Nous enregistrons cette formule comme un nom défini appelé « ventesParPeriode » :

=ventesParPeriode(A9:A23;G9:G23;M9;M10)

Cette formule nous donnera le montant total des ventes réalisées entre les deux dates sélectionnées.

   3.3. Fonction "JoursOuvres" : liste des jours ouvrés entre deux dates

Dans cette section, nous allons voir comment LAMBDA peut nous aider à résoudre des problèmes plus complexes, comme la génération d'une liste de jours ouvrés (du lundi au vendredi) entre deux dates. Cette fonction peut être très utile pour planifier des activités professionnelles, des livraisons, ou des rendez-vous.

Pour créer cette fonction, nous allons combiner plusieurs fonctions Excel puissantes : SEQUENCE, FILTRE et JOURSEM.

=LAMBDA(dateDebut; dateFin;FILTRE(SEQUENCE(dateFin-dateDebut+1; 1; dateDebut; 1);JOURSEM(SEQUENCE(dateFin-dateDebut+1; 1; dateDebut; 1); 2) <= 5;""))

Examinons cette formule en détail :

• dateDebut et dateFin sont nos paramètres d'entrée, représentant l'intervalle de dates à considérer.
• SEQUENCE(dateFin-dateDebut+1; 1; dateDebut; 1) génère une séquence de toutes les dates entre dateDebut et dateFin, avec un pas de 1 jour.
• JOURSEM(...; 2) convertit chaque date en son jour de la semaine, avec 1 pour lundi, 2 pour mardi, etc., jusqu'à 7 pour dimanche (le paramètre 2 indique que la semaine commence le lundi).
• Le critère JOURSEM(...) <= 5 filtre uniquement les jours de lundi à vendredi.
• La fonction FILTRE renvoie donc toutes les dates qui correspondent à des jours ouvrés.

Nous enregistrons cette formule comme un nom défini appelé « joursOuvres ».

Et pour obtenir la liste des jours ouvrés de l’année 2025, il nous suffit d’utiliser la formule :

=joursOuvres("01/01/2025";"31/12/2025")