Comment faire pour séparer la colonne "C1 & C2" ci-dessous avec une seule fonction?
L'objectif est de comprendre la fonction REDUCE, fournir une application pratique et ouvrir la porte à des formules plus complexes pour vos propres fichiers.
Voici la réponse directement implantable, puis la vidéo en dessous pour une explication détaillée.
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 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.
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 :
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 :
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.
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 :
