مقارنة النصوص في HotXLS: ترتيب فرز كلمات Excel في Delphi

يقارن HotXLS Delphi Component قيمتين نصيتين كما يفعل Excel 16 منذ v2.384.67: دون اعتبار لحالة الأحرف، وبترتيب «فرز الكلمات» الخاص بالإعدادات الإقليمية لمستخدم Windows، وهو ما تعيده CompareStringW بعلم NORM_IGNORECASE. وتُتخطى الشرطة والفاصلة العليا في المرور الأول، ولا دور لهما إلا في فصل التعادل، فتكون ="a-b">"ab" ‏TRUE، بينما ترتب بقية علامات الترقيم قبل الأرقام والحروف، فتكون ="a~b"<"ab" ‏TRUE أيضاً. والترتيب نفسه يقود الآن معاملات المقارنة، ومعايير > / <، وفرز النطاقات، و VLOOKUP

لا أحد يفتح بلاغ عيب بعنوان «عدم توافق الترتيب». البلاغات تقول إن COUNTIF(A:A,">M") تعدّ صفاً إضافيين على الخادم مقارنةً بـ Excel، وأن قائمة أسعار فرزتها خدمة التقارير وضعت X-100 في موضع لا يضعه فيه Excel، أو أن VLOOKUP("ABC",...) تعيد #N/A رغم أن العمود يضم abc بوضوح. والثلاثة كلها ينبع من السؤال نفسه: حين يكون كلا المعاملين نصاً، أيهما أصغر؟ لدى Excel جواب دقيق، وهو ليس الجواب الذي يعطيه أغلب أكواد Delphi، وقبل v2.384.67 كان HotXLS يعطي ثلاثة أجوبة مختلفة بحسب مسار الكود الذي يسأل

أي قاعدة يستخدم Excel لمقارنة سلسلتين نصيتين؟

يقارن Excel النصوص بفرز الكلمات الخاص بإعدادات المستخدم الإقليمية، متجاهلاً الحالة. وفرز الكلمات هو الترتيب الافتراضي لدوال المقارنة في Windows NLS: تقارن الحروف بترتيبها اللغوي لا بنقاط ترميزها، وتجلس الحروف المنقوطة بجانب حروفها الأساسية، ويحصل محرفان على معاملة خاصة. فالشرطة - والفاصلة العليا ' تُتجاهلان في المرور الأول، فتجاور co-op و coop كل منهما الأخرى، ولا يقرر حضورهما الترتيب إلا حين يتعادل ما عداهما من السلسلتين. وكل علامة ترقيم أخرى ذات معنى وترتب قبل الأرقام، والأرقام ترتب قبل الحروف

يعرض الجدول ما يعنيه ذلك عملياً، إلى جانب المقارنتين اللتين يلجأ إليهما مطور Delphi غالباً. ويحمل عمود Excel الأحكام التي أعادها Excel 16 لـ IF(A<B,...)، وهي التي يعيد إنتاجها HotXLS منذ v2.384.67

A مقابل B	Excel 16 / HotXLS	‏CompareStr (ترتيبي)	CompareText
"a-b" مقابل "ab"	أكبر	أصغر	أصغر
"a'b" مقابل "ab"	أكبر	أصغر	أصغر
"a~b" مقابل "ab"	أصغر	أكبر	أكبر
"a_b" مقابل "ab"	أصغر	أصغر	أكبر
"ab" مقابل "AB"	متساويان	أكبر	متساويان
"é" مقابل "f"	أصغر	أكبر	أكبر
"Z" مقابل "f"	أكبر	أصغر	أكبر

نتيجتان يسهل فواتهما. أولاها أن دور الشرطة في فصل التعادل يعني أن ="a-b"="ab" ‏FALSE: السلسلتان جارتان متلاصقتان في الترتيب، ومع ذلك غير متساويتين. وثانيتها أن المساواة تتجاهل الحالة كلياً، فـ ab و AB و Ab مفتاح واحد عند أي مقارنة. وفرز 20 كلمة اختبارية بـ Range.Sort الخاص بـ Excel يعطي a b و a.b و a_b و a~b و a0 و a1b و ab / AB / Ab و ab- و a'b و a-b و -ab و ab1 و abc و b و e و é و f و Z؛ وداخل مجموعة ab يحسم موضع المحرف المتجاهَل الترتيب

كيف ثُبّت ترتيب نصوص Excel؟

عُرف ترتيب نصوص Excel بالقياس لا بالتوثيق، لأن توثيق Excel لا يسمي الترتيب. ولّد الاختبار 4,000 زوج سلاسل عشوائي من علامات ترقيم ASCII والأرقام وحالتي الحرف والمسافات و é و ß و ä ومحارف صينية وصور عريضة والمسافة غير الفاصلة، بأطوال من 0 إلى 4، وبنصف الأزواج مبني على هيئة شبه مطابقات. قيّم Excel 16 ‏IF(A<B,-1,IF(A=B,0,1)) لكل زوج، وقورنت الأحكام بواجهة المقارنة في Windows بمجموعات أعلام مختلفة

• ‏NORM_IGNORECASE وحده ‏(فرز الكلمات الافتراضي بإعدادات المستخدم): لا اختلاف حقيقي. الاختلافات السبع الوحيدة كانت خلايا محتواها كله '، التي يستهلكها Excel بوصفها محرف سابقة النص، فكانت شواذ عينة لا اختلافات ترتيب
• ‏NORM_IGNORECASE مع SORT_STRINGSORT: ‏41 اختلافاً. يعامل فرز السلاسل الشرطة والفاصلة العليا رمزين عاديين، وهذا بالضبط السلوك الذي لا يملكه Excel
• إضافة NORM_IGNOREWIDTH: خطأ بطريقة أخرى، لأنه يجعل صورتي الحرف العريضة والضيقة تتساويان، و Excel يفصل بينهما

وفحص ثانٍ منتقى يدوياً قارن الأزواج الـ 190 كلها المستمدة من 20 كلمة صعبة مع نتيجة Range.Sort الخاصة بـ Excel على العمود نفسه. وافق كلاهما فرزَ الكلمات الصريح بـ NORM_IGNORECASE، وأصبحت تلك الأحكام الـ 190 مع الترتيب المصنف جزءاً من منظومة انحدار HotXLS، تُشغَّل عبر محرك TXLSWorkbook الكلاسيكي ومحرك TXLSXWorkbook الأصلي لـ XLSX معاً

لماذا تخطئ CompareText والمقارنة الترتيبية؟

تخطئ CompareText والمقارنة الترتيبية ترتيبَ Excel لأنها تقارن وحدات UTF-16 الشيفرة، وترتيب نقاط الشيفرة يضع علامات الترقيم في مواضع اعتباطية بالنسبة إلى الحروف. الشرطة U+002D والفاصلة العليا U+0027، وكلاهما تحت كل حرف، فتدعي المقارنة الترتيبية أن "a-b" أصغر من "ab" بدل معاملة الشرطة فاصلةً للتعادل. والمدّة U+007E فوق كل حرف، فيخرج "a~b" أكبر، عكس Excel. و CompareText في مكتبة Delphi RTL توحّد الحالة لـ a..z فقط ثم تقارن وحدات الشيفرة، وهذا يضيف تشويهاً ثانياً: الشرطة السفلية U+005F تقع بين الحروف الكبيرة والصغيرة، فتوحيد الحالة إلى الكبير ينقل "a_b" من تحت "ab" إلى فوقها. ولا إحدى الدالتين تعرف أن é تجلس بين e و f

أدوات Delphi المعتادة تقع على الجانبين معاً:

• ‏CompareStr ومعامل < على السلاسل و TComparer<string>.Default ‏(التي تستدعي CompareStr) ترتيبية وحساسة للحالة، فـ TArray.Sort<string> دون مستقارن يضع Z قبل f
• ‏CompareText و SameText ترتيبيتان بعد توحيد حالة ASCII فقط
• ‏AnsiCompareText و WideCompareText في Delphi RTL على ويندوز تستدعيان CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...)، الاستدعاء نفسه الذي يطابق Excel. و TStringList المصنف بإعداداته الافتراضية ‏(UseLocale ‏True و CaseSensitive ‏False) يمر عبر AnsiCompareText فيوافق Excel كذلك
• على أهداف POSIX يحوّل Delphi RTL ‏AnsiCompareText عبر مُرتِّب ICU، وهي خوارزمية مختلفة بقواعد ترقيم مختلفة، و AnsiCompareText في Free Pascal على ويندوز يستدعي CompareStringA بعد التحويل إلى صفحة الشيفرة ANSI، فيفقد أي محرف لا تمثلها تلك الصفحة

فالدوال الواعية بالإعدادات الإقليمية في RTL صائبة على ويندوز بحكم التنفيذ لا بحكم العقد، والكود الذي يحتاج ترتيب Excel خيرٌ له أن يجري استدعاء الواجهة صراحةً. كان لدى HotXLS الخليط نفسه داخلياً: معاملات المقارنة وحّدت حالة السلسلتين وقارنت نقاط الشيفرة، وفروع > / < لدوال المعايير استخدمت مقارنة Variant الحساسة للحالة في Delphi، و VLOOKUP / HLOOKUP طابقتا النص بتلك المقارنة الحساسة للحالة أيضاً، ولهذا لم تستطع VLOOKUP("ABC",A1:A20,1,FALSE) إيجاد abc. أما فرز النطاقات فكان يستخدم WideCompareText أصلاً. ثلاثة مسارات، ثلاثة ترتيبات

ما الذي تغير في HotXLS ‏v2.384.67؟

منذ v2.384.67 تمر مقارنات النص-مقابل-النص في مسارات الحساب والفرز في HotXLS عبر دالة واحدة، XlsCompareText في lxStandard.pas، تستدعي CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) وتطرح CSTR_EQUAL. والمستدعون هم معاملات المقارنة الستة، والمقارنات العنصرية في صيغ المصفوفات، وفروع > و < و >= و <= لمعايير نمط COUNTIF ودوال قواعد البيانات، و VLOOKUP و HLOOKUP ‏(التام والتقريبي)، ومساعدات الترتيب خلف دوال المصفوفة الديناميكية و XLOOKUP / XMATCH، وفرز النطاقات في المحركين. وتوجيه فرز النطاقات عبر الدالة نفسها يضمن ألا ينفرقا ترتيب الفرز وترتيب المقارنة مجدداً، وهذا مهم لأن VLOOKUP التقريبي على النص لا معنى له إلا حين يُفرز العمود بالترتيب الذي يقارن فيه البحث

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // يحسب Calculate على الورقة النشطة
    Writeln(VarToStr(Book.Calculate('="a-b">"ab"')));   // True: الشرطة تفصل التعادل فقط
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False: حُسم التعادل، وليستا متساويتين
    Writeln(VarToStr(Book.Calculate('="a~b"<"ab"')));   // True: علامات الترقيم أولاً
    Writeln(VarToStr(Book.Calculate('="ABC"="abc"')));  // True: تجاهل الحالة
  finally
    Book.Free;
  end;
end;

المقارنات عبر الأنواع قاعدة مستقلة ولم تتغير: كل رقم تحت كل قيمة نصية وكل قيمة نصية تحت كل قيمة منطقية، كما في مقالة سلاسل المقارنة والمعاملات الفارغة و SUMIF. وفرز الكلمات لا يطبق إلا حين يكون كلا المعاملين نصاً. ومطابقة أحرف البدل مستقلة أيضاً: المعيار مثل "a*" أو "=ab" اختبار نمط أو مساواة، مغطى في دليل أحرف البدل في Excel لـ COUNTIF و MATCH و DSUM، والترتيب الذي تناقشه هذه المقالة يحسم معاملات الترتيب وحدها

المثال التالي يحمل الكلمات الاختبارية العشرين في عمود، ويفرزه بـ TXLSXWorksheet.SortRange، ويفحص عدّ معيار وبحثاً. والأعداد هي التي أعادها Excel 16 للعمود نفسه

const
  Words: array [0..19] of string = ('ab', 'a-b', 'a~b', 'a_b', 'AB', 'a b',
    'ab1', 'ab-', '-ab', 'abc', 'a''b', 'Ab', 'b', 'a.b', 'a1b', 'a0',
    #$00E9, 'e', 'f', 'Z');
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  i: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Words');
    for i := 0 to High(Words) do
      Sheet.Cells[i + 1, 1].Value := WideString(Words[i]);

    // ‏Excel 16 على العمود نفسه: 11، 11، 14
    Writeln(VarToStr(Book.Calculate('=COUNTIF(A1:A20,">ab")')));
    Writeln(VarToStr(Book.Calculate('=COUNTIF(A1:A20,"<a-b")')));
    Writeln(VarToStr(Book.Calculate('=COUNTIF(A1:A20,">=AB")')));

    // كانت #N/A قبل v2.384.67: كان البحث يقارن حساساً للحالة
    Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
    Book.Recalculate;
    Writeln(VarToStr(Sheet.Cells[1, 3].Value));                // abc

    // عمود مفتاح واحد، تصاعدي: a b, a.b, a_b, a~b, a0, a1b, ab, AB, Ab, ...
    Sheet.SortRange(1, 1, 20, 1, [1], [False]);
    for i := 1 to 20 do
      Writeln(VarToStr(Sheet.Cells[i, 1].Value));
  finally
    Book.Free;
  end;
end;

يستخدم TXLSXWorksheet.SortRange فرز دمج مستقراً، فتبقى ab و AB و Ab، المتساوية في المقارنة، بترتيبها النسبي السابق على الفرز. وتذهب الخلايا الفارغة إلى النهاية في الاتجاهين، كما في Excel

كيف أمطئ ترتيب Excel في كود Delphi الخاص بي؟

لمطابقة ترتيب نص Excel في كودك استدعِ CompareStringW بـ LOCALE_USER_DEFAULT و NORM_IGNORECASE، ولا تضف SORT_STRINGSORT ولا NORM_IGNOREWIDTH. والقيمة المعادة ليست نتيجة مقارنة إشارية: تعيد الواجهة CSTR_LESS_THAN ‏(1) أو CSTR_EQUAL ‏(2) أو CSTR_GREATER_THAN ‏(3)، وتعيد 0 عند إخفاق الاستدعاء. اطرح 2 لتحصل على اصطلاح سالب / صفر / موجب المعتاد، وافحص الصفر أولاً، لأن إخفاقاً حُسب نتيجةً يصير -2، «أصغر من» بصمت

uses
  Winapi.Windows, System.SysUtils, System.Generics.Defaults,
  System.Generics.Collections;

// ترتيب نص Excel: فرز كلمات بإعدادات المستخدم الإقليمية، بتجاهل الحالة
function ExcelCompareText(const A, B: string): Integer;
var
  R: Integer;
begin
  R := CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE,
    PWideChar(A), Length(A), PWideChar(B), Length(B));
  if R = 0 then
    RaiseLastOSError;          // الصفر إخفاق لا نتيجة مقارنة
  Result := R - CSTR_EQUAL;    // ‏1/2/3 تصير -1/0/1
end;

var
  Keys: TArray<string>;
begin
  Keys := ['abc', 'a-b', 'AB', 'a~b', '-ab', 'ab'];
  TArray.Sort<string>(Keys, TComparer<string>.Construct(
    function(const L, R: string): Integer
    begin
      Result := ExcelCompareText(L, R);
    end));
  // ‏a~b, ab / AB (متساويتان، بأي ترتيب), a-b, -ab, abc
end;

‏TArray.Sort غير مستقر، فالمفاتيح المتساوية في المقارنة، مثل ab و AB، قد تخرج بأي ترتيب؛ وإن كان الترتيب الأصلي للمفاتيح المتساوية مهماً فرتب مصفوفة فهارس بالموضع الأصلي مفتاحاً ثانوياً. وتقوم الحالة المعاكسة أيضاً: أحياناً لا يجب أن يتبع عمودٌ ترتيبَ Excel، كأرقام قطع يكون فيها X-100 و X100 شفرتين مختلفتين ينبغي أن تترتبا بنقطة الشيفرة. وله TXLSXWorksheet.SortRange تحميل زائد يأخذ TXLSSortCompareEvent، طريقةً بتوقيع function(const Left, Right: Variant): Integer of object، ويستخدمها بدل المقارنة المدمجة

uses
  System.SysUtils, System.Variants, lxStandard, lxHandleX;

type
  TPartNumberOrder = class
    function Compare(const Left, Right: Variant): Integer;
  end;

function TPartNumberOrder.Compare(const Left, Right: Variant): Integer;
begin
  // المستقارن المخصص يستلم الخلايا الفارغة أيضاً (بصيغة Null): ضعها بنفسك
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // ترتيبي، حساس للحالة
end;

var
  Sheet: TXLSXWorksheet;   // ورقة معبأة، الصفوف 2..501 والأعمدة A..D
  Order: TPartNumberOrder;
begin
  // ...
  Order := TPartNumberOrder.Create;
  try
    // بالمفتاح على العمود A، تصاعدي
    Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
      xlsSortExcelLike, Order.Compare);
  finally
    Order.Free;
  end;
end;

حين يزوَّد مستقارن مخصص يتخطى HotXLS معالجته للخلايا الفارغة ويمرر قيم المفاتيح الخام، فعلى المستقارن أن يتدبر Null. وللمفتاح التنازلي تنفي HotXLS ما يعيده المستقارن، وهذا ينقل الفارغات إلى القمة أيضاً ما لم يراعِ المستقارن ذلك. واحفظ أن العمود المفرز هكذا لم يعد بالترتيب الذي يتوقعه VLOOKUP التقريبي في Excel أو XLOOKUP بالبحث الثنائي؛ ومزالق تلك الأوضاع على بيانات مفرزة بترتيب مختلف مغطاة في دليل وضعي البحث الثنائي في XLOOKUP و XMATCH

لماذا قد يفرز المصنف نفسه فرزاً مختلفاً على جهاز آخر؟

قد يفرز المصنف نفسه فرزاً مختلفاً على جهاز آخر لأن ترتيب نص Excel يعتمد على الإعدادات الإقليمية لمستخدم Windows، ويتعمد HotXLS اتباع ذلك الاعتماد. فرز الكلمات خاص بلغة: الترتيب السويدي مثلًا يضع ä بعد z، حيث تبقيه الإنجليزية والألمانية بجانب a. ويرث Excel ذلك من الإعدادات الإقليمية التي يعمل تحتها، فيمكن لمصنف أعاد حسابه زميلٌ في ستوكهولم أن يعيد COUNTIF(...,">y") مختلفاً عن الملف نفسه على حسب مكتب في شيكاغو. يمرر HotXLS ‏LOCALE_USER_DEFAULT حتى تساوي نتائجه نتائج Excel على الجهاز نفسه؛ وأي إعدادات إقليمية مثبتة ستجعل HotXLS يخالف Excel على كل جهاز بإعداد مختلف

ثلاث نتائج عملية تترتب على التوليد من جهة الخادم:

• الإعدادات الإقليمية العدودة هي الخاصة بالحساب الذي تشتغل فيه العملية. قد تستخدم خدمة Windows أو تجمع تطبيقات IIS صيغة إقليمية مختلفة عن سطح مكتب المطور، فما يُلاحظ في بيئة التطوير ليس بالضرورة ما يحسبه الإنتاج
• نتائج الصيغ المخبأة المكتوبة في الملف تعكس إعدادات جهاز التوليد الإقليمية. ويعيد Excel الحساب بإعداداته الخاصة، فقد تتغير قيمة حين يفتح الملف في مكان آخر ويعاد حسابه؛ وذلك سلوك Excel لا شذوذ من HotXLS
• تختلف الإعدادات الإقليمية غالباً في الحروف المنقوطة، وفي تركيبات الحروف التي تعاملها بعض اللغات حرفاً واحداً، وفي الكتابات غير اللاتينية، فبيانات اختبار محصورة بكلمات إنجليزية عادية لن تكشف المشكلة

حد المنصة بسيط. ‏HotXLS مكتبة ويندوز، مبنية لـ Win32 و Win64 بـ Delphi و C++Builder ولأهداف win32 / win64 بـ Lazarus و Free Pascal، وكل هذه البناءات تستدعي CompareStringW نفسها. لا يوجد مسار ترتيب منفصل لغير ويندوز. والبديل الوحيد هو إخفاق استدعاء الواجهة: إذا أعادت CompareStringW ‏0 قارنت XlsCompareText السلسلتين الموحدتَي الحالة بوحدة شيفرة بدل رفع استثناء في منتصف إعادة حساب، فيبقى الحساب جارياً لكنه لا يضمن ترتيب Excel بعد الآن

مرجع سريع: مقارنة نصوص Excel في HotXLS

• القاعدة: فرز كلمات بإعدادات المستخدم مع NORM_IGNORECASE، بلا SORT_STRINGSORT، بلا NORM_IGNOREWIDTH، في HotXLS منذ v2.384.67
• ‏- و ' تفصلان التعادل فقط: ‏="a-b">"ab" ‏TRUE و ="a-b"="ab" ‏FALSE
• بقية علامات الترقيم ترتب قبل الأرقام والأرقام قبل الحروف: ‏="a~b"<"ab" و ="a0"<"ab" ‏TRUE
• الحالة لا تهم قط: ‏="ABC"="abc" ‏TRUE و VLOOKUP("ABC",...) تجد abc
• المسارات المغطاة: معاملات المقارنة، ومقارنات المصفوفات، ومعايير > / <، و VLOOKUP / HLOOKUP، وترتيب المصفوفة الديناميكية، و SortRange في المحركين
• ما لا تغطيه هذه القاعدة: الأنواع المختلطة ‏(رقم < نص < منطقي) ومعايير أحرف البدل، ولكلٍّ قواعده الخاصة
• في كود Delphi: ‏CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...)، افحص الصفر، واطرح CSTR_EQUAL؛ وتجنب CompareText و CompareStr و TComparer<string>.Default حين يجب أن يوافق الناتج Excel
• النتائج تعتمد على إعدادات الحساب المشغِّل للكود الإقليمية، في Excel وفي HotXLS على السواء

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