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