تدقيق ذاكرات صيغ Excel المخبأة مع HotXLS Deep Recalc

يجيب HotXLS عن السؤال الذي يضطر إليه كل خط معالجة جداول في النهاية: هل ما زالت الأعداد المخزنة في مصنف تطابق الصيغ التي أنتجتها. فـ CalculateAndVerify تعيد حساب مخطط الاعتماديات كله في طبقة معزولة، وتقارن كل نتيجة بالقيمة المخبأة الموجودة أصلًا في الخلية، وتبلّغ عن الخلافات. وهي افتراضيًا لا تغير شيئًا

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

لماذا القيمة المخبأة المخالفة لصيغتها خطرة إلى هذا الحد

لأنها غير مرئية في كل مسار قراءة عادي. افتح الملف في عارضة، واقرأ الخلية عبر واجهة برمجية، وصدّرها CSV أو PDF، وستحصل على العدد المخبأ. والصيغة أمامك في الخلية نفسها، ولا أحد يقارنهما. والخلاف لا يظهر إلا حين يفتح أحدهم المصنف في Excel، التي تعيد الحساب عند التحميل تحت معظم الإعدادات، فيعرض تقريرٌ كان مُصادقًا عليه الربع الماضي إجماليات مختلفة فجأة

والتدقيق موجود ليجعل تلك المقارنة عملية متعمدة مجدولة لا مصادفة. إنه المقابل الجداولي للتحقق من checksum: رخيص بما يكفي ليجرى في خط معالجة داخِل، وهو الشيء الوحيد الذي يحوّل مشكلة سلامة بيانات صامتة إلى تقرير تتصرف على أساسه

var
  Book: TXLSWorkbook;
  Options: TXLSRecalcAuditOptions;
  Report: TXLSCalculationAuditReport;
  I: Integer;
begin
  Book := TXLSWorkbook.Create(nil);
  try
    Book.LoadFromFile('quarterly-close.xls');
    Options := TXLSRecalcAuditOptions.Default;
    Options.MaxIssues := 500;
    Report := Book.CalculateAndVerify(Options);
    try
      for I := 0 to Report.Count - 1 do
        if Report[I].Kind = xlcaiCacheMismatch then
          Writeln(Report[I].SheetName, '!',
                  Report[I].Row, ':', Report[I].Col, '  ',
                  Report[I].Formula,
                  '  cached=', VarToStr(Report[I].Actual),
                  '  recomputed=', VarToStr(Report[I].Expected));
      if Report.Truncated then
        Writeln('issue budget reached, raise MaxIssues');
    finally
      Report.Free;
    end;
  finally
    Book.Free;
  end;
end;

وهناك ثلاث تحميلات زائدة تجيب عن ثلاثة أسئلة مختلفة. فـ CalculateAndVerify بلا وسائط تعيد عدد الخلافات، وهو كل ما يحتاجه فحص صحة. والتحميل ذو مصفوفة out للخلافات يعطيك الخلايا. والتحميل الآخذ بـ TXLSRecalcAuditOptions يعيد TXLSCalculationAuditReport كاملة، وهي ما تلجأ إليه حين تحتاج أن تعرف ليس فقط أن قيمة تخالف بل لماذا لم يستطع التدقيق تقييم شيء ما

الطبقة، ولماذا لا يكتب التدقيق

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

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

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

الإخفاقات تصنف لا تكدس معًا

الخلية التي لا يستطيع التدقيق تقييمها ليست النتيجة نفسها كخلية تخالف قيمتها، وTXLSCalculationAuditIssueKind يبقي الفئات منفصلة. فـ xlcaiCacheMismatch هو خلاف القيمة. وxlcaiMissingFunction وxlcaiMissingName تقولان إن المقيم قابل شيئًا لا ينفذه أو لا يستطيع حله. وxlcaiUnsupportedArguments تغطي أشكال وسائط خارج المجموعة الجزئية المدعومة. وxlcaiExternalReferenceDenied وxlcaiExternalReferenceMissing تفصلان رفض سياسة عن مصنف غائب. وxlcaiCircularReference وxlcaiDataTableSkipped وxlcaiParseFailure وxlcaiCancelled وxlcaiInternalFailure تكمل الطقم

وتمييز واحد يستحق الذكر لأنه يقلب افتراضًا شائعًا. فرمز خطأ Excel موجب نتيجة لا إخفاق. الخلية التي تُقيَّم مشروعًا إلى #DIV/0! حسبت صحيحًا، فيخزن التدقيق ذلك الخطأ في الطبقة ويقارنه بالمخبأ كأي قيمة أخرى. ومصنف مليء بخلايا خطأ مقصودة ينتج صفر ملاحظات، ومصنف ظهر فيه خطأ أو اختفى منذ خُبئت القيم ينتج بالضبط الملاحظات التي تريدها

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

قراءة سلسلة إخفاق

حين تخفق صيغة في التقييم، معرفة الخلية التي أخفقت نادرًا ما تكفي، لأن الإخفاق عادة على بعد ثلاثة مستويات في سلسلة مراجع. لذا تحمل كل ملاحظة سلسلة Stack تُصيَّر بالإطار الخارجي أولًا، على شكل Sheet1!A1 > Sheet1!B2 > Data!C7، فيشير التقرير إلى الخلية التي انكسرت فعلًا لا الخلية التي صادف أنك نظرت إليها

والمسجل محدود. فـ MaxStackFrames افتراضيها 64 بأرضية 8، وأعمق سلسلة إخفاق هي التي تُحفظ: الإطار الداخلي يسجل السلسلة حين يكون منشأ الإخفاق هناك، والإطارات الخارجية المنفككة بعدها لا تستبدلها. وإذا تجاوزت أي سلسلة الميزانية، يُضبط Report.StackTruncated، وهو يخبرك الفرق بين سلسلة قصيرة وسلسلة لم ترَها كاملة

// القراءة فقط افتراضيًا. ApplyResults تلتزم الطبقة فقط بعد
// تدقيق ناجح كاملًا، تحت حارس كتابة يرفض الالتزام
// إذا تغيرت بنية المصنف أثناء جريان التدقيق
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // مقارنة تامة، تكشف الانجراف
Options.RelativeTolerance := 0;
Options.OnProgress := HandleProgress;

Report := Book.CalculateAndVerify(Options);
try
  if Report.Applied then
    Book.SaveToFile('quarterly-close-repaired.xls')
  else
    Writeln('not applied: ', Report.Count, ' issues blocked the commit');
finally
  Report.Free;
end;

procedure THarness.HandleProgress(ASender: TObject;
  ACurrent, ATotal: Integer; var ACancel: Boolean);
begin
  ACancel := FUserRequestedStop;   // يتوقف التدقيق عند حد العقدة التالي
end;

متى تترك التدقيق يصلح المصنف

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

ولاحظ اللاتماثل المتعمد. فخلافات المخبأ لا تحجب التطبيق، لأنها بالضبط ما جاء الالتزام ليصلحه. أما ملاحظات صنف الإخفاق فتحجبه، لأن مصنفًا تعذر تقييم بعض صيغه سيكون نصف مُصلَح، والمصنف نصف المصلح أسوأ من غير المصلح الذي تعرف ألا تثق به

السماحية قرار سياسة لا افتراض

المقارنة الافتراضية سماحية مطلقة 1E-6 مع تعطيل السماحية النسبية، وهذا يحفظ السلوك الكلاسيكي ويقبل انجراف 4E-7 بصمت. وهذا صائب عادة: فاختلافات ترتيب التقييم العشري العائم بين ما أنتج الملف والمقيم الحالي ستنتج اختلافات بهذا الحجم في المجاميع الطويلة، وتبليغها ملاحظاتَ سلامة ضجيج

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

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