Referensi Rumus Saat Menyisipkan Baris di HotXLS

HotXLS menyesuaikan referensi rumus secara otomatis ketika Anda menyisipkan atau menghapus baris atau kolom dalam lembar kerja XLSX. Metode InsertRows, DeleteRows, InsertCols, dan DeleteCols milik mesin ini menulis ulang setiap rumus yang bertahan sehingga referensi A1-nya — relatif, absolut, dan rentang — tetap menunjuk ke data yang sama setelah pengeditan struktural, dan referensi ke blok yang dihapus menjadi #REF!, sesuai dengan perilaku Excel

Bug yang dicegah oleh fitur ini adalah salah satu yang paling tidak terlihat dalam pembuatan laporan. Generator menulis data harian ke dalam C2:C9 dengan =SUM(C2:C9) di bawahnya, lalu langkah berikutnya menyisipkan baris tajuk di bagian atas. Jika mesin hanya memindahkan nilai sel dan membiarkan teks rumus apa adanya, fungsi SUM tersebut tetap membaca C2:C9 sementara data sekarang berada di C3:C10 — sehingga totalnya secara diam-diam menghilangkan hari terakhir dan menghitung ganda tajuk utama. Tidak ada yang memicu pengecualian, berkas terbuka dengan baik, dan angkanya salah begitu saja. Sebelum versi 2.160, mesin HotXLS XLSX membiarkan rumus tidak tersentuh selama pengeditan struktural; sejak versi 2.160, penulisan ulang berjalan otomatis dan tidak ada bendera (flag) yang perlu diatur

Apa yang terjadi pada rumus saat Anda menyisipkan baris di Excel?

Aturan Excel adalah bahwa referensi mengikuti data, bukan alamat. Ketika sebuah baris disisipkan, setiap referensi yang indeks barisnya berada pada atau di bawah titik penyisipan akan bergeser ke bawah sebanyak jumlah baris yang disisipkan; referensi yang sepenuhnya berada di atas titik penyisipan tidak tersentuh. Menghapus baris menjalankan aturan yang sama secara terbalik: referensi di bawah blok yang dihapus bergeser ke atas, dan referensi ke blok yang dihapus itu sendiri menjadi #REF! karena sel yang mereka beri nama tidak lagi ada. Kolom berperilaku identik di sepanjang sumbu lainnya. Pustaka spreadsheet yang menginginkan keluarannya bertahan saat berhadapan dengan pengguna Excel harus meniru mekanisme ini secara persis, karena pengguna memikirkan rumus mereka dalam istilah-istilah ini tanpa pernah memikirkannya

Bagian yang mengejutkan para pengembang adalah bahwa referensi absolut juga ikut berpindah. Jangkar $ dalam $B$2 mengontrol apa yang terjadi ketika rumus disalin atau diisi ke sel lain — jangkar tersebut tidak melakukan apa pun selama pengeditan struktural. Sisipkan baris di atas baris 2 dan Excel menulis ulang $B$2 menjadi $B$3, dengan tanda dolar tetap utuh, karena nilai yang diandalkan rumus tersebut secara fisik berpindah ke baris 3. Mesin yang hanya menggeser referensi relatif akan merusak rumus yang paling sengaja dijangkar orang. HotXLS menggeser kedua bentuk tersebut dan mempertahankan tanda $ dalam teks yang ditulis ulang

Bagaimana HotXLS menggeser referensi rumus secara otomatis?

Keempat metode pengeditan struktural pada TXLSXWorksheet didelegasikan ke mesin geometri tunggal: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). InsertRows(BeforeRow, Count) memanggilnya dengan delta baris positif, DeleteRows(StartRow, Count) dengan delta negatif, dan metode kolom melakukan hal yang sama pada sumbu kolom. Rutinitas pertama-tama memindahkan lokasi sel itu sendiri — menghapus sel mana pun yang jatuh di dalam blok yang dihapus — lalu menyusuri setiap sel rumus yang bertahan dan meneruskan teksnya melalui XlsxAdjustFormulaRowColRefs, sebuah pemindai yang menemukan referensi gaya A1 dan menulis ulang komponen baris dan kolomnya terhadap pergeseran tersebut. Langkah yang sama memindahkan rentang yang digabungkan, hipertautan, komentar, gambar, bagan, format bersyarat, validasi data, dan rentang tabel, sehingga seluruh lembar kerja bergerak sebagai satu kesatuan

// Tata letak sebelum pengeditan:
//   C2..C9  data harian
//   C10     =SUM(C2:C9)
Sheet.InsertRows(2, 1);      // satu baris kosong sebelum baris 2
// Tata letak setelah panggilan:
//   C3..C10 data harian
//   C11     =SUM(C3:C10)    -- rentang bergerak bersama data

Beberapa detail pemindai patut diketahui. XlsxAdjustFormulaRowColRefs mengenali referensi sel tunggal dalam keempat bentuk jangkar (A1, $A1, A$1, $A$1) dan rentang dua sudut seperti A1:B3, menyesuaikan setiap titik akhir secara independen. Rumus yang teksnya sudah dimulai dengan # — penanda kesalahan dari pengeditan sebelumnya — dilewati alih-alih dipindai ulang. Dan InsertCols menambahkan satu kehalusan paritas Excel-nya sendiri: kolom yang baru disisipkan mewarisi lebar tetangga kirinya, yang merupakan apa yang dilakukan oleh perintah Insert Sheet Columns milik Excel

Kapan referensi yang dihapus menjadi #REF!?

Aturan pergeseran untuk satu baris atau indeks kolom memiliki tiga hasil. Indeks sebelum titik pengeditan tidak berubah. Indeks pada atau setelah titik pengeditan bergeser sebesar delta. Dan saat penghapusan, indeks yang berada di dalam blok yang dihapus tidak memiliki nilai baru yang bermakna — sel tersebut hilang — sehingga pemindai menulis ulang seluruh referensi sebagai #REF!. Untuk referensi rentang, kedua titik akhir dijalankan melalui aturan yang sama, dan jika salah satu titik akhir mendarat di dalam blok yang dihapus, referensi tersebut ditulis ulang sebagai #REF! alih-alih dibiarkan valid setengahnya

// A12 menampung  =A4+A6+A10
Sheet.DeleteRows(5, 3);      // hapus baris 5..7
// Rumus sekarang di A9, membaca  =A4+#REF!+A7
//   A4  : di atas blok yang dihapus, tidak berubah
//   A6  : di dalam baris 5..7, hilang      -> #REF!
//   A10 : di bawah blok, bergeser ke atas  -> A7

Menghasilkan nilai error #REF! secara langsung alih-alih mengarahkan ulang secara diam-diam adalah pilihan yang tepat, dan itu adalah pilihan yang diambil Excel. Rumus yang menunjuk ke sel tetangga setelah masukan aslinya dihapus akan mengembalikan angka yang terlihat masuk akal; #REF! menyebar melalui rumus yang bergantung dan muncul pada uji asap (smoke test) pertama. Konversi yang sama berlaku pada sumbu kolom

// E1 menampung  =B1*$C$1
Sheet.DeleteCols(3, 1);      // hapus kolom C
// Rumus sekarang di D1, membaca  =B1*#REF!
// Jangkar absolut tidak melindungi $C$1 -- sel itu sendiri hilang

Bentuk referensi mana yang tidak ditulis ulang?

Pemindai menargetkan referensi A1 lembar kerja yang sama dengan bentuk eksplisit huruf-kolom-ditambah-nomor-baris, dan penting untuk memperjelas apa saja yang berada di luar itu. Referensi kolom penuh seperti A:A dan referensi baris penuh seperti 1:1 kekurangan salah satu dari dua komponen, sehingga pemindai membiarkannya seperti yang ditulis. Referensi tabel terstruktur (Table1[Amount]) juga dilewatkan tanpa tersentuh. Penulisan ulang juga beroperasi secara ketat pada notasi A1 — jika kode Anda membangun rumus dalam gaya R1C1, konversikan ke A1 sebelum pengeditan struktural, seperti yang dijelaskan dalam artikel pendamping tentang notasi rumus R1C1 di Delphi

Konstruksi lintas lembar kerja (cross-sheet) dan tingkat buku kerja (workbook-level) ditangani oleh proses terpisah alih-alih oleh pemindai teks sel. Setelah menyesuaikan lembar kerja yang diedit, ShiftSheetGeometry menyebarkan perubahan geometri yang sama ke rumus di lembar kerja lain yang merujuk ke lembar kerja yang diedit, ke rentang seri bagan, ke target hipertautan internal, dan ke nama yang ditentukan (defined names). Nama yang ditentukan mendapatkan perlindungan lebih lanjut pada tingkat siklus hidup lembar kerja: sejak versi 2.150, menghapus lembar kerja menulis ulang setiap kualifikasi SheetN! di dalam rumus nama yang ditentukan menjadi #REF!, dan mengganti nama lembar kerja menulis ulang kualifikasi ke nama baru, sehingga nama tidak pernah menunjuk ke lembar kerja yang tidak lagi ada. Bagaimana nama dan rumus lintas lembar kerja berpadu dibahas dalam artikel tentang nama yang ditentukan dan rumus lintas lembar kerja

Hitung ulang setelah pergeseran

Penyesuaian referensi menulis ulang teks rumus; tindakan ini tidak menghitung ulang hasil. Setelah pengeditan struktural, nilai cache yang disimpan bersama rumus menggambarkan geometri lama, sehingga urutan yang andal adalah: lakukan semua penyisipan dan penghapusan terlebih dahulu, lalu picu perhitungan ulang sekali, lalu simpan. Menjalankan pergeseran sebelum perhitungan ulang juga menjaga informasi ketergantungan tetap jujur — setiap referensi yang ditulis ulang menyebutkan pendahulu yang sebenarnya, yang merupakan apa yang dibutuhkan oleh mesin perhitungan ulang inkremental dan grafik ketergantungannya untuk menghitung ulang sel terpengaruh yang minimal. Jika rumus yang ditulis ulang sekarang berisi #REF!, perhitungan ulang akan langsung memunculkan nilai error alih-alih membiarkan angka usang di dalam berkas

Penyesuaian referensi rumus dikirimkan sebagai perilaku standar dari mesin XLSX di HotXLS Delphi Excel Component untuk Delphi dan C++Builder; halaman produk menyediakan referensi API pengeditan lembar kerja yang lengkap, termasuk metode penyisipan dan penghapusan yang ditunjukkan di sini