Стъпково проследяване на Excel формули в Delphi

Excel крие малък дебъгер пред очите на всички. Изберете клетка, отворете Formulas и щракнете върху Evaluate Formula, и диалогов прозорец ще покаже формулата с един подчертан подизраз. Натиснете Evaluate и този подизраз се свива до своята стойност, след това следващият се подчертава и вие наблюдавате как дълъг израз се смалява до едно число редукция след редукция. Това е най-бързият начин да намерите кой клон на вложено IF реално се е задействал или коя препратка е подала грешна обща сума. HotXLS възпроизвежда точно това поведение чрез TXLSFormulaTracer, така че програма на Delphi или C++Builder може да визуализира същия списък от стъпки за одит на работна книга, отстраняване на грешки в генерирана формула или обясняване на някого защо даден резултат се е получил по този начин. Всяка записана стъпка носи текста на подизраза и стойността, до която той се редуцира

Как машината за редукция обхожда израза

Проследителят не бърка в изчислителната машина. Той токенизира формулата и я анализира с парсер с рекурсивно спускане, след което редуцира дървото в дълбочина, започвайки от най-вътрешния изчислим подизраз. Когато даден възел се редуцира до стойност, тази стойност се замества обратно в обкръжаващия израз като литерал, а машината иска от истинския калкулатор да преизчисли вече по-простия израз. Тъй като всяка стъпка се изчислява чрез публичния метод Calculate на работния лист вместо чрез частен пряк път, всяка стъпка съвпада точно с това, което би произвело пълното преизчисляване на клетката. Парсерът умишлено не е инвазивен, което му позволява да работи с всеки работен лист, без да нарушава състоянието му

Парсерът следва стълбица на приоритет на операторите, с по едно рекурсивно ниво за всяка лента на приоритет. От най-слабо към най-силно свързване лентите са: ниво 0 сравнение (=, <>, <, >, <=, >=), ниво 1 конкатенация на низове (&), ниво 2 събиране и изваждане, ниво 3 умножение и деление, ниво 4 степенуване и накрая унарни плюс и минус под тях. Всяко ниво анализира нивото над него за своите операнди, така че по-висока лента се свързва по-тясно. Това е същият приоритет, който Excel прилага, поради което A1*B1+A2*B1 редуцира двете произведения преди сумата: умножението стои на ниво 3, събирането на ниво 2, така че умноженията са по-дълбоко в дървото и се редуцират първи

Проследяване на формула и обхождане на стъпките

Употребата отразява включеното демо в Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Създайте работен лист (или отворете съществуваща работна книга), конструирайте проследител върху листа, извикайте Trace и обходете върнатия масив. Всяка стъпка TXLSFormulaStep предоставя Depth за отстъп, Source за оригиналния подизраз, Expression за този подизраз с вече заместени операнди и Value за резултата от стъпката

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;

Препратките към клетки се разрешават първи и се появяват като собствени стъпки, след това произведенията се редуцират, след това данъчният фактор в скобите и накрая финалното умножение приключва процеса. Полето Depth ви позволява да правите отстъп, така че най-вътрешните редукции видимо да стоят най-дълбоко, точно както Excel подчертава най-вътрешния член преди всеки външен

Капанът на литералите без локал

Най-опасният детайл в цялата тази схема е невидим на английска машина и се счупва шумно на немска такава. Когато изчислено число се замести обратно в текста на формулата, то трябва да бъде записано като низ и след това преанализирано от изчислителната машина, която третира . като десетичен знак. Ако заместването използваше системния локал, немски TFormatSettings би записал 1,08 за данъчния фактор, запетаята би била прочетена като разделител на аргументи и преизчисляването на A1*B1*1,08 или би се анализирало в грешна форма, или би се провалило напълно

Проследителят избягва това, като форматира всеки числов литерал чрез частен TFormatSettings, който фиксира при конструирането, като DecimalSeparator е принудително зададен на . и ThousandSeparator е зададен на #0, така че никога не се извежда знак за групиране. Тогава FloatToStr произвежда литерал, който машината винаги може да прочете обратно, независимо от регионалните настройки на оператора

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

Това е видът бъг, който никога не се появява при собственото тестване на автора и изплува само когато клиент в друг локал изпълни същия код, така че си струва да се заяви ясно: двупосочното преминаване на стойност през текст на формула е проблем на сериализация, а сериализацията трябва да бъде независима от локала

Булевите стойности се редуцират до 1 и 0

Свързано решение за заместване се отнася до логическите стойности. Когато подизраз се оцени като булева стойност, проследителят го записва обратно като 1 или 0, а не като TRUE или FALSE. Причината е, че редуцираният литерал трябва да се преанализира чисто в какъвто и контекст да го заобикаля, а аритметиката е взискателният случай. Ако сравнение като A1>A2 се редуцира до текста TRUE и този текст попадне вътре в TRUE*B1, преизчисляването би зависело от това машината да приеме гола булева ключова дума в умножение. Заместването на 1 заобикаля въпроса напълно, защото 1*B1 е недвусмислено във всяка аритметична позиция. Това също съвпада със собственото принудително преобразуване на Excel, където TRUE се държи като 1, а FALSE като 0 в момента, в който се очаква число

Извикванията на функции се редуцират атомарно

Наивна машина за стъпки би редуцирала първо аргументите на дадена функция, а след това извикването. Това е грешно за Excel и проследителят умишлено не го прави. Извикването на функция се изчислява като цяло, от оригиналния му текст, в една единствена стъпка. Причината е семантиката на късо съединение. IF, CHOOSE и IFERROR изчисляват само клона, който избират, и редуцирането на аргументите първо би принудило машината да изчисли клонове, до които Excel никога не се докосва. Класическата жертва е защита от деление на нула като IF(B1=0,0,A1/B1): ако проследителят редуцираше A1/B1 преди да изчисли IF, защитата би се провалила и би предизвикала точно грешката, която съществува, за да предотврати. Като изчислява цялото извикване атомарно, проследителят запазва мързеливото изчисляване, което кара такива защити да работят

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

Компромисът е, че не виждате вътрешността на извикването на функцията като отделни стъпки, но това е правилното поведение. Показването на редукции на аргументи, които Excel никога не извършва, би било по-подвеждащо проследяване от третирането на извикването като една единствена изчислителна единица, каквото то всъщност е

Разделители на аргументи и непокътнати диапазони

Още две нормализации поддържат преизчисляването честно. Компилаторът на изчислителната машина очаква ; като разделител на аргументи за функции, така че когато проследителят възстановява извикване на функция от своето анализирано дърво, той свързва аргументите с ;, дори ако потребителят първоначално е въвел ,. Формула, написана като SUM(A1,A2,A3), се преизчислява като SUM(A1;A2;A3), което машината приема. Заместването на стойности е това, което прави това възстановяване необходимо, а правилното задаване на разделителя е това, което позволява възстановеният код да се анализира успешно

Препратките към диапазони са другият случай. Диапазон като A1:A3 не е скалар и не трябва да се разделя на три отделни стойности, защото функцията, която го консумира, очаква аргумент диапазон. Проследителят запазва диапазона непокътнат като оригиналния му текст и позволява обграждащата го функция да се редуцира като цяло. В SUM(A1:A3)*B1 диапазонът остава цял, SUM(A1:A3) се редуцира до едно число в една атомарна стъпка и едва тогава се изпълнява външното умножение. Това е същата граница, която Excel чертае между операнд диапазон и скалара, който той в крайна сметка допринася

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

Взети заедно, тези правила правят списъка със стъпки вярно огледало на командата Evaluate Formula на Excel, а не нейно приближение. Редукциите се случват в реда, в който Excel ги изпълнява, заместените литерали оцеляват във всеки локал, булевите стойности се преобразуват по начина, по който Excel ги преобразува, а мързеливите функции остават мързеливи. Ако искате да разширите машината допълнително със свои собствени функции, статията за машината за формули и персонализирани функции показва как да ги регистрирате, а за по-тежка числена работа статията за статистически функции за разпределение в Delphi покрива вградената библиотека, срещу която проследителят изчислява. Всичко това се доставя като част от електронната таблица HotXLS Component за Delphi и C++Builder, наред с API-тата за четене, записване, форматиране и изчисление, разгледани на други места в този блог