Salah satu kesalahan yang mungkin terjadi saat menggunakan rumus array dinamis di Excel adalah pesan kesalahan #SPILL!.
Artikel ini membahas #SPILL!, penyebab, cara mengatasinya, dan cara mencegahnya.
Apa Itu Error #SPILL! di Excel?
Error #SPILL! terjadi saat rumus array dinamis mencoba menampilkan hasil ke beberapa cell, tetapi Excel tidak bisa menumpahkan hasilnya karena ada hambatan. Hambatan ini bisa berupa data, objek, cell gabungan, atau referensi yang tidak valid.
Berbeda dengan error seperti #VALUE! atau #REF!, #SPILL! belum tentu terjadi karena rumus salah.
Rentang Tumpahan Tidak Kosong
Penjelasan: Cell tempat hasil array akan ditampilkan sudah terisi data, rumus, atau objek seperti gambar atau chart. Perhatikan cell E4 pada gambar.
Solusi: Hapus atau pindahkan isi cell yang menghalangi rentang tumpahan.
Mengisi Rentang Tumpahan
Penjelasan: #SPILL! juga terjadi saat kita mengedit secara manual salah satu cell ke mana hasil array tertumpah.
Solusi: Hindari melakukan edit terhadap cell hasil tumpahan. Modifikasi data sumber atau atau sesuaikan perhitungan rumus untuk hasil yang berbeda.
Cell Gabungan
Penjelasan: Rentang tumpahan berisi cell yang digabung. Perhatikan cell E4 pada gambar di bawah.
Solusi: Pisahkan cell gabungan menjadi cell-cell pembentuknya.
- Pilih cell gabungan
- Pada tab Home, kelompok Alignment, pada Merge & Center, pilih Unmerge Cells
Besaran Argumen Rumus Tidak Stabil
Penjelasan: Rumus di bawah menghasilkan array dengan ukuran tidak pasti.
=SEQUENCE(RANDBETWEEN(1;5))
Solusi: Gunakan nilai tetap atau kontrol ukuran array dengan logika tambahan.
Referensi Terlalu Besar
Penjelasan: Rumus menggunakan rentang yang terlalu besar atau tidak terdefinisi dengan benar, yang menyebabkan Excel mengembalikan #SPILL!.
Pada gambar di bawah, worksheet menggunakan rumus =B:B*1,7%. Excel akan menghitung 1.048.576 baris dan menempatkan hasil perhitungan pertama di Baris 2—karena rumus ada di cell C2—dan hasil terbawah di Baris 1048577. Excel akan menampilkan #SPILL! karena baris terbawah adalah Baris 1048576. Tidak ada Baris 1048577.
Solusi: Periksa referensi. Sederhanakan rumus jika memungkinkan, dan pastikan semua referensi valid dan dalam batas yang didukung Excel.
Spilling Range adalah Tabel Data
Penjelasan: Rumus array dinamis tidak bisa menumpahkan hasil ke dalam tabel Excel.
Solusi: Kondisi ini memang demikian dari sananya. Bila memang ingin menggunakan array dinamis bersamaan dengan tabel data, berikut beberapa hal yang bisa kita lakukan:
- Gunakan di luar area tabel
- Gunakan kolom pembantu
- Konversi tabel data menjadi rentang data biasa
Rumus yang Berpotensi Menghasilkan #SPILL!
Rumus-rumus berikut sering digunakan dalam array dinamis dan bisa memicu error #SPILL! jika rentang tumpahan tidak tersedia:
Secara konsep, rumus apa pun yang mengembalikan array berpotensi memicu error #SPILL! jika rentang spill tidak tersedia.
Cara Menemukan Kesalahan #SPILL!
Ada 3 cara menemukan #SPILL! di dalam worksheet:
- Memeriksa secara manual
- Menggunakan pencarian error dengan Find and Replace
- Menggunakan Error Checking Option Menu
Menggunakan Error Checking Option Menu
Berikut cara menemukan cell yang menyebabkan #SPILL!:
- Klik cell yang menampilkan #SPILL!
- Klik panah yang muncul
- Pilih Select Obstructing Cell
- Excel akan mengantar kursor cell ke cell yang berisi nilai atau merged cell
Mencegah #SPILL! di Masa Mendatang Kesalahan
- Rencanakan rentang tumpahan: Selalu pastikan area tempat rumus akan tumpah bersih dari data, cell gabungan, dan objek.
- Hindari cell gabungan: Gunakan pemformatan cell, alih-alih penggabungan, jika memungkinkan.
- Audit worksheet secara berkala: Periksa secara berkala data, objek, atau pemformatan tersembunyi yang mungkin menghalangi rentang tumpahan.
- Gunakan dynamic array dengan benar: Pahami perilaku fungsi baru dan hindari metode lama seperti Ctrl+Shift+Enter
- Aktifkan fitur untuk memeriksa kesalahan: Fitur ini membantu untuk Excel mendapatkan informasi tentang masalah rumus.
Kesimpulan
Error #SPILL! adalah sinyal bahwa Excel mencoba membantu menampilkan hasil array. Dengan memahami penyebab umum dan menerapkan strategi pencegahan, kita bisa mengatasi dan bahkan mencegah error ini sebelum terjadi.
Dengan menguasai cara kerja tumpahan dan #SPILL!, teman-teman dapat membuka pintu menuju pengobalahan worksheet yang lebih cerdas, efisien, dan bebas gangguan.