Тази статия ще ви помогне да разберете всички причини за #SPILL грешки, както и решенията за тяхното коригиране в Excel 365.
#ПРАЙТЕ! е нов вид грешка в Excel, която възниква главно, когато формула, която произвежда множество резултати от изчисленията, опитвайки се да покаже своите резултати в диапазон на разлив, но този диапазон вече съдържа някои други данни.
Блокиращите данни могат да бъдат всичко, включително текстова стойност, обединени клетки, обикновен символ за интервал или дори когато няма достатъчно място за връщане на резултатите. Решението е просто, или изчистете диапазона от всички блокиращи данни, или изберете празен масив от клетки, които не съдържат никакъв тип данни в него.
Грешка при разливане обикновено се случва при изчисляване на формули за динамичен масив, тъй като формулата на динамичния масив е тази, която извежда резултатите в множество клетки или масив. Нека разгледаме по-подробно и да разберем какво задейства тази грешка в Excel и как да я разрешим.
Какво причинява грешка при разливане?
Тъй като стартирането на динамични масиви през 2018 г., формулите на Excel могат да обработват множество стойности наведнъж и да връщат резултати в повече от една клетка. Динамичните масиви са масиви с възможност за промяна на размера, които позволяват на формулите да връщат множество резултати към диапазон от клетки в работния лист въз основа на формула, въведена в една клетка.
Когато формула за динамичен масив върне множество резултати, тези резултати автоматично се разливат в съседните клетки. Това поведение се нарича „Разливане“ в Excel. И диапазонът от клетки, в който резултатите се разливат, се нарича „диапазон на разливане“. Диапазонът на разлива ще се разширява или свива автоматично въз основа на стойностите на източника.
Ако формула се опитва да запълни диапазон на разливане с множество резултати, но е блокирана от нещо в този диапазон, тогава възниква грешка #SPILL.
Excel вече има 9 функции, които използват функционалност на динамичен масив за решаване на проблеми, те включват:
- ПОСЛЕДОВАТЕЛНОСТ
- ФИЛТРЪР
- ТРАНСПОЗИРАНЕ
- ВИД
- СОРТИРАНЕ
- RANDARRAY
- УНИКАЛЕН
- XLOOKUP
- XMATCH
Формулите за динамичен масив са налични само в „Excel 365“ и понастоящем не се поддържат от никой от офлайн софтуера на Excel (т.е. Microsoft Excel 2016, 2019).
Грешките при разливане не са причинени само от възпрепятстване на данни, има няколко причини, поради които може да получите грешка #Spill. Позволете ни да проучим различните ситуации, в които може да се сблъскате с #ПРИЯТИЯ! грешки и как да ги поправите.
Диапазонът на разлива не е празен
Една от основните причини за грешка при разливане е, че диапазонът на разлива не е празен. Например, ако се опитвате да покажете 10 резултата, но ако има някакви данни в която и да е от клетките в областта на разливането, формулата връща #SPILL! грешка.
Пример 1:
В примера по-долу сме въвели функцията TRANSPOSE в клетка C2, за да преобразуваме вертикалния диапазон от клетки (B2:B5) в хоризонтален диапазон (C2:F2). Вместо да превключва колоната към ред, Excel ни показва #ПРЕКРИВАНЕ! грешка.
И когато щракнете върху клетката с формула, ще видите прекъсната синя рамка, показваща зоната/обхвата на разлива (C2:F2), която е необходима за показване на резултатите, както е показано по-долу. Също така ще забележите жълт предупредителен знак с удивителен знак върху него.
За да разберете причината за грешката, щракнете върху иконата за предупреждение до грешката и вижте съобщението в първия ред, маркирано в сиво. Както можете да видите, тук пише „Обхватът на разливане не е празен“.
Проблемът тук е, че клетките в обхвата на разливане D2 и E2 имат текстови знаци (не празни), оттук и грешката.
Решение:
Решението е просто, или изчистете данните (или преместете, или изтрийте), разположени в обхвата на разлива, или преместете формулата на друго място, където няма препятствия.
Веднага след като изтриете или преместите блокирането, Excel автоматично ще попълни клетките с резултатите от формулата. Тук, когато изчистим текста в D2 и E2, формулата транспонира колоната в ред, както е предвидено.
Пример 2:
В примера по-долу, въпреки че диапазонът на разлива изглежда празен, формулата все още показва Разлива! грешка. Това е така, защото разливът всъщност не е празен, той има невидимо пространство в една от клетките.
Трудно е да се намерят символи за интервал или друг невидим знак, който се крие в това, което изглежда като празни клетки. За да намерите такива клетки с нежелани данни, щракнете върху полето за грешка (предупредителен знак) и изберете „Избор на пречещи клетки“ от менюто и това ще ви отведе до клетката, която съдържа възпрепятстващите данни.
Както можете да видите, на екранната снимка по-долу клетка E2 има два знака за интервал. Когато изчистите тези данни, ще получите правилния изход.
Понякога невидимият знак може да бъде текст, форматиран със същия цвят на шрифта като цвета на запълването на клетката или стойност на клетка, форматирана по избор с цифровия код ;;;. Когато персонализирате стойност на клетка с ;;;, тя ще скрие всичко в тази клетка, независимо от цвета на шрифта или цвета на клетката.
Диапазонът на разлива съдържа обединени клетки
Понякога #ПРИЯТ! възниква грешка, когато диапазонът на разливане съдържа обединените клетки. Формулата за динамичен масив не работи със слети клетки. За да коригирате това, всичко, което трябва да направите, е да прекратите обединяването на клетки в диапазона на разливане или да преместите формулата в друг диапазон, който няма обединени клетки.
В примера по-долу, въпреки че диапазонът на разливане е празен (C2:CC8), формулата връща грешката при разливане. Това е така, защото клетките C4 и C5 са обединени.
За да сте сигурни, че обединените клетки са причината да получавате грешката, щракнете върхупредупредителен знак и проверете причината – „Обхватът на разлива е обединена клетка“.
Решение:
За да отмените обединяването на клетките, изберете обединените клетки, след това в раздела „Начало“ щракнете върху бутона „Обединяване и център“ и изберете „Отмяна на обединяването на клетки“.
Ако ви е трудно да намерите обединените клетки във вашата голяма електронна таблица, щракнете върху опцията „Избор на пречещи клетки“ от менюто с предупредителен знак, за да преминете към обединените клетки.
Диапазон на разлива в табл
Формулите за разляти масиви не се поддържат в таблиците на Excel. Формулата за динамичен масив трябва да се въвежда само в една отделна клетка. Ако въведете формула за разлят масив в таблица или когато областта на разливане попадне в таблица, ще получите грешка при разливане. Когато това се случи, опитайте да преобразувате таблицата в нормален диапазон или преместете формулата извън таблицата.
Например, когато въведем следната формула за разлят диапазон в таблица на Excel, ще получим грешка при разливане във всяка клетка на таблицата, а не само в клетката с формула. Това е така, защото Excel автоматично копира всяка формула, въведена в таблица, във всяка клетка в колоната на таблицата.
Освен това ще получите грешка при разливане, когато формула се опита да разлее резултати в таблица. На екранната снимка по-долу зоната на разливане попада в съществуващата таблица, така че получаваме грешка при разливане.
За да потвърдите причината за тази грешка, щракнете върху предупредителния знак и вижте причината за грешката – „Обхват на разливане в таблицата“
Решение:
За да коригирате грешката, ще трябва да върнете таблицата на Excel обратно към диапазона. За да направите това, щракнете с десния бутон навсякъде в таблицата, щракнете върху „Таблица“ и след това изберете опцията „Преобразуване в диапазон“. Като алтернатива можете да щракнете с левия бутон на мишката навсякъде в таблицата, след което да отидете в раздела „Дизайн на таблица“ и да изберете опцията „Преобразуване в диапазон“.
Обхватът на разлива е неизвестен
Ако Excel не е успял да установи размера на разлятия масив, той ще задейства грешката при разливане. Понякога формулата позволява на динамичен масив да преоразмерява между всяко преминаване на изчисление. Ако размерът на динамичния масив продължава да се променя по време на преминаване на изчисленията и не се балансира, това ще доведе до #SPILL! Грешка.
Този тип грешка при разливане обикновено се задейства при използване на променливи функции като функции RAND, RANDARRAY, RANDBETWEEN, OFFSET и INDIRECT.
Например, когато използваме формулата по-долу в клетка B3, получаваме грешка при разливане:
=ПОСЛЕДОВАТЕЛНОСТ(ПОРЕДНА МЕЖДУ(1, 500))В примера функцията RANDBETWEEN връща произволно цяло число между числата 1 и 500 и нейният изход непрекъснато се променя. И функцията SEQUENCE не знае колко стойности да произведе в масив за разлив. Следователно грешката #SPILL.
Можете също да потвърдите причината за грешката, като щракнете върху Предупредителен знак – „Обхватът на разлива е неизвестен“.
Решение:
За да коригирате грешката за тази формула, единственият ви избор е да използвате различна формула за вашето изчисление.
Обхватът на разлива е твърде голям
Понякога може да изпълните формула, която извежда разлят диапазон, който е твърде голям, за да може работният лист да се справи, и може да се простира извън ръбовете на работния лист. Когато това се случи, вие може да получите #SPILL! грешка. За да отстраните този проблем, можете да опитате да посочите конкретен диапазон или една клетка вместо цели колони или да използвате знака „@“, за да активирате имплицитно пресичане
В примера по-долу се опитваме да изчислим 20% от числата за продажби в колона A и да върнем резултатите в колона B, но вместо това получаваме грешка при разливане.
Формулата в B3 изчислява 20% от стойността в A3, след това 20% от стойността в A4 и т.н. Той произвежда над милион резултата (1 048 576) и разлива всички в колона B, започвайки от клетка B3, но ще стигне до края на работния лист. Няма достатъчно място за показване на всички изходи, в резултат на което получаваме грешка #SPILL.
Както можете да видите, причината за тази грешка е, че – „Обхватът на разливане е твърде голям“.
Решения:
За да разрешите този проблем, опитайте да промените цялата колона със съответен диапазон или препратка към една клетка или добавете оператора @, за да извършите имплицитно пресичане.
Поправете 1: Можете да опитате да препратите диапазони, а не цели колони. Тук променяме целия диапазон A:A с A3:A11 във формулата и формулата автоматично ще попълни диапазона с резултати.
Поправка 2: Заменете цялата колона само с препратка към клетка на същия ред (A3) и след това копирайте формулата надолу по диапазона, като използвате манипулатора за запълване.
Поправка 3: Можете също да опитате да добавите оператор @ преди препратката, за да извършите неявно пресичане. Това ще покаже изхода само в клетката с формула.
След това копирайте формулата от клетка B3 в останалата част от диапазона.
Забележка: Когато редактирате разлята формула, можете да редактирате само първата клетка в областта/обхвата на разливане. Можете да видите формулата в други клетки от обхвата на разлива, но те ще бъдат оцветени в сиво и не могат да бъдат актуализирани.
Недостиг на памет
Ако изпълните формула за разлят масив, която води до изчерпване на паметта на Excel, това може да предизвика грешка #SPILL. При тези обстоятелства опитайте да посочите по-малък масив или диапазон.
Неразпознат / Резервен
Можете също да получите грешка при разливане, дори когато Excel не разпознава или не може да съгласува причината за грешката. В такива случаи проверете отново формулата си и се уверете, че всички параметри на функциите са правилни.
Сега знаете всички причини и решения за #SPILL! грешки в Excel 365.