Plus fort que F9 et FORMULETEXTE!

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 formule 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.