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ó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 |
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
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
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
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í queCOUNTIF(A1:A10,"[x]")contaría celdas conxen 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 encontrarab, no el literala~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