Formules HotXLS : pourquoi Excel ajoute @ et #VALUE!

Excel 365 insère un @ dans une formule telle que =SUM(A1:B1*{10,100}) et affiche #VALUE! quand le fichier la stocke comme une formule ordinaire, parce qu’Excel applique alors l’intersection implicite héritée à chaque opérande d’opérateur. Depuis v2.384.68, HotXLS Delphi Component stocke ces formules à opérateurs matriciels comme le fait Excel 365 : en formules de tableau dynamique à cellule unique dans XLSX et en formules matricielles à une cellule dans XLS

Le symptôme survit à la revue de code. Votre service Delphi écrit un classeur, HotXLS le recalcule et met en cache 210 pour =SUM(A1:B1*{10,100}), et le client l’ouvre dans Excel 16 pour découvrir =SUM(@A1:B1*@{10,100}) dans la barre de formule et #VALUE! dans la cellule. Rien dans le fichier n’est malformé. Ce qui manque, c’est la métadonnée qui indique à Excel que la formule a été écrite sous les règles des tableaux dynamiques, et sans elle Excel retombe sur son modèle d’évaluation d’avant les tableaux dynamiques

Pourquoi Excel 365 ajoute-t-il un @ à une formule que HotXLS a calculée correctement ?

Excel 365 ajoute un @ parce qu’une formule sans marquage de tableau dynamique est, par définition, une formule héritée, et les formules héritées réduisent une plage multi-cellules à une cellule partout où un opérateur attend une valeur unique. Cette réduction, c’est l’intersection implicite : Excel prend la cellule de la plage qui partage la ligne de la formule (pour une plage verticale) ou la colonne (pour une plage horizontale), et si une telle cellule n’existe pas le résultat est #VALUE!. Excel 365 conserve ce sens pour les formules à l’ancienne et affiche le @ pour rendre la réduction visible

Posez =SUM(A1:B1*{10,100}) en E5 et la lecture héritée devient évidente. A1:B1 est une plage horizontale, la formule siège en colonne E, la plage n’a aucune cellule en colonne E, donc @A1:B1 vaut #VALUE! et tout le SUM en hérite. Sous les règles des tableaux dynamiques, le même texte multiplie élément par élément, 1 × 10 + 2 × 100, et renvoie 210. Le moteur de formules de HotXLS évaluait déjà à la manière des tableaux dynamiques depuis les versions v2.384.61 et v2.384.63 ; le format de fichier ne le disait simplement pas. Avec A1:B2 contenant 1, 2, 3 et 4, voici les formules sondes et ce qu’affiche Excel 16 :

Formule	Résultat HotXLS	Excel 16, stockée comme formule ordinaire	Stockage depuis v2.384.68
=SUM(A1:B1*{10,100})	210	#VALUE!	Tableau dynamique, Excel affiche 210
=SUM((A1:B2>2)*1)	2	Intersection implicite, faux ou erreur	Tableau dynamique, Excel affiche 2
=SUMPRODUCT((A1:B2>2)*1)	2	Intersection implicite, faux ou erreur	Tableau dynamique, Excel affiche 2
=MAX(A1:B2-1)	3	Intersection implicite, faux ou erreur	Tableau dynamique, Excel affiche 3
=SUM(A1:B2)	10	10	Formule ordinaire, inchangée

La dernière ligne compte autant que les quatre premières. SUM(A1:B2) passe une plage directement à un paramètre de fonction qui accepte des références, donc aucun opérateur ne voit jamais une plage multi-cellules et aucune intersection ne peut se produire. Excel 365 lui-même enregistre cette formule comme formule ordinaire, et HotXLS fait pareil

Comment HotXLS stocke les formules à opérateurs matriciels dans XLSX et XLS

HotXLS écrit une formule à opérateurs matriciels dans XLSX comme un tableau dynamique à cellule unique : l’élément <c> porte cm="1", la formule est <f t="array" ref="E5">, et le paquet gagne un xl/metadata.xml avec un type de métadonnée XLDAPR dont l’extension contient dynamicArrayProperties fDynamic="1". L’attribut cm est un index base 1 dans le bloc cellMetadata de cette partie, et l’enregistrement XLDAPR derrière lui est ce qui dit à Excel « évalue ceci sous les règles des tableaux dynamiques ». C’est la même structure qu’écrit Excel 16 quand vous tapez la même formule et sauvegardez, et c’est ainsi qu’a été établie à l’origine la disposition cible

Dans XLS, il n’y a pas de partie de métadonnées, donc HotXLS utilise le seul constructe dont dispose BIFF8 pour l’évaluation matricielle : une formule matricielle à une cellule. La cellule reçoit un enregistrement FORMULA dont le flux de tokens est un unique PtgExp pointant sur elle-même, suivi d’un enregistrement ARRAY ($0221) portant la vraie formule analysée sur la plage d’une cellule. Excel 365 écrit les formules de tableau dynamique dans XLS de la même façon, et une version plus ancienne d’Excel lisant le fichier voit une formule matricielle classique Ctrl+Shift+Enter

Aucune nouvelle API n’entre en jeu. Le marquage se produit quand vous affectez la formule par l’API de cellule normale, dans les deux moteurs. Côté XLSX, il s’agit de TXLSXCell.Formula :

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    Sheet.Cells[1, 1].Value := 1;
    Sheet.Cells[1, 2].Value := 2;
    Sheet.Cells[2, 1].Value := 3;
    Sheet.Cells[2, 2].Value := 4;

    // Opérateur sur une plage ou un tableau littéral : stocké en tableau dynamique
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // Plage passée directement à une fonction : reste un <f> ordinaire
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

    if Book.Recalculate = lxOk then
      Writeln(VarToStr(Sheet.Cells[5, 5].Value));   // 210

    // La racine du tableau garde son texte sans le '=' de tête
    Writeln(Sheet.Cells[5, 5].Formula);              // SUM(A1:B1*{10,100})

    Book.SaveAs('probe.xlsx');   // E5 et E6 reçoivent cm="1" + t="array"
  finally
    Book.Free;
  end;
end;

Après la conversion, TXLSXCell.Formula renvoie le texte sans le =, la même forme que stocke TXLSXRange.SetDynamicArrayFormula, donc un code qui compare des chaînes de formules après affectation devrait normaliser le = de tête

Le moteur classique suit la même règle via IXLSRange.Formula sur une cellule unique. L’affectation de la formule la réaiguille en interne vers le chemin matriciel à une cellule, donc le XLS sauvegardé contient la paire FORMULA plus ARRAY :

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['A1', 'A1'].Value := 1;
  Sh.Range['B1', 'B1'].Value := 2;
  Sh.Range['A2', 'A2'].Value := 3;
  Sh.Range['B2', 'B2'].Value := 4;

  Sh.Range['E5', 'E5'].Formula := '=SUM(A1:B1*{10,100})';  // enregistrement ARRAY
  Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)';         // enregistrement ARRAY
  Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)';           // FORMULA ordinaire

  Writeln(VarToStr(Sh.Range['E5', 'E5'].Value));   // 210
  Writeln(VarToStr(Sh.Range['E6', 'E6'].Value));   // 3
  Wb.SaveAs('probe.xls');
end;

Si vous ancrez un résultat multi-cellules plutôt qu’un agrégat scalaire, les API explicites restent le bon outil : SetArrayFormula pour un rectangle prédimensionné, comme décrit dans les formules de débordement de tableau dynamique avec HotXLS, ou TXLSXRange.SetDynamicArrayFormula quand vous voulez le marquage tableau dynamique XLSX sur une plage que vous dimensionnez vous-même. Le chemin automatique de cet article ne couvre que les formules saisies dans une seule cellule

Quelles formules HotXLS marque-t-il comme tableaux dynamiques ?

HotXLS ne marque une formule que quand un opérateur a un sous-arbre d’opérande qui produit un tableau. La vérification tourne sur l’arbre syntaxique compilé, et un opérande produit un tableau s’il s’agit d’une plage multi-cellules, d’une constante de tableau littérale, ou d’une autre expression d’opérateur qui possède elle-même un tel opérande. Les parenthèses sont transparentes. Les opérateurs qui comptent sont les arithmétiques (+ - * / ^), la concaténation (&), les six comparaisons, le plus et le moins unaires, et le pourcentage :

• A1:B1*{10,100}, (A1:B2>2)*1, --(B1:B2>0) et A1:B2-1 sont marquées, où qu’elles apparaissent dans la formule, y compris dans SUMPRODUCT
• SUM(A1:B2) et SUMPRODUCT(A1:A2,{1;10}) ne sont pas marquées, parce que la plage et le tableau entrent directement dans un argument de fonction et qu’aucun opérateur ne les touche
• A1*2 ou SUM(A1,B1)*2 ne sont pas marquées : les références à cellule unique et les résultats de fonction sont des scalaires pour cette vérification

Trois frontières sont délibérées. D’abord, le marquage ne se produit que quand une formule est saisie via l’API, c’est-à-dire TXLSXCell.Formula dans le moteur XLSX et une affectation Formula ou Value à cellule unique dans le moteur classique. Les formules chargées depuis un fichier sont réécrites exactement telles qu’elles ont été trouvées, car une formule héritée d’un autre producteur peut dépendre volontairement de l’intersection implicite. Ensuite, un texte ne contenant ni : ni { est sauté sans seconde compilation. Enfin, une formule qui déborderait, telle que =A1:B1*2 seule, est marquée comme tableau dynamique à cellule unique ancré là où vous l’avez posée. HotXLS ne la fait pas déborder, et Excel étendra le résultat aux cellules voisines au prochain recalcul

Cette règle d’opérande est la sœur de la règle de classe d’argument traitée dans l’intersection implicite des noms définis dans HotXLS. Cet article-là parle des paramètres de fonction déclarés en classe valeur ; celui-ci parle des opérateurs, qui dans le modèle hérité exigent toujours des valeurs

Ce qui a changé dans le moteur de calcul pour que les résultats coïncident

Le correctif de stockage de v2.384.68 repose sur le fait que le moteur de formules de HotXLS renvoyait déjà les valeurs d’Excel 365, ce qui a demandé plusieurs correctifs antérieurs dans les deux moteurs. Le plus visible concernait SUMPRODUCT : jusqu’à v2.384.61 il n’acceptait que deux plages ordinaires ou plus, donc SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) et même SUMPRODUCT(B1:B2) à un seul argument renvoyaient #N/A. HotXLS évalue désormais les arguments expression élément par élément avec les règles d’Excel :

• chaque argument doit avoir exactement la même forme, un scalaire comptant pour 1 × 1, sinon le résultat est #VALUE!
• une valeur d’erreur à l’intérieur de n’importe quel argument est renvoyée comme résultat
• les éléments texte et logiques comptent pour 0, donc (B1:B2>0)*1 ou -- reste nécessaire pour transformer TRUE en 1
• les arguments qui sont toutes des plages ordinaires gardent la boucle de streaming d’origine, si bien que les grandes plages ne sont pas matérialisées en tableaux

La famille SUM (SUM, COUNT, AVERAGE, MIN, MAX, COUNTA) utilise le même évaluateur élément par élément quand un argument est une expression d’opérateur sur une plage, donc =SUM((B1:B2>0)*1) compte les deux lignes au lieu de regarder seulement la première cellule. v2.384.62 a fait retourner à l’opérateur d’intersection par espace le rectangle commun de deux références, avec #NULL! quand elles ne se chevauchent pas, donc =SUM(A1:B2 B1:B2) vaut 6 plutôt que 2 et le résultat peut alimenter des paramètres de référence comme ROWS et INDEX. v2.384.63 a ajouté à l’analyseur les constantes de tableau littérales comme {1,2;3,4} (les virgules séparent les colonnes, les points-virgules les lignes) et les unions de références comme (A1:B2,D4). Les comparaisons élément par élément donnent aussi à un élément vide le type de l’autre côté, FALSE contre un logique, dans la ligne de la règle scalaire de v2.384.53 décrite dans les chaînes de comparaison et les cellules vides dans HotXLS

var
  V: Variant;
begin
  // Book est le TXLSXWorkbook du premier exemple ;
  // sa feuille active contient A1:B2 = 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10, un seul argument
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6, plage commune B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16, chevauchement compté deux fois
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1, valait -1 avant v2.384.61
end;

TXLSXWorkbook.Calculate évalue une chaîne de formule contre la feuille active sans la stocker, un moyen rapide de vérifier le comportement du moteur. Une précaution à propos du @ lui-même : HotXLS a historiquement accepté le @ entre deux références comme une intersection binaire, et il évalue désormais cette forme avec une vraie sémantique d’intersection. Dans Excel 365, le @ est un préfixe unaire d’intersection implicite. N’écrivez pas de @ dans le texte d’une formule en comptant sur le sens d’Excel ; utilisez un espace pour l’intersection et laissez les règles de stockage ci-dessus gérer la sémantique de tableau dynamique

Pourquoi Excel refusait-il d’ouvrir le fichier ou calculait-il une mauvaise valeur ?

Faire accepter le marquage de tableau dynamique à Excel a demandé trois correctifs qu’aucun test aller-retour sur ses propres fichiers n’aurait attrapés, car HotXLS relisait correctement sa propre sortie dans tous les cas. Chacun a été trouvé en ouvrant la sortie de HotXLS dans Excel 16 et en remplaçant une variable à la fois :

1. Le GUID d’extension doit être tout en minuscules. Le ext uri dans xl/metadata.xml doit être exactement {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. Un ancien modèle HotXLS l’épelait en casse mixte, et Excel 16 refusait d’ouvrir tout le paquet, pas seulement la cellule. Les classeurs créés avec TXLSXRange.SetDynamicArrayFormula avant v2.384.68 avaient le même problème
2. Le texte de la racine du tableau ne porte pas de = de tête. L’écrivain XLSX émet le texte stocké d’une racine de tableau verbatim dans <f>. Si la cellule convertie gardait son =, l’élément lirait <f t="array" ref="E5">=SUM(...)</f>, qu’Excel rejette aussi à l’ouverture. HotXLS le retire pendant la conversion, c’est pourquoi TXLSXCell.Formula se relit sans lui
3. Double(True) vaut -1 en Delphi. La conversion Variant suit la convention COM où TRUE a tous les bits à 1, et VarIsNumeric(True) renvoie True également. Avant v2.384.61, cela faisait renvoyer -1 à =TRUE*1 et laissait les éléments logiques de tableaux être classés comme nombres, si bien qu’une comparaison comme (B1:B2>0)=TRUE se trompait. HotXLS teste désormais varBoolean avant de traiter un Variant comme un nombre dans l’arithmétique scalaire, l’arithmétique de tableaux et la classification d’éléments de tableaux, et TRUE compte pour 1

Classes d’opérandes BIFF8 : les détails au niveau de l’octet pour les implémenteurs de format

Dans BIFF8, chaque token d’opérande porte sa classe d’opérande dans l’octet du token lui-même, et Excel fait plus confiance à cette classe qu’à la structure de la formule. [MS-XLS] définit la classe comme un champ PtgDataType de deux bits dans les bits 5 et 6 du token : 1 pour référence, 2 pour valeur, 3 pour tableau. Les cinq bits bas nomment le token, donc la même référence de zone a trois épellations :

Token	Classe référence	Classe valeur	Classe tableau
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

HotXLS s’est trompé sur trois d’entre elles à des endroits différents, et chacune a produit un symptôme distinct dans Excel tout en se relisant bien dans HotXLS :

• Constantes de tableau en classe référence. L’encodeur choisissait la classe selon le contexte, et les paramètres de SUM ou ROWS sont en classe référence, donc =SUM({1,2}) était écrit avec PtgArray en $20. Excel affiche toute la formule comme =#N/A. Une constante de tableau ne peut jamais être une référence, donc depuis v2.384.63 HotXLS écrit la classe tableau $60 partout où le contexte demande une référence
• Opérandes en classe valeur de PtgIsect et PtgUnion. Les opérateurs binaires prenaient des opérandes en classe valeur, ce qui est juste pour * mais faux pour les opérateurs de référence. Avec des zones en $45 devant PtgIsect ($0F), Excel lisait =SUM(A1:B2 B1:B2) comme =SUM(@A1:B2 @B1:B2) et renvoyait #VALUE!. Depuis v2.384.62, les opérandes de PtgIsect et PtgUnion ($10) sont écrits en classe référence, $25
• Opérandes en classe valeur dans l’enregistrement ARRAY. Excel applique l’intersection implicite même à l’intérieur d’une formule matricielle quand un opérande est en classe valeur. HotXLS y écrivait $45, donc la formule matricielle à une cellule de =SUM(A1:B1*{10,100}) s’évaluait à 10 dans Excel. Depuis v2.384.68, le flux de tokens d’un enregistrement ARRAY promeut toute référence en classe valeur et toute constante de tableau en classe tableau, $65 et $60, ce qu’écrit Excel

Un lecteur qui ignore les bits de classe fait l’aller-retour des trois sans broncher, donc si vous maintenez votre propre écrivain BIFF8, comparez les bits de classe de chaque token d’opérande à un fichier sauvegardé par Excel de la même formule, pas seulement aux numéros de tokens

Aide-mémoire

• Excel 365 affiche un @ quand un opérateur dans une formule ordinaire non marquée reçoit une plage multi-cellules ou un tableau littéral
• HotXLS v2.384.68 et ultérieur stocke ces formules en tableaux dynamiques XLSX à cellule unique (cm="1", t="array", métadonnées XLDAPR) et en formules matricielles XLS à une cellule (FORMULA avec PtgExp plus ARRAY $0221)
• Seuls les opérandes d’opérateurs comptent ; une plage passée directement à un argument de fonction reste une formule ordinaire
• Seules les formules saisies via TXLSXCell.Formula ou le Formula / Value classique à cellule unique sont marquées ; les formules chargées ne sont pas touchées
• La cellule racine convertie se relit sans le = de tête
• Le GUID ext uri du tableau dynamique doit être en minuscules sinon Excel rejette le paquet
• En Delphi, Double(True) vaut -1 ; testez varBoolean avant la conversion numérique
• BIFF8 : constantes de tableau jamais en classe référence, opérandes de PtgIsect / PtgUnion en classe référence, opérandes de l’enregistrement ARRAY en classe tableau

HotXLS lit, écrit et calcule des classeurs XLS et XLSX nativement depuis Delphi et C++Builder, et stocke les formules à opérateurs matriciels pour qu’Excel 365 les ouvre avec les mêmes valeurs que HotXLS a calculées. Voir le composant tableur HotXLS Delphi pour les éditions, la documentation et un essai à télécharger