Formula Array HotXLS: Kenapa Excel Menambah @ dan #VALUE!

Excel 365 menyisipkan @ ke dalam formula seperti =SUM(A1:B1*{10,100}) dan menampilkan #VALUE! saat file menyimpannya sebagai formula biasa, karena Excel lalu menerapkan implicit intersection legacy pada setiap operand operator. Sejak v2.384.68, HotXLS Delphi Component menyimpan formula operator array ini seperti cara Excel 365: sebagai dynamic array formula satu sel di XLSX dan sebagai array formula satu sel di XLS

Gejalanya lolos dari code review. Service Delphi Anda menulis workbook, HotXLS menghitung ulang dan men-cache 210 untuk =SUM(A1:B1*{10,100}), lalu customer membukanya di Excel 16 dan menemukan =SUM(@A1:B1*@{10,100}) di formula bar serta #VALUE! di sel. Tidak ada yang malformed di file itu. Yang hilang adalah metadata yang memberi tahu Excel bahwa formula ditulis di bawah aturan dynamic array, dan tanpa itu Excel jatuh kembali ke model evaluasi pra-dynamic array-nya

Mengapa Excel 365 menambahkan @ ke formula yang dihitung HotXLS dengan benar?

Excel 365 menambahkan @ karena formula tanpa penanda dynamic array, secara definisi, adalah formula legacy, dan formula legacy mereduksi range multi-sel menjadi satu sel di mana pun operator mengharapkan satu nilai. Reduksi itulah implicit intersection: Excel mengambil sel dari range yang berbagi baris dengan formula (untuk range vertikal) atau kolom (untuk range horizontal), dan jika sel semacam itu tak ada, hasilnya #VALUE!. Excel 365 mempertahankan makna itu untuk formula gaya lama dan menampilkan @ agar reduksinya terlihat

Letakkan =SUM(A1:B1*{10,100}) di E5 dan pembacaan legacy-nya jadi jelas. A1:B1 adalah range horizontal, formula ada di kolom E, range itu tak punya sel di kolom E, jadi @A1:B1 bernilai #VALUE! dan seluruh SUM mewarisi nilai itu. Di bawah aturan dynamic array, teks yang sama mengalikan elemen demi elemen, 1 × 10 + 2 × 100, dan mengembalikan 210. Formula engine HotXLS sudah mengevaluasi dengan cara dynamic array sejak rilis v2.384.61 dan v2.384.63; format filenya saja yang belum mengatakannya. Dengan A1:B2 berisi 1, 2, 3, dan 4, ini formula probe-nya beserta yang ditampilkan Excel 16:

Formula	Hasil HotXLS	Excel 16, disimpan sebagai formula biasa	Disimpan sejak v2.384.68
=SUM(A1:B1*{10,100})	210	#VALUE!	Dynamic array, Excel menampilkan 210
=SUM((A1:B2>2)*1)	2	Implicit intersection, salah atau error	Dynamic array, Excel menampilkan 2
=SUMPRODUCT((A1:B2>2)*1)	2	Implicit intersection, salah atau error	Dynamic array, Excel menampilkan 2
=MAX(A1:B2-1)	3	Implicit intersection, salah atau error	Dynamic array, Excel menampilkan 3
=SUM(A1:B2)	10	10	Formula biasa, tak berubah

Baris terakhir sama pentingnya dengan empat baris pertama. SUM(A1:B2) meneruskan range langsung ke parameter fungsi yang menerima reference, jadi tak ada operator yang melihat range multi-sel dan tak ada intersection yang bisa terjadi. Excel 365 sendiri menyimpan formula itu sebagai formula biasa, dan HotXLS melakukan hal yang sama

Bagaimana HotXLS menyimpan formula operator array di XLSX dan XLS

HotXLS menulis formula operator array di XLSX sebagai dynamic array satu sel: elemen <c> membawa cm="1", formula menjadi <f t="array" ref="E5">, dan paketnya mendapat xl/metadata.xml dengan tipe metadata XLDAPR yang extension-nya memuat dynamicArrayProperties fDynamic="1". Atribut cm adalah index satu-basis ke blok cellMetadata di part itu, dan record XLDAPR di baliknyalah yang memberi tahu Excel "evaluasi ini di bawah aturan dynamic array". Strukturnya sama persis dengan yang ditulis Excel 16 saat Anda mengetik formula yang sama lalu menyimpannya, dan memang dari sanalah layout target ini ditetapkan

Di XLS tak ada part metadata, jadi HotXLS memakai satu-satunya construct yang dimiliki BIFF8 untuk evaluasi array: array formula satu sel. Sel tersebut mendapat record FORMULA yang token stream-nya berupa satu PtgExp yang menunjuk dirinya sendiri, diikuti record ARRAY ($0221) yang membawa formula hasil parse yang sesungguhnya di atas range satu sel itu. Excel 365 menulis dynamic array formula ke XLS dengan cara yang sama, dan versi Excel yang lebih tua yang membaca file itu melihatnya sebagai array formula klasik Ctrl+Shift+Enter

Tidak ada API baru yang terlibat. Penandaan terjadi saat Anda meng-assign formula lewat API sel normal, di kedua engine. Di sisi XLSX itu 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 di atas range atau array inline: disimpan sebagai dynamic array
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // Range diteruskan langsung ke fungsi: tetap <f> biasa
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

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

    // Root array menyimpan teksnya tanpa '=' di depan
    Writeln(Sheet.Cells[5, 5].Formula);              // SUM(A1:B1*{10,100})

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

Setelah konversi, TXLSXCell.Formula mengembalikan teks tanpa =, bentuk yang sama dengan yang disimpan TXLSXRange.SetDynamicArrayFormula, jadi kode yang membandingkan string formula setelah assignment sebaiknya menormalkan = di depan

Engine classic mengikuti aturan yang sama lewat IXLSRange.Formula pada satu sel. Meng-assign formula akan mengarahkannya ke jalur array satu sel secara internal, sehingga XLS yang tersimpan memuat pasangan 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})';  // record ARRAY
  Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)';         // record ARRAY
  Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)';           // FORMULA biasa

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

Jika yang Anda anchoring adalah hasil multi-sel dan bukan agregat skalar, API eksplisit tetap menjadi alat yang tepat: SetArrayFormula untuk persegi yang ukurannya sudah disiapkan, seperti dibahas di dynamic array spill formula dengan HotXLS, atau TXLSXRange.SetDynamicArrayFormula saat Anda ingin penanda dynamic array XLSX pada range yang ukurannya Anda tentukan sendiri. Jalur otomatis di artikel ini hanya mencakup formula yang diketik ke satu sel

Formula mana yang ditandai HotXLS sebagai dynamic array?

HotXLS menandai sebuah formula hanya ketika ada operator yang punya subtree operand yang menghasilkan array. Pemeriksaan berjalan di atas syntax tree hasil kompilasi, dan sebuah operand menghasilkan array jika ia range multi-sel, array constant inline, atau ekspresi operator lain yang sendirian punya operand semacam itu. Tanda kurung transparan. Operator yang dihitung adalah operator aritmetika (+ - * / ^), konkatenasi (&), enam perbandingan, unary plus dan minus, serta percent:

• A1:B1*{10,100}, (A1:B2>2)*1, --(B1:B2>0), dan A1:B2-1 ditandai, di mana pun posisinya dalam formula, termasuk di dalam SUMPRODUCT
• SUM(A1:B2) dan SUMPRODUCT(A1:A2,{1;10}) tidak ditandai, karena range dan array masuk langsung ke argumen fungsi dan tak ada operator yang menyentuhnya
• A1*2 atau SUM(A1,B1)*2 tidak ditandai: reference satu sel dan hasil fungsi adalah skalar bagi pemeriksaan ini

Tiga batas berikut disengaja. Pertama, penandaan hanya terjadi saat formula dimasukkan lewat API, yaitu TXLSXCell.Formula di engine XLSX dan assignment Formula atau Value satu sel di engine classic. Formula yang dimuat dari file ditulis kembali persis seperti ditemukan, karena formula legacy dari producer lain bisa saja sengaja bergantung pada implicit intersection. Kedua, teks yang tidak memuat : maupun { dilewati tanpa kompilasi kedua. Ketiga, formula yang akan spill, misalnya =A1:B1*2 berdiri sendiri, ditandai sebagai dynamic array satu sel yang di-anchor di tempat Anda meletakkannya. HotXLS tidak men-spill-nya, dan Excel akan memperluas hasilnya ke sel tetangga saat menghitung ulang berikutnya

Aturan operand ini adalah saudara dari aturan argument-class yang dibahas di implicit intersection untuk defined name di HotXLS. Artikel itu tentang parameter fungsi yang dideklarasikan sebagai value class; yang ini tentang operator, yang di model legacy selalu menuntut value

Apa yang berubah di calculation engine agar hasilnya cocok

Perbaikan penyimpanan di v2.384.68 bertumpu pada formula engine HotXLS yang sudah lebih dulu mengembalikan nilai Excel 365, dan itu dibayar oleh beberapa perbaikan sebelumnya di kedua engine. Yang paling terlihat adalah SUMPRODUCT: sampai v2.384.61 ia hanya menerima dua plain range atau lebih, sehingga SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)), bahkan SUMPRODUCT(B1:B2) satu argumen mengembalikan #N/A. HotXLS kini mengevaluasi argumen ekspresi elemen demi elemen dengan aturan Excel:

• setiap argumen harus punya bentuk yang persis sama, skalar dihitung sebagai 1 × 1, jika tidak hasilnya #VALUE!
• error value di dalam argumen mana pun dikembalikan sebagai hasilnya
• elemen teks dan logical dihitung 0, jadi (B1:B2>0)*1 atau -- tetap diperlukan untuk mengubah TRUE menjadi 1
• argumen yang semuanya plain range tetap memakai streaming loop asli, sehingga range besar tidak dimaterialisasi sebagai array

Keluarga SUM (SUM, COUNT, AVERAGE, MIN, MAX, COUNTA) memakai evaluator elemen-demi-elemen yang sama ketika sebuah argumen adalah ekspresi operator di atas range, jadi =SUM((B1:B2>0)*1) menghitung kedua baris alih-alih melihat sel pertama saja. v2.384.62 membuat operator intersection spasi mengembalikan persegi panjang bersama dari dua reference, dengan #NULL! ketika keduanya tak tumpang tindih, jadi =SUM(A1:B2 B1:B2) bernilai 6 alih-alih 2 dan hasilnya bisa mengisi parameter reference seperti ROWS dan INDEX. v2.384.63 menambahkan array constant inline seperti {1,2;3,4} (koma memisahkan kolom, titik koma memisahkan baris) dan reference union seperti (A1:B2,D4) ke parser. Perbandingan elemen-demi-elemen juga memberi elemen kosong tipe dari sisi seberang, FALSE saat dihadapkan ke logical, selaras dengan aturan skalar dari v2.384.53 yang dibahas di comparison chain dan sel kosong di HotXLS

var
  V: Variant;
begin
  // Book adalah TXLSXWorkbook dari contoh pertama;
  // active sheet-nya memuat A1:B2 = 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10, satu argumen
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6, range bersama B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16, overlap dihitung dua kali
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1, dulunya -1 sebelum v2.384.61
end;

TXLSXWorkbook.Calculate mengevaluasi string formula terhadap active sheet tanpa menyimpannya, cara cepat untuk menguji perilaku engine. Satu kehati-hatian soal @ itu sendiri: HotXLS secara historis menerima @ di antara dua reference sebagai binary intersection, dan kini ia mengevaluasi bentuk itu dengan semantik intersection yang sesungguhnya. Di Excel 365, @ adalah prefix unary implicit intersection. Jangan menulis @ ke dalam teks formula lalu berharap maknanya seperti milik Excel; pakai spasi untuk intersection dan biarkan aturan penyimpanan di atas yang mengurus semantik dynamic array

Mengapa Excel menolak membuka file atau menghitung nilai yang salah?

Membuat Excel menerima penanda dynamic array butuh tiga perbaikan yang tak akan tertangkap oleh test round-trip terhadap output sendiri, karena HotXLS membaca output-nya sendiri dengan benar di semua kasus. Masing-masing ditemukan dengan membuka output HotXLS di Excel 16 sambil mengganti satu variabel pada satu waktu:

1. GUID extension harus seluruhnya huruf kecil. ext uri di xl/metadata.xml harus tepat {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. Template HotXLS lama menulisnya dengan huruf campuran, dan Excel 16 menolak membuka seluruh paketnya, bukan cuma selnya. Workbook yang dibuat dengan TXLSXRange.SetDynamicArrayFormula sebelum v2.384.68 mengalami masalah yang sama
2. Teks root array tidak membawa = di depan. Writer XLSX memancarkan teks tersimpan dari array root verbatim ke <f>. Kalau sel hasil konversi masih membawa =, elemennya akan berbunyi <f t="array" ref="E5">=SUM(...)</f>, yang juga ditolak Excel saat membuka file. HotXLS menghapusnya saat konversi, itulah sebabnya TXLSXCell.Formula terbaca kembali tanpanya
3. Double(True) bernilai -1 di Delphi. Konversi Variant mengikuti konvensi COM yang menjadikan TRUE semua bit terisi, dan VarIsNumeric(True) pun mengembalikan True. Sebelum v2.384.61, itu membuat =TRUE*1 mengembalikan -1 dan elemen array logical ikut terklasifikasi sebagai angka, sehingga perbandingan seperti (B1:B2>0)=TRUE menjadi salah. HotXLS kini mengetes varBoolean sebelum memperlakukan Variant sebagai angka dalam aritmetika skalar, aritmetika array, dan klasifikasi elemen array, dan TRUE dihitung 1

Kelas operand BIFF8: detail level byte untuk implementer format

Di BIFF8, setiap operand token membawa kelas operandnya di byte token itu sendiri, dan Excel memercayai kelas itu lebih daripada struktur formula. [MS-XLS] mendefinisikan kelas tersebut sebagai field PtgDataType dua bit di bit 5 dan 6 token: 1 untuk reference, 2 untuk value, 3 untuk array. Lima bit rendah menamai tokennya, jadi area reference yang sama punya tiga ejaan:

Token	Kelas reference	Kelas value	Kelas array
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

HotXLS keliru menulis tiga hal di atas di tempat yang berbeda, dan masing-masing memunculkan gejala yang khas di Excel sementara dibaca balik di HotXLS tetap mulus:

• Array constant berkelas reference. Encoder memilih kelas dari konteks, dan parameter SUM atau ROWS berkelas reference, sehingga =SUM({1,2}) ditulis dengan PtgArray sebagai $20. Excel menampilkan seluruh formula sebagai =#N/A. Array constant tak mungkin menjadi reference, jadi sejak v2.384.63 HotXLS menulis kelas array $60 di mana pun konteksnya meminta reference
• Operand PtgIsect dan PtgUnion berkelas value. Operator binary memakai operand berkelas value, yang benar untuk * tapi salah untuk operator reference. Dengan area $45 sebelum PtgIsect ($0F), Excel membaca =SUM(A1:B2 B1:B2) sebagai =SUM(@A1:B2 @B1:B2) dan mengembalikan #VALUE!. Sejak v2.384.62 operand PtgIsect dan PtgUnion ($10) ditulis berkelas reference, $25
• Operand berkelas value di dalam record ARRAY. Excel menerapkan implicit intersection bahkan di dalam array formula ketika operand berkelas value. HotXLS menulis $45 di sana, sehingga array formula satu sel untuk =SUM(A1:B1*{10,100}) terevaluasi menjadi 10 di Excel. Sejak v2.384.68, token stream sebuah record ARRAY mempromosikan setiap reference berkelas value dan array constant ke kelas array, $65 dan $60, persis seperti yang ditulis Excel

Reader yang mengabaikan bit kelas akan me-round-trip ketiganya dengan mulus, jadi kalau Anda merawat writer BIFF8 sendiri, bandingkan bit kelas dari setiap operand token dengan file yang disimpan Excel untuk formula yang sama, bukan cuma nomor tokennya

Referensi cepat

• Excel 365 menampilkan @ ketika operator dalam formula biasa yang tak bertanda menerima range multi-sel atau array inline
• HotXLS v2.384.68 dan seterusnya menyimpan formula semacam itu sebagai dynamic array satu sel XLSX (cm="1", t="array", metadata XLDAPR) dan sebagai array formula satu sel XLS (FORMULA dengan PtgExp plus ARRAY $0221)
• Hanya operand operator yang dihitung; range yang diteruskan langsung ke argumen fungsi tetap formula biasa
• Hanya formula yang dimasukkan lewat TXLSXCell.Formula atau Formula / Value satu sel classic yang ditandai; formula yang dimuat tidak disentuh
• Sel root hasil konversi terbaca kembali tanpa = di depan
• GUID ext uri dynamic array harus huruf kecil, atau Excel menolak paketnya
• Di Delphi, Double(True) bernilai -1; test varBoolean sebelum konversi numerik
• BIFF8: array constant tidak pernah berkelas reference, operand PtgIsect / PtgUnion berkelas reference, operand record ARRAY berkelas array

HotXLS membaca, menulis, dan menghitung workbook XLS dan XLSX secara native dari Delphi dan C++Builder, dan menyimpan formula operator array agar Excel 365 membukanya dengan nilai yang sama seperti yang dihitung HotXLS. Lihat komponen spreadsheet Delphi HotXLS untuk edisi, dokumentasi, dan unduhan trial