Artículo técnico

Comparaciones encadenadas, celdas vacías y SUMIF en HotXLS

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;
Dos árboles de parseo para =1<2<3: un parseo asociativo por la derecha evalúa 1<(2<3) como TRUE, mientras que el plegado de izquierda a derecha evalúa (1<2)<3 como FALSE, porque Excel jerarquiza todo número por debajo del texto y todo valor de texto por debajo de un boolean
Ambos motores de HotXLS pliegan las cadenas de comparación de izquierda a derecha — un boolean va por encima de todo número, así que que TRUE pierda contra 3 es justo lo que hace FALSE la comprobación de rango encadenada

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

Sustitución del operando vacío en HotXLS: una A1 vacía compara igual a 0 y al texto vacío, un vacío frente a un boolean compara como FALSE así que =A1=FALSE es TRUE como en Excel, mientras que un vacío mantenido como el rango más bajo haría =A1<0 TRUE para cada saldo vacío
La sustitución se ajusta al tipo del otro operando, 0, la cadena vacía o FALSE — un ranking bruto de Null es lo que etiquetaría cada saldo vacío como en descubierto

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;
Redimensionado de SUMIF y AVERAGEIF en HotXLS donde =SUMIF(A1:A10,">5",B1) recorre el rango de criterios de diez filas leyendo B1 a B10 en offsets coincidentes para un resultado de 4000, donde recortar al rango de suma de una celda devolvería 0
Excel solo toma prestada la esquina superior izquierda del rango de suma y conserva la forma de los criterios, así que una plantilla que pasa B1 quiere decir B1:B10 — SUMIF y AVERAGEIF recorren los diez offsets y recortan un rango sobredimensionado de la misma manera

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