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 a dos niveles, durante la evaluación y durante la extracción de dependencias, porque acertar el valor no basta. Un caminante de dependencias que expande el nombre a su área completa deja que cualquier fórmula posterior que alimenta una celda de esa área cierre un ciclo que no existe, y TXLSXWorkbook.Recalculate tendría entonces que rechazar el libro entero como circular

Un libro de amortización de préstamos con garantía con 4805 fórmulas es el caso de estrés que pone todo esto a trabajar. Recalcularlo correctamente desde cachés obsoletas depende de tres mecanismos separados: la intersección implícita para nombres, el orden de los escaneos de lookup, y el tipo numérico usado para la aritmética anidada. Equivócate con cualquiera de ellos y el síntoma es o bien lxErrorRef, el código de referencia circular, o totales que se van derivando de un cálculo independiente fila a fila. Este artículo recorre cada mecanismo con el código fuente implicado

¿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
      // Expandir Vertical a A1:A2 aquí convertiría B1 -> A2 -> B1 en un ciclo falso
    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. 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 se apoya en ellas también: 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. Ambos caminantes llevan 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 toman el camino regular, 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é debe MATCH esperar a una columna medio calculada?

Porque el argumento lookup-array de MATCH es una referencia de escaneo, y las referencias de escaneo se mantienen deliberadamente fuera del orden de evaluación duro. El artículo del escaneo de lookups presenta TXLSDepRange.LookupScan y expone lo que pierdes al excluir del orden las aristas de escaneo: 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 único de una plantilla cuyas cachés están obsoletas no: PaymentCount, definido como =MATCH(0.01,Balances,-1)+1, leería los valores obsoletos todavía sentados en la columna de saldo y devolvería un número de pagos que no podría ser correcto

TXLSDepGraph.TopoOrder trata por tanto 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 grafo ya guarda. 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 puede esperar a un precedente terminable lo hace. Con tres celdas de saldo con valores obsoletos, PaymentCount vuelve como 3; cambia la entrada a cero y =IFERROR(PaymentCount,99) ve el #N/A y devuelve 99

Por qué la aritmética anidada se fuerza a Double

Porque la aritmética de Variant de Delphi tiene una trampa que solo se ve en posiciones anidadas. Copiar un + o - de nivel superior a dos locales Double mantiene =B1-A1 exacta. Dentro de =IF(TRUE,B1-A1,0), la implementación obvia corre la misma resta como Value := Value - SubValue sobre dos Variant, y cuando un operando es un valor de celda Int64 y el otro Double, el resultado que observamos fue un Currency, un tipo de punto fijo con cuatro decimales, así que 1066.1854641400994 menos 120 vuelve 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, razón por la que TXLSCalculator.GetValueItem convierte ambos operandos antes de que corra el operador

// 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, así que con Int64(120) en A1 y 1066.1854641400994 en B1 la diferencia, suma, producto y cociente anidados mantienen todos doble precisión. HotXLS no pretende conocer cada regla de promoción que la RTL aplica a tipos Variant mixtos entre versiones del compilador; trata la aritmética de hoja de cálculo como double IEEE y vuelve ambos operandos double antes de que el operador los vea, lo que elimina la pregunta

Lo que el motor garantiza para las columnas con nombre

Con los tres mecanismos en marcha, ambas arquitecturas del motor recalculan la plantilla de amortización desde cachés obsoletas hasta lxOk, y los 4805 valores coinciden con una expectativa independiente fila a fila dentro de 1E-7. No se activa iteración ni se silencia código de error alguno para lograrlo. Un ciclo genuino a través de un nombre, =B1 en A1 con B1 todavía leyendo Vertical, sigue devolviendo error, exactamente como debe

Las reglas siguen a Excel. La intersección implícita se aplica 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 expanden por completo. El ordenamiento blando es una preferencia, no una garantía: un ciclo solo de escaneo se evalúa en orden estable y lee lo que haya en caché, que es el compromiso que el artículo del escaneo de lookups acepta a propósito. 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