#ПРОЛИВАТЬ! ошибка в Excel – что это значит и как исправить
Что означает #РАЗБИВКА в Excel? Это ошибка, возникающая, когда формула не может заполнить несколько ячеек результатами вычислений. Чтобы узнать, что может вызвать эту ошибку и как ее устранить, продолжайте читать.
Только представьте себе: вы перешли на Microsoft Office 365 с последними обновлениями Excel, и по какой-то причине испытанные формулы, которые вы использовали годами, внезапно перестали работать. Что бы вы ни делали, теперь вы получаете ошибку #SPILL. Кто-нибудь знает, что это значит? Конечно, мы делаем, и вы получите свое решение через мгновение 🙂
Что означает #РАЗБИВКА в Excel?
В общем, #РАЗЛИВ! ошибка возникает, когда формула выдает несколько результатов, но не может вывести их все на лист.
Прежде чем мы углубимся в конкретные варианты использования, давайте получим общее представление о разливе в Excel.
С запуском динамических массивов в Excel 365 любая формула, которая дает несколько результатов вычислений, автоматически «распространяет» эти результаты в соседние ячейки. Обратите внимание, любая формула, даже та, которая изначально не предназначена для работы с массивами. Диапазон ячеек, содержащих результаты, называется диапазоном заполнения. И если что-то на листе мешает заполнить этот диапазон, #ПРОЛИВ! возникает ошибка.
По большей части такое поведение понятно и предсказуемо. Например, если ожидается, что ваша формула вернет более одного значения, но соседние ячейки заполнены другими данными, просто удалите эти данные, и ошибка исчезнет.
Но иногда причины не столь очевидны и потому запутанны. Возможно, одна или несколько ячеек в области разлива содержат пробел или непечатаемый символ, невидимый человеческому глазу. Или одна и та же формула, скопированная по всему столбцу, блокирует диапазон разлива. Или вы могли столкнуться с одной из очень немногих функций, которые не поддерживают динамические массивы. Чтобы исправить ошибку, вам придется исследовать каждый случай отдельно и определить корень проблемы.
Как исправить #РАЗЛИВ! ошибка в экселе
Как упоминалось выше, #РАЗЛИВ! ошибка может быть вызвана различными причинами. Чтобы узнать точную причину проблемы, щелкните значок предупреждения (желтый ромб с восклицательным знаком) и прочитайте сообщение в первой строке, выделенное серым цветом:
После того, как вы определили причину, найдите соответствующий пример ниже с подробными инструкциями по устранению этого конкретного случая.
Диапазон разлива не пуст.
Причина: область, которая будет заполнена результатами формулы, содержит непустые ячейки.
Решение: очистить предполагаемый диапазон разлива.
В простейшем случае достаточно щелкнуть ячейку формулы, и вы увидите пунктирную границу, обозначающую границы диапазона разлива — любые данные внутри нее являются препятствием. Итак, либо удалите существующие данные из области разлива, либо переместите формулу в другое место, где нет блокировки.
Однако в некоторых ситуациях диапазон разлива может выглядеть пустым, но на самом деле это не так. Проблема может заключаться в невидимых символах, таких как пробелы, скрывающиеся в некоторых ячейках, или в формулах, возвращающих пустую строку.
Чтобы обнаружить такие клетки, нажмите на предупреждающий знак, и вы увидите это объяснение – Диапазон разлива не пуст.. Под ним есть несколько вариантов. Нажмите «Выбрать мешающие ячейки», и Excel покажет вам, какие ячейки предотвращают разбрызгивание формулы.
На приведенном ниже снимке экрана мешающей ячейкой является A6, которая содержит пустую строку (“”), возвращаемую формулой.
Чтобы очистить блокирующие ячейки, перейдите к Дом вкладка > Редактирование группу и нажмите чистый > Очистить все.
Диапазон разброса содержит объединенные ячейки
Причина: Разлив не работает с объединенными ячейками.
Решение: разъединить ячейки в области разлива или переместить формулу в другое место, где нет объединенных ячеек.
Если в спроецированном разлитом массиве есть одна или несколько объединенных ячеек, отображается следующее сообщение об ошибке: Диапазон разлива объединил ячейку.
Если у вас возникли трудности с визуальным обнаружением объединенных ячеек, используйте параметр «Выбрать мешающие ячейки», чтобы перейти к проблемным ячейкам:
Диапазон разлива в таблице
Причина: динамические массивы не поддерживаются в таблицах Excel.
Решение: преобразовать таблицу в обычный диапазон или поместить формулу за пределы таблицы, чтобы она могла растекаться. Не совсем понятно, почему формулы динамического массива не работают из таблиц Excel (может быть, из-за специфического синтаксиса структурированных ссылок), но все равно эти две очень полезные вещи не уживаются 🙁
Чтобы подтвердить основную причину проблемы, щелкните значок с восклицательным знаком, и вы увидите этот текст в первой строке – Диапазон разлива в таблице.
В этом случае лучшее, что вы можете сделать, это преобразовать таблицу в диапазон. Для этого щелкните правой кнопкой мыши в любом месте таблицы и выберите Стол > Преобразовать в диапазон. Кроме того, вы можете переместить формулу за пределы таблицы.
Диапазон разлива неизвестен
Причина: Excel не может установить размер разлитого массива.
Решение: Попробуйте разработать другую формулу для своей задачи.
При использовании изменчивых функций, таких как СЛУЧАЙ, СЛУЧАЙ или СЛУЧМЕЖДУ, в сочетании с функциями динамического массива может возникнуть ошибка #SPILL, поскольку массив, возвращаемый изменчивой функцией, изменяется между вычислениями электронной таблицы, а функция «обертки» не может определить его размер.
В такой ситуации сообщение об ошибке говорит – Диапазон разлива неизвестен.
Например, следующая формула выдает ошибку #ПРОЛИВ! ошибка, потому что вывод RANDBETWEEN постоянно изменяется, а SEQUENCE не знает, сколько значений генерировать:
=ПОСЛЕДОВАТЕЛЬНОСТЬ(СЛУЧМЕЖДУ(1100))
Диапазон разлива слишком большой
Причина: Excel не может вывести разбросанный массив, так как он выходит за границы электронной таблицы.
Решение: вместо вычисления целых столбцов используйте ссылку на используемый диапазон, отдельную ячейку или добавьте оператор @ для выполнения неявного пересечения.
Это самый сложный случай, и в зависимости от вашей цели и структуры рабочего листа вам может потребоваться изменить формулы по-разному. Здесь мы собираемся продемонстрировать общий подход на очень простом примере.
Предположим, у вас есть список чисел в столбце B, а в столбце C вы хотите вернуть 10% этих чисел.
В традиционном Excel 2019 и более ранних версиях следующая формула работала без сбоев (в левой части изображения ниже она находится в ячейках с C2 по C7):
=В:В*10%
В динамическом Excel 365 та же формула вызывает ошибку #SPILL.
Причина ошибки Диапазон разлива слишком большой.
Почему формула не работает в новом Excel? Потому что неявное пересечение больше не выполняется молча в фоновом режиме. Термин звучит загадочно, но логика, стоящая за ним, довольно проста — свести несколько значений к одному значению.
В версиях до 365 это было поведением Excel по умолчанию — поскольку ячейка могла содержать только одно значение, формула была вынуждена возвращать один результат. В нашем примере, несмотря на то, что мы указываем целый столбец (B:B), Excel обрабатывает только одно значение в той же строке, что и формула. Таким образом, формула в C2 вычисляет 10 % значения в B2, формула в C3 вычисляет 10 % значения в B3 и так далее.
В динамическом массиве Excel поведение по умолчанию другое: любая формула, которая потенциально может возвращать несколько результатов, автоматически переносит их на рабочий лист. В нашем случае Excel умножает каждую ячейку в столбце B на 10%, получает более миллиона результатов (точнее, 1 048 576), пытается вывести их все в столбце C, начиная с C2, но достигает конца сетки рабочего листа — отсюда #ОШИБКА РАЗЛИВ. Угадайте, что произойдет, если мы поместим формулу в ячейку C1? Да, это сработает, потому что на этот раз ячеек достаточно для заполнения – диапазон разлива займет ровно 1 048 576 ячеек!
Решение 1. Обращайтесь к диапазонам, а не к столбцам
Это очень разумный подход — вместо того, чтобы ссылаться на целые столбцы, ссылайтесь только на соответствующие данные. Помимо исправления ошибки #SPILL, это также помогает сэкономить ресурсы Excel и повысить производительность.
Для нашего примера набора данных формула выглядит так:
=В2:В10*10%
Формула вводится только в одну ячейку (C2). В результате получается динамический массив, который автоматически распределяется по нескольким ячейкам:
Примечание. Поскольку динамические массивы не поддерживаются в таблицах Excel, это решение работает только в пределах нормы.
Решение 2. Вычислите одну ячейку и скопируйте формулу вниз
Это традиционный подход — сослаться на одну ячейку в той же строке и скопировать формулу вниз.
В нашем случае приведенная ниже формула переходит к C2, а затем вы перетаскиваете ее вниз через C10:
=В2*10%
В отличие от формул динамического массива, этот традиционный стиль формул одинаково хорошо работает в диапазонах и таблицах:
Решение 3. Применить неявное пересечение
Этот подход немного сложнее — используйте неявный оператор пересечения (@символ), который был введен в Excel 365 как часть обновления динамических массивов.
Вставьте в формулу символ @ там, где вы хотите сократить массив или диапазон до одного значения, например, перед ссылкой на диапазон/столбец/строку, функцией или выражением. Введите формулу в самую верхнюю ячейку, а затем перетащите ее вниз на столько строк, сколько необходимо.
Для нашего примера набора данных формула принимает следующий вид:
=@В:В*10%
Поскольку результатом является одно значение, а не динамический массив, эту формулу можно использовать как в диапазонах, так и в таблицах. Однако в последнем случае более элегантным и эффективным решением будет структурированная ссылка:
знак равно[@Sales]*10%
В какой-то степени это объясняет, почему символ “@” иногда появляется в формулах, созданных в старых версиях, для сохранения совместимости. Поскольку в преддинамическом Excel формулы не могут распространяться на несколько ячеек, оператор неявного пересечения обеспечивает такое же поведение при импорте формулы в динамический массив Excel.
Мой личный совет — используйте этот подход с осторожностью, потому что он отключает замечательную новую функцию Excel, которая очень эффективна и полезна во многих сценариях.
Вот как можно устранить и исправить ошибку #SPILL в Excel. В следующей статье мы собираемся исследовать и устранить эту ошибку в нескольких популярных формулах, таких как ВПР, СОВПАДЕНИЕ С ИНДЕКСОМ, СУММЕСЛИ и т. д. Спасибо за чтение и, пожалуйста, следите за обновлениями!
