Com a~b em D1 e ab em D2, =COUNTIF(D1:D2,"a~b") retorna 1, e a célula contada é a D1. =MATCH("a~b",D1:D2,0) retorna 2, que aponta para a D2. Mesma string de padrão, mesmas duas células, e o Excel escolhe uma célula diferente dependendo de qual função lê o padrão. Um motor de planilhas que quer que os resultados em cache batam com o Excel precisa reproduzir essa divisão, junto com mais duas regras: funções de banco de dados como o DSUM tratam texto puro como "começa com", e o Find de célula inteira precisa fazer backtrack até o último *
Relatos que esbarram nisso raramente mencionam wildcards. Eles dizem que uma pasta de trabalho gerada no servidor conta duas linhas a menos do que o mesmo arquivo recalculado num desktop, ou que um número de peça contendo um til é encontrado por uma fórmula e ignorado pela seguinte. Passar todo padrão por uma máscara de arquivo estilo DOS acerta os casos do dia a dia, então a divergência só aparece quando códigos de produto de verdade começam a conter ~ ou [
Uma string de padrão, quatro conjuntos de regras do Excel
O Excel nunca unificou suas regras de casamento; cada funcionalidade trouxe a sua. As funções de critério (COUNTIF, SUMIF, AVERAGEIF e a família *IFS) decidem por critério se wildcards se aplicam ou não. As funções de lookup (MATCH com match type 0, XLOOKUP com match_mode 2) sempre os aplicam. As funções de banco de dados (DSUM, DCOUNTA e companhia) herdam a regra do Advanced Filter, em que uma palavra nua é um prefixo. O diálogo Find tem modos próprios de célula inteira e parcial. O HotXLS Delphi Component avalia cada família pela regra dela, tanto no motor clássico quanto no motor XLSX. A tabela lista quais células casam com cada padrão contra uma coluna contendo a~b, ab, AB, abc, abcb, a*b e axb, com toda função em seu modo padrão sem diferenciar maiúsculas
| Padrão | COUNTIF / SUMIF | MATCH(…,0) / XLOOKUP modo 2 | Critério DSUM | Find, célula inteira, wildcards ligados |
|---|---|---|---|---|
ab | ab, AB | ab, AB | ab, AB, abc, abcb | ab, AB |
a*b | a~b, ab, AB, abcb, a*b, axb | igual ao COUNTIF | toda entrada, abc incluída | igual ao COUNTIF |
a~b | só a~b | ab, AB | ab, AB, abc, abcb | ab, AB |
a~*b | só a*b | só a*b | só a*b | só a*b |
=ab | ab, AB | não se aplica | ab, AB | não se aplica |
Duas linhas merecem um segundo olhar. Na linha do a~b, COUNTIF e MATCH pousam em células diferentes, e códigos digitados à mão contêm tildes com mais frequência do que ninguém planeja. Na linha do a*b, abc casa para o DSUM e falha para o COUNTIF, porque a função de banco de dados anexa um * ao critério silenciosamente. A entrada do DSUM para a~b segue dessa mesma estrela anexada: uma vez que ela está lá, o critério é um padrão wildcard, e ~b é só um b escapado
Quando o COUNTIF entra em modo wildcard?
O COUNTIF entra em modo wildcard só quando o texto do critério contém * ou ?, escapados ou não. Sem nenhum dos dois caracteres, o critério é comparado com cada célula como string inteira, sem diferenciar maiúsculas, e um til é um caractere comum, então COUNTIF(A1:A7,"a~b") conta a célula que literalmente contém a~b. Uma estrela muda o significado. Em "a~b*" o til agora escapa o b, o padrão se lê como "ab seguido de qualquer coisa", e a célula a~b sai da contagem. O HotXLS aplica esse portão em COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS e nas funções de banco de dados igualmente
Dentro do modo wildcard a regra de escape é curta. O ~ torna o próximo caractere literal seja ele qual for, então ~b significa b e ~~ significa um til; um til bem no fim do padrão é descartado, então "a*~" se comporta como "a*". Colchetes nunca são especiais. Um critério "[x]" conta células que contêm os três caracteres [x], e "[a-z]" não conta nada em dados comuns. O caminho mais rápido para conferir tudo isso contra os seus próprios dados é o TXLSXWorkbook.Calculate, que avalia uma string de fórmula contra a planilha ativa e devolve um Variant; uma pasta de trabalho recém-criada torna a primeira planilha adicionada a ativa
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'); // primeira planilha, então é a ativa
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 ... para o total de um SUMIF nomear as linhas
end;
Sheet.Cells[8, 1].Value := 5; // um número; A9 fica vazia
Show('=COUNTIF(A1:A7,"a~b")'); // 1 sem * ou ?: texto puro, a célula a~b
Show('=COUNTIF(A1:A7,"a~b*")'); // 4 modo wildcard: ab, AB, abc, abcb
Show('=COUNTIF(A1:A7,"a*b")'); // 6 wildcard de string inteira, abc excluído
Show('=SUMIF(A1:A7,"a*b",B1:B7)'); // 119 toda linha exceto abc (8)
Show('=COUNTIF(A1:A7,"a~*b")'); // 1 o literal a*b
Show('=COUNTIF(A1:A9,"<>ab")'); // 7 o número 5 e a A9 vazia contam
Show('=COUNTIF(A1:A9,"<>")'); // 8 células não vazias
finally
Book.Free;
end;
end.
O que "<>text" conta de verdade
Um critério "<>text" conta toda célula que não é aquele texto, e no Excel isso inclui números, booleans, valores de erro e células vazias. Um "<>" nu pergunta outra coisa completamente: "não é uma célula vazia". Ele pula células vazias mas conta todo valor, incluindo o texto vazio que uma fórmula como ="" devolve. A armadilha do Delphi está na comparação de Variant. Compare 'ab' com uma célula numérica e o Delphi tenta converter 'ab' para número, a conversão lança exceção, e um handler que engole a exceção como "sem casamento" tira células numéricas da contagem. O HotXLS as conta, então as duas últimas linhas do exemplo retornam 7 e 8. O lado de célula vazia dessa história, incluindo o que um operando vazio iguala numa comparação comum, está coberto em como o HotXLS trata cadeias de comparação, células vazias e SUMIF
Por que o MATCH encontra ab quando você busca a~b?
O MATCH com match type 0 e o XLOOKUP com match_mode 2 estão sempre em modo wildcard, então o til é um escape mesmo quando o padrão não contém * nem ?. No intervalo de duas células da abertura, MATCH("a~b",D1:D2,0) retorna 2. Num intervalo que contém só a~b, a mesma chamada retorna #N/A. Buscar o texto literal a~b exige "a~~b". Quando as chaves de lookup vêm de input do usuário ou de um sistema externo e podem conter um til, duplique todo til antes de montar a fórmula; esse único StringReplace sai mais barato do que explicar uma linha sumida depois
O HotXLS mantém essas duas decisões separadas. MATCH, XLOOKUP e as funções de critério compartilham um matcher de backtrack, com o mesmo tratamento de escape e a mesma regra de til no fim; o que difere é o portão na frente dele. O caminho de critérios primeiro pergunta se o texto contém * ou ?. O caminho de lookup nunca pergunta. Um único ponto de entrada "case um padrão" usado pelos dois acertaria uma família e erraria a outra, fosse qual fosse o portão. Lookups com wildcard carregam uma condição a mais: o XLOOKUP retorna #VALUE! quando o match_mode 2 é combinado com um modo de busca binária, o que está coberto no guia do HotXLS sobre modos de busca do XLOOKUP e XMATCH
Critérios do DSUM: texto puro significa "começa com"
O DSUM e as outras funções de banco de dados leem um critério de texto sem =, < ou > inicial como um prefixo, com wildcards ainda ativos. Essa é a regra do Advanced Filter, e ela difere do COUNTIF de propósito. Sobre uma coluna Name contendo abc, ab, xab e AB, o critério ab casa abc, ab e AB, enquanto =ab casa só ab e AB. <>ab é uma desigualdade de entrada inteira, a*b e a? são padrões de prefixo também, e >ab é uma comparação de texto comum. xab nunca casa ab; a regra é "começa com", não "contém"
A parte espinhosa para uma implementação é que um parser de critérios que arranca o operador vê ab e =ab como o mesmo teste de igualdade. Só o texto cru do critério os diferencia. O HotXLS então olha esse texto cru antes de confiar na condição parseada: um critério de texto cujo primeiro caractere não é =, < nem > recebe um * anexado e passa pelo matcher wildcard, e todo o resto mantém a comparação de entrada inteira
Montar um intervalo de critérios em código tem uma pegadinha específica de motor. No motor XLSX, atribuir a string '=ab' a TXLSXCell.Value armazena texto, que é exatamente o que uma célula de critério precisa. O motor clássico TXLSWorkbook compila um valor string que começa com = como fórmula, a menos que você o prefixe com um apóstrofo. Para intervalos de critérios montados em runtime, o motor XLSX é a escolha menos propensa a erro. O loop abaixo troca cinco critérios em D2 e recalcula o mesmo DSUM a cada passada
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'; // cabeçalho de critério em D1
for i := 0 to High(Criteria) do
begin
Sheet.Cells[2, 4].Value := Criteria[i]; // permanece texto no motor XLSX
Writeln(Criteria[i], ' -> ',
VarToStr(Book.Calculate('=DSUM(A1:B8,"Val",D1:D2)')));
end;
// ab -> 30 ab, AB, abc, abcb (começa com)
// =ab -> 6 ab, AB (entrada inteira)
// <>ab -> 121 tudo exceto ab e AB
// a*b -> 127 a*b* casa as sete, abc incluído
// a~* -> 32 só o literal a*b
finally
Book.Free;
end;
end;
Critérios de ordenação como >ab seguem ainda outra regra. Uma comparação de code points faz "a~b">"ab" ser TRUE, porque o ~ fica acima de toda letra no Unicode. O Excel põe pontuação antes de letras e retorna FALSE. No Windows, o HotXLS avalia critérios de texto > e <, comparação de texto comum e ordenação com a collation word sort do sistema para a localidade do usuário atual, então os três concordam entre si e com o Excel
Find de célula inteira em Delphi: backtrack até a última estrela
O atalho tentador para o Find de célula inteira é reaproveitar o matcher parcial por trás do Replace e depois exigir que o casamento cubra a célula inteira. Ele falha em dados comuns. Um matcher parcial retorna assim que o padrão se esgota, então a*b contra abcb para depois do ab, tendo consumido 2 de 4 caracteres, e a célula é rejeitada. O HotXLS roda o Find de célula inteira por um matcher separado que trata "padrão acabou, texto não" como mais um descasamento e recomeça da última estrela. Com isso, a*b casa abcb e a?b*b casa axbyb, como o Find do Excel faz com "Match entire cell contents" marcado
O Find do Excel, tanto em modo de célula inteira quanto parcial, trata o ~ como escape para qualquer caractere seguinte: a~b encontra ab, a~~b encontra a~b, e um til no fim é ignorado, então q~ se comporta como q. Um matcher que só conhece ~*, ~? e ~~ faria a~b encontrar o texto a~b, a célula oposta. O HotXLS escapa qualquer caractere depois de um til, no Find e no Replace tanto quanto nas fórmulas
No motor XLSX a chamada é TXLSXWorksheet.FindText com um conjunto TXLSXFindOptions. lxfUseWildcards liga *, ? e ~; lxfWholeCell exige que a célula inteira case; lxfMatchCase torna a comparação sensível a maiúsculas. Sem lxfUseWildcards, todo caractere, estrela incluída, é literal. O Find olha apenas valores de texto. Células numéricas são puladas, e células de fórmula são puladas a menos que lxfSearchFormulas esteja ligado, caso em que o texto da fórmula é buscado. lxfBackward e lxfByColumns mudam a ordem da varredura. A âncora dada por StartRow e StartCol é inclusiva, então um loop de Find All precisa avançar uma coluna além de cada 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 rejeitada, abcb faz backtrack
if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
Writeln('a~b whole cell -> row ', Row); // 4: ~b é um b escapado
if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
Writeln('a~~b whole cell -> row ', Row); // 3: ~~ é um til literal
// Match parcial, Find All: a célula âncora está incluída, então avance além de cada hit
NextRow := 1;
NextCol := 1;
while Sheet.FindText('a*b', Row, Col, [lxfUseWildcards], NextRow, NextCol) do
begin
Writeln('a*b contained in row ', Row); // linhas 1, 2, 3 e 4
NextRow := Row;
NextCol := Col + 1;
end;
// O replace wildcard de célula inteira reescreve só o literal a~b
Changed := Sheet.ReplaceText('a~~b', 'a-b', Opts);
Writeln(Changed, ' cell(s) replaced'); // 1
finally
Book.Free;
end;
end;
O loop parcial reporta as quatro linhas, abc incluída, porque no modo parcial o a*b só precisa ocorrer em algum lugar dentro da célula. FindTextIn e ReplaceTextIn recebem as mesmas opções mais uma janela FirstRow, FirstCol, LastRow, LastCol, a versão programática de buscar dentro de uma seleção; passe 0 para qualquer limite que deva ficar aberto. O motor clássico expõe as mesmas regras de casamento por uma sobrecarga com três booleans, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), mais uma sobrecarga ReplaceText correspondente. Os resultados de linha e coluna são 1-based, e como a versão XLSX ele pula células de fórmula e valores que não são texto
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;
Por que uma máscara de arquivo DOS é o matcher errado para critérios do Excel
Um matcher de máscara de arquivo como o MatchesMask fala uma língua diferente dos wildcards do Excel, mesmo com a sobreposição dos dois em * e ?. Essa sobreposição é exatamente o motivo de ele ser escolhido, e de os erros dele ficarem escondidos até os dados ficarem interessantes:
[x]é lido como conjunto de caracteres, entãoCOUNTIF(A1:A10,"[x]")contaria células comxem vez do texto entre colchetes, e"[a-z]"casaria qualquer célula de uma letra- Não há escape por til, então
"a~*b"não consegue casar um asterisco literal - Uma máscara malformada, como um colchete sem fechamento, lança uma exceção que o chamador pode engolir facilmente como "sem casamento", transformando um typo num critério num total errado
- Do lado das lookups, um conjunto de escapes limitado a
~*,~?e~~também fica aquém:MATCH("a~b",…,0)tem que encontrarab, não o literala~b
Se as suas pastas de trabalho só usarem * e ? sobre dados alfanuméricos puros, todos esses matchers concordam e nada disso importa. Colchetes, tildes, colunas de tipos mistos sob "<>text" e critérios do DSUM escritos como palavras nuas são onde os totais de um matcher mais simples divergem do Excel, e são esses os casos que o código acima exercita. Filtros salvos levantam uma pergunta relacionada sobre como o Excel armazena um critério versus como o compara, discutida no artigo do HotXLS sobre critérios DOPER do AutoFilter no BIFF8
O HotXLS avalia COUNTIF, MATCH, XLOOKUP, DSUM e o resto da biblioteca de funções nativamente em Delphi e C++Builder, tanto no motor clássico quanto no motor XLSX, sem Excel instalado na máquina. Edições, documentação e o download da versão trial estão na página do componente de planilha HotXLS para Delphi