Lookup skeny HotXLS a falošné cyklické odkazy

Vložte =VLOOKUP(A1,B:B,1) do bunky v stĺpci B a Excel to vypočíta bez sťažnosti. Podajte ten istý zošit recalculačnému enginu s grafom závislostí a pravdepodobne dostanete chybu cyklického odkazu, pretože vzorec závisí od rozsahu, ktorý vzorec obsahuje. HotXLS presne to hlásil až do v2.361.98. Oprava nie je špeciálny prípad pre celostĺpcové rozsahy; je to rozlíšenie medzi dvoma druhmi hrán závislostí, ktoré engine tabuliek potrebuje a obyčajný orientovaný graf nemá

Argument lookup-array rodiny lookup — LOOKUP, MATCH, HLOOKUP, VLOOKUP, XLOOKUP a XMATCH — je teraz označený ako scan referencia. Scan referencia stále označuje neaktuálnosť, takže úprava bunky vnútri rozsahu prepočíta vzorec, ale nikdy neprispieva k detekcii cyklov ani k poradiu vyhodnocovania. Skutočné cykly sa stále nachádzajú; falošné sú preč

Prečo Excel povoľuje, aby lookup rozsah obsahoval vzorec?

Pretože ten argument nie je konzumovaný spôsobom aritmetického operandu. Rodina lookup skenuje rozsah po cacheovaných hodnotách a vracia zhodu; nevyžaduje, aby bol rozsah najprv vyhodnotený do konca. Excel chápe vlastným prekryvom sa rozsah lookup ako čítanie toho, čo tie bunky práve držia, čo je tá istá sémantika, ktorú aplikuje na akýkoľvek neiteratívny zošit: bunky, ktoré neboli v tomto priebehu prepočítané, prispievajú svojou poslednou vypočítanou hodnotou

Celostĺpcové referencie robia z tohto bežný prípad, nie exotický. B:B je idiomatický spôsob, ako zapísať „celú lookup tabuľku“ v hárku, kde sa riadky pripájajú, a akýkoľvek vzorec, ktorý býva v stĺpci B, je potom vnútri vlastného lookup rozsahu. Finančné modely, zlučovacie hárky a audítorské zošity to robia neustále, zvyčajne bez toho, aby si ktokoľvek všimol, že sa rozsah prekrýva

Čo robí graf závislostí s tým istým vzorcom

HotXLS prepočítava inkrementálne, čo vyžaduje skutočný graf závislostí: uzly pre bunky, hrany pre referencie, topologické poradie pre vyhodnocovanie a priebeh silno súvislých komponentov na klasifikáciu cyklov. Tá mechanika je popísaná v článku o inkrementálnom prepočte a je presne tým dôvodom, prečo sa falošná pozitívnosť objavila

Vytiahnite závislosti z =VLOOKUP(A1,B:B,1) v bunke B7 a druhý argument dáva rozsah obsahujúci samotné B7. Graf teraz má slučku na seba. Vstupný stupeň toho uzlu nikdy nedosiahne nulu, takže topologický priebeh ho nemôže nikdy naplánovať a priebeh komponentov ho klasifikuje ako cyklus. Engine uvažuje správne o grafe, ktorý dostal. Graf je zlý model, pretože kóduje jeden typ hrany tam, kde tabuľka má dva

Dve triedy hrán, jeden graf

Zmena pridáva príznak k záznamu rozriešenej referencie, TXLSDepRange.LookupScan, ktorý extraktor závislostí nastaví, keď prechádza argument lookup-array jednej zo šiestich funkcií. Po prúde sa hrany pôvodom z týchto referencií uchovávajú oddelene od obyčajných hrán: uzol grafu vedie zoznamy ScanDependents a ScanPrecedents popri svojich normálnych zoznamoch závislých a precedensov

Oddelenie je to, čo robí sémantiku správnu. Scan hrany prechádza propagácia neaktuálnosti, takže úprava kdekoľvek v B:B stále označí B7 za neaktuálny a B7 sa prepočíta. Scan hrany sa nikdy nepočítajú do vstupného stupňa a nikdy nevojdú do staviteľa komponentov, takže nemôžu vytvoriť topologické napätie a nemôžu byť klasifikované ako cyklus. Obe implementácie grafu v knižnici, klasický graf per zošit a graf workspace naprieč zošitmi nesúci analýzu komponentov, boli zmenené spolu; nechať ich rozísť by produkovalo zošit, ktorý sa prepočítava odlišne podľa toho, či bol otvorený sám alebo ako súčasť 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 rozsah pokrýva stĺpec B a tento vzorec v ňom býva
    Sheet.Cells[7, 2].Formula := 'VLOOKUP(A1,B:B,1)';

    case Book.Recalculate of
      lxOk:
        // Pred v2.361.98 bola táto vetva pre tento hárok nedosiahnuteľná
        SaveReport(Book);
      lxErrorRef:
        LogWarning('Genuine circular reference - review model inputs');
    end;
  finally
    Book.Free;
  end;
end;

Čoho sa vzdáte vylúčením scan hrán z poradia

Práve jednu vec a stojí za to povedať ju otvorene, nie ju skrývať. Pretože scan hrany sa nezúčastňujú topologického poradia, lookup vzorec môže byť vyhodnotený v tom istom priebehu skôr, než boli niektoré bunky v jeho lookup rozsahu prepočítané, a potom prečíta ich predchádzajúce hodnoty. Výsledok sa skonverguje pri ďalšom prepočte

To je prijateľné, pretože to je to, čo Excel robí. Pre zošit bez zapnutej iteratívnej kalkulácie je vlastná odpoveď Excelu na hodnotu ešte neprepočítanú v aktuálnom priebehu posledná vypočítaná hodnota, takže engine, ktorý reprodukuje toto správanie, sa zhoduje s referenčnou implementáciou, nie ju približuje. Ak potrebujete skutočne skonvergovanú odpoveď nad sebou odkazujúcim sa modelom, mechanizmom na to je iteratívna kalkulácia s explicitným limitom iterácií, pokrytá v článku o iteratívnej kalkulácii a platí pre skutočné cykly, nie pre scan prekrytia

Regresné nebezpečenstvo skryté v oprave

Pridanie LookupScan do TXLSDepRange zaviedlo riziko, ktoré nemá nič s lookupmi a všetko s Pascalom. TXLSDepRange je nespravovaný záznam, takže lokálna premenná tohto typu nie je nulovo inicializovaná. Každé miesto v kódovej báze, ktoré jeden stavia ručne, vrátane blokov závislostí data tabuliek a niekoľkých testovacích pomocníkov, preto muselo byť aktualizované, aby nastavilo nové pole explicitne. Vynecháte jedno a ľubovoľný bajt, ktorý sa náhodou nachádzal na zásobníku, rozhodne, či sa tá referencia považuje za scan hranu, čo produkuje recalculačnú chybu, ktorá sa objavuje a mizne s nesúvisiacimi zmenami kódu

// Nové Boolean pole v nespravovanom zázname robí z každého miesta
// ručnej stavby latentnú chybu. Dva bezpečné idiómy:
var
  R: TXLSDepRange;
begin
  FillChar(R, SizeOf(R), 0);      // vynulovať všetko, potom vyplniť
  R.Sheet1 := SheetIndex;
  R.Sheet2 := SheetIndex;
  R.Row1 := Row; R.Col1 := Col;
  R.Row2 := Row; R.Col2 := Col;

  // alebo nastaviť každé pole vrátane nového na každom mieste
  R.LookupScan := False;
end;

Všeobecné pravidlo, ktoré sa z toho vyhralo: pridať pole k záznamu konštruovanému na zásobníku na viac než hrsť miest je rizikovejšia zmena, než vyzerá, a kompilátor vám nepomôže miesta nájsť. Ak je záznam dosiahnuteľný z horúcej cesty, uprednostnite pomocníka, ktorý ho inicializuje úplne, pred dôverou, že každé miesto volania bude aktualizované

Rozlíšenie skutočného cyklu od scan prekrytia

Nič na tejto zmene neoslabuje detekciu cyklov. =B7+1 v B7 je stále cyklus, reťaz troch vzorcov, ktorá sa zatvára na seba, je stále cyklus a oba sa stále hlásia cez výsledok prepočtu s členmi cyklu, ktoré si ponechávajú predchádzajúce cacheované hodnoty, kým všetko mimo cyklu zostáva aktuálne. Zmenilo sa len to, že argument lookup-array už nevyrobí cykly, ktoré Excel nevidí

Ak audítujete zošit a chcete vedieť, ktoré referencie engine skutočne rozriešil a v akom poradí, nástrojom na to je tracer vyhodnocovania; článok o traceri vyhodnocovania vzorcov popisuje, ako čítať jeho výstup. HotXLS je natívna tabuľková komponenta pre Delphi a C++Builder, ktorá číta a zapisuje XLS, XLSX, ODS a CSV bez nainštalovaného Excelu a recalculačný engine je na každom formáte ten istý; aktuálne pokrytie funkcií a enginu je uvedené na produktovej stránke HotXLS Delphi spreadsheet component