أحرف البدل في Excel مع HotXLS: COUNTIF و MATCH و DSUM و Find

يقرأ HotXLS Delphi Component سلسلة النمط نفسها بأربع طرق مختلفة، لأن Excel 16 يفعل ذلك. في COUNTIF و SUMIF يكون النص a~b حرفياً ما لم يحوِ المعيار أيضاً * أو ?؛ وفي MATCH و XLOOKUP بوضع أحرف البدل تكون المدّة تهريباً دائماً، فـ a~b تجد ab؛ وفي DSUM ودوال قواعد البيانات الأخرى يعني النص الصريح «يبدأ بـ»؛ ويجب أن يعود Find على مستوى الخلية كاملةً إلى آخر *. يتبع HotXLS هذه القواعد المقيسة منذ v2.384.52 و v2.384.60 و v2.384.64

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

لماذا تعني سلسلة النمط الواحدة أربعة أشياء مختلفة في Excel؟

تعني سلسلة النمط الواحدة أربعة أشياء مختلفة لأن Excel ورث أربع قواعد مطابقة من أربع مزايا ولم يوحّدها قط. دوال المعايير ‏(COUNTIF و SUMIF و AVERAGEIF وعائلة *IFS) تحسم لكل معيار هل تنطبق أحرف البدل أصلاً. ودوال البحث ‏(MATCH بنوع مطابقة 0، و XLOOKUP بـ match_mode 2) تطبقها دوماً. ودوال قواعد البيانات ‏(DSUM و DCOUNTA وأخواتهما) تتبع عامل التصفية المتقدم، حيث تكون الكلمة العارية سابقة. وحوار Find يملك وضعي الخلية الكاملة والجزئي الخاصين به. يسرد الجدول أدناه أي الخلايا تطابق كل نمط مقابل عمود يحوي a~b و ab و AB و abc و abcb و a*b و axb، وكل دالة بوضعها الافتراضي متجاهل الحالة

النمط	COUNTIF / SUMIF	‏MATCH(…,0) / XLOOKUP وضع 2	معيار DSUM	‏Find، خلية كاملة، أحرف بدل مفعلة
ab	ab, AB	ab, AB	ab, AB, abc, abcb	ab, AB
a*b	a~b, ab, AB, abcb, a*b, axb	كما في COUNTIF	كل المدخلات، abc مشمولة	كما في COUNTIF
a~b	a~b وحدها	ab, AB	ab, AB, abc, abcb	ab, AB
a~*b	a*b وحدها	a*b وحدها	a*b وحدها	a*b وحدها
=ab	ab, AB	لا ينطبق	ab, AB	لا ينطبق

صف a~b هو موضع خلاف COUNTIF و MATCH، وأرقام القطع والشيفرات المكتوبة يدوياً تحوي مددات أكثر مما يتوقع أحد. وصف a*b يعرض الفخ الآخر: ‏abc تطابق لـ DSUM ولا تطابق لـ COUNTIF، لأن دالة قواعد البيانات تلحق * بصمت. ومدخلات DSUM لـ ab و a*b و =ab مأخوذة مباشرة من تشغيلات Excel 16؛ أما مدخل DSUM لـ a~b فيتبع قاعدة السابقة نفسها، إذ يحوّل الـ * الملحقة المعيارَ نمطَ أحرف بدل تكون فيه ~b هي b محمية

متى تتحول COUNTIF إلى وضع أحرف البدل؟

لا تتحول COUNTIF إلى وضع أحرف البدل إلا حين يحوِ نص المعيار * أو ?، محميةً كانت أم لا. وفي غياب المحرفين يقارن Excel المعيار بكل خلية سلسلةً كاملة، متجاهلاً الحالة، وتكون المدّة مدّةً فحسب، فيعدّ COUNTIF(A1:A7,"a~b") الخلية التي تحمل a~b حرفياً. أضف نجمةً واحدة ينقلب المعنى: في "a~b*" تحمي المدّة الآن الـ b، ويقرأ النمط «ab يليه أي شيء»، فلم تعد الخلية a~b معدودة. طبّق HotXLS هذه القاعدة في المحركين منذ v2.384.52، عبر مستقارن معايير واحد في lxCalc تتشاركه COUNTIF و SUMIF و AVERAGEIF و COUNTIFS و SUMIFS و AVERAGEIFS ودوال قواعد البيانات

داخل وضع أحرف البدل قواعد التهريب هي نفسها في كل مكان آخر في Excel: تجعل ~ المحرف التالي حرفياً أياً كان، فـ ~b تعني b و ~~ تعني مدّةً واحدة، وتُسقط المدّة في آخر النمط تماماً، فيتصرف "a*~" كأنه "a*". والأقواس المعقوفة ليست خاصة قط. معيار "[x]" يعدّ الخلايا التي تحمل المحارف الثلاثة [x]، و "[a-z]" لا يعدّ شيئاً على بيانات عادية. يقيّم TXLSXWorkbook.Calculate سلسلة صيغة على الورقة النشطة ويعيد Variant، وهي أسرع طريقة لفحص هذه القواعد على بياناتك

uses
  System.Variants, lxHandleX;

const
  Names: array [1..7] of string = ('a~b', 'ab', 'AB', 'abc', 'abcb', 'a*b', 'axb');
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  i: Integer;

  procedure Show(const Formula: string);
  begin
    Writeln(Formula, ' = ', VarToStr(Book.Calculate(Formula)));
  end;

begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    for i := 1 to High(Names) do
    begin
      Sheet.Cells[i, 1].Value := Names[i];
      Sheet.Cells[i, 2].Value := 1 shl (i - 1);  // ‏1, 2, 4 ... فيسمي مجموع SUMIF صفوفه
    end;
    Sheet.Cells[8, 1].Value := 5;                // رقم؛ وتبقى A9 فارغة

    Show('=COUNTIF(A1:A7,"a~b")');      // 1    بلا * أو ?؛ نص صريح، الخلية a~b
    Show('=COUNTIF(A1:A7,"a~b*")');     // 4    وضع أحرف البدل: ab, AB, abc, abcb
    Show('=COUNTIF(A1:A7,"a*b")');      // 6    بدل على السلسلة كلها، abc مستبعدة
    Show('=SUMIF(A1:A7,"a*b",B1:B7)');  // 119  كل الصفوف إلا abc ‏(8)
    Show('=COUNTIF(A1:A7,"a~*b")');     // 1    ‏a*b الحرفية
    Show('=COUNTIF(A1:A9,"<>ab")');     // 7    الرقم 5 و A9 الفارغة تعدان
    Show('=COUNTIF(A1:A9,"<>")');       // 8    الخلايا غير الفارغة
  finally
    Book.Free;
  end;
end.

ماذا يعدّ «<>text»؟

معيار "<>text" يعدّ كل خلية ليست ذلك النص، وفي Excel 16 يشمل ذلك الأرقام والقيم المنطقية وقيم الخطأ والخلايا الفارغة. و "<>" العارية سؤال مختلف تماماً: تعني «ليست خلية فارغة»، فتتخطى الخلايا الفارغة لكنها تعدّ كل قيمة، بما في ذلك النص الفارغ الذي تعيده صيغة مثل ="". حصل كود HotXLS القديم على خلايا النص صحيحة وخطئ في الأرقام: جعلت متباينة Variant في Delphi تحوّل 'ab' رقماً، ورفع التحويل استثناءً، وابتلعه معالج بوصفه «لا تطابق»، فتساقت الخلايا العددية من العدّ بصمت. وجانب الخلية الفارغة من هذه الحكاية، بما فيه ما يساويه معامل فارغ في مقارنة عادية، مغطى في كيف يتولى HotXLS سلاسل المقارنة والخلايا الفارغة و SUMIF

لماذا تجد MATCH الخلية ab حين تبحث عن a~b؟

تجد MATCH الخلية ab حين تبحث عن a~b لأن MATCH بنوع مطابقة 0 و XLOOKUP بـ match_mode 2 في وضع أحرف البدل دوماً، فالمدّة تهريب حتى لو لم يحوِ النمط * أو ?. يؤكد Excel 16 ذلك على نطاق خليتين تحملان a~b و ab: تعيد MATCH("a~b",D1:D2,0) القيمة 2، وعلى نطاق يحمل a~b وحدها تعيد الاستدعاء نفسه #N/A. ولبحث النص الحرفي a~b عليك كتابة "a~~b". وفي الوقت نفسه تعيد COUNTIF(D1:D2,"a~b") على الخليتين نفسيهما القيمة 1، تعدّ الخلية الأخرى. السلسلة نفسها، والنطاق نفسه، والخلية المعاكسة

لهذا يبقي HotXLS القرارين منفصلين بدل إخفائهما خلف مدخل واحد «طابق نمطاً». المستقارن نفسه مشترك: منذ v2.384.52 تشغل MATCH و XLOOKUP ودوال المعايير المستقارن الرجعي نفسه، بمعالجة التهريب نفسها وقاعدة المدّة الختامية نفسها. والمختلف هو البوابة أمامه. يسأل مسار المعايير أولاً «هل يحوي هذا النص * أو ?؟»؛ ولا يسأل مسار البحث قط. دمجهما كان سيصلح عائلةً ويكسر الأخرى، وكلا الاتجاهين مفحوص مقابل قيم Excel 16 في المحركين. وبحوث أحرف البدل لها شرط مسبق خاص بها أيضاً: يرفض XLOOKUP مطابقة أحرف البدل مقرونة بوضع بحث ثنائي، وهي قاعدة موصوفة في دليل HotXLS لوضعي بحث XLOOKUP و XMATCH

كيف تقرأ DSUM ودوال قواعد البيانات معيار نص صريحاً؟

تقرأ DSUM ودوال قواعد البيانات الأخرى معيار النص الخالي من = أو < أو > ابتدائيةً بمعنى «يبدأ بـ»، وأحرف البدل ما زالت نشطة. تلك قاعدة التصفية المتقدم، وتختلف عن COUNTIF عن قصد. قيس Excel 16 على عمود Name يحوي abc و ab و xab و AB و a~b و a*b: المعيار ab يطابق abc و ab و AB؛ و =ab يطابق ab و AB وحدهما؛ و <>ab متباينة مدخل كامل؛ و a*b و a? نمطا سابقة أيضاً؛ و >ab مقارنة عادية. قبل v2.384.64 طابقت HotXLS ‏ab مطابقةً تامة، فعادت DSUM على بيانات الاختبار تلك 10 حيث يعيد Excel 11

كان على الإصلاح أن يتحايل على محلل الشروط، الذي يطوي ab و =ab في شرط المساواة نفسه. يفحص HotXLS لذلك نص المعيار الخام قبل أن يثق بالشرط المُفسَّر: معيار النص الذي لا يبدأ محرفه الأول بـ = أو < أو > يُلحق فيه * ويمر عبر مستقارن أحرف البدل، وكل ما عداه يبقي مقارنته للمدخل الكامل. ملاحظة عملية عند بناء نطاقات معايير في الكود: في محرك XLSX يخزّن إسناد السلسلة '=ab' إلى TXLSXCell.Value نصاً، بينما يجمع محرك TXLSWorkbook الكلاسيكي القيمة التي تبدأ بـ = صيغةً ما لم تسبقها بفاصلة عليا

const
  Names: array [1..7] of string = ('a~b', 'ab', 'AB', 'abc', 'abcb', 'a*b', 'axb');
  Criteria: array [0..4] of string = ('ab', '=ab', '<>ab', 'a*b', 'a~*');
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  i: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Db');
    Sheet.Cells[1, 1].Value := 'Name';
    Sheet.Cells[1, 2].Value := 'Val';
    for i := 1 to High(Names) do
    begin
      Sheet.Cells[i + 1, 1].Value := Names[i];
      Sheet.Cells[i + 1, 2].Value := 1 shl (i - 1);
    end;
    Sheet.Cells[1, 4].Value := 'Name';            // ترويسة المعايير في D1
    for i := 0 to High(Criteria) do
    begin
      Sheet.Cells[2, 4].Value := Criteria[i];     // تبقى نصاً في محرك XLSX
      Writeln(Criteria[i], ' -> ',
        VarToStr(Book.Calculate('=DSUM(A1:B8,"Val",D1:D2)')));
    end;
    // ab   -> 30   ab, AB, abc, abcb ‏(يبدأ بـ)
    // =ab  -> 6    ab, AB ‏(المدخل كله)
    // <>ab -> 121  كل شيء إلا ab و AB
    // a*b  -> 127  ‏a*b* تطابق السبعة كلها، abc مشمولة
    // a~*  -> 32   ‏a*b الحرفية فقط
  finally
    Book.Free;
  end;
end;

اختلاف ذو صلة واحد نجا من إصلاح السابقة وله وزن على البناءات الأقدم. كانت مقارنات النص مثل >ab بترتيب نقاط الشيفرة، بينما يضع Excel علامات الترقيم قبل الحروف، فكانت "a~b">"ab" ‏FALSE في Excel و TRUE في HotXLS. ومنذ v2.384.67 تستخدم معايير > و <، مع المقارنة النصية العادية والفرز، ترتيبَ فرز الكلمات الخاص بـ Excel تحت الإعدادات الإقليمية للمستخدم الحالي، فتوافقا من جديد

لماذا فاتت الخلية abcb من Find على مستوى الخلية كاملة؟

فاتت abcb من Find على مستوى الخلية كاملةً لأن المستقارن توقف عند أول موضع استُهلك فيه النمط بدل العودة إلى آخر *. مستقارن المطابقة الجزئية خلف Replace يعود فور استنفاد النمط؛ وأعاد Find على مستوى الخلية استخدامه ثم اشترط أن يغطي التطابق الخلية بأكملها: توقف a*b أمام abcb بعد ab، فاستهلك محرفين من أربعة ورُفض. ومنذ v2.384.60 أصبح مستقارن الخلية الكاملة تنفيذاً منفصلاً يعامل «انتهى النمط ولم ينته النص» عدمَ تطابقٍ إضافياً ويعيد المحاولة من آخر نجمة، فيطابق a*b الخلية abcb وتطابق a?b*b الخلية axbyb، كما يفعل Find في Excel 16 مع تفعيل «مطابقة محتويات الخلية بأكملها»

غيّر الإصدار نفسه المدّة أيضاً. يعامل Find في Excel 16، في الوضعين الكامل والجزئي معاً، ~ تهريباً لأي محرف يليها: ‏a~b تجد ab، و a~~b تجد a~b، وتُتجاهل المدّة الختامية، فيتصرف q~ كأنه q. كان مستقارن HotXLS الأقدم لا يعترف إلا بـ ~* و ~? و ~~ تهريباً، فوجدت a~b النص a~b. ونمط Find من مدّةٍ واحدة ~ غير مستقر في Excel نفسه، يطابق أي خلية كنمط فارغ، ولا يقلد HotXLS ذلك

في محرك XLSX البحث هو TXLSXWorksheet.FindText مع مجموعة TXLSXFindOptions: يفعّل lxfUseWildcards ‏* و ? و ~، ويشترط lxfWholeCell تطابقَ الخلية كاملةً، ويجعل lxfMatchCase المقارنة حساسةً للحالة. ومن دون lxfUseWildcards يكون كل محرف حرفياً، والنجمة ضمنه. ينظر Find إلى القيم النصية وحدها؛ وتتخطى الخلايا العددية، وتتخطى خلايا الصيغ ما لم يضبط lxfSearchFormulas، حيث يُبحث نص الصيغة. والمرساة المعطاة بـ StartRow و StartCol شاملة، فتخطو حلقة Find All عموداً واحداً بعد كل إصابة

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Row, Col, NextRow, NextCol, Changed: Integer;
  Opts: TXLSXFindOptions;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Parts');
    Sheet.Cells[1, 1].Value := WideString('abc');
    Sheet.Cells[2, 1].Value := WideString('abcb');
    Sheet.Cells[3, 1].Value := WideString('a~b');
    Sheet.Cells[4, 1].Value := WideString('ab');

    Opts := [lxfUseWildcards, lxfWholeCell];
    if Sheet.FindText('a*b', Row, Col, Opts, 1, 1) then
      Writeln('a*b  whole cell -> row ', Row);   // ‏2: رُفضت abc وعادت abcb للخلف
    if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
      Writeln('a~b  whole cell -> row ', Row);   // ‏4: ‏~b هي b محمية
    if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
      Writeln('a~~b whole cell -> row ', Row);   // ‏3: ‏~~ مدّة حرفية واحدة

    // مطابقة جزئية، Find All: الخلية المرساة مشمولة، فتخطَّ كل إصابة
    NextRow := 1;
    NextCol := 1;
    while Sheet.FindText('a*b', Row, Col, [lxfUseWildcards], NextRow, NextCol) do
    begin
      Writeln('a*b  contained in row ', Row);     // الصفوف 1 و 2 و 3 و 4
      NextRow := Row;
      NextCol := Col + 1;
    end;

    // الاستبدال ببدل على الخلية كلها يعيد كتابة a~b الحرفية وحدها
    Changed := Sheet.ReplaceText('a~~b', 'a-b', Opts);
    Writeln(Changed, ' cell(s) replaced');         // 1
  finally
    Book.Free;
  end;
end;

تجد الحلقة الجزئية الصفوف الأربعة كلها، بما فيها abc، لأن a*b في الوضع الجزئي يكفي أن تظهر في مكان ما داخل الخلية. و FindTextIn و ReplaceTextIn تأخذان الخيارات نفسها زائد نافذة FirstRow و FirstCol و LastRow و LastCol، وهي المقابل البرمجي للبحث داخل تحديد. ويعرض المحرك الكلاسيكي القواعد نفسها عبر تحميل زائد بثلاث قيم منطقية، ‏TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell)، مع تحميل زائد مطابق لـ ReplaceText، وبنواتج صفٍ وعمودٍ يبدأ ترقيمهما من 1:

var
  Classic: IXLSWorkbook;
  Sheet: TXLSWorksheet;
  Row, Col: Integer;
begin
  Classic := TXLSWorkbook.Create;
  Sheet := Classic.Sheets.Add;
  Sheet.Range['A1', 'A1'].Value := 'abcb';
  // ‏MatchCase = False و UseWildcards = True و WholeCell = True
  if Sheet.FindText('a*b', Row, Col, False, True, True) then
    Writeln('found at ', Row, ',', Col);           // 1,1
  if not Sheet.FindText('a*c', Row, Col, False, True, True) then
    Writeln('a*c does not cover abcb');
end;

ماذا أخطأ مستقارن أقنعة DOS القديم؟

أخطأ المستقارن القديم في المحارف الخاصة، لأن قناع ملفات DOS لغةٌ مختلفة عن حرف بدل Excel. قبل v2.384.52 كانت دوال المعايير ودوال قواعد البيانات تمرر كل نمط إلى MatchesMask، مستقارن أقنعة ملفات في وحدة lxMasks. تتداخل صياغته مع صياغة Excel في الحالات الشائعة، ولهذا بقي المشكل مخفياً، لكنه ينحرف حيث تصبح البيانات الحقيقية مثيرة:

• قُرئت [x] مجموعة محارف، فعادت COUNTIF(A1:A10,"[x]") تعدّ الخلايا التي تحمل x بدل النص بين قوسين، وطابقت "[a-z]" أي خلية بمحرف واحد
• لم يكن ثمة تهريب بالمدّة، فلم تستطع "a~*b" مطابقة نجمة حرفية
• القناع المشوه، كقوس غير مغلق، رفع استثناءً ابتلعه المستدعي بوصفه «لا تطابق»، فيتحول خطأ مطبعي في معيار إلى مجموع خاطئ بصمت
• وعلى جانب البحث لم تعتبر MATCH و XLOOKUP إلا ~* و ~? و ~~ تهريباً، فوجدت MATCH("a~b",…,0) النص a~b الحرفي بدل ab

إن كانت مصنفاتك استخدمت * و ? على بيانات أبجدية رقمية عادية فحسب، فالنتائج كانت صائبة سلفاً ولن تتغير. وإن احوت أقواساً أو مددات أو أعمدة أنواع مختلطة تحت "<>text" أو معايير DSUM مكتوبة كلماتٍ عارية، فقد تغيرت المجاميع بإعادة حسابها بـ v2.384.64 أو أحدث، والمجاميع الجديدة هي التي يعرضها Excel. ويقوم التمييز نفسه بين كيف يخزن Excel المعيار وكيف يقارنه لفلاتر محفوظة أيضاً، كما في مقالة HotXLS عن معايير DOPER في AutoFilter بـ BIFF8

مرجع سريع: قواعد أحرف البدل في Excel مع HotXLS

• تستخدم COUNTIF و SUMIF و AVERAGEIF وعائلة *IFS أحرف البدل فقط حين يحوي المعيار * أو ?؛ وإلا قارنت السلسلتين كاملتين متجاهلةً الحالة وتكون ~ حرفية ‏(منذ v2.384.52)
• تستخدم MATCH بنوع مطابقة 0 و XLOOKUP بـ match_mode 2 أحرف البدل دوماً، فـ a~b تجد ab والحرفي يحتاج a~~b ‏(منذ v2.384.52)
• في وضع أحرف البدل تحمي ~ أي محرف تالٍ وتُسقط ~ الختامية؛ و [ و ] محرفان عاديان
• يعدّ "<>text" الأرقام والقيم المنطقية والأخطاء والخلايا الفارغة؛ و "<>" العارية تعدّ الخلايا غير الفارغة، ونتائج ="" مشمولة
• تعامل DSUM ودوال قواعد البيانات الأخرى النص الصريح بمعنى «يبدأ بـ»؛ و =text و <>text تقارنان المدخل كله ‏(منذ v2.384.64)
• يعود Find على مستوى الخلية كاملةً بـ lxfUseWildcards و lxfWholeCell إلى الخلف، فيطابق a*b الخلية abcb؛ ويعامل Find و Replace المدّةَ ~ تهريباً لأي محرف ‏(منذ v2.384.60)
• يتبع ترتيب النص في معياري > و < ترتيبَ فرز الكلمات الخاص بـ Excel، وعلامات الترقيم قبل الحروف ‏(منذ v2.384.67)

توافق Excel في محرك صيغ هو غالباً حالات حدية كهذه، مقيسة على Excel لا مخمّنة من التوثيق. يقيّم HotXLS ‏COUNTIF و MATCH و XLOOKUP و DSUM وبقية مكتبة دواله أصلياً في Delphi و C++Builder، في المحرك الكلاسيكي ومحرك XLSX معاً، دون Excel مثبتاً. والتفاصيل والإصدارات وتنزيل النسخة التجريبية في صفحة مكوّن HotXLS Delphi للجداول الحسابية