صيغ المصفوفات في HotXLS: لماذا يضيف Excel علامة @ و #VALUE!

يُقحم Excel 365 علامة @ في صيغة مثل =SUM(A1:B1*{10,100}) ويعرض #VALUE! حين يخزن الملف تلك الصيغة صيغةً عادية، لأن Excel يطبق حينئذٍ التقاطع الضمني القديم على معاملات العوامل كلها. ومنذ v2.384.68 يخزن HotXLS Delphi Component هذه الصيغ ذات عوامل المصفوفات كما يخزنها Excel 365: صيغ مصفوفة ديناميكية بخلية واحدة في XLSX، وصيغ مصفوفة بخلية واحدة في XLS

العَرَض يعبر مراجعة الكود سليماً. خدمتك في Delphi تكتب مصنفاً، ويعيد HotXLS حسابه ويخبئ 210 من أجل =SUM(A1:B1*{10,100})، ثم يفتحه العميل في Excel 16 ليجد في شريط الصيغ =SUM(@A1:B1*@{10,100}) وفي الخلية #VALUE!. لا شيء في الملف مشوّه. والمفقود هو البيانات الوصفية التي تخبر Excel أن الصيغة كُتبت وفق قواعد المصفوفة الديناميكية، ومن دونها يتراجع Excel إلى نموذج التقييم الذي سبق المصفوفات الديناميكية

لماذا يضيف Excel 365 علامة @ إلى صيغة حسبها HotXLS حساباً صائباً؟

يضيف Excel 365 علامة @ لأن الصيغة بلا وسم مصفوفة ديناميكية هي بالتعريف صيغة قديمة، والصيغ القديمة تختزل النطاق متعدد الخلايا إلى خلية واحدة أينما توقع العامل قيمة مفردة. وتلك الاختصلة هي التقاطع الضمني: يأخذ Excel خلية النطاق التي تشارك صفي الصيغة (في نطاق رأسي) أو عمودها (في نطاق أفقي)، وإن لم توجد خلية كهذا الوصف كان الناتج #VALUE!. ويحتفظ Excel 365 بذلك المعنى للصيغ القديمة الطراز ويعرض @ ليُظهر الاختصلة

ضع =SUM(A1:B1*{10,100}) في E5 تصبح القراءة القديمة جلية. ‏A1:B1 نطاق أفقي، والصيغة تجلس في العمود E، والنطاق لا يملك خلية في العمود E، فيكون @A1:B1 هو #VALUE! وتورثه كامل الدالة SUM. ووفق قواعد المصفوفة الديناميكية يضرب النص نفسه عنصراً بعنصر، 1 × 10 + 2 × 100، ويعيد 210. محرك صيغ HotXLS يحسب على الطريقة الديناميكية منذ الإصدارين v2.384.61 و v2.384.63؛ صيغة الملف ببساطة لم تقل ذلك. ومع A1:B2 تحمل 1 و 2 و 3 و 4، فهذه صيغ الاختبار وما يعرضه Excel 16:

الصيغة	نتيجة HotXLS	Excel 16 مخزنةً صيغة عادية	المخزنة منذ v2.384.68
=SUM(A1:B1*{10,100})	210	#VALUE!	مصفوفة ديناميكية، يعرضها Excel بـ 210
=SUM((A1:B2>2)*1)	2	تقاطع ضمني، نتيجة خاطئة أو خطأ	مصفوفة ديناميكية، يعرضها Excel بـ 2
=SUMPRODUCT((A1:B2>2)*1)	2	تقاطع ضمني، نتيجة خاطئة أو خطأ	مصفوفة ديناميكية، يعرضها Excel بـ 2
=MAX(A1:B2-1)	3	تقاطع ضمني، نتيجة خاطئة أو خطأ	مصفوفة ديناميكية، يعرضها Excel بـ 3
=SUM(A1:B2)	10	10	صيغة عادية، بلا تغيير

الصف الأخير يهم بقدر الصفوف الأربعة الأولى. ‏SUM(A1:B2) تمرر نطاقاً مباشرةً إلى وسيط دالة يقبل المراجع، فلا يرى أي عامل نطاقاً متعدد الخلايا قط ولا يمكن لأي تقاطع أن يحدث. ويحفظ Excel 365 نفسه تلك الصيغة صيغةً عادية، ويفعل HotXLS الشيء نفسه

كيف يخزن HotXLS صيغ عوامل المصفوفات في XLSX و XLS

يكتب HotXLS صيغة عامل المصفوفة في XLSX مصفوفةً ديناميكية بخلية واحدة: عنصر <c> يحمل cm="1"، والصيغة هي <f t="array" ref="E5">، وتكتسب الحزمة xl/metadata.xml بنوع بيانات وصفية XLDAPR يحمل امتداده dynamicArrayProperties fDynamic="1". والخاصية cm فهرس يبدأ من واحد داخل كتلة cellMetadata في ذلك الجزء، وسجل XLDAPR الكامن خلفه هو ما يخبر Excel «احسب هذه وفق قواعد المصفوفة الديناميكية». وهي البنية نفسها التي يكتبها Excel 16 حين تكتب الصيغة نفسها وتحفظ، وهي أصلاً كيف ثُبّت التخطيط الهدف من أصل الأمر

في XLS لا يوجد جزء بيانات وصفية، فيستخدم HotXLS البنية الوحيدة التي يملكها BIFF8 لحساب المصفوفات: صيغة مصفوفة بخلية واحدة. تأخذ الخلية سجل FORMULA تدفقه الرمزي PtgExp واحد يشير إلى الخلية ذاتها، يليه سجل ARRAY ‏($0221) يحمل الصيغة المفسرة الحقيقية فوق النطاق ذي الخلية الواحدة. ويكتب Excel 365 صيغ المصفوفة الديناميكية إلى XLS بالطريقة نفسها، فيرى إصدار Excel الأقدم الذي يقرأ الملف صيغة مصفوفة كلاسيكية بـ Ctrl+Shift+Enter

لا واجهة API جديدة في الأمر. يحدث الوسم حين تسند الصيغة عبر واجهة الخلايا المعتادة، في المحركين معاً. وعلى جانب XLSX تلك هي TXLSXCell.Formula:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    Sheet.Cells[1, 1].Value := 1;
    Sheet.Cells[1, 2].Value := 2;
    Sheet.Cells[2, 1].Value := 3;
    Sheet.Cells[2, 2].Value := 4;

    // عامل فوق نطاق أو مصفوفة مضمّنة: يخزن مصفوفة ديناميكية
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // نطاق يمرر مباشرةً إلى دالة: يبقى <f> عادية
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

    if Book.Recalculate = lxOk then
      Writeln(VarToStr(Sheet.Cells[5, 5].Value));   // 210

    // جذر المصفوفة يحفظ نصه دون '=' الابتدائية
    Writeln(Sheet.Cells[5, 5].Formula);              // SUM(A1:B1*{10,100})

    Book.SaveAs('probe.xlsx');   // ‏E5 و E6 تحصلان على cm="1" + t="array"
  finally
    Book.Free;
  end;
end;

بعد التحويل تعيد TXLSXCell.Formula النص دون =، وهي الصورة نفسها التي يخزنها TXLSXRange.SetDynamicArrayFormula، فالكود الذي يقارن سلاسل الصيغ بعد الإسناد عليه توحيد = الابتدائية

يتبع المحرك الكلاسيكي القاعدة نفسها عبر IXLSRange.Formula على خلية واحدة. وإسناد الصيغة يحوّل مسارها داخلياً إلى مسار المصفوفة ذات الخلية الواحدة، فيحوي XLS المحفوظ زوج FORMULA مع ARRAY:

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['A1', 'A1'].Value := 1;
  Sh.Range['B1', 'B1'].Value := 2;
  Sh.Range['A2', 'A2'].Value := 3;
  Sh.Range['B2', 'B2'].Value := 4;

  Sh.Range['E5', 'E5'].Formula := '=SUM(A1:B1*{10,100})';  // سجل ARRAY
  Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)';         // سجل ARRAY
  Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)';           // ‏FORMULA عادية

  Writeln(VarToStr(Sh.Range['E5', 'E5'].Value));   // 210
  Writeln(VarToStr(Sh.Range['E6', 'E6'].Value));   // 3
  Wb.SaveAs('probe.xls');
end;

إذا كنت ترسّي نتيجة متعددة الخلايا لا تجميعاً عددياً مفرّداً، فالواجهات الصريحة هي الأداة الصائبة ما زالت: ‏SetArrayFormula لمستطيل مُقاس سلفاً، كما في صيغ امتداد المصفوفة الديناميكية مع HotXLS، أو TXLSXRange.SetDynamicArrayFormula حين تريد وسم XLSX للمصفوفة الديناميكية على نطاق تقيسه بنفسك. أما المسار الآلي في هذه المقالة فلا يغطي إلا الصيغ المكتوبة في خلية واحدة

أي الصيغ يوسمها HotXLS مصفوفات ديناميكية؟

لا يوسم HotXLS صيغةً إلا حين يملك أحد العوامل شجرة معاملات فرعية تنتج مصفوفة. والفحص يجري على شجرة الصياغة المترجمة، والمعامل ينتج مصفوفة إذا كان نطاقاً متعدد الخلايا، أو ثابت مصفوفة مضمّناً، أو تعبير عامل آخر يملك هو نفسه معاملات كهذه. والأقواس شفافة. والعوامل المحتسبة هي الحسابية (+ - * / ^)، والدمج (&)، والمقارنات الست، وزائد وسالب الأحادي، والنسبة المئوية:

• ‏A1:B1*{10,100} و (A1:B2>2)*1 و --(B1:B2>0) و A1:B2-1 تُوسم أينما ظهرت في الصيغة، بما في ذلك داخل SUMPRODUCT
• ‏SUM(A1:B2) و SUMPRODUCT(A1:A2,{1;10}) لا تُوسمان، لأن النطاق والمصفوفة يدخلان مباشرةً وسيط دالة ولا يلمسهما عامل
• ‏A1*2 أو SUM(A1,B1)*2 لا تُوسمان: المراجع ذات الخلية الواحدة ونواتج الدوال عدديات مفرّدة عند هذا الفحص

ثلاثة حدود هي مقصودة. أولاها أن الوسم لا يحدث إلا حين تُدخل الصيغة عبر الواجهة، أي TXLSXCell.Formula في محرك XLSX وإسناد Formula أو Value على خلية واحدة في المحرك الكلاسيكي. والصيغ المحمّلة من ملف تُكتب كما وُجدت حرفياً، لأن صيغة قديمة من منتج آخر قد تعتمد التقاطع الضمني عمداً. وثانيتها أن النص الذي لا يحوي : ولا { يُتخطى دون ترجمة ثانية. وثالثتها أن الصيغة التي ستتوسع، مثل =A1:B1*2 وحدها، تُوسم مصفوفةً ديناميكية بخلية واحدة ترسا حيث وضعتها. لا يوسّعها HotXLS، وسيمدد Excel الناتج إلى الخلايا المجاورة في أول إعادة حساب

قاعدة المعاملات هذه هي أخت قاعدة أصناف الوسائط المغطاة في التقاطع الضمني للأسماء المعرفة في HotXLS. تلك المقالة عن وسائط الدوال المعلنة بصنف قيمة؛ وهذه عن العوامل، التي تطلب في النموذج القديم قيماً دوماً

ما الذي تغير في محرك الحساب حتى تتطابق النواتج

إصلاح التخزين في v2.384.68 يستند إلى أن محرك صيغ HotXLS كان يعيد قيم Excel 365 أصلاً، وهو ما استغرق عدة إصلاحات أبكر في المحركين. وأبرزها كان SUMPRODUCT: حتى v2.384.61 كان يقبل نطاقين عاديين فأكثر فقط، فكانت SUMPRODUCT((B1:B2>0)*1) و SUMPRODUCT(--(B1:B2>0)) وحتى SUMPRODUCT(B1:B2) ذو الوسيط الواحد تعيد #N/A. يقيّم HotXLS الآن وسائط التعبيرات عنصراً بعنصر بقواعد Excel:

• كل وسيط لا بد أن يكون بالشكل نفسه تماماً، والعددي المفرد يحتسب 1 × 1، وإلا كان الناتج #VALUE!
• قيمة خطأ داخل أي وسيط تعود نتيجةً هي نفسها
• عناصر النص والمنطق تحتسب 0، فما زال يلزم (B1:B2>0)*1 أو -- لتحويل TRUE إلى 1
• الوسائط المكونة كلها من نطاقات عادية تحتفظ بحلقة التدفق الأصلية، فالنطاقات الكبيرة لا تتجسد مصفوفات

عائلة SUM ‏(SUM و COUNT و AVERAGE و MIN و MAX و COUNTA) تستخدم المقيّم العنصري نفسه حين يكون الوسيط تعبير عامل فوق نطاق، فـ =SUM((B1:B2>0)*1) تحتسب الصفين معاً بدل النظر إلى الخلية الأولى وحدها. وجعل v2.384.62 عامل التقاطع بالفراغ يعيد المستطيل المشترك لمرجعين، وبـ #NULL! حين لا يتداخلمان، فتصير =SUM(A1:B2 B1:B2) تساوي 6 لا 2، ويستطيع الناتج أن يغذي وسائط مرجعية مثل ROWS و INDEX. وأضاف v2.384.63 إلى المحلل ثوابت مصفوفة مضمّنة مثل {1,2;3,4} ‏(الفواصل تفصل الأعمدة والفواصل المنقوطة تفصل الصفوف) واتحادات مراجع مثل (A1:B2,D4). والمقارنات العنصرية تعطي العنصر الفارغ نوع الطرف الآخر أيضاً، FALSE مقابل منطقي، بما يطابق قاعدة العدد المفرد من v2.384.53 الموصوفة في سلاسل المقارنة والخلايا الفارغة في HotXLS

var
  V: Variant;
begin
  // ‏Book هو TXLSXWorkbook من المثال الأول؛
  // ورقته النشطة تحمل A1:B2 = 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10، وسيط واحد
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6، النطاق المشترك B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16، التداخل يحتسب مرتين
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1، كانت -1 قبل v2.384.61
end;

يقيّم TXLSXWorkbook.Calculate سلسلة صيغة على الورقة النشطة دون تخزينها، وهي طريقة سريعة لفحص سلوك المحرك. وتحذير واحد بخصوص @ ذاتها: قبِل HotXLS تاريخياً @ بين مرجعين بوصفها تقاطعاً ثنائياً، وهو الآن يقيّم تلك الصورة بدلالات تقاطع حقيقية. أما في Excel 365 فـ @ سابقة أحادية للتقاطع الضمني. لا تكتب @ في نص الصيغة متوقعاً معنى Excel؛ استخدم فراغاً للتقاطع ودع قواعد التخزين أعلاه تتولى دلالات المصفوفة الديناميكية

لماذا رفض Excel فتح الملف أو حسب قيمة خاطئة؟

إقناع Excel بقبول وسم المصفوفة الديناميكية استغرق ثلاثة إصلاحات لن يمسكها أي اختبار دورة ذاتية، لأن HotXLS كان يقرأ ناتجه الخاص صائباً في كل حالة. وكُشف كل واحد منها بفتح ناتج HotXLS في Excel 16 وتبديل متغير واحد في كل مرة:

1. يجب أن يكون GUID الامتداد بأحرف صغيرة كلها. ينبغي أن يكون ext uri في xl/metadata.xml بالضبط {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. كان قالب HotXLS الأقدم يهجئه بأحرف مختلطة، فرد Excel 16 فتح الحزمة كلها، لا الخلية وحدها. والمصنفات المبنية بـ TXLSXRange.SetDynamicArrayFormula قبل v2.384.68 أصابها المشكل نفسه
2. نص جذر المصفوفة لا يحمل = ابتدائية. يبث كاتب XLSX النص المخزن لجذر مصفوفة حرفياً في <f>. لو احتفظت الخلية المحولة بـ = لقرأ العنصر <f t="array" ref="E5">=SUM(...)</f>، وهو ما يرفضه Excel وقت الفتح أيضاً. يقلعه HotXLS أثناء التحويل، ولهذا تعود TXLSXCell.Formula دونه
3. ‏Double(True) تساوي -1 في Delphi. تحويل Variant يتبع اصطلاح COM حيث TRUE تعني جميع البتات مرتفعة، و VarIsNumeric(True) تعيد True كذلك. قبل v2.384.61 كان ذلك يجعل =TRUE*1 تعيد -1 ويسمح بتصنيف عناصر المصفوفة المنطقية أرقاماً، فتخطئ مقارنة مثل (B1:B2>0)=TRUE. يفحص HotXLS الآن varBoolean قبل معاملة Variant عدداً في الحساب العددي المفرد والحساب المصفوفي وتصنيف عناصر المصفوفة، وتحتسب TRUE واحدةً

أصناف المعاملات في BIFF8: تفاصيل مستوى البايتة لمنفذي الصيغ

في BIFF8 يحمل كل رمز معاملٍ صنفَه في بايتة الرمز ذاتها، ويثق Excel بذلك الصنف أكثر من ثقته بهيكل الصيغة. ويعرّف [MS-XLS] الصنف حقل PtgDataType من بتتين في البتتين 5 و 6 من الرمز: 1 للمرجع، 2 للقيمة، 3 للمصفوفة. والبتات الخمس الدنيا تسمي الرمز، فللمرجع المساحي نفسه ثلاث تهجئات:

الرمز	صنف المرجع	صنف القيمة	صنف المصفوفة
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

أخطأ HotXLS في ثلاثة منها في مواضع مختلفة، وأنتج كل خطأ عَرَضاً مميزاً في Excel مع بقاء القراءة العكسية سليمة في HotXLS:

• ثوابت مصفوفة بصنف المرجع. كان المرمّز يختار الصنف من السياق، ووسيطا SUM و ROWS من صنف المرجع، فكُتبت =SUM({1,2}) بـ PtgArray على صورة $20. ويعرض Excel الصيغة كلها =#N/A. ثابت المصفوفة لا يمكن أن يكون مرجعاً أبداً، فمنذ v2.384.63 يكتب HotXLS صنف المصفوفة $60 أينما طلب السياق مرجعاً
• معاملات PtgIsect و PtgUnion بصنف القيمة. كان العاملان الثنائيان يأخذان معاملات بصنف القيمة، وهذا صحيح لـ * لكنه خطأ للعاملين المرجعيين. ومع نطاقات $45 قبل PtgIsect ‏($0F)، قرأ Excel ‏=SUM(A1:B2 B1:B2) على أنها =SUM(@A1:B2 @B1:B2) وأعاد #VALUE!. ومنذ v2.384.62 تُكتب معاملات PtgIsect و PtgUnion ‏($10) بصنف المرجع، ‏$25
• معاملات بصنف القيمة داخل سجل ARRAY. يطبق Excel التقاطع الضمني حتى داخل صيغة مصفوفة حين يكون أحد المعاملات بصنف القيمة. كتب HotXLS ‏$45 هناك، فقيّمت صيغة المصفوفة ذات الخلية الواحدة لـ =SUM(A1:B1*{10,100}) إلى 10 في Excel. ومنذ v2.384.68 يرقّي تدفق رموز سجل ARRAY كل مرجع بصنف القيمة وكل ثابت مصفوفة إلى صنف المصفوفة، ‏$65 و $60، وهو ما يكتبه Excel

القارئ الذي يتجاهل بتات الصنف يعيد دورة الثلاثة كلها بلا مشكلة، فإن كنت تحافظ على كاتب BIFF8 خاص بك، فقارن بتات الصنف لكل رمز معاملٍ مقابل ملف حُفظ من Excel للصيغة نفسها، لا الأرقام الرمزية وحدها

مرجع سريع

• يعرض Excel 365 ‏@ حين يستلم عاملٌ في صيغة عادية غير موسومة نطاقاً متعدد الخلايا أو مصفوفة مضمّنة
• يخزن HotXLS ‏v2.384.68 وما بعدها تلك الصيغ مصفوفات XLSX ديناميكية بخلية واحدة ‏(cm="1" و t="array" وبيانات وصفية XLDAPR) وصيغ مصفوفة XLS بخلية واحدة ‏(FORMULA مع PtgExp يليها ARRAY ‏$0221)
• معاملات العوامل وحدها تحتسب؛ والنطاق الذي يمرر مباشرةً إلى وسيط دالة يبقى صيغة عادية
• الصيغ المدخلة عبر TXLSXCell.Formula أو عبر Formula / Value الكلاسيكية على خلية واحدة وحدها تُوسم؛ والصيغ المحمّلة لا تُمس
• الخلية الجذرية المحولة تعود للقراءة دون = الابتدائية
• يجب أن يكون GUID ‏ext uri للمصفوفة الديناميكية بأحرف صغيرة وإلا رفض Excel الحزمة
• في Delphi تكون Double(True) تساوي -1؛ افحص varBoolean قبل التحويل العددي
• في BIFF8: ثوابت المصفوفة لا تكون صنف مرجع قط، ومعاملات PtgIsect / PtgUnion بصنف المرجع، ومعاملات سجل ARRAY بصنف المصفوفة

يقرأ HotXLS مصنفات XLS و XLSX ويكتبها ويحسبها أصلياً من Delphi و C++Builder، ويخزن صيغ عوامل المصفوفات بحيث يفتحها Excel 365 بالقيم نفسها التي حسبها HotXLS. راجع مكوّن HotXLS Delphi للجداول الحسابية للإصدارات والوثائق وتنزيل النسخة التجريبية