HotXLS pretrage lookup-a i lažne kružne reference

Stavite =VLOOKUP(A1,B:B,1) u ćeliju u koloni B i Excel ga izračunava bez prigovora. Dajte istu radnu svesku motoru preračunavanja grafa zavisnosti i verovatno ćete dobiti grešku kružne reference, jer formula zavisi od opsega koji sadrži formulu. HotXLS je tačno to izveštavao do v2.361.98. Popravka nije poseban slučaj za opsege cele kolone; to je razlika između dve vrste grane zavisnosti koje motor tabele traži, a običan usmeren graf nema

Argument lookup-niza familije pretrage, LOOKUP, MATCH, HLOOKUP, VLOOKUP, XLOOKUP i XMATCH, sada je označen kao referenca skeniranja. Referenca skeniranja i dalje seje prljavost, pa uređivanje ćelije unutar opsega preračunava formulu, ali nikad ne doprinosi detekciji ciklusa niti uređenju evaluacije. Pravi ciklusi se i dalje nalaze; lažni su nestali

Zašto Excel dopušta da opseg pretrage sadrži formulu?

Zato se taj argument ne troši na način aritmetičkog operanda. Familija pretrage skenira opseg za keširane vrednosti i vraća podudaranje; ne traži da je opseg prvo evaluiran do kraja. Excel tretira preklapajući-sam-sobom opseg pretrage kao čitanje onoga što te ćelije trenutno drže, što je ista semantika koju primenjuje na bilo koju neiterativnu radnu svesku: ćelije koje nisu preračunate u ovom prolazu doprinose svoju poslednju izračunatu vrednost

Reference cele kolone čine ovo uobičajenim slučajem, a ne egzotičnim. B:B je idiomatski način da se napiše "cela tabela pretrage" u listu gde se redovi dodaju, i svaka formula koja živi u koloni B tada je unutar svog sopstvenog opsega pretrage. Finansijski modeli, listovi usaglašavanja i revizorske radne sveske ovo rade stalno, obično bez da itko primeti da se opseg preklapa

Šta graf zavisnosti radi sa istom formulom

HotXLS preračunava inkrementalno, što traži pravi graf zavisnosti: čvorove za ćelije, grane za reference, topološki redosled za evaluaciju i prolaz jako povezanih komponenti za razvrstavanje ciklusa. Ta mašinerija opisana je u članku o inkrementalnom preračunavanju, i upravo je to razlog zašto se lažno pozitivno pojavilo

Izvucite zavisnosti iz =VLOOKUP(A1,B:B,1) u ćeliji B7 i drugi argument daje opseg koji sadrži B7 sam. Graf sada ima samu-petlju. Ulazni stepen tog čvora nikad ne stigne do nule, pa topološki prolaz nikad ne može zakazati, i prolaz komponenti razvrstava ga kao ciklus. Motor rasuđuje ispravno o grafu koji mu je dat. Graf je pogrešan model, jer koduje jednu vrstu grane gde tabela ima dve

Dve klase grana, jedan graf

Izmena dodaje zastavicu zapisu razrešene reference, TXLSDepRange.LookupScan, koju izvodič zavisnosti postavlja kada obilazi argument lookup-niza jedne od šest funkcija. Nizvodno, grane potekle iz tih referenci čuvaju se odvojeno od običnih grana: čvor grafa drži popise ScanDependents i ScanPrecedents pored svojih normalnih popisa zavisnih i prethodnih

Razdvajanje je ono što čini semantiku ispravnom. Grane skeniranja prelaze se propagacijom prljavosti, pa uređivanje bilo gde u B:B i dalje označava B7 prljavim i B7 se preračunava. Grane skeniranja nikad se ne računaju u ulazni stepen i nikad ne ulaze u graditelja komponenti, pa ne mogu stvoriti topološki zastoj niti biti razvrstane kao ciklus. Obe implementacije grafa u biblioteci, klasični graf po radnoj svesci i graf radnog prostora preko sveski koji nosi analizu komponenti, menjane su zajedno; pustiti ih da odstanu proizvelo bi radnu svesku koja se preračunava drugačije zavisno od toga da li je otvorena sama ili kao deo radnog prostora

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;
    // Opseg pretrage pokriva kolonu B, i ova formula živi u njoj
    Sheet.Cells[7, 2].Formula := 'VLOOKUP(A1,B:B,1)';

    case Book.Recalculate of
      lxOk:
        // Pre v2.361.98 ova grana bila je nedostižna za ovaj list
        SaveReport(Book);
      lxErrorRef:
        LogWarning('Genuine circular reference - review model inputs');
    end;
  finally
    Book.Free;
  end;
end;

Šta odustajete isključivanjem grana skeniranja iz uređenja

Tačno jedna stvar, i vredi je izjaviti jasno umesto kriti. Pošto grane skeniranja ne učestvuju u topološkom redosledu, formula pretrage može biti evaluirana u istom prolazu pre nego što neke ćelije u svom opsegu pretrage budu preračunate, i tada će čitati njihove prethodne vrednosti. Rezultat konvergira na sledećem preračunavanju

To je prihvatljivo jer je to ono što Excel radi. Za radnu svesku bez uključenog iterativnog računanja, sopstveni odgovor Excel-a za vrednost još nepreračunatu u tekućem prolazu jeste poslednja izračunata vrednost, pa motor koji reprodukuje ovo ponašanje poklapa referentnu implementaciju umesto da je aproksimira. Ako vam treba istinski konvergiran odgovor preko samoreferencijalnog modela, mehanizam za to je iterativno računanje sa eksplicitnom granicom iteracija, pokriveno u članku o iterativnom računanju, i primenjuje se na prave cikluse umesto na preklapanja skeniranja

Opasnost regresije koja se krije unutar popravke

Dodavanje LookupScan u TXLSDepRange unelo je rizik koji nema ništa sa pretragama a sve sa Pascal-om. TXLSDepRange je neupravljani zapis, pa lokalna promenljiva tog tipa nije nula-inicijalizovana. Svako mesto u bazi koda koje ga gradi ručno, uključujući blokove zavisnosti tabela podataka i nekoliko test pomagača, moralo je biti ažurirano da postavi novo polje eksplicitno. Preskočite jedno i bilo koji bajt koji se slučajno nalazio na steku odlučuje da li se ta referenca tretira kao grana skeniranja, što proizvodi grešku preračunavanja koja se pojavljuje i nestaje sa nevezanim izmenama koda

// Novo Boolean polje u neupravljanom zapisu čini svako mesto
// ručne konstrukcije latentnom greškom. Dva bezbedna idioma:
var
  R: TXLSDepRange;
begin
  FillChar(R, SizeOf(R), 0);      // nula sve, pa popunite
  R.Sheet1 := SheetIndex;
  R.Sheet2 := SheetIndex;
  R.Row1 := Row; R.Col1 := Col;
  R.Row2 := Row; R.Col2 := Col;

  // ili postavite svako polje, uključujući novo, na svakom mestu
  R.LookupScan := False;
end;

Opšte pravilo koje je ovo zaradilo: dodavanje polja zapisu koji se konstruiše na steku na više od šake mesta promena je višeg rizika nego što izgleda, i kompajler vam neće pomoći naći mesta. Ako je zapis dostižan iz vruće putanje, dajte prednost pomagaču koji ga kompletno inicijalizuje nad verovanjem da će se svako mesto poziva ažurirati

Razlikovanje pravog ciklusa od preklapanja skeniranja

Ništa kod ove izmene ne slabi detekciju ciklusa. =B7+1 u B7 i dalje je ciklus, lanac od tri formule koji se zatvara na sebe i dalje je ciklus, i oba se i dalje izveštavaju kroz rezultat preračunavanja sa članovima ciklusa koji zadržavaju prethodne keširane vrednosti dok sve van ciklusa ostaje tekuće. Ono što se promenilo jeste samo to da argument lookup-niza više ne proizvodi cikluse koje Excel ne vidi

Ako revizionirate radnu svesku i želite znati koje reference je motor zapravo razrešio i u kojem redosledu, tracer evaluacije je alat za to; članak o tracer-u evaluacije formula pokriva kako čitati njegov izlaz. HotXLS je nativna Delphi i C++Builder komponenta tabele koja čita i piše XLS, XLSX, ODS i CSV bez instaliranog Excel-a, i motor preračunavanja isti je na svakom formatu; trenutna pokrivenost funkcija i motora navedena je na stranici proizvoda HotXLS Delphi spreadsheet component