Para calcular referencias circulares intencionales 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 la cantidad de pasadas en IterateCount, o hasta que cada celda cambie menos del valor en IterateDelta) en lugar de devolver #REF! y detenerse
Esa distinción importa más de lo que sugiere el valor booleano simple. El mismo motor que detecta un ciclo de referencia y se niega a iterar sobre él, con una propiedad cambiada, evaluará ese ciclo deliberadamente hasta que se estabilice. Mantener claros ambos comportamientos (cuándo un ciclo es un defecto a reportar y cuándo es un modelo a resolver) es el tema central de este artículo
¿Por qué una referencia circular genera un error de forma predeterminada?
De forma predeterminada, 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. El funcionamiento de ese grafo, y por qué se omiten los miembros del ciclo en lugar de iterar sobre ellos, se cubre en el artículo complementario sobre recálculo incremental de fórmulas y el grafo de dependencias
El comportamiento predeterminado es el seguro porque la mayoría de los ciclos son errores: una fila de resumen que se incluye accidentalmente en su propio rango de SUM, o un copiar y pegar que desplaza una referencia sobre sí misma. Un código de error explícito en el momento del recálculo es exactamente lo que usted desea para esos casos. Sin embargo, una clase específica e importante de modelos es circular a propósito. Los cronogramas de interés sobre interés, las asignaciones circulares de costos o gastos generales entre departamentos y los cálculos de comisiones basados en saldos describen un valor que legítimamente se retroalimenta en sus propias entradas, y Excel los calcula solo una vez que el usuario activa Archivo → Opciones → Fórmulas → Habilitar cálculo iterativo
¿Cómo se habilita 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 está en 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: opening principal
Sheet.Cells[2, 2].Value := 0.05; // B2: period rate
Sheet.Cells[3, 2].Formula := 'B1+B4'; // B3: balance = principal + interest
Sheet.Cells[4, 2].Formula := 'B3*B2'; // B4: interest = balance * rate
Book.Iterate := True; // opt in to iterative calculation
if Book.Recalculate = lxOk then
// B3 converges to 1052.63..., B4 to 52.63...
Report(Sheet.Cells[3, 2].Value);
finally
Book.Free;
end;
end;B3 hace referencia a B4 y B4 a B3, por lo que el grafo de dependencias reporta un ciclo de dos nodos. Con Iterate en su valor predeterminado de False, ese par devolvería lxErrorRef y ninguna celda se calcularía. Con la propiedad en True, Recalculate inicializa el ciclo con los valores almacenados en caché y vuelve a evaluar sus miembros pasada tras pasada, utilizando las salidas de cada pasada como las entradas de la siguiente, hasta que los números dejen de cambiar. Aquí, la fórmula matemática directa es principal / (1 - rate), por lo que el saldo final se estabiliza en 1052.63 y el interés en 52.63, los mismos valores que Excel produce con la iteración habilitada
¿Qué hace que se detenga la iteración?
Dos condiciones de detención independientes limitan al solucionador, y comprender ambas es lo que evita que un modelo genere errores o entre en un bucle infinito. IterateCount es el límite estricto de cuántas veces se vuelven a evaluar los miembros del ciclo; su valor predeterminado es 100, al igual que en Excel. IterateDelta es el umbral de convergencia: después de cada pasada, el solucionador mide el mayor cambio numérico en todas las celdas del ciclo, y una vez que ese cambio máximo cae por debajo de IterateDelta (por defecto 0.001), el bucle de pasadas se interrumpe de forma anticipada. 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;
Vale la pena mencionar claramente una consecuencia, porque es el límite real de la función. Cuando se alcanza el límite sin que el cambio caiga por debajo de IterateDelta, SolveCycleIteratively no genera una excepción: devuelve lxOk y deja las celdas con sus valores de la última iteración, tal como Excel escribe los últimos números calculados cuando se alcanza su propio límite de iteración sin convergencia. Por lo tanto, un código de retorno exitoso de Recalculate en modo iterativo significa que "el solucionador se ejecutó", no que "el solucionador convergió". Un modelo cuyo bucle de retroalimentación diverja u oscile consumirá silenciosamente todas las pasadas de IterateCount y devolverá números que no representan en absoluto un punto fijo, sin que ninguna excepción marque la diferencia
¿Cómo se guarda la configuración en archivos XLSX y XLS files?
La configuración de cálculo iterativo persiste en ambos formatos de hoja de cálculo, por lo que un libro abierto en Excel se comporta tal como lo configuró su código en Delphi. En el lado de XLSX, el escritor emite el elemento OOXML <calcPr> solo cuando Iterate está en True, y omite cualquier atributo que se mantenga en su valor predeterminado para mantener la salida al mínimo: un libro que usa 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 abrirlo, TXLSXWorkbook lee de vuelta los mismos tres atributos, por lo que el viaje de ida y vuelta es simétrico
El motor heredado BIFF8 (.xls), TXLSWorkbook, transporta el estado equivalente a través de tres registros separados bajo un trío de propiedades diferente. EnableIteration se asocia 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 configuradores (setters) aplican los rangos de la especificación: CalcCount debe estar entre 1 y 32767, por lo que MaxIterations se limita, y un valor negativo de MaxIterationChange regresa al valor predeterminado de 0.001. Configúrelos 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;Tenga en cuenta la división deliberada en la nomenclatura: el motor XLSX utiliza Iterate / IterateCount / IterateDelta (el vocabulario OOXML), mientras que el motor BIFF8 utiliza EnableIteration / MaxIterations / MaxIterationChange (alineado 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 iteración 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 desaparecer las advertencias de referencia circular, y tratarlo así es el error común. Activar Iterate de forma global convierte cada ciclo accidental (los que el código de error predeterminado debía capturar) en un número silenciosamente convergente o silenciosamente no convergente. La disciplina correcta es la opuesta: mantenga Iterate en False como el modo normal para que los errores genuinos de creación sigan mostrándose como lxErrorRef, y habilite la iteración solo en libros de trabajo cuya circularidad sea por diseño y esté comprendida
Cuando aparece un ciclo y usted no está seguro de qué tipo es, el trazador de evaluación de fórmulas es la herramienta que los diferencia: trace la fórmula sospechosa y la cadena de referencia que regresa 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 perdida. También ayuda recordar que una celda de ciclo puede llamar a cualquier función integrada en su trayecto por el bucle: el mismo calculador que resuelve una fórmula de ingeniería o de 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 la iteración
La lista de verificación práctica es corta. Confirme que el bucle tenga un punto fijo real antes de confiar en la iteración; mantenga IterateDelta lo suficientemente ajustado como para que la "convergencia" signifique lo que su modelo requiere; y después de un Recalculate que espera que converja, realice una comprobación de coherencia en una salida conocida en lugar de confiar solo en el lxOk, ya que ese código no puede distinguir la convergencia de un límite de iteración alcanzado
El cálculo iterativo de referencias circulares forma parte del motor XLSX en el HotXLS Delphi Excel Component, junto con el recálculo incremental de grafo de dependencias sobre el que se construye y la persistencia de OOXML y BIFF8 que traslada la configuración a cada archivo que usted escribe. Para los modelos financieros y de ingeniería que son circulares a propósito, es la diferencia entre un código de error y una respuesta