Comparaisons en chaîne, cellules vides et SUMIF dans HotXLS

HotXLS Delphi Component évalue =1<2<3 en FALSE, la même réponse qu'Excel 16, parce que depuis la v2.384.3 son analyseur de formules replie les opérateurs de comparaison de gauche à droite : 1<2 devient TRUE, et TRUE<3 est FALSE parce qu'un booléen se classe au-dessus de tout nombre. La même version rend un opérande vide égal à la fois à 0 et à "", et laisse SUMIF étirer une plage de somme à une cellule vers la forme de sa plage de critères. Chacun de ces points ressemble à un détail tant qu'un classeur calculé en Delphi ne contredit pas le même classeur ouvert dans Excel

Le désaccord commence d'ordinaire avec une formule écrite par intuition. Quelqu'un tape =0<B2<100 pour vérifier qu'une quantité est dans la plage, Excel répond tranquillement FALSE pour chaque ligne, et la feuille part en production avec ce bogue cuit en elle. Un moteur de calcul n'a pas à corriger l'intention de l'utilisateur ; son travail est de produire la valeur qu'Excel produirait, pour que le résultat en cache qu'HotXLS écrit dans le fichier corresponde à ce qu'Excel affiche après un recalcul. Avant la v2.384.3, HotXLS répondait TRUE à ce contrôle de plage sur chaque ligne, faux dans l'autre sens, et un rapport généré sur un serveur contredisait le même rapport ouvert sur un poste de bureau

Pourquoi =1<2<3 renvoie-t-il FALSE dans Excel ?

Excel renvoie FALSE parce qu'il lit une chaîne de comparaisons comme (1<2)<3, et le TRUE intérieur perd alors le concours de classement de types contre le nombre 3. L'ancien analyseur de HotXLS lisait le même texte comme 1<(2<3) : TXLSSyntax.Parse_expr dans lxFormula.pas analysait un opérande, voyait un jeton de comparaison, et récurse dans Parse_expr pour le côté droit, ce qui rend l'opérateur associatif à droite. Cela donne 1<TRUE, et un nombre est en dessous d'un booléen, donc le résultat était TRUE. L'erreur est symétrique : =3>2>1 est TRUE dans Excel et était FALSE dans HotXLS, et =1=1=TRUE est TRUE dans Excel et était FALSE avant la correction. La régression CalculateFormula_ComparisonChainsFoldLeftToRight épingle sept formules de ce genre contre les valeurs que renvoie Excel 16, et fait passer chacune par les deux architectures de moteur, le TXLSWorkbook classique et le TXLSXWorkbook natif XLSX, via la méthode Calculate décrite dans le panorama du moteur de formules HotXLS

const
  Formulas: array [0..6] of string = ('=1<2<3', '=3>2>1', '=(1<2)<3',
    '=1<(2<3)', '=1=1=TRUE', '=3>2>1=TRUE', '=1<2<3=FALSE');
  // Ce que renvoie Excel 16 :  FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE
var
  Classic: IXLSWorkbook;
  Xlsx: TXLSXWorkbook;
  i: Integer;
begin
  Classic := TXLSWorkbook.Create;
  Xlsx := TXLSXWorkbook.Create;
  try
    // TXLSXWorkbook.Calculate évalue contre la feuille active et
    // renvoie Null quand le classeur n'a aucune feuille du tout
    Xlsx.Sheets.Add('Data');
    for i := 0 to High(Formulas) do
      Writeln(Formulas[i], '  classic=', VarToStr(Classic.Calculate(Formulas[i])),
        '  xlsx=', VarToStr(Xlsx.Calculate(Formulas[i])));
  finally
    Xlsx.Free;
  end;
end;

La correction transforme Parse_expr en une boucle de la même forme que celle que Parse_expr1 utilise déjà pour +, - et &. Elle analyse le premier opérande avec Parse_expr1, et tant que le jeton suivant est l'un de =, <>, <, >, <= ou >=, elle crée un nœud de comparaison, attache le résultat gauche accumulé comme premier enfant, analyse l'opérande suivant avec Parse_expr1 plutôt que Parse_expr, et fait du nouveau nœud le résultat gauche du tour suivant. Deux détails étaient faciles à rater en convertissant la récursion en itération, et tous deux sont dans les notes des mainteneurs : le nœud accumulé doit être transféré (lChild := Item; Item := nil) dans cet ordre, et le chemin d'erreur doit faire Exit après avoir libéré le nœud à moitié construit plutôt que de tomber hors de la boucle et renvoyer un arbre pendouillant

Comment HotXLS classe-t-il nombres, textes et booléens dans une comparaison ?

HotXLS classe les types mélangés comme Excel : tout nombre est inférieur à toute valeur texte, et toute valeur texte est inférieure à tout booléen. TXLSCalculator.CompareVariants dans lxCalc.pas classe les deux opérandes avec GetRetValueType dans l'énumération TXLSRetValueType = (xlVariantValue, xlNumberValue, xlStringValue, xlBooleanValue), et quand les deux classes diffèrent, il compare simplement leurs ordinaux, si bien que l'ordre de déclaration de cette énumération est la règle inter-types. À l'intérieur d'une classe, la comparaison est la naturelle, avec une spécificité Excel pour le texte : les deux chaînes passent d'abord par lxUpperCase, donc ="abc"="ABC" est TRUE. Ce classement est la raison pour laquelle le résultat d'une chaîne ne peut pas se raisonner sans lui. TRUE<3 n'est pas une coercition de TRUE vers 1, c'est un booléen comparé à un nombre, et le booléen gagne. Les dates sont des numéros de série pour le moteur (varDate se classe comme xlNumberValue), donc une date est toujours sous tout texte, y compris un texte qui ressemble par hasard à une date

À quoi égale une cellule vide dans une comparaison ?

Une cellule vide utilisée comme opérande de comparaison égale 0 quand l'autre côté est un nombre, égale "" quand l'autre côté est du texte, et depuis la v2.384.53 égale FALSE quand l'autre côté est une valeur logique, si bien qu'avec A1 vide, =A1=0, =A1="" et =A1=FALSE sont tous TRUE. TXLSCalculator.CompareVarValues, qui sert les six opérateurs de comparaison, remplace le vide avant d'appeler CompareVariants : si exactement un opérande est Null, il devient WideString('') quand son partenaire est une chaîne, False quand son partenaire est un booléen, et 0 sinon. Deux vides se comparent toujours égaux entre eux sans substitution. Le chemin arithmétique transformait toujours un vide en 0, voilà pourquoi =A1+1 donnait 1, mais CompareVariants garde Null comme son propre rang le plus bas, sous tout nombre, et les opérateurs de comparaison utilisaient ce rang directement

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['B1', 'B1'].Value := 5;      // A1 reste vide exprès

  Writeln(VarToStr(Wb.Calculate('=A1=0')));    // True
  Writeln(VarToStr(Wb.Calculate('=A1=""')));   // True
  Writeln(VarToStr(Wb.Calculate('=A1<B1')));   // True : le vide se compare comme 0
  Writeln(VarToStr(Wb.Calculate('=A1<0')));    // False ; True avant la v2.384.3
end;

La dernière ligne est celle qui a fait mal en pratique. Sous l'ancien rang, un vide était plus petit que tout nombre, négatifs compris, si bien que =IF(A1<0,"overdrawn","ok") étiquetait chaque cellule de solde vide à découvert, et =A1=0 était FALSE pour une cellule que tout utilisateur décrirait comme zéro. Une frontière restait après la v2.384.3 : la substitution ne choisissait qu'entre 0 et la chaîne vide, donc un vide comparé à un booléen devenait 0, qui se classe sous TRUE et FALSE à la fois, et =A1=FALSE sur un A1 vide s'évaluait en FALSE. Depuis HotXLS 2.384.53, un vide comparé à une valeur logique est traité comme FALSE dans les deux moteurs XLS et XLSX, comme Excel : avec A1 vide, =A1=FALSE et =A1<TRUE renvoient TRUE et =A1=TRUE renvoie FALSE. Cela signifie aussi que la comparaison ne peut pas distinguer un vide de FALSE, dans Excel comme dans HotXLS ; quand une feuille a besoin de cette distinction, testez avec ISBLANK ou =A1=""

Pourquoi un SUMIF avec une plage de somme à une cellule renvoyait-il 0 ?

SUMIF renvoyait 0 parce que HotXLS bornait l'itération à la plus petite des deux plages, tandis qu'Excel garde la forme de la plage de critères et n'utilise la plage de somme que pour sa cellule supérieure gauche. =SUMIF(A1:A10,">5",B1) signifie donc B1:B10 dans Excel, une commodité dont dépendent beaucoup de modèles construits à la main. Le worker partagé TXLSCalculator.GetValueItemRange2 réduisait ses comptages de lignes et de colonnes à ceux de la plage de valeurs, ce qui ramenait l'exemple à un unique test de A1 contre B1. La v2.384.3 retire la borne : la boucle parcourt désormais la plage de critères et lit chaque valeur au même offset depuis le coin supérieur gauche de la plage de somme. Comme CalcSumIF et CalcAverageIF appellent tous deux ce worker, AVERAGEIF obtient le même redimensionnement, et une plage de somme plus grande que la plage de critères est rognée à la forme des critères pour la même raison. L'argument de critères au milieu est un argument de classe valeur et les deux extérieurs sont de classe référence, la distinction couverte dans l'article sur l'intersection implicite et les classes d'arguments

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Sales');
    for Row := 1 to 10 do
    begin
      Sheet.Cells[Row, 1].Value := Row;          // colonne de critères : 1..10
      Sheet.Cells[Row, 2].Value := Row * 100;    // montants : 100..1000
    end;
    Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)';     // plage de somme à une cellule
    Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)'; // plage de somme explicite
    if Book.Recalculate = lxOk then
      // D1 et D2 valent tous deux 4000 (600+700+800+900+1000) ; D1 valait 0 avant la v2.384.3
      Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ', VarToStr(Sheet.Cells[2, 4].Value));
  finally
    Book.Free;
  end;
end;

INDIRECT et YEARFRAC : deux corrections plus discrètes

INDIRECT honore désormais son second argument, et du texte après une référence valide est une erreur au lieu d'être ignoré. Avec a1 à FALSE, le texte est analysé comme du R1C1 absolu, donc =INDIRECT("R2C3",FALSE) lit C2 ; l'ancien code ignorait le drapeau, lisait « R2 » comme colonne R, ligne 2, et renvoyait en silence la mauvaise cellule. Le drapeau est dispatché sur son type variant (booléen, nombre ou texte) parce que convertir un variant chaîne directement en Double lève une exception. Un texte R1C1 relatif tel que R[1]C[1] renvoie #REF!, puisque INDIRECT n'a aucune cellule de formule origine contre laquelle le résoudre, et un texte A1 avec des caractères de trop, "B2 junk", renvoie #REF! lui aussi. YEARFRAC avec base 0 applique désormais les règles NASD de fin de février que DAYS360 implémentait déjà : quand les deux dates sont le dernier jour de février, le jour de fin devient 30, puis un début au dernier jour de février devient 30. Du 2024-02-29 au 2025-02-28, le décompte est désormais de 360 jours, une fraction d'exactement 1, là où l'ancien Days360US comptait 359

Que garantissent ces corrections, et quelle fut la leçon ?

Le comportement des chaînes de comparaisons est garanti par un test qui compare les deux moteurs avec des valeurs mesurées dans Excel 16, et ce test existe parce que la première description de la correction était fausse. La note de version v2.384.3 disait à l'origine que le repliage de gauche à droite rendait =1<2<3 TRUE, ce qui est précisément ce que produisait l'ancien analyseur associatif à droite et le contraire de ce que renvoient Excel et le nouveau code. Personne n'avait évalué l'exemple ; il était écrit depuis l'intuition que « 1 est inférieur à 2 est inférieur à 3 ». La note a été corrigée et le test à sept formules ajouté dans un commit de suivi, et la règle qui en est sortie vaut pour quiconque documente la sémantique des tableurs : faites tourner l'exemple dans Excel avant d'écrire la valeur attendue. La substitution d'opérande vide et le redimensionnement de SUMIF suivent le même comportement Excel, y compris le cas vide contre booléen depuis la v2.384.53, et les agrégats conditionnels qui doivent aussi sauter les lignes filtrées ou cachées suivent les règles séparées de l'article SUBTOTAL et AGGREGATE sur les lignes cachées

HotXLS est un composant tableur natif Delphi et C++Builder qui lit, recalcule et écrit XLS, XLSX, ODS et CSV sans Excel installé, et les règles de comparaison, de vide et de SUMIF décrites ici vivent dans le moteur de calcul que les deux architectures de classeur partagent. La liste complète des fonctions et les options de licence sont sur la page produit du composant tableur HotXLS pour Delphi