HotXLS matrisformler: varför Excel infogar @ och #VALUE!

Excel 365 infogar @ i en formel som =SUM(A1:B1*{10,100}) och visar #VALUE! när filen lagrar den som en vanlig formel, för att Excel då tillämpar äldre implicit skärning på varje operatoroperand. Sedan v2.384.68 lagrar HotXLS Delphi Component dessa matrisoperatorformler på samma sätt som Excel 365: som dynamiska matrisformler i en enda cell i XLSX och som encells-matrisformler i XLS

Felet överlever kodgranskningen. Din Delphi-tjänst skriver en arbetsbok, HotXLS räknar om den och cachar 210 för =SUM(A1:B1*{10,100}), och kunden öppnar den i Excel 16 och hittar =SUM(@A1:B1*@{10,100}) i formelfältet och #VALUE! i cellen. Inget i filen är felformat. Det som saknas är metadatan som talar om för Excel att formeln skrevs enligt reglerna för dynamiska matriser, och utan den faller Excel tillbaka på sin utvärderingsmodell från tiden före dynamiska matriser

Varför infogar Excel 365 @ i en formel HotXLS räknat rätt?

Excel 365 infogar @ för att en formel utan markering för dynamiska matriser per definition är en äldre formel, och äldre formler reducerar ett område över flera celler till en cell överallt där en operator förväntar sig ett enda värde. Den reduktionen är implicit skärning: Excel tar cellen i området som delar formelns rad (för ett vertikalt område) eller kolumn (för ett horisontellt område), och finns ingen sådan cell blir resultatet #VALUE!. Excel 365 behåller den betydelsen för formler i gamla stilen och visar @ för att göra reduktionen synlig

Lägg =SUM(A1:B1*{10,100}) i E5 och den äldre läsningen blir uppenbar. A1:B1 är ett horisontellt område, formeln står i kolumn E, området har ingen cell i kolumn E, så @A1:B1 är #VALUE! och hela SUM ärver det. Enligt reglerna för dynamiska matriser multiplicerar samma text element för element, 1 × 10 + 2 × 100, och returnerar 210. HotXLS formelmotor har utvärderat på det dynamiska matrissättet sedan utgåvorna v2.384.61 och v2.384.63; filformatet sa bara inte till. Med A1:B2 innehållande 1, 2, 3 och 4 är det här testformlerna och vad Excel 16 visar:

Formel	HotXLS-resultat	Excel 16, lagrad som vanlig formel	Lagrad sedan v2.384.68
=SUM(A1:B1*{10,100})	210	#VALUE!	Dynamisk matris, Excel visar 210
=SUM((A1:B2>2)*1)	2	Implicit skärning, fel värde eller fel	Dynamisk matris, Excel visar 2
=SUMPRODUCT((A1:B2>2)*1)	2	Implicit skärning, fel värde eller fel	Dynamisk matris, Excel visar 2
=MAX(A1:B2-1)	3	Implicit skärning, fel värde eller fel	Dynamisk matris, Excel visar 3
=SUM(A1:B2)	10	10	Vanlig formel, oförändrad

Sista raden betyder lika mycket som de fyra första. SUM(A1:B2) skickar ett område direkt till en funktionsparameter som tar emot referenser, så ingen operator ser någonsin ett område över flera celler och ingen skärning kan ske. Excel 365 själv sparar den formeln som en vanlig formel, och HotXLS gör detsamma

Hur HotXLS lagrar matrisoperatorformler i XLSX och XLS

HotXLS skriver en matrisoperatorformel i XLSX som en dynamisk matris i en enda cell: elementet <c> bär cm="1", formeln är <f t="array" ref="E5">, och paketet får xl/metadata.xml med en XLDAPR-metadatatyp vars utbyggnad innehåller dynamicArrayProperties fDynamic="1". Attributet cm är ett ettbaserat index in i cellMetadata-blocket i den delen, och XLDAPR-posten bakom det är det som talar om för Excel "utvärdera detta enligt reglerna för dynamiska matriser". Det är samma struktur som Excel 16 skriver när du skriver samma formel och sparar, och så etablerades mållayouten från början

I XLS finns ingen metadatadel, så HotXLS använder den enda konstruktion BIFF8 har för matrisutvärdering: en matrisformel i en enda cell. Cellen får en FORMULA-post vars tokenström är en enda PtgExp som pekar på den själv, följt av en ARRAY-post ($0221) som bär den verkliga parsade formeln över encellsområdet. Excel 365 skriver formler med dynamiska matriser till XLS på samma sätt, och en äldre Excelversion som läser filen ser en klassisk Ctrl+Shift+Enter-matrisformel

Inget nytt API är inblandat. Markeringen sker när du tilldelar formeln via det vanliga cell-API:et, i båda motorerna. På XLSX-sidan är 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 över ett område eller infogad matris: lagras som en dynamisk matris
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // Område skickat rakt in i en funktion: förblir en vanlig <f>
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

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

    // Matrisroten behåller sin text utan inledande '='
    Writeln(Sheet.Cells[5, 5].Formula);              // SUM(A1:B1*{10,100})

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

Efter konverteringen returnerar TXLSXCell.Formula texten utan =, samma form som TXLSXRange.SetDynamicArrayFormula lagrar, så kod som jämför formelsträngar efter tilldelningen bör normalisera det inledande =

Den klassiska motorn följer samma regel via IXLSRange.Formula på en enda cell. Tilldelar du formeln omdirigeras den internt till encellsvägen för matriser, så den sparade XLS-filen innehåller paret 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-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;

Om du vill förankra ett resultat över flera celler i stället för en skalär summa är de explicita API:erna fortfarande rätt verktyg: SetArrayFormula för en i förväg storlekssatt rektangel, som beskrivs i dynamiska matrisformler med spill i HotXLS, eller TXLSXRange.SetDynamicArrayFormula när du vill ha XLSX-markeringen för dynamiska matriser på ett område du storlekssätter själv. Den automatiska vägen i den här artikeln täcker bara formler skrivna i en enda cell

Vilka formler märker HotXLS som dynamiska matriser?

HotXLS märker en formel bara när en operator har ett operanddelträd som producerar en matris. Kontrollen körs på det kompilerade syntaxträdet, och en operand producerar en matris om den är ett område över flera celler, en infogad matriskonstant eller ett annat operatoruttryck som själv har en sådan operand. Parenteser är transparenta. De operatorer som räknas är de aritmetiska (+ - * / ^), konkatenering (&), de sex jämförelserna, unärt plus och minus samt procent:

• A1:B1*{10,100}, (A1:B2>2)*1, --(B1:B2>0) och A1:B2-1 märks, var de än dyker upp i formeln, även inuti SUMPRODUCT
• SUM(A1:B2) och SUMPRODUCT(A1:A2,{1;10}) märks inte, eftersom området och matrisen går rakt in i ett funktionsargument och ingen operator rör dem
• A1*2 eller SUM(A1,B1)*2 märks inte: referenser till en enda cell och funktionsresultat är skalärer för den här kontrollen

Tre gränser är medvetna val. För det första sker markeringen bara när en formel matas in via API:et, alltså TXLSXCell.Formula i XLSX-motorn och en tilldelning av Formula eller Value till en enda cell i den klassiska motorn. Formler som lästs från en fil skrivs tillbaka exakt som de påträffades, för en äldre formel från en annan producent kan bero på implicit skärning med flit. För det andra hoppas text som varken innehåller : eller { över utan en andra kompilering. För det tredje märks en formel som skulle spilla, som =A1:B1*2 på egen hand, som en dynamisk matris i en enda cell förankrad där du la den. HotXLS spillar den inte, och Excel kommer att breda ut resultatet till de angränsande cellerna nästa gång det räknar om

Den här operandregeln är syster till argumentklassregeln som tas upp i implicit skärning för definierade namn i HotXLS. Den artikeln handlar om funktionsparametrar deklarerade som värdeklass; den här handlar om operatorer, som i den äldre modellen alltid kräver värden

Vad ändrades i beräkningsmotorn för att resultaten ska stämma

Lagringsfixen i v2.384.68 bygger på att HotXLS formelmotor redan returnerade Excels 365-värden, vilket krävde flera tidigare fixar i båda motorerna. Den mest synliga var SUMPRODUCT: fram till v2.384.61 accepterade den bara två eller flera vanliga områden, så SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) och till och med SUMPRODUCT(B1:B2) med ett enda argument returnerade #N/A. HotXLS utvärderar nu uttrycksargument element för element efter Excels regler:

• varje argument måste ha exakt samma form, där en skalär räknas som 1 × 1, annars blir resultatet #VALUE!
• ett felvärde inuti något argument returneras som resultatet
• text- och logiska element räknas som 0, så (B1:B2>0)*1 eller -- behövs fortfarande för att göra om TRUE till 1
• argument som alla är vanliga områden behåller den ursprungliga strömmande loopen, så stora områden materialiseras inte som matriser

SUM-familjen (SUM, COUNT, AVERAGE, MIN, MAX, COUNTA) använder samma elementvisa utvärderare när ett argument är ett operatoruttryck över ett område, så =SUM((B1:B2>0)*1) räknar båda raderna i stället för att bara titta på första cellen. v2.384.62 fick skärningsoperatorn med mellanslag att returnera den gemensamma rektangeln av två referenser, med #NULL! när de inte överlappar, så =SUM(A1:B2 B1:B2) är 6 i stället för 2 och resultatet kan mata referensparametrar som ROWS och INDEX. v2.384.63 lade till infogade matriskonstanter som {1,2;3,4} (kommatecken skiljer kolumner, semikolon skiljer rader) och referensföreningar som (A1:B2,D4) till parsaren. Elementvisa jämförelser ger också ett tomt element den andra sidans typ, FALSE mot en logisk, i linje med skalärregeln från v2.384.53 som beskrivs i jämförelsekedjor och tomma celler i HotXLS

var
  V: Variant;
begin
  // Book är TXLSXWorkbook från det första exemplet;
  // dess aktiva blad innehåller A1:B2 = 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10, ett enda argument
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6, gemensamt område B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16, överlapp räknat två gånger
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1, var -1 före v2.384.61
end;

TXLSXWorkbook.Calculate utvärderar en formelsträng mot det aktiva bladet utan att lagra den, ett snabbt sätt att kontrollera motorns beteende. En varning om @ själv: HotXLS har historiskt accepterat @ mellan två referenser som en binär skärning, och nu utvärderas den formen med verklig skärningssemantik. I Excel 365 är @ ett unärt prefix för implicit skärning. Skriv inte @ i formulartexten och räkna med Excels betydelse; använd ett mellanslag för skärning och låt lagringsreglerna ovan hantera semantiken för dynamiska matriser

Varför vägrade Excel öppna filen eller räknade fram fel värde?

Att få Excel att acceptera markeringen för dynamiska matriser krävde tre fixar som inget eget rundturntest skulle ha fångat, för HotXLS läste sin egen utdata korrekt i vart och ett av fallen. Var och en hittades genom att öppna HotXLS-utdata i Excel 16 och byta ut en variabel i taget:

1. Tilläggets GUID måste vara helt i gemener. ext uri i xl/metadata.xml måste vara exakt {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. En äldre HotXLS-mall stavade den med blandad skift, och Excel 16 vägrade öppna hela paketet, inte bara cellen. Arbetsböcker skapade med TXLSXRange.SetDynamicArrayFormula före v2.384.68 hade samma problem
2. Matrisrotens text bär inget inledande =. XLSX-skrivaren skriver ut en matrisrots lagrade text ordagrant i <f>. Behöll den konverterade cellen sitt = skulle elementet lyda <f t="array" ref="E5">=SUM(...)</f>, vilket Excel också avvisar vid öppning. HotXLS tar bort den under konverteringen, vilket är anledningen till att TXLSXCell.Formula läses tillbaka utan den
3. Double(True) är -1 i Delphi. Variantkonvertering följer COM-konventionen där TRUE är alla bitar satta, och VarIsNumeric(True) returnerar också True. Före v2.384.61 fick det =TRUE*1 att returnera -1 och lät logiska matriselement klassificeras som tal, så en jämförelse som (B1:B2>0)=TRUE gick fel. HotXLS testar nu för varBoolean innan en Variant behandlas som ett tal i skalär aritmetik, matrisaritmetik och klassificering av matriselement, och TRUE räknas som 1

BIFF8-operandklasser: detaljerna på bytenivå för dig som implementerar formatet

I BIFF8 bär varje operandtoken sin operandklass i tokenbyten själv, och Excel litar mer på den klassen än på formelns struktur. [MS-XLS] definierar klassen som ett tvåbitars PtgDataType-fält i bit 5 och 6 av token: 1 för referens, 2 för värde, 3 för matris. De fem låga bitarna namnger token, så samma områdesreferens har tre stavningar:

Token	Referensklass	Värdeklass	Matrisklass
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

HotXLS hade tre av dessa fel på olika ställen, och var och ett gav ett eget symptom i Excel medan allt lästes tillbaka felfritt i HotXLS:

• Matriskonstanter i referensklass. Kodaren valde klassen utifrån sammanhanget, och SUM- eller ROWS-parametrar är referensklass, så =SUM({1,2}) skrevs med PtgArray som $20. Excel visar hela formeln som =#N/A. En matriskonstant kan aldrig vara en referens, så sedan v2.384.63 skriver HotXLS matrisklass $60 överallt där sammanhanget begär en referens
• Värdeklassoperanderna hos PtgIsect och PtgUnion. Binära operatorer tog operanderna i värdeklass, vilket är rätt för * men fel för referensoperatorerna. Med $45-områden före PtgIsect ($0F) läste Excel =SUM(A1:B2 B1:B2) som =SUM(@A1:B2 @B1:B2) och returnerade #VALUE!. Sedan v2.384.62 skrivs operanderna hos PtgIsect och PtgUnion ($10) i referensklass, $25
• Värdeklassoperanden inuti ARRAY-posten. Excel tillämpar implicit skärning även inuti en matrisformel när en operand är i värdeklass. HotXLS skrev $45 där, så encells-matrisformeln för =SUM(A1:B1*{10,100}) utvärderades till 10 i Excel. Sedan v2.384.68 befordrar tokenströmmen i en ARRAY-post varje värdeklassreferens och matriskonstant till matrisklass, $65 och $60, vilket är vad Excel skriver

En läsare som ignorerar klassbitarna hanterar alla tre felen glatt vid sparande och återinläsning, så om du underhåller din egen BIFF8-skrivare ska du jämföra klassbitarna i varje operandtoken mot en fil sparad från Excel med samma formel, inte bara tokennumren

Snabbreferens

• Excel 365 visar @ när en operator i en vanlig, omärkt formel tar emot ett område över flera celler eller en infogad matris
• HotXLS v2.384.68 och senare lagrar sådana formler som XLSX-dynamiska matriser i en enda cell (cm="1", t="array", XLDAPR-metadata) och som XLS-matrisformler i en enda cell (FORMULA med PtgExp plus ARRAY $0221)
• Bara operatoroperanden räknas; ett område skickat rakt in i ett funktionsargument förblir en vanlig formel
• Bara formler matade in via TXLSXCell.Formula eller den klassiska encells-Formula / Value märks; inlästa formler lämnas orörda
• Den konverterade rotcellen läses tillbaka utan det inledande =
• GUID:en för den dynamiska matrisens ext uri måste vara i gemener, annars avvisar Excel paketet
• I Delphi är Double(True) -1; testa varBoolean före numerisk konvertering
• BIFF8: matriskonstanter aldrig referensklass, PtgIsect / PtgUnion-operanden i referensklass, ARRAY-postoperanden i matrisklass

HotXLS läser, skriver och räknar XLS- och XLSX-arbetsböcker nativt från Delphi och C++Builder, och lagrar matrisoperatorformler så att Excel 365 öppnar dem med samma värden som HotXLS räknat fram. Se HotXLS Delphi spreadsheet component för utgåvor, dokumentation och en nedladdningsbar testversion