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

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

كيف يمشي محرك التخفيض عبر التعبير

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

يتبع المحلل سلماً لأولوية المعاملات، مع مستوى تعودي واحد لكل نطاق أولوية. من أدنى ارتباط إلى أعلاه، تكون النطاقات كالتالي: المستوى 0 المقارنة (=، <>، <، >، <=، >=)، المستوى 1 تسلسل السلاسل النصية (&)، المستوى 2 الجمع والطرح، المستوى 3 الضرب والقسمة، المستوى 4 الرفع للأس، وأخيراً الموجب والسالب الأحاديين أسفل ذلك. يقوم كل مستوى بتحليل المستوى الذي يعلوه للحصول على معاملاته، لذلك يكون ارتباط النطاق الأعلى أقوى. هذه هي نفس الأولوية التي يطبقها Excel، وهذا هو السبب في أن A1*B1+A2*B1 تخفض حاصلَي الضرب قبل المجموع: يقع الضرب في المستوى 3، والجمع في المستوى 2، لذا فإن عمليات الضرب تكون أعمق في الشجرة وتخفض أولاً

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

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

فخ الحرفيات المستقلة عن الإعدادات المحلية

إن التفصيل الأكثر خطورة في هذا المخطط بأكمله غير مرئي على جهاز يعمل بالإنجليزية ويتعطل بصوت عالٍ على جهاز يعمل بالألمانية. عندما يتم استبدال رقم محسوب مرة أخرى في نص الصيغة، يجب كتابته كسلسلة نصية ثم إعادة تحليله بواسطة محرك الحساب، والذي يعامل . كنقطة عشرية. إذا استخدم الاستبدال الإعدادات المحلية للنظام (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)

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

تُخفض القيم المنطقية إلى 1 و 0

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

تنخفض استدعاءات الدوال بشكل ذري

قد يقوم محرك خطوات ساذج بتخفيض وسيطات الدالة أولاً ثم الاستدعاء. هذا خاطئ بالنسبة لـ Excel، والمتتبع يتعمد عدم القيام بذلك. يتم تقييم استدعاء الدالة ككل، من نصها الأصلي، في خطوة واحدة. والسبب هو دلالات الدائرة القصيرة (short-circuit). تُقيّم دوال IF و CHOOSE و IFERROR فقط الفرع الذي تختاره، وتخفيض الوسيطات أولاً سيجبر المحرك على حساب فروع لا يمسها Excel أبداً. الإصابة الكلاسيكية هي حارس القسمة على صفر مثل 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.

المقايضة هي أنك لا ترى داخل استدعاء الدالة كخطوات منفصلة، ولكن هذا هو السلوك الصحيح. إن إظهار تخفيضات الوسيطات التي لا ينفذها Excel أبداً سيكون تتبعاً أكثر تضليلاً من معاملة الاستدعاء كوحدة التقييم الوحيدة التي هي عليه في الواقع

فواصل الوسيطات والنطاقات السليمة

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

مراجع النطاقات هي الحالة الأخرى. إن نطاقاً مثل A1:A3 ليس مقداراً قياسياً (scalar) ويجب ألا يُقسم إلى ثلاث قيم منفصلة، لأن الدالة التي تستهلكه تتوقع وسيطة نطاق. يحافظ المتتبع على النطاق سليماً كنصه الأصلي ويسمح للدالة المحيطة بالانخفاض ككل. في 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 لـ Delphi و C++Builder، إلى جانب واجهات برمجة تطبيقات القراءة والكتابة والتنسيق والحساب التي تمت تغطيتها في مكان آخر في هذه المدونة