ردیابی گام به گام ارزیابی فرمول اکسل در دلفی

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

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

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

تجزیه‌گر از یک نردبان اولویت عملگر (operator-precedence) با یک سطح بازگشتی در هر باند اولویت پیروی می‌کند. از کمترین پیوند (binding) به بیشترین پیوند، باندها عبارتند از: سطح 0 مقایسه (=, <>, <, >, <=, >=)، سطح 1 اتصال رشته (&)، سطح 2 جمع و تفریق، سطح 3 ضرب و تقسیم، سطح 4 توان، و در نهایت مثبت و منفی یکانی زیر آن. هر سطح، سطح بالای خود را برای عملوندهایش تجزیه می‌کند، بنابراین یک باند بالاتر پیوند محکم‌تری دارد. این همان اولویتی است که اکسل اعمال می‌کند، که به همین دلیل است که 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 به شما امکان می‌دهد تو رفتگی ایجاد کنید تا درونی‌ترین کاهش‌ها به وضوح در عمیق‌ترین سطح قرار گیرند، دقیقاً همانطور که اکسل زیر درونی‌ترین عبارت را پیش از هر عبارت بیرونی خط می‌کشد

تله نماد مستقل-از-زبان (locale-free literal trap)

خطرناک‌ترین جزئیات در کل این طرح‌بندی روی یک ماشین انگلیسی نامرئی است و در یک ماشین آلمانی با صدای بلند می‌شکند. هنگامی که یک عدد محاسبه شده به متن فرمول بازگردانده می‌شود، باید به عنوان یک رشته نوشته شود و سپس دوباره توسط موتور محاسبه که . را به عنوان ممیز اعشار در نظر می‌گیرد، تجزیه شود. اگر جایگزینی از زبان سیستم (system locale) استفاده کند، 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)

این نوعی از باگ است که هرگز در تست‌های خود نویسنده ظاهر نمی‌شود و تنها زمانی ظاهر می‌شود که مشتری در زبان دیگری همان کد را اجرا کند، بنابراین ارزش دارد به وضوح بیان کنیم: انتقال یک مقدار رفت و برگشت از طریق متن فرمول یک مشکل سریال‌سازی است و سریال‌سازی باید مستقل از زبان (locale-free) باشد

کاهش Boolean‌ها به 1 و 0

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

ارزیابی تماس‌های تابع به شکل اتمی

یک موتور گام ساده لوحانه ابتدا آرگومان‌های یک تابع را کاهش داده و سپس خود تماس را کاهش می‌دهد. این برای اکسل اشتباه است، و ردیاب عمداً این کار را نمی‌کند. تماس تابع به عنوان یک کل، از متن اصلی خود، در یک گام ارزیابی می‌شود. دلیل آن معناشناسی اتصال-کوتاه (short-circuit semantics) است. IF، CHOOSE و IFERROR تنها شاخه‌ای را که انتخاب می‌کنند ارزیابی می‌نمایند، و کاهش آرگومان‌ها قبل از آن موتور را مجبور می‌کند شاخه‌هایی را که اکسل هرگز لمس نمی‌کند محاسبه کند. نمونه کلاسیک محافظ تقسیم بر صفر مانند IF(B1=0,0,A1/B1) است: اگر ردیاب A1/B1 را پیش از ارزیابی IF کاهش دهد، محافظ به اشتباه عمل می‌کند و دقیقاً همان خطایی را که برای جلوگیری از آن وجود دارد بالا می‌آورد. با ارزیابی کل تماس به صورت اتمی، ردیاب ارزیابی تنبل (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.

معاوضه این است که شما داخل تماس تابع را به عنوان گام‌های جداگانه نمی‌بینید، اما این رفتار صحیح است. نشان دادن کاهش‌های آرگومانی که اکسل هرگز انجام نمی‌دهد یک ردیابی بسیار گمراه‌کننده‌تر نسبت به تلقی تماس به عنوان واحد ارزیابی یکتایی است که واقعا هست

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

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

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