Mengaudit Cache Formula Excel dengan Deep Recalc HotXLS

HotXLS menjawab pertanyaan yang cepat atau lambat wajib diajukan setiap pipeline spreadsheet: apakah angka yang tersimpan di workbook masih cocok dengan formula yang menghasilkannya. CalculateAndVerify menghitung ulang seluruh dependency graph ke dalam overlay terisolasi, membandingkan tiap hasil dengan nilai cache yang sudah ada di sel, dan melaporkan ketidakcocokannya. Secara bawaan ia tidak mengubah apa pun

Alasan ini penting: file spreadsheet menyimpan dua hal per sel formula — formula itu sendiri dan nilai terakhir yang pernah dihitung seseorang untuknya. Excel menjaga keduanya tetap sinkron. Segala hal lain di dunia boleh jadi tidak. File yang lewat tangan pustaka lama, perhitungan ulang parsial, bagian XML yang disunting manual, atau tool yang menulis nilai tanpa menghitung ulang dengan senang hati menyajikan total yang tak lagi mengikuti dari inputnya, dan tak ada apa pun di format file yang menandai hal itu

Mengapa nilai cache yang tak cocok dengan formula-nya begitu berbahaya?

Karena ia tak terlihat di semua jalur pembacaan biasa. Buka file di viewer, baca sel lewat API, ekspor ke CSV atau PDF, dan Anda mendapat angka cache-nya. Formula ada di sana di sel yang sama, dan tak ada yang membandingkan keduanya. Ketidakcocokan baru muncul ketika seseorang membuka workbook di Excel — yang menghitung ulang saat memuat di bawah kebanyakan setting — dan tiba-tiba laporan yang ditandatangani kuartal lalu menampilkan total yang berbeda

Audit ini ada untuk menjadikan perbandingan itu operasi yang disengaja dan terjadwal, bukan kecelakaan. Ia padanan spreadsheet dari verifikasi checksum: cukup murah untuk dijalankan di pipeline intake, dan satu-satunya yang mengubah masalah integritas data yang senyap menjadi laporan yang bisa Anda tindaklanjuti

var
  Book: TXLSWorkbook;
  Options: TXLSRecalcAuditOptions;
  Report: TXLSCalculationAuditReport;
  I: Integer;
begin
  Book := TXLSWorkbook.Create(nil);
  try
    Book.LoadFromFile('quarterly-close.xls');
    Options := TXLSRecalcAuditOptions.Default;
    Options.MaxIssues := 500;
    Report := Book.CalculateAndVerify(Options);
    try
      for I := 0 to Report.Count - 1 do
        if Report[I].Kind = xlcaiCacheMismatch then
          Writeln(Report[I].SheetName, '!',
                  Report[I].Row, ':', Report[I].Col, '  ',
                  Report[I].Formula,
                  '  cached=', VarToStr(Report[I].Actual),
                  '  recomputed=', VarToStr(Report[I].Expected));
      if Report.Truncated then
        Writeln('issue budget reached, raise MaxIssues');
    finally
      Report.Free;
    end;
  finally
    Book.Free;
  end;
end;

Ada tiga overload dan ketiganya menjawab tiga pertanyaan berbeda. CalculateAndVerify tanpa parameter mengembalikan hitungan ketidakcocokan — semua yang dibutuhkan health check. Overload dengan out array ketidakcocokan memberi Anda sel-selnya. Overload yang menerima TXLSRecalcAuditOptions mengembalikan TXLSCalculationAuditReport penuh, dan itulah yang dicari ketika Anda perlu tahu bukan cuma bahwa sebuah nilai tak cocok, tapi mengapa audit tak bisa mengevaluasi sesuatu

Overlay, dan mengapa audit tidak menulis

Setiap nilai hasil hitung ulang mendarat di overlay alih-alih di cache sel, dan overlay disuntikkan di paling depan callback pembacaan sel di kedua engine workbook. Penempatan itulah yang membuat audit ini konsisten dengan dirinya sendiri: ketika B1 dihitung ulang dan C1 bergantung pada B1, C1 melihat nilai dari pass audit ini, bukan yang cache basi. Tanpa itu, satu error di hulu dilaporkan sekali lalu terserap, dan setiap sel di hilir tampak sepakat dengan input yang salah

Sel yang nilai hitung ulangnya cocok dengan cache tidak masuk overlay sama sekali. Itu bukan micro-optimization — itulah yang menjaga audit tetap terjangkau. Workbook bersih dengan seratus ribu formula melakukan nol penulisan overlay dan passnya bertahan di dalam anggaran 1.35x dibanding perhitungan ulang penuh, dan itulah selisih antara sesuatu yang bisa dijalankan di setiap intake dan sesuatu yang dijalankan sekali per kuartal

Evaluasi mengikuti urutan topologis serial yang diturunkan dari dependency graph, dengan tiap node ditandai dirty lebih dulu, sehingga tiap sel dihitung persis sekali setelah input-inputnya. Kalau yang Anda inginkan adalah mesin inkremental yang menjaga workbook yang hidup tetap mutakhir alih-alih mengaudit yang tersimpan, itu mekanisme berbeda, dibahas di perhitungan ulang inkremental dan dependency graph

Kegagalan diklasifikasikan, bukan dicampur jadi satu

Sel yang tak bisa dievaluasi audit bukan temuan yang sama dengan sel yang nilainya tak cocok, dan TXLSCalculationAuditIssueKind menjaga kategori-kategori itu terpisah. xlcaiCacheMismatch adalah ketidakcocokan nilai. xlcaiMissingFunction dan xlcaiMissingName menyatakan evaluator menemui sesuatu yang tidak diimplementasikannya atau tak bisa di-resolve. xlcaiUnsupportedArguments mencakup bentuk argumen di luar subset yang didukung. xlcaiExternalReferenceDenied dan xlcaiExternalReferenceMissing memisahkan penolakan kebijakan dari workbook yang tidak ada. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled dan xlcaiInternalFailure melengkapi himpunan itu

Satu distingsi layak dinyatakan karena membalik asumsi umum. Kode error Excel positif adalah hasil, bukan kegagalan. Sel yang sah mengevaluasi ke #DIV/0! telah menghitung dengan benar, jadi audit menyimpan error itu di overlay dan membandingkannya dengan cache seperti nilai lain. Workbook penuh sel yang sengaja error menghasilkan nol temuan, dan workbook yang errornya muncul atau hilang sejak nilainya di-cache menghasilkan persis temuan yang Anda inginkan

Referensi sirkular mendapat perlakuan sendiri. Node di dalam siklus tak pernah masuk urutan topologis, jadi masing-masing dilaporkan individual sebagai xlcaiCircularReference, dan audit tidak menjalankan solver iteratif. Itu kontrak baca-saja yang disengaja: apakah iterasi diaktifkan mempengaruhi bagaimana kode hasil seharusnya ditafsirkan, bukan apa yang dilakukan audit. Mekanika evaluasi iteratif dibahas terpisah di perhitungan iteratif dan referensi sirkular

Membaca rantai kegagalan

Ketika sebuah formula gagal dievaluasi, tahu sel mana yang gagal nyaris tak pernah cukup, karena kegagalan biasanya berada tiga level di bawah rantai referensi. Karena itu tiap isu membawa string Stack yang dirender frame terluar lebih dulu, dalam bentuk Sheet1!A1 > Sheet1!B2 > Data!C7, sehingga laporan menunjuk sel yang benar-benar patah alih-alih sel yang kebetulan Anda lihat

Rekordernya berbatas. MaxStackFrames bawaannya 64 dengan batas bawah 8, dan rantai gagal terdalam adalah yang disimpan: frame dalam merekam rantai ketika kegagalan berasal dari sana, dan frame luar yang unwinding setelahnya tidak menimpanya. Kalau rantai mana pun melampaui anggaran, Report.StackTruncated diset, yang memberi tahu selisih antara rantai pendek dan rantai yang tidak Anda lihat seluruhnya

// Baca-saja secara bawaan. ApplyResults meng-commit overlay hanya setelah
// audit yang sukses penuh, di bawah write guard yang menolak commit
// jika struktur workbook berubah selama audit berjalan
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // perbandingan persis, memunculkan drift
Options.RelativeTolerance := 0;
Options.OnProgress := HandleProgress;

Report := Book.CalculateAndVerify(Options);
try
  if Report.Applied then
    Book.SaveToFile('quarterly-close-repaired.xls')
  else
    Writeln('not applied: ', Report.Count, ' issues blocked the commit');
finally
  Report.Free;
end;

procedure THarness.HandleProgress(ASender: TObject;
  ACurrent, ATotal: Integer; var ACancel: Boolean);
begin
  ACancel := FUserRequestedStop;   // audit berhenti di batas node berikutnya
end;

Kapan Anda sebaiknya membiarkan audit memperbaiki workbook?

Hanya ketika audit kembali benar-benar bersih dari isu kelas kegagalan — persis kondisi yang ApplyResults tegakkan untuk Anda. Commit terjadi setelah pass yang sukses penuh, tidak dibatalkan, dan lolos guard struktural: engine biner mengawasi identifier perubahan workbook, engine OOXML mengambil snapshot generasi struktur per worksheet. Kalau ada apa pun yang bergerak selama audit berjalan, hasilnya mendeskripsikan workbook yang tak lagi ada dan commit ditolak

Perhatikan asimetrinya yang disengaja. Ketidakcocokan cache tidak memblokir penerapan, karena itulah persisnya yang ada untuk diperbaiki oleh commit. Isu kelas kegagalan memblokirnya, karena workbook yang sebagian formulirnya tak bisa dievaluasi akan terbenahi separuh, dan workbook setengah benahi lebih buruk daripada yang tak dibenahi yang Anda tahu untuk tidak dipercaya

Toleransi adalah keputusan kebijakan, bukan bawaan

Perbandingan bawaannya adalah toleransi absolut 1E-6 dengan toleransi relatif dimatikan, yang mempertahankan perilaku klasik dan diam-diam menerima drift 4E-7. Itu biasanya benar: perbedaan urutan evaluasi floating-point antara apa pun yang menghasilkan file dan evaluator saat ini akan menghasilkan selisih sebesar itu pada penjumlahan panjang, dan melaporkannya sebagai temuan integritas adalah derau

Nolkan kedua toleransi ketika pertanyaannya berbeda — ketika Anda mencari tahu apakah sebuah evaluator mengubah perilaku antar versi, atau apakah tool pihak ketiga menulis ulang nilai dengan cara yang halus berbeda. Pada nol, drift 4E-7 yang sama menjadi terlihat, dan begitu pula segala hal lain. Pilih toleransi berdasarkan pertanyaan mana yang Anda ajukan, dan catat pilihannya di samping laporannya, karena laporan tanpa toleransinya tidak bisa ditafsirkan

Dua kemampuan tetangga melengkapi gambarannya. Ketika Anda ingin tahu mengapa satu formula menghasilkan nilai yang dihasilkannya, tampilan langkah demi langkah di tracer evaluasi formula adalah tool yang tepat. Ketika Anda sengaja ingin nilai cache dihormati tanpa hitung ulang apa pun — misalnya di jalur intake yang harus mereproduksi file persis seperti saat tiba — mode itu dideskripsikan di membaca nilai formula cache tanpa menghitung ulang. Auditlah yang duduk di antara keduanya: ia memberi tahu apakah memercayai cache itu aman. Audit tersedia dalam komponen spreadsheet Delphi HotXLS untuk kedua engine biner dan OOXML