Formula Spill Array Dinamis di Delphi dengan HotXLS

HotXLS mengevaluasi formula spill array dinamis Excel 365 secara asli di Delphi dan C++Builder: Anda menyerahkan workbook yang sel-selnya menampung XLOOKUP atau FILTER, dan engine formulanya menghitung set hasil lalu menumpahkannya (spill) di seluruh persegi panjang output yang ditambatkan, nilai yang sama dengan yang dihasilkan Excel. Komponen HotXLS Delphi Excel memodelkan formula spill sebagai satu sel akar yang memiliki perhitungan ditambah blok sel dependen yang membaca kembali darinya, yang merupakan gambaran lengkap yang Anda butuhkan sebelum menulis baris kode

Model tersebut penting karena tugas sehari-hari tidaklah abstrak. Pelanggan mengirimkan file .xlsx yang dibuat di Excel 365, lembarannya penuh dengan =FILTER(...) dan =XLOOKUP(...), dan layanan Anda harus mereproduksi angka yang sama persis secara headless, di mesin tanpa Excel yang terinstal, lalu membaca nilai spill tersebut atau menulis wilayah spill baru milik Anda sendiri. Array dinamis mengubah kontrak perhitungan dari "one formula, one cell" to "one formula, a rectangle of cells", and the HotXLS engine follows that contract rather than faking it with a pre-expanded grid

Bagaimana cara mengevaluasi XLOOKUP dan FILTER di Delphi?

HotXLS mengirimkan setiap fungsi array dinamis melalui satu titik masuk evaluator, CalcDynArrayFunc di lxCalc.pas, sehingga seluruh keluarga fungsi tersebut berbagi satu jalur kode spill. Set yang didukung adalah XLOOKUP dan FILTER (dua yang asli), bergabung dengan XMATCH, SORT, UNIQUE, dan SEQUENCE. Masing-masing mengembalikan array varian 2-D dan bukan skalar: SEQUENCE(3;2;1;1) menghasilkan kisi 3x2, SORT mengurutkan kembali baris berdasarkan kolom kunci apa pun secara menaik (ascending) atau menurun (descending), UNIQUE menciutkan baris duplikat dengan mempertahankan kemunculan pertama, dan XMATCH melaporkan posisi berbasis 1 dari suatu nilai di bawah mode kecocokan persis, wildcard, terkecil berikutnya, dan terbesar berikutnya yang sama seperti XLOOKUP. Berbeda dengan fungsi distribusi statistik skalar, yang mengembalikan satu angka per panggilan, fungsi-fungsi ini mengembalikan seluruh bentuk (shape)

Untuk menghitung workbook yang sudah membawa formula tersebut, buka workbook, panggil Recalculate sekali, dan baca sel tempat spill mendarat. TXLSXWorkbook.Recalculate menelusuri grafik ketergantungan dan mengevaluasi setiap formula kotor dalam urutan topologis, sehingga akar spill dihitung sekali dan elemen-elemennya ditulis langsung ke dalam sel anggota

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  r, c: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('from-customer.xlsx');
    Book.Recalculate;                 // evaluate every formula, spills included
    Sheet := Book.Sheets[1];          // Sheets is 1-based
    // Read back the block the XLOOKUP / FILTER spilled into, say D2:F9.
    for r := 2 to 9 do
      for c := 4 to 6 do
        Writeln(Sheet.Cells[r, c].Text);
  finally
    Book.Free;
  end;
end;

Jika workbook membutuhkan fungsi yang tidak disediakan oleh engine, evaluator yang sama memungkinkan Anda memasukkan logika sendiri melalui kait fungsi lembar kerja kustom, dan fungsi kustom bebas mengembalikan array varian sehingga tumpah (spill) persis seperti fungsi bawaan

Menambatkan rentang spill dengan SetArrayFormula

HotXLS tidak pernah menebak seberapa besar spill seharusnya: Anda menentukan persegi panjang output, dan TXLSRange.SetArrayFormula menambatkan formula ke sana. Metode ini mengompilasi formula sekali, menyimpan pohon sintaks pada sel akar kiri-atas bersama dengan rentang yang ditambatkan, dan memberikan formula ringan ke setiap sel lain di dalam persegi panjang yang mereferensikan sel akar secara lemah. Pada perhitungan ulang (recalculation), sel akar dievaluasi sekali dan elemen matriksnya disiarkan langsung ke setiap sel anggota, yang juga bagaimana akar spill muncul sebagai satu node dalam grafik ketergantungan perhitungan ulang bertahap. Ini adalah jalur yang digunakan ketika tugasnya adalah menulis wilayah spill kembali ke dalam file alih-alih membaca nilai keluar

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('report.xlsx');
    Sheet := Book.Sheets[1];
    // Size the rectangle to the result: a 3-row by 2-column grid.
    if Sheet.Range['A1:B3'].SetArrayFormula('=SEQUENCE(3;2;1;1)') > 0 then
      Book.Recalculate;               // fill A1:B3 with 1..6 down the rows
    Book.SaveAs('report-out.xlsx');
  finally
    Book.Free;
  end;
end;

Konsekuensi dari penambatan eksplisit adalah batasan yang patut dinyatakan secara jelas: HotXLS tidak mengubah ukuran spill secara otomatis seperti yang dilakukan Excel interaktif. Excel memperbesar dan memperkecil wilayah spill seiring perubahan inputnya dan memunculkan kesalahan #SPILL! ketika sel target ditempati. Di HotXLS, persegi panjang yang Anda teruskan ke SetArrayFormula adalah persegi panjang yang Anda dapatkan, jadi Anda harus mengukurnya sesuai dengan hasil yang Anda harapkan, dan jika suatu fungsi menghasilkan lebih banyak baris daripada yang Anda tambatkan, kelebihannya tidak memiliki tempat untuk mendarat

Penyiaran array berdasarkan elemen di seluruh operator

HotXLS menerapkan operator aritmatika elemen demi elemen ketika salah satu sisi operasi adalah array. Pembantu ApplyArrayBinaryOp mencakup +, -, *, /, dan ^ pada array varian 1-D dan 2-D: operan skalar disiarkan ke setiap elemen, dua array harus cocok dalam bentuknya atau operasi akan ditolak, dan pembagian dengan nol merambat sebagai kesalahan alih-alih crash. Jalur aritmatika skalar tidak tersentuh, sehingga ini hanya aktif ketika setidaknya satu operan benar-benar berupa array, seperti rentang spill atau hasil fungsi matriks. Itu berarti formula seperti =D2:D13*1.1 yang ditambatkan pada kolom akan mengalikan setiap elemen secara bergantian, dan =D2:D13*E2:E13 mengalikan dua kolom dengan bentuk yang sama posisi demi posisi

// Scalar broadcast: every cell of the anchor gets D(n) * 1.1.
Sheet.Range['F2:F13'].SetArrayFormula('=D2:D13*1.1');
// Two arrays of identical shape multiply element by element.
Sheet.Range['G2:G13'].SetArrayFormula('=D2:D13*E2:E13');
Book.Recalculate;

Apa fungsi operator @ versus array CSE lama?

HotXLS membaca @ eksplisit sebagai operator perpotongan-referensi (reference-intersection), bukan sebagai perpotongan implisit pemilihan baris pada Excel langsung. Di dalam engine, A1:A3 @ B1:B3 mengembalikan sel tunggal di mana kedua rentang tersebut bersilang, dan operator ini mengikat lebih kuat daripada ^ dan lebih longgar daripada %. Satu catatan jujur menyertainya: bentuk perpotongan implisit yang dipisahkan spasi belum ditokenisasi, karena lexer masih melewati spasi putih, sehingga Anda hanya mendapatkan perpotongan melalui simbol @ eksplisit

Perbedaan yang lebih mendalam adalah antara formula array CSE lama dan array dinamis modern, dan di HotXLS keduanya mengalir melalui mekanisme penambatan yang sama. Formula array array klasik adalah blok {=...} yang dimasukkan dengan Ctrl+Shift+Enter di atas pilihan yang telah ditentukan ukurannya, setiap sel berbagi satu formula terkompilasi. Array dinamis adalah formula tunggal yang hasilnya menentukan bentuknya sendiri. HotXLS menyatukan mereka di bawah SetArrayFormula: Anda selalu menentukan persegi panjang, akar memiliki pohon terkompilasi, dan anggota mereferensikannya, baik formula tersebut berupa ekspresi matriks gaya lama atau SORT baru. Yang tidak pernah Anda dapatkan adalah riak otomatis Excel di mana wilayah tersebut tumbuh kembali secara diam-diam, dan mengingat hal itu menjaga kedua model mental agar tidak kabur

Di mana batas dukungan array dinamis

Mengetahui batasan dapat menghemat waktu debug Anda. SORT mematuhi arah per-kunci, menaik secara bawaan dan menurun ketika argumen urutan bernilai negatif; arah perbandingan adalah tempat yang patut diuji untuk kolom kunci Anda sendiri terhadap Excel, karena perbandingan terbalik secara diam-diam mengembalikan baris dalam urutan berlawanan daripada gagal. UNIQUE mempertahankan kemunculan pertama dari setiap baris yang berbeda, yang dikonfirmasi oleh engine dengan memindai baris-baris sebelumnya alih-alih mempercayai jumlah duplikat mentah. Fungsi TABLE (tabel data Analisis What-If) dikenali sehingga dapat bolak-balik (round-trip) melalui file, tetapi ia dievaluasi sebagai placeholder karena hasil substitusi sebenarnya adalah nilai cache yang sudah disimpan Excel, dan HotXLS tidak menjalankan kembali kisi what-if

LET adalah batasan paling ketat yang perlu disebutkan. HotXLS mendaftarkan LET dan membawa jalur evaluasi untuknya, tetapi parser menolak nama kosong yang bukan nama yang sudah ditentukan, sehingga =LET(x;10;x) gagal saat penguraian sebelum evaluator tercapai. Perlakukan LET sebagai tidak didukung hingga parser mendapatkan kesadaran cakupan (scope awareness) untuk nama-nama yang terikat. Catatan portabilitas yang lebih kecil: string formula dalam contoh ini menggunakan pemisah daftar engine, yaitu titik koma, jadi sesuaikan pemisah apa pun yang diharapkan oleh build Anda saat menyusun teks formula. Fungsi array dinamis yang dibahas di sini, mulai dari XLOOKUP dan FILTER hingga SORT, UNIQUE, SEQUENCE, dan XMATCH, didistribusikan dalam HotXLS Delphi Excel Component, yang referensi formulanya mencantumkan katalog fungsi lengkap dan mode argumen yang didukung dari setiap fungsi