Artikel ini akan membantu Anda memahami semua penyebab kesalahan #SPILL serta solusi untuk memperbaikinya di Excel 365.
#TUMPAHAN! adalah jenis kesalahan Excel baru yang terutama terjadi saat rumus yang menghasilkan beberapa hasil perhitungan mencoba menampilkan outputnya dalam rentang tumpahan tetapi rentang tersebut sudah berisi beberapa data lain.
Data pemblokiran dapat berupa apa saja termasuk, nilai teks, sel gabungan, karakter spasi biasa, atau bahkan jika tidak ada cukup tempat untuk mengembalikan hasil. Solusinya sederhana, hapus rentang data pemblokiran apa pun atau pilih larik sel kosong yang tidak berisi jenis data apa pun di dalamnya.
Kesalahan tumpahan biasanya terjadi saat menghitung rumus larik dinamis, karena rumus larik dinamis adalah rumus yang mengeluarkan hasil ke beberapa sel atau larik. Mari kita lihat lebih detail dan pahami apa yang memicu kesalahan ini di Excel dan cara mengatasinya.
Apa yang menyebabkan kesalahan tumpahan?
Sejak peluncuran array Dinamis pada tahun 2018, rumus Excel dapat menangani beberapa nilai sekaligus dan mengembalikan hasil di lebih dari satu sel. Array dinamis adalah array yang dapat diubah ukurannya yang memungkinkan rumus mengembalikan beberapa hasil ke rentang sel pada lembar kerja berdasarkan rumus yang dimasukkan dalam satu sel.
Ketika rumus array dinamis mengembalikan beberapa hasil, hasil ini secara otomatis tumpah ke sel tetangga. Perilaku ini disebut 'Tumpahan' di Excel. Dan rentang sel tempat hasil tumpah disebut 'Rentang tumpahan'. Kisaran tumpahan akan meluas atau menyusut secara otomatis berdasarkan nilai sumber.
Jika rumus mencoba mengisi rentang tumpahan dengan beberapa hasil tetapi terhalang oleh sesuatu pada rentang tersebut, maka kesalahan #SPILL terjadi.
Excel kini memiliki 9 fungsi yang menggunakan fungsionalitas Dynamic Array untuk menyelesaikan masalah, antara lain:
- URUTAN
- SARING
- MENGUBAH URUTAN
- MENYORTIR
- SORTIR DENGAN
- RANDARRAY
- UNIK
- XLOOKUP
- XMATCH
Rumus array dinamis hanya tersedia di 'Excel 365' dan saat ini tidak didukung oleh perangkat lunak Excel offline mana pun (mis. Microsoft Excel 2016, 2019).
Kesalahan tumpahan tidak hanya disebabkan oleh menghalangi data, ada beberapa alasan mengapa Anda mungkin mendapatkan kesalahan #Tumpahan. Mari kita jelajahi berbagai situasi di mana Anda mungkin menghadapi #SPILL! kesalahan dan cara memperbaikinya.
Rentang Tumpahan tidak Kosong
Salah satu penyebab utama kesalahan tumpahan adalah rentang tumpahan tidak kosong. Misalnya, jika Anda mencoba menampilkan 10 hasil, tetapi jika ada data di salah satu sel di area tumpahan, rumus mengembalikan #SPILL! kesalahan.
Contoh 1:
Pada contoh di bawah, kita telah memasukkan fungsi TRANSPOSE di sel C2 untuk mengubah rentang vertikal sel (B2:B5) menjadi rentang horizontal (C2:F2). Alih-alih mengganti kolom menjadi satu baris, Excel menunjukkan kepada kita #SPILL! kesalahan.
Dan ketika Anda mengklik sel rumus, Anda akan melihat batas biru putus-putus yang menunjukkan area/rentang tumpahan (C2:F2) yang diperlukan untuk menampilkan hasil seperti yang ditunjukkan di bawah ini. Juga, Anda akan melihat tanda peringatan kuning dengan tanda seru di atasnya.
Untuk memahami alasan di balik kesalahan, klik ikon peringatan di sebelah kesalahan dan lihat pesan di baris pertama yang disorot dengan warna abu-abu. Seperti yang Anda lihat, tertulis 'Rentang tumpahan tidak kosong' di sini.
Masalahnya di sini adalah bahwa sel dalam rentang tumpahan D2 dan E2 memiliki karakter teks (tidak kosong), oleh karena itu, kesalahannya.
Larutan:
Solusinya sederhana, hapus data (baik memindahkan atau menghapus) yang terletak di rentang tumpahan atau memindahkan formula ke lokasi lain yang tidak ada halangan.
Segera setelah Anda menghapus atau memindahkan penyumbatan, Excel akan secara otomatis mengisi sel dengan hasil rumus. Di sini, saat kami menghapus teks di D2 dan E2, rumus mengubah kolom menjadi baris seperti yang diinginkan.
Contoh 2:
Pada contoh di bawah ini, meskipun rentang tumpahan tampak kosong, rumusnya tetap menunjukkan Tumpahan! kesalahan. Karena tumpahan sebenarnya tidak kosong, ia memiliki karakter ruang yang tidak terlihat di salah satu sel.
Sulit untuk menemukan karakter spasi atau karakter tak terlihat lainnya yang bersembunyi di sel yang tampaknya kosong. Untuk menemukan sel tersebut dengan data yang tidak diinginkan, klik Error floatie (tanda peringatan) dan pilih 'Select Obstructing Cells' dari menu dan itu akan membawa Anda ke sel yang berisi data yang menghalangi.
Seperti yang Anda lihat, pada tangkapan layar di bawah, sel E2 memiliki dua karakter spasi. Saat Anda menghapus data tersebut, Anda akan mendapatkan hasil yang sesuai.
Terkadang, karakter yang tidak terlihat bisa berupa teks yang diformat dengan warna font yang sama dengan warna isian sel atau nilai sel yang diformat khusus dengan kode angka ;;;. Saat Anda memformat nilai sel dengan ;;;, itu akan menyembunyikan apa pun di sel itu, terlepas dari warna font atau warna sel.
Rentang Tumpahan Berisi Sel yang Digabung
Terkadang, #SPILL! kesalahan terjadi ketika rentang tumpahan berisi sel yang digabungkan. Rumus array dinamis tidak berfungsi dengan sel yang digabungkan. Untuk memperbaikinya, yang harus Anda lakukan adalah memisahkan sel dalam rentang tumpahan atau memindahkan rumus ke rentang lain yang tidak memiliki sel gabungan.
Dalam contoh di bawah ini, meskipun rentang tumpahan kosong (C2:CC8), rumus mengembalikan kesalahan Tumpahan. Itu karena sel C4 dan C5 digabungkan.
Untuk memastikan bahwa sel yang digabungkan adalah alasan Anda mendapatkan kesalahan, klik tomboltanda peringatan dan verifikasi penyebabnya – 'Rentang tumpahan telah menggabungkan sel'.
Larutan:
Untuk memisahkan sel, pilih sel yang digabungkan, lalu pada tab 'Beranda', klik tombol 'Gabungkan & Tengahkan' dan pilih 'Lepaskan Sel'.
Jika Anda kesulitan menemukan sel yang digabungkan dalam spreadsheet besar Anda, klik opsi 'Select Obstructing Cells' dari menu tanda peringatan untuk melompat ke sel yang digabungkan.
Kisaran Tumpahan di Tabel
Rumus array tumpah tidak didukung di tabel Excel. Rumus array dinamis hanya boleh dimasukkan dalam satu sel individual. Jika Anda memasukkan rumus array tumpah dalam tabel atau ketika area tumpahan jatuh ke dalam tabel, Anda akan mendapatkan kesalahan tumpahan. Saat ini terjadi, coba ubah tabel ke rentang normal atau pindahkan rumus ke luar tabel.
Misalnya, ketika kita memasukkan rumus rentang tumpahan berikut dalam tabel Excel, kita akan mendapatkan kesalahan tumpahan di setiap sel tabel, bukan hanya sel rumus. Itu karena Excel secara otomatis menyalin formula apa pun yang dimasukkan dalam tabel ke setiap sel di kolom tabel.
Selain itu, Anda akan mendapatkan kesalahan tumpahan saat formula mencoba menumpahkan hasil dalam tabel. Pada tangkapan layar di bawah, area tumpahan termasuk dalam tabel yang ada, jadi kami mendapatkan kesalahan tumpahan.
Untuk mengonfirmasi penyebab di balik kesalahan ini, klik tanda peringatan dan lihat alasan kesalahan – 'Rentang tumpahan dalam tabel'
Larutan:
Untuk memperbaiki kesalahan, Anda harus mengembalikan tabel Excel kembali ke kisaran. Untuk melakukannya, klik kanan di mana saja di dalam tabel, klik 'Tabel', lalu pilih opsi 'Konversi ke Rentang'. Atau, Anda dapat mengklik kiri di mana saja di dalam tabel, lalu pergi ke tab 'Desain Tabel' dan pilih opsi 'Konversi ke Rentang'.
Kisaran Tumpahan Tidak Diketahui
Jika Excel tidak dapat menetapkan ukuran array tumpah, itu akan memicu kesalahan tumpahan. Terkadang, rumus memungkinkan larik dinamis untuk mengubah ukuran di antara setiap lintasan penghitungan. Jika ukuran array dinamis terus berubah selama perhitungan berlalu dan tidak seimbang, itu akan menyebabkan #SPILL! Kesalahan.
Jenis kesalahan Tumpahan ini biasanya dipicu saat menggunakan fungsi volatil seperti fungsi RAND, RANDARRAY, RANDBETWEEN, OFFSET, dan INDIRECT.
Misalnya, ketika kami menggunakan rumus di bawah ini di sel B3, kami mendapatkan kesalahan Tumpahan:
=URUTAN(RANDBETWEEN(1, 500))Dalam contoh, fungsi RANDBETWEEN mengembalikan bilangan bulat acak antara angka 1 dan 500, dan outputnya terus berubah. Dan fungsi SEQUENCE tidak tahu berapa banyak nilai yang akan dihasilkan dalam array tumpahan. Oleh karena itu, kesalahan #SPILL.
Anda juga dapat mengonfirmasi penyebab kesalahan dengan mengklik tanda Peringatan – ‘Rentang tumpahan tidak diketahui’.
Larutan:
Untuk memperbaiki kesalahan rumus ini, satu-satunya pilihan Anda adalah menggunakan rumus yang berbeda untuk perhitungan Anda.
Kisaran Tumpahan Terlalu Besar
Kadang-kadang Anda dapat menjalankan rumus yang menghasilkan rentang tumpahan yang terlalu besar untuk ditangani oleh lembar kerja, dan mungkin melampaui tepi lembar kerja. Ketika itu terjadi, Anda mungkin mendapatkan #SPILL! kesalahan. Untuk memperbaiki masalah ini, Anda dapat mencoba mereferensikan rentang tertentu atau satu sel alih-alih seluruh kolom atau menggunakan karakter '@' untuk mengaktifkan perpotongan implisit
Pada contoh di bawah ini, kami mencoba menghitung 20% dari angka Penjualan di kolom A dan mengembalikan hasilnya di kolom B, tetapi sebaliknya, kami mendapatkan kesalahan Tumpahan.
Rumus di B3 menghitung 20% dari nilai di A3, lalu 20% dari nilai di A4, dan seterusnya. Ini menghasilkan lebih dari satu juta hasil (1.048.576) dan menumpahkan semuanya di kolom B mulai dari sel B3, tetapi akan mencapai akhir lembar kerja. Tidak cukup ruang untuk menampilkan semua output, akibatnya, kami mendapatkan kesalahan #SPILL.
Seperti yang Anda lihat, penyebab kesalahan ini adalah – 'Rentang tumpahan terlalu besar'.
Solusi:
Untuk mengatasi masalah ini, coba ubah seluruh kolom dengan rentang yang relevan atau referensi sel tunggal, atau tambahkan operator @ untuk melakukan perpotongan implisit.
Perbaiki 1: Anda dapat mencoba merujuk rentang daripada seluruh kolom. Di sini, kami mengubah seluruh rentang A:A dengan A3:A11 dalam rumus, dan rumus akan secara otomatis mengisi rentang dengan hasil.
Perbaiki 2: Ganti seluruh kolom hanya dengan referensi sel pada baris yang sama (A3), lalu salin rumus ke bawah rentang menggunakan gagang isian.
Perbaiki 3: Anda juga dapat mencoba menambahkan operator @ sebelum referensi untuk melakukan perpotongan implisit. Ini akan menampilkan output dalam sel rumus saja.
Kemudian, salin rumus dari sel B3 ke rentang lainnya.
Catatan: Saat Anda mengedit formula tumpah, Anda hanya bisa mengedit sel pertama di area/rentang tumpahan. Anda dapat melihat rumus di sel lain dari rentang tumpahan, tetapi rumus tersebut akan berwarna abu-abu dan tidak dapat diperbarui.
kehabisan memori
Jika Anda menjalankan rumus array tumpah yang menyebabkan Excel kehabisan memori, ini mungkin memicu kesalahan #SPILL. Dalam keadaan seperti itu, coba referensikan array atau rentang yang lebih kecil.
Tidak dikenali / Penggantian
Anda juga bisa mendapatkan kesalahan tumpahan bahkan ketika Excel tidak mengenali atau tidak dapat merekonsiliasi penyebab kesalahan. Dalam kasus seperti itu, periksa kembali rumus Anda dan pastikan semua parameter fungsi sudah benar.
Sekarang, Anda tahu semua penyebab dan solusi untuk #SPILL! kesalahan di Excel 365.