Jäljitä Excel-kaavojen laskentaa vaihe vaiheelta Delphissä

Excel piilottaa pienen vianetsintäohjelman (debugger) aivan näkyville. Valitse solu, avaa Kaavat-välilehti ja napsauta Arvioi kaava, niin näyttöön avautuu valintaikkuna, jossa kaavasta on alleviivattu yksi alilauseke. Paina Arvioi, niin kyseinen alilauseke tiivistyy arvoonsa, sitten seuraava alleviivataan, ja näet, kuinka pitkä lauseke kutistuu yksittäiseksi numeroksi yksi pelkistys (reduction) kerrallaan. Se on nopein tapa löytää, mikä sisäkkäisen IF-lausekkeen haara todella laukesi, tai mikä viittaus syötti väärän kokonaissumman. HotXLS toistaa täsmälleen saman toiminnan TXLSFormulaTracer-luokan kautta, joten Delphi- tai C++Builder-ohjelma voi renderöidä saman vaiheluettelon työkirjan auditointia, luodun kaavan vianetsintää tai sen opettamista varten, miksi tulos päätyi sellaiseksi kuin se päätyi. Jokainen tallennettu vaihe kantaa mukanaan alilausekkeen tekstin ja arvon, joksi se pelkistyy

Kuinka pelkistysmoottori käy lausekkeen läpi

Jäljitin ei kajoa laskentamoottoriin. Se tokenisoi kaavan ja jäsentää sen rekursiivisesti alaspäin etenevällä jäsentimellä (recursive-descent parser), minkä jälkeen se pelkistää puun syvyyssuunnassa (depth-first), sisin arvioitavissa oleva alilauseke ensin. Kun solmu pelkistyy arvoksi, tuo arvo sijoitetaan takaisin ympäröivään lausekkeeseen literaalina, ja moottori pyytää varsinaista laskinta laskemaan nyt yksinkertaisemman lausekkeen uudelleen. Koska jokainen vaihe arvioidaan laskentataulukon julkisen Calculate-metodin kautta yksityisen oikotien sijaan, jokainen vaihe täsmää täydellisesti siihen, mitä solun täysi uudelleenlaskenta tuottaisi. Jäsennin on suunniteltu puuttumattomaksi (non-invasive), minkä ansiosta se voi toimia mitä tahansa laskentataulukkoa vasten häiritsemättä sen tilaa

Jäsennin seuraa operaattorien presedenssitikapuita (operator-precedence ladder), jossa jokaisella presedenssivyöhykkeellä on yksi rekursiivinen taso. Alimmasta sidonnasta korkeimpaan vyöhykkeet ovat: taso 0 vertailu (=, <>, <, >, <=, >=), taso 1 merkkijonojen yhdistäminen (&), taso 2 yhteen- ja vähennyslasku, taso 3 kerto- ja jakolasku, taso 4 potenssiin korotus, ja lopuksi unaarinen plus ja miinus sen alapuolella. Jokainen taso jäsentää yläpuolellaan olevan tason operandikseen, joten korkeampi vyöhyke sitoo tiukemmin. Tämä on sama presedenssi, jota Excel soveltaa, minkä vuoksi A1*B1+A2*B1 pelkistää kaksi tuloa ennen summaa: kertolasku on tasolla 3, yhteenlasku tasolla 2, joten kertolaskut ovat syvemmällä puussa ja pelkistyvät ensin

Kaavan jäljitys ja vaiheiden läpikäynti

Käyttö peilaa toimitettua demoa osoitteessa Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Rakenna laskentataulukko (tai avaa olemassa oleva työkirja), rakenna jäljitin taulukon ylle, kutsu Trace ja iteroi palautettu taulukko. Jokainen TXLSFormulaStep tarjoaa kentän Depth sisennystä varten, Source alkuperäistä alilauseketta varten, Expression kyseistä alilauseketta varten, kun sen operandit on jo sijoitettu, ja Value vaiheen tulosta varten

uses
  SysUtils, Variants, lxHandle, lxHandleX, lxFormulaTrace;

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Tracer: TXLSFormulaTracer;
  Steps: TXLSFormulaStepArray;
  Final: Variant;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Order');
    Sheet.Cells[1, 1].Value := 10;    // A1 units
    Sheet.Cells[1, 2].Value := 25;    // B1 unit price
    Sheet.Cells[1, 3].Value := 0.08;  // C1 tax rate

    Tracer := TXLSFormulaTracer.Create(Sheet);
    try
      Final := Tracer.Trace('A1*B1*(1+C1)', Steps);
      for I := 0 to High(Steps) do
        Writeln(StringOfChar(' ', Steps[I].Depth * 2),
                Steps[I].Source, ' -> ', Steps[I].Expression,
                ' = ', VarToStr(Steps[I].Value));
      Writeln('result = ', VarToStr(Final));
    finally
      Tracer.Free;
    end;
  finally
    Book.Free;
  end;
end;

Soluviittaukset ratkeavat ensin ja näkyvät ominaan vaiheinaan, sitten tulot pelkistyvät, sitten suluissa oleva verokerroin, ja lopullinen kertolasku päättää sen. Depth-kenttä antaa sinun sisentää niin, että sisimmät pelkistykset näkyvät selvästi syvimmällä, aivan kuten Excel alleviivaa sisimmän termin ennen mitään ulompaa

Lokaalista riippumaton literaaliansa

Tämän koko järjestelmän vaarallisin yksityiskohta on näkymätön englantilaisella koneella ja hajoaa äänekkäästi saksalaisella. Kun laskettu luku sijoitetaan takaisin kaavatekstiin, se on kirjoitettava merkkijonona ja laskentamoottorin on jäsennettävä se uudelleen, jolloin moottori käsittelee merkkiä . desimaalierottimena. Jos sijoituksessa käytettäisiin järjestelmän lokaalia, saksalainen TFormatSettings kirjoittaisi verokertoimeksi 1,08, pilkku luettaisiin argumenttierottimena, ja lausekkeen A1*B1*1,08 uudelleenlaskenta joko jäsentyisi väärään muotoon tai epäonnistuisi kokonaan

Jäljitin välttää tämän muotoilemalla jokaisen numeerisen literaalin yksityisen TFormatSettings-asetuksen kautta, jonka se kiinnittää rakennusvaiheessa. DecimalSeparator on pakotettu arvoon . ja ThousandSeparator on asetettu arvoon #0, joten ryhmitysmerkkiä ei koskaan tulosteta. FloatToStr tuottaa silloin literaalin, jonka moottori voi aina lukea takaisin, riippumatta operaattorin alueellisista asetuksista

// Conceptually what the tracer pins once, at construction
FFloatFmt := FormatSettings;
FFloatFmt.DecimalSeparator := '.';
FFloatFmt.ThousandSeparator := #0;
// every reduced number is written with: FloatToStr(Double(V), FFloatFmt)

Tämä on sellainen ohjelmointivirhe, joka ei koskaan ilmene kirjoittajan omassa testauksessa ja nousee esiin vasta, kun asiakas toisessa lokaalissa ajaa samaa koodia. Siksi on syytä todeta selvästi: arvon edestakainen matka kaavatekstin kautta on sarjallistusongelma, ja sarjallistuksen on oltava lokaalista riippumatonta

Totuusarvot pelkistyvät 1:een ja 0:aan

Läheisesti liittyvä sijoituspäätös koskee loogisia arvoja. Kun alilauseke arvioidaan totuusarvoksi, jäljitin kirjoittaa sen takaisin arvona 1 tai 0, ei muodossa TRUE tai FALSE. Syynä on se, että pelkistetyn literaalin on jäsennyttävä puhtaasti uudelleen missä tahansa sitä ympäröivässä kontekstissa, ja aritmetiikka on tässä vaativa tapaus. Jos vertailu kuten A1>A2 pelkistyisi tekstiksi TRUE ja tuo teksti päätyisi osaksi lauseketta TRUE*B1, uudelleenlaskenta riippuisi siitä, hyväksyykö moottori paljaan totuusarvo-avainsanan kertolaskussa. Arvon 1 sijoittaminen ohittaa kysymyksen kokonaan, koska 1*B1 on yksiselitteinen missä tahansa aritmeettisessa sijainnissa. Se vastaa myös Excelin omaa tyyppimuunnosta (coercion), jossa TRUE käyttäytyy kuin 1 ja FALSE kuin 0 sillä hetkellä, kun odotetaan lukua

Funktiokutsut pelkistyvät atomaarisesti

Naiivi vaihemoottori pelkistäisi funktion argumentit ensin ja sitten kutsun. Se on väärin Excelille, ja jäljitin jättää sen tarkoituksella tekemättä. Funktiokutsu arvioidaan kokonaisuutena alkuperäisestä tekstistään yhdessä vaiheessa. Syynä on oikosulkusemantiikka (short-circuit semantics). IF, CHOOSE ja IFERROR arvioivat vain valitsemansa haaran, ja argumenttien pelkistäminen ensin pakottaisi moottorin laskemaan haaroja, joihin Excel ei koskaan koske. Klassinen uhri on nollalla jakamisen suojus, kuten IF(B1=0,0,A1/B1): jos jäljitin pelkistäisi A1/B1 ennen IF-lausekkeen arviointia, suojus epäonnistuisi ja nostaisi juuri sen virheen, jota se on olemassa estääkseen. Arvioimalla koko kutsun atomaarisesti, jäljitin säilyttää laiskan arvioinnin (lazy evaluation), joka saa tällaiset suojukset toimimaan

// IF is one atomic step; only the selected branch is evaluated
Final := Tracer.Trace('IF(A1>A2,A1*B1,A2*B1)', Steps);
// A1>A2 is true, so the step records A1*B1 as the chosen result;
// A2*B1 is never computed, exactly as Excel would do it.

Kompromissina on se, että et näe funktiokutsun sisään erillisinä vaiheina, mutta se on oikea toimintatapa. Sellaisten argumenttipelkistysten näyttäminen, joita Excel ei koskaan suorita, olisi harhaanjohtavampi jäljitys kuin kutsun käsitteleminen yhtenä ainoana arviointiyksikkönä, jota se todellisuudessa on

Argumenttierottimet ja ehjät alueet

Kaksi muutakin normalisointia pitää uudelleenlaskennan rehellisenä. Laskentamoottorin kääntäjä odottaa ;-merkkiä funktion argumenttierottimena, joten kun jäljitin rakentaa funktiokutsun uudelleen jäsennellystä puustaan, se yhdistää argumentit merkillä ;, vaikka käyttäjä olisi alun perin kirjoittanut ,. Kaava, joka on kirjoitettu muodossa SUM(A1,A2,A3), lasketaan uudelleen muodossa SUM(A1;A2;A3), minkä moottori hyväksyy. Arvojen sijoittaminen on se, mikä tekee tämän uudelleenrakentamisen välttämättömäksi, ja oikean erottimen saaminen on se, mikä saa uudelleenrakennuksen jäsentymään

Alueviittaukset ovat se toinen tapaus. Alue, kuten A1:A3, ei ole skalaari, eikä sitä saa jakaa kolmeen erilliseen arvoon, koska sitä kuluttava funktio odottaa alueargumenttia. Jäljitin pitää alueen ehjänä sen alkuperäisenä tekstinä ja antaa ympäröivän funktion pelkistyä kokonaisuutena. Kaavassa SUM(A1:A3)*B1 alue pysyy kokonaisena, SUM(A1:A3) pelkistyy yhdeksi luvuksi yhdessä atomaarisessa vaiheessa, ja vasta sitten ulompi kertolasku suoritetaan. Tämä on sama raja, jonka Excel vetää alueoperandin ja sen lopulta tuottaman skalaarin välille

// The range A1:A3 is never split; SUM is one atomic reduction,
// then the product with B1 reduces on top of it.
Final := Tracer.Trace('SUM(A1:A3)*B1', Steps);
for I := 0 to High(Steps) do
  Writeln(Steps[I].Source, ' = ', VarToStr(Steps[I].Value));

Yhdessä nämä säännöt tekevät vaiheluettelosta uskollisen peilikuvan Excelin Arvioi kaava -komennosta sen likiarvon sijaan. Pelkistykset tapahtuvat siinä järjestyksessä kuin Excel ne suorittaa, sijoitetut literaalit selviävät mistä tahansa lokaalista, totuusarvot muuntuvat siten kuin Excel ne muuntaa, ja laiskat funktiot pysyvät laiskoina. Jos haluat puskea moottoria pidemmälle omilla funktioillasi, artikkeli kaavamoottori ja mukautetut funktiot näyttää kuinka ne rekisteröidään, ja raskaampaa numeerista työtä varten tilastolliset jakaumafunktiot Delphissä kattaa sisäänrakennetun kirjaston, jota vasten jäljitin arvioi. Kaikki tämä toimitetaan osana HotXLS-taulukkolaskentakomponenttia Delphille ja C++Builderille, yhdessä luku-, kirjoitus-, muotoilu- ja laskentaohjelmointirajapintojen kanssa, joita käsitellään muualla tässä blogissa