Excel ascunde un mic depanator la vedere. Selectați o celulă, deschideți Formule și faceți clic pe Evaluare Formulă, iar un dialog afișează formula cu o subexpresie subliniată. Apăsați Evaluare și acea subexpresie se reduce la valoarea sa, apoi următoarea este subliniată, și urmăriți cum o expresie lungă se micșorează la un singur număr, câte o reducere pe rând. Aceasta este cea mai rapidă metodă de a găsi care ramură dintr-un IF imbricat s-a executat de fapt, sau care referință a alimentat un total greșit. HotXLS reproduce exact acel comportament prin TXLSFormulaTracer, astfel încât un program Delphi sau C++Builder poate reda aceeași listă de pași pentru auditarea unui registru de lucru, depanarea unei formule generate sau explicarea motivului pentru care un rezultat a ieșit astfel. Fiecare pas înregistrat conține textul subexpresiei și valoarea la care se reduce
Cum parcurge motorul de reducere expresia
Tracerul nu intră în motorul de calcul. Tokenizează formula și o analizează sintactic cu un parser de descindere recursivă, după care reduce arborele în profunzime primul, subexpresia evaluabilă cea mai interioară prima. Când un nod se reduce la o valoare, acea valoare este substituită înapoi în expresia înconjurătoare ca literal, iar motorul cere calculatorului real să recalculeze expresia acum mai simplă. Deoarece fiecare pas este evaluat prin metoda publică Calculate a foii de lucru, nu printr-o scurtătură privată, fiecare pas este perfect de acord cu ceea ce ar produce o recalculare completă a celulei. Parserul este neinvaziv prin proiectare, ceea ce îi permite să ruleze pe orice foaie de lucru fără a-i perturba starea
Parserul urmează o scară de precedență a operatorilor, cu un nivel recursiv per bandă de precedență. De la legătura cea mai slabă la cea mai puternică, benzile sunt: nivelul 0 comparație (=, <>, <, >, <=, >=), nivelul 1 concatenare de șiruri (&), nivelul 2 adunare și scădere, nivelul 3 înmulțire și împărțire, nivelul 4 ridicare la putere, și în final plus și minus unare sub acestea. Fiecare nivel analizează nivelul de deasupra pentru operanzii săi, astfel încât o bandă mai înaltă leagă mai strâns. Aceasta este aceeași precedență pe care o aplică Excel, motiv pentru care A1*B1+A2*B1 reduce mai întâi cele două produse înainte de sumă: înmulțirea se află la nivelul 3, adunarea la nivelul 2, deci înmulțirile sunt mai adânci în arbore și se reduc primele
Urmărirea unei formule și parcurgerea pașilor
Utilizarea oglindește demo-ul furnizat la Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Construiți o foaie de lucru (sau deschideți un registru de lucru existent), construiți un tracer peste foaie, apelați Trace și iterați matricea returnată. Fiecare TXLSFormulaStep expune Depth pentru indentare, Source pentru subexpresia originală, Expression pentru acea subexpresie cu operanzii deja substituiți, și Value pentru rezultatul pasului
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;Referințele de celule se rezolvă primele și apar ca pași proprii, apoi produsele se reduc, după aceea factorul de taxă între paranteze, iar înmulțirea finală îl încheie. Câmpul Depth vă permite să indentați astfel încât reducerile cele mai interioare să fie vizibil cel mai adânci, exact cum Excel subliniază termenul cel mai interior înainte de orice termen exterior
Capcana literalului fără localizare
Cel mai periculos detaliu din întreaga schemă este invizibil pe o mașină cu limbă engleză și se sparge zgomotos pe una germană. Când un număr calculat este substituit înapoi în textul formulei, trebuie scris ca șir și apoi re-analizat de motorul de calcul, care tratează . ca separator zecimal. Dacă substituirea ar folosi locale-ul sistemului, un TFormatSettings german ar scrie 1,08 pentru factorul de taxă, virgula ar fi citită ca separator de argumente, iar recalcularea lui A1*B1*1,08 s-ar analiza greșit sau ar eșua complet
Tracerul evită acest lucru formatând fiecare literal numeric printr-un TFormatSettings privat pe care îl fixează la construcție, cu DecimalSeparator forțat la . și ThousandSeparator setat la #0 astfel încât să nu fie emis niciun caracter de grupare. FloatToStr produce astfel un literal pe care motorul îl poate citi întotdeauna, indiferent de setările regionale ale operatorului
// 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)Acesta este tipul de eroare care nu apare niciodată în testele proprii ale autorului și apare numai când un client dintr-un alt locale rulează același cod, deci merită spus clar: transportul unei valori prin textul formulei este o problemă de serializare, iar serializarea trebuie să fie fără locale
Booleenii se reduc la 1 și 0
O decizie de substituire înrudită privește valorile logice. Când o subexpresie se evaluează la un boolean, tracerul îl scrie înapoi ca 1 sau 0, nu ca TRUE sau FALSE. Motivul este că literalul redus trebuie să se re-analizeze curat în orice context îl înconjoară, iar aritmetica este cazul exigent. Dacă o comparație precum A1>A2 s-ar reduce la textul TRUE și acel text ar ateriza în interiorul lui TRUE*B1, recalcularea ar depinde de acceptarea de către motor a unui cuvânt cheie boolean simplu într-o înmulțire. Substituirea cu 1 ocolește complet problema, deoarece 1*B1 este neambiguu în orice poziție aritmetică. De asemenea, corespunde constrângerii proprii a Excel, unde TRUE se comportă ca 1 și FALSE ca 0 imediat ce se așteaptă un număr
Apelurile de funcții se reduc atomic
Un motor de pași naiv ar reduce mai întâi argumentele unei funcții și apoi apelul. Acesta este greșit pentru Excel, iar tracerul nu face asta în mod deliberat. Un apel de funcție este evaluat ca un întreg, din textul său original, într-un singur pas. Motivul îl constituie semantica de scurtcircuit. IF, CHOOSE și IFERROR evaluează numai ramura pe care o selectează, iar reducerea argumentelor mai întâi ar forța motorul să calculeze ramuri pe care Excel nu le atinge niciodată. Victima clasică este o gardă de împărțire la zero precum IF(B1=0,0,A1/B1): dacă tracerul ar reduce A1/B1 înainte de a evalua IF, garda ar eșua și ar ridica exact eroarea pe care există să o prevină. Evaluând întregul apel atomic, tracerul păstrează evaluarea leneșă care face să funcționeze astfel de gărzi
// 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.Compromisul este că nu vedeți interiorul apelului de funcție ca pași separați, dar acesta este comportamentul corect. Afișarea reducerilor de argumente pe care Excel nu le efectuează ar fi o urmărire mai înșelătoare decât tratarea apelului ca unitate de evaluare singulară care este de fapt
Separatori de argumente și intervale intacte
Două normalizări suplimentare mențin recalcularea corectă. Compilatorul motorului de calcul se așteaptă la ; ca separator de argumente ale funcției, deci când tracerul reconstruiește un apel de funcție din arborele analizat, unește argumentele cu ;, chiar dacă utilizatorul a scris inițial ,. O formulă scrisă ca SUM(A1,A2,A3) este recalculată ca SUM(A1;A2;A3), pe care motorul o acceptă. Substituirea valorilor este ceea ce face necesară această reconstruire, iar folosirea separatorului corect este ceea ce face ca reconstruirea să se analizeze corect
Referințele de interval sunt celălalt caz. Un interval precum A1:A3 nu este scalar și nu trebuie împărțit în trei valori separate, deoarece funcția care îl consumă se așteaptă la un argument de tip interval. Tracerul menține un interval intact ca text original și lasă funcția care îl înconjoară să se reducă ca întreg. În SUM(A1:A3)*B1, intervalul rămâne întreg, SUM(A1:A3) se reduce la un singur număr într-un pas atomic, și numai atunci rulează înmulțirea exterioară. Aceasta este aceeași limită pe care Excel o trasează între un operand de tip interval și scalarul la care contribuie în cele din urmă
// 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));Luate împreună, aceste reguli fac lista de pași o oglindă fidelă a comenzii Evaluare Formulă din Excel, nu o aproximare a ei. Reducerile au loc în ordinea în care Excel le execută, literalele substituite supraviețuiesc oricărui locale, booleenii se constrâng la fel cum îi constrânge Excel, iar funcțiile leneșe rămân leneșe. Dacă doriți să extindeți motorul cu propriile funcții, articolul despre motorul de formule și funcțiile personalizate arată cum să le înregistrați, iar pentru lucrul numeric mai intens, articolul despre funcțiile de distribuție statistică în Delphi acoperă biblioteca încorporată față de care tracerul evaluează. Toate acestea sunt livrate ca parte a componentei HotXLS spreadsheet component pentru Delphi și C++Builder, alături de API-urile de citire, scriere, formatare și calcul acoperite în altă parte pe acest blog