Scanări de lookup și referințe circulare false în HotXLS

Puneți =VLOOKUP(A1,B:B,1) într-o celulă din coloana B și Excel o calculează fără reclamație. Dați același registru de lucru unui motor de recalcul cu graf de dependențe și este probabil să primiți o eroare de referință circulară, deoarece formula depinde de un interval care conține formula. HotXLS a raportat exact asta până la v2.361.98. Remedierea nu este un caz special pentru intervale pe coloană întreagă; este o distincție între două feluri de muchii de dependență de care are nevoie un motor de tabele și pe care un graf orientat simplu nu le are

Argumentul de vector de căutare al familiei de lookup, LOOKUP, MATCH, HLOOKUP, VLOOKUP, XLOOKUP și XMATCH, este marcat acum ca referință de scanare. O referință de scanare tot seamănă murdărie, deci editarea unei celule din interval recalculează formula, dar nu contribuie niciodată la detecția de cicluri sau la ordonarea evaluării. Ciclurile reale sunt tot găsite; cele false au dispărut

De ce permite Excel unui interval de căutare să conțină formula?

Pentru că acel argument nu este consumat așa cum este un operand aritmetic. Familia de lookup scanează intervalul după valori din cache și returnează o potrivire; nu cere ca intervalul să fi fost evaluat complet mai întâi. Excel tratează un interval de căutare care se autoveștește ca citind ceea ce dețin în prezent acele celule, ceea ce este aceeași semantică pe care o aplică oricărui registru de lucru non-iterativ: celulele care nu au fost recalculate în această trecere contribuie cu ultima lor valoare calculată

Referințele pe coloană întreagă fac din acesta cazul comun, nu unul exotic. B:B este modul idiomatic de a scrie „întreaga tabelă de căutare" într-o foaie în care rândurile sunt adăugate, iar orice formulă care trăiește în coloana B este atunci în interiorul propriului ei interval de căutare. Modelele financiare, foile de reconciliere și registrele de audit fac asta constant, de obicei fără ca cineva să observe că intervalul se suprapune

Ce face un graf de dependențe cu aceeași formulă

HotXLS recalculează incremental, ceea ce cere un graf real de dependențe: noduri pentru celule, muchii pentru referințe, o ordine topologică pentru evaluare și o trecere de componente puternic conexe pentru a clasifica ciclurile. Mecanismul acela este descris în articolul despre recalculul incremental, și este exact motivul pentru care a apărut falsul pozitiv

Extrageți dependențele din =VLOOKUP(A1,B:B,1) în celula B7 și al doilea argument produce un interval care conține B7 însăși. Graful are acum o buclă proprie. Gradul de intrare al nodului acela nu ajunge niciodată la zero, deci trecerea topologică nu îl poate programa niciodată, iar trecerea de componente îl clasifică drept ciclu. Motorul raționează corect despre graful pe care l-a primit. Graful este modelul greșit, deoarece encodează un singur tip de muchie acolo unde foaia de calcul are două

Două clase de muchii, un graf

Schimbarea adaugă un fanion înregistrării de referință rezolvat, TXLSDepRange.LookupScan, pe care extractorul de dependențe îl setează când parcurge argumentul de vector de căutare al uneia dintre cele șase funcții. În aval, muchiile provenite din acele referințe sunt stocate separat de muchiile obișnuite: nodul graf păstrează liste ScanDependents și ScanPrecedents alături de listele lui obișnuite de dependenți și precedenți

Separarea este cea care face semantica corectă. Muchiile de scanare sunt parcurse de propagarea murdăriei, deci o editare oriunde în B:B îl marchează tot pe B7 ca murdar, iar B7 recalculează. Muchiile de scanare nu sunt niciodată contorizate în gradul de intrare și nu intră niciodată în constructorul de componente, deci nu pot crea un blocaj topologic și nu pot fi clasificate drept ciclu. Ambele implementări de graf din bibliotecă, graful clasic per registru de lucru și graful de spațiu de lucru cross-workbook care poartă analiza de componente, au fost schimbate împreună; a le lăsa să diverge ar produce un registru de lucru care recalculează diferit în funcție de dacă a fost deschis singur sau ca parte a unui spațiu de lucru

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;
    // Intervalul de căutare acoperă coloana B, iar această formulă trăiește în ea
    Sheet.Cells[7, 2].Formula := 'VLOOKUP(A1,B:B,1)';

    case Book.Recalculate of
      lxOk:
        // Înainte de v2.361.98 această ramură era inaccesibilă pentru această foaie
        SaveReport(Book);
      lxErrorRef:
        LogWarning('Genuine circular reference - review model inputs');
    end;
  finally
    Book.Free;
  end;
end;

La ce renunțați excluzând muchiile de scanare din ordonare

Exact un lucru, și merită enunțat limpede în loc să fie ascuns. Deoarece muchiile de scanare nu participă la ordinea topologică, o formulă de căutare poate fi evaluată în aceeași trecere înainte ca unele celule din intervalul ei de căutare să fi fost recalculate, și va citi atunci valorile lor anterioare. Rezultatul converg la recalculul următor

Aceasta este acceptabilă deoarece este ceea ce face Excel. Pentru un registru de lucru fără calcul iterativ activat, răspunsul propriu al Excel pentru o valoare încă nerecalculată în trecerea curentă este ultima valoare calculată, deci un motor care reproduce acest comportament se potrivește cu implementarea de referință în loc să o aproximeze. Dacă aveți nevoie de un răspuns efectiv convergent peste un model autoreferențial, mecanismul pentru asta este calculul iterativ cu o limită explicită de iterații, tratat în articolul despre calculul iterativ, și se aplică ciclurilor reale, nu suprapunerilor de scanare

Pericolul de regresie ascuns în interiorul remedierii

Adăugarea lui LookupScan la TXLSDepRange a introdus un risc care nu are nimic de-a face cu căutările și totul de-a face cu Pascal. TXLSDepRange este un record nemanagement, deci o variabilă locală de acel tip nu este inițializată cu zero. Fiecare loc din baza de cod care construiește unul manual, inclusiv blocurile de dependență ale tabelelor de date și mai mulți helperi de test, a trebuit prin urmare actualizat pentru a seta noul câmp explicit. Săriți peste unul și orice octet s-a aflat întâmplător pe stivă decide dacă referința aceea este tratată ca muchie de scanare, ceea ce produce o eroare de recalcul care apare și dispare odată cu schimbări de cod fără legătură

// Un câmp Boolean nou într-un record nemanagement face fiecare loc de
// construcție manual o eroare latentă. Două idiomuri sigure:
var
  R: TXLSDepRange;
begin
  FillChar(R, SizeOf(R), 0);      // pune zero peste tot, apoi completează
  R.Sheet1 := SheetIndex;
  R.Sheet2 := SheetIndex;
  R.Row1 := Row; R.Col1 := Col;
  R.Row2 := Row; R.Col2 := Col;

  // sau setați fiecare câmp, inclusiv cel nou, la fiecare loc
  R.LookupScan := False;
end;

Regula generală câștigată: adăugarea unui câmp unui record construit pe stivă în mai mult de o mână de locuri este o schimbare cu risc mai mare decât pare, iar compilatorul nu vă va ajuta să găsiți locurile. Dacă recordul este accesibil dintr-o cale fierbinte, preferați un helper care îl inițializează complet în loc să aveți încredere că fiecare loc de apel va fi actualizat

Distingerea unui ciclu real de o suprapunere de scanare

Nimic din această schimbare nu slăbește detecția de cicluri. =B7+1 în B7 este tot un ciclu, un lanț de trei formule care se închide pe sine este tot un ciclu, iar ambele sunt tot raportate prin rezultatul recalculului, cu membrii ciclului păstrându-și valorile anterioare din cache în timp ce totul din afara ciclului rămâne actual. Ce s-a schimbat este doar că argumentul de vector de căutare nu mai fabrică cicluri pe care Excel nu le vede

Dacă auditați un registru de lucru și vreți să știți ce referințe a rezolvat efectiv motorul și în ce ordine, tracerul de evaluare este unealta pentru asta; articolul despre tracerul de evaluare a formulelor acoperă cum să citiți rezultatul lui. HotXLS este o componentă de tabele nativă Delphi și C++Builder care citește și scrie XLS, XLSX, ODS și CSV fără Excel instalat, iar motorul de recalcul este același pe fiecare format; acoperirea actuală de funcții și motor este listată pe pagina de produs HotXLS Delphi spreadsheet component