Техническая статья

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

Пусть в D1 лежит a~b, а в D2 — ab: =COUNTIF(D1:D2,"a~b") возвращает 1, и посчитанная ячейка — D1. =MATCH("a~b",D1:D2,0) возвращает 2, то есть указывает на D2. Строка шаблона одна, ячейки те же две, а Excel выбирает разные ячейки в зависимости от того, какая функция читает шаблон. Движку таблиц, который хочет, чтобы его кэшированные результаты сходились с Excel, придётся воспроизвести этот разрыв, плюс ещё два правила: функции баз данных вроде DSUM трактуют голый текст как «начинается с», а Find по целой ячейке обязан откатываться к последней *

Отчёты, которые на это натыкаются, редко упоминают wildcards. Они говорят, что серверная книга насчитывает на две строки меньше, чем тот же файл после пересчёта на десктопе, или что артикул с тильдой находит одна формула и игнорирует следующая. Прогон каждого шаблона через файловую маску в стиле DOS даёт верные ответы в повседневных случаях, поэтому расхождение всплывает лишь тогда, когда реальные коды изделий начинают содержать ~ или [

Одна строка шаблона, четыре свода правил Excel

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

ШаблонCOUNTIF / SUMIFMATCH(…,0) / XLOOKUP режим 2Критерий DSUMFind, целая ячейка, wildcards включены
abab, ABab, ABab, AB, abc, abcbab, AB
a*ba~b, ab, AB, abcb, a*b, axbкак у COUNTIFвсякая запись, включая abcкак у COUNTIF
a~bтолько a~bab, ABab, AB, abc, abcbab, AB
a~*bтолько a*bтолько a*bтолько a*bтолько a*b
=abab, ABнеприменимоab, ABнеприменимо

Две строки заслуживают второго взгляда. В строке a~b COUNTIF и MATCH приземляются на разные ячейки, а тильды в кодах, набитых вручную, встречаются чаще, чем кто-либо планирует. В строке a*b abc совпадает для DSUM и не совпадает для COUNTIF, потому что функция баз данных тихо дописывает * к критерию. Запись DSUM для a~b следует из той же дописанной звёздочки: раз она там есть, критерий — шаблон wildcard, и ~b — просто экранированная b

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

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

Схема ворот wildcards HotXLS: COUNTIF и SUMIF применяют wildcards, только когда критерий содержит звёздочку или вопросительный знак, так что a~b считает буквальную ячейку и возвращает 1, а MATCH типа 0 и XLOOKUP режима 2 всегда в режиме wildcards, так что a~b находит ab на позиции 2
Ворота — вся разница: COUNTIF спрашивает про звёздочку или вопросительный знак, прежде чем считать тильду экранированием, MATCH не спрашивает никогда, поэтому одна строка шаблона считает одну ячейку и находит другую

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

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

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

HotXLS держит эти два решения порознь. MATCH, XLOOKUP и функции критериев пользуются одним откатным матчером с одинаковым экранированием и одинаковым правилом хвостовой тильды; различаются ворота перед ним. Критериальный путь сперва спрашивает, содержит ли текст * или ?. Путь поиска не спрашивает никогда. Единая точка входа «сопоставить шаблон» для обоих семейств получила бы одно семейство правым, а другое — левым, как ни поставь ворота. У wildcard-поиска есть ещё одно условие: XLOOKUP возвращает #VALUE!, когда match_mode 2 сочетается с режимом двоичного поиска, — этот случай разобран в руководстве HotXLS по режимам поиска XLOOKUP и XMATCH

Критерии DSUM: голый текст значит «начинается с»

DSUM и остальные функции баз данных читают текстовый критерий без ведущего =, < или > как префикс, с активными wildcards. Это правило Advanced Filter, и от COUNTIF оно отличается намеренно. На колонке Name с abc, ab, xab и AB критерий ab ловит abc, ab и AB, тогда как =ab — только ab и AB. <>ab — неравенство по всей записи, a*b и a? — тоже префиксные шаблоны, а >ab — обычное текстовое сравнение. xab не матчится с ab никогда; правило — «начинается с», а не «содержит»

Неудобство для реализации в том, что парсер критериев, срезающий оператор, видит ab и =ab как одну и ту же проверку равенства. Различает их только сырой текст критерия. Поэтому HotXLS смотрит на этот сырой текст, прежде чем доверять разобранному условию: текстовый критерий, чей первый символ не =, < и не >, получает дописанную * и идёт через wildcard-матчер, а всё остальное сохраняет сравнение по всей записи

Схема HotXLS правила критерия DSUM: голый текстовый критерий получает дописанную звёздочку и ловит как префикс, так что ab достаёт ab, AB, abc и abcb, equals ab сравнивает всю запись, угловая скобка ab исключает обе, а тильда со звёздочкой выживает как буквальная a*b — с замеренными суммами DSUM 30, 6, 121 и 32
Excel унаследовал для функций баз данных правило Advanced Filter: голый текст значит «начинается с», а ведущий знак равенства или неравенства сравнивает всю запись; HotXLS осматривает сырой текст критерия, прежде чем доверять разобранному условию

У построения диапазона критериев из кода есть одна движковая тонкость. В движке XLSX присваивание строки '=ab' ячейке TXLSXCell.Value сохраняет текст — именно то, что нужно ячейке критерия. Классический движок TXLSWorkbook компилирует строковое значение, начинающееся с =, как формулу, если не приставить апостроф. Для диапазонов критериев, собираемых в рантайме, движок XLSX — менее рискованный выбор. Цикл ниже подставляет пять критериев в D2 и каждый раз пересчитывает тот же DSUM

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

Find по целой ячейке в Delphi: откат к последней звёздочке

Соблазнительный короткий путь для Find по целой ячейке — переиспользовать частичный матчер за Replace и дополнительно требовать, чтобы совпадение покрыло ячейку целиком. На обычных данных это ломается. Частичный матчер возвращается, как только шаблон исчерпан, поэтому a*b против abcb останавливается после ab, из 4 символов потреблено 2, и ячейка отвергается. HotXLS прогоняет Find по целой ячейке через отдельный матчер, который считает «шаблон кончился, текст нет» ещё одним несовпадением и повторяет попытку от последней звёздочки. С ним a*b матчит abcb, а a?b*b — axbyb, как Excel Find с отмеченным «Match entire cell contents»

Схема отката wildcard-Find по целой ячейке в HotXLS: шаблон a*b съедает a и b в ячейке abcb, и матчер, останавливающийся при исчерпании шаблона, отвергает ячейку, тогда как матчер HotXLS считает «шаблон кончился, текст остался» ещё одним несовпадением и повторяет попытку от последней звёздочки, пока не сойдётся вся ячейка
Совпадение по целой ячейке не закончено, когда исчерпался шаблон; трактовка остатка текста как ещё одного несовпадения возвращает матчер к последней звёздочке — так a*b достаёт abcb, как и Excel Find

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

В движке XLSX поиск — это TXLSXWorksheet.FindText с набором TXLSXFindOptions. lxfUseWildcards включает *, ? и ~; lxfWholeCell требует совпадения всей ячейки; lxfMatchCase делает сравнение чувствительным к регистру. Без lxfUseWildcards каждый символ, включая звёздочку, буквален. Find смотрит только текстовые значения. Числовые ячейки пропускаются, а ячейки формул пропускаются, если не задан lxfSearchFormulas — тогда ищется текст формулы. lxfBackward и lxfByColumns меняют порядок сканирования. Якорь, заданный StartRow и StartCol, включён, поэтому циклу «найти все» приходится шагать на колонку дальше каждого попадания

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 — программную версию поиска внутри выделения; передайте 0 вместо любой границы, чтобы оставить её открытой. Классический движок открывает те же правила сопоставления через перегрузку с тремя булевыми, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), плюс парную перегрузку ReplaceText. Её результаты в строках и колонках считаются с единицы, и, как XLSX-версия, она пропускает ячейки формул и нетекстовые значения

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 — не тот матчер для критериев Excel

Матчер файловых масок вроде MatchesMask говорит на другом языке, нежели wildcards Excel, даже если на * и ? они пересекаются. Именно из-за этого пересечения его и выбирают, и именно поэтому его ошибки остаются скрытыми, пока данные не станут интересными:

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

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

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