Excel cache un petit débogueur bien en vue. Sélectionnez une cellule, ouvrez Formules et cliquez sur Évaluer la formule, et une boîte de dialogue affiche la formule avec une sous-expression soulignée. Appuyez sur Évaluer et cette sous-expression se réduit à sa valeur, puis la suivante est soulignée, et vous regardez une longue expression se réduire à un seul nombre, une réduction à la fois. C'est le moyen le plus rapide de trouver quelle branche d'un SI imbriqué s'est réellement déclenchée, ou quelle référence a alimenté un total erroné. HotXLS reproduit ce comportement exact via TXLSFormulaTracer, afin qu'un programme Delphi ou C++Builder puisse afficher la même liste d'étapes pour l'audit d'un classeur, le débogage d'une formule générée ou l'explication à quelqu'un de la raison pour laquelle un résultat a été obtenu de cette façon. Chaque étape enregistrée porte le texte de la sous-expression et la valeur à laquelle elle se réduit
Comment le moteur de réduction parcourt l'expression
Le traceur n'intervient pas dans le moteur de calcul. Il divise la formule en jetons (tokenizes) et l'analyse avec un analyseur syntaxique à descente récursive, puis réduit l'arbre en profondeur d'abord (depth-first), en commençant par la sous-expression évaluable la plus profonde. Lorsqu'un nœud se réduit à une valeur, cette valeur est substituée dans l'expression environnante en tant que littéral, et le moteur demande au véritable calculateur de recalculer l'expression désormais plus simple. Étant donné que chaque étape est évaluée via la méthode publique Calculate de la feuille de calcul plutôt que par un raccourci privé, chaque étape correspond exactement à ce que produirait un recalcul complet de la cellule. L'analyseur (parser) est non invasif par conception, ce qui lui permet de s'exécuter sur n'importe quelle feuille de calcul sans perturber son état
L'analyseur suit une échelle de précédence des opérateurs, avec un niveau récursif par bande de précédence. De la liaison la plus faible à la plus forte, les bandes sont : niveau 0 comparaison (=, <>, <, >, <=, >=), niveau 1 concaténation de chaînes (&), niveau 2 addition et soustraction, niveau 3 multiplication et division, niveau 4 exponentiation, et enfin plus et moins unaires en dessous. Chaque niveau analyse le niveau au-dessus de lui pour ses opérandes, de sorte qu'une bande supérieure se lie plus étroitement. Il s'agit de la même précédence qu'applique Excel, c'est pourquoi A1*B1+A2*B1 réduit les deux produits avant la somme : la multiplication se situe au niveau 3, l'addition au niveau 2, les multiplications sont donc plus profondes dans l'arbre et se réduisent en premier
Suivre une formule et parcourir les étapes
L'utilisation reflète la démo fournie dans Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Construisez une feuille de calcul (ou ouvrez un classeur existant), construisez un traceur sur la feuille, appelez Trace, et itérez le tableau renvoyé. Chaque TXLSFormulaStep expose Depth pour l'indentation, Source pour la sous-expression d'origine, Expression pour cette sous-expression avec ses opérandes déjà substitués, et Value pour le résultat de l'étape
uses
SysUtils, Variants, lxHandle, lxHandleX, lxFormulaTrace;
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Tracer: TXLSFormulaTracer;
Steps: TXLSFormulaStepArray;
Final: Variant;
I: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Order');
Sheet.Cells[1, 1].Value := 10; // A1 unités
Sheet.Cells[1, 2].Value := 25; // B1 prix unitaire
Sheet.Cells[1, 3].Value := 0.08; // C1 taux de taxe
Tracer := TXLSFormulaTracer.Create(Sheet);
try
Final := Tracer.Trace('A1*B1*(1+C1)', Steps);
for I := 0 to High(Steps) do
Writeln(StringOfChar(' ', Steps[I].Depth * 2),
Steps[I].Source, ' -> ', Steps[I].Expression,
' = ', VarToStr(Steps[I].Value));
Writeln('resultat = ', VarToStr(Final));
finally
Tracer.Free;
end;
finally
Book.Free;
end;
end;Les références de cellules se résolvent en premier et apparaissent comme leurs propres étapes, puis les produits se réduisent, puis le facteur de taxe entre parenthèses, et la multiplication finale conclut le tout. Le champ Depth (profondeur) vous permet d'indenter de sorte que les réductions les plus profondes soient visiblement les plus en retrait, exactement comme Excel souligne le terme le plus interne avant tout terme externe
Le piège du littéral dépendant des paramètres régionaux (locale-free)
Le détail le plus dangereux de tout ce schéma est invisible sur une machine anglaise et échoue bruyamment sur une machine allemande. Lorsqu'un nombre calculé est substitué dans le texte de la formule, il doit être écrit sous forme de chaîne de caractères, puis ré-analysé par le moteur de calcul, qui traite . comme le point décimal. Si la substitution utilisait les paramètres régionaux du système, un TFormatSettings allemand écrirait 1,08 pour le facteur de taxe, la virgule serait lue comme un séparateur d'arguments, et le nouveau calcul de A1*B1*1,08 s'analyserait de manière erronée ou échouerait carrément
Le traceur évite cela en formatant chaque littéral numérique via un TFormatSettings privé qu'il fixe lors de la construction, avec DecimalSeparator forcé à . et ThousandSeparator défini sur #0 afin qu'aucun caractère de regroupement ne soit jamais émis. FloatToStr produit alors un littéral que le moteur peut toujours relire, quels que soient les paramètres régionaux de l'opérateur
// Conceptuellement, ce que le traceur fixe une fois, à la construction
FFloatFmt := FormatSettings;
FFloatFmt.DecimalSeparator := '.';
FFloatFmt.ThousandSeparator := #0;
// chaque nombre réduit est écrit avec : FloatToStr(Double(V), FFloatFmt)C'est le genre de bogue qui n'apparaît jamais lors des propres tests de l'auteur et qui ne fait surface que lorsqu'un client dans une autre région exécute le même code, il vaut donc la peine de le dire clairement : faire un aller-retour d'une valeur via le texte d'une formule est un problème de sérialisation, et la sérialisation doit être indépendante des paramètres régionaux (locale-free)
Les booléens se réduisent à 1 et 0
Une décision de substitution connexe concerne les valeurs logiques. Lorsqu'une sous-expression s'évalue en booléen, le traceur la réécrit sous forme de 1 ou 0, et non de VRAI ou FAUX. La raison en est que le littéral réduit doit se ré-analyser proprement dans le contexte qui l'entoure, et l'arithmétique est le cas le plus exigeant. Si une comparaison comme A1>A2 se réduisait au texte VRAI et que ce texte atterrissait dans VRAI*B1, le nouveau calcul dépendrait de l'acceptation par le moteur d'un mot-clé booléen nu dans une multiplication. Remplacer par 1 contourne entièrement la question, car 1*B1 est sans ambiguïté dans n'importe quelle position arithmétique. Cela correspond également à la propre contrainte d'Excel, où VRAI se comporte comme 1 et FAUX comme 0 dès qu'un nombre est attendu
Les appels de fonction se réduisent de manière atomique
Un moteur d'étapes naïf réduirait d'abord les arguments d'une fonction, puis l'appel. C'est faux pour Excel, et le traceur ne le fait délibérément pas. Un appel de fonction est évalué dans son ensemble, à partir de son texte d'origine, en une seule étape. La raison en est la sémantique de court-circuit. SI, CHOISIR et SIERREUR n'évaluent que la branche qu'ils sélectionnent, et réduire d'abord les arguments obligerait le moteur à calculer des branches qu'Excel ne touche jamais. La victime classique est une protection contre la division par zéro comme SI(B1=0;0;A1/B1) : si le traceur réduisait A1/B1 avant d'évaluer le SI, la protection ferait un raté et déclencherait l'erreur même qu'elle est censée empêcher. En évaluant l'intégralité de l'appel de manière atomique, le traceur préserve l'évaluation paresseuse (lazy evaluation) qui permet à ces protections de fonctionner
// SI est une étape atomique ; seule la branche sélectionnée est évaluée
Final := Tracer.Trace('SI(A1>A2,A1*B1,A2*B1)', Steps);
// A1>A2 est vrai, donc l'étape enregistre A1*B1 comme résultat choisi ;
// A2*B1 n'est jamais calculé, exactement comme le ferait Excel.Le compromis est que vous ne voyez pas à l'intérieur de l'appel de fonction en tant qu'étapes distinctes, mais c'est le comportement correct. Montrer des réductions d'arguments qu'Excel n'effectue jamais constituerait une trace plus trompeuse que de traiter l'appel comme l'unité d'évaluation unique qu'il est réellement
Séparateurs d'arguments et plages intactes
Deux normalisations supplémentaires permettent de conserver l'honnêteté du recalcul. Le compilateur du moteur de calcul attend ; comme séparateur d'arguments de fonction, donc lorsque le traceur reconstruit un appel de fonction à partir de son arbre analysé, il joint les arguments avec ;, même si l'utilisateur a initialement tapé ,. Une formule écrite comme SOMME(A1,A2,A3) est recalculée comme SOMME(A1;A2;A3), ce que le moteur accepte. La substitution des valeurs est ce qui rend cette reconstruction nécessaire, et l'obtention du bon séparateur est ce qui permet l'analyse de la reconstruction
Les références de plages sont l'autre cas. Une plage telle que A1:A3 n'est pas un scalaire et ne doit pas être divisée en trois valeurs distinctes, car la fonction qui la consomme attend un argument de plage. Le traceur conserve une plage intacte dans son texte d'origine et laisse la fonction d'encadrement la réduire dans son ensemble. Dans SOMME(A1:A3)*B1 la plage reste entière, SOMME(A1:A3) se réduit à un seul nombre en une étape atomique, et ce n'est qu'ensuite que la multiplication externe s'exécute. C'est la même limite qu'Excel trace entre un opérande de plage et le scalaire auquel il finit par contribuer
// La plage A1:A3 n'est jamais divisée ; SOMME est une réduction atomique,
// puis le produit avec B1 se réduit par-dessus.
Final := Tracer.Trace('SOMME(A1:A3)*B1', Steps);
for I := 0 to High(Steps) do
Writeln(Steps[I].Source, ' = ', VarToStr(Steps[I].Value));Mises ensemble, ces règles font de la liste des étapes un fidèle miroir de la commande Évaluer la formule d'Excel plutôt qu'une approximation de celle-ci. Les réductions se produisent dans l'ordre où Excel les effectue, les littéraux substitués survivent à tous les paramètres régionaux, les booléens sont contraints de la manière dont Excel les contraint, et les fonctions paresseuses restent paresseuses. Si vous souhaitez pousser le moteur plus loin avec vos propres fonctions, l'article sur le moteur de formules et les fonctions personnalisées montre comment les enregistrer, et pour un travail numérique plus lourd, l'article sur les fonctions de distribution statistique dans Delphi couvre la bibliothèque intégrée que le traceur évalue. Tout cela est livré dans le cadre du Composant de feuille de calcul HotXLS pour Delphi et C++Builder, à côté des API de lecture, d'écriture, de formatage et de calcul abordées ailleurs sur ce blog