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:
| Formule | HotXLS-resultaat | Excel 16, opgeslagen als gewone formule | Opgeslagen door HotXLS |
|---|---|---|---|
=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:
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)enA1:B2-1worden gemarkeerd, waar ze ook in de formule voorkomen, ook binnen SUMPRODUCTSUM(A1:B2)enSUMPRODUCT(A1:A2,{1;10})worden niet gemarkeerd, omdat het bereik en de array rechtstreeks in een functieargument gaan en geen enkele operator ze aanraaktA1*2ofSUM(A1,B1)*2worden 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)*1of--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:
- De extensie-GUID moet volledig in kleine letters. De
ext uriinxl/metadata.xmlmoet 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 - 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 leestTXLSXCell.Formulahem niet terug Double(True)is -1 in Delphi. Variantconversie volgt de COM-conventie waarbij TRUE alle bits op 1 heeft, enVarIsNumeric(True)geeft ook True terug. Een naïeve conversie laat=TRUE*1daarom -1 teruggeven en laat logische array-elementen als getallen classificeren, dus een vergelijking als(B1:B2>0)=TRUEgaat mis. HotXLS test opvarBooleanvoordat 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:
| Token | Reference-klasse | Value-klasse | Array-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})metPtgArrayals$20. Excel toont de hele formule als=#N/A. Een arrayconstante kan nooit een reference zijn, dus HotXLS schrijft de array-klasse$60overal waar de context om een reference vraagt - Value-klasse-operands van
PtgIsectenPtgUnion. Binaire operators van value-klasse-operands voorzien klopt voor*maar is verkeerd voor de reference-operators. Met$45-areas vóórPtgIsect($0F) leest Excel=SUM(A1:B2 B1:B2)als=SUM(@A1:B2 @B1:B2)en geeft#VALUE!terug. HotXLS schrijft de operands vanPtgIsectenPtgUnion($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
$45daar 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,$65en$60, wat Excel ook 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 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 metPtgExpplus ARRAY$0221) - Alleen operator-operands tellen; een bereik dat rechtstreeks in een functieargument gaat, blijft een gewone formule
- Alleen formules die via
TXLSXCell.Formulaof de klassieke één-cel-Formula/Valueworden 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 opvarBooleanvóór de numerieke conversie - BIFF8: arrayconstanten nooit in de reference-klasse, operands van
PtgIsect/PtgUnionin 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