Як ми всі знаємо, Office 365 поставляється в комплекті з Excel 365. Microsoft додала до Excel 365 різні нові функції. Однією з таких особливостей є Формули динамічного масиву. Зазвичай формула повертає в осередку лише одне значення. Але тепер, коли є ця нова функція, можна повернути кілька значень.
Наприклад, у Excel 2019 та попередніх версіях, скажімо, ви застосуєте формулу = D2: D5 до комірки, результат буде обмежений першою клітиною.
Коли нам потрібно було застосувати формулу до всіх відповідних комірок, ми скористалися позначенням масиву (Ctrl+Shift+Enter). Однак у Excel 365 це не так. Коли ви застосовуєте ту саму формулу, значення автоматично розливаються по всіх відповідних осередках. Для більш детальної інформації зверніться до зображення нижче.
Ділянка клітин, в яку виливається результат, називається Діапазон розливів. Зверніться до зображення нижче
ПРИМІТКА:
- Файл Розлив автоматично вмикається за допомогою динамічних масивів (наразі ця функція підтримується лише в Excel 365), і її неможливо вимкнути.
- Функція розливу ввімкнена у всіх формулах з функціями чи без них.
Помилки розливу відображаються, коли формула призначена для повернення кількох значень, однак результати не можна розмістити на клітинках. Помилка виглядає так:
Можливі причини виникнення помилки #SPILL:
- Діапазон витоку містить деяке значення, через що результати не можуть бути заповнені в клітинках
- Діапазон розливу містить об’єднані комірки.
- Коли в Excel365 відкриваються старі аркуші (створені за допомогою Excel 2016 або ранішої версії) з формулами, що підтримують неявний перетин.
- Коли ви застосовуєте формулу динамічного масиву до таблиці Excel.
Якщо ви бачите помилку #SPILL у програмі Excel, не хвилюйтесь. У цій статті ми продемонструємо різні способи визначення першопричини цієї проблеми, а також розглянемо способи виправлення помилки #SPILL
Зміст
Визначте, що викликає помилку #SPILL
Коли ви бачите помилку розливу, спочатку перевірте, чому ви бачите помилку, щоб це зробити,
Крок 1: Натисніть на клітинку, яка відображається #ПОРОШКА! помилка
Крок 2: Натисніть на Знак оклику як показано нижче
Крок 3: Перший рядок повідомляє нам, що викликає помилку. Наприклад, у цьому випадку помилка відображається, оскільки діапазон витоку не є порожнім
Виправлення, які слід виконувати, коли діапазон витоку не є порожнім
Виконайте наведені нижче виправлення, коли побачите, що Діапазон витоку не є порожнім
Виправлення 1: Видаліть дані, які блокують діапазон витоку
Якщо деякі дані вже є у клітинках у діапазоні витоку, ви побачите помилку #SPILL при застосуванні формули.
Коли ви чітко бачите дані, які блокують діапазон витоку
Розглянемо наведений нижче приклад. Коли ви застосовуєте формулу = D2: D5 до даних, виникає помилка SPILL, оскільки Я тут в межах діапазону розливу.
Щоб позбутися помилки #SPILL, просто перемістіть дані або видаліть дані з діапазону витоку.
Коли дані, що блокують діапазон витоку, приховані
У деяких випадках дані, що блокують діапазон витоку, є прихованими і не дуже очевидними, як це видно у випадку 1. Розглянемо наступний приклад,
У таких випадках, щоб знайти клітинку, яка блокує діапазон розливу, виконайте наступні кроки:
Крок 1: Натисніть на клітинку, яка відображається #ПОРОШКА! помилка
Крок 2: Натисніть на Знак оклику як показано нижче, Ви можете побачити, що помилка пов'язана з тим, що Діапазон витоку не є порожнім.
Крок 3: У спадному меню натисніть Виберіть Обструктивні клітини
Крок 4: Клітинка, яка блокує діапазон розливу, виділяється, як показано нижче
Тепер, коли ви знаєте, яка клітинка блокує, перевірте, що саме викликає проблему.
Крок 5: При ретельному огляді клітини ви можете побачити деякі дані, заховані всередині клітин.
Як видно на зображенні вище, є деякі дані. Оскільки шрифт має білий колір, розпізнати завал нелегко. Щоб позбутися від помилки, видаліть дані з комірки в межах діапазону витоку.
Виправити 2: Видалити форматування користувацького номера;;; наноситься на клітинку
Іноді під час форматування користувацького номера ;; ; застосовується до клітинки, є ймовірність побачити помилку РОЗЛИВ. У таких випадках
Крок 1: Натисніть на клітинку, яка відображається #ПОРОШКА! помилка
Крок 2: Натисніть на Знак оклику як показано нижче.
Крок 3: У спадному меню натисніть Виберіть Обструктивні клітини
Крок 4: Клітинка, яка блокує діапазон розливу, виділяється, як показано нижче
Крок 5: Клацніть правою кнопкою миші клітинку, що перешкоджає.
Крок 6: Виберіть Формат комірок
Крок 7: Відкриється вікно Форматування клітинок. Перейдіть до Номер вкладка
Крок 8: На лівій панелі виберіть На замовлення
Крок 9: На правій бічній панелі змініть Тип з;;; до Загальні
Крок 10: Натисніть на В порядку кнопку
Виправлення, якого слід дотримуватись, коли діапазон витоку об’єднав комірки
Якщо ви бачите, що помилка через те, що Діапазон витоку об’єднав комірки як показано нижче,
Крок 1: Натисніть на Виберіть Обструктивні клітини зі спадного меню
Крок 2: Перешкоджає осередку виділено
Крок 3: Під Додому вкладку, натисніть Злиття та центр
Крок 4: У спадному меню виберіть Роз'єднати клітини
Виправлення, якого слід дотримуватися, коли Діапазон розливу в таблиці
Формули динамічного масиву не підтримуються в таблицях Excel. Якщо ви бачите помилку #SPILL у таблиці Excel, як показано нижче з повідомленням Діапазон розливу в табл,
Крок 1: Виберіть таблицю повністю
Крок 2: Натисніть на Дизайн столу на верхній панелі меню
Крок 3: Виберіть Перетворити на діапазон
Крок 4: Ви побачите діалогове вікно підтвердження, натисніть Так
Виправлення, якого слід дотримуватись, коли діапазон витоку втрачає пам'ять
Якщо ви намагаєтесь визначити причину помилки #SPILL, якщо бачите, що це повідомлення про помилку Недостатньо помяті, це тому, що формула динамічного масиву, яку ви використовуєте, посилається на великий діапазон, і в таких випадках працює Excel недостатньо помяті викликаючи помилку розливу. Щоб подолати помилку, можна спробувати посилатися на менший діапазон.
Виправлення, якого слід дотримуватися, коли діапазон витоку невідомий
Ця помилка спостерігається, коли розмір розлитого масиву змінюється, і Excel не може встановити розмір діапазону розлитого матеріалу. Як правило, при використанні випадкових функцій, таких як RANDARRAY, RAND або RANDBETWEEN разом із функціями Dynamic Array, такими як SEQUENCE, з'являється ця помилка.
Щоб краще зрозуміти це, розглянемо наведений нижче приклад, скажімо, використовується функція SEQUENCE (RANDBETWEEN (1100)). Тут RANDBETWEEN генерує випадкове ціле число, яке більше або дорівнює 1 і менше або дорівнює 100. А SEQUENCE породжує послідовні числа (наприклад, SEQUENCE (5) породжує 1,2,3,4,5). Однак RANDBETWEEN - це мінлива функція, яка постійно змінює своє значення щоразу, коли відкривається або змінюється лист Excel. Тому що внаслідок цього функція SEQUENCE не зможе визначити розмір масиву, який він повинен генерувати. Він не знав би, скільки значень генерувати, і таким чином видається помилка SPILL.
Коли ви визначаєте причину помилки, ви бачите Діапазон витоку невідомий
Щоб виправити таку помилку, спробуйте мати іншу формулу, яка відповідає вашим потребам.
Виправлення, яких слід дотримуватися, коли діапазон витоку занадто великий
Скажімо, ви визначаєте причину і помічаєте, що помилка бачиться через те, що Діапазон витоку занадто великий як показано нижче.
Коли Dynamic Array не було на місці, у Excel було щось таке, що називається неявним перетином змушений повернути єдиний результат, навіть якщо формула мала потенціал повернення кратного результати. Розглянемо приклад, якщо формула = B: B*5% застосовується до Excel 2019 або більш ранніх версій із неявним перетином, результат буде таким:
Однак, коли таку саму формулу використовується в Excel 365, ви бачите таку помилку
Щоб вирішити цю проблему, спробуйте наведені нижче виправлення
Виправлення 1: Застосовуйте неявне перетин за допомогою оператора @
Коли ми кажемо = В: В, динамічний масив посилатиметься на весь стовпець B. Замість цього ми можемо змусити Excel накласти неявне перетин за допомогою оператора @
Змініть формулу на[захищена електронною поштою]: B*5%
Оскільки неявне перетин додається, формула буде застосована до однієї комірки. Щоб розширити формулу,
1. Просто натисніть на крапку, як показано нижче
2. Перетягніть його вниз по клітинках, якщо потрібно. Це буде застосовувати ту саму формулу до цих клітин.
Виправлення 2: Замість посилання на стовпець зверніться до діапазону
У формулі, = B: B*5%, ми маємо на увазі колонку В. Натомість зверніться до певного діапазону, = B2: B4*5%
Це все
Сподіваємось, ця стаття була інформативною.
Будь ласка, ставте коментар, якщо вам вдалося вирішити проблему за допомогою вищевказаних методів.
Дякую за читання.
