IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Excel Discussion :

EXCEL : LAMBDA, la fonction qui permet de créer des fonctions


Sujet :

Excel

  1. #1
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    18 957
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 55
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 18 957
    Points : 55 298
    Points
    55 298
    Billets dans le blog
    128
    Par défaut EXCEL : LAMBDA, la fonction qui permet de créer des fonctions
    Salut.


    Exclusivité Excel 365


    La fonction LAMBDA, on en parle depuis longtemps.

    Elle est apparue dans les versions Insider d'Office, mais elle est maintenant disponible, en tout cas sur ma version (Microsoft 365 apps for Entreprise) =>

    Cette fonction permet de mémoriser une formule en lui donnant un alias, et de passer des arguments à cette formule et donc, de la transformer en fonction. On avait déjà LET qui permet d'utiliser des variables au sein d'une formule, que Lambda vient compléter pour donner encore plus de puissance à vos formules Excel. Sa syntaxe est simple:
    Code excel : Sélectionner tout - Visualiser dans une fenêtre à part
    =LAMBDA(Par1;Par2;...;ParN;Formule utilisant les arguments)

    On pouvait déjà, par le passé:
    • créer des fonctions personnalisées en VBA, mais il faut savoir programmer, d'une part, et d'autre part cela transforme le fichier en xlam, amenant un risque de sécurité;
    • créer des formules nommées, mais cela imposait une conception de classeur puisque les formules nommées sont liées à des cellules.


    Dans un prochain billet, j'illustrerai ces deux méthodes.


    La fonction LAMBDA permet d'oublier le VBA, si la formule est exprimable en Excel, et découple la formule de la cellule dans laquelle elle est utilisée.


    Elle permet de créer une fonction dans Excel que l'on utilise comme une fonction native. Prenons comme exemple le calcul du volume d'un cylindre dont on connaît le diamètre et la hauteur. Ok, on peut le faire par formule: = (A2/2)^2*PI()*B2, avec A2 pour le diamètre et B2 pour la hauteur. Et comme on peut le faire par formule, on peut utiliser LAMBDA. Il pourra alors être intéressant d'avoir une fonction toute faite: =VolumeCylindre(A2,B2).


    Nom : 2022-02-26_194252.png
Affichages : 350143
Taille : 2,7 Ko

    Sympa, non? Et bien, LAMBDA permet de réaliser cela, et facilement, en plus.

    On crée une fonction en trois temps:
    1. On écrit la formule;
    2. On teste La fonction LAMBDA sur la feuille;
    3. On enregistre cette fonction LAMBDA dans le gestionnaire de noms et on lui donne le nom souhaité.



    1ère étape

    Nom : 2022-02-26_194940.png
Affichages : 1032
Taille : 3,8 Ko


    2ième étape

    On inclut la formule dans la fonction LAMBDA en:
    1. spécifiant d'abord les arguments (Diamètre et hauteur);
    2. remplaçant les références de cellule par les arguments, et l'on note que l'infobulle d'aide à la saisie propose les arguments définis en début de fonction;
    3. passant les arguments, entre parenthèses, après la parenthèse de la fonction Lambda (comme on le ferait pour n'importe quelle fonction native d'Excel.


    Nom : 2022-02-26_200748.png
Affichages : 1040
Taille : 47,3 Ko


    3ième étape

    1. On copie la formule sans les arguments utilisés pour la tester;
    2. On crée une fonction nommée dans le gestionnaire de noms;
    3. En référence, on colle la formule utilisant LAMBDA.


    Nom : 2022-02-26_201618.png
Affichages : 1035
Taille : 138,4 Ko

    A cette étape, on peut également ajouter une légende qui apparaitra en infobulle lors de la sélection de la fonction. A noter que, actuellement, cette description n'est pas reprise dans l'assistant Fonctions.

    Nom : 2022-02-27_150745.png
Affichages : 1009
Taille : 115,8 Ko



    Utilisation

    On utilise la fonction comme une fonction native d'Excel.

    Nom : 2022-02-26_202113.png
Affichages : 1023
Taille : 24,2 Ko


    La fonction est utilisable partout dans le classeur. Vous devrez la recréer dans chaque classeur dans lequel vous souhaitez l'utiliser. Un OneNote vous permettra de sauvergarder le code de la fonction dans votre bibliothèque de codes.

    Bien sûr, j'ai détaillé les étapes ici pour la prise en main de cette nouvelle fonction, mais vous pouvez évidemment saisir directement la fonction Lambda sans passer par l'étape 1, et rédiger la formule à l'intérieur de la fonction Lambda... Il est tout à fait possible de saisir la formule directement dans le gestionnaire de nom en l'encapsulant dans la fonction LAMBDA ou, pour la tester, la saisir dans une cellule, là aussi encapsulée dans LAMBDA. Toutefois, pour les formules un peu complexes, l'étape 1 peut être intéressante.



    Et pour vous, cette fonction est disponible?

    N'hésitez pas à la tester et à commenter ici vos impressions sur l'utilité de cette fonction.
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  2. #2
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    18 957
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 55
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 18 957
    Points : 55 298
    Points
    55 298
    Billets dans le blog
    128
    Par défaut Exemples d'utilisation
    Au delà de de la théorie sur la fonction, LAMBDA permet de simplifier la vie de l'utilisateur Excel, en mémorisant des formules dans des fonctions "clé sur porte". Quelques exemples parmi d'autres.


    Calcul du trimestre

    Il est parfois intéressant de retrouver le trimestre auquel appartient une date. Excel propose d'ailleurs cela dans les tableaux croisés dynamiques, ou la date est automatiquement éclatée en Année/Trimestre/Mois/Jour. Mais Excel ne propose pas (encore) de fonction TRIMESTRE. LAMBDA permet de pallier ce manque.

    Calculer le trimestre d'une date s'effectue avec la formule =ENT((MOIS(MaDate)-1)/3)+1. Il serait dès lors intéressant d'avoir la fonction TRIMESTRE, qui permettrait de faire abstraction de la formule:

    Nom : 2022-02-27_074343.png
Affichages : 253
Taille : 73,9 Ko



    "Faire" du RECHERCHEX sans le savoir (1)

    Pour simplifier la recherche de valeurs dans une liste, on peut camoufler le RECHERCHEX derrière une fonction LAMBDA. Avec le tableau suivant nommé t_Contacts, on peut proposer à l'utilisateur une fonction pour retrouver rapidement le prénom d'un contact sur base de son matricule:

    Nom : 2022-02-27_075322.png
Affichages : 250
Taille : 79,7 Ko




    "Faire" du RECHERCHEX sans le savoir (2)

    En utilisant INDIRECT, on peut généraliser la fonction pour retrouver la valeur de n'importe quelle colonne avec une fonction qui serait par exemple =CONTACT_CHAMP(Matricule; Champ).

    Nom : 2022-02-27_080149.png
Affichages : 250
Taille : 86,4 Ko




    A vous, maintenant, de trouver d'autres utilisation de cette fonction qui permet aux utilisateurs de vos classeurs de faire abstraction des fonctions natives pour utiliser les fonctions "orientées classeur" que vous mettrez à leur disposition.
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  3. #3
    Expert éminent sénior

    Profil pro
    Conseil, Formation, Développement - Indépendant
    Inscrit en
    février 2010
    Messages
    7 807
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations professionnelles :
    Activité : Conseil, Formation, Développement - Indépendant

    Informations forums :
    Inscription : février 2010
    Messages : 7 807
    Points : 15 008
    Points
    15 008
    Par défaut
    Bonjour

    Dispo aussi sur 365 famille

    Merci pour ces exemples
    Chris
    PowerQuery existe depuis plus de 10 ans, est totalement intégré à Excel 2016 &+. Utilisez-le !

    Quand un homme a faim, mieux vaut lui apprendre à pêcher que de lui donner un poisson.
    Confucius

    ----------------------------------------------------------------------------------------------
    En cas de résolution, n'hésitez pas cliquer sur c'est toujours apprécié...

  4. #4
    Membre éprouvé
    Homme Profil pro
    ingénieur
    Inscrit en
    mars 2015
    Messages
    429
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : ingénieur
    Secteur : Finance

    Informations forums :
    Inscription : mars 2015
    Messages : 429
    Points : 1 188
    Points
    1 188
    Par défaut
    Merci Pierre
    En parallèle à LAMBDA, il y a également une nouvelle série de fonctions "MAKEARRAY" sur 365 Famille (je n'ai pas la dernière version au bureau)
    - MAKEARRAY
    - SCAN
    - MAP
    - BYCOL
    - BYROW
    - REDUCE
    Ces fonctions ne sont pas traduites en français, les noms d'origine ont été conservés.

    Cela semble intéressant, à voir à l'usage. J'ai déjà beaucoup de collègues perdus avec les fonctions matricielles, l'utilisation des références A1#, ... J'ai peur que les utilisateurs "lambdas" soient un peu dépassés par ces formules.
    Les formations sont importantes mais pour que chacun s'approprie les nouveautés il faut pratiquer et là bien souvent on repart sur les bases connues et non sur ces nouveaux concepts.

    Stéphane

  5. #5
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    18 957
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 55
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 18 957
    Points : 55 298
    Points
    55 298
    Billets dans le blog
    128
    Par défaut
    @Stéphane


    Je suis en train d'écrire un tuto sur LAMBDA, LET et les fonctions liées à LAMBDA que tu cites ici. Je nourrirai le tuto avec des exemples que j'espère pertinents


    @Chris

    Merci sur l'info relative à la disponibilité. Cela signifie que Lambda et ses dérivées sont disponibles sur les versions 365 normales (hors Insider)
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  6. #6
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    18 957
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 55
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 18 957
    Points : 55 298
    Points
    55 298
    Billets dans le blog
    128
    Par défaut
    Suite à cette discussion, petite création de fonction perso avec LAMBDA et LET pour permettre une saisie rapide d'un "temps". L'idée est de saisir une valeur horaire sans les séparateurs et la virgule pour les parties de secondes:
    1243564 => 12:43:56,4

    On crée une lambda que l'on enregistre dans le gestionnaire de noms et on lui donne un nom valide qui sera celui de la fonction utilisable en Excel. Dans l'exemple, j'ai nommé la lambda ConversionSaisie. La fonction ici illustre que LET peut utiliser les arguments de LAMBDA et que les variables de LET peuvent utiliser les variables qui ont été déclarées avant elles (H utilise T qui utilise V...)

    Code Excel : Sélectionner tout - Visualiser dans une fenêtre à part
    =LAMBDA(v;LET(t;TEXTE(v;"0000000");h;GAUCHE(t;2);m;STXT(t;3;2);s;STXT(t;5;2);n;DROITE(t;1);SI(ET(m>="00";m<="59";s>="00";s<="59");h/24+m/1440+s/86400+n/864000;#VALEUR!)))

    Nom : 2022-03-17_144500.png
Affichages : 182
Taille : 6,8 Ko


    Il y a moyen de vraiment bien s'amuser
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  7. #7
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    18 957
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 55
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 18 957
    Points : 55 298
    Points
    55 298
    Billets dans le blog
    128
    Par défaut REDUCE et LAMBDA
    On continue avec les fonctions REDUCE et LAMBDA.

    J'ai montré une utilisation de LAMBDA qui consiste à enregistrer une formule Excel en l'incluant dans LAMBDA => =LAMBDA([Arg1], [Arg2],..., [Arg127],Formule) dans le gestionnaire de noms puis en l'utilisant comme une fonction native à laquelle on passe les arguments requis.

    Mais comme l'a précisé Stéphane, on peut utiliser LAMBDA en combinaison avec d'autres fonctions, et notamment REDUCE qui fonctionne en boucle pour ramener le résultat final dans une cellule.

    Explications


    Soit à devoir supprimer les chiffres contenus dans une cellule pour ne garder que les lettres.

    Nom : 2022-03-18_205235.png
Affichages : 173
Taille : 131,6 Ko

    En découpant le process, on peut comprendre que l'on va devoir remplacer 1 par "" dans la chaine initiale pour créer la chaine 1, puis 2 par "" dans la chaine 1 pour créer la chaine 2, ..., puis 0 par "" dans la chaine 9 pour créer la chaine finale.

    Nom : 2022-03-18_205703.png
Affichages : 164
Taille : 109,7 Ko


    REDUCE, associée avec LAMBDA, va permettre cela, en partant d'une valeur initiale et en bouclant sur toutes les valeurs d'un tableau pour appliquer le LAMBDA.

    Syntaxe: REDUCE(ValeurInitiale; Matrice; Lambda(Arg1, Arg2, Formule)) dans laquelle Arg1 = ValeurInitiale et Arg2 = Matrice.

    Important: Il faut comprendre ici que Matrice peut être un tableau structuré (souvent une colonne de ce tableau), mais aussi une matrice constante.

    On remarque avec cette syntaxe que le Lambda reçoit ValeurInitiale et Tableau comme arguments 1 et 2. REDUCE va partir de la valeur initiale puis boucler autant de fois qu'il y a de valeurs dans le tableau pour appliquer le LAMBDA à chaque boucle et repartir du résultat intermédiaire obtenu pour la boucle suivante. Le résultat renvoyé par REDUCE est donc la valeur transformée après toutes les boucles.

    C'est donc bien le processus illustré ci-dessus, qui boucle 10 fois pour remplacer à chaque passage la valeur x de la matrice par du vide.

    Nom : 2022-03-18_211750.png
Affichages : 163
Taille : 105,9 Ko


    Autre exemple

    Dans un texte contenant des caractères "HTML", remplacer les caractères HTML par leur équivalent ASCII...

    Nom : 2022-03-18_212100.png
Affichages : 163
Taille : 110,0 Ko

    Le principe est identique, à savoir parcourir un texte en bouclant sur les caractères à remplacer, sauf que dans ce cas-ci, les caractères ne sont pas remplacés par "" mais par un caractère différent à chaque fois. Autrement dit, on va passer une table de correspondance qui permettra d'effectuer les remplacements par boucle. Le problème est que l'on ne peut passer qu'une matrice alors que l'on a besoin de deux, les caractères HTML et les caractères de remplacement. Ici, C1 contient un espace.

    Nom : 2022-03-18_224044.png
Affichages : 164
Taille : 107,3 Ko

    Ce problème n'existe pas si l'on accepte l'idée que l'on va rechercher les valeurs d'une matrice dans une colonne d'un tableau structuré pour récupérer les valeurs correspondantes dans une autre colonne du même valeur structuré.

    Nom : 2022-03-18_224523.png
Affichages : 163
Taille : 124,7 Ko

    On voit donc ici qu'au départ de ValeurInitiale (A1), on va rechercher chaque caractère d'une matrice (la colonne HTML du tableau) dans la colonne HTML du tableau pour le remplacer par le caractère de la colonne Ascii du tableau. On retrouve donc ici les deux variables nécessaires à REDUCE... Du coup, on peut boucler en une seule formule.

    Nom : 2022-03-18_224954.png
Affichages : 167
Taille : 92,6 Ko
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  8. #8
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    18 957
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 55
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 18 957
    Points : 55 298
    Points
    55 298
    Billets dans le blog
    128
    Par défaut MAP et LAMBDA
    Après Lambda pour créer des fonctions personnalisées, nous avons vu que nous pouvions créer des formules "qui bouclent", un peu comme des matricielles, avec REDUCE et LAMBDA.

    Ici, nous allons approcher MAP et LAMBDA pour permettre de récupérer des données sous conditions dans des plages dynamiques, voire de les sommer pour récupérer le total.


    SOMME.SI.ENS permet de sommer des valeurs en fonction de plusieurs critères dont le nombre varie de 1 à 127. Mais ces critères doivent tous être validés pour que la ligne du tableau soit prise en compte: Critère1 ET Critère2 ET Critère3... Il n'est pas possible avec SOMME.SI.ENS de tester Critère1 OU Critère2, ni Critère1 OU ((Critère2 ET Critère3) ou (Critère3 ET Critère4))... Jusque maintenant, on devait se tourner vers SOMMEPROD ou une formule matricielle, validée avec MAJ+CTRL+ENTER. La version 365 apporte une solution qui, avant l'apparition de MAP et LAMBDA, permettait de simplifier un peu la formule, mais qui obligeait de passer par la case "arithmétique booléenne".


    Le problème est qu'il faut savoir jongler avec l'arithmétique booléenne qui nous permet de transformer VRAI et FAUX en 1 et 0, permettant d'établir des tables de vérité complexes, mais délicates à écrire en Excel. En effet, ET et OU ne peuvent pas être utilisées dans une matricielle puisque leur utilité est de renvoyer UNE valeur booléenne, VRAI ou FAUX, pour une matrice qu'elle reçoivent en argument. Malgré la matricielle, ET et OU renvoient donc UNE valeur et non pas une matrice de valeurs.



    MAP et LAMBDA permettent de "retomber sur nos pattes" en proposant aux formulistes une approche plus conforme à leur utilisation habituelle de ET et OU.

    Syntaxe: MAP(Matrice1; [Matrice2]; [Matrice3; ...; [Matrice127];LAMBDA(Arg1; [Arg2]; [Arg3]; ...; [Arg127];Formule)) dans laquelle il y a correspondance entre les MatriceN et les ArgN: Arg1 = Matrice1, Matrice2 = Arg2, ... On veillera donc à avoir autant de Arg dans le LAMBDA que de Matrice dans le MAP.


    Exemple:

    Soit à sommer les montants lorsque l'employé est Alain Térieur OU que le département est celui des achats...

    Nom : 2022-03-19_180808.png
Affichages : 161
Taille : 119,6 Ko



    On pourra bien sûr toujours s'en sortir avec des colonnes supplémentaires, mais je n'aime pas, car les tableaux sont alors assez vite ingérables. On peut y aller avec beaucoup de colonnes ou peu de colonnes. Examinons plus en détails la deuxième image qui rassemble différentes colonnes intermédiaires, car c'est sur le modèle de cette formule conditionnelle que nous travaillerons avec MAP et LAMBDA

    Nom : 2022-03-19_181239.png
Affichages : 155
Taille : 322,3 Ko



    Dans le cas présent, on pourrait s'en sortir avec une série de 3 SOMME.SI.ENS, car les conditions s'y prêtent, mais d'autres cas de figure rendront cette solution impossible à mettre en place

    Nom : 2022-03-19_181548.png
Affichages : 162
Taille : 213,2 Ko



    On pourrait utiliser SOMMEPROD ou une SOMME matricielle, mais au prix d'une arithmétique booléenne qui paraît parfois ésotérique au formuliste puisque ET et OU sont exclus des matricielles.

    Nom : 2022-03-19_181721.png
Affichages : 158
Taille : 182,5 Ko



    Avec Excel 365, on peut également utiliser FILTRE qui filtre un tableau ou une colonne d'un tableau sur bases de conditions. Là aussi, savoir manipuler l'algèbre booléenne est indispensable dès lors que l'on sort d'une simple condition a ET b.

    Nom : 2022-03-19_181904.png
Affichages : 154
Taille : 374,5 Ko




    C'est ici que MAP et LAMBDA vont nous aider.

    L'idée consiste à passer chaque colonne du tableau nécessaire au calcul à MAP, qui va utiliser un LAMBDA en boucle sur chaque ligne des tableaux, en considérant qu'à chaque boucle, on travaille sur une ligne particulière. On peut alors sans problèmes utiliser ET et OU comme dans une formule Excel "normale" pour créer un vecteur de résultat sous forme d'une plage dynamique. Une simple SOMME sur cette plage permettra de récupérer le montant des lignes qui correspondent aux conditions exprimées.

    MAP va recevoir les colonnes du tableau nécessaire à l'évaluation des conditions ainsi que la colonne reprenant les montants. LAMBDA va recevoir ces colonnes en arguments et il suffira d'exprimer la formule en utilisant les arguments "comme si on la rédigeait pour une ligne de données"... Du coup, on peut utiliser les fonctions ET et OU comme dans la seconde illustration de la solution avec colonnes supplémentaires.

    Nom : 2022-03-19_182201.png
Affichages : 159
Taille : 91,5 Ko

    MAP construit donc une plage dynamique, et on peut alors l'englober dans une simple fonction SOMME.

    Nom : 2022-03-19_182309.png
Affichages : 162
Taille : 141,4 Ko


    LAMBDA, associée à MAP, permet donc l'expression des conditions avec ET, OU, OUX et évite de devoir jongler avec l'algèbre booléenne.
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  9. #9
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    18 957
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 55
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 18 957
    Points : 55 298
    Points
    55 298
    Billets dans le blog
    128
    Par défaut SCAN et LAMBDA
    Avec REDUCE et LAMBDA, nous avons vu que nous pouvions créer une formule qui boucle et qui se sert à chaque boucle du résultat intermédiaire résultant des boucles précédentes. Pour rappel, sa structure est =REDUCE(ValeurInitiale; Tableau; LAMBDA(Arg1; Arg2; Formule)) où Arg1 et Arg2 correspondent respectivement à ValeurInitiale et Tableau.

    SCAN possède la même syntaxe que REDUCE ( SCAN(ValeurInitiale; Tableau; LAMBDA(Arg1; Arg2; Formule)), mais renvoie le tableau des résultats intermédiaires alors que REDUCE renvoie la dernière valeur de ce tableau. Donc, si vous avez compris REDUCE, vous allez comprendre SCAN...

    Reprenons l'exemple vu avec REDUCE pour supprimer les chiffres présents dans une chaîne de caractères:


    Nom : 2022-03-18_205703.png
Affichages : 140
Taille : 109,7 Ko

    On voit bien qu'à chaque "passe", on s'est servi du résultat de la passe précédente: A2 supprime 1 de A1, A3 supprime 2 de A2,.... En 10 passes, on a supprimé les 10 caractères numériques et REDUCE affiche l'unique résultat final.

    Scan va procéder comme REDUCE en créant la plage dynamique des résultats intermédiaires. (Astuce: Ici, pour ne pas saisir la matrice constante, j'ai utilisé Ligne(A1:A10...))


    Nom : 2022-03-25_134335.png
Affichages : 139
Taille : 150,5 Ko


    Rien de bien compliqué, donc.


    Cas pratique


    Soit à trouver le plus grand nombre de cellules vides consécutives d'une plage... On peut bien sûr passer par une colonne intermédiaire qui "remet le compteur à zéro" dès qu'une cellule n'est pas vide, et qui incrémente le résultat précédent si la cellule est vide... Il suffit alors de prendre le max de la plage des résultats et on obtient notre donnée. Dans l'exemple ci-dessous, la plage A7:A11 est la plus grande plage vide de A2:A14 et contient 5 cellules vides. On comprend, avec la formule illustrée =SI(ESTVIDE(A2);D1+1;0) que l'on incrémente le résultat précédent sauf si la cellule n'est pas vide, auquel cas ce résultat partiel devient 0, et on recommence à compter. C'est bien ainsi que l'on arrive à 5...

    Nom : 2022-03-25_135020.png
Affichages : 141
Taille : 78,1 Ko


    Mais... Attendez... Compteur, incrément? Mais alors, SCAN et LAMBDA, bien sûr!!

    A chaque boucle de LAMBDA, la valeur de Arg1 de LAMBDA est modifiée par la boucle et sert de nouvelle valeur initiale pour la boucle suivante. SCAN crée donc la plage dynamique des résultats intermédiaires, et il suffit d'en calculer le maximum avec MAX...

    Nom : 2022-03-25_135957.png
Affichages : 142
Taille : 98,6 Ko



    Voici donc un nouvel exemple d'utilisation de ces fonctions attachées à LAMBDA.

    Bon travail et bon weekend
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

Discussions similaires

  1. Réponses: 4
    Dernier message: 12/04/2022, 00h33
  2. Réponses: 1
    Dernier message: 30/06/2018, 12h31
  3. [XL-2013] Créer une fonction qui permet de compter des données complexe.
    Par RabiK33 dans le forum Macros et VBA Excel
    Réponses: 2
    Dernier message: 06/04/2016, 11h58
  4. Créer un wizard qui permet de créer une visual class
    Par nicolas33400 dans le forum Eclipse Java
    Réponses: 3
    Dernier message: 08/08/2010, 16h53
  5. Appli qui permet de créer un exécutable
    Par chaours dans le forum Delphi
    Réponses: 4
    Dernier message: 03/11/2006, 15h35

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo