SUBTOTAL وAGGREGATE والصفوف المخفية في Delphi مع HotXLS

إن كان SUBTOTAL(109, ...) وSUBTOTAL(9, ...) يعيدان الرقم نفسه على مصنَّف يحتوي صفوفًا مخفية، فأحدهما خاطئ. تصرفت HotXLS، مكوّن جدول بيانات Excel الأصلي لـDelphi وC++Builder، بالضبط بهذه الطريقة حتى الإصدار 2.197.0، لأن محرك الحساب فيها لم يكن يملك طريقة ليسأل ورقة عمل ما إذا كان صف معيّن مخفيًا

نادرًا ما يصل العرَض كتقرير علّة عن رموز الصيغ. بل يصل كتباين: مهمة دفعية batch على الخادم تحسب مجموعًا، ويفتح مستخدم الملف نفسه في Excel بفلتر مُطبَّق، ويختلف الرقمان بمقدار ما كانت الصفوف المُفلترة خارجًا تساويه في المجموع. لا أحد يشتبه بدالة التجميع، لأن سلسلة الصيغة في الخلية متطابقة في كلا الموضعين. الفارق كليًا فيما سُمح لأداة التقييم أن تراه

لماذا يتضمن SUBTOTAL 109 الصفوف المخفية؟

لأنه في معظم تصاميم المحركات، الطبقة التي تُقيّم صيغة لا تعرف شيئًا عن رؤية الصف. كانت HotXLS حالة نموذجية: محرك الحساب في lxCalc.pas كان يصل إلى قيم الخلايا عبر استدعاء رجوع callback وحيد TXLSGetValue يجيب بقيمة لثلاثية (ورقة، صف، عمود) ولا شيء غير ذلك. الرؤية سمة عرض مخزَّنة على سجلّ الصف، ولم يكن أي جزء من ذلك السجلّ يسافر أسفل سلسلة الاستدعاء. فامتلك المحرك مسار تجميع واحدًا، وحلّ نصفا جدول أرقام دوال SUBTOTAL إليه كلاهما. هذا ليس صنفًا من عيب خطأ تقريب: إنه السبب الكامل لوجود النصف الثاني من الجدول. يعرّف ECMA-376 الجزء 1، المنشور كـISO/IEC 29500-1، دالة SUBTOTAL في تعريفات دوال الصيغ (§18.17.7) بمعامل أول يختار كلًا من التجميع الداخلي وسياسة الصف المخفي. الرموز من 1 إلى 11 تُخطَّط إلى AVERAGE وCOUNT وCOUNTA وMAX وMIN وPRODUCT وSTDEV وSTDEVP وSUM وVAR وVARP بينما تتضمن القيم على الصفوف المخفية يدويًا. الرموز من 101 إلى 111 تختار التجميعات الأحد عشر نفسها وتستبعدها. المستخدم الذي يكتب 109 بدل 9 يصرّح ببيان متعمَّد بشأن البيانات المخفية، والمحرك الذي يدمج التمييز يبطل ذلك التصريح بصمت

ما الذي تُخطَّط إليه أرقام الدوال داخل المحرك

تحلّ HotXLS معامل SUBTOTAL الأول في CalcSubtotalFunc، التي تُطبِّع الرموز من 101 إلى 111 إلى معرّفات الدوال الداخلية نفسها للرموز من 1 إلى 11 ثم توزّع على التجميع نفسه. معظم العائلة يمرّ عبر مُجمِّع ExcelSum التزايدي، وهو الذي يعالج SUM وCOUNT وCOUNTA وMIN وMAX وAVERAGE. خمسة منها لا يمكنها ذلك: STDEV وVAR وSTDEVP وVARP وPRODUCT تحتاج تمريرة صيغة مغلقة closed-form فوق البيانات، فتوجّه CalcSubtotalFunc الرموز الداخلية 12 و46 و193 و194 و183 إلى مُختزِل reducer منفصل، SubtotalReduceVariance. ذلك الانقسام هو أول ما يستحق تخطيطه قبل لمس أي شيء، لأن مساري تجميع مستقلين يعنيان حلقتي اجتياز خلايا مستقلتين، وإصلاح يُطبَّق على واحدة منهما فقط ينتج أسوأ نتيجة ممكنة: SUBTOTAL(109, ...) يحترم الفلتر بينما SUBTOTAL(107, ...) على النطاق نفسه لا يفعل. عدّ الحلقات في HotXLS كشف عن ستة منها بمجرد تضمين AGGREGATE، موزَّعة عبر تقييم النطاق، وجمع النطاق العادي، وثلاثة مُختزِلات منفصلة

لماذا حقل مؤقت بدل ستة توقيعات جديدة؟

لأن تمرير معامل جديد عبر ست دوال اجتياز خلايا، إضافة إلى كل ما يستدعيها، تغيير واسع على مسار كود ساخن hot code path من أجل قيمة منطقية واحدة. كانت HotXLS تملك بالفعل سابقة للبديل: حقل عابر transient على الحاسبة، بالروح نفسها للحقل المؤقت الذي تستخدمه GetRangeInfo لتسجيل متى حُلّت إشارة ثلاثية الأبعاد إلى مصنَّف خارجي. أضاف الإصدار 2.197.0 حقلًا ثانيًا. اكتسب المحرك نوع استدعاء رجوع، TXLSIsRowHidden، معلَنًا كدالة من (SheetIndex، صف) تُعيد قيمة منطقية، مخزَّنة في FIsRowHidden، إضافة إلى علامة عابرة FIgnoreHiddenRows. تُسلَّح العلامة عند دخول CalcSubtotalFunc حين يقع رمز الدالة بين 101 و111، وعند دخول CalcAggregateFunc لرموز خيار AGGREGATE التي تختار استبعاد الصفوف المخفية. عندئذ تفحصها كل حلقة اجتياز خلايا وتتخطى صفًا واحدًا حين تكون مضبوطة، بإضافة سطر واحد فقط لكل منها

// The shape repeated in all six cell-walk loops
for sh := s1 to s2 do
  for rr := r1 to r2 do
  begin
    if FIgnoreHiddenRows and Assigned(FIsRowHidden) and FIsRowHidden(sh, rr) then
      Continue;
    for cc := c1 to c2 do
    begin
      // ... fold Cells[rr, cc] into the accumulator ...
    end;
  end;

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

prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
  FIgnoreHiddenRows := True;
try
  // aggregate over Item.Child[2] .. Item.Child[ChildCount]
  // every Exit path below is covered by the finally
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
end;

فحص Assigned هو ما يبقي التغيير متوافقًا. وسّعت HotXLS باني الحاسبة constructor بمعامل ثالث افتراضه nil، فأي كود يبني TXLSCalculator بالاستدعاء القديم من معاملين ما يزال يُصرَّف ويحصل على السلوك القديم الذي يتضمن المخفي. لم يتغيّر شكل الواجهة البرمجية القائمة في شيء

من أين يأتي بت الصف المخفي فعلًا؟

من ورقة العمل، عبر مصدرين مختلفين، لأن HotXLS تحمل محركي مصنَّف. الجانب القديم لـBIFF يجيب من TXLSRowInfoList.GetHidden، مصلاً إليه عبر TXLSWorkbook.GetRowHidden. جانب OOXML يجيب من TXLSXWorksheet.GetRowHidden، مصلاً إليه عبر TXLSXWorkbook.GetCalcRowHidden. كلاهما موصَّل بالحاسبة عند وقت البناء، إلى جانب استدعاء رجوع قيمة الخلية الذي يعكسانه. اتفاقيات الصفوف هي حيث يخطئ هذا النوع من الجسور عادة، فهي تستحق التصريح الصريح. تسلّم الحاسبة استدعاء الرجوع صفًا مرقَّمًا من 0، مطابقًا للإحداثيات التي يستخدمها TXLSGetValue أصلًا. أما ورقة عمل XLSX فترتّب خريطة الصف المخفي فيها برقم صف مرقَّم من 1، تمامًا كما يرقّم Excel الصفوف، وهو أيضًا ما تكشفه خاصية RowHidden[ARow] العامة. لذا يضيف جسر XLSX واحدًا قبل البحث، بينما لا يفعل جسر BIFF ذلك، لأن TXLSRowInfoList مرقَّمة من 0 أصلًا. كلا الجسرين يعامل فهرس ورقة أو صفًا خارج النطاق الصحيح على أنه مرئي، فاستعلام خارج الحدود يتراجع إلى الإجابة القديمة التي تتضمن المخفي بدل إسقاط بيانات

ما الذي يتغيّر للمصنَّفات المُفلترة

هذه هي الحالة التي تولّد تذاكر الدعم. تطبيق فلتر تلقائي AutoFilter في HotXLS عبر ApplyAutoFilter يقيّم معايير العمود ويخفي كل صف بيانات لا يطابق، وهذا بالضبط ما يفعله Excel حين ينقر مستخدم قائمة فلتر منسدلة. قبل الإصدار 2.197.0 كانت تلك الصفوف المخفية غير مرئية للمستخدم ومرئية بالكامل لمحرك الحساب، فكان SUBTOTAL(109, ...) من جانب الخادم يُبلغ عن المجموع غير المُفلتر. الآن يُبلغ الاستدعاء نفسه عن المجموع المُفلتر

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  VisibleRows: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[0];

    Sheet.SetAutoFilter('A1:E500');
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');
    VisibleRows := Sheet.ApplyAutoFilter;   // hides the non-matching rows

    Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
    Book.Recalculate;
    // The cell value now agrees with what Excel shows for the same filter,
    // and VisibleRows tells you how many rows fed into it

    Book.SaveAs('orders-filtered.xlsx');
  finally
    Book.Free;
  end;
end;

الإخفاء اليدوي يعمل بالطريقة نفسها، إذ إن RowHidden[ARow] := True هي الحالة نفسها التي يكتبها الفلتر. ذلك التكافؤ متعمَّد في Excel والآن قائم في HotXLS أيضًا. نتيجة واحدة تستحق ملاحظة في أي توثيق يُشحَن مع مصنَّفاتك المولَّدة: المجموع المحسوب برمز 109 رقم يعتمد على العرض view-dependent، فمستلِم يمسح الفلتر يغيّره. حين يجب أن يذكر تقرير رقمًا ثابتًا بصرف النظر عمّا يفعله القارئ بالعرض، فرمز 9 هو الاختيار الصحيح وكان دومًا كذلك. الفلاتر والتحقق والجداول مشروحة معًا في المقال عن التحقق من صحة البيانات وAutoFilter والجداول. ولأن إخفاء الصفوف لا يلمس أي صيغة، فهو أيضًا لا يوسّخ رسم التبعيات dependency graph من تلقاء نفسه، وهذا يستحق المعرفة إن كنت تعتمد على إعادة الحساب التزايدية عبر الرسم الفرعي المتّسخ لإبقاء المصنَّفات الكبيرة سريعة الاستجابة

رموز خيار AGGREGATE وحد واحد ما يزال مفتوحًا

AGGREGATE هو SUBTOTAL بمعامل سياسة ثانٍ، وتعالجه HotXLS في CalcAggregateFunc. معامل الخيار يُرمِّز مفاتيح مستقلة: هل تُتخطى استدعاءات SUBTOTAL وAGGREGATE المتداخلة داخل النطاق، وهل تُتخطى القيم على الصفوف المخفية، وهل تُقمَع قيم الأخطاء بدل نشرها. تسلّح HotXLS بوابة الصف المخفي المشتركة لرموز الخيار 2 و3 و6 و7، وتقمع قيم الأخطاء لرموز الخيار من 4 إلى 7. عندئذ يختار معامل رقم الدالة التجميع تمامًا كما تفعل SUBTOTAL، بما في ذلك توجيه التباين والانحراف المعياري والجداء عبر مُختزِلاتها الخاصة. تبقى فجوة موثَّقة واحدة، ومن الأفضل ذكرها هنا من اكتشافها في الإنتاج: دلالات تجاهل-SUBTOTAL-المتداخل المرتبطة برموز الخيار المنخفضة غير منفَّذة في HotXLS. اكتشاف SUBTOTAL متداخل داخل نطاق مُشار إليه يتطلب تعليم حالة عودية recursion لأداة التقييم بحيث يستطيع تجميع داخلي أن يُعلن عن نفسه للخارجي، وهو تغيير أكبر من بوابة الصف المخفي. عمليًا التعرّض صغير، لأن المصنَّفات الحقيقية تضع دومًا تقريبًا صيغ SUBTOTAL خارج النطاقات التي تجمِّع فوقها صيغ SUBTOTAL أخرى. إن كان مولِّدك يبني نطاقات تجميع متداخلة فعلًا، فلا تعتمد على رموز الخيار المنخفضة لإزالة تكرارها

حارس الوسائط arity الذي شُحن إلى جانبه

أغلق الإصدار 2.197.0 أيضًا فجوة تحقق في المُوزِّع نفسه، وسبب التصميم هو نفسه الذي دفع الحقل المؤقت: ضع الفحص حيث يمكن كتابته مرة واحدة. نحو 280 جسمًا لدالة مدمَجة كل واحد منها كان يتحقق من عدد وسائطه الخاص مقابل Item.ChildCount، ما ترك بلا حد متّسق لحالة وجود وسائط زائدة. استدعاء مثل =SIN(1,2) كان يصل إلى جسم دالة يفحص وسيطه الأول، ويتجاهل الفائض، ويُعيد رقمًا معقولًا حيث يُعيد Excel #VALUE!. كانت HotXLS تخزّن بالفعل arity المُعلَن لكل دالة مدمَجة في سجلّ دوالها، مكشوفًا كـTHashFunc.ArgsCnt بقيمة -1 تُعلّم دالة متغيرة الوسائط variadic مثل SUM أو IF أو CONCAT. مرّر الإصدار 2.197.0 ذلك عبر خاصية TXLSFormula.FuncArgsCntByPtg جديدة وأضاف بوابة واحدة في أعلى GetValueItemFunc، المُوزِّع الرئيسي

lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
  lProvidedArgs := Item.ChildCount - 1;   // Child[0] is the function node
  if lProvidedArgs > lDeclaredArgs then
  begin
    Result := lxErrorValue;               // =SIN(1,2) now yields #VALUE!
    Exit;
  end;
end;

يرفض الحارس الوسائط الزائدة ولا يقول شيئًا عن الناقصة عمدًا. حذف وسيط اختياري لاحق شرعي في Excel لدوال VLOOKUP وSUBSTITUTE وقائمة طويلة غيرها، ففحص متماثل كان سيكسر صيغًا صحيحة ليمسك بأخرى خاطئة. المعرّفات غير المعروفة تُبلغ كمتغيرة الوسائط وتتخطى البوابة كليًا، وهذا ما يبقي الدوال المُعرَّفة من المستخدم بعيدة عن طريقها؛ إن كنت تسجّل دوالك الخاصة، فإن السلوك الموصوف في دليل محرك الصيغ والدوال المخصَّصة غير متأثر. مركزة حالة النقص عمل منفصل، لأن كل واحد من تلك الأجسام الـ280 له دلالات رمز خطأ خاصة به ويجب مراجعتها واحدًا واحدًا لا افتراضها

محرك الحساب الموصوف هنا، وواجهتا المصنَّف كلتاهما، وواجهتا برمجة AutoFilter ورؤية الصف اللتان تغذيانه، جزء من مكوّن HotXLS لجدول بيانات Delphi، الذي يُشحن بمصدر كامل لـDelphi وC++Builder ولا يتطلب تثبيت Excel على الجهاز الذي يشغّله