أوضاع البحث الثنائي لـ XLOOKUP وXMATCH في Delphi

يُقيّم HotXLS، مكوّن جداول البيانات الأصلي لـ Delphi وC++Builder، دالتي XLOOKUP وXMATCH عبر نواة بحث مشتركة واحدة. تلك النواة تقبل أربعة أوضاع مطابقة (-1، 0، 1، 2) وأربعة أوضاع بحث (-2، -1، 1، 2)، وتُشغّل نزولًا ثنائيًا لوغاريتميًا كلما كان وضع البحث المطلق يساوي 2، وترفض أي مجموعة أخرى بخطأ صيغة

تقرير الخلل الذي يُرسلك إلى هنا لا يقول أبدًا "وضع البحث". يقول إن المصنّف المُولَّد من الخادم يُظهر رقمًا مختلفًا عن نفس الملف المفتوح في Excel، ربما في أربعة صفوف من تسعة آلاف. تلك الصفوف الأربعة تشترك دائمًا في شيء: مفتاح بحث مكرر، أو تطابق تقريبي اضطر لاختيار جار، أو عمود بحث رتّبه أحدهم حسب عمود مختلف الأسبوع الماضي. دوال البحث هي حيث يتوقف محرك الصيغ عن كونه حسابًا ويبدأ في كونه عقدًا، والعقد يحمل بنودًا لا يقرؤها معظم المستدعين أبدًا

أي أرقام أوضاع تقبلها XLOOKUP فعليًا؟

أربعة بالضبط من كل نوع، ولا شيء آخر. يتحقق HotXLS من match_mode مقابل -1 و0 و1 و2 ومن search_mode مقابل -2 و-1 و1 و2 قبل أن يلمس خلية واحدة، وأي قيمة أخرى تُعيد #VALUE! بدلًا من تقييدها إلى أقرب وضع شرعي. أوضاع المطابقة الأربعة هي 0 للتطابق التام، و-1 للتطابق التام أو الأصغر التالي، و1 للتطابق التام أو الأكبر التالي، و2 لأحرف البدل؛ وأوضاع البحث الأربعة هي 1 لمسح خطي أمامي، و-1 لمسح خطي عكسي، و2 لبحث ثنائي على بيانات تصاعدية، و-2 لبحث ثنائي على بيانات تنازلية. حذفها يختار وضع المطابقة 0 ووضع البحث 1، وهو الاقتران الذي تستخدمه تقريبًا كل صيغة حقيقية. أعداد الوسائط تُضبط بنفس الطريقة: تأخذ XLOOKUP من ثلاث إلى ست وسائط وتأخذ XMATCH من وسيطتين إلى أربع، وأي شيء خارج تلك النطاقات هو #VALUE! قبل بدء التقييم

// Shared by XLOOKUP and XMATCH, before any cell is read
if ((RequestedMatchMode <> -1) and (RequestedMatchMode <> 0) and
    (RequestedMatchMode <> 1) and (RequestedMatchMode <> 2)) or
   ((RequestedSearchMode <> -2) and (RequestedSearchMode <> -1) and
    (RequestedSearchMode <> 1) and (RequestedSearchMode <> 2)) then
begin
  Result := lxErrorValue;          // #VALUE!
  Exit;
end;

if Abs(RequestedSearchMode) = 2 then
begin
  if RequestedMatchMode = 2 then   // wildcards cannot ride a binary descent
  begin
    Result := lxErrorValue;
    Exit;
  end;
  // ... O(log n) descent over the lookup vector
end;

قبل خطوة واحدة هناك فحص أكثر هدوءًا يستحق المعرفة. وسائط الوضع تصل كتعبيرات ورقة عمل، لذا يُحوِّلها HotXLS إلى رقم، ويرفض NaN واللانهاية، ثم يشترط أن يساوي الرقم قيمته المُقرَّبة الخاصة. XLOOKUP(x, A:A, B:B, "none", 0, 1.5) هي #VALUE!، لا وضع بحث 2 متخفٍّ. هذا مهم عندما يأتي الوضع من خلية أنتجها حساب كثيف التقريب، وهو أكثر شيوعًا في المصنّفات المُولَّدة منه في المصنّفات المكتوبة يدويًا

لماذا يُعطي وضع البحث 2 إجابة خاطئة على بيانات غير مرتَّبة؟

لأنه يفعل بالضبط ما طلبته. وضع البحث 2 يُخبر المحرك أن ناقل البحث مُرتَّب بالفعل تصاعديًا، ولا يستطيع بحث ثنائي التحقق من ذلك الادعاء دون مرور O(n) يُدمّر السبب من استخدامه. لذا يثق HotXLS بالمستدعي، ويُنصّف الفترة، ويُعيد أيًا كان ما ينزل إليه النزول. على مدخل غير مرتَّب، الإجابة ليست خطأ، إنها خاطئة بصمت، وهذه مخالفة عقد لا خلل في المحرك

يوثِّق Microsoft نفس عدم التماثل لـ XLOOKUP وXMATCH: الأوضاع الثنائية تتطلب بيانات مرتَّبة وتُنتج نتائج غير صالحة غير ذلك. الفقرة 18.17 من ISO 29500-1، التي تُعرِّف نحو صيغة SpreadsheetML، تحمل وصفَي LOOKUP وVLOOKUP الأقدم بمتطلبهما الخاص للترتيب التصاعدي، وتأتي XLOOKUP وXMATCH بعد ذلك النص بمدة كافية بحيث تسافران في الملف كـ _xlfn.XLOOKUP و_xlfn.XMATCH تحت اتفاقية الدالة المستقبلية. جيل مختلف، نفس الصفقة: المستدعي يوفّر ثابت الترتيب، والمحرك يوفّر اللوغاريتم

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Rates');
    Sheet.Cells[1, 1].Value := 40;  Sheet.Cells[1, 2].Value := 0.10;
    Sheet.Cells[2, 1].Value := 10;  Sheet.Cells[2, 2].Value := 0.25;
    Sheet.Cells[3, 1].Value := 30;  Sheet.Cells[3, 2].Value := 0.15;

    // Forward linear scan: finds key 40 wherever it sits
    Sheet.Cells[5, 1].Formula := 'XLOOKUP(40,A1:A3,B1:B3,"missing",0,1)';
    // Binary ascending: the promise was broken, the key is never visited
    Sheet.Cells[6, 1].Formula := 'XLOOKUP(40,A1:A3,B1:B3,"missing",0,2)';

    Book.SaveAs('lookup-modes.xlsx');
  finally
    Book.Free;
  end;
end;

تتبّع الصيغة الثانية والفشل ميكانيكي تمامًا. النزول يفحص الخلية الوسطى، يقرأ 10، يقرر أن 10 أصغر من 40، يُلغي النصف الأيسر بما فيه الصف الذي يحمل فعليًا 40، يفحص 30، يُلغي مرة أخرى، وتنفد الفترة. يتصرف Excel بنفس الطريقة، وهذا بيت القصيد: إعادة إنتاج الإجابة الخاطئة متطلب توافق، لا مجاملة. فرضية الترتيب أيضًا أكثر صرامة من "أرقام تصاعدية"، لأن المُقارِن يُرتِّب القيم بحسب النوع أولًا، بترتيب الأرقام، ثم النص، ثم القيم المنطقية، ثم قيم الخطأ، ثم الفراغات، ولا يقارن ضمن نوع إلا بعد ذلك. عمود من رموز أجزاء رقمية تحمل ثلاث خلايا منه نصًا بدلًا من ذلك ليس تصاعديًا تحت ذلك المُقارِن مهما بدا على الشاشة، وستُسيء الأوضاع الثنائية قراءته بسعادة

أين تقع المفاتيح المكررة؟

عند طرف حتمي من سلسلة التكرار، وأي طرف يعتمد على وضع البحث لا على الحظ. عندما يصل النزول الثنائي إلى مفتاح متساوٍ تحت وضع البحث 2 يُسجّل الموضع ثم يستمر في التضييق نحو اليسار، لذا النتيجة أدنى فهرس في السلسلة؛ تحت وضع البحث -2، على بيانات تنازلية، يُسجّل الموضع ويُضيّق نحو اليمين، لذا النتيجة أعلى فهرس. الأوضاع الخطية أبسط: وضع البحث 1 يُعيد أول إصابة تقدمًا، ووضع البحث -1 أول إصابة تراجعًا. هذه هي التفصيلة التي تُنتج تباين الأربعة صفوف من الفقرة الافتتاحية، لأن مصنّفًا مفاتيحه فريدة يُعطي إجابات متطابقة تحت الأوضاع الأربعة كلها ويُخفي الفارق عبر كل اختبار كتبته من ملف عينة نظيف. أضف رمز عميل مكررًا واحدًا إلى بيانات الإنتاج وتبدأ الأوضاع بالاختلاف بالضبط على الصفوف التي تكررت: لم يتغيّر شيء في المحرك، فقط توقف المدخل عن كونه مجموعة وأصبح متعدد المجموعة

// A1:A7 holds 1, 3, 5, 5, 5, 7, 9 - ascending, with a run of three
Sheet.Cells[1, 3].Formula := 'XMATCH(5,A1:A7,0,1)';   // 3, first forward hit
Sheet.Cells[2, 3].Formula := 'XMATCH(5,A1:A7,0,-1)';  // 5, first reverse hit
Sheet.Cells[3, 3].Formula := 'XMATCH(5,A1:A7,0,2)';   // 3, lowest index of the run

// B1:B7 holds 9, 7, 5, 5, 5, 3, 1 - descending
Sheet.Cells[4, 3].Formula := 'XMATCH(5,B1:B7,0,-2)';  // 5, highest index of the run

كيف تختار المطابقة التقريبية الوصيف؟

بالاحتفاظ بأفضل مرشّح جنبًا إلى جنب مع بحث المطابقة التامة وإعادته فقط إذا لم تظهر إصابة تامة. يعامل HotXLS match_mode -1 كـ "أكبر قيمة ليست أكبر من الهدف" وmatch_mode 1 كـ "أصغر قيمة ليست أصغر"، وكلاهما يُحلّ عبر المنطقة الممسوحة بأكملها بدلًا من التوقف عند أول جار مقبول. في المسار الثنائي تسقط نفس الفكرة من النزول مجانًا: كل خطوة تتجاوز أو تقصر عن الهدف تُحدّث المرشّح، لذا المرشّح النهائي هو عنصر الحد المجاور للموضع الذي كان سيُدرَج فيه المفتاح

// Linear path: refine the candidate only on a strict improvement
if (RequestedMatchMode = -1) or (RequestedMatchMode = 1) then
begin
  CompareResult := CompareDynamicValues(CurrentValue, RequestedValue);
  if ((RequestedMatchMode = -1) and (CompareResult <= 0) and
      ((CandidateIndex < 0) or
       (CompareDynamicValues(CurrentValue, CandidateValue) > 0))) or
     ((RequestedMatchMode = 1) and (CompareResult >= 0) and
      ((CandidateIndex < 0) or
       (CompareDynamicValues(CurrentValue, CandidateValue) < 0))) then
  begin
    CandidateIndex := ScanIndex;
    CandidateValue := CurrentValue;
  end;
end;

اقرأ الشرط الداخلي بعناية، لأن فض التعادل يعيش هناك. خلية جديدة تستبدل المرشّح القائم فقط عندما تكون أفضل بصرامة، لا عندما تُساويه فحسب، لذا من بين عدة خلايا تحمل نفس قيمة الوصيف، الخلية المحتفَظ بها هي أول واحدة تُصادَف في ترتيب المسح: أدنى فهرس تحت مسح أمامي، وأعلى فهرس تحت مسح عكسي. إذا لم تجد XLOOKUP وXMATCH إصابة تامة ولا جارًا مقبولًا، تلجأ XLOOKUP إلى وسيطها if_not_found عند توفيرها وإلى #N/A عندما لا تكون كذلك، بينما تُنتج XMATCH دائمًا #N/A

لماذا لا يمكن لأحرف البدل والبحث الثنائي التعايش؟

لأن نمط حرف البدل ليس موضعًا في ترتيب. وضع المطابقة 2 يسأل ما إذا كانت خلية تُطابق قناعًا، ومطابقة القناع تُجيب نعم أو لا؛ النزول الثنائي يحتاج إجابة ثلاثية الاتجاهات تُخبره أي نصف يحتفظ به. لا توجد طريقة يمكن الدفاع عنها لسؤال ما إذا كان ACME-* يقع يسار أو يمين خلية معطاة، لذا يرفض HotXLS match_mode 2 مقترنًا بـ search_mode 2 أو -2 مقدمًا بـ #VALUE! بدلًا من تخمين ترتيب وإنتاج هراء يبدو معقولًا. المساران أيضًا يقارنان القيم بشكل مختلف، مما يُعزز الانقسام: المسح الخطي يُقرر التساوي بمقارنة نصية غير حساسة لحالة الأحرف، أو بمطابقة قناع عندما تكون أحرف البدل مفعّلة، بينما يُقرر النزول الثنائي التساوي بسؤال مُقارِن الترتيب عن صفر. هذا متعمَّد وليس حادثة طبقات، إذ لا يجوز للمسار الثنائي استخدام إلا العلاقة التي يتنقل بها فعليًا. إن احتجت أحرف البدل، استخدم وضع البحث 1 أو -1 واقبل التكلفة الخطية، وهي نفس المفاضلة التي صُمّم تتبع الاعتمادية خلف إعادة الحساب التزايدي للحفاظ عليها بعيدًا عن مسارك الحرج

أخطاء الشكل: نطاقات ثنائية الأبعاد ونواقل إرجاع غير متطابقة

تتطلب كلتا الدالتين نطاق بحث أحادي البعد حقيقيًا. إذا امتد النطاق المُقدَّم عبر أكثر من صف وأكثر من عمود في آن واحد، يُعيد HotXLS #VALUE! بدلًا من اختيار محور نيابةً عنك، ونطاق صف واحد أو عمود واحد يُقرأ على طول محوره الطويل. تضيف XLOOKUP قاعدة شكل ثانية: يجب أن يكون نطاق الإرجاع بالضبط بطول نطاق البحث على طول المحور المطابق، لذا بحث عمودي على 500 صف مقترن بنطاق إرجاع من 499 صفًا خطأ، لا خطأ إزاحة واحد يُحلّ بصمت عند الصف الأخير. عندما يكون نطاق الإرجاع أعرض من عمود واحد لبحث عمودي، أو أطول من صف واحد لبحث أفقي، تُعيد XLOOKUP الشريحة المطابقة بأكملها كمصفوفة وتنسكب في الخلايا المجاورة تحت نفس قواعد دوال المصفوفة الديناميكية الأخرى، الموصوفة في مقال نطاقات الانسكاب والمصفوفات الديناميكية. هذا مفيد حقًا لسحب سجل كامل من جدول بصيغة واحدة، وهو أيضًا أسرع طريقة للكتابة فوق عمود كنت تنوي الحفاظ عليه

اختيار وضع عندما لا أحد يراقب الشاشة

يستحق التوليد من جانب الخادم سياسة أكثر صرامة من الاستخدام التفاعلي، لأنه لا يوجد إنسان يلاحظ أن مجموعًا يبدو خاطئًا. الافتراضي القابل للدفاع عنه هو وضع البحث 1 مع وضع المطابقة 0: خطي، تام، مستقل عن الترتيب، ومستحيل إبطاله بإعادة ترتيب ورقة. اذهب إلى وضع البحث 2 فقط حيث أنتج نفس مسار الكود الترتيب أيضًا، في نفس التشغيلة، على نفس العمود، واكتب تلك الاعتمادية بجانب الصيغة، لأن بحثًا ثنائيًا على عمود مُرتَّب بمفتاح مختلف هو أرخص طريقة ممكنة لحساب رقم خاطئ واثق. عندما يكون البحث ساخنًا حقًا والبيانات مرتَّبة حقًا فالعائد حقيقي: يقرأ النزول بترتيب log n خلية بدلًا من n، وكل واحدة من تلك القراءات تمر عبر تحليل كامل لخلية مصنّف، لذا التوفير أكبر مما يوحي به عدد التعليمات

إذا كان شكل المشكلة أقرب إلى قاعدة نطاق منه إلى بحث، فإن استدعاء رد نداء إلى كود Pascal الخاص بك، كما هو مشروح في مقال الدوال المخصصة لورقة العمل، سيتفوق عادةً على أي ترتيب ذكي للدوال المدمجة. تنفيذا XLOOKUP وXMATCH المشروحان هنا يُشحنان مع مكوّن HotXLS القياسي لجداول بيانات Delphi، الذي تحمل صفحة منتجه المرجع الكامل للدوال المدعومة لـ Delphi وC++Builder