Lookup skeny v HotXLS a falešné cyklické reference

Dejte =VLOOKUP(A1,B:B,1) do buňky ve sloupci B a Excel ji spočítá bez reptání. Podáte tentýž pracovní sešit enginu přepočtu s grafem závislostí a pravděpodobně dostanete chybu cyklické reference, protože vzorec závisí na oblasti, která vzorec obsahuje. HotXLS hlásil přesně to až do v2.361.98. Oprava není zvláštní případ pro oblasti celých sloupců; je to rozlišení mezi dvěma druhy hrany závislosti, které tabulkový engine potřebuje a prostý orientovaný graf nemá

Argument lookup pole rodiny vyhledávacích funkcí, LOOKUP, MATCH, HLOOKUP, VLOOKUP, XLOOKUP a XMATCH, je nyní označován jako scan reference. Scan reference stále započne zašpinění, takže úprava buňky uvnitř oblasti přepočítá vzorec, ale nikdy nepřispívá k detekci cyklů ani k řazení vyhodnocování. Skutečné cykly se stále nacházejí; ty falešné jsou pryč

Proč Excel dovoluje, aby oblast vyhledávání obsahovala vzorec?

Protože ten argument není konzumován způsobem aritmetického operandu. Rodina vyhledávání skenuje oblast po cachovaných hodnotách a vrací shodu; nevyžaduje, aby oblast byla nejprve vyhodnocena do konce. Excel chápe sebe překrývající se vyhledávací oblast jako čtení toho, co ty buňky právě drží, což je stejná sémantika, jakou aplikuje na jakýkoli neiterativní sešit: buňky, které nebyly v tomto průchodu přepočteny, přispívají svou poslední vypočtenou hodnotou

Reference celých sloupců z toho dělají běžný případ, nikoli exotický. B:B je idiomatický způsob, jak zapsat „celou vyhledávací tabulku“ v listu, do něhož se přidávají řádky, a jakýkoli vzorec žijící ve sloupci B je pak uvnitř vlastní vyhledávací oblasti. Finanční modely, odsouhlasovací listy a auditní sešity to dělají neustále, obvykle bez toho, aby si kdokoli všiml překryvu

Co graf závislostí udělá s tímž vzorcem

HotXLS přepočítává inkrementálně, což vyžaduje skutečný graf závislostí: uzly pro buňky, hrany pro reference, topologické pořadí pro vyhodnocování a průchod silně souvislými komponentami pro klasifikaci cyklů. Ten mechanizmus popisuje článek o inkrementálním přepočtu a právě proto se falešný pozitiv objevil

Vyjměte-li závislosti z =VLOOKUP(A1,B:B,1) v buňce B7, druhý argument dá oblast obsahující B7 samotné. Graf má nyní self-loop. Vstupní stupeň toho uzlu nikdy nedosáhne nuly, takže topologický průchod jej nemůže nikdy naplánovat a průchod komponentami jej klasifikuje jako cyklus. Engine uvažuje správně o grafu, který dostal. Graf je špatný model, protože kóduje jeden typ hrany tam, kde tabulka má dva

Dvě třídy hran, jeden graf

Změna přidává příznak do záznamu rozřešené reference, TXLSDepRange.LookupScan, který extraktor závislostí nastaví, když prochází argument lookup pole jedné ze šesti funkcí. Downstream se hrany pocházející z těch referencí ukládají stranou od obyčejných hran: uzel grafu drží seznamy ScanDependents a ScanPrecedents vedle svých běžných seznamů závislých a předků

Rozdělení je tím, co dělá sémantiku správnou. Scan hrany se procházejí šířením zašpinění, takže úprava kdekoli v B:B stále označí B7 jako špinavé a B7 se přepočítá. Scan hrany se nikdy nepočítají do vstupního stupně a nikdy nevstoupí do stavitele komponent, takže nemohou vytvořit topologické uváznutí ani být klasifikovány jako cyklus. Obě implementace grafu v knihovně, klasický graf na sešit a cross-workbook graf workspace nesoucí analýzu komponent, se změnily společně; necháte-li je rozdílet, vznikne sešit, který se přepočítává odlišně podle toho, zda byl otevřen sám nebo jako část 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;
    // Vyhledávací oblast pokrývá sloupec B a tento vzorec v něm žije
    Sheet.Cells[7, 2].Formula := 'VLOOKUP(A1,B:B,1)';

    case Book.Recalculate of
      lxOk:
        // Před v2.361.98 byla tato větev pro tento list nedosažitelná
        SaveReport(Book);
      lxErrorRef:
        LogWarning('Genuine circular reference - review model inputs');
    end;
  finally
    Book.Free;
  end;
end;

Co vzdáváte vyloučením scan hran z řazení

Přesně jednu věc a stojí za řeč prostou, nikoli ukrytou. Protože scan hrany se neúčastní topologického pořadí, může být vyhledávací vzorec vyhodnocen v témže průchodu dříve, než byly některé buňky jeho vyhledávací oblasti přepočteny, a poté přečte jejich předchozí hodnoty. Výsledek konverguje při příštím přepočtu

To je přijatelné, protože přesně to Excel dělá. Pro sešit bez zapnutého iterativního výpočtu je vlastní odpověď Excelu na hodnotu, která ještě nebyla přepočtena v aktuálním průchodu, poslední vypočtená hodnota, takže engine reprodukující toto chování odpovídá referenční implementaci, nikoli ji aproximuje. Potřebujete-li skutečně konvergovanou odpověď nad sebe odkazujícím se modelem, mechanismem je iterativní výpočet s explicitním limitem iterací, předmět článku o iterativním výpočtu, a ten platí pro skutečné cykly, nikoli pro překryvy scanů

Regresní riziko ukrývající se v opravě

Přidání LookupScan do TXLSDepRange přineslo riziko, které nemá nic společného s vyhledáváním a všechno s Pascalem. TXLSDepRange je nespravovaný záznam, takže lokální proměnná tohoto typu není nulově inicializována. Každé místo v kódové základně, které jeden staví ručně, včetně bloků závislostí datových tabulek a několika testovacích pomocníků, muselo být proto aktualizováno, aby explicitně nastavilo nové pole. Vynecháte-li jedno, rozhodne jakýkoli bajt, který se náhodou vyskytl na zásobníku, zda se ta reference chápe jako scan hrana, čímž vznikne bug přepočtu, který se objevuje a mizí s nesouvisejícími změnami kódu

// Nové booleovské pole v nespravovaném záznamu dělá z každého
// ručního místa konstrukce latentní bug. Dvě bezpečné idiomy:
var
  R: TXLSDepRange;
begin
  FillChar(R, SizeOf(R), 0);      // vynulujte vše, poté vyplňte
  R.Sheet1 := SheetIndex;
  R.Sheet2 := SheetIndex;
  R.Row1 := Row; R.Col1 := Col;
  R.Row2 := Row; R.Col2 := Col;

  // nebo nastavte každé pole, včetně nového, na každém místě
  R.LookupScan := False;
end;

Obecné pravidlo, které to vysloužilo: přidání pole do záznamu stavěného na zásobníku na víc než hrstce míst je změna s vyšším rizikem, než vypadá, a kompilátor vám s hledáním těch míst nepomůže. Je-li záznam dosažitelný z horké cesty, dávejte přednost pomocníkovi, který jej inicializuje úplně, před důvěrou, že se každé místo volání aktualizuje

Jak rozlišit skutečný cyklus od překryvu scanu

Nic na této změně neoslabuje detekci cyklů. =B7+1 v B7 je stále cyklus, řetěz tří vzorců uzavírající se na sebe je stále cyklus a obojí se stále hlásí přes výsledek přepočtu, přičemž členové cyklu si ponechávají své předchozí cachované hodnoty, zatímco všechno mimo cyklus zůstává aktuální. Změnilo se jen to, že argument lookup pole už nevyrobí cykly, které Excel nevidí

Auditujete-li pracovní sešit a chcete vědět, které reference engine skutečně rozřešil a v jakém pořadí, je nástrojem k tomu vyhodnocovací tracer; článek o vyhodnocovacím traceru vzorců pokrývá, jak číst jeho výstup. HotXLS je nativní tabulková komponenta pro Delphi a C++Builder, která čte a zapisuje XLS, XLSX, ODS a CSV bez nainstalovaného Excelu a přepočtový engine je stejný v každém formátu; aktuální pokrytí funkcí a enginu je uvedeno na stránce produktu HotXLS Delphi spreadsheet component