صيغ تمدد المصفوفات الديناميكية في Delphi باستخدام HotXLS

يقيم HotXLS صيغ تمدد المصفوفات الديناميكية (dynamic array spill formulas) لـ Excel 365 بشكل أصيل في Delphi و C++Builder: حيث تسلمه دفتر عمل تحتوي خلاياه على XLOOKUP أو FILTER، ويقوم محرك الصيغ الخاص به بحساب مجموعة النتائج وتمديدها (spill) عبر مستطيل المخرجات المثبت، وهي نفس القيم التي قد ينتجها Excel؛ ويصيغ مكون HotXLS لـ Delphi Excel صيغة التمدد كخلية جذرية واحدة تمتلك الحساب بالإضافة إلى كتلة من الخلايا التابعة التي تقرأ منها، وهو ما يمثل الصورة الكاملة التي تحتاجها قبل كتابة سطر كود واحد

ويهمنا هذا النموذج لأن المهمة اليومية ليست مجرد كلام نظري؛ حيث يرسل العميل ملف .xlsx تم بناؤه في Excel 365، وتكون أوراقه مليئة بـ =FILTER(...) و =XLOOKUP(...)، ويتعين على خدمتك إعادة إنتاج نفس الأرقام تمامًا بدون واجهة رسومية، على جهاز لم يتم تثبيت Excel عليه، ثم إما قراءة القيم الممتدة أو كتابة منطقة تمدد جديدة خاصة بك؛ ولقد غيرت المصفوفات الديناميكية عقد الحساب من "صيغة واحدة، خلية واحدة" إلى "صيغة واحدة، مستطيل من الخلايا"، ويتبع محرك HotXLS هذا العقد بدلاً من تزييفه بشبكة موسعة مسبقًا

كيف تقوم بتقييم XLOOKUP و FILTER في Delphi؟

يوجه HotXLS كل دالة مصفوفة ديناميكية من خلال نقطة دخول تقييم واحدة، وهي CalcDynArrayFunc في lxCalc.pas، بحيث تتشارك العائلة بأكملها في مسار كود تمدد واحد؛ والمجموعة المدعومة هي XLOOKUP و FILTER (الأولى والثانية الأصليتان)، وينضم إليهما XMATCH و SORT و UNIQUE و SEQUENCE؛ وترجع كل منها مصفوفة variant ثنائية الأبعاد بدلاً من قيمة مفردة (scalar): حيث ينتج التعبير SEQUENCE(3;2;1;1) شبكة ٣ × ٢، ويعيد SORT ترتيب الصفوف حسب أي عمود مفتاح تصاعديًا أو تنازليًا، ويقلص UNIQUE الصفوف المكررة محتفظًا بالظهور الأول، ويبلغ XMATCH عن الموضع (الذي يبدأ من ١) لقيمة ما تحت نفس أوضاع المطابقة التامة، والبديلة (wildcard)، والأصغر التالي، والأكبر التالي مثل XLOOKUP؛ وعلى عكس دوال التوزيع الإحصائي المفردة، والتي ترجع رقمًا واحدًا لكل استدعاء، تعيد هذه الدوال شكلاً كاملاً

ولحساب دفتر عمل يحمل بالفعل هذه الصيغ، افتحه، واستدعِ Recalculate مرة واحدة، واقرأ الخلايا التي هبط فيها التمدد؛ حيث يمر TXLSXWorkbook.Recalculate عبر رسم بياني للتبعيات ويقيم كل صيغة متسخة بالترتيب الطوبولوجي، بحيث يتم حساب جذر التمدد مرة واحدة وتُكتب عناصره مباشرة في الخلايا التابعة

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  r, c: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('from-customer.xlsx');
    Book.Recalculate;                 // تقييم كل صيغة، بما في ذلك التمدد
    Sheet := Book.Sheets[1];          // الأوراق (Sheets) تبدأ من ١
    // قراءة الكتلة التي تمدد فيها XLOOKUP / FILTER، ولتكن D2:F9.
    for r := 2 to 9 do
      for c := 4 to 6 do
        Writeln(Sheet.Cells[r, c].Text);
  finally
    Book.Free;
  end;
end;

وإذا احتاج دفتر العمل إلى دالة لا يشحنها المحرك، فإن نفس المقيِّم يتيح لك توصيل منطقك الخاص من خلال خطاف دوال ورقة العمل المخصصة، وتكون الدالة المخصصة حرة في إرجاع مصفوفة variant بحيث تتمدد تماماً مثل الدوال المدمجة

تثبيت نطاق التمدد باستخدام SetArrayFormula

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

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('report.xlsx');
    Sheet := Book.Sheets[1];
    // ضبط حجم المستطيل وفقاً للنتيجة: شبكة من ٣ صفوف في عمودين.
    if Sheet.Range['A1:B3'].SetArrayFormula('=SEQUENCE(3;2;1;1)') > 0 then
      Book.Recalculate;               // ملء A1:B3 بالأرقام من ١ إلى ٦ على امتداد الصفوف
    Book.SaveAs('report-out.xlsx');
  finally
    Book.Free;
  end;
end;

ونتيجة التثبيت الصريح هي حد يستحق ذكره بوضوح: لا يقوم HotXLS بتغيير حجم التمدد تلقائياً بالطريقة التي يفعلها برنامج Excel التفاعلي؛ حيث يوسع Excel ويقلص منطقة التمدد مع تغير مدخلاتها ويثير الخطأ #SPILL! عندما تكون الخلايا المستهدفة مشغولة؛ وفي HotXLS، المستطيل الذي تمرره إلى SetArrayFormula هو المستطيل الذي تحصل عليه، ولذلك تضبط حجمه وفقاً للنتيجة التي تتوقعها، وإذا أنتجت دالة صفوفاً أكثر مما ثبتته، فلن تجد الزيادة مكاناً تهبط فيه

بث المصفوفات عنصراً بعنصر عبر المعاملات

يطبق HotXLS المعاملات الحسابية عنصراً بعنصر عندما يكون أي من جانبي العملية مصفوفة؛ ويغطي المساعد ApplyArrayBinaryOp العمليات + و - و * و / و ^ على مصفوفات variant أحادية وثنائية الأبعاد: حيث يتم بث المعامل المفرد (scalar) إلى كل عنصر، ويجب أن تتطابق المصفوفتان في الشكل وإلا ترفض العملية، وينتقل القسمة على الصفر كخطأ بدلاً من الانهيار؛ ومسار الحسابات المفردة لا يلمس، ولذلك لا يتفعل هذا إلا عندما يكون معامل واحد على الأقل مصفوفة حقيقية، مثل نطاق ممتد أو نتيجة دالة مصفوفة؛ وهذا يعني أن صيغة مثل =D2:D13*1.1 مثبتة على عمود تضرب كل عنصر بدوره، والصيغة =D2:D13*E2:E13 تضرب عمودين متطابقي الشكل موضعاً بموضع

// بث مفرد: تحصل كل خلية في التثبيت على D(n) * 1.1.
Sheet.Range['F2:F13'].SetArrayFormula('=D2:D13*1.1');
// تضرب مصفوفتان متطابقتا الشكل عنصراً بعنصر.
Sheet.Range['G2:G13'].SetArrayFormula('=D2:D13*E2:E13');
Book.Recalculate;

ماذا يفعل المعامل @ مقارنة بمصفوفة CSE القديمة؟

يقرأ HotXLS الرمز @ الصريح كمعامل تقاطع المراجع (reference-intersection operator)، وليس كتقاطع ضمني لاختيار الصفوف في Excel الحي؛ وفي المحرك، يعيد التعبير A1:A3 @ B1:B3 الخلية الواحدة التي يتقاطع فيها النطاقان، ويرتبط المعامل بشكل أقوى من ^ وأضعف من %؛ ويرافق ذلك ملاحظة صريحة: شكل التقاطع الضمني المفصول بمسافة لم يتم تحليله إلى رموز بعد، لأن المحلل المعجمي (lexer) لا يزال يتخطى المسافات البيضاء، ولذلك تحصل على التقاطع فقط من خلال رمز @ الصريح

التمييز الأعمق هو بين صيغة مصفوفة CSE القديمة والمصفوفة الديناميكية الحديثة، وفي HotXLS يتدفق كلاهما من خلال نفس آلية التثبيت؛ فصيغة المصفوفة الكلاسيكية كانت عبارة عن كتلة {=...} يتم إدخالها باستخدام Ctrl+Shift+Enter فوق تحديد محدد الحجم مسبقًا، وتتشارك كل خلية في صيغة مجمعة واحدة؛ أما المصفوفة الديناميكية فهي صيغة واحدة تحدد نتيجتها شكلها الخاص؛ ويوحد HotXLS بينهما تحت SetArrayFormula: حيث تحدد المستطيل دائمًا، ويمتلك الجذر الشجرة المجمعة، وتشير الأعضاء إليه، سواء كانت الصيغة تعبير مصفوفة قديم الطراز أو دالة SORT جديدة؛ وما لن تحصل عليه أبدًا هو التموج التلقائي لـ Excel حيث تعيد المنطقة نمو نفسها بصمت، وإبقاء هذا الحد في الاعتبار يمنع طمس النموذجين الذهنيين

أين يتوقف دعم المصفوفات الديناميكية

معرفة الحدود توفر عليك وقتًا طويلاً من تتبع الأخطاء؛ حيث تحترم SORT اتجاهًا لكل مفتاح، وهو تصاعدي افتراضيًا وتنازلي عندما تكون وسيطة الترتيب سالبة؛ واتجاه المقارنة هو مكان يستحق اختبار أعمدة المفاتيح الخاصة بك ضد Excel، لأن المقارنة المعكوسة تعيد الصفوف بالترتيب المعاكس بصمت بدلاً من الفشل؛ وتحتفظ UNIQUE بالظهور الأول لكل صف مميز، وهو ما يؤكده المحرك بفحص الصفوف السابقة بدلاً من الثقة في عدد التكرار الخام؛ ويتم التعرف على الدالة TABLE (جداول بيانات تحليل ماذا لو) بحيث تدور دورة كاملة في الملف، ولكنها تقيم كعنصر نائب لأن نتائج الاستبدال الحقيقية هي القيم المخزنة مؤقتًا التي خزنها Excel بالفعل، ولا يعيد HotXLS تشغيل شبكة ماذا لو

تعد LET هي أشد الحدود التي يجب التنبيه إليها؛ حيث يسجل HotXLS الدالة LET ويحمل مسار تقييم لها، ولكن المحلل يرفض الاسم المجرد الذي ليس اسمًا محددًا بالفعل، ولذلك يفشل التعبير =LET(x;10;x) أثناء التحليل قبل الوصول إلى المقيِّم على الإطلاق؛ وعامل LET على أنها غير مدعومة حتى يكتسب المحلل وعياً بالنطاق لأسماءه المرتبطة؛ وملاحظة قابلية نقل أصغر: تستخدم سلاسل الصيغ في هذه الأمثلة فاصل القائمة للمحرك، وهو الفاصلة المنقوطة، ولذلك طابق أي فاصل يتوقعه بناؤك عند تجميع نص الصيغة؛ ويتم شحن دوال المصفوفات الديناميكية المغطاة هنا، بدءاً من XLOOKUP و FILTER وحتى SORT و UNIQUE و SEQUENCE و XMATCH، في مكون HotXLS لـ Delphi Excel، الذي يسرد مرجع صيغه قائمة الدوال الكاملة وأوضاع الوسيطات المدعومة لكل دالة