Содержание
Excel – это мощное приложение для работы с электронными таблицами. Одной из особенностей является формула, которая позволяет быстро и легко выполнять простые и сложные вычисления. Вы получите #разлив! ошибка в Microsoft Excel, когда формула возвращает несколько результатов. Это математически неверно, поэтому Excel вместо отображения результата отображает ошибку SPILL. Давайте разберемся подробнее, что такое разлив и массив в Excel, как выявить такие ошибки и как их исправить.
Что такое ошибка разлива и как ее обнаружить
Ошибка сброса — это когда вы вводите формулу в Excel, а она возвращается с ошибкой, например, из-за того, что диапазон слишком велик или недостаточно памяти. Таким образом, Excel либо отобразит ошибку, либо отобразит эти результаты в разных ячейках. Таким образом, эти несколько значений (ответ на запрос) вводятся в соседние ячейки. Некоторые другие ошибки разлива, с которыми вы можете столкнуться:
- диапазон сброса пуст
- диапазон разлива неизвестен
- Диапазону сброса не хватает памяти.
- произошла утечка из-за резервного варианта
- Диапазон разлива слишком велик.
- диапазон заполнения не пуст.
- Диапазон разгрузки объединен с ячейкой
Простой способ устранить ошибку диапазона разлива — вставить формулу в пустой лист Excel и воссоздать ошибку. Таким образом, вы не потеряете ценные данные и часы напряженной работы.
Позвольте мне привести пример.
У вас есть таблица с именами, идентификаторами клиентов и адресами сотрудников. Вы вводите формулу для получения номеров мобильных телефонов сотрудников из другого листа, когда идентификатор клиента совпадает. Excel выдаст ошибку разгрузки, если с одним и тем же идентификатором клиента связано более двух телефонных номеров. Теперь диапазон стал больше и не может отображаться в одной ячейке, что приводит к перераспределению данных на соседние ячейки.
Поможет ли этот пример исправить ошибку разлива на листе Excel? Если нет, вот несколько решений.
Исправить ошибку диапазона разлива в Microsoft Excel
Я создал фиктивный лист с последовательностью, возвращающей #SPILL! ошибка в Excel. Давайте посмотрим, что мы можем сделать, чтобы решить эту проблему. Вот как это выглядит.
1. Когда диапазон разлива слишком велик
Давайте сначала посмотрим, что я ввел в ячейку.
Диапазон слишком широк для расчета размера массива, что приводит к ошибке сброса. Если массив является динамическим по своей природе, Excel ответит сообщением об ошибке, чтобы убедиться, что другие вычисления в электронной таблице завершены. Попробуйте уменьшить ширину диапазона, если сможете, и посмотрите, поможет ли это.
2. Когда диапазон раскрытия не пустой
При вводе формулы в лист Excel, но диапазон сброса для массива не является пустым. Вот что вы получаете — ошибка сброса с пунктирной линией вокруг. Граница обозначает территорию ареала разлива.
Эту ошибку разлива можно быстро исправить. Щелкните правой кнопкой мыши, чтобы выбрать плавающее сообщение «Ошибка», и выберите параметр «Выбрать мешающие ячейки», чтобы перейти к мешающим ячейкам. Затем вы можете устранить ошибку, удалив или переместив запись мешающей ячейки. Как только препятствие будет устранено, формула массива будет перераспределена, как и предполагалось.
В этом случае число 4 необходимо удалить или переместить, чтобы устранить блокировку и предотвратить утечку.
3. Когда Excel не хватает памяти
Это проще исправить. Все, что вам нужно сделать, это сослаться на меньший диапазон или массив, поскольку текущий диапазон слишком велик и приводит к «нехватке памяти» Excel.
4. Когда ошибка разлива выходит за пределы края листа
Знаете ли вы, сколько ячеек в столбце Excel? Ответ: 1 048 576. Да, существует ограничение, и формула, которую вы только что ввели (была ли это ВПР?), заставляет Excel искать или искать весь столбец и возвращать 1 048 576 результатов. Но затем он пытается выйти за пределы диапазона листа, что приводит к ошибке сброса.
Решением будет попробовать использовать меньший диапазон. В этом случае перемещение формулы в ячейку F2 должно устранить ошибку разлива. Динамические массивы не всегда были частью листов Excel. С его добавлением Excel будет использовать все значения в искомом_значении, что означает, что если в качестве аргумента предлагается весь столбец (в нашем случае это D:D), Excel будет искать 1 048 576 значений в этом столбце. После этого он попытается перенести их в сетку, но попадет в конец или край листа Excel, что приведет к ошибке пропуска.
Их три способа исправить эту ошибку разлива , и выбор того, который вы выберете, будет зависеть от желаемого конечного результата.
- Укажите нужные значения поиска. Эта формула вернет динамический массив, но не будет работать с таблицами Excel, имейте это в виду.
- Просто укажите значение, которое находится в той же строке, а затем скопируйте приведенную ниже формулу. Этот традиционный подход будет работать с таблицами, но не вернет динамический массив.
- Используйте оператор @, чтобы выполнить пересечение (неявно), а затем скопируйте приведенную ниже формулу. Это снова будет работать с таблицами, но не вернет динамический массив.
5. Формула таблицы
Таблицы в Excel просто не поддерживают формулы разгрузки, поэтому использование одной из них внутри таблицы приведет к ошибке диапазона разгрузки. Либо удалите таблицу, что легче сказать, чем сделать, либо уберите из нее формулу разлива. Другим решением, средним вариантом, может быть преобразование таблицы в диапазон.
Для этого нажмите Конструктор таблиц >Инструменты >Преобразовать в диапазон.
6. Распределение по объединенным ячейкам
Есть ли у вас объединенные ячейки в таблице Excel? Если да, формула развернутого массива не может быть успешно перенесена в объединенные ячейки. Чтобы устранить эту ошибку разлива в Excel, вам необходимо разделить ячейки. Если это невозможно, переместите формулу в другую ячейку, чтобы объединенные ячейки больше не мешали и не пересекались с диапазоном.
Как вы можете заметить на скриншоте выше, при выборе формулы создается пунктирная линия границы, подчеркивающая диапазон разлива. Снова щелкните правой кнопкой мыши и выберите параметр Выбрать мешающие ячейки, чтобы найти объединенные ячейки, если вы не уверены, где они находятся.
Выплесните это
Ошибки заполнения в таблицах Excel могут возникать по ряду причин. Они могут различаться в зависимости от введенных вами данных. Гораздо важнее найти причину разлива в диапазоне массива. Только после этого вы сможете применить подходящее исправление.
Для дальнейшего чтения мы рекомендуем узнать, как защитить паролем файл Excel .
