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

O HotXLS Delphi Component lê a mesma string de padrão de quatro maneiras diferentes, porque o Excel 16 faz isso. Em COUNTIF e SUMIF o texto a~b é um literal a menos que o critério também contenha * ou ?; em MATCH e XLOOKUP em modo wildcard o til é sempre um escape, então a~b encontra ab; em DSUM e nas outras funções de banco de dados texto puro significa "começa com"; e o Find de célula inteira precisa fazer backtrack até o último *. O HotXLS segue essas regras medidas desde a v2.384.52, v2.384.60 e v2.384.64

Os relatos de bug nessa área nunca mencionam wildcards. Eles dizem que um relatório gerado no servidor conta duas linhas a menos que o mesmo arquivo recalculado no Excel, ou que um número de peça contendo um til é encontrado por uma fórmula e ignorado pela seguinte. A causa é um matcher que assume que um padrão significa uma coisa em todo lugar. O Excel não funciona assim, então um motor cujos resultados em cache precisam concordar com o Excel também não pode. Antes da v2.384.52 o HotXLS passava todo critério por uma máscara de arquivo estilo DOS, que acertava os padrões do dia a dia e errava silenciosamente os casos extremos

Por que uma string de padrão significa quatro coisas diferentes no Excel?

Uma string de padrão significa quatro coisas diferentes porque o Excel herdou quatro regras de casamento de quatro funcionalidades e nunca as unificou. As funções de critério (COUNTIF, SUMIF, AVERAGEIF e a família *IFS) decidem por critério se wildcards se aplicam de fato. As funções de lookup (MATCH com match type 0, XLOOKUP com match_mode 2) sempre as aplicam. As funções de banco de dados (DSUM, DCOUNTA e companhia) seguem o Advanced Filter, em que uma palavra nua é um prefixo. O diálogo Find tem os modos próprios de célula inteira e parcial. A tabela abaixo 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

A linha do a~b é aquela em que COUNTIF e MATCH divergem, e números de peça e códigos digitados à mão contêm tildes com mais frequência do que ninguém espera. A linha do a*b mostra a outra armadilha: abc casa para o DSUM mas não para o COUNTIF, porque a função de banco de dados anexa um * silenciosamente. As entradas do DSUM para ab, a*b e =ab vêm direto de execuções no Excel 16; a entrada do DSUM para a~b segue da mesma regra de prefixo, já que o * anexado transforma o critério num padrão wildcard em que ~b é 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 Excel compara o critério com cada célula como string inteira, sem diferenciar maiúsculas, e um til é só um til, então COUNTIF(A1:A7,"a~b") conta a célula que literalmente contém a~b. Adicione uma estrela e o significado vira: 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 deixa de ser contada. O HotXLS aplica esta regra nos dois motores desde a v2.384.52, por meio de um único matcher de critérios no lxCalc compartilhado por COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS e as funções de banco de dados

Dentro do modo wildcard as regras de escape são as mesmas de todo o resto do Excel: ~ torna o próximo caractere literal seja ele qual for, então ~b significa b e ~~ significa um til, e um til 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 TXLSXWorkbook.Calculate avalia uma string de fórmula contra a planilha ativa e devolve um Variant, o caminho mais rápido para conferir essas regras contra os seus próprios dados

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 ... 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?

Um critério "<>text" conta toda célula que não é aquele texto, e no Excel 16 isso inclui números, booleans, valores de erro e células vazias. Um "<>" nu é uma pergunta completamente diferente: significa "não é uma célula vazia", então pula células vazias mas conta todo valor, incluindo o texto vazio que uma fórmula como ="" devolve. O código antigo do HotXLS acertava células de texto mas não números: uma desigualdade de Variant fazia o Delphi converter 'ab' para número, a conversão lançava uma exceção, um handler a engolia como "sem casamento", e células numéricas caíam fora da contagem silenciosamente. 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 encontra ab quando você busca a~b porque 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 ?. O Excel 16 confirma isso num intervalo de duas células contendo a~b e ab: MATCH("a~b",D1:D2,0) devolve 2, e num intervalo que contém só a~b a mesma chamada devolve #N/A. Para buscar o texto literal a~b você tem de escrever "a~~b". Enquanto isso, COUNTIF(D1:D2,"a~b") sobre as mesmas duas células devolve 1, contando a outra célula. Mesma string, mesmo intervalo, célula oposta

É por isso que o HotXLS mantém as duas decisões separadas em vez de escondê-las atrás de um único ponto de entrada "case um padrão". O matcher em si é compartilhado: desde a v2.384.52, MATCH, XLOOKUP e as funções de critério rodam o mesmo matcher com 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 pergunta primeiro "esse texto contém * ou ??"; o caminho de lookup nunca pergunta. Fundir os dois consertaria uma família e quebraria a outra, e as duas direções são conferidas contra valores do Excel 16 nos dois motores. Lookups com wildcard também têm uma pré-condição própria: o XLOOKUP rejeita casamento por wildcard combinado com modo de busca binária, uma regra descrita em o guia do HotXLS para modos de busca do XLOOKUP e XMATCH

Como o DSUM e as funções de banco de dados leem um critério de texto puro?

O DSUM e as outras funções de banco de dados leem um critério de texto sem =, < ou > inicial como "começa com", com wildcards ainda ativos. Essa é a regra do Advanced Filter, e ela difere do COUNTIF de propósito. O Excel 16 medido sobre uma coluna Name contendo abc, ab, xab, AB, a~b e a*b: o critério ab casa abc, ab e AB; =ab casa só ab e AB; <>ab é uma desigualdade de entrada inteira; a*b e a? também são padrões de prefixo; >ab é uma comparação comum. Antes da v2.384.64 o HotXLS casava ab exatamente, então um DSUM sobre esses dados de teste devolvia 10 onde o Excel devolve 11

A correção teve de contornar o parser de condições, que dobra ab e =ab na mesma condição de igualdade. O HotXLS portanto inspeciona o texto cru do critério 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. Uma nota prática quando você monta intervalos de critério em código: no motor XLSX, atribuir a string '=ab' ao TXLSXCell.Value armazena texto, enquanto o motor clássico TXLSWorkbook compila um valor que começa com = como fórmula a menos que você o prefixe com um apóstrofo

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;

Uma diferença relacionada sobreviveu à correção do prefixo e importa em builds mais antigas. Comparações de texto como >ab usavam a ordem por code point, enquanto o Excel põe a pontuação antes das letras, então "a~b">"ab" é FALSE no Excel e era TRUE no HotXLS. Desde a v2.384.67 os critérios > e <, junto com a comparação e a ordenação de texto comuns, usam a collation word sort do Excel sob o locale atual do usuário, e os dois voltam a concordar

Por que o Find de célula inteira perdeu abcb?

O Find de célula inteira perdeu abcb porque o matcher parou no primeiro ponto em que o padrão se esgotou em vez de fazer backtrack até o último *. O matcher de casamento parcial por trás do Replace devolve assim que o padrão se esgota; o Find de célula inteira o reutilizava e depois exigia que o casamento cobrisse a célula inteira: a*b contra abcb parou depois de ab, consumiu 2 caracteres de 4, e foi rejeitado. Desde a v2.384.60 o matcher de célula inteira é uma implementação separada que trata "padrão acabou, texto não" como mais um descasamento e recomeça da última estrela, então a*b casa abcb e a?b*b casa axbyb, como o Find do Excel 16 faz com "Match entire cell contents" marcado

O mesmo release mudou o til. O Find do Excel 16, tanto em modo de célula inteira quanto parcial, trata ~ 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. O matcher antigo do HotXLS reconhecia só ~*, ~? e ~~ como escapes, então a~b encontrava o texto a~b. Um padrão Find de um único ~ é instável no próprio Excel, casando qualquer célula como um padrão vazio, e o HotXLS não imita isso

No motor XLSX a busca é o TXLSXWorksheet.FindText com um conjunto TXLSXFindOptions: lxfUseWildcards liga *, ? e ~, lxfWholeCell exige que a célula inteira case, e lxfMatchCase torna a comparação sensível a maiúsculas. Sem lxfUseWildcards todo caractere, estrela incluída, é literal. O Find olha só 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. A âncora dada por StartRow e StartCol é inclusiva, então um loop de Find All avança 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 encontra as quatro linhas, incluindo abc, porque no modo parcial a*b só precisa ocorrer em algum lugar dentro da célula. O FindTextIn e o ReplaceTextIn recebem as mesmas opções mais uma janela FirstRow, FirstCol, LastRow, LastCol, o equivalente programático de buscar dentro de uma seleção. O motor clássico expõe as mesmas regras por uma sobrecarga com três booleans, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), mais uma sobrecarga ReplaceText correspondente, com resultados de linha e coluna em base um:

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;

O que o matcher antigo de máscara DOS errava?

O matcher antigo errava os caracteres especiais, porque uma máscara de arquivo DOS é uma linguagem diferente de um wildcard do Excel. Antes da v2.384.52 as funções de critério e as funções de banco de dados passavam todo padrão ao MatchesMask, um matcher de máscaras de arquivo na unit lxMasks. A sintaxe dele se sobrepõe à do Excel nos casos comuns, e é por isso que o problema ficou escondido, mas ela diverge onde dados reais ficam interessantes:

• [x] era lido como conjunto de caracteres, então COUNTIF(A1:A10,"[x]") contava células com x em vez do texto entre colchetes, e "[a-z]" casava qualquer célula de uma letra
• Não havia escape por til, então "a~*b" não podia casar um asterisco literal
• Uma máscara malformada, como um colchete sem fechamento, lançava uma exceção que o chamador engolia como "sem casamento", transformando um typo num critério num total silenciosamente errado
• Do lado das lookups, MATCH e XLOOKUP tratavam só ~*, ~? e ~~ como escapes, então MATCH("a~b",…,0) encontrava o literal a~b em vez de ab

Se os seus workbooks só usaram * e ? sobre dados alfanuméricos puros, os resultados já estavam certos e não mudam. Se eles contêm colchetes, tildes, colunas de tipos mistos sob "<>text", ou critérios DSUM escritos como palavras nuas, recalculá-los com a v2.384.64 ou posterior pode mudar totais, e os novos totais são os que o Excel mostra. A mesma distinção entre como o Excel armazena um critério e como o compara aparece nos filtros salvos, discutida em o artigo do HotXLS sobre critérios DOPER do AutoFilter BIFF8

Referência rápida: regras de wildcard do Excel no HotXLS

• COUNTIF, SUMIF, AVERAGEIF e a família *IFS usam wildcards só quando o critério contém * ou ?; caso contrário comparam strings inteiras sem diferenciar maiúsculas e ~ é literal (desde a v2.384.52)
• MATCH com match type 0 e XLOOKUP com match_mode 2 sempre usam wildcards, então a~b encontra ab e o literal precisa de a~~b (desde a v2.384.52)
• Em modo wildcard ~ escapa qualquer caractere seguinte e um ~ no fim é descartado; [ e ] são caracteres comuns
• "<>text" conta números, booleans, erros e células vazias; um "<>" nu conta células não vazias, resultados ="" incluídos
• DSUM e as outras funções de banco de dados tratam texto puro como "começa com"; =text e <>text comparam a entrada inteira (desde a v2.384.64)
• Find de célula inteira com lxfUseWildcards e lxfWholeCell faz backtrack, então a*b casa abcb; Find e Replace tratam ~ como escape para qualquer caractere (desde a v2.384.60)
• A ordem de texto em critérios > e < segue a collation word sort do Excel, pontuação antes das letras (desde a v2.384.67)

Compatibilidade com o Excel num motor de fórmulas é quase toda feita de casos extremos como estes, medidos contra o Excel em vez de adivinhados da documentação. 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. Detalhes, edições e o download da versão trial estão na página do componente de planilha HotXLS para Delphi