Articolo tecnico

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. 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. Un servizio Delphi scrive una cartella di lavoro, il suo motore di formule mette in cache 210 per =SUM(A1:B1*{10,100}) in una cella di formula ordinaria, 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 calcolata 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

Mettete =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, e anche il formato file deve dirlo. Con A1:B2 che contiene 1, 2, 3 e 4, queste sono le formule sonda e ciò che Excel 16 mostra:

Diagramma HotXLS che confronta implicit intersection e valutazione dynamic array di SUM(A1:B1*{10,100}) nella cella E5: il modello legacy non trova nessuna cella dell'intervallo orizzontale A1:B1 in colonna E e restituisce #VALUE!, mentre il modello dynamic array moltiplica 1 per 10 e 2 per 100 e restituisce 210
Excel inserisce @ nella formula normale e mostra #VALUE!, perché la implicit intersection non trova nulla in colonna E; con la marcatura dynamic array di HotXLS la stessa formula moltiplica elemento per elemento e atterra su 210
FormulaRisultato HotXLSExcel 16, salvata come formula normaleSalvata da HotXLS
=SUM(A1:B1*{10,100})210#VALUE!Dynamic array, Excel mostra 210
=SUM((A1:B2>2)*1)2Implicit intersection, sbagliato o erroreDynamic array, Excel mostra 2
=SUMPRODUCT((A1:B2>2)*1)2Implicit intersection, sbagliato o erroreDynamic array, Excel mostra 2
=MAX(A1:B2-1)3Implicit intersection, sbagliato o erroreDynamic array, Excel mostra 3
=SUM(A1:B2)1010Formula 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

Diagramma di salvataggio HotXLS per la formula con operatore array SUM(A1:B1*{10,100}): il motore XLSX scrive un dynamic array a cella singola con cm uguale a 1, un elemento f di tipo array e un record XLDAPR in xl/metadata.xml il cui GUID minuscolo è obbligatorio, mentre il motore XLS scrive un record FORMULA con PtgExp più un record ARRAY 0221
Il motore XLSX contrassegna la cella con cm=1 più un record di metadati XLDAPR e il motore classico accoppia una FORMULA PtgExp con un record ARRAY su una cella; Excel 365 salva le dynamic array in XLS allo stesso modo

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:

uses
  SysUtils, Variants, lxCalc, lxHandle, lxHandleX;   // lxOk è dichiarato in lxCalc

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

Come il motore di calcolo fa combaciare i valori di Excel 365

La marcatura di salvataggio ripaga solo se il motore di formule restituisce innanzitutto i valori di Excel 365, in entrambi i motori. SUMPRODUCT è il caso più visibile: un'implementazione che accetta solo due o più intervalli normali restituisce #N/A per SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) e perfino la SUMPRODUCT(B1:B2) a argomento singolo. HotXLS 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. L'operatore di intersezione con spazio restituisce 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. Il parser accetta costanti array inline come {1,2;3,4} (le virgole separano le colonne, i punti e virgola le righe) e 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 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, TRUE conta come 1
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 accetta @ tra due riferimenti come intersezione binaria e valuta quella forma con vera semantica di intersezione. In Excel 365, @ è un prefisso unario di implicit intersection. Non scrivete @ nel testo della formula aspettandovi il significato di Excel; usate uno spazio per l'intersezione e lasciate che le regole di salvataggio qui sopra gestiscano la semantica dynamic array

Tre dettagli che Excel controlla quando apre output dynamic array

Far accettare a Excel la marcatura dynamic array si riduce a tre dettagli che nessun test di round trip con sé stesso becca, perché una libreria rilegge correttamente il proprio output in ogni caso. Ognuno emerge solo quando aprite l'output in Excel 16 e sostituite 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}. Scrivetelo con maiuscole e minuscole miste ed Excel 16 si rifiuta di aprire l'intero pacchetto, non solo la cella
  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. Una conversione ingenua quindi fa restituire -1 a =TRUE*1 e lascia che gli elementi logici di un array vengano classificati come numeri, così un confronto come (B1:B2>0)=TRUE sbaglia. HotXLS 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:

TokenClasse riferimentoClasse valoreClasse array
PtgRef$24$44$64
PtgArea$25$45$65
PtgArray$20$40$60

Tre punti di un writer BIFF8 sono facili da sbagliare, e ognuno produce un sintomo distinto in Excel rileggendo invece liscio nel reader dello stesso writer:

  • Costanti array di classe riferimento. Un encoder che sceglie la classe dal contesto vede che i parametri di SUM o ROWS sono di classe riferimento e scrive =SUM({1,2}) con PtgArray come $20. Excel mostra l'intera formula come =#N/A. Una costante array non può mai essere un riferimento, quindi HotXLS scrive classe array $60 ovunque il contesto chieda un riferimento
  • Operandi di classe valore di PtgIsect e PtgUnion. Dare agli operatori binari operandi di classe valore è giusto per * ma sbagliato per gli operatori di riferimento. Con aree $45 davanti a PtgIsect ($0F), Excel legge =SUM(A1:B2 B1:B2) come =SUM(@A1:B2 @B1:B2) e restituisce #VALUE!. HotXLS scrive gli operandi di PtgIsect e PtgUnion ($10) 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. Con $45 lì, la formula array a cella singola per =SUM(A1:B1*{10,100}) valuta 10 in Excel. HotXLS quindi promuove, nel token stream di un record ARRAY, ogni riferimento di classe valore e ogni costante array alla classe array, $65 e $60, che è ciò che scrive Excel
Diagramma BIFF8 di HotXLS: i bit 5 e 6 di ogni byte di token scelgono la classe riferimento, valore o array, così PtgArea si scrive 25, 45 e 65, con tre difetti fissi: costanti array come 20 mostravano #N/A, operandi di PtgIsect come 45 restituivano #VALUE!, e operandi del record ARRAY come 45 facevano restituire 10 a SUM(A1:B1*{10,100})
Ogni token operando BIFF8 porta la propria classe nei bit 5 e 6, ed Excel si fida di quei bit più che della struttura; HotXLS scrive le costanti array come 60, gli operandi di PtgIsect come 25, e promuove i token del record ARRAY alla classe array

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 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