La semaine dernière était le sommet des MVP de Microsoft. L’occasion de comprendre certaines fonctionnalités, de se mettre à jour sur ce qui va arriver dans excel et d'interagir avec les équipes de Microsoft. Bref, la tête explosée par les possibles, des étoiles dans les yeux, et beaucoup d'impatience, car certaines fonctionnalités sont superbes, mais elles ne sont pas encore matures, et de toutes façons, je ne peux pas en parler (NDA).

Ceci dit, il y a plusieurs choses dont je peux parler, dont notamment une technique brillamment expliquée par Diarmuid (il y a surement pleins d'autres personnes qui ont pensé à cette utilisation, comme c'est souvent le cas avec Excel).

Pour rappel, j'avais déjà parlé de débogage de LET ici :
https://www.xlerateur.com/divers/2023/03/25/truc-pour-deboguer-une-let-13487/

L'idée est maintenant d'utiliser un ASSEMB.H pour afficher les résultats sous forme d'un tableau de débogage. Voici le fichier de démonstration.

Par exemple, en décochant le mode débogage, on a ceci :

Et en cochant, on a l'éclatement de la formule.

Voici la formule utilisée pour les tests

=LET(
_x;Tab_1[x];
_y;Tab_1[y];
_z;Tab_1[z];
_xy;_x*_y;
_2z;2*_z;
_3z;3*_z;
_resultat;_xy+_2z;
SI($I$2;ASSEMB.H(_xy;_2z;_resultat);_resultat))

Et celle qui permet de retourner les titres de colonnes

=LET(
_formule_complete;FORMULETEXTE(I6);
_formule_debogage;TEXTE.AVANT(TEXTE.APRES(_formule_complete;"ASSEMB.H(";-1);")";1);
_formule_propagee;FRACTIONNER.TEXTE(_formule_debogage;";");
SI($I$2;_formule_propagee;"Résultat"))

Et finalement, la formule de comparaison, aussi expliquée ici.

=SI(I2;LET(plage1;E6:G8;plage2;I6#;SI(SOMME(SI(plage1=plage2;0;1))=0;"Aucune erreur";"Erreur(s) possible(s) en "&JOINDRE.TEXTE("/";VRAI;SI(plage1=plage2;"";SUBSTITUE(ADRESSE(1;COLONNE(plage1);4);"1";"")&LIGNE(plage1)&"('"&plage1&"' vs. '"&plage2&"')"))));"")

Dans la vidéo ci-dessous, je vous explique comment tout cela fonctionne !

Voici un sujet qui est assez intéressant.

Comment combiner le résultat d'une plage dynamique dans une seule cellule et que cette cellule se recopie vers le bas?

La solution est assez simple, on va utiliser la fonction JOINDRE.TEXTE

Ca nous donne ceci :

=JOINDRE.TEXTE(CAR(10);;
         FILTRE(T_Ventes[ID Produits];[@[ID Client]]=T_Ventes[ID Client]))

où FILTRE renvoie la liste des ID produits, et joindre.texte permet de les combiner ensemble dans une seule cellule. J'utilise CAR(10) pour un retour à la ligne, mais on aurait aussi pu utiliser point-virgule. Si vous utilisez CAR(10), il faut ensuite formater avec "Renvoyer à la ligne automatiquement" dans le menu Accueil.
Ca nous donne ceci :

On peut aller plus loin en faisant l'inverse grâce à la fonction FRACTIONNER.TEXTE.

On a donc :

=JOINDRE.TEXTE(CAR(10);;
       RECHERCHEX(
          FRACTIONNER.TEXTE([@[Liste Produits]];;CAR(10))*1;
          T_Produits[ID Produit];
          T_Produits[Description]))

FRACTIONNER.TEXTE va permettre de scinder les ID sur plusieurs lignes, et ensuite RECEHERCHEX va permettre de faire la recherche pour chacun de ces IDs et ensuite retourner la description. On multiplie ici par 1, car les ID sont en numérique, mais FRACTIONNER.TEXTE renvoie du texte.

IMPORTANT, dans les deux cas de figure, il faut s'assurer que les séparateurs n'existent pas dans les données, sinon on va joindre ou séparer au mauvais endroit.

Voilà, j'espère que cela vous donnera des idées !

Bien que n'ayant pas encore utilisé cette fonction dans un vrai cas de figure, je voudrais apporter quelques éléments de réflexion qu'il me semble capital de maitriser.

Dans la très grande majorité des cas, utiliser des tableaux structurés est beaucoup plus avantageux et lisible (le nom du tableau et de la colonne sont automatiquement dans la formule).

RECHERCHEX et INDEX/EQUIV

Donc, SUPPR.PLAGE et .:. réduisent la plage de travail, mais si on travaille sur deux colonnes devant avoir le même nombre de lignes, on prend un gros risque.

Par exemple...

Pour la première formule, la valeur qu'on aurait attendu serait 0 (ou vide avec & "").
Pour la deuxième formule, la valeur retournée est #VALEUR, ce qui est logique (mais pas forcément attendu) puisque les deux plages dans recherchex n'ont pas le même nombre de lignes.
Les troisièmes et quatrièmes formules illustrent le même problème, mais avec la combinaison INDEX/EQUIV. On voit d'ailleurs comment la dernière formule renvoie une valeur carrément fausse.

Les cellules de fin ou début de plage contenant un espace ne sont pas "supprimées" avec Suppr.Plage.

Par exemple, j'ai ici ajouté un espace en B5 et remis le 1 en A1.
Regardez l'impact sur les formules.

Ou bien encore cet espace ajouté malencontreusement en B9 qui engendre quelques subtilités.

Subtilité 1 : les cellules de fin ou début de plage contenant un espace ne sont pas "supprimées" avec Suppr.Plage.

Subtilité 2 : Lorsqu'une référence dynamique rencontre des cellules vides, elle va les transformer en zéro. Bien ou pas bien, je ne sais pas, mais il faut juste en être conscient. On a un exemple en C1, qui va retourner les cellules vides avec des zéros.

Subtilité 3 : Ensuite, vous pouvez vous amuser avec les fonctions NB et NBVAL pour comprendre les impacts et les utiliser à bon escient en fonction de ce que vous voulez retourner !

La fonction NBVAL compte le nombre de cellules qui ne sont pas vides dans une plage. Donc 5 (A,B,C,D,Espace) pour NBVAL(A.:.A) et 9 (on ajoute les 4 zéros) pour NBVAL(C1#).

La fonction NB() retourne les valeurs numériques.

NB(A.:.A) retourne donc 0 valeurs numériques. NB(C1#) retourne 4 pour la plage dynamique propagée réduite de A:A avec un remplacement des cellules vides par un zéro - donc du numérique. Argh !

Mini bonus : =LIGNES(A.:.A) renvoit bien 9. Ouf !

En conclusion, j'aime beaucoup avoir cette nouvelle fonction pour des cas tordus, mais pour le moment rien ne remplace un bon tableau structuré en raison de ces effets de bord !

L'occasion de relire ce post

Les tableaux structurés dans Excel