HotXLS-matriseformler: Derfor setter Excel inn @ og #VALUE!

Excel 365 setter inn @ i en formel som =SUM(A1:B1*{10,100}) og viser #VALUE! når filen lagrer den som en vanlig formel, fordi Excel da bruker gammeldags implisitt skjæring på hver operand til en operator. Siden v2.384.68 lagrer HotXLS Delphi Component disse matriseoperatorformlene på samme måte som Excel 365: som dynamiske matriseformler med én celle i XLSX og som arrayformler med én celle i XLS

Symptomet overlever kodegjennomgang. Delphi-tjenesten din skriver en arbeidsbok, HotXLS rekalkulerer den og cacher 210 for =SUM(A1:B1*{10,100}), og kunden åpner den i Excel 16 og finner =SUM(@A1:B1*@{10,100}) i formellinjen og #VALUE! i cellen. Ingenting i filen er feilformet. Det som mangler er metadataene som forteller Excel at formelen ble skrevet under reglene for dynamiske matriser, og uten dem faller Excel tilbake på evalueringsmodellen fra før dynamiske matriser

Hvorfor legger Excel 365 til @ i en formel HotXLS regnet ut riktig?

Excel 365 legger til @ fordi en formel uten merking for dynamiske matriser per definisjon er en gammeldags formel, og gamle formler reduserer et område med flere celler til én celle der en operator forventer én enkelt verdi. Denne reduksjonen er implisitt skjæring: Excel tar cellen i området som deler formelens rad (for et vertikalt område) eller kolonne (for et horisontalt område), og finnes det ingen slik celle, blir resultatet #VALUE!. Excel 365 beholder den betydningen for formler i gammel stil og viser @ for å gjøre reduksjonen synlig

Legger du =SUM(A1:B1*{10,100}) i E5, blir den gammeldagse lesingen åpenbar. A1:B1 er et horisontalt område, formelen står i kolonne E, området har ingen celle i kolonne E, så @A1:B1 er #VALUE!, og hele SUM arver den. Under reglene for dynamiske matriser multipliserer samme tekst element for element, 1 × 10 + 2 × 100, og returnerer 210. HotXLS formelmotor har evaluert på den dynamiske matrisemåten siden utgivelsene v2.384.61 og v2.384.63; filformatet sa det bare ikke. Med A1:B2 som inneholder 1, 2, 3 og 4, er dette prøveformlene og det Excel 16 viser:

Formel	HotXLS-resultat	Excel 16, lagret som vanlig formel	Lagret siden v2.384.68
=SUM(A1:B1*{10,100})	210	#VALUE!	Dynamisk matrise, Excel viser 210
=SUM((A1:B2>2)*1)	2	Implisitt skjæring, feil svar eller feil	Dynamisk matrise, Excel viser 2
=SUMPRODUCT((A1:B2>2)*1)	2	Implisitt skjæring, feil svar eller feil	Dynamisk matrise, Excel viser 2
=MAX(A1:B2-1)	3	Implisitt skjæring, feil svar eller feil	Dynamisk matrise, Excel viser 3
=SUM(A1:B2)	10	10	Vanlig formel, uendret

Siste rad betyr like mye som de fire første. SUM(A1:B2) sender et område rett inn i en funksjonsparameter som godtar referanser, så ingen operator ser et område med flere celler, og ingen skjæring kan skje. Excel 365 selv lagrer den formelen som en vanlig formel, og det gjør HotXLS også

Slik lagrer HotXLS matriseoperatorformler i XLSX og XLS

HotXLS skriver en matriseoperatorformel i XLSX som en dynamisk matrise med én celle: <c>-elementet bærer cm="1", formelen er <f t="array" ref="E5">, og pakken får xl/metadata.xml med en XLDAPR-metadatatype hvis utvidelse inneholder dynamicArrayProperties fDynamic="1". Attributten cm er en én-basert indeks inn i cellMetadata-blokken i den delen, og XLDAPR-posten bak den er det som forteller Excel «beregn dette under reglene for dynamiske matriser». Dette er samme struktur Excel 16 skriver når du taster samme formel og lagrer, og det var slik måloppsettet ble etablert i utgangspunktet

I XLS finnes det ingen metadatadel, så HotXLS bruker det eneste konstruksjonsmiddelet BIFF8 har for matriseevaluering: en arrayformel med én celle. Cellen får en FORMULA-post hvis tokenstrøm er en enkelt PtgExp som peker på seg selv, etterfulgt av en ARRAY-post ($0221) som bærer den virkelige parsede formelen over området med én celle. Excel 365 skriver dynamiske matriseformler til XLS på samme måte, og en eldre Excel-versjon som leser filen ser en klassisk Ctrl+Shift+Enter-arrayformel

Ingen nytt API er involvert. Merkingen skjer når du tildeler formelen gjennom det vanlige celle-API-et, 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 en innebygd matrise: lagres som dynamisk matrise
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // Område sendt rett til en funksjon: forblir en vanlig <f>
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

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

    // Matriseroten beholder teksten sin uten ledende '='
    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;

Etter konverteringen returnerer TXLSXCell.Formula teksten uten =, samme form som TXLSXRange.SetDynamicArrayFormula lagrer, så kode som sammenligner formelstrenger etter tildeling bør normalisere det ledende =-tegnet

Den klassiske motoren følger samme regel gjennom IXLSRange.Formula på en enkelt celle. Å tildele formelen sender den internt om til én-celle-arraystien, så den lagrede XLS-filen inneholder FORMULA- og ARRAY-paret:

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-post
  Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)';         // ARRAY-post
  Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)';           // vanlig 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 i stedet for et skalaraggregat, er de eksplisitte API-ene fortsatt riktig verktøy: SetArrayFormula for et forhåndsdimensjonert rektangel, som beskrevet i dynamiske matrise-spillformler med HotXLS, eller TXLSXRange.SetDynamicArrayFormula når du vil ha XLSX-merkingen for dynamiske matriser på et område du dimensjonerer selv. Den automatiske stien i denne artikkelen dekker bare formler tastet inn i én celle

Hvilke formler merker HotXLS som dynamiske matriser?

HotXLS merker en formel bare når en operator har et operand-undertre som produserer en matrise. Kontrollen kjører på det kompilerte syntakstreet, og en operand produserer en matrise hvis det er et område med flere celler, en innebygd matrisekonstant eller et annet operatoruttrykk som selv har en slik operand. Parenteser er transparente. Operatorene som teller er de aritmetiske (+ - * / ^), konkatenering (&), de seks sammenligningene, unær pluss og minus, og prosent:

• A1:B1*{10,100}, (A1:B2>2)*1, --(B1:B2>0) og A1:B2-1 merkes, uansett hvor de dukker opp i formelen, også inne i SUMPRODUCT
• SUM(A1:B2) og SUMPRODUCT(A1:A2,{1;10}) merkes ikke, fordi området og matrisen går rett inn i et funksjonsargument uten at noen operator rører dem
• A1*2 eller SUM(A1,B1)*2 merkes ikke: referanser til én celle og funksjonsresultater er skalarer for denne kontrollen

Tre grenser er bevisste. For det første skjer merkingen bare når en formel tastes inn gjennom API-et, altså TXLSXCell.Formula i XLSX-motoren og en tildeling til Formula eller Value på én celle i den klassiske motoren. Formler lastet fra en fil skrives tilbake nøyaktig slik de ble funnet, fordi en gammeldags formel fra en annen produsent kan avhenge av implisitt skjæring med vilje. For det andre hoppes tekst som verken inneholder : eller { over uten ny kompilering. For det tredje merkes en formel som ville spillt ut, som =A1:B1*2 på egen hånd, som en dynamisk matrise med én celle forankret der du la den. HotXLS spiller den ikke ut, og Excel vil utvide resultatet til nabocellene neste gang det rekalkulerer

Denne operand-regelen er søsteren til argumentklasse-regelen som er dekket i implisitt skjæring for definerte navn i HotXLS. Den artikkelen handler om funksjonsparametre deklarert som verdiklasse; denne handler om operatorer, som i den gamle modellen alltid krever verdier

Hva endret seg i beregningsmotoren for at resultatene skulle stemme?

Lagringsfiksen i v2.384.68 bygger på at HotXLS formelmotor allerede returnerte Excel 365-verdier, noe som krevde flere tidligere fikser i begge motorer. Den mest synlige var SUMPRODUCT: frem til v2.384.61 godtok den bare to eller flere vanlige områder, så SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) og til og med enkeltargument-varianten SUMPRODUCT(B1:B2) returnerte #N/A. HotXLS evaluerer nå uttrykksargumenter element for element etter Excels regler:

• hvert argument må ha nøyaktig samme form, der en skalar teller som 1 × 1, ellers blir resultatet #VALUE!
• en feilverdi inne i et argument returneres som resultatet
• tekst- og logiske elementer teller som 0, så (B1:B2>0)*1 eller -- trengs fortsatt for å gjøre TRUE om til 1
• argumenter som alle er vanlige områder beholder den opprinnelige strømmingløkken, så store områder materialiseres ikke som matriser

SUM-familien (SUM, COUNT, AVERAGE, MIN, MAX, COUNTA) bruker samme elementvise evaluator når et argument er et operatoruttrykk over et område, så =SUM((B1:B2>0)*1) teller begge radene i stedet for å se bare på første celle. v2.384.62 fikk skjæringsoperatoren med mellomrom til å returnere det felles rektangelet av to referanser, med #NULL! når de ikke overlapper, så =SUM(A1:B2 B1:B2) er 6 i stedet for 2, og resultatet kan mate referanseparametre som ROWS og INDEX. v2.384.63 la til innebygde matrisekonstanter som {1,2;3,4} (komma skiller kolonner, semikolon skiller rader) og referanseunioner som (A1:B2,D4) i parseren. Elementvise sammenligninger gir også et tomt element typen til den andre siden, FALSE mot en logisk verdi, i tråd med skalarregelen fra v2.384.53 som er beskrevet i artikkelen om sammenligningskjeder og tomme celler i HotXLS

var
  V: Variant;
begin
  // Book er TXLSXWorkbook fra det første eksemplet;
  // dens aktive ark inneholder A1:B2 = 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10, ett argument
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6, felles område B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16, overlappingen tell dobbelt
  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 mot det aktive arket uten å lagre den, en rask måte å sjekke motoratferden på. En advarsel om @ i seg selv: HotXLS har historisk godtatt @ mellom to referanser som en binær skjæring, og den evaluerer nå den formen med ekte skjæringssemantikk. I Excel 365 er @ et unært implisitt-skjærings-prefiks. Ikke skriv @ inn i formelteksten og forvent Excels betydning; bruk et mellomrom for skjæring, og la lagringsreglene over håndtere semantikken for dynamiske matriser

Hvorfor nektet Excel å åpne filen eller regnet ut feil verdi?

Å få Excel til å godta merkingen for dynamiske matriser krevde tre fikser som ingen selvrundtur-test ville fanget, fordi HotXLS leste sin egen utdata riktig i alle tilfellene. Hver ble funnet ved å åpne HotXLS-utdata i Excel 16 og bytte én variabel om gangen:

1. Utvidelses-GUID-en må være i små bokstaver. ext uri i xl/metadata.xml må være nøyaktig {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. En eldre HotXLS-mal stavet den med blandede bokstaver, og Excel 16 nektet å åpne hele pakken, ikke bare cellen. Arbeidsbøker laget med TXLSXRange.SetDynamicArrayFormula før v2.384.68 hadde samme problem
2. Matriserotens tekst bærer ingen ledende =. XLSX-skriveren sender den lagrede teksten til en matriserot ordrett inn i <f>. Holdt den konverterte cellen på sin =, ville elementet lest <f t="array" ref="E5">=SUM(...)</f>, som Excel også avviser ved åpning. HotXLS stripper den under konverteringen, og det er derfor TXLSXCell.Formula leses tilbake uten den
3. Double(True) er -1 i Delphi. Variantkonvertering følger COM-konvensjonen der TRUE er alle biter satt, og VarIsNumeric(True) returnerer True også. Før v2.384.61 fikk det =TRUE*1 til å returnere -1 og lot logiske matriseelementer klassifiseres som tall, så en sammenligning som (B1:B2>0)=TRUE gikk galt. HotXLS tester nå for varBoolean før en Variant behandles som tall i skalararitmetikk, matrisearitmetikk og klassifisering av matriseelementer, og TRUE teller som 1

BIFF8-operandklasser: bytenivådetaljene for de som implementerer formatet

I BIFF8 bærer hvert operand-token sin operandklasse i token-byten selv, og Excel stoler mer på den klassen enn på formelens struktur. [MS-XLS] definerer klassen som et to-bit PtgDataType-felt i bit 5 og 6 av tokenet: 1 for referanse, 2 for verdi, 3 for matrise. De fem lave bitene navngir tokenet, så samme områdereferanse har tre stavinger:

Token	Referanseklasse	Verdiklasse	Matriseklasse
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

HotXLS fikk tre av disse feil på ulike steder, og hver ga et eget symptom i Excel samtidig som den leste fint tilbake i HotXLS:

• Matrisekonstanter i referanseklasse. Enkoderen valgte klassen ut fra konteksten, og SUM- eller ROWS-parametre er referanseklasse, så =SUM({1,2}) ble skrevet med PtgArray som $20. Excel viser hele formelen som =#N/A. En matrisekonstant kan aldri være en referanse, så siden v2.384.63 skriver HotXLS matriseklasse $60 der konteksten ber om referanse
• Operander i verdiklasse for PtgIsect og PtgUnion. De binære operatorene tok operander i verdiklasse, som stemmer for * men er feil for referanseoperatorene. Med $45-områder før PtgIsect ($0F) leste Excel =SUM(A1:B2 B1:B2) som =SUM(@A1:B2 @B1:B2) og returnerte #VALUE!. Siden v2.384.62 skrives operandene til PtgIsect og PtgUnion ($10) i referanseklasse, $25
• Operander i verdiklasse inne i ARRAY-posten. Excel bruker implisitt skjæring også inne i en arrayformel når en operand er i verdiklasse. HotXLS skrev $45 der, så arrayformelen med én celle for =SUM(A1:B1*{10,100}) evaluerte til 10 i Excel. Siden v2.384.68 forfremmer tokenstrømmen i en ARRAY-post hver referanse og matrisekonstant i verdiklasse til matriseklasse, $65 og $60, som er det Excel skriver

En leser som ignorerer klasse-bitene, rundturer alle tre problemfritt, så vedlikeholder du din egen BIFF8-skriver, sammenlign klasse-bitene til hvert operand-token mot en Excel-lagret fil med samme formel, ikke bare token-nummerene

Hurtigreferanse

• Excel 365 viser @ når en operator i en vanlig, umerket formel mottar et område med flere celler eller en innebygd matrise
• HotXLS v2.384.68 og senere lagrer slike formler som XLSX-dynamiske matriser med én celle (cm="1", t="array", XLDAPR-metadata) og som XLS-arrayformler med én celle (FORMULA med PtgExp pluss ARRAY $0221)
• Bare operator-operanden teller; et område sendt rett til et funksjonsargument forblir en vanlig formel
• Bare formler tastet inn gjennom TXLSXCell.Formula eller den klassiske enkeltcelle-Formula / Value merkes; formler lastet fra fil røres ikke
• Den konverterte rotcellen leses tilbake uten det ledende =
• GUID-en i ext uri for dynamiske matriser må være i små bokstaver, ellers avviser Excel pakken
• I Delphi er Double(True) lik -1; test varBoolean før numerisk konvertering
• BIFF8: matrisekonstanter aldri i referanseklasse, PtgIsect / PtgUnion-operander i referanseklasse, ARRAY-post-operander i matriseklasse

HotXLS leser, skriver og beregner XLS- og XLSX-arbeidsbøker nativt fra Delphi og C++Builder, og lagrer matriseoperatorformler slik at Excel 365 åpner dem med samme verdier som HotXLS beregnet. Se HotXLS Delphi regnearkkomponent for utgaver, dokumentasjon og en prøveversjon