HotXLS oppslagsskann og falske sirkulære referanser

Sett =VLOOKUP(A1,B:B,1) i en celle i kolonne B, og Excel beregner den uten klage. Mater den samme arbeidsboken til en avhengighetsgraf-omberegningsmotor, får du sannsynligvis en sirkulær referansefeil, fordi formelen avhenger av et område som inneholder formelen. HotXLS rapporterte nøyaktig det til v2.361.98. Fiksen er ikke et spesialtilfelle for hele-kolonne-områder; den er et skille mellom to slags avhengighetskant som en regnearkmotor trenger og en enkel rettet graf ikke har

Lookup-array-argumentet til oppslagsfamilien, LOOKUP, MATCH, HLOOKUP, VLOOKUP, XLOOKUP og XMATCH, er nå merket som en skannreferanse. En skannreferanse såer fortsatt skittenshet, så å redigere en celle inne i området beregner formelen på nytt, men den bidrar aldri til syklusedeteksjon eller til evalueringsordening. Ekte sykluser finnes fortsatt; de falske er borte

Hvorfor tillater Excel at et oppslagsområde inneholder formelen?

Fordi det argumentet ikke konsumeres slik en aritmetisk operand er. Oppslagsfamilien skanner området for bufrede verdier og returnerer et treff; den krever ikke at området er evaluert til fullførelse først. Excel behandler et selv-overlappende oppslagsområde som å lese hva de cellene for øyeblikket holder, noe som er den samme semantikken den anvender på enhver ikke-iterativ arbeidsbok: celler som ikke er beregnet på nytt i denne gjennomkjøringen, bidrar med sin sist beregnede verdi

Hele-kolonne-referanser gjør dette til det vanlige tilfellet snarere enn et eksotisk. B:B er den idiome måten å skrive «hele oppslagstabellen» på i et ark der rader appenderes, og enhver formel som bor i kolonne B, er da inne i sitt eget oppslagsområde. Finansielle modeller, avstemmingsark og revisjonsarbeidsbøker gjør dette stadig, vanligvis uten at noen legger merke til at området overlapper

Hva en avhengighetsgraf gjør med den samme formelen

HotXLS beregner inkrementelt på nytt, noe som krever en ekte avhengighetsgraf: noder for celler, kanter for referanser, en topologisk ordening for evaluering og en sterkt-sammenkoblet-komponent-passering for å klassifisere sykluser. Det maskineriet er beskrevet i artikkelen om inkrementell omberegning, og det er presist hvorfor den falske positive oppsto

Trekk ut avhengigheter fra =VLOOKUP(A1,B:B,1) i cellen B7, og det andre argumentet gir et område som inneholder B7 selv. Grafen har nå en selvsløyfe. In-graden av den noden når aldri null, så den topologiske passeringen kan aldri planlegge den, og komponentpasseringen klassifiserer den som en syklus. Motoren resonerer korrekt om grafen den fikk. Grafen er feil modell, fordi den koder én kanttype der regnearket har to

To kantklasser, én graf

Endringen legger et flagg til den løste referanserecorden, TXLSDepRange.LookupScan, som avhengighetsekstraktoren setter når den går gjennom lookup-array-argumentet til én av de seks funksjonene. Nedstrøms lagres kanter med kilde i de referansene adskilt fra ordinære kanter: grafnoden beholder ScanDependents- og ScanPrecedents-lister ved siden av sine normale dependent- og precedent-lister

Skillelinjen er det som gjør semantikken riktig. Skannkanter traverseres av skitten-propagering, så en redigering hvor som helst i B:B markerer fortsatt B7 som skitten, og B7 beregnes på nytt. Skannkanter telles aldri inn i in-graden og går aldri inn i komponentbyggeren, så de kan ikke skape en topologisk vranglås og kan ikke klassifiseres som en syklus. Begge grafimplementeringene i biblioteket, den klassiske per-arbeidsbok-grafen og tvers-arbeidsbok-arbeidsområdegrafen som bærer komponentanalysen, ble endret sammen; å la dem drive ville produsere en arbeidsbok som beregnes annerledes avhengig av om den ble åpnet alene eller som del av et arbeidsområde

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;
    // Oppslagsområdet dekker kolonne B, og denne formelen 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 grenen uoppnåelig for dette arket
        SaveReport(Book);
      lxErrorRef:
        LogWarning('Genuine circular reference - review model inputs');
    end;
  finally
    Book.Free;
  end;
end;

Hva du gir avkall på ved å ekskludere skannkanter fra ordeningen

Nøyaktig én ting, og det er verdt å si klart snarere enn å gjemme. Fordi skannkanter ikke deltar i den topologiske ordeningen, kan en oppslagsformel evalueres i samme passering før noen celler i sitt oppslagsområde er beregnet på nytt, og den leser da deres tidligere verdier. Resultatet konvergerer ved neste omberegning

Det er akseptabelt fordi det er hva Excel gjør. For en arbeidsbok uten iterativ beregning aktivert, er Excels eget svar på en verdi ennå ikke beregnet i den nåværende passeringen den sist beregnede verdien, så en motor som reproduserer denne atferden, matcher referanseimplementeringen snarere enn å tilnærme den. Hvis du trenger et genuint konvergert svar over en selv-referensiell modell, er mekanismen for det iterativ beregning med en eksplisitt iterasjonsgrense, dekket i artikkelen om iterativ beregning, og den gjelder ekte sykluser snarere enn skannoverlapp

Regresjonsfaren som gjemmer seg inne i fiksen

Å legge LookupScan til TXLSDepRange innførte en risiko som ikke har noe med oppslag å gjøre og alt med Pascal. TXLSDepRange er en uadministrert record, så en lokal variabel av den typen er ikke null-initialisert. Hvert sted i kodebasen som bygger én for hånd, inkludert datatabell-avhengighetsblokkene og flere testhjelpere, måtte derfor oppdateres til å sette det nye feltet eksplisitt. Miss én, og den byten som tilfeldigvis var på stakken avgjør om den referansen behandles som en skannkant, noe som produserer en omberegningsfeil som opptrer og forsvinner med urelatte kodeendringer

// Et nytt Boolean-felt i en uadministrert record gjør hvert manuelle
// konstruksjonssted til en latent feil. To trygge idiomer:
var
  R: TXLSDepRange;
begin
  FillChar(R, SizeOf(R), 0);      // nullstill alt, og fyll deretter inn
  R.Sheet1 := SheetIndex;
  R.Sheet2 := SheetIndex;
  R.Row1 := Row; R.Col1 := Col;
  R.Row2 := Row; R.Col2 := Col;

  // eller sett hvert felt, inkludert det nye, på hvert sted
  R.LookupScan := False;
end;

Den generelle regelen dette tjente: å legge et felt til en record som konstrueres på stakken på mer enn en håndfull steder, er en høyrisikoendring sammenlignet med hvordan den ser ut, og kompilatoren vil ikke hjelpe deg å finne stedene. Hvis recorden er nåbar fra en varm vei, foretrekk en hjelper som initialiserer den fullstendig fremfor å stole på at hvert kallsted oppdateres

Å skille en ekte syklus fra en skannoverlapp

Ingenting ved denne endringen svekker syklusedeteksjon. =B7+1 i B7 er fortsatt en syklus, en kjede av tre formler som lukker seg på seg selv er fortsatt en syklus, og begge rapporteres fortsatt gjennom omberegningsresultatet med syklusmedlemmene som beholder sine tidligere bufrede verdier mens alt utenfor syklusen forblir gjeldende. Det som endret seg, er bare at lookup-array-argumentet ikke lenger fabriserer sykluser Excel ikke ser

Hvis du reviderer en arbeidsbok og vil vite hvilke referanser motoren faktisk løste og i hvilken rekkefølge, er evalueringssporeren verktøyet for det; artikkelen om formelevalueringssporeren dekker hvordan du leser outputen. HotXLS er en nativ Delphi- og C++Builder-regnearkkomponent som leser og skriver XLS, XLSX, ODS og CSV uten Excel installert, og omberegningsmotoren er den samme på hvert format; nåværende funksjons- og motordekning er oppført på produktsiden for HotXLS Delphi spreadsheet component