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 engine de hojas de cálculo que quiere que sus resultados en caché concuerden con Excel tiene que reproducir esa separación, junto con dos reglas más: las funciones de base de datos como DSUM tratan el texto suelto como "comienza con", y el Find de celda completa tiene que hacer backtracking hacia la última *

Los reportes que chocan con esto rara vez mencionan wildcards. Dicen que un workbook generado en el servidor cuenta dos filas menos que el mismo archivo recalculado en una computadora de escritorio, o que un número de parte que contiene 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 deriva solo aparece cuando los códigos de producto reales empiezan a contener ~ o [

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

Excel nunca unificó sus reglas de emparejamiento; cada función trajo la suya. 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) heredan la regla del Advanced Filter, donde una palabra suelta 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 engine clásico como en el engine XLSX. La tabla 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ónCOUNTIF / SUMIFMATCH(…,0) / XLOOKUP modo 2Criterio DSUMFind, celda completa, wildcards activos
abab, ABab, ABab, AB, abc, abcbab, AB
a*ba~b, ab, AB, abcb, a*b, axbigual que COUNTIFtodas las entradas, 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 caen en celdas distintas, y los códigos tecleados a mano contienen tildes más a menudo de lo que cualquiera planea. En la fila a*b, abc empareja para DSUM y falla para COUNTIF, porque la función de base de datos agrega un * al criterio en silencio. La entrada DSUM de a~b se sigue de esa misma estrella agregada: una vez que está ahí, el criterio es un patrón wildcard, y ~b es apenas 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 los dos caracteres, el criterio se compara con cada celda como cadena completa, 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 sola estrella voltea el significado. 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 sale del conteo. HotXLS aplica esta compuerta por igual en COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS y las funciones de base de datos

Diagrama de la compuerta 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 que 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 compuerta es toda la diferencia: COUNTIF pide una estrella o 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 lo que 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 verificar 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 workbook recién creado hace de su primera hoja agregada la hoja activa

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

Lo que "<>texto" cuenta en realidad

Un criterio "<>texto" cuenta toda celda que no sea ese texto, y en Excel eso incluye números, booleanos, valores de error y celdas vacías. Un "<>" suelto pregunta otra cosa por completo: "no 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 se la traga como "sin match" saca las celdas numéricas del conteo. HotXLS las cuenta, así que las últimas dos líneas del ejemplo devuelven 7 y 8. El lado de celdas vacías de esto, 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 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 solo contiene a~b, la misma llamada devuelve #N/A. Buscar el texto literal a~b toma "a~~b". Cuando las claves de lookup vienen de entrada del usuario o de un sistema externo y pueden contener una tilde, duplique cada tilde antes de armar la fórmula; ese único StringReplace sale más barato que explicar después una fila que falta

HotXLS mantiene estas dos decisiones separadas. MATCH, XLOOKUP y las funciones de criterios comparten un 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 si el texto contiene * o ?. La ruta de lookup nunca pregunta. Un único punto de entrada "empareja un patrón" usado por ambos dejaría una familia bien y la otra mal, con la compuerta puesta como se la ponga. Los lookups con wildcard llevan una condición más: XLOOKUP devuelve #VALUE! cuando match_mode 2 se combina con un modo de búsqueda binaria, regla cubierta en la guía de HotXLS de los modos de búsqueda de XLOOKUP y XMATCH

Criterios de DSUM: el texto suelto significa "comienza con"

DSUM y las demás funciones de base de datos leen un criterio de texto sin =, < ni > al inicio como un prefijo, con wildcards todavía 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 empareja abc, ab y AB, mientras que =ab solo empareja ab y AB. <>ab es una desigualdad de entrada completa, a*b y a? también son patrones de prefijo, y >ab es una comparación de texto ordinaria. xab nunca empareja ab; la regla es "comienza con", no "contiene"

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

Diagrama de HotXLS de la regla de criterios de DSUM: un criterio de texto suelto recibe una estrella agregada y empareja como prefijo, así que ab alcanza a ab, AB, abc y abcb, un igual ab compara la entrada completa, un angle bracket ab excluye a ambos, y una tilde estrella sobrevive como la literal a*b, con los totales DSUM medidos 30, 6, 121 y 32
Excel heredó la regla del Advanced Filter para las funciones de base de datos: texto suelto significa comienza con, mientras que un igual o distinto de al inicio compara la entrada completa; HotXLS inspecciona el texto crudo del criterio antes de fiarse de la condición parseada

Armar un rango de criterios en código tiene una trampa propia de cada engine. En el engine XLSX, asignar la cadena '=ab' a TXLSXCell.Value guarda texto, que es exactamente lo que una celda de criterios necesita. El engine clásico TXLSWorkbook compila como fórmula un valor de cadena que empieza con = salvo que usted lo prefije con un apóstrofe. Para rangos de criterios armados en tiempo de ejecución, el engine XLSX es la elección menos propensa a errores. El loop 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 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;

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

Find de celda completa en Delphi: backtracking hacia la última estrella

El atajo tentador para el Find de celda completa es reutilizar el matcher parcial que está detrás de Replace y después exigir que el match cubra la celda entera. Falla con datos ordinarios. Un matcher parcial retorna apenas el patrón se agota, así que a*b contra abcb se detiene tras ab, ha consumido 2 de 4 caracteres, y la celda se rechaza. HotXLS corre 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 empareja abcb y a?b*b empareja 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 la a y la b en la celda abcb y un matcher que se detiene cuando el patrón se agota rechaza la celda, mientras que 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 completa empareja
Un match 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 a 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 conoce ~*, ~? y ~~ haría que a~b encuentre el texto a~b, la celda contraria. HotXLS escapa cualquier carácter después de una tilde, en Find y Replace igual que en las fórmulas

En el engine XLSX la llamada es TXLSXWorksheet.FindText con un set de TXLSXFindOptions. lxfUseWildcards enciende *, ? y ~; lxfWholeCell exige que la celda completa empareje; 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. lxfBackward y lxfByColumns cambian el orden de barrido. El ancla dada por StartRow y StartCol es inclusiva, así que un loop de Find All tiene que avanzar 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 reporta las cuatro filas, abc incluida, 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, la versión programática de buscar dentro de una selección; pase 0 para cualquier límite que quiera dejar abierto. El engine clásico expone las mismas reglas de emparejamiento por una sobrecarga con tres booleanos, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), más una sobrecarga ReplaceText correspondiente. Sus resultados de fila y columna son base uno, y como la versión XLSX se salta 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 un idioma distinto del de los wildcards de Excel, aunque los dos se solapan en * y ?. Ese solape es exactamente la razón por la que se lo elige, y por la que sus errores quedan escondidos hasta que los datos se ponen interesantes:

  • [x] se lee como un set de caracteres, así que COUNTIF(A1:A10,"[x]") contaría las celdas con x en vez del texto entre corchetes, y "[a-z]" emparejaría cualquier celda de una sola letra
  • No hay escape de tilde, así que "a~*b" no puede emparejar un asterisco literal
  • Una máscara mal formada, como un corchete sin cerrar, lanza una excepción que quien llama puede tragarse fácilmente como "sin match", convirtiendo un typo en un criterio en un total equivocado
  • Del lado de los lookups, un set de escapes limitado a ~*, ~? y ~~ también se queda corto: MATCH("a~b",…,0) tiene que encontrar ab, no la literal a~b

Si sus workbooks solo usan * y ? sobre datos alfanuméricos simples, todos estos matchers concuerdan y nada de esto importa. Los corchetes, las tildes, las columnas de tipos mezclados bajo "<>texto" y los criterios DSUM escritos como palabras sueltas son donde los totales de un matcher más simple derivan de los de Excel, y esos son los casos que el código de arriba ejercita. Los filtros guardados plantean una pregunta relacionada sobre cómo Excel guarda un criterio en contraste con cómo lo compara, discutida en el artículo de HotXLS sobre criterios DOPER del AutoFilter BIFF8

HotXLS evalúa COUNTIF, MATCH, XLOOKUP, DSUM y el resto de su librería de funciones de forma nativa en Delphi y C++Builder, en el engine clásico y en el engine XLSX, sin Excel instalado en la máquina. Las ediciones, la documentación y la build de prueba están listadas en la página del componente de hojas de cálculo HotXLS para Delphi