Wildcards Excel в HotXLS: COUNTIF, MATCH, DSUM и Find

HotXLS Delphi Component читает одну и ту же строку шаблона четырьмя разными способами — потому что так делает Excel 16. В COUNTIF и SUMIF текст a~b буквален, пока критерий не содержит ещё и * или ?; в режиме wildcards у MATCH и XLOOKUP тильда всегда экранирование, так что a~b находит ab; в DSUM и прочих функциях баз данных простой текст значит «начинается с»; а Find по целой ячейке обязан откатываться к последней *. HotXLS следует этим вымеренным правилам с v2.384.52, v2.384.60 и v2.384.64

Баг-репорты из этой области никогда не упоминают wildcards. Они говорят, что серверный отчёт насчитывает на пару строк меньше, чем тот же файл, пересчитанный в Excel, или что артикул с тильдой находит одна формула и игнорирует следующая. Причина — матчер, уверенный, что шаблон значит одно и то же всюду. Excel так не работает, а значит, движок, чьи закэшированные результаты обязаны сходиться с Excel, тоже не может. До v2.384.52 HotXLS прогонял всякий критерий через файловую маску в духе DOS, которая верно брала будничные шаблоны и тихо ошибалась на крайних случаях

Почему одна строка шаблона значит в Excel четыре разные вещи?

Одна строка шаблона значит четыре разные вещи, потому что Excel унаследовал четыре правила сопоставления от четырёх функций и никогда их не унифицировал. Функции критериев (COUNTIF, SUMIF, AVERAGEIF и семейство *IFS) решают по каждому критерию, применяются ли wildcards вообще. Функции поиска (MATCH с match type 0, XLOOKUP с match_mode 2) применяют их всегда. Функции баз данных (DSUM, DCOUNTA и компания) следуют Advanced Filter, где голое слово — префикс. У диалога Find свои режимы целой ячейки и частичного совпадения. Таблица ниже перечисляет, какие ячейки ловит каждый шаблон на одном столбце с a~b, ab, AB, abc, abcb, a*b и axb, у всякой функции в её дефолтном режиме без учёта регистра

Шаблон	COUNTIF / SUMIF	MATCH(…,0) / XLOOKUP режим 2	Критерий DSUM	Find, целая ячейка, wildcards включены
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, потому что функция баз данных молча дописывает *. Записи DSUM для ab, a*b и =ab взяты прямо из прогонов Excel 16; запись DSUM для a~b следует из того же правила префикса, поскольку дописанная * превращает критерий в шаблон wildcard, где ~b — экранированный b

Когда COUNTIF переключается в режим wildcards?

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

Внутри режима wildcards правила экранирования те же, что всюду в Excel: ~ делает следующий символ буквальным, чем бы он ни был, так что ~b значит b, ~~ — одна тильда, а тильда в самом конце шаблона отбрасывается, так что "a*~" ведёт себя как "a*". Квадратные скобки спецсимволами не бывают никогда. Критерий "[x]" считает ячейки, держащие три символа [x], а "[a-z]" не считает ничего на обычных данных. TXLSXWorkbook.Calculate вычисляет строку формулы на активном листе и возвращает 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    режим wildcards: 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, ошибочные значения и пустые ячейки. Голый "<>" — вопрос совсем другой: он значит «не пустая ячейка», так что пустые ячейки он пропускает, но считает всякое значение, включая пустой текст, который возвращает формула вроде ="". Старый код HotXLS брал верно текстовые ячейки, но не числа: неравенство Variant заставляло Delphi конвертировать 'ab' в число, конверсия бросала исключение, обработчик глотал его как «нет совпадения», и числовые ячейки молча выпадали из счёта. Пустоячеечная сторона этой истории, включая то, чему равен пустой операнд в обычном сравнении, разобрана в статье о том, как HotXLS обрабатывает цепочки сравнения, пустые ячейки и SUMIF

Почему MATCH находит ab, когда вы ищете a~b?

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

Потому HotXLS и держит эти два решения порознь, а не за одним входом «сопоставить шаблон». Сам матчер общий: с v2.384.52 MATCH, XLOOKUP и функции критериев гоняют один и тот же матчер с откатами, с той же обработкой экранирования и тем же правилом хвостовой тильды. Различаются ворота перед ним. Путь критериев сперва спрашивает «содержит ли этот текст * или ??»; путь поиска не спрашивает никогда. Слияние двух починило бы одно семейство и сломало бы другое, и оба направления проверены против значений Excel 16 в обоих движках. У wildcards-поисков есть и своё предусловие: XLOOKUP отвергает wildcard-сопоставление в сочетании с режимом бинарного поиска — правило описано в руководстве HotXLS по режимам поиска XLOOKUP и XMATCH

Как DSUM и функции баз данных читают простой текстовый критерий?

DSUM и прочие функции баз данных читают текстовый критерий без ведущего =, < или > как «начинается с», при активных wildcards. Это правило 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

Правке пришлось обходить парсер условий, который сворачивает и ab, и =ab в одно и то же условие равенства. Потому HotXLS осматривает сырой текст критерия, прежде чем доверять разобранному условию: текстовый критерий, чей первый символ — не =, не < и не >, получает дописанную * и идёт через wildcard-матчер, а всё остальное сохраняет сравнение по всей записи. Практическое замечание, когда строите диапазоны критериев кодом: в движке XLSX присвоение строки '=ab' в TXLSXCell.Value сохраняет текст, а классический движок TXLSWorkbook компилирует значение, начинающееся с =, как формулу, если не приставить впереди апостроф

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 пользовались порядком кодовых точек, тогда как Excel ставит пунктуацию раньше букв, так что "a~b">"ab" в Excel FALSE, а в HotXLS было TRUE. С v2.384.67 критерии > и <, вместе с обычным сравнением текста и сортировкой, пользуются коллацией word sort Excel под текущей локалью пользователя, и оба снова согласны

Почему Find по целой ячейке пропускал abcb?

Find по целой ячейке пропускал abcb, потому что матчер останавливался в первой точке, где шаблон кончился, вместо отката к последней *. Матчер частичного совпадения за Replace возвращается, как только исчерпан шаблон; Find по целой ячейке переиспользовал его, а затем требовал, чтобы совпадение накрыло всю ячейку: a*b против abcb остановился после ab, съев 2 символа из 4, и был отвергнут. С v2.384.60 матчер целой ячейки — отдельная реализация, трактующая «шаблон кончился, текст нет» как ещё одно несовпадение и повторяющая попытку от последней звёздочки, так что a*b ловит abcb, а a?b*b — axbyb, как Find в Excel 16 с взведённым «Match entire cell contents»

Тот же релиз поменял тильду. Find в Excel 16, в обоих режимах, целой ячейки и частичном, трактует ~ как экранирование любого следующего символа: a~b находит ab, a~~b находит a~b, а хвостовая тильда игнорируется, так что q~ ведёт себя как q. Старый матчер HotXLS признавал экранированиями только ~*, ~? и ~~, так что a~b находил текст a~b. Шаблон Find из одной ~ нестабилен в самом Excel — ловит любую ячейку, как пустой шаблон, — и HotXLS это не имитирует

В движке XLSX поиск — это 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 через откат
    if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
      Writeln('a~b  whole cell -> row ', Row);   // 4: ~b — экранированный b
    if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
      Writeln('a~~b whole cell -> row ', Row);   // 3: ~~ — одна буквальная тильда

    // Частичное совпадение, 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;

    // Замена с 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 берут те же опции плюс окно FirstRow, FirstCol, LastRow, LastCol — программный эквивалент поиска внутри выделения. Классический движок выставляет те же правила через перегрузку с тремя boolean, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), плюс парную перегрузку ReplaceText, с результатами строк и столбцов с единицы:

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-маской брал неверно?

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

• [x] читался как набор символов, так что COUNTIF(A1:A10,"[x]") считал ячейки с x вместо текста в скобках, а "[a-z]" ловил любую ячейку из одной буквы
• Не было тильда-экранирования, так что "a~*b" не мог поймать буквальную звёздочку
• Битая маска, скажем незакрытая скобка, бросала исключение, которое звонящий глотал как «нет совпадения», превращая опечатку в критерии в беззвучно неверную сумму
• На стороне поиска MATCH и XLOOKUP признавали экранированиями только ~*, ~? и ~~, так что MATCH("a~b",…,0) находил буквальную a~b вместо ab

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

Шпаргалка: правила wildcards Excel в HotXLS

• COUNTIF, SUMIF, AVERAGEIF и семейство *IFS пользуются wildcards, только когда критерий содержит * или ?; иначе сравнивают целые строки без учёта регистра, а ~ буквальна (с v2.384.52)
• MATCH с match type 0 и XLOOKUP с match_mode 2 пользуются wildcards всегда, так что a~b находит ab, а буквальной нужно a~~b (с v2.384.52)
• В режиме wildcards ~ экранирует любой следующий символ, а хвостовая ~ отбрасывается; [ и ] — обычные символы
• "<>text" считает числа, boolean, ошибки и пустые ячейки; голый "<>" считает непустые ячейки, включая результаты =""
• DSUM и прочие функции баз данных трактуют простой текст как «начинается с»; =text и <>text сравнивают всю запись (с v2.384.64)
• Find по целой ячейке с lxfUseWildcards и lxfWholeCell делает откаты, так что a*b ловит abcb; Find и Replace трактуют ~ как экранирование любого символа (с v2.384.60)
• Текстовый порядок в критериях > и < следует коллации word sort Excel — пунктуация раньше букв (с v2.384.67)

Совместимость с Excel в формульном движке — по большей части такие вот крайние случаи, вымеренные против Excel, а не угаданные по документации. HotXLS вычисляет COUNTIF, MATCH, XLOOKUP, DSUM и остальную свою библиотеку функций нативно в Delphi и C++Builder, в обоих движках, классическом и XLSX, без установленного Excel. Детали, издания и пробная загрузка — на странице HotXLS Delphi spreadsheet component page