Wildcard Excel HotXLS: COUNTIF, MATCH, DSUM và Find

HotXLS Delphi Component đọc cùng một chuỗi pattern theo bốn cách khác nhau, vì Excel 16 làm vậy. Trong COUNTIF và SUMIF, text a~b là nguyên văn trừ khi tiêu chí còn chứa * hay ?; trong MATCH và XLOOKUP chế độ wildcard, dấu ngã luôn là escape, nên a~b tìm thấy ab; trong DSUM và các hàm database khác, text trơn nghĩa là "bắt đầu bằng"; và Find cả ô phải backtrack về * cuối. HotXLS theo các luật đo đạc được này kể từ v2.384.52, v2.384.60 và v2.384.64

Các bug report trong vùng này chẳng bao giờ nhắc tới wildcard. Chúng nói rằng một report sinh trên server đếm ít hơn vài hàng so với cùng tệp được tính lại trong Excel, hay rằng một part number chứa dấu ngã được formula này tìm thấy và bị formula kế bỏ qua. Nguyên nhân là một bộ khớp giả định pattern có một nghĩa duy nhất ở mọi nơi. Excel không làm việc kiểu đó, nên một engine mà kết quả cache phải khớp với Excel cũng không thể vậy. Trước v2.384.52, HotXLS đẩy mọi tiêu chí qua một file mask kiểu DOS, thứ cho kết quả đúng với các pattern thường ngày và sai lặng lẽ với các edge case

Vì sao một chuỗi pattern mang bốn nghĩa khác nhau trong Excel?

Một chuỗi pattern mang bốn nghĩa khác nhau vì Excel kế thừa bốn luật khớp từ bốn tính năng và chưa bao giờ thống nhất chúng. Các hàm tiêu chí (COUNTIF, SUMIF, AVERAGEIF và nhà *IFS) quyết định theo từng tiêu chí xem wildcard có được áp dụng hay không. Các hàm lookup (MATCH với match type 0, XLOOKUP với match_mode 2) luôn áp dụng chúng. Các hàm database (DSUM, DCOUNTA và bạn bè) đi theo Advanced Filter, nơi một từ trơn là một tiền tố. Hộp thoại Find có riêng chế độ cả ô và chế độ một phần. Bảng dưới liệt kê ô nào khớp với mỗi pattern trên một cột chứa a~b, ab, AB, abc, abcb, a*b và axb, với mọi hàm ở chế độ mặc định không phân biệt chữ hoa thường

Pattern	COUNTIF / SUMIF	MATCH(…,0) / XLOOKUP mode 2	Tiêu chí DSUM	Find, cả ô, bật wildcard
ab	ab, AB	ab, AB	ab, AB, abc, abcb	ab, AB
a*b	a~b, ab, AB, abcb, a*b, axb	như COUNTIF	mọi entry, kể cả abc	như COUNTIF
a~b	chỉ a~b	ab, AB	ab, AB, abc, abcb	ab, AB
a~*b	chỉ a*b	chỉ a*b	chỉ a*b	chỉ a*b
=ab	ab, AB	không áp dụng	ab, AB	không áp dụng

Hàng a~b là hàng mà COUNTIF và MATCH bất đồng, và part number cùng mã gõ tay chứa dấu ngã nhiều hơn ai tưởng. Hàng a*b phơi ra cái bẫy kia: abc khớp với DSUM nhưng không với COUNTIF, vì hàm database lặng lẽ gắn thêm một *. Các entry DSUM cho ab, a*b và =ab lấy thẳng từ các lần chạy Excel 16; entry DSUM cho a~b suy ra từ cùng luật tiền tố, vì * gắn thêm biến tiêu chí thành một pattern wildcard mà trong đó ~b là một b đã escape

COUNTIF khi nào chuyển sang chế độ wildcard?

COUNTIF chỉ chuyển sang chế độ wildcard khi text tiêu chí chứa * hay ?, escape hay không. Thiếu một trong hai ký tự đó, Excel so tiêu chí với từng ô như một chuỗi nguyên vẹn, không phân biệt chữ hoa thường, và dấu ngã chỉ là dấu ngã, nên COUNTIF(A1:A7,"a~b") đếm ô đúng nghĩa đen chứa a~b. Thêm một ngôi sao vào là nghĩa lật ngược: trong "a~b*", dấu ngã giờ escape chữ b, pattern đọc thành "ab theo sau bởi bất cứ gì", và ô a~b không còn được đếm. HotXLS áp luật này ở cả hai engine kể từ v2.384.52, qua một bộ khớp tiêu chí duy nhất trong lxCalc dùng chung cho COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS và các hàm database

Bên trong chế độ wildcard, luật escape giống mọi nơi khác trong Excel: ~ làm ký tự kế tiếp nguyên văn bất kể nó là gì, nên ~b nghĩa là b và ~~ nghĩa là một dấu ngã, còn dấu ngã ở tận cuối pattern bị bỏ, nên "a*~" hành xử như "a*". Dấu ngoặc vuông không bao giờ đặc biệt. Một tiêu chí "[x]" đếm các ô chứa đúng ba ký tự [x], và "[a-z]" chẳng đếm gì trên dữ liệu thường. TXLSXWorkbook.Calculate đánh giá một chuỗi công thức trên sheet active rồi trả về một Variant, cách nhanh nhất để thử các luật này với dữ liệu của bạn

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 ... để tổng SUMIF chỉ tên các hàng của nó
    end;
    Sheet.Cells[8, 1].Value := 5;                // một số; A9 vẫn trống

    Show('=COUNTIF(A1:A7,"a~b")');      // 1    không có * hay ?: text thường, ô a~b
    Show('=COUNTIF(A1:A7,"a~b*")');     // 4    chế độ wildcard: ab, AB, abc, abcb
    Show('=COUNTIF(A1:A7,"a*b")');      // 6    wildcard cả chuỗi, loại trừ abc
    Show('=SUMIF(A1:A7,"a*b",B1:B7)');  // 119  mọi hàng trừ abc (8)
    Show('=COUNTIF(A1:A7,"a~*b")');     // 1    a*b nguyên văn
    Show('=COUNTIF(A1:A9,"<>ab")');     // 7    số 5 và A9 trống đều được đếm
    Show('=COUNTIF(A1:A9,"<>")');       // 8    các ô không trống
  finally
    Book.Free;
  end;
end.

"<>text" đếm cái gì?

Một tiêu chí "<>text" đếm mọi ô không phải text đó, và trong Excel 16 điều đó gồm cả số, boolean, giá trị lỗi và ô trống. Một "<>" trơn là một câu hỏi khác hẳn: nó nghĩa là "không phải ô trống", nên nó nhảy qua ô rỗng nhưng đếm mọi giá trị, kể cả text rỗng mà một công thức như ="" trả về. Code HotXLS cũ cho đúng các ô text nhưng trật với số: một phép so bất đẳng Variant khiến Delphi chuyển 'ab' thành số, phép chuyển ném exception, một handler nuốt nó như "không khớp", và các ô số lặng lẽ rơi khỏi phép đếm. Phía ô trống của câu chuyện này, gồm cả operand rỗng bằng gì trong một phép so thường, được nói trong cách HotXLS xử lý chuỗi so sánh, ô trống và SUMIF

Vì sao MATCH tìm thấy ab khi bạn tìm a~b?

MATCH tìm thấy ab khi bạn tìm a~b vì MATCH với match type 0 và XLOOKUP với match_mode 2 luôn ở chế độ wildcard, nên dấu ngã là escape kể cả khi pattern không chứa * hay ?. Excel 16 xác nhận điều đó trên một range hai ô chứa a~b và ab: MATCH("a~b",D1:D2,0) trả về 2, còn trên range chỉ chứa a~b thì cùng lời gọi trả về #N/A. Muốn tra đúng text a~b, bạn phải viết "a~~b". Trong khi đó COUNTIF(D1:D2,"a~b") trên cùng hai ô đó trả về 1, đếm ô kia. Cùng chuỗi, cùng range, hai ô đối lập

Đó là lý do HotXLS giữ hai quyết định tách biệt thay vì nhét sau một entry point "khớp pattern" duy nhất. Bản thân bộ khớp dùng chung: kể từ v2.384.52, MATCH, XLOOKUP và các hàm tiêu chí chạy cùng một bộ khớp backtracking, cùng cách xử lý escape và cùng luật dấu ngã cuối. Khác là ở cổng đứng trước nó. Đường tiêu chí hỏi trước "text này có chứa * hay ? không?"; đường lookup không bao giờ hỏi. Gộp hai cái lại sẽ sửa được nhà này và làm hỏng nhà kia, và cả hai chiều đều được đối chiếu với giá trị Excel 16 ở cả hai engine. Lookup wildcard còn có điều kiện tiên quyết riêng: XLOOKUP từ chối khớp wildcard ghép với chế độ binary search, một luật được nói trong cẩm nang HotXLS về các chế độ tìm kiếm XLOOKUP và XMATCH

DSUM và các hàm database đọc một tiêu chí text trơn thế nào?

DSUM và các hàm database khác đọc một tiêu chí text không có =, < hay > đứng đầu thành "bắt đầu bằng", với wildcard vẫn hoạt động. Đó là luật Advanced Filter, và nó cố tình khác với COUNTIF. Excel 16 đo trên một cột Name chứa abc, ab, xab, AB, a~b và a*b: tiêu chí ab khớp abc, ab và AB; =ab chỉ khớp ab và AB; <>ab là phép bất đẳng trên cả entry; a*b và a? cũng là pattern tiền tố; >ab là phép so sánh thường. Trước v2.384.64, HotXLS khớp ab chính xác, nên một DSUM trên dữ liệu test đó trả về 10 trong khi Excel trả về 11

Bản sửa phải xoay xở quanh bộ parse điều kiện, thứ gộp cả ab lẫn =ab vào cùng một điều kiện bằng. Vì thế HotXLS soi text tiêu chí thô trước khi tin vào điều kiện đã parse: một tiêu chí text mà ký tự đầu không phải =, < hay > được gắn thêm một * rồi đi qua bộ khớp wildcard, còn mọi thứ khác giữ phép so trên cả entry. Một lưu ý thực dụng khi bạn dựng các range tiêu chí trong code: ở engine XLSX, gán chuỗi '=ab' cho TXLSXCell.Value sẽ lưu thành text, trong khi engine kinh điển TXLSWorkbook biên dịch một giá trị bắt đầu bằng = thành công thức trừ khi bạn thêm dấu nháy đơn phía trước

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';            // header tiêu chí trong D1
    for i := 0 to High(Criteria) do
    begin
      Sheet.Cells[2, 4].Value := Criteria[i];     // giữ là text ở engine XLSX
      Writeln(Criteria[i], ' -> ',
        VarToStr(Book.Calculate('=DSUM(A1:B8,"Val",D1:D2)')));
    end;
    // ab   -> 30   ab, AB, abc, abcb (bắt đầu bằng)
    // =ab  -> 6    ab, AB (cả entry)
    // <>ab -> 121  mọi thứ trừ ab và AB
    // a*b  -> 127  a*b* khớp cả bảy, kể cả abc
    // a~*  -> 32   chỉ a*b nguyên văn
  finally
    Book.Free;
  end;
end;

Một khác biệt liên quan sống sót qua bản sửa tiền tố và vẫn có nghĩa trên các bản dựng cũ. Các phép so text như >ab từng dùng thứ tự code-point, trong khi Excel đặt dấu câu trước chữ cái, nên "a~b">"ab" là FALSE trong Excel và từng là TRUE trong HotXLS. Kể từ v2.384.67, các tiêu chí > và <, cùng phép so text thường và sort, dùng collation word sort của Excel dưới user locale hiện tại, và hai bên lại thống nhất

Vì sao Find cả ô bỏ sót abcb?

Find cả ô bỏ sót abcb vì bộ khớp dừng ở điểm đầu tiên mà pattern bị dùng hết thay vì backtrack về * cuối. Bộ khớp một phần đứng sau Replace trả về ngay khi pattern cạn; Find cả ô tái dùng nó rồi đòi phép khớp phải phủ toàn bộ ô: a*b đối với abcb dừng sau ab, tiêu thụ 2 trên 4 ký tự, và bị loại. Kể từ v2.384.60, bộ khớp cả ô là một bản hiện thực riêng coi "pattern hết, text còn" như một lần không khớp nữa và thử lại từ ngôi sao cuối, nên a*b khớp abcb và a?b*b khớp axbyb, đúng như Excel 16 Find với tùy chọn "Match entire cell contents" được tích

Cùng bản phát hành đó đổi luôn dấu ngã. Excel 16 Find, ở cả chế độ cả ô lẫn một phần, coi ~ là escape cho bất kỳ ký tự nào theo sau: a~b tìm thấy ab, a~~b tìm thấy a~b, và dấu ngã cuối bị bỏ qua, nên q~ hành xử như q. Bộ khớp HotXLS cũ chỉ nhận ~*, ~? và ~~ làm escape, nên a~b tìm thấy đúng text a~b. Một pattern Find chỉ có một ~ thì bất ổn ngay trong bản thân Excel, khớp mọi ô như một pattern rỗng, và HotXLS không bắt chước điều đó

Ở engine XLSX, phép tìm là TXLSXWorksheet.FindText với một bộ TXLSXFindOptions: lxfUseWildcards bật *, ? và ~, lxfWholeCell đòi cả ô phải khớp, và lxfMatchCase khiến phép so phân biệt chữ hoa thường. Thiếu lxfUseWildcards, mọi ký tự, kể cả ngôi sao, đều nguyên văn. Find chỉ nhìn các giá trị text; ô số bị bỏ qua, và ô công thức bị bỏ qua trừ khi đặt lxfSearchFormulas, khi đó text công thức được soi. Mỏ neo do StartRow và StartCol đưa ra được tính luôn, nên vòng Find All phải bước một cột qua từng điểm trúng

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 bị loại, abcb backtrack
    if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
      Writeln('a~b  whole cell -> row ', Row);   // 4: ~b là một b đã escape
    if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
      Writeln('a~~b whole cell -> row ', Row);   // 3: ~~ là một dấu ngã nguyên văn

    // Khớp một phần, Find All: ô neo được tính, nên bước qua từng điểm trúng
    NextRow := 1;
    NextCol := 1;
    while Sheet.FindText('a*b', Row, Col, [lxfUseWildcards], NextRow, NextCol) do
    begin
      Writeln('a*b  contained in row ', Row);     // các hàng 1, 2, 3 và 4
      NextRow := Row;
      NextCol := Col + 1;
    end;

    // Replace wildcard cả ô chỉ viết lại a~b nguyên văn
    Changed := Sheet.ReplaceText('a~~b', 'a-b', Opts);
    Writeln(Changed, ' cell(s) replaced');         // 1
  finally
    Book.Free;
  end;
end;

Vòng một phần tìm thấy cả bốn hàng, kể cả abc, vì ở chế độ một phần a*b chỉ cần xuất hiện ở đâu đó trong ô. FindTextIn và ReplaceTextIn nhận cùng các tùy chọn cộng thêm cửa sổ FirstRow, FirstCol, LastRow, LastCol, tương đương lập trình của việc tìm trong một selection. Engine kinh điển phơi các luật đó qua một overload với ba boolean, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), cộng một overload ReplaceText tương ứng, với kết quả hàng và cột tính từ 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;

Bộ khớp DOS-mask cũ sai ở chỗ nào?

Bộ khớp cũ sai các ký tự đặc biệt, vì một file mask DOS là một ngôn ngữ khác với wildcard Excel. Trước v2.384.52, các hàm tiêu chí và các hàm database đẩy mọi pattern tới MatchesMask, một bộ khớp file mask trong unit lxMasks. Cú pháp của nó chồng lên cú pháp Excel ở các trường hợp phổ thông, đó là lý do vấn đề nấp được, nhưng nó rẽ nhánh đúng nơi dữ liệu thật bắt đầu thú vị:

• [x] được đọc thành một tập ký tự, nên COUNTIF(A1:A10,"[x]") đếm các ô chứa x thay vì text có ngoặc, và "[a-z]" khớp bất kỳ ô một chữ cái nào
• Không có escape bằng dấu ngã, nên "a~*b" không thể khớp một dấu sao nguyên văn
• Một mask dị dạng, ví dụ ngoặc không đóng, ném một exception mà caller nuốt thành "không khớp", biến một lỗi gõ trong tiêu chí thành một tổng sai một cách lặng lẽ
• Phía lookup, MATCH và XLOOKUP chỉ coi ~*, ~? và ~~ làm escape, nên MATCH("a~b",…,0) tìm thấy đúng a~b nguyên văn thay vì ab

Nếu các workbook của bạn chỉ từng dùng * và ? trên dữ liệu alphanumeric trơn, kết quả đã đúng từ trước và sẽ không đổi. Nếu chúng chứa ngoặc, dấu ngã, các cột kiểu hỗn hợp dưới "<>text", hay các tiêu chí DSUM viết thành từ trơn, tính lại bằng v2.384.64 trở lên có thể đổi các tổng, và các tổng mới chính là những gì Excel hiện. Sự phân biệt giữa cách Excel lưu một tiêu chí và cách nó so tiêu chí cũng gặp lại ở các filter đã lưu, được bàn trong bài HotXLS về tiêu chí AutoFilter DOPER trong BIFF8

Tra nhanh: các luật wildcard Excel trong HotXLS

• COUNTIF, SUMIF, AVERAGEIF và nhà *IFS chỉ dùng wildcard khi tiêu chí chứa * hay ?; nếu không chúng so cả chuỗi không phân biệt chữ hoa thường và ~ là nguyên văn (kể từ v2.384.52)
• MATCH với match type 0 và XLOOKUP với match_mode 2 luôn dùng wildcard, nên a~b tìm thấy ab và muốn nguyên văn phải viết a~~b (kể từ v2.384.52)
• Trong chế độ wildcard, ~ escape mọi ký tự kế tiếp và một ~ cuối bị bỏ; [ và ] là ký tự thường
• "<>text" đếm số, boolean, lỗi và ô trống; một "<>" trơn đếm các ô không trống, gồm cả kết quả =""
• DSUM và các hàm database khác coi text trơn là "bắt đầu bằng"; =text và <>text so trên cả entry (kể từ v2.384.64)
• Find cả ô với lxfUseWildcards và lxfWholeCell có backtrack, nên a*b khớp abcb; Find và Replace coi ~ là escape cho mọi ký tự (kể từ v2.384.60)
• Thứ tự text trong các tiêu chí > và < đi theo collation word sort của Excel, dấu câu trước chữ cái (kể từ v2.384.67)

Tương thích Excel trong một engine công thức phần lớn là những edge case thế này, đo trước Excel chứ không đoán từ tài liệu. HotXLS đánh giá COUNTIF, MATCH, XLOOKUP, DSUM và phần còn lại của thư viện hàm thuần nhất trong Delphi và C++Builder, ở cả engine kinh điển lẫn engine XLSX, không cần cài Excel. Chi tiết, các edition và bản dùng thử nằm trên trang HotXLS Delphi spreadsheet component