HotXLS implicit intersection for defined names i Delphi

Et defined name, der refererer til en hel kolonne, læses af Excel som en enkelt celle, når det optræder i en skalær position: =Vertical+1 i række 7 betyder "cellen i række 7 af Vertical", ikke hele området. HotXLS Delphi Component anvender den implicit intersection i v2.382.4 på to niveauer, under evaluering og under dependency-udtræk, fordi en låneskabelon med 4805 formler viste, at det ikke er nok at få værdien rigtig. Når dependency-walkeren ekspanderer navnet til sit fulde område, lukker en downstream-formel, som føder en hvilken som helst celle i det område, en cyklus, der ikke findes, og TXLSXWorkbook.Recalculate afviser hele arbejdsbogen

Skabelonen i spørgsmål er en arbejdsbog til afdragsordning for lån. Med alle cachede værdier forgiftet til 777 og en fuld Recalculate-kørsel returnerede begge engine-arkitekturer 23, hvilket er lxErrorRef, koden for cirkulær reference. 3842 af de 4805 formler matchede ikke den uafhængige forventning, B18 holdt #VALUE!, E18 var stadig 777, og betalingsantallet i J7 havde læst pladsholderne i en ufærdig balancekolonne. Tre separate defekter gemte sig bag én returkode, og denne artikel gennemgår hver af dem med den kilde, der rettede den

Hvorfor skaber en skalær reference til et kolonnenavn en falsk cyklus?

Fordi en dependency-graf kun kender kanter, og en kant fra en formel til et område på 480 rækker er 480 kanter, hvoraf én peger tilbage gennem en celle, der afhænger af formlen. Betragt =IF(TRUE,Vertical+1,0) i B1 med Vertical defineret som Inputs!$A$1:$A$2 og =B1+1 i A2. Excel evaluerer B1 som A1+1 og A2 som B1+1, en lige kæde. En walker, der registrerer B1 som afhængig af A1:A2, gør A2 til en precedent for B1, A2 lister allerede B1 som precedent, og Kahn-køen, der driver inkrementel rekalkulation i HotXLS, ser aldrig nogen af noderne nå in-degree nul. Det er mønstret, låneskabeloner er bygget af: Hver perioderække refererer navngivne kolonner for balance, rente og betalingsantal, hvert navn spænder over hele planen, og hver række skriver også ind i de kolonner. Ekspander navnene, og grafen er én kæmpe stærkt sammenhængende komponent. Evaluér dem med implicit intersection, og grafen er et sæt korte kæder, én pr. række, hvilket er det, ECMA-376 Part 1 §18.17.2 beskriver for en reference-operand, der forbruges, hvor en enkelt værdi kræves

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ær position: Vertical kollapser til A1, fordi formlen er i række 1
    Sheet.Cells[1, 2].Formula := '=IF(TRUE,Vertical+1,0)';
    Sheet.Cells[2, 1].Formula := '=B1+1';
    // Et navn, hvis definition er et andet navn, intersecter stadig, så dette er A2
    Sheet.Cells[2, 2].Formula := '=Alias';
    // Reference-klasse-argument: Hele området summes, ingen intersection
    Sheet.Cells[3, 2].Formula := '=SUM(Vertical)';
    // Række 6 ligger uden for A1:A2, intersectionen er tom, og IFERROR fanger den
    Sheet.Cells[6, 2].Formula := '=IFERROR(Vertical,42)';

    if Book.Recalculate = lxOk then
    begin
      // B1 = 2, A2 = 3, B2 = 3, B3 = 4, B6 = 42
      // Før v2.382.4 var denne gren utilgængelig: B1 -> A2 -> B1 var en cyklus
    end;
  finally
    Book.Free;
  end;
end;

Hvordan afgør HotXLS, at et argument er skalært?

HotXLS læser svaret fra funktionstabellen frem for fra argumentets form. Hver post i TXLSFormula.InitFuncHash registreres gennem THashFunc.SetValue med en valgfri per-argument-klassestreng: 'IF' bærer '100', 'SUMIF' bærer '010', 'VLOOKUP' bærer '1011', og 'SUM' bærer ingen, så alle dens argumenter falder tilbage til funktionsniveau-klasse 0. Den nye TXLSFormula.FunctionArgumentClass(APtg, AArgument) eksponerer den byte gennem THashFuncEntry.ArgClass, og et resultat af 1 betyder value-klasse. Det er de samme tre klasser, som [MS-XLS] §2.2.2 tildeler operand-tokens, og encoderen var allerede afhængig af dem: Når den skriver en reference, beregner den ptg'en som $24 + $20 * aClass, hvilket giver PtgRef for klasse 0, PtgRefV for klasse 1 og PtgRefA for klasse 2. En BIFF-fil skrevet af Excel gemmer den klasse i hver reference-token, så en engine, hvis tabel matcher specifikationen, kan svare på "er dette argument skalært" uden at se på dataene. Mellemargumentet i SUMIF er kriteriet, en værdi; det første og tredje er områder, referencer. SUMPRODUCT er registreret med funktionsniveau-klasse 2, array, og det er derfor, =SUMPRODUCT(Vertical,Vertical) stadig multiplicerer hele området

Tre funktioner konsulterer ikke deres egen tabelpost for noget efter første argument. IF (ptg 1), CHOOSE (ptg 100) og IFERROR (ptg 255) lader det, de vælger, passere igennem, så deres grenargumenter arver klassen af den position, funktionen selv optager. Den ene regel er det, der lader =CHOOSE(1,Vertical,0) i G2 opløses til A2, mens =SUMIF(Vertical,">0",Vertical) ved siden af stadig summer begge rækker, og det er den regel, en afdragsplan trænger mest, fordi dens periodeceller læner sig op ad IF for at teste, om lånet stadig er åbent

At føre klassen med gennem dependency-gennemløbet

Dependency-ekstraktoren i lxCalc.pas er et rekursivt Walk over det kompilerede syntakstræ, og den findes to gange, én gang i TXLSCalculator.ExtractDependencies for per-workbook-grafen og én gang i ExtractWorkspaceDependencies for cross-workbook-grafen. v2.382.4 giver begge walkere to ekstra parametre. AScalar starter som True ved formulens rod, beregnes om for hvert funktionsbarn ud fra FunctionArgumentClass og sendes uændret videre for grenargumenterne til ptg 1, 100 og 255. ANameRoot bliver kun True, når walkeren går ned i et navns kompilerede definition, og den overlever kun gennem SA_GROUP-noder, parenteserne, så et navn defineret som =A1:A2+1 ikke tages for et almindeligt område. Når begge flag er True ved en SA_RANGE-node, indsnævrer AddResolvedRange området med den samme hjælper, som evaluatoren bruger, før den registrerer dependencen. Hjælperen er kort nok til at citere i fuldt omfang

function IntersectNamedScalarRange(CurRow, CurCol: Integer;
  var Row1, Row2, Col1, Col2: Integer): Boolean;
begin
  Result := False;
  if (Row1 = Row2) and (Col1 = Col2) then Exit(True);   // allerede en celle
  if (Col1 = Col2) and (CurRow >= Row1) and (CurRow <= Row2) then
  begin
    Row1 := CurRow; Row2 := CurRow;                     // enkelt kolonne: tag denne række
    Exit(True);
  end;
  if (Row1 = Row2) and (CurCol >= Col1) and (CurCol <= Col2) then
  begin
    Col1 := CurCol; Col2 := CurCol;                     // enkelt række: tag denne kolonne
    Result := True;
  end;
end;

Alt, hvad hjælperen afviser, et todimensionelt område, en multi-sheet-reference eller en formel, hvis række ligger uden for den navngivne kolonne, producerer #VALUE! på evalueringssiden og slet ingen dependency på grafsiden, hvilket er det, Excel gør ved en tom intersection. Evaluationssiden bor i TXLSCalculator.GetValueItemName: Den fjerner SA_GROUP-wrappere fra den kompilerede definition, og hvis roden er en SA_RANGE, kalder den GetRangeInfo, intersecter og henter den ene celle gennem FGetValue i stedet for at evaluere hele definitionen. Eksterne referencer forbliver på den gamle vej, fordi der ikke er nogen lokal række at intersecte imod. Hvor et navns lager og scope overhovedet kommer fra, er dækket i artiklen om defined names og cross-sheet formler; pointen her er kun, hvad enginen gør, når navnet først er opløst

Hvorfor læste MATCH over en halvberegnet kolonne 777?

Fordi lookup-array-argumentet i MATCH er en scan-reference, og scan-referencer bevidst var holdt uden for evalueringsrækkefølgen. Lookup scan-artiklen introducerede TXLSDepRange.LookupScan og sluttede med et afsnit kaldet "Hvad du opgiver ved at holde scan-kanter ude af ordenen": En lookup-formel kan komme til at køre, før hver celle i sit range er genberegnet, og læse forældede værdier. I en interaktiv session konvergerer det ved næste pass. I en batch-rekalkulation af en forgiftet skabelon gør det ikke, og PaymentCount, defineret som =MATCH(0.01,Balances,-1)+1, læste de 777-pladsholdere, der stadig lå i balancekolonnen, og returnerede et antal perioder, der ikke kunne være rigtigt

TXLSDepGraph.TopoOrder behandler nu scan-kanter som bløde ordningskanter. Ved siden af den hårde in-degree fører den et ScanInDeg-array, der tæller urene scan-precedents pr. node og tæller den ned, efterhånden som precedenterne emitteres, med brug af ScanPrecedents-, ScanDependents- og ScanPrecedentCount-listerne, som den tidligere ændring allerede gemte. Ved hver iteration scanner Kahn-køen sit klar-vindue for den første node, hvis ScanInDeg er nul, og bytter den op i hovedet; hvis alle klare noder stadig venter på en scan-precedent, poppes hovedet i sin stabile rækkefølge. Scan-kanter kommer aldrig ind i den hårde in-degree, så en selvrefererende VLOOKUP over sin egen kolonne er stadig lovlig, men en lookup, der kunne vente på en precedent, der kan blive færdig, gør det nu. Regressionen, der fastlåser det, LookupScan_WaitsForDirtyFormulaValues, forgifter tre balanceceller til 777 og forventer, at PaymentCount kommer tilbage som 3, og vender derefter inputtet til nul og forventer, at =IFERROR(PaymentCount,99) ser #N/A og returnerer 99

Hvor kom afkortningen til fire decimaler fra?

Fra Delphi Variant-aritmetik, og kun i indlejrede positioner. De binære operatorer i TXLSCalculator.GetValueItem kopierede allerede en top-level + eller - ud i to Double-lokaler, så =B1-A1 var fint. Inde i =IF(TRUE,B1-A1,0) kørte den samme subtraktion som Value := Value - SubValue på to Variants, og når den ene operand var en Int64-celleværdi og den anden en Double, var resultatet, vi observerede, en Currency, en fastkommatype med fire decimaler, så 1066.1854641400994 minus 120 kom tilbage afkortet til fire decimaler. Hen over en plan, hvor hver betaling sammensættes af forrige række, vandrer den fejl gennem hundreder af perioder, før den når totalerne

// TXLSCalculator.GetValueItem, binær aritmetik-gren (lxCalc.pas)
if VarIsNull(Value) then Value := 0;
if VarIsNull(SubValue) then SubValue := 0;
// Blandet Int64/Double Variant-aritmetik kan promovere til Currency.
// Regnearksaritmetik skal bevare floating point-præcision.
if VarIsNumeric(Value) then Value := Double(Value);
if VarIsNumeric(SubValue) then SubValue := Double(SubValue);

Vogteren kører for både SA_ADD, SA_SUB, SA_MUL og SA_DIV, og regressionen Arithmetic_MixedInt64AndDoubleKeepsPrecision gemmer Int64(120) i A1 og 1066.1854641400994 i B1 og tjekker derefter den indlejrede differens og sum til 1E-10 og produktet og kvotienten til 1E-8 og 1E-12. HotXLS påstår ikke at kende hver eneste promotionsregel, RTL'en anvender på blandede Variant-typer på tværs af compiler-versioner; den påstår, at regnearksaritmetik er IEEE double, og den gør nu begge operander til double, før operatoren ser dem, hvilket fjerner spørgsmålet

Hvad fixet garanterer, og hvad det ikke gør

Efter v2.382.4 returnerer begge engine-arkitekturer lxOk for den forgiftede skabelon, alle 4805 cachede værdier matcher den uafhængige række-for-række-forventning inden for 1E-7, og assertionerne om, at cacherne virkelig var forgiftet, at kildehashen er uændret og at hver formel stadig er til stede, holder alle. Ingen iteration blev slået til, og ingen fejlkode blev undertrykt for at komme dertil. En ægte cyklus gennem et navn, =B1 i A1, hvor B1 stadig læser Vertical, returnerer stadig en fejl, og testen NamedScalarRanges_IntersectWithoutFalseCycles slutter med at asserte præcis det

Grænserne er det værd at sige klart. Implicit intersection gælder kun for et navn, hvis kompilerede definition, efter parenteserne er fjernet, er et enkelt-kolonne- eller enkelt-række-område på ét ark; et todimensionelt navn i en skalær position er #VALUE! som i Excel, og en funktion, tabellen ikke kender, får klasse 0 fra FunctionArgumentClass, så dens navneargumenter stadig ekspanderes i fuldt omfang. Den bløde orden er en præference, ikke en garanti: En kun-scan-cyklus evalueres stadig i stabil rækkefølge og læser det, der er cached, hvilket er den adfærd, lookup scan-artiklen bevidst accepterede. Og helt-skabelon-resultatet verificeres mod et uafhængigt forventningsscript, ikke mod en anden regnearks-engine, fordi reference-office-suitten ikke nåede at genberegne den originale skabelon inden for et 60-sekundersbudget. HotXLS er en native Delphi- og C++Builder-regnearkskomponent, der læser, genberegner og skriver XLS, XLSX, ODS og CSV uden Excel installeret; navne-intersectionen, argumentklasse-tabellen og den bløde scan-orden gælder alle formater, fordi beregningsenginen er fælles, og den nuværende funktionsdækning er listet på productsiden HotXLS Delphi spreadsheet-komponent