wildcard ของ Excel ใน HotXLS: COUNTIF, MATCH, DSUM และ Find

HotXLS Delphi Component อ่านสตริงรูปแบบเดียวกันได้สี่ความหมาย เพราะ Excel 16 ก็ทำแบบนั้น ใน COUNTIF กับ SUMIF ข้อความ a~b เป็นตรงตัวเว้นแต่เกณฑ์ยังมี * หรือ ? ปนอยู่ ในโหมด wildcard ของ MATCH กับ XLOOKUP tilde เป็น escape เสมอ a~b จึงหา ab เจอ ใน DSUM กับฟังก์ชันฐานข้อมูลตัวอื่น ข้อความธรรมดาหมายถึง “เริ่มด้วย” และ Find แบบทั้งเซลล์ต้องย้อน backtrack เข้า * ตัวสุดท้าย HotXLS เดินตามกฎที่วัดมาพวกนี้ตั้งแต่ v2.384.52, v2.384.60 และ v2.384.64

บั๊กรายงานในแถวนี้ไม่เคยพูดถึง wildcard เลย รายงานบอกแค่ว่ารายงานที่เซิร์ฟเวอร์ผลิตนับได้น้อยกว่าสองสามแถวเมื่อเทียบกับไฟล์เดียวกันที่คำนวณใหม่ใน Excel หรือ part number ที่มี tilde ถูกสูตรหนึ่งหาเจอแต่สูตรถัดไปเมิน สาเหตุคือ matcher ที่ตั้งสมมติว่ารูปแบบหนึ่งหมายถึงอย่างเดียวทุกที่ Excel ไม่ได้ทำงานแบบนั้น เอนจินที่ผลแคชต้องตรงกับ Excel จึงทำแบบนั้นไม่ได้เช่นกัน ก่อน v2.384.52 HotXLS ป้อนเกณฑ์ทุกตัวผ่าน file mask สไตล์ DOS ซึ่งเดารูปแบบในชีวิตประจำวันถูก แต่เดา edge case ผิดอย่างเงียบ ๆ

ทำไมสตริงรูปแบบเดียวใน Excel ถึงหมายถึงสี่ความหมาย

สตริงรูปแบบเดียวหมายถึงสี่ความหมาย เพราะ Excel สืบทอดกฎการจับคู่สี่ชุดจากสี่ฟีเจอร์มาแล้วไม่เคยรวมให้เป็นอันเดียว ฟังก์ชันเกณฑ์ (COUNTIF, SUMIF, AVERAGEIF กับตระกูล *IFS) ตัดสินเป็นรายเกณฑ์ว่าจะใช้ wildcard เลยหรือไม่ ฟังก์ชัน lookup (MATCH แบบ match type 0, XLOOKUP แบบ match_mode 2) ใช้ wildcard ตลอด ฟังก์ชันฐานข้อมูล (DSUM, DCOUNTA กับพวก) เดินตาม Advanced Filter ที่คำเปล่า ๆ หนึ่งคือคำนำหน้า ส่วนกล่อง Find มีโหมดทั้งเซลล์กับบางส่วนของตัวเอง ตารางด้านล่างระบุว่าเซลล์ไหนจับคู่กับรูปแบบแต่ละแบบ บนคอลัมน์ที่มี a~b, ab, AB, abc, abcb, a*b กับ axb โดยทุกฟังก์ชันอยู่ในโหมดเริ่มต้นที่ไม่สนตัวพิมพ์

รูปแบบ	COUNTIF / SUMIF	MATCH(…,0) / XLOOKUP mode 2	เกณฑ์ DSUM	Find ทั้งเซลล์ เปิด wildcard
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 ขัดกัน และ part number กับรหัสที่พิมพ์มือมี tilde บ่อยกว่าที่ใครคาด แถว a*b โชว์กับดักอีกอัน: abc จับคู่ได้กับ DSUM แต่ไม่ได้กับ COUNTIF เพราะฟังก์ชันฐานข้อมูลเติม * ให้เงียบ ๆ ค่า DSUM ของ ab, a*b กับ =ab มาจากการรัน Excel 16 ตรง ๆ ส่วนค่า DSUM ของ a~b ตามมาจากกฎคำนำหน้าเดียวกัน เพราะ * ที่ถูกเติมทำให้เกณฑ์กลายเป็นรูปแบบ wildcard ที่ ~b คือ b ที่ถูก escape

COUNTIF สลับเข้าโหมด wildcard เมื่อไร

COUNTIF สลับเข้าโหมด wildcard เฉพาะเมื่อข้อความเกณฑ์มี * หรือ ? ไม่ว่าจะถูก escape หรือไม่ ถ้าไม่มีทั้งสองตัว Excel จะเทียบเกณฑ์กับแต่ละเซลล์เป็นสตริงทั้งสาย ไม่สนตัวพิมพ์ และ tilde ก็เป็นแค่ tilde COUNTIF(A1:A7,"a~b") จึงนับเซลล์ที่ถือ a~b ตรงตัว เติมดาวเพียงตัวเดียวความหมายพลิกทันที ใน "a~b*" tilde กลายเป็น escape ของ b รูปแบบอ่านว่า “ab ตามด้วยอะไรก็ได้” และเซลล์ a~b ไม่ถูกนับอีก HotXLS ใช้กฎนี้ในทั้งสองเอนจินตั้งแต่ v2.384.52 ผ่าน criteria matcher ตัวเดียวใน lxCalc ที่แชร์กันระหว่าง COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS กับฟังก์ชันฐานข้อมูล

ในโหมด wildcard กฎ escape เหมือนกับทุกที่ใน Excel: ~ ทำให้อักขระถัดไปเป็นตรงตัวไม่ว่าจะตัวไหน ~b หมายถึง b ~~ หมายถึง tilde หนึ่งตัว และ tilde ที่อยู่สุดท้ายของรูปแบบถูกทิ้ง "a*~" จึงทำงานเท่ากับ "a*" ส่วนวงเล็บเหลี่ยมไม่มีวันพิเศษ เกณฑ์ "[x]" นับเซลล์ที่ถือสามอักขระ [x] และ "[a-z]" นับไม่ได้เลยบนข้อมูลธรรมดา TXLSXWorkbook.Calculate ประเมินสตริงสูตรบน active sheet แล้วคืน 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    โหมด wildcard: ab, AB, abc, abcb
    Show('=COUNTIF(A1:A7,"a*b")');      // 6    wildcard ทั้งสตริง ไม่รวม 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 รวมตัวเลข boolean ค่า error และเซลล์ว่างด้วย ส่วน "<>" เปล่า ๆ เป็นคำถามคนละเรื่องเลย หมายถึง “ไม่ใช่เซลล์ว่าง” ข้ามเซลล์ว่าง แต่นับค่าทุกค่า รวมข้อความว่างที่สูตรอย่าง ="" คืนมา โค้ด HotXLS รุ่นเก่าทำเซลล์ข้อความถูกแต่พลาดตัวเลข: การเทียบไม่เท่าของ Variant บังคับให้ Delphi แปลง 'ab' เป็นตัวเลข การแปลง throw exception ตัว handler กลืนมันไปเป็น “ไม่จับคู่” เซลล์ตัวเลขจึงหลุดจากการนับอย่างเงียบ ๆ เรื่องเซลล์ว่างฝั่งนี้ รวมถึงว่า operand ว่างเท่ากับอะไรในการเปรียบเทียบธรรมดา เล่าไว้ในว่า HotXLS จัดการลูกโซ่การเปรียบเทียบ เซลล์ว่าง และ SUMIF อย่างไร

ทำไม MATCH ถึงหาเจอ ab ตอนคุณค้นหา a~b

MATCH หาเจอ ab ตอนคุณค้นหา a~b เพราะ MATCH แบบ match type 0 กับ XLOOKUP แบบ match_mode 2 อยู่ในโหมด wildcard ตลอด tilde จึงเป็น escape แม้รูปแบบจะไม่มี * หรือ ? เลย Excel 16 ยืนยันเรื่องนี้บนช่วงสองเซลล์ที่ถือ a~b กับ ab: MATCH("a~b",D1:D2,0) คืน 2 และบนช่วงที่ถือแต่ a~b ตัวเดียว call เดียวกันคืน #N/A ถ้าอยาก lookup ข้อความ a~b ตรงตัวต้องเขียน "a~~b" ระหว่างนั้น COUNTIF(D1:D2,"a~b") บนเซลล์สองตัวเดิมคืน 1 โดยนับอีกเซลล์หนึ่ง สตริงเดียวกัน ช่วงเดียวกัน เซลล์ตรงข้ามกันเป๊ะ

นี่คือเหตุที่ HotXLS แยกการตัดสินสองอย่างนี้ไว้คนละจุด แทนที่จะซ่อนหลัง entry point เดียวแบบ “จับคู่รูปแบบ” ตัว matcher เองแชร์กัน: ตั้งแต่ v2.384.52 MATCH, XLOOKUP กับฟังก์ชันเกณฑ์รัน backtracking matcher ตัวเดียวกัน กฎ escape เดียวกัน กฎ tilde ท้ายแบบเดียวกัน ต่างกันที่ประตูด้านหน้า เส้นทางเกณฑ์ถามก่อนว่า “ข้อความนี้มี * หรือ ? ไหม” ส่วนเส้นทาง lookup ไม่เคยถาม รวมสองอย่างเข้าด้วยกันจะแก้ให้ตระกูลหนึ่งแต่พังอีกตระกูล และทั้งสองทิศถูกเช็กกับค่าจาก Excel 16 ในทั้งสองเอนจิน wildcard lookup ยังมีเงื่อนไขเบื้องต้นของตัวเอง: XLOOKUP ปฏิเสธการจับคู่ wildcard ที่ผสมกับโหมด binary search กฎนี้เล่าไว้ในคู่มือโหมดค้นหา XLOOKUP กับ XMATCH ของ HotXLS

DSUM กับฟังก์ชันฐานข้อมูลอ่านเกณฑ์เป็นข้อความธรรมดาอย่างไร

DSUM กับฟังก์ชันฐานข้อมูลตัวอื่นอ่านเกณฑ์ข้อความที่ไม่ขึ้นต้นด้วย =, < หรือ > ว่า “เริ่มด้วย” โดย wildcard ยังทำงานอยู่ นั่นคือกฎของ Advanced Filter และตั้งใจให้ต่างจาก 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

การแก้ต้องวนเลี่ยงตัว parse เงื่อนไข ซึ่งพับทั้ง ab กับ =ab เข้าเงื่อนไขความเท่ากันอันเดียวกัน HotXLS จึงส่องข้อความเกณฑ์ดิบก่อนเชื่อเงื่อนไขที่ parse แล้ว เกณฑ์ข้อความที่อักขระแรกไม่ใช่ =, < หรือ > จะถูกเติม * แล้ววิ่งผ่าน wildcard matcher ส่วนกรณีอื่นคงการเทียบทั้งค่าเดิม ข้อสังเกตทางปฏิบัติตอนสร้างช่วงเกณฑ์ด้วยโค้ด: ในเอนจิน XLSX assign สตริง '=ab' ให้ TXLSXCell.Value เก็บเป็นข้อความ แต่เอนจินคลาสสิก TXLSWorkbook คอมไพล์ค่าที่ขึ้นต้นด้วย = เป็นสูตร เว้นแต่คุณนำหน้าด้วย apostrophe

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 เคยใช้ลำดับ code point ขณะที่ Excel วางเครื่องหมายวรรคตอนไว้หน้าตัวอักษร "a~b">"ab" จึงเป็น FALSE ใน Excel แต่เคยเป็น TRUE ใน HotXLS ตั้งแต่ v2.384.67 เกณฑ์ > กับ < พร้อมการเทียบข้อความธรรมดาและการเรียง ใช้ collation แบบ word sort ของ Excel ใต้ locale ผู้ใช้ปัจจุบัน สองฝ่ายจึงลงรอยกันอีกครั้ง

ทำไม Find แบบทั้งเซลล์ถึงพลาด abcb

Find แบบทั้งเซลล์พลาด abcb เพราะ matcher หยุดที่จุดแรกที่รูปแบบถูกใช้หมด แทนที่จะย้อน backtrack เข้า * ตัวสุดท้าย matcher จับคู่บางส่วนที่อยู่หลัง Replace คืนผลทันทีที่รูปแบบหมด Find ทั้งเซลล์หยิบมันไปใช้ซ้ำแล้วเพิ่มเงื่อนไขว่าการจับคู่ต้องครอบทั้งเซลล์ a*b เทียบ abcb จึงหยุดหลัง ab กินไป 2 จาก 4 ตัวอักษร แล้วโดนปัดทิ้ง ตั้งแต่ v2.384.60 matcher ทั้งเซลล์เป็น implementation แยกที่ถือว่า “รูปแบบหมดแต่ข้อความยังเหลือ” เป็น mismatch อีกหนึ่งกรณี แล้วลองใหม่จากดาวตัวสุดท้าย a*b จึงจับคู่ abcb ได้ และ a?b*b จับคู่ axbyb ได้ เหมือนที่ Excel 16 Find ทำเมื่อติ๊ก “Match entire cell contents”

รีลีสเดียวกันเปลี่ยนเรื่อง tilde ด้วย Excel 16 Find ทั้งโหมดทั้งเซลล์และบางส่วนถือ ~ เป็น escape ของอักขระถัดไปไม่ว่าตัวไหน: a~b หา ab เจอ a~~b หา a~b เจอ และ tilde ท้ายถูกเมิน q~ จึงทำงานเท่ากับ q matcher ของ HotXLS รุ่นเก่ารู้จัก escape แค่ ~*, ~? กับ ~~ a~b จึงหาเจอข้อความ a~b ส่วนรูปแบบ Find ที่เป็น ~ เดี่ยว ๆ นั้นไม่เสถียรแม้ในตัว Excel จับคู่ได้ทุกเซลล์เหมือนรูปแบบว่าง HotXLS ไม่เลียนแบบพฤติกรรมนั้น

ในเอนจิน XLSX การค้นหาคือ TXLSXWorksheet.FindText พร้อมชุด TXLSXFindOptions: lxfUseWildcards เปิด *, ? กับ ~, lxfWholeCell บังคับให้ทั้งเซลล์ต้องจับคู่ได้ และ lxfMatchCase ทำให้การเทียบสนตัวพิมพ์ ถ้าไม่มี lxfUseWildcards ทุกอักขระ รวมดาว เป็นตรงตัวหมด Find มองแต่ค่าข้อความเท่านั้น เซลล์ตัวเลขถูกข้าม เซลล์สูตรก็ถูกข้ามเว้นแต่ตั้ง lxfSearchFormulas ซึ่งงั้นจะค้นข้อความสูตรแทน จุด anchor ที่กำหนดด้วย 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 ย้อน backtrack
    if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
      Writeln('a~b  whole cell -> row ', Row);   // 4: ~b คือ b ที่ถูก escape
    if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
      Writeln('a~~b whole cell -> row ', Row);   // 3: ~~ คือ tilde ตรงตัวหนึ่งตัว

    // จับคู่บางส่วน Find All: เซลล์ anchor ถูกรวมด้วย จึงก้าวข้ามจุดที่เจอแต่ละจุด
    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;

    // replace แบบ wildcard ทั้งเซลล์เขียนทับเฉพาะ 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 รับ option ชุดเดิมบวกหน้าต่าง FirstRow, FirstCol, LastRow, LastCol เทียบเท่ากับการค้นหาภายใน selection แบบเขียนโค้ด เอนจินคลาสสิกเปิดกฎชุดเดียวกันผ่าน overload ที่รับ boolean สามตัว TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell) พร้อม overload ของ 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;

matcher แบบ DOS mask ตัวเก่าพลาดอะไรไปบ้าง

matcher ตัวเก่าอ่านอักขระพิเศษผิด เพราะ file mask ของ DOS เป็นภาษาคนละภาษากับ wildcard ของ Excel ก่อน v2.384.52 ฟังก์ชันเกณฑ์กับฟังก์ชันฐานข้อมูลส่งรูปแบบทุกตัวไปให้ MatchesMask file-mask matcher ในยูนิต lxMasks ไวยากรณ์ของมันทับกับของ Excel ในกรณีทั่วไป ปัญหาจึงซ่อนอยู่ได้ แต่มันแยกทางตรงที่ข้อมูลจริงเริ่มสนุก:

• [x] ถูกอ่านเป็นชุดอักขระ COUNTIF(A1:A10,"[x]") จึงนับเซลล์ที่ถือ x แทนที่จะนับข้อความที่มีวงเล็บ และ "[a-z]" จับคู่เซลล์ที่มีตัวอักษรเดียวได้ทุกเซลล์
• ไม่มี escape ด้วย tilde "a~*b" จึงจับคู่ดาวตรงตัวไม่ได้
• mask ที่เขียนไม่สมบูรณ์อย่างวงเล็บไม่ปิด throw exception ที่ผู้เรียกกลืนไปเป็น “ไม่จับคู่” พิมพ์ผิดในเกณฑ์หนึ่งครั้งจึงกลายเป็นยอดรวมที่ผิดอย่างเงียบ ๆ
• ฝั่ง lookup MATCH กับ XLOOKUP ถือเฉพาะ ~*, ~? กับ ~~ เป็น escape MATCH("a~b",…,0) จึงหาเจอ a~b ตรงตัวแทนที่จะเป็น ab

ถ้า workbook ของคุณเคยใช้แต่ * กับ ? บนข้อมูลตัวอักษร-ตัวเลขธรรมดา ผลเดิมถูกอยู่แล้วและจะไม่เปลี่ยน ถ้ามีวงเล็บ tilde คอลัมน์ชนิดผสมใต้เกณฑ์ "<>text" หรือเกณฑ์ DSUM ที่เขียนเป็นคำเปล่า ๆ คำนวณใหม่ด้วย v2.384.64 ขึ้นไปอาจเปลี่ยนยอดรวม และยอดใหม่นั่นแหละคือยอดที่ Excel แสดง เรื่องต่างระหว่างว่า Excel เก็บเกณฑ์อย่างไรกับเทียบอย่างไรก็มาเยือนใน saved filter เช่นกัน เล่าไว้ในบทความ HotXLS เรื่องเกณฑ์ DOPER ของ BIFF8 AutoFilter

สรุป: กฎ wildcard ของ Excel ใน HotXLS

• COUNTIF, SUMIF, AVERAGEIF กับตระกูล *IFS ใช้ wildcard เมื่อเกณฑ์มี * หรือ ? เท่านั้น ไม่งั้นเทียบสตริงทั้งสายแบบไม่สนตัวพิมพ์ และ ~ เป็นตรงตัว (ตั้งแต่ v2.384.52)
• MATCH แบบ match type 0 กับ XLOOKUP แบบ match_mode 2 ใช้ wildcard ตลอด a~b จึงหา ab เจอ และอยากได้ตรงตัวต้องใช้ a~~b (ตั้งแต่ v2.384.52)
• ในโหมด wildcard ~ escape อักขระถัดไปทุกตัวและ ~ ท้ายถูกทิ้ง [ กับ ] เป็นอักขระธรรมดา
• "<>text" นับตัวเลข boolean ค่า error และเซลล์ว่าง "<>" เปล่า ๆ นับเซลล์ที่ไม่ว่าง รวมผล ="" ด้วย
• DSUM กับฟังก์ชันฐานข้อมูลตัวอื่นถือข้อความธรรมดาว่า “เริ่มด้วย” =text กับ <>text เทียบทั้งค่า (ตั้งแต่ v2.384.64)
• Find ทั้งเซลล์ที่มี lxfUseWildcards กับ lxfWholeCell ย้อน backtrack ได้ a*b จึงจับคู่ abcb ได้ และ Find กับ Replace ถือ ~ เป็น escape ของทุกอักขระ (ตั้งแต่ v2.384.60)
• ลำดับข้อความในเกณฑ์ > กับ < ตาม collation แบบ word sort ของ Excel เครื่องหมายวรรคตอนมาก่อนตัวอักษร (ตั้งแต่ v2.384.67)

ความเข้ากันได้กับ Excel ในเอนจินสูตรส่วนใหญ่คือ edge case พวกนี้แหละ วัดกับตัว Excel จริง ไม่ใช่เดาจากเอกสาร HotXLS ประเมิน COUNTIF, MATCH, XLOOKUP, DSUM กับไลบรารีฟังก์ชันที่เหลือใน Delphi กับ C++Builder โดยตรง ทั้งเอนจินคลาสสิกและเอนจิน XLSX โดยไม่ต้องติดตั้ง Excel รายละเอียด รุ่น และดาวน์โหลดรุ่นทดลองอยู่ที่หน้า HotXLS Delphi spreadsheet component