HotXLS-arrayformler: Hvorfor Excel indsætter @ og #VALUE!

Excel 365 indsætter @ i en formel som =SUM(A1:B1*{10,100}) og viser #VALUE!, når filen gemmer den som en almindelig formel, fordi Excel så anvender legacy implicit intersection på hver operator-operand. Siden v2.384.68 gemmer HotXLS Delphi Component disse array-operatorformler på samme måde som Excel 365: som dynamiske array-formler i én celle i XLSX og som én-celle-array-formler i XLS

Symptomet overlever en code review. Din Delphi-service skriver en workbook, HotXLS genberegner den og cacher 210 for =SUM(A1:B1*{10,100}), og kunden åbner den i Excel 16 og finder =SUM(@A1:B1*@{10,100}) i formellinjen og #VALUE! i cellen. Intet i filen er misdannet. Det, der mangler, er den metadata, der fortæller Excel, at formlen er skrevet under dynamic-array-reglerne; uden den falder Excel tilbage på den evalueringsmodel, den brugte før dynamic arrays

Hvorfor tilføjer Excel 365 @ til en formel, HotXLS har beregnet korrekt?

Excel 365 tilføjer @, fordi en formel uden dynamic-array-markering per definition er en legacy-formel, og legacy-formler reducerer et område med flere celler til én celle, overalt hvor en operator forventer en enkelt værdi. Den reduktion er implicit intersection: Excel tager den celle af området, der deler formlens række (for et lodret område) eller kolonne (for et vandret område), og findes der ingen sådan celle, bliver resultatet #VALUE!. Excel 365 bevarer den betydning for legacy-formler og viser @ for at gøre reduktionen synlig

Sæt =SUM(A1:B1*{10,100}) i E5, og den legacy-læsning bliver åbenlys. A1:B1 er et vandret område, formlen står i kolonne E, området har ingen celle i kolonne E, så @A1:B1 bliver #VALUE!, og hele SUM arver den. Under dynamic-array-reglerne ganger den samme tekst element for element, 1 × 10 + 2 × 100, og returnerer 210. HotXLS' formelmotor har evalueret på dynamic-array-måden siden udgivelserne v2.384.61 og v2.384.63; filformatet sagde det bare ikke. Med A1:B2 indeholdende 1, 2, 3 og 4 er dette probe-formlerne, og hvad Excel 16 viser:

HotXLS-diagram, der sammenligner implicit intersection og dynamic-array-evaluering af SUM(A1:B1*{10,100}) i cellen E5: legacy-modellen finder ingen celle af det vandrette område A1:B1 i kolonne E og returnerer #VALUE!, mens dynamic-array-modellen ganger 1 med 10 og 2 med 100 og returnerer 210 — Excel indsætter @ i den almindelige formel og viser #VALUE!, fordi implicit intersection ikke finder noget i kolonne E; med HotXLS' dynamic-array-markering ganger den samme formel element for element og lander på 210

Formel	HotXLS-resultat	Excel 16, gemt som almindelig formel	Gemt siden v2.384.68
`=SUM(A1:B1*{10,100})`	210	`#VALUE!`	Dynamisk array, Excel viser 210
`=SUM((A1:B2>2)*1)`	2	Implicit intersection, forkert eller fejl	Dynamisk array, Excel viser 2
`=SUMPRODUCT((A1:B2>2)*1)`	2	Implicit intersection, forkert eller fejl	Dynamisk array, Excel viser 2
`=MAX(A1:B2-1)`	3	Implicit intersection, forkert eller fejl	Dynamisk array, Excel viser 3
`=SUM(A1:B2)`	10	10	Almindelig formel, uændret

Den sidste række betyder lige så meget som de fire første. SUM(A1:B2) sender et område direkte videre til en funktionsparameter, der accepterer referencer, så ingen operator ser et område med flere celler, og ingen intersection kan ske. Excel 365 selv gemmer den formel som en almindelig formel, og HotXLS gør det samme

Sådan gemmer HotXLS array-operatorformler i XLSX og XLS

HotXLS skriver en array-operatorformel i XLSX som et dynamisk array i én enkelt celle: <c>-elementet bærer cm="1", formlen er <f t="array" ref="E5">, og pakken får xl/metadata.xml med en XLDAPR-metadatatype, hvis udvidelse indeholder dynamicArrayProperties fDynamic="1". Attributten cm er et 1-baseret indeks ind i den dels cellMetadata-blok, og XLDAPR-posten bag den er det, der fortæller Excel "evaluer denne under dynamic-array-reglerne". Det er samme struktur, Excel 16 skriver, når du taster den samme formel og gemmer, og dét er i øvrigt sådan, mållayoutet blev fastlagt

I XLS er der ingen metadatadel, så HotXLS bruger den eneste konstruktion, BIFF8 har til array-evaluering: en én-celle-array-formel. Cellen får en FORMULA-record, hvis tokenstream er en enkelt PtgExp, der peger på cellen selv, efterfulgt af en ARRAY-record ($0221) med den rigtige parsede formel over områdets ene celle. Excel 365 skriver dynamiske array-formler til XLS på samme måde, og en ældre Excel-version, der læser filen, ser en klassisk Ctrl+Shift+Enter-arrayformel

HotXLS-lagringsdiagram for array-operatorformlen SUM(A1:B1*{10,100}): XLSX-motoren skriver et dynamisk array i én celle med cm lig 1, et f-element af typen array og en XLDAPR-post i xl/metadata.xml, hvor GUID'en med små bogstaver er påkrævet, mens XLS-motoren skriver en FORMULA-record med PtgExp plus en ARRAY-record 0221 — XLSX-motoren markerer cellen med cm=1 plus en XLDAPR-metadatapost, og den klassiske motor parer en PtgExp-FORMULA med en ARRAY-record over én celle; Excel 365 gemmer dynamiske arrays i XLS på samme måde

Ingen ny API er involveret. Markeringen sker, når du tildeler formlen via det normale celle-API, i begge motorer. På XLSX-siden er det 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 et område eller et inline array: gemmes som dynamisk array
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // Område sendt direkte til en funktion: forbliver en almindelig <f>
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

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

    // Array-roden beholder sin tekst uden det foranstillede '='
    Writeln(Sheet.Cells[5, 5].Formula);              // SUM(A1:B1*{10,100})

    Book.SaveAs('probe.xlsx');   // E5 og E6 får cm="1" + t="array"
  finally
    Book.Free;
  end;
end;

Efter konverteringen returnerer TXLSXCell.Formula teksten uden =, samme form som TXLSXRange.SetDynamicArrayFormula gemmer, så kode, der sammenligner formelstrenge efter tildeling, bør normalisere det foranstillede =

Den klassiske motor følger samme regel gennem IXLSRange.Formula på en enkelt celle. Tildeling af formlen viderestiller den internt til én-celle-array-vejen, så den gemte XLS indeholder FORMULA plus ARRAY-parret:

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)';           // almindelig FORMULA

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

Skal du forankre et resultat med flere celler frem for et skalar-aggregat, er de eksplicitte API'er stadig det rigtige værktøj: SetArrayFormula til et foruddimensioneret rektangel, som beskrevet i dynamiske array-spill-formler med HotXLS, eller TXLSXRange.SetDynamicArrayFormula, når du vil have XLSX-dynamic-array-markeringen på et område, du selv dimensionerer. Den automatiske vej i denne artikel dækker kun formler, der tastes i én celle

Hvilke formler markerer HotXLS som dynamiske arrays?

HotXLS markerer kun en formel, når en operator har en operand-subtræ, der producerer et array. Tjekket kører på det kompilerede syntakstræ, og en operand producerer et array, hvis den er et område med flere celler, en inline array-konstant eller et andet operatorudtryk, der selv har en sådan operand. Parenteser er transparente. De operatorer, der tæller, er de aritmetiske (+ - * / ^), konkatenation (&), de seks sammenligninger, unær plus og minus samt procent:

A1:B1*{10,100}, (A1:B2>2)*1, --(B1:B2>0) og A1:B2-1 markeres, uanset hvor de optræder i formlen, også inde i SUMPRODUCT
SUM(A1:B2) og SUMPRODUCT(A1:A2,{1;10}) markeres ikke, fordi området og arrayet går direkte ind i et funktionsargument, og ingen operator rører dem
A1*2 eller SUM(A1,B1)*2 markeres ikke: referencer til én enkelt celle og funktionsresultater er skalarer for dette tjek

Tre grænser er bevidste. For det første sker markeringen kun, når en formel indtastes via API'et, altså TXLSXCell.Formula i XLSX-motoren og en enkelt-celle-Formula- eller Value-tildeling i den klassiske motor. Formler, der indlæses fra en fil, skrives tilbage præcis, som de blev fundet, fordi en legacy-formel fra en anden producent kan være afhængig af implicit intersection med vilje. For det andet springes tekst, der hverken indeholder : eller {, over uden en ekstra kompilering. For det tredje markeres en formel, der ville spill'e, som =A1:B1*2 på egen hånd, som et dynamisk array i én celle, forankret dér, hvor du satte den. HotXLS lader den ikke spille, og Excel udvider resultatet til nabocellerne, næste gang det genberegner

Denne operandregel er søster til argumentklasse-reglen, der er dækket i implicit intersection for defined names i HotXLS. Den artikel handler om funktionsparametre, der er deklareret som value-klasse; denne handler om operatorer, som i legacy-modellen altid kræver værdier

Hvad ændrede sig i beregningsmotoren, så resultaterne kom til at matche

Lagringsfixet i v2.384.68 bygger på, at HotXLS' formelmotor allerede returnerede Excel 365-værdier, hvilket krævede adskillige tidligere fixes i begge motorer. Den mest synlige var SUMPRODUCT: indtil v2.384.61 accepterede den kun to eller flere rene områder, så SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) og selv argumentet-én SUMPRODUCT(B1:B2) returnerede #N/A. HotXLS evaluerer nu udtryksargumenter element for element med Excels regler:

alle argumenter skal have nøjagtigt samme form, en skalar tæller som 1 × 1, ellers er resultatet #VALUE!
en fejlværdi inde i et argument returneres som resultatet
tekst- og logiske elementer tæller som 0, så (B1:B2>0)*1 eller -- stadig skal til for at gøre TRUE til 1
argumenter, der alle er rene områder, beholder den oprindelige streaming-løkke, så store områder materialiseres ikke som arrays

SUM-familien (SUM, COUNT, AVERAGE, MIN, MAX, COUNTA) bruger den samme elementvise evaluator, når et argument er et operatorudtryk over et område, så =SUM((B1:B2>0)*1) tæller begge rækker i stedet for kun at kigge på den første celle. v2.384.62 fik space intersection-operatoren til at returnere det fælles rektangel af to referencer, med #NULL!, når de ikke overlapper, så =SUM(A1:B2 B1:B2) er 6 frem for 2, og resultatet kan føres videre til referenceparametre som ROWS og INDEX. v2.384.63 tilføjede inline array-konstanter som {1,2;3,4} (kommaer adskiller kolonner, semikolonner rækker) og reference-unioner som (A1:B2,D4) til parseren. Elementvise sammenligninger giver også et tomt element den anden sides type, FALSE mod en logisk værdi, i overensstemmelse med skalareglen fra v2.384.53, beskrevet i sammenligningskæder og tomme celler i HotXLS

var
  V: Variant;
begin
  // Book er TXLSXWorkbook'et fra det første eksempel;
  // dets aktive ark indeholder A1:B2 = 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10, kun ét argument
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6, fælles område B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16, overlap tælles to gange
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1, var -1 før v2.384.61
end;

TXLSXWorkbook.Calculate evaluerer en formelstreng mod det aktive ark uden at gemme den, en hurtig måde at tjekke motorens opførsel på. Én advarsel om @ selv: HotXLS har historisk accepteret @ mellem to referencer som en binær intersection, og den form evalueres nu med ægte intersection-semantik. I Excel 365 er @ et unært implicit-intersection-præfiks. Skriv ikke @ ind i formelteksten og forvent Excels betydning; brug et mellemrum til intersection, og lad lagringsreglerne ovenfor håndtere dynamic-array-semantikken

Hvorfor nægtede Excel at åbne filen eller beregnede den forkerte værdi?

At få Excel til at acceptere dynamic-array-markeringen krævede tre fixes, som ingen selv-rundtur-test ville fange, fordi HotXLS læste sit eget output korrekt i alle tilfælde. Hver enkelt blev fundet ved at åbne HotXLS-output i Excel 16 og udskifte én variabel ad gangen:

Udvidelsens GUID skal være helt med små bogstaver. ext uri i xl/metadata.xml skal være nøjagtigt {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. En ældre HotXLS-skabelon stavede den med blandede bogstaver, og Excel 16 nægtede at åbne hele pakken, ikke kun cellen. Workbooks oprettet med TXLSXRange.SetDynamicArrayFormula før v2.384.68 havde det samme problem
Array-rodens tekst bærer ikke noget foranstillet =. XLSX-skriveren sender teksten fra en array-rod uændret videre ind i <f>. Havde den konverterede celle beholdt sit =, ville elementet lyde <f t="array" ref="E5">=SUM(...)</f>, hvilket Excel også afviser ved åbning. HotXLS fjerner det under konverteringen, hvilket er grunden til, at TXLSXCell.Formula læses tilbage uden det
Double(True) er -1 i Delphi. Variant-konvertering følger COM-konventionen, hvor TRUE er alle bits sat, og VarIsNumeric(True) returnerer også True. Før v2.384.61 fik det =TRUE*1 til at returnere -1 og lod logiske array-elementer blive klassificeret som tal, så en sammenligning som (B1:B2>0)=TRUE gik galt. HotXLS tester nu for varBoolean, før en Variant behandles som tal i skalararitmetik, array-aritmetik og klassificering af array-elementer, og TRUE tæller som 1

BIFF8-operandklasser: detaljerne på byteniveau for formatimplementører

I BIFF8 bærer hver operand-token sin operandklasse i selve token-byten, og Excel stoler mere på den klasse end på formlens struktur. [MS-XLS] definerer klassen som et to-bit PtgDataType-felt i bit 5 og 6 af tokenen: 1 for reference, 2 for value, 3 for array. De lave fem bits navngiver tokenen, så den samme område-reference har tre stavninger:

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

HotXLS fik tre af disse sat forkert forskellige steder, og hver gav et tydeligt symptom i Excel, mens de læste fint tilbage i HotXLS:

Array-konstanter i reference-klassen. Encoderen valgte klassen ud fra konteksten, og SUM- eller ROWS-parametre er reference-klasse, så =SUM({1,2}) blev skrevet med PtgArray som $20. Excel viser hele formlen som =#N/A. En array-konstant kan aldrig være en reference, så siden v2.384.63 skriver HotXLS array-klassen $60, hvor end konteksten beder om en reference
Value-klasse-operander af PtgIsect og PtgUnion. Binære operatorer tog value-klasse-operander, hvilket er rigtigt for *, men forkert for reference-operatorerne. Med $45-områder før PtgIsect ($0F) læste Excel =SUM(A1:B2 B1:B2) som =SUM(@A1:B2 @B1:B2) og returnerede #VALUE!. Siden v2.384.62 skrives operanderne af PtgIsect og PtgUnion ($10) i reference-klassen, $25
Value-klasse-operander inde i ARRAY-recorden. Excel anvender implicit intersection selv inde i en array-formel, når en operand er value-klasse. HotXLS skrev $45 dér, så én-celle-array-formlen for =SUM(A1:B1*{10,100}) evaluerede til 10 i Excel. Siden v2.384.68 promoveres hver value-klasse-reference og array-konstant i en ARRAY-records tokenstream til array-klassen, $65 og $60, hvilket er præcis, hvad Excel skriver

HotXLS BIFF8-diagram: bit 5 og 6 af hver token-byte vælger reference-, value- eller array-klasse, så PtgArea staves 25, 45 og 65, med tre faste defekter: array-konstanter som 20 viste #N/A, PtgIsect-operander som 45 returnerede #VALUE!, og ARRAY-record-operander som 45 fik SUM(A1:B1*{10,100}) til at returnere 10 — Hver BIFF8-operand-token bærer sin klasse i bit 5 og 6, og Excel stoler på de bits frem for strukturen; HotXLS skriver array-konstanter som 60, PtgIsect-operander som 25 og promoverer ARRAY-record-tokens til array-klassen

En læser, der ignorerer klasse-bitene, round-tripper alle tre gladeligt, så vedligeholder du din egen BIFF8-skriver, så sammenlign klasse-bitene af hver operand-token mod en Excel-gemt fil med den samme formel, ikke kun token-numrene

Hurtig reference

Excel 365 viser @, når en operator i en almindelig, umarkeret formel modtager et område med flere celler eller et inline array
HotXLS v2.384.68 og senere gemmer sådanne formler som XLSX dynamiske arrays i én celle (cm="1", t="array", XLDAPR-metadata) og som XLS én-celle-array-formler (FORMULA med PtgExp plus ARRAY $0221)
Kun operator-operanden tæller; et område, der sendes direkte til et funktionsargument, forbliver en almindelig formel
Kun formler indtastet gennem TXLSXCell.Formula eller den klassiske enkelt-celle Formula / Value markeres; indlæste formler røres ikke
Den konverterede rodcelle læses tilbage uden det foranstillede =
Dynamic-array-ext uri-GUID'en skal være med små bogstaver, ellers afviser Excel pakken
I Delphi er Double(True) -1; test varBoolean, før du konverterer til tal
BIFF8: array-konstanter aldrig i reference-klassen, PtgIsect / PtgUnion-operander i reference-klassen, ARRAY-record-operander i array-klassen

HotXLS læser, skriver og beregner XLS- og XLSX-workbooks nativt fra Delphi og C++Builder og gemmer array-operatorformler, så Excel 365 åbner dem med de samme værdier, HotXLS har beregnet. Se HotXLS Delphi spreadsheet-komponenten for udgaver, dokumentation og en trial-download