Una comprobación de rango escrita como =0<B2<100 devuelve FALSE en Excel en cada fila, incluida la fila donde B2 sostiene 50. Excel evalúa 0<B2 primero, obtiene TRUE, y luego pregunta si TRUE es menor que 100; un valor lógico se posiciona por encima de cada número, así que la respuesta es FALSE sea lo que sea que B2 contenga. Las celdas vacías y SUMIF tienen sorpresas del mismo tipo: una celda vacía iguala 0, "" y FALSE al mismo tiempo, y =SUMIF(A1:A10,">5",B1) suma diez celdas de la columna B
Un motor de cálculo que recalcule semejante workbook fuera de Excel tiene que reproducir esas respuestas, aunque obviamente no sean lo que el autor quiso decir. El resultado en caché guardado en el archivo es lo que un visor, una vista previa de correo o una importación aguas abajo ve antes de que alguien presione F9. Si el servidor escribe TRUE para la comprobación de rango y Excel después muestra FALSE, el mismo reporte contiene dos números distintos dependiendo de dónde se abrió, y nadie puede decir en cuál confiar. Corregir 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 asocian de izquierda a derecha, así que la fórmula significa (1<2)<3, y el TRUE interno entonces pierde un concurso de jerarquía de tipos contra el número 3. HotXLS Delphi Component parsea 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 ahora como hijo izquierdo y el siguiente operando como hijo derecho, y luego carga ese nodo a la ronda siguiente. Esa es la misma forma de bucle que usa para +, - y &
La trampa se sienta en la gramática de libro de texto. Escrita como expr = operand op expr, un parser de descenso recursivo recursa en sí mismo para el lado derecho y convierte silenciosamente al operador en asociativo por la derecha. Bajo esa lectura =1<2<3 se vuelve 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 derecha, y =1=1=TRUE se voltea igual. Las formas entre paréntesis salen bien de cualquier manera, así que el error se escapa de cualquier prueba que solo revise (a<b)<c
El método Calculate evalúa un string de fórmula sin ponerlo en una celda, lo que lo vuelve una manera rápida de revisar una lista entera de cadenas contra ambos motores. El método está cubierto con más profundidad en la visión general del 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 conteo 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 todavía no tiene 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 booleanos: la jerarquía entre tipos
Cuando los dos lados de una comparación tienen tipos distintos, Excel no convierte uno en el otro. En su lugar posiciona los tipos: cada número es menor que cada valor de texto, y cada valor de texto es menor que cada booleano. HotXLS clasifica ambos operandos como número, texto o booleano y, cuando las clases difieren, compara las clases solas. Así =TRUE>99999 es TRUE, ="1">2 es TRUE, y =FALSE>"zzz" también es TRUE. Esta es la pieza que hace que las comparaciones encadenadas se sientan arbitrarias. TRUE<3 no es TRUE coercido a 1 y comparado con 3; es un booleano comparado con un número, y el booleano gana
Dentro de una clase la comparación es la natural, con un giro para el texto: la comparación de strings 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 siempre se posiciona debajo de cualquier texto, incluida una celda de texto que solo se ve como fecha. Una columna de fechas importadas donde la mitad de las celdas llegó como strings se ordenará y comparará en dos bandas separadas, y =A2>DATE(2026,1,1) devuelve TRUE para cada una de las celdas de string
¿A qué iguala una celda vacía en una comparación?
Una celda vacía usada como operando de comparación toma el tipo del otro lado: iguala 0 contra un número, "" contra texto y FALSE contra 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 posicionar los dos operandos, para los seis operadores de comparación y en ambos motores. Cuando ambos operandos están vacíos no sucede sustitución alguna, y comparan iguales
var
Wb: IXLSWorkbook;
Sh: TXLSWorksheet;
begin
Wb := TXLSWorkbook.Create;
Sh := Wb.Sheets.Add; // la primera hoja se vuelve 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á 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 conserve "vacío" como su propio rango más bajo, debajo de cada número, responde TRUE para =A1<0, y luego =IF(A1<0,"overdrawn","ok") etiqueta cada celda de saldo vacía como sobregirada. Sustituir solo 0 o el string vacío tampoco basta: contra un booleano el vacío se volvería 0, que se posiciona debajo de TRUE y de FALSE, y =A1=FALSE saldría FALSE en una celda vacía
Hay una consecuencia que conviene planificar. Porque un vacío compara como FALSE contra un valor lógico, =A1=FALSE no puede distinguir una celda vacía de una celda que sostiene FALSE, ni en Excel ni en HotXLS, y =A1=0 no puede distinguir vacío de cero. Las plantillas que necesitan saber si alguien realmente tecleó un valor deberían probar con ISBLANK(A1). La forma =A1="" es más débil, ya que también es TRUE para una fórmula que devuelve un string vacío
¿Por qué SUMIF con un rango de suma de una celda cubre la columna completa?
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 armadas a mano dependen de ese atajo sin que sus autores lo sepan. La implementación obvia, acotar 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 estiramiento, 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; // montos: 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 estiramiento
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 usted escriba, deletree el rango de suma completo. El motor honra el atajo porque archivos existentes dependen de él, pero B1 en una fórmula se lee como una sola celda para la siguiente persona que mantenga la hoja, y esa persona razonablemente asumirá que solo se está sumando un monto. El criterio en medio se evalúa como un valor único mientras que los dos argumentos exteriores son referencias; esa división entre clases de argumento se explica en el artículo sobre intersección implícita y clases de argumento. Los totales condicionales que además deben saltarse filas filtradas u ocultas siguen reglas distintas otra vez, cubiertas en el artículo de filas ocultas de SUBTOTAL y AGGREGATE
Estilos de referencia de INDIRECT y basis 0 de YEARFRAC
Dos funciones más pequeñas cargan el mismo tipo de trampa. INDIRECT toma un segundo argumento opcional, a1, y cuando es FALSE el texto es una referencia R1C1: =INDIRECT("R2C3",FALSE) lee C2. Un parser que ignore 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 booleano, como número (cero significa FALSE) o como 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. El texto con cualquier cosa sobrante después de una referencia válida, como "B2 junk", devuelve #REF! en lugar de usar B2 en silencio
YEARFRAC con basis 0 usa el conteo de días 30/360 US (NASD), incluidas las reglas de fin de febrero que DAYS360 aplica: cuando ambas fechas son el último día de febrero, el día final se vuelve 30, y luego un inicio en el último día de febrero se vuelve 30. De 2024-02-29 a 2025-02-28 eso da 360 días y una fracción de exactamente 1. Un conteo 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 abarcan el fin 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)'))); // row 2, column 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 documente o pruebe comportamiento semejante, tome 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 Excel muestra junto a ellas atrapa los cuatro casos de este artículo: cadenas, vacíos, el estiramiento de SUMIF y el conteo 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, de celda vacía y de agregados condicionales de arriba pertenecen al motor de cálculo que ambas clases de workbook comparten. La lista de funciones soportadas, las ediciones y el licenciamiento están en la página del componente de hojas de cálculo HotXLS para Delphi