Apa itu kesalahan tumpahan? Cara menyelesaikan #spill! Kesalahan di Excel 365

Seperti yang kita semua tahu Office 365 dilengkapi dengan Excel 365 yang dibundel dengan itu. Microsoft telah menambahkan berbagai fitur baru ke Excel 365. Salah satu fitur tersebut adalah Rumus array dinamis. Biasanya, formula hanya akan mengembalikan nilai tunggal sebagai akibat dari sel. Tapi sekarang, dengan fitur baru ini di tempat, beberapa nilai dapat dikembalikan.

Misalnya, di Excel 2019 dan versi sebelumnya, katakanlah Anda menerapkan rumus = D2: D5 di sel, hasilnya akan terbatas pada sel pertama.

Ketika kami harus menerapkan rumus pada semua sel yang sesuai, kami memanfaatkan notasi array (Ctrl+Shift+Enter). Namun, di Excel 365 tidak demikian. Saat Anda menerapkan rumus yang sama, secara otomatis nilainya tumpah ke semua sel yang sesuai. Lihat gambar di bawah ini, untuk detail lebih lanjut.

Luas sel di mana hasilnya tumpah disebut Rentang tumpahan. Lihat gambar di bawah ini

CATATAN:

• Itu Tumpahan diaktifkan secara otomatis dengan array dinamis (saat ini fitur ini hanya didukung di Excel 365) dan fitur tidak dapat dinonaktifkan.
• Fitur tumpahan diaktifkan pada semua rumus dengan atau tanpa fungsi.

Kesalahan tumpahan terlihat ketika rumus dimaksudkan untuk mengembalikan beberapa nilai, namun, hasilnya tidak dapat dimasukkan ke dalam sel. Kesalahan terlihat sebagai berikut:

Kemungkinan alasan untuk menghadapi kesalahan #spill adalah:

• Rentang tumpahan berisi beberapa nilai karena hasilnya tidak dapat diisi dalam sel
• Rentang tumpahan telah menggabungkan sel.
• Saat lembaran tua (dibuat menggunakan Excel 2016 atau sebelumnya) dengan formula yang mendukung persimpangan implisit dibuka di Excel365.
• Saat Anda menerapkan formula array dinamis pada tabel Excel.

Jika Anda melihat kesalahan #spill di Excel, maka jangan khawatir. Dalam artikel ini, kami akan menunjukkan berbagai cara untuk mengidentifikasi akar penyebab untuk masalah ini dan juga melihat cara untuk memperbaiki kesalahan #spill

Daftar isi

• Identifikasi apa yang menyebabkan kesalahan #spill
• Perbaikan yang harus diikuti saat rentang tumpahan tidak kosong
• Perbaiki untuk diikuti saat rentang tumpahan telah menggabungkan sel
• Perbaiki untuk diikuti saat rentang tumpahan di tabel
• Perbaiki untuk diikuti saat rentang tumpahan keluar dari memori
• Perbaiki untuk diikuti saat rentang tumpahan tidak diketahui
• Perbaikan yang harus diikuti saat rentang tumpahan terlalu besar

Identifikasi apa yang menyebabkan kesalahan #spill

Saat Anda melihat kesalahan tumpahan, pertama -tama periksa mengapa Anda melihat kesalahan, untuk melakukannya,

Langkah 1: Klik pada sel yang ditampilkan #TUMPAHAN! kesalahan

Langkah 2: Klik pada Tanda seru seperti yang ditunjukkan di bawah ini

Langkah 3: Baris pertama memberi tahu kita apa yang menyebabkan kesalahan. Misalnya, dalam hal ini, kesalahan dilihat sebagai rentang tumpahan tidak kosong

Perbaikan yang harus diikuti saat rentang tumpahan tidak kosong

Ikuti perbaikan di bawah ini saat Anda melihat bahwa Rentang tumpahan tidak kosong

Perbaiki 1: Hapus data yang memblokir rentang tumpahan

Jika ada beberapa data yang sudah ada di dalam sel dalam kisaran tumpahan, Anda akan melihat kesalahan #spill saat menerapkan rumus.

Ketika Anda dapat dengan jelas melihat data yang memblokir rentang tumpahan

Pertimbangkan contoh di bawah ini, saat Anda menerapkan rumus = d2: d5 pada data, kesalahan tumpahan dilemparkan seperti yang ada Aku disini dalam kisaran tumpahan.

Untuk menyingkirkan kesalahan #spill cukup pindahkan data atau hapus data dari rentang tumpahan.

Saat data memblokir rentang tumpahan disembunyikan

Dalam beberapa kasus, data yang menghalangi rentang tumpahan tersembunyi dan tidak terlalu jelas seperti yang terlihat dalam kasus 1. Pertimbangkan contoh di bawah ini,

Dalam kasus seperti itu, untuk menemukan sel yang menghalangi rentang tumpahan, ikuti langkah -langkah di bawah ini:

Langkah 1: Klik pada sel yang ditampilkan #TUMPAHAN! kesalahan

Langkah 2: Klik pada Tanda seru Seperti yang ditunjukkan di bawah ini, Anda dapat melihat bahwa kesalahannya adalah karena Rentang tumpahan tidak kosong.

Langkah 3: Dari drop-down, klik Pilih sel yang menghalangi

Langkah 4: Sel yang memblokir rentang tumpahan disorot seperti yang ditunjukkan di bawah ini

Sekarang, Anda tahu sel mana yang menghalangi, periksa apa yang sebenarnya menyebabkan masalah ini.

Langkah 5: Pada pemeriksaan sel yang cermat, Anda dapat melihat beberapa data yang tersembunyi di dalam sel.

Seperti yang terlihat pada gambar di atas, ada beberapa data. Karena font memiliki warna putih, tidak mudah untuk mengenali penyumbatan. Untuk menyingkirkan kesalahan, hapus data dari sel dalam rentang tumpahan.

Memperbaiki 2: Hapus pemformatan nomor khusus; ; ; diterapkan di sel

Kadang -kadang, saat format nomor kustom ; ; ; diterapkan pada sel, ada kemungkinan untuk melihat kesalahan tumpahan. Dalam beberapa kasus,

Langkah 1: Klik pada sel yang ditampilkan #TUMPAHAN! kesalahan

Langkah 2: Klik pada Tanda seru seperti yang ditunjukkan di bawah ini.

Langkah 3: Dari drop-down, klik Pilih sel yang menghalangi

Langkah 4: Sel yang memblokir rentang tumpahan disorot seperti yang ditunjukkan di bawah ini

Langkah 5: Klik kanan pada sel yang menghalangi.

Langkah 6: Pilih Format sel

Langkah 7: Format sel sel terbuka. Pergi ke Nomor tab

Langkah 8: Dari panel kiri, pilih Kebiasaan

Langkah 9: Dari panel sisi kanan, ubah jenisnya dari; ; ; ke Umum

Langkah 10: Klik pada Oke tombol

Perbaiki untuk diikuti saat rentang tumpahan telah menggabungkan sel

Jika Anda melihat kesalahannya adalah karena Rentang tumpahan telah menggabungkan sel seperti yang ditunjukkan di bawah ini,

Langkah 1: Klik Pilih sel yang menghalangi dari drop-down

Langkah 2: Sel yang menghalangi akan disorot

Langkah 3: Di bawah Rumah tab, klik Gabungan & Pusat

Langkah 4: Dari drop-down, pilih Sel -sel yang membongkar

Perbaiki untuk diikuti saat Rentang tumpahan di meja

Rumus array dinamis tidak didukung dalam tabel excel. Jika Anda melihat kesalahan #spill pada tabel Excel seperti yang ditunjukkan di bawah ini dengan pesan Rentang tumpahan di meja,

Langkah 1: Pilih tabel sepenuhnya

Langkah 2: Klik pada Desain meja Tab dari bilah menu teratas

Langkah 3: Pilih Konversi ke Range

Langkah 4: Anda akan melihat dialog konfirmasi bermunculan, klik Ya

Perbaiki untuk diikuti saat rentang tumpahan keluar dari memori

Saat Anda mencoba mengidentifikasi penyebab kesalahan #spill, jika Anda melihat bahwa kesalahan menyatakan Di luar ingatan, Maka itu karena rumus array dinamis yang Anda gunakan referensi rentang besar, dalam kasus seperti itu, excel berjalan di luar ingatan menyebabkan kesalahan tumpahan. Untuk mengatasi kesalahan, seseorang dapat mencoba merujuk pada rentang yang lebih kecil.

Perbaiki untuk diikuti saat rentang tumpahan tidak diketahui

Kesalahan ini terlihat saat ukuran array yang tumpah berubah dan Excel tidak dapat menetapkan ukuran kisaran tumpah. Secara umum, saat Anda menggunakan fungsi acak seperti Randarray, Rand, atau Randbetween bersama dengan fungsi array dinamis seperti urutan kesalahan ini terlihat.

Untuk memahaminya dengan lebih baik, pertimbangkan contoh di bawah ini, katakanlah urutan fungsi (randbetween (1.100)) digunakan. Di sini Randbetween menghasilkan bilangan bulat acak yang lebih besar dari atau sama dengan 1 dan kurang dari atau sama dengan 100. Dan urutan menghasilkan angka berurutan (EG-quence (5) menghasilkan 1,2,3,4,5). Namun, Randbetween adalah fungsi yang mudah menguap dan terus mengubah nilainya setiap kali lembar excel dibuka atau diubah. Karena, dari ini, fungsi urutan tidak akan dapat menentukan ukuran array yang harus dihasilkannya. Itu tidak akan tahu berapa banyak nilai yang akan dihasilkan dan dengan demikian melemparkan kesalahan tumpahan.

Saat Anda mengidentifikasi penyebab kesalahan, Anda lihat Rentang tumpahan tidak diketahui

Untuk memperbaiki kesalahan semacam ini, Cobalah memiliki formula berbeda yang sesuai dengan kebutuhan Anda.

Perbaikan yang harus diikuti saat rentang tumpahan terlalu besar

Katakanlah Anda mengidentifikasi penyebabnya dan Anda melihat bahwa kesalahan terlihat karena Rentang tumpahan terlalu besar seperti yang ditunjukkan di bawah ini.

Ketika array dinamis tidak ada, ada sesuatu yang disebut persimpangan implisit di Excel yang terpaksa mengembalikan hasil tunggal bahkan ketika formula memiliki potensi untuk mengembalikan beberapa hasil. Pertimbangkan contoh, jika formula = B: B*5% diterapkan pada Excel 2019 atau versi sebelumnya, dengan persimpangan implisit, hasilnya adalah sebagai berikut:

Namun, ketika formula yang sama digunakan di Excel 365, Anda melihat kesalahan berikut

Untuk menyelesaikan ini, cobalah perbaikan berikut

Perbaiki 1: Terapkan persimpangan implisit menggunakan @ operator

Saat kita berkata = B: b, Array dinamis akan merujuk seluruh kolom B. Sebaliknya, kami dapat memaksa Excel untuk memaksakan persimpangan implisit menggunakan @ operator

Ubah formula menjadi =@B: b*5%

Karena persimpangan implisit ditambahkan, formula akan diterapkan pada satu sel tunggal. Untuk memperpanjang formula,

1. Cukup klik pada titik seperti yang ditunjukkan di bawah ini

2. Seret ke bawah ke sel sesuai kebutuhan. Ini akan menerapkan formula yang sama untuk sel -sel ini.

Perbaiki 2: Alih -alih mengacu pada kolom, lihat rentang

Dalam formula, = B: B*5% , Kami mengacu pada kolom B. Alih -alih merujuk ke rentang tertentu, = B2: B4*5%

Itu saja

Kami berharap artikel ini informatif.

Mohon suka dan berkomentar jika Anda dapat memperbaiki masalah dengan metode di atas.

Terima kasih telah membaca.