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 contenga además * o ?; en modo wildcard de MATCH y XLOOKUP la tilde es siempre un escape, así que a~b encuentra ab; en DSUM y las demás funciones de base de datos el texto plano significa «empieza por»; y el Find de celda completa debe retroceder a la última *. HotXLS sigue estas reglas medidas desde v2.384.52, v2.384.60 y v2.384.64

Los reportes de bug en esta zona nunca mencionan wildcards. Dicen que un informe generado en el servidor cuenta un par de filas menos que el mismo archivo recalculado en Excel, o que un número de pieza con una tilde lo encuentra una fórmula y lo ignora la siguiente. La causa es un matcher que asume que un patrón significa lo mismo en todas partes. Excel no funciona así, así que un motor cuyos resultados cacheados deben coincidir 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 fallaba en silencio con los casos límite

¿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 coincidencia 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 siquiera. Las funciones de búsqueda (MATCH con match type 0, XLOOKUP con match_mode 2) siempre los aplican. Las funciones de base de datos (DSUM, DCOUNTA y compañeros) siguen el Advanced Filter, donde una palabra desnuda es un prefijo. El diálogo Find tiene sus propios modos de celda completa y parcial. La tabla de abajo lista qué celdas iguala cada patrón contra una columna con a~b, ab, AB, abc, abcb, a*b y axb, con todas las funciones en su modo por defecto sin distinguir mayúsculas

Patrón	COUNTIF / SUMIF	MATCH(…,0) / XLOOKUP mode 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	cada entrada, 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 la que hace discrepar a COUNTIF y MATCH, y los números de pieza y los códigos tecleados a mano llevan tildes más a menudo de lo que nadie espera. La fila a*b muestra la otra trampa: abc iguala para DSUM pero no para COUNTIF, porque la función de base de datos añade una * en silencio. Las entradas DSUM de ab, a*b y =ab salen directamente de ejecuciones en Excel 16; la entrada DSUM de a~b se sigue de la misma regla de prefijo, ya que la * añadida convierte el criterio en un patrón wildcard en el que ~b es una b escapada

¿Cuándo entra COUNTIF en modo wildcard?

COUNTIF entra en modo wildcard solo cuando el texto del criterio contiene * o ?, escapados o no. Sin ninguno de los dos caracteres, Excel compara el criterio con cada celda como cadena entera, 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. Añada una sola estrella y el significado se voltea: en "a~b*" la tilde ahora escapa la b, el patrón se lee como «ab seguido de cualquier cosa», y la celda a~b deja de contarse. HotXLS aplica esta regla en ambos motores desde v2.384.52, por medio de un único 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 cualquier otro sitio de Excel: ~ vuelve literal al siguiente carácter sea cual 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 nunca 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 comprobar 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 entera, abc excluida
    Show('=SUMIF(A1:A7,"a*b",B1:B7)');  // 119  cada fila excepto abc (8)
    Show('=COUNTIF(A1:A7,"a~*b")');     // 1    el 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 "<>text"?

Un criterio "<>text" 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 "<>" desnudo es una pregunta distinta por completo: significa «no es 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 la tragaba como «sin coincidencia», y las celdas numéricas caían del conteo en silencio. El lado de celdas vacías de esta historia, incluido a qué equivale un operando vacío en una comparación ordinaria, está tratado 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 sobre un rango de dos celdas con a~b y ab: MATCH("a~b",D1:D2,0) devuelve 2, y sobre un rango que contiene solo 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 lugar de esconderlas tras un único punto de entrada «igualar un patrón». El matcher en sí es compartido: desde v2.384.52, MATCH, XLOOKUP y las funciones de criterios ejecutan el mismo matcher de backtracking, con el mismo manejo de escapes y la misma regla de tilde final. Lo que difiere es la puerta delante de él. El camino de criterios pregunta primero «¿este texto contiene * o ??»; el camino de búsquedas nunca pregunta. Fusionar ambos arreglaría una familia y rompería la otra, y las dos direcciones se comprueban contra valores de Excel 16 en ambos motores. Las búsquedas con wildcard tienen además una precondición propia: XLOOKUP rechaza la coincidencia wildcard combinada 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 > iniciales como «empieza por», con los wildcards aún activos. Esa es la regla del Advanced Filter, y difiere de COUNTIF a propósito. Medido en Excel 16 sobre una columna Name con abc, ab, xab, AB, a~b y a*b: el criterio ab iguala abc, ab y AB; =ab iguala solo ab y AB; <>ab es una desigualdad de entrada completa; a*b y a? son también patrones de prefijo; >ab es una comparación ordinaria. Antes de v2.384.64 HotXLS igualaba ab exactamente, así que un DSUM sobre esos datos de prueba devolvía 10 donde Excel devuelve 11

El arreglo tuvo que rodear al parser de condiciones, que pliega tanto ab como =ab en la misma condición de igualdad. HotXLS inspecciona por tanto el texto del criterio en crudo antes de fiarse de la condición parseada: un criterio de texto cuyo primer carácter no es =, < ni > recibe una * añadida y pasa por el matcher wildcard, y todo lo demás conserva su comparación de entrada completa. Una nota práctica al construir rangos de criterios en código: en el motor XLSX, asignar la cadena '=ab' a TXLSXCell.Value guarda texto, mientras el motor clásico TXLSWorkbook compila un valor que empieza por = como fórmula 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 motor XLSX
      Writeln(Criteria[i], ' -> ',
        VarToStr(Book.Calculate('=DSUM(A1:B8,"Val",D1:D2)')));
    end;
    // ab   -> 30   ab, AB, abc, abcb (empieza por)
    // =ab  -> 6    ab, AB (entrada completa)
    // <>ab -> 121  todo excepto ab y AB
    // a*b  -> 127  a*b* iguala las siete, abc incluida
    // a~*  -> 32   solo el literal a*b
  finally
    Book.Free;
  end;
end;

Una diferencia relacionada sobrevivió al arreglo del prefijo y importa en compilaciones antiguas. Las comparaciones de texto como >ab usaban el orden de code points, mientras Excel pone la puntuación antes que 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 de texto ordinaria y la ordenación, usan la collation word sort de Excel bajo el locale de usuario actual, y ambos vuelven a coincidir

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

El Find de celda completa se perdía abcb porque el matcher se paraba en el primer punto donde el patrón se agotaba en lugar de retroceder a la última *. El matcher de coincidencia parcial detrás de Replace devuelve en cuanto el patrón se agota; el Find de celda completa lo reutilizaba y después exigía que la coincidencia cubriera la celda entera: a*b contra abcb se paraba 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 iguala abcb y a?b*b iguala axbyb, como hace el Find de Excel 16 con «Match entire cell contents» marcada

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 solo reconocía ~*, ~? 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, igualando cualquier celda como un patrón vacío, y HotXLS no imita eso

En el motor XLSX la búsqueda es TXLSXWorksheet.FindText con un conjunto TXLSXFindOptions: lxfUseWildcards activa *, ? y ~, lxfWholeCell exige que la celda entera iguale, y lxfMatchCase hace la comparación sensible a 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 bucle Find All avanza una columna más allá de cada acierto

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 retrocede
    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

    // Coincidencia parcial, Find All: la celda ancla se incluye, así que avance más allá de cada acierto
    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 reemplazo wildcard de celda completa reescribe solo el literal a~b
    Changed := Sheet.ReplaceText('a~~b', 'a-b', Opts);
    Writeln(Changed, ' cell(s) replaced');         // 1
  finally
    Book.Free;
  end;
end;

El bucle parcial encuentra las cuatro filas, incluida abc, porque en modo parcial a*b solo tiene que aparecer en algún sitio dentro de la celda. FindTextIn y ReplaceTextIn aceptan las mismas opciones más una ventana FirstRow, FirstCol, LastRow, LastCol, el equivalente programático de buscar dentro de una selección. El motor 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 equivalente, 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é hacía mal el matcher antiguo de máscaras DOS?

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

• [x] se leía como conjunto de caracteres, así que COUNTIF(A1:A10,"[x]") contaba las celdas con x en lugar del texto entre corchetes, y "[a-z]" igualaba cualquier celda de una sola letra
• No había escape de tilde, así que "a~*b" no podía igualar un asterisco literal
• Una máscara malformada, como un corchete sin cerrar, lanzaba una excepción que el llamador tragaba como «sin coincidencia», convirtiendo un typo en un criterio en un total silenciosamente equivocado
• Por el lado de búsquedas, MATCH y XLOOKUP trataban solo ~*, ~? y ~~ como escapes, así que MATCH("a~b",…,0) encontraba el literal a~b en lugar de ab

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

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

• COUNTIF, SUMIF, AVERAGEIF y la familia *IFS usan wildcards solo cuando el criterio contiene * o ?; si no, comparan cadenas enteras sin distinguir mayúsculas y ~ es literal (desde v2.384.52)
• MATCH con match type 0 y XLOOKUP con match_mode 2 usan siempre wildcards, así que a~b encuentra ab y el 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
• "<>text" cuenta números, booleanos, errores y celdas vacías; un "<>" desnudo cuenta celdas no vacías, resultados ="" incluidos
• DSUM y las demás funciones de base de datos tratan el texto plano como «empieza por»; =text y <>text comparan la entrada completa (desde v2.384.64)
• El Find de celda completa con lxfUseWildcards y lxfWholeCell retrocede, así que a*b iguala 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 que letras (desde v2.384.67)

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