HotXLS matrične formule: zakaj Excel doda @ in #VALUE!

Excel 365 vstavi @ v formulo, kot je =SUM(A1:B1*{10,100}), in prikaže #VALUE!, kadar jo datoteka shrani kot navadno formulo, ker Excel takrat na vsakem operandu operatorja uporabi zastarelo implicitno presekovanje. Od v2.384.68 komponenta HotXLS Delphi shrani te formule z matričnimi operatorji tako kot Excel 365: kot formule dinamičnih matrik za eno celico v XLSX in kot matrične formule za eno celico v XLS

Simptoma ne ujame noben pregled kode. Vaša storitev v Delphiju zapiše delovni zvezek, HotXLS ga preračuna in spravi v predpomnilnik 210 za =SUM(A1:B1*{10,100}), stranka pa ga odpre v Excelu 16 in v vrstici s formulami najde =SUM(@A1:B1*@{10,100}), v celici pa #VALUE!. V datoteki ni nič okvarjenega. Manjka le metapodatek, ki Excelu pove, da je bila formula zapisana po pravilih dinamičnih matrik; brez njega se Excel vrne na model vrednotenja iz časov pred dinamičnimi matrikami

Zakaj Excel 365 doda @ formuli, ki jo je HotXLS pravilno izračunal?

Excel 365 doda @, ker je formula brez oznake dinamične matrike po definiciji zastarela formula, zastarele formule pa krčijo večcelični obseg na eno celico povsod, kjer operator pričakuje eno vrednost. Ta krčitev je implicitno presekovanje: Excel vzame celico obsega, ki si deli vrstico formule (pri navpičnem obsegu) ali stolpec (pri vodoravnem obsegu), če take celice ni, pa je rezultat #VALUE!. Excel 365 ohranja ta pomen za formule starega sloga in prikaže @, da je krčitev vidna

Vstavite =SUM(A1:B1*{10,100}) v E5 in zastarela interpretacija postane očitna. A1:B1 je vodoravni obseg, formula stoji v stolpcu E, obseg pa nima nobene celice v stolpcu E, zato je @A1:B1 vrednost #VALUE! in celoten SUM jo podeduje. Po pravilih dinamičnih matrik isto besedilo množi element po element, 1 × 10 + 2 × 100, in vrne 210. Formulski pogon HotXLS vrednoti na dinamično-matrični način že od izdaj v2.384.61 in v2.384.63; datotečna oblika tega pa preprosto ni povedala. Če A1:B2 hrani 1, 2, 3 in 4, so to preizkusne formule in tisto, kar Excel 16 prikaže:

Formula	Rezultat HotXLS	Excel 16, shranjeno kot navadna formula	Shranjeno od v2.384.68
=SUM(A1:B1*{10,100})	210	#VALUE!	Dinamična matrika, Excel prikaže 210
=SUM((A1:B2>2)*1)	2	Implicitno presekovanje, napačno ali napaka	Dinamična matrika, Excel prikaže 2
=SUMPRODUCT((A1:B2>2)*1)	2	Implicitno presekovanje, napačno ali napaka	Dinamična matrika, Excel prikaže 2
=MAX(A1:B2-1)	3	Implicitno presekovanje, napačno ali napaka	Dinamična matrika, Excel prikaže 3
=SUM(A1:B2)	10	10	Navadna formula, nespremenjena

Zadnja vrstica je enako pomembna kot prve štiri. SUM(A1:B2) posreduje obseg neposredno parametru funkcije, ki sprejema reference, zato noben operator nikoli ne vidi večceličnega obsega in nobeno presekovanje se ne more zgoditi. Excel 365 sam to formulo shrani kot navadno formulo, HotXLS pa dela enako

Kako HotXLS shrani formule z matričnimi operatorji v XLSX in XLS

HotXLS zapiše formulo z matričnim operatorjem v XLSX kot dinamično matriko za eno celico: element <c> nosi cm="1", formula je <f t="array" ref="E5">, paket pa dobi xl/metadata.xml z metapodatkovno vrsto XLDAPR, katere razširitev hrani dynamicArrayProperties fDynamic="1". Atribut cm je eno-osnovni indeks v blok cellMetadata tega dela, zapis XLDAPR za njim pa je tisto, kar Excelu pove »vrednoti to po pravilih dinamičnih matrik«. To je enaka struktura, ki jo Excel 16 zapiše, ko vtipičate isto formulo in shranite — tako smo sploh ugotovili ciljno postavitev

V XLS ni metapodatkovnega dela, zato HotXLS uporabi edini konstruk, ki ga BIFF8 ima za vrednotenje matrik: matrično formulo za eno celico. Celica dobi zapis FORMULA, katerega tok žetonov je en sam PtgExp, ki kaže nase, mu pa sledi zapis ARRAY ($0221) z pravo razčlenjeno formulo nad enoceličnim obsegom. Excel 365 zapiše formule dinamičnih matrik v XLS na enak način, starejša različica Excela pa ob branju datoteke vidi klasično matrično formulo Ctrl+Shift+Enter

Nič novega API-ja. Oznaka se doda, ko formulo dodelite prek običajnega celičnega API-ja, in to v obeh pogonih. Na strani XLSX je to 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 nad obsegom ali vgrajeno matriko: shrani se kot dinamična matrika
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // Obseg, posredovan neposredno funkciji: ostane navaden <f>
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

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

    // Koren matrike ohrani svoje besedilo brez vodilnega '='
    Writeln(Sheet.Cells[5, 5].Formula);              // SUM(A1:B1*{10,100})

    Book.SaveAs('probe.xlsx');   // E5 in E6 dobita cm="1" + t="array"
  finally
    Book.Free;
  end;
end;

Po pretvorbi TXLSXCell.Formula vrne besedilo brez =, isto obliko, ki jo shrani TXLSXRange.SetDynamicArrayFormula, zato naj koda, ki po dodelitvi primerja nize formul, najprej poenoti vodilni =

Klasični pogon sledi istemu pravilu prek IXLSRange.Formula na eni celici. Dodelitev formule jo interno preusmeri na enocelično matrično pot, zato shranjeni XLS vsebuje par 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})';  // zapis ARRAY
  Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)';         // zapis ARRAY
  Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)';           // navaden FORMULA

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

Če zasidrujete večcelični rezultat in ne skalarne seštevka, so še vedno pravo orodje izrecni API-ji: SetArrayFormula za vnaprej določen pravokotnik, kot opisuje formule razlitja dinamičnih matrik s HotXLS, ali TXLSXRange.SetDynamicArrayFormula, kadar želite oznako dinamične matrike XLSX na obsegu, ki ga raztegnete sami. Samodejna pot iz tega članka pokriva samo formule, vtipičane v eno celico

Katere formule HotXLS označi kot dinamične matrike?

HotXLS označi formulo samo, kadar ima operator poddrevo operanda, ki ustvari matriko. Preverjanje teče na prevedenem sintaksnem drevesu, operand pa ustvari matriko, če je večcelični obseg, vgrajena matrična konstanta ali drug operatorski izraz, ki sam ima tak operand. Oklepaji so prosojni. Operatorji, ki štejejo, so aritmetični (+ - * / ^), stikanje (&), šest primerjav, enojni plus in minus ter odstotek:

• A1:B1*{10,100}, (A1:B2>2)*1, --(B1:B2>0) in A1:B2-1 so označeni, kjer koli se pojavijo v formuli, tudi znotraj SUMPRODUCT
• SUM(A1:B2) in SUMPRODUCT(A1:A2,{1;10}) nista označena, ker obseg in matrika greta neposredno v argument funkcije in ju noben operator ne omeni
• A1*2 ali SUM(A1,B1)*2 nista označena: enocelične reference in rezultati funkcij so za to preverjanje skalarni

Tri meje so namerne. Prvič, oznaka se doda samo, kadar je formula vnešena prek API-ja, to je TXLSXCell.Formula v pogonu XLSX in dodelitev Formula ali Value na eni celici v klasičnem pogonu. Formule, naložene iz datoteke, se zapišejo nazaj točno tako, kot so bile najdene, ker lahko zastarela formule drugega izdelovalca namenoma računa na implicitno presekovanje. Drugič, besedilo, ki ne vsebuje ne : ne {, se preskoči brez drugega prevajanja. Tretjič, formula, ki bi se razlila, na primer =A1:B1*2 sama zase, se označi kot dinamična matrika za eno celico, zasidrana tam, kjer ste jo postavili. HotXLS je ne razlije, Excel pa bo rezultat naslednjič ob preračunu raztegnil na sosednje celice

To pravilo o operandih je brat pravilu o razredih argumentov, opisanemu v implicitnem presekovanju za definirana imena v HotXLS. Ta članek govori o parametrih funkcij, deklariranih kot razred vrednosti; ta tukaj pa o operatorjih, ki v zastarelem modelu vedno zahtevajo vrednosti

Kaj se je spremenilo v računskem pogonu, da so se rezultati ujeli

Popravek shranjevanja v v2.384.68 se opira na to, da formulski pogon HotXLS že vrača vrednosti Excel 365, kar je zahtevalo nekaj zgodnejših popravkov v obeh pogonih. Najbolj viden je bil SUMPRODUCT: do v2.384.61 je sprejel samo dva ali več navadnih obsegov, zato so SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) in celo enoargumentski SUMPRODUCT(B1:B2) vračali #N/A. HotXLS zdaj vrednoti izrazne argumente element po element po Excelovih pravilih:

• vsak argument mora imeti točno enako obliko, skalar šteje kot 1 × 1, sicer je rezultat #VALUE!
• napačna vrednost znotraj katerega koli argumenta se vrne kot rezultat
• besedilni in logični elementi štejejo 0, zato je (B1:B2>0)*1 ali -- še vedno potreben, da TRUE postane 1
• argumenti, ki so vsi navadni obsegi, obdržijo izvirno pretočno zanko, tako da veliki obsegi ne postanejo matrike v pomnilniku

Družina SUM (SUM, COUNT, AVERAGE, MIN, MAX, COUNTA) uporablja isti evaluator po elementih, kadar je argument operatorski izraz nad obsegom, zato =SUM((B1:B2>0)*1) prešteje obe vrstici in ne gleda samo prve celice. v2.384.62 je naredil, da operator presekovanja s presledkom vrne skupni pravokotnik dveh referenc, z #NULL!, kadar si ne prekrivata, tako da je =SUM(A1:B2 B1:B2) enako 6 in ne 2, rezultat pa lahko napaja referenčne parametre, kot sta ROWS in INDEX. v2.384.63 je razčlenjevalniku dodal vgrajene matrične konstante, kot je {1,2;3,4} (vejice ločujejo stolpce, podpičja vrstice), in referenčne unije, kot je (A1:B2,D4). Primerjave po elementih praznemu elementu dajo tudi tip druge strani, FALSE ob logični vrednosti, kar se ujema s skalarskim pravilom iz v2.384.53, opisanim v primerjalnih verigah in praznih celicah v HotXLS

var
  V: Variant;
begin
  // Book je TXLSXWorkbook iz prvega primera;
  // njegov aktivni list hrani A1:B2 = 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10, enojni argument
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6, skupni obseg B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16, prekrivanje šteje dvakrat
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1, pred v2.384.61 je bilo -1
end;

TXLSXWorkbook.Calculate vrednoti niz formule na aktivnem listu, ne da bi ga shranil — hiter način, da preverite obnašanje pogona. Ena pazljivost glede samega @: HotXLS je zgodovinsko sprejemal @ med dvema referencama kot dvojiško presekovanje in to obliko zdaj vrednoti s pravo semantiko presekovanja. V Excelu 365 je @ enojska predpona implicitnega presekovanja. Ne zapišite @ v besedilo formule in ne pričakujte Excelovega pomena; za presekovanje uporabite presledek, semantiko dinamičnih matrik pa pustite zgoraj opisanim pravilom shranjevanja

Zakaj je Excel zavrnil odpreti datoteko ali izračunal napačno vrednost?

Da je Excel sprejel oznako dinamične matrike, je potreboval tri popravke, ki jih noben test povratnega pretvarjanja skozi sam sebe ne bi ujel, ker je HotXLS v vsakem primeru pravilno prebral svojo lastno izhodno datoteko. Vsakega so odkrili tako, da so odprli izhod HotXLS v Excelu 16 in zamenjali eno spremenljivko naenkrat:

1. GUID razširitve mora biti povsem z malimi črkami. ext uri v xl/metadata.xml mora biti točno {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. Starejša predloga HotXLS ga je zapisala z mešanimi velikimi in malimi črkami, Excel 16 pa je zavrnil odpreti celoten paket, ne le celico. Delovni zvezki, ustvarjeni s TXLSXRange.SetDynamicArrayFormula pred v2.384.68, so imeli enako težavo
2. Besedilo korena matrike nima vodilnega =. Zapisovalnik XLSX shranjeno besedilo korena matrike izpiše dobesedno v <f>. Če bi pretvorjena celica obdržala svoj =, bi element bral <f t="array" ref="E5">=SUM(...)</f>, kar Excel zavrne tudi ob odpiranju. HotXLS ga med pretvorbo odstrani, zato TXLSXCell.Formula bere nazaj brez njega
3. Double(True) je v Delphiju -1. Pretvorba Variant sledi dogovoru COM, kjer je TRUE vseh bitov nastavljenih, VarIsNumeric(True) pa vrne True. Pred v2.384.61 je to naredilo, da je =TRUE*1 vrnil -1, logični matrični elementi pa so bili uvrščeni med števila, zato je šla narobe primerjava, kot je (B1:B2>0)=TRUE. HotXLS zdaj preveri varBoolean, preden Variant obravnava kot število v skalarski aritmetiki, matrični aritmetiki in uvrščanju matričnih elementov, TRUE pa šteje 1

Razredi operandov BIFF8: podrobnosti na ravni bajtov za izdelovalce formatov

V BIFF8 vsak žeton operanda nosi svoj razred operanda v samem bajtu žetona, Excel pa zaupa temu razredu več kot strukturi formule. [MS-XLS] definira razred kot dvobitno polje PtgDataType v bitih 5 in 6 žetona: 1 za referenco, 2 za vrednost, 3 za matriko. Nizkih pet bitov poimenuje žeton, zato ima ista referenca na območje tri črkovanja:

Žeton	Referenčni razred	Razred vrednosti	Matrični razred
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

HotXLS je pri treh od teh zadel napoko na različnih mestih, vsaka pa je v Excelu dala drugačen simptom, medtem ko se je v HotXLS prebrala povsem v redu:

• Matrične konstante referenčnega razreda. Koder je razred izbral iz konteksta, parametri SUM ali ROWS pa so referenčni razred, zato je bila =SUM({1,2}) zapisana s PtgArray kot $20. Excel prikaže celotno formulo kot =#N/A. Matrična konstanta nikoli ne more biti referenca, zato HotXLS od v2.384.63 piše matrični razred $60, kjer koli kontekst zahteva referenco
• Operandi razreda vrednosti pri PtgIsect in PtgUnion. Dvojiški operatorji so jemali operande razreda vrednosti, kar je prav za * in narobe za referenčne operatorje. Z območji $45 pred PtgIsect ($0F) je Excel prebral =SUM(A1:B2 B1:B2) kot =SUM(@A1:B2 @B1:B2) in vrnil #VALUE!. Od v2.384.62 so operandi PtgIsect in PtgUnion ($10) zapisani v referenčnem razredu, $25
• Operandi razreda vrednosti znotraj zapisa ARRAY. Excel uporabi implicitno presekovanje tudi znotraj matrične formule, kadar je operand razreda vrednosti. HotXLS je tam zapisal $45, zato se je enocelična matrična formula za =SUM(A1:B1*{10,100}) v Excelu vrednotila v 10. Od v2.384.68 tok žetonov zapisa ARRAY povzdigne vsako referenco razreda vrednosti in matrično konstanto v matrični razred, $65 in $60, kar je tudi to, kar zapiše Excel

Bralnik, ki ignorira bite razreda, vse tri brezskrbno prenese naprej in nazaj, zato če vzdržujete svoj lasten zapisovalnik BIFF8, primerjajte bite razreda vsakega žetona operanda z datoteko, shranjeno z Excelom za isto formulo, ne samo s številkami žetonov

Hiter pregled

• Excel 365 prikaže @, ko operator v navadni, neoznačeni formuli dobi večcelični obseg ali vgrajeno matriko
• HotXLS od v2.384.68 dalje take formule shrani kot dinamične matrike XLSX za eno celico (cm="1", t="array", metapodatki XLDAPR) in kot matrične formule XLS za eno celico (FORMULA s PtgExp plus ARRAY $0221)
• Štejejo samo operandi operatorjev; obseg, posredovan neposredno argumentu funkcije, ostane navadna formula
• Označene so samo formule, vnešene prek TXLSXCell.Formula ali klasične enocelične Formula / Value; naložene formule ostanejo nedotaknjene
• Pretvorjena korenska celica se prebere nazaj brez vodilnega =
• GUID ext uri dinamične matrike mora imeti male črke, sicer Excel zavrne paket
• V Delphiju je Double(True) enako -1; pred številčno pretvorbo preverite varBoolean
• BIFF8: matrične konstante nikoli referenčni razred, operandi PtgIsect / PtgUnion v referenčnem razredu, operandi zapisa ARRAY v matričnem razredu

HotXLS izvorno bere, zapisuje in preračunava delovne zvezke XLS in XLSX iz Delphija in C++Builderja ter shrani formule z matričnimi operatorji tako, da jih Excel 365 odpre z enakimi vrednostmi, ki jih je izračunal HotXLS. Za izdaje, dokumentacijo in preizkusni prenos glej komponento HotXLS Delphi preglednic