Para calcular referencias circulares intencionadas en Delphi, HotXLS expone el cálculo iterativo en su motor XLSX: establezca TXLSXWorkbook.Iterate en True y TXLSXWorkbook.Recalculate recorrerá cada ciclo de referencia detectado hacia un punto fijo —hasta IterateCount pasadas, o hasta que cada celda cambie en menos de IterateDelta— en lugar de devolver #REF! y rendirse
Esa distinción importa más de lo que sugiere el único valor booleano. El mismo motor que detecta un ciclo de referencia y se niega a iterar sobre él evaluará, con una propiedad cambiada, ese ciclo deliberadamente hasta que se estabilice. Mantener claros los dos comportamientos —cuándo un ciclo es un defecto que se debe reportar y cuándo es un modelo a resolver— es todo el tema de este artículo
¿Por qué una referencia circular genera un error por defecto?
Por defecto, HotXLS trata cualquier ciclo de referencia como un error de creación y lo reporta en lugar de calcularlo. TXLSXWorkbook.Recalculate construye un grafo de dependencias de fórmulas, evalúa cada celda de fórmula en orden topológico y devuelve lxErrorRef en el momento en que encuentra un ciclo —los nodos que nunca se pueden liberar durante la ordenación topológica. Esos miembros del ciclo conservan sus valores anteriores almacenados en caché; cada fórmula fuera del ciclo se sigue evaluando normalmente. La mecánica de ese grafo, y por qué los miembros del ciclo se omiten en lugar de entrar en un bucle, se cubren en el artículo complementario sobre recalculación incremental de fórmulas y el grafo de dependencias
El valor predeterminado es el seguro porque la mayoría de los ciclos son errores: una fila de resumen introducida accidentalmente en su propio rango de SUM, o copiar y pegar que desplazó una referencia sobre sí misma. Un código de error evidente en el momento de la recalculación es exactamente lo que desea para esos casos. Pero una clase específica e importante de modelos es circular a propósito. Los esquemas de interés sobre interés, las asignaciones circulares de costos o gastos generales entre departamentos y los cálculos de tarifas basados en saldos describen un valor que legítimamente se retroalimenta de sus propias entradas, y Excel los calcula solo una vez que el usuario marca Archivo → Opciones → Fórmulas → Habilitar cálculo iterativo
¿Cómo habilitar el cálculo iterativo en HotXLS?
HotXLS refleja esa casilla de verificación de Excel con tres propiedades en TXLSXWorkbook: Iterate activa el modo, y cuando es True, un ciclo detectado se entrega a un solucionador iterativo en lugar de producir lxErrorRef. Considere el par de celdas canónico de interés sobre interés, donde el saldo de cierre depende del interés y el interés depende del saldo
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Model');
Sheet.Cells[1, 2].Value := 1000; // B1: principal de apertura
Sheet.Cells[2, 2].Value := 0.05; // B2: tasa del periodo
Sheet.Cells[3, 2].Formula := 'B1+B4'; // B3: saldo = principal + interés
Sheet.Cells[4, 2].Formula := 'B3*B2'; // B4: interés = saldo * tasa
Book.Iterate := True; // opt in to iterative calculation
if Book.Recalculate = lxOk then
// B3 convergerá a 1052.63..., B4 a 52.63...
Report(Sheet.Cells[3, 2].Value);
finally
Book.Free;
end;
end;B3 hace referencia a B4 y B4 hace referencia a B3, por lo que el grafo de dependencias informa un ciclo de dos nodos. Si se deja Iterate en su valor predeterminado de False, ese par regresaría como lxErrorRef y ninguna de las celdas se estabilizaría. Con este valor en True, Recalculate inicializa el ciclo a partir de los valores actuales almacenados en caché y vuelve a evaluar a sus miembros pasada tras pasada, retroalimentando las salidas de cada pasada como las entradas de la siguiente pasada, hasta que los números dejen de moverse. Aquí la forma cerrada es principal / (1 - rate), por lo que el saldo se estabiliza en 1052.63 y el interés en 52.63 —las mismas cifras que produce Excel con la iteración habilitada
¿Qué hace que la iteración se detenga?
Dos condiciones de parada independientes limitan al solucionador, y comprender ambas es lo que evita que un modelo falle o gire indefinidamente. IterateCount es el límite estricto de cuántas veces se vuelven a evaluar los miembros del ciclo; tiene un valor predeterminado de 100, que coincide con Excel. IterateDelta es el umbral de convergencia: después de cada pasada, el solucionador mide el cambio numérico más grande en todas las celdas del ciclo, y una vez que ese cambio máximo cae por debajo de IterateDelta —el valor predeterminado es 0.001—, el bucle de pasadas se rompe antes de tiempo. Cualquiera de las condiciones que se cumpla primero finaliza la iteración
Book.Iterate := True;
Book.IterateCount := 1000; // hard cap: at most 1000 passes over the cycle
Book.IterateDelta := 0.0001; // convergence: stop once every cell moves < 0.0001
case Book.Recalculate of
lxOk:
// the cycle converged, OR it hit the 1000-pass cap and kept its
// last-iteration values -- both paths return lxOk when Iterate is True
SaveWorkbook(Book);
lxErrorRef:
// only reachable with Iterate = False: the cycle was reported, not solved
LogWarning('Circular reference with iteration disabled');
end;
Una consecuencia vale la pena exponerse claramente, porque es el límite honesto de la función. Cuando se alcanza el límite sin que el cambio caiga por debajo de IterateDelta, SolveCycleIteratively no genera un error: devuelve lxOk y deja las celdas con sus valores de la última iteración, exactamente como Excel escribe los últimos números calculados cuando se alcanza su propio límite de iteraciones sin convergencia. Por lo tanto, un código de retorno exitoso de Recalculate bajo el modo iterativo significa "el solucionador se ejecutó", no "el solucionador convergió". A modelo cuyo bucle de retroalimentación diverge u oscila consumirá silenciosamente todas las pasadas de IterateCount y devolverá números que no representan en absoluto un punto fijo, y ninguna excepción marca la diferencia
¿Cómo se guarda la configuración en los archivos XLSX y XLS?
La configuración de cálculo iterativo persiste en ambos formatos de hoja de cálculo, por lo que un libro de trabajo abierto en Excel se comporta de la manera en que lo configuró su código Delphi. En el lado de XLSX, el escritor emite el elemento OOXML <calcPr> solo cuando Iterate es True, y omite cualquier atributo que aún conserve su valor predeterminado para mantener la salida al mínimo: un libro de trabajo que utiliza los valores predeterminados escribe solo <calcPr iterate="1"/>, mientras que iterateCount aparece solo cuando difiere de 100 e iterateDelta solo cuando difiere de 0.001. Al abrirse, TXLSXWorkbook lee los mismos tres atributos de vuelta, por lo que el proceso de ida y vuelta es simétrico
El motor heredado BIFF8 (.xls), TXLSWorkbook, mantiene el estado equivalente a través de tres registros separados bajo un trío de propiedades diferente. EnableIteration se asigna al registro CalcIter ($0011, [MS-XLS] §2.4.33), MaxIterations al registro CalcCount ($000C, [MS-XLS] §2.4.31) y MaxIterationChange al registro CalcDelta ($0010, [MS-XLS] §2.4.32). Los establecedores imponen los rangos de la especificación: CalcCount debe estar en 1..32767, por lo que MaxIterations se delimita, y un MaxIterationChange negativo regresa al valor predeterminado de 0.001. Establézcalos en un libro .xls cargado y los tres registros de cálculo se escribirán fielmente al guardar
var
Book: TXLSWorkbook; // BIFF8 (.xls) engine
begin
Book := TXLSWorkbook.Create;
try
Book.Open('model.xls');
Book.EnableIteration := True; // CalcIter record $0011
Book.MaxIterations := 500; // CalcCount record $000C (clamped to 1..32767)
Book.MaxIterationChange := 0.0001; // CalcDelta record $0010
Book.SaveAs('model.xls'); // the three calc records round-trip
finally
Book.Free;
end;
end;Note la división deliberada en los nombres: el motor XLSX utiliza Iterate / IterateCount / IterateDelta (el vocabulario de OOXML), mientras que el motor BIFF8 utiliza EnableIteration / MaxIterations / MaxIterationChange (alineados con los nombres de registro de [MS-XLS]). Ambos tríos describen los mismos tres controles —un interruptor de encendido/apagado, un límite de iteraciones y un delta de convergencia— con los mismos valores predeterminados de apagado, 100 y 0.001
¿Cuándo una referencia circular es un error en lugar de un modelo?
Habilitar la iteración no es una forma de hacer que desaparezcan las advertencias de referencia circular, y tratarlo de esa manera es la trampa. Activar Iterate de forma global convierte cada ciclo accidental —los que el código de error predeterminado debía capturar— en un número convergido o no convergido silenciosamente. La disciplina es la contraria: mantener Iterate en False como el modo normal para que los errores genuinos de creación sigan apareciendo como lxErrorRef, y habilitar la iteración solo en libros de trabajo cuya circularidad sea por diseño y se comprenda
Cuando aparece un ciclo y no está seguro de qué tipo es, el rastreador de evaluación de fórmulas es la herramienta que los diferencia: rastree la fórmula sospechosa y la cadena de referencias que se pliega sobre sí misma se hará visible paso a paso, de modo que pueda decidir si codifica un bucle de retroalimentación real o una autorreferencia extraviada. También ayuda recordar que una celda de ciclo puede llamar a cualquier función integrada en el recorrido del bucle —el mismo calculador que resuelve una fórmula de ingeniería o números complejos evalúa los miembros del ciclo en cada pasada—, por lo que un modelo divergente suele ser un problema de fórmula dentro del bucle, no un problema de configuración de iteración
La lista de comprobación práctica es corta. Confirme que el bucle tiene un punto fijo real antes de confiar en la iteración; mantenga IterateDelta lo suficientemente ajustado como para que "convergido" signifique lo que su modelo necesita; y después de un Recalculate que espera que converja, realice una comprobación de coherencia en una salida conocida en lugar de confiar únicamente en el lxOk, ya que ese código no puede distinguir la convergencia de haber alcanzado el límite de iteraciones
El cálculo iterativo de referencias circulares es parte del motor XLSX en HotXLS Delphi Excel Component, junto con la recalculación incremental basada en grafos de dependencias sobre la que se construye y la persistencia OOXML y BIFF8 que traslada la configuración a cada archivo que escribe. Para los modelos financieros y de ingeniería que son circulares a propósito, representa la diferencia entre un código de error y una respuesta