Обработка ошибки SPILL

Если формула динамического массива не может разместить весь результат, Excel показывает в исходной ячейке #SPILL!. Обычно формула составлена правильно; проблема в том, что на пути размещения результата что-то находится.

Представьте, что Вы пытаетесь припарковать автобус на месте, где уже стоит автомобиль. С автобусом всё в порядке, но место занято.

Самая распространённая причина — одна или несколько ячеек в диапазоне разлива уже содержат данные. Если формуле =UNIQUE(A2:A12) нужно заполнить C1:C3, но в C2 находится случайное значение, разлив блокируется.

Исправление простое: выберите исходную ячейку, найдите пунктирную границу диапазона разлива и очистите все непустые ячейки внутри неё.

Щёлкните ячейку с ошибкой #SPILL!. Excel обведёт пунктирной границей область, которую должен занять результат. Причиной является любая непустая ячейка внутри этой границы.

Также можно щёлкнуть предупреждающий треугольник рядом с ячейкой и выбрать пункт Выбрать мешающие ячейки. Откроются ячейки, вызывающие проблему, и Вы сможете удалить их содержимое.

Диапазон разлива не может пересекаться с объединёнными ячейками. Даже пустая объединённая ячейка считается препятствием, поскольку для разлива нужны отдельные ячейки.

Если результат попадает на объединённую строку заголовка или подпись, отмените объединение ячеек (вкладка «Главная», переключатель «Объединить и поместить в центре»), и формула будет разливаться правильно.

Разлив также завершается ошибкой, если выходит за край листа или попадает в таблицу. Например, формуле возле последней строки, которой нужно разлиться вниз, некуда размещать результат.

Переместите формулу выше или в столбец, под которым есть свободное место. Убедитесь, что в направлении разлива достаточно пустых ячеек для всего результата.

Таблицы Excel, созданные с помощью Ctrl+T, не позволяют динамическим массивам разливаться внутри них, поскольку таблица предполагает возможность редактирования каждой ячейки.

Если формула с разливом находится в столбце таблицы, появится ошибка #SPILL!. Преобразуйте таблицу обратно в обычный диапазон (конструктор таблиц, «Преобразовать в диапазон») или переместите формулу за пределы таблицы.

Ссылка в формуле с разливом на целый столбец также может вызвать ошибку #SPILL!, поскольку результат становится огромным, может столкнуться с другими данными или превысить ограничения.

Например, формула =A:A*2 пытается разлить результат на миллион строк. Ограничьте ссылку фактическим диапазоном данных, например =A2:A100*2, чтобы размер разлива был умеренным и чётко определённым.

Наведите указатель на предупреждающий треугольник в ячейке с ошибкой #SPILL!, и Excel точно объяснит причину. Среди распространённых сообщений: Диапазон разлива не пуст, В диапазоне разлива есть объединённые ячейки и Диапазон разлива слишком велик.

Всегда сначала читайте эту подсказку. Она сразу указывает причину и избавляет от необходимости гадать, а каждое сообщение соответствует одному из изученных способов исправления.

Важно помнить, что ошибка #SPILL! редко означает ошибку в логике формулы. Вычисление выполнено успешно — не удалось только разместить результат.

После устранения препятствия та же, не изменённая формула разливается без проблем. Сравните это с ошибками #VALUE! или #REF!: обычно они действительно указывают на проблему внутри самой формулы.

Вы вводите =SORT(UNIQUE(A2:A20)) в D1 и получаете #SPILL!. Выбираете D1, видите пунктирный контур диапазона D1:D5 и замечаете старую заметку в D3.

Вы удаляете содержимое D3. Формула мгновенно разливает отсортированный список уникальных значений вниз по диапазону D1:D5. Изменять формулу вообще не потребовалось.

Небольшое планирование позволяет избежать большинства ошибок разлива. Оставляйте для формул динамических массивов пустой столбец или область, куда они смогут расширяться, а подписи и заметки размещайте в стороне от направления разлива.

При ссылке на другой разлив используйте оператор #, например =SUM(D1#), чтобы формулы адаптировались при изменении размеров диапазонов. Размещайте результаты в свободном пространстве — тогда ошибка #SPILL! будет появляться редко.

Проверьте, насколько хорошо Вы поняли ошибку SPILL.

Вы узнали, что #SPILL! — это проблема размещения результата, а не ошибка логики:

• Чаще всего ячейка в диапазоне разлива не пуста.
• Объединённые ячейки и таблицы блокируют разлив.
• Избегайте ссылок на целые столбцы, из-за которых результат становится слишком большим.
• Используйте команды Выбрать блокирующие ячейки и подсказку, чтобы найти причину.

На этом курс «Динамические массивы и разлив» завершён. Теперь Вы умеете возвращать множество результатов одной формулой и уверенно исправлять ошибки разлива.