L'incroyable fonction REDUCE

Je commence tout doucement à tomber amoureux de cette fonction ! J'en avais déjà parlé ici et . 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))

Pourquoi SI(SEQUENCE(4);"On répète 4 fois") fonctionne dans excel?

Propager un élément plusieurs fois avec SI et SEQUENCE

=SI(SEQUENCE(4);"On répète 4 fois")

En fait, tout ce qui est numérique et différent de 0 est "vrai" pour Excel.

Ça se démontre facilement ici :

De fait, si on incorpore SEQUENCE(4) dans un SI, c'est comme si on incorporait 4 fois le résultat "vrai". De fait, ca nous génère 4 lignes.

On peut donc créer une plage propagée très simplement.

Note : le fait que tout numérique différent de 0 est égal à vrai est aussi utilisé pour simuler un OU dans une expression du style (...=...)+(...=...) qu'on peut utiliser avec les fonctions FILTRE ou SOMMEPROD.

A quoi ça sert?

En plus du cas classique qui consiste à recopier un même élément plusieurs fois, vous pouvez aussi dupliquer chaque élément d'une liste plusieurs fois :

=REDUCE("Période";{"a";"z";"b"};LAMBDA(_accu;_val;ASSEMB.V(_accu;SI(SEQUENCE(4);_val))))

qui nous donne

Et sur le même principe :

=REDUCE("Période";SEQUENCE(4);LAMBDA(_ac;_v;ASSEMB.V(_ac;{"a";"z";"b"})))

Pour obtenir

Évidemment, vous pouvez changer le 4 pour ce que vous voulez, et le {"a";"z";"b"} par une référence de plage ou par {1;2;3}

Pour aller plus loin et voir quelques variations complémentaires sur ces mega-formules :

Le pouvoir caché de la fonction REDUCE : regrouper des tables à partir d'une liste

Cette fonction m'a toujours intriguée, car elle porte en elle des pouvoirs extraordinaire, mais il faut réussir à en déverrouiller le code intellectuel.

Voici un exemple très pratique qui permet de comprendre ce qui se passe. Pour d'autres approches, vous pouvez aller voir ici. Le gros intérêt de cette approche est qu'elle ne nécessite pas d'actualiser les données. Aussi, comme on renvoie une plage dynamique, on peut définir un nom y faisant référence (avec le #) et l'utiliser dans un TCD ou ailleurs.

Notre objectif est donc de combiner plusieurs tables ensemble.

Le plus simple est d'utiliser la fonction ASSEMB.V et ensuite de sélectionner les tables les unes après les autres. Pour la première table, on prend aussi l'en-tête.

Ça va donner ceci :

Maintenant, on veut automatiser l'approche en stockant le nom des tables dans une liste. Cela permet d'éviter de devoir modifier directement la formule.

Avec REDUCE, vous partez d'un élément de départ (ca peut être une plage de cellule), puis vous donnez un tableau à parcourir, et enfin, pour chaque élément de ce tableau, vous définissez une lambda, donc n'importe quelle fonction. Le résultat va combiner ensemble l'élément de départ avec le résultat de la fonction LAMBDA. Puis la fonction va recommencer avec la valeur suivante du tableau à parcourir et combiner les deux résultats ensemble. Le résultat final de la fonction peut être soit une valeur, soit une plage propagée.

Par exemple, dans cette fonction :

=REDUCE(T_Mois_Janvier_2024[#En-têtes];
        L_Tables[Liste des tables];
        LAMBDA(_cumul;_valeur;
               ASSEMB.V(_cumul;SIERREUR(INDIRECT(_valeur);"Erreur : table " & _valeur & " inexistante"))))

On va démarrer avec les en-têtes du premier tableau.
Puis, on va parcourir toutes les cellules de la table "L_Tables" pour récupérer les noms des tables à mettre les unes en dessous des autres.
Ensuite, on va faire l'assemblage vertical (ASSEMB.V) de ces tables, à l'aide de la LAMBDA.

Note : en théorie, BYROW aurait du permettre de faire la même chose, mais je n'ai pas encore compris pourquoi ca n'a pas fonctionné !