Intersezione implicita dei nomi definiti in HotXLS

Un nome definito che punta a un'intera colonna viene letto da Excel come una singola cella quando compare in posizione scalare: =Vertical+1 alla riga 7 significa "la cella di Vertical alla riga 7", non l'intera area. Il componente HotXLS per Delphi applica quella intersezione implicita nella v2.382.4 su due livelli, in fase di valutazione e in fase di estrazione delle dipendenze, perché un template di prestito con 4805 formule ha mostrato che ottenere il valore giusto non basta. Quando il walker delle dipendenze espande il nome alla sua area completa, una formula a valle che alimenta una qualsiasi cella di quell'area chiude un ciclo che non esiste, e TXLSXWorkbook.Recalculate rifiuta l'intero workbook

Il template in questione è un normale workbook di ammortamento di un prestito. Con ogni valore in cache avvelenato a 777 e un Recalculate completo, entrambe le architetture del motore restituivano 23, cioè lxErrorRef, il codice di riferimento circolare. 3842 delle 4805 formule non corrispondevano all'attesa indipendente, B18 conteneva #VALUE!, E18 era ancora 777, e il conteggio delle rate in J7 aveva letto i segnaposto in una colonna del saldo non ancora completata. Tre difetti distinti si nascondevano dietro un unico codice di ritorno, e questo articolo li ripercorre uno per uno con il codice che li ha corretti

Perché un riferimento scalare a un nome di colonna crea un ciclo falso?

Perché un grafo di dipendenze conosce solo archi, e un arco da una formula a un'area di 480 righe sono 480 archi, uno dei quali punta all'indietro attraverso una cella che dipende dalla formula. Prendi =IF(TRUE,Vertical+1,0) in B1 con Vertical definito come Inputs!$A$1:$A$2, e =B1+1 in A2. Excel valuta B1 come A1+1 e A2 come B1+1, una catena lineare. Un walker che registra B1 come dipendente da A1:A2 rende A2 un precedente di B1, A2 elenca già B1 tra i suoi precedenti, e la coda di Kahn che guida il ricalcolo incrementale in HotXLS non vede mai nessuno dei due nodi raggiungere in-degree zero. È il pattern di cui sono fatti i template di prestito: ogni riga di periodo referenzia colonne con nome per il saldo, il tasso e il conteggio delle rate, ogni nome copre l'intero piano di ammortamento, e ogni riga scrive anche in quelle colonne. Espandi i nomi e il grafo è un unico componente fortemente connesso. Valutali con l'intersezione implicita e il grafo diventa un insieme di catene corte, una per riga, che è quanto ECMA-376 Part 1 §18.17.2 descrive per un operando di riferimento consumato dove serve un valore singolo

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;
    // Posizione scalare: Vertical collassa ad A1 perché la formula è nella riga 1
    Sheet.Cells[1, 2].Formula := '=IF(TRUE,Vertical+1,0)';
    Sheet.Cells[2, 1].Formula := '=B1+1';
    // Un nome la cui definizione è un altro nome interseca comunque, quindi questo è A2
    Sheet.Cells[2, 2].Formula := '=Alias';
    // Argomento di classe reference: viene sommata tutta l'area, nessuna intersezione
    Sheet.Cells[3, 2].Formula := '=SUM(Vertical)';
    // La riga 6 è fuori da A1:A2, l'intersezione è vuota e IFERROR la intercetta
    Sheet.Cells[6, 2].Formula := '=IFERROR(Vertical,42)';

    if Book.Recalculate = lxOk then
    begin
      // B1 = 2, A2 = 3, B2 = 3, B3 = 4, B6 = 42
      // Prima della v2.382.4 questo ramo era irraggiungibile: B1 -> A2 -> B1 era un ciclo
    end;
  finally
    Book.Free;
  end;
end;

Come fa HotXLS a stabilire che un argomento è scalare?

HotXLS legge la risposta dalla tabella delle funzioni invece che dalla forma dell'argomento. Ogni voce di TXLSFormula.InitFuncHash è registrata tramite THashFunc.SetValue con una stringa opzionale di classi per argomento: 'IF' porta '100', 'SUMIF' porta '010', 'VLOOKUP' porta '1011', e 'SUM' non ne porta nessuna, quindi tutti i suoi argomenti ricadono sulla classe 0 a livello di funzione. La nuova TXLSFormula.FunctionArgumentClass(APtg, AArgument) espone quel byte tramite THashFuncEntry.ArgClass, e un risultato di 1 significa classe value. Sono le stesse tre classi che [MS-XLS] §2.2.2 assegna ai token operando, e l'encoder dipendeva già da loro: quando scrive un riferimento calcola il ptg come $24 + $20 * aClass, che produce PtgRef per la classe 0, PtgRefV per la classe 1 e PtgRefA per la classe 2. Un file BIFF scritto da Excel memorizza quella classe in ogni token di riferimento, quindi un motore la cui tabella corrisponde alla specifica può rispondere alla domanda "questo argomento è scalare" senza guardare i dati. L'argomento centrale di SUMIF è il criterio, un valore; il primo e il terzo sono aree, riferimenti. SUMPRODUCT è registrata con classe 2 a livello di funzione, array, ed è per questo che =SUMPRODUCT(Vertical,Vertical) moltiplica ancora l'intera area

Tre funzioni non consultano la propria voce di tabella per nulla oltre al primo argomento. IF (ptg 1), CHOOSE (ptg 100) e IFERROR (ptg 255) lasciano passare ciò che selezionano, quindi i loro argomenti di ramo ereditano la classe della posizione che la funzione stessa occupa. È quella singola regola a permettere che =CHOOSE(1,Vertical,0) in G2 si risolva in A2 mentre =SUMIF(Vertical,">0",Vertical) accanto somma ancora entrambe le righe, ed è la regola che un piano di ammortamento esercita di più, perché le sue celle di periodo si appoggiano a IF per verificare se il prestito è ancora aperto

Portare la classe attraverso il walk delle dipendenze

L'estrattore di dipendenze in lxCalc.pas è un Walk ricorsivo sull'albero sintattico compilato, ed esiste due volte, una in TXLSCalculator.ExtractDependencies per il grafo a livello di workbook e una in ExtractWorkspaceDependencies per il grafo tra workbook. La v2.382.4 dà a entrambi i walker due parametri in più. AScalar parte come True alla radice di una formula, viene ricalcolato per ogni figlio funzione a partire da FunctionArgumentClass, e viene passato invariato per gli argomenti di ramo dei ptg 1, 100 e 255. ANameRoot diventa True solo quando il walker scende nella definizione compilata di un nome, e sopravvive solo attraverso i nodi SA_GROUP, le parentesi, così un nome definito come =A1:A2+1 non viene scambiato per un'area semplice. Quando entrambi i flag sono True su un nodo SA_RANGE, AddResolvedRange restringe l'area con lo stesso helper che usa il valutatore prima di registrare la dipendenza. L'helper è abbastanza corto da citarlo per intero

function IntersectNamedScalarRange(CurRow, CurCol: Integer;
  var Row1, Row2, Col1, Col2: Integer): Boolean;
begin
  Result := False;
  if (Row1 = Row2) and (Col1 = Col2) then Exit(True);   // già una cella
  if (Col1 = Col2) and (CurRow >= Row1) and (CurRow <= Row2) then
  begin
    Row1 := CurRow; Row2 := CurRow;                     // colonna singola: prendi questa riga
    Exit(True);
  end;
  if (Row1 = Row2) and (CurCol >= Col1) and (CurCol <= Col2) then
  begin
    Col1 := CurCol; Col2 := CurCol;                     // riga singola: prendi questa colonna
    Result := True;
  end;
end;

Tutto ciò che l'helper rifiuta, un'area bidimensionale, un riferimento multi-foglio o una formula la cui riga cade fuori dalla colonna con nome, produce #VALUE! sul versante della valutazione e nessuna dipendenza sul versante del grafo, che è ciò che Excel fa per un'intersezione vuota. Il versante della valutazione vive in TXLSCalculator.GetValueItemName: rimuove gli involucri SA_GROUP dalla definizione compilata e, se la radice è un SA_RANGE, chiama GetRangeInfo, interseca e recupera l'unica cella tramite FGetValue invece di valutare l'intera definizione. I riferimenti esterni restano sul vecchio percorso, perché non c'è una riga locale con cui intersecare. Da dove arrivino memorizzazione e scope di un nome lo racconta l'articolo sui nomi definiti e le formule tra fogli; qui interessa solo cosa fa il motore una volta che il nome si risolve

Perché MATCH su una colonna calcolata a metà leggeva 777?

Perché l'argomento lookup-array di MATCH è un riferimento di scansione, e i riferimenti di scansione erano stati deliberatamente esclusi dall'ordine di valutazione. L'articolo sugli scan nelle ricerche introduceva TXLSDepRange.LookupScan e si chiudeva con una sezione intitolata "What you give up by excluding scan edges from the ordering": una formula di ricerca può girare prima che ogni cella del suo intervallo sia stata ricalcolata e leggere valori obsoleti. In una sessione interattiva la cosa converge al passaggio successivo. In un ricalcolo batch di un template avvelenato no, e PaymentCount, definito come =MATCH(0.01,Balances,-1)+1, leggeva i segnaposto 777 ancora presenti nella colonna del saldo e restituiva un numero di rate che non poteva essere giusto

Ora TXLSDepGraph.TopoOrder tratta gli archi di scansione come archi di ordinamento soft. Accanto all'in-degree hard mantiene un array ScanInDeg, che conta i precedenti di scansione sporchi per nodo e lo decrementa man mano che quei precedenti vengono emessi, usando le liste ScanPrecedents, ScanDependents e ScanPrecedentCount che la modifica precedente memorizzava già. A ogni iterazione la coda di Kahn scorre la sua finestra di nodi pronti in cerca del primo nodo il cui ScanInDeg è zero e lo scambia in testa; se ogni nodo pronto sta ancora aspettando un precedente di scansione, la testa viene estratta nel suo ordine stabile. Gli archi di scansione non entrano mai nell'in-degree hard, quindi un VLOOKUP autoreferenziale sulla propria colonna resta legale, ma una ricerca che potrebbe aspettare un precedente completabile ora lo fa. La regressione che fissa questo comportamento, LookupScan_WaitsForDirtyFormulaValues, avvelena tre celle del saldo a 777 e si aspetta che PaymentCount torni 3, poi porta l'input a zero e si aspetta che =IFERROR(PaymentCount,99) veda il #N/A e restituisca 99

Da dove veniva il troncamento a quattro decimali?

Dall'aritmetica Variant di Delphi, e solo nelle posizioni annidate. Gli operatori binari in TXLSCalculator.GetValueItem copiavano già un + o un - di primo livello in due variabili locali Double, quindi =B1-A1 andava bene. Dentro =IF(TRUE,B1-A1,0) la stessa sottrazione girava come Value := Value - SubValue su due Variant, e quando un operando era un valore di cella Int64 e l'altro un Double, il risultato che osservavamo era un Currency, un tipo a virgola fissa con quattro decimali, quindi 1066.1854641400994 meno 120 tornava troncato a quattro decimali. Su un piano dove ogni rata è composta dalla riga precedente, quell'errore attraversa centinaia di periodi prima di arrivare ai totali

// TXLSCalculator.GetValueItem, ramo dell'aritmetica binaria (lxCalc.pas)
if VarIsNull(Value) then Value := 0;
if VarIsNull(SubValue) then SubValue := 0;
// L'aritmetica Variant mista Int64/Double può promuovere a Currency.
// L'aritmetica di un foglio di calcolo deve mantenere la precisione in virgola mobile.
if VarIsNumeric(Value) then Value := Double(Value);
if VarIsNumeric(SubValue) then SubValue := Double(SubValue);

La guardia gira prima di SA_ADD, SA_SUB, SA_MUL e SA_DIV allo stesso modo, e la regressione Arithmetic_MixedInt64AndDoubleKeepsPrecision memorizza Int64(120) in A1 e 1066.1854641400994 in B1, poi verifica la differenza e la somma annidate entro 1E-10 e il prodotto e il quoziente entro 1E-8 e 1E-12. HotXLS non pretende di conoscere ogni regola di promozione che la RTL applica ai tipi Variant misti attraverso le versioni del compilatore; sostiene che l'aritmetica di un foglio di calcolo è double IEEE, e ora rende entrambi gli operandi double prima che l'operatore li veda, il che elimina la domanda

Che cosa garantisce la correzione e che cosa no

Dopo la v2.382.4 entrambe le architetture del motore restituiscono lxOk per il template avvelenato, tutti i 4805 valori in cache corrispondono all'attesa indipendente riga per riga entro 1E-7, e le asserzioni che le cache fossero davvero avvelenate, che l'hash sorgente sia invariato e che ogni formula sia ancora presente valgono tutte. Non è stata abilitata alcuna iterazione e nessun codice di errore è stato soppresso per arrivarci. Un ciclo reale attraverso un nome, =B1 in A1 con B1 che legge ancora Vertical, restituisce comunque un errore, e il test NamedScalarRanges_IntersectWithoutFalseCycles si chiude asserendo esattamente questo

Vale la pena dire chiaramente quali siano i confini. L'intersezione implicita si applica solo a un nome la cui definizione compilata, dopo aver tolto le parentesi, è un'area su una singola colonna o una singola riga di un unico foglio; un nome bidimensionale in posizione scalare dà #VALUE!, come in Excel, e una funzione che la tabella non conosce riceve la classe 0 da FunctionArgumentClass, quindi i suoi argomenti con nome vengono comunque espansi per intero. L'ordinamento soft è una preferenza, non una garanzia: un ciclo fatto solo di scansioni viene comunque valutato in ordine stabile e legge ciò che è in cache, che è il comportamento che l'articolo sugli scan nelle ricerche accettava di proposito. E il risultato sull'intero template è verificato contro uno script di attesa indipendente, non contro un altro motore di fogli di calcolo, perché la suite office di riferimento non ha finito di ricalcolare il template originale entro un budget di 60 secondi. HotXLS è un componente nativo per fogli di calcolo per Delphi e C++Builder che legge, ricalcola e scrive XLS, XLSX, ODS e CSV senza Excel installato; l'intersezione dei nomi, la tabella delle classi di argomento e l'ordinamento soft degli scan valgono per ogni formato perché il motore di calcolo è condiviso, mentre la copertura attuale delle funzioni è elencata nella pagina del componente spreadsheet HotXLS per Delphi