ردیابی مرحله‌به‌مرحله ارزیابی فرمول اکسل در دلفی (Delphi)

اکسل (Excel) یک اشکال‌زدای کوچک را در پیش روی شما پنهان کرده است. یک سلول را انتخاب کنید، بخش Formulas را باز کرده و روی Evaluate Formula کلیک کنید، دیالوگی ظاهر می‌شود که فرمول را با یک زیرعبارت (subexpression) خط‌کشیده‌شده نشان می‌دهد. با فشردن Evaluate، آن زیرعبارت به مقدار خود کاهش می‌یابد، سپس زیرعبارت بعدی خط‌کشیده می‌شود و شما می‌بینید که یک عبارت طولانی، مرحله به مرحله، به یک عدد واحد تقلیل می‌یابد. این سریع‌ترین راه برای یافتن این است که کدام شاخه از یک IF تودرتو واقعاً اجرا شده است، یا کدام ارجاع باعث ایجاد یک مجموع اشتباه شده است. HotXLS دقیقاً همین رفتار را از طریق TXLSFormulaTracer بازتولید می‌کند، بنابراین یک برنامه Delphi یا C++Builder می‌تواند همان لیست مراحل را برای ممیزی یک کتاب‌کار (workbook)، اشکال‌زدایی یک فرمول تولیدشده، یا آموزش اینکه چرا یک نتیجه به این شکل درآمده است، رندر کند. هر مرحله ثبت‌شده شامل متن زیرعبارت و مقداری است که به آن کاهش می‌یابد

موتور کاهش چگونه عبارت را پیمایش می‌کند

ردیاب (tracer) مستقیماً به موتور محاسبه (calculation engine) وارد نمی‌شود. این ردیاب فرمول را توکنیزه (tokenize) کرده و آن را با یک پارسر بازگشتی-نزولی (recursive-descent parser) تجزیه می‌کند، سپس درخت را به روش جستجوی عمق-اول (depth-first)، ابتدا برای داخلی‌ترین زیرعبارت قابل ارزیابی، کاهش می‌دهد. زمانی که یک گره به یک مقدار کاهش می‌یابد، آن مقدار به عنوان یک لیترال (literal) به درون عبارت احاطه‌کننده خود جایگزین می‌شود، و موتور از محاسبه‌گر اصلی درخواست می‌کند تا عبارت اکنون ساده‌تر شده را دوباره محاسبه کند. از آنجایی که هر مرحله به جای یک میانبر خصوصی، از طریق متد عمومی Calculate در کاربرگ (worksheet) ارزیابی می‌شود، هر مرحله دقیقاً با آنچه یک محاسبه مجدد کامل سلول تولید می‌کند مطابقت دارد. طراحی این پارسر به‌گونه‌ای است که تهاجمی نیست (non-invasive)، که این امر به آن اجازه می‌دهد تا روی هر کاربرگی بدون ایجاد اختلال در وضعیت آن اجرا شود

پارسر از یک نردبان تقدم عملگرها (operator-precedence) پیروی می‌کند، با یک سطح بازگشتی برای هر باند تقدم. از کمترین پیوند (binding) تا بیشترین، این باندها عبارتند از: سطح 0 مقایسه (=، <>، <، >، <=، >=)، سطح 1 الحاق رشته (&)، سطح 2 جمع و تفریق، سطح 3 ضرب و تقسیم، سطح 4 توان، و در نهایت علامت‌های مثبت و منفی یگانی (unary plus and minus) در زیر آن. هر سطح، سطح بالاتر از خود را برای عملوندهایش تجزیه می‌کند، بنابراین یک باند بالاتر پیوند محکم‌تری دارد. این دقیقاً همان تقدمی است که اکسل اعمال می‌کند، به همین دلیل است که A1*B1+A2*B1 دو حاصلضرب را پیش از مجموع کاهش می‌دهد: ضرب در سطح 3 قرار دارد و جمع در سطح 2، بنابراین ضرب‌ها در درخت عمیق‌تر هستند و زودتر کاهش می‌یابند

ردیابی یک فرمول و پیمایش مراحل

نحوه استفاده مشابه دموی ارائه‌شده در مسیر Demo/Delphi/FormulaTrace/FormulaTrace.dpr است. یک کاربرگ بسازید (یا یک کتاب‌کار موجود را باز کنید)، یک ردیاب روی آن شیت (sheet) ایجاد کنید، متد Trace را فراخوانی کرده و روی آرایه بازگشتی تکرار (iterate) کنید. هر TXLSFormulaStep ویژگی‌های زیر را ارائه می‌دهد: Depth برای تورفتگی (indentation)، 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;

ابتدا ارجاعات سلول (cell references) حل (resolve) می‌شوند و به عنوان مراحل مجزا ظاهر می‌گردند، سپس حاصلضرب‌ها کاهش می‌یابند، بعد از آن ضریب مالیات داخل پرانتز محاسبه می‌شود، و ضرب نهایی کار را تمام می‌کند. فیلد Depth به شما اجازه می‌دهد تورفتگی ایجاد کنید تا کاهش‌های داخلی‌تر به‌طور بصری در عمیق‌ترین قسمت قرار گیرند، دقیقاً همان‌طور که اکسل پیش از هر عبارت بیرونی، زیر داخلی‌ترین عبارت خط می‌کشد

تله لیترال مستقل از منطقه جغرافیایی (locale-free literal)

خطرناک‌ترین جزئیات در کل این طرح روی یک ماشین با تنظیمات انگلیسی نامرئی است اما روی یک ماشین آلمانی با صدای بلند از کار می‌افتد. زمانی که یک عدد محاسبه‌شده مجدداً درون متن فرمول جایگزین می‌شود، باید به عنوان یک رشته (string) نوشته شده و سپس توسط موتور محاسبه دوباره تجزیه (re-parsed) شود، که در این حالت . به عنوان نقطه اعشار (decimal point) در نظر گرفته می‌شود. اگر این جایگزینی از منطقه جغرافیایی سیستم (system locale) استفاده کند، TFormatSettings آلمانی برای ضریب مالیات 1,08 را می‌نویسد، این کاما به عنوان جداکننده آرگومان خوانده می‌شود، و محاسبه مجدد A1*B1*1,08 یا به شکلی اشتباه تجزیه می‌شود یا کاملاً با شکست مواجه می‌گردد

ردیاب از این مشکل با قالب‌بندی (formatting) هر لیترال عددی از طریق یک TFormatSettings خصوصی که هنگام ساخت (construction) تثبیت می‌کند، جلوگیری می‌نماید، که در آن DecimalSeparator به اجبار روی . تنظیم می‌شود و ThousandSeparator روی #0 تنظیم می‌گردد تا هیچ کاراکتر گروه‌بندی هرگز صادر نشود. سپس FloatToStr لیترالی تولید می‌کند که موتور همیشه می‌تواند فارغ از تنظیمات منطقه‌ای اپراتور (regional settings) آن را دوباره بخواند

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

این از آن دست باگ‌هایی است که هرگز در آزمایش‌های خود برنامه‌نویس ظاهر نمی‌شود و تنها زمانی خود را نشان می‌دهد که مشتری در منطقه جغرافیایی دیگری همان کد را اجرا کند، بنابراین بیان صریح آن ارزشمند است: بازگرداندن (round-tripping) یک مقدار از طریق متن فرمول یک مشکل سریال‌سازی (serialization) است، و سریال‌سازی باید کاملاً مستقل از منطقه جغرافیایی (locale-free) باشد

عبارات بولی به 1 و 0 کاهش می‌یابند

یک تصمیم جایگزینی مرتبط دیگر در مورد مقادیر منطقی (logical values) است. هنگامی که یک زیرعبارت به یک بولین (boolean) ارزیابی می‌شود، ردیاب آن را به صورت 1 یا 0 می‌نویسد، نه به صورت TRUE یا FALSE. دلیل این امر آن است که لیترال کاهش‌یافته باید بتواند در هر متنی که آن را احاطه کرده است به طور تمیز دوباره تجزیه شود، و در این بین حساب (arithmetic) چالش‌برانگیزترین مورد است. اگر مقایسه‌ای مانند A1>A2 به متن TRUE کاهش یابد و آن متن درون TRUE*B1 قرار گیرد، محاسبه مجدد به این بستگی خواهد داشت که آیا موتور کلمه کلیدی بولین را به تنهایی در یک عملیات ضرب می‌پذیرد یا خیر. جایگزینی با 1 به طور کامل این مشکل را دور می‌زند، زیرا 1*B1 در هر موقعیت حسابی بدون ابهام است. این کار همچنین با تبدیل اجباری (coercion) خود اکسل مطابقت دارد، جایی که در لحظه‌ای که نیاز به یک عدد باشد، TRUE به عنوان 1 و FALSE به عنوان 0 رفتار می‌کند

فراخوانی توابع به صورت اتمی کاهش می‌یابند

یک موتور مرحله‌ای ساده‌لوح ابتدا آرگومان‌های یک تابع را کاهش می‌دهد و سپس فراخوانی را. این کار برای اکسل اشتباه است و ردیاب عمداً این کار را انجام نمی‌دهد. یک فراخوانی تابع (function call) به عنوان یک کل، از متن اصلی خود، و در یک مرحله واحد ارزیابی می‌شود. دلیل این امر معناشناسی اتصال کوتاه (short-circuit semantics) است. توابع IF، CHOOSE و IFERROR تنها شاخه‌ای را که انتخاب می‌کنند ارزیابی می‌نمایند، و کاهش آرگومان‌ها از پیش باعث می‌شود موتور شاخه‌هایی را محاسبه کند که اکسل هرگز به آن‌ها نمی‌پردازد. نمونه بارز آسیب این کار، یک محافظ تقسیم‌بر‌صفر (divide-by-zero guard) مانند IF(B1=0,0,A1/B1) است: اگر ردیاب پیش از ارزیابی IF، عبارت A1/B1 را کاهش می‌داد، محافظ عمل نمی‌کرد و دقیقاً همان خطایی که برای جلوگیری از آن ایجاد شده بود، بروز می‌کرد. با ارزیابی کل فراخوانی به صورت اتمی، ردیاب ارزیابی تنبل (lazy evaluation) را حفظ می‌کند که باعث می‌شود چنین محافظ‌هایی به درستی کار کنند

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

بهای این کار (trade-off) این است که شما داخل فراخوانی تابع را به عنوان مراحل جداگانه نمی‌بینید، اما این رفتار صحیح است. نشان دادن کاهش آرگومان‌هایی که اکسل هرگز آن‌ها را انجام نمی‌دهد، ردیابی گمراه‌کننده‌تری خواهد بود تا اینکه بخواهیم فراخوانی را همان‌طور که واقعاً هست، به عنوان یک واحد ارزیابی یکپارچه در نظر بگیریم

جداکننده‌های آرگومان و محدوده‌های دست‌نخورده (intact ranges)

دو عادی‌سازی (normalization) دیگر، محاسبه مجدد را صحیح و دقیق نگه می‌دارند. کامپایلر موتور محاسبه از ; به عنوان جداکننده آرگومان تابع استفاده می‌کند، بنابراین وقتی ردیاب یک فراخوانی تابع را از درخت تجزیه‌شده خود بازسازی می‌کند، آرگومان‌ها را با ; به هم متصل می‌سازد، حتی اگر کاربر در ابتدا , تایپ کرده باشد. فرمولی که به صورت SUM(A1,A2,A3) نوشته شده، به عنوان SUM(A1;A2;A3) دوباره محاسبه می‌شود، که موتور آن را می‌پذیرد. جایگزینی مقادیر، همان چیزی است که این بازسازی را ضروری می‌سازد، و استفاده از جداکننده درست باعث می‌شود که این بازسازی به درستی تجزیه شود

ارجاعات محدوده (Range references) مورد دیگر است. یک محدوده مانند A1:A3 اسکالر (scalar) نیست و نباید به سه مقدار جداگانه تقسیم شود، زیرا تابعی که آن را مصرف می‌کند انتظار یک آرگومان محدوده را دارد. ردیاب یک محدوده را به عنوان متن اصلی خود دست‌نخورده نگه می‌دارد و به تابع احاطه‌کننده اجازه می‌دهد به عنوان یک کل کاهش یابد. در SUM(A1:A3)*B1 محدوده کامل می‌ماند، SUM(A1:A3) در یک مرحله اتمی به یک عدد کاهش می‌یابد، و تنها در آن زمان است که ضرب بیرونی اجرا می‌شود. این دقیقاً همان مرزی است که اکسل بین یک عملوند محدوده و اسکالری که در نهایت ارائه می‌دهد، ترسیم می‌کند

// 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 اکسل باشد، به یک آینه وفادار از آن تبدیل شود. کاهش‌ها به ترتیبی رخ می‌دهند که اکسل آن‌ها را انجام می‌دهد، لیترال‌های جایگزین‌شده در هر منطقه جغرافیایی بدون خطا باقی می‌مانند، عبارات بولی همان‌طور که در اکسل تبدیل می‌شوند به طور اجباری (coerced) تبدیل می‌گردند، و توابع تنبل همچنان تنبل می‌مانند. اگر می‌خواهید با توابع سفارشی خودتان موتور را بیشتر توسعه دهید، مقاله موتور فرمول و توابع سفارشی نحوه ثبت آن‌ها را نشان می‌دهد، و برای کارهای عددی سنگین‌تر، مقاله توابع توزیع آماری در دلفی کتابخانه داخلی که ردیاب بر اساس آن ارزیابی می‌کند را پوشش می‌دهد. تمام این موارد به عنوان بخشی از کامپوننت صفحات گسترده HotXLS برای Delphi و C++Builder عرضه می‌شود، در کنار APIهای خواندن، نوشتن، قالب‌بندی و محاسبه که در جاهای دیگر این وبلاگ پوشش داده شده‌اند