Formule array in HotXLS: perché Excel aggiunge @ e #VALUE!

Excel 365 inserisce @ in una formula come =SUM(A1:B1*{10,100}) e mostra #VALUE! quando il file la salva come formula ordinaria, perché a quel punto Excel applica la implicit intersection alla vecchia maniera a ogni operando di operatore. Dalla v2.384.68, HotXLS Delphi Component salva queste formule con operatori array come fa Excel 365: come formule dynamic array a cella singola in XLSX e come formule array a cella singola in XLS

Il sintomo sopravvive alla code review. Il tuo servizio Delphi scrive una cartella di lavoro, HotXLS la ricalcola e mette in cache 210 per =SUM(A1:B1*{10,100}), e il cliente la apre in Excel 16 trovando =SUM(@A1:B1*@{10,100}) nella barra della formula e #VALUE! nella cella. Nulla nel file è malformato. Quello che manca sono i metadati che dicono a Excel che la formula è stata scritta sotto le regole dynamic array, e senza quelli Excel ricade nel suo modello di valutazione precedente alle dynamic array

Perché Excel 365 aggiunge @ a una formula che HotXLS ha calcolato correttamente?

Excel 365 aggiunge @ perché una formula senza marcatura dynamic array è, per definizione, una formula legacy, e le formule legacy riducono un intervallo multicella a una cella ovunque un operatore si aspetti un valore singolo. Quella riduzione è la implicit intersection: Excel prende la cella dell'intervallo che condivide la riga della formula (per un intervallo verticale) o la colonna (per un intervallo orizzontale), e se una cella del genere non esiste il risultato è #VALUE!. Excel 365 conserva questo significato per le formule old-style e mostra @ per rendere visibile la riduzione

Mettili alla prova: scrivi =SUM(A1:B1*{10,100}) in E5 e la lettura legacy diventa ovvia. A1:B1 è un intervallo orizzontale, la formula sta nella colonna E, l'intervallo non ha celle in colonna E, quindi @A1:B1 è #VALUE! e l'intera SUM lo eredita. Sotto le regole dynamic array lo stesso testo moltiplica elemento per elemento, 1 × 10 + 2 × 100, e restituisce 210. Il motore di formule di HotXLS valuta alla maniera dynamic array dalle release v2.384.61 e v2.384.63; il formato file semplicemente non lo dice. Con A1:B2 che contiene 1, 2, 3 e 4, queste sono le formule sonda e ciò che Excel 16 mostra:

Formula	Risultato HotXLS	Excel 16, salvata come formula normale	Salvataggio dalla v2.384.68
=SUM(A1:B1*{10,100})	210	#VALUE!	Dynamic array, Excel mostra 210
=SUM((A1:B2>2)*1)	2	Implicit intersection, sbagliato o errore	Dynamic array, Excel mostra 2
=SUMPRODUCT((A1:B2>2)*1)	2	Implicit intersection, sbagliato o errore	Dynamic array, Excel mostra 2
=MAX(A1:B2-1)	3	Implicit intersection, sbagliato o errore	Dynamic array, Excel mostra 3
=SUM(A1:B2)	10	10	Formula normale, invariata

L'ultima riga conta quanto le prime quattro. SUM(A1:B2) passa un intervallo direttamente a un parametro di funzione che accetta riferimenti, quindi nessun operatore vede mai un intervallo multicella e nessuna intersezione può avvenire. Excel 365 stesso salva quella formula come formula normale, e HotXLS fa lo stesso

Come HotXLS salva le formule con operatori array in XLSX e XLS

HotXLS scrive una formula con operatore array in XLSX come dynamic array a cella singola: l'elemento <c> porta cm="1", la formula è <f t="array" ref="E5">, e il pacchetto si guadagna xl/metadata.xml con un tipo di metadato XLDAPR la cui estensione contiene dynamicArrayProperties fDynamic="1". L'attributo cm è un indice a base uno nel blocco cellMetadata di quella parte, e il record XLDAPR dietro è ciò che dice a Excel "valuta questa sotto le regole dynamic array". È la stessa struttura che Excel 16 scrive quando digiti la stessa formula e salvi, ed è così che il layout bersaglio è stato individuato in primo luogo

In XLS non c'è una parte di metadati, quindi HotXLS usa l'unico costrutto che BIFF8 ha per la valutazione array: una formula array a cella singola. La cella prende un record FORMULA il cui token stream è un solo PtgExp che punta a sé stessa, seguito da un record ARRAY ($0221) che porta la formula vera parseata sull'intervallo di una cella. Excel 365 scrive le formule dynamic array in XLS allo stesso modo, e una versione di Excel più vecchia che legge il file vede una classica formula array da Ctrl+Shift+Enter

Nessuna API nuova. La marcatura avviene quando assegni la formula attraverso la normale API di cella, in entrambi i motori. Sul lato XLSX è TXLSXCell.Formula:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    Sheet.Cells[1, 1].Value := 1;
    Sheet.Cells[1, 2].Value := 2;
    Sheet.Cells[2, 1].Value := 3;
    Sheet.Cells[2, 2].Value := 4;

    // Operatore su un intervallo o un array inline: salvato come dynamic array
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // Intervallo passato dritto a una funzione: resta un <f> ordinario
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

    if Book.Recalculate = lxOk then
      Writeln(VarToStr(Sheet.Cells[5, 5].Value));   // 210

    // La radice array conserva il testo senza l'uguale iniziale
    Writeln(Sheet.Cells[5, 5].Formula);              // SUM(A1:B1*{10,100})

    Book.SaveAs('probe.xlsx');   // E5 ed E6 prendono cm="1" + t="array"
  finally
    Book.Free;
  end;
end;

Dopo la conversione, TXLSXCell.Formula restituisce il testo senza =, la stessa forma che memorizza TXLSXRange.SetDynamicArrayFormula, così il codice che confronta stringhe di formula dopo l'assegnazione dovrebbe normalizzare l'= iniziale

Il motore classico segue la stessa regola attraverso IXLSRange.Formula su una cella singola. Assegnare la formula la dirotta internamente sul percorso array a cella singola, quindi l'XLS salvato contiene la coppia FORMULA più ARRAY:

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['A1', 'A1'].Value := 1;
  Sh.Range['B1', 'B1'].Value := 2;
  Sh.Range['A2', 'A2'].Value := 3;
  Sh.Range['B2', 'B2'].Value := 4;

  Sh.Range['E5', 'E5'].Formula := '=SUM(A1:B1*{10,100})';  // record ARRAY
  Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)';         // record ARRAY
  Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)';           // FORMULA normale

  Writeln(VarToStr(Sh.Range['E5', 'E5'].Value));   // 210
  Writeln(VarToStr(Sh.Range['E6', 'E6'].Value));   // 3
  Wb.SaveAs('probe.xls');
end;

Se stai ancorando un risultato multicella anziché un aggregato scalare, le API esplicite restano lo strumento giusto: SetArrayFormula per un rettangolo già dimensionato, come descritto in formule dynamic array con spill in HotXLS, oppure TXLSXRange.SetDynamicArrayFormula quando vuoi la marcatura dynamic array XLSX su un intervallo che dimensioni da te. Il percorso automatico di questo articolo copre solo le formule digitate in una cella

Quali formule HotXLS contrassegna come dynamic array?

HotXLS contrassegna una formula solo quando un operatore ha un sottoalbero di operandi che produce un array. Il controllo gira sull'albero sintattico compilato, e un operando produce un array se è un intervallo multicella, una costante array inline, o un'altra espressione di operatore che a sua volta ha un tale operando. Le parentesi sono trasparenti. Gli operatori che contano sono quelli aritmetici (+ - * / ^), la concatenazione (&), i sei confronti, più e meno unari, e percento:

• A1:B1*{10,100}, (A1:B2>2)*1, --(B1:B2>0) e A1:B2-1 vengono contrassegnati, ovunque compaiano nella formula, incluso dentro SUMPRODUCT
• SUM(A1:B2) e SUMPRODUCT(A1:A2,{1;10}) non vengono contrassegnati, perché l'intervallo e l'array vanno dritti in un argomento di funzione e nessun operatore li tocca
• A1*2 oppure SUM(A1,B1)*2 non vengono contrassegnati: riferimenti a cella singola e risultati di funzione sono scalari per questo controllo

Tre confini sono deliberati. Primo, la marcatura avviene solo quando una formula entra tramite l'API, cioè TXLSXCell.Formula nel motore XLSX e un assegnamento a cella singola di Formula o Value nel motore classico. Le formule caricate da un file vengono riscritte esattamente come sono state trovate, perché una formula legacy di un altro produttore può dipendere dalla implicit intersection di proposito. Secondo, il testo che non contiene né : né { viene saltato senza una seconda compilazione. Terzo, una formula che spillerebbe, come =A1:B1*2 da sola, viene contrassegnata come dynamic array a cella singola ancorata dove la metti. HotXLS non la spilla, e Excel estenderà il risultato alle celle vicine al prossimo ricalcolo

Questa regola degli operandi è la gemella della regola delle classi di argomento trattata in implicit intersection per i nomi definiti in HotXLS. Quell'articolo riguarda i parametri di funzione dichiarati di classe value; questo riguarda gli operatori, che nel modello legacy esigono sempre valori

Che cosa è cambiato nel motore di calcolo per far combaciare i risultati

La correzione di salvataggio nella v2.384.68 poggia sul fatto che il motore di formule di HotXLS restituiva già i valori di Excel 365, il che ha richiesto diverse correzioni precedenti in entrambi i motori. La più visibile fu SUMPRODUCT: fino alla v2.384.61 accettava solo due o più intervalli normali, quindi SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) e perfino la SUMPRODUCT(B1:B2) a argomento singolo restituivano #N/A. HotXLS ora valuta gli argomenti espressione elemento per elemento con le regole di Excel:

• ogni argomento deve avere esattamente la stessa forma, con uno scalare che conta come 1 × 1, o il risultato è #VALUE!
• un valore di errore dentro un qualsiasi argomento viene restituito come risultato
• gli elementi testo e logici contano 0, quindi serve ancora (B1:B2>0)*1 oppure -- per trasformare TRUE in 1
• gli argomenti che sono tutti intervalli normali conservano il loop di streaming originale, così i grandi intervalli non vengono materializzati come array

La famiglia SUM (SUM, COUNT, AVERAGE, MIN, MAX, COUNTA) usa lo stesso valutatore elemento per elemento quando un argomento è un'espressione di operatore su un intervallo, quindi =SUM((B1:B2>0)*1) conta entrambe le righe invece di guardare solo la prima cella. La v2.384.62 ha fatto restituire all'operatore di intersezione con spazio il rettangolo comune di due riferimenti, con #NULL! quando non si sovrappongono, così =SUM(A1:B2 B1:B2) è 6 anziché 2 e il risultato può alimentare parametri per riferimento come ROWS e INDEX. La v2.384.63 ha aggiunto al parser le costanti array inline come {1,2;3,4} (le virgole separano le colonne, i punti e virgola le righe) e le unioni di riferimenti come (A1:B2,D4). I confronti elemento per elemento danno anche a un elemento vuoto il tipo dell'altro lato, FALSE contro un logico, in linea con la regola scalare della v2.384.53 descritta in catene di confronto e celle vuote in HotXLS

var
  V: Variant;
begin
  // Book è il TXLSXWorkbook del primo esempio;
  // il suo foglio attivo contiene A1:B2 = 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10, argomento singolo
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6, intervallo comune B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16, sovrapposizione contata due volte
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1, era -1 prima della v2.384.61
end;

TXLSXWorkbook.Calculate valuta una stringa di formula sul foglio attivo senza salvarla, un modo rapido di verificare il comportamento del motore. Una cautela sull'@ in sé: HotXLS ha storicamente accettato @ tra due riferimenti come intersezione binaria, e ora valuta quella forma con vera semantica di intersezione. In Excel 365, @ è un prefisso unario di implicit intersection. Non scrivere @ nel testo della formula aspettandoti il significato di Excel; usa uno spazio per l'intersezione e lascia che le regole di salvataggio qui sopra gestiscano la semantica dynamic array

Perché Excel si rifiutava di aprire il file o calcolava il valore sbagliato?

Far accettare a Excel la marcatura dynamic array ha richiesto tre correzioni che nessun test di round trip con sé stesso avrebbe beccato, perché HotXLS leggeva correttamente il proprio output in ogni caso. Ognuna è stata trovata aprendo l'output di HotXLS in Excel 16 e sostituendo una variabile alla volta:

1. Il GUID dell'estensione deve essere tutto minuscolo. L'ext uri in xl/metadata.xml dev'essere esattamente {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. Un vecchio template di HotXLS lo scriveva con maiuscole e minuscole miste, ed Excel 16 si rifiutava di aprire l'intero pacchetto, non solo la cella. Le cartelle di lavoro create con TXLSXRange.SetDynamicArrayFormula prima della v2.384.68 avevano lo stesso problema
2. Il testo della radice array non porta l'= iniziale. Il writer XLSX emette il testo salvato di una radice array alla lettera dentro <f>. Se la cella convertita avesse conservato il suo =, l'elemento sarebbe risultato <f t="array" ref="E5">=SUM(...)</f>, che Excel respinge anche all'apertura. HotXLS lo toglie durante la conversione, ecco perché TXLSXCell.Formula si rilegge senza
3. Double(True) è -1 in Delphi. La conversione Variant segue la convenzione COM in cui TRUE è tutti bit a 1, e VarIsNumeric(True) restituisce True anch'esso. Prima della v2.384.61 questo faceva restituire -1 a =TRUE*1 e lasciava che gli elementi logici di un array venissero classificati come numeri, così un confronto come (B1:B2>0)=TRUE sbagliava. HotXLS ora testa varBoolean prima di trattare un Variant come numero nell'aritmetica scalare, nell'aritmetica array e nella classificazione degli elementi array, e TRUE conta 1

Classi degli operandi BIFF8: i dettagli a livello di byte per chi implementa formati

In BIFF8, ogni token operando porta la propria classe operando nel byte del token stesso, ed Excel si fida di quella classe più che della struttura della formula. [MS-XLS] definisce la classe come un campo PtgDataType a due bit nei bit 5 e 6 del token: 1 per reference, 2 per value, 3 per array. I cinque bit bassi nominano il token, quindi lo stesso riferimento ad area ha tre grafie:

Token	Classe riferimento	Classe valore	Classe array
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

HotXLS ne ha sbagliate tre in posti diversi, e ognuna ha prodotto un sintomo distinto in Excel rileggendo invece liscio in HotXLS:

• Costanti array di classe riferimento. L'encoder sceglieva la classe dal contesto, e i parametri di SUM o ROWS sono di classe riferimento, quindi =SUM({1,2}) veniva scritta con PtgArray come $20. Excel mostra l'intera formula come =#N/A. Una costante array non può mai essere un riferimento, quindi dalla v2.384.63 HotXLS scrive classe array $60 ovunque il contesto chieda un riferimento
• Operandi di classe valore di PtgIsect e PtgUnion. Gli operatori binari prendevano operandi di classe valore, giusto per * ma sbagliato per gli operatori di riferimento. Con aree $45 davanti a PtgIsect ($0F), Excel leggeva =SUM(A1:B2 B1:B2) come =SUM(@A1:B2 @B1:B2) e restituiva #VALUE!. Dalla v2.384.62 gli operandi di PtgIsect e PtgUnion ($10) vengono scritti in classe riferimento, $25
• Operandi di classe valore dentro il record ARRAY. Excel applica la implicit intersection perfino dentro una formula array quando un operando è di classe valore. HotXLS scriveva $45 lì, così la formula array a cella singola per =SUM(A1:B1*{10,100}) valutava 10 in Excel. Dalla v2.384.68, il token stream di un record ARRAY promuove ogni riferimento di classe valore e ogni costante array alla classe array, $65 e $60, che è ciò che scrive Excel

Un reader che ignora i bit di classe fa il round trip di tutti e tre felici e contento, quindi se mantieni un writer BIFF8 tuo, confronta i bit di classe di ogni token operando con un file salvato da Excel della stessa formula, non solo i numeri di token

Riferimento rapido

• Excel 365 mostra @ quando un operatore in una formula normale non contrassegnata riceve un intervallo multicella o un array inline
• HotXLS dalla v2.384.68 in poi salva queste formule come dynamic array XLSX a cella singola (cm="1", t="array", metadati XLDAPR) e come formule array XLS a cella singola (FORMULA con PtgExp più ARRAY $0221)
• Contano solo gli operandi di operatore; un intervallo passato dritto a un argomento di funzione resta una formula normale
• Vengono contrassegnate solo le formule immesse tramite TXLSXCell.Formula o la Formula / Value classica a cella singola; le formule caricate restano intoccate
• La cella radice convertita si rilegge senza l'= iniziale
• Il GUID ext uri della dynamic array dev'essere minuscolo o Excel respinge il pacchetto
• In Delphi, Double(True) è -1; testa varBoolean prima della conversione numerica
• BIFF8: le costanti array mai in classe riferimento, operandi di PtgIsect / PtgUnion in classe riferimento, operandi del record ARRAY in classe array

HotXLS legge, scrive e calcola cartelle di lavoro XLS e XLSX nativamente da Delphi e C++Builder, e salva le formule con operatori array così che Excel 365 le apra con gli stessi valori calcolati da HotXLS. Vedi il componente foglio di calcolo Delphi HotXLS per edizioni, documentazione e download di prova