Wildcards de Excel en HotXLS: COUNTIF, MATCH, DSUM y Find

HotXLS Delphi Component lee la misma cadena de patrón de cuatro maneras distintas, porque Excel 16 lo hace. En COUNTIF y SUMIF el texto a~b es literal salvo que el criterio también contenga * o ?; en modo wildcard de MATCH y XLOOKUP la tilde siempre es un escape, así que a~b encuentra ab; en DSUM y las demás funciones de base de datos el texto plano significa "comienza con"; y el Find de celda completa debe hacer backtracking hacia el último *. HotXLS sigue estas reglas medidas desde v2.384.52, v2.384.60 y v2.384.64

Los reportes de bug en esta área jamás mencionan wildcards. Dicen que un reporte generado en el servidor cuenta un par de filas menos que el mismo archivo recalculado en Excel, o que un número de parte que contiene una tilde lo encuentra una fórmula y lo ignora la siguiente. La causa es un matcher que asume que un patrón significa una sola cosa en todas partes. Excel no funciona así, así que un engine cuyos resultados en caché deben concordar con Excel tampoco puede. Antes de v2.384.52 HotXLS pasaba cada criterio por una máscara de archivos estilo DOS, que acertaba con los patrones cotidianos y se equivocaba en silencio con los casos borde

¿Por qué una cadena de patrón significa cuatro cosas distintas en Excel?

Una cadena de patrón significa cuatro cosas distintas porque Excel heredó cuatro reglas de emparejamiento de cuatro funciones y nunca las unificó. Las funciones de criterios (COUNTIF, SUMIF, AVERAGEIF y la familia *IFS) deciden por criterio si los wildcards aplican o no. Las funciones de lookup (MATCH con match type 0, XLOOKUP con match_mode 2) siempre los aplican. Las funciones de base de datos (DSUM, DCOUNTA y compañeras) siguen el Advanced Filter, donde una palabra suelta es un prefijo. El diálogo Find tiene sus propios modos de celda completa y parcial. La tabla de abajo lista qué celdas empareja cada patrón contra una columna con a~b, ab, AB, abc, abcb, a*b y axb, con cada función en su modo por defecto sin distinguir mayúsculas

Patrón	COUNTIF / SUMIF	MATCH(…,0) / XLOOKUP modo 2	Criterio DSUM	Find, celda completa, wildcards activos
ab	ab, AB	ab, AB	ab, AB, abc, abcb	ab, AB
a*b	a~b, ab, AB, abcb, a*b, axb	igual que COUNTIF	todas las entradas, abc incluida	igual que COUNTIF
a~b	solo a~b	ab, AB	ab, AB, abc, abcb	ab, AB
a~*b	solo a*b	solo a*b	solo a*b	solo a*b
=ab	ab, AB	no aplica	ab, AB	no aplica

La fila a~b es donde COUNTIF y MATCH discrepan, y los números de parte y los códigos tecleados a mano contienen tildes más a menudo de lo que cualquiera esperaría. La fila a*b muestra la otra trampa: abc empareja para DSUM pero no para COUNTIF, porque la función de base de datos agrega un * en silencio. Las entradas DSUM de ab, a*b y =ab salen directo de corridas de Excel 16; la entrada DSUM de a~b se sigue de la misma regla de prefijo, ya que el * agregado convierte el criterio en un patrón wildcard donde ~b es una b escapada

¿Cuándo pasa COUNTIF a modo wildcard?

COUNTIF pasa a modo wildcard solo cuando el texto del criterio contiene * o ?, escapados o no. Sin ninguno de esos caracteres, Excel compara el criterio con cada celda como cadena completa, sin distinguir mayúsculas, y una tilde es solo una tilde, así que COUNTIF(A1:A7,"a~b") cuenta la celda que contiene literalmente a~b. Agregue una sola estrella y el significado se voltea: en "a~b*" la tilde ahora escapa a la b, el patrón se lee como "ab seguido de lo que sea", y la celda a~b ya no se cuenta. HotXLS aplica esta regla en ambos engines desde v2.384.52, por medio de un matcher de criterios en lxCalc compartido por COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS y las funciones de base de datos

Dentro del modo wildcard las reglas de escape son las mismas que en el resto de Excel: ~ vuelve literal al siguiente carácter sea lo que sea, así que ~b significa b y ~~ significa una tilde, y una tilde al final mismo del patrón se descarta, así que "a*~" se comporta como "a*". Los corchetes jamás son especiales. Un criterio "[x]" cuenta las celdas que contienen los tres caracteres [x], y "[a-z]" no cuenta nada sobre datos ordinarios. TXLSXWorkbook.Calculate evalúa una cadena de fórmula contra la hoja activa y devuelve un Variant, la manera más rápida de verificar estas reglas contra sus propios datos

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 que un total SUMIF nombre sus filas
    end;
    Sheet.Cells[8, 1].Value := 5;                // un número; A9 queda vacía

    Show('=COUNTIF(A1:A7,"a~b")');      // 1    sin * ni ?: texto plano, la celda a~b
    Show('=COUNTIF(A1:A7,"a~b*")');     // 4    modo wildcard: ab, AB, abc, abcb
    Show('=COUNTIF(A1:A7,"a*b")');      // 6    wildcard de cadena completa, abc excluida
    Show('=SUMIF(A1:A7,"a*b",B1:B7)');  // 119  todas las filas menos abc (8)
    Show('=COUNTIF(A1:A7,"a~*b")');     // 1    la literal a*b
    Show('=COUNTIF(A1:A9,"<>ab")');     // 7    el número 5 y la A9 vacía cuentan
    Show('=COUNTIF(A1:A9,"<>")');       // 8    celdas no vacías
  finally
    Book.Free;
  end;
end.

¿Qué cuenta "<>texto"?

Un criterio "<>texto" cuenta toda celda que no sea ese texto, y en Excel 16 eso incluye números, booleanos, valores de error y celdas vacías. Un "<>" suelto es otra pregunta por completo: significa "no una celda vacía", así que se salta las celdas vacías pero cuenta todo valor, incluido el texto vacío que devuelve una fórmula como ="". El código antiguo de HotXLS acertaba con las celdas de texto pero no con los números: una desigualdad de Variant hacía que Delphi convirtiera 'ab' a número, la conversión lanzaba una excepción, un handler se la tragaba como "sin match", y las celdas numéricas caían del conteo en silencio. El lado de celdas vacías de esta historia, incluido contra qué iguala un operando vacío en una comparación ordinaria, está cubierto en cómo maneja HotXLS las cadenas de comparación, las celdas vacías y SUMIF

¿Por qué MATCH encuentra ab cuando busca a~b?

MATCH encuentra ab cuando busca a~b porque MATCH con match type 0 y XLOOKUP con match_mode 2 están siempre en modo wildcard, así que la tilde es un escape aunque el patrón no contenga ni * ni ?. Excel 16 lo confirma en un rango de dos celdas con a~b y ab: MATCH("a~b",D1:D2,0) devuelve 2, y sobre un rango que solo contiene a~b la misma llamada devuelve #N/A. Para buscar el texto literal a~b hay que escribir "a~~b". Mientras tanto, COUNTIF(D1:D2,"a~b") sobre las mismas dos celdas devuelve 1, contando la otra celda. Misma cadena, mismo rango, celda contraria

Por eso HotXLS mantiene las dos decisiones separadas en vez de esconderlas detrás de un único punto de entrada "empareja un patrón". El matcher en sí es compartido: desde v2.384.52, MATCH, XLOOKUP y las funciones de criterios corren el mismo matcher con backtracking, el mismo manejo de escapes y la misma regla de tilde final. Lo que difiere es la compuerta delante de él. La ruta de criterios pregunta primero "¿este texto contiene * o ??"; la ruta de lookup nunca pregunta. Fusionarlas arreglaría una familia y rompería la otra, y ambas direcciones se verifican contra valores de Excel 16 en ambos engines. Los lookups con wildcard además tienen una precondición propia: XLOOKUP rechaza el matching con wildcard combinado con un modo de búsqueda binaria, regla descrita en la guía de HotXLS de los modos de búsqueda de XLOOKUP y XMATCH

¿Cómo leen DSUM y las funciones de base de datos un criterio de texto plano?

DSUM y las demás funciones de base de datos leen un criterio de texto sin =, < ni > al inicio como "comienza con", con wildcards aún activos. Esa es la regla del Advanced Filter, y difiere de COUNTIF a propósito. Excel 16 medido sobre una columna Name con abc, ab, xab, AB, a~b y a*b: el criterio ab empareja abc, ab y AB; =ab solo empareja ab y AB; <>ab es una desigualdad de entrada completa; a*b y a? también son patrones de prefijo; >ab es una comparación ordinaria. Antes de v2.384.64 HotXLS emparejaba ab exacto, así que un DSUM sobre esos datos de prueba devolvía 10 donde Excel devuelve 11

La corrección tuvo que sortear el parser de condiciones, que pliega tanto ab como =ab en la misma condición de igualdad. HotXLS por eso inspecciona el texto crudo del criterio antes de fiarse de la condición parseada: un criterio de texto cuyo primer carácter no es =, < ni > recibe un * agregado y pasa por el matcher wildcard, y todo lo demás conserva su comparación de entrada completa. Una nota práctica cuando arme rangos de criterios en código: en el engine XLSX, asignar la cadena '=ab' a TXLSXCell.Value guarda texto, mientras que el engine clásico TXLSWorkbook compila como fórmula un valor que empieza con = salvo que lo prefije con un apóstrofe

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';            // cabecera de criterios en D1
    for i := 0 to High(Criteria) do
    begin
      Sheet.Cells[2, 4].Value := Criteria[i];     // queda como texto en el engine XLSX
      Writeln(Criteria[i], ' -> ',
        VarToStr(Book.Calculate('=DSUM(A1:B8,"Val",D1:D2)')));
    end;
    // ab   -> 30   ab, AB, abc, abcb (comienza con)
    // =ab  -> 6    ab, AB (entrada completa)
    // <>ab -> 121  todo menos ab y AB
    // a*b  -> 127  a*b* empareja las siete, abc incluida
    // a~*  -> 32   solo la literal a*b
  finally
    Book.Free;
  end;
end;

Una diferencia relacionada sobrevivió a la corrección del prefijo y importa en builds antiguos. Las comparaciones de texto como >ab usaban orden por code point, mientras que Excel pone la puntuación antes de las letras, así que "a~b">"ab" es FALSE en Excel y era TRUE en HotXLS. Desde v2.384.67 los criterios > y <, junto con la comparación y el ordenamiento de texto ordinarios, usan la collation word sort de Excel bajo el locale de usuario actual, y los dos vuelven a concordar

¿Por qué el Find de celda completa se perdía abcb?

El Find de celda completa se perdía abcb porque el matcher se detenía en el primer punto donde el patrón se agotaba en vez de hacer backtracking hacia el último *. El matcher de match parcial detrás de Replace retorna apenas el patrón se agota; el Find de celda completa lo reutilizaba y después exigía que el match cubriera la celda entera: a*b contra abcb se detenía tras ab, consumía 2 caracteres de 4, y era rechazado. Desde v2.384.60 el matcher de celda completa es una implementación aparte que trata "patrón terminado, texto no" como un desajuste más y reintenta desde la última estrella, así que a*b empareja abcb y a?b*b empareja axbyb, como hace el Find de Excel 16 con "Match entire cell contents" marcado

La misma versión cambió la tilde. El Find de Excel 16, tanto en modo de celda completa como parcial, trata ~ como escape de cualquier carácter siguiente: a~b encuentra ab, a~~b encuentra a~b, y una tilde final se ignora, así que q~ se comporta como q. El matcher antiguo de HotXLS reconocía solo ~*, ~? y ~~ como escapes, así que a~b encontraba el texto a~b. Un patrón Find de una sola ~ es inestable en el propio Excel, emparejando cualquier celda como un patrón vacío, y HotXLS no imita eso

En el engine XLSX la búsqueda es TXLSXWorksheet.FindText con un set de TXLSXFindOptions: lxfUseWildcards enciende *, ? y ~, lxfWholeCell exige que la celda completa empareje, y lxfMatchCase hace la comparación con distinción de mayúsculas. Sin lxfUseWildcards cada carácter, estrella incluida, es literal. Find mira solo valores de texto; las celdas numéricas se saltan, y las celdas de fórmula se saltan salvo que lxfSearchFormulas esté activo, en cuyo caso se busca el texto de la fórmula. El ancla dada por StartRow y StartCol es inclusiva, así que un loop de Find All avanza una columna más allá 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 rechazada, abcb por backtracking
    if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
      Writeln('a~b  whole cell -> row ', Row);   // 4: ~b es una b escapada
    if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
      Writeln('a~~b whole cell -> row ', Row);   // 3: ~~ es una tilde literal

    // Match parcial, Find All: la celda ancla se incluye, así que avance más allá 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);     // filas 1, 2, 3 y 4
      NextRow := Row;
      NextCol := Col + 1;
    end;

    // El replace con wildcard de celda completa reescribe solo la literal a~b
    Changed := Sheet.ReplaceText('a~~b', 'a-b', Opts);
    Writeln(Changed, ' cell(s) replaced');         // 1
  finally
    Book.Free;
  end;
end;

El loop parcial encuentra las cuatro filas, incluida abc, porque en modo parcial a*b solo tiene que ocurrir en algún lugar dentro de la celda. FindTextIn y ReplaceTextIn toman las mismas opciones más una ventana FirstRow, FirstCol, LastRow, LastCol, el equivalente programático de buscar dentro de una selección. El engine clásico expone las mismas reglas por una sobrecarga con tres booleanos, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), más una sobrecarga ReplaceText correspondiente, con resultados de fila y columna basados en 1:

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;

¿Qué se equivocaba el matcher antiguo de máscaras DOS?

El matcher antiguo se equivocaba con los caracteres especiales, porque una máscara de archivos DOS es un idioma distinto de un wildcard de Excel. Antes de v2.384.52 las funciones de criterios y las funciones de base de datos pasaban cada patrón a MatchesMask, un matcher de máscaras de archivos en la unidad lxMasks. Su sintaxis se solapa con la de Excel en los casos comunes, razón por la cual el problema quedó escondido, pero diverge donde los datos reales se ponen interesantes:

• [x] se leía como un set de caracteres, así que COUNTIF(A1:A10,"[x]") contaba las celdas con x en vez del texto entre corchetes, y "[a-z]" emparejaba cualquier celda de una letra
• No había escape de tilde, así que "a~*b" no podía emparejar un asterisco literal
• Una máscara mal formada, como un corchete sin cerrar, lanzaba una excepción que quien llamaba se tragaba como "sin match", convirtiendo un typo en un criterio en un total silenciosamente equivocado
• Del lado de los lookups, MATCH y XLOOKUP trataban solo ~*, ~? y ~~ como escapes, así que MATCH("a~b",…,0) encontraba la literal a~b en vez de ab

Si sus workbooks solo usaron * y ? sobre datos alfanuméricos simples, los resultados ya eran correctos y no cambiarán. Si contienen corchetes, tildes, columnas de tipos mezclados bajo "<>texto", o criterios DSUM escritos como palabras sueltas, recalcularlos con v2.384.64 o posterior puede cambiar totales, y los totales nuevos son los que Excel muestra. La misma distinción entre cómo Excel guarda un criterio y cómo lo compara aparece en los filtros guardados, discutida en el artículo de HotXLS sobre criterios DOPER del AutoFilter BIFF8

Referencia rápida: reglas de wildcard de Excel en HotXLS

• COUNTIF, SUMIF, AVERAGEIF y la familia *IFS usan wildcards solo cuando el criterio contiene * o ?; si no, comparan cadenas completas sin distinguir mayúsculas y ~ es literal (desde v2.384.52)
• MATCH con match type 0 y XLOOKUP con match_mode 2 siempre usan wildcards, así que a~b encuentra ab y la literal necesita a~~b (desde v2.384.52)
• En modo wildcard ~ escapa a cualquier carácter siguiente y una ~ final se descarta; [ y ] son caracteres ordinarios
• "<>texto" cuenta números, booleanos, errores y celdas vacías; un "<>" suelto cuenta las celdas no vacías, resultados ="" incluidos
• DSUM y las demás funciones de base de datos tratan el texto plano como "comienza con"; =text y <>text comparan la entrada completa (desde v2.384.64)
• El Find de celda completa con lxfUseWildcards y lxfWholeCell hace backtracking, así que a*b empareja abcb; Find y Replace tratan ~ como escape de cualquier carácter (desde v2.384.60)
• El orden de texto en los criterios > y < sigue la collation word sort de Excel, puntuación antes de letras (desde v2.384.67)

La compatibilidad con Excel en un motor de fórmulas es sobre todo casos borde como estos, medidos contra Excel y no adivinados desde la documentación. HotXLS evalúa COUNTIF, MATCH, XLOOKUP, DSUM y el resto de su librería de funciones de forma nativa en Delphi y C++Builder, tanto en el engine clásico como en el engine XLSX, sin Excel instalado. Detalles, ediciones y la descarga de prueba están en la página del componente de hojas de cálculo HotXLS para Delphi