Artículo técnico

Recálculo incremental de fórmulas en HotXLS para Delphi

Una columna de 10.000 fórmulas, cada una haciendo crecer el valor de la celda que tiene encima, es más o menos el modelo más barato que una hoja de cálculo puede cargar. En un evaluador que calcula cada fórmula referenciada en el acto cada vez que se encuentra una, los totales siguen saliendo bien, pero la factura aritmética no: la última celda arrastra 9.999 ancestros por la evaluación, la de encima 9.998, y una única pasada completa suma unos 50 millones de evaluaciones para 10.000 multiplicaciones. Cambiad un tipo de crecimiento de la punta y ese evaluador tiene que pagar la factura entera otra vez, porque no guarda registro de qué celda lee 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 en caché, 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 hasta que nada cambie. Es correcto, y su coste sigue el tamaño del libro en vez del tamaño de la edición

Excel evita ambos con su cadena de cálculo, una ordenación de las celdas con fórmula que permite a una edición marcar un conjunto pequeño de celdas y recorrer 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 todas las fórmulas de todas las hojas y las evalúa todas. Cada llamada posterior evalúa solo las fórmulas aguas abajo de las celdas cuyos valores cambiaron desde la pasada anterior, más una lista corta de fórmulas que tienen que ejecutarse cada vez

Cómo Recalculate convierte una edición en una pasada ordenada

Cada celda con fórmula se convierte en un nodo, y una arista va de cada celda con 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 con fórmula dentro de ese rectángulo, y una referencia 3D sobre una racha de hojas se coteja contra cada hoja de la racha. Las constantes planas no son nodos en absoluto; 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 encola la dirección de la celda, y eso es todo. Cuando Recalculate arranca, cada fórmula cuyas referencias cubren una dirección encolada 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 nunca se ha llamado a Recalculate no guarda grafo alguno, y sus escrituras cuestan una única comprobación 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 abarata la pasada es cómo se cuentan los grados de entrada: solo cuentan los precedentes sucios. Un precedente limpio ya guarda un valor en caché válido, así que una fórmula nunca espera por él. Durante la pasada, una fórmula que referencia otra celda con fórmula lee el valor en caché de esa celda en vez de evaluarla otra vez; la ordenación garantiza que la caché ya está fresca. Cada fórmula sucia se evalúa exactamente una vez

Diagrama de flujo de una pasada de Workbook.Recalculate de HotXLS en Delphi, desde la recogida de marcas sucias y la ordenación topológica hasta la evaluación y el informe de ciclos
Cada pasada recoge el conjunto sucio, lo ordena topológicamente y evalúa cada celda afectada una sola vez; con el cálculo iterativo apagado, un ciclo de referencias vuelve como lxErrorRef en lugar de entrar en bucle

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 la hoja 2; las filas y columnas de Cells también se basan en 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;      // encola una dirección
    if Book.Recalculate = lxOk then        // las fórmulas alimentadas por Inputs!B2 se ejecutan de nuevo (aquí: las 9.999)
      Last := Model.Cells[10000, 2].Value; // resultado en caché, se lee como cualquier valor

    // Lectura puntual: RecalcCell ejecuta Recalculate y 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 concreto cada fórmula lee Inputs!$B$2, así que la segunda pasada todavía 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 en caché

Ejemplo de grafo de dependencias que muestra cómo una celda de entrada editada en HotXLS marca como sucias solo las fórmulas del modelo Delphi que dependen de ella para su reevaluación
Editar Inputs!B2 siembra un nodo sucio; la suciedad fluye por las aristas de precedente a dependiente y solo ese subgrafo vuelve a ejecutarse

Las escrituras de valor son baratas, las escrituras 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 el grafo entero como obsoleto, y el siguiente Recalculate lo reconstruye desde cero y evalúa cada fórmula. Eso cubre asignar Formula, limpiar una celda con fórmula escribiéndole Null o un valor vacío, añadir, mover o borrar hojas, insertar o borrar filas y columnas, editar nombres definidos y cambiar las tablas de datos de análisis. Registrar o desregistrar una función de usuario, o asignar OnUserFunction, conserva el grafo pero marca todos los nodos como sucios

La consecuencia práctica para los generadores de informes es una forma de bucle. Colocad las fórmulas una vez, llamad a Recalculate, y a partir de ahí tocad solo valores. Un bucle que reescribe una plantilla de fórmula por cliente y luego recalcula se lleva una reconstrucción completa en cada iteración, que es exactamente el coste que el grafo existe para evitar. Cuando de verdad haga falta un cambio de plantilla, agrupad todas las ediciones de fórmula antes de un único Recalculate, así la reconstrucción ocurre una vez. El bucle de escenarios mismo escribe entonces 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 encoladas o sucias
    if Book.Recalculate <> lxOk then
      Break;
  Results[I] := Model.Cells[10000, 2].Value;
end;

// Dejad que SaveAs recalcule solo si algo sigue pendiente
Book.RecalcBeforeSave := xrmIfDirty;
Book.SaveAs('scenarios.xlsx');

RecalcBeforeSave tiene por defecto xrmManual, que nunca recalcula durante un guardado. xrmIfDirty es el ajuste para los archivos generados: recalcula solo cuando el grafo está obsoleto o tiene fórmulas sucias, así que un libro ya recalculado en el bucle se escribe tal cual, y uno con una edición olvidada no se guarda con valores cacheados rancios

¿Qué funciones de Excel fuerzan el recálculo 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 como sucias al principio de cada pasada, haya o no haya cambiado algo aguas arriba, y también lo son todos sus dependientes. Las funciones de reloj y aleatorias son volátiles por la razón obvia. OFFSET e INDIRECT son volátiles porque las celdas que leen se calculan a partir de los valores de los argumentos en tiempo de ejecución, y ningún recorrido estático de la fórmula puede decir qué aristas dibujar

La misma regla se aplica a las referencias que el constructor del grafo no puede clavar a un rectángulo concreto, a un nombre definido que se refiere a sí mismo a través de otros nombres, a las referencias de desbordamiento de matriz dinámica y a las tablas de datos de análisis. 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 que falta significa un número rancio en un informe que parece terminado. Dejad OFFSET e INDIRECT fuera de los caminos calientes de los modelos grandes y confinadlos a las pocas celdas que de verdad necesitan direcciones calculadas, porque una celda volátil cerca de la punta de una cadena vuelve volátil toda la cadena en la práctica

Las funciones suministradas a través de 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 por tanto devolviendo su última respuesta entre pasadas. Escribid el tipo de cambio en una celda de entrada y pasad esa celda como argumento; el grafo ve entonces la dependencia, y la fórmula se reejecuta justo cuando cambia el tipo. 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 valor por defecto Iterate = False, TXLSXWorkbook.Recalculate devuelve lxErrorRef cuando la ordenación encuentra un ciclo. Los miembros del ciclo son los nodos sucios que el algoritmo de Kahn nunca 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 anteriores, mientras que todo lo demás del conjunto sucio se evalúa en orden. Una pasada fallida también marca el grafo como obsoleto, así que la llamada siguiente 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 siguen guardando sus cachés viejas;
    // LogWarning es una rutina vuestra
    LogWarning('Circular reference in model, report not written');
else
  LogWarning('Recalculation did not complete');
end;

Fijar Iterate := True activa el mismo cálculo iterativo que ofrece Excel. El motor evalúa el ciclo y sus dependientes bloqueados repetidamente, hasta IterateCount pasadas (100 por defecto), hasta que ningún valor se mueva en IterateDelta (0.001 por defecto) 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 los informes generados: los ciclos en modelos de informes reales son casi siempre errores, como una fila de totales atrapada dentro de su propio rango SUM, y lo que queréis de ellos es un código de error definido. Activadlo solo para modelos construidos alrededor de un bucle intencionado, como un interés cargado sobre un saldo medio que incluye el interés

Un caso que parece circular no se informa como ciclo. El argumento lookup-array de VLOOKUP, HLOOKUP, LOOKUP, MATCH, XLOOKUP y XMATCH sigue ensuciando 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) escrita en la columna B evalúa con normalidad. Para ciclos de verdad, el tracer de evaluación de fórmulas muestra la cadena de referencias que se pliega sobre sí misma, un paso cada vez

Fórmulas de matriz y pasadas multihilo

Una fórmula de matriz 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 matriz dinámica se manejan con más cautela: se ejecutan 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 nunca se asientan en 32 rondas, la llamada devuelve lxErrorCalc

RecalcThreads fijado por encima de 1 evalúa cada nivel topológico de fórmulas independientes repartido entre hilos de trabajo, con una calculadora separada por hilo. Las tablas de datos siguen corriendo en serie, y un libro que contiene matrices dinámicas se queda en la ruta serie. Cualquier manejador de funciones de usuario tiene que ser thread-safe antes de subir este valor; para un manejador que toca estado compartido, dejar RecalcThreads en su valor por defecto de 0 es la elección más segura

El grafo de dependencias, el manejo de volátiles y el cálculo iterativo forman 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 a su alrededor. Para aplicaciones Delphi y C++Builder que mantienen abiertas todo el día hojas de precios o libros de consolidación alimentándoles entradas nuevas, el trabajo por edición sigue entonces las celdas a las que la edición llega de verdad