HotXLS lookup-scans en valse kringverwijzingen

Zet =VLOOKUP(A1,B:B,1) in een cel in kolom B en Excel rekent die zonder mopperen uit. Geeft u hetzelfde werkboek aan een herberekeningsengine met afhankelijkheidsgraaf, dan krijgt u waarschijnlijk een fout over een kringverwijzing, want de formule hangt af van een bereik dat de formule bevat. HotXLS meldde precies dat tot v2.361.98. De oplossing is geen bijzonder geval voor hele-kolom-bereiken; het is een onderscheid tussen twee soorten afhankelijkheidsranden die een spreadsheet-engine nodig heeft en een kale gerichte graaf niet heeft

Het lookup-array-argument van de lookupfamilie, LOOKUP, MATCH, HLOOKUP, VLOOKUP, XLOOKUP en XMATCH, wordt nu gemarkeerd als scanverwijzing. Een scanverwijzing zet nog steeds de vervuilingsmarkering uit, dus het bewerken van een cel binnen het bereik herberekent de formule, maar die draagt nooit bij aan cyclusdetectie of evaluatievolgorde. Echte cycli worden nog steeds gevonden; de valse zijn weg

Waarom staat Excel toe dat een lookupbereik de formule bevat?

Omdat dat argument niet wordt verbruikt zoals een rekenkundige operand. De lookupfamilie scant het bereik op gecachte waarden en geeft een match terug; die vereist niet dat het bereik eerst volledig is uitgerekend. Excel behandelt een zichzelf overlappend lookupbereik als het lezen van wat die cellen momenteel bevatten, wat dezelfde semantiek is die die toepast op elk niet-iteratief werkboek: cellen die in deze passe niet zijn herberekend leveren hun laatst berekende waarde

Hele-kolom-verwijzingen maken dit het gewone geval in plaats van een exotische. B:B is de idiomatische manier om "de hele opzoektabel" te schrijven in een blad waar rijen worden toegevoegd, en elke formule die in kolom B woont zit dan binnen zijn eigen lookupbereik. Financiële modellen, afstemmingsbladen en auditwerkboeken doen dit constant, gewoonlijk zonder dat iemand merkt dat het bereik overlapt

Wat een afhankelijkheidsgraaf met dezelfde formule doet

HotXLS herberekent incrementeel, wat een echte afhankelijkheidsgraaf vereist: knopen voor cellen, randen voor verwijzingen, een topologische ordening voor evaluatie en een passe met sterk samenhangende componenten om cycli te classificeren. Die mechaniek wordt beschreven in het artikel over incrementele herberekening, en dat is precies waarom de foute positieve verscheen

Haalt u de afhankelijkheden uit =VLOOKUP(A1,B:B,1) in cel B7, dan levert het tweede argument een bereik op dat B7 zelf bevat. De graaf heeft nu een zelf-lus. De in-graad van die knoop bereikt nooit nul, dus de topologische passe kan die nooit inplannen, en de componentpasse classificeert die als cyclus. De engine redeneert correct over de graaf die haar werd gegeven. De graaf is het verkeerde model, want die codeert één randtype waar de spreadsheet er twee heeft

Twee randklassen, één graaf

De wijziging voegt een vlag toe aan het record van de geresolveerde verwijzing, TXLSDepRange.LookupScan, die de afhankelijkheidsextractor zet wanneer die het lookup-array-argument van een van de zes functies doorloopt. Stroomafwaarts worden randen die uit die verwijzingen komen apart van gewone randen opgeslagen: de graafknoop houdt lijsten ScanDependents en ScanPrecedents bij naast zijn normale dependent- en precedentlijsten

De scheiding is wat de semantiek juist maakt. Scanranden worden doorlopen door vuilpropagatie, dus een bewerking ergens in B:B markeert B7 nog steeds als vervuild en B7 herberekent. Scanranden worden nooit meegeteld in de in-graad en komen nooit in de componentbouwer, dus ze kunnen geen topologische impasse creëren en niet als cyclus worden geclassificeerd. Beide graafimplementaties in de bibliotheek, de klassieke graaf per werkboek en de werkboekoverschrijdende werkruimtegraaf die de componentanalyse draagt, zijn samen gewijzigd; ze laten uiteenlopen zou een werkboek opleveren dat anders herberekent naargelang het alleen werd geopend of als deel van een werkruimte

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;
    // Het lookupbereik beslaat kolom B, en deze formule woont erin
    Sheet.Cells[7, 2].Formula := 'VLOOKUP(A1,B:B,1)';

    case Book.Recalculate of
      lxOk:
        // Vóór v2.361.98 was deze tak onbereikbaar voor dit blad
        SaveReport(Book);
      lxErrorRef:
        LogWarning('Genuine circular reference - review model inputs');
    end;
  finally
    Book.Free;
  end;
end;

Wat u opgeeft door scanranden buiten de ordening te houden

Precies één ding, en het is het helder benoemen waard in plaats van het te verbergen. Omdat scanranden niet deelnemen aan de topologische ordening, kan een lookupformule in dezelfde passe worden geëvalueerd voordat sommige cellen in zijn lookupbereik zijn herberekend, en die leest dan hun vorige waarden. Het resultaat convergeert bij de volgende herberekening

Dat is acceptabel omdat het is wat Excel doet. Voor een werkboek zonder iteratieve berekening is het eigen antwoord van Excel op een waarde die in de huidige passe nog niet is herberekend de laatst berekende waarde, dus een engine die dit gedrag reproduceert volgt de referentie-implementatie in plaats van die te benaderen. Heeft u een werkelijk geconvergeerd antwoord nodig over een naar zichzelf verwijzend model, dan is de mechaniek daarvoor iteratieve berekening met een expliciete iteratielimiet, behandeld in het artikel over iteratieve berekening, en die geldt voor echte cycli in plaats van voor scanoverlappen

Het regressiegevaar dat in de oplossing schuilgaat

Het toevoegen van LookupScan aan TXLSDepRange introduceerde een risico dat niets met lookups te maken heeft en alles met Pascal. TXLSDepRange is een niet-beheerd record, dus een lokale variabele van dat type wordt niet met nul geïnitialiseerd. Elke plek in de codebase die er met de hand één bouwt, inclusief de databestand-afhankelijkheidsblokken en enkele testhulpjes, moest daarom worden bijgewerkt om het nieuwe veld expliciet te zetten. Eén missen en welke byte er toevallig op de stapel stond beslist of die verwijzing als scanrand wordt behandeld, wat een herberekeningsbug oplevert die verschijnt en verdwijnt met ongerelateerde codewijzigingen

// Een nieuw Boolean-veld in een niet-beheerd record maakt elke
// handmatige bouwplaats een latente bug. Twee veilige idiomen:
var
  R: TXLSDepRange;
begin
  FillChar(R, SizeOf(R), 0);      // alles op nul, dan invullen
  R.Sheet1 := SheetIndex;
  R.Sheet2 := SheetIndex;
  R.Row1 := Row; R.Col1 := Col;
  R.Row2 := Row; R.Col2 := Col;

  // of zet elk veld, inclusief het nieuwe, op elke plaats
  R.LookupScan := False;
end;

De algemene regel die dit opleverde: een veld toevoegen aan een record dat op meer dan een handjevol plaatsen op de stapel wordt geconstrueerd is een wijziging met meer risico dan die lijkt, en de compiler helpt u niet de plaatsen te vinden. Is het record bereikbaar vanaf een heet pad, geef dan de voorkeur aan een helper die het volledig initialiseert boven het vertrouwen dat elke aanroepplaats wordt bijgewerkt

Een echte cyclus onderscheiden van een scanoverlap

Niets aan deze wijziging verzwakt de cyclusdetectie. =B7+1 in B7 is nog steeds een cyclus, een keten van drie formules die op zichzelf sluit is nog steeds een cyclus, en beide worden nog steeds gemeld via het herberekeningsresultaat, waarbij de cyclusleden hun vorige gecachte waarden houden terwijl alles buiten de cyclus actueel blijft. Wat veranderde is alleen dat het lookup-array-argument geen cycli meer fabriceert die Excel niet ziet

Auditeert u een werkboek en wilt u weten welke verwijzingen de engine werkelijk heeft geresolveerd en in welke volgorde, dan is de evaluatietracer het gereedschap daarvoor; het artikel over de formule-evaluatietracer behandelt hoe u zijn uitvoer leest. HotXLS is een native Delphi- en C++Builder-spreadsheetcomponent die XLS, XLSX, ODS en CSV leest en schrijft zonder geïnstalleerde Excel, en de herberekeningsengine is hetzelfde op elk formaat; de huidige dekking van functies en engine staat op de productpagina van de HotXLS Delphi spreadsheet component