HotXLS-uppslagsskanningar och falska cirkelreferenser

Lägg =VLOOKUP(A1,B:B,1) i en cell i kolumn B och Excel beräknar den utan klagan. Mata samma arbetsbok till en beroendegrafs-omberäkningsmotor och du får sannolikt ett cirkelreferensfel, eftersom formeln beror på ett område som innehåller formeln. HotXLS rapporterade exakt det till v2.361.98. Fixen är inte ett specialfall för helkolumnsområden; den är en distinktion mellan två slags beroendekanter som en kalkylbladsmotor behöver och en vanlig riktad graf inte har

Uppslagsarray-argumentet av uppslagsfamiljen, LOOKUP, MATCH, HLOOKUP, VLOOKUP, XLOOKUP och XMATCH, är nu märkt som en skankreferens. En skankreferens sår fortfarande orenhet, så att redigera en cell inuti området räknar om formeln, men den bidrar aldrig till cykeldetektering eller till utvärderingsordning. Äkta cykler hittas fortfarande; de falska är borta

Varför tillåter Excel att ett uppslagsområde innehåller formeln?

För det argumentet konsumeras inte som en aritmetisk operand gör. Uppslagsfamiljen skannar området efter cachade värden och returnerar en träff; den kräver inte att området har utvärderats till slut först. Excel behandlar ett självöverlappande uppslagsområde som att läsa vadhelst de cellerna för tillfället håller, vilket är samma semantik den tillämpar på valfri icke-iterativ arbetsbok: celler som inte räknats om i detta pass bidrar med sitt senast beräknade värde

Helkolumnreferenser gör detta till det vanliga fallet i stället för ett exotiskt. B:B är det idiomatiska sättet att skriva ”hela uppslagstabellen” i ett ark där rader läggs till, och varje formel som bor i kolumn B är då inuti sitt eget uppslagsområde. Finansiella modeller, avstämningsark och granskningsarbetsböcker gör detta ständigt, vanligtvis utan att någon lägger märke till att området överlappar

Vad en beroendegraf gör med samma formel

HotXLS räknar om inkrementellt, vilket kräver en riktig beroendegraf: noder för celler, kanter för referenser, en topologisk ordning för utvärdering och ett pass för starkt sammanhängande komponenter för att klassificera cykler. Det maskineriet beskrivs i artikeln om inkrementell omberäkning, och det är precis varför den falska positiva dök upp

Extrahera beroenden ur =VLOOKUP(A1,B:B,1) i cell B7 och det andra argumentet ger ett område som innehåller B7 själv. Grafen har nu en självloop. Ingraden för den noden når aldrig noll, så det topologiska passet kan aldrig schemalägga den, och komponentpasset klassificerar den som en cykel. Motorn resonerar korrekt om grafen den fick. Grafen är fel modell, eftersom den kodar en kanttyp där kalkylbladet har två

Två kantklasser, en graf

Ändringen lägger till en flagga till den upplösta referensposten, TXLSDepRange.LookupScan, som beroendeextraktorn sätter när den går igenom uppslagsarray-argumentet för en av de sex funktionerna. Nedströms lagras kanter med källa i de referenserna åtskilda från vanliga kanter: grafnoden håller ScanDependents- och ScanPrecedents-listor jämte sina normala dependent- och precedentlistor

Separationen är det som gör semantiken rätt. Skankanter traverseras av smutsspridning, så en redigering var som helst i B:B märker fortfarande B7 som smutsig och B7 räknas om. Skankanter räknas aldrig in i ingraden och går aldrig in i komponentbyggaren, så de kan inte skapa en topologisk dödläge och kan inte klassificeras som en cykel. Båda grafimplementeringarna i biblioteket, den klassiska per-arbetsboksgrafen och den kors-arbetsboks arbetsytegrafen som bär komponentanalysen, ändrades tillsammans; att låta dem drifta skulle ge en arbetsbok som räknar om olika beroende på huruvida den öppnades ensam eller som del av en arbetsyta

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;
    // Uppslagsområdet täcker kolumn B, och denna formel bor i den
    Sheet.Cells[7, 2].Formula := 'VLOOKUP(A1,B:B,1)';

    case Book.Recalculate of
      lxOk:
        // Före v2.361.98 var denna gren onåbar för detta ark
        SaveReport(Book);
      lxErrorRef:
        LogWarning('Genuine circular reference - review model inputs');
    end;
  finally
    Book.Free;
  end;
end;

Vad du ger upp genom att utesluta skankanter från ordningen

Exakt en sak, och den är värd att ange blankt i stället för att döljas. Eftersom skankanter inte deltar i den topologiska ordningen kan en uppslagsformel utvärderas i samma pass innan vissa celler i dess uppslagsområde har räknats om, och den kommer då läsa deras tidigare värden. Resultatet konvergerar vid nästa omberäkning

Det är acceptabelt eftersom det är vad Excel gör. För en arbetsbok utan iterativ beräkning aktiverad är Excels eget svar på ett värde som ännu inte räknats om i aktuellt pass det senast beräknade värdet, så en motor som återskapar detta beteende matchar referensimplementeringen i stället för att approximera den. Om du behöver ett genuint konvergerat svar över en självreferentiell modell är mekanismen för det iterativ beräkning med en uttrycklig iterationsgräns, som tas upp i artikeln om iterativ beräkning, och den gäller äkta cykler i stället för skannöverlappningar

Regressionsfaran som gömmer sig inuti fixen

Att lägga till LookupScan till TXLSDepRange introducerade en risk som inte har något med uppslag att göra och allt med Pascal att göra. TXLSDepRange är en ohanterad post, så en lokal variabel av den typen är inte nollinitierad. Varenda plats i kodbasen som bygger en för hand, inklusive datatabellens beroendeblock och flera testhjälpare, måste därför uppdateras att sätta det nya fältet uttryckligen. Missa en och det byte som råkade ligga på stacken avgör huruvida den referensen behandlas som en skankant, vilket ger en omberäkningsbugg som visar sig och försvinner med orelaterade kodändringar

// Ett nytt Boolean-fält i en ohanterad post gör varje manuell
// konstruktionsplats till en latent bugg. Två säkra idiom:
var
  R: TXLSDepRange;
begin
  FillChar(R, SizeOf(R), 0);      // nollställ allt, fyll sedan i
  R.Sheet1 := SheetIndex;
  R.Sheet2 := SheetIndex;
  R.Row1 := Row; R.Col1 := Col;
  R.Row2 := Row; R.Col2 := Col;

  // eller sätt vart och ett fält, inklusive det nya, på varje plats
  R.LookupScan := False;
end;

Den allmänna regeln detta förtjänade: att lägga till ett fält till en post som konstrueras på stacken på fler än ett handfull platser är en högre-risk ändring än den ser ut, och kompilatorn hjälper dig inte hitta platserna. Om posten är nåbar från en het väg, föredra en hjälpare som initierar den helt framför att lita på att varje anropsplats uppdateras

Att skilja en äkta cykel från en skannöverlappning

Ingenting vid denna ändring försvagar cykeldetekteringen. =B7+1 i B7 är fortfarande en cykel, en kedja av tre formler som sluter sig på sig själv är fortfarande en cykel, och båda rapporteras fortfarande genom omberäkningsresultatet med cykelmedlemmarna som behåller sina tidigare cachade värden medan allt utanför cykeln förblir aktuellt. Det som ändrades är bara att uppslagsarray-argumentet inte längre tillverkar cykler som Excel inte ser

Om du granskar en arbetsbok och vill veta vilka referenser motorn faktiskt löste upp och i vilken ordning är utvärderingsspåraren verktyget för det; artikeln om utvärderingsspåraren för formler täcker hur man läser dess utmatning. HotXLS är en nativ Delphi- och C++Builder-kalkylbladskomponent som läser och skriver XLS, XLSX, ODS och CSV utan Excel installerat, och omberäkningsmotorn är densamma på varje format; den aktuella funktions- och motortäckningen listas på produktsidan för HotXLS Delphi spreadsheet component