Je commence tout doucement à tomber amoureux de cette fonction ! J'en avais déjà parlé ici et là. Je continue en allant plus loin dans la démarche.
Mise en garde : cette fonction est très intéressante pour de petites tables, mais sur de gros volumes de données avec beaucoup de répétition de ASSEMB.V, il faut bien tester. La fonction MAKEARRAY peut être une bonne alternative. Pour une discussion intéressante, mais assez technique sur ce sujet, vous pouvez aller ici (en Anglais). Dans mes cas pratiques, je les ai utilisées pour construire des graphiques en escalier avec un axe chronologique.
Voici un exemple d'utilisation pour répéter une information toutes les lignes. Cet exemple va nous permettre de comprendre comment fonctionne REDUCE.
De 1,2,3 à 1,1,2,2,3,3
Par exemple : comment passer de 1, 2, 3 à 1, 1, 2, 2, 3, 3. Cela peut être utile pour la construction de graphiques en escalier, pour répéter des périodes ou encore pour la construction d'une fonction de dépivotage.
Le cœur de la formule est assez simple (en gras, les paramètres à changer).
=REDUCE("";{1;2;3};LAMBDA(_accu;_val;ASSEMB.V(_accu;_val;_val)))REDUCE part d'une valeur initiale (le premier paramètre, ici ""), puis parcourt toutes les valeurs du tableau fourni en deuxième paramètre (ici {1;2;3}). La LAMBDA permet ensuite d'effectuer une opération qui est "réduite" dans une variable. La beauté de REDUCE est que cette fonction peut retourner des tableaux, donc, avec ASSEMB.V on peut capturer les étapes les unes après les autres.
Dans l'exemple, on va donc passer à travers tous les éléments de {1;2;3}, et pour chaque élément, on le duplique avec ASSEMB.V(_accu,_val;_val). La répétition de _val dans ASSEMB.V est ce qui génère la duplication. ASSEMB.V permet d'assembler verticalement les valeurs les unes en dessous des autres.
REDUCE a une caractéristique importante, c'est qu'elle demande de fournir quelque chose au départ. Dans le cas d'un assemblage vertical, on peut donc EXCLURE le premier élément, ou bien le remplacer par un titre en fonction des besoins.
=EXCLURE(REDUCE("";{1;2;3};LAMBDA(_accu;_val;ASSEMB.V(_accu;_val;_val)));1)=REDUCE("Période";{1;2;3};LAMBDA(_accu;_val;ASSEMB.V(_accu;_val;_val)))Il reste alors à paramétrer le {1;2;3}, par exemple, en utilisant une référence :
=EXCLURE(REDUCE("";A2:A4;LAMBDA(_accu;_val;ASSEMB.V(_accu;_val;_val)));1)Parfois, il est utile de ne pas prendre la première valeur de la série (par exemple pour un graphique en escalier)
On peut donc exclure 2 lignes au lieu d'une :
=EXCLURE(REDUCE("";{1;2;3};LAMBDA(_accu;_val;ASSEMB.V(_accu;_val;_val)));2)Si on veut recopier plus de deux fois, on peut ajouter _val plusieurs fois, ici on va répéter 4 fois :
=REDUCE("Période";{1;2;3};LAMBDA(_accu;_val;ASSEMB.V(_accu;_val;_val;_val;_val)))Finalement, si on veut paramétrer le nombre de fois que l'on va répéter l'opération, on peut le faire comme ceci. L'approche est un peu différente, on va utiliser l'astuce suivante qui permet de répéter une chaine de caractère un certain nombre de fois (explications détaillées sur pourquoi ca fonctionne ici) :
=SI(SEQUENCE(4);"variable à répéter 4 fois")Cette formule fonctionne car tout ce qui est numérique et différent de 0 pour excel est "vrai"
Et on va incorporer cette formule dans la lambda :
=REDUCE("Période";A2:A4;LAMBDA(_accu;_val;ASSEMB.V(_accu;SI(SEQUENCE(4);_val))))Comme auparavant, on peut aussi EXCLURE le premier élément :
=EXCLURE(REDUCE("";A2:A4;LAMBDA(_accu;_val;ASSEMB.V(_accu;SI(SEQUENCE(4);_val))));1)Intégration en LAMBDA
Finalement, voici la fonction LAMBDA correspondante à ajouter dans votre fichier :
L_REPETER.ELEMENT.PAR.ELEMENT=LAMBDA(_tableau;_NbDeRepetitions;EXCLURE(REDUCE("Nombres";_tableau;LAMBDA(_accu;_val;ASSEMB.V(_accu;SI(SEQUENCE(_NbDeRepetitions);_val))));1))
En vidéo
De 1,2,3 à 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3
L'approche est relativement similaire.
La formule
=REDUCE("Période";SEQUENCE(4);LAMBDA(_ac;_v;ASSEMB.V(_ac;A2:A4)))SEQUENCE(4) nous indique le nombre de passage que la LAMBDA va faire, donc, le nombre de répétitions.
Pour chaque passage, on va assembler verticalement l'accumulateur (donc la dernière valeur calculée) avec la plage A2:A4. Cela revient donc à répéter la plage 4 fois.
Comme indiqué précédemment, pour ne pas avoir "période", on peut utiliser la fonction EXCLURE et remplacer "période" par "". ce remplacement n'est pas obligatoire, mais il permet de réduire la formule.
=EXCLURE(REDUCE("";SEQUENCE(4);LAMBDA(_ac;_v;ASSEMB.V(_ac;A2:A4)));1)Et en LAMBDA :
L.REPETER.PLAGE=LAMBDA(_Tableau;_NbDeRepetitions;EXCLURE(REDUCE("";SEQUENCE(_NbDeRepetitions);LAMBDA(_ac;_v;ASSEMB.V(_ac;_Tableau)));1))
