HotXLS: implicitná intersection pre defined names

Defined name, ktorý odkazuje na celý stĺpec, číta Excel ako jednu bunku, keď sa objaví v skalárnej pozícii: =Vertical+1 v riadku 7 znamená „bunka riadku 7 v Vertical“, nie celá oblasť. HotXLS Delphi Component aplikuje túto implicitnú intersection vo v2.382.4 na dvoch úrovniach, pri vyhodnocovaní aj pri extrakcii závislostí, pretože úverová šablóna s 4805 formulami ukázala, že správna hodnota nestačí. Keď dependency walker rozbalí meno na celú jeho oblasť, downstream formula, ktorá napája ktorúkoľvek bunku tej oblasti, uzavrie cyklus, ktorý neexistuje, a TXLSXWorkbook.Recalculate odmietne celý workbook

Spomínaná šablóna je bežný workbook na amortizáciu úveru. So všetkými cached hodnotami otrávenými na 777 a s plným behom Recalculate vrátili obe architektúry engine 23, čo je lxErrorRef, kód circular reference. 3842 z 4805 formúl nezhodovalo s nezávislým očakávaním, B18 držalo #VALUE!, E18 bolo stále 777 a počet platieb v J7 prečítal placeholdery v nedokončenom stĺpci zostatku. Za jedným návratovým kódom sa skrývali tri samostatné defekty a tento článok prechádza každý z nich spolu so zdrojom, ktorý ho opravil

Prečo skalárny odkaz na meno stĺpca vytvára falošný cyklus?

Pretože dependency graf pozná len hrany, a hrana z formuly do oblasti so 480 riadkami je 480 hrán, z ktorých jedna vedie späť cez bunku, ktorá závisí od tej formuly. Vezmite =IF(TRUE,Vertical+1,0) v B1 s Vertical definovaným ako Inputs!$A$1:$A$2 a =B1+1 v A2. Excel vyhodnotí B1 ako A1+1 a A2 ako B1+1, teda priamy reťaz. Walker, ktorý zaznamená B1 ako závislé od A1:A2, spraví z A2 precedens B1, A2 už B1 ako precedens uvádza, a Kahn queue, ktorá poháňa inkrementálny prepočet v HotXLS, sa nikdy nedočká, že niektorý z nódov dosiahne in-degree nula. Z tohto vzoru sú postavené úverové šablóny: každý riadok periódy odkazuje na pomenované stĺpce pre zostatok, sadzbu a počet platieb, každé meno pokrýva celý splátkový kalendár a každý riadok do tých stĺpcov aj zapisuje. Rozbaľte mená a graf je jeden obrovský strongly connected component. Vyhodnoťte ich s implicitnou intersection a graf je sada krátkych reťazov, jeden na riadok, čo je presne to, čo ECMA-376 Part 1 §18.17.2 opisuje pre reference operand spotrebovaný tam, kde sa vyžaduje jediná hodnota

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árna pozícia: Vertical sa zbalí na A1, pretože formula je v riadku 1
    Sheet.Cells[1, 2].Formula := '=IF(TRUE,Vertical+1,0)';
    Sheet.Cells[2, 1].Formula := '=B1+1';
    // Meno, ktorého definícia je iné meno, stále intersectuje, takže toto je A2
    Sheet.Cells[2, 2].Formula := '=Alias';
    // Argument referenciavej triedy: sčíta sa celá oblasť, žiadna intersection
    Sheet.Cells[3, 2].Formula := '=SUM(Vertical)';
    // Riadok 6 leží mimo A1:A2, intersection je prázdna a IFERROR to zachytí
    Sheet.Cells[6, 2].Formula := '=IFERROR(Vertical,42)';

    if Book.Recalculate = lxOk then
    begin
      // B1 = 2, A2 = 3, B2 = 3, B3 = 4, B6 = 42
      // Pred v2.382.4 bola táto vetva nedosiahnuteľná: B1 -> A2 -> B1 bol cyklus
    end;
  finally
    Book.Free;
  end;
end;

Ako HotXLS rozhodne, že argument je skalárny?

HotXLS číta odpoveď z funkčnej tabuľky, nie z tvaru argumentu. Každý záznam v TXLSFormula.InitFuncHash sa registruje cez THashFunc.SetValue s voliteľným reťazcom triedy pre jednotlivé argumenty: 'IF' nesie '100', 'SUMIF' nesie '010', 'VLOOKUP' nesie '1011' a 'SUM' nenesie nič, takže všetky jeho argumenty padajú na triedu 0 na úrovni funkcie. Nová TXLSFormula.FunctionArgumentClass(APtg, AArgument) vystavuje ten bajt cez THashFuncEntry.ArgClass a výsledok 1 znamená value class. Sú to tie isté tri triedy, ktoré [MS-XLS] §2.2.2 priraďuje operand tokenom, a encoder na nich už závisel: keď zapisuje referenciu, počíta ptg ako $24 + $20 * aClass, čo dáva PtgRef pre triedu 0, PtgRefV pre triedu 1 a PtgRefA pre triedu 2. BIFF súbor zapísaný Excelom ukladá tú triedu v každom referenčnom tokenu, takže engine, ktorého tabuľka sedí so špecifikáciou, dokáže odpovedať na otázku „je tento argument skalárny“ bez toho, aby sa pozrel na dáta. Prostredný argument SUMIF je kritérium, teda hodnota; prvý a tretí sú oblasti, teda referencie. SUMPRODUCT je registrovaný s triedou 2 na úrovni funkcie, teda array, a preto =SUMPRODUCT(Vertical,Vertical) stále násobí celú oblasť

Tri funkcie sa so svojím vlastným záznamom v tabuľke neradia v ničom za prvým argumentom. IF (ptg 1), CHOOSE (ptg 100) a IFERROR (ptg 255) prepúšťajú to, čo si vyberú, takže ich vetvové argumenty dedia triedu pozície, ktorú zaberá samotná funkcia. Práve toto jedno pravidlo dovoľuje, aby sa =CHOOSE(1,Vertical,0) v G2 vyriešilo na A2, kým =SUMIF(Vertical,">0",Vertical) vedľa neho stále sčíta oba riadky, a je to pravidlo, ktoré amortizačný kalendár používa najviac, pretože jeho bunky periód sa opierajú o IF pri teste, či je úver ešte otvorený

Ako sa trieda prenáša cez dependency walk

Extrahovač závislostí v lxCalc.pas je rekurzívny Walk po skompilovanom syntax tree a existuje dvakrát, raz v TXLSCalculator.ExtractDependencies pre per-workbook graf a raz v ExtractWorkspaceDependencies pre cross-workbook graf. v2.382.4 dáva obom walkerom dva parametre navyše. AScalar začína ako True v koreni formuly, prepočítava sa pre každé funkčné dieťa z FunctionArgumentClass a pre vetvové argumenty ptg 1, 100 a 255 sa prenáša nezmenený. ANameRoot sa nastaví na True len vtedy, keď walker zostúpi do skompilovanej definície mena, a prežije len cez SA_GROUP nody, teda zátvorky, takže meno definované ako =A1:A2+1 sa nepletie s obyčajnou oblasťou. Keď sú oba flagy True na SA_RANGE node, AddResolvedRange zúži oblasť tým istým helperom, ktorý používa evaluator, skôr než závislosť zaznamená. Helper je dosť krátky na to, aby sa citoval celý

function IntersectNamedScalarRange(CurRow, CurCol: Integer;
  var Row1, Row2, Col1, Col2: Integer): Boolean;
begin
  Result := False;
  if (Row1 = Row2) and (Col1 = Col2) then Exit(True);   // už je to bunka
  if (Col1 = Col2) and (CurRow >= Row1) and (CurRow <= Row2) then
  begin
    Row1 := CurRow; Row2 := CurRow;                     // jeden stĺpec: vezmi tento riadok
    Exit(True);
  end;
  if (Row1 = Row2) and (CurCol >= Col1) and (CurCol <= Col2) then
  begin
    Col1 := CurCol; Col2 := CurCol;                     // jeden riadok: vezmi tento stĺpec
    Result := True;
  end;
end;

Čokoľvek helper odmietne, teda dvojrozmernú oblasť, multi-sheet referenciu alebo formulu, ktorej riadok leží mimo pomenovaného stĺpca, vyprodukuje na strane vyhodnotenia #VALUE! a na strane grafu žiadnu závislosť, čo je to, čo Excel robí pre prázdnu intersection. Strana vyhodnotenia žije v TXLSCalculator.GetValueItemName: zo skompilovanej definície strhne SA_GROUP obaly a ak je koreň SA_RANGE, zavolá GetRangeInfo, intersectne a jednu bunku vytiahne cez FGetValue namiesto vyhodnotenia celej definície. Externé referencie zostávajú na starej ceste, pretože neexistuje lokálny riadok, proti ktorému by sa dalo intersectnúť. Odkiaľ sa berie úložisko a scope mena, pokrýva článok o defined names a cross-sheet formulách; tu ide len o to, čo engine robí, keď sa meno vyrieši

Prečo MATCH nad spolovice prepočítaným stĺpcom prečítal 777?

Pretože lookup-array argument MATCH je scan referencia a scan referencie boli z poradia vyhodnocovania zámerne vylúčené. Článok o lookup scane priniesol TXLSDepRange.LookupScan a končil sekciou „Čo tým strácate, keď scan hrany vylúčite z poradia“: lookup formula môže bežať skôr, než sa prepočítali všetky bunky v jej rozsahu, a prečítať zastarané hodnoty. V interaktívnej session to skonverguje v ďalšom priechode. V dávkovom prepočte otrávenej šablóny nie, a PaymentCount definovaný ako =MATCH(0.01,Balances,-1)+1 prečítal 777 placeholdery stále sediacich v stĺpci zostatku a vrátil počet periód, ktorý nemohol byť správny

TXLSDepGraph.TopoOrder teraz berie scan hrany ako soft hrany poradia. Popri tvrdom in-degree drží pole ScanInDeg, ktoré počíta dirty scan precedenty na node a znižuje ho, ako sa tie precedenty emitujú, pričom využíva zoznamy ScanPrecedents, ScanDependents a ScanPrecedentCount, ktoré predchádzajúca zmena už ukladala. V každej iterácii Kahn queue prehľadá svoje ready okno a prvý node s nulovým ScanInDeg prehodí na hlavu; ak každý ready node ešte čaká na scan precedens, hlava sa vyberie v stabilnom poradí. Scan hrany nikdy nevstupujú do tvrdého in-degree, takže samoreferenčný VLOOKUP nad vlastným stĺpcom je stále legálny, ale lookup, ktorý mohol počkať na dokončiteľný precedens, teraz počká. Regresia, ktorá to pripína, LookupScan_WaitsForDirtyFormulaValues, otrávi tri bunky zostatku na 777 a očakáva, že PaymentCount príde ako 3, potom prepne vstup na nulu a očakáva, že =IFERROR(PaymentCount,99) uvidí #N/A a vráti 99

Odkiaľ sa vzalo skrátenie na štyri desatinné miesta?

Z aritmetiky Delphi Variant, a to len v nested pozíciách. Binárne operátory v TXLSCalculator.GetValueItem už kopírovali top-level + alebo - do dvoch Double lokálnych premenných, takže =B1-A1 bolo v poriadku. Vnútri =IF(TRUE,B1-A1,0) bežalo to isté odčítanie ako Value := Value - SubValue nad dvoma Variantmi, a keď bol jeden operand Int64 hodnota bunky a druhý Double, výsledok, ktorý sme pozorovali, bol Currency, fixed-point typ so štyrmi desatinnými miestami, takže 1066.1854641400994 mínus 120 sa vrátilo skrátené na štyri desatinné miesta. V kalendári, kde sa každá platba skladá z predchádzajúceho riadku, tá chyba prejde stovky periód, kým dorazí k súčtom

// TXLSCalculator.GetValueItem, vetva binárnej aritmetiky (lxCalc.pas)
if VarIsNull(Value) then Value := 0;
if VarIsNull(SubValue) then SubValue := 0;
// Zmiešaná aritmetika Int64/Double Variantov môže povýšiť na Currency.
// Tabuľková aritmetika musí zachovať presnosť floating pointu.
if VarIsNumeric(Value) then Value := Double(Value);
if VarIsNumeric(SubValue) then SubValue := Double(SubValue);

Guard beží pred SA_ADD, SA_SUB, SA_MUL aj SA_DIV rovnako, a regresia Arithmetic_MixedInt64AndDoubleKeepsPrecision uloží Int64(120) do A1 a 1066.1854641400994 do B1, potom kontroluje nested rozdiel a súčet na 1E-10 a súčin a podiel na 1E-8 a 1E-12. HotXLS netvrdí, že pozná každé promotion pravidlo, ktoré RTL aplikuje na zmiešané Variant typy naprieč verziami kompilátora; tvrdí, že tabuľková aritmetika je IEEE double, a teraz spraví oba operandy double skôr, než ich operátor uvidí, čím tá otázka mizne

Čo oprava zaručuje a čo nie

Po v2.382.4 vrátia obe architektúry engine pre otrávenú šablónu lxOk, všetkých 4805 cached hodnôt sedí s nezávislým očakávaním riadok po riadku v rámci 1E-7 a platia aj všetky assertion-y, že cache boli naozaj otrávené, že hash zdroja je nezmenený a že každá formula je stále prítomná. Nič sa nezaplo iteráciami a žiadny error kód sa nepotlačil, aby sa to dosiahlo. Skutočný cyklus cez meno, =B1 v A1 s B1 stále čítajúcim Vertical, stále vracia chybu, a test NamedScalarRanges_IntersectWithoutFalseCycles končí presne takouto assertion

Hranice stoja za to pomenovať priamo. Implicitná intersection platí len pre meno, ktorého skompilovaná definícia je po strhnutí zátvoriek jednosĺpcová alebo jednoriadková oblasť na jednom hárku; dvojrozmerné meno v skalárnej pozícii je #VALUE!, ako v Exceli, a funkcia, ktorú tabuľka nepozná, dostane z FunctionArgumentClass triedu 0, takže jej meno-argumenty sa stále rozbaľujú v plnej šírke. Soft poradie je preferencia, nie záruka: scan-only cyklus sa stále vyhodnotí v stabilnom poradí a prečíta, čo je v cache, čo je správanie, ktoré článok o lookup scane prijal zámerne. A výsledok celej šablóny je overený proti nezávislému očakávanému skriptu, nie proti inému tabuľkovému engine, pretože referenčný office balík nedokončil prepočet pôvodnej šablóny v rámci 60-sekundového rozpočtu. HotXLS je natívny Delphi a C++Builder tabuľkový komponent, ktorý číta, prepočítava a zapisuje XLS, XLSX, ODS a CSV bez nainštalovaného Excelu; intersection mien, tabuľka tried argumentov a soft scan poradie platia pre každý formát, pretože výpočtový engine je zdieľaný, a aktuálne pokrytie funkcií je uvedené na produktovej stránke HotXLS Delphi spreadsheet component