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. 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. Un service Delphi écrit un classeur, son moteur de formules met en cache 210 pour =SUM(A1:B1*{10,100}) dans une cellule à formule ordinaire, 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 correctement calculée ?
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 évalue à la manière des tableaux dynamiques, et le format de fichier doit aussi le dire. 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 par HotXLS |
|---|---|---|---|
=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 :
uses
SysUtils, Variants, lxCalc, lxHandle, lxHandleX; // lxOk est déclaré dans lxCalc
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)etA1:B2-1sont marquées, où qu’elles apparaissent dans la formule, y compris dans SUMPRODUCTSUM(A1:B2)etSUMPRODUCT(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 toucheA1*2ouSUM(A1,B1)*2ne 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
Comment le moteur de calcul aligne ses valeurs sur Excel 365
Le marquage de stockage ne vaut que si le moteur de formules renvoie d’emblée les valeurs d’Excel 365, et ce dans les deux moteurs. SUMPRODUCT est le cas le plus visible : une implémentation qui n’accepte que deux plages ordinaires ou plus renvoie #N/A pour SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) et même SUMPRODUCT(B1:B2) à un seul argument. HotXLS évalue 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)*1ou--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. L’opérateur d’intersection par espace renvoie 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. L’analyseur accepte 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, conformément à la règle scalaire 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, TRUE compte pour 1
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 accepte le @ entre deux références comme une intersection binaire et évalue 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
Trois détails qu’Excel vérifie quand il ouvre une sortie tableau dynamique
Faire accepter le marquage de tableau dynamique à Excel tient à trois détails qu’aucun test aller-retour sur ses propres fichiers n’attrape, car une bibliothèque relit correctement sa propre sortie dans tous les cas. Chacun ne se manifeste qu’au moment où vous ouvrez la sortie dans Excel 16 en remplaçant une variable à la fois :
- Le GUID d’extension doit être tout en minuscules. Le
ext uridansxl/metadata.xmldoit être exactement{bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. Épelez-le en casse mixte et Excel 16 refuse d’ouvrir tout le paquet, pas seulement la cellule - 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 pourquoiTXLSXCell.Formulase relit sans lui Double(True)vaut -1 en Delphi. La conversion Variant suit la convention COM où TRUE a tous les bits à 1, etVarIsNumeric(True)renvoie True également. Une conversion naïve fait donc renvoyer -1 à=TRUE*1et laisse les éléments logiques de tableaux être classés comme nombres, si bien qu’une comparaison comme(B1:B2>0)=TRUEse trompe. HotXLS testevarBooleanavant 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 |
Trois endroits d’un écrivain BIFF8 sont faciles à rater, et chacun produit un symptôme distinct dans Excel tout en se relisant très bien dans le lecteur de l’écrivain lui-même :
- Constantes de tableau en classe référence. Un encodeur qui choisit la classe selon le contexte voit que les paramètres de SUM ou ROWS sont en classe référence et écrit
=SUM({1,2})avecPtgArrayen$20. Excel affiche toute la formule comme=#N/A. Une constante de tableau ne peut jamais être une référence, donc HotXLS écrit la classe tableau$60partout où le contexte demande une référence - Opérandes en classe valeur de
PtgIsectetPtgUnion. Donner à des opérateurs binaires des opérandes en classe valeur est juste pour*mais faux pour les opérateurs de référence. Avec des zones en$45devantPtgIsect($0F), Excel lit=SUM(A1:B2 B1:B2)comme=SUM(@A1:B2 @B1:B2)et renvoie#VALUE!. HotXLS écrit les opérandes dePtgIsectetPtgUnion($10) 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. Avec
$45à cet endroit, la formule matricielle à une cellule de=SUM(A1:B1*{10,100})s’évalue à 10 dans Excel. HotXLS promeut donc, dans le flux de tokens d’un enregistrement ARRAY, toute référence en classe valeur et toute constante de tableau en classe tableau,$65et$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 stocke ces formules en tableaux dynamiques XLSX à cellule unique (
cm="1",t="array", métadonnéesXLDAPR) et en formules matricielles XLS à une cellule (FORMULA avecPtgExpplus 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.Formulaou leFormula/Valueclassique à 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 uridu tableau dynamique doit être en minuscules sinon Excel rejette le paquet - En Delphi,
Double(True)vaut -1 ; testezvarBooleanavant la conversion numérique - BIFF8 : constantes de tableau jamais en classe référence, opérandes de
PtgIsect/PtgUnionen 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