HotXLS tömbképletek: miért @ és miért #VALUE!

Az Excel 365 @-ot szúr be egy =SUM(A1:B1*{10,100}) szerű képletbe, és #VALUE!-t mutat, amikor a fájl közönséges képletként tárolja, mert az Excel ekkor örökölt implicit intersectiont alkalmaz minden operátoroperandusra. v2.384.68 óta a HotXLS Delphi Component úgy tárolja ezeket a tömboperátoros képleteket, ahogy az Excel 365: egycellás dinamikus tömbként XLSX-ben és egycellás tömbképletként XLS-ben

A tünet túléli a kódreview-t. A Delphi szolgáltatásod ír egy munkafüzetet, a HotXLS újraszámolja, és 210-et cache-el a =SUM(A1:B1*{10,100})-ra, az ügyfél pedig Excel 16-ban nyitja meg, és a képletsávban =SUM(@A1:B1*@{10,100})-t, a cellában #VALUE!-t talál. A fájlban semmi nem formátlan. Ami hiányzik, az a metadat, ami megmondja az Excelnek, hogy a képlet dinamikus tömb szabályok alatt íródott, és nélküle az Excel visszaesik a dinamikus tömbök előtti kiértékelési modelljére

Miért tesz @-ot az Excel 365 egy általa jól kiszámolt képletbe?

Az Excel 365 azért tesz @-ot, mert egy dinamikus tömb jelölés nélküli képlet definíció szerint örökölt képlet, és az örökölt képletek mindenhol, ahol egy operátor egyetlen értéket vár, többcellás tartományt egyetlen cellára zsugorítanak. Az a zsugorítás az implicit intersection: az Excel a tartomány azon celláját veszi, ami megosztja a képlet sorát (függőleges tartománynál) vagy oszlopát (vízszintes tartománynál), és ha nincs ilyen cella, az eredmény #VALUE!. Az Excel 365 megtartja ezt a jelentést a régimódi képleteknél, és megjeleníti az @-ot, hogy a zsugorítás látható legyen

Tedd az =SUM(A1:B1*{10,100})-ot az E5-be, és az örökölt olvasat nyilvánvalóvá válik. Az A1:B1 vízszintes tartomány, a képlet az E oszlopban ül, a tartománynak nincs cellája az E oszlopban, így az @A1:B1 #VALUE!, és az egész SUM örökli. Dinamikus tömb szabályok alatt ugyanez a szöveg elemenként szoroz, 1 × 10 + 2 × 100, és 210-et ad vissza. A HotXLS képletengine a v2.384.61-es és v2.384.63-as kiadások óta dinamikus tömb módon értékel ki; a fájlformátum egyszerűen nem mondta ki. Ha az A1:B2-ben 1, 2, 3 és 4 áll, ezek a próbaképletek és ami az Excel 16-ban megjelenik:

Képlet	HotXLS eredmény	Excel 16, sima képletként tárolva	Tárolás v2.384.68 óta
=SUM(A1:B1*{10,100})	210	#VALUE!	Dinamikus tömb, Excel 210-et mutat
=SUM((A1:B2>2)*1)	2	Implicit intersection, rossz vagy hiba	Dinamikus tömb, Excel 2-t mutat
=SUMPRODUCT((A1:B2>2)*1)	2	Implicit intersection, rossz vagy hiba	Dinamikus tömb, Excel 2-t mutat
=MAX(A1:B2-1)	3	Implicit intersection, rossz vagy hiba	Dinamikus tömb, Excel 3-at mutat
=SUM(A1:B2)	10	10	Sima képlet, változatlan

Az utolsó sor ugyanolyan fontos, mint az első négy. A SUM(A1:B2) tartományt ad át közvetlenül egy hivatkozásokat elfogadó függvényparaméternek, így operátor soha nem lát többcellás tartományt, és intersection nem történhet. Maga az Excel 365 is sima képletként menti azt a képletet, és a HotXLS is így tesz

Hogyan tárolja a HotXLS a tömboperátoros képleteket XLSX-ben és XLS-ben

A HotXLS a tömboperátoros képletet XLSX-ben egycellás dinamikus tömbként írja: a <c> elem cm="1"-et hordoz, a képlet <f t="array" ref="E5">, és a csomag kap egy xl/metadata.xml-t egy olyan XLDAPR metadattípussal, aminek a kiterjesztése dynamicArrayProperties fDynamic="1"-t tart. A cm attribútum egy egymásalapú index az adott rész cellMetadata blokkjába, és a mögötte álló XLDAPR rekord mondja meg az Excelnek, hogy „értékeld ezt dinamikus tömb szabályok alatt". Ez ugyanaz a struktúra, amit az Excel 16 ír, ha ugyanezt a képletet gépeled és mented, így állt elő a cél layout egyébként

XLS-ben nincs metadatrész, ezért a HotXLS az egyetlen konstrukciót használja, ami a BIFF8-nak van tömbkiértékelésre: egycellás tömbképlet. A cella FORMULA rekordot kap, aminek a tokenfolyama egyetlen, önmagára mutató PtgExp, majd egy ARRAY rekord ($0221) következik az egycellás tartomány fölött a valódi parzolt képlettel. Az Excel 365 ugyanígy ír dinamikus tömb képleteket XLS-be, és egy öregebb Excel verzió klasszikus Ctrl+Shift+Enter tömbképletet lát a fájlban

Nincs benne új API. A jelölés akkor történik, amikor a képletet a rendes cella API-n keresztül rendeled hozzá, mindkét engineben. XLSX oldalon ez a 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;

    // Operátor tartományon vagy inline tömbön: dinamikus tömbként tárolva
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // Függvénynek közvetlenül átadott tartomány: sima <f> marad
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

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

    // A tömbgyökér megtartja a szövegét a kezdő '=' nélkül
    Writeln(Sheet.Cells[5, 5].Formula);              // SUM(A1:B1*{10,100})

    Book.SaveAs('probe.xlsx');   // az E5 és E6 cm="1" + t="array"-t kap
  finally
    Book.Free;
  end;
end;

A konverzió után a TXLSXCell.Formula a szöveget = nélkül adja vissza, ugyanabban a formában, ahogy a TXLSXRange.SetDynamicArrayFormula tárolja, így a hozzárendelés után képletszövegeket összehasonlító kódnak normalizálnia kell a kezdő =-ot

A klasszikus engine ugyanezt a szabályt követi a IXLSRange.Formula-n át egyetlen cellán. A képlet hozzárendelése belül átirányítja az egycellás tömb útra, így a mentett XLS a FORMULA meg ARRAY párost tartalmazza:

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 rekord
  Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)';         // ARRAY rekord
  Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)';           // sima FORMULA

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

Ha többcellás eredményt rögzítesz, nem skaláris aggregátumot, az explicit API-ok továbbra is a jó eszközök: SetArrayFormula előre méretezett téglalaphoz, ahogy a dinamikus tömb spill képletek a HotXLS-szel írja, vagy TXLSXRange.SetDynamicArrayFormula, ha a XLSX dinamikus tömb jelölést akarod egy általad méretezett tartományra. A cikk automatikus útja csak egyetlen cellába gépelt képleteket fed le

Mely képleteket jelöli dinamikus tömbként a HotXLS?

A HotXLS csak akkor jelöl meg egy képletet, ha egy operátorának van olyan operandusrészfája, ami tömböt produkál. A teszt a kompilált szintaxfafán fut, és egy operandus akkor produkál tömböt, ha többcellás tartomány, inline tömbkonstans vagy másik operátorkifejezés, aminek magának van ilyen operandusa. A zárójelek átlátszóak. A számító operátorok az aritmetikaiak (+ - * / ^), a konkatenáció (&), a hat összehasonlítás, az unáris plusz meg mínusz és a százalék:

• Az A1:B1*{10,100}, (A1:B2>2)*1, --(B1:B2>0) és A1:B2-1 megjelölődnek, bárhol is jelennek meg a képletben, a SUMPRODUCT-on belül is beleértve
• A SUM(A1:B2) és a SUMPRODUCT(A1:A2,{1;10}) nem jelölődnek, mert a tartomány és a tömb közvetlenül függvényargumentumba mennek, és operátor nem érinti őket
• Az A1*2 vagy a SUM(A1,B1)*2 nem jelölődnek: az egycellás hivatkozások és a függvényeredmények skalárok ennek a tesztnek

Három határ szándékos. Először a jelölés csak akkor történik, ha a képlet az API-n át kerül be, azaz TXLSXCell.Formula az XLSX engineben és egycellás Formula vagy Value hozzárendelés a klasszikus engineben. A fájlból betöltött képletek pontosan úgy íródnak vissza, ahogy találták őket, mert egy másik producertől származó örökölt képlet szándékosan függhet implicit intersectiontől. Másodszor az olyan szöveg, amiben se : se { nincs, második kompilálás nélkül kimarad. Harmadszor egy spillelő képlet, mint az önmagában álló =A1:B1*2, egycellás dinamikus tömbként jelölődik, oda rögzítve, ahova tetted. A HotXLS nem spilleli, és az Excel legközelebbi újraszámoláskor a szomszédos cellákra terjeszti az eredményt

Ez az operandusszabály a defined name-ek implicit intersectionja a HotXLS-ben cikkben taglalt argumentumosztály-szabály testvére. Az a cikk a value osztályként deklarált függvényparaméterekről szól; ez az operátorokról, amik az örökölt modellben mindig értéket követelnek

Mi változott a számító engineben, hogy az eredmények egyezzenek

A v2.384.68-as tárolási javítás arra épít, hogy a HotXLS képletengine már Excel 365 értékeket ad vissza, ami több korábbi javítást vett igénybe mindkét engineben. A leglátványosabb a SUMPRODUCT volt: v2.384.61-ig csak kettő vagy több sima tartományt fogadott, így a SUMPRODUCT((B1:B2>0)*1), a SUMPRODUCT(--(B1:B2>0)) és még az egyargumentumos SUMPRODUCT(B1:B2) is #N/A-t adott. A HotXLS most kifejezésargumentumokat elemenként értékel ki az Excel szabályaival:

• minden argumentumnak pontosan ugyanolyan alakúnak kell lennie, egy skalár 1 × 1-ként számolva, különben az eredmény #VALUE!
• bármely argumentumon belüli hibaérték maga az eredmény
• a szöveg és logikai elemek 0-ként számolnak, így a (B1:B2>0)*1 vagy -- még mindig kell, hogy a TRUE 1 legyen
• a csupa sima tartományból álló argumentumok megtartják az eredeti streaming ciklust, így nagy tartományok nem anyagosódnak tömbbé

A SUM család (SUM, COUNT, AVERAGE, MIN, MAX, COUNTA) ugyanazt az elemenkénti kiértékelőt használja, amikor egy argumentum tartomány fölötti operátorkifejezés, így a =SUM((B1:B2>0)*1) mindkét sort megszámolja az első cella megnézése helyett. A v2.384.62 a szóköz intersection operátort két hivatkozás közös téglalapját visszaadóvá tette, #NULL!-lal, ha nem fedik át egymást, így a =SUM(A1:B2 B1:B2) 6, nem 2, és az eredmény táplálhatja a ROWS vagy INDEX szerű hivatkozásparamétereket. A v2.384.63 inline tömbkonstansokat vett fel a parserbe, mint az {1,2;3,4} (a vessző oszlopokat, a pontosvessző sorokat választ el), és hivatkozásuniókat, mint az (A1:B2,D4). Az elemenkénti összehasonlítások emellett a másik oldal típusát adják az üres elemnek, FALSE-t logikaival szemben, egyezve a v2.384.53-as skalárszabállyal, amit a összehasonlítási láncok és üres cellák a HotXLS-ben ír le

var
  V: Variant;
begin
  // A Book az első példa TXLSXWorkbook-ja;
  // az aktív sheetje A1:B2 = 1, 2, 3, 4 értékeket tart
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10, egyetlen argumentum
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6, közös tartomány B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16, az átfedés kétszer számolva
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1, v2.384.61 előtt -1 volt
end;

A TXLSXWorkbook.Calculate képletszöveget értékel ki az aktív sheeten tárolás nélkül, gyors módja annak, hogy megnézd az engine viselkedését. Egy óvintézkedés magáról az @-ról: a HotXLS történetileg két hivatkozás közti @-ot bináris intersectionként fogadta, és mostantól valódi intersection szemantikával értékeli azt a formát. Az Excel 365-ben az @ unáris implicit-intersection prefix. Ne írj @-ot a képletszövegbe az Excel jelentésében bízva; intersectionhoz szóközt használj, a dinamikus tömb szemantikát pedig hagyd a fenti tárolási szabályokra

Miért nem nyitotta meg az Excel a fájlt, vagy miért számolt rossz értéket?

Hogy az Excel elfogadja a dinamikus tömb jelölést, három javítás kellett, amiket egyetlen ön-körutazó teszt sem kapott volna el, mert a HotXLS minden esetben helyesen olvasta vissza a saját kimenetét. Mindegyiket Excel 16-ban nyitott HotXLS kimeneten, egyszerre egy változó cseréjével találták:

1. A kiterjesztés GUIDjének csupa kisbetűnek kell lennie. Az ext uri az xl/metadata.xml-ben pontosan {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3} kell legyen. Egy öregebb HotXLS template vegyesen nagybetűsen írta, és az Excel 16 az egész csomag megnyitását utasította el, nem csak a cellát. A TXLSXRange.SetDynamicArrayFormula-mal v2.384.68 előtt készített munkafüzeteknek ugyanez a bajuk volt
2. A tömbgyökér szövege nem hordoz kezdő =-ot. Az XLSX író egy tömbgyökér tárolt szövegét szó szerint írja az <f>-be. Ha a konvertált cella megtartotta a =-ot, az elem <f t="array" ref="E5">=SUM(...)</f> lenne, amit az Excel megnyitáskor szintén elutasít. A HotXLS konverzió közben levágja, ezért a TXLSXCell.Formula nélküle adja vissza
3. A Double(True) -1 Delphiben. A Variant konverzió a COM konvenciót követi, ahol a TRUE minden bitje be van állítva, és a VarIsNumeric(True) is True. v2.384.61 előtt ettől a =TRUE*1 -1-et adott, és a logikai tömbelemek számként osztályozódtak, így egy (B1:B2>0)=TRUE összehasonlítás rosszra ment. A HotXLS mostantól varBoolean-ra tesztel, mielőtt egy Variantot számként kezelne skaláris aritmetikában, tömbaritmetikában és tömbelem-osztályozásban, és a TRUE 1-ként számol

BIFF8 operandusosztályok: bájtszintű részletek formátumimplementálóknak

BIFF8-ban minden operandus token az operandusosztályát magában a tokenbájtban hordozza, és az Excel jobban bízik abban az osztályban, mint a képlet struktúrájában. A [MS-XLS] az osztályt kétbites PtgDataType mezőként definiálja a token 5. és 6. bitjében: 1 referencia, 2 érték, 3 tömb. Az alsó öt bit a token nevét adja, így ugyanannak a területhivatkozásnak három helyesírása van:

Token	Referenciaosztály	Értékosztály	Tömbosztály
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

A HotXLS háromat elrontott ebből különböző helyeken, és mindegyik különböző tünetet adott Excelben, miközben HotXLS-ben szépen visszaolvasott:

• Referenciaosztályú tömbkonstansok. Az enkóder a kontextusból választotta az osztályt, és a SUM vagy ROWS paraméterek referenciaosztályúak, így a =SUM({1,2}) PtgArray-ként $20-ként íródott. Az Excel az egész képletet =#N/A-ként jeleníti meg. Egy tömbkonstans soha nem lehet referencia, ezért v2.384.63 óta a HotXLS tömbosztályú $60-ot ír, ahol a kontextus referenciát kér
• A PtgIsect és PtgUnion értékosztályú operandusai. A bináris operátorok értékosztályú operandusokat kaptak, ami a *-nak jó, de a hivatkozásoperátoroknak rossz. $45-ös területekkel a PtgIsect ($0F) előtt az Excel a =SUM(A1:B2 B1:B2)-t =SUM(@A1:B2 @B1:B2)-ként olvasta, és #VALUE!-t adott. v2.384.62 óta a PtgIsect és PtgUnion ($10) operandusai referenciaosztályban, $25-ként íródnak
• Értékosztályú operandusok az ARRAY rekordon belül. Az Excel implicit intersectiont alkalmaz tömbképleten belül is, ha egy operandus értékosztályú. A HotXLS ott $45-öt írt, így a =SUM(A1:B1*{10,100}) egycellás tömbképlete Excelben 10-re értékelődött ki. v2.384.68 óta egy ARRAY rekord tokenfolyama minden értékosztályú hivatkozást és tömbkonstansot tömbosztályra emel, $65-re és $60-ra, ami az, amit az Excel ír

Egy olvasó, ami ignorálja az osztálybiteket, mindhármat szépen körbeviszi, ezért ha saját BIFF8 írót tartasz fenn, vesd össze minden operandus token osztálybitjeit egy Excel-mentett fájllal ugyanarról a képletről, nem csak a tokenszámokat

Gyorsreferencia

• Az Excel 365 @-ot mutat, amikor egy sima, jelöletlen képlet operátora többcellás tartományt vagy inline tömböt kap
• A HotXLS v2.384.68 és újabb XLSX egycellás dinamikus tömbként tárolja az ilyen képleteket (cm="1", t="array", XLDAPR metadat) és XLS egycellás tömbképletként (FORMULA PtgExppel meg ARRAY $0221)
• Csak operátoroperandusok számítanak; függvényargumentumba közvetlenül átadott tartomány sima képlet marad
• Csak a TXLSXCell.Formula-n vagy a klasszikus egycellás Formula / Value-n át bevitt képletek jelölődnek; a betöltött képletek békén maradnak
• A konvertált gyökécella a kezdő = nélkül adja vissza a szöveget
• A dinamikus tömb ext uri GUIDjének kisbetűsnek kell lennie, különben az Excel elutasítja a csomagot
• Delphiben a Double(True) -1; tesztelj varBoolean-ra numerikus konverzió előtt
• BIFF8: a tömbkonstansok soha nem referenciaosztályúak, a PtgIsect / PtgUnion operandusok referenciaosztályban, az ARRAY rekord operandusai tömbosztályban

A HotXLS XLS és XLSX munkafüzeteket olvas, ír és számol natívan Delphiből és C++Builderből, és úgy tárolja a tömboperátoros képleteket, hogy az Excel 365 ugyanazokkal az értékekkel nyissa meg őket, amiket a HotXLS kiszámolt. Kiadásokért, dokumentációért és próbaverzióért lásd a HotXLS Delphi spreadsheet component oldalt