HotXLS: implicit intersection for definerte navn i Delphi

Et definert navn som peker på en hel kolonne, leses av Excel som én enkelt celle når det står i en skalar posisjon: =Vertical+1 i rad 7 betyr «cellen i rad 7 av Vertical», ikke hele området. HotXLS Delphi Component bruker den implicitte intersectionen fra og med v2.382.4 på to nivåer, både under evaluering og under avhengighetsuttrekket, fordi en låmemal med 4805 formler viste at det ikke er nok å få verdien riktig. Når avhengighetsvandreren utvider navnet til hele området sitt, lukker en nedstrøms formel som mater en vilkårlig celle i det området, en syklus som ikke finnes, og TXLSXWorkbook.Recalculate avviser hele arbeidsboken

Malen det gjelder, er en helt vanlig arbeidsbok for låneamortisering. Med hver bufret verdi forgiftet til 777 og en full Recalculate-kjøring returnerte begge motorarkitekturene 23, som er lxErrorRef, koden for sirkelreferanse. 3842 av de 4805 formlene stemte ikke med den uavhengige forventningen, B18 inneholdt #VALUE!, E18 var fortsatt 777, og betalingsantallet i J7 hadde lest plassholderne i en uferdig saldokolonne. Tre separate feil gjemte seg bak én returkode, og denne artikkelen går gjennom hver av dem med kildekoden som rettet den

Hvorfor skaper en skalar referanse til et kolonnenavn en falsk syklus?

Fordi en avhengighetsgraf bare kjenner kanter, og en kant fra en formel til et område på 480 rader er 480 kanter, hvorav én peker tilbake gjennom en celle som avhenger av formelen. Tenk deg =IF(TRUE,Vertical+1,0) i B1 med Vertical definert som Inputs!$A$1:$A$2, og =B1+1 i A2. Excel evaluerer B1 som A1+1 og A2 som B1+1, en rett kjede. En vandrer som registrerer B1 som avhengig av A1:A2, gjør A2 til en forgjenger til B1, og A2 fører allerede opp B1 som forgjenger, så Kahn-køen som driver inkrementell omregning i HotXLS, ser aldri at noen av nodene når inngrad null. Dette er mønsteret låmemaler er bygget av: hver perioderad refererer til navngitte kolonner for saldo, rente og betalingsantall, hvert navn spenner over hele planen, og hver rad skriver også inn i de samme kolonnene. Utvid navnene, og grafen er én gigantisk sterkt sammenhengende komponent. Evaluer dem med implicit intersection, og grafen er et sett korte kjeder, én per rad, som er det ECMA-376 Part 1 §18.17.2 beskriver for en referanseoperand som brukes der en enkelt verdi kreves

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;
    // Skalar posisjon: Vertical kollapser til A1 fordi formelen står i rad 1
    Sheet.Cells[1, 2].Formula := '=IF(TRUE,Vertical+1,0)';
    Sheet.Cells[2, 1].Formula := '=B1+1';
    // Et navn hvis definisjon er et annet navn, intersekterer fortsatt, så dette er A2
    Sheet.Cells[2, 2].Formula := '=Alias';
    // Argument i referanseklasse: hele området summeres, ingen intersection
    Sheet.Cells[3, 2].Formula := '=SUM(Vertical)';
    // Rad 6 ligger utenfor A1:A2, intersectionen er tom og IFERROR fanger det
    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 grenen uoppnåelig: B1 -> A2 -> B1 var en syklus
    end;
  finally
    Book.Free;
  end;
end;

Hvordan avgjør HotXLS at et argument er skalart?

HotXLS leser svaret fra funksjonstabellen og ikke fra formen på argumentet. Hver oppføring i TXLSFormula.InitFuncHash registreres gjennom THashFunc.SetValue med en valgfri klassestreng per argument: 'IF' bærer '100', 'SUMIF' bærer '010', 'VLOOKUP' bærer '1011', og 'SUM' bærer ingen, så alle argumentene faller tilbake til klassen 0 på funksjonsnivå. Den nye TXLSFormula.FunctionArgumentClass(APtg, AArgument) eksponerer den byten gjennom THashFuncEntry.ArgClass, og et resultat på 1 betyr verdiklasse. Dette er de samme tre klassene som [MS-XLS] §2.2.2 tildeler operandtokenene, og innkoderen var allerede avhengig av dem: når den skriver en referanse, regner den ut ptg-en som $24 + $20 * aClass, noe som gir PtgRef for klasse 0, PtgRefV for klasse 1 og PtgRefA for klasse 2. En BIFF-fil skrevet av Excel lagrer den klassen i hvert referansetoken, så en motor der tabellen stemmer med spesifikasjonen, kan svare på «er dette argumentet skalart» uten å se på dataene. Det midterste argumentet i SUMIF er kriteriet, en verdi; det første og det tredje er områder, referanser. SUMPRODUCT er registrert med klasse 2 på funksjonsnivå, array, og derfor multipliserer =SUMPRODUCT(Vertical,Vertical) fortsatt hele området

Tre funksjoner ser ikke på sin egen tabelloppføring for noe ut over det første argumentet. IF (ptg 1), CHOOSE (ptg 100) og IFERROR (ptg 255) slipper gjennom det de velger, så grenargumentene deres arver klassen til posisjonen funksjonen selv står i. Den ene regelen er det som lar =CHOOSE(1,Vertical,0) i G2 løse seg til A2 mens =SUMIF(Vertical,">0",Vertical) ved siden av fortsatt summerer begge radene, og det er regelen en amortiseringsplan bruker mest, fordi periodecellene lener seg på IF for å teste om lånet fortsatt er åpent

Å bære klassen gjennom avhengighetsvandringen

Avhengighetsuttrekket i lxCalc.pas er en rekursiv Walk over det kompilerte syntakstreet, og det finnes to ganger, én i TXLSCalculator.ExtractDependencies for grafen per arbeidsbok og én i ExtractWorkspaceDependencies for grafen på tvers av arbeidsbøker. v2.382.4 gir begge vandrerne to ekstra parametere. AScalar starter som True i roten av en formel, regnes på nytt for hvert funksjonsbarn fra FunctionArgumentClass, og sendes uendret videre for grenargumentene til ptg 1, 100 og 255. ANameRoot blir True bare når vandreren går ned i den kompilerte definisjonen av et navn, og den overlever bare gjennom SA_GROUP-noder, parentesene, så et navn definert som =A1:A2+1 blir ikke forvekslet med et rent område. Når begge flaggene er True i en SA_RANGE-node, innsnevrer AddResolvedRange området med den samme hjelperen som evaluatoren bruker, før den registrerer avhengigheten. Hjelperen er kort nok til å siteres i sin helhet

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 én celle
  if (Col1 = Col2) and (CurRow >= Row1) and (CurRow <= Row2) then
  begin
    Row1 := CurRow; Row2 := CurRow;                     // én kolonne: ta denne raden
    Exit(True);
  end;
  if (Row1 = Row2) and (CurCol >= Col1) and (CurCol <= Col2) then
  begin
    Col1 := CurCol; Col2 := CurCol;                     // én rad: ta denne kolonnen
    Result := True;
  end;
end;

Alt hjelperen avviser — et todimensjonalt område, en referanse over flere ark eller en formel hvis rad ligger utenfor den navngitte kolonnen — gir #VALUE! på evalueringssiden og ingen avhengighet i det hele tatt på grafsiden, som er det Excel gjør for en tom intersection. Evalueringssiden bor i TXLSCalculator.GetValueItemName: den stripper SA_GROUP-innpakninger fra den kompilerte definisjonen, og hvis roten er en SA_RANGE, kaller den GetRangeInfo, intersekterer og henter den ene cellen gjennom FGetValue i stedet for å evaluere hele definisjonen. Eksterne referanser blir på den gamle stien, fordi det ikke finnes noen lokal rad å intersektere mot. Hvor lagringen og omfanget til et navn kommer fra i utgangspunktet, er dekket i artikkelen om definerte navn og formler på tvers av ark; poenget her er bare hva motoren gjør når navnet først er løst opp

Hvorfor leste MATCH over en halvferdig kolonne 777?

Fordi oppslagsarray-argumentet til MATCH er en scan-referanse, og scan-referanser ble bevisst holdt utenfor evalueringsrekkefølgen. Artikkelen om lookup-scan innførte TXLSDepRange.LookupScan og avsluttet med et avsnitt kalt «Hva du gir avkall på ved å holde scan-kanter utenfor ordningen»: en oppslagsformel kan kjøre før hver celle i området sitt er regnet om på nytt, og lese utdaterte verdier. I en interaktiv økt konvergerer det i neste pass. I en satsvis omregning av en forgiftet mal gjør det ikke det, og PaymentCount, definert som =MATCH(0.01,Balances,-1)+1, leste 777-plassholderne som fortsatt lå i saldokolonnen, og returnerte et periodeantall som ikke kunne være riktig

TXLSDepGraph.TopoOrder behandler nå scan-kanter som myke ordningskanter. Ved siden av den harde inngraden holder den et ScanInDeg-array som teller skitne scan-forgjengere per node og reduserer den etter hvert som forgjengerne sendes ut, ved hjelp av listene ScanPrecedents, ScanDependents og ScanPrecedentCount som den tidligere endringen allerede lagret. Ved hver iterasjon skanner Kahn-køen sitt klare vindu etter den første noden der ScanInDeg er null og bytter den til hodet; hvis hver klar node fortsatt venter på en scan-forgjenger, poppes hodet i sin stabile rekkefølge. Scan-kanter kommer aldri inn i den harde inngraden, så en selv-refererende VLOOKUP over sin egen kolonne er fortsatt lovlig, men et oppslag som kunne vente på en fullførbar forgjenger, gjør det nå. Regresjonstesten som fester dette, LookupScan_WaitsForDirtyFormulaValues, forgifter tre saldoceller til 777 og forventer at PaymentCount kommer tilbake som 3, snur så inndataene til null og forventer at =IFERROR(PaymentCount,99) ser #N/A og returnerer 99

Hvor kom avkortningen til fire desimaler fra?

Fra Variant-aritmetikk i Delphi, og bare i nestede posisjoner. De binære operatørene i TXLSCalculator.GetValueItem kopierte allerede et + eller - på toppnivå inn i to Double-lokale variabler, så =B1-A1 gikk fint. Inne i =IF(TRUE,B1-A1,0) kjørte den samme subtraksjonen som Value := Value - SubValue på to Variants, og da den ene operanden var en Int64-celleverdi og den andre en Double, var resultatet vi observerte en Currency, en fastpunktstype med fire desimaler, så 1066.1854641400994 minus 120 kom tilbake avkortet til fire desimaler. Gjennom en plan der hver betaling er bygget videre fra raden over, vandrer den feilen gjennom hundrevis av perioder før den når totalene

// TXLSCalculator.GetValueItem, grenen for binær aritmetikk (lxCalc.pas)
if VarIsNull(Value) then Value := 0;
if VarIsNull(SubValue) then SubValue := 0;
// Blandet Int64/Double Variant-aritmetikk kan promotere til Currency.
// Regnearkaritmetikk må beholde flyttallspresisjon.
if VarIsNumeric(Value) then Value := Double(Value);
if VarIsNumeric(SubValue) then SubValue := Double(SubValue);

Vakten kjører før SA_ADD, SA_SUB, SA_MUL og SA_DIV alle sammen, og regresjonstesten Arithmetic_MixedInt64AndDoubleKeepsPrecision lagrer Int64(120) i A1 og 1066.1854641400994 i B1, og sjekker så den nestede differansen og summen mot 1E-10 og produktet og kvotienten mot 1E-8 og 1E-12. HotXLS påstår ikke å kjenne hver promoteringsregel RTL-en bruker på blandede Variant-typer på tvers av kompilatorversjoner; det påstår at regnearkaritmetikk er IEEE double, og det gjør nå begge operandene til double før operatøren ser dem, noe som fjerner spørsmålet

Hva rettelsen garanterer, og hva den ikke garanterer

Etter v2.382.4 returnerer begge motorarkitekturene lxOk for den forgiftede malen, alle 4805 bufrede verdiene stemmer med den uavhengige forventningen rad for rad innenfor 1E-7, og påstandene om at bufferen virkelig var forgiftet, at kildehashen er uendret og at hver formel fortsatt finnes, holder alle sammen. Ingen iterasjon ble slått på og ingen feilkode ble undertrykt for å komme dit. En ekte syklus gjennom et navn, =B1 i A1 med B1 som fortsatt leser Vertical, returnerer fortsatt en feil, og testen NamedScalarRanges_IntersectWithoutFalseCycles avslutter med å hevde nettopp det

Grensene er verdt å si rett ut. Implicit intersection gjelder bare et navn der den kompilerte definisjonen, etter at parentesene er strippet, er et område med én kolonne eller én rad på ett ark; et todimensjonalt navn i en skalar posisjon gir #VALUE!, som i Excel, og en funksjon tabellen ikke kjenner, får klasse 0 fra FunctionArgumentClass, så navneargumentene dens utvides fortsatt i sin helhet. Den myke ordningen er en preferanse, ikke en garanti: en ren scan-syklus evalueres fortsatt i stabil rekkefølge og leser det som er bufret, og det er oppførselen lookup-scan-artikkelen godtok med vilje. Og resultatet for hele malen er verifisert mot et uavhengig forventningsskript, ikke mot en annen regnearkmotor, fordi den refererte kontorpakken ikke ble ferdig med å regne om den opprinnelige malen innenfor et budsjett på 60 sekunder. HotXLS er en innebygd regnearkkomponent for Delphi og C++Builder som leser, regner om og skriver XLS, XLSX, ODS og CSV uten at Excel er installert; navneintersectionen, argumentklassetabellen og den myke scan-ordningen gjelder for alle formatene fordi beregningsmotoren er felles, og den gjeldende funksjonsdekningen står på produktsiden for HotXLS Delphi-regnearkkomponenten