HotXLS, natiivi Delphin ja C++Builderin Excel-kirjasto, suorittaa kaavojen inkrementaalisen uudelleenlaskennan TXLSXWorkbook.Recalculate-metodin kautta. Ensimmäinen kutsu rakentaa kaavojen riippuvuuskaavion ja laskee jokaisen kaavasolun arvon; jokainen myöhempi kutsu laskee uudelleen vain ne solut, joihin edellisen ajon jälkeen tehdyt arvokirjoitukset vaikuttavat. Tämä suoritetaan topologisessa järjestyksessä yhdellä pyyhkäisyllä, jonka resurssikustannus on suhteessa muuttuneiden (dirty) solujen määrään työkirjan koon sijaan
Tämä yksi suunnittelupäätös tekee eron talousmallin välillä, joka vastaa muuttuneeseen oletukseen millisekunneissa, ja sellaisen, joka jumiutuu sekunneiksi. Jos luot raportteja, joissa kourallinen syötesoluja ruokkii tuhansia jatkokaavoja, loppuosa tästä artikkelista selittää, mitä kaavio tekee, mitkä funktiot jättäytyvät inkrementaalisuuden ulkopuolelle ja miten kehäviittauksista (circular references) raportoidaan ikuisen silmukan sijaan
Miksi yhden solun muuttaminen laskee uudelleen satatuhatta kaavaa?
Yksinkertaisella kaavamoottorilla ei ole muistia siitä, kuka riippuu kenestä, joten sen ainoa turvallinen toimenpide minkä tahansa muokkauksen jälkeen on laskea kaikki uudelleen. Mikä pahempaa, perinteinen rekursiivinen strategia — jossa kaava A viittaa kaavaan B ja laskee B:n paikan päällä — laskee viitatut solut uudelleen ehdottomasti välittämättä välimuistiin tallennetuista arvoista. Ketju, jossa on n kaavaa ja kukin viittaa edelliseen, maksaa O(n²) laskentaa täyttä ajoa kohden, ja kehäviittaus suistaa rekursion jyrkänteeltä. Jokainen taulukkolaskentakehittäjä, joka on kytkenyt ketjutetun mallin rekursiiviseen laskimeen, on nähnyt molempien vikatilojen tapahtuvan
Excel itse ratkaisi tämän vuosikymmeniä sitten laskentaketjullaan: kaavasolut pidetään sellaisessa järjestyksessä, että muokkaus merkitsee pienen määrän soluja muuttuneiksi (dirty), ja moottori käy läpi vain ketjun kyseisen osan. HotXLS soveltaa samaa ideaa nimenomaisena riippuvuuskaavion, joka rakennetaan kerran käännetyistä kaavapuista ja jota käytetään uudelleen uudelleenlaskenta-ajoissa. Tässä ei ole kyse nokkeluudesta, vaan siitä, että uudelleenlaskennan kustannuksen tulisi seurata muokkauksen kokoa, ei työkirjan kokoa
Miten riippuvuuskaavio muuttaa muokkauksen yhdeksi ajoksi
HotXLS-riippuvuuskaavio antaa kullekin kaavasolulle yhden solmun, jossa reunaviivat kulkevat edeltävästä (precedent) riippuvaiseen (dependent). Kun koodisi kirjoittaa solun arvon, työkirja tallentaa solun muuttuneeksi; kun Recalculate ajetaan, muuttuneisuus leviää reunoja pitkin jokaiseen jatkokaavaan, ja muuttunut alikaavio lasketaan täsmälleen kerran topologisessa järjestyksessä Kahnin algoritmia käyttäen. Koska kaavaan ei koskaan viitata ennen sen edeltäjiä, kukin solmu tarvitsee vain yhden laskennan — mikä tekee ajosta O(dirty)-luokan
Topologinen järjestys korjaa myös rekursio-ongelman juuriaan myöten. Uudelleenlaskenta-ajon aikana moottori siirtyy erikoistilaan, jossa mikä tahansa viittaus toiseen kaavasoluun lukee suoraan kyseisen solun välimuistiin tallennetun arvon sen uudelleenlaskemisen sijaan — järjestys takaa, että välimuisti on jo tuore. Sama mekanismi tarkoittaa, että kehäviittaus ei voi aiheuttaa rajatonta rekursiota: mikään ajon sisällä ei koskaan palaa laskimeen naapurisolun kohdalla
Kukin tulos päätyy solun välimuistiin tallennettuun Value-arvoon, joten Recalculate-metodin paluun jälkeen luet tulokset samalla tavalla kuin minkä tahansa muun solun. Raportinluontisilmukassa malli on täsmälleen yllä olevan koodin kaltainen: lataa tai rakenna malli kerran, ja vaihtele sitten muutaman syötesolun kirjoittamisen ja Recalculate-kutsun välillä maksaen vain kaavoista, jotka todella riippuvat muuttuneesta tiedosta
var
Book: TXLSXWorkbook;
Inputs, Model: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Inputs := Book.Sheets.Add('Inputs');
Model := Book.Sheets.Add('Model');
Inputs.Cells[2, 2].Value := 0.05; // growth assumption
Model.Cells[2, 2].Formula := 'Inputs!B2*1000'; // XLSX formulas take no leading '='
Model.Cells[3, 2].Formula := 'B2*(1+Inputs!B2)';
// ... thousands more rows cascading off the same assumption ...
Book.Recalculate; // first call: builds the graph, full evaluation
Inputs.Cells[2, 2].Value := 0.07; // one edit marks one cell dirty
Book.Recalculate; // second call: only the downstream chain runs
finally
Book.Free;
end;
end;Mitkä Excel-funktiot pakottavat uudelleenlaskennan jokaisella ajolla?
HotXLS käsittelee funktioita NOW, TODAY, RAND, OFFSET ja INDIRECT epävakaina (volatile): kaikki ne sisältävät kaavat lasketaan uudelleen jokaisella Recalculate-ajolla riippumatta siitä, muuttuiko mikään aiemmassa vaiheessa. Kolme ensimmäistä ovat epävakaita samasta syystä kuin Excelissä — niiden tulos riippuu laskentahetkestä, ei muista soluista. OFFSET ja INDIRECT ovat epävakaita hienovaraisemmasta syystä: solut, joita ne lukevat, lasketaan suoritusaikana, joten kaavio ei voi tietää staattisesti, mitä reunaviivoja niille tulisi piirtää
Sama varovainen sääntö ulottuu viittauksiin, joita kaavion rakentaja ei voi kohdistaa yhteen suorakulmioon. Kaava, joka kulkee monialueisen nimetyn alueen läpi tai viittaa ulkoiseen työkirjaan, alennetaan vastaavasti epävakaaksi ja lasketaan uudelleen jokaisella ajolla. Tämä käytäntö on tarkoituksellinen: ylimääräinen laskenta maksaa hieman aikaa, mutta puuttuva riippuvuusreuna tarkoittaa hiljaisesti vanhentunutta arvoa toimitetussa raportissa, mikä on huomattavasti pahempi virhe. Jos mallisi tukeutuu työkirjalaajuisiin nimiin, kumppaniartikkeli nimetyistä alueista ja ristiintaulukollisista kaavoista kattaa sen, miten yksialueiset nimet selvitetään — ne osallistuvat kaavioon normaalisti
Käytännön ohje seuraa tästä suoraan. Pidä suuren mallin kriittiset polut tai aluviittauksissa, joissa kaavio voi tehdä tehtävänsä, ja eristä OFFSET ja INDIRECT vain niihin harvoihin paikkoihin, jotka todella tarvitsevat dynaamista osoitusta. Malli, jossa on tuhat epävakaata kaavaa, suorittaa nuo tuhat uudelleen jokaisella ajolla riippumatta siitä, kuinka pieni muokkaus oli — täsmälleen se käyttäytyminen, jonka Excel-käyttäjät tuntevat työkirjoista, jotka "laskevat uudelleen jokaisella näppäimen painalluksella"
Miten HotXLS raportoi kehäviittauksista?
TXLSXWorkbook.Recalculate palauttaa arvon lxOk puhtaassa ajossa ja lxErrorRef, kun se havaitsee kehäviittauksen. Kehän jäsenet tunnistetaan topologisen lajittelun aikana — ne ovat solmuja, joita Kahnin algoritmi ei voi koskaan vapauttaa — ja ne ohitetaan silmukan kiertämisen sijaan: niiden välimuistiin tallennetut arvot pysyvät sellaisina kuin ne olivat, kun taas kaikki kehän ulkopuoliset kaavat lasketaan edelleen normaalisti järjestyksessä. Kutsuva koodi saa selkeän virhekoodin jumiutumisen sijaan
case Book.Recalculate of
lxOk:
SaveReport(Book);
lxErrorRef:
// a reference cycle exists; cycle members kept their previous
// cached values and everything outside the cycle is up to date
LogWarning('Circular reference detected - review model inputs');
end;Sen löytäminen, mitkä solut muodostavat kehän, on vianetsintätyötä, ja kaavan laskennan jäljitin on siihen oikea työkalu: jäljitä epäiltyä kaavaa, ja itsensä päälle sulkeutuva viiteketju tulee näkyviin vaihe vaiheelta. Kehät todellisissa malleissa ovat lähes poikkeuksetta kirjoitusvirheitä — yhteenvetorivi on vahingossa sisällytetty omaan SUM-alueeseensa — joten selkeä virhekoodi uudelleenlaskenta-aikana on juuri se, mitä halutaan
Taulukkokaat, muuttuneisuuden seuranta ja kaavion uudelleenrakentaminen
CSE-taulukkokartat saavat yhden solmun koko ankkuroidulle suorakulmiolle, eivät omaa solmua jokaiselle solulle. Juurikaava lasketaan kerran ajoa kohden; tuloksena saatu matriisi kirjoitetaan suoraan jokaiseen jäsensoluun, ja kaava, joka viittaa mihin tahansa ankkuroidun alueen soluun — ei vain vasemman yläkulman ankkuriin — saa riippuvuusreunan tuosta juurisolmusta. Skalaaritulokset jaetaan suorakulmion yli siten, kuin Excelin perinteinen taulukkosemantiikka määrää
Muuttuneisuuden seuranta kytkeytyy tavallisiin ominaisuuksien asettajiin, joten koodissasi ei mikään muutu. Solun Value-arvon kirjoittaminen ilmoittaa työkirjalle ja merkitsee riippuvuudet muuttuneiksi; uuden Formula-kaavan määrittäminen on rakenteellinen muutos, joten se merkitsee koko kaavion vanhentuneeksi, ja seuraava Recalculate rakentaa sen uudelleen ennen laskentaa. Sivujen lisääminen, poistaminen tai siirtäminen mitätöi myös kaavion, sillä solmun identiteetti koodaa sivun indeksin. Kun kaaviota ei ole aktiivisena — työkirjassa, jolle et koskaan kutsu Recalculate-metodia — kytkennät maksavat vain yhden nil-tarkistuksen per asetus, joten tavalliset luku- ja kirjoitustyöt eivät kärsi
Yksi rajoitus on syytä todeta rehellisesti: kaavio seuraa solujen välisiä riippuvuuksia, joten OnUserFunction-metodin kautta rekisteröity käyttäjän määrittelemä funktio lasketaan uudelleen silloin, kun sen argumentteja syöttävät solut muuttuvat, aivan kuten mikä tahansa muu kaava. Jos laajennat moottoria tällä tavoin, artikkeli mukautetuista funktioista HotXLS-kaavamoottorissa käy läpi takaisinkutsusopimuksen ja sen, miten argumenttiarvot saapuvat
Kaavojen inkrementaalinen uudelleenlaskenta on osa vakiotason XLSX-moottoria HotXLS Delphi Excel Component -kirjastossa, yhdessä kaavalaskimen, nimettyjen alueiden ja sen kiihdyttämän tuonti-/vientiputken kanssa. Jos Delphi- tai C++Builder-sovelluksesi ylläpitää eläviä malleja — kuten hinnoittelutaulukoita, konsolidointityökirjoja tai raporttiketjuja — Recalculate tekee eron työkirjan uudelleenlaskennan ja pelkän muokkauksen uudelleenlaskennan välillä