تتبع تقييم صيغة Excel خطوة بخطوة في Delphi

يخفي Excel مصحح أخطاء (debugger) صغيراً على مرأى من الجميع. حدد خلية، وافتح الصيغ (Formulas) وانقر فوق تقييم الصيغة (Evaluate Formula)، ويُظهر مربع حوار الصيغة مع تسطير تعبير فرعي (subexpression) واحد. اضغط على تقييم وينهار ذلك التعبير الفرعي إلى قيمته، ثم يتم تسطير التعبير التالي، وتشاهد تعبيراً طويلاً يتقلص إلى رقم واحد بانخفاض (reduction) واحد في كل مرة. إنها أسرع طريقة للعثور على فرع IF المتداخل الذي تم تشغيله بالفعل، أو المرجع الذي غذى إجمالياً خاطئاً. يعيد HotXLS إنتاج هذا السلوك الدقيق من خلال TXLSFormulaTracer، بحيث يمكن لبرنامج Delphi أو C++Builder تقديم نفس قائمة الخطوات لتدقيق مصنف، أو تصحيح أخطاء صيغة تم إنشاؤها، أو تعليم شخص ما سبب ظهور النتيجة بالطريقة التي ظهرت بها. تحمل كل خطوة مسجلة نص التعبير الفرعي والقيمة التي يتم تقليصها إليه

كيف يمشي محرك الاختزال (reduction engine) عبر التعبير

لا يصل المتتبع (tracer) إلى محرك الحساب. إنه يرمز (tokenizes) الصيغة ويحللها (parses) باستخدام محلل نزول عودي (recursive-descent parser)، ثم يختزل الشجرة بالعمق أولاً، بدءاً من التعبير الفرعي الأعمق القابل للتقييم. عندما يختزل العقدة إلى قيمة، يتم استبدال تلك القيمة مرة أخرى في التعبير المحيط كقيمة حرفية، ويطلب المحرك من الآلة الحاسبة الحقيقية إعادة حساب التعبير الأبسط الآن. نظراً لأن كل خطوة يتم تقييمها من خلال طريقة Calculate العامة في ورقة العمل بدلاً من اختصار خاص، فإن كل خطوة تتفق تماماً مع ما قد ينتج عن إعادة الحساب الكامل للخلية. المحلل غير غازٍ (non-invasive) حسب التصميم، وهو ما يسمح له بالعمل ضد أي ورقة عمل دون الإخلال بحالتها

يتبع المحلل (parser) سلم أسبقية المشغل (operator-precedence ladder)، مع مستوى عودي واحد لكل نطاق أسبقية (precedence band). من أدنى ارتباط إلى أعلاه، النطاقات هي: مقارنة المستوى 0 (=، <>، <، >، <=، >=)، وتوصيل سلسلة (string concatenation) المستوى 1 (&)، والجمع والطرح في المستوى 2، والضرب والقسمة في المستوى 3، والأس (exponentiation) في المستوى 4، وأخيراً الزائد والناقص الأحاديان (unary plus and minus) أسفل ذلك. يحلل كل مستوى المستوى الذي فوقه لمعاملاته، وبالتالي فإن النطاق الأعلى يرتبط بشكل أضيق. هذه هي نفس الأسبقية التي يطبقها Excel، وهو سبب قيام A1*B1+A2*B1 باختزال حاصل الضربين قبل المجموع: يقع الضرب في المستوى 3، والجمع في المستوى 2، لذلك تكون عمليات الضرب أعمق في الشجرة ويتم اختزالها أولاً

تتبع صيغة والمشي عبر الخطوات

يعكس الاستخدام العرض التوضيحي (demo) المشحون في Demo/Delphi/FormulaTrace/FormulaTrace.dpr. قم ببناء ورقة عمل (أو فتح مصنف موجود)، وقم بإنشاء متتبع (tracer) على الورقة، واستدعِ 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;

تُحل مراجع الخلية أولاً وتظهر كخطوات خاصة بها، ثم تُختزل حواصل الضرب، ثم عامل الضريبة الموضوع بين قوسين، وعملية الضرب النهائية تغلقها. يسمح لك حقل Depth بعمل مسافة بادئة بحيث تجلس عمليات الاختزال الأعمق في أعمق مكان بشكل مرئي، تماماً كما يقوم Excel بتسطير المصطلح الأعمق قبل أي مصطلح خارجي

فخ القيمة الحرفية الخالية من المنطقة (locale-free literal trap)

التفصيل الأكثر خطورة في هذا المخطط بأكمله غير مرئي على آلة إنجليزية وينكسر بصوت عالٍ على آلة ألمانية. عند استبدال رقم محسوب مرة أخرى في نص الصيغة، يجب كتابته كسلسلة (string) ثم إعادة تحليله بواسطة محرك الحساب، الذي يعامل . كنقطة عشرية (decimal point). إذا استخدم الاستبدال إعدادات المنطقة للنظام (system locale)، فستكتب TFormatSettings الألمانية 1,08 لعامل الضريبة، وستُقرأ الفاصلة كفاصل وسيطة (argument separator)، وإعادة حساب A1*B1*1,08 إما أن يتم تحليلها في الشكل الخاطئ أو تفشل تماماً

يتجنب المتتبع (tracer) ذلك من خلال تنسيق كل قيمة حرفية رقمية (numeric literal) من خلال TFormatSettings خاص يقوم بتثبيته عند البناء، مع إجبار 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)

هذا هو نوع الخطأ الذي لا يظهر أبداً في اختبار المؤلف الخاص ويظهر فقط عندما يقوم عميل في منطقة أخرى بتشغيل نفس الرمز، لذا يستحق الأمر أن نذكره بصراحة: رحلة دائرية لقيمة من خلال نص الصيغة هي مشكلة تسلسل (serialization)، والتسلسل يجب أن يكون خالياً من المنطقة

القيم المنطقية تختزل إلى 1 و 0

يتعلق قرار الاستبدال ذي الصلة بالقيم المنطقية. عندما يتم تقييم تعبير فرعي على أنه منطقي (boolean)، يعيد المتتبع كتابته كـ 1 أو 0، وليس كـ TRUE أو FALSE. والسبب هو أن القيمة الحرفية المختزلة يجب إعادة تحليلها بشكل نظيف في أي سياق يحيط بها، والحساب هو الحالة الصعبة. إذا اختُزلت مقارنة مثل A1>A2 إلى النص TRUE وهبط هذا النص داخل TRUE*B1، فإن إعادة الحساب ستعتمد على قبول المحرك لكلمة منطقية مجردة في عملية الضرب. استبدال 1 يتجنب السؤال تماماً، لأن 1*B1 لا لبس فيه في أي موضع حسابي. كما أنه يتطابق مع إكراه (coercion) Excel الخاص، حيث يتصرف TRUE كـ 1 و FALSE كـ 0 في اللحظة التي يُتوقع فيها رقم

استدعاءات الدالة تختزل ذرياً (atomically)

قد يختزل محرك الخطوة الساذج (naive step engine) وسيطات الدالة أولاً ثم الاستدعاء. هذا خطأ بالنسبة لـ Excel، ولا يفعل المتتبع ذلك عن عمد. يتم تقييم استدعاء الدالة ككل، من نصها الأصلي، في خطوة واحدة. السبب هو دلالات الدائرة القصيرة (short-circuit semantics). تقوم IF و CHOOSE و IFERROR بتقييم الفرع الذي تختاره فقط، وتقليص الوسائط أولاً من شأنه أن يجبر المحرك على حساب فروع لا يمسها Excel أبداً. الضحية الكلاسيكية هي حارس القسمة على صفر (divide-by-zero guard) مثل IF(B1=0,0,A1/B1): إذا قلص المتتبع A1/B1 قبل تقييم IF، فإن الحارس سيخطئ الهدف ويثير الخطأ نفسه الذي وُجد لمنعه. من خلال تقييم الاستدعاء بأكمله بشكل ذري (atomically)، يحافظ المتتبع على التقييم الكسول (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) هي أنك لا ترى داخل استدعاء الدالة كخطوات منفصلة، ولكن هذا هو السلوك الصحيح. إن إظهار عمليات اختزال الوسيطات التي لا يقوم بها Excel أبداً سيكون تتبعاً مضللاً أكثر من معاملة الاستدعاء كوحدة تقييم واحدة كما هو الحال في الواقع

فواصل الوسيطات والنطاقات السليمة (intact ranges)

تحافظ عمليتا تسوية (normalizations) أخريان على نزاهة إعادة الحساب. يتوقع مترجم (compiler) محرك الحساب ; كفاصل لوسيطة الدالة، لذلك عندما يعيد المتتبع بناء استدعاء دالة من شجرته المحللة، فإنه يربط الوسائط بـ ;، حتى لو كتب المستخدم , في الأصل. تتم إعادة حساب صيغة مكتوبة على النحو SUM(A1,A2,A3) لتصبح SUM(A1;A2;A3)، وهو ما يقبله المحرك. استبدال القيم هو ما يجعل إعادة البناء هذه ضرورية، والحصول على الفاصل الصحيح هو ما يجعل إعادة البناء تُحلل (parse)

مراجع النطاق (Range references) هي الحالة الأخرى. النطاق مثل A1:A3 ليس عدداً قياسياً (scalar) ولا يجب تقسيمه إلى ثلاث قيم منفصلة، لأن الدالة التي تستهلكه تتوقع وسيطة نطاق. يحافظ المتتبع على النطاق سليماً كنصه الأصلي ويسمح للدالة المرفقة (enclosing function) بالاختزال ككل. في SUM(A1:A3)*B1 يظل النطاق كاملاً، ويختزل SUM(A1:A3) إلى رقم واحد في خطوة ذرية (atomic step) واحدة، وبعد ذلك فقط تعمل عملية الضرب الخارجية. هذا هو نفس الحد الذي يرسمه Excel بين معامل (operand) النطاق والعدد القياسي الذي يساهم به في النهاية

// 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 بدلاً من أن تكون تقريبية له. تحدث التخفيضات (reductions) بالترتيب الذي يؤديه Excel، وتنجو القيم الحرفية المستبدلة من أي منطقة، وتُكره (coerce) القيم المنطقية بالطريقة التي يكرهها Excel، وتظل الدوال الكسولة (lazy functions) كسولة. إذا كنت ترغب في دفع المحرك إلى أبعد من ذلك باستخدام دوالك الخاصة، فإن مقالة محرك الصيغة والدوال المخصصة توضح كيفية تسجيلها، وللعمل الرقمي الثقيل تغطي دوال التوزيع الإحصائي في مقالة Delphi المكتبة المدمجة التي يقيِّم المتتبع في مقابلها. يتم شحن كل ذلك كجزء من مكون جداول البيانات HotXLS لـ Delphi و C++Builder، إلى جانب واجهات برمجة تطبيقات القراءة، والكتابة، والتنسيق، والحساب التي تم تناولها في مكان آخر في هذه المدونة