Una columna de 10,000 fórmulas, cada una haciendo crecer el valor de la celda superior, es de los modelos más baratos que una hoja de cálculo puede cargar. En un evaluador que computa una fórmula referenciada en el acto cada vez que se topa con una, los totales salen bien, pero la cuenta aritmética no: la última celda arrastra 9,999 ancestros por la evaluación, la de arriba 9,998, y una sola pasada completa suma unos 50 millones de evaluaciones para 10,000 multiplicaciones. Cambie una tasa de crecimiento arriba y ese evaluador tiene que pagar la cuenta entera otra vez, porque no guarda registro de qué celda lee a cuál
Por qué una celda editada puede costar una pasada de todo el libro
Dos atajos producen ese comportamiento, y la mayoría de los motores de fórmulas hechos a mano toman uno de ellos. El atajo recursivo evalúa cada fórmula referenciada desde cero e ignora cualquier valor ya cacheado, lo que convierte una cadena en trabajo cuadrático y una referencia circular en un desbordamiento de pila. El atajo de pasada completa es más seguro: evaluar cada fórmula en algún orden fijo, tantas veces como haga falta para que nada cambie. Es correcto, y su costo sigue el tamaño del libro en lugar del tamaño de la edición
Excel evita ambos con su cadena de cálculo, un ordenamiento de celdas de fórmula que deja que una edición marque un conjunto pequeño de celdas y recorra solo la cola afectada. El motor XLSX de HotXLS usa la misma idea en forma de un grafo de dependencias explícito detrás de TXLSXWorkbook.Recalculate. La primera llamada construye el grafo a partir de cada fórmula en cada hoja y evalúa todas. Cada llamada posterior evalúa solo las fórmulas aguas abajo de las celdas cuyos valores cambiaron desde la pasada previa, más una lista corta de fórmulas que deben correr cada vez
Cómo Recalculate convierte una edición en una pasada ordenada
Cada celda de fórmula se convierte en un nodo, y una arista corre desde cada celda de fórmula que una fórmula lee hacia la fórmula que la lee. Una referencia como SUM(Model!B2:B500) produce una arista desde cada celda de fórmula dentro de ese rectángulo, y una referencia 3D a través de una tanda de hojas se coteja contra cada hoja de la tanda. Las constantes simples no son nodos; solo importan como el lugar donde empieza una edición
Escribir Value en una celda casi no hace nada en el momento de la escritura. El libro pone la dirección de la celda en cola, y eso es todo. Cuando Recalculate arranca, cada fórmula cuyas referencias cubren una dirección en cola se marca como sucia, y la marca fluye por las aristas hacia todos los dependientes de esa fórmula, y luego hacia los de ellos. Un libro sobre el que jamás se ha llamado Recalculate no guarda grafo, y sus escrituras cuestan una verificación de nil, así que el código que solo lee y escribe valores no paga nada por la función
La parte sucia del grafo se ordena entonces con el algoritmo de Kahn. El detalle que vuelve barata la pasada es cómo se cuentan los grados de entrada: solo cuentan los precedentes sucios. Un precedente limpio ya carga un valor cacheado válido, así que una fórmula jamás lo espera. Durante la pasada, una fórmula que referencia otra celda de fórmula lee el valor cacheado de esa celda en lugar de evaluarla otra vez; el ordenamiento garantiza que la caché ya está fresca. Cada fórmula sucia se evalúa exactamente una vez
El código de abajo construye la cadena del párrafo inicial y edita su única entrada. Los índices de hoja en HotXLS empiezan en 1, así que Inputs es la hoja 1 y Model es la hoja 2; las filas y columnas en Cells también usan base 1
uses
SysUtils, lxCalc, lxHandleX; // lxOk se declara en lxCalc
var
Book: TXLSXWorkbook;
Inputs, Model: TXLSXWorksheet;
Row: Integer;
Last: Variant;
begin
Book := TXLSXWorkbook.Create;
try
Inputs := Book.Sheets.Add('Inputs'); // Sheets[1]
Model := Book.Sheets.Add('Model'); // Sheets[2]
Inputs.Cells[2, 2].Value := 0.05; // supuesto de crecimiento en Inputs!B2
Model.Cells[2, 2].Formula := 'Inputs!B2*1000';
for Row := 3 to 10000 do
Model.Cells[Row, 2].Formula :=
'B' + IntToStr(Row - 1) + '*(1+Inputs!$B$2)';
if Book.Recalculate <> lxOk then // construye el grafo, evalúa 9,999 fórmulas
Exit;
Inputs.Cells[2, 2].Value := 0.07; // pone en cola una dirección
if Book.Recalculate = lxOk then // las fórmulas alimentadas por Inputs!B2 corren de nuevo (aquí: las 9,999)
Last := Model.Cells[10000, 2].Value; // resultado cacheado, se lee como cualquier valor
// Lectura única: RecalcCell ejecuta Recalculate y luego devuelve la celda
// (hoja 2 = Model, fila 10000, columna 2)
Book.RecalcCell(2, 10000, 2, Last);
finally
Book.Free;
end;
end;
En este modelo en particular cada fórmula lee Inputs!$B$2, así que la segunda pasada igual toca las 9,999; el grafo no puede encoger una edición que de verdad afecta a todo. El ahorro aparece en modelos con muchas entradas, donde un bucle de escenarios escribe tres o cuatro celdas y el resto del libro conserva sus valores cacheados
Las escrituras de valor son baratas, las de fórmula reconstruyen el grafo
Solo las ediciones de valor se mantienen incrementales. Cualquier cosa que pueda cambiar el conjunto de nodos o aristas marca todo el grafo como vencido, y el siguiente Recalculate lo reconstruye desde cero y evalúa cada fórmula. Eso cubre asignar Formula, limpiar una celda de fórmula escribiéndole Null o un valor vacío, agregar, mover o borrar hojas, insertar o borrar filas y columnas, editar nombres definidos, y cambiar las tablas de datos what-if. Registrar o desregistrar una función de usuario, o asignar OnUserFunction, conserva el grafo pero marca cada nodo como sucio
La consecuencia práctica para generadores de reportes es una forma de bucle. Disponga las fórmulas una vez, llame a Recalculate, y de ahí en adelante toque solo valores. Un bucle que reescribe una plantilla de fórmula por cliente y luego recalcula recibe una reconstrucción completa en cada iteración, que es justo el costo que el grafo existe para evitar. Cuando de verdad se necesita un cambio de plantilla, agrupe todas las ediciones de fórmula antes de un Recalculate, para que la reconstrucción ocurra una vez. El bucle de escenarios mismo entonces escribe solo valores
// Modelo construido una vez; Recalculate ya llamado
for I := 0 to High(Scenarios) do
begin
Inputs.Cells[2, 2].Value := Scenarios[I].Growth; // solo escrituras de valor
Inputs.Cells[3, 2].Value := Scenarios[I].Discount;
if Book.RecalcNeeded then // True cuando hay celdas en cola o sucias
if Book.Recalculate <> lxOk then
Break;
Results[I] := Model.Cells[10000, 2].Value;
end;
// Deje que SaveAs recalcule solo si algo sigue pendiente
Book.RecalcBeforeSave := xrmIfDirty;
Book.SaveAs('scenarios.xlsx');
RecalcBeforeSave tiene por default xrmManual, que jamás recalcula durante un guardado. xrmIfDirty es la opción para archivos generados: recalcula solo cuando el grafo está vencido o tiene fórmulas sucias, así un libro ya recalculado en el bucle se escribe tal cual, y uno con una edición olvidada no se guarda con valores cacheados vencidos
¿Qué funciones de Excel fuerzan la recalculación en cada pasada?
Las fórmulas que llaman a NOW, TODAY, RAND, RANDBETWEEN, RANDARRAY, OFFSET, INDIRECT, CELL, INFO o GETPIVOTDATA son volátiles en HotXLS: se marcan sucias al inicio de cada pasada, cambió o no algo aguas arriba, y también todos sus dependientes. Las funciones de reloj y aleatorias son volátiles por la razón obvia. OFFSET y INDIRECT son volátiles porque las celdas que leen se computan de valores de argumento en tiempo de ejecución, y ningún recorrido estático de la fórmula puede decir qué aristas dibujar
La misma regla aplica a referencias que el constructor del grafo no puede fijar a un rectángulo concreto, a un nombre definido que se refiere a sí mismo a través de otros nombres, a referencias de desbordamiento de arreglos dinámicos, y a tablas de datos what-if. Los nombres definidos en general no son problema: el constructor expande un nombre a los rangos a los que se refiere, así que las fórmulas que pasan por nombres de libro o de hoja reciben aristas reales. El artículo sobre nombres definidos y fórmulas entre hojas cubre cómo se resuelven esos nombres
La dirección conservadora es la correcta aquí. Una evaluación innecesaria cuesta un poco de tiempo; una arista faltante significa un número vencido en un reporte que se ve terminado. Mantenga OFFSET y INDIRECT fuera de las rutas calientes de modelos grandes y confínelos a las pocas celdas que de verdad necesitan direcciones computadas, porque una celda volátil cerca de la cima de una cadena vuelve volátil a toda la cadena en la práctica
Las funciones suministradas por OnUserFunction o RegisterUserFunction están en el otro extremo. No son volátiles, así que un manejador se vuelve a llamar solo cuando cambian las celdas que alimentan sus argumentos, como cualquier función integrada. Un manejador que lee un tipo de cambio de una base de datos sigue devolviendo su última respuesta a través de las pasadas. Escriba la tasa en una celda de entrada y pase esa celda como argumento; el grafo entonces ve la dependencia, y la fórmula se reejecuta justo cuando la tasa cambia. El artículo sobre funciones personalizadas en el motor de fórmulas de HotXLS explica cómo llegan los argumentos del manejador
Referencias circulares: lxErrorRef o cálculo iterativo
Con el default Iterate = False, TXLSXWorkbook.Recalculate devuelve lxErrorRef cuando el ordenamiento encuentra un ciclo. Los miembros del ciclo son los nodos sucios que el algoritmo de Kahn jamás puede liberar, y cualquier fórmula sucia aguas abajo de ellos queda bloqueada por la misma razón. Esas fórmulas conservan sus valores cacheados previos, mientras todo lo demás del conjunto sucio se evalúa en orden. Una pasada fallida también marca el grafo como vencido, así que la llamada posterior lo reconstruye y evalúa cada fórmula una vez más
case Book.Recalculate of
lxOk:
Book.SaveAs(OutputFile);
lxErrorRef:
// el ciclo y sus fórmulas aguas abajo aún cargan sus cachés viejos;
// LogWarning es su propia rutina
LogWarning('Circular reference in model, report not written');
else
LogWarning('Recalculation did not complete');
end;
Asignar Iterate := True enciende el mismo cálculo iterativo que ofrece Excel. El motor evalúa el ciclo y sus dependientes bloqueados repetidamente, hasta IterateCount pasadas (default 100), hasta que ningún valor se mueva por IterateDelta (default 0.001) o más. Los miembros vacíos del ciclo parten de 0. Llegar al límite de pasadas conserva los últimos resultados y aun así devuelve lxOk, que es por qué la iteración debería quedarse apagada para reportes generados: los ciclos en modelos de reportes reales casi siempre son errores, como una fila de totales atrapada dentro de su propio rango SUM, y un código de error definido es lo que usted quiere de ellos. Enciéndalo solo para modelos construidos alrededor de un bucle intencional, como un interés cobrado sobre un saldo promedio que incluye el interés
Un caso que parece circular no se reporta como ciclo. El argumento lookup-array de VLOOKUP, HLOOKUP, LOOKUP, MATCH, XLOOKUP y XMATCH igual vuelve sucia a la fórmula cuando una celda de ese rango cambia, pero no cuenta para la detección de ciclos, así que VLOOKUP(A1,B:B,1) escrito en la columna B evalúa con normalidad. Para ciclos reales, el tracer de evaluación de fórmulas muestra la cadena de referencias que se dobla sobre sí misma, un paso a la vez
Fórmulas de arreglo y pasadas multi-hilo
Una fórmula de arreglo CSE heredada es un nodo para todo su rectángulo anclado. La fórmula raíz se evalúa una vez por pasada, su matriz se escribe en cada celda miembro (un resultado escalar se difunde por el rectángulo), y una fórmula que referencia cualquier celda del rectángulo, no solo el ancla superior izquierda, recibe una arista desde la raíz. Las fórmulas de arreglo dinámico se manejan con más cautela: corren en cada pasada, y cuando un desbordamiento cambia de tamaño, Recalculate reconstruye el grafo y repite la pasada hasta que las formas de los desbordamientos se asientan. Si jamás se asientan dentro de 32 rondas, la llamada devuelve lxErrorCalc
RecalcThreads en un valor arriba de 1 evalúa cada nivel topológico de fórmulas independientes a través de hilos de trabajo, con una calculadora separada por hilo. Las tablas de datos siguen corriendo en serie, y un libro que contiene arreglos dinámicos se queda en la ruta en serie. Cualquier manejador de funciones de usuario debe ser thread-safe antes de subir este valor; para un manejador que toca estado compartido, mantener RecalcThreads en su default de 0 es la elección más segura
El grafo de dependencias, el manejo de volátiles y el cálculo iterativo son parte del motor XLSX del HotXLS Delphi Excel Component, que también cubre la calculadora de fórmulas, los nombres definidos y el pipeline de lectura y escritura alrededor de ellos. Para aplicaciones de Delphi y C++Builder que mantienen hojas de precios o libros de consolidación abiertos y les meten entradas nuevas todo el día, el trabajo por edición sigue entonces a las celdas que la edición realmente alcanza