HotXLS definiált nevek implicit metszése Delphiben

Az egész oszlopra hivatkozó definiált nevet az Excel egyetlen cellaként olvassa, ha skaláris pozícióban szerepel: a =Vertical+1 a 7. sorban a Vertical 7. sorába eső celláját jelenti, nem a teljes területet. A HotXLS Delphi Component ezt az implicit metszést a v2.382.4-ben két szinten alkalmazza, az értékelésnél és a függőségkinyerésnél, mert egy 4805 képletet tartalmazó hitelsablon megmutatta, hogy az érték helyes kiszámítása önmagában nem elég. Amikor a függőségbejáró a nevet a teljes területére tágítja, egy olyan utólagos képlet, amely a terület bármely celláját táplálja, olyan kört zár be, amely nem is létezik, és a TXLSXWorkbook.Recalculate az egész munkafüzetet visszautasítja

A szóban forgó sablon egy szokásos hiteltörlesztési munkafüzet. Ha minden gyorsítótárazott értéket 777-re mérgezünk, és lefuttatjuk a teljes Recalculate-et, mindkét motorarchitektúra 23-at adott vissza, ami a lxErrorRef, a körkörös hivatkozás hibakódja. A 4805 képletből 3842 nem egyezett a független elvárással, a B18 #VALUE!-t tartalmazott, az E18 még mindig 777 volt, a J7-ben lévő befizetésszám pedig a befejezetlen egyenlegoszlop helyőrzőit olvasta ki. Három külön hiba bújt meg egyetlen visszatérési kód mögött, és ez a cikk végigmegy mindegyiken a javító forráskóddal együtt

Miért hoz létre téves kört egy oszlopnévre mutató skaláris hivatkozás?

Mert a függőségi gráf csak éleket ismer, és egy képlettől egy 480 soros területig vezető él 480 él, amelyek közül az egyik visszafelé mutat egy olyan cellán keresztül, amely a képlettől függ. Vegyük a B1-ben lévő =IF(TRUE,Vertical+1,0) képletet, ahol a Vertical definíciója Inputs!$A$1:$A$2, és az A2-ben álló =B1+1 képletet. Az Excel a B1-et A1+1-ként, az A2-t B1+1-ként értékeli ki, ez egy egyenes lánc. Egy olyan bejáró, amely a B1-et az A1:A2 területtől függőként jegyzi fel, az A2-t a B1 előzményévé teszi, az A2 viszont már most is a B1-et sorolja fel előzményeként, és a HotXLS növekményes újraszámítását hajtó Kahn-sor soha nem látja úgy, hogy bármelyik csomópont bemeneti foka elérné a nullát. Pontosan ebből a mintából épülnek a hitelsablonok: minden periódussor névvel hivatkozott oszlopokat használ az egyenleghez, a kamathoz és a befizetésszámhoz, minden név a teljes ütemezésre kiterjed, minden sor pedig ír is ezekbe az oszlopokba. Tágítsuk ki a neveket, és a gráf egyetlen óriási erősen összefüggő komponenssé válik. Értékeljük ki őket implicit metszéssel, és a gráf rövid láncok halmaza lesz, soronként egy, pontosan ez az, amit az ECMA-376 Part 1 §18.17.2 leír egy olyan referenciaoperandusra, amelyet egyetlen értéket igénylő helyen használunk fel

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Inputs');
    Book.DefinedNames.Add('Vertical', 'Inputs!$A$1:$A$2');
    Book.DefinedNames.Add('Alias', '=Vertical');
    Sheet.Cells[1, 1].Value := 1;
    // Skaláris pozíció: a Vertical az A1-re esik össze, mert a képlet az 1. sorban van
    Sheet.Cells[1, 2].Formula := '=IF(TRUE,Vertical+1,0)';
    Sheet.Cells[2, 1].Formula := '=B1+1';
    // Az a név is metsződik, amelynek a definíciója egy másik név, tehát ez az A2
    Sheet.Cells[2, 2].Formula := '=Alias';
    // Referenciaosztályú argumentum: a teljes terület összegződik, nincs metszés
    Sheet.Cells[3, 2].Formula := '=SUM(Vertical)';
    // A 6. sor kívül esik az A1:A2-n, a metszés üres, és az IFERROR elkapja
    Sheet.Cells[6, 2].Formula := '=IFERROR(Vertical,42)';

    if Book.Recalculate = lxOk then
    begin
      // B1 = 2, A2 = 3, B2 = 3, B3 = 4, B6 = 42
      // A v2.382.4 előtt ez az ág elérhetetlen volt: a B1 -> A2 -> B1 kör volt
    end;
  finally
    Book.Free;
  end;
end;

Hogyan dönti el a HotXLS, hogy egy argumentum skaláris?

A HotXLS a függvénytáblából olvassa ki a választ, nem az argumentum alakjából. A TXLSFormula.InitFuncHash minden bejegyzése a THashFunc.SetValue híváson keresztül regisztrálódik, opcionális, argumentumonkénti osztálysztringgel: az 'IF' a '100'-at hordozza, a 'SUMIF' a '010'-et, a 'VLOOKUP' a '1011'-et, a 'SUM' pedig egyet sem, így minden argumentuma a függvényszintű 0-s osztályra esik vissza. Az új TXLSFormula.FunctionArgumentClass(APtg, AArgument) a THashFuncEntry.ArgClass mezőn keresztül teszi elérhetővé ezt a bájtot, az 1-es eredmény pedig értékosztályt jelent. Ugyanaz a három osztály ez, amelyet az [MS-XLS] §2.2.2 rendel az operandustokenekhez, és a kódoló eddig is támaszkodott rájuk: amikor referenciát ír, a ptg-t $24 + $20 * aClass alakban számítja, ami a 0-s osztályra PtgRef-et, az 1-esre PtgRefV-t, a 2-esre PtgRefA-t ad. Az Excel által írt BIFF-fájl minden referenciatokenben tárolja ezt az osztályt, így egy olyan motor, amelynek a táblája megegyezik a specifikációval, az adatok megvizsgálása nélkül meg tudja válaszolni, hogy egy argumentum skaláris-e. A SUMIF középső argumentuma a feltétel, egy érték; az első és a harmadik terület, azaz referencia. A SUMPRODUCT függvényszintű 2-es, tömb osztállyal van regisztrálva, ezért a =SUMPRODUCT(Vertical,Vertical) továbbra is a teljes területet szorozza össze

Három függvény az első argumentumon túl semmit nem kérdez meg a saját táblabejegyzésétől. Az IF (ptg 1), a CHOOSE (ptg 100) és az IFERROR (ptg 255) átengedik azt, amit kiválasztanak, így az ágargumentumaik annak a pozíciónak az osztályát öröklik, amelyet maga a függvény foglal el. Ez az egyetlen szabály az, ami miatt a G2-ben lévő =CHOOSE(1,Vertical,0) az A2-re oldódik fel, miközben a mellette álló =SUMIF(Vertical,">0",Vertical) továbbra is mindkét sort összegzi, és ez az a szabály, amelyet egy törlesztési ütemezés a legtöbbet gyakorol, mert a perióduscellái az IF-re támaszkodnak annak vizsgálatához, hogy a hitel nyitva van-e még

Az osztály átvitele a függőségbejáráson

Az lxCalc.pas-ban lévő függőségkinyerő rekurzív Walk a lefordított szintaxisfán, és kétszer is létezik: egyszer a TXLSCalculator.ExtractDependencies metódusban a munkafüzeten belüli gráfhoz, egyszer pedig az ExtractWorkspaceDependencies metódusban a munkafüzetek közötti gráfhoz. A v2.382.4 két további paramétert ad mindkét bejárónak. Az AScalar egy képlet gyökerénél True értékkel indul, minden függvénygyerekre újraszámolódik a FunctionArgumentClass alapján, az 1-es, 100-as és 255-ös ptg ágargumentumainál pedig változatlanul öröklődik. Az ANameRoot csak akkor lesz True, amikor a bejáró egy név lefordított definíciójába ereszkedik le, és kizárólag SA_GROUP csomópontokon, azaz zárójeleken keresztül marad meg, így az =A1:A2+1 alakban definiált név nem tévesztődik össze egy sima területtel. Ha mindkét jelző True egy SA_RANGE csomóponton, az AddResolvedRange ugyanazzal a segédfüggvénnyel szűkíti a területet, amelyet az értékelő használ, mielőtt rögzíti a függőséget. A segédfüggvény elég rövid ahhoz, hogy teljes egészében idézzük

function IntersectNamedScalarRange(CurRow, CurCol: Integer;
  var Row1, Row2, Col1, Col2: Integer): Boolean;
begin
  Result := False;
  if (Row1 = Row2) and (Col1 = Col2) then Exit(True);   // már egy cella
  if (Col1 = Col2) and (CurRow >= Row1) and (CurRow <= Row2) then
  begin
    Row1 := CurRow; Row2 := CurRow;                     // egyszeres oszlop: ezt a sort vesszük
    Exit(True);
  end;
  if (Row1 = Row2) and (CurCol >= Col1) and (CurCol <= Col2) then
  begin
    Col1 := CurCol; Col2 := CurCol;                     // egyszeres sor: ezt az oszlopot vesszük
    Result := True;
  end;
end;

Amit a segédfüggvény visszautasít — kétdimenziós terület, több munkalapra átnyúló hivatkozás vagy olyan képlet, amelynek a sora kívül esik a névvel jelölt oszlopon —, az az értékelési oldalon #VALUE!-t ad, a gráf oldalán pedig egyáltalán nem rögzül függőség, pontosan ahogy az Excel viselkedik egy üres metszésnél. Az értékelési oldal a TXLSCalculator.GetValueItemName metódusban lakik: lehántja az SA_GROUP burkokat a lefordított definícióról, és ha a gyökér egy SA_RANGE, meghívja a GetRangeInfo metódust, elvégzi a metszést, majd az FGetValue útvonalon kiolvassa azt az egy cellát a teljes definíció kiértékelése helyett. A külső hivatkozások a régi úton maradnak, mert nincs helyi sor, amelyhez metszeni lehetne. Hogy egy név tárolása és hatóköre egyáltalán honnan származik, azt a definiált nevekről és munkalapok közötti képletekről szóló cikk tárgyalja; itt csak az a lényeg, hogy mit tesz a motor, miután a név feloldódott

Miért olvasott 777-et a MATCH egy félig kiszámolt oszlopon?

Mert a MATCH keresőtömb-argumentuma egy vizsgálati referencia, a vizsgálati referenciákat pedig szándékosan kizárták a kiértékelési sorrendből. A keresési vizsgálatról szóló cikk bevezette a TXLSDepRange.LookupScan mezőt, és azzal a fejezettel zárt, hogy miről mondunk le, ha a vizsgálati éleket kivesszük a sorrendezésből: egy keresőképlet lefuthat, mielőtt a tartományában minden cella újraszámolódott volna, és elavult értékeket olvas. Egy interaktív munkamenetben ez a következő menetben konvergál. Egy mérgezett sablon kötegelt újraszámításánál viszont nem, és a =MATCH(0.01,Balances,-1)+1 alakban definiált PaymentCount az egyenlegoszlopban még ott ülő 777-es helyőrzőket olvasta ki, és olyan periódusszámot adott vissza, amely nem lehetett helyes

A TXLSDepGraph.TopoOrder mostantól lágy sorrendezési élként kezeli a vizsgálati éleket. A kemény bemeneti fok mellett vezet egy ScanInDeg tömböt is, amely csomópontonként számolja a piszkos vizsgálati előzményeket, és csökkenti, ahogy ezek az előzmények kibocsátódnak, azokra a ScanPrecedents, ScanDependents és ScanPrecedentCount listákra támaszkodva, amelyeket a korábbi módosítás már eltárolt. Minden iterációban a Kahn-sor végignézi a kész ablakát az első olyan csomópontért, amelynek a ScanInDeg értéke nulla, és azt a fejre cseréli; ha minden kész csomópont egy vizsgálati előzményre vár még, a fej a stabil sorrendjében kerül ki. A vizsgálati élek soha nem kerülnek be a kemény bemeneti fokba, így egy önmagára mutató, a saját oszlopán végigfutó VLOOKUP továbbra is legális, egy olyan keresés viszont, amely megvárhat egy befejezhető előzményt, mostantól meg is várja. Az ezt rögzítő regressziós teszt, a LookupScan_WaitsForDirtyFormulaValues, három egyenlegcellát mérgez 777-re, és elvárja, hogy a PaymentCount 3-ként jöjjön vissza, majd a bemenetet nullára billenti, és elvárja, hogy a =IFERROR(PaymentCount,99) lássa a #N/A-t, és 99-et adjon vissza

Honnan jött a négy tizedesre csonkolás?

A Delphi Variant-aritmetikából, és kizárólag beágyazott pozíciókban. A TXLSCalculator.GetValueItem bináris operátorai a legfelső szintű + vagy - műveletet már eddig is két Double lokális változóba másolták, így a =B1-A1 rendben volt. A =IF(TRUE,B1-A1,0) belsejében ugyanez a kivonás Value := Value - SubValue alakban futott két Varianton, és amikor az egyik operandus egy Int64 cellaérték, a másik pedig egy Double volt, a megfigyelt eredmény egy Currency lett, egy négy tizedesjegyű fixpontos típus, így az 1066.1854641400994 mínusz 120 négy tizedesre csonkolva jött vissza. Egy olyan ütemezésben, ahol minden befizetés az előző sorból halmozódik, ez a hiba több száz perióduson keresztül vándorol, mire eléri az összesítéseket

// TXLSCalculator.GetValueItem, bináris aritmetikai ág (lxCalc.pas)
if VarIsNull(Value) then Value := 0;
if VarIsNull(SubValue) then SubValue := 0;
// A vegyes Int64/Double Variant-aritmetika Currency típusra léphet elő.
// A táblázatkezelő aritmetikájának meg kell tartania a lebegőpontos pontosságot.
if VarIsNumeric(Value) then Value := Double(Value);
if VarIsNumeric(SubValue) then SubValue := Double(SubValue);

Az őr egyaránt lefut a SA_ADD, SA_SUB, SA_MUL és SA_DIV előtt, az Arithmetic_MixedInt64AndDoubleKeepsPrecision regresszió pedig Int64(120)-at tárol az A1-ben és 1066.1854641400994-et a B1-ben, majd a beágyazott különbséget és összeget 1E-10-hez, a szorzatot és a hányadost 1E-8-hoz, illetve 1E-12-höz ellenőrzi. A HotXLS nem állítja, hogy ismeri az RTL összes előléptetési szabályát a vegyes Variant-típusokra fordítóverziók között; azt állítja, hogy a táblázatkezelő aritmetikája IEEE double, és mostantól mindkét operandust double-lé teszi, mielőtt az operátor látná őket, ami megszünteti a kérdést

Mit garantál a javítás, és mit nem

A v2.382.4 után mindkét motorarchitektúra lxOk-ot ad vissza a mérgezett sablonra, mind az 4805 gyorsítótárazott érték 1E-7-en belül egyezik a független, soronkénti elvárással, és azok az állítások is megállják a helyüket, hogy a gyorsítótárak tényleg mérgezettek voltak, hogy a forrás hash nem változott, és hogy minden képlet megvan még. Ehhez nem kellett iterációt bekapcsolni, és hibakódot sem kellett elnyomni. Egy néven keresztül vezető valódi kör, az A1-ben lévő =B1, ahol a B1 továbbra is a Vertical nevet olvassa, továbbra is hibát ad vissza, és a NamedScalarRanges_IntersectWithoutFalseCycles teszt pontosan ezzel az állítással zárul

A határokat érdemes egyenesen kimondani. Az implicit metszés csak arra a névre érvényes, amelynek a lefordított definíciója a zárójelek lehántása után egyetlen munkalapon lévő egyszeres oszlop vagy egyszeres sor; egy kétdimenziós név skaláris pozícióban #VALUE!, akárcsak az Excelben, egy olyan függvény pedig, amelyet a tábla nem ismer, a FunctionArgumentClass metódustól 0-s osztályt kap, így a névargumentumai továbbra is teljes egészükben tágulnak. A lágy sorrendezés preferencia, nem garancia: a csak vizsgálati élekből álló kör továbbra is stabil sorrendben értékelődik ki, és azt olvassa, ami épp a gyorsítótárban van, ezt a viselkedést vállalta be a keresési vizsgálatról szóló cikk szándékosan. A teljes sablonra vonatkozó eredmény pedig egy független elvárásszkripttel van verifikálva, nem egy másik táblázatkezelő motorral, mert a referenciaként használt irodai csomag nem végzett az eredeti sablon újraszámításával egy 60 másodperces kereten belül. A HotXLS natív Delphi és C++Builder táblázatkezelő komponens, amely Excel telepítése nélkül olvassa, számolja újra és írja az XLS, XLSX, ODS és CSV formátumokat; a névmetszés, az argumentumosztály-tábla és a lágy vizsgálati sorrendezés minden formátumra érvényes, mert a számítási motor közös, az aktuális függvénylefedettség pedig a HotXLS Delphi táblázatkezelő komponens termékoldalán van felsorolva