Artigo Técnico

Wildcards do Excel no HotXLS: COUNTIF, MATCH, DSUM e Find

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ãoCOUNTIF / SUMIFMATCH(…,0) / XLOOKUP modo 2Critério DSUMFind, célula inteira, wildcards ligados
abab, ABab, ABab, AB, abc, abcbab, AB
a*ba~b, ab, AB, abcb, a*b, axbigual ao COUNTIFtoda entrada, abc incluídaigual ao COUNTIF
a~bsó a~bab, ABab, AB, abc, abcbab, AB
a~*bsó a*bsó a*bsó a*bsó a*b
=abab, ABnão se aplicaab, ABnã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

Diagrama do portão wildcard do HotXLS: COUNTIF e SUMIF aplicam wildcards só quando o critério contém estrela ou ponto de interrogação, então a~b conta a célula literal e devolve 1, enquanto MATCH type 0 e XLOOKUP modo 2 estão sempre em modo wildcard, então a~b encontra ab na posição 2
O portão é toda a diferença: o COUNTIF pede uma estrela ou ponto de interrogação antes de tratar um til como escape, o MATCH nunca pergunta, então uma mesma string de padrão conta uma célula e encontra a outra

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

Diagrama do HotXLS da regra de critério do DSUM: um critério de texto nu recebe uma estrela anexada e casa como prefixo, então ab alcança ab, AB, abc e abcb, igual ab compara a entrada inteira, diferente ab exclui ambos, e til estrela sobrevive como o literal a*b, com os totais DSUM resultantes 30, 6, 121 e 32
O Excel herdou a regra do Advanced Filter para funções de banco de dados: texto nu significa começa com, enquanto um igual ou diferente inicial compara a entrada inteira; o HotXLS inspeciona o texto cru do critério antes de confiar na condição parseada

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

Diagrama do HotXLS do backtrack do Find wildcard de célula inteira: o padrão a*b consome a e b na célula abcb e um matcher que para quando o padrão se esgota rejeita a célula, enquanto o matcher do HotXLS trata padrão acabado com texto restante como mais um descasamento e recomeça da última estrela até a célula inteira casar
Um casamento de célula inteira não termina quando o padrão se esgota; tratar o texto restante como mais um descasamento manda o matcher de volta à última estrela, e é assim que a*b alcança abcb como o Find do Excel

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ão COUNTIF(A1:A10,"[x]") contaria células com x em 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 encontrar ab, não o literal a~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