Technisch artikel

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. De HotXLS Delphi Component bewaart 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. Een Delphi-service schrijft een workbook weg, zijn formule-engine cachet 210 voor =SUM(A1:B1*{10,100}) in een cel met een gewone formule, 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 correct berekende formule?

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 evalueert op de dynamic-arraymanier, en het bestandsformaat moet dat ook zeggen. Met 1, 2, 3 en 4 in A1:B2 zijn dit de probe-formules en wat Excel 16 toont:

HotXLS-diagram dat implicit intersection en dynamic-array-evaluatie van SUM(A1:B1*{10,100}) in cel E5 vergelijkt: het legacy-model vindt geen cel van het horizontale bereik A1:B1 in kolom E en geeft #VALUE!, terwijl het dynamic-arraymodel 1 met 10 en 2 met 100 vermenigvuldigt en 210 oplevert
Excel plaatst @ in de gewone formule en toont #VALUE!, omdat impliciete intersectie niets in kolom E vindt; met de dynamic-arraymarkering van HotXLS vermenigvuldigt dezelfde formule elementsgewijs en landt op 210
FormuleHotXLS-resultaatExcel 16, opgeslagen als gewone formuleOpgeslagen door HotXLS
=SUM(A1:B1*{10,100})210#VALUE!Dynamic array, Excel toont 210
=SUM((A1:B2>2)*1)2Impliciete intersectie, verkeerd of foutDynamic array, Excel toont 2
=SUMPRODUCT((A1:B2>2)*1)2Impliciete intersectie, verkeerd of foutDynamic array, Excel toont 2
=MAX(A1:B2-1)3Impliciete intersectie, verkeerd of foutDynamic array, Excel toont 3
=SUM(A1:B2)1010Gewone 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

HotXLS-opslagdiagram voor de array-operatorformule SUM(A1:B1*{10,100}): de XLSX-engine schrijft een dynamic array over één cel met cm gelijk aan 1, een f-element van het type array en een XLDAPR-record in xl/metadata.xml waarvan de GUID in kleine letters verplicht is, terwijl de XLS-engine een FORMULA-record met PtgExp plus een ARRAY-record 0221 schrijft
De XLSX-engine markeert de cel met cm=1 plus een XLDAPR-metadatarecord en de klassieke engine koppelt een PtgExp-FORMULA aan een ARRAY-record over één cel; Excel 365 slaat dynamic arrays op dezelfde manier op in XLS

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:

uses
  SysUtils, Variants, lxCalc, lxHandle, lxHandleX;   // lxOk wordt gedeclareerd in 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;

    // 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

Hoe de rekenengine de waarden van Excel 365 matcht

De opslagmarkering verdient alleen zijn geld als de formule-engine sowieso al de waarden van Excel 365 teruggeeft, in beide engines. SUMPRODUCT is de meest zichtbare casus: een implementatie die alleen twee of meer kale bereiken accepteert geeft #N/A terug voor SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) en zelfs de één-argument-SUMPRODUCT(B1:B2). HotXLS evalueert expressie-argumenten 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. De intersectie-operator met spatie geeft de gemeenschappelijke rechthoek van twee referenties terug, 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. De parser accepteert inline arrayconstanten zoals {1,2;3,4} (komma’s scheiden kolommen, puntkomma’s rijen) en referentie-unies zoals (A1:B2,D4). Elementsgewijze vergelijkingen geven een leeg element ook het type van de andere kant, FALSE tegen een logical, passend bij de scalarregel die wordt beschreven in 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, TRUE telt als 1
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 accepteert @ tussen twee referenties als binaire intersectie en evalueert die vorm 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

Drie details die Excel controleert wanneer hij dynamic-arrayuitvoer opent

Excel zover krijgen dat hij de dynamic-arraymarkering accepteert komt neer op drie details die geen enkele self-round-trip-test vangt, want een library leest zijn eigen uitvoer hoe dan ook correct terug. Elk daarvan verschijnt pas wanneer u de uitvoer in Excel 16 opent en telkens één variabele vervangt:

  1. De extensie-GUID moet volledig in kleine letters. De ext uri in xl/metadata.xml moet exact {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3} zijn. Met wisselende hoofd- en kleine letters gespeld weigert Excel 16 het hele package te openen, niet alleen de cel
  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 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. Een naïeve conversie laat =TRUE*1 daarom -1 teruggeven en laat logische array-elementen als getallen classificeren, dus een vergelijking als (B1:B2>0)=TRUE gaat mis. HotXLS test op varBoolean voordat hij een Variant als getal behandelt 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:

TokenReference-klasseValue-klasseArray-klasse
PtgRef$24$44$64
PtgArea$25$45$65
PtgArray$20$40$60

Drie plekken in een BIFF8-writer zijn makkelijk verkeerd te doen, en elk levert een eigen symptoom op in Excel terwijl het in de eigen reader van de writer prima terugleest:

  • Arrayconstanten in de reference-klasse. Een encoder die de klasse uit de context kiest ziet dat parameters van SUM of ROWS reference-klasse zijn en schrijft =SUM({1,2}) met PtgArray als $20. Excel toont de hele formule als =#N/A. Een arrayconstante kan nooit een reference zijn, dus HotXLS schrijft de array-klasse $60 overal waar de context om een reference vraagt
  • Value-klasse-operands van PtgIsect en PtgUnion. Binaire operators van value-klasse-operands voorzien klopt voor * maar is verkeerd voor de reference-operators. Met $45-areas vóór PtgIsect ($0F) leest Excel =SUM(A1:B2 B1:B2) als =SUM(@A1:B2 @B1:B2) en geeft #VALUE! terug. HotXLS schrijft de operands van PtgIsect en PtgUnion ($10) in de reference-klasse, $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. Met $45 daar evalueert de arrayformule over één cel voor =SUM(A1:B1*{10,100}) in Excel tot 10. HotXLS promoveert daarom in de token-stream van een ARRAY-record elke value-klasse-referentie en arrayconstante naar de array-klasse, $65 en $60, wat Excel ook schrijft
HotXLS BIFF8-diagram: bits 5 en 6 van elke tokenbyte kiezen de reference-, value- of array-klasse, dus PtgArea spelt als 25, 45 en 65, met drie gefixte defecten: arrayconstanten als 20 toonden #N/A, PtgIsect-operands als 45 gaven #VALUE!, en ARRAY-record-operands als 45 lieten SUM(A1:B1*{10,100}) 10 teruggeven
Elke BIFF8-operand-token draagt zijn klasse in bits 5 en 6, en Excel vertrouwt die bits meer dan de structuur; HotXLS schrijft arrayconstanten als 60, PtgIsect-operands als 25, en promoveert ARRAY-record-tokens naar de array-klasse

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 slaat 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 één-cel-Formula / Value 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