HotXLS lookup-scanninger og falske cirkulære referencer

Læg =VLOOKUP(A1,B:B,1) i en celle i kolonne B, og Excel beregner den uden brok. Giv samme projektmappe til en afhængighedsgraf-genberegningsmotor, og du får formentlig en cirkulær reference-fejl, for formlen afhænger af et område, der indeholder formlen. HotXLS rapporterede præcis det indtil v2.361.98. Rettelsen er ikke et specialtilfælde for helkolonne-områder; den er en skelnen mellem to slags afhængighedskant, som et regnearksmotor behøver, og som en almindelig rettet graf ikke har

Lookup-array-argumentet i lookup-familien — LOOKUP, MATCH, HLOOKUP, VLOOKUP, XLOOKUP og XMATCH — markeres nu som en scan-reference. En scan-reference såer stadig snavs, så redigering af en celle inde i området genberegner formlen, men den bidrager aldrig til cyklusdetektion eller til evalueringsorden. Ægte cykler findes stadig; de falske er væk

Hvorfor tillader Excel, at et lookup-område indeholder formlen?

Fordi det argument ikke indtages, som en aritmetisk operand gør. Lookup-familien scanner området for cachede værdier og returnerer et match; den kræver ikke, at området først er blevet evalueret til ende. Excel behandler et selvoverlappende lookup-område som at læse, hvad end de celler reelt holder, hvilket er samme semantik, den anvender på enhver ikke-iterativ projektmappe: celler, der ikke er blevet genberegnet i dette gennemløb, bidrager med deres sidst beregnede værdi

Helkolonne-referencer gør dette til det almindelige tilfælde frem for et eksotisk. B:B er den idiomske måde at skrive "hele opslagstabellen" på i et ark, hvor rækker tilføjes, og enhver formel, der bor i kolonne B, er så inde i sit eget lookup-område. Finansielle modeller, afstemningsark og audit-projektmapper gør det konstant, som regel uden, at nogen lægger mærke til, at området overlapper

Hvad en afhængighedsgraf gør ved samme formel

HotXLS genberegner inkrementelt, hvilket kræver en ægte afhængighedsgraf: noder til celler, kanter til referencer, en topologisk orden til evaluering og et strongly connected component-gennemløb til at klassificere cykler. Det maskineri er beskrevet i artiklen om inkrementel genberegning, og det er præcis derfor, det falske positive optrådte

Udtræk afhængigheder fra =VLOOKUP(A1,B:B,1) i cellen B7, og det andet argument giver et område, der indeholder B7 selv. Grafen har nu en selvsløjfe. Nodens in-degree når aldrig nul, så det topologiske gennemløb kan aldrig planlægge den, og komponentgennemløbet klassificerer den som en cyklus. Motoren ræsonnerer korrekt om grafen, den fik. Grafen er den forkerte model, for den koder én kanttype, hvor regnearket har to

To kantklasser, én graf

Ændringen tilføjer et flag til den opløste reference-record, TXLSDepRange.LookupScan, som afhængighedsudtrækkeren sætter, når den gennemgår lookup-array-argumentet for en af de seks funktioner. Nedefter gemmes kanter med kilde i de referencer adskilt fra almindelige kanter: grafnoden holder ScanDependents- og ScanPrecedents-lister ved siden af sine almindelige dependent- og precedent-lister

Adskillelsen er det, der gør semantikken rigtig. Scan-kanter gennemgås af dirty-propagering, så en redigering hvor som helst i B:B stadig markerer B7 som snavset, og B7 genberegner. Scan-kanter tælles aldrig med i in-degree og kommer aldrig ind i komponentbyggeren, så de kan ikke skabe en topologisk deadlock og ikke klassificeres som en cyklus. Begge grafgimplementationer i biblioteket, den klassiske pr. projektmappe gældende graf og den kryds-projektmappe workspace-graf, der bærer komponentanalysen, blev ændret sammen; at lade dem drive ville give en projektmappe, der genberegner forskelligt afhængigt af, om den åbnedes alene eller som del af et workspace

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Ledger');
    Sheet.Cells[1, 1].Value := 'ACC-4471';
    Sheet.Cells[1, 2].Value := 1200.00;
    // Lookup-området dækker kolonne B, og denne formel bor i den
    Sheet.Cells[7, 2].Formula := 'VLOOKUP(A1,B:B,1)';

    case Book.Recalculate of
      lxOk:
        // Før v2.361.98 var denne gren uopnåelig for dette ark
        SaveReport(Book);
      lxErrorRef:
        LogWarning('Genuine circular reference - review model inputs');
    end;
  finally
    Book.Free;
  end;
end;

Hvad du opgiver ved at udelukke scan-kanter fra ordenen

Præcis én ting, og den er værd at sige klart frem for at skjule. Fordi scan-kanter ikke deltager i den topologiske orden, kan en lookup-formel evalueres i samme gennemløb, før nogle celler i dens lookup-område er blevet genberegnet, og den læser så deres forrige værdier. Resultatet konvergerer ved næste genberegning

Det er acceptabelt, for det er, hvad Excel gør. For en projektmappe uden iterativ beregning slået til er Excels eget svar på en værdi, der endnu ikke er genberegnet i det aktuelle gennemløb, den sidst beregnede værdi, så en motor, der gengiver denne adfærd, matcher referenceimplementationen frem for at approksimere den. Hvis du behøver et genuint konvergeret svar over en selvrefererende model, er mekanismen til det iterativ beregning med en eksplicit iterationsgrænse, dækket i artiklen om iterativ beregning, og den gælder ægte cykler snarere end scan-overlap

Regressionsfaren, der skjuler sig inde i rettelsen

At tilføje LookupScan til TXLSDepRange indførte en risiko, der intet har med lookups at gøre og alt med Pascal. TXLSDepRange er en uadministreret record, så en lokal variabel af den type er ikke nul-initialiseret. Ethvert sted i kodebasen, der bygger en i hånden, inklusive data-tabel-afhængighedsblokkene og flere testhjælpere, måtte derfor opdateres til at sætte det nye felt eksplicit. Drop ét, og den byte, der tilfældigvis lå på stakken, beslutter, om den reference behandles som en scan-kant, hvilket giver en genberegningsfejl, der optræder og forsvinder med urelaterede kodeændringer

// Et nyt Boolean-felt i en uadministreret record gør hvert manuelt
// konstruktionssted til en latent fejl. To sikre idiomer:
var
  R: TXLSDepRange;
begin
  FillChar(R, SizeOf(R), 0);      // nulstil alt, og udfyld derefter
  R.Sheet1 := SheetIndex;
  R.Sheet2 := SheetIndex;
  R.Row1 := Row; R.Col1 := Col;
  R.Row2 := Row; R.Col2 := Col;

  // eller sæt hvert felt, inklusive det nye, på hvert sted
  R.LookupScan := False;
end;

Den generelle regel, dette indbragte: at tilføje et felt til en record, der konstrueres på stakken på mere end en håndfuld steder, er en højrisiko-ændring, end den ser ud, og compileren hjælper dig ikke med at finde stederne. Hvis recorden er tilgængelig fra en varm vej, foretræk en hjælper, der initialiserer den fuldstændigt, frem for at stole på, at hvert call site bliver opdateret

At skelne en ægte cyklus fra et scan-overlap

Intet ved denne ændring svækker cyklusdetektion. =B7+1 i B7 er stadig en cyklus, en kæde på tre formler, der lukker om sig selv, er stadig en cyklus, og begge rapporteres stadig gennem genberegningsresultatet med cyklusmedlemmerne, der beholder deres tidligere cachede værdier, mens alt uden for cyklussen forbliver aktuelt. Det, der ændredes, er blot, at lookup-array-argumentet ikke længere fabrikerer cykler, som Excel ikke ser

Hvis du reviderer en projektmappe og vil vide, hvilke referencer motoren reelt opløste og i hvilken rækkefølge, er evalueringstraceren værktøjet til det; artiklen om formel-evalueringstraceren dækker, hvordan man læser dens output. HotXLS er en native Delphi- og C++Builder-regnearkskomponent, der læser og skriver XLS, XLSX, ODS og CSV uden Excel installeret, og genberegningsmotoren er den samme på alle formater; den aktuelle funktions- og motordækning er opført på produktsiden HotXLS Delphi spreadsheet component