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:
| Formula | Risultato HotXLS | Excel 16, salvata come formula normale | Salvata da HotXLS |
|---|---|---|---|
=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:
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)eA1:B2-1vengono contrassegnati, ovunque compaiano nella formula, incluso dentro SUMPRODUCTSUM(A1:B2)eSUMPRODUCT(A1:A2,{1;10})non vengono contrassegnati, perché l'intervallo e l'array vanno dritti in un argomento di funzione e nessun operatore li toccaA1*2oppureSUM(A1,B1)*2non 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)*1oppure--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:
- Il GUID dell'estensione deve essere tutto minuscolo. L'
ext uriinxl/metadata.xmldev'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 - 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.Formulasi rilegge senza Double(True)è -1 in Delphi. La conversione Variant segue la convenzione COM in cui TRUE è tutti bit a 1, eVarIsNumeric(True)restituisce True anch'esso. Una conversione ingenua quindi fa restituire -1 a=TRUE*1e lascia che gli elementi logici di un array vengano classificati come numeri, così un confronto come(B1:B2>0)=TRUEsbaglia. HotXLS testavarBooleanprima 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 |
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})conPtgArraycome$20. Excel mostra l'intera formula come=#N/A. Una costante array non può mai essere un riferimento, quindi HotXLS scrive classe array$60ovunque il contesto chieda un riferimento - Operandi di classe valore di
PtgIsectePtgUnion. Dare agli operatori binari operandi di classe valore è giusto per*ma sbagliato per gli operatori di riferimento. Con aree$45davanti aPtgIsect($0F), Excel legge=SUM(A1:B2 B1:B2)come=SUM(@A1:B2 @B1:B2)e restituisce#VALUE!. HotXLS scrive gli operandi diPtgIsectePtgUnion($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
$45lì, 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,$65e$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 salva queste formule come dynamic array XLSX a cella singola (
cm="1",t="array", metadatiXLDAPR) e come formule array XLS a cella singola (FORMULA conPtgExppiù 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.Formulao laFormula/Valueclassica a cella singola; le formule caricate restano intoccate - La cella radice convertita si rilegge senza l'
=iniziale - Il GUID
ext uridella dynamic array dev'essere minuscolo o Excel respinge il pacchetto - In Delphi,
Double(True)è -1; testavarBooleanprima della conversione numerica - BIFF8: le costanti array mai in classe riferimento, operandi di
PtgIsect/PtgUnionin 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