Untuk menghitung referensi melingkar yang disengaja di Delphi, HotXLS mengekspos perhitungan iteratif pada engine XLSX-nya: atur TXLSXWorkbook.Iterate ke True dan TXLSXWorkbook.Recalculate menelusuri setiap siklus referensi yang terdeteksi menuju titik tetap — hingga IterateCount lintasan, atau sampai setiap sel berubah kurang dari IterateDelta — alih-alih mengembalikan #REF! dan menyerah
Perbedaan tersebut lebih penting daripada yang ditunjukkan oleh satu boolean saja. Engine yang sama yang mendeteksi siklus referensi dan menolak untuk berulang padanya akan, dengan satu properti yang dibalik, mengevaluasi siklus tersebut secara sengaja hingga stabil. Memahami kedua perilaku tersebut dengan jelas — kapan siklus merupakan cacat yang harus dilaporkan dan kapan siklus merupakan model yang harus diselesaikan — adalah seluruh bahasan dari artikel ini
Mengapa referensi melingkar secara bawaan menghasilkan kesalahan?
Secara bawaan HotXLS memperlakukan siklus referensi apa pun sebagai kesalahan penulisan dan melaporkannya alih-alih menghitungnya. TXLSXWorkbook.Recalculate membangun grafik ketergantungan formula, mengevaluasi setiap sel formula dalam urutan topologis, dan mengembalikan lxErrorRef saat menemukan siklus – simpul yang tidak pernah dapat dilepaskan selama pengurutan topologis. Anggota siklus tersebut mempertahankan nilai cache mereka sebelumnya; setiap formula di luar siklus tetap dievaluasi secara normal. Mekanisme grafik tersebut, dan mengapa anggota siklus dilewati alih-alih berulang, dibahas dalam artikel pendamping tentang perhitungan ulang formula bertahap dan grafik ketergantungan
Setelan bawaan adalah setelan yang aman karena sebagian besar siklus adalah bug: baris ringkasan yang tidak sengaja dimasukkan ke dalam rentang SUM-nya sendiri, atau salin-tempel yang menggeser referensi ke dirinya sendiri. Kode kesalahan yang jelas pada saat perhitungan ulang adalah apa yang Anda inginkan untuk kasus-kasus tersebut. Namun, kelas model yang spesifik dan penting memang sengaja dibuat melingkar. Jadwal bunga-berbunga, alokasi biaya melingkar atau overhead antar departemen, dan perhitungan biaya berdasarkan saldo semuanya menggambarkan nilai yang secara sah mengumpan balik ke inputnya sendiri — dan Excel menghitungnya hanya setelah pengguna mencentang File → Pilihan → Formula → Aktifkan perhitungan iteratif
Bagaimana cara mengaktifkan perhitungan iteratif di HotXLS?
HotXLS mencerminkan kotak centang Excel tersebut dengan tiga properti pada TXLSXWorkbook: Iterate mengaktifkan mode tersebut, dan ketika bernilai True, siklus yang terdeteksi diserahkan ke solver iteratif alih-alih menghasilkan lxErrorRef. Pertimbangkan pasangan sel bunga-berbunga kanonis, di mana saldo penutupan bergantung pada bunga dan bunga bergantung pada saldo
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Model');
Sheet.Cells[1, 2].Value := 1000; // B1: opening principal
Sheet.Cells[2, 2].Value := 0.05; // B2: period rate
Sheet.Cells[3, 2].Formula := 'B1+B4'; // B3: balance = principal + interest
Sheet.Cells[4, 2].Formula := 'B3*B2'; // B4: interest = balance * rate
Book.Iterate := True; // opt in to iterative calculation
if Book.Recalculate = lxOk then
// B3 converges to 1052.63..., B4 to 52.63...
Report(Sheet.Cells[3, 2].Value);
finally
Book.Free;
end;
end;B3 merujuk ke B4 dan B4 merujuk ke B3, sehingga grafik ketergantungan melaporkan siklus dua simpul. Dengan Iterate dibiarkan pada bawaannya yaitu False, pasangan tersebut akan kembali sebagai lxErrorRef dan tidak ada sel yang akan stabil. Dengan bernilai True, Recalculate menyemai siklus dari nilai cache saat ini dan mengevaluasi kembali anggotanya lintasan demi lintasan, mengumpankan balik output setiap lintasan sebagai input lintasan berikutnya, hingga angka-angkanya berhenti bergerak. Di sini bentuk tertutupnya adalah principal / (1 - rate), sehingga saldo stabil di 1052.63 dan bunga di 52.63 — angka yang sama dengan yang dihasilkan Excel dengan iterasi yang diaktifkan
Apa yang membuat iterasi berhenti?
Dua kondisi henti independen membatasi solver, dan memahami keduanya adalah apa yang mencegah model mengalami kesalahan atau berputar tanpa henti. IterateCount adalah batas atas yang ketat tentang berapa kali anggota siklus dievaluasi kembali; bawaannya adalah 100, sama seperti Excel. IterateDelta adalah ambang batas konvergensi: setelah setiap lintasan, solver mengukur perubahan numerik terbesar di seluruh sel siklus, dan setelah perubahan maksimum tersebut turun di bawah IterateDelta — bawaan 0.001 — perulangan lintasan akan berhenti lebih awal. Kondisi mana pun yang terpenuhi terlebih dahulu akan mengakhiri iterasi
Book.Iterate := True;
Book.IterateCount := 1000; // hard cap: at most 1000 passes over the cycle
Book.IterateDelta := 0.0001; // convergence: stop once every cell moves < 0.0001
case Book.Recalculate of
lxOk:
// the cycle converged, OR it hit the 1000-pass cap and kept its
// last-iteration values -- both paths return lxOk when Iterate is True
SaveWorkbook(Book);
lxErrorRef:
// only reachable with Iterate = False: the cycle was reported, not solved
LogWarning('Circular reference with iteration disabled');
end;
Satu konsekuensi patut dinyatakan secara jelas, karena ini adalah batasan jujur dari fitur ini. Ketika batas atas tercapai tanpa perubahan turun di bawah IterateDelta, SolveCycleIteratively tidak memunculkan kesalahan — ia mengembalikan lxOk dan membiarkan sel-sel menyimpan nilai iterasi terakhir mereka, persis seperti Excel menulis angka komputasi terakhir ketika batas iterasinya sendiri tercapai tanpa konvergensi. Jadi kode pengembalian sukses dari Recalculate di bawah mode iteratif berarti "solver berjalan", bukan "solver konvergen". Model yang putaran umpan baliknya divergen atau berosilasi akan secara diam-diam menghabiskan semua lintasan IterateCount dan mengembalikan angka yang sama sekali bukan titik tetap, dan tidak ada pengecualian yang menandai perbedaan tersebut
Bagaimana pengaturan disimpan ke file XLSX dan XLS?
Pengaturan perhitungan iteratif tetap ada di kedua format spreadsheet, sehingga workbook yang dibuka di Excel berperilaku seperti yang dikonfigurasi oleh kode Delphi Anda. Di sisi XLSX, writer memancarkan elemen OOXML <calcPr> hanya ketika Iterate bernilai True, dan mengabaikan atribut apa pun yang masih pada bawaannya untuk menjaga output tetap minimal: workbook yang menggunakan bawaan hanya menulis <calcPr iterate="1"/>, sedangkan iterateCount hanya muncul ketika berbeda dari 100 dan iterateDelta hanya ketika berbeda dari 0.001. Saat dibuka, TXLSXWorkbook membaca kembali tiga atribut yang sama, sehingga round-trip berjalan simetris
Engine warisan BIFF8 (.xls), TXLSWorkbook, membawa status setara melalui tiga rekaman terpisah di bawah tiga properti yang berbeda. EnableIteration memetakan ke rekaman CalcIter ($0011, [MS-XLS] §2.4.33), MaxIterations ke rekaman CalcCount ($000C, [MS-XLS] §2.4.31), dan MaxIterationChange ke rekaman CalcDelta ($0010, [MS-XLS] §2.4.32). Setter menerapkan rentang spesifikasi — CalcCount harus berada dalam 1..32767, sehingga MaxIterations dijepit, dan MaxIterationChange yang negatif akan kembali ke bawaan 0.001. Atur mereka pada workbook .xls yang dimuat dan ketiga rekaman kalkulasi tersebut akan ditulis secara akurat saat disimpan
var
Book: TXLSWorkbook; // BIFF8 (.xls) engine
begin
Book := TXLSWorkbook.Create;
try
Book.Open('model.xls');
Book.EnableIteration := True; // CalcIter record $0011
Book.MaxIterations := 500; // CalcCount record $000C (clamped to 1..32767)
Book.MaxIterationChange := 0.0001; // CalcDelta record $0010
Book.SaveAs('model.xls'); // the three calc records round-trip
finally
Book.Free;
end;
end;Perhatikan pembagian penamaan yang disengaja: engine XLSX menggunakan istilah Iterate / IterateCount / IterateDelta (kosakata OOXML), sedangkan engine BIFF8 menggunakan istilah EnableIteration / MaxIterations / MaxIterationChange (diselaraskan dengan nama rekaman [MS-XLS]). Kedua set properti tersebut menjelaskan tiga tombol yang sama — sakelar hidup/mati, batas iterasi, dan delta konvergensi — dengan nilai bawaan yang sama, yaitu mati, 100, dan 0.001
Kapan referensi melingkar merupakan bug dan bukan model?
Mengaktifkan iterasi bukanlah cara untuk make circular-reference warnings disappear, and treating it that way is the trap. Mengaktifkan Iterate secara global akan mengubah setiap siklus yang tidak disengaja — siklus yang seharusnya ditangkap oleh kode kesalahan bawaan — menjadi angka yang konvergen secara diam-diam atau tidak konvergen secara diam-diam. Aturan disiplinnya adalah sebaliknya: biarkan Iterate bernilai False sebagai mode normal sehingga kesalahan penulisan yang sebenarnya tetap muncul sebagai lxErrorRef, dan aktifkan iterasi hanya pada workbook yang melingkar secara sengaja dan telah dipahami
Ketika sebuah siklus muncul dan Anda tidak yakin jenis apa itu, the formula evaluation tracer is the tool that tells them apart: trace the suspect formula and the reference chain that folds back on itself becomes visible step by step, so you can decide whether it encodes a real feedback loop or a stray self-reference. Ingat juga bahwa sel siklus dapat memanggil fungsi bawaan apa pun saat berputar di dalam loop — kalkulator yang sama yang menyelesaikan formula teknik atau bilangan kompleks mengevaluasi anggota siklus pada setiap lintasan — sehingga model yang divergen sering kali merupakan masalah formula di dalam loop, bukan masalah pengaturan iterasi
Daftar periksa praktisnya singkat. Pastikan putaran memiliki titik tetap yang nyata sebelum Anda mengandalkan iterasi; jaga agar IterateDelta cukup ketat sehingga "konvergen" berarti apa yang dibutuhkan model Anda; dan setelah Recalculate yang Anda harapkan konvergen, periksa kembali output yang diketahui daripada hanya mempercayai lxOk saja, karena kode tersebut tidak dapat membedakan konvergensi dari batas iterasi yang tercapai
Perhitungan referensi melingkar iteratif adalah bagian dari engine XLSX di HotXLS Delphi Excel Component, bersama dengan perhitungan ulang grafik ketergantungan bertahap yang menjadi dasarnya serta persistensi OOXML dan BIFF8 yang membawa pengaturan tersebut ke dalam setiap file yang Anda tulis. Untuk model keuangan dan teknik yang melingkar secara sengaja, ini adalah perbedaan antara kode kesalahan dan sebuah jawaban