Sekite Excel formulių skaičiavimą žingsnis po žingsnio Delphi aplinkoje

Excel slepia nedidelį derintuvą (debugger) atviroje vietoje. Pasirinkite langelį, atidarykite Formules ir spustelėkite „Apskaičiuoti formulę“ (Evaluate Formula), ir dialogas parodys formulę su viena pabraukta poišreiška. Paspauskite „Apskaičiuoti“ (Evaluate), ir ta poišreiška susitrauks į savo reikšmę, tada bus pabraukta kita, ir jūs matysite, kaip ilga išraiška susitraukia į vieną skaičių, po vieną redukavimą vienu metu. Tai greičiausias būdas rasti, kuri įdėto (nested) IF šaka iš tikrųjų suveikė, arba kuri nuoroda pateikė neteisingą sumą. HotXLS atkuria lygiai tokį patį elgesį per TXLSFormulaTracer, todėl Delphi arba C++Builder programa gali atvaizduoti tą patį žingsnių sąrašą darbaknygės auditui, sugeneruotos formulės derinimui arba norint paaiškinti kam nors, kodėl rezultatas gavosi būtent toks. Kiekviename įrašytame žingsnyje yra poišreiškos tekstas ir reikšmė, į kurią ji redukuojasi

Kaip redukavimo variklis pereina išraišką

Sekiklis (tracer) nesikiša į skaičiavimo variklį. Jis padalina formulę į žetonus (tokenizes) ir analizuoja ją naudodamas rekursyvaus nusileidimo analizatorių (recursive-descent parser), tada redukuoja medį į gylį (depth-first), pirmiausia pradedant nuo giliausios apskaičiuojamos poišreiškos. Kai mazgas redukuojamas į reikšmę, ta reikšmė yra įstatoma atgal į supančią išraišką kaip literalas, ir variklis paprašo tikrojo skaičiuotuvo perskaičiuoti dabar jau paprastesnę išraišką. Kadangi kiekvienas žingsnis apskaičiuojamas per viešą skaičiuoklės Calculate metodą, o ne per privatų trumpinį (shortcut), kiekvienas žingsnis tiksliai atitinka tai, ką duotų pilnas langelio perskaičiavimas. Analizatorius pagal dizainą yra neinvazinis, todėl jį galima paleisti prieš bet kurią skaičiuoklę, nesutrikdant jos būsenos

Analizatorius seka operatorių pirmumo kopėčiomis, turėdamas po vieną rekursyvų lygį kiekvienai pirmumo grupei (band). Nuo žemiausio susiejimo iki aukščiausio grupės yra: 0 lygio palyginimas (=, <>, <, >, <=, >=), 1 lygio eilučių sujungimas (&), 2 lygio sudėtis ir atimtis, 3 lygio daugyba ir dalyba, 4 lygio kėlimas laipsniu, ir galiausiai unarinis pliusas ir minusas po to. Kiekvienas lygis analizuoja lygį virš jo ieškodamas savo operandų, todėl aukštesnė grupė susieja stipriau. Tai yra toks pats pirmumas, kokį taiko Excel, todėl A1*B1+A2*B1 sumažina dvi sandaugas prieš sumą: daugyba yra 3 lygyje, sudėtis – 2 lygyje, todėl daugybos yra giliau medyje ir redukuojamos pirmos

Formulės sekimas ir ėjimas per žingsnius

Naudojimas atspindi pateiktą demonstracinę versiją Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Sukurkite skaičiuoklę (arba atidarykite esamą darbaknygę), sukurkite sekiklį virš lapo, iškvieskite Trace ir iteruokite grąžintą masyvą. Kiekvienas TXLSFormulaStep atskleidžia Depth įtraukai (indentation), Source pradinei poišreiškai, Expression tai pačiai poišreiškai su jau įstatytais operandais ir Value žingsnio rezultatui

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;

Langelio nuorodos išsprendžiamos pirmiausia ir pasirodo kaip atskiri jų pačių žingsniai, tada redukuojamos sandaugos, tuomet skliausteliuose esantis mokesčių koeficientas, ir galutinis dauginimas visa tai užbaigia. Laukas Depth leidžia jums padaryti įtrauką, kad giliausios redukcijos vizualiai būtų giliausiai – lygiai taip pat, kaip Excel pabraukia giliausią terminą prieš bet kurį išorinį

Nuo lokalės nepriklausančio literalo spąstai

Pati pavojingiausia šios visos schemos detalė yra nematoma angliškame kompiuteryje, bet triukšmingai lūžta vokiškame. Kai apskaičiuotas skaičius yra įstatomas atgal į formulės tekstą, jis turi būti užrašytas kaip eilutė, o po to iš naujo išanalizuotas skaičiavimo variklio, kuris . traktuoja kaip dešimtainį kablelį. Jei įstatymui būtų naudojama sistemos lokalė, vokiškas TFormatSettings įrašytų 1,08 kaip mokesčių koeficientą, kablelis būtų nuskaitomas kaip argumentų skirtukas, ir A1*B1*1,08 perskaičiavimas arba susiformuotų į neteisingą formą, arba visiškai nepavyktų

Sekiklis to išvengia formatuodamas kiekvieną skaitmeninį literalą per privatų TFormatSettings, kurį jis užfiksuoja konstravimo metu, priverstinai nustatydamas DecimalSeparator į . ir ThousandSeparator į #0, todėl grupavimo simbolis niekada neišvedamas. Tada FloatToStr sukuria literalą, kurį variklis visada gali perskaityti atgal, nepriklausomai nuo operatoriaus regioninių nustatymų

// 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)

Tai yra tokia klaida, kuri niekada nepasirodo paties autoriaus testavimo metu ir iškyla tik tada, kai klientas kitoje lokalėje paleidžia tą patį kodą, todėl verta tai pasakyti aiškiai: reikšmės perdavimas pirmyn ir atgal (round-tripping) per formulės tekstą yra serializavimo problema, o serializavimas turi nepriklausyti nuo lokalės (locale-free)

Būlio reikšmės redukuojasi į 1 ir 0

Susijęs sprendimas dėl įstatymo liečia logines reikšmes. Kai poišreiška įvertinama kaip loginė (boolean), sekiklis ją įrašo atgal kaip 1 arba 0, o ne kaip TRUE arba FALSE. Priežastis ta, kad redukuotas literalas turi švariai iš naujo būti išanalizuotas bet kokiame jį supančiame kontekste, o aritmetika yra reiklus atvejis. Jei palyginimas, pvz., A1>A2, redukuotųsi į tekstą TRUE ir tas tekstas atsidurtų TRUE*B1 viduje, perskaičiavimas priklausytų nuo to, ar variklis priima pliką (bare) loginį raktažodį daugyboje. 1 įstatymas visiškai išvengia šio klausimo, nes 1*B1 yra vienareikšmis bet kurioje aritmetinėje pozicijoje. Tai taip pat atitinka paties Excel tipų vertimą (coercion), kur TRUE elgiasi kaip 1, o FALSE – kaip 0 tą akimirką, kai tikimasi skaičiaus

Funkcijų iškvietimai redukuojami atomiškai

Naivus žingsnių variklis pirmiausia redukuotų funkcijos argumentus, o tada iškvietimą. Excel atveju tai yra neteisinga, ir sekiklis sąmoningai to nedaro. Funkcijos iškvietimas įvertinamas kaip visuma, iš jos originalaus teksto, vienu žingsniu. Priežastis yra trumpojo jungimo (short-circuit) semantika. IF, CHOOSE ir IFERROR įvertina tik tą šaką, kurią pasirenka, ir išankstinis argumentų redukavimas priverstų variklį apskaičiuoti šakas, kurių Excel niekada neliečia. Klasikinė auka yra apsauga nuo dalybos iš nulio, pvz., IF(B1=0,0,A1/B1): jei sekiklis redukuotų A1/B1 prieš įvertindamas IF, apsauga nesuveiktų ir sukeltų būtent tą klaidą, kuriai užkirsti kelią ji ir egzistuoja. Atomiškai įvertindamas visą iškvietimą, sekiklis išsaugo tingųjį įvertinimą (lazy evaluation), dėl kurio tokios apsaugos veikia

// 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.

Kompromisas yra tas, kad nematote funkcijos iškvietimo vidaus kaip atskirų žingsnių, bet tai yra teisingas elgesis. Argumentų redukcijų, kurių Excel niekada neatlieka, rodymas būtų labiau klaidinantis sekimas nei iškvietimo traktavimas kaip vieno skaičiavimo vieneto, koks jis iš tikrųjų ir yra

Argumentų skirtukai ir nepažeisti rėžiai

Dar dvi normalizacijos išlaiko perskaičiavimą sąžiningą. Skaičiavimo variklio kompiliatorius tikisi ; kaip funkcijos argumentų skirtuko, todėl, kai sekiklis atkuria funkcijos iškvietimą iš savo išanalizuoto medžio, jis sujungia argumentus su ;, net jei vartotojas iš pradžių įvedė ,. Formulė, užrašyta kaip SUM(A1,A2,A3), perskaičiuojama kaip SUM(A1;A2;A3), kurią variklis priima. Būtent reikšmių įstatymas daro šį atkūrimą būtiną, o teisingas skirtuko parinkimas užtikrina, kad atkurta formulė bus teisingai išanalizuota

Rėžių (range) nuorodos yra kitas atvejis. Toks rėžis kaip A1:A3 nėra skaliaras ir neturi būti padalintas į tris atskiras reikšmes, nes jį naudojanti funkcija tikisi rėžio argumento. Sekiklis išlaiko rėžį nepaliestą kaip jo pradinį tekstą ir leidžia supančiai funkcijai redukuotis kaip visumai. Išraiškoje SUM(A1:A3)*B1 rėžis lieka sveikas, SUM(A1:A3) redukuojasi į vieną skaičių vienu atomišku žingsniu, ir tik tada vykdoma išorinė daugyba. Tai yra ta pati riba, kurią Excel brėžia tarp rėžio operando ir skaliaro, kurį jis galiausiai pateikia

// 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));

Sudėjus kartu, šios taisyklės padaro žingsnių sąrašą ištikimu Excel komandos „Apskaičiuoti formulę“ (Evaluate Formula) atspindžiu, o ne jos apytikslia kopija. Redukcijos vyksta ta tvarka, kuria jas atlieka Excel, įstatyti literalai išlieka teisingi bet kokioje lokalėje, loginės reikšmės transformuojasi (coerce) taip, kaip tai daro Excel, o tingiosios funkcijos (lazy functions) išlieka tingiomis. Jei norite dar labiau išplėsti variklį su savo pačių funkcijomis, straipsnis apie formulių variklį ir pasirinktines funkcijas rodo, kaip jas užregistruoti, o sudėtingesniam skaitmeniniam darbui statistikos skirstinio funkcijų Delphi aplinkoje straipsnis aptaria įtaisytą biblioteką, pagal kurią sekiklis atlieka vertinimus. Visa tai pateikiama kaip dalis HotXLS skaičiuoklių komponento, skirto Delphi ir C++Builder, kartu su skaitymo, rašymo, formatavimo ir skaičiavimo API, aptartais kitur šiame tinklaraštyje