HotXLS Delphi Component evalúa =1<2<3 como FALSE, la misma respuesta que da Excel 16, porque desde la v2.384.3 su parser de fórmulas pliega los operadores de comparación de izquierda a derecha: 1<2 se vuelve TRUE, y TRUE<3 es FALSE porque un boolean jerarquiza sobre cualquier número. La misma release hace que un operando vacío sea igual a 0 y a "", y deja que SUMIF estire un rango de suma de una celda a la forma de su rango de criterios. Cada una de estas parece trivia hasta que un libro calculado en Delphi discrepa del mismo libro abierto en Excel
La discrepancia suele arrancar con una fórmula que una persona escribió por intuición. Alguien tipea =0<B2<100 para chequear que una cantidad está en rango, Excel responde FALSE en silencio para cada fila, y la hoja se despacha con ese bug horneado. Un motor de cálculo no puede andar arreglando la intención del usuario; su trabajo es producir el valor que Excel produciría, de modo que el resultado cacheado que HotXLS escribe en el archivo coincida con lo que Excel muestra tras un recálculo. Antes de la v2.384.3 HotXLS respondía TRUE para ese chequeo de rango en cada fila, equivocado en la dirección contraria, y un reporte generado en un servidor contradecía al mismo reporte 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 interno entonces pierde 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 a Parse_expr para el lado derecho, lo que hace al operador asociativo por la derecha. Eso da 1<TRUE, y un número está 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 corre cada una por ambas arquitecturas de motor, el clásico TXLSWorkbook y el nativo XLSX TXLSXWorkbook, usando el método Calculate descrito en la introducción 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 libro 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 vuelve al nodo nuevo el resultado izquierdo de la ronda siguiente. Dos detalles eran fáciles de errar 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 tiene que hacer Exit después de liberar el nodo a medio construir en lugar 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 específico de Excel para texto: ambos strings pasan primero por lxUpperCase, así que ="abc"="ABC" es TRUE. Esta jerarquía es la razón por la que el resultado de la cadena no se puede razonar sin ella. TRUE<3 no es una coerción de TRUE a 1, es un boolean comparado con un número, y el boolean gana. Las fechas son números seriales para el motor (varDate clasifica como xlNumberValue), así que una fecha está siempre debajo de cualquier texto, incluido el texto que por casualidad 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, equivale a "" cuando el otro lado es texto, y desde la v2.384.53 equivale 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 vuelve WideString('') cuando su pareja es string, False cuando su pareja es boolean, y 0 en los demás casos. Dos vacíos siguen comparando iguales entre sí sin sustitución. El camino aritmético siempre había vuelto un vacío en 0, que es por qué =A1+1 daba 1, pero CompareVariants mantenía Null como su propia jerarquía más baja, 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ío 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 dolió en la práctica. Bajo la vieja jerarquía, 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 sobregirada, y =A1=0 era FALSE para una celda que cualquier usuario describiría como cero. Quedó un borde después de la v2.384.3: la sustitución elegía solo entre 0 y el string vacío, así que un vacío comparado con un boolean se volvía 0, que jerarquiza debajo de TRUE y 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 la hoja necesita esa distinción, pruebe con ISBLANK o =A1=""
¿Por qué SUMIF con un rango de suma de una 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) entonces significa B1:B10 en Excel, una conveniencia de la que dependen muchas plantillas armadas a mano. El worker compartido TXLSCalculator.GetValueItemRange2 encogía sus conteos de fila y columna a los del rango de valores, lo que reducía el ejemplo a una sola prueba de A1 contra B1. La v2.384.3 quita el recorte: el bucle ahora recorre el rango de criterios y lee cada valor al mismo offset desde la esquina superior izquierda del rango de suma. Como CalcSumIF y CalcAverageIF llaman ambas a ese worker, AVERAGEIF recibe el mismo resize, 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 criterio 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 argumento
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 criterio: 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
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 silenciosas
INDIRECT ahora honra su segundo argumento, y el texto después de 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 código viejo ignoraba la bandera, leía "R2" como columna R, fila 2, y devolvía calladamente la celda equivocada. La bandera se despacha por su tipo de variant (boolean, número o texto) porque convertir una variant de string directo a Double lanza una excepción. Texto R1C1 relativo como R[1]C[1] devuelve #REF!, porque INDIRECT no tiene origen de celda de fórmula contra el que resolverlo, y texto A1 con caracteres de sobra, "B2 junk", también devuelve #REF!. YEARFRAC con base 0 ahora aplica 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 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 el conteo ahora es 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 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 "1 es menor que 2 que es menor que 3". La nota se corrigió y el test de siete fórmulas se agregó en un commit posterior, y la regla que salió de ahí aplica para cualquiera que documente semántica de hojas de cálculo: corra el ejemplo en Excel antes de anotar el valor esperado. La sustitución de operandos vacíos y el resize de SUMIF siguen el mismo comportamiento de Excel, incluido el caso vacío-contra-boolean desde la 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 de 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 libro. 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