Excel приховує невеликий зневаджувач на видноті. Виберіть клітинку, відкрийте вкладку «Формули» та натисніть «Обчислити формулу», і у діалоговому вікні відобразиться формула з одним підкресленим підвиразом. Натисніть «Обчислити», і цей підвираз згорнеться до свого значення, потім підкреслиться наступний, і ви побачите, як довгий вираз скорочується до єдиного числа крок за кроком. Це найшвидший спосіб дізнатися, яка саме гілка вкладеного 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));Разом ці правила роблять список кроків вірним відображенням команди «Обчислити формулу» в Excel, а не її наближенням. Згортання відбуваються в тому порядку, в якому їх виконує Excel, підставлені літерали витримують будь-яку локаль, логічні значення приводяться так, як їх приводить Excel, а ледачі функції залишаються ледачими. Якщо ви хочете розширити рушій власними функціями, у статті про рушій формул та власні функції показано, як їх зареєструвати, а для більш важкої числової роботи стаття про функції статистичного розподілу в Delphi охоплює вбудовану бібліотеку, проти якої обчислює трасувальник. Все це постачається як частина компонента електронних таблиць HotXLS для Delphi та C++Builder, поряд з API для читання, запису, форматування та обчислень, які висвітлюються в інших статтях цього блогу