Artículo técnico

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

Con a~b en D1 y ab en D2, =COUNTIF(D1:D2,"a~b") devuelve 1, y la celda que cuenta es D1. =MATCH("a~b",D1:D2,0) devuelve 2, que apunta a D2. Misma cadena de patrón, mismas dos celdas, y Excel elige una celda distinta según qué función lea el patrón. Un motor de hojas de cálculo que quiera que sus resultados cacheados coincidan con Excel tiene que reproducir esa bifurcación, junto con dos reglas más: las funciones de base de datos como DSUM tratan el texto desnudo como «empieza por», y el Find de celda completa tiene que retroceder a la última *

Los reportes que chocan con esto rara vez mencionan wildcards. Dicen que un libro generado en el servidor cuenta dos filas menos que el mismo archivo recalculado en un escritorio, o que un número de pieza con una tilde lo encuentra una fórmula y lo ignora la siguiente. Pasar cada patrón por una máscara de archivos estilo DOS acierta con los casos cotidianos, así que la desviación solo aparece cuando los códigos reales de producto empiezan a llevar ~ o [

Una cadena de patrón, cuatro conjuntos de reglas de Excel

Excel nunca unificó sus reglas de coincidencia; cada función trajo la suya. 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 el resto) heredan la regla del Advanced Filter, donde una palabra desnuda es un prefijo. El diálogo Find tiene sus propios modos de celda completa y parcial. HotXLS Delphi Component evalúa cada familia por su propia regla, tanto en el motor clásico como en el motor XLSX. La tabla lista qué celdas iguala cada patrón contra una columna con a~b, ab, AB, abc, abcb, a*b y axb, todas las funciones en su modo por defecto sin distinguir mayúsculas

PatrónCOUNTIF / SUMIFMATCH(…,0) / XLOOKUP mode 2Criterio DSUMFind, celda completa, wildcards activos
abab, ABab, ABab, AB, abc, abcbab, AB
a*ba~b, ab, AB, abcb, a*b, axbigual que COUNTIFcada entrada, abc incluidaigual que COUNTIF
a~bsolo a~bab, ABab, AB, abc, abcbab, AB
a~*bsolo a*bsolo a*bsolo a*bsolo a*b
=abab, ABno aplicaab, ABno aplica

Dos filas merecen una segunda mirada. En la fila a~b, COUNTIF y MATCH aterrizan en celdas distintas, y los códigos tecleados a mano llevan tildes más a menudo de lo que nadie planea. En la fila a*b, abc iguala para DSUM y falla para COUNTIF, porque la función de base de datos añade una * al criterio en silencio. La entrada DSUM de a~b se sigue de esa misma estrella añadida: una vez que está ahí, el criterio es un patrón wildcard, y ~b es simplemente 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, el criterio se compara con cada celda como cadena entera, sin distinguir mayúsculas, y una tilde es un carácter ordinario, así que COUNTIF(A1:A7,"a~b") cuenta la celda que contiene literalmente a~b. Una estrella le da la vuelta al significado. 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 sale del conteo. HotXLS aplica esta puerta en COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS y las funciones de base de datos por igual

Diagrama de la puerta wildcard de HotXLS: COUNTIF y SUMIF aplican wildcards solo cuando el criterio contiene una estrella o un signo de interrogación, así que a~b cuenta la celda literal y devuelve 1, mientras MATCH tipo 0 y XLOOKUP modo 2 están siempre en modo wildcard, así que a~b encuentra ab en la posición 2
La puerta es toda la diferencia: COUNTIF pide una estrella o un signo de interrogación antes de tratar una tilde como escape, MATCH nunca pregunta, así que una misma cadena de patrón cuenta una celda y encuentra la otra

Dentro del modo wildcard la regla de escape es corta. ~ vuelve literal al siguiente carácter sea cual sea, así que ~b significa b y ~~ significa una tilde; 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. La manera más rápida de comprobar todo esto contra sus propios datos es TXLSXWorkbook.Calculate, que evalúa una cadena de fórmula contra la hoja activa y devuelve un Variant; un libro recién creado convierte en activa la primera hoja que se le añade

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');            // primera hoja, así que es la activa
    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 de verdad "<>text"

Un criterio "<>text" cuenta toda celda que no sea ese texto, y en Excel eso incluye números, booleanos, valores de error y celdas vacías. Un "<>" desnudo pregunta otra cosa por completo: «no es una celda vacía». Se salta las celdas vacías pero cuenta todo valor, incluido el texto vacío que devuelve una fórmula como ="". La trampa de Delphi está en la comparación de Variant. Compare 'ab' con una celda numérica y Delphi intenta convertir 'ab' a número, la conversión lanza una excepción, y un handler que la traga como «sin coincidencia» saca las celdas numéricas del conteo. HotXLS las cuenta, así que las dos últimas líneas del ejemplo devuelven 7 y 8. 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 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 ?. Sobre el rango de dos celdas de la apertura, MATCH("a~b",D1:D2,0) devuelve 2. Sobre un rango que contiene solo a~b, la misma llamada devuelve #N/A. Buscar el texto literal a~b exige escribir "a~~b". Cuando las claves de búsqueda vienen de entrada de usuario o de un sistema externo y pueden contener una tilde, duplique cada tilde antes de construir la fórmula; ese único StringReplace cuesta menos que explicar luego una fila desaparecida

HotXLS mantiene esas dos decisiones separadas. MATCH, XLOOKUP y las funciones de criterios comparten un único 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 si el texto contiene * o ?. El camino de búsquedas nunca pregunta. Un único punto de entrada «igualar un patrón» usado por ambos acertaría con una familia y fallaría con la otra, pinchuese la puerta como se pinche. Las búsquedas con wildcard llevan una condición más: XLOOKUP devuelve #VALUE! cuando match_mode 2 se combina con un modo de búsqueda binaria, cosa que trata la guía de HotXLS de los modos de búsqueda de XLOOKUP y XMATCH

Criterios de DSUM: el texto desnudo significa «empieza por»

DSUM y las demás funciones de base de datos leen un criterio de texto sin =, < ni > iniciales como un prefijo, con los wildcards aún activos. Esa es la regla del Advanced Filter, y difiere de COUNTIF a propósito. Sobre una columna Name con abc, ab, xab y AB, el criterio ab iguala abc, ab y AB, mientras que =ab iguala solo ab y AB. <>ab es una desigualdad de entrada completa, a*b y a? son también patrones de prefijo, y >ab es una comparación de texto ordinaria. xab nunca iguala ab; la regla es «empieza por», no «contiene»

Lo incómodo para una implementación es que un parser de criterios que elimina el operador ve ab y =ab como la misma prueba de igualdad. Solo el texto del criterio en crudo los distingue. HotXLS mira por tanto ese texto 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

Diagrama de HotXLS de la regla de criterio de DSUM: un criterio de texto desnudo recibe una estrella añadida e iguala como prefijo así que ab alcanza ab, AB, abc y abcb, equals ab compara la entrada completa, angle bracket ab excluye ambas, y una tilde estrella sobrevive como el literal a*b, con los totales DSUM resultantes 30, 6, 121 y 32
Excel heredó la regla del Advanced Filter para las funciones de base de datos: el texto desnudo significa empieza por, mientras un igual o distinto inicial compara la entrada completa; HotXLS inspecciona el texto del criterio en crudo antes de fiarse de la condición parseada

Construir un rango de criterios en código tiene una trampa propia de cada motor. En el motor XLSX, asignar la cadena '=ab' a TXLSXCell.Value guarda texto, que es justo lo que necesita una celda de criterios. El motor clásico TXLSWorkbook compila un valor de cadena que empieza por = como fórmula salvo que lo prefije con un apóstrofe. Para rangos de criterios construidos en tiempo de ejecución, el motor XLSX es la opción menos propensa a errores. El bucle de abajo intercambia cinco criterios en D2 y recalcula el mismo DSUM cada vez

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;

Los criterios de orden como >ab siguen otra regla más. Una comparación simple por code points hace que "a~b">"ab" sea TRUE, porque ~ está por encima de todas las letras en Unicode. Excel pone la puntuación antes que las letras y devuelve FALSE. En Windows, HotXLS evalúa los criterios de texto > y <, la comparación de texto ordinaria y la ordenación con la collation word sort del sistema para el locale de usuario actual, de modo que las tres cosas coinciden entre sí y con Excel

Find de celda completa en Delphi: retroceder a la última estrella

El atajo tentador para el Find de celda completa es reutilizar el matcher parcial detrás de Replace y exigir después que la coincidencia cubra la celda entera. Falla con datos ordinarios. Un matcher parcial devuelve en cuanto el patrón se agota, así que a*b contra abcb se para tras ab, ha consumido 2 de 4 caracteres, y la celda queda rechazada. HotXLS pasa el Find de celda completa por un matcher aparte que trata «patrón terminado, texto no» como un desajuste más y reintenta desde la última estrella. Con eso, a*b iguala abcb y a?b*b iguala axbyb, como hace el Find de Excel con «Match entire cell contents» marcado

Diagrama de HotXLS del backtracking del Find wildcard de celda completa: el patrón a*b consume a y b en la celda abcb y y un matcher que se para cuando el patrón se agota rechaza la celda, mientras el matcher de HotXLS trata patrón terminado con texto restante como un desajuste más y reintenta desde la última estrella hasta que la celda entera iguala
Una coincidencia de celda completa no termina cuando el patrón se agota; tratar el texto sobrante como un desajuste más manda al matcher de vuelta a la última estrella, que es cómo a*b alcanza abcb como el Find de Excel

El Find de Excel, 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. Un matcher que solo conozca ~*, ~? y ~~ haría que a~b encontrara el texto a~b, la celda contraria. HotXLS escapa cualquier carácter tras una tilde, en Find y Replace igual que en fórmulas

En el motor XLSX la llamada es TXLSXWorksheet.FindText con un conjunto TXLSXFindOptions. lxfUseWildcards activa *, ? y ~; lxfWholeCell exige que la celda entera iguale; 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. lxfBackward y lxfByColumns cambian el orden de barrido. El ancla dada por StartRow y StartCol es inclusiva, así que un bucle de Find All tiene que avanzar 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 reporta las cuatro filas, abc incluida, 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, la versión programática de buscar dentro de una selección; pase 0 en cualquier borde para dejarlo abierto. El motor clásico expone las mismas reglas de coincidencia por una sobrecarga con tres booleanos, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), más una sobrecarga ReplaceText equivalente. Sus resultados de fila y columna son 1-based, y como la versión XLSX se saltan las celdas de fórmula y los valores que no son 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 qué una máscara de archivos DOS es el matcher equivocado para los criterios de Excel

Un matcher de máscaras de archivos como MatchesMask habla otro idioma distinto de los wildcards de Excel, aunque ambos se solapan en * y ?. Ese solape es exactamente por lo que se elige, y por lo que sus errores quedan escondidos hasta que los datos se ponen interesantes:

  • [x] se lee como un conjunto de caracteres, así que COUNTIF(A1:A10,"[x]") contaría celdas con x en lugar del texto entre corchetes, y "[a-z]" igualaría cualquier celda de una sola letra
  • No hay escape por tilde, así que "a~*b" no puede igualar una estrella literal
  • Una máscara malformada, como un corchete sin cerrar, lanza una excepción que un llamador se puede tragarse fácilmente como «sin coincidencia», convirtiendo una errata en un criterio en un total equivocado
  • En el lado de las búsquedas, un conjunto de escapes limitado a ~*, ~? y ~~ se queda corto también: MATCH("a~b",…,0) tiene que encontrar ab, no el literal a~b

Si sus libros solo usan * y ? sobre datos alfanuméricos planos, todos estos matchers coinciden y nada de esto importa. Los corchetes, las tildes, las columnas de tipos mezclados bajo "<>text" y los criterios DSUM escritos como palabras desnudas son donde los totales de un matcher más simple se desvían de los de Excel, y esos son los casos que ejercita el código de arriba. Los filtros guardados plantean una pregunta relacionada sobre cómo Excel guarda un criterio frente a cómo lo compara, tratada en el artículo de HotXLS sobre los criterios DOPER del AutoFilter BIFF8

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 en la máquina. Ediciones, documentación y la build de prueba están en la página del componente de hojas de cálculo Delphi HotXLS