Una comprobación de rango escrita como =0<B2<100 devuelve FALSE en Excel en todas las filas, incluida la fila donde B2 guarda 50. Excel evalúa primero 0<B2, obtiene TRUE, y después pregunta si TRUE es menor que 100; un valor lógico se jerarquiza por encima de todo número, así que la respuesta es FALSE sea lo que contenga B2. Las celdas vacías y SUMIF guardan sorpresas del mismo tipo: una celda vacía equivale a 0, a "" y a FALSE al mismo tiempo, y =SUMIF(A1:A10,">5",B1) suma diez celdas de la columna B
Un motor de cálculo que recalcula semejante workbook fuera de Excel tiene que reproducir esas respuestas, incluso cuando claramente no son lo que el autor quiso decir. El resultado cacheado guardado en el fichero es lo que ve un visor, una vista previa de correo o una importación río abajo antes de que nadie pulse F9. Si el servidor escribe TRUE para la comprobación de rango y Excel después muestra FALSE, el mismo informe contiene dos números distintos según dónde se abrió, y nadie sabe a cuál hacer caso. Arreglar la fórmula del autor es trabajo del autor
¿Por qué =1<2<3 devuelve FALSE en Excel?
Excel devuelve FALSE para =1<2<3 porque los seis operadores de comparación comparten un nivel de precedencia y se asocian de izquierda a derecha, así que la fórmula significa (1<2)<3, y el TRUE interior pierde luego el concurso de jerarquía de tipos contra el número 3. HotXLS Delphi Component parsea las cadenas de comparación de la misma manera en sus dos motores de workbook, el clásico TXLSWorkbook y el nativo XLSX TXLSXWorkbook. El parser lee un operando, y mientras el siguiente token sea =, <>, <, >, <= o >=, construye un nodo de comparación con todo lo parseado hasta entonces como hijo izquierdo y el siguiente operando como hijo derecho, y arrastra ese nodo a la siguiente ronda. Esa es la misma forma de bucle que usa para +, - y &
La trampa vive en la gramática de manual. Escrita como expr = operand op expr, un parser de descenso recursivo recursa en sí mismo para el lado derecho y convierte al operador en asociativo por la derecha sin hacer ruido. Bajo esa lectura =1<2<3 pasa a ser 1<(2<3), que es 1<TRUE, que es TRUE. El error corre en ambas direcciones: =3>2>1 es TRUE en Excel y FALSE bajo asociatividad por la derecha, y =1=1=TRUE se voltea igual. Las formas entre paréntesis salen bien de cualquier manera, así que el fallo se cuela en cualquier prueba que solo compruebe (a<b)<c
El método Calculate evalúa una cadena de fórmula sin meterla en una celda, lo que lo convierte en una manera rápida de comprobar toda una lista de cadenas contra ambos motores. El método está cubierto con más profundidad en el repaso al motor de fórmulas de HotXLS
uses
SysUtils, Variants, lxCalc, lxHandle, lxHandleX; // lxOk se declara en lxCalc
const
Formulas: array [0..6] of string = ('=1<2<3', '=3>2>1', '=(1<2)<3',
'=1<(2<3)', '=1=1=TRUE', '=3>2>1=TRUE', '=1<2<3=FALSE');
// Respuestas de Excel: FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE
var
Classic: IXLSWorkbook;
Xlsx: TXLSXWorkbook;
i: Integer;
begin
Classic := TXLSWorkbook.Create; // con recuento de referencias a través de la interfaz
Xlsx := TXLSXWorkbook.Create;
try
// TXLSXWorkbook.Calculate evalúa contra la hoja activa y
// devuelve Null cuando el workbook aún no tiene ninguna hoja
Xlsx.Sheets.Add('Data');
for i := 0 to High(Formulas) do
Writeln(Formulas[i], ' classic=', VarToStr(Classic.Calculate(Formulas[i])),
' xlsx=', VarToStr(Xlsx.Calculate(Formulas[i])));
finally
Xlsx.Free;
end;
end;
Números, texto y booleans: la jerarquía entre tipos
Cuando los dos lados de una comparación tienen tipos distintos, Excel no convierte uno en el otro. Jerarquiza los tipos en cambio: todo número es menor que todo valor de texto, y todo valor de texto es menor que todo boolean. HotXLS clasifica ambos operandos como número, texto o boolean y, cuando las clases difieren, compara solo las clases. Así que =TRUE>99999 es TRUE, ="1">2 es TRUE, y =FALSE>"zzz" es TRUE también. Esta es la pieza que hace que las comparaciones encadenadas parezcan arbitrarias. TRUE<3 no es TRUE convertido a la fuerza en 1 y comparado con 3; es un boolean comparado con un número, y gana el boolean
Dentro de una clase la comparación es la natural, con un giro para el texto: la comparación de cadenas ignora mayúsculas y minúsculas, así que ="abc"="ABC" devuelve TRUE en Excel y en HotXLS. Las fechas son números de serie para el motor, lo que significa que una fecha se jerarquiza siempre por debajo de cualquier texto, incluida una celda de texto que solo parezca una fecha. Una columna de fechas importadas donde la mitad de las celdas llegaron como cadenas se ordenará y comparará en dos bandas separadas, y =A2>DATE(2026,1,1) devuelve TRUE para cada una de las celdas de cadena
¿A qué equivale una celda vacía en una comparación?
Una celda vacía usada como operando de comparación toma el tipo del otro lado: equivale a 0 frente a un número, a "" frente a texto y a FALSE frente a un valor lógico. Con A1 vacía, =A1=0, =A1="" y =A1=FALSE son todos TRUE en Excel. HotXLS hace la misma sustitución antes de jerarquizar los dos operandos, para los seis operadores de comparación y en ambos motores. Cuando ambos operandos están vacíos no hay sustitución, y comparan iguales
var
Wb: IXLSWorkbook;
Sh: TXLSWorksheet;
begin
Wb := TXLSWorkbook.Create;
Sh := Wb.Sheets.Add; // la primera hoja pasa a ser la activa
Sh.Range['B1', 'B1'].Value := 5; // A1 se deja vacía a propósito
Writeln(VarToStr(Wb.Calculate('=A1=0'))); // True
Writeln(VarToStr(Wb.Calculate('=A1=""'))); // True
Writeln(VarToStr(Wb.Calculate('=A1=FALSE'))); // True
Writeln(VarToStr(Wb.Calculate('=A1<B1'))); // True: el vacío compara como 0
Writeln(VarToStr(Wb.Calculate('=A1<0'))); // False: 0 no está por debajo de 0
end;
La última línea es la que muerde en la práctica. La aritmética ya trata un vacío como 0, así que =A1+1 da 1 en cualquier motor. La comparación es donde un atajo sale mal. Un motor que mantiene «vacío» como su propio rango más bajo, por debajo de todo número, responde TRUE para =A1<0, y entonces =IF(A1<0,"overdrawn","ok") etiqueta cada celda de saldo vacía como en descubierto. Sustituir solo por 0 o por la cadena vacía tampoco basta: frente a un boolean el vacío pasaría a ser 0, que se jerarquiza por debajo tanto de TRUE como de FALSE, y =A1=FALSE saldría FALSE sobre una celda vacía
Hay una consecuencia que conviene tener prevista. Como un vacío compara como FALSE frente a un valor lógico, =A1=FALSE no puede distinguir una celda vacía de una celda que guarda FALSE, ni en Excel ni en HotXLS, y =A1=0 no distingue vacío de cero. Las plantillas que necesitan saber si alguien realmente introdujo un valor deberían comprobar con ISBLANK(A1). La forma =A1="" es más débil, ya que también es TRUE para una fórmula que devuelve una cadena vacía
¿Por qué SUMIF con un rango de suma de una sola celda cubre toda la columna?
Excel conserva la forma del rango de criterios y usa el rango de suma solo para su celda superior izquierda, así que =SUMIF(A1:A10,">5",B1) significa B1:B10. Muchas plantillas hechas a mano dependen de esa abreviatura sin que sus autores lo sepan. La implementación obvia, recortar el recorrido al menor de los dos rangos, reduce el ejemplo a una sola prueba de A1 contra B1 y devuelve 0. HotXLS recorre el rango de criterios y lee cada valor en el mismo offset de fila y columna desde la esquina superior izquierda del rango de suma. AVERAGEIF pasa por el mismo recorrido y recibe el mismo redimensionado, y un rango de suma mayor que el rango de criterios se recorta a la forma de los criterios
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Row: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Sales');
for Row := 1 to 10 do
begin
Sheet.Cells[Row, 1].Value := Row; // columna de criterios: 1..10
Sheet.Cells[Row, 2].Value := Row * 100; // importes: 100..1000
end;
Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)'; // rango de suma de una celda
Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)'; // rango de suma explícito
Sheet.Cells[3, 4].Formula := '=AVERAGEIF(A1:A10,">5",B1)'; // mismo redimensionado
if Book.Recalculate = lxOk then
// D1 y D2: 4000 (600+700+800+900+1000); D3: 800
Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ',
VarToStr(Sheet.Cells[2, 4].Value), ' ', VarToStr(Sheet.Cells[3, 4].Value));
finally
Book.Free;
end;
end;
Para las plantillas que escribes tú, deletrea el rango de suma completo. El motor honra la abreviatura porque ficheros existentes dependen de ella, pero B1 en una fórmula se lee como una sola celda para la siguiente persona que mantenga la hoja, y esa persona asumirá razonablemente que solo se está sumando un importe. El criterio del medio se evalúa como un único valor mientras los dos argumentos exteriores son referencias; esa división entre clases de argumentos se explica en el artículo sobre intersección implícita y clases de argumentos. Los totales condicionales que además deben saltarse filas filtradas u ocultas siguen reglas distintas otra vez, cubiertos en el artículo sobre SUBTOTAL y AGGREGATE con filas ocultas
Estilos de referencia de INDIRECT y base 0 de YEARFRAC
Dos funciones menores cargan con el mismo tipo de trampa. INDIRECT acepta un segundo argumento opcional, a1, y cuando es FALSE el texto es una referencia R1C1: =INDIRECT("R2C3",FALSE) lee C2. Un parser que ignora el flag lee «R2» como columna R, fila 2, que es una celda real, así que la fórmula devuelve un valor plausible del lugar equivocado. HotXLS lee el flag como un boolean, un número (cero significa FALSE) o el texto «FALSE», y convierte el texto R1C1 antes de resolverlo, con offsets entre corchetes como R[1]C[1] medidos desde la celda que sostiene la fórmula. Texto con cualquier cosa sobrante tras una referencia válida, como "B2 junk", devuelve #REF! en lugar de usar B2 en silencio
YEARFRAC con base 0 usa el recuento de días 30/360 estadounidense (NASD), incluidas las reglas del último de febrero que DAYS360 aplica: cuando ambas fechas son el último día de febrero, el día final pasa a 30, y luego un inicio en el último día de febrero pasa a 30. De 2024-02-29 a 2025-02-28 eso da 360 días y una fracción de exactamente 1. Un recuento 30/360 plano que se salte esas reglas da 359 días y 0.99722, y la diferencia aparece en acumulaciones de interés que atraviesan el final de febrero
var
Wb: IXLSWorkbook;
Sh: TXLSWorksheet;
begin
Wb := TXLSWorkbook.Create;
Sh := Wb.Sheets.Add;
Sh.Range['C2', 'C2'].Value := 'row 2, column 3';
Writeln(VarToStr(Wb.Calculate('=INDIRECT("R2C3",FALSE)'))); // fila 2, columna 3
Writeln(VarToStr(Wb.Calculate('=ISERROR(INDIRECT("B2 junk"))'))); // True: #REF!
Writeln(VarToStr(Wb.Calculate(
'=YEARFRAC(DATE(2024,2,29),DATE(2025,2,28),0)'))); // 1
end;
Cuando documentas o pruebas comportamiento así, toma los valores esperados del propio Excel y no de lo que la fórmula parece decir. «1 es menor que 2 es menor que 3» sugiere que =1<2<3 es TRUE, que es precisamente la respuesta que da un parser asociativo por la derecha y lo contrario de lo que Excel devuelve. Una tabla corta de fórmulas con los valores que muestra Excel al lado caza los cuatro casos de este artículo: cadenas, vacíos, el redimensionado de SUMIF y el recuento de días de febrero
HotXLS lee, recalcula y escribe XLS, XLSX, ODS y CSV desde Delphi y C++Builder sin Excel en la máquina, y las reglas de comparación, celda vacía y agregados condicionales de arriba pertenecen al motor de cálculo que comparten ambas clases de workbook. La lista de funciones soportadas, las ediciones y las licencias están en la página del componente de hojas de cálculo HotXLS para Delphi