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 IF imbriqué s'est réellement déclenchée, ou quelle référence a alimenté un mauvais total. HotXLS reproduit exactement ce comportement via TXLSFormulaTracer, de sorte qu'un programme Delphi ou C++Builder peut afficher la même liste d'étapes pour l'audit d'un classeur, le débogage d'une formule générée, ou pour expliquer pourquoi un résultat est apparu ainsi. Chaque étape enregistrée contient 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 découpe la formule en jetons et l'analyse avec un analyseur descendant récursif, puis réduit l'arbre en profondeur d'abord, 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 sous forme de 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 qu'un recalcul complet de la cellule produirait. L'analyseur 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 priorité des opérateurs, avec un niveau récursif par bande de priorité. 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 de cela. Chaque niveau analyse le niveau au-dessus de lui pour ses opérandes, de sorte qu'une bande supérieure se lie plus étroitement. C'est la même priorité qu'Excel applique, 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, de sorte que les multiplications sont plus profondes dans l'arbre et se réduisent en premier
Tracer une formule et parcourir les étapes
L'utilisation reflète la démo livrée 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 units
Sheet.Cells[1, 2].Value := 25; // B1 unit price
Sheet.Cells[1, 3].Value := 0.08; // C1 tax rate
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('result = ', VarToStr(Final));
finally
Tracer.Free;
end;
finally
Book.Free;
end;
end;Les références de cellule sont résolues 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 clôture le tout. Le champ Depth vous permet d'indenter afin que les réductions les plus profondes soient visiblement les plus enfoncées, exactement comme Excel souligne le terme le plus profond avant tout terme extérieur
Le piège du littéral dépendant des paramètres régionaux
Le détail le plus dangereux de tout ce système est invisible sur une machine anglaise et se brise bruyamment sur une machine allemande. Lorsqu'un nombre calculé est réinséré dans le texte de la formule, il doit être écrit sous forme de chaîne, puis réanalysé par le moteur de calcul, qui traite . comme le séparateur 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 recalcul de A1*B1*1,08 s'analyserait soit de manière incorrecte, soit échouerait carrément
Le traceur évite cela en formatant chaque littéral numérique via un TFormatSettings privé qu'il fige 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
// Conceptually what the tracer pins once, at construction
FFloatFmt := FormatSettings;
FFloatFmt.DecimalSeparator := '.';
FFloatFmt.ThousandSeparator := #0;
// every reduced number is written with: FloatToStr(Double(V), FFloatFmt)C'est le genre de bogue qui n'apparaît jamais dans les 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 l'énoncer clairement : faire l'aller-retour d'une valeur dans 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
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 à un booléen, le traceur la réécrit sous la forme 1 ou 0, et non comme TRUE ou FALSE. La raison en est que le littéral réduit doit s'analyser proprement dans n'importe quel contexte l'entourant, et l'arithmétique est le cas le plus exigeant. Si une comparaison comme A1>A2 se réduisait au texte TRUE et que ce texte atterrissait dans TRUE*B1, le recalcul dépendrait de l'acceptation par le moteur d'un mot-clé booléen nu dans une multiplication. Substituer 1 contourne entièrement la question, car 1*B1 n'est ambigu dans aucune position arithmétique. Cela correspond également à la propre coercition d'Excel, où TRUE se comporte comme 1 et FALSE comme 0 au moment où 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 est la sémantique de court-circuit. IF, CHOOSE et IFERROR n'évaluent que la branche qu'ils sélectionnent, et réduire les arguments en premier forcerait le moteur à calculer des branches qu'Excel ne touche jamais. La victime classique est une protection contre la division par zéro telle que IF(B1=0,0,A1/B1) : si le traceur réduisait A1/B1 avant d'évaluer le IF, la protection échouerait et déclencherait l'erreur même qu'elle existe pour éviter. En évaluant l'appel entier de manière atomique, le traceur préserve l'évaluation paresseuse qui permet à de telles protections de fonctionner
// IF is one atomic step; only the selected branch is evaluated
Final := Tracer.Trace('IF(A1>A2,A1*B1,A2*B1)', Steps);
// A1>A2 is true, so the step records A1*B1 as the chosen result;
// A2*B1 is never computed, exactly as Excel would do it.Le compromis est que vous ne voyez pas à l'intérieur de l'appel de fonction comme des étapes distinctes, mais c'est le comportement correct. Afficher des réductions d'arguments qu'Excel n'effectue jamais serait 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 autres normalisations maintiennent 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 SUM(A1,A2,A3) est recalculée comme SUM(A1;A2;A3), ce que le moteur accepte. La substitution de valeurs est ce qui rend cette reconstruction nécessaire, et avoir le bon séparateur est ce qui permet à la reconstruction d'être analysée
Les références de plage 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 garde une plage intacte comme son texte d'origine et laisse la fonction englobante la réduire dans son ensemble. Dans SUM(A1:A3)*B1, la plage reste entière, SUM(A1:A3) se réduit à un nombre en une étape atomique, et ce n'est qu'alors 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 qu'il finit par apporter
// The range A1:A3 is never split; SUM is one atomic reduction,
// then the product with B1 reduces on top of it.
Final := Tracer.Trace('SUM(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 miroir fidèle 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 forcés de la même manière qu'Excel les force, 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 des travaux numériques plus lourds, l'article sur les fonctions de distribution statistique dans Delphi couvre la bibliothèque intégrée avec laquelle le traceur évalue. Tout ceci est livré dans le composant de tableur HotXLS pour Delphi et C++Builder, aux côtés des API de lecture, d'écriture, de formatage et de calcul couvertes ailleurs sur ce blog