HotXLS intersección implícita de nombres definidos en Delphi

Un nombre definido que apunta a una columna completa es leído por 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. HotXLS Delphi Component aplica esa intersección implícita en dos niveles, durante la evaluación y durante la extracción de dependencias, porque acertar con el valor no basta. Un walker de dependencias que expande el nombre a su área completa deja que cualquier fórmula aguas abajo que alimente una celda de esa área cierre un ciclo que no existe, y TXLSXWorkbook.Recalculate tendría entonces que rechazar el libro de trabajo completo como circular

Un libro de amortización de préstamos con 4805 fórmulas es el caso de estrés que pone todo esto a funcionar. Recalcularlo correctamente desde cachés obsoletas depende de tres mecanismos separados: la intersección implícita para los nombres, el ordenamiento de los escaneos de búsqueda, y el tipo numérico usado para la aritmética anidada. Equivoque cualquiera de los tres y el síntoma es o bien lxErrorRef, el código de referencia circular, o totales que se alejan de un cálculo independiente fila por fila. Este artículo recorre cada mecanismo con el código fuente involucrado

¿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 por una celda que depende de la fórmula. Considere =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 walker que registra B1 como dependiente de A1:A2 hace de A2 un precedente de B1, A2 ya lista a B1 como precedente, y la cola de Kahn que impulsa el recálculo incremental en HotXLS nunca ve que ninguno de los dos nodos llegue a grado de entrada cero. Este es el patrón del que están hechas las plantillas de préstamos: cada fila de período referencia columnas con nombre para el saldo, la tasa y el conteo de pagos, cada nombre abarca el cronograma completo, y cada fila además escribe en esas columnas. Expanda los nombres y el grafo es un componente fuertemente conexo gigante. Evalúelos con intersección implícita y el grafo es 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 se intersecta, 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 está fuera de A1:A2, la intersección es vacía e IFERROR la atrapa
    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 de TXLSFormula.InitFuncHash se registra a través de THashFunc.SetValue con una cadena opcional de clases 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 1 significa clase valor. Estas son las mismas tres clases que [MS-XLS] §2.2.2 asigna a los tokens operando, y el encoder confía en ellas también: al escribir una referencia calcula el ptg como $24 + $20 * aClass, lo que produce PtgRef para la clase 0, PtgRefV para la clase 1 y PtgRefA para la clase 2. Un archivo BIFF escrito por Excel guarda esa clase en cada token de referencia, así que un motor cuya tabla coincida con la especificación puede responder "¿este argumento es escalar?" sin mirar los datos. El argumento del medio 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) todavía multiplica 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) e IFERROR (ptg 255) dejan pasar lo que seleccionen, así que sus argumentos de rama heredan la clase de la posición que la función misma 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 ambas filas, y es la regla que más ejercita un cronograma de amortización, porque sus celdas de período se apoyan en IF para probar si el préstamo sigue abierto

Llevar la clase a través del recorrido de dependencias

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

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 rechace, un área bidimensional, una referencia multi-hoja o una fórmula cuya fila cae fuera de la columna con nombre, produce #VALUE! del lado de la evaluación y ninguna dependencia del 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: quita los envoltorios SA_GROUP de la definición compilada, y si la raíz es un SA_RANGE llama a GetRangeInfo, intersecta, y busca la única celda vía FGetValue en lugar de evaluar la definición completa. Las referencias externas toman el camino regular, porque no hay fila local contra la cual intersectar. De dónde salen en primer lugar el almacenamiento y el alcance 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é MATCH debe esperar a una columna a medio calcular?

Porque el argumento lookup-array de MATCH es una referencia de scan, y las referencias de scan se mantienen deliberadamente fuera del orden de evaluación duro. El artículo del lookup scan presenta TXLSDepRange.LookupScan y deletrea lo que usted sacrifica al excluir las aristas de scan del ordenamiento: una fórmula de búsqueda puede correr antes de que cada celda de su rango haya sido recalculada y leer valores obsoletos. En una sesión interactiva eso converge en el pase 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 conteo de períodos que no podría estar bien

TXLSDepGraph.TopoOrder por lo tanto trata las aristas de scan como aristas de ordenamiento suaves. Junto al grado de entrada duro mantiene un arreglo ScanInDeg, contando precedentes de scan sucios por nodo y decrementándolo a medida que esos precedentes se emiten, usando las listas ScanPrecedents, ScanDependents y ScanPrecedentCount que el grafo ya almacena. En cada iteración la cola de Kahn recorre su ventana de listos buscando el primer nodo cuyo ScanInDeg es cero y lo intercambia a la cabeza; si cada nodo listo sigue esperando un precedente de scan, la cabeza se desapila en su orden estable. Las aristas de scan nunca entran al 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 cargando valores obsoletos, PaymentCount regresa como 3; cambie 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 aparece en posiciones anidadas. Copiar un + o - de nivel superior a dos locales Double mantiene =B1-A1 exacto. 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 un Double, el resultado que observamos era un Currency, un tipo de punto fijo con cuatro decimales, así que 1066.1854641400994 menos 120 regresa truncado a cuatro decimales. A lo largo de un cronograma donde cada pago se capitaliza desde la fila anterior, ese error camina por cientos de períodos antes de llegar a los totales, razón por la cual TXLSCalculator.GetValueItem convierte ambos operandos antes de que el operador corra

// 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 promover a Currency.
// La aritmética de hoja de cálculo debe conservar precisión de punto flotante.
if VarIsNumeric(Value) then Value := Double(Value);
if VarIsNumeric(SubValue) then SubValue := Double(SubValue);

La guarda 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 todos conservan precisión double. 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 IEEE double 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 su lugar, 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 por fila dentro de 1E-7. No se activó iteración y no se suprimió ningún código de error para llegar ahí. Un ciclo genuino a través de un nombre, =B1 en A1 con B1 todavía leyendo Vertical, sigue devolviendo un error, exactamente como debe

Las reglas siguen a Excel. La intersección implícita aplica a un nombre cuya definición compilada, tras quitar paréntesis, es un área de columna única o de fila única 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 completos. El ordenamiento suave es una preferencia, no una garantía: un ciclo solo de scan evalúa en orden estable y lee lo que haya en caché, que es el trade-off que el artículo del lookup scan 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 de scan suave 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