Пусть в 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 / 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 для 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 правило экранирования короткое. ~ делает следующий символ буквальным, чем бы он ни был, так что ~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-матчер, а всё остальное сохраняет сравнение по всей записи
У построения диапазона критериев из кода есть одна движковая тонкость. В движке 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»
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