Artikel ini akan membantu anda memahami semua punca ralat #SPILL serta penyelesaian untuk membetulkannya dalam Excel 365.
#TUHAN! ialah jenis ralat Excel baharu yang berlaku terutamanya apabila formula yang menghasilkan berbilang hasil pengiraan cuba memaparkan outputnya dalam julat tumpahan tetapi julat itu sudah mengandungi beberapa data lain.
Data menyekat boleh jadi apa sahaja termasuk, nilai teks, sel yang digabungkan, aksara ruang kosong, atau walaupun tidak ada tempat yang mencukupi untuk mengembalikan hasil. Penyelesaiannya adalah mudah, sama ada kosongkan julat mana-mana data yang menyekat atau pilih tatasusunan sel kosong yang tidak mengandungi sebarang jenis data di dalamnya.
Ralat tumpahan biasanya berlaku apabila mengira formula tatasusunan dinamik, kerana formula tatasusunan dinamik ialah formula yang mengeluarkan hasil ke dalam berbilang sel atau tatasusunan. Mari lihat lebih terperinci dan fahami perkara yang mencetuskan ralat ini dalam Excel dan cara menyelesaikannya.
Apakah yang menyebabkan ralat tumpahan?
Sejak pelancaran tatasusunan Dinamik pada 2018, formula Excel boleh mengendalikan berbilang nilai pada satu masa dan mengembalikan hasil dalam lebih daripada satu sel. Tatasusunan dinamik ialah tatasusunan boleh ubah saiz yang membenarkan formula mengembalikan berbilang hasil kepada julat sel pada lembaran kerja berdasarkan formula yang dimasukkan dalam sel tunggal.
Apabila formula tatasusunan dinamik mengembalikan berbilang hasil, hasil ini secara automatik tumpah ke dalam sel jiran. Tingkah laku ini dipanggil 'Spill' dalam Excel. Dan julat sel di mana keputusan tumpah ke dalam dipanggil 'julat tumpahan'. Julat tumpahan akan berkembang atau mengecut secara automatik berdasarkan nilai sumber.
Jika formula cuba mengisi julat tumpahan dengan berbilang hasil tetapi disekat oleh sesuatu pada julat itu, maka ralat #SPILL berlaku.
Excel kini mempunyai 9 fungsi yang menggunakan fungsi Dynamic Array untuk menyelesaikan masalah, ini termasuk:
- URUTAN
- PENAPIS
- TRANSPOSE
- SUSUN
- DISUSUN MENGIKUT
- RANDARRAY
- UNIK
- XLOOKUP
- XMATCH
Formula tatasusunan dinamik hanya tersedia dalam 'Excel 365' dan pada masa ini ia tidak disokong oleh mana-mana perisian Excel luar talian (iaitu Microsoft Excel 2016, 2019).
Ralat tumpahan bukan sahaja disebabkan oleh menghalang data, terdapat beberapa sebab mengapa anda mungkin mendapat ralat #Spill. Marilah kami meneroka situasi yang berbeza di mana anda mungkin menghadapi #TUHAN! ralat dan cara membetulkannya.
Julat Tumpahan tidak Kosong
Salah satu punca utama ralat tumpahan ialah julat tumpahan tidak kosong. Contohnya, jika anda cuba memaparkan 10 hasil, tetapi jika terdapat sebarang data dalam mana-mana sel dalam kawasan tumpahan, formula mengembalikan #TUMPAHAN! ralat.
Contoh 1:
Dalam contoh di bawah, kami telah memasukkan fungsi TRANSPOSE dalam sel C2 untuk menukar julat menegak sel (B2:B5) kepada julat mendatar (C2:F2). Daripada menukar lajur kepada baris, Excel menunjukkan kepada kami #TUHAN! ralat.
Dan apabila anda mengklik pada sel formula, anda akan melihat sempadan biru putus-putus yang menunjukkan kawasan/julat tumpahan (C2:F2) yang diperlukan untuk memaparkan keputusan seperti ditunjukkan di bawah. Selain itu, anda akan melihat tanda amaran kuning dengan tanda seru padanya.
Untuk memahami sebab di sebalik ralat, klik ikon amaran di sebelah ralat dan lihat mesej dalam baris pertama yang diserlahkan dalam warna kelabu. Seperti yang anda lihat, ia berkata 'Julat tumpahan tidak kosong' di sini.
Masalahnya di sini ialah sel dalam julat tumpahan D2 dan E2 mempunyai aksara teks (tidak kosong), oleh itu, ralat.
Penyelesaian:
Penyelesaiannya mudah, sama ada kosongkan data (sama ada alih atau padam) yang terletak dalam julat tumpahan atau pindahkan formula ke lokasi lain yang tiada halangan.
Sebaik sahaja anda memadam atau mengalihkan sekatan, Excel akan mengisi sel secara automatik dengan hasil formula. Di sini, apabila kita mengosongkan teks dalam D2 dan E2, formula mengubah lajur ke baris seperti yang dimaksudkan.
Contoh 2:
Dalam contoh di bawah, walaupun julat tumpahan kelihatan kosong, formula masih menunjukkan Spill! ralat. Ini kerana tumpahan itu sebenarnya tidak kosong, ia mempunyai watak ruang yang tidak kelihatan dalam salah satu sel.
Sukar untuk mencari watak angkasa atau mana-mana watak halimunan lain yang bersembunyi di dalam sel yang kelihatan kosong. Untuk mencari sel sedemikian dengan data yang tidak diingini, klik Ralat floatie (tanda amaran) dan pilih 'Pilih Sel Menghalang' daripada menu dan ia akan membawa anda ke sel yang mengandungi data yang menghalang.
Seperti yang anda lihat, dalam tangkapan skrin di bawah, sel E2 mempunyai dua aksara ruang. Apabila anda mengosongkan data tersebut, anda akan mendapat output yang betul.
Kadangkala, watak halimunan boleh menjadi teks yang diformatkan dengan warna fon yang sama seperti warna isian sel atau nilai sel tersuai yang diformatkan dengan kod nombor ;;;. Apabila anda memformat nilai sel tersuai dengan ;;;, ia akan menyembunyikan apa sahaja dalam sel itu, tanpa mengira warna fon atau warna sel.
Julat Tumpahan Mengandungi Sel Bercantum
Kadang-kadang, #TUHAN! ralat berlaku apabila julat tumpahan mengandungi sel yang digabungkan. Formula tatasusunan dinamik tidak berfungsi dengan sel yang digabungkan. Untuk membetulkannya, anda hanya perlu menyahcantum sel dalam julat tumpahan atau mengalihkan formula ke julat lain yang tidak mempunyai sel bercantum.
Dalam contoh di bawah, walaupun julat tumpahan kosong (C2:CC8), formula mengembalikan ralat Tumpahan. Ini kerana sel C4 dan C5 digabungkan.
Untuk memastikan sel yang digabungkan adalah sebab anda mendapat ralat, klik padatanda amaran dan sahkan punca - 'Julat tumpahan telah bergabung sel'.
Penyelesaian:
Untuk menyahgabungkan sel, pilih sel yang digabungkan, kemudian pada tab 'Rumah', klik butang 'Gabung & Pusat' dan pilih 'Nyahgabungkan Sel'.
Jika anda mengalami kesukaran untuk mencari sel yang digabungkan dalam hamparan besar anda, klik pada pilihan 'Pilih Sel Menghalang' daripada menu tanda amaran untuk melompat ke sel yang digabungkan.
Julat Tumpahan dalam Jadual
Formula tatasusunan tumpah tidak disokong dalam jadual Excel. Formula tatasusunan dinamik hanya boleh dimasukkan dalam sel individu tunggal. Jika anda memasukkan formula tatasusunan tertumpah dalam jadual atau apabila kawasan tumpahan jatuh ke dalam jadual, anda akan mendapat ralat Tumpahan. Apabila ini berlaku, cuba tukar jadual kepada julat biasa atau alihkan formula di luar jadual.
Contohnya, apabila kita memasukkan formula julat tertumpah berikut dalam jadual Excel, kita akan mendapat ralat Tumpahan dalam setiap sel jadual, bukan hanya sel formula. Ini kerana Excel secara automatik menyalin sebarang formula yang dimasukkan dalam jadual ke setiap sel dalam lajur jadual.
Selain itu, anda akan mendapat ralat tumpahan apabila formula cuba menumpahkan hasil dalam jadual. Dalam tangkapan skrin di bawah, kawasan tumpahan berada dalam jadual sedia ada, jadi kami mendapat ralat Tumpahan.
Untuk mengesahkan punca ralat ini, klik tanda amaran dan lihat sebab ralat - 'Julat tumpahan dalam jadual'
Penyelesaian:
Untuk membetulkan ralat, anda perlu mengembalikan jadual Excel kepada julat. Untuk berbuat demikian, klik kanan di mana-mana dalam jadual, klik 'Jadual', dan kemudian pilih pilihan 'Tukar kepada Julat'. Sebagai alternatif, anda boleh klik kiri di mana-mana dalam jadual, kemudian pergi ke tab 'Reka Bentuk Jadual' dan pilih pilihan 'Tukar kepada Julat'.
Julat Tumpahan Tidak Diketahui
Jika Excel tidak dapat menetapkan saiz tatasusunan tumpah, ia akan mencetuskan ralat Tumpahan. Kadangkala, formula membolehkan tatasusunan dinamik mengubah saiz antara setiap pas pengiraan. Jika saiz tatasusunan dinamik terus berubah semasa pengiraan berlalu dan tidak mengimbangi, ia akan menyebabkan #TUHAN! ralat.
Ralat Tumpahan jenis ini biasanya dicetuskan apabila menggunakan fungsi yang tidak menentu seperti fungsi RAND, RANDARRAY, RANDBETWEEN, OFFSET dan TIDAK LANGSUNG.
Sebagai contoh, apabila kami menggunakan formula di bawah dalam sel B3, kami mendapat ralat Tumpahan:
=JURUTAN(RANDBETWEEN(1, 500))Dalam contoh, fungsi RANDBETWEEN mengembalikan integer rawak antara nombor 1 dan 500, dan outputnya sentiasa berubah. Dan fungsi SEQUENCE tidak tahu berapa banyak nilai yang perlu dihasilkan dalam tatasusunan tumpahan. Oleh itu, ralat #SPILL.
Anda juga boleh mengesahkan punca ralat dengan mengklik tanda Amaran - 'Julat tumpahan tidak diketahui'.
Penyelesaian:
Untuk membetulkan ralat untuk formula ini, satu-satunya pilihan anda ialah menggunakan formula yang berbeza untuk pengiraan anda.
Julat Tumpahan Terlalu Besar
Kadangkala anda mungkin melaksanakan formula yang mengeluarkan julat tumpah yang terlalu besar untuk dikendalikan oleh lembaran kerja, dan ia mungkin melangkaui tepi lembaran kerja. Apabila itu berlaku anda mungkin mendapat #TUHAN! ralat. Untuk menyelesaikan isu ini, anda boleh cuba merujuk julat tertentu atau satu sel dan bukannya keseluruhan lajur atau menggunakan aksara '@' untuk mendayakan persimpangan tersirat
Dalam contoh di bawah, kami cuba mengira 20% daripada nombor Jualan dalam lajur A dan mengembalikan hasil dalam lajur B, tetapi sebaliknya, kami mendapat ralat Tumpahan.
Formula dalam B3 mengira 20% daripada nilai dalam A3, kemudian 20% daripada nilai dalam A4, dan seterusnya. Ia menghasilkan lebih sejuta hasil (1,048,576) dan menumpahkan kesemuanya dalam lajur B bermula dalam sel B3, tetapi ia akan sampai ke penghujung lembaran kerja. Tidak ada ruang yang mencukupi untuk menunjukkan semua output, akibatnya, kami mendapat ralat #SPILL.
Seperti yang anda boleh lihat punca ralat ini ialah - 'Julat tumpahan terlalu besar'.
Penyelesaian:
Untuk menyelesaikan isu ini, cuba tukar keseluruhan lajur dengan julat yang berkaitan atau rujukan sel tunggal atau tambah operator @ untuk melakukan persilangan tersirat.
Betulkan 1: Anda boleh cuba merujuk julat dan bukannya keseluruhan lajur. Di sini, kami menukar keseluruhan julat A:A dengan A3:A11 dalam formula dan formula akan mengisi julat secara automatik dengan hasil.
Betulkan 2: Gantikan keseluruhan lajur dengan hanya rujukan sel pada baris yang sama (A3), dan kemudian salin formula ke bawah julat menggunakan pemegang isian.
Betulkan 3: Anda juga boleh cuba menambah operator @ sebelum rujukan untuk melakukan persilangan tersirat. Ini akan memaparkan output dalam sel formula sahaja.
Kemudian, salin formula dari sel B3 ke seluruh julat.
Nota: Apabila anda mengedit formula tumpahan, anda hanya boleh mengedit sel pertama dalam kawasan/julat tumpahan. Anda boleh melihat formula dalam sel lain dalam julat tumpahan, tetapi ia akan dikelabukan dan tidak boleh dikemas kini.
Lupa
Jika anda melaksanakan formula tatasusunan tertumpah yang menyebabkan Excel kehabisan memori, ia boleh mencetuskan ralat #SPILL. Dalam keadaan tersebut, cuba rujuk tatasusunan atau julat yang lebih kecil.
Tidak Diiktiraf / Fallback
Anda juga boleh mendapat ralat Tumpahan walaupun Excel tidak mengenali atau tidak dapat mendamaikan punca ralat. Dalam kes sedemikian, semak semula formula anda dan pastikan semua parameter fungsi adalah betul.
Kini, anda tahu semua punca dan penyelesaian untuk #TUHAN! ralat dalam Excel 365.