Excel verbergt een kleine debugger in het volle zicht. Selecteer een cel, open Formules en klik op Formule Evalueren, en een dialoogvenster toont de formule met één subexpressie onderstreept. Druk op Evalueren en die subexpressie stort in tot zijn waarde, waarna de volgende wordt onderstreept, en u ziet een lange expressie krimpen tot een enkel getal, reductie per reductie. Het is de snelste manier om te vinden welke tak van een geneste IF daadwerkelijk is uitgevoerd, of welke referentie een verkeerd totaal heeft gevoed. HotXLS reproduceert exact dit gedrag via TXLSFormulaTracer, zodat een Delphi- of C++Builder-programma dezelfde stappenlijst kan renderen voor het controleren van een werkmap, het debuggen van een gegenereerde formule, of om iemand te leren waarom een resultaat zo is uitgevallen als het is uitgevallen. Elke geregistreerde stap bevat de subexpressietekst en de waarde waartoe deze reduceert
Hoe de reductie-engine door de expressie loopt
De tracer grijpt niet in op de berekenings-engine. Hij tokeniseert de formule en ontleedt deze met een recursieve-daling-parser (recursive-descent), en reduceert de boom vervolgens diepte-eerst (depth-first), te beginnen met de binnenste evalueerbare subexpressie. Wanneer een knoop reduceert tot een waarde, wordt die waarde als letterlijke waarde teruggesubstitueerd in de omringende expressie, en vraagt de engine aan de echte rekenmachine om de nu eenvoudigere expressie opnieuw te berekenen. Omdat elke stap wordt geëvalueerd via de publieke Calculate-methode van het werkblad en niet via een privé-snelkoppeling, komt elke stap exact overeen met wat een volledige herberekening van de cel zou opleveren. De parser is qua ontwerp niet-invasief, wat hem in staat stelt om tegen elk werkblad te draaien zonder de staat ervan te verstoren
De parser volgt een operator-voorrangsladder, met één recursief niveau per voorrangsband. Van de laagste binding naar de hoogste zijn de banden: niveau 0 vergelijking (=, <>, <, >, <=, >=), niveau 1 tekenreekssamenvoeging (&), niveau 2 optellen en aftrekken, niveau 3 vermenigvuldigen en delen, niveau 4 machtsverheffen, en ten slotte unaire plus en min daaronder. Elk niveau parseert het niveau erboven voor zijn operanden, zodat een hogere band strakker bindt. Dit is dezelfde voorrang die Excel toepast, wat verklaart waarom A1*B1+A2*B1 de twee producten vóór de som reduceert: vermenigvuldiging zit op niveau 3, optelling op niveau 2, dus de vermenigvuldigingen zitten dieper in de boom en reduceren als eerste
Een formule traceren en door de stappen lopen
Het gebruik weerspiegelt de meegeleverde demo in Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Bouw een werkblad (of open een bestaande werkmap), construeer een tracer over het blad, roep Trace aan, en itereer over de geretourneerde array. Elke TXLSFormulaStep stelt Depth beschikbaar voor inspringing, Source voor de originele subexpressie, Expression voor die subexpressie met de operanden al gesubstitueerd, en Value voor het resultaat van de stap
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;De celreferenties worden als eerste opgelost en verschijnen als hun eigen stappen, vervolgens reduceren de producten, dan de belastingfactor tussen haakjes, en de laatste vermenigvuldiging sluit het af. Het Depth-veld laat u inspringen zodat de binnenste reducties zichtbaar het diepst zitten, precies zoals Excel de binnenste term onderstreept voordat er een buitenste aan de beurt is
De locale-vrije letterlijke waarde-valstrik
Het gevaarlijkste detail in dit hele schema is onzichtbaar op een Engelse machine en breekt met veel lawaai op een Duitse. Wanneer een berekend getal wordt teruggesubstitueerd in de formule-tekst, moet het worden geschreven als een string en vervolgens opnieuw worden geparseerd door de berekenings-engine, die . als decimaalteken behandelt. Als de substitutie de systeem-locale zou gebruiken, zou een Duitse TFormatSettings 1,08 schrijven voor de belastingfactor, de komma zou worden gelezen als een argumentscheidingsteken, en de herberekening van A1*B1*1,08 zou ofwel in de verkeerde vorm worden geparseerd of helemaal mislukken
De tracer voorkomt dit door elke numerieke letterlijke waarde te formatteren via een private TFormatSettings die hij bij constructie vastpint, waarbij DecimalSeparator wordt geforceerd naar . en ThousandSeparator wordt ingesteld op #0 zodat er nooit een groeperingsteken wordt uitgevoerd. FloatToStr produceert dan een letterlijke waarde die de engine altijd kan teruglezen, ongeacht de regionale instellingen van de operator
// 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)Dit is het soort bug dat nooit verschijnt in de eigen tests van de auteur en pas opduikt wanneer een klant in een andere locale dezelfde code uitvoert, dus het is de moeite waard om het duidelijk te stellen: het heen en weer halen (round-tripping) van een waarde door formulet-tekst is een serialisatieprobleem, en serialisatie moet locale-vrij zijn
Booleans reduceren tot 1 en 0
Een gerelateerde substitutiebeslissing betreft logische waarden. Wanneer een subexpressie evalueert tot een boolean, schrijft de tracer deze terug als 1 of 0, niet als TRUE of FALSE. De reden is dat de gereduceerde letterlijke waarde netjes opnieuw moet parseren in welke context er ook omheen staat, en rekenkunde is de veeleisende casus. Als een vergelijking zoals A1>A2 zou reduceren tot de tekst TRUE en die tekst zou binnen TRUE*B1 belanden, dan zou de herberekening afhangen van het feit of de engine een puur boolean sleutelwoord in een vermenigvuldiging accepteert. Het substitueren van 1 omzeilt de vraag volledig, omdat 1*B1 ondubbelzinnig is in elke rekenkundige positie. Het komt ook overeen met Excel's eigen dwang (coercion), waarbij TRUE zich gedraagt als 1 en FALSE als 0 zodra er een getal wordt verwacht
Functie-aanroepen reduceren atomair
Een naïeve stap-engine zou eerst de argumenten van een functie reduceren en dan pas de aanroep. Dat is fout voor Excel, en de tracer doet het bewust niet. Een functie-aanroep wordt in zijn geheel, vanaf zijn oorspronkelijke tekst, in één stap geëvalueerd. De reden is short-circuit semantiek. IF, CHOOSE en IFERROR evalueren alleen de tak die ze selecteren, en het eerst reduceren van argumenten zou de engine dwingen om takken te berekenen die Excel nooit aanraakt. Het klassieke slachtoffer is een deling-door-nul-beveiliging zoals IF(B1=0,0,A1/B1): als de tracer A1/B1 zou reduceren vóór het evalueren van de IF, zou de beveiliging averechts werken en de fout opwerpen die hij juist moet voorkomen. Door de hele aanroep atomair te evalueren, behoudt de tracer de luie evaluatie (lazy evaluation) die zulke beveiligingen laat werken
// 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.De afweging is dat u niet in de functie-aanroep kunt kijken als afzonderlijke stappen, maar dat is het correcte gedrag. Het tonen van argumentreducties die Excel nooit uitvoert, zou een meer misleidende trace zijn dan het behandelen van de aanroep als de enkele evaluatie-eenheid die het werkelijk is
Argumentscheidingstekens en intacte bereiken
Nog twee normalisaties houden de herberekening eerlijk. De compiler van de berekenings-engine verwacht ; als argumentscheidingsteken van de functie, dus wanneer de tracer een functie-aanroep opbouwt uit zijn geparseerde boom, voegt hij argumenten samen met ;, zelfs als de gebruiker oorspronkelijk , heeft getypt. Een formule die is geschreven als SUM(A1,A2,A3) wordt herberekend als SUM(A1;A2;A3), wat de engine accepteert. De substitutie van waarden is wat deze heropbouw noodzakelijk maakt, en het correct krijgen van het scheidingsteken is wat de heropbouw laat parseren
Bereikreferenties (range references) zijn het andere geval. Een bereik zoals A1:A3 is geen scalair en mag niet worden opgesplitst in drie afzonderlijke waarden, omdat de functie die het consumeert een bereik-argument verwacht. De tracer houdt een bereik intact als zijn oorspronkelijke tekst en laat de omhullende functie in zijn geheel reduceren. In SUM(A1:A3)*B1 blijft het bereik heel, SUM(A1:A3) reduceert tot één getal in één atomaire stap, en pas daarna wordt de buitenste vermenigvuldiging uitgevoerd. Dit is dezelfde grens die Excel trekt tussen een bereik-operand en de scalair die deze uiteindelijk bijdraagt
// 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));Alles bij elkaar zorgen deze regels ervoor dat de stappenlijst een getrouwe weerspiegeling is van de Formule Evalueren-opdracht van Excel in plaats van een benadering ervan. De reducties vinden plaats in de volgorde waarin Excel ze uitvoert, de gesubstitueerde letterlijke waarden overleven elke locale, booleans worden gedwongen op de manier waarop Excel ze dwingt, en luie functies blijven lui. Als u de engine verder wilt pushen met uw eigen functies, laat het artikel formule-engine en aangepaste functies zien hoe u ze kunt registreren, en voor zwaarder numeriek werk behandelt het artikel statistische verdelingsfuncties in Delphi de ingebouwde bibliotheek waartegen de tracer evalueert. Dit alles wordt meegeleverd als onderdeel van de HotXLS spreadsheet-component voor Delphi en C++Builder, naast de API's voor lezen, schrijven, formatteren en berekenen die elders op deze blog worden behandeld