Maticové vzorce v HotXLS: prečo Excel pridáva @ a #VALUE!

Excel 365 vloží do vzorca ako =SUM(A1:B1*{10,100}) znak @ a ukáže #VALUE!, keď je vo súbore uložený ako obyčajný vzorec, pretože Excel potom na každý operand operátora aplikuje starú implicitnú intersekciu. Od v2.384.68 ukladá HotXLS Delphi Component tieto vzorce s operátormi nad poliami tak ako Excel 365: v XLSX ako dynamic array vzorce jednej bunky a v XLS ako maticové vzorce jednej bunky

Tento príznak prežije aj code review. Vaša Delphi služba zapíše zošit, HotXLS ho prepočíta a pri =SUM(A1:B1*{10,100}) uloží do cache 210 a zákazník ho otvorí v Exceli 16, kde vidí v riadku vzorcov =SUM(@A1:B1*@{10,100}) a v bunke #VALUE!. Nič v súbore nie je nijak pokazené. Chýbajú metadáta, ktoré Excelu hovoria, že vzorec vznikol pod pravidlami dynamic array, a bez nich Excel spadne späť na svoj model vyhodnocovania z čias pred dynamic array

Prečo Excel 365 pridáva @ do vzorca, ktorý HotXLS spočítal správne?

Excel 365 pridáva @ preto, lebo vzorec bez označenia dynamic array je z definície legacy vzorec a legacy vzorce redukujú viacbunkový rozsah na jednu bunku všade tam, kde operátor očakáva jedinú hodnotu. Táto redukcia je implicitná intersekcia: Excel vezme bunku rozsahu zdieľajúcu riadok vzorca (pri zvislom rozsahu) alebo stĺpec (pri vodorovnom rozsahu) a ak taká bunka neexistuje, výsledkom je #VALUE!. Excel 365 túto interpretáciu pri starých vzorcoch zachováva a zobrazuje @, aby bola redukcia viditeľná

Vložte =SUM(A1:B1*{10,100}) do E5 a legacy čítanie je náhle zrejmé. A1:B1 je vodorovný rozsah, vzorec sedí v stĺpci E, rozsah nemá v stĺpci E žiadnu bunku, takže @A1:B1 je #VALUE! a celé SUM to zdedí. Pod pravidlami dynamic array ten istý text násobí po prvkoch, 1 × 10 + 2 × 100, a vráti 210. Vzorcový engine HotXLS počítal spôsobom dynamic array od vydaní v2.384.61 a v2.384.63; formát súboru to proste nehovoril. Keď A1:B2 drží 1, 2, 3 a 4, toto sú testovacie vzorce a to, čo zobrazí Excel 16:

Vzorec	Výsledok HotXLS	Excel 16, uložené ako obyčajný vzorec	Ukladané od v2.384.68
=SUM(A1:B1*{10,100})	210	#VALUE!	Dynamic array, Excel ukáže 210
=SUM((A1:B2>2)*1)	2	Implicitná intersekcia, zle alebo chyba	Dynamic array, Excel ukáže 2
=SUMPRODUCT((A1:B2>2)*1)	2	Implicitná intersekcia, zle alebo chyba	Dynamic array, Excel ukáže 2
=MAX(A1:B2-1)	3	Implicitná intersekcia, zle alebo chyba	Dynamic array, Excel ukáže 3
=SUM(A1:B2)	10	10	Obyčajný vzorec, bez zmeny

Posledný riadok je rovnako dôležitý ako prvé štyri. SUM(A1:B2) podá rozsah priamo parametru funkcie, ktorý prijíma referencie, takže žiaden operátor nikdy nevidí viacbunkový rozsah a intersekcia nemôže nastať. Excel 365 sám ukladá tento vzorec ako obyčajný vzorec a HotXLS robí to isté

Ako HotXLS ukladá vzorce s operátormi nad poliami v XLSX a XLS

HotXLS zapisuje vzorec s operátorom nad poľom v XLSX ako dynamic array v jednej bunke: element <c> nesie cm="1", vzorec je <f t="array" ref="E5"> a balík získa xl/metadata.xml s typom metadát XLDAPR, ktorého rozšírenie drží dynamicArrayProperties fDynamic="1". Atribút cm je index od jedničky do bloku cellMetadata tejto časti a záznam XLDAPR za ním je to, čo Excelu hovorí „počítaj to pod pravidlami dynamic array“. Je to tá istá štruktúra, ktorú Excel 16 zapíše, keď ten istý vzorec napíšete a uložíte, a presne tak bol cieľový layout vôbec zistený

V XLS žiadna metadata part neexistuje, takže HotXLS použije jediný konštrukt, ktorý BIFF8 pre maticové vyhodnotenie má: maticový vzorec v jednej bunke. Bunka dostane záznam FORMULA, ktorého token stream je jediný PtgExp ukazujúci sám na seba, nasledovaný záznamom ARRAY ($0221) nesúcim skutočný rozparsovaný vzorec cez rozsah jednej bunky. Excel 365 zapisuje vzorce dynamic array do XLS rovnakým spôsobom a staršia verzia Excelu vidí pri čítaní súboru klasický maticový vzorec cez Ctrl+Shift+Enter

Nie je pri tom žiadne nové API. Označenie sa vykoná, keď vzorec priradíte cez normálne bunkové API, v oboch engineoch. Na strane XLSX je to 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;

    // Operátor nad rozsahom alebo inline poľom: uložené ako dynamic array
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // Rozsah podaný priamo funkcii: ostáva obyčajný <f>
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

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

    // Koreň poľa si ponechá text bez úvodného '='
    Writeln(Sheet.Cells[5, 5].Formula);              // SUM(A1:B1*{10,100})

    Book.SaveAs('probe.xlsx');   // E5 a E6 dostanú cm="1" + t="array"
  finally
    Book.Free;
  end;
end;

Po konverzii vracia TXLSXCell.Formula text bez =, v tej istej forme, v akej ho ukladá TXLSXRange.SetDynamicArrayFormula, takže kód, ktorý po priradení porovnáva reťazce vzorcov, by mal znormalizovať úvodné =

Klasický engine nasleduje to isté pravidlo cez IXLSRange.Formula na jednej bunke. Priradenie vzorca ho interne presmeruje na maticovú cestu jednej bunky, takže uložené XLS obsahuje pár 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})';  // záznam ARRAY
  Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)';         // záznam ARRAY
  Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)';           // obyčajná FORMULA

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

Ak ukotvujete viacbunkový výsledok, nie skalárny agregát, správnym nástrojom stále zostávajú explicitné API: SetArrayFormula pre vopred určený obdĺžnik, ako popisuje článok o dynamic array spill vzorcoch s HotXLS, alebo TXLSXRange.SetDynamicArrayFormula, keď chcete označenie XLSX dynamic array na rozsahu, ktorý si určíte sami. Automatická cesta z tohto článku pokrýva len vzorce napísané do jednej bunky

Ktoré vzorce označí HotXLS ako dynamic array?

HotXLS označí vzorec iba vtedy, keď má operátor podstrom operandu, ktorý produkuje pole. Kontrola beží na skompilovanom syntaktickom strome a operand produkuje pole, ak je to viacbunkový rozsah, inline konštanta poľa alebo iný operátorový výraz, ktorý má sám taký operand. Zátvorky sú priehľadné. Operátory, ktoré sa počítajú, sú aritmetické (+ - * / ^), zreťazenie (&), šesť porovnaní, unárne plus a mínus a percento:

• A1:B1*{10,100}, (A1:B2>2)*1, --(B1:B2>0) a A1:B2-1 sa označia, ktorokoľvek sa vo vzorci objavia, vrátane vnútra SUMPRODUCT
• SUM(A1:B2) a SUMPRODUCT(A1:A2,{1;10}) sa neoznačia, lebo rozsah aj pole idú priamo do argumentu funkcie a žiadny operátor sa ich nedotkne
• A1*2 alebo SUM(A1,B1)*2 sa neoznačia: referencie na jednu bunku a výsledky funkcií sú pre túto kontrolu skaláre

Tri hranice sú zámerné. Po prvé, označenie nastáva len vtedy, keď vzorec vstupuje cez API, teda TXLSXCell.Formula v engine XLSX a jedno-bunkové priradenie Formula alebo Value v klasickom engine. Vzorce načítané zo súboru sa zapisujú späť presne tak, ako boli nájdené, lebo legacy vzorec od iného producenta môže na implicitnej intersekcií závisieť zámerne. Po druhé, text, ktorý neobsahuje ani :, ani {, sa preskočí bez druhého prekladu. Po tretie, vzorec, ktorý by sa rozlieval (spill), ako je =A1:B1*2 sám o sebe, sa označí ako dynamic array jednej bunky ukotvený tam, kde ste ho vložili. HotXLS ho nerozlieva a Excel pri ďalšom prepočte rozšíri výsledok do susedných buniek

Toto pravidlo operandu je súrodenec pravidla triedy argumentov, ktoré popisuje článok o implicitnej intersekcií pre defined names v HotXLS. Ten článok je o funkčných parametroch deklarovaných ako value class; tento je o operátoroch, ktoré v legacy modeli vždy vyžadujú hodnoty

Čo sa zmenilo vo výpočtovom engine, aby si výsledky sadli

Oprava ukladania vo v2.384.68 stojí na tom, že vzorcový engine HotXLS už vracia hodnoty Excelu 365, čo si vyžiadalo viacero skorších opráv v oboch engineoch. Najviditeľnejšia bola SUMPRODUCT: do v2.384.61 prijímala len dva a viac obyčajných rozsahov, takže SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) a dokonca jednargumentová SUMPRODUCT(B1:B2) vracali #N/A. HotXLS teraz vyhodnocuje výrazové argumenty prvok po prvku pod Excelovými pravidlami:

• každý argument musí mať presne rovnaký tvar, skalár sa počíta ako 1 × 1, inak je výsledkom #VALUE!
• chybová hodnota vo vnútri ktoréhokoľvek argumentu sa vráti ako výsledok
• textové a logické prvky sa počítajú ako 0, takže (B1:B2>0)*1 alebo -- je stále potrebné, aby sa TRUE zmenilo na 1
• argumenty, ktoré sú všetky obyčajné rozsahy, držia pôvodnú streamovaciu slučku, takže veľké rozsahy sa nematerializujú do polí

Rodina SUM (SUM, COUNT, AVERAGE, MIN, MAX, COUNTA) používa ten istý prvkový evaluator, keď je argumentom operátorový výraz nad rozsahom, takže =SUM((B1:B2>0)*1) spočíta oba riadky namiesto pozretia sa len na prvú bunku. v2.384.62 prinútila operátor priesečníka medzerou vracať spoločný obdĺžnik dvoch referencií, s #NULL!, keď sa neprekrývajú, takže =SUM(A1:B2 B1:B2) je 6 a nie 2 a výsledok môže živiť referenčné parametre ako ROWS a INDEX. v2.384.63 pridala do parsera inline konštanty polí ako {1,2;3,4} (čiarky oddeľujú stĺpce, bodkočiarky riadky) a zjednotenia referencií ako (A1:B2,D4). Prvkové porovnania dávajú prázdnému prvku typ druhej strany, FALSE proti logickému prvku, čo sedí so skalárnym pravidlom z v2.384.53 popísaným v článku o porovnávacích reťazcoch a prázdnych bunkách v HotXLS

var
  V: Variant;
begin
  // Book je TXLSXWorkbook z prvého príkladu;
  // jeho aktívny hárok drží A1:B2 = 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10, jediný argument
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6, spoločný rozsah B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16, prekrytie počítané dvakrát
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1, pred v2.384.61 bolo -1
end;

TXLSXWorkbook.Calculate vyhodnotí reťazec vzorca proti aktívnemu hárku bez ukladania, rýchly spôsob, ako si overiť, ako sa engine správa. Jedna výstraha k samotnému @: HotXLS historicky prijímal @ medzi dvomi referenciami ako binárnu intersekciu a teraz túto formu vyhodnocuje s pravou priesečníkovou sémantikou. V Exceli 365 je @ unárny prefix implicitnej intersekcie. Nepíšte @ do textu vzorca v očakávaní Excelového významu; na intersekciu použite medzeru a nechajte úložné pravidlá vyššie vybaviť sémantiku dynamic array

Prečo Excel odmietol súbor otvoriť alebo spočítal zlú hodnotu?

Donútiť Excel prijať označenie dynamic array stálo tri opravy, ktoré by žiadny round-trip test nad vlastným výstupom nechytil, lebo HotXLS čítal svoj vlastný výstup v každom prípade správne. Každú odhalilo otvorenie výstupu HotXLS v Exceli 16 a vymena jednej premennej naraz:

1. GUID rozšírenia musí byť celý malými písmenami. ext uri v xl/metadata.xml musí byť presne {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. Staršia šablóna HotXLS ho hláskovala zmiešane veľkými a malými písmenami a Excel 16 odmietol otvoriť celý balík, nie len bunku. Zošity vytvorené cez TXLSXRange.SetDynamicArrayFormula pred v2.384.68 mali ten istý problém
2. Text koreňa poľa nemá žiadne úvodné =. XLSX zapisovač emituje uložený text koreňa poľa doslova do <f>. Keby konvertovaná bunka nechala svoje =, element by čítal <f t="array" ref="E5">=SUM(...)</f>, čo Excel tiež odmietne pri otváraní. HotXLS ho pri konverzii odstrihne, preto TXLSXCell.Formula číta späť bez neho
3. Double(True) je v Delphi -1. Konverzia Variant nasleduje konvenciu COM, kde TRUE znamená všetky bity nastavené, a VarIsNumeric(True) tiež vracia True. Pred v2.384.61 to robilo =TRUE*1 rovné -1 a nechalo logické prvky polí klasifikovať ako čísla, takže porovnanie ako (B1:B2>0)=TRUE išlo zle. HotXLS teraz testuje varBoolean, než považuje Variant za číslo v skalárnej aritmetike, aritmetike polí a klasifikácii prvkov polí, a TRUE sa počíta ako 1

Triedy operandov BIFF8: detaily na úrovni bajtov pre implementátorov formátu

V BIFF8 nesie každý token operandu svoju triedu operandu priamo v tokenovom bajte a Excel tej triede verí viac než štruktúre vzorca. [MS-XLS] definuje triedu ako dvojbitové pole PtgDataType v bitoch 5 a 6 tokenu: 1 pre referenciu, 2 pre hodnotu, 3 pre pole. Dolných päť bitov menuje token, takže rovnaká area referencia má tri hláskovania:

Token	Trieda referencie	Trieda hodnoty	Trieda poľa
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

HotXLS mal tri z nich zle na rôznych miestach a každá zlá položka spôsobila v Exceli iný príznak, zatiaľ čo čítanie späť v HotXLS bolo vždy v poriadku:

• Konštanty polí v triede referencie. Enkoder si triedu vybral z kontextu a parametre SUM alebo ROWS sú trieda referencie, takže =SUM({1,2}) sa zapísalo s PtgArray ako $20. Excel zobrazí celý vzorec ako =#N/A. Konštanta poľa nemôže byť nikdy referenciou, takže od v2.384.63 zapisuje HotXLS triedu poľa $60 všade tam, kde kontext žiada referenciu
• Operandy PtgIsect a PtgUnion v triede hodnoty. Binárne operátory brali operandy triedy hodnoty, čo sedí pri *, ale pri referenčných operátoroch je to zle. S area $45 pred PtgIsect ($0F) čítal Excel =SUM(A1:B2 B1:B2) ako =SUM(@A1:B2 @B1:B2) a vrátil #VALUE!. Od v2.384.62 sa operandy PtgIsect a PtgUnion ($10) zapisujú v triede referencie, $25
• Operandy v triede hodnoty vnútri záznamu ARRAY. Excel aplikuje implicitnú intersekciu aj vnútri maticového vzorca, keď je operand v triede hodnoty. HotXLS tam zapisoval $45, takže maticový vzorec jednej bunky pre =SUM(A1:B1*{10,100}) vyšiel v Exceli ako 10. Od v2.384.68 povyšuje token stream záznamu ARRAY každú referenciu v triede hodnoty a konštantu poľa na triedu poľa, $65 a $60, presne tak, ako zapisuje Excel

Čítačka, ktorá ignoruje triedové bity, preženie round-trip všetkých troch bez reptania, takže ak si udržiavate vlastný BIFF8 zapisovač, porovnávajte triedové bity každého tokenového operandu so súborom uloženým Excelom pre ten istý vzorec, nielen čísla tokenov

Rýchly prehľad

• Excel 365 ukáže @, keď operátor v obyčajnom neoznačenom vzorci dostane viacbunkový rozsah alebo inline pole
• HotXLS od v2.384.68 ukladá také vzorce v XLSX ako dynamic array jednej bunky (cm="1", t="array", metadáta XLDAPR) a v XLS ako maticové vzorce jednej bunky (FORMULA s PtgExp plus ARRAY $0221)
• Počítajú sa len operandy operátorov; rozsah podaný priamo do argumentu funkcie ostáva obyčajným vzorcom
• Označia sa len vzorce vložené cez TXLSXCell.Formula alebo klasické jedno-bunkové Formula / Value; načítané vzorce sa nijak nedotknú
• Konvertovaná koreňová bunka sa načíta späť bez úvodného =
• GUID ext uri pre dynamic array musí byť malými písmenami, inak Excel balík odmietne
• V Delphi je Double(True) -1; pred číselnou konverziou testujte varBoolean
• BIFF8: konštanty polí nikdy nie v triede referencie, operandy PtgIsect / PtgUnion v triede referencie, operandy záznamu ARRAY v triede poľa

HotXLS číta, zapisuje a počíta zošity XLS a XLSX natívne z Delphi a C++Builder a ukladá vzorce s operátormi nad poliami tak, aby ich Excel 365 otvoril s tými istými hodnotami, aké spočítal HotXLS. Edície, dokumentáciu a skúšobnú verziu na stiahnutie nájdete na stránke tabuľkového komponentu HotXLS pre Delphi