Excel döljer en liten felsökare i fullt synfält. Välj en cell, öppna Formler och klicka på Utvärdera formel, så visar en dialogruta formeln med ett deluttryck understruket. Tryck på Utvärdera och det deluttrycket kollapsar till sitt värde, sedan understryks nästa, och du ser ett långt uttryck krympa till ett enda nummer en reduktion i taget. Det är det snabbaste sättet att hitta vilken gren av en nästlad IF som faktiskt utlöstes, eller vilken referens som matade in en felaktig totalsumma. HotXLS reproducerar det exakta beteendet genom TXLSFormulaTracer, så att ett Delphi- eller C++Builder-program kan rendera samma steglista för att granska en arbetsbok, felsöka en genererad formel eller lära någon varför ett resultat blev som det blev. Varje registrerat steg bär på deluttryckets text och det värde det reduceras till
Hur reduktionsmotorn går igenom uttrycket
Spåraren sträcker sig inte in i beräkningsmotorn. Den delar upp formeln i tokens och tolkar den med en rekursiv nedstigningsparser, sedan reducerar den trädet djupet-först, med det innersta utvärderingsbara deluttrycket först. När en nod reduceras till ett värde, ersätts det värdet tillbaka in i det omgivande uttrycket som en bokstav, och motorn ber den riktiga kalkylatorn att beräkna om det nu enklare uttrycket. Eftersom varje steg utvärderas genom kalkylbladets publika Calculate-metod snarare än en privat genväg, stämmer varje steg exakt överens med vad en fullständig omberäkning av cellen skulle producera. Parsern är icke-invasiv avsiktligt, vilket är vad som låter den köras mot vilket kalkylblad som helst utan att störa dess tillstånd
Parsern följer en operatorprioritetsstege, med en rekursiv nivå per prioritetsband. Från lägsta bindning till högsta är banden: nivå 0 jämförelse (=, <>, <, >, <=, >=), nivå 1 strängsammanfogning (&), nivå 2 addition och subtraktion, nivå 3 multiplikation och division, nivå 4 exponentiering, och slutligen unärt plus och minus därunder. Varje nivå tolkar nivån ovanför för sina operander, så ett högre band binder hårdare. Detta är samma prioritet som Excel tillämpar, vilket är anledningen till att A1*B1+A2*B1 reducerar de två produkterna före summan: multiplikation sitter på nivå 3, addition på nivå 2, så multiplikationerna ligger djupare i trädet och reduceras först
Att spåra en formel och gå igenom stegen
Användningen speglar den medföljande demon på Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Bygg ett kalkylblad (eller öppna en befintlig arbetsbok), konstruera en spårare över bladet, anropa Trace och iterera den returnerade arrayen. Varje TXLSFormulaStep exponerar Depth för indrag, Source för det ursprungliga deluttrycket, Expression för det deluttrycket med dess operander redan ersatta, och Value för resultatet av steget
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;Cellreferenserna löses upp först och visas som sina egna steg, sedan reduceras produkterna, därefter den parentesiserade skattefaktorn, och den sista multiplikationen stänger det hela. Fältet Depth låter dig göra indrag så att de innersta reduktionerna synbart sitter djupast, exakt som Excel understryker den innersta termen före någon yttre
Fällan med den lokal-fria litteralen
Den farligaste detaljen i hela detta system är osynlig på en engelsk maskin och går sönder högljutt på en svensk. När ett beräknat tal ersätts tillbaka in i formeltexten, måste det skrivas som en sträng och sedan tolkas om av beräkningsmotorn, som behandlar . som decimalkomma. Om ersättningen använde systemets nationella inställningar, skulle en svensk TFormatSettings skriva 1,08 för skattefaktorn, kommatecknet skulle läsas som en argumentavskiljare, och omberäkningen av A1*B1*1,08 skulle antingen tolkas till fel form eller misslyckas helt
Spåraren undviker detta genom att formatera varje numerisk litteral via en privat TFormatSettings som den fäster vid konstruktionen, med DecimalSeparator framtvingad till . och ThousandSeparator satt till #0 så att inget grupperingsuttryck någonsin matas ut. FloatToStr producerar sedan en litteral som motorn alltid kan läsa tillbaka, oavsett operatörens regionala inställningar
// 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)Detta är den typ av bugg som aldrig uppträder i författarens egna tester och som bara dyker upp när en kund i en annan region kör samma kod, så det är värt att säga rent ut: att göra en rundtur med ett värde genom formeltext är ett serialiseringsproblem, och serialisering måste vara oberoende av regionala inställningar
Booleska värden reduceras till 1 och 0
Ett relaterat ersättningsbeslut rör logiska värden. När ett deluttryck utvärderas till ett booleskt värde, skriver spåraren tillbaka det som 1 eller 0, inte som TRUE eller FALSE. Anledningen är att den reducerade litteralen måste tolkas rent i vilket sammanhang som än omger den, och aritmetik är det krävande fallet. Om en jämförelse som A1>A2 reducerades till texten TRUE och den texten hamnade inuti TRUE*B1, skulle omberäkningen vara beroende av att motorn accepterar ett rent booleskt nyckelord i en multiplikation. Att ersätta med 1 kringgår frågan helt och hållet, eftersom 1*B1 är otvetydigt i alla aritmetiska positioner. Det matchar också Excels eget tvång, där TRUE beter sig som 1 och FALSE som 0 i samma ögonblick som ett nummer förväntas
Funktionsanrop reduceras atomärt
En naiv stegmotor skulle reducera en funktions argument först och därefter anropet. Det är fel för Excel, och spåraren gör det medvetet inte. Ett funktionsanrop utvärderas som en helhet, från sin ursprungliga text, i ett enda steg. Anledningen är kortslutningssemantik. IF, CHOOSE och IFERROR utvärderar endast den gren de väljer, och att reducera argument först skulle tvinga motorn att beräkna grenar som Excel aldrig rör. Det klassiska offret är ett skydd mot division med noll, såsom IF(B1=0,0,A1/B1): om spåraren reducerade A1/B1 innan IF utvärderades, skulle skyddet klicka och utlösa exakt det fel som det existerar för att förhindra. Genom att utvärdera hela anropet atomärt, bevarar spåraren den lata utvärderingen som får sådana skydd att fungera
// 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.Kompromissen är att du inte ser inuti funktionsanropet som separata steg, men det är det korrekta beteendet. Att visa argumentreduktioner som Excel aldrig utför skulle vara en mer vilseledande spårning än att behandla anropet som den enda utvärderingsenhet det verkligen är
Argumentavskiljare och intakta intervall
Ytterligare två normaliseringar håller omberäkningen ärlig. Beräkningsmotorns kompilator förväntar sig ; som funktionsargumentavskiljare, så när spåraren bygger om ett funktionsanrop från sitt tolkade träd sammanfogar den argument med ;, även om användaren ursprungligen skrev in ,. En formel skriven som SUM(A1,A2,A3) beräknas om som SUM(A1;A2;A3), vilket motorn accepterar. Ersättningen av värden är det som gör denna ombyggnad nödvändig, och att få separatorn rätt är vad som får ombyggnaden att tolkas
Intervallreferenser är det andra fallet. Ett intervall såsom A1:A3 är inte en skalär och får inte delas upp i tre separata värden, eftersom funktionen som konsumerar det förväntar sig ett intervallargument. Spåraren håller ett intervall intakt som sin ursprungliga text och låter den omslutande funktionen reduceras som helhet. I SUM(A1:A3)*B1 förblir intervallet helt, SUM(A1:A3) reduceras till ett tal i ett enda atomärt steg, och först då körs den yttre multiplikationen. Detta är samma gräns som Excel drar mellan en intervalloperand och den skalär den eventuellt bidrar med
// 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));Sammantaget gör dessa regler steglistan till en trogen spegel av Excels Utvärdera formel-kommando i stället för en approximation av det. Reduktionerna sker i den ordning Excel utför dem, de utbytta litteralerna överlever varje region, booleska värden framtvingas på det sätt Excel framtvingar dem, och lata funktioner förblir lata. Om du vill pressa motorn ytterligare med dina egna funktioner, visar artikeln om formelmotorn och anpassade funktioner hur man registrerar dem, och för tyngre numeriskt arbete täcker artikeln om statistiska distributionsfunktioner i Delphi det inbyggda bibliotek som spåraren utvärderar mot. Allt detta levereras som en del av kalkylbladskomponenten HotXLS för Delphi och C++Builder, vid sidan av de API:er för läsning, skrivning, formatering och beräkning som täcks på andra ställen på den här bloggen