Tutulungan ka ng artikulong ito na maunawaan ang lahat ng mga sanhi ng mga error sa #SPILL pati na rin ang mga solusyon upang ayusin ang mga ito sa Excel 365.
#SILL! ay isang bagong uri ng error sa Excel na pangunahing nangyayari kapag ang isang formula na gumagawa ng maraming resulta ng pagkalkula na sinusubukang ipakita ang mga output nito sa isang spill range ngunit ang hanay na iyon ay naglalaman na ng ilang iba pang data.
Ang pagharang ng data ay maaaring maging anumang bagay kabilang ang, text value, pinagsamang mga cell, isang plain space na character, o kahit na walang sapat na lugar upang ibalik ang mga resulta. Ang solusyon ay simple, alinman sa i-clear ang hanay ng anumang pagharang ng data o pumili ng isang walang laman na hanay ng mga cell na hindi naglalaman ng anumang uri ng data dito.
Ang spill error ay kadalasang nangyayari kapag kinakalkula ang mga dynamic na array formula, dahil ang dynamic na array formula ay ang naglalabas ng mga resulta sa maraming cell o array. Tingnan natin ang higit pang detalye at unawain kung ano ang nag-trigger ng error na ito sa Excel at kung paano ito lutasin.
Ano ang nagiging sanhi ng isang spill error?
Dahil, ang paglulunsad ng mga Dynamic na array noong 2018, ang mga formula ng Excel ay maaaring humawak ng maramihang mga halaga sa isang pagkakataon at magbalik ng mga resulta sa higit sa isang cell. Ang mga dynamic na array ay mga resizable array na nagbibigay-daan sa mga formula na magbalik ng maraming resulta sa isang hanay ng mga cell sa worksheet batay sa isang formula na inilagay sa isang cell.
Kapag ang isang dynamic na array formula ay nagbabalik ng maraming resulta, ang mga resultang ito ay awtomatikong dumaloy sa mga kalapit na cell. Ang pag-uugaling ito ay tinatawag na 'Spill' sa Excel. At ang hanay ng mga cell kung saan dumaloy ang mga resulta ay tinatawag na 'Spill range'. Ang saklaw ng spill ay lalawak o awtomatikong mag-ikli batay sa mga halaga ng pinagmulan.
Kung sinusubukan ng isang formula na punan ang isang spill range na may maraming resulta ngunit na-block ng isang bagay sa range na iyon, magkakaroon ng #SPILL error.
Ang Excel ay mayroon na ngayong 9 na function na gumagamit ng Dynamic Array functionality upang malutas ang mga problema, kabilang dito ang:
- PAGSUNOD
- FILTER
- TRANSPOSE
- PAGBUBAYIN
- SORTBY
- RANDARRAY
- NATATANGING
- XLOOKUP
- XMATCH
Available lang ang mga dynamic na array formula sa 'Excel 365' at kasalukuyan itong hindi sinusuportahan ng alinman sa offline na Excel software (ibig sabihin, Microsoft Excel 2016, 2019).
Ang mga error sa pag-spill ay hindi lamang sanhi ng pagharang sa data, may ilang mga dahilan kung bakit maaari kang makakuha ng #Spill error. Tuklasin natin ang iba't ibang sitwasyon kung saan maaari kang makatagpo ng #SPILL! error at kung paano ayusin ang mga ito.
Ang Spill Range ay hindi Blangko
Ang isa sa mga pangunahing dahilan para sa spill error ay ang saklaw ng spill ay hindi walang laman. Halimbawa, kung sinusubukan mong magpakita ng 10 resulta, ngunit kung mayroong anumang data sa alinman sa mga cell sa spill area, ang formula ay nagbabalik ng #SPILL! pagkakamali.
Halimbawa 1:
Sa halimbawa sa ibaba, ipinasok namin ang TRANSPOSE function sa cell C2 upang i-convert ang patayong hanay ng mga cell (B2:B5) sa isang pahalang na hanay (C2:F2). Sa halip na ilipat ang column sa isang row, ipinapakita sa amin ng Excel ang #SPILL! pagkakamali.
At kapag nag-click ka sa formula cell, makakakita ka ng dashed-blue border na nagpapahiwatig ng spill area/range (C2:F2) na kailangan upang ipakita ang mga resulta tulad ng ipinapakita sa ibaba. Gayundin, mapapansin mo ang isang dilaw na tanda ng babala na may tandang padamdam.
Upang maunawaan ang dahilan sa likod ng error, i-click ang icon ng babala sa tabi ng error at tingnan ang mensahe sa unang linya na naka-highlight sa kulay abo. Gaya ng nakikita mo, nakasaad dito ang 'Spill range isn't blank' dito.
Ang problema dito ay ang mga cell sa spill range D2 at E2 ay may mga text na character (hindi walang laman), kaya, ang error.
Solusyon:
Ang solusyon ay simple, maaaring i-clear ang data (ilipat o tanggalin) na matatagpuan sa hanay ng spill o ilipat ang formula sa ibang lokasyon kung saan walang sagabal.
Sa sandaling tanggalin mo o ilipat ang blockage, awtomatikong pupunuin ng Excel ang mga cell ng mga resulta ng formula. Dito, kapag na-clear namin ang text sa D2 at E2, inililipat ng formula ang column sa row ayon sa nilalayon.
Halimbawa 2:
Sa halimbawa sa ibaba, bagama't mukhang walang laman ang saklaw ng spill, ipinapakita pa rin ng formula ang Spill! pagkakamali. Ito ay dahil ang spill ay hindi aktwal na walang laman, mayroon itong hindi nakikitang karakter sa espasyo sa isa sa mga cell.
Mahirap hanapin ang mga space character o anumang iba pang invisible na character na nagtatago sa tila walang laman na mga cell. Upang mahanap ang mga naturang cell na may hindi gustong data, i-click ang Error floatie (sign ng babala) at piliin ang 'Piliin ang Mga Nakahaharang na Cell' mula sa menu at dadalhin ka nito sa cell na naglalaman ng data na humahadlang.
Tulad ng nakikita mo, sa screenshot sa ibaba, ang cell E2 ay may dalawang space character. Kapag na-clear mo ang data na iyon, makukuha mo ang tamang output.
Minsan, ang invisible na character ay maaaring isang text na na-format na may parehong kulay ng font gaya ng fill color ng cell o isang cell value na custom na naka-format gamit ang number code ;;;. Kapag nag-format ka ng custom na halaga ng cell gamit ang ;;;, itatago nito ang anumang bagay sa cell na iyon, anuman ang kulay ng font o kulay ng cell.
Ang Spill Range ay Naglalaman ng Mga Pinagsamang Cell
Minsan, ang #SPILL! nangyayari ang error kapag ang saklaw ng spill ay naglalaman ng mga pinagsamang cell. Hindi gumagana ang dynamic na array formula sa mga pinagsama-samang cell. Para ayusin ito, ang kailangan mo lang gawin ay i-unmerge ang mga cell sa spill range o ilipat ang formula sa isa pang range na walang mga merged na cell.
Sa halimbawa sa ibaba, kahit na walang laman ang saklaw ng spill (C2:CC8), ibinabalik ng formula ang Spill error. Ito ay dahil ang mga cell C4 at C5 ay pinagsama.
Upang matiyak na ang mga pinagsamang cell ang dahilan kung bakit ka nagkaka-error, mag-click sasenyales ng babala at i-verify ang sanhi - 'Spill range has merged cell'.
Solusyon:
Upang i-unmerge ang mga cell, piliin ang pinagsamang mga cell, pagkatapos ay sa tab na 'Home', i-click ang button na 'Pagsamahin at Igitna' at piliin ang 'I-unmerge ang Mga Cell'.
Kung nahihirapan kang hanapin ang mga pinagsanib na cell sa iyong malaking spreadsheet, mag-click sa opsyong ‘Piliin ang Mga Nakahaharang na Cell’ mula sa menu ng babalang tanda upang tumalon sa pinagsanib na mga cell.
Saklaw ng Spill sa Table
Ang mga spilled array formula ay hindi sinusuportahan sa mga Excel table. Ang dynamic na array formula ay dapat lamang ilagay sa isang indibidwal na cell. Kung maglalagay ka ng spilled array formula sa isang table o kapag nahulog ang spill area sa isang table, makukuha mo ang Spill error. Kapag nangyari ito, subukang i-convert ang talahanayan sa isang normal na hanay o ilipat ang formula sa labas ng talahanayan.
Halimbawa, kapag ipinasok natin ang sumusunod na formula ng spilled range sa isang Excel table, makakakuha tayo ng Spill error sa bawat cell ng table, hindi lang ang formula cell. Ito ay dahil awtomatikong kinokopya ng Excel ang anumang formula na ipinasok sa isang talahanayan sa bawat cell sa column ng talahanayan.
Gayundin, makakakuha ka ng spill error kapag sinubukan ng formula na ibuhos ang mga resulta sa isang talahanayan. Sa screenshot sa ibaba, ang spill area ay nasa kasalukuyang talahanayan, kaya nakakakuha kami ng Spill error.
Upang kumpirmahin ang dahilan sa likod ng error na ito, i-click ang babala at tingnan ang dahilan ng error - 'Spill range in table'
Solusyon:
Upang ayusin ang error, kakailanganin mong ibalik ang talahanayan ng Excel sa hanay. Upang gawin iyon, mag-right-click saanman sa loob ng talahanayan, i-click ang 'Talahanayan', at pagkatapos ay piliin ang opsyong 'I-convert sa Saklaw'. Bilang kahalili, maaari kang mag-left-click saanman sa loob ng talahanayan, pagkatapos ay pumunta sa tab na 'Disenyo ng Talahanayan' at piliin ang opsyong 'I-convert sa Saklaw'.
Ang Spill Range ay Hindi Alam
Kung hindi nagawang itatag ng Excel ang laki ng natapong array, magti-trigger ito ng Spill error. Minsan, binibigyang-daan ng formula ang isang dynamic na array na baguhin ang laki sa pagitan ng bawat pass sa pagkalkula. Kung ang laki ng dynamic na array ay patuloy na nagbabago habang lumilipas ang mga kalkulasyon at hindi nabalanse, magdudulot ito ng #SPILL! Error.
Karaniwang nati-trigger ang ganitong uri ng Spill error kapag gumagamit ng mga pabagu-bagong function tulad ng RAND, RANDARRAY, RANDBETWEEN, OFFSET, at INDIRECT na mga function.
Halimbawa, kapag ginamit namin ang formula sa ibaba sa cell B3, nakukuha namin ang Spill error:
=SEQUENCE(RANDBETWEEN(1, 500))Sa halimbawa, ang RANDBETWEEN function ay nagbabalik ng random na integer sa pagitan ng mga numero 1 at 500, at ang output nito ay patuloy na nagbabago. At hindi alam ng SEQUENCE function kung gaano karaming mga value ang gagawin sa isang spill array. Kaya, ang #SPILL error.
Maaari mo ring kumpirmahin ang sanhi ng error sa pamamagitan ng pag-click sa Babala sign – ‘Spill range is unknown’.
Solusyon:
Upang ayusin ang error para sa formula na ito, ang tanging pagpipilian mo ay gumamit ng ibang formula para sa iyong pagkalkula.
Masyadong Malaki ang Spill Range
Minsan maaari kang magsagawa ng formula na naglalabas ng natapong hanay na masyadong malaki para mahawakan ng worksheet, at maaari itong lumampas sa mga gilid ng worksheet. Kapag nangyari iyon maaari kang makakuha ng #SPILL! pagkakamali. Upang ayusin ang isyung ito, maaari mong subukang mag-refer ng isang partikular na hanay o isang cell sa halip na ang buong column o gamitin ang character na '@' para paganahin ang implicit intersection
Sa halimbawa sa ibaba, sinusubukan naming kalkulahin ang 20% ng mga numero ng Benta sa column A at ibalik ang mga resulta sa column B, ngunit sa halip, nakakakuha kami ng Spill error.
Kinakalkula ng formula sa B3 ang 20% ng value sa A3, pagkatapos ay 20% ng value sa A4, at iba pa. Gumagawa ito ng higit sa isang milyong resulta ( 1,048,576) at itinapon ang lahat ng ito sa column B simula sa cell B3, ngunit aabot ito sa dulo ng worksheet. Walang sapat na espasyo para ipakita ang lahat ng mga output, bilang resulta, nakakakuha kami ng #SPILL error.
Tulad ng nakikita mo ang sanhi ng error na ito ay ang - 'Spill range is too big'.
Mga solusyon:
Upang malutas ang isyung ito, subukang baguhin ang buong column gamit ang isang nauugnay na hanay o isang solong cell na reference, o idagdag ang @ operator upang magsagawa ng implicit na intersection.
Ayusin 1: Maaari mong subukang mag-refer ng mga hanay sa halip na buong column. Dito, binabago namin ang buong hanay na A:A na may A3:A11 sa formula, at awtomatikong pupunuin ng formula ang hanay ng mga resulta.
Ayusin 2: Palitan ang buong column ng cell reference lang sa parehong row (A3), at pagkatapos ay kopyahin ang formula pababa sa range gamit ang fill handle.
Ayusin ang 3: Maaari mo ring subukang idagdag ang @ operator bago ang reference upang magsagawa ng implicit intersection. Ipapakita nito ang output sa formula cell lamang.
Pagkatapos, kopyahin ang formula mula sa cell B3 hanggang sa natitirang bahagi ng hanay.
Tandaan: Kapag nag-e-edit ka ng spilled formula, maaari mo lang i-edit ang unang cell sa spill area/range. Maaari mong makita ang formula sa iba pang mga cell ng saklaw ng spill, ngunit magiging kulay-abo ang mga ito at hindi maa-update.
Wala sa memorya
Kung mag-execute ka ng spilled array formula na nagiging sanhi ng Excel na maubusan ng memory, maaari itong mag-trigger ng #SPILL error. Sa ilalim ng mga sitwasyong iyon, subukang mag-refer ng mas maliit na array o range.
Hindi kinikilala / Fallback
Maaari ka ring makakuha ng Spill error kahit na hindi nakilala ng Excel o hindi mapagkasundo ang sanhi ng error. Sa ganitong mga kaso, i-double check ang iyong formula at tiyaking tama ang lahat ng parameter ng mga function.
Ngayon, alam mo na ang lahat ng dahilan at solusyon para sa #SPILL! mga error sa Excel 365.