Article technique

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

Un contrôle de plage écrit =0<B2<100 renvoie FALSE dans Excel sur chaque ligne, y compris la ligne où B2 porte 50. Excel évalue d'abord 0<B2, obtient TRUE, puis demande si TRUE est inférieur à 100 ; une valeur logique se classe au-dessus de chaque nombre, donc la réponse est FALSE quel que soit le contenu de B2. Les cellules vides et SUMIF ont des surprises du même genre : une cellule vide égale 0, "" et FALSE à la fois, et =SUMIF(A1:A10,">5",B1) additionne dix cellules de la colonne B

Un moteur de calcul qui recalcule un tel classeur hors Excel doit reproduire ces réponses, même quand elles ne sont visiblement pas ce que l'auteur voulait dire. Le résultat en cache stocké dans le fichier est ce qu'une visionneuse, un aperçu de mail ou un import en aval voit avant que quiconque n'appuie sur F9. Si le serveur écrit TRUE pour le contrôle de plage et qu'Excel affiche plus tard FALSE, le même rapport contient deux nombres différents selon l'endroit où il a été ouvert, et personne ne peut dire lequel croire. Corriger la formule de l'auteur est le travail de l'auteur

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

Excel renvoie FALSE pour =1<2<3 parce que les six opérateurs de comparaison partagent un seul niveau de précédence et s'associent de gauche à droite, donc la formule signifie (1<2)<3, et le TRUE interne perd ensuite un concours de classement de types contre le nombre 3. HotXLS Delphi Component analyse les chaînes de comparaisons de la même façon dans ses deux moteurs de classeur, le classique TXLSWorkbook et le TXLSXWorkbook natif XLSX. Le parseur lit un opérande, et tant que le jeton suivant est =, <>, <, >, <= ou >=, il construit un nœud de comparaison avec tout ce qui est déjà analysé comme enfant gauche et l'opérande suivant comme enfant droit, puis emporte ce nœud dans le tour suivant. C'est la même forme de boucle qu'il emploie pour +, - et &

Le piège siège dans la grammaire des manuels. Écrite expr = operand op expr, une grammaire à descente récursive se récurse dans elle-même pour le côté droit et rend l'opérateur associatif à droite en silence. Sous cette lecture =1<2<3 devient 1<(2<3), soit 1<TRUE, soit TRUE. L'erreur court dans les deux sens : =3>2>1 vaut TRUE dans Excel et FALSE sous associativité à droite, et =1=1=TRUE bascule pareil. Les formes parenthèsées sortent justes dans les deux cas, donc la faute se glisse sous tout test qui ne vérifie que (a<b)<c

La méthode Calculate évalue une chaîne de formule sans la poser dans une cellule, ce qui en fait un moyen rapide de vérifier toute une liste de chaînes contre les deux moteurs. La méthode est présentée plus en profondeur dans la vue d'ensemble du moteur de formules HotXLS

uses
  SysUtils, Variants, lxCalc, lxHandle, lxHandleX;   // lxOk est déclaré dans lxCalc

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');
  // Les réponses d'Excel :  FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE
var
  Classic: IXLSWorkbook;
  Xlsx: TXLSXWorkbook;
  i: Integer;
begin
  Classic := TXLSWorkbook.Create;   // à comptage de références via l'interface
  Xlsx := TXLSXWorkbook.Create;
  try
    // TXLSXWorkbook.Calculate évalue contre la feuille active et
    // renvoie Null quand le classeur n'a pas encore de feuille
    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;
Deux arbres d'analyse pour =1<2<3 : une analyse associative à droite évalue 1<(2<3) en TRUE, tandis que le pliage de gauche à droite évalue (1<2)<3 en FALSE, car Excel classe chaque nombre sous tout texte et toute valeur texte sous un booléen
Les deux moteurs HotXLS plient les chaînes de comparaisons de gauche à droite — un booléen domine chaque nombre, donc TRUE perdant contre 3 est exactement ce qui rend le contrôle de plage chaîné FALSE

Nombres, textes et booléens : le classement entre types

Quand les deux côtés d'une comparaison ont des types différents, Excel ne convertit pas l'un dans l'autre. Il classe les types à la place : chaque nombre est inférieur à toute valeur texte, et toute valeur texte est inférieure à tout booléen. HotXLS classe les deux opérandes en nombre, texte ou booléen et, quand les classes diffèrent, compare les seules classes. Donc =TRUE>99999 vaut TRUE, ="1">2 vaut TRUE, et =FALSE>"zzz" vaut TRUE aussi. C'est la pièce qui fait paraître arbitraires les comparaisons chaînées. TRUE<3 n'est pas TRUE converti en 1 puis comparé à 3 ; c'est un booléen comparé à un nombre, et le booléen gagne

À l'intérieur d'une classe, la comparaison est la naturelle, avec une torsion pour le texte : la comparaison de chaînes ignore la casse, donc ="abc"="ABC" renvoie TRUE dans Excel comme dans HotXLS. Les dates sont des numéros de série pour le moteur, ce qui veut dire qu'une date se classe toujours sous tout texte, y compris une cellule texte qui ressemble seulement à une date. Une colonne de dates importées où la moitié des cellules sont arrivées en chaînes se triera et se comparera en deux bandes distinctes, et =A2>DATE(2026,1,1) renvoie TRUE pour chacune des cellules chaînes

À quoi une cellule vide est-elle égale dans une comparaison ?

Une cellule vide employée comme opérande de comparaison prend le type de l'autre côté : elle égale 0 contre un nombre, "" contre du texte et FALSE contre une valeur logique. Avec A1 vide, =A1=0, =A1="" et =A1=FALSE sont tous TRUE dans Excel. HotXLS fait la même substitution avant de classer les deux opérandes, pour les six opérateurs de comparaison et dans les deux moteurs. Quand les deux opérandes sont vides, aucune substitution n'a lieu, et ils comparent égaux

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;                  // la première feuille devient l'active
  Sh.Range['B1', 'B1'].Value := 5;      // A1 est laissée vide exprès

  Writeln(VarToStr(Wb.Calculate('=A1=0')));      // True
  Writeln(VarToStr(Wb.Calculate('=A1=""')));     // True
  Writeln(VarToStr(Wb.Calculate('=A1=FALSE')));  // True
  Writeln(VarToStr(Wb.Calculate('=A1<B1')));     // True : la vide compare comme 0
  Writeln(VarToStr(Wb.Calculate('=A1<0')));      // False : 0 n'est pas sous 0
end;

La dernière ligne est celle qui mord en pratique. L'arithmétique traite déjà une vide comme 0, donc =A1+1 donne 1 dans n'importe quel moteur. La comparaison est l'endroit où le raccourci tourne mal. Un moteur qui garde « vide » comme son propre rang le plus bas, sous chaque nombre, répond TRUE à =A1<0, et alors =IF(A1<0,"overdrawn","ok") étiquette chaque cellule de solde vide comme à découvert. Substituer seulement 0 ou la chaîne vide ne suffit pas non plus : contre un booléen la vide deviendrait 0, qui se classe sous TRUE et FALSE, et =A1=FALSE sortirait FALSE sur une cellule vide

Substitution d'opérande vide HotXLS : une A1 vide compare égale à 0 et au texte vide, une vide contre un booléen compare comme FALSE donc =A1=FALSE vaut TRUE comme dans Excel, tandis qu'une vide gardée au rang le plus bas rendrait =A1<0 TRUE pour chaque solde vide
La substitution épouse le type de l'autre opérande, 0, la chaîne vide ou FALSE — un classement Null brut est ce qui étiquetterait chaque solde vide à découvert

Il y a une conséquence qui mérite d'être anticipée. Comme une vide compare FALSE contre une valeur logique, =A1=FALSE ne peut pas distinguer une cellule vide d'une cellule portant FALSE, dans Excel comme dans HotXLS, et =A1=0 ne peut pas distinguer vide de zéro. Les modèles qui doivent savoir si quelqu'un a réellement saisi une valeur devraient tester avec ISBLANK(A1). La forme =A1="" est plus faible, car elle est aussi TRUE pour une formule qui renvoie une chaîne vide

Pourquoi SUMIF avec une plage de somme à une cellule couvre-t-il toute la colonne ?

Excel garde la forme de la plage de critères et n'emploie la plage de somme que pour sa cellule supérieure gauche, donc =SUMIF(A1:A10,">5",B1) signifie B1:B10. Bien des modèles bâtis à la main comptent sur ce raccourci sans que leurs auteurs le sachent. L'implémentation évidente, qui bride la marche au plus petit des deux plages, réduit l'exemple à un seul test de A1 contre B1 et renvoie 0. HotXLS marche la plage de critères et lit chaque valeur au même décalage de ligne et colonne depuis le coin supérieur gauche de la plage de somme. AVERAGEIF passe par la même marche et reçoit 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

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
    Sheet.Cells[3, 4].Formula := '=AVERAGEIF(A1:A10,">5",B1)';  // même redimensionnement
    if Book.Recalculate = lxOk then
      // D1 et D2 : 4000 (600+700+800+900+1000) ; D3 : 800
      Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ',
        VarToStr(Sheet.Cells[2, 4].Value), ' ', VarToStr(Sheet.Cells[3, 4].Value));
  finally
    Book.Free;
  end;
end;
HotXLS SUMIF et AVERAGEIF redimensionnent là où =SUMIF(A1:A10,">5",B1) marche la plage de critères de dix lignes en lisant B1 à B10 aux décalages correspondants pour un résultat de 4000, là où brider à la plage de somme à une cellule renverrait 0
Excel n'emprunte que le coin supérieur gauche de la plage de somme et garde la forme des critères, donc un modèle qui passe B1 veut dire B1:B10 — SUMIF et AVERAGEIF marchent les dix décalages et rognent une plage surdimensionnée pareil

Pour les modèles que vous écrivez vous-même, épellez la plage de somme complète. Le moteur honore le raccourci parce que des fichiers existants en dépendent, mais B1 dans une formule se lit comme une cellule unique pour la prochaine personne qui maintiendra la feuille, et cette personne supposera raisonnablement qu'un seul montant est additionné. Le critère du milieu est évalué comme valeur unique tandis que les deux arguments extérieurs sont des références ; cette scission entre classes d'arguments est expliquée dans l'article sur l'intersection implicite et les classes d'arguments. Les totaux conditionnels qui doivent en plus sauter les lignes filtrées ou cachées suivent d'autres règles encore, traitées dans l'article sur SUBTOTAL et AGGREGATE et les lignes cachées

Styles de référence INDIRECT et base 0 de YEARFRAC

Deux fonctions plus petites portent le même genre de piège. INDIRECT prend un second argument optionnel, a1, et quand il est FALSE le texte est une référence R1C1 : =INDIRECT("R2C3",FALSE) lit C2. Un parseur qui ignore le drapeau lit « R2 » comme colonne R, ligne 2, ce qui est une vraie cellule, donc la formule renvoie une valeur plausible depuis le mauvais endroit. HotXLS lit le drapeau comme booléen, nombre (zéro signifie FALSE) ou texte « FALSE », et convertit le texte R1C1 avant de le résoudre, les décalages entre crochets comme R[1]C[1] étant mesurés depuis la cellule qui porte la formule. Un texte avec quelque chose en trop après une référence valide, comme "B2 junk", renvoie #REF! au lieu d'employer B2 en silence

YEARFRAC avec base 0 emploie le décompte 30/360 US (NASD), y compris les règles de fin de février qu'applique DAYS360 : quand les deux dates sont le dernier jour de février, le jour de fin devient 30, puis un départ au dernier jour de février devient 30. Du 2024-02-29 au 2025-02-28, cela donne 360 jours et une fraction d'exactement 1. Un décompte 30/360 simple qui saute ces règles donne 359 jours et 0,99722, et la différence surgit dans les accumulations d'intérêts qui enjambent la fin de février

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['C2', 'C2'].Value := 'row 2, column 3';

  Writeln(VarToStr(Wb.Calculate('=INDIRECT("R2C3",FALSE)')));   // row 2, column 3
  Writeln(VarToStr(Wb.Calculate('=ISERROR(INDIRECT("B2 junk"))')));  // True: #REF!
  Writeln(VarToStr(Wb.Calculate(
    '=YEARFRAC(DATE(2024,2,29),DATE(2025,2,28),0)')));           // 1
end;

Quand vous documentez ou testez un comportement pareil, prenez les valeurs attendues dans Excel lui-même et non dans ce que la formule semble dire. « 1 est inférieur à 2 est inférieur à 3 » suggère que =1<2<3 vaut TRUE, qui est précisément la réponse d'un parseur associatif à droite et l'opposé de ce qu'Excel renvoie. Une petite table de formules avec les valeurs qu'Excel montre à côté attrape les quatre cas de cet article : chaînes, vides, redimensionnement SUMIF et décompte de février

HotXLS lit, recalcule et écrit XLS, XLSX, ODS et CSV depuis Delphi et C++Builder sans Excel sur la machine, et les règles de comparaison, de cellule vide et d'agrégats conditionnels ci-dessus appartiennent au moteur de calcul que les deux classes de classeur partagent. La liste des fonctions prises en charge, les éditions et les licences sont sur la page du HotXLS Delphi spreadsheet component