L'ajout d'un $ devant la ligne ou la colonne permet de recopier une formule vers le bas ou vers la droite en gardant la référence initiale. On a ainsi figé la référence.

Par exemple, A$1 recopié vers le bas sera toujours A$1, alors que A1 se transformera en A2.

Etape 1 : sélectionner une référence dans une formule.

Etape 2 : appuyer sur F4, automatiquement un $ est ajouté à la ligne, colonne, aux deux, puis pas du tout et on reboucle.

Vous connaissez peut être

1. La touche F9 appliquée à une partie de fonction (à ne pas valider).
2. Faire une sélection d'une partie de la formule pour voir apparaître sa valeur au dessus.
3. Ou encore utiliser l'outil "de calculs dans "Évaluer la formule" dans l'onglet Formules.

Voici une autre approche qui permet de calculer les éléments d'une formules directement.
L'idée est donc de voir les valeurs de chaque référence (A1 ou A1:B2) d'une formule, afin de faciliter le débogage. Voici quelques exemples. En vert, on retrouve l'équivalent de FORMULETEXTE, mais avec les valeurs à la place des références de type A1. Pour tableau1, j'ai eu du bol, car tableau1 a la même structure qu'une référence (lettres + chiffres).

Voici la formule et il suffit de remplacer A1 par la référence contenant une formule de votre choix.

=LET(
  f; FORMULETEXTE(A1);
  refs; REGEX.EXTRAIRE(f; "(\$?[A-Za-z]+\$?[0-9]+(?::\$?[A-Za-z]+\$?[0-9]+)?)"; 1);
  REDUCE(f; refs; LAMBDA(acc;ref;
    LET(
      val; INDIRECT(ref);
      rep; SI(OU(LIGNES(val)>1; COLONNES(val)>1);
              LET(
                rowStrings; BYROW(val; LAMBDA(row; JOINDRE.TEXTE("."; VRAI; MAP(row; LAMBDA(item; SI(ESTNUM(item); item; """" & item & """"))))));
                "{" & JOINDRE.TEXTE(";"; VRAI; rowStrings) & "}"
              );
              SI(ESTNUM(val); val; """" & val & """")
      );
      SUBSTITUE(acc; ref; rep)
    )
  ))
)

L'idée est de passer à travers une formule et de remplacer les références simples par leurs valeurs. La fonction REGEX détaillée dans le schéma ci-contre permet de faire cela directement.

En VBA, chatGPT fait ca très bien, mais en formule excel, ca se corse beaucoup et on fait appel à la fonction REDUCE, à la récursivité d'une lambda et aux REGEX. Bref du super modern Excel, mais le tout en une fonction, ce qui est assez extraordinaire.

Le top du top, c'est que j'ai réussi à arriver à cette fonction, sans rien taper, just du vibe coding (ou du vibe excelling, ici)

Evidemment, il y a eu quelques hallucinations:

1. REDUIRE qui n'existe pas, on doit utiliser REDUCE
2. EXTRAIRE.REGEX.TOUT n'existe pas, mais si on met un 1 en troisième argument de REGEX.EXTRAIRE, on obtient le même résultat.
3. NB.LIGNES n'existe pas (il faut utiliser LIGNES)
4. Les ; sont remplacés par des ,
5. Il faut guider l'IA tout du long, car la question est un peu complexe à traduire en formule.

Bref, je pense avoir réussi.

-----

Voici l'explication technique fournie par ChatGPT. J'ai relu et tout se tient.

Objectif général de la formule

Cette formule transforme la formule inscrite dans la cellule C1 en une représentation textuelle où chaque référence de cellule ou plage est remplacée par les valeurs réelles contenues dans ces cellules. Elle aide à comprendre visuellement les formules complexes en montrant directement les données utilisées.

Explication étape par étape

1. Déclaration avec LET :
   • f : récupère la formule présente dans la cellule C1 sous forme de texte grâce à FORMULETEXTE(C1).
2. Extraction des références avec REGEX.EXTRAIRE :
   • refs : extrait toutes les références de cellules et plages (par exemple A1, $B$2, C3:D4) de la formule récupérée en utilisant une expression régulière.
3. Traitement des références avec REDUCE et LAMBDA :
   • Parcourt chaque référence trouvée (refs) pour remplacer la référence dans la formule initiale par sa valeur réelle.
4. Récupération des valeurs réelles avec INDIRECT :
   • Obtient les valeurs de chaque référence (ref) grâce à INDIRECT(ref).
5. Gestion des cellules individuelles ou plages avec SI :
   • Vérifie si la référence est une plage (plusieurs cellules) ou une cellule unique :
     • Si c'est une plage : transforme en texte sous forme d'un tableau {valeur1;valeur2}.
     • Si c’est une cellule individuelle : affiche directement sa valeur.
6. Formatage des plages :
   • Utilise BYROW pour parcourir chaque ligne de la plage et MAP pour traiter chaque élément individuellement :
     • Si l’élément est numérique, il reste tel quel.
     • Si c’est du texte, il est entouré de guillemets.
   • Les valeurs d’une même ligne sont séparées par des points ..
   • Chaque ligne est séparée par un point-virgule ;.
   • L’ensemble est encapsulé entre accolades {} pour représenter un tableau.
7. Substitution des références par leurs valeurs avec SUBSTITUE :
   • Remplace chaque référence (ref) dans la formule par sa valeur réelle formatée (rep).

Exemple pratique

Si la cellule C1 contient la formule :

=A1+B2:C2

et que les cellules sont :

• A1 = 10
• B2:C2 = [5, "Bonjour"]

La formule traduite affichera :

=10+{5."Bonjour"}

Cette méthode facilite le débogage et la compréhension des formules Excel complexes en montrant clairement les valeurs réelles utilisées.

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 !