Овај чланак ће вам помоћи да разумете све узроке #СПИЛЛ грешака, као и решења за њихово исправљање у програму Екцел 365.
#СПИЛЛ! је нова врста грешке у Екцел-у која се углавном јавља када формула која даје вишеструке резултате прорачуна покушава да прикаже своје излазе у опсегу изливања, али тај опсег већ садржи неке друге податке.
Подаци за блокирање могу бити било шта укључујући текстуалну вредност, спојене ћелије, размак, или чак када нема довољно места за враћање резултата. Решење је једноставно, или обришите опсег свих блокирајућих података или изаберите празан низ ћелија које не садрже никакву врсту података у себи.
Грешка преливања се обично дешава када се израчунавају формуле динамичког низа, јер је формула динамичког низа она која резултате даје у више ћелија или низ. Хајде да погледамо детаљније и разумемо шта покреће ову грешку у Екцелу и како да је решимо.
Шта узрокује грешку при изливању?
Од покретања динамичких низова 2018. године, Екцел формуле могу да обрађују више вредности истовремено и враћају резултате у више од једне ћелије. Динамички низови су низови променљиве величине који омогућавају формулама да врате више резултата у опсег ћелија на радном листу на основу формуле унете у једну ћелију.
Када формула динамичког низа врати више резултата, ови резултати се аутоматски преливају у суседне ћелије. Ово понашање се у Екцел-у назива „Просипање“. А опсег ћелија у који се резултати преливају назива се „опсег изливања“. Опсег изливања ће се аутоматски проширити или скупити на основу изворних вредности.
Ако формула покушава да попуни опсег просуте са више резултата, али је блокира нешто у том опсегу, онда се јавља грешка #СПИЛЛ.
Екцел сада има 9 функција које користе функцију динамичког низа за решавање проблема, а то укључује:
- НИЗ
- ФИЛТЕР
- ТРАНСПОСЕ
- ВРСТА
- СОРТИРАЈ ПО
- РАНДАРРАИ
- УНИКУЕ
- КСЛООКУП
- КСМАТЦХ
Формуле динамичког низа су доступне само у „Екцел 365“ и тренутно их не подржава ниједан офлајн Екцел софтвер (тј. Мицрософт Екцел 2016, 2019).
Грешке при изливању нису узроковане само ометањем података, постоји неколико разлога зашто можете добити грешку #Спилл. Дозволите нам да истражимо различите ситуације у којима можете наићи на #ПРОЛИВАЊЕ! грешке и како их поправити.
Опсег изливања није празан
Један од примарних узрока грешке при изливању је тај што опсег изливања није празан. На пример, ако покушавате да прикажете 10 резултата, али ако има било каквих података у било којој од ћелија у области изливања, формула враћа #ПРОЛИВАЊЕ! грешка.
Пример 1:
У примеру испод, унели смо функцију ТРАНСПОСЕ у ћелију Ц2 да бисмо претворили вертикални опсег ћелија (Б2:Б5) у хоризонтални опсег (Ц2:Ф2). Уместо да пребацује колону у ред, Екцел нам показује #СПИЛЛ! грешка.
А када кликнете на ћелију формуле, видећете испрекидану плаву ивицу која означава подручје/опсег изливања (Ц2:Ф2) који је потребан за приказ резултата као што је приказано испод. Такође, приметићете жути знак упозорења са знаком узвика на њему.
Да бисте разумели разлог за грешку, кликните на икону упозорења поред грешке и видите поруку у првом реду означену сивом бојом. Као што видите, овде пише „Опсег изливања није празан“.
Овде је проблем у томе што ћелије у опсегу Д2 и Е2 имају текстуалне знакове (нису празне), отуда и грешка.
Решење:
Решење је једноставно, или обришите податке (или преместите или избришите) који се налазе у опсегу изливања или преместите формулу на другу локацију где нема препрека.
Чим избришете или преместите блокаду, Екцел ће аутоматски попунити ћелије са резултатима формуле. Овде, када обришемо текст у Д2 и Е2, формула транспонује колону у ред како је предвиђено.
Пример 2:
У следећем примеру, иако је опсег изливања празан, формула и даље показује Проливање! грешка. То је зато што изливање заправо није празно, већ има карактер невидљивог простора у једној од ћелија.
Тешко је лоцирати знакове за размак или било који други невидљиви лик који се крије у ономе што изгледа као празне ћелије. Да бисте пронашли такве ћелије са нежељеним подацима, кликните на флоатие Еррор (знак упозорења) и изаберите „Изабери ћелије које ометају“ из менија и одвешће вас до ћелије која садржи податке који ометају.
Као што видите, на слици испод, ћелија Е2 има два знака за размак. Када избришете те податке, добићете одговарајући излаз.
Понекад невидљиви знак може бити текст форматиран истом бојом фонта као боја попуне ћелије или вредност ћелије прилагођена кодом броја ;;;. Када прилагођено форматирате вредност ћелије помоћу ;;;, она ће сакрити било шта у тој ћелији, без обзира на боју фонта или боју ћелије.
Опсег изливања садржи спојене ћелије
Понекад, #СПИЛЛ! грешка се јавља када опсег изливања садржи спојене ћелије. Формула динамичког низа не ради са спојеним ћелијама. Да бисте ово поправили, све што треба да урадите је да поништите спајање ћелија у опсегу преливања или преместите формулу у други опсег који нема спојене ћелије.
У примеру испод, иако је опсег изливања празан (Ц2:ЦЦ8), формула враћа грешку изливања. То је зато што су ћелије Ц4 и Ц5 спојене.
Да бисте били сигурни да су спојене ћелије разлог зашто добијате грешку, кликните назнак упозорења и проверите узрок – „Опсег изливања је спојио ћелију“.
Решење:
Да бисте опозвали спајање ћелија, изаберите спојене ћелије, а затим на картици „Почетна“ кликните на дугме „Споји и центар“ и изаберите „Опозови спајање ћелија“.
Ако вам је тешко да пронађете спојене ћелије у великој табели, кликните на опцију „Изабери ћелије које ометају“ у менију са знаком упозорења да бисте прешли на спојене ћелије.
Распон изливања у табели
Формуле просутих низова нису подржане у Екцел табелама. Формула динамичког низа треба да се унесе само у једну појединачну ћелију. Ако унесете просуту формулу низа у табелу или када подручје просуте пада у табелу, добићете грешку изливања. Када се то догоди, покушајте да конвертујете табелу у нормалан опсег или преместите формулу ван табеле.
На пример, када унесемо следећу формулу просутог опсега у Екцел табелу, добили бисмо грешку преливања у свакој ћелији табеле, а не само у ћелији формуле. То је зато што Екцел аутоматски копира било коју формулу унету у табелу у сваку ћелију у колони табеле.
Такође, добићете грешку при преливању када формула покуша да проспе резултате у табели. На снимку екрана испод, област изливања спада у постојећу табелу, тако да добијамо грешку изливања.
Да бисте потврдили узрок ове грешке, кликните на знак упозорења и погледајте разлог грешке – „Распон изливања у табели“
Решење:
Да бисте исправили грешку, мораћете да вратите Екцел табелу назад у опсег. Да бисте то урадили, кликните десним тастером миша било где у табели, кликните на „Табела“, а затим изаберите опцију „Претвори у опсег“. Алтернативно, можете да кликнете левим тастером миша било где у табели, а затим идите на картицу „Дизајн табеле“ и изаберете опцију „Претвори у опсег“.
Распон изливања је непознат
Ако Екцел није могао да утврди величину просутог низа, то ће покренути грешку изливања. Понекад формула омогућава динамичком низу да промени величину између сваког прорачуна. Ако величина динамичког низа настави да се мења током проласка прорачуна и не буде у равнотежи, то ће изазвати #ПРОЛИВАЊЕ! Грешка.
Ова врста грешке при изливању се обично покреће када се користе променљиве функције као што су функције РАНД, РАНДАРРАИ, РАНДБЕТВЕЕН, ОФФСЕТ и ИНДИРЕЦТ.
На пример, када користимо формулу испод у ћелији Б3, добијамо грешку изливања:
=СЕКУЕНЦЕ(МЕЂУ (1, 500))У примеру, функција РАНДБЕТВЕЕН враћа насумични цео број између бројева 1 и 500, а њен излаз се непрекидно мења. А функција СЕКУЕНЦЕ не зна колико вредности да произведе у низу изливања. Отуда, грешка #СПИЛЛ.
Узрок грешке можете потврдити и кликом на знак упозорења – „Опсег изливања је непознат“.
Решење:
Да бисте исправили грешку за ову формулу, ваш једини избор је да користите другу формулу за свој прорачун.
Распон изливања је превелик
Понекад можете извршити формулу која даје просути опсег који је превелик да би радни лист могао да обради, и може се протезати преко ивица радног листа. Када се то догоди, можда ћете добити #СПИЛЛ! грешка. Да бисте решили овај проблем, можете покушати да референцирате одређени опсег или једну ћелију уместо читавих колона или користите знак „@“ да бисте омогућили имплицитни пресек
У примеру испод, покушавамо да израчунамо 20% бројева продаје у колони А и вратимо резултате у колону Б, али уместо тога добијамо грешку изливања.
Формула у Б3 израчунава 20% вредности у А3, затим 20% вредности у А4, и тако даље. Он производи преко милион резултата (1.048.576) и све их пребацује у колону Б почевши од ћелије Б3, али ће стићи до краја радног листа. Нема довољно простора за приказ свих излаза, као резултат, добијамо грешку #СПИЛЛ.
Као што видите, узрок ове грешке је тај што је – „Опсег изливања превелик“.
Решења:
Да бисте решили овај проблем, покушајте да промените целу колону са релевантним опсегом или референцом на једну ћелију или додајте оператор @ да бисте извршили имплицитни пресек.
Поправи 1: Можете покушати да упутите на опсеге уместо на читаве колоне. Овде мењамо цео опсег А:А са А3:А11 у формули, а формула ће аутоматски попунити опсег са резултатима.
Исправка 2: Замените целу колону само референцом ћелије у истом реду (А3), а затим копирајте формулу низ опсег користећи ручицу за попуњавање.
Исправка 3: Такође можете покушати да додате оператор @ пре референце да бисте извршили имплицитни пресек. Ово ће приказати излаз само у ћелији формуле.
Затим копирајте формулу из ћелије Б3 у остатак опсега.
Белешка: Када уређујете просуту формулу, можете уређивати само прву ћелију у области/опсегу изливања. Можете видети формулу у другим ћелијама опсега изливања, али оне ће бити засивљене и неће се моћи ажурирати.
Без меморије
Ако извршите просуту формулу низа која узрокује да Екцел остане без меморије, то може изазвати грешку #СПИЛЛ. У тим околностима, покушајте да референцирате мањи низ или опсег.
Непрепознато / резервни
Такође можете добити грешку изливања чак и када Екцел не препознаје или не може да помири узрок грешке. У таквим случајевима, још једном проверите формулу и уверите се да су сви параметри функција тачни.
Сада знате све узроке и решења за #СПИЛЛ! грешке у програму Екцел 365.