Apa itu ralat tumpahan? Cara menyelesaikan #spill! Ralat dalam Excel 365

Seperti yang kita semua tahu Office 365 dilengkapi dengan Excel 365 yang dibundel dengannya. Microsoft telah menambah pelbagai ciri baru kepada Excel 365. Satu ciri sedemikian adalah Formula array dinamik. Biasanya, formula akan mengembalikan hanya satu nilai sebagai hasil kepada sel. Tetapi sekarang, dengan ciri baru ini, pelbagai nilai dapat dikembalikan.

Sebagai contoh, dalam versi Excel 2019 dan terdahulu, katakan anda menggunakan formula = d2: d5 pada sel, hasilnya akan terhad kepada sel pertama.

Apabila kita terpaksa menggunakan formula pada semua sel yang sepadan, kami menggunakan notasi array (Ctrl+Shift+Enter). Walau bagaimanapun, dalam Excel 365 tidak begitu. Apabila anda menggunakan formula yang sama, secara automatik nilai -nilai ditumpahkan ke semua sel yang sepadan. Rujuk gambar di bawah, untuk maklumat lanjut.

Kawasan sel di mana hasilnya tumpah ke dalam dipanggil Pelbagai tumpahan. Rujuk gambar di bawah

CATATAN:

• The Tumpahan diaktifkan secara automatik dengan array dinamik (pada masa ini ciri ini disokong hanya dalam Excel 365) dan ciri tidak dapat dilumpuhkan.
• Ciri tumpahan didayakan pada semua formula dengan atau tanpa fungsi.

Kesalahan tumpahan dilihat apabila formula dimaksudkan untuk mengembalikan pelbagai nilai, bagaimanapun, hasilnya tidak dapat diletakkan pada sel. Ralat kelihatan seperti berikut:

Kemungkinan sebab untuk menghadapi kesilapan #spill adalah:

• Julat tumpahan mengandungi beberapa nilai kerana hasilnya tidak dapat dihuni di sel
• Julat tumpahan telah menggabungkan sel.
• Apabila Lembaran Lama (dibuat menggunakan Excel 2016 atau lebih awal) dengan formula yang menyokong persimpangan tersirat dibuka di Excel365.
• Apabila anda menggunakan formula array dinamik di atas meja excel.

Sekiranya anda melihat ralat #spill di Excel, maka jangan risau. Dalam artikel ini, kami akan menunjukkan cara yang berbeza untuk mengenal pasti punca utama untuk isu ini dan juga melihat cara untuk memperbaiki ralat #spill

Isi kandungan

• Kenal pasti apa yang menyebabkan ralat #spill
• Pembetulan yang akan diikuti apabila julat tumpahan tidak kosong
• Betulkan untuk diikuti apabila julat tumpahan telah menggabungkan sel
• Betulkan untuk diikuti apabila tumpahan di dalam jadual
• Betulkan untuk diikuti apabila julat tumpahan keluar dari ingatan
• Betulkan untuk diikuti apabila julat tumpahan tidak diketahui
• Pembetulan yang harus diikuti apabila julat tumpahan terlalu besar

Kenal pasti apa yang menyebabkan ralat #spill

Apabila anda melihat ralat tumpahan, periksa terlebih dahulu mengapa anda melihat ralat, untuk melakukannya,

Langkah 1: Klik pada sel yang menunjukkan #Spill! ralat

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

Langkah 3: Baris pertama memberitahu kita apa yang menyebabkan kesilapan. Contohnya, dalam kes ini, ralat dilihat sebagai julat tumpahan tidak kosong

Pembetulan yang akan diikuti apabila julat tumpahan tidak kosong

Ikuti pembaikan di bawah apabila anda melihat bahawa Julat tumpahan tidak kosong

Betulkan 1: Padamkan data yang menyekat julat tumpahan

Sekiranya terdapat beberapa data yang sudah ada dalam sel -sel dalam julat tumpahan, anda akan melihat ralat #spill untuk menggunakan formula.

Apabila anda dapat melihat data yang jelas menyekat julat tumpahan

Pertimbangkan contoh di bawah, apabila anda menggunakan formula = d2: d5 pada data, ralat tumpahan dilemparkan kerana ada Saya di sini dalam julat tumpahan.

Untuk menghilangkan ralat #spill, gerakkan data atau padam data dari julat tumpahan.

Apabila data menyekat julat tumpahan tersembunyi

Dalam beberapa kes, data yang menyekat julat tumpahan tersembunyi dan tidak begitu jelas seperti yang dilihat dalam kes 1. Pertimbangkan contoh di bawah,

Dalam kes sedemikian, untuk mencari sel yang menyekat julat tumpahan, ikuti langkah -langkah di bawah:

Langkah 1: Klik pada sel yang menunjukkan #Spill! ralat

Langkah 2: Klik pada Tanda seru Seperti yang ditunjukkan di bawah, anda dapat melihat bahawa ralatnya adalah kerana Julat tumpahan tidak kosong.

Langkah 3: Dari drop-down, klik pada Pilih menghalang sel

Langkah 4: Sel yang menyekat julat tumpahan diserlahkan seperti yang ditunjukkan di bawah

Sekarang, anda tahu sel mana yang menyekat, periksa apa sebenarnya yang menyebabkan masalah.

Langkah 5: Semasa pemeriksaan sel yang teliti, anda dapat melihat beberapa data yang tersembunyi di dalam sel.

Seperti yang dilihat dalam imej di atas, terdapat beberapa data. Oleh kerana fon mempunyai warna putih, tidak mudah untuk mengenali penyumbatan. Untuk menghilangkan kesilapan, padamkan data dari sel dalam julat tumpahan.

Menetapkan 2: Keluarkan pemformatan nombor tersuai; ; ; digunakan di sel

Kadang -kadang, apabila pemformatan nombor tersuai ; ; ; digunakan pada sel, ada peluang untuk melihat ralat tumpahan. Dalam kes sedemikian,

Langkah 1: Klik pada sel yang menunjukkan #Spill! ralat

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

Langkah 3: Dari drop-down, klik pada Pilih menghalang sel

Langkah 4: Sel yang menyekat julat tumpahan diserlahkan seperti yang ditunjukkan di bawah

Langkah 5: Klik kanan pada sel menghalang.

Langkah 6: Pilih Sel format

Langkah 7: Tetingkap Format Sel dibuka. Pergi ke Nombor tab

Langkah 8: Dari anak tetingkap kiri, pilih Adat

Langkah 9: Dari anak tetingkap sebelah kanan, tukar jenis dari; ; ; ke Umum

Langkah 10: Klik pada Okey butang

Betulkan untuk diikuti apabila julat tumpahan telah menggabungkan sel

Sekiranya anda melihat bahawa ralatnya adalah kerana Pelbagai tumpahan telah menggabungkan sel seperti yang ditunjukkan di bawah,

Langkah 1: Klik Pilih menghalang sel dari drop-down

Langkah 2: Sel menghalang akan diserlahkan

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

Langkah 4: Dari drop-down, pilih Sel -sel unmerge

Betulkan untuk diikuti ketika Jarak tumpahan dalam jadual

Formula array dinamik tidak disokong dalam jadual excel. Sekiranya anda melihat ralat #Spill pada jadual Excel seperti yang ditunjukkan di bawah dengan mesej Tumpah dalam jadual,

Langkah 1: Pilih jadual sepenuhnya

Langkah 2: Klik pada Reka bentuk jadual tab dari bar menu atas

Langkah 3: Pilih Tukar ke julat

Langkah 4: Anda akan melihat dialog pengesahan muncul, klik Ya

Betulkan untuk diikuti apabila julat tumpahan keluar dari ingatan

Apabila anda cuba mengenal pasti punca ralat #spill, jika anda melihat bahawa kesilapan menyatakan Lupa, maka itu kerana formula array dinamik yang anda gunakan rujukan julat yang besar, dalam kes seperti itu, Excel berjalan Lupa menyebabkan ralat tumpahan. Untuk mengatasi kesilapan, seseorang boleh mencuba merujuk julat yang lebih kecil.

Betulkan untuk diikuti apabila julat tumpahan tidak diketahui

Kesalahan ini dilihat apabila saiz perubahan tumpahan tumpah dan Excel tidak dapat menubuhkan saiz julat tumpah. Umumnya, apabila anda menggunakan fungsi rawak seperti Randarray, Rand, atau Randbet antara fungsi array dinamik seperti urutan ralat ini dilihat.

Untuk memahaminya dengan lebih baik, pertimbangkan contoh di bawah, katakan urutan fungsi (randbetween (1,100)) digunakan. Di sini randbetween menghasilkan integer rawak yang lebih besar daripada atau sama dengan 1 dan kurang dari atau sama dengan 100. Dan urutan menjana nombor berurutan (contohnya urutan (5) menjana 1,2,3,4,5). Walau bagaimanapun, Randbetween adalah fungsi yang tidak menentu dan terus mengubah nilainya setiap kali lembaran Excel dibuka atau diubah. Kerana ini, fungsi urutan tidak dapat menentukan saiz array yang harus dihasilkan. Ia tidak akan tahu berapa banyak nilai untuk menjana dan dengan itu membuang ralat tumpahan.

Apabila anda mengenal pasti punca kesilapan, anda lihat Julat tumpahan tidak diketahui

Untuk menyelesaikan kesilapan seperti ini, Cuba mempunyai formula yang berbeza yang sesuai dengan keperluan anda.

Pembetulan yang harus diikuti apabila julat tumpahan terlalu besar

Katakan anda mengenal pasti punca itu dan anda perhatikan bahawa ralat itu dilihat kerana Jarak tumpahan terlalu besar seperti yang ditunjukkan di bawah.

Apabila array dinamik tidak disediakan, terdapat sesuatu yang disebut persimpangan tersirat di Excel yang terpaksa mengembalikan hasil tunggal walaupun formula itu berpotensi untuk mengembalikan pelbagai hasil. Pertimbangkan contoh, jika formula = B: b*5% digunakan pada versi Excel 2019 atau terdahulu, dengan persimpangan tersirat di tempat, hasilnya akan menjadi seperti berikut:

Walau bagaimanapun, apabila formula yang sama digunakan dalam Excel 365, anda melihat ralat berikut

Untuk menyelesaikannya, cubalah pembetulan berikut

Betulkan 1: Sapukan persimpangan tersirat menggunakan @ operator

Apabila kita katakan = B: b, Arahan dinamik akan merujuk seluruh lajur B. Sebaliknya, kita boleh memaksa Excel untuk mengenakan persimpangan tersirat menggunakan @ operator

Tukar formula ke =@B: b*5%

Kerana persimpangan tersirat ditambah, formula akan digunakan pada satu sel. Untuk melanjutkan formula,

1. Cukup klik pada titik seperti yang ditunjukkan di bawah

2. Seret ke sel seperti yang diperlukan. Ini akan menggunakan formula yang sama dengan sel -sel ini.

Betulkan 2: Daripada merujuk kepada lajur, rujuk julat

Dalam formula, = B: b*5% , Kami merujuk kepada lajur b. Sebaliknya merujuk kepada julat tertentu, = B2: b4*5%

Itu sahaja

Kami harap artikel ini bermaklumat.

Suka dan komen jika anda dapat menyelesaikan masalah dengan kaedah di atas.

Terima kasih kerana membaca.