On a déjà vu la fonction LAMBDA (Voir l'article Les bases de la fonction LAMBDA), je vous propose de découvrir des fonctions corollaires de cette fonction.
J'ai tenté de présenter des exemples "simple" qui me semblent intéressants pour illustrer leur fonctionnement et les cas où les employer.
Pour comprendre ces formules, imaginer que l'on "boucle" sur chaque valeur du/des tableau(x) de départ, à la manière d'un For Each (pour ceux qui programme un peu).
Toutes ces fonctions intègrent une fonction LAMBDA.
- Cette fonction LAMBA peut être directement saisie ou définit via le Gestionnaire de noms puis appelée.
- Le nom des arguments de cette fonction LAMBDA sont libres (exemple : c ou col ou colonne) mais ne peuvent commencer par un chiffre ni correspondre à une cellule (xfe1 est accepté, pas xfd1).
La fonction REDUCE
Rôle
Renvoie une valeur représentant le cumule des valeurs renvoyées par une fonction LAMBDA appliquée à chaque valeur de la matrice de départ.
Permet de rendre matricielle des fonctions qui ne le sont pas (elles ne sont pas capables de renvoyer une matrice de résultats).
Syntaxe
=REDUCE([Initial_Value] ; Tableau ; LAMBDA(Paramètre1 ; Paramètre2 ; Opération_sur_Paramètre2))- Initial_Value : Valeur de départ de Paramètre1 (Cumul), 0 par défaut.
- Tableau : Matrice de départ alimentant Paramètre2 (Valeur).
- Lambda : Une fonction LAMBDA avec seulement 2 arguments Paramètre1 (Cumul) et Paramètre2 (Valeur) et une formule (Opération).
- Paramètre1 : Argument servant à mémoriser le cumul et sera renvoyé comme résultat à la fin (Par exemple : Cumul, Total, ...).
- Paramètre2 : Argument servant à mémoriser tour à tour chaque valeur de Tableau (Par exemple : Valeur, Qt, Nb...).
- Opération_sur_Paramètre2 : Formule appliquée à Paramètre2 dont le résultat est enregistré dans Paramètre1.
Syntaxe classique
=REDUCE([Initial_Value] ; Tableau ; LAMBDA(Cumul; Valeur; Cumul + Opération_sur_Valeur))Exemple REDUCE 1 & 2
Cumuler le double des valeurs d'une plage.
Ces formules n'ayant pas un sens particulier mais illustrant l'utilisation des arguments.
Test sur le contenu de Valeur : Cumul du double des valeurs si ces valeurs sont inférieures à 50.
=REDUCE(1000;B4:B6;LAMBDA(Cumul;Valeur;Cumul+SI(Valeur<50;Valeur*2;0)))La même en moins "lourd" en se passant de la fonction SI.
=REDUCE(1000;B4:B6;LAMBDA(Cumul;Valeur;Cumul+(Valeur<50)*Valeur*2))Test sur le contenu de Cumul : Cumul du double des valeurs tant que le cumul est inférieur ou égale à 1201.
=REDUCE(1000;B4:B6;LAMBDA(Cumul;Valeur;Cumul+SI(Cumul<=1201;Valeur*2;0)))Passer le seuil de 1201 à 1202 et on a comme résultat 1242 (ajout de la dernière valeur 20*2).
Exemple REDUCE 3 : Compter les valeurs paires
La formule = SOMME( EST.PAIR( B2:B6 ) *1 ) renvoie une erreur car la fonction EST.PAIR ne fonctionne pas sur une matrice/plage.
(*1 pour convertir les VRAI en 1 et les FAUX en 0).
On peut contourner cela avec notre fonction REDUCE.
=REDUCE(0;B2:B6;LAMBDA(Cumul;Valeur;Cumul + EST.PAIR(Valeur)))
Remarques :
- On pourrait aussi remplacer la fonction EST.PAIR par la fonction MOD (reste d'une division) et tester s'il n'y a pas de reste (=0) en cas de division par 2
=SOMME((MOD(B2:B6;2)=0)*1). - LAMBDA écrase automatiquement l'ancien cumul par le résultat de l'opération d'où le Cumul + Opération afin de préserver l'ancienne valeur (cf plus bas l'exemple de la fonction SCAN).
- Si Tableau correspond à une plage, le paramètre2 (Valeur) contient la référence de la cellule.
- On a l'habitude d'utiliser la fonction LAMBDA pour créer une fonction via le Gestionnaire de noms, c'est tout à fait possible ici.
On définit une fonction LAMDA (ex : L_NB_EST.PAIR) et on utilise ce nom dans REDUCE pour l'argument Lambda.
L_NB_EST.PAIR > =LAMBDA(Cumul;Valeur;Cumul+EST.PAIR(Valeur))
=REDUCE(0;B2:B6;L_NB_EST.PAIR)La fonction MAP
Rôle
Renvoie une matrice formée par l'application d'une LAMBDA à chaque valeur de la/des matrices de départ.
Permet de garder une matrice résultante avec des fonctions qui "aplatiraient" la matrice en générant une synthèse (SOMME, MOYENNE, MAX...).
Syntaxe
=MAP (Tableau1 [; Tableau2] [; ...Tableau253] ; LAMBDA(paramètres1 [; paramètres2] [;... paramètres253] ; Opération))- Tableau1 à 253 : Tableaux de même taille qui seront passés en tant que paramètres pour la fonction LAMBDA.
- Lambda : Une fonction LAMBDA avec autant de de paramètres que d'arguments Tableau et une formule (Opération).
- Paramètres 1 à 253 : Correspondent au arguments Tableau.
- Opération : Formule de calcul utilisant les valeurs de chaque tableau de même rang (la 1ere valeur du tableau résultat de MAP sera calculée à partir de la 1ere valeur de chaque tableau).
Exemple MAP
Comparer 2 tableaux ligne/ligne et ramener la plus grande valeur.
=MAP(B5:B7;D5:D7;LAMBDA(Quat1;Quat2;MAX(Quat1;Quat2)))Attention : Qt1 ou Qut1 ne sont pas valable comme nom d'argument car ils correspondent à des cellules.
Ici la fonction en F2 =MAX(B5:B7;D5:D7) a aplatie la matrice en ne renvoyant qu'un résultat alors que MAP renvoie 3 valeurs.
La fonction SCAN
Rôle
Renvoie une matrice représentant les cumules progressifs des valeurs renvoyées par une fonction LAMBDA appliquée à chaque valeur de la matrice de départ.
Même logique que la fonction REDUCE mais en renvoyant la matrice de tous les cumuls successifs.
Syntaxe
=SCAN([Initial_Value] ; Tableau ; LAMBDA(Paramètre1 ; Paramètre2 ; Opération_sur_Paramètre2))- Initial_Value : Valeur de départ de Paramètre1 (Cumul), 0 par défaut.
- Tableau : Matrice de départ alimentant Paramètre2 (Valeur).
- Lambda : Une fonction LAMBDA avec seulement 2 arguments Paramètre1 (Cumul) et Paramètre2 (Valeur) et une formule (Opération).
- Paramètre1 : Argument servant à mémoriser le cumul et sera renvoyé comme résultat à la fin (Par exemple : Cumul, Total, ...).
- Paramètre2 : Argument servant à mémoriser tour à tour chaque valeur de Tableau (Par exemple : Valeur, Qt, Nb...).
- Opération_sur_Paramètre2 : Formule appliquée à Paramètre2 dont le résultat est enregistré dans Paramètre1.
Syntaxe classique
=SCAN([Initial_Value] ; Tableau ; LAMBDA(Cumul; Valeur ; Cumul + Opération_sur_Valeur))Exemple SCAN : Faire un cumul mois par mois réinitialisé à chaque trimestre
=SCAN(0;B2:B17;LAMBDA(C;V;SI(MOD(MOIS(DECALER(V;;-1))-1;3)=0;V;C + V)))Remarques :
- DECALER( V ; ; -1 ) > V (valeur) ne contient pas seulement la valeur mais la référence à le cellule donc on peut la décaler pour récupérer la mois en cours.
(On retire 1 pour numéroter les mois de 0 à 11 puis on teste si ce n° de mois est multiple de 3). - Si le mois est multiple de 3 (début du trimestre) , on remplace le cumul par la valeur du mois > La valeur de LAMBDA écrase automatiquement le contenu de C (Cumul) donc on utilise C+V pour préserver l'ancien C et V pour l'écraser.
Formule facilement adaptable pour un calcule du type YTD (year to date).
= SCAN(0;B2:B17;LAMBDA(C;V;SI(MOIS(DECALER(V;;-1))=1;V;C+V)))La fonction MAKEARRAY
Rôle
Renvoyer une matrice de la taille désirée dont le contenu est généré par une fonction LAMBDA.
Syntaxe
=MAKEARRAY(Lignes, Colonnes, lambda(Ligne, Colonne; Opération))- Lignes : Nombre de ligne de la matrice résultat (entier >0).
- Colonnes : Nombre de colonne de la matrice résultat (entier >0).
- Lambda : Une fonction LAMBDA avec 2 paramètres (Ligne et Colonne) et une formule (Opération).
- Ligne : Paramètre qui aura successivement pour valeur les valeurs de 1 à Lignes (Index de la ligne en cours).
- Colonne : Paramètre qui aura successivement pour valeur les valeurs de 1 à Colonnes (Index de la colonne en cours).
- Opération : Formule qui générera les valeurs de la matrice résultat à partir des valeurs Ligne et Colonne.
Exemple 1 à 4
Récupérer une matrice (de valeurs numériques, textuelles, fixes ou incrémentées) d'une taille précise à appliquer dans une autre fonction.
=MAKEARRAY(3;2;LAMBDA(r;c;1))
=MAKEARRAY(3;2;LAMBDA(r;c;"A"))
=MAKEARRAY(3;2;LAMBDA(r;c;(r-1)*2+c))
=MAKEARRAY(3;2;LAMBDA(r;c;r+(c-1)*3))
Remarques :
- Dans (r-1)*2+c), le *2 car il y a 2 colonnes.
- Dans r+(c-1)*3), le *3 car il y a 3 lignes.
Exemple 5
Décomposer une chaîne de texte en plaçant les caractères qui la compose en 2 colonnes.
=MAKEARRAY(ENT(NBCAR(B2)/2)+1;2;LAMBDA(r;c;STXT(B2;(r-1)*2+c;1)))
Remarques :
- ENT(NBCAR(B2)/2)+1 permet d'avoir le nombre de lignes en faisant un arrondi supérieur (la fonction ARRONDI.SUP aurait pu être utilisé).
- Autre solution =STXT(B2;SEQUENCE(ENT(NBCAR(B2)/2)+1;2);1).
Exemple 6
Pivoter un tableau de valeurs en pouce et ajouter 2 colonnes de conversion en Cm et Pt (Points typographique).
=MAKEARRAY(NB(C2:H2);3;LAMBDA(Lig;col;INDEX(C2:H2;;Lig)*CHOISIR(col;1;2,54;72)))
Les fonctions BYCOL et BYROW
Rôle
Renvoie une matrice à une dimension contenant le résultat d'une opération sur les colonnes/lignes d'une matrice réalisée par une fonction Lambda.
La matrice aura autant de valeurs que la matrice de départ aura de colonne/ligne.
Syntaxe
=BYCOL(Tableau, LAMBDA(Colonne ; Opération))
=BYROW(Tableau, LAMBDA(Ligne ; Opération))- Tableau : Plage dont on veut une synthèse par colonne ou par ligne.
- Lambda : Une fonction LAMBDA avec 1 paramètres (Colonne / Ligne) et une formule (Opération).
- Colonnes / Lignes : Argument servant à mémoriser les plages de valeurs en colonne ou en ligne.
- Opération : Formule qui générera les valeurs de la matrice résultat à partir des valeurs Ligne et Colonne.
Exemple 7
Totaliser les colonnes et les lignes d'un tableau.
=BYCOL(C4:E6;LAMBDA(col;SOMME(col)))
=BYROW(C4:E6;LAMBDA(lig;SOMME(lig)))
Nouveautés 11/2023
Des "raccourcis" spécifiques aux fonctions LAMBDA appelés "eta reduced lambda" permette de générer rapidement les fonctions LAMBDA les plus standards.
Pour l'instant 16 raccourcis sont disponibles : SOMME, POUCENTAGE.DE, MOYENNE, MEDIANE, NB, NBVAL, MAX, MIN, PRODUIT, TABLEAU.EN TEXTE, CONCAT, ECARTYPE.STANDARD, ECARTYPE.PEARSON, VAR.S, VAR.P.N, MODE.SIMPLE.
Exemples
=BYROW(C4:E6;LAMBDA(lig;SOMME(lig)))
devient
=BYROW(C4:E6;SOMME)=MAP(B5:B7;D5:D7;LAMBDA(Quat1;Quat2;MAX(Quat1;Quat2)))
devient
=MAP(B5:B7;D5:D7;MAX)Merci pour votre attention bienveillante.
Un commentaire