Impliciete doorsnijding van namen in HotXLS voor Delphi

Een gedefinieerde naam die naar een hele kolom verwijst wordt door Excel als één cel gelezen wanneer hij in een scalaire positie staat: =Vertical+1 in rij 7 betekent "de cel uit rij 7 van Vertical", niet het hele gebied. HotXLS Delphi Component past die impliciete doorsnijding op twee niveaus toe, tijdens de evaluatie en tijdens het trekken van afhankelijkheden, want de juiste waarde krijgen is niet genoeg. Een afhankelijkheidswalker die de naam uitklapt naar zijn volledige gebied laat elke formule stroomafwaarts die een cel van dat gebied voedt een cyclus sluiten die niet bestaat, en TXLSXWorkbook.Recalculate zou dan de hele werkmap als circulair moeten weigeren

Een aandelenleen-aflossingswerkmap met 4805 formules is het belastinggeval dat dit alles aan het werk zet. Correct herberekenen vanuit verouderde caches hangt aan drie aparte mechanieken: impliciete doorsnijding voor namen, de volgorde van opzoekscans, en het numerieke type dat voor geneste rekenkunde wordt gebruikt. Er één verkeerd doen en het symptoom is ofwel lxErrorRef, de circulaire-referentiecode, of totalen die wegschuiven van een onafhankelijke rij-voor-rij-berekening. Dit artikel loopt elke mechaniek langs met de broncode die erbij hoort

Waarom veroorzaakt een scalaire verwijzing naar een kolomnaam een valse cyclus?

Omdat een afhankelijkheidsgraaf alleen randen kent, en een rand van een formule naar een gebied van 480 rijen zijn 480 randen, waarvan er één terugwijst via een cel die van de formule afhangt. Neem =IF(TRUE,Vertical+1,0) in B1 met Vertical gedefinieerd als Inputs!$A$1:$A$2, en =B1+1 in A2. Excel evalueert B1 als A1+1 en A2 als B1+1, een simpele keten. Een walker die B1 vastlegt als afhankelijk van A1:A2 maakt A2 tot precedent van B1, A2 vermeldt B1 al als precedent, en de Kahn-wachtrij die de incrementele herberekening in HotXLS aandrijft, ziet geen van beide knopen ooit in-degree nul bereiken. Dit is het patroon waarvan leningtemplates gemaakt zijn: elke perioderij verwijst naar benoemde kolommen voor de balans, de rente en het aantal betalingen, elke naam omvat het hele schema, en elke rij schrijft ook in die kolommen. Klap de namen uit en de graaf is één grote sterk samenhangende component. Evalueer ze met impliciete doorsnijding en de graaf is een reeks korte ketens, één per rij, precies wat ECMA-376 Part 1 §18.17.2 beschrijft voor een verwijzingsoperand dat gebruikt wordt waar één waarde vereist is

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;
    // Scalaire positie: Vertical klapt in tot A1 omdat de formule in rij 1 staat
    Sheet.Cells[1, 2].Formula := '=IF(TRUE,Vertical+1,0)';
    Sheet.Cells[2, 1].Formula := '=B1+1';
    // Een naam wiens definitie een andere naam is doorsnijdt alsnog, dus dit is A2
    Sheet.Cells[2, 2].Formula := '=Alias';
    // Verwijzingsklasse-argument: het hele gebied wordt opgeteld, geen doorsnijding
    Sheet.Cells[3, 2].Formula := '=SUM(Vertical)';
    // Rij 6 ligt buiten A1:A2, de doorsnijding is leeg en IFERROR vangt hem op
    Sheet.Cells[6, 2].Formula := '=IFERROR(Vertical,42)';

    if Book.Recalculate = lxOk then
    begin
      // B1 = 2, A2 = 3, B2 = 3, B3 = 4, B6 = 42
      // Vertical hier uitklappen naar A1:A2 zou van B1 -> A2 -> B1 een valse cyclus maken
    end;
  finally
    Book.Free;
  end;
end;

Hoe bepaalt HotXLS dat een argument scalair is?

HotXLS leest het antwoord uit de functietabel in plaats van uit de vorm van het argument. Elke entry in TXLSFormula.InitFuncHash wordt geregistreerd via THashFunc.SetValue met een optionele per-argument-klassetekenreeks: 'IF' draagt '100', 'SUMIF' draagt '010', 'VLOOKUP' draagt '1011', en 'SUM' draagt niets, dus alle argumenten vallen terug op de functieniveau-klasse 0. TXLSFormula.FunctionArgumentClass(APtg, AArgument) stelt die byte bloot via THashFuncEntry.ArgClass, en een resultaat van 1 betekent waardeklasse. Dit zijn dezelfde drie klassen die [MS-XLS] §2.2.2 aan operandtokens toekent, en de encoder leunt er ook op: wanneer hij een verwijzing schrijft berekent hij de ptg als $24 + $20 * aClass, wat PtgRef oplevert voor klasse 0, PtgRefV voor klasse 1 en PtgRefA voor klasse 2. Een BIFF-bestand dat Excel schrijft bewaart die klasse in elke verwijzingstoken, dus een engine waarvan de tabel met de spec overeenkomt kan "is dit argument scalair" beantwoorden zonder naar de data te kijken. Het middelste argument van SUMIF is het criterium, een waarde; het eerste en derde zijn gebieden, verwijzingen. SUMPRODUCT is geregistreerd met functieniveau-klasse 2, matrix, en dat is de reden dat =SUMPRODUCT(Vertical,Vertical) nog steeds het hele gebied vermenigvuldigt

Drie functies raadplegen hun eigen tabelitem niet voor iets na het eerste argument. IF (ptg 1), CHOOSE (ptg 100) en IFERROR (ptg 255) geven door wat ze selecteren, dus hun takargumenten erven de klasse van de positie die de functie zelf inneemt. Die ene regel is wat =CHOOSE(1,Vertical,0) in G2 naar A2 laat verwijzen terwijl =SUMIF(Vertical,">0",Vertical) ernaast nog steeds beide rijen optelt, en het is de regel die een aflossingsschema het meest gebruikt, want de periodecellen leunen op IF om te testen of de lening nog openstaat

De klasse door de afhankelijkheidswalk dragen

De afhankelijkheidsextractor in lxCalc.pas is een recursieve Walk over de gecompileerde syntaxboom, en hij bestaat twee keer, een keer in TXLSCalculator.ExtractDependencies voor de per-werkmap-graaf en een keer in ExtractWorkspaceDependencies voor de cross-werkmap-graaf. Beide walkers dragen twee extra parameters. AScalar begint als True aan de wortel van een formule, wordt voor elk functiekind herberekend vanuit FunctionArgumentClass, en gaat ongewijzigd door voor de takargumenten van ptg 1, 100 en 255. ANameRoot wordt pas True wanneer de walker afdaalt in de gecompileerde definitie van een naam, en hij overleeft alleen via SA_GROUP-knopen, de haakjes, dus een naam gedefinieerd als =A1:A2+1 wordt niet aangezien voor een kaal gebied. Wanneer beide vlaggen True staan op een SA_RANGE-knoop, beperkt AddResolvedRange het gebied met dezelfde helper die de evaluator gebruikt voordat hij de afhankelijkheid vastlegt. De helper is kort genoeg om volledig te citeren

function IntersectNamedScalarRange(CurRow, CurCol: Integer;
  var Row1, Row2, Col1, Col2: Integer): Boolean;
begin
  Result := False;
  if (Row1 = Row2) and (Col1 = Col2) then Exit(True);   // is al een cel
  if (Col1 = Col2) and (CurRow >= Row1) and (CurRow <= Row2) then
  begin
    Row1 := CurRow; Row2 := CurRow;                     // enkele kolom: neem deze rij
    Exit(True);
  end;
  if (Row1 = Row2) and (CurCol >= Col1) and (CurCol <= Col2) then
  begin
    Col1 := CurCol; Col2 := CurCol;                     // enkele rij: neem deze kolom
    Result := True;
  end;
end;

Alles wat de helper weigert — een tweedimensionaal gebied, een multi-sheet-verwijzing of een formule waarvan de rij buiten de benoemde kolom ligt — levert #VALUE! op aan de evaluatiekant en helemaal geen afhankelijkheid aan de graafkant, wat Excel ook doet voor een lege doorsnijding. De evaluatiekant woont in TXLSCalculator.GetValueItemName: hij stript SA_GROUP-omschuivingen van de gecompileerde definitie, en als de wortel een SA_RANGE is roept hij GetRangeInfo aan, doorsnijdt, en haalt de ene cel via FGetValue in plaats van de hele definitie te evalueren. Externe verwijzingen nemen het gewone pad, want er is geen lokale rij om tegen te doorsnijden. Waar de opslag en scope van een naam vandaan komen staat in het artikel over gedefinieerde namen en cross-sheet-formules; hier telt alleen wat de engine doet zodra de naam oplost

Waarom moet MATCH wachten op een half uitgerekende kolom?

Omdat het lookup-array-argument van MATCH een scanverwijzing is, en scanverwijzingen worden bewust buitengesloten uit de harde evaluatievolgorde. Het artikel over opzoekscans introduceert TXLSDepRange.LookupScan en legt uit wat je opgeeft door scanranden buiten de volgorde te houden: een opzoekformule kan draaien voordat elke cel in zijn bereik is herberekend en verouderde waarden lezen. In een interactieve sessie die op de volgende ronde convergeert maakt dat niet uit. In één batchherberekening van een sjabloon wiens caches verouderd zijn wel: PaymentCount, gedefinieerd als =MATCH(0.01,Balances,-1)+1, zou de verouderde waarden lezen die nog in de balanskolom zitten en een periodetelling teruggeven die niet juist kan zijn

TXLSDepGraph.TopoOrder behandelt scanranden daarom als zachte ordeningsranden. Naast de harde in-degree houdt hij een ScanInDeg-array bij, die vuile scanprecedenten per knoop telt en vermindert naarmate die precedenten worden uitgezonden, met de lijsten ScanPrecedents, ScanDependents en ScanPrecedentCount die de graaf al bewaart. Bij elke iteratie scant de Kahn-wachtrij zijn klaar-venster op de eerste knoop wiens ScanInDeg nul is en wisselt hem naar de kop; wacht elke klaare knoop nog op een scanprecedent, dan wordt de kop in zijn stabiele volgorde weggehaald. Scanranden komen nooit in de harde in-degree terecht, dus een zelfverwijzende VLOOKUP over zijn eigen kolom blijft legaal, maar een opzoeking die kan wachten op een afrondbaar precedent doet dat. Met drie balanscellen die verouderde waarden dragen komt PaymentCount terug als 3; zet de invoer op nul en =IFERROR(PaymentCount,99) ziet de #N/A en geeft 99 terug

Waarom geneste rekenkunde naar Double wordt gedwongen

Omdat Delphi Variant-rekenkunde een val kent die alleen in geneste posities opduikt. Een topniveau-+ of - kopiëren naar twee Double-locals houdt =B1-A1 exact. Binnen =IF(TRUE,B1-A1,0) draait de voor de hand liggende implementatie dezelfde aftrekking als Value := Value - SubValue op twee Variants, en wanneer de ene operand een Int64-celwaarde is en de andere een Double, was het resultaat dat we zagen een Currency, een vastekomma-type met vier decimalen, dus 1066.1854641400994 min 120 komt terug afgekapt op vier decimalen. Over een schema waarin elke betaling uit de vorige rij wordt samengesteld loopt die fout door honderden perioden voordat hij de totalen bereikt, en daarom zet TXLSCalculator.GetValueItem beide operands om voordat de operator draait

// TXLSCalculator.GetValueItem, tak voor binaire rekenkunde (lxCalc.pas)
if VarIsNull(Value) then Value := 0;
if VarIsNull(SubValue) then SubValue := 0;
// Gemengde Int64/Double Variant-rekenkunde kan promoveren naar Currency.
// Spreadsheet-rekenkunde moet floating-point-precisie behouden.
if VarIsNumeric(Value) then Value := Double(Value);
if VarIsNumeric(SubValue) then SubValue := Double(SubValue);

De bewaking draait voor SA_ADD, SA_SUB, SA_MUL en SA_DIV al, dus met Int64(120) in A1 en 1066.1854641400994 in B1 houden het geneste verschil, de som, het product en het quotiënt allemaal dubbele precisie. HotXLS beweert niet elke promotieregel te kennen die de RTL toepast op gemengde Variant-types over compiler-versies heen; hij behandelt spreadsheetrekenkunde als IEEE double en zet beide operands naar double voordat de operator hen ziet, wat de vraag wegneemt

Wat de engine garandeert voor benoemde kolommen

Met de drie mechanieken op plaats herberekenen beide engine-architecturen het aflossingssjabloon vanuit verouderde caches naar lxOk, en alle 4805 waarden matchen een onafhankelijke rij-voor-rij-verwachting binnen 1E-7. Er is geen iteratie aangezet en geen foutcode onderdrukt om daar te komen. Een echte cyclus via een naam, =B1 in A1 terwijl B1 nog Vertical leest, geeft alsnog een fout terug, precies zoals het hoort

De regels volgen Excel. Impliciete doorsnijding geldt voor een naam wiens gecompileerde definitie, na het strippen van haakjes, een gebied van één kolom of één rij op één blad is; een tweedimensionale naam in een scalaire positie is #VALUE!, net als in Excel, en een functie die de tabel niet kent krijgt klasse 0 van FunctionArgumentClass, dus zijn naamargumenten worden volledig uitgeklapt. De zachte ordening is een voorkeur, geen garantie: een scan-only-cyclus evalueert in stabiele volgorde en leest wat er gecacht is, en dat is de ruil die het artikel over opzoekscans bewust accepteert. HotXLS is een native Delphi- en C++Builder-spreadsheetcomponent die XLS, XLSX, ODS en CSV leest, herberekent en schrijft zonder Excel op de machine; de naamdoorsnijding, de argumentklasse-tabel en de zachte scanvolgorde gelden voor elk formaat omdat de rekenengine gedeeld is, en de huidige functiedekking staat op de productpagina van de HotXLS Delphi spreadsheet component