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

Excel 365 umeće @ u formulu poput =SUM(A1:B1*{10,100}) i prikazuje #VALUE! kad je datoteka tu formulu spremila kao običnu, jer Excel tada na svaki operand operatora primjenjuje naslijeđeni implicitni presjek. Od v2.384.68 HotXLS Delphi Component sprema te formule s operatorima polja onako kako to čini Excel 365: kao formule dinamičkog polja jedne ćelije u XLSX-u i kao formule polja jedne ćelije u XLS-u

Simptom preživi i code review. Vaša Delphi usluga zapiše radnu knjigu, HotXLS je preračuna i predmemorira 210 za =SUM(A1:B1*{10,100}), a kupac je otvori u Excelu 16 i u traci formuli pronađe =SUM(@A1:B1*@{10,100}), a u ćeliji #VALUE!. Ništa u datoteci nije neispravno. Fali metapodatak koji Excelu govori da je formula zapisana pod pravilima dinamičkog polja, a bez njega Excel se vraća na model evaluacije iz vremena prije dinamičkih polja

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

Excel 365 dodaje @ jer je formula bez oznake dinamičkog polja po definiciji naslijeđena formula, a naslijeđene formule svugdje gdje operator očekuje jednu vrijednost svode višećelijski raspon na jednu ćeliju. To svodenje jest implicitni presjek: Excel uzima ćeliju raspona koja dijeli red formule (kod okomitog raspona) ili stupac (kod vodoravnog raspona), a ako takva ćelija ne postoji, rezultat je #VALUE!. Excel 365 zadržava to značenje za formule starog stila i prikazuje @ da bi svodenje bilo vidljivo

Stavite =SUM(A1:B1*{10,100}) u E5 i naslijeđeno čitanje postane očito. A1:B1 je vodoravni raspon, formula sjedi u stupcu E, raspon nema ćeliju u stupcu E, pa je @A1:B1 #VALUE! i cijeli SUM to nasljeđuje. Pod pravilima dinamičkog polja isti tekst množi element po element, 1 × 10 + 2 × 100, i vraća 210. HotXLS motor formula evaluira na način dinamičkog polja još od izdanja v2.384.61 i v2.384.63; format datoteke jednostavno to nije govorio. S A1:B2 koji drži 1, 2, 3 i 4, ovo su probne formule i ono što Excel 16 prikazuje:

Formula	HotXLS rezultat	Excel 16, spremljeno kao obična formula	Spremljeno od v2.384.68
=SUM(A1:B1*{10,100})	210	#VALUE!	Dinamičko polje, Excel prikazuje 210
=SUM((A1:B2>2)*1)	2	Implicitni presjek, pogrešno ili greška	Dinamičko polje, Excel prikazuje 2
=SUMPRODUCT((A1:B2>2)*1)	2	Implicitni presjek, pogrešno ili greška	Dinamičko polje, Excel prikazuje 2
=MAX(A1:B2-1)	3	Implicitni presjek, pogrešno ili greška	Dinamičko polje, Excel prikazuje 3
=SUM(A1:B2)	10	10	Obična formula, nepromijenjeno

Posljednji red vrijedi jednako koliko i prva četiri. SUM(A1:B2) prosljeđuje raspon izravno u parametar funkcije koji prima reference, pa operator nikad ne vidi višećelijski raspon i presjek se ne može dogoditi. Excel 365 i sam tu formulu sprema kao običnu formulu, a HotXLS čini isto

Kako HotXLS sprema formule s operatorima polja u XLSX i XLS

HotXLS zapisuje formulu s operatorom polja u XLSX-u kao dinamičko polje jedne ćelije: element <c> nosi cm="1", formula je <f t="array" ref="E5">, a paket dobiva xl/metadata.xml s metapodatkovnim tipom XLDAPR čija ekstenzija drži dynamicArrayProperties fDynamic="1". Atribut cm jest indeks počevši od jedan u blok cellMetadata tog dijela, a XLDAPR zapis iza njega jest ono što Excelu govori "evaluiraj ovo pod pravilima dinamičkog polja". To je ista struktura koju Excel 16 zapisuje kad upišete istu formulu i spremite, i upravo tako je ciljni raspored uopće i utvrđen

U XLS-u nema dijela s metapodacima, pa HotXLS koristi jedinu konstrukciju koju BIFF8 ima za evaluaciju polja: formulu polja jedne ćelije. Ćelija dobiva FORMULA zapis čiji je tok tokena jedan PtgExp koji pokazuje na samu sebe, iza kojeg slijedi ARRAY zapis ($0221) koji nosi stvarno parsiranu formulu nad rasponom jedne ćelije. Excel 365 formule dinamičkog polja u XLS zapisuje na isti način, a starija verzija Excela koja čita datoteku vidi klasičnu formulu polja s Ctrl+Shift+Enter

Nema novog API-ja. Označavanje se događa kad formulu dodijelite kroz normalni API ćelije, u oba motora. 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 rasponom ili ugrađenim poljem: sprema se kao dinamičko polje
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // Raspon proslijeđen izravno u funkciju: 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

    // Korijen polja 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 dobivaju cm="1" + t="array"
  finally
    Book.Free;
  end;
end;

Nakon pretvorbe TXLSXCell.Formula vraća tekst bez =, isti oblik koji sprema TXLSXRange.SetDynamicArrayFormula, pa kod koji uspoređuje nizove formula nakon dodjele treba normalizirati vodeći =

Klasični motor slijedi isto pravilo kroz IXLSRange.Formula na jednoj ćeliji. Dodjela formule interno je preusmjerava na put formule polja jedne ćelije, pa spremljeni 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čni FORMULA

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

Ako usidravate višećelijski rezultat, a ne skalarnu agregaciju, eksplicitni API-ji i dalje su pravi alat: SetArrayFormula za unaprijed dimenzionirani pravokutnik, kako je opisano u spill formulama dinamičkog polja s HotXLS-om, ili TXLSXRange.SetDynamicArrayFormula kad želite XLSX oznaku dinamičkog polja na rasponu koji sami dimenzionirate. Automatski put iz ovog članka pokriva samo formule upisane u jednu ćeliju

Koje formule HotXLS označava kao dinamička polja?

HotXLS označava formulu samo kad operator ima podstablo operanda koje proizvodi polje. Provjera se izvodi nad kompajliranim sintaksnim stablom, a operand proizvodi polje ako je višećelijski raspon, ugrađena konstanta polja ili drugi izraz operatora koji sam ima takav operand. Zagrade su prozirne. Operatori koji se računaju jesu aritmetički (+ - * / ^), konkatenacija (&), šest usporedbi, unarni plus i minus te postotak:

• A1:B1*{10,100}, (A1:B2>2)*1, --(B1:B2>0) i A1:B2-1 označavaju se, gdje god se pojavili u formuli, uključivo unutar SUMPRODUCT
• SUM(A1:B2) i SUMPRODUCT(A1:A2,{1;10}) ne označavaju se jer raspon i polje idu izravno u argument funkcije i nijedan operator ih ne dira
• A1*2 i SUM(A1,B1)*2 ne označavaju se: reference pojedinačnih ćelija i rezultati funkcija skalar su za ovu provjeru

Tri granice namjerne su. Prvo, označavanje se dogodi samo kad se formula unese kroz API, što znači TXLSXCell.Formula u XLSX motoru te dodjelu Formula ili Value jedne ćelije u klasičnom motoru. Formule učitane iz datoteke zapisuju se natrag točno onakve kakve su nađene, jer naslijeđena formula drugog proizvođača može namjerno ovisiti o implicitnom presjeku. Drugo, tekst koji ne sadrži ni : ni { preskače se bez drugog kompajliranja. Treće, formula koja bi se prelijevala, poput same =A1:B1*2, označava se kao dinamičko polje jedne ćelije usidreno tamo gdje ste je stavili. HotXLS je ne prelijeva, a Excel će proširiti rezultat na susjedne ćelije sljedeći put kad preračuna

Ovo pravilo operanda brat je blizanac pravilu klase argumenata obrađenom u implicitnom presjeku za definirana imena u HotXLS-u. Taj članak govori o parametrima funkcija deklariranim kao value klasa; ovaj govori o operatorima, koji u naslijeđenom modelu uvijek traže vrijednosti

Što se promijenilo u motoru izračuna da bi se rezultati poklopili

Popravak pohrane u v2.384.68 oslanja se na to da HotXLS motor formula već vraća vrijednosti Excela 365, što je zahtijevalo nekoliko ranijih popravaka u oba motora. Najvidljiviji bio je SUMPRODUCT: do v2.384.61 primala je samo dva ili više običnih raspona, pa su SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) pa čak i jednog argumenta SUMPRODUCT(B1:B2) vraćale #N/A. HotXLS sada evaluira argumente izraza element po element s Excelovim pravilima:

• svaki argument mora imati potpuno isti oblik, pri čemu se skalar računa kao 1 × 1, inače je rezultat #VALUE!
• greška unutar bilo kojeg argumenta vraća se kao rezultat
• tekstovni i logički elementi računaju se kao 0, pa je (B1:B2>0)*1 ili -- i dalje potrebno da TRUE postane 1
• argumenti koji su svi obični rasponi zadržavaju izvornu petlju s tokom, pa se veliki rasponi ne materijaliziraju kao polja

Obitelj SUM (SUM, COUNT, AVERAGE, MIN, MAX, COUNTA) koristi isti evaluator po elementu kad je argument izraz operatora nad rasponom, pa =SUM((B1:B2>0)*1) broji oba reda umjesto da gleda samo prvu ćeliju. v2.384.62 učinila je da operator presjeka s razmakom vraća zajednički pravokutnik dviju referenci, s #NULL! kad se ne preklapaju, pa je =SUM(A1:B2 B1:B2) 6 umjesto 2, a rezultat može hraniti referentne parametre poput ROWS i INDEX. v2.384.63 dodala je parseru ugrađene konstante polja poput {1,2;3,4} (zarezi odvajaju stupce, točke-zarez redove) i unije referenci poput (A1:B2,D4). Usporedbe po elementu daju praznom elementu tip druge strane, FALSE protiv logičke vrijednosti, u skladu sa skalarnim pravilom iz v2.384.53 opisanim u lancima usporedbi i praznim ćelijama u HotXLS-u

var
  V: Variant;
begin
  // Book je TXLSXWorkbook iz prvog primjera;
  // njezin 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 raspon B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16, preklapanje brojano dvaput
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1, bilo je -1 prije v2.384.61
end;

TXLSXWorkbook.Calculate evaluira niz formule nad aktivnim listom bez spremanja, brz način provjere ponašanja motora. Jedno upozorenje o samom @: HotXLS je povijesno prihvaćao @ između dviju referenci kao binarni presjek, i tu formu sada evaluira sa stvarnom semantikom presjeka. U Excelu 365 @ je unarni prefiks implicitnog presjeka. Ne upisujte @ u tekst formule i ne očekujte Excelovo značenje; za presjek koristite razmak, a pravila pohrane iz gore neka se pobrinu za semantiku dinamičkog polja

Zašto je Excel odbio otvoriti datoteku ili izračunao krivu vrijednost?

Da Excel prihvati oznaku dinamičkog polja trebala su tri popravka koje nijedan test povratnog ciklusa sam sa sobom ne bi uhvatio, jer je HotXLS u svakom slučaju točno čitao svoj vlastiti izlaz. Svaki je nađen otvaranjem HotXLS izlaza u Excelu 16 i zamjenom jedne varijable u jednom:

1. GUID ekstenzije mora biti potpuno malim slovima. ext uri u xl/metadata.xml mora biti točno {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. Stariji HotXLS predložak pisao ga je miješano, a Excel 16 odbio je otvoriti cijeli paket, ne samo ćeliju. Radne knjige stvorene s TXLSXRange.SetDynamicArrayFormula prije v2.384.68 imale su isti problem
2. Tekst korijena polja ne nosi vodeći =. XLSX pisač upisuje pohranjeni tekst korijena polja doslovno u <f>. Da je pretvorena ćelija zadržala svoj =, element bi glasio <f t="array" ref="E5">=SUM(...)</f>, što Excel također odbija pri otvaranju. HotXLS ga uklanja tijekom pretvorbe, pa TXLSXCell.Formula čita natrag bez njega
3. Double(True) je -1 u Delphiju. Pretvorba Variant slijedi COM konvenciju gdje je TRUE svih bitova postavljenih, a VarIsNumeric(True) također vraća True. Prije v2.384.61 to je činilo da =TRUE*1 vraća -1 i dopuštalo da se logički elementi polja klasificiraju kao brojevi, pa je usporedba poput (B1:B2>0)=TRUE išla krivo. HotXLS sada testira varBoolean prije nego Variant tretira kao broj u skalarnoj aritmetici, aritmetici polja i klasifikaciji elemenata polja, i TRUE računa se kao 1

BIFF8 klase operanada: detalji na razini bajta za implementatore formata

U BIFF8 svaki token operanda nosi svoju klasu operanda u samom bajtu tokena, a Excel vjeruje toj klasi više nego strukturi formule. [MS-XLS] definira klasu kao dvobitno polje PtgDataType u bitovima 5 i 6 tokena: 1 za referencu, 2 za vrijednost, 3 za polje. Niskih pet bitova imenuje token, pa ista referenca područja ima tri načina zapisa:

Token	Klasa reference	Klasa vrijednosti	Klasa polja
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

HotXLS je tri od ovih pogriješio na raznim mjestima, a svaki je u Excelu proizveo drukčiji simptom dok se u HotXLS-u čitao bez problema:

• Konstante polja klase reference. Enkoder birao je klasu iz konteksta, a SUM ili ROWS parametri su klase reference, pa je =SUM({1,2}) zapisan s PtgArray kao $20. Excel prikazuje cijelu formulu kao =#N/A. Konstanta polja nikad ne može biti referenca, pa HotXLS od v2.384.63 piše klasu polja $60 gdje god kontekst traži referencu
• Operandi klase vrijednosti kod PtgIsect i PtgUnion. Binarni operatori primali su operande klase vrijednosti, što je ispravno za * ali krivo za referentne operatore. S područjima $45 ispred PtgIsect ($0F), Excel je čitao =SUM(A1:B2 B1:B2) kao =SUM(@A1:B2 @B1:B2) i vraćao #VALUE!. Od v2.384.62 operandi PtgIsect i PtgUnion ($10) zapisuju se u klasi reference, $25
• Operandi klase vrijednosti unutar ARRAY zapisa. Excel primjenjuje implicitni presjek čak i unutar formule polja kad je operand klase vrijednosti. HotXLS je tamo pisao $45, pa je formula polja jedne ćelije za =SUM(A1:B1*{10,100}) u Excelu evaluirala u 10. Od v2.384.68 tok tokena ARRAY zapisa promiče svaku referencu klase vrijednosti i konstantu polja u klasu polja, $65 i $60, što je i ono što Excel piše

Čitač koji ignorira bitove klase sretno provodi povratni ciklus sa sva tri slučaja, pa ako održavate vlastiti BIFF8 pisač, usporedite bitove klase svakog tokena operanda s Excelom spašenom datotekom iste formule, a ne samo s brojevima tokena

Brza referenca

• Excel 365 prikazuje @ kad operator u običnoj, neoznačenoj formuli primi višećelijski raspon ili ugrađeno polje
• HotXLS v2.384.68 i noviji sprema takve formule kao XLSX dinamička polja jedne ćelije (cm="1", t="array", XLDAPR metapodaci) i kao XLS formule polja jedne ćelije (FORMULA s PtgExp plus ARRAY $0221)
• Računaju se samo operandi operatora; raspon proslijeđen izravno u argument funkcije ostaje obična formula
• Označavaju se samo formule unesene kroz TXLSXCell.Formula ili klasičnu dodjelu Formula / Value jedne ćelije; učitane formule ostaju netaknute
• Pretvorena korijenska ćelija čita se natrag bez vodećeg =
• GUID ext uri dinamičkog polja mora biti malim slovima inače Excel odbija paket
• U Delphiju je Double(True) -1; testirajte varBoolean prije numeričke pretvorbe
• BIFF8: konstante polja nikad klasa reference, operandi PtgIsect / PtgUnion u klasi reference, operandi ARRAY zapisa u klasi polja

HotXLS čita, zapisuje i računa XLS i XLSX radne knjige nativno iz Delphija i C++Buildera, a formule s operatorima polja sprema tako da ih Excel 365 otvara s istim vrijednostima koje je HotXLS izračunao. Pogledajte HotXLS Delphi proračunsku komponentu za izdanja, dokumentaciju i probno preuzimanje