Formule cu array în HotXLS: de ce adaugă Excel @ și #VALUE!

Excel 365 inserează @ într-o formulă precum =SUM(A1:B1*{10,100}) și afișează #VALUE! când fișierul o salvează ca formulă obișnuită, pentru că Excel aplică atunci intersectarea implicită legacy fiecărui operand de operator. Din v2.384.68, HotXLS Delphi Component salvează aceste formule cu operatori de array exact cum face Excel 365: ca formule dynamic-array pe celulă unică în XLSX și ca formule array pe o singură celulă în XLS

Simptomul supraviețuiește code review-ului. Serviciul vostru Delphi scrie un workbook, HotXLS îl recalculează și pune în cache 210 pentru =SUM(A1:B1*{10,100}), iar clientul îl deschide în Excel 16 și descoperă =SUM(@A1:B1*@{10,100}) în formula bar și #VALUE! în celulă. Nimic din fișier nu e malformat. Ce lipsește e metadatul care îi spune Excel-ului că formula a fost scrisă sub regulile de dynamic array, iar fără el Excel cade înapoi pe modelul său de evaluare de dinainte de dynamic array

De ce adaugă Excel 365 @ într-o formulă pe care HotXLS a calculat-o corect?

Excel 365 adaugă @ pentru că o formulă fără marcaj de dynamic array este, prin definiție, o formulă legacy, iar formulele legacy reduc un interval de mai multe celule la o singură celulă ori de câte ori un operator așteaptă o valoare unică. Reducerea aceea e intersectarea implicită: Excel ia celula intervalului care împarte rândul formulei (pentru un interval vertical) sau coloana (pentru unul orizontal), iar dacă o asemenea celulă nu există rezultatul e #VALUE!. Excel 365 păstrează acest sens pentru formulele în stil vechi și afișează @ ca să facă reducerea vizibilă

Puneți =SUM(A1:B1*{10,100}) în E5 și citirea legacy devine evidentă. A1:B1 e un interval orizontal, formula stă în coloana E, intervalul nu are nicio celulă în coloana E, deci @A1:B1 e #VALUE! și tot SUM-ul moștenește asta. Sub regulile de dynamic array, același text înmulțește element cu element, 1 × 10 + 2 × 100, și întoarce 210. Motorul de formule HotXLS evaluează în felul dynamic array încă de la versiunile v2.384.61 și v2.384.63; pur și simplu formatul de fișier nu spunea asta. Cu A1:B2 conținând 1, 2, 3 și 4, acestea sunt formulele de sondă și ce afișează Excel 16:

Formula	Rezultat HotXLS	Excel 16, salvată ca formulă simplă	Salvare din v2.384.68
=SUM(A1:B1*{10,100})	210	#VALUE!	Dynamic array, Excel afișează 210
=SUM((A1:B2>2)*1)	2	Intersectare implicită, greșit sau eroare	Dynamic array, Excel afișează 2
=SUMPRODUCT((A1:B2>2)*1)	2	Intersectare implicită, greșit sau eroare	Dynamic array, Excel afișează 2
=MAX(A1:B2-1)	3	Intersectare implicită, greșit sau eroare	Dynamic array, Excel afișează 3
=SUM(A1:B2)	10	10	Formulă simplă, neschimbată

Ultimul rând contează la fel de mult ca primele patru. SUM(A1:B2) trece un interval direct către un parametru de funcție care acceptă referințe, deci niciun operator nu vede vreodată un interval de mai multe celule și nicio intersectare nu poate avea loc. Excel 365 însuși salvează formula aceea ca formulă simplă, iar HotXLS procedează la fel

Cum salvează HotXLS formulele cu operatori de array în XLSX și XLS

HotXLS scrie o formulă cu operatori de array în XLSX ca dynamic array pe celulă unică: elementul <c> poartă cm="1", formula e <f t="array" ref="E5">, iar pachetul câștigă xl/metadata.xml cu un tip de metadate XLDAPR a cărui extensie conține dynamicArrayProperties fDynamic="1". Atributul cm e un index cu baza unu în blocul cellMetadata al acelei părți, iar înregistrarea XLDAPR din spatele lui e cea care îi spune Excel-ului „evaluează asta sub regulile de dynamic array”. E exact structura pe care o scrie Excel 16 când tastați aceeași formulă și salvați, iar așa s-a stabilit de la început layout-ul țintă

În XLS nu există parte de metadate, deci HotXLS folosește singura construcție pe care BIFF8 o are pentru evaluarea de array: o formulă array pe o singură celulă. Celula primește o înregistrare FORMULA al cărei token stream e un singur PtgExp care arată spre ea însăși, urmat de o înregistrare ARRAY ($0221) care poartă formula parsată reală peste intervalul de o celulă. Excel 365 scrie formulele dynamic array în XLS exact la fel, iar o versiune mai veche de Excel care citește fișierul vede o formulă array clasică de tip Ctrl+Shift+Enter

Niciun API nou nu e implicat. Marcajul apare când atribui formula prin API-ul obișnuit de celulă, în ambele motoare. Pe partea de XLSX e vorba de 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;

    // Operator peste un interval sau array inline: salvat ca dynamic array
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // Interval trecut direct către o funcție: rămâne un <f> obișnuit
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

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

    // Rădăcina array păstrează textul fără '='-ul de la început
    Writeln(Sheet.Cells[5, 5].Formula);              // SUM(A1:B1*{10,100})

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

După conversie, TXLSXCell.Formula întoarce textul fără =, aceeași formă pe care o salvează TXLSXRange.SetDynamicArrayFormula, deci codul care compară șirurile de formule după atribuire ar trebui să normalizeze =-ul de la început

Motorul clasic urmează aceeași regulă prin IXLSRange.Formula pe o celulă unică. Atribuirea formulei o redirecționează intern pe calea de array pe o celulă, deci XLS-ul salvat conține perechea FORMULA plus 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})';  // înregistrare ARRAY
  Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)';         // înregistrare ARRAY
  Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)';           // FORMULA simplă

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

Dacă ancorați un rezultat de mai multe celule, nu o agregare scalară, API-urile explicite rămân unealta potrivită: SetArrayFormula pentru un dreptunghi dimensionat în prealabil, descris în formulele spill de dynamic array cu HotXLS, sau TXLSXRange.SetDynamicArrayFormula când vreți marcajul de dynamic array XLSX pe un interval pe care îl dimensionați voi. Calea automată din articolul acesta acoperă doar formulele tastate într-o singură celulă

Ce formule marchează HotXLS ca dynamic array?

HotXLS marchează o formulă doar când un operator are un subarbore de operand care produce un array. Verificarea rulează pe arborele de sintaxă compilat, iar un operand produce un array dacă e un interval de mai multe celule, o constantă de array inline sau o altă expresie de operator care are la rândul ei un asemenea operand. Parantezele sunt transparente. Operatorii care contează sunt cei aritmetici (+ - * / ^), concatenarea (&), cele șase comparații, plus-ul și minus-ul unari și procentul:

• A1:B1*{10,100}, (A1:B2>2)*1, --(B1:B2>0) și A1:B2-1 sunt marcate, oriunde apar în formulă, inclusiv în interiorul lui SUMPRODUCT
• SUM(A1:B2) și SUMPRODUCT(A1:A2,{1;10}) nu sunt marcate, pentru că intervalul și array-ul intră direct ca argument de funcție și niciun operator nu le atinge
• A1*2 sau SUM(A1,B1)*2 nu sunt marcate: referințele pe celulă unică și rezultatele de funcții sunt scalari pentru această verificare

Sunt trei limite trase în mod intenționat. În primul rând, marcajul apare doar când formula e introdusă prin API, adică TXLSXCell.Formula în motorul XLSX și o atribuire pe celulă unică Formula sau Value în motorul clasic. Formulele încărcate din fișier sunt rescrise exact așa cum au fost găsite, pentru că o formulă legacy de la alt producător se poate baza în mod intenționat pe intersectarea implicită. În al doilea rând, textul care nu conține nici : nici { e sărit fără o a doua compilare. În al treilea rând, o formulă care ar putea face spill, precum =A1:B1*2 de una singură, e marcată ca dynamic array pe celulă unică, ancorat acolo unde l-ați pus. HotXLS nu face spill, iar Excel va extinde rezultatul către celulele vecine la următoarea recalcular

Regula asta a operanzilor e sora regulii despre clasa de argumente acoperită în intersectarea implicită pentru defined names în HotXLS. Articolul acela e despre parametrii de funcție declarați ca value class; acesta e despre operatori, care în modelul legacy cer întotdeauna valori

Ce s-a schimbat în motorul de calcul ca rezultatele să se potrivească

Repararea stocării din v2.384.68 se sprijină pe faptul că motorul de formule HotXLS întorcea deja valorile Excel 365, lucru care a cerut mai multe reparații anterioare în ambele motoare. Cea mai vizibilă a fost SUMPRODUCT: până la v2.384.61 accepta doar două sau mai multe intervale simple, deci SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) și chiar SUMPRODUCT(B1:B2) cu un singur argument întorceau #N/A. HotXLS evaluează acum argumentele-expresie element cu element, cu regulile Excel-ului:

• fiecare argument trebuie să aibă exact aceeași formă, un scalar numărând ca 1 × 1, altfel rezultatul e #VALUE!
• o valoare de eroare din interiorul oricărui argument e întoarsă ca rezultat
• elementele text și logice contează 0, deci tot (B1:B2>0)*1 sau -- e nevoie ca să transformi TRUE în 1
• argumentele care sunt toate intervale simple păstrează bucla streaming originală, deci intervalele mari nu sunt materializate ca array-uri

Familia SUM (SUM, COUNT, AVERAGE, MIN, MAX, COUNTA) folosește același evaluator element cu element când un argument e o expresie de operator peste un interval, deci =SUM((B1:B2>0)*1) numără ambele rânduri în loc să se uite doar la prima celulă. v2.384.62 a făcut ca operatorul de intersecție prin spațiu să întoarcă dreptunghiul comun al două referințe, cu #NULL! când ele nu se suprapun, deci =SUM(A1:B2 B1:B2) e 6, nu 2, iar rezultatul poate hrăni parametri de referință precum ROWS și INDEX. v2.384.63 a adăugat parser-ului constante de array inline precum {1,2;3,4} (virgulele separă coloanele, punct și virgulă separă rândurile) și uniuni de referințe precum (A1:B2,D4). Comparațiile element cu element dau și unui element gol tipul celeilalte părți, FALSE față de un logic, în linie cu regula scalară din v2.384.53 descrisă în lanțurile de comparație și celulele goale în HotXLS

var
  V: Variant;
begin
  // Book e TXLSXWorkbook-ul din primul exemplu;
  // sheet-ul lui activ conține A1:B2 = 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10, argument unic
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6, intervalul comun B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16, suprapunerea numărată de două ori
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1, era -1 înainte de v2.384.61
end;

TXLSXWorkbook.Calculate evaluează un șir de formulă peste sheet-ul activ fără să-l salveze, o cale rapidă de a verifica comportamentul motorului. O atenționare despre @ în sine: HotXLS a acceptat istoric @ între două referințe ca intersecție binară și acum evaluează forma aceea cu semantică de intersecție reală. În Excel 365, @ e un prefix unar de intersectare implicită. Nu scrieți @ în textul formulei și nu vă așteptați la sensul Excel-ului; folosiți un spațiu pentru intersecție și lăsați regulile de stocare de mai sus să gestioneze semantica de dynamic array

De ce a refuzat Excel să deschidă fișierul sau a calculat o valoare greșită?

Să-l convingi pe Excel să accepte marcajul de dynamic array a cerut trei reparații pe care niciun test de round-trip cu propria producție nu le-ar fi prins, fiindcă HotXLS își citea corect output-ul în toate cazurile. Fiecare a fost găsită deschizând output-ul HotXLS în Excel 16 și schimbând câte o variabilă pe rând:

1. GUID-ul extensiei trebuie să fie integral cu litere mici. ext uri din xl/metadata.xml trebuie să fie exact {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. Un template HotXLS mai vechi îl scria cu majuscule și minuscule amestecate, iar Excel 16 refuza să deschidă întregul pachet, nu doar celula. Workbook-urile create cu TXLSXRange.SetDynamicArrayFormula înainte de v2.384.68 aveau aceeași problemă
2. Textul rădăcinii de array nu poartă = la început. Writer-ul XLSX emite textul stocat al unei rădăcini de array verbatim în <f>. Dacă celula convertită și-ar fi păstrat =-ul, elementul ar fi citit <f t="array" ref="E5">=SUM(...)</f>, pe care Excel îl respinge și el la deschidere. HotXLS îl îndepărtează în timpul conversiei, de aceea TXLSXCell.Formula se citește înapoi fără el
3. Double(True) e -1 în Delphi. Conversia Variant urmează convenția COM în care TRUE e toți biții setați, iar VarIsNumeric(True) întoarce și el True. Înainte de v2.384.61 asta făcea ca =TRUE*1 să întoarcă -1 și lăsa elementele logice de array să fie clasificate ca numere, deci o comparație precum (B1:B2>0)=TRUE ieșea greșit. HotXLS testează acum varBoolean înainte să trateze un Variant ca număr în aritmetica scalară, aritmetica de array și clasificarea elementelor de array, iar TRUE contează 1

Clasele de operanzi BIFF8: detaliile la nivel de byte pentru implementatorii de formate

În BIFF8, fiecare token de operand își poartă clasa de operand chiar în byte-ul de token, iar Excel are mai multă încredere în clasa aceea decât în structura formulei. [MS-XLS] definește clasa ca un câmp PtgDataType pe doi biți în biții 5 și 6 ai token-ului: 1 pentru referință, 2 pentru valoare, 3 pentru array. Ceilalți cinci biți de jos numesc token-ul, deci aceeași referință de arie are trei scrieri:

Token	Clasă referință	Clasă valoare	Clasă array
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

HotXLS a greșit trei dintre acestea în locuri diferite, iar fiecare a produs un simptom distinct în Excel, în timp ce în HotXLS se recitea bine:

• Constante de array cu clasă de referință. Encoder-ul alegea clasa din context, iar parametrii SUM sau ROWS sunt de clasă referință, deci =SUM({1,2}) era scris cu PtgArray ca $20. Excel afișează întreaga formulă ca =#N/A. O constantă de array nu poate fi niciodată o referință, deci din v2.384.63 HotXLS scrie clasa array $60 ori de câte ori contextul cere o referință
• Operanzi de clasă valoare la PtgIsect și PtgUnion. Operatorii binari primeau operanzi de clasă valoare, ceea ce e corect pentru * dar greșit pentru operatorii de referință. Cu arii $45 înaintea lui PtgIsect ($0F), Excel citea =SUM(A1:B2 B1:B2) ca =SUM(@A1:B2 @B1:B2) și întorcea #VALUE!. Din v2.384.62, operanzii lui PtgIsect și PtgUnion ($10) sunt scriși în clasa referință, $25
• Operanzi de clasă valoare în interiorul înregistrării ARRAY. Excel aplică intersectarea implicită chiar și în interiorul unei formule array când un operand e de clasă valoare. HotXLS scria $45 acolo, deci formula array pe o celulă pentru =SUM(A1:B1*{10,100}) evalua la 10 în Excel. Din v2.384.68, token stream-ul unei înregistrări ARRAY promovează fiecare referință de clasă valoare și fiecare constantă de array la clasa array, $65 și $60, exact ce scrie Excel

Un reader care ignoră biții de clasă face round-trip pe toate trei fără probleme, deci dacă întrețineți propriul vostru writer BIFF8, comparați biții de clasă ai fiecărui token de operand cu un fișier salvat din Excel pentru aceeași formulă, nu doar cu numerele de token

Referință rapidă

• Excel 365 afișează @ când un operator dintr-o formulă simplă, nemarcată, primește un interval de mai multe celule sau un array inline
• HotXLS v2.384.68 și ulterior salvează astfel de formule ca dynamic array-uri XLSX pe celulă unică (cm="1", t="array", metadate XLDAPR) și ca formule array XLS pe o celulă (FORMULA cu PtgExp plus ARRAY $0221)
• Doar operanzii de operator contează; un interval trecut direct ca argument de funcție rămâne formulă simplă
• Doar formulele introduse prin TXLSXCell.Formula sau prin Formula / Value clasic pe celulă unică sunt marcate; formulele încărcate rămân neatinse
• Celula rădăcină convertită se citește înapoi fără =-ul de la început
• GUID-ul ext uri de dynamic array trebuie să fie cu litere mici, altfel Excel respinge pachetul
• În Delphi, Double(True) e -1; testați varBoolean înainte de conversia numerică
• BIFF8: constantele de array niciodată clasă referință, operanzii PtgIsect / PtgUnion în clasa referință, operanzii înregistrării ARRAY în clasa array

HotXLS citește, scrie și calculează workbook-uri XLS și XLSX nativ din Delphi și C++Builder, și salvează formulele cu operatori de array astfel încât Excel 365 să le deschidă cu aceleași valori pe care le-a calculat HotXLS. Vezi componenta Delphi spreadsheet HotXLS pentru ediții, documentație și o descărcare de probă