HotXLS array formule: zašto Excel dodaje @ i #VALUE!

Excel 365 umeće @ u formulu poput =SUM(A1:B1*{10,100}) i prikazuje #VALUE! kada fajl čuva formulu kao običnu, jer Excel tada na svaki operand operatora primenjuje nasleđeni implicitni presek. Od v2.384.68 HotXLS Delphi Component čuva ove formule s operatorskim nizovima na način kako to radi Excel 365: kao formule dinamičkog niza jedne ćelije u XLSX i kao array formule jedne ćelije u XLS

Simptom preživi i code review. Vaš Delphi servis upiše radnu svesku, HotXLS je preračuna i kešira 210 za =SUM(A1:B1*{10,100}), a kupac je otvori u Excelu 16 i u traci formula zatekne =SUM(@A1:B1*@{10,100}) i #VALUE! u ćeliji. Ništa u fajlu nije deformisano. Ono što nedostaje jeste metapodatak koji Excelu govori da je formula zapisana po pravilima dinamičkog niza, i bez njega Excel se vraća na model evaluacije iz doba pre dinamičkih nizova

Zašto Excel 365 dodaje @ formuli koju je HotXLS ispravno izračunao?

Excel 365 dodaje @ jer je formula bez oznake dinamičkog niza, po definiciji, formula starog tipa (legacy), a formule starog tipa svode višećelijski opseg na jednu ćeliju svuda gde operator očekuje jednu vrednost. To svođenje je implicitni presek: Excel uzima onu ćeliju opsega koja je u istom redu kao formula (kod vertikalnog opsega) odnosno koloni (kod horizontalnog), a ako takve ćelije nema, rezultat je #VALUE!. Excel 365 to značenje zadržava za formule starog stila i prikazuje @ da bi svođenje bilo vidljivo

Stavite =SUM(A1:B1*{10,100}) u E5 i nasleđeno čitanje postane očigledno. A1:B1 je horizontalni opseg, formula sedi u koloni E, opseg nema ćeliju u koloni E, pa je @A1:B1 zapravo #VALUE! i ceo SUM to nasleđuje. Po pravilima dinamičkog niza isti tekst množi element po element, 1 × 10 + 2 × 100, i vraća 210. HotXLS formula engine računa na dinamički način još od izdanja v2.384.61 i v2.384.63; format fajla jednostavno to nije govorio. Sa A1:B2 u kojem stoje 1, 2, 3 i 4, ovo su probne formule i ono što Excel 16 prikazuje:

Formula	HotXLS rezultat	Excel 16, čuvano kao obična formula	Čuvano od v2.384.68
=SUM(A1:B1*{10,100})	210	#VALUE!	Dinamički niz, Excel pokazuje 210
=SUM((A1:B2>2)*1)	2	Implicitni presek, pogrešno ili greška	Dinamički niz, Excel pokazuje 2
=SUMPRODUCT((A1:B2>2)*1)	2	Implicitni presek, pogrešno ili greška	Dinamički niz, Excel pokazuje 2
=MAX(A1:B2-1)	3	Implicitni presek, pogrešno ili greška	Dinamički niz, Excel pokazuje 3
=SUM(A1:B2)	10	10	Obična formula, nepromenjeno

Poslednji red znači koliko i prva četiri. SUM(A1:B2) predaje opseg direktno parametru funkcije koji prima reference, pa nijedan operator nikada ne vidi višećelijski opseg i nijedan presek ne može da se dogodi. Sam Excel 365 tu formulu čuva kao običnu, i HotXLS radi isto

Kako HotXLS čuva formule s operatorskim nizovima u XLSX i XLS

HotXLS zapisuje formulu s operatorskim nizom u XLSX kao dinamički niz jedne ćelije: element <c> nosi cm="1", formula je <f t="array" ref="E5">, a paket dobija xl/metadata.xml s metapodatkom tipa XLDAPR čija ekstenzija nosi dynamicArrayProperties fDynamic="1". Atribut cm jeste indeks počevši od 1 u blok cellMetadata tog dela, a XLDAPR zapis iza njega je ono što Excelu govori „izračunaj ovo po pravilima dinamičkog niza”. To je ista struktura koju Excel 16 zapisuje kada ukuca istu formulu i sačuva, i baš tako je ustanovljen ciljni raspored

U XLS nema dela s metapodacima, pa HotXLS koristi jedinu konstrukciju koju BIFF8 ima za evaluaciju niza: array formulu jedne ćelije. Ćelija dobija FORMULA zapis čiji je tok tokena jedan PtgExp koji pokazuje na samu sebe, a za njim ARRAY zapis ($0221) koji nosi pravu isparsiranu formulu nad opsegom od jedne ćelije. Excel 365 dinamičko-nizovske formule u XLS piše na isti način, a starija verzija Excela koja čita fajl vidi klasičnu Ctrl+Shift+Enter array formulu

Novi API nije u pitanju. Oznaka se stavlja kada formulu dodelite kroz uobičajeni cell API, u oba engine-a. Na XLSX strani to je 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 nad opsegom ili inline nizom: čuva se kao dinamički niz
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // Opseg predat pravo funkciji: ostaje običan <f>
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

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

    // Koren niza zadržava svoj tekst bez vodećeg '='
    Writeln(Sheet.Cells[5, 5].Formula);              // SUM(A1:B1*{10,100})

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

Nakon konverzije, TXLSXCell.Formula vraća tekst bez =, u istom obliku u kojem ga čuva i TXLSXRange.SetDynamicArrayFormula, pa kod koji posle dodele poredi formule kao stringove treba da normalizuje vodeći =

Klasični engine prati isto pravilo kroz IXLSRange.Formula na jednoj ćeliji. Dodela formule interno je preusmeri na put jednoćelijske array formule, pa sačuvani XLS sadrži par 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})';  // ARRAY zapis
  Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)';         // ARRAY zapis
  Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)';           // običan FORMULA

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

Ako sidrite rezultat na više ćelija, a ne skalarnu agregaciju, eksplicitni API-ji su i dalje pravi alat: SetArrayFormula za unapred odmeren pravougaonik, kako je opisano u tekstu o dynamic array spill formulama s HotXLS-om, ili TXLSXRange.SetDynamicArrayFormula kada želite XLSX oznaku dinamičkog niza na opsegu koji sami odmerite. Automatski put iz ovog teksta pokriva samo formule ukucone u jednu ćeliju

Koje formule HotXLS označava kao dinamičke nizove?

HotXLS označava formulu samo kada neki operator ima podstablo operanda koje proizvodi niz. Provera ide po kompajliranom sintaksnom stablu, a operand proizvodi niz ako je višećelijski opseg, inline array konstanta ili drugi operatorski izraz koji sam ima takav operand. Zagrade su transparentne. Operatori koji se računaju jesu aritmetički (+ - * / ^), konkatenacija (&), šest poređenja, unarni plus i minus i procenat:

• A1:B1*{10,100}, (A1:B2>2)*1, --(B1:B2>0) i A1:B2-1 se označavaju, gde god se pojave u formuli, uključujući i unutar SUMPRODUCT
• SUM(A1:B2) i SUMPRODUCT(A1:A2,{1;10}) se ne označavaju, jer opseg i niz idu direktno u argument funkcije i nijedan operator ih ne dira
• A1*2 i SUM(A1,B1)*2 se ne označavaju: reference jedne ćelije i rezultati funkcija su za ovu proveru skalari

Tri granice su namerne. Prvo, oznaka se stavlja samo kada se formula unese kroz API, to jest TXLSXCell.Formula u XLSX engine-u i dodela Formula ili Value na jednoj ćeliji u klasičnom engine-u. Formule učitane iz fajla vraćaju se zapisane tačno onako kako su nađene, jer formula starog tipa od drugog proizvođača može namerno da zavisi od implicitnog preseka. Drugo, tekst koji ne sadrži ni : ni { preskače se bez drugog kompajliranja. Treće, formula koja bi se razlila, poput same =A1:B1*2, označava se kao dinamički niz jedne ćelije usidren gde je stavite. HotXLS je ne razliva, a Excel će rezultat proširiti na susedne ćelije pri sledećem preračunavanju

Ovo pravilo o operandima je srodnik pravila o klasi argumenata obrađenog u tekstu o implicitnom preseku za defined names u HotXLS-u. Taj tekst je o parametrima funkcija deklarisanim kao value class; ovaj je o operatorima, koji u nasleđenom modelu uvek traže vrednosti

Šta se promenilo u računskom engine-u da bi se rezultati poklopili

Popravka skladištenja u v2.384.68 oslanja se na to da HotXLS formula engine već vraća vrednosti Excela 365, što je zahtevalo nekoliko ranijih popravki u oba engine-a. Najvidljivija je bila SUMPRODUCT: do v2.384.61 primala je samo dva ili više običnih opsega, pa su SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) pa čak i SUMPRODUCT(B1:B2) s jednim argumentom vraćale #N/A. HotXLS sada izrazne argumente računa element po element po Excelovim pravilima:

• svaki argument mora imati potpuno isti oblik, skalarni se računa kao 1 × 1, ili je rezultat #VALUE!
• greška unutar bilo kog argumenta vraća se kao rezultat
• tekstualni i logički elementi računaju se kao 0, pa je i dalje potreban (B1:B2>0)*1 ili -- da TRUE postane 1
• argumenti koji su svi obični opsezi zadržavaju prvobitnu streaming petlju, pa se veliki opsezi ne materijalizuju kao nizovi

SUM porodica (SUM, COUNT, AVERAGE, MIN, MAX, COUNTA) koristi isti evaluator po elementima kada je argument operatorski izraz nad opsegom, pa =SUM((B1:B2>0)*1) broji oba reda umesto da gleda samo prvu ćeliju. v2.384.62 je naterala operator preseka razmakom da vraća zajednički pravougaonik dve reference, uz #NULL! kada se ne preklapaju, pa je =SUM(A1:B2 B1:B2) 6 umesto 2 i rezultat može da hrani referentne parametre poput ROWS i INDEX. v2.384.63 je dodala parseru inline array konstante poput {1,2;3,4} (zapete dele kolone, tačka-zapete redove) i unije referenci poput (A1:B2,D4). Poređenja po elementima takođe praznom elementu daju tip druge strane, FALSE naspram logičke vrednosti, u skladu sa skalarnim pravilom iz v2.384.53 opisanim u tekstu o lancima poređenja i praznim ćelijama u HotXLS-u

var
  V: Variant;
begin
  // Book je TXLSXWorkbook iz prvog primera;
  // njegov aktivni list drži A1:B2 = 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10, jedan argument
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6, zajednički opseg B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16, preklapanje računato dva puta
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1, pre v2.384.61 bilo je -1
end;

TXLSXWorkbook.Calculate izračunava string formule nad aktivnim listom bez čuvanja, brz način da proverite ponašanje engine-a. Jedno upozorenje za sam @: HotXLS je istorijski prihvatao @ između dve reference kao binarni presek, i taj oblik sada računa s pravom semantikom preseka. U Excelu 365 je @ unarni prefiks implicitnog preseka. Nemojte pisati @ u tekst formule i očekivati Excelovo značenje; za presek koristite razmak, a pravila čuvanja iz gore nek se bave semantikom dinamičkog niza

Zašto je Excel odbijao da otvori fajl ili je računao pogrešnu vrednost?

Da Excel prihvati oznaku dinamičkog niza trebalo je tri popravke koje nijedan round-trip test sopstvenim čitačem ne bi uhvatio, jer je HotXLS svoj izlaz čitao ispravno u svakom slučaju. Svaka je nađena otvaranjem HotXLS izlaza u Excelu 16 i zamenom jedne promenljive u svakom prolazu:

1. GUID ekstenzije mora biti sasvim malim slovima. ext uri u xl/metadata.xml mora glasiti tačno {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. Stariji HotXLS šablon pisao ga je mešanim velikim i malim slovima, a Excel 16 odbijao da otvori ceo paket, ne samo ćeliju. Radne sveske napravljene s TXLSXRange.SetDynamicArrayFormula pre v2.384.68 imale su isti problem
2. Tekst korena niza ne nosi vodeći =. XLSX writer ubacuje sačuvani tekst korena niza u <f> doslovno. Da je konvertovana ćelija zadržala svoj =, element bi glasio <f t="array" ref="E5">=SUM(...)</f>, što Excel takođe odbacuje pri otvaranju. HotXLS ga skida tokom konverzije, pa TXLSXCell.Formula čita nazad bez njega
3. Double(True) je -1 u Delphiju. Variant konverzija prati COM konvenciju u kojoj je TRUE svih bitova postavljenih, a VarIsNumeric(True) vraća True. Pre v2.384.61 to je činilo da =TRUE*1 vraća -1 i puštalo je logičke elemente niza da se svrstaju u brojeve, pa je poređenje poput (B1:B2>0)=TRUE išlo naopako. HotXLS sada proverava varBoolean pre nego što Variant tretira kao broj u skalarskoj aritmetici, aritmetici nizova i klasifikaciji elemenata niza, i TRUE se računa kao 1

BIFF8 klase operanada: detalji na nivou bajta za implementatore formata

U BIFF8 svaki token operanda nosi svoju klasu operanda u samom bajtu tokena, i Excel toj klasi veruje više nego strukturi formule. [MS-XLS] definiše klasu kao dvobitno polje PtgDataType u bitovima 5 i 6 tokena: 1 za referencu, 2 za vrednost, 3 za niz. Donjih pet bitova imenuje token, pa ista referenca na opseg ima tri zapisa:

Token	Referentna klasa	Klasa vrednosti	Klasa niza
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

HotXLS je tri od ovih pogrešio na različitim mestima, i svaka greška je u Excelu davala poseban simptom dok se u HotXLS-u čitala lepo:

• Array konstante u referentnoj klasi. Enkoder je birao klasu iz konteksta, a SUM ili ROWS parametri su referentna klasa, pa je =SUM({1,2}) zapisivan s PtgArray kao $20. Excel celu formulu prikazuje kao =#N/A. Array konstanta nikad ne može biti referenca, pa HotXLS od v2.384.63 piše klasu niza $60 gde god kontekst traži referencu
• Operandi PtgIsect-a i PtgUnion-a u klasi vrednosti. Binarni operatori su uzimali operande klase vrednosti, što je ispravno za * ali pogrešno za referentne operatore. Sa $45 oblastima ispred PtgIsect ($0F), Excel je =SUM(A1:B2 B1:B2) čitao kao =SUM(@A1:B2 @B1:B2) i vraćao #VALUE!. Od v2.384.62 operandi PtgIsect-a i PtgUnion-a ($10) pišu se u referentnoj klasi, $25
• Operandi klase vrednosti unutar ARRAY zapisa. Excel primenjuje implicitni presek i unutar array formule kada je operand klase vrednosti. HotXLS je tu pisao $45, pa je jednoćelijska array formula za =SUM(A1:B1*{10,100}) u Excelu vrednovala 10. Od v2.384.68 tok tokena ARRAY zapisa unapređuje svaku referencu klase vrednosti i array konstantu u klasu niza, $65 i $60, što je i ono što Excel piše

Čitač koji ignoriše bitove klase sva tri srećno propusti kroz povratak, pa ako održavate svoj BIFF8 writer, uporedite bitove klase svakog tokena operanda sa fajlom iste formule sačuvanim iz Excela, a ne samo sa brojevima tokena

Brzi podsetnik

• Excel 365 prikazuje @ kada operator u običnoj, neoznačenoj formuli dobije višećelijski opseg ili inline niz
• HotXLS v2.384.68 i kasnije čuva takve formule kao XLSX dinamičke nizove jedne ćelije (cm="1", t="array", XLDAPR metapodatak) i kao XLS array formule jedne ćelije (FORMULA s PtgExp plus ARRAY $0221)
• Račaju se samo operatorski operandi; opseg predat pravo argumentu funkcije ostaje obična formula
• Označavaju se samo formule unete kroz TXLSXCell.Formula ili klasičnu jednoćelijsku Formula / Value; učitane formule ostaju nedirnute
• Konvertovana korenska ćelija čita se nazad bez vodećeg =
• GUID ext uri dinamičkog niza mora biti malim slovima ili Excel odbacuje paket
• U Delphiju je Double(True) -1; proverite varBoolean pre numeričke konverzije
• BIFF8: array konstante nikad referentna klasa, operandi PtgIsect-a / PtgUnion-a u referentnoj klasi, operandi ARRAY zapisa u klasi niza

HotXLS čita, piše i računa XLS i XLSX radne sveske nativno iz Delphija i C++Buildera, i čuva formule s operatorskim nizovima tako da Excel 365 otvara fajl s istim vrednostima koje je HotXLS izračunao. Pogledajte HotXLS Delphi spreadsheet component za izdanja, dokumentaciju i probnu verziju