Wildcard Excel HotXLS: COUNTIF, MATCH, DSUM, dan Find

HotXLS Delphi Component membaca string pattern yang sama dengan empat cara berbeda, karena Excel 16 memang begitu. Di COUNTIF dan SUMIF, teks a~b adalah literal kecuali kriterianya juga memuat * atau ?; di mode wildcard MATCH dan XLOOKUP, tilde selalu menjadi escape, jadi a~b menemukan ab; di DSUM dan fungsi database lainnya, teks polos berarti "dimulai dengan"; dan Find whole-cell harus backtrack ke * terakhir. HotXLS mengikuti aturan-aturan hasil pengukuran ini sejak v2.384.52, v2.384.60, dan v2.384.64

Laporan bug di area ini tak pernah menyebut wildcard. Isinya: laporan yang dibangkitkan server menghitung beberapa baris lebih sedikit dari file yang sama yang dihitung ulang di Excel, atau part number yang memuat tilde ditemukan oleh satu formula dan diabaikan oleh formula berikutnya. Penyebabnya adalah matcher yang menganggap sebuah pattern bermakna satu hal di mana pun. Excel tidak bekerja begitu, jadi engine yang hasil ter-cachenya harus sepakat dengan Excel juga tidak bisa. Sebelum v2.384.52, HotXLS menyodorkan setiap kriteria ke file mask bergaya DOS, yang mengenali pattern sehari-hari dengan benar dan secara diam-diam keliru di edge case

Mengapa satu string pattern bermakna empat hal berbeda di Excel?

Satu string pattern bermakna empat hal berbeda karena Excel mewarisi empat aturan pencocokan dari empat fitur dan tak pernah menyatukannya. Fungsi kriteria (COUNTIF, SUMIF, AVERAGEIF, dan keluarga *IFS) memutuskan per kriteria apakah wildcard berlaku sama sekali. Fungsi lookup (MATCH dengan match type 0, XLOOKUP dengan match_mode 2) selalu menerapkannya. Fungsi database (DSUM, DCOUNTA, dan kawan-kawannya) mengikuti Advanced Filter, tempat kata polos berarti prefix. Dialog Find punya mode whole-cell dan partial-nya sendiri. Tabel di bawah mencatat sel mana yang cocok dengan tiap pattern terhadap satu kolom berisi a~b, ab, AB, abc, abcb, a*b, dan axb, dengan setiap fungsi dalam mode default case-insensitive-nya

Pattern	COUNTIF / SUMIF	MATCH(…,0) / XLOOKUP mode 2	Kriteria DSUM	Find, whole cell, wildcards aktif
ab	ab, AB	ab, AB	ab, AB, abc, abcb	ab, AB
a*b	a~b, ab, AB, abcb, a*b, axb	sama seperti COUNTIF	semua entri, abc termasuk	sama seperti COUNTIF
a~b	hanya a~b	ab, AB	ab, AB, abc, abcb	ab, AB
a~*b	hanya a*b	hanya a*b	hanya a*b	hanya a*b
=ab	ab, AB	tidak berlaku	ab, AB	tidak berlaku

Baris a~b adalah baris tempat COUNTIF dan MATCH berselisih, dan part number serta kode hasil ketikan tangan memuat tilde lebih sering daripada dugaan siapa pun. Baris a*b memperlihatkan jebakan lain: abc cocok untuk DSUM tapi tidak untuk COUNTIF, karena fungsi database diam-diam menambahkan *. Entri DSUM untuk ab, a*b, dan =ab datang langsung dari run Excel 16; entri DSUM untuk a~b mengikuti aturan prefix yang sama, karena * tambahan itu mengubah kriteria menjadi pattern wildcard di mana ~b adalah b yang di-escape

Kapan COUNTIF beralih ke mode wildcard?

COUNTIF beralih ke mode wildcard hanya ketika teks kriteria memuat * atau ?, di-escape atau tidak. Tanpa salah satu karakter itu, Excel membandingkan kriteria dengan tiap sel sebagai string utuh, case-insensitive, dan tilde hanyalah tilde, jadi COUNTIF(A1:A7,"a~b") menghitung sel yang benar-benar berisi a~b. Tambahkan satu bintang dan maknanya berbalik: di "a~b*", tilde kini meng-escape b, pattern terbaca sebagai "ab diikuti apa pun", dan sel a~b tak lagi dihitung. HotXLS menerapkan aturan ini di kedua engine sejak v2.384.52, lewat satu criteria matcher di lxCalc yang dipakai bersama oleh COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS, dan fungsi database

Di dalam mode wildcard, aturan escape-nya sama seperti di mana pun di Excel: ~ menjadikan karakter berikutnya literal apa pun bentuknya, jadi ~b berarti b dan ~~ berarti satu tilde, dan tilde di ujung terakhir pattern dibuang, sehingga "a*~" berperilaku seperti "a*". Kurung siku tak pernah istimewa. Kriteria "[x]" menghitung sel yang memuat tiga karakter [x], dan "[a-z]" tak menghitung apa pun pada data biasa. TXLSXWorkbook.Calculate mengevaluasi string formula terhadap active sheet dan mengembalikan Variant, cara tercepat mengetes aturan-aturan ini terhadap data Anda sendiri

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 ... agar total SUMIF menamai barisnya
    end;
    Sheet.Cells[8, 1].Value := 5;                // sebuah angka; A9 tetap kosong

    Show('=COUNTIF(A1:A7,"a~b")');      // 1    tanpa * atau ?: teks polos, sel a~b
    Show('=COUNTIF(A1:A7,"a~b*")');     // 4    mode wildcard: ab, AB, abc, abcb
    Show('=COUNTIF(A1:A7,"a*b")');      // 6    wildcard string utuh, abc dikecualikan
    Show('=SUMIF(A1:A7,"a*b",B1:B7)');  // 119  semua baris kecuali abc (8)
    Show('=COUNTIF(A1:A7,"a~*b")');     // 1    literal a*b
    Show('=COUNTIF(A1:A9,"<>ab")');     // 7    angka 5 dan A9 kosong ikut dihitung
    Show('=COUNTIF(A1:A9,"<>")');       // 8    sel tidak kosong
  finally
    Book.Free;
  end;
end.

Apa yang dihitung oleh "<>text"?

Kriteria "<>text" menghitung setiap sel yang bukan teks itu, dan di Excel 16 itu mencakup angka, boolean, error value, dan sel kosong. "<>" polos adalah pertanyaan yang sama sekali berbeda: ia berarti "bukan sel kosong", jadi ia melewati sel kosong tapi menghitung setiap nilai, termasuk teks kosong yang dikembalikan formula seperti ="". Kode HotXLS lama mengenali sel teks dengan benar tapi bukan angka: ketidaksamaan Variant membuat Delphi mengonversi 'ab' ke angka, konversinya melempar exception, sebuah handler menelannya sebagai "tak cocok", dan sel numerik diam-diam keluar dari hitungan. Sisi sel kosong dari cerita ini, termasuk apa yang setara dengan operand kosong dalam perbandingan biasa, dibahas di cara HotXLS menangani comparison chain, sel kosong, dan SUMIF

Mengapa MATCH menemukan ab saat Anda mencari a~b?

MATCH menemukan ab saat Anda mencari a~b karena MATCH dengan match type 0 dan XLOOKUP dengan match_mode 2 selalu berada di mode wildcard, jadi tilde adalah escape meski pattern tak memuat * atau ?. Excel 16 mengonfirmasinya pada range dua sel berisi a~b dan ab: MATCH("a~b",D1:D2,0) mengembalikan 2, dan pada range yang hanya berisi a~b, panggilan yang sama mengembalikan #N/A. Untuk mencari teks literal a~b, Anda harus menulis "a~~b". Sementara itu, COUNTIF(D1:D2,"a~b") atas dua sel yang sama mengembalikan 1, menghitung sel yang satu lagi. String sama, range sama, sel berlawanan

Itulah kenapa HotXLS menjaga kedua keputusan itu terpisah alih-alih di balik satu entry point "cocokkan pattern". Matchernya sendiri dipakai bersama: sejak v2.384.52, MATCH, XLOOKUP, dan fungsi kriteria menjalankan backtracking matcher yang sama, dengan penanganan escape yang sama dan aturan tilde di ekor yang sama. Yang berbeda adalah gate di depannya. Jalur kriteria menanyakan "apakah teks ini memuat * atau ??" lebih dulu; jalur lookup tak pernah bertanya. Menggabungkan keduanya akan memperbaiki satu keluarga dan merusak keluarga satunya, dan kedua arah diperiksa terhadap nilai Excel 16 di kedua engine. Lookup wildcard juga punya prasyarat sendiri: XLOOKUP menolak pencocokan wildcard yang dikombinasikan dengan mode binary search, aturan yang dijelaskan di panduan HotXLS tentang mode pencarian XLOOKUP dan XMATCH

Bagaimana DSUM dan fungsi database membaca kriteria teks polos?

DSUM dan fungsi database lainnya membaca kriteria teks tanpa awalan =, <, atau > sebagai "dimulai dengan", dengan wildcard tetap aktif. Itu aturan Advanced Filter, dan sengaja berbeda dari COUNTIF. Pengukuran Excel 16 atas kolom Name berisi abc, ab, xab, AB, a~b, dan a*b: kriteria ab mencocokkan abc, ab, dan AB; =ab hanya mencocokkan ab dan AB; <>ab adalah ketidaksamaan seluruh entri; a*b dan a? juga pattern prefix; >ab adalah perbandingan biasa. Sebelum v2.384.64, HotXLS mencocokkan ab secara persis, sehingga DSUM atas data uji itu mengembalikan 10 tempat Excel mengembalikan 11

Perbaikannya harus mengakali condition parser, yang melipat ab maupun =ab ke kondisi kesamaan yang sama. Karena itu HotXLS memeriksa teks kriteria mentah sebelum memercayai kondisi hasil parse: kriteria teks yang karakter pertamanya bukan =, <, atau > ditambah * dan melewati wildcard matcher, dan sisanya mempertahankan perbandingan seluruh entrinya. Satu catatan praktis saat Anda membangun criteria range di kode: di engine XLSX, meng-assign string '=ab' ke TXLSXCell.Value menyimpan teks, sedangkan engine classic TXLSWorkbook mengompilasi nilai yang diawali = sebagai formula kecuali Anda mengawalinya dengan apostrof

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 kriteria di D1
    for i := 0 to High(Criteria) do
    begin
      Sheet.Cells[2, 4].Value := Criteria[i];     // tetap teks di engine XLSX
      Writeln(Criteria[i], ' -> ',
        VarToStr(Book.Calculate('=DSUM(A1:B8,"Val",D1:D2)')));
    end;
    // ab   -> 30   ab, AB, abc, abcb (dimulai dengan)
    // =ab  -> 6    ab, AB (seluruh entri)
    // <>ab -> 121  semuanya kecuali ab dan AB
    // a*b  -> 127  a*b* mencocokkan ketujuhnya, abc termasuk
    // a~*  -> 32   hanya literal a*b
  finally
    Book.Free;
  end;
end;

Satu perbedaan terkait bertahan lebih lama dari perbaikan prefix dan berpengaruh di build lama. Perbandingan teks seperti >ab memakai urutan code point, sementara Excel menaruh tanda baca sebelum huruf, sehingga "a~b">"ab" FALSE di Excel dan dulu TRUE di HotXLS. Sejak v2.384.67, kriteria > dan <, bersama perbandingan teks biasa dan pengurutan, memakai collation word sort Excel di bawah user locale saat ini, dan keduanya kembali sepakat

Mengapa Find whole-cell melewatkan abcb?

Find whole-cell melewatkan abcb karena matchernya berhenti di titik pertama saat pattern habis alih-alih backtrack ke * terakhir. Matcher partial-match di balik Replace segera kembali begitu pattern habis; Find whole-cell memakainya ulang lalu menuntut kecocokan menutupi seluruh sel: a*b terhadap abcb berhenti setelah ab, mengonsumsi 2 dari 4 karakter, dan ditolak. Sejak v2.384.60, matcher whole-cell adalah implementasi terpisah yang memperlakukan "pattern habis, teks belum" sebagai satu ketidakcocokan lagi dan mencoba ulang dari bintang terakhir, sehingga a*b mencocokkan abcb dan a?b*b mencocokkan axbyb, seperti yang dilakukan Find Excel 16 dengan "Match entire cell contents" tercentang

Rilis yang sama mengubah tilde. Find Excel 16, di mode whole-cell maupun partial, memperlakukan ~ sebagai escape untuk karakter berikutnya apa pun: a~b menemukan ab, a~~b menemukan a~b, dan tilde di ekor diabaikan, sehingga q~ berperilaku seperti q. Matcher HotXLS lama hanya mengenali ~*, ~?, dan ~~ sebagai escape, sehingga a~b menemukan teks a~b. Pattern Find berupa satu ~ saja tidak stabil di Excel sendiri, mencocokkan sel mana pun seperti pattern kosong, dan HotXLS tidak meniru itu

Di engine XLSX, pencariannya adalah TXLSXWorksheet.FindText dengan set TXLSXFindOptions: lxfUseWildcards mengaktifkan *, ?, dan ~, lxfWholeCell mensyaratkan seluruh sel cocok, dan lxfMatchCase membuat perbandingan case-sensitive. Tanpa lxfUseWildcards, setiap karakter, termasuk bintang, adalah literal. Find hanya melihat nilai teks; sel numerik dilewati, dan sel formula dilewati kecuali lxfSearchFormulas diset, dalam hal ini teks formula yang dicari. Anchor yang diberikan StartRow dan StartCol bersifat inklusif, jadi loop Find All melangkah satu kolom melewati tiap hit

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 ditolak, abcb backtrack
    if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
      Writeln('a~b  whole cell -> row ', Row);   // 4: ~b adalah b yang di-escape
    if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
      Writeln('a~~b whole cell -> row ', Row);   // 3: ~~ adalah satu tilde literal

    // Partial match, Find All: sel anchor ikut tercakup, jadi melangkah melewati tiap hit
    NextRow := 1;
    NextCol := 1;
    while Sheet.FindText('a*b', Row, Col, [lxfUseWildcards], NextRow, NextCol) do
    begin
      Writeln('a*b  contained in row ', Row);     // baris 1, 2, 3, dan 4
      NextRow := Row;
      NextCol := Col + 1;
    end;

    // Replace wildcard whole-cell hanya menulis ulang literal a~b
    Changed := Sheet.ReplaceText('a~~b', 'a-b', Opts);
    Writeln(Changed, ' cell(s) replaced');         // 1
  finally
    Book.Free;
  end;
end;

Loop partial menemukan keempat baris, termasuk abc, karena di mode partial a*b cukup muncul di suatu tempat di dalam sel. FindTextIn dan ReplaceTextIn menerima opsi yang sama plus jendela FirstRow, FirstCol, LastRow, LastCol, padanan programatik dari pencarian dalam seleksi. Engine classic memaparkan aturan yang sama lewat sebuah overload dengan tiga boolean, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), plus overload ReplaceText yang sepadan, dengan hasil baris dan kolom basis-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;

Apa yang keliru dari matcher DOS-mask lama?

Matcher lama keliru soal karakter spesial, karena DOS file mask adalah bahasa yang berbeda dari wildcard Excel. Sebelum v2.384.52, fungsi kriteria dan fungsi database meneruskan setiap pattern ke MatchesMask, matcher file-mask di unit lxMasks. Sintaksnya tumpang tindih dengan milik Excel untuk kasus umum, itulah kenapa masalahnya tersembunyi, tapi ia menyimpang di tempat data nyata mulai menarik:

• [x] terbaca sebagai character set, sehingga COUNTIF(A1:A10,"[x]") menghitung sel yang berisi x alih-alih teks berkurung, dan "[a-z]" mencocokkan sel satu huruf apa pun
• Tak ada escape tilde, sehingga "a~*b" tak bisa mencocokkan asterisk literal
• Mask yang malformed, seperti kurung yang tak ditutup, melempar exception yang ditelan caller sebagai "tak cocok", mengubah typo di sebuah kriteria menjadi total yang salah secara diam-diam
• Di sisi lookup, MATCH dan XLOOKUP hanya memperlakukan ~*, ~?, dan ~~ sebagai escape, sehingga MATCH("a~b",…,0) menemukan literal a~b alih-alih ab

Kalau workbook Anda hanya pernah memakai * dan ? pada data alfanumerik polos, hasilnya sudah benar dan tidak akan berubah. Kalau isinya memuat kurung, tilde, kolom campuran tipe di bawah "<>text", atau kriteria DSUM yang ditulis sebagai kata polos, menghitung ulangnya dengan v2.384.64 atau lebih baru bisa mengubah total, dan total baru itulah yang ditampilkan Excel. Distingsi yang sama antara bagaimana Excel menyimpan kriteria dan bagaimana ia membandingkannya muncul juga untuk filter tersimpan, dibahas di artikel HotXLS tentang kriteria DOPER AutoFilter BIFF8

Referensi cepat: aturan wildcard Excel di HotXLS

• COUNTIF, SUMIF, AVERAGEIF, dan keluarga *IFS memakai wildcard hanya ketika kriteria memuat * atau ?; jika tidak, mereka membandingkan string utuh secara case-insensitive dan ~ adalah literal (sejak v2.384.52)
• MATCH dengan match type 0 dan XLOOKUP dengan match_mode 2 selalu memakai wildcard, jadi a~b menemukan ab dan literalnya butuh a~~b (sejak v2.384.52)
• Di mode wildcard, ~ meng-escape karakter berikutnya apa pun dan ~ di ekor dibuang; [ dan ] adalah karakter biasa
• "<>text" menghitung angka, boolean, error, dan sel kosong; "<>" polos menghitung sel tak kosong, termasuk hasil =""
• DSUM dan fungsi database lainnya memperlakukan teks polos sebagai "dimulai dengan"; =text dan <>text membandingkan seluruh entri (sejak v2.384.64)
• Find whole-cell dengan lxfUseWildcards dan lxfWholeCell melakukan backtrack, jadi a*b mencocokkan abcb; Find dan Replace memperlakukan ~ sebagai escape untuk karakter apa pun (sejak v2.384.60)
• Urutan teks di kriteria > dan < mengikuti collation word sort Excel, tanda baca sebelum huruf (sejak v2.384.67)

Kompatibilitas Excel di sebuah formula engine sebagian besar adalah edge case seperti ini, diukur terhadap Excel alih-alih ditebak dari dokumentasi. HotXLS mengevaluasi COUNTIF, MATCH, XLOOKUP, DSUM, dan sisa library fungsinya secara native di Delphi dan C++Builder, di engine classic maupun engine XLSX, tanpa Excel terinstal. Detail, edisi, dan unduhan trial ada di halaman komponen spreadsheet Delphi HotXLS