Excel wildcard у HotXLS: COUNTIF, MATCH, DSUM і Find

HotXLS Delphi Component читає той самий pattern-рядок чотирма різними способами, бо так робить Excel 16. У COUNTIF і SUMIF текст a~b — дослівний, якщо критерій не містить ще й * чи ?; у MATCH та XLOOKUP у wildcard режимі тильда завжди escape, тож a~b знаходить ab; у DSUM та інших database функціях plain text означає «починається з»; а whole-cell Find мусить робити backtracking в останню *. HotXLS слідує цим виміряним правилам від v2.384.52, v2.384.60 і v2.384.64

Bug-звіти в цій галузі ніколи не згадують wildcard. Вони кажуть, що серверний звіт рахує на пару рядків менше, ніж той самий файл, перерахований в Excel, чи що артикул із тильдою знаходиться однією формулою і ігнорується наступною. Причина — matcher, що вважає шаблон однозначним усюди. Excel так не працює, тож engine, чиї кешовані результати мусять збігатися з Excel, теж не може. До v2.384.52 HotXLS проганяв кожен критерій через DOS-style файлову маску, яка робила правильно буденні шаблони і тихо помилялася на edge cases

Чому один pattern-рядок означає чотири різні речі в Excel?

Один pattern-рядок означає чотири різні речі, бо Excel успадкував чотири правила зіставлення з чотирьох фіч і ніколи їх не уніфікував. Критеріальні функції (COUNTIF, SUMIF, AVERAGEIF і родина *IFS) вирішують для кожного критерію, чи застосовувати wildcard узагалі. Lookup-функції (MATCH з match type 0, XLOOKUP з match_mode 2) застосовують їх завжди. Database функції (DSUM, DCOUNTA та друзі) слідують Advanced Filter, де голе слово — префікс. У діалозі Find свої whole-cell і partial режими. Таблиця нижче перелічує, які клітинки збігаються з кожним шаблоном над одним стовпцем, що тримає a~b, ab, AB, abc, abcb, a*b і axb, з кожною функцією в її усталеному case-insensitive режимі

Pattern	COUNTIF / SUMIF	MATCH(…,0) / XLOOKUP mode 2	Критерій DSUM	Find, whole cell, wildcards on
`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 розходяться, а артикули та ручні коди містять тильди частіше, ніж хто завгодно очікує. Рядок a*b показує іншу пастку: abc збігається для DSUM, але не для COUNTIF, бо database функція тихо додає *. Записи DSUM для ab, a*b і =ab походять прямо з прогонів Excel 16; запис DSUM для a~b випливає з того самого префіксного правила, адже доданий * перетворює критерій на wildcard шаблон, у якому ~b — escaped b

Коли COUNTIF перемикається у wildcard режим?

COUNTIF перемикається у wildcard режим лише коли текст критерію містить * чи ?, escaped чи ні. Без жодного з цих символів Excel порівнює критерій із кожною клітинкою як цілий рядок, без урахування регістру, і тильда — просто тильда, тож COUNTIF(A1:A7,"a~b") рахує клітинку, що дослівно тримає a~b. Додайте одну зірку — і значення перевертається: у "a~b*" тильда тепер escape для b, шаблон читається як «ab, за яким що завгодно», і клітинка a~b більше не рахується. HotXLS застосовує це правило в обох engines від v2.384.52 через один criteria matcher у lxCalc, спільний для COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS і database функцій

Діаграма wildcard gate HotXLS: COUNTIF і SUMIF застосовують wildcard лише коли критерій містить зірку чи знак питання, тож a~b рахує дослівну клітинку і повертає 1, а MATCH type 0 і XLOOKUP mode 2 завжди в wildcard режимі, тож a~b знаходить ab на позиції 2 — Gate — вся різниця: COUNTIF питає про зірку чи знак питання, перш ніж трактувати тильду як escape, MATCH не питає ніколи, тож один pattern-рядок рахує одну клітинку і знаходить іншу

Усередині wildcard режиму правила escape ті самі, що всюди в Excel: ~ робить наступний символ дослівним, яким би він не був, тож ~b означає b, а ~~ — одну тильду, і тильда в самому кінці шаблону відкидається, тож "a*~" поводиться як "a*". Квадратні дужки ніколи не спеціальні. Критерій "[x]" рахує клітинки, що тримають три символи [x], а "[a-z]" не рахує нічого на звичайних даних. TXLSXWorkbook.Calculate обчислює рядок формули проти активного 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    без * чи ?: plain text, клітинка a~b
    Show('=COUNTIF(A1:A7,"a~b*")');     // 4    wildcard режим: ab, AB, abc, abcb
    Show('=COUNTIF(A1:A7,"a*b")');      // 6    whole-string 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' у число, конвертація піднімала exception, handler ковтав його як «немає збігу», і числові клітинки тихо випадали з рахунку. Порожньо-клітинковий бік цієї історії, включно з тим, чому дорівнює порожній операнд у звичайному порівнянні, покритий у як HotXLS опікується ланцюжками порівнянь, порожніми клітинками та SUMIF

Чому MATCH знаходить ab, коли ви шукаєте a~b?

MATCH знаходить ab, коли ви шукаєте a~b, бо MATCH з match type 0 і XLOOKUP з match_mode 2 завжди в wildcard режимі, тож тильда — escape навіть коли шаблон не містить * чи ?. Excel 16 підтверджує це на двоклітинковому range з a~b і ab: MATCH("a~b",D1:D2,0) повертає 2, а на range, що тримає лише a~b, той самий виклик повертає #N/A. Щоб знайти дослівний текст a~b, треба написати "a~~b". Тим часом COUNTIF(D1:D2,"a~b") над тими самими двома клітинками повертає 1, рахуючи іншу клітинку. Той самий рядок, той самий range, протилежна клітинка

Саме тому HotXLS тримає ці два рішення окремо, а не за одним входом «зістав шаблон». Сам matcher спільний: від v2.384.52 MATCH, XLOOKUP і критеріальні функції гоняють той самий backtracking matcher, з тією самою обробкою escape і тим самим правилом кінцевої тильди. Відрізняється gate перед ним. Критеріальний шлях спершу питає «чи містить цей текст * чи ??»; lookup шлях не питає ніколи. Злиття обох виправило б одну родину і зламало б іншу, і обидва напрями звіряються зі значеннями Excel 16 в обох engines. Wildcard lookups мають ще власну передумову: XLOOKUP відхиляє wildcard зіставлення, поєднане з binary search режимом, — правило описане в посібнику HotXLS з режимів пошуку XLOOKUP і XMATCH

Як DSUM і database функції читають plain text критерій?

DSUM та інші database функції читають текстовий критерій без початкового =, < чи > як «починається з», з активними 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

Виправленню довелося обходити parser умов, який згортає і ab, і =ab в одну умову рівності. Тому HotXLS оглядає сирий текст критерію, перш ніж довіряти розпарсеній умові: текстовий критерій, у якого перший символ не =, < чи >, отримує доданий * і йде через wildcard matcher, а все інше зберігає порівняння цілого запису. Практична нотатка, коли ви будуєте criteria ranges у коді: в XLSX engine присвоєння рядка '=ab' до TXLSXCell.Value зберігає текст, тоді як classic engine TXLSWorkbook компілює значення, що починається з =, як формулу, якщо ви не префіксуєте його апострофом

Діаграма HotXLS правила критерію DSUM: голий текстовий критерій отримує додану зірку і збирається як префікс, тож ab сягає ab, AB, abc і abcb, equals ab порівнює весь запис, angle bracket ab виключає обидва, а тильда-зірка виживає як дослівний a*b, з виміряними сумами DSUM 30, 6, 121 і 32 — Excel успадкував правило Advanced Filter для database функцій: голий текст означає «починається з», тоді як початковий equals чи not-equals порівнює весь запис; HotXLS оглядає сирий текст критерію, перш ніж довіряти розпарсеній умові

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';            // criteria-заголовок у D1
    for i := 0 to High(Criteria) do
    begin
      Sheet.Cells[2, 4].Value := Criteria[i];     // лишається текстом у XLSX engine
      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;

Одна споріднена відмінність пережила prefix fix і має значення на старіших збірках. Текстові порівняння на кшталт >ab уживали code-point порядок, тоді як Excel кладе пунктуацію перед літерами, тож "a~b">"ab" у Excel — FALSE, а в HotXLS було TRUE. Від v2.384.67 критерії > і <, разом зі звичайним текстовим порівнянням і сортуванням, уживають word-sort collation Excel за поточною локаллю користувача, і обидва знову згоджуються

Чому whole-cell Find пропустив abcb?

Whole-cell Find пропустив abcb, бо matcher зупинявся на першій точці, де шаблон вичерпався, замість backtracking в останню *. Matcher часткових збігів за Replace повертається щойно шаблон вичерпано; whole-cell Find перевживав його, а потім вимагав, щоб збіг покривав усю клітинку: a*b проти abcb зупинявся після ab, споживши 2 символи з 4, і відхилявся. Від v2.384.60 whole-cell matcher — окрема імплементація, що трактує «шаблон скінчився, текст — ні» як ще одну невдачу і повторює спробу від останньої зірки, тож a*b збирає abcb, а a?b*b — axbyb, як робить Excel 16 Find із галочкою «Match entire cell contents»

Whole-cell збіг не скінчений, коли шаблон вичерпався; трактування тексту, що залишився, як ще одної невдачі повертає matcher до останньої зірки — так a*b сягає abcb, як Excel 16 Find

Той самий реліз змінив тильду. Excel 16 Find, у whole-cell і partial режимах, трактує ~ як escape для будь-якого наступного символу: a~b знаходить ab, a~~b знаходить a~b, а кінцева тильда ігнорується, тож q~ поводиться як q. Старіший matcher HotXLS розпізнавав як escape лише ~*, ~? і ~~, тож a~b знаходив текст a~b. Find-шаблон з однієї ~ нестабільний у самому Excel — він збирає будь-яку клітинку, як порожній шаблон, і HotXLS це не імітує

У XLSX engine пошук — це TXLSXWorksheet.FindText з набором TXLSXFindOptions: lxfUseWildcards вмикає *, ? і ~, lxfWholeCell вимагає збігу цілої клітинки, а lxfMatchCase робить порівняння чутливим до регістру. Без lxfUseWildcards кожен символ, включно з зіркою, — дослівний. Find дивиться лише на текстові значення; числові клітинки пропускаються, і клітинки формул пропускаються, якщо не поставлено lxfSearchFormulas, — тоді шукається текст формули. Анкор, заданий 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 через backtracking
    if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
      Writeln('a~b  whole cell -> row ', Row);   // 4: ~b — escaped b
    if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
      Writeln('a~~b whole cell -> row ', Row);   // 3: ~~ — одна дослівна тильда

    // Partial match, Find All: клітинка-анкор включна, тож крок за кожний збіг
    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;

    // Whole-cell wildcard replace переписує лише дослівний a~b
    Changed := Sheet.ReplaceText('a~~b', 'a-b', Opts);
    Writeln(Changed, ' cell(s) replaced');         // 1
  finally
    Book.Free;
  end;
end;

Цикл часткових збігів знаходить усі чотири рядки, включно з abc, бо в partial режимі a*b достатньо, щоб він трапився десь усередині клітинки. FindTextIn і ReplaceTextIn приймають ті самі опції плюс вікно FirstRow, FirstCol, LastRow, LastCol — програмний еквівалент пошуку в межах виділення. Classic engine відкриває ті самі правила через перевантаження з трьома boolean: TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), плюс парне перевантаження 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;

Що старий DOS-mask matcher робив неправильно?

Старий matcher помилявся на спеціальних символах, бо файлова маска DOS — інша мова, ніж Excel wildcard. До v2.384.52 критеріальні функції та database функції передавали кожен шаблон у MatchesMask — matcher файлових масок з юніта lxMasks. Його синтаксис перетинається з Excel на буденних випадках, чому проблема й залишалася схованою, але розходиться там, де реальні дані стають цікавими:

[x] читався як набір символів, тож COUNTIF(A1:A10,"[x]") рахував клітинки з x замість тексту в дужках, а "[a-z]" збирав будь-яку клітинку з однієї літери
Escape тильди не було, тож "a~*b" не міг зібрати дослівну зірочку
Погана маска, як-от незакрита дужка, піднімала exception, який викликач ковтав як «немає збігу», перетворюючи одруківку в критерії на тихо неправильну суму
На lookup боці MATCH і XLOOKUP трактували як escape лише ~*, ~? і ~~, тож MATCH("a~b",…,0) знаходив дослівний a~b замість ab

Якщо ваші workbooks уживали лише * і ? на простих алфавітно-цифрових даних, результати вже були правильними і не зміняться. Якщо ж вони містять дужки, тильди, змішано-типові стовпці під "<>text" чи критерії DSUM, написані голими словами, перерахунок їх v2.384.64 чи новішою може змінити суми, і нові суми — ті, що показує Excel. Та сама відмінність між тим, як Excel зберігає критерій, і тим, як він його порівнює, випливає для збережених фільтрів — обговорено в статті HotXLS про BIFF8 AutoFilter DOPER критерії

Швидка довідка: правила Excel wildcard у 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, errors і порожні клітинки; голий "<>" рахує непорожні клітинки, включно з результатами =""
DSUM та інші database функції трактують plain text як «починається з»; =text і <>text порівнюють весь запис (від v2.384.64)
Whole-cell Find з lxfUseWildcards і lxfWholeCell робить backtracking, тож a*b збирає abcb; Find і Replace трактують ~ як escape для будь-якого символу (від v2.384.60)
Текстовий порядок у критеріях > і < слідує word-sort collation Excel, пунктуація перед літерами (від v2.384.67)

Excel-сумісність у formula engine — переважно такі ось edge cases, виміряні проти Excel, а не вгадані з документації. HotXLS обчислює COUNTIF, MATCH, XLOOKUP, DSUM і решту своєї бібліотеки функцій нативно в Delphi та C++Builder, у classic engine і XLSX engine, без установленого Excel. Деталі, видання та пробне завантаження — на сторінці HotXLS Delphi spreadsheet component