Формула Excel: Як виправити #SPILL! помилка -

Зміст

Резюме

Помилка #SPILL виникає, коли діапазон розливу заблокований чимось на аркуші. Рішенням зазвичай є очищення діапазону розливу будь-яких перешкоджаючих даних. Дивіться нижче для отримання додаткової інформації та кроків для вирішення.

Пояснення

Про розлив та # РОЗЛИВ! помилка

З введенням динамічних масивів в Excel формули, які повертають кілька значень, «розливають» ці значення безпосередньо на робочий аркуш. Прямокутник, що охоплює значення, називається "діапазоном розливу". Коли дані змінюються, діапазон розливу розширюватиметься або скорочуватиметься за необхідності. Можливо, ви побачите додані нові значення або існуючі значення зникнуть.

Відео: Розлив і діапазон розливу

Помилка #SPILL виникає, коли діапазон розливу заблокований чимось на аркуші. Іноді це очікується. Наприклад, ви ввели формулу, очікуючи, що вона виллється, але наявні дані на аркуші перешкоджають. Рішення полягає лише в тому, щоб очистити діапазон розливу будь-яких перешкоджаючих даних.

Однак іноді помилка може бути несподіваною і, отже, заплутаною. Про те, як може бути спричинена ця помилка, і що ви можете зробити, щоб усунути, читайте нижче.

Поведінка розливу є рідною

Важливо розуміти, що поведінка розливу є автоматичною і рідною. У Dynamic Excel (в даний час лише для Office 365 Excel) будь-яка формула, навіть проста формула без функцій, може розлити результати. Хоча існують способи зупинити формулу від повернення кількох результатів, само розливання неможливо вимкнути за допомогою глобальної настройки.

Так само в Excel немає можливості "вимкнути помилки #SPILL. Щоб виправити помилку #SPILL, вам доведеться дослідити та усунути першопричину проблеми.

Виправлення №1 - очистіть діапазон розливу

Це найпростіший випадок вирішення. Формула повинна розлити кілька значень, але натомість вона повертає #SPILL! бо щось заважає. Щоб усунути помилку, виберіть будь-яку клітинку в діапазоні розливу, щоб ви могли бачити її межі. Потім або перемістіть дані блокування в нове місце, або взагалі видаліть дані. Зверніть увагу, що комірки в діапазоні розливу повинні бути порожніми, тому зверніть увагу на комірки, які містять невидимі символи, наприклад пробіли.

На екрані нижче "x" блокує діапазон розливу:

Після того, як "x" видалено, функція UNIQUE розливається, як правило:

Виправлення # 2 - додавання символу @

До Dynamic Arrays Excel мовчки застосовував поведінку, яка називається "неявним перетином", щоб переконатись, що певні формули, які можуть повертати кілька результатів, повертають лише один результат. У нединамічному масиві Excel ці формули повертають звичайний результат без помилок. Однак у деяких випадках та сама формула, введена в Dynamic Excel, може спричинити помилку #SPILL. Наприклад, на екрані нижче клітинка D5 містить цю формулу, скопійовану вниз:

=$B$5:$B$10+3

Ця формула не призведе до помилки, скажімо, в Excel 2016, оскільки неявне перетин заважає формулі повертати кілька результатів. Однак у Dynamic Excel формула автоматично повертає розливання кількох результатів на робочий аркуш і які врізаються один в одного, оскільки формула копіюється з D5: D10.

Одним із рішень є використання символу @ для включення неявного перетину, як це:

= @$B$5:$B$10+3

З цією зміною кожна формула знову повертає один результат і помилка #SPILL зникає.

Примітка. Це частково пояснює, чому ви можете раптом побачити символ "@" у формулах, створених у старих версіях Excel. Це робиться для підтримки сумісності. Оскільки формули у старих версіях Excel не можуть розлитися на кілька комірок, символ @ додається, щоб забезпечити однакову поведінку при відкритті формули в Dynamic Excel.