Pemindaian Lookup HotXLS dan Referensi Sirkular Palsu

Letakkan =VLOOKUP(A1,B:B,1) di sel pada kolom B dan Excel menghitungnya tanpa keluhan. Berikan workbook yang sama ke engine rekalkulasi berbasis graph dependensi dan Anda kemungkinan besar mendapat error referensi sirkular, karena formula tersebut bergantung pada rentang yang memuat formula itu. HotXLS melaporkan persis itu sampai v2.361.98. Perbaikannya bukan kasus khusus untuk rentang satu kolom penuh; melainkan distinksi antara dua jenis edge dependensi yang dibutuhkan engine spreadsheet dan tidak dimiliki graph berarah polos

Argumen lookup-array dari keluarga lookup, LOOKUP, MATCH, HLOOKUP, VLOOKUP, XLOOKUP, dan XMATCH, kini ditandai sebagai scan reference. Scan reference tetap menyemai kotor, sehingga menyunting sel di dalam rentang tetap merekalkulasi formula itu, tetapi tidak pernah berkontribusi pada deteksi siklus maupun pengurutan evaluasi. Siklus yang sungguhan tetap ditemukan; yang palsu hilang

Mengapa Excel mengizinkan rentang lookup memuat formula itu?

Karena argumen itu tidak dikonsumsi sebagaimana operand aritmetika. Keluarga lookup memindai rentang untuk nilai yang di-cache dan mengembalikan kecocokan; ia tidak mensyaratkan rentang sudah dievaluasi sampai tuntas lebih dulu. Excel memperlakukan rentang lookup yang tumpang-tindih dengan dirinya sebagai membaca apa pun yang sel-sel itu pegang saat ini, semantik yang sama yang diterapkannya pada workbook non-iteratif mana pun: sel yang belum direkalkulasi dalam lintasan ini menyumbang nilai hasil hitung terakhirnya

Referensi satu kolom penuh menjadikan ini kasus yang lazim, bukan yang eksotis. B:B adalah cara idiomatis menulis "seluruh tabel lookup" pada sheet yang barisnya ditambah terus, dan formula apa pun yang berdiam di kolom B kini berada di dalam rentang lookup-nya sendiri. Model keuangan, sheet rekonsiliasi, dan workbook audit melakukan ini terus-menerus, biasanya tanpa ada yang menyadari rentangnya tumpang-tindih

Apa yang dilakukan graph dependensi pada formula yang sama

HotXLS merekalkulasi secara inkremental, yang mensyaratkan graph dependensi sungguhan: simpul untuk sel, edge untuk referensi, urutan topologis untuk evaluasi, dan lintasan strongly connected component untuk mengklasifikasi siklus. Mesineri itu dijelaskan dalam artikel rekalkulasi inkremental, dan itulah tepatnya alasan false positive muncul

Ekstrak dependensi dari =VLOOKUP(A1,B:B,1) di sel B7 dan argumen kedua menghasilkan rentang yang memuat B7 sendiri. Graph kini punya self-loop. In-degree simpul itu tidak pernah mencapai nol, sehingga lintasan topologis tidak pernah bisa menjadwalkannya, dan lintasan komponen mengklasifikasikannya sebagai siklus. Engine tersebut menalar dengan benar tentang graph yang diberikan kepadanya. Graph itulah model yang salah, karena ia mengodekan satu tipe edge padahal spreadsheet punya dua

Dua kelas edge, satu graph

Perubahan ini menambahkan flag pada record referensi yang sudah diselesaikan, TXLSDepRange.LookupScan, yang di-set oleh ekstraktor dependensi ketika ia menelusuri argumen lookup-array salah satu dari enam fungsi itu. Di hilir, edge yang bersumber dari referensi-referensi tersebut disimpan terpisah dari edge biasa: simpul graph menyimpan daftar ScanDependents dan ScanPrecedents berdampingan dengan daftar dependent dan precedent normalnya

Pemisahan itulah yang membuat semantiknya benar. Scan edge dilintasi oleh propagasi kotor, sehingga suntingan di mana pun di B:B tetap menandai B7 kotor dan B7 merekalkulasi. Scan edge tidak pernah dihitung ke dalam in-degree dan tidak pernah masuk ke pembangun komponen, sehingga tidak bisa menciptakan deadlock topologis dan tidak bisa diklasifikasikan sebagai siklus. Kedua implementasi graph di pustaka, graph klasik per workbook dan graph workspace lintas workbook yang membawa analisis komponen, diubah bersamaan; membiarkan keduanya menyimpang akan menghasilkan workbook yang merekalkulasi berbeda tergantung dibuka sendirian atau sebagai bagian workspace

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Ledger');
    Sheet.Cells[1, 1].Value := 'ACC-4471';
    Sheet.Cells[1, 2].Value := 1200.00;
    // Rentang lookup mencakup kolom B, dan formula ini berdiam di dalamnya
    Sheet.Cells[7, 2].Formula := 'VLOOKUP(A1,B:B,1)';

    case Book.Recalculate of
      lxOk:
        // Sebelum v2.361.98 cabang ini tidak terjangkau untuk sheet ini
        SaveReport(Book);
      lxErrorRef:
        LogWarning('Genuine circular reference - review model inputs');
    end;
  finally
    Book.Free;
  end;
end;

Apa yang Anda korbankan dengan mengecualikan scan edge dari pengurutan

Tepat satu hal, dan layak dinyatakan terus terang alih-alih disembunyikan. Karena scan edge tidak berpartisipasi dalam urutan topologis, formula lookup bisa dievaluasi dalam lintasan yang sama sebelum beberapa sel di rentang lookup-nya direkalkulasi, dan ia kemudian membaca nilai-nilai sebelumnya mereka. Hasilnya konvergen pada rekalkulasi berikutnya

Itu dapat diterima karena itulah yang dilakukan Excel. Untuk workbook tanpa iterative calculation yang diaktifkan, jawaban Excel sendiri atas nilai yang belum direkalkulasi dalam lintasan saat ini adalah nilai hasil hitung terakhir, sehingga engine yang mereproduksi perilaku itu menyamai implementasi acuannya, bukan mendekatinya. Jika Anda membutuhkan jawaban yang benar-benar konvergen atas model yang merujuk dirinya sendiri, mekanismenya adalah iterative calculation dengan batas iterasi eksplisit, dibahas dalam artikel iterative calculation, dan itu berlaku untuk siklus sungguhan, bukan tumpang-tindih scan

Bahaya regresi yang bersembunyi di dalam perbaikan

Menambahkan LookupScan ke TXLSDepRange memunculkan risiko yang tidak ada sangkut pindanya dengan lookup dan semuanya ada sangkut pindanya dengan Pascal. TXLSDepRange adalah record yang tidak dikelola, sehingga variabel lokal bertipe itu tidak diinisialisasi nol. Setiap tempat di codebase yang membangunnya dengan tangan, termasuk blok dependensi data-table dan beberapa helper uji, karena itu harus diperbarui untuk meng-set field baru secara eksplisit. Melewatkan satu dan byte apa pun yang kebetulan ada di stack memutuskan apakah referensi itu diperlakukan sebagai scan edge, yang menghasilkan bug rekalkulasi yang muncul dan hilang bersama perubahan kode yang tidak berhubungan

// Field Boolean baru di record tak terkelola menjadikan setiap lokasi
// konstruksi manual bug laten. Dua idiom yang aman:
var
  R: TXLSDepRange;
begin
  FillChar(R, SizeOf(R), 0);      // nolkan semuanya, lalu isi
  R.Sheet1 := SheetIndex;
  R.Sheet2 := SheetIndex;
  R.Row1 := Row; R.Col1 := Col;
  R.Row2 := Row; R.Col2 := Col;

  // atau set setiap field, termasuk yang baru, di setiap lokasi
  R.LookupScan := False;
end;

Aturan umum yang diperoleh dari sini: menambahkan field ke record yang dibangun di stack di lebih dari segelintir tempat adalah perubahan berisiko lebih tinggi daripada kelihatannya, dan kompiler tidak akan menolong Anda menemukan lokasi-lokasinya. Jika record itu terjangkau dari hot path, utamakan helper yang menginisialisasinya secara lengkap daripada mempercayai setiap call site untuk diperbarui

Membedakan siklus sungguhan dari tumpang-tindih scan

Tidak ada satu pun tentang perubahan ini yang melemahkan deteksi siklus. =B7+1 di B7 tetap siklus, rantai tiga formula yang menutup ke dirinya sendiri tetap siklus, dan keduanya tetap dilaporkan melalui hasil rekalkulasi dengan anggota siklus mempertahankan nilai cache sebelumnya sementara segala di luar siklus tetap mutakhir. Yang berubah hanyalah argumen lookup-array tidak lagi mengarang siklus yang tidak dilihat Excel

Jika Anda mengaudit sebuah workbook dan ingin tahu referensi mana yang benar-benar diselesaikan engine dan dalam urutan apa, evaluation tracer adalah alatnya; artikel tracer evaluasi formula membahas cara membaca outputnya. HotXLS adalah komponen spreadsheet Delphi dan C++Builder native yang membaca dan menulis XLS, XLSX, ODS, dan CSV tanpa Excel terpasang, dan engine rekalkulasinya sama pada setiap format; cakupan fungsi dan engine saat ini tercantum di halaman produk HotXLS Delphi spreadsheet component