Tahallisten kehäviittausten (circular references) laskemiseksi Delphissä HotXLS tarjoaa kehälaskennan (iterative calculation) XLSX-moottorissaan: aseta TXLSXWorkbook.Iterate arvoon True, ja TXLSXWorkbook.Recalculate käy läpi kunkin havaitun kehän kohti kiintopistettä — jopa IterateCount-kierroksen verran tai kunnes jokainen solu muuttuu vähemmän kuin IterateDelta-arvo — sen sijaan, että se palauttaisi virheen #REF! ja luovuttaisi
Tällä erolla on suurempi merkitys kuin pelkkä boolean-arvo antaa ymmärtää. Sama moottori, joka havaitsee kehäviittauksen ja kieltäytyy silmukoimasta sitä, arvioi kyseisen kehän tietoisesti, kun yksi ominaisuus käännetään. Näiden kahden toimintatavan pitäminen erillään — milloin kehä on raportoitava virhe ja milloin se on ratkaistava malli — on tämän artikkelin koko aihe
Miksi kehäviittaus aiheuttaa virheen oletusarvoisesti?
Oletusarvoisesti HotXLS käsittelee mitä tahansa kehäviittausta laadintavirheenä ja raportoi sen sen sijaan, että laskisi sen. TXLSXWorkbook.Recalculate rakentaa kaavojen riippuvuuskaavion (formula dependency graph), arvioi jokaisen kaavasolun topologisessa järjestyksessä ja palauttaa virheen lxErrorRef heti havaitessaan kehän — solmut, joita ei voida koskaan vapauttaa topologisen lajittelun aikana. Nämä kehän jäsenet säilyttävät aiemmat välimuistiin tallennetut arvonsa. Jokainen kehän ulkopuolinen kaava arvioidaan silti normaalisti. Tämän kaavion mekaniikkaa ja syytä, miksi kehän jäsenet ohitetaan silmukoinnin sijasta, käsitellään artikkelissa inkrementaalinen kaavojen uudelleenlaskenta ja riippuvuuskaavio
Oletusarvo on turvallinen, koska useimmat kehät ovat bugeja: yhteenvetorivi on vahingossa sisällytetty omaan SUM-alueeseensa tai kopiointi ja liittäminen on siirtänyt viittauksen itseensä. Äänekäs virhekoodi uudelleenlaskennassa on juuri sitä mitä haluat näissä tapauksissa. Mutta tietty ja tärkeä malliluokka on kehämäinen tarkoituksella. Korkoa korolle -aikataulut, kehämäiset kustannus- tai yleiskulujen kohdistukset osastojen välillä ja saldoon perustuvat palkkiolaskelmat kuvaavat kaikki arvoa, joka laillisesti syöttää tietoa takaisin omiin syötteisiinsä — ja Excel laskee ne vain silloin, kun käyttäjä valitsee Tiedosto → Asetukset → Kaavat → Ota käyttöön kehälaskenta
Miten kehälaskenta otetaan käyttöön HotXLS:ssä?
HotXLS peilaa tuota Excelin valintaruutua kolmella TXLSXWorkbook-luokan ominaisuudella: Iterate kytkee tilan päälle, ja kun se on True, havaittu kehä annetaan kehälaskentaratkaisijalle (iterative solver) sen sijaan, että se tuottaisi virheen lxErrorRef. Harkitse tyypillistä korkoa korolle -soluparia, jossa loppusaldo riippuu korosta ja korko riippuu saldosta
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Model');
Sheet.Cells[1, 2].Value := 1000; // B1: alkupääoma
Sheet.Cells[2, 2].Value := 0.05; // B2: jakson korko
Sheet.Cells[3, 2].Formula := 'B1+B4'; // B3: saldo = pääoma + korko
Sheet.Cells[4, 2].Formula := 'B3*B2'; // B4: korko = saldo * korko
Book.Iterate := True; // ota kehälaskenta käyttöön
if Book.Recalculate = lxOk then
// B3 konvergoituu arvoon 1052.63..., B4 arvoon 52.63...
Report(Sheet.Cells[3, 2].Value);
finally
Book.Free;
end;
end;B3 viittaa soluun B4 ja B4 viittaa soluun B3, joten riippuvuuskaavio raportoi kahden solmun kehän. Jos Iterate jätetään oletusarvoonsa False, tämä pari palauttaisi virheen lxErrorRef eikä kumpikaan solu asettuisi. Kun se on True, Recalculate siementää (seed) kehän nykyisistä välimuistiin tallennetuista arvoista ja arvioi sen jäsenet kierros kierrokselta, syöttäen kunkin kierroksen tulokset takaisin seuraavan kierroksen syötteiksi, kunnes luvut lakkaavat liikkumasta. Tässä suljettu muoto on pääoma / (1 - korko), joten saldo asettuu arvoon 1052.63 ja korko arvoon 52.63 — samat luvut, jotka Excel tuottaa kehälaskennan ollessa käytössä
Mikä saa iteroinnin pysähtymään?
Kaksi toisistaan riippumatonta pysäytysehtoa rajoittaa ratkaisijaa, ja molempien ymmärtäminen estää mallia joko antamasta virhettä tai pyörimästä ikuisesti. IterateCount on kehän jäsenten uudelleenarvioinnin kova katto. Se on oletusarvoisesti 100, vastaten Exceliä. IterateDelta on konvergenssikynnys: jokaisen kierroksen jälkeen ratkaisija mittaa suurimman numeerisen muutoksen kaikissa kehän soluissa, ja kun tuo suurin muutos putoaa alle IterateDelta-arvon — oletusarvoisesti 0.001 —, kierrossilmukka katkeaa etuajassa. Se ehto, joka täyttyy ensin, lopettaa iteroinnin
Book.Iterate := True;
Book.IterateCount := 1000; // kova katto: enintään 1000 kierrosta kehän yli
Book.IterateDelta := 0.0001; // konvergenssi: lopeta, kun jokainen solu muuttuu < 0.0001
case Book.Recalculate of
lxOk:
// kehä konvergoitui TAI saavutti 1000 kierroksen katon ja säilytti
// viimeisen iteraation arvot -- molemmat polut palauttavat lxOk, kun Iterate on True
SaveWorkbook(Book);
lxErrorRef:
// saavutettavissa vain, kun Iterate = False: kehä raportoitiin, ei ratkaistu
LogWarning('Circular reference with iteration disabled');
end;
Yksi seuraus on syytä sanoa suoraan, koska se on toiminnon rehellinen raja. Kun katto saavutetaan ilman, että muutos putoaa alle IterateDelta-arvon, SolveCycleIteratively ei nosta virhettä — se palauttaa lxOk ja jättää solut säilyttämään viimeisen iteraationsa arvot, aivan kuten Excel kirjoittaa viimeisimmät lasketut luvut, kun sen oma iteraatioraja saavutetaan ilman konvergenssia. Siksi onnistunut palautuskoodi Recalculate-metodista kehälaskentatilassa tarkoittaa 'ratkaisija ajettiin', ei 'ratkaisija konvergoitui'. Malli, jonka palautekehä (feedback loop) eroaa (diverges) tai värähtelee (oscillates), kuluttaa hiljaisesti kaikki IterateCount-kierrokset ja antaa takaisin lukuja, jotka eivät ole lainkaan kiintopisteitä, eikä mikään poikkeus merkitse eroa
Miten asetus tallennetaan XLSX- ja XLS-tiedostoihin?
Kehälaskenta-asetukset säilyvät molemmissa taulukkolaskentamuodoissa, joten Excelissä avattu työkirja käyttäytyy samalla tavalla kuin Delphi-koodisi sen konfiguroi. XLSX-puolella kirjoittaja tuottaa OOXML:n <calcPr>-elementin vain silloin, kun Iterate on True, ja se jättää pois kaikki määritteet, jotka ovat edelleen oletusarvoissaan, pitääkseen tulosteen mahdollisimman pienenä: oletuksia käyttävä työkirja kirjoittaa vain <calcPr iterate="1"/>, kun taas iterateCount kirjoitetaan vain, jos se poikkeaa arvosta 100, ja iterateDelta vain silloin, kun se poikkeaa arvosta 0.001. Avattaessa TXLSXWorkbook lukee samat kolme määritettä takaisin, joten edestakainen matka on symmetrinen
Vanha BIFF8 (.xls) -moottori, TXLSWorkbook, kuljettaa vastaavan tilan kolmen erillisen tietueen kautta eri ominaisuustrippelin alla. EnableIteration mäppäytyy CalcIter-tietueeseen ($0011, [MS-XLS] §2.4.33), MaxIterations CalcCount-tietueeseen ($000C, [MS-XLS] §2.4.31) ja MaxIterationChange CalcDelta-tietueeseen ($0010, [MS-XLS] §2.4.32). Asettajat valvovat spesifikaation alueita — CalcCount-arvon on oltava välillä 1..32767, joten MaxIterations on rajoitettu, ja negatiivinen MaxIterationChange napsahtaa takaisin 0.001-oletukseen. Aseta ne ladatulle .xls-työkirjalle, niin kolme laskentatietuetta kirjoitetaan uskollisesti tallennuksen yhteydessä
var
Book: TXLSWorkbook; // BIFF8 (.xls) -moottori
begin
Book := TXLSWorkbook.Create;
try
Book.Open('model.xls');
Book.EnableIteration := True; // CalcIter record $0011
Book.MaxIterations := 500; // CalcCount-tietue $000C (rajoitettu välille 1..32767)
Book.MaxIterationChange := 0.0001; // CalcDelta record $0010
Book.SaveAs('model.xls'); // kolme laskentatietuetta siirtyy edestakaisin
finally
Book.Free;
end;
end;Huomaa tietoinen nimien jakaminen: XLSX-moottori puhuu arvoista Iterate / IterateCount / IterateDelta (OOXML-sanasto), kun taas BIFF8-moottori puhuu arvoista EnableIteration / MaxIterations / MaxIterationChange (kohdistettuna [MS-XLS] tietuenimiin). Molemmat trippelit kuvaavat samoja kolmea nuppia — päällä/pois-kytkintä, iteraatiokattoa ja konvergenssierotusta — samoilla oletuksilla: pois päältä, 100 ja 0.001
Milloin kehäviittaus on bugi eikä malli?
Kehälaskennan käyttöönotto ei ole tapa saada kehäviittausvaroituksia katoamaan, ja sen käsitteleminen sellaisena on sudenkuoppa. Iterate-ominaisuuden kytkeminen päälle globaalisti muuttaa jokaisen vahingossa tapahtuneen kehän — ne, jotka oletusarvoinen virhekoodi oli tarkoitettu pyydystämään — hiljaisesti konvergoituneeksi tai hiljaisesti ei-konvergoituneeksi luvuksi. Kurinalaisuus toimii päinvastoin: pidä Iterate arvossa False normaalina tilana, jotta aidot laadintavirheet nousevat edelleen esiin muodossa lxErrorRef, ja ota kehälaskenta käyttöön vain työkirjoissa, joiden kehämäisyys on suunniteltua ja ymmärrettyä
Kun kehä ilmestyy etkä ole varma kumpaa tyyppiä se on, kaavanarvioinnin jäljitin on työkalu, joka erottaa ne toisistaan: jäljitä epäiltyä kaavaa, ja riippuvuusketju, joka taittuu takaisinkytkennäksi itseensä, tulee näkyväksi vaihe vaiheelta. Voit silloin päättää, koodaako se todellista palautekehää vai hajavirheellistä itseviittausta. Auttaa myös muistaa, että kehäsolu voi kutsua mitä tahansa sisäänrakennettua funktiota matkalla kehän ympäri — sama laskin, joka ratkaisee teknisen tai kompleksilukukaavan, arvioi kehän jäsenet jokaisella kierroksella —, joten hajoava (diverging) malli on usein kaavaongelma kehän sisällä, ei iteraatioasetusongelma
Käytännön muistilista on lyhyt. Varmista, että kehällä on todellinen kiintopiste ennen kuin luotat kehälaskentaan. Pidä IterateDelta tarpeeksi tiukkana, jotta 'konvergoitunut' tarkoittaa sitä, mitä mallisi tarvitsee. Ja odottamasi konvergoituneen Recalculate-kutsun jälkeen varmista tunnettu tulos pelkän lxOk-koodin luottamisen sijaan, sillä tuo koodi ei pysty erottamaan konvergenssia saavutetusta iteraatiokatosta
Kehällinen kehäviittauslaskenta on osa XLSX-moottoria HotXLS Delphi Excel -komponentissa, yhdessä sen rakentaman inkrementaalisen riippuvuuskaavion uudelleenlaskennan ja OOXML- ja BIFF8-tallennuksen kanssa, joka kuljettaa asetuksen jokaiseen kirjoittamaasi tiedostoon. Taloudellisille ja teknisille malleille, jotka ovat kehämäisiä tarkoituksella, se on ero virhekoodin ja vastauksen välillä