HotXLS:n lookup-skannaukset ja väärät kehäviittaukset

Laita =VLOOKUP(A1,B:B,1) soluun sarakkeessa B, ja Excel laskee sen valittamatta. Syötä sama työkirja riippuvuusgraafin uudelleenlaskentakoneelle, ja saat todennäköisesti kehäviittausvirheen, koska kaava riippuu alueesta, joka sisältää kaavan. HotXLS raportoi juuri tuota versioon v2.361.98 asti. Korjaus ei ole erikoistapaus kokonaissarakealueille; se on ero kahdenlaisen riippuvuusreunan välillä, jonka laskentataulukkokone tarvitsee ja jota tavallinen suunnattu graafi ei ole

Hakuperheen, LOOKUP, MATCH, HLOOKUP, VLOOKUP, XLOOKUP ja XMATCH, hakutaulukkoargumentti merkitään nyt skannausviittaukseksi. Skannausviittaus kylvää yhä likaisuuden, joten solun muokkaaminen alueen sisällä laskee kaavan uudelleen, mutta se ei koskaan osallistu kehätunnistukseen eikä arviointijärjestykseen. Oikeat kehat löytyvät yhä; väärät ovat poissa

Miksi Excel sallii hakualueen sisältää kaavan?

Koska kyseistä argumenttia ei kuluteta niin kuin aritmeettista operandia. Hakuperhe skannaa alueen välimuistitettujen arvojen varalta ja palauttaa osuman; se ei vaadi, että alue olisi arvioitu loppuun ensin. Excel käsittelee itsensä päällekkäin menevää hakualuetta niin, että se lukee, mitä kyseiset solut tällä hetkellä pitävät, mikä on sama semantiikka, jota se soveltaa mihin tahansa ei-iteratiiviseen työkirjaan: solut, joita ei ole laskettu uudelleen tässä ajossa, antavat viimeksi lasketun arvonsa

Kokonaissarakeviittaukset tekevät tästä tavallisen tapauksen eikä eksoottista. B:B on idiomattinen tapa kirjoittaa ”koko hakutaulukko” taulukkovälilehdessä, johon rivejä lisätään, ja mikä tahansa kaava, joka asuu sarakkeessa B, on sitten oman hakualueensa sisällä. Taloudelliset mallit, täsmäytyystaulukot ja auditointityökirjat tekevät tätä jatkuvasti, yleensä ilman että kukaan huomaa alueen päällekkäisyyttä

Mitä riippuvuusgraafi tekee samalla kaavalla

HotXLS laskee uudelleen inkrementaalisesti, mikä vaatii aidon riippuvuusgraafin: solmuja soluille, reunoja viittauksille, topologisen järjestyksen arviointiin ja vahvasti yhtenäisten komponenttien ajon kehien luokitteluun. Kyseinen koneisto on kuvattu artikkelissa inkrementaalinen uudelleenlaskenta, ja se on täsmälleen syy siihen, että väärä positiivinen ilmestyi

Poimi riippuvuudet kaavasta =VLOOKUP(A1,B:B,1) solussa B7, ja toinen argumentti tuottaa alueen, joka sisältää B7:n itsensä. Graafissa on nyt itseensäsilmukka. Kyseisen solmun saapuvien kaarien määrä ei koskaan saavuta nollaa, joten topologinen ajo ei koskaan voi aikatauluttaa sitä, ja komponenttiajo luokittelee sen kehäksi. Kone päättelee oikein graafista, jonka se sai. Graafi on väärä malli, koska se koodaa yhden reunatyypin siellä, missä laskentataulukossa on kaksi

Kaksi reunaluokkaa, yksi graafi

Muutos lisää lipun ratkaistun viittauksen tietueeseen, TXLSDepRange.LookupScan, jonka riippuvuuspoimija asettaa kulkiessaan yhden kuudesta funktiosta hakutaulukkoargumentin läpi. Alavirrassa kyseisistä viittauksista lähtöisin olevat reunat tallennetaan erilleen tavallisista reunoista: graafisolmu pitää luettelot ScanDependents ja ScanPrecedents tavallisten riippuvaisten ja edeltäjien luetteloidensa rinnalla

Ero on se, mikä tekee semantiikasta oikean. Skannausreunat kuljetaan likaisuuden etenemisessä, joten muokkaus missä tahansa kohteessa B:B merkitsee yhä B7:n likaiseksi ja B7 laskee uudelleen. Skannausreunoja ei koskaan lasketa saapuvien kaarien määrään eikä ne koskaan mene komponentinrakentajaan, joten ne eivät voi luoda topologista umpikujaa eikä niitä voida luokitella kehäksi. Molemmat kirjaston graafitoteutukset, klassinen työkirjakohtainen graafi ja työkirjojen välinen työtilagraafi, joka kantaa komponenttianalyysin, muutettiin yhdessä; niiden erkaneminen tuottaisi työkirjan, joka laskee uudelleen eri tavalla riippuen siitä, avattiinko se yksin vai osana työtilaa

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;
    // Hakualue kattaa sarakkeen B, ja tämä kaava asuu siinä
    Sheet.Cells[7, 2].Formula := 'VLOOKUP(A1,B:B,1)';

    case Book.Recalculate of
      lxOk:
        // Ennen versiota v2.361.98 tämä haara oli saavuttamaton
        // tälle taulukkovälilehdelle
        SaveReport(Book);
      lxErrorRef:
        LogWarning('Genuine circular reference - review model inputs');
    end;
  finally
    Book.Free;
  end;
end;

Mistä luovut sulkiessasi skannausreunat järjestyksestä

Täsmälleen yhden asian, ja se kannattaa sanoa suoraan eikä piilottaa. Koska skannausreunat eivät osallistu topologiseen järjestykseen, hakukaava voidaan arvioida samassa ajossa ennen kuin jotkin sen hakualueen solut on laskettu uudelleen, ja se lukee silloin niiden aiemmat arvot. Tulos konvergoituu seuraavassa uudelleenlaskennassa

Se on hyväksyttävää, koska se on sitä, mitä Excel tekee. Työkirjalle ilman iteroivan laskennan käyttöönottoa Excelin oma vastaus arvolle, jota ei ole vielä laskettu uudelleen nykyisessä ajossa, on viimeksi laskettu arvo, joten kone, joka toistaa tämän käyttäytymisen, täsmää vertailutoteutukseen sen likiarvioimisen sijaan. Jos tarvitset aidosti konvergoituneen vastauksen itseensä viittaavan mallin yllä, mekanismi siihen on iteroiva laskenta eksplisiittisellä iteraatiorajalla, mistä kerrotaan artikkelissa iteroiva laskenta, ja se koskee oikeita kehiä eikä skannauspäällekkäisyyksiä

Regressioriski, joka piiloutuu korjauksen sisään

Arvon LookupScan lisääminen kohteeseen TXLSDepRange toi riskin, jolla ei ole mitään tekemistä hakujen kanssa ja kaikkea tekemistä Pascalin kanssa. TXLSDepRange on hallitsematon tietue, joten kyseisen tyypin lokaali muuttuja ei ole nolla-alustettu. Jokainen paikka koodikannassa, joka rakentaa yhden käsin, mukaan lukien datataulukon riippuvuuslohkot ja useat testiavustajat, piti siksi päivittää asettamaan uusi kenttä eksplisiittisesti. Ohita yksi, ja se tavu, joka sattui olemaan pinossa, päättää, käsitelläänkö kyseinen viittaus skannausreunana, mikä tuottaa uudelleenlaskentavirheen, joka ilmestyy ja katoaa asiaankuulumattomien koodimuutosten mukana

// Uusi Boolean-kenttä hallitsemattomassa tietueessa tekee jokaisesta
// manuaalisesta rakennuspaikasta latentin vian. Kaksi turvallista tapaa:
var
  R: TXLSDepRange;
begin
  FillChar(R, SizeOf(R), 0);      // nollaa kaikki, täytä sitten
  R.Sheet1 := SheetIndex;
  R.Sheet2 := SheetIndex;
  R.Row1 := Row; R.Col1 := Col;
  R.Row2 := Row; R.Col2 := Col;

  // tai aseta jokainen kenttä, myös uusi, jokaisella paikalla
  R.LookupScan := False;
end;

Yleinen sääntö, jonka tämä ansaitsi: kentän lisääminen tietueeseen, joka rakennetaan pinossa useammissa kuin kourallisessa paikkaa, on korkeampiriskinen muutos kuin se näyttää, eikä kääntäjä auta sinua löytämään paikkoja. Jos tietue on saavutettavissa kuumasta polusta, suosi apuria, joka alustaa sen kokonaan, sen sijaan että luottaisit jokaisen kutsupaikan päivittyvän

Oikean kehän erottaminen skannauspäällekkäisyydestä

Mikään tässä muutoksessa ei heikennä kehätunnistusta. =B7+1 solussa B7 on yhä kehää, kolmen kaavan ketju, joka sulkeutuu itseensä, on yhä kehää, ja molemmat raportoidaan yhä uudelleenlaskennan tuloksen kautta siten, että kehän jäsenet säilyttävät aiemmat välimuistiarvonsa, kun taas kaikki kehän ulkopuolella pysyy ajan tasalla. Se, mikä muuttui, on vain se, ettei hakutaulukkoargumentti enää valmista kehää, jota Excel ei näe

Jos auditoit työkirjaa ja haluat tietää, mitkä viittaukset kone oikeasti ratkaisi ja missä järjestyksessä, arviointiseurain on siihen työkalu; artikkeli kaavan arviointiseurain kattaa, miten sen tuloste luetaan. HotXLS on natiivi Delphi- ja C++Builder-laskentataulukkokomponentti, joka lukee ja kirjoittaa muodot XLS, XLSX, ODS ja CSV ilman asennettua Exceliä, ja uudelleenlaskentakone on sama jokaisella muodolla; nykyinen funktio- ja konekattavuus on lueteltu HotXLS Delphi spreadsheet -komponentin tuotesivulla