Matriks Opsi AGGREGATE dan Kebocoran Gate di HotXLS

HotXLS, komponen spreadsheet Excel native untuk Delphi dan C++Builder, mengirim dua perbaikan AGGREGATE yang saling berhubungan pada September 2026. Versi 2.382.0 membetulkan argumen options-nya supaya kode 1/3/5/7 mengabaikan baris tersembunyi, 2/3/6/7 mengabaikan error, dan 0 sampai 3 mengabaikan sel SUBTOTAL serta AGGREGATE bersarang, persis seperti yang didokumentasikan Microsoft. Versi 2.382.3 lalu menghentikan flag pemilihan itu bocor ke evaluasi sel-sel yang justru dirujuk fungsinya. Cacat pertama memalukan dengan cara yang selalu memalukan pada bug transkripsi tabel: posisi bit-nya tertukar, jadi setiap formula yang memakai kode options bukan nol mendapat kebijakan yang tidak diminta penulisnya. Yang kedua lebih menarik, karena itu bentuk yang akan Anda temui di evaluator mana pun yang memakai field sementara untuk mengantar konteks ke penelusuran rekursif. Sebuah agregasi luar mempersenjatai flag, menelusuri satu range, lalu menarik sel yang formulanya belum dihitung. Formula itu berjalan di kalkulator yang sama, melihat flag yang sama yang masih terpasang, dan diam-diam mengaggregasi baris yang salah, menghasilkan angka yang selisihnya tidak bisa dijelaskan siapa pun dari teks formulanya saja

Sebenarnya apa yang dipilih opsi AGGREGATE 0 sampai 7?

Argumen options AGGREGATE adalah matriks tiga bit, dan ketiga bit-nya independen. Bit 0 (nilai 1) berarti abaikan baris tersembunyi, bit 1 (nilai 2) berarti abaikan nilai error, dan bit 2 (nilai 4) berarti berhenti mengabaikan sel SUBTOTAL dan AGGREGATE bersarang, karena melewatinya adalah default untuk kode rendah. Dua hal soal ini mudah terbalik. Bit baris tersembunyinya adalah bit rendah, bukan bit tengah, jadi AGGREGATE(9,1,...) adalah bentuk total terfilter dan AGGREGATE(9,2,...) adalah bentuk yang toleran error. Dan kebijakan agregat bersarang terbalik dibandingkan dua yang lain: hanya kode 4 sampai 7 yang memperlakukan sel yang formulanya sendiri SUBTOTAL atau AGGREGATE sebagai nilai biasa. ECMA-376 Part 1 §18.17.7 mendefinisikan SUBTOTAL dengan pemisahan sertakan-atau-abaikan baris tersembunyi yang sama di kode 1-11 dan 101-111, dan AGGREGATE, yang disimpan di file OOXML dengan prefiks _xlfn., menggeneralisasi pemisahan itu ke dalam argumen options-nya, jadi tabel yang diterbitkan Microsoft untuk fungsi AGGREGATE adalah kontrak yang harus dipenuhi engine, bukan sekadar kemudahan

Opsi	Baris tersembunyi	Nilai error	SUBTOTAL / AGGREGATE bersarang
0	disertakan	dipropagasikan	diabaikan
1	diabaikan	dipropagasikan	diabaikan
2	disertakan	diabaikan	diabaikan
3	diabaikan	diabaikan	diabaikan
4	disertakan	dipropagasikan	disertakan
5	diabaikan	dipropagasikan	disertakan
6	disertakan	diabaikan	disertakan
7	diabaikan	diabaikan	disertakan

Kenapa HotXLS punya opsi AGGREGATE yang terbalik?

Karena TXLSCalculator.CalcAggregateFunc yang aslinya ditulis dari parafrase tabelnya, bukan dari tabelnya. Ia menghitung ignoreErrors := (optCode >= 4) and (optCode <= 7) dan mempersenjatai gate baris tersembunyi untuk kode 2, 3, 6, dan 7, sementara kebijakan agregat bersarangnya tidak diimplementasikan sama sekali. Artikel sebelumnya soal baris tersembunyi SUBTOTAL dan AGGREGATE mencantumkan celah itu sebagai batas yang masih terbuka dan menjelaskan pemetaan lamanya seperti yang saat itu dikirim; deskripsinya akurat soal kodenya dan salah soal Excel, dan tak ada yang menyadarinya untuk waktu yang lama karena dua kebijakan yang paling sering digabung orang, tersembunyi plus error, mendarat di kode 3 dan 7 pada kedua tabel. Hanya kode satu-bit yang membongkar pertukaran itu: AGGREGATE(9,1,A1:A4) mengembalikan jumlah yang tidak terfilter, dan AGGREGATE(9,2,...) melewati baris tersembunyi sambil tetap mempropagasikan #DIV/0!. Cacat itu muncul dari tinjauan statis atas lxCalc.pas, tercatat sebagai HXLS-008 di register known-issues proyek, bukan dari file pelanggan, dan itu mengatakan sesuatu soal betapa jarangnya kode satu-bit muncul di workbook produksi. Versi 2.382.0 menulis ulang decode-nya jadi tiga uji keanggotaan himpunan dan menambahkan gate kedua untuk kebijakan bersarang, disalurkan lewat callback baru TXLSIsSubtotalCell yang disediakan workbook bersama TXLSIsRowHidden

// TXLSCalculator.CalcAggregateFunc, bentuk v2.382.3
if (optCode < 0) or (optCode > 7) then
begin
  Result := lxErrorValue;            // Excel menolak kode di luar 0..7
  Exit;
end;
ignoreErrors := optCode in [2, 3, 6, 7];
prevIgnoreHidden := FIgnoreHiddenRows;
prevIgnoreSubtotal := FIgnoreSubtotalCells;
FIgnoreHiddenRows := (optCode in [1, 3, 5, 7]) and Assigned(FIsRowHidden);
FIgnoreSubtotalCells := (optCode in [0, 1, 2, 3]) and Assigned(FIsSubtotalCell);
try
  // ... petakan function_num ke iftab dalamnya, telusuri ref1..refN ...
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
  FIgnoreSubtotalCells := prevIgnoreSubtotal;
end;

Perhatikan bahwa kedua flag-nya ditugaskan tanpa syarat alih-alih hanya diset ketika opsinya memintanya. Versi 2.382.0 masih memakai if ... then FIgnoreHiddenRows := True, yang berarti AGGREGATE dengan kode 4 yang bersarang di dalam SUBTOTAL(109, ...) mewarisi gate baris tersembunyi luar alih-alih membersihkannya. Menugaskan nilai hasil decode saat masuk lalu memulihkan nilai sebelumnya di blok finally membuat setiap panggilan AGGREGATE memiliki kebijakannya sendiri selama penelusurannya dan tidak lebih. Versi 2.382.0 juga membuat bentuk array-nya jujur: ketika sebuah argumen dievaluasi jadi array Variant satu atau dua dimensi, CalcAggregateFunc sekarang menelusuri setiap elemen dan menerapkan kebijakan error per elemen, sedangkan kode lamanya hanya menguji double NaN dan selebihnya menyerahkan seluruh array-nya ke ExcelSum

Kenapa AGGREGATE luar bocor ke formula yang dirujuknya?

Karena FIgnoreHiddenRows dan FIgnoreSubtotalCells adalah field di kalkulatornya, dan kalkulatornya dipakai bersama oleh setiap formula yang dievaluasi selama satu kalkulasi ulang. Gate-nya dirancang sebagai field scratch justru supaya enam loop penelusuran sel bisa menyimaknya tanpa mengalirkan parameter ke setiap signature, dan rancangan itu sehat selama segala sesuatu yang berjalan saat gate terpasang milik agregasi yang memasangnya. Asumsinya patah di satu titik spesifik: FGetValue. Ketika seorang walker meminta nilai sel ke workbook dan sel itu memuat formula tanpa hasil cache, workbook-nya mengompilasi formula itu dan mengevaluasinya saat itu juga, di TXLSCalculator yang sama, dengan gate luarnya masih terpasang. Fixture regresi di HotXLS.WorkbookApiTests.pas memperlihatkan kegagalannya dengan empat sel. A1 berisi 10, A2 berisi 20 di baris tersembunyi, A3 berisi =1/0, dan A4 berisi =SUBTOTAL(9,A1:A2), yang nilai benarnya 30. Sekarang evaluasi =AGGREGATE(9,7,A1:A4): abaikan baris tersembunyi, abaikan error, hitung subtotal bersarangnya sebagai nilai. Excel mengembalikan 10 + 30 = 40. Dengan A4 yang belum di-cache, engine pra-2.382.3 memasang gate baris tersembunyi, menelusuri ke A4, memicu evaluasinya, dan CalcSubtotalFunc untuk kode 9 mewarisi gate yang terpasang, karena ia hanya pernah menyetel flag itu untuk kode 101 sampai 111 dan tidak pernah membersihkannya. A4 dievaluasi jadi 10 alih-alih 30, dan total luarnya kembali sebagai 20. Tidak ada apa pun di kedua formula itu yang menyebut baris tersembunyi di jalur yang menghasilkan angka salah itu

Gate agregat bersarang bocor dengan cara yang sama ke arah sebaliknya. Dengan kode 0 sampai 3, FIgnoreSubtotalCells terpasang, dan walker range generik di GetValueItemRange menuruti itu, jadi preseden yang formulanya =SUM(B1:B3) akan diam-diam membuang B2 kalau B2 kebetulan berisi SUBTOTAL. Lebih buruk lagi, CalcSubtotalFunc mereset FIgnoreSubtotalCells ke False saat keluar alih-alih memulihkan nilai sebelumnya, jadi preseden SUBTOTAL yang belum di-cache dan tersentuh di tengah penelusuran mematikan gate luar untuk setiap sel sesudahnya. Register known-issues proyek mengarsipkan ini di bawah HXLS-008 sebagai kebocoran state pemilihan bersarang, dan itu nama yang tepat untuk kelas bug-nya: flag sementara global yang benar untuk frame yang memasangnya dan salah untuk setiap frame yang mewarisinya

Bagaimana AggregateGetCellValue dan AggregateGetItemValue mengisolasi penelusurannya

Perbaikan di v2.382.3 menaruh batas di sekitar setiap titik di mana AGGREGATE membaca nilai yang tidak dihitungnya sendiri. TXLSCalculator.AggregateGetCellValue membungkus panggilan FGetValue mentahnya: ia menyimpan kedua flag-nya, membersihkannya, melakukan pengambilannya, lalu memulihkannya di blok finally. Agregasi luarnya tetap menerapkan kebijakannya sendiri pada sel yang baru diambilnya itu, karena uji baris tersembunyi dan sel bersarangnya terjadi di walker-nya di sekitar pengambilan tersebut, tapi formula presedennya sendiri berjalan tanpa kebijakan apa pun, dan itulah yang dilakukan Excel

function TXLSCalculator.AggregateGetCellValue(SheetIndex, Row, Col: Integer;
  var Value: Variant; var OutOfRange: Boolean): Integer;
var
  Hidden, Nested: Boolean;
begin
  Hidden := FIgnoreHiddenRows;
  Nested := FIgnoreSubtotalCells;
  FIgnoreHiddenRows := False;        // formula preseden memiliki kebijakannya sendiri
  FIgnoreSubtotalCells := False;
  try
    Result := FGetValue(SheetIndex, Row, Col, Value, OutOfRange);
  finally
    FIgnoreHiddenRows := Hidden;
    FIgnoreSubtotalCells := Nested;
  end;
end;

AggregateGetItemValue melakukan hal yang sama untuk argumen non-range, dan ia harus berbuat lebih dari sekadar membersihkan flag, karena argumen seperti A1:A4/(B1:B4-20) adalah array hasil komputasi yang bentuk elemennya harus tetap terjaga. Wrapper-nya mewujudkan range polos jadi array Variant dua dimensi lewat AggregateGetCellValue, memetakan sel yang mengembalikan kode error ke VarAsError supaya kebijakan error-nya masih bisa diterapkan per elemen, dan ia berulang menembus node operator biner dan uner (SA_ADD, SA_DIV, SA_UNARMINUS, dan sisanya) dengan ApplyArrayBinaryOp dan ApplyArrayUnaryOp; apa pun selain itu jatuh ke GetValueItem yang normal. Dua penjaga duduk di depan pewujudannya: range yang lebih besar dari EffectiveFormulaArrayMemoryLimit mengembalikan lxErrorResourceLimit, dan range multi-sheet atau range terbalik mengembalikan #VALUE!. Kode batas resource sengaja tidak diperlakukan sebagai error sel yang bisa diabaikan bahkan di bawah opsi 2/3/6/7, karena engine yang menelan sinyal out-of-memory-nya sendiri gara-gara pengguna meminta melewati #N/A itu berbohong. Ketiga walker AGGREGATE, AggregateCollectRange untuk keluarga SUM, AggregateReduceVariance untuk STDEV, VAR, dan PRODUCT, serta AggregateReduceWithK untuk MEDIAN dan bentuk kuantilnya, semuanya dialihkan dari FGetValue dan GetValueItem ke kedua wrapper itu, dan masing-masing mendapat uji sel bersarang lewat FIsSubtotalCell

Error mana yang dikembalikan AGGREGATE ketika ia tidak mengabaikan error?

Error aslinya, sejak v2.382.3. Versi 2.382.0 mendeteksi sel error dengan benar tapi melipat semuanya jadi lxErrorValue, jadi AGGREGATE(9,4,A1:A3) atas sel #DIV/0! mengembalikan #VALUE!, padahal Excel mempropagasikan error pertama yang ditemuinya tanpa diubah. Helper pengganti AggregateErrorCode memetakan Variant ke kode lxError* yang cocok, entah Variant-nya varError sungguhan atau salah satu dari tujuh string error, dan AggregateValueIsError sekarang sekadar uji hasil bukan nol. Setiap walker mencatat kode error pertama yang dilihatnya dan mengembalikan kode itu, yang juga berarti sel yang formulanya tidak pernah dihitung, dan karena itu error-nya datang sebagai kode balik dari FGetValue alih-alih sebagai Variant yang di-cache, terpropagasi dengan cara yang sama seperti yang di-cache. Dua fungsi penghitung mendapat perlakuan khusus di dalam AggregateCollectRange, dan perlakuannya mengikuti SUBTOTAL alih-alih SUM. Untuk fungsi dalam 0, COUNT, sel error tidak pernah dihitung dan tidak pernah dipropagasikan terlepas dari kode options-nya, karena COUNT hanya menghitung angka. Untuk fungsi dalam 169, COUNTA, sel error adalah nilai tak kosong dan dihitung sebagai 1 kecuali kode options-nya mengabaikan error, dan dalam hal itu ia dilewati. Asimetri itulah cara Excel memperlakukan COUNT dan COUNTA di luar AGGREGATE juga, dan itu jenis detail yang diam-diam disalahkan oleh aturan generik "kalau error maka propagasikan"

Apa yang diverifikasi matriks regresi delapan opsi

Fixture yang dijelaskan di atas dijalankan sebagai matriks penuh di AggregateFunc_OptionMatrixCoversHiddenErrorsAndNestedAggregates: untuk setiap kode options dari 0 sampai 7 ia mengevaluasi bentuk SUM dan bentuk MEDIAN atas A1:A4 lalu memeriksa hasilnya terhadap ekspektasi yang diturunkan tangan. Kode 0, 1, 4, dan 5 harus mempropagasikan #DIV/0! dari A3, karena tidak satu pun dari itu mengabaikan error. Kode 2 memberi SUM 30 dan MEDIAN 15, dari 10 dan 20 dengan A4 yang bersarang dilewati. Kode 3 memberi 10 dan 10. Kode 6 memberi 60 dan 20, karena 30 di A4 kini terhitung. Kode 7 memberi 40 dan 20, dan itulah kasus yang mengembalikan 20 sebelum perbaikan kebocorannya. Rangkaian penerimaan yang lebih luas yang tercatat di register known-issues mencakup kesembilan belas nomor fungsi terhadap kedelapan kode, dengan setiap preseden dalam keadaan ter-cache maupun belum ter-cache, untuk 304 skenario di Win32 dan Win64

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    Sheet.Cells[1, 1].Value := 10;
    Sheet.Cells[2, 1].Value := 20;
    Sheet.Cells[3, 1].Formula := '=1/0';
    Sheet.Cells[4, 1].Formula := '=SUBTOTAL(9,A1:A2)';   // subtotal grup = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  tersembunyi dilewati, error dipropagasikan
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       tersembunyi + error + bersarang dilewati
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       hanya error yang dilewati
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       bernilai 20 sebelum v2.382.3
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Di mana batasnya masih ada

Tiga batas layak diketahui sebelum Anda membangun di atas ini. Pertama, predikat agregat bersarangnya tekstual. TXLSXWorkbook.GetCalcIsSubtotalCell dan kembarannya di engine klasik menjawab True ketika formula sebuah sel diawali SUBTOTAL(, AGGREGATE(, atau _xlfn.AGGREGATE(, dengan atau tanpa tanda sama dengan di depan, jadi formula seperti =IF(C1,SUBTOTAL(9,B1:B9),0) atau =SUBTOTAL(9,B1:B9)*2 tidak dikenali sebagai bersarang dan akan dihitung ganda oleh kode 0 sampai 3 di tempat Excel akan melewatinya; generator yang mengeluarkan subtotal hasil komputasi sebaiknya menaruh panggilan agregasinya di kepala formulanya. Kedua, isolasinya ada di ketiga walker AGGREGATE. CalcSubtotalFunc masih menelusuri lewat GetValueItemRange, CollectRangeValues, dan SubtotalReduceVariance, yang memanggil FGetValue langsung, jadi SUBTOTAL(109, ...) yang range-nya memuat formula preseden belum ter-cache masih bisa meneruskan gate baris tersembunyinya ke preseden itu. Recalculate penuh mengevaluasi preseden sebelum dependennya, jadi jalur yang di-cache yang diambil dan gate-nya tidak pernah diwarisi; paparannya terbatas pada evaluasi ad hoc lewat Calculate dan pada workbook yang dimuat tanpa nilai cache, dan kalau Anda mengandalkan kalkulasi ulang inkremental atas dependency graph untuk menjaga model besar tetap responsif, jaminan urutan yang sama itulah yang menjaga kebocoran ini tetap dorman. Ketiga, kedua gate-nya bersyarat pada Assigned(FIsRowHidden) dan Assigned(FIsSubtotalCell). Kedua facade workbook-nya menyambungkan callback itu di konstruktornya, tapi kode yang membangun TXLSCalculator dengan tangan hanya dengan dua argumen aslinya akan mendapat perilaku lama yang menyertakan segalanya untuk setiap kode options, tanpa suara. Ketika sebuah total terlihat salah sementara teks formulanya terlihat benar, menelusuri evaluasinya langkah demi langkah adalah cara tercepat untuk melihat apakah sebuah preseden dievaluasi di bawah gate yang diwarisi atau apakah sebuah callback memang tidak pernah tersambung

Engine kalkulasi yang dijelaskan di sini, decoder opsinya, wrapper pengambilan yang terisolasi, dan matriks regresi yang memakunya semuanya dikirim sebagai source bersama komponen spreadsheet HotXLS untuk Delphi, yang membaca, menulis, dan menghitung ulang workbook XLS, XLSX, dan ODS di Delphi dan C++Builder tanpa perlu instalasi Excel