Archives de la catégorie : Divers

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 :

L’incroyable fonction REDUCE

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é !

Il arrive très souvent de devoir combiner plusieurs feuilles ou tableaux ensemble.

Voici comment faire en deux minutes :

Autres méthodes et discussion

On peut cependant arriver au même résultat de nombreuses manières :

• La meilleure méthode : transformer les données en tableaux structurés et utiliser PowerQuery.
  L'actualisation - qu'on peut automatiser - sera alors la seule chose à faire. Ci-dessous une autre approche qui permet de gérer d'autres cas particuliers, notamment si on n'a pas le contrôle sur les noms des tables.
• Via une formule ASSEMB.V. C'est aussi une excellente méthode si vous n'ajoutez pas fréquemment de nouvelles feuilles. Elle est parfois plus puissante que PowerQuery, car il n'y a pas besoin d'actualiser.
• Manuellement via des copier coller, mais c'est dangereux et long
• Avec l'outil de consolidation dans Excel. J'ai vu faire, mais je ne le maîtrise pas encore parfaitement

Voici donc une autre approche avec plusieurs astuces en plus.

Quelques points importants à garder en tête

• Au lieu de List.Contains, on aurait aussi pu joindre les deux tables en ne gardant que les données identiques. À faire sans transformer en liste par drill-down.
• C'est évident, mais ça va mieux en le disant, si on ajoute un nouveau mois, le nom de la table structurée doit respecter la convention de nommage, par exemple "T_Mai_2024". Copier-coller le nouveau mois à partir de la liste est probablement la méthode la plus rapide et la plus sûre.
• Sur du long terme, il faudra peut-être ajouter plus de mois ou redémarrer le fichier avec seulement les données de la nouvelle année. On peut aussi faire en sorte que la formule dynamique prenne en compte la date d'aujourd'hui.
• La fonction M (powerquery) Excel.CurrentWorkbook ne rapatrie pas les feuilles. Si on doit quand même travailler au niveau des feuilles (et pas d'un tableau structuré), on peut passer par Excel.Workbooks via Données - Obtenir des données - A partir d'un fichier - A partir d'un classeur Excel et faire référence au même classeur. On peut ensuite sélectionner les feuilles que l'on veut combiner ensemble. Il faut aussi retravailler les titres si nécessaire.