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 o faz. No COUNTIF e no SUMIF o texto a~b é um literal a menos que o critério também contenha * ou ?; no MATCH e no XLOOKUP em modo wildcard o til é sempre um escape, por isso a~b encontra ab; no DSUM e nas outras funções de base de dados texto simples significa "começa por"; e o Find de célula inteira tem de recuar até ao último *. O HotXLS segue estas regras medidas desde a v2.384.52, v2.384.60 e v2.384.64

Os relatórios de bug nesta área nunca mencionam wildcards. Dizem que um relatório gerado no servidor conta umas linhas a menos do que o mesmo ficheiro 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 o lado. O Excel não funciona assim, por isso um motor cujos resultados em cache têm de coincidir com o Excel também não pode. Antes da v2.384.52 o HotXLS passava todos os critérios por uma máscara de ficheiros à DOS, que acertava nos padrões do dia a dia e errava em silêncio nos casos extremos

Porque é 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 correspondência de quatro funcionalidades e nunca as unificou. As funções de critérios (COUNTIF, SUMIF, AVERAGEIF e a família *IFS) decidem por critério se os wildcards se aplicam de todo. As funções de lookup (MATCH com match type 0, XLOOKUP com match_mode 2) aplicam-nas sempre. As funções de base de dados (DSUM, DCOUNTA e companhia) seguem o Advanced Filter, onde uma palavra nua é um prefixo. O diálogo Find tem os seus modos próprios de célula inteira e parcial. A tabela abaixo lista que células correspondem a cada padrão contra uma coluna que contém a~b, ab, AB, abc, abcb, a*b e axb, com todas as funções no seu modo por omissão sem distinguir 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	todas as entradas, abc incluído	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 aplicável	ab, AB	não aplicável

A linha do a~b é aquela em que o COUNTIF e o MATCH divergem, e números de peça e códigos escritos à mão contêm tis mais vezes do que ninguém espera. A linha do a*b mostra a outra armadilha: o abc corresponde para o DSUM mas não para o COUNTIF, porque a função de base de dados acrescenta silenciosamente um *. As entradas DSUM para ab, a*b e =ab vêm diretamente de corridas no Excel 16; a entrada DSUM para a~b decorre da mesma regra de prefixo, já que o * acrescentado torna o critério um padrão wildcard em que ~b é um b escapado

Quando é que o COUNTIF entra em modo wildcard?

O COUNTIF entra em modo wildcard apenas quando o texto do critério contém * ou ?, escapados ou não. Sem nenhum desses caracteres, o Excel compara o critério com cada célula como uma string inteira, sem distinguir maiúsculas, e um til é só um til, por isso o COUNTIF(A1:A7,"a~b") conta a célula que literalmente contém a~b. Acrescente uma única estrela e o significado vira: em "a~b*" o til agora escapa o b, o padrão lê-se como "ab seguido de qualquer coisa", e a célula a~b deixa de ser contada. O HotXLS aplica esta regra em ambos os motores desde a v2.384.52, através de um único matcher de critérios em lxCalc partilhado por COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS e as funções de base de dados

Dentro do modo wildcard as regras de escape são as mesmas de todo o resto do Excel: o ~ torna o próximo carácter literal seja ele qual for, por isso ~b significa b e ~~ significa um til, e um til no fim do padrão é descartado, por isso "a*~" comporta-se como "a*". Parênteses retos nunca são especiais. Um critério "[x]" conta células que contenham 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 folha ativa e devolve um Variant, a maneira mais rápida de verificar estas 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 um total SUMIF nomear as suas linhas
    end;
    Sheet.Cells[8, 1].Value := 5;                // um número; A9 fica em branco

    Show('=COUNTIF(A1:A7,"a~b")');      // 1    sem * nem ?: texto simples, 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  todas as linhas 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 o A9 em branco contam
    Show('=COUNTIF(A1:A9,"<>")');       // 8    células não em branco
  finally
    Book.Free;
  end;
end.

O que é que "<>text" conta?

Um critério "<>text" conta todas as células que não são aquele texto, e no Excel 16 isso inclui números, booleanos, valores de erro e células em branco. Um "<>" nu é uma pergunta completamente diferente: significa "não é uma célula em branco", por isso salta células vazias mas conta todos os valores, incluindo o texto vazio que uma fórmula como ="" devolve. O código antigo do HotXLS acertava nas células de texto mas não nos números: uma desigualdade de Variant fazia o Delphi converter 'ab' em número, a conversão lançava uma exceção, um handler engolia-a como "sem correspondência", e as células numéricas caíam silenciosamente fora da contagem. O lado das células em branco desta 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 em branco e SUMIF

Porque é que o MATCH encontra ab quando se procura a~b?

O MATCH encontra ab quando se procura a~b porque o MATCH com match type 0 e o XLOOKUP com match_mode 2 estão sempre em modo wildcard, por isso o til é um escape mesmo quando o padrão não contém * nem ?. O Excel 16 confirma-o num intervalo de duas células com a~b e ab: MATCH("a~b",D1:D2,0) devolve 2, e num intervalo que só contém a~b a mesma chamada devolve #N/A. Para procurar o texto literal a~b é preciso escrever "a~~b". Entretanto 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 as esconder atrás de um único ponto de entrada "corresponde a um padrão". O matcher em si é partilhado: desde a v2.384.52, o MATCH, o XLOOKUP e as funções de critérios correm o mesmo matcher de backtracking, com o mesmo tratamento de escapes e a mesma regra do til final. O que difere é o portão à frente dele. O caminho dos critérios pergunta primeiro "este texto contém * ou ??"; o caminho dos lookups nunca pergunta. Fundir os dois corrigiria uma família e partiria a outra, e ambas as direções são verificadas contra valores do Excel 16 em ambos os motores. Os lookups wildcard também têm uma pré-condição própria: o XLOOKUP rejeita correspondência wildcard combinada com um modo de pesquisa binária, regra descrita em o guia HotXLS dos modos de pesquisa XLOOKUP e XMATCH

Como é que o DSUM e as funções de base de dados leem um critério de texto simples?

O DSUM e as outras funções de base de dados leem um critério de texto sem =, < ou > inicial como "começa por", com wildcards ainda ativos. Essa é a regra do Advanced Filter, e difere do COUNTIF de propósito. O Excel 16 medido sobre uma coluna Name com abc, ab, xab, AB, a~b e a*b: o critério ab corresponde a abc, ab e AB; =ab corresponde só a 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 correspondia ab exatamente, por isso 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 funde ab e =ab na mesma condição de igualdade. O HotXLS por isso inspeciona o texto bruto do critério antes de confiar na condição analisada: um critério de texto cujo primeiro carácter não é =, < ou > recebe um * acrescentado e passa pelo matcher wildcard, e todo o resto mantém a sua comparação de entrada inteira. Uma nota prática quando constrói intervalos de critérios em código: no motor XLSX, atribuir a string '=ab' a TXLSXCell.Value grava texto, enquanto o motor clássico TXLSWorkbook compila um valor que começa por = como fórmula a menos que 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';            // header de critérios em D1
    for i := 0 to High(Criteria) do
    begin
      Sheet.Cells[2, 4].Value := Criteria[i];     // fica 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 por)
    // =ab  -> 6    ab, AB (entrada inteira)
    // <>ab -> 121  tudo exceto ab e AB
    // a*b  -> 127  a*b* casa os 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 interessa em compilações 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, por isso "a~b">"ab" é FALSE no Excel e era TRUE no HotXLS. Desde a v2.384.67 os critérios > e <, juntamente com a comparação de texto comum e a ordenação, usam a ordenação word sort do Excel sob o locale de utilizador atual, e os dois voltaram a coincidir

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

O Find de célula inteira perdeu o abcb porque o matcher parou no primeiro ponto em que o padrão se esgotou em vez de recuar até ao último *. O matcher de correspondência parcial por trás do Replace devolve assim que o padrão se esgota; o Find de célula inteira reutilizou-o e depois exigiu que a correspondência cobrisse a célula inteira: a*b contra abcb parou depois de ab, consumiu 2 caracteres em 4, e foi rejeitado. Desde a v2.384.60 o matcher de célula inteira é uma implementação separada que trata "padrão terminou, o texto não" como mais um desacerto e volta a tentar a partir da última estrela, por isso a*b corresponde a abcb e a?b*b corresponde a axbyb, como o Find do Excel 16 faz com "Match entire cell contents" marcado

A mesma versão mudou o til. O Find do Excel 16, tanto em modo de célula inteira como parcial, trata o ~ como escape para qualquer carácter seguinte: a~b encontra ab, a~~b encontra a~b, e um til final é ignorado, por isso q~ comporta-se como q. O matcher antigo do HotXLS reconhecia só ~*, ~? e ~~ como escapes, por isso a~b encontrava o texto a~b. Um padrão Find de um único ~ é instável no próprio Excel, correspondendo a qualquer célula como um padrão vazio, e o HotXLS não imita isso

No motor XLSX a pesquisa é o TXLSXWorksheet.FindText com um conjunto TXLSXFindOptions: lxfUseWildcards liga *, ? e ~, lxfWholeCell exige que a célula inteira corresponda, e lxfMatchCase torna a comparação sensível a maiúsculas. Sem lxfUseWildcards todos os caracteres, estrela incluída, são literais. O Find olha apenas para valores de texto; células numéricas são saltadas, e células de fórmula são saltadas a menos que lxfSearchFormulas esteja definido, caso em que o texto da fórmula é pesquisado. A âncora dada por StartRow e StartCol é inclusiva, por isso um ciclo Find All avança uma coluna para lá 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 rejeitado, abcb recua
    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

    // Correspondência parcial, Find All: a célula âncora está incluída, avance para lá 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;

    // Substituição 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 ciclo parcial encontra as quatro linhas, incluindo abc, porque em modo parcial o a*b só tem de ocorrer algures 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 pesquisar dentro de uma seleção. O motor clássico expõe as mesmas regras através de uma overload com três booleanos, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), mais uma overload de ReplaceText correspondente, com resultados de linha e coluna de 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 é que o matcher antigo de máscaras DOS errava?

O matcher antigo errava os caracteres especiais, porque uma máscara de ficheiros DOS é uma língua diferente de um wildcard do Excel. Antes da v2.384.52 as funções de critérios e as funções de base de dados passavam todos os padrões ao MatchesMask, um matcher de máscaras de ficheiros na unidade lxMasks. A sua sintaxe sobrepõe-se à do Excel nos casos comuns, razão pela qual o problema ficou escondido, mas diverge onde os dados reais ficam interessantes:

• O [x] era lido como um conjunto de caracteres, por isso o COUNTIF(A1:A10,"[x]") contava células com x em vez do texto entre parênteses retos, e "[a-z]" correspondia a qualquer célula de uma letra
• Não havia escape por til, por isso "a~*b" não conseguia corresponder a um asterisco literal
• Uma máscara malformada, como um parêntese reto não fechado, lançava uma exceção que o chamador engolia como "sem correspondência", transformando um erro de escrita num critério num total silenciosamente errado
• Do lado dos lookups, o MATCH e o XLOOKUP tratavam só ~*, ~? e ~~ como escapes, por isso o MATCH("a~b",…,0) encontrava o literal a~b em vez de ab

Se os seus livros só usaram * e ? sobre dados alfanuméricos simples, os resultados já estavam certos e não mudarão. Se contêm parênteses retos, tis, 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 guardados, discutida em o artigo HotXLS sobre critérios DOPER do AutoFilter BIFF8

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

• O COUNTIF, o SUMIF, o AVERAGEIF e a família *IFS usam wildcards só quando o critério contém * ou ?; caso contrário comparam strings inteiras sem distinguir maiúsculas e o ~ é literal (desde a v2.384.52)
• O MATCH com match type 0 e o XLOOKUP com match_mode 2 usam sempre wildcards, por isso a~b encontra ab e o literal precisa a~~b (desde a v2.384.52)
• Em modo wildcard o ~ escapa qualquer próximo carácter e um ~ final é descartado; [ e ] são caracteres comuns
• Um "<>text" conta números, booleanos, erros e células em branco; um "<>" nu conta células não em branco, resultados ="" incluídos
• O DSUM e as outras funções de base de dados tratam texto simples como "começa por"; =text e <>text comparam a entrada inteira (desde a v2.384.64)
• O Find de célula inteira com lxfUseWildcards e lxfWholeCell recua, por isso a*b corresponde a abcb; o Find e o Replace tratam ~ como escape para qualquer carácter (desde a v2.384.60)
• A ordem de texto nos critérios > e < segue a ordenação word sort do Excel, pontuação antes das letras (desde a v2.384.67)

A compatibilidade com o Excel num motor de fórmulas é sobretudo 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 sua biblioteca de funções nativamente em Delphi e C++Builder, tanto no motor clássico como no motor XLSX, sem Excel instalado. Detalhes, edições e a transferência de avaliação estão na página do componente de folhas de cálculo Delphi HotXLS