Nombres definidos HotXLS: intersección implícita en Delphi

Un nombre definido que apunta a una columna entera lo lee Excel como una sola celda cuando aparece en posición escalar: =Vertical+1 en la fila 7 significa "la celda de la fila 7 de Vertical", no el área completa. El HotXLS Delphi Component aplica esa intersección implícita en v2.382.4 a dos niveles, durante la evaluación y durante la extracción de dependencias, porque una plantilla de préstamos con 4805 fórmulas demostró que acertar el valor no basta. Cuando el caminante de dependencias expande el nombre a su área completa, una fórmula posterior que alimenta cualquier celda de esa área cierra un ciclo que no existe, y TXLSXWorkbook.Recalculate rechaza el libro entero

La plantilla en cuestión es un libro estándar de amortización de préstamos. Con todos los valores en caché envenenados a 777 y una pasada completa de Recalculate, las dos arquitecturas del motor devolvían 23, que es lxErrorRef, el código de referencia circular. 3842 de las 4805 fórmulas no cuadraban con la expectativa independiente, B18 contenía #VALUE!, E18 seguía en 777, y el número de pagos en J7 había leído los placeholders de una columna de saldo a medio construir. Tres defectos distintos se escondían detrás de un único código de retorno, y este artículo recorre cada uno con el código fuente que lo arregló

¿Por qué una referencia escalar a un nombre de columna crea un ciclo falso?

Porque un grafo de dependencias solo conoce aristas, y una arista de una fórmula a un área de 480 filas son 480 aristas, una de las cuales apunta de vuelta a través de una celda que depende de la fórmula. Considera =IF(TRUE,Vertical+1,0) en B1 con Vertical definido como Inputs!$A$1:$A$2, y =B1+1 en A2. Excel evalúa B1 como A1+1 y A2 como B1+1, una cadena recta. Un caminante que registra B1 como dependiente de A1:A2 convierte a A2 en precedente de B1, A2 ya lista a B1 como precedente, y la cola de Kahn que mueve el recálculo incremental en HotXLS nunca ve que ningún nodo llegue a grado de entrada cero. Este es el patrón del que están hechas las plantillas de préstamos: cada fila de periodo referencia columnas con nombre para el saldo, el tipo y el número de pagos, cada nombre cubre toda la tabla, y cada fila además escribe en esas columnas. Expandir los nombres hace del grafo un único componente fuertemente conexo gigante. Evaluarlos con intersección implícita lo convierte en un conjunto de cadenas cortas, una por fila, que es lo que ECMA-376 Part 1 §18.17.2 describe para un operando de referencia consumido donde se requiere un único valor

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Inputs');
    Book.DefinedNames.Add('Vertical', 'Inputs!$A$1:$A$2');
    Book.DefinedNames.Add('Alias', '=Vertical');
    Sheet.Cells[1, 1].Value := 1;
    // Posición escalar: Vertical colapsa a A1 porque la fórmula está en la fila 1
    Sheet.Cells[1, 2].Formula := '=IF(TRUE,Vertical+1,0)';
    Sheet.Cells[2, 1].Formula := '=B1+1';
    // Un nombre cuya definición es otro nombre también interseca, así que esto es A2
    Sheet.Cells[2, 2].Formula := '=Alias';
    // Argumento de clase referencia: se suma el área completa, sin intersección
    Sheet.Cells[3, 2].Formula := '=SUM(Vertical)';
    // La fila 6 cae fuera de A1:A2, la intersección es vacía y IFERROR la captura
    Sheet.Cells[6, 2].Formula := '=IFERROR(Vertical,42)';

    if Book.Recalculate = lxOk then
    begin
      // B1 = 2, A2 = 3, B2 = 3, B3 = 4, B6 = 42
      // Antes de v2.382.4 esta rama era inalcanzable: B1 -> A2 -> B1 era un ciclo
    end;
  finally
    Book.Free;
  end;
end;

¿Cómo decide HotXLS que un argumento es escalar?

HotXLS lee la respuesta de la tabla de funciones y no de la forma del argumento. Cada entrada en TXLSFormula.InitFuncHash se registra mediante THashFunc.SetValue con una cadena opcional de clase por argumento: 'IF' lleva '100', 'SUMIF' lleva '010', 'VLOOKUP' lleva '1011', y 'SUM' no lleva ninguna, así que todos sus argumentos caen a la clase 0 a nivel de función. El nuevo TXLSFormula.FunctionArgumentClass(APtg, AArgument) expone ese byte vía THashFuncEntry.ArgClass, y un resultado de 1 significa clase valor. Son las mismas tres clases que [MS-XLS] §2.2.2 asigna a los tokens operando, y el codificador ya dependía de ellas: cuando escribe una referencia calcula el ptg como $24 + $20 * aClass, lo que produce PtgRef para la clase 0, PtgRefV para la 1 y PtgRefA para la 2. Un archivo BIFF escrito por Excel almacena esa clase en cada token de referencia, de modo que un motor cuya tabla coincide con la especificación puede responder "¿es este argumento escalar?" sin mirar los datos. El argumento central de SUMIF es el criterio, un valor; el primero y el tercero son áreas, referencias. SUMPRODUCT está registrado con clase 2 a nivel de función, array, y por eso =SUMPRODUCT(Vertical,Vertical) sigue multiplicando el área completa

Tres funciones no consultan su propia entrada de tabla para nada más allá del primer argumento. IF (ptg 1), CHOOSE (ptg 100) y IFERROR (ptg 255) dejan pasar lo que seleccionen, así que sus argumentos de rama heredan la clase de la posición que la propia función ocupa. Esa única regla es la que permite que =CHOOSE(1,Vertical,0) en G2 resuelva a A2 mientras =SUMIF(Vertical,">0",Vertical) al lado sigue sumando las dos filas, y es la regla que más ejercita una tabla de amortización, porque sus celdas de periodo se apoyan en IF para comprobar si el préstamo sigue abierto

Llevar la clase a través del recorrido de dependencias

El extractor de dependencias en lxCalc.pas es un Walk recursivo sobre el árbol de sintaxis compilado, y existe por duplicado: una vez en TXLSCalculator.ExtractDependencies para el grafo por libro y otra en ExtractWorkspaceDependencies para el grafo entre libros. v2.382.4 da a ambos caminantes dos parámetros extra. AScalar empieza como True en la raíz de la fórmula, se recalcula para cada hijo función desde FunctionArgumentClass, y se pasa sin cambios para los argumentos de rama de los ptg 1, 100 y 255. ANameRoot se vuelve True solo cuando el caminante desciende a la definición compilada de un nombre, y sobrevive únicamente a través de nodos SA_GROUP, los paréntesis, de modo que un nombre definido como =A1:A2+1 no se confunda con un área simple. Cuando ambos flags son True en un nodo SA_RANGE, AddResolvedRange reduce el área con el mismo helper que usa el evaluador antes de registrar la dependencia. El helper es lo bastante corto para citarlo entero

function IntersectNamedScalarRange(CurRow, CurCol: Integer;
  var Row1, Row2, Col1, Col2: Integer): Boolean;
begin
  Result := False;
  if (Row1 = Row2) and (Col1 = Col2) then Exit(True);   // ya es una celda
  if (Col1 = Col2) and (CurRow >= Row1) and (CurRow <= Row2) then
  begin
    Row1 := CurRow; Row2 := CurRow;                     // columna única: toma esta fila
    Exit(True);
  end;
  if (Row1 = Row2) and (CurCol >= Col1) and (CurCol <= Col2) then
  begin
    Col1 := CurCol; Col2 := CurCol;                     // fila única: toma esta columna
    Result := True;
  end;
end;

Todo lo que el helper rechaza, un área bidimensional, una referencia multi-hoja o una fórmula cuya fila cae fuera de la columna con nombre, produce #VALUE! en el lado de la evaluación y ninguna dependencia en el lado del grafo, que es lo que Excel hace para una intersección vacía. El lado de la evaluación vive en TXLSCalculator.GetValueItemName: elimina los envoltorios SA_GROUP de la definición compilada, y si la raíz es un SA_RANGE llama a GetRangeInfo, interseca, y obtiene la única celda a través de FGetValue en lugar de evaluar la definición completa. Las referencias externas se quedan en el camino antiguo, porque no hay fila local contra la que intersecar. De dónde salen el almacenamiento y el ámbito de un nombre se cubre en el artículo de nombres definidos y fórmulas entre hojas; aquí el punto es solo qué hace el motor una vez que el nombre se resuelve

¿Por qué un MATCH sobre una columna a medio calcular leía 777?

Porque el argumento lookup-array de MATCH es una referencia de escaneo, y las referencias de escaneo se excluyeron deliberadamente del orden de evaluación. El artículo del escaneo de lookups presentó TXLSDepRange.LookupScan y cerró con una sección llamada "Lo que pierdes al excluir las aristas de escaneo del ordenamiento": una fórmula de búsqueda puede ejecutarse antes de que cada celda de su rango se haya recalculado y leer valores obsoletos. En una sesión interactiva eso converge en la pasada siguiente. En un recálculo por lotes de una plantilla envenenada no, y PaymentCount, definido como =MATCH(0.01,Balances,-1)+1, leía los placeholders 777 todavía sentados en la columna de saldo y devolvía un número de pagos que no podía ser correcto

TXLSDepGraph.TopoOrder trata ahora las aristas de escaneo como aristas de ordenamiento blandas. Junto al grado de entrada duro mantiene un array ScanInDeg, contando los precedentes de escaneo sucios por nodo y decrementándolo a medida que esos precedentes se emiten, usando las listas ScanPrecedents, ScanDependents y ScanPrecedentCount que el cambio anterior ya almacenaba. En cada iteración la cola de Kahn escanea su ventana de listos buscando el primer nodo cuyo ScanInDeg sea cero y lo intercambia a la cabeza; si todo nodo listo sigue esperando a un precedente de escaneo, la cabeza se extrae en su orden estable. Las aristas de escaneo nunca entran en el grado de entrada duro, así que un VLOOKUP autorreferencial sobre su propia columna sigue siendo legal, pero una búsqueda que podía esperar a un precedente terminable ahora espera. La regresión que fija esto, LookupScan_WaitsForDirtyFormulaValues, envenena tres celdas de saldo a 777 y espera que PaymentCount devuelva 3, luego voltea la entrada a cero y espera que =IFERROR(PaymentCount,99) vea el #N/A y devuelva 99

¿De dónde salió el truncado a cuatro decimales?

De la aritmética de Variant de Delphi, y solo en posiciones anidadas. Los operadores binarios en TXLSCalculator.GetValueItem ya copiaban un + o - de nivel superior a dos locales Double, así que =B1-A1 estaba bien. Dentro de =IF(TRUE,B1-A1,0) la misma resta corría como Value := Value - SubValue sobre dos Variant, y cuando un operando era un valor de celda Int64 y el otro Double, el resultado que observamos era un Currency, un tipo de punto fijo con cuatro decimales, así que 1066.1854641400994 menos 120 volvía truncado a cuatro decimales. En una tabla donde cada pago se compone a partir de la fila anterior, ese error camina por cientos de periodos antes de llegar a los totales

// TXLSCalculator.GetValueItem, rama de aritmética binaria (lxCalc.pas)
if VarIsNull(Value) then Value := 0;
if VarIsNull(SubValue) then SubValue := 0;
// La aritmética Variant mixta Int64/Double puede promocionar a Currency.
// La aritmética de hoja de cálculo debe retener precisión de coma flotante.
if VarIsNumeric(Value) then Value := Double(Value);
if VarIsNumeric(SubValue) then SubValue := Double(SubValue);

El guardia corre por igual antes de SA_ADD, SA_SUB, SA_MUL y SA_DIV, y la regresión Arithmetic_MixedInt64AndDoubleKeepsPrecision guarda Int64(120) en A1 y 1066.1854641400994 en B1, y luego comprueba la resta y la suma anidadas a 1E-10 y el producto y el cociente a 1E-8 y 1E-12. HotXLS no pretende conocer cada regla de promoción que la RTL aplica a tipos Variant mixtos entre versiones del compilador; pretende que la aritmética de hoja de cálculo es double IEEE, y ahora convierte ambos operandos a double antes de que el operador los vea, lo que elimina la pregunta

Lo que el arreglo garantiza, y lo que no

Tras v2.382.4 las dos arquitecturas del motor devuelven lxOk para la plantilla envenenada, los 4805 valores en caché coinciden con la expectativa independiente fila a fila dentro de 1E-7, y las aserciones de que las cachés estaban realmente envenenadas, de que el hash del origen no cambió y de que cada fórmula sigue presente se cumplen todas. No se activó iteración ni se silenció código de error alguno para llegar ahí. Un ciclo genuino a través de un nombre, =B1 en A1 con B1 todavía leyendo Vertical, sigue devolviendo error, y el test NamedScalarRanges_IntersectWithoutFalseCycles termina precisamente afirmando eso

Merece la pena enunciar los límites sin rodeos. La intersección implícita se aplica solo a un nombre cuya definición compilada, tras eliminar paréntesis, es un área de una sola columna o de una sola fila en una hoja; un nombre bidimensional en posición escalar es #VALUE!, como en Excel, y una función que la tabla no conoce recibe clase 0 de FunctionArgumentClass, así que sus argumentos de nombre se siguen expandiendo por completo. El ordenamiento blando es una preferencia, no una garantía: un ciclo solo de escaneo se evalúa aún en orden estable y lee lo que haya en caché, que es el comportamiento que el artículo del escaneo de lookups aceptó a propósito. Y el resultado de la plantilla completa se verifica contra un script de expectativa independiente, no contra otro motor de hojas de cálculo, porque la suite ofimática de referencia no terminó de recalcular la plantilla original dentro de un presupuesto de 60 segundos. HotXLS es un componente de hojas de cálculo nativo para Delphi y C++Builder que lee, recalcula y escribe XLS, XLSX, ODS y CSV sin Excel instalado; la intersección de nombres, la tabla de clases de argumento y el ordenamiento blando de escaneos se aplican a todos los formatos porque el motor de cálculo es compartido, y la cobertura actual de funciones está listada en la página de producto del componente de hojas de cálculo HotXLS para Delphi