HotXLS-arrayformules: waarom Excel @ en #VALUE! toevoegt

Excel 365 plaatst een @ in een formule zoals =SUM(A1:B1*{10,100}) en toont #VALUE! zodra het bestand haar als gewone formule opslaat, want in dat geval past Excel legacy implicit intersection toe op elke operand van een operator. Sinds v2.384.68 bewaart de HotXLS Delphi Component deze array-operatorformules zoals Excel 365 dat doet: als dynamic-arrayformules over één cel in XLSX en als arrayformules over één cel in XLS

Het symptoom overleeft de code review. Uw Delphi-service schrijft een workbook weg, HotXLS herberekent hem en cachet 210 voor =SUM(A1:B1*{10,100}), en de klant opent het bestand in Excel 16 en ziet =SUM(@A1:B1*@{10,100}) in de formulebalk staan en #VALUE! in de cel. Er is niets misvormd in het bestand. Wat ontbreekt is de metadata die Excel vertelt dat de formule volgens de dynamic-arrayregels is geschreven, en zonder die metadata valt Excel terug op zijn evaluatiemodel van vóór de dynamic arrays

Waarom voegt Excel 365 een @ toe aan een formule die HotXLS correct heeft berekend?

Excel 365 voegt @ toe omdat een formule zonder dynamic-arraymarkering per definitie een legacyformule is, en legacyformules reduceren een meer-cellig bereik tot één cel waar een operator een enkele waarde verwacht. Die reductie is impliciete intersectie: Excel pakt de cel van het bereik die dezelfde rij deelt als de formule (bij een verticaal bereik) of dezelfde kolom (bij een horizontaal bereik), en bestaat zo'n cel niet dan is het resultaat #VALUE!. Excel 365 houdt die betekenis aan voor formules in de oude stijl en toont @ om de reductie zichtbaar te maken

Zet =SUM(A1:B1*{10,100}) in E5 en de legacy-lezing spreekt voor zich. A1:B1 is een horizontaal bereik, de formule staat in kolom E, het bereik heeft geen cel in kolom E, dus @A1:B1 is #VALUE! en de hele SUM erft dat. Onder de dynamic-arrayregels vermenigvuldigt dezelfde tekst elementsgewijs, 1 × 10 + 2 × 100, en levert 210 op. De formule-engine van HotXLS rekende al sinds de releases v2.384.61 en v2.384.63 op de dynamic-arraymanier; het bestandsformaat zei dat alleen nog niet. Met 1, 2, 3 en 4 in A1:B2 zijn dit de probe-formules en wat Excel 16 toont:

Formule	HotXLS-resultaat	Excel 16, opgeslagen als gewone formule	Opgeslagen sinds v2.384.68
=SUM(A1:B1*{10,100})	210	#VALUE!	Dynamic array, Excel toont 210
=SUM((A1:B2>2)*1)	2	Impliciete intersectie, verkeerd of fout	Dynamic array, Excel toont 2
=SUMPRODUCT((A1:B2>2)*1)	2	Impliciete intersectie, verkeerd of fout	Dynamic array, Excel toont 2
=MAX(A1:B2-1)	3	Impliciete intersectie, verkeerd of fout	Dynamic array, Excel toont 3
=SUM(A1:B2)	10	10	Gewone formule, ongewijzigd

De laatste rij telt even zwaar als de eerste vier. SUM(A1:B2) geeft een bereik rechtstreeks door aan een functieparameter die references accepteert, dus geen enkele operator ziet ooit een meer-cellig bereik en intersectie kan niet plaatsvinden. Excel 365 zelf slaat die formule op als gewone formule, en HotXLS doet hetzelfde

Hoe HotXLS array-operatorformules opslaat in XLSX en XLS

HotXLS schrijft een array-operatorformule in XLSX als dynamic array over één cel: het element <c> draagt cm="1", de formule is <f t="array" ref="E5">, en het package krijgt er xl/metadata.xml bij met een metadata-type XLDAPR waarvan de extensie dynamicArrayProperties fDynamic="1" bevat. Het attribuut cm is een index vanaf 1 in het blok cellMetadata van dat onderdeel, en het record XLDAPR erachter is wat Excel vertelt: evalueer dit volgens de dynamic-arrayregels. Dit is dezelfde structuur die Excel 16 schrijft wanneer u dezelfde formule intypt en opslaat; zo is de doellayout in de eerste plaats ook vastgesteld

In XLS is er geen metadata-onderdeel, dus HotXLS gebruikt het enige construct dat BIFF8 voor array-evaluatie heeft: een arrayformule over één cel. De cel krijgt een FORMULA-record waarvan de token-stream uit één enkele PtgExp bestaat die naar zichzelf wijst, gevolgd door een ARRAY-record ($0221) met de echt geparseerde formule over het één-celbereik. Excel 365 schrijft dynamic-arrayformules op dezelfde manier naar XLS, en een oudere Excel-versie die het bestand leest ziet een klassieke Ctrl+Shift+Enter-arrayformule

Er komt geen nieuwe API aan te pas. De markering vindt plaats zodra u de formule via de normale cel-API toewijst, in beide engines. Aan de XLSX-kant is dat 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;

    // Operator over een bereik of inline array: opgeslagen als dynamic array
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // Bereik rechtstreeks aan een functie doorgegeven: blijft een gewone <f>
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

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

    // De array-root houdt zijn tekst zonder het voorafgaande '='
    Writeln(Sheet.Cells[5, 5].Formula);              // SUM(A1:B1*{10,100})

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

Na de conversie geeft TXLSXCell.Formula de tekst zonder = terug, dezelfde vorm die TXLSXRange.SetDynamicArrayFormula opslaat, dus code die na toewijzing formulestrings vergelijkt moet het voorafgaande = normaliseren

De klassieke engine volgt dezelfde regel via IXLSRange.Formula op een enkele cel. Bij toewijzing wordt de formule intern omgeleid naar het arraypad voor één cel, dus de opgeslagen XLS bevat het paar 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})';  // ARRAY-record
  Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)';         // ARRAY-record
  Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)';           // gewone FORMULA

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

Wilt u een meer-cellig resultaat verankeren in plaats van een scalaire aggregatie, dan blijven de expliciete APIs het juiste gereedschap: SetArrayFormula voor een van tevoren ingemaakte rechthoek, zoals beschreven bij dynamic array spill-formules met HotXLS, of TXLSXRange.SetDynamicArrayFormula als u de XLSX-dynamic-arraymarkering op een zelf ingemaakt bereik wilt. Het automatische pad in dit artikel dekt alleen formules die in één cel worden getypt

Welke formules markeert HotXLS als dynamic arrays?

HotXLS markeert een formule alleen wanneer een operator een operand-subtree heeft die een array oplevert. De controle draait op de gecompileerde syntaxtree, en een operand levert een array op als het een meer-cellig bereik is, een inline arrayconstante, of een andere operatorexpressie die zelf zo'n operand heeft. Haakjes zijn transparant. De operators die meetellen zijn de rekenkundige (+ - * / ^), concatenatie (&), de zes vergelijkingen, unaire plus en min, en percent:

• A1:B1*{10,100}, (A1:B2>2)*1, --(B1:B2>0) en A1:B2-1 worden gemarkeerd, waar ze ook in de formule voorkomen, ook binnen SUMPRODUCT
• SUM(A1:B2) en SUMPRODUCT(A1:A2,{1;10}) worden niet gemarkeerd, omdat het bereik en de array rechtstreeks in een functieargument gaan en geen enkele operator ze aanraakt
• A1*2 of SUM(A1,B1)*2 worden niet gemarkeerd: referenties naar één cel en functieresultaten zijn scalars voor deze controle

Drie grenzen zijn bewust gekozen. Ten eerste gebeurt de markering alleen als een formule via de API wordt ingevoerd, dus via TXLSXCell.Formula in de XLSX-engine en via toewijzing van Formula of Value op één cel in de klassieke engine. Formules die uit een bestand worden geladen, worden exact zoals gevonden teruggeschreven, want een legacyformule van een andere producent kan met opzet op impliciete intersectie vertrouwen. Ten tweede wordt tekst zonder : en zonder { overgeslagen zonder tweede compilatie. Ten derde wordt een formule die zou spillen, zoals =A1:B1*2 op zichzelf, gemarkeerd als dynamic array over één cel, verankerd waar u haar neerzet. HotXLS laat haar niet spillen, en Excel breidt het resultaat bij de volgende herberekening uit naar de buurcellen

Deze operandregel is het broertje van de argumentklasseregel die wordt behandeld bij impliciete intersectie voor defined names in HotXLS. Dat artikel gaat over functieparameters die als value class zijn gedeclareerd; dit artikel gaat over operators, die in het legacy-model altijd waarden eisen

Wat er in de rekenengine is veranderd om de resultaten te laten matchen

De opslagfix in v2.384.68 leunt op een formule-engine van HotXLS die al de waarden van Excel 365 terug gaf, wat nog enkele eerdere fixes in beide engines kostte. De meest zichtbare was SUMPRODUCT: tot v2.384.61 accepteerde hij alleen twee of meer kale bereiken, dus SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) en zelfs de één-argument SUMPRODUCT(B1:B2) gaven #N/A terug. HotXLS evalueert expressie-argumenten nu element voor element volgens de regels van Excel:

• elk argument moet exact dezelfde vorm hebben, waarbij een scalar als 1 × 1 telt, anders is het resultaat #VALUE!
• een foutwaarde in een argument wordt als resultaat teruggegeven
• tekst- en logische elementen tellen als 0, dus (B1:B2>0)*1 of -- is nog steeds nodig om TRUE in 1 om te zetten
• argumenten die allemaal kale bereiken zijn houden de originele streaming-loop, dus grote bereiken worden niet als arrays gerealiseerd

De SUM-familie (SUM, COUNT, AVERAGE, MIN, MAX, COUNTA) gebruikt dezelfde elementsgewijze evaluator zodra een argument een operatorexpressie over een bereik is, dus =SUM((B1:B2>0)*1) telt beide rijen in plaats van alleen naar de eerste cel te kijken. v2.384.62 zorgde ervoor dat de intersectie-operator met spatie de gemeenschappelijke rechthoek van twee referenties teruggeeft, met #NULL! als ze niet overlappen, dus =SUM(A1:B2 B1:B2) is 6 in plaats van 2 en het resultaat kan referentieparameters zoals ROWS en INDEX voeden. v2.384.63 voegde inline arrayconstanten zoals {1,2;3,4} (komma's scheiden kolommen, puntkomma's rijen) en referentie-unies zoals (A1:B2,D4) toe aan de parser. Elementsgewijze vergelijkingen geven een leeg element ook het type van de andere kant, FALSE tegen een logical, in overeenstemming met de scalarregel uit v2.384.53 die wordt beschreven bij vergelijkingsketens en lege cellen in HotXLS

var
  V: Variant;
begin
  // Book is de TXLSXWorkbook uit het eerste voorbeeld;
  // op zijn actieve werkblad staat A1:B2 = 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10, één argument
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6, gemeenschappelijk bereik B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16, overlap dubbel geteld
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1, was -1 vóór v2.384.61
end;

TXLSXWorkbook.Calculate evalueert een formulestring tegen het actieve werkblad zonder haar op te slaan, een snelle manier om het gedrag van de engine te checken. Eén waarschuwing over @ zelf: HotXLS heeft @ tussen twee referenties historisch geaccepteerd als binaire intersectie en evalueert die vorm nu met echte intersectiesemantiek. In Excel 365 is @ een unaire prefix voor impliciete intersectie. Schrijf @ niet in formuletekst en verwacht dan de betekenis van Excel; gebruik een spatie voor intersectie en laat de opslagregels hierboven de dynamic-arraysemantiek regelen

Waarom weigerde Excel het bestand te openen of berekende het een verkeerde waarde?

Excel zover krijgen dat hij de dynamic-arraymarkering accepteerde kostte drie fixes die geen enkele self-round-trip-test zou vangen, want HotXLS las zijn eigen output in elk geval correct. Elke fix werd gevonden door de output van HotXLS in Excel 16 te openen en telkens één variabele te vervangen:

1. De extensie-GUID moet volledig in kleine letters. De ext uri in xl/metadata.xml moet exact {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3} zijn. Een oudere HotXLS-template schreef haar met wisselende hoofd- en kleine letters, en Excel 16 weigerde het hele package te openen, niet alleen de cel. Workbooks die vóór v2.384.68 met TXLSXRange.SetDynamicArrayFormula waren aangemaakt hadden hetzelfde probleem
2. De tekst van de array-root draagt geen voorafgaande =. De XLSX-writer schrijft de opgeslagen tekst van een array-root letterlijk in <f>. Had de geconverteerde cel zijn = gehouden, dan zou het element er als <f t="array" ref="E5">=SUM(...)</f> uitzien, en ook dat wijst Excel bij het openen af. HotXLS stript hem tijdens de conversie, en daarom leest TXLSXCell.Formula hem ook niet terug
3. Double(True) is -1 in Delphi. Variantconversie volgt de COM-conventie waarbij TRUE alle bits op 1 heeft, en VarIsNumeric(True) geeft ook True terug. Vóór v2.384.61 liet dat =TRUE*1 -1 teruggeven en werden logische array-elementen als getallen geclassificeerd, dus een vergelijking als (B1:B2>0)=TRUE ging mis. HotXLS test nu op varBoolean voordat een Variant als getal wordt behandeld in scalarrekenkunde, arrayrekenkunde en de classificatie van array-elementen, en TRUE telt als 1

BIFF8-operandklassen: de details op byteniveau voor format-implementers

In BIFF8 draagt elke operand-token zijn operandklasse in de tokenbyte zelf, en Excel vertrouwt die klasse meer dan de structuur van de formule. [MS-XLS] definieert de klasse als een veld PtgDataType van twee bits in bits 5 en 6 van de token: 1 voor reference, 2 voor value, 3 voor array. De laagste vijf bits noemen de token, dus dezelfde area-reference heeft drie spellings:

Token	Reference-klasse	Value-klasse	Array-klasse
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

HotXLS had er drie hiervan op verschillende plekken verkeerd, en elk leverde een eigen symptoom op in Excel terwijl HotXLS hem prima teruglas:

• Arrayconstanten in de reference-klasse. De encoder koos de klasse uit de context, en parameters van SUM of ROWS zijn reference-klasse, dus =SUM({1,2}) werd geschreven met PtgArray als $20. Excel toont de hele formule als =#N/A. Een arrayconstante kan nooit een reference zijn, dus sinds v2.384.63 schrijft HotXLS de array-klasse $60 overal waar de context om een reference vraagt
• Value-klasse-operands van PtgIsect en PtgUnion. Binaire operators namen operands in de value-klasse, wat klopt voor * maar verkeerd is voor de reference-operators. Met $45-areas vóór PtgIsect ($0F) las Excel =SUM(A1:B2 B1:B2) als =SUM(@A1:B2 @B1:B2) en gaf #VALUE! terug. Sinds v2.384.62 worden de operands van PtgIsect en PtgUnion ($10) in de reference-klasse geschreven, $25
• Value-klasse-operands binnen het ARRAY-record. Excel past impliciete intersectie zelfs binnen een arrayformule toe zodra een operand in de value-klasse zit. HotXLS schreef daar $45, dus de arrayformule over één cel voor =SUM(A1:B1*{10,100}) evalueerde in Excel tot 10. Sinds v2.384.68 promoveert de token-stream van een ARRAY-record elke value-klasse-reference en arrayconstante naar de array-klasse, $65 en $60, precies wat Excel schrijft

Een reader die de klassebits negeert round-tript alle drie probleemloos, dus onderhoudt u uw eigen BIFF8-writer, vergelijk dan de klassebits van elke operand-token met een door Excel opgeslagen bestand van dezelfde formule, en niet alleen de tokencodes

Snelnaslag

• Excel 365 toont @ zodra een operator in een gewone, ongemarkeerde formule een meer-cellig bereik of inline array ontvangt
• HotXLS v2.384.68 en later slaan zulke formules op als XLSX-dynamic arrays over één cel (cm="1", t="array", XLDAPR-metadata) en als XLS-arrayformules over één cel (FORMULA met PtgExp plus ARRAY $0221)
• Alleen operator-operands tellen; een bereik dat rechtstreeks in een functieargument gaat, blijft een gewone formule
• Alleen formules die via TXLSXCell.Formula of de klassieke Formula / Value op één cel worden ingevoerd, worden gemarkeerd; geladen formules blijven onaangeroerd
• De geconverteerde rootcel leest terug zonder het voorafgaande =
• De ext uri-GUID van de dynamic array moet in kleine letters, anders wijst Excel het package af
• In Delphi is Double(True) -1; test op varBoolean vóór de numerieke conversie
• BIFF8: arrayconstanten nooit in de reference-klasse, operands van PtgIsect / PtgUnion in de reference-klasse, ARRAY-record-operands in de array-klasse

HotXLS leest, schrijft en berekent XLS- en XLSX-workbooks native vanuit Delphi en C++Builder, en slaat array-operatorformules zó op dat Excel 365 ze opent met dezelfde waarden die HotXLS heeft berekend. Zie de HotXLS Delphi spreadsheet component voor edities, documentatie en een proefdownload