إعادة الحساب التزايدي للصيغ في HotXLS لدلفي

يقوم HotXLS، وهو مكتبة Excel الأصلية لدلفي و C++Builder، بإجراء إعادة حساب تزايدي للصيغ من خلال TXLSXWorkbook.Recalculate. ويقوم الاستدعاء الأول ببناء مخطط تبعية الصيغ وتقييم كل خلية صيغة؛ ويعيد كل استدعاء لاحق تقييم الخلايا المتأثرة بعمليات كتابة القيم منذ التمريرة الأخيرة فقط، بالترتيب الطوبولوجي، في تمريرة واحدة تتناسب تكلفتها مع عدد الخلايا غير النظيفة (dirty) بدلاً من حجم المصنف

هذا القرار التصميمي الفردي هو الفارق بين نموذج مالي يستجيب للافتراض المعدل في أجزاء من الثانية ونموذج آخر يتوقف لثوانٍ. وإذا كنت تنشئ تقارير تغذي فيها حفنة من خلايا المدخلات آلاف الصيغ اللاحقة، فإن بقية هذا المقال يشرح ما يفعله المخطط، وما هي الدوال التي تخرج عن نطاق التزايدية، وكيف يتم الإبلاغ عن المراجع الدائرية بدلاً من الدوران إلى الأبد

لماذا يؤدي تغيير خلية واحدة إلى إعادة حساب مئة ألف صيغة؟

لا يمتلك محرك الصيغ الساذج ذاكرة لمن يعتمد على من، لذا فإن حركته الآمنة الوحيدة بعد أي تعديل هي تقييم كل شيء مرة أخرى. والأسوأ من ذلك أن استراتيجية العودية (recursive) الكلاسيكية — عندما تشير الصيغة A إلى الصيغة B، فيتم تقييم B على الفور — تعيد تقييم الخلايا المشار إليها دون شروط، متجاهلة أي قيمة مخزنة مؤقتًا. وتكلف سلسلة مكونة من n من الصيغ تشير كل منها إلى السابقة تقييمات بمعدل O(n²) لكل تمريرة كاملة، ويرسل المرجع الدائري العودية إلى هاوية سحيقة. وقد شاهد كل مطور جداول بيانات قام بربط نموذج متتالي بمقيم عودي كلا وضعي الفشل يحدثان

كيف يحول مخطط التبعية تعديلاً إلى تمريرة واحدة

يعطي مخطط التبعية في HotXLS لكل خلية صيغة عقدة واحدة، مع حواف تمتد من السابقة إلى التابعة. وعندما يكتب الكود الخاص بك قيمة خلية، يسجل المصنف الخلية على أنها غير نظيفة (dirty)؛ وعند تشغيل Recalculate، تنتقل عدم النظافة على طول الحواف إلى كل صيغة لاحقة، ويتم تقييم المخطط الفرعي غير النظيف مرة واحدة بالضبط بالترتيب الطوبولوجي باستخدام خوارزمية Kahn. ونظرًا أنه لا يتم زيارة الصيغة أبدًا قبل سوابقها، فإن كل عقدة تحتاج إلى تقييم واحد — وهذا ما يجعل التمريرة بمعدل O(dirty)

ترتيب الطوبولوجي يصلح أيضًا مشكلة العودية من جذورها. فخلال تمريرة إعادة الحساب، ينتقل المحرك إلى وضع مخصص يقرأ فيه أي مرجع لخلية صيغة أخرى قيمة تلك الخلية المخزنة مؤقتًا مباشرة بدلاً من إعادة تقييمها — ويضمن الترتيب أن تكون الذاكرة المؤقتة جديدة بالفعل. وتعني هذه الآلية نفسها أن حلقة المراجع لا يمكن أن تثير عودية غير محدودة: فلا يوجد شيء داخل التمريرة يعيد الدخول إلى المقيم لخلية مجاورة

var
  Book: TXLSXWorkbook;
  Inputs, Model: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Inputs := Book.Sheets.Add('Inputs');
    Model  := Book.Sheets.Add('Model');

    Inputs.Cells[2, 2].Value := 0.05;                 // growth assumption
    Model.Cells[2, 2].Formula := 'Inputs!B2*1000';    // XLSX formulas take no leading '='
    Model.Cells[3, 2].Formula := 'B2*(1+Inputs!B2)';
    // ... thousands more rows cascading off the same assumption ...

    Book.Recalculate;                 // first call: builds the graph, full evaluation

    Inputs.Cells[2, 2].Value := 0.07; // one edit marks one cell dirty
    Book.Recalculate;                 // second call: only the downstream chain runs
  finally
    Book.Free;
  end;
end;

تستقر كل نتيجة في Value المخزنة مؤقتًا للخلية، لذا بعد إرجاع Recalculate تقرأ المخرجات بنفس الطريقة التي تقرأ بها أي خلية أخرى. وفي حلقة إنشاء التقارير، يكون النمط هو الكود الموضح أعلاه بالضبط: تحميل أو بناء النموذج مرة واحدة، ثم التناوب بين كتابة بضع خلايا مدخلات واستدعاء Recalculate، والدفع فقط مقابل الصيغ التي تعتمد فعليًا على ما تغير

ما هي دوال Excel التي تفرض إعادة الحساب في كل تمريرة؟

يتعامل HotXLS مع NOW، و TODAY، و RAND، و OFFSET، و INDIRECT كدوال متقلبة: حيث يتم إعادة تقييم أي صيغة تحتوي على إحداها في كل تمريرة Recalculate، سواء تغير أي شيء في المراحل السابقة أم لا. والثلاثة الأولى متقلبة لنفس السبب الذي يجعلها كذلك في Excel — فنتيجتها تعتمد على لحظة التقييم، وليس على خلايا أخرى. وتعد OFFSET و INDIRECT متقلبتين لسبب أكثر دقة: فالخلايا التي تقرأها يتم حسابها في وقت التشغيل، وبالتالي لا يمكن للمخطط معرفة الحواف التي يجب رسمها لها بشكل ثابت

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

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

كيف يبلغ HotXLS عن المراجع الدائرية؟

ترجع TXLSXWorkbook.Recalculate القيمة lxOk في التمريرة النظيفة و lxErrorRef عندما تكتشف حلقة مرجعية. ويتم تحديد أعضاء الحلقة أثناء الترتيب الطوبولوجي — وهي العقد التي لا يمكن لخوارزمية Kahn تحريرها أبدًا — ويتم تخطيها بدلاً من الدوران فيها: فتظل قيمها المخزنة مؤقتًا كما كانت، بينما يتم تقييم كل صيغة خارج الحلقة بشكل طبيعي بالترتيب. ويحصل موقع الاستدعاء الخاص بك على رمز خطأ محدد بدلاً من التعليق

case Book.Recalculate of
  lxOk:
    SaveReport(Book);
  lxErrorRef:
    // a reference cycle exists; cycle members kept their previous
    // cached values and everything outside the cycle is up to date
    LogWarning('Circular reference detected - review model inputs');
end;

يعد العثور على الخلايا التي تشكل الحلقة مهمة تصحيح أخطاء، و متتبع تقييم الصيغ هو الأداة المناسبة لذلك: تتبع الصيغة المشبوهة وتصبح سلسلة المراجع التي تطوى على نفسها مرئية خطوة بخطوة. والحلقات في النماذج الحقيقية هي دائما تقريباً خطأ في التأليف — مثل إدراج صف ملخص بالخطأ في نطاق SUM الخاص به — لذا فإن رمز خطأ واضح في وقت إعادة الحساب هو بالضبط ما تريده

صيغ المصفوفات، وتتبع الخلايا غير النظيفة (dirty)، ومتى يعاد بناء المخطط

تحصل صيغ مصفوفة CSE على عقدة واحدة للمستطيل المرتكز بالكامل، وليس عقدة واحدة لكل خلية. ويتم تقييم الصيغة الجذرية مرة واحدة لكل تمريرة؛ وتُكتب المصفوفة الناتجة مباشرة في كل خلية عضو، والصيغة التي تشير إلى أي خلية داخل النطاق المرتكز — وليس فقط المرسى العلوي الأيسر — تلتقط حافة تبعية من تلك العقدة الجذرية. وتنتشر النتائج القياسية (scalar) عبر المستطيل بالطريقة التي تفرضها دلالات المصفوفات القديمة في Excel

ترتبط خطافات تتبع عدم النظافة بواضعي الخصائص العاديين، لذا لا يتغير شيء في الكود الخاص بك. وكتابة Value على خلية يخطر المصنف ويعلم التوابع كغير نظيفة (dirty)؛ وتعيين Formula جديدة هو تغيير هيكلي، لذا فإنه يعلم المخطط بأكمله كقديم، وتقوم Recalculate التالية بإعادة بنائه قبل التقييم. ويؤدي إضافة الأوراق أو حذفها أو نقلها أيضًا إلى إبطال المخطط، نظرًا لأن هوية العقدة تشفر فهرس الورقة. وعندما لا يكون هناك مخطط نشط — كمصنف لا تستدعي عليه Recalculate أبدًا — تكلف الخطافات فحص nil واحدًا لكل تعيين، وبالتالي لا تتأثر أعباء عمل القراءة والكتابة العادية

حد واحد يستحق التصريح به بصدق

يتتبع المخطط التبعيات بين الخلايا، لذا فإن دالة معرفة من قبل المستخدم ومسجلة عبر OnUserFunction يتم إعادة تقييمها عندما تتغير الخلايا التي تغذي معاملاتها، مثل أي صيغة أخرى. وإذا كنت تقوم بتوسيع المحرك بهذه الطريقة، فإن المقال الخاص بـ الدوال المخصصة في محرك صيغ HotXLS يستعرض عقد الاستدعاء وكيفية وصول قيم المعاملات

إعادة الحساب التزايدي هي جزء من محرك XLSX القياسي في مكون HotXLS لدلفي و Excel، إلى جانب حاسبة الصيغ، والأسماء المعرفة، وخط أنابيب الاستيراد والتصدير الذي تسرعه. وإذا كان تطبيق دلفي أو C++Builder الخاص بك يحافظ على نماذج حية — أوراق التسعير، مصنفات الدمج، تقارير متتالية — فإن Recalculate هي الفارق بين إعادة حساب مصنف وإعادة حساب تعديل