Seuraa Excel-kaavan arviointia askel askeleelta Delphissä

Excel piilottaa pienen debuggerin suoraan näkyville. Valitse solu, avaa kaavat ja valitse Evaluate Formula, jolloin valintaikkuna näyttää kaavan yhdellä alilausekkeella alleviivattuna. Paina "Evaluate" ja alilauseke tiivistyy arvokseen, minkä jälkeen seuraava alleviivataan, ja näet pitkän lausekkeen kutistuvan yhdeksi luvuksi yksi vähennys kerrallaan. Se on nopein tapa selvittää, mikä sisäkkäisen IF:n haara itse asiassa käynnistyi tai mikä viittaus syötti väärän loppusumman. HotXLS toistaa tämän tarkan toiminnan TXLSFormulaTracer:n avulla, jotta Delphi tai C++Builder-ohjelma voi tehdä saman vaiheluettelon työkirjan tarkistamista (auditing), luodun kaavan virheenkorjausta varten tai opettaakseen jollekulle miksi tulos tuli sellaisena kuin se näkyy. Jokainen kirjattu vaihe kantaa alilauseketekstiä ja arvoa mihin se lopulta supistuu

Miten supistusmoottori (reduction engine) kävelee lausekkeen

Jäljitysohjelma ei ulotu laskentakoneistoon (calculation engine). Se muuttaa (tokenizes) kaavan, ja jäsentää sen rekursiivisesti laskevalla jäsentäjällä (recursive-descent parser). Sitten se pienentää puun syvyys ensin -menetelmällä (depth-first), eli sisin arvioitavissa oleva alilauseke ratkaistaan ensimmäisenä. Kun solmu pienenee arvoon tämä arvo korvataan takaisin ympäröivään lausekkeeseen literaalina, ja moottori pyytää varsinaista laskinta laskemaan uudelleen nyt yksinkertaisemman lausekkeen. Koska jokainen vaihe arvioidaan laskentataulukon julkisen Calculate -menetelmän kautta yksityisen pikakuvakkeen sijaan, kukin askel on täsmälleen sama mitä solun täysi uudelleenlaskenta tuottaisi. Jäsennin (parser) ei ole suunnittelultaan ei-invasiivinen mikä on myös se seikka mikä sallii sen ajettavan mitä tahansa laskentataulukkoa vastaan sen tilaa häiritsemättä

Jäsennin noudattaa operaattorin prioriteetti-tikkaita (operator-precedence ladder), joissa on yksi rekursiivinen taso prioriteetti-kaistaa kohden. Pienimmästä sitovuudesta korkeimpaan kaistat ovat: taso 0 vertailu (=, <>, <, >, <=, >=), taso 1 merkkijonon ketjutus (&), tason 2 yhteen- ja vähennyslasku, tason 3 kerto- ja jakolasku, tason 4 eksponentio ja lopuksi unraari plus ja miinus. Jokainen taso jäsentää sen yläpuolella olevan tason operandinsa etsimiseksi, joten korkeampi kaista sitoutuu tiukemmin. Tämä on sama etusija, jota Excel itsekin soveltaa ja siksi A1*B1+A2*B1 pienentää kaksi tuloa (products) ennen summaa: kertolasku sijaitsee tasolla 3, yhteenlasku tasolla 2, joten kertolaskelmat ovat syvemmällä puussa ja vähentyvät ensin

Kaavan jäljittäminen ja askeleissa käveleminen

Käyttö peilaa toimitettua esittelyä kohdassa Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Luo laskentataulukko (tai avaa olemassa oleva työkirja), rakenna taulukkoon jäljitin, soita kohteeseen Trace, ja toista palautettu matriisi. Jokainen TXLSFormulaStep paljastaa Depth syvennyksen osalta, Sourcen alkuperäiselle alilausekkeelle, Expressionn kyseiselle alilausekkeelle kun sen operandit on jo korvattu, ja Valuen askeleen tuloksena

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 -yksiköt
    Sheet.Cells[1, 2].Value := 25;    // B1 -yksikön hinta
    Sheet.Cells[1, 3].Value := 0.08;  // C1 -veroprosentti

    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;

Soluviitteet ratkeavat ensin ja näkyvät ominaan vaiheina, minkä jälkeen tuotteet vähenevät, sitten suluissa oleva verokerroin, ja lopullinen kertolasku sulkee sen pois. Depth -kentän avulla voit sisennyttää siten, että sisimmät pelkistykset näkyvät sisimpinä – täsmälleen kuten Excel korostaa sisimmän osan (term) ennen ulompaa

Sijainnista vapaa literaaliloukku

Vaarallisin yksityiskohta koko järjestelmässä on näkymätön englanninkielisessä koneessa ja rikkoutuu kovaäänisesti saksalaisessa. Kun laskettu numero korvataan takaisin kaavatekstiin se on kirjoitettava merkkijonona ja sen jälkeen laskentakoneen on jäsennettävä se uudelleen, se käsittelee kohteen . desimaalipilkkuna. Jos korvaaminen käytti järjestelmän maa-asetusta (locale) ja saksalaisen TFormatSettings:n on kirjoitettava 1,08 verokertoimelle, pilkku luetaan argumentin erottimeksi, ja uudelleenlaskenta kohteessa A1*B1*1,08 joko jäsentyisi väärään muotoon tai epäonnistuisi suoraan

Jäljitin välttää tämän muotoilemalla jokaisen numeerisen literaalin yksityisen TFormatSettings:n kautta, jonka se liittää rakenteeseen DecimalSeparator pakotettuna kohteeseen . ja ThousandSeparatorn kohteen arvolla #0, joten mitään ryhmittelymerkkiä ei koskaan lähetetä. FloatToStr tuottaa sitten kirjaimellisen arvon minkä moottori (engine) voi aina lukea takaisin, riippumatta operaattorin alueellisista asetuksista

// Käsitteellisesti mitä jäljitin naulaa kerran rakentamisen yhteydessä
FFloatFmt := FormatSettings;
FFloatFmt.DecimalSeparator := '.';
FFloatFmt.ThousandSeparator := #0;
// jokainen vähennetty luku kirjoitetaan seuraavalla tavalla: FloatToStr(Double(V), FFloatFmt)

Tämä on sellainen bugi, joka ei koskaan näy kirjoittajan omissa testeissä ja ilmenee vasta, kun toisella alueella asuva asiakas käyttää samaa koodia, joten se on syytä sanoa suoraan: edestakaisen arvon vieminen kaavatekstin läpi on sarjoitusongelma ja sarjoituksen tulee olla paikkavapaa (locale-free)

Booleans pelkistyvät arvoihin 1 ja 0

Asiaan liittyvä korvauspäätös koskee loogisia arvoja. Kun alilausekkeen arvoksi tulee boolean, jäljittäjä (tracer) kirjoittaa sen takaisin numerona 1 tai 0, ei sanoin TRUE tai FALSE. Syynä on se että pienennetyn literaalin (reduced literal) täytyy pystyä jäsentymään siististi uudelleen missä tahansa sitä ympäröivässä kontekstissa, ja aritmetiikka on vaativa tapaus. Jos vertailu kuten A1>A2 pienennetään tekstiksi TRUE ja kyseinen teksti päätyy kohteeseen TRUE*B1, uudelleenlaskenta riippuisi siitä että moottori hyväksyisi paljaan boolean-avainsanan kerto-operaatiossa. Korvaamalla arvot 1 tai 0 ohittaa kysymyksen kokonaan, koska 1*B1 on yksiselitteinen kaikissa laskutoimituksissa. Tämä vastaa myös Excelin omaa pakottamista (coercion), jossa TRUE käyttäytyy kuin 1 ja FALSE kuin 0 sillä hetkellä, kun numeroa odotetaan

Toimintopuhelut pelkistyvät atomisesti

Naiivi askelettaja-moottori vähentäisi toiminnon argumentit ensin ja sitten vasta puhelun. Se on kuitenkin väärin Excelin osalta, ja juuri siksi jäljitin tarkoituksella ei sitä tee. Funktiokutsu arvioidaan kokonaisuutena alkuperäisestä tekstistään yhdessä vaiheessa. Syynä on oikosulun semantiikka (short-circuit semantics). IF, CHOOSE ja IFERROR arvioivat vain valitsemansa haaran, ja argumenttien pienentäminen ensin pakottaisi moottorin laskemaan oksat (branches), joihin Excel ei koskaan kosketa. Klassinen uhri on nollalla jaon vartija (divide-by-zero guard), kuten IF(B1=0,0,A1/B1): jos jäljitin (tracer) pienenisi A1/B1 arvoa ennen IF:n arviointia, vartija ampuisi ja nostaisi juuri sen virheen, jota sen on tarkoitus estää. Arvioimalla koko puhelun atomisesti, jäljitin säilyttää tällaisia "vartijoita" vaativan laiskan arvion

// JOS on yksi atomin askel; vain valittu haara arvioidaan
Final := Tracer.Trace('IF(A1>A2,A1*B1,A2*B1)', Steps);
// A1>A2 on tosi, joten askel kirjaa A1*B1 valituksi tulokseksi;
// A2*B1 ei koskaan lasketa, aivan kuten Excel tekisi sen itse.

Kompromissi (trade-off) on, että et näe toimintokutsun (function call) sisälle erillisinä askeleina, mutta se on kyllä täysin oikea käyttäytyminen. Excelin argumenttien vähennysten näyttäminen olisi harhaanjohtavampi jälki, kuin puhelun käsitteleminen sen aitona ja todellisena yksittäisenä arviointiyksikkönä

Argumenttierottimet ja ehjät alueet

Vielä kaksi normaalia pitää uudelleenlaskennan kurissa ja rehellisenä. Laskentakoneen (calculation engine) kääntäjä odottaa symbolia ; funktion argumentin erottimena, joten kun jäljittäjä (tracer) rakentaa uudelleen funktiokutsun sen jäsennetystä (parsed) puusta, se liittää argumentteihin symbolin ;, vaikka käyttäjä olisi alun perin kirjoittanut ,. Yksinkertainen kaava, kuten SUM(A1,A2,A3) lasketaan uudelleen muodossa SUM(A1;A2;A3), jonka myös moottori hyväksyy. Arvojen korvaaminen tekee uudelleenrakentamisen välttämättömäksi, ja erottimen oikea määrittäminen saa aikaan jäsennyksen oikein (parse)

Alueen viitteet ovat toinen asia. Aluetta kuten A1:A3 ei ole skalaarinen, eikä sitä saa jakaa kolmeen erilliseen arvoon, koska sitä kuluttava funktio odottaa alueargumenttia. Jäljitin pitää alueen ennallaan alkuperäisenä tekstinään ja antaa sulkeutuvan toiminnon toimia kokonaisuutena. Kirjoitetussa SUM(A1:A3)*B1-kaavassa alue pysyy kokonaisena, SUM(A1:A3) pienennetään yhdeksi luvuksi yhdessä atomin askeleessa ja vasta sen jälkeen suoritetaan ulompi kerto-lasku. Tämä on sama Excelin piirtämä raja-alue operandin (alue ja range) sekä skalaarin (joihin ne vaikuttavat) välillä

// Alue A1:A3 ei ole koskaan jaettu; SUM on yksi atomipieneneminen,
// sitten tulos B1:n kanssa asettuu sen päälle.
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 Excelin "Evaluate Formula" -komennon uskollisen peilin - pikemmin kuin vain arvion siitä. Pienentäminen tapahtuu siinä järjestyksessä, jossa Excel ne suorittaa, korvatut literaalit selviävät mistä tahansa paikasta, loogiset arvot kohersevat Excelin asettamalla tavalla, ja laiskat toiminnot pysyvät laiskoina. Jos haluat edistää moottoria omilla toiminnoillasi, tutustu Kaavamoottori ja mukautetut funktiot -artikkeliin joka näyttää kuinka voit rekisteröidä ne. Raskaampaan numerotyöskentelyyn suosittelemme Tilastolliset jakautumatoiminnot Delphissä -artikkeliamme joka kattaa myös sisäänrakennetun kirjaston, jota vastaan jäljittäjä (tracer) toimii. Kaikki toimitetaan osana HotXLS-laskentataulukko komponentteja Delphille ja C++Builderille samalla viivalla kirjoittamisen, asettelun sekä laajemman asiakirjojen APIen ja muiden sovellusliittymien kanssa joita käsitellään muualla tässä blogissa