Що таке помилка #SPILL в Excel і як її виправити?

Ця стаття допоможе вам зрозуміти всі причини помилок #SPILL, а також рішення для їх виправлення в Excel 365.

#ПРОЛИТИ! – це новий тип помилки Excel, який в основному виникає, коли формула, яка дає кілька результатів обчислень, намагається відобразити свої результати в діапазоні розливу, але цей діапазон вже містить деякі інші дані.

Блокуючими даними можуть бути будь-які дані, включаючи текстове значення, об’єднані клітинки, звичайний пробіл або навіть якщо немає достатньо місця для повернення результатів. Рішення просте: очистіть діапазон будь-яких блокуючих даних або виберіть порожній масив комірок, які не містять жодного типу даних.

Помилка розливу зазвичай виникає під час обчислення формул динамічного масиву, оскільки формула динамічного масиву – це та, яка виводить результати в декілька комірок або масив. Давайте розглянемо докладніше і зрозуміємо, що викликає цю помилку в Excel і як її усунути.

Що викликає помилку розливу?

З моменту запуску динамічних масивів у 2018 році формули Excel можуть обробляти кілька значень одночасно і повертати результати в більш ніж одній клітинці. Динамічні масиви — це масиви з можливістю зміни розміру, які дозволяють формулам повертати кілька результатів у діапазон комірок на аркуші на основі формули, введеної в одну клітинку.

Коли формула динамічного масиву повертає кілька результатів, ці результати автоматично розливаються в сусідні клітинки. Така поведінка в Excel називається «розлив». А діапазон комірок, у який розливаються результати, називається «діапазоном розливу». Діапазон розливу буде розширюватися або скорочуватися автоматично на основі вихідних значень.

Якщо формула намагається заповнити діапазон розливу кількома результатами, але блокується чимось у цьому діапазоні, виникає помилка #SPILL.

Excel тепер має 9 функцій, які використовують функціональні можливості динамічного масиву для вирішення проблем, зокрема:

  • Послідовність
  • ФІЛЬТР
  • ТРАНСПОЗИРОВАНИЕ
  • СОРТ
  • СОРТУВАТИ ЗА
  • RANDARRAY
  • УНІКАЛЬНИЙ
  • XLOOKUP
  • XMATCH

Формули динамічного масиву доступні лише в «Excel 365», і наразі вони не підтримуються жодним програмним забезпеченням Excel в автономному режимі (наприклад, Microsoft Excel 2016, 2019).

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

Діапазон розливу не порожній

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

Приклад 1:

У наведеному нижче прикладі ми ввели функцію TRANSPOSE в клітинку C2, щоб перетворити вертикальний діапазон комірок (B2:B5) у горизонтальний діапазон (C2:F2). Замість того, щоб перемикати стовпець на рядок, Excel показує нам #SPILL! помилка.

А коли ви клацнете на клітинці формули, ви побачите пунктирну синю рамку, що вказує область/діапазон розливу (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.