Az Excel egy kis hibakeresőt rejt mindenki szeme láttára. Válasszon ki egy cellát, nyissa meg a Képletek fület, és kattintson a Képlet kiértékelése gombra; ekkor megjelenik egy párbeszédablak a képlettel, amelyben egy részkifejezés alá van húzva. Nyomja meg a Kiértékelés gombot, és az adott részkifejezés összeomlik a saját értékévé, majd a következő aláhúzásra kerül, és Ön végignézheti, ahogy egy hosszú kifejezés lépésenként egyetlen számmá zsugorodik. Ez a leggyorsabb módja annak, hogy megtalálja, a beágyazott IF melyik ága futott le valójában, vagy melyik hivatkozás táplált be hibás végösszeget. A HotXLS pontosan ezt a viselkedést reprodukálja a TXLSFormulaTracer segítségével, így egy Delphi vagy C++Builder program ugyanazt a lépéslistát képes megjeleníteni egy munkafüzet auditálásához, egy generált képlet hibakereséséhez, vagy annak bemutatásához, hogy egy eredmény miért úgy alakult, ahogy. Minden rögzített lépés tartalmazza a részkifejezés szövegét és azt az értéket, amelyre csökken
Hogyan járja be a csökkentő motor a kifejezést
A nyomkövető nem nyúl bele a számítási motorba. Tokenizálja a képletet, és egy rekurzív leszálló elemzővel (recursive-descent parser) elemzi, majd mélységi bejárással csökkenti a fát, kezdve a legbelső kiértékelhető részkifejezéssel. Amikor egy csomópont értékké csökken, azt az értéket literálként helyettesíti vissza a környező kifejezésbe, és a motor arra kéri a valódi kalkulátort, hogy számítsa újra a most már egyszerűbb kifejezést. Mivel minden lépést a munkalap nyilvános Calculate metódusán keresztül értékel ki egy privát parancsikon helyett, minden lépés pontosan megegyezik azzal, amit a cella teljes újraszámítása eredményezne. Az elemző tervezésénél fogva non-invazív, ami lehetővé teszi, hogy bármilyen munkalapon lefusson anélkül, hogy megzavarná annak állapotát
Az elemző egy operátor-precedencia létrát követ, precedencia sávonként egy rekurzív szinttel. A legalacsonyabb kötéstől a legmagasabbig a sávok a következők: 0. szintű összehasonlítás (=, <>, <, >, <=, >=), 1. szintű karakterlánc-összefűzés (&), 2. szintű összeadás és kivonás, 3. szintű szorzás és osztás, 4. szintű hatványozás, és végül alatta az unáris plusz és mínusz. Minden szint a felette lévő szintet elemzi az operandusaihoz, így egy magasabb sáv szorosabban kötődik. Ugyanezt a precedenciát alkalmazza az Excel is, ezért az A1*B1+A2*B1 az összeg előtt csökkenti a két szorzatot: a szorzás a 3. szinten van, az összeadás a 2. szinten, így a szorzások mélyebben vannak a fában, és először csökkennek
Képlet nyomon követése és a lépések bejárása
A használat tükrözi a Demo/Delphi/FormulaTrace/FormulaTrace.dpr alatt szállított demót. Hozzon létre egy munkalapot (vagy nyisson meg egy meglévő munkafüzetet), készítsen egy nyomkövetőt a lap felett, hívja meg a Trace metódust, és iteráljon a visszaadott tömbön. Minden TXLSFormulaStep közzéteszi a Depth (mélység) tulajdonságot a behúzáshoz, a Source-t (forrás) az eredeti részkifejezéshez, az Expression-t (kifejezés) ahhoz a részkifejezéshez, amelynek az operandusai már be vannak helyettesítve, és a Value-t (érték) a lépés eredményéhez
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;A cellahivatkozások először oldódnak fel, és saját lépésként jelennek meg, majd a szorzatok csökkennek, ezután a zárójelezett adótényező, és végül az utolsó szorzás zárja a sort. A Depth mező lehetővé teszi a behúzást, így a legbelső csökkentések láthatóan a legmélyebben helyezkednek el, pontosan úgy, ahogy az Excel aláhúzza a legbelső tagot bármely külső tag előtt
A lokalizációmentes literál csapdája
A legveszélyesebb részlet ebben az egész sémában egy angol gépen láthatatlan, egy német gépen viszont hangosan eltörik. Amikor egy kiszámított számot visszahelyettesítenek a képlet szövegébe, karakterláncként kell megírni, majd a számítási motornak újra kell elemeznie, amely a .-ot tizedesvesszőként kezeli. Ha a helyettesítés a rendszer területi beállításait használná, egy német TFormatSettings 1,08-at írna az adótényezőre, a vesszőt argumentumelválasztóként olvasná, és az A1*B1*1,08 újraszámítása vagy rossz formátumban elemezné, vagy egyenesen meghiúsulna
A nyomkövető ezt úgy kerüli el, hogy minden numerikus literált egy privát TFormatSettings segítségével formáz, amelyet a felépítéskor rögzít, a DecimalSeparator-t kötelezően .-ra, a ThousandSeparator-t pedig #0-ra állítja, így soha nem bocsát ki csoportosító karaktert. A FloatToStr ezután olyan literált hoz létre, amelyet a motor mindig képes visszaolvasni, függetlenül az operátor területi beállításaitól
// 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)Ez az a fajta hiba, amely soha nem jelenik meg a szerző saját tesztelésében, és csak akkor bukkan fel, amikor egy másik területi beállítással rendelkező ügyfél futtatja ugyanazt a kódot, így érdemes nyíltan kijelenteni: egy érték képletszövegen keresztüli oda-vissza alakítása szerializációs probléma, és a szerializációnak lokalizációmentesnek kell lennie
A logikai értékek 1-re és 0-ra csökkennek
Egy kapcsolódó helyettesítési döntés a logikai értékekre vonatkozik. Amikor egy részkifejezés logikai értéket ad eredményül, a nyomkövető 1-ként vagy 0-ként írja vissza, nem pedig TRUE vagy FALSE formában. Ennek oka az, hogy a csökkentett literálnak tisztán újra kell elemződnie bármilyen kontextusban, ami körülveszi, és az aritmetika a legigényesebb eset. Ha egy olyan összehasonlítás, mint az A1>A2 a TRUE szövegre csökkenne, és ez a szöveg a TRUE*B1-ben landolna, az újraszámítás attól függne, hogy a motor elfogad-e egy puszta logikai kulcsszót a szorzásban. Az 1 behelyettesítése teljesen kikerüli a kérdést, mert az 1*B1 egyértelmű bármilyen aritmetikai pozícióban. Emellett megegyezik az Excel saját kényszerítésével, ahol a TRUE 1-ként, a FALSE pedig 0-ként viselkedik abban a pillanatban, amikor a rendszer számot vár
A függvényhívások atomikusan csökkennek
Egy naiv lépésmotor először a függvény argumentumait csökkentené, majd a hívást. Ez az Excel esetében helytelen, és a nyomkövető szándékosan nem is teszi meg. Egy függvényhívást egészként értékel ki, az eredeti szövegéből, egyetlen lépésben. Ennek oka a rövidzárlatos szemantika. Az IF, a CHOOSE és az IFERROR csak a kiválasztott ágat értékeli ki, és az argumentumok előzetes csökkentése arra kényszerítené a motort, hogy olyan ágakat számítson ki, amelyeket az Excel soha nem érint. A klasszikus áldozat egy nullával való osztás elleni védelem, mint például az IF(B1=0,0,A1/B1): ha a nyomkövető az A1/B1-et az IF kiértékelése előtt csökkentené, a védelem visszafelé sülne el, és pont azt a hibát váltaná ki, amelynek a megelőzésére szolgál. Az egész hívás atomikus kiértékelésével a nyomkövető megőrzi azt a lusta kiértékelést (lazy evaluation), amely működőképessé teszi ezeket a védelmeket
// 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.A kompromisszum az, hogy a függvényhíváson belül nem látjuk a különálló lépéseket, de ez a helyes viselkedés. Olyan argumentumcsökkentések bemutatása, amelyeket az Excel soha nem hajt végre, félrevezetőbb nyomkövetést jelentene, mintha a hívást egyetlen kiértékelési egységként kezelnénk, ahogy az valójában is van
Argumentumelválasztók és érintetlen tartományok
Még két normalizáció tartja őszintén az újraszámítást. A számítási motor fordítója ;-t vár függvény argumentumelválasztóként, ezért amikor a nyomkövető újraépíti a függvényhívást az elemzett fájából, az argumentumokat ;-vel köti össze, még akkor is, ha a felhasználó eredetileg ,-t gépelt. Egy SUM(A1,A2,A3) formában írt képlet SUM(A1;A2;A3)-ként lesz újraszámítva, amit a motor elfogad. Az értékek behelyettesítése teszi szükségessé ezt az újraépítést, és a megfelelő elválasztó biztosítja az újraépítés sikeres elemzését
A tartományhivatkozások a másik eset. Egy olyan tartomány, mint az A1:A3, nem skaláris, és nem szabad három külön értékre osztani, mert a fogyasztó függvény tartományargumentumot vár. A nyomkövető egy tartományt az eredeti szövegében, érintetlenül tart, és engedi, hogy a befogadó függvény egészként csökkenjen. A SUM(A1:A3)*B1 esetében a tartomány egész marad, a SUM(A1:A3) egyetlen számra csökken egy atómi lépésben, és csak ezután fut le a külső szorzás. Ez ugyanaz a határ, amelyet az Excel húz meg a tartományoperandus és az általa végül hozzáadott skalár között
// 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));Összegezve, ezek a szabályok a lépéslistát az Excel Képlet kiértékelése parancsának hű tükrévé teszik, nem pedig annak megközelítésévé. A csökkentések abban a sorrendben történnek, ahogy az Excel végrehajtja őket, a helyettesített literálok túlélnek minden területi beállítást, a logikai értékek úgy kényszerülnek, ahogyan az Excel kényszeríti őket, és a lusta függvények lusták maradnak. Ha saját függvényeivel szeretné tovább fokozni a motor képességeit, a képletmotorról és egyéni függvényekről szóló cikk bemutatja regisztrálásukat, a nehezebb numerikus feladatokhoz pedig a statisztikai eloszlásfüggvények Delphiben cikk foglalkozik azzal a beépített könyvtárral, amely ellen a nyomkövető kiértékel. Mindez a Delphi és a C++Builder rendszerekhez készült HotXLS táblázatkezelő komponens részeként érkezik, a blogon máshol tárgyalt olvasási, írásási, formázási és számítási API-k mellett