Formula Excel: Cara memperbaiki #SPILL! ralat -

Isi kandungan

Ringkasan

Kesalahan #SPILL berlaku apabila julat tumpahan disekat oleh sesuatu di lembaran kerja. Penyelesaiannya biasanya untuk membersihkan julat tumpahan dari sebarang data yang menghalang. Lihat di bawah untuk maklumat lebih lanjut dan langkah penyelesaiannya.

Penjelasan

Mengenai tumpahan dan #SPILL! kesilapan

Dengan pengenalan Dynamic Arrays di Excel, formula yang mengembalikan banyak nilai "tumpah" nilai ini terus ke lembaran kerja. Segi empat tepat yang merangkumi nilai disebut "spill range". Apabila data berubah, rentang tumpahan akan mengembang atau berkontrak sesuai keperluan. Anda mungkin melihat nilai baru ditambahkan, atau nilai yang ada hilang.

Video: Tumpahan dan jarak tumpahan

Ralat #SPILL berlaku apabila julat tumpahan disekat oleh sesuatu di lembaran kerja. Kadang-kadang ini dijangkakan. Contohnya, anda telah memasukkan formula, menjangkakan tumpahan, tetapi data yang ada di lembaran kerja menghalangi. Penyelesaiannya hanyalah dengan membersihkan jarak tumpahan dari sebarang data yang menghalang.

Kadang-kadang namun kesalahan itu mungkin tidak dijangka dan oleh itu membingungkan. Baca di bawah untuk mengetahui bagaimana ralat ini mungkin disebabkan, dan apa yang boleh anda lakukan untuk menyelesaikannya.

Tingkah laku tumpahan adalah asli

Penting untuk memahami bahawa tingkah laku tumpahan adalah automatik dan asli. Dalam Dynamic Excel (hanya Office 365 Excel sahaja) formula apa pun, walaupun formula mudah tanpa fungsi, dapat menumpahkan hasil. Walaupun ada cara untuk menghentikan formula agar tidak mengembalikan banyak hasil, tumpahan itu sendiri tidak dapat dilumpuhkan dengan pengaturan global.

Begitu juga, tidak ada pilihan di Excel untuk "mematikan ralat #SPILL. Untuk memperbaiki ralat #SPILL, anda harus menyiasat dan menyelesaikan punca masalah.

Betulkan # 1 - kosongkan jarak tumpahan

Ini adalah kes paling mudah untuk diselesaikan. Rumusnya harus menumpahkan banyak nilai, tetapi sebaliknya mengembalikan # SPILL! kerana ada yang menghalangi. Untuk menyelesaikan ralat, pilih mana-mana sel dalam julat tumpahan sehingga anda dapat melihat sempadannya. Kemudian pindahkan data penyekat ke lokasi baru, atau hapus data sama sekali. Perhatikan bahawa sel dalam jarak tumpahan mesti kosong, jadi perhatikan sel yang mengandungi watak yang tidak kelihatan, seperti spasi.

Pada skrin di bawah, "x" menyekat jarak tumpahan:

Setelah "x" dikeluarkan, fungsi TANPA UNIK menghasilkan normal:

Betulkan # 2 - tambah @ watak

Sebelum Array Dinamik, Excel secara diam-diam menerapkan tingkah laku yang disebut "persimpangan tersirat" untuk memastikan bahawa formula tertentu yang berpotensi mengembalikan banyak hasil hanya mengembalikan satu hasil. Dalam array bukan dinamik Excel, formula ini mengembalikan hasil yang kelihatan normal tanpa ralat. Walau bagaimanapun, dalam kes tertentu formula yang sama yang dimasukkan dalam Dynamic Excel mungkin menghasilkan kesalahan #SPILL. Sebagai contoh, pada skrin di bawah, sel D5 mengandungi formula ini, disalin ke bawah:

=$B$5:$B$10+3

Rumus ini tidak akan menimbulkan ralat, katakan Excel 2016 kerana persimpangan tersirat akan menghalang formula mengembalikan banyak hasil. Walau bagaimanapun, dalam Dynamic Excel, formula secara automatik mengembalikan banyak hasil ke lembaran kerja, dan yang saling bertabrakan, kerana formula disalin dari D5: D10.

Salah satu penyelesaiannya adalah dengan menggunakan watak @ untuk membolehkan persimpangan tersirat seperti ini:

= @$B$5:$B$10+3

Dengan perubahan ini, setiap formula mengembalikan satu keputusan sekali lagi dan ralat #SPILL hilang.

Catatan: ini menjelaskan sebahagian mengapa anda tiba-tiba dapat melihat watak "@" muncul dalam formula yang dibuat dalam versi Excel yang lebih lama. Ini dilakukan untuk mengekalkan keserasian. Oleh kerana formula dalam versi Excel yang lebih lama tidak dapat tumpah ke beberapa sel, @ ditambahkan untuk memastikan tingkah laku yang sama ketika formula dibuka di Dynamic Excel.

Betulkan # 3 - formula array dinamik asli

Kaedah lain (lebih baik) untuk memperbaiki ralat #SPILL yang ditunjukkan di atas adalah dengan menggunakan formula array dinamik asli di D5 seperti ini: