HotXLS Delphi Component evalúa =1<2<3 como FALSE, la misma respuesta que da Excel 16, porque desde v2.384.3 su parser de fórmulas pliega los operadores de comparación de izquierda a derecha: 1<2 se convierte en TRUE, y TRUE<3 es FALSE porque un boolean va por encima de cualquier número. La misma release hace que un operando vacío equivalga a 0 y a "", y deja que SUMIF estire un rango de suma de una celda hasta la forma de su rango de criterios. Cada una de estas parece una curiosidad hasta que un workbook calculado en Delphi discrepa del mismo workbook abierto en Excel
La discrepancia suele empezar con una fórmula que una persona escribió por intuición. Alguien teclea =0<B2<100 para comprobar que una cantidad está en rango, Excel responde FALSE en silencio para cada fila, y la hoja se envía con ese bug horneado. Un motor de cálculo no puede arreglar la intención del usuario; su trabajo es producir el valor que produciría Excel, de modo que el resultado cacheado que HotXLS escribe en el archivo coincida con lo que Excel muestra tras un recálculo. Antes de v2.384.3 HotXLS respondía TRUE para esa comprobación de rango en todas las filas, equivocado en la dirección contraria, y un informe generado en un servidor contradecía al mismo informe abierto en un escritorio
¿Por qué =1<2<3 devuelve FALSE en Excel?
Excel devuelve FALSE porque lee una cadena de comparaciones como (1<2)<3, y el TRUE interior pierde luego el concurso de jerarquía de tipos contra el número 3. El viejo parser de HotXLS leía el mismo texto como 1<(2<3): TXLSSyntax.Parse_expr en lxFormula.pas parseaba un operando, veía un token de comparación y recursaba en Parse_expr para el lado derecho, lo que hace al operador asociativo por la derecha. Eso da 1<TRUE, y un número está por debajo de un boolean, así que el resultado era TRUE. El error es simétrico: =3>2>1 es TRUE en Excel y era FALSE en HotXLS, y =1=1=TRUE es TRUE en Excel y era FALSE antes del fix. La regresión CalculateFormula_ComparisonChainsFoldLeftToRight fija siete fórmulas así contra los valores que devuelve Excel 16, y pasa cada una por las dos arquitecturas de motor, la clásica TXLSWorkbook y la nativa XLSX TXLSXWorkbook, usando el método Calculate descrito en el repaso al motor de fórmulas de HotXLS
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');
// Lo que devuelve Excel 16: FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE
var
Classic: IXLSWorkbook;
Xlsx: TXLSXWorkbook;
i: Integer;
begin
Classic := TXLSWorkbook.Create;
Xlsx := TXLSXWorkbook.Create;
try
// TXLSXWorkbook.Calculate evalúa contra la hoja activa y
// devuelve Null cuando el workbook 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;
El fix convierte Parse_expr en un bucle de la misma forma que Parse_expr1 ya usaba para +, - y &. Parsea el primer operando con Parse_expr1, y mientras el siguiente token sea uno de =, <>, <, >, <= o >=, crea un nodo de comparación, cuelga el resultado izquierdo acumulado como primer hijo, parsea el siguiente operando con Parse_expr1 en lugar de Parse_expr, y convierte al nodo nuevo en el resultado izquierdo de la siguiente ronda. Dos detalles eran fáciles de torcer al convertir la recursión en iteración, y ambos están en las notas de los mantenedores: el nodo acumulado hay que entregarlo (lChild := Item; Item := nil) en ese orden, y el camino de error debe hacer Exit tras liberar el nodo a medio construir en vez de caer fuera del bucle y devolver un árbol colgante
¿Cómo jerarquiza HotXLS números, texto y booleans en una comparación?
HotXLS jerarquiza los tipos mezclados como Excel: todo número es menor que todo valor de texto, y todo valor de texto es menor que todo boolean. TXLSCalculator.CompareVariants en lxCalc.pas clasifica ambos operandos con GetRetValueType en la enumeración TXLSRetValueType = (xlVariantValue, xlNumberValue, xlStringValue, xlBooleanValue), y cuando las dos clases difieren simplemente compara sus ordinales, así que el orden de declaración de ese enum es la regla entre tipos. Dentro de una clase la comparación es la natural, con un giro propio de Excel para texto: ambas cadenas pasan antes por lxUpperCase, así que ="abc"="ABC" es TRUE. Esta jerarquía es la razón por la que no se puede razonar sobre el resultado de la cadena sin ella. TRUE<3 no es una coerción de TRUE a 1, es un boolean comparado con un número, y gana el boolean. Las fechas son números de serie para el motor (varDate clasifica como xlNumberValue), así que una fecha está siempre por debajo de cualquier texto, incluido el texto que casualmente parezca una fecha
¿A qué equivale una celda vacía en una comparación?
Una celda vacía usada como operando de comparación equivale a 0 cuando el otro lado es un número, a "" cuando el otro lado es texto, y desde v2.384.53 a FALSE cuando el otro lado es un valor lógico, así que con A1 vacía =A1=0, =A1="" y =A1=FALSE son todos TRUE. TXLSCalculator.CompareVarValues, que sirve a los seis operadores de comparación, sustituye el vacío antes de llamar a CompareVariants: si exactamente un operando es Null se convierte en WideString('') cuando su pareja es una cadena, en False cuando su pareja es un boolean, y en 0 en los demás casos. Dos vacíos siguen comparando iguales entre sí sin sustitución. El camino aritmético siempre había convertido un vacío en 0, razón por la que =A1+1 daba 1, pero CompareVariants mantiene Null como su propio rango más bajo, por debajo de todo número, y los operadores de comparación usaban ese rango directamente
var
Wb: IXLSWorkbook;
Sh: TXLSWorksheet;
begin
Wb := TXLSWorkbook.Create;
Sh := Wb.Sheets.Add;
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<B1'))); // True: el vacío compara como 0
Writeln(VarToStr(Wb.Calculate('=A1<0'))); // False; True antes de v2.384.3
end;
La última línea es la que dolía en la práctica. Con el viejo ranking, un vacío era menor que todo número, incluidos los negativos, así que =IF(A1<0,"overdrawn","ok") etiquetaba cada celda de saldo vacía como en descubierto, y =A1=0 era FALSE para una celda que cualquier usuario describiría como cero. Quedó un caso fronterizo tras v2.384.3: la sustitución solo elegía entre 0 y la cadena vacía, así que un vacío comparado con un boolean se convertía en 0, que va por debajo tanto de TRUE como de FALSE, y =A1=FALSE sobre una A1 vacía evaluaba a FALSE. Desde HotXLS 2.384.53 un vacío comparado con un valor lógico se trata como FALSE en ambos motores, XLS y XLSX, como hace Excel: con A1 vacía, =A1=FALSE y =A1<TRUE devuelven TRUE y =A1=TRUE devuelve FALSE. Eso también significa que la comparación no puede distinguir vacío de FALSE, ni en Excel ni en HotXLS; cuando una hoja necesita esa distinción, prueba con ISBLANK o =A1=""
¿Por qué SUMIF con un rango de suma de una sola celda devolvía 0?
SUMIF devolvía 0 porque HotXLS recortaba la iteración al menor de los dos rangos, mientras Excel conserva la forma del rango de criterios y solo usa el rango de suma para su celda superior izquierda. =SUMIF(A1:A10,">5",B1) significa por tanto B1:B10 en Excel, una conveniencia de la que dependen muchas plantillas hechas a mano. El worker compartido TXLSCalculator.GetValueItemRange2 reducía sus conteos de filas y columnas a los del rango de valores, lo que dejaba el ejemplo en una única prueba de A1 contra B1. v2.384.3 quita el recorte: el bucle recorre ahora el rango de criterios y lee cada valor en el mismo offset desde la esquina superior izquierda del rango de suma. Como CalcSumIF y CalcAverageIF llaman ambos a ese worker, AVERAGEIF recibe el mismo redimensionado, y un rango de suma mayor que el rango de criterios se recorta a la forma de los criterios por la misma razón. El argumento de criterios del medio es de clase valor y los dos exteriores son de clase referencia, la distinción que cubre el artículo sobre intersección implícita y clases de argumentos
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
if Book.Recalculate = lxOk then
// Tanto D1 como D2 son 4000 (600+700+800+900+1000); D1 era 0 antes de v2.384.3
Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ', VarToStr(Sheet.Cells[2, 4].Value));
finally
Book.Free;
end;
end;
INDIRECT y YEARFRAC: dos correcciones más discretas
INDIRECT honra ahora su segundo argumento, y el texto tras una referencia válida es un error en lugar de ignorarse. Con a1 en FALSE el texto se parsea como R1C1 absoluto, así que =INDIRECT("R2C3",FALSE) lee C2; el viejo código ignoraba el flag, leía "R2" como columna R, fila 2, y devolvía en silencio la celda equivocada. El flag se despacha por su tipo de variante (boolean, número o texto) porque convertir una variante de cadena directamente a Double lanza una excepción. Texto R1C1 relativo como R[1]C[1] devuelve #REF!, ya que INDIRECT no tiene celda de fórmula origen contra la que resolverlo, y texto A1 con caracteres de más, "B2 junk", devuelve también #REF!. YEARFRAC con base 0 aplica ahora las reglas NASD del último de febrero que DAYS360 ya implementaba: 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 el conteo es ahora de 360 días, una fracción de exactamente 1, donde el anterior Days360US contaba 359
¿Qué garantizan estos fixes, y cuál fue la lección?
El comportamiento de las cadenas de comparación está garantizado por un test que compara ambos motores con valores medidos en Excel 16, y ese test existe porque la primera descripción del fix estaba equivocada. La nota de la release v2.384.3 decía originalmente que el plegado de izquierda a derecha hacía =1<2<3 TRUE, que es precisamente lo que producía el viejo parser asociativo por la derecha y lo contrario de lo que devuelven tanto Excel como el código nuevo. Nadie había evaluado el ejemplo; estaba escrito desde la intuición de que "1 es menor que 2 es menor que 3". La nota se corrigió y el test de siete fórmulas se añadió en un commit posterior, y la regla que salió de ahí sirve para cualquiera que documente semántica de hojas de cálculo: ejecuta el ejemplo en Excel antes de apuntar el valor esperado. La sustitución de operandos vacíos y el redimensionado de SUMIF siguen el mismo comportamiento de Excel, incluido el caso vacío contra boolean desde v2.384.53, y los agregados condicionales que además tienen que saltarse filas filtradas u ocultas siguen las reglas aparte de el artículo sobre SUBTOTAL y AGGREGATE con filas ocultas
HotXLS es un componente de hojas de cálculo nativo para Delphi y C++Builder que lee, recalcula y escribe XLS, XLSX, ODS y CSV sin Excel instalado, y las reglas de comparación, vacíos y SUMIF descritas aquí viven en el motor de cálculo que comparten ambas arquitecturas de workbook. La lista completa de funciones y las opciones de licencia están en la página de producto del componente de hojas de cálculo HotXLS para Delphi