Ошибка заливки в Excel

Обновлено: 01.07.2024

В этом руководстве я расскажу вам о некоторых важных методах устранения этой ошибки.

По теме:

Что означает разлив в Excel?

Прежде чем мы перейдем к рассмотрению всех методов исправления ошибки Excel, нам необходимо сначала понять, что означает «расширение» в Excel.

Последняя версия Excel в подписке на Office 365 поставляется с набором формул, поддерживающих динамические массивы. Это означает, что эти формулы могут выполнять несколько вычислений и возвращать несколько значений одновременно, в отличие от обычной формулы Excel.

Если результатов несколько, они заполняются в ячейках, примыкающих к ячейке с формулой. В Excel это называется «разливом».

Что вызывает ошибку заполнения в Excel?

Но в некоторых случаях причины не столь очевидны.

Могут быть и другие причины, например невидимые значения, блокирующие диапазон заполнения, перетаскивание формулы, блокирующее диапазон заполнения, неподдерживаемое или неправильное использование динамических массивов и т. д.

Вы должны внимательно изучить каждую такую ​​ошибку, чтобы определить причину проблемы.

Также читайте:

Как исправить ошибку разлива в Excel?

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

Затем следуйте инструкциям для соответствующего сообщения об ошибке.

Диапазон разлива не пустой

Это означает, что какое-то значение или формула препятствуют расширению диапазона формулы динамического массива. Чтобы исправить ошибку, выполните следующие действия:

1. Очистить весь диапазон разлива после формулы динамического массива.
1. Или переместите формулу динамического массива в другое место.
1. Если область разлива визуально свободна, но по-прежнему вызывает ошибку разлива, щелкните параметр «Выбрать мешающие ячейки» под сообщением об ошибке. Это выделит ячейки, препятствующие разливу. Удалите их, чтобы устранить ошибку.







1. Имейте в виду, что формулы динамического массива не следует перетаскивать в направлении диапазона заполнения.

Разлив внутри таблиц

Имейте в виду, что формулы динамического массива не поддерживаются в таблицах Excel. Эта ошибка означает, что это так. Чтобы исправить это, либо переместите формулу в другое место, либо отформатируйте таблицу как диапазоны.







Диапазон заполнения содержит объединенные ячейки

Это означает, что одна или несколько объединенных ячеек препятствуют разливу. Чтобы исправить эту ошибку, разъедините объединенные ячейки или удалите их. Если вы не можете найти их визуально, щелкните параметр «Выбрать мешающие ячейки», чтобы выделить их.




Диапазон разлива неизвестен

Это сообщение об ошибке означает, что вы используете комбинацию двух или более функций динамического массива в формате Function1(Function2()).

Здесь функция Function1 не может определить размер массива сброса, возвращаемого функцией Function2. Это означает, что функция Function2 нестабильна.

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

Диапазон разлива слишком велик

Это означает, что ваша функция динамического массива возвращает массив разлива, длина которого выходит за границы вашей электронной таблицы Excel. (1 048 576 строк)

Это может произойти по многим причинам. Но обычная причина — неудачный выбор формулы, когда в качестве ссылки используются столбцы, а не диапазоны ячеек.




Чтобы исправить это, используйте диапазоны или ячейки в качестве ссылок внутри формулы. Но учтите, что это превратит формулу динамического массива в обычную формулу.




Чтобы продолжать использовать динамическую формулу, используйте неявное пересечение внутри формулы, добавив "@" перед ссылкой на столбец, вызывающий проблему.

Неявное пересечение — это причудливый способ попросить Excel принудительно объединить несколько значений в одно значение ячейки, тем самым исправив ошибки Excel.




Предлагаемые чтения:

Как отключить разлив в Excel?

Чтобы отключить разлив в Excel, вставьте символ «@» перед формулой, вызывающей ошибку. Это включит функцию неявного пересечения, т. е. автоматически уменьшит несколько значений до одного значения.

Как мне исправить ошибку утечки Vlookup?

Ошибки Excel Spill в функции ВПР обычно возникают из-за использования столбцов в качестве ссылки в формуле ВПР. Измените все такие ссылки на ссылки нормального диапазона. Обычно это исправляет ошибку заполнения.

Заключительные мысли

В этом руководстве мы рассмотрели причины возникновения ошибок Excel. Я объяснил различные методы исправления этих ошибок на примерах. Помните об этих вещах, так как они пригодятся в любое время.

Если у вас есть какие-либо вопросы об этой или любой другой функции Excel, сообщите нам об этом в разделе комментариев.

Здесь вы можете найти больше высококачественных руководств по Excel.

Готовы погрузиться в Excel? Нажмите здесь, чтобы перейти к продвинутым курсам Excel с углубленными учебными модулями.

Саймон Сез, ИТ-специалист, более десяти лет преподает Excel и другое программное обеспечение для бизнеса. За небольшую ежемесячную плату вы можете получить доступ к более чем 100 обучающим курсам по ИТ.

Адам Лейси

Адам Лейси – энтузиаст Excel и эксперт по онлайн-обучению. Он сочетает эти две страсти в Simon Sez IT, где он носит несколько разных шляп. Когда Адама не беспокоит посещаемость сайта или сводные таблицы, вы найдете его на теннисном корте или на кухне, готовя бурю.

С введением в Excel динамических массивов формулы, возвращающие несколько значений, "распространяют" эти значения непосредственно на листе. Прямоугольник, в который заключены значения, называется «диапазоном заполнения». При изменении данных диапазон разливов будет расширяться или сужаться по мере необходимости. Вы можете увидеть добавление новых значений или исчезновение существующих значений.

Поведение разлива является родным

Распространить информацию об ошибке




1. Дальность разлива заблокирована

Чтобы исправить ошибку, выберите любую ячейку в диапазоне разлива, чтобы увидеть ее границы. Затем убедитесь, что все ячейки в диапазоне разлива пусты. Как только "x" удален, функция UNIQUE выдает результаты в обычном режиме:

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

2. Таблицы Excel не поддерживают динамические массивы

3. Дальность разлива неизвестна

Это происходит из-за того, что СЛУЧМЕЖДУ является изменчивым, и поэтому массив, возвращаемый ПОСЛЕДОВАТЕЛЬНОСТЬЮ, будет иметь неизвестную длину. Единственное решение — избегать формул динамического массива, создающих массивы или диапазоны неизвестной длины.

4. Слишком большой диапазон разлива

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

5. Неявное пересечение (@)

Эта формула не выдаст ошибку в Excel 2016, поскольку неявное пересечение не позволит формуле вернуть несколько результатов. Однако в Dynamic Excel одна и та же формула автоматически возвращает несколько результатов, которые накладываются друг на друга, поскольку формула копируется в D5:D10. Одним из решений является использование символа @ для включения неявного пересечения следующим образом:

Примечание. Это также объясняет, почему вы можете внезапно увидеть символ "@" в формулах, созданных в более ранних версиях Excel. Это сделано для сохранения совместимости. Поскольку формулы в более ранних версиях Excel не могут распределяться по нескольким ячейкам, добавляется символ @, чтобы обеспечить такое же поведение при открытии формулы в версии Excel, поддерживающей динамические массивы.

В Dynamic Excel эта одна формула перенесет результаты в диапазон D5:D10, как показано на экране ниже:

Обратите внимание, что нет необходимости использовать абсолютную ссылку, так как одна формула создает все шесть результатов.

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

Уровень квалификации: средний

Посмотреть обучающее видео

Загрузить файлы Excel

Вы можете следовать руководству, используя файлы ниже. Я включил файлы Excel «До» и «После».

Диапазон заполнения в таблице Excel BEFORE.xlsx

Диапазон заполнения в таблице Excel ПОСЛЕ.xlsx







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

Есть несколько способов решить эту проблему.

1. Объедините значения с помощью TEXTJOIN

Мы можем обернуть нашу существующую функцию FILTER функцией TEXTJOIN. Это объединит несколько результатов в одну ячейку, разделенную разделителем (символ или знак по нашему выбору).

Первым аргументом функции TEXTJOIN является разделитель. Вы можете использовать запятую, пробел, косую черту, вертикальную черту или что-то еще, что, по вашему мнению, будет работать лучше всего. При вводе этой части формулы заключайте разделитель в кавычки. Кроме того, не забудьте добавить один или несколько пробелов до/после разделителя в зависимости от того, насколько вы хотите, чтобы ваши результаты были отделены друг от друга.

Я обнаружил, что при большом количестве таких чисел запятая не совсем разделяет записи визуально, а также что-то вроде пробела, символа вертикальной черты и другого пробела: " | "

Следующий аргумент — «Игнорировать пустые». Это означает, что формула будет пропускать записи, в которых нет ничего. Установите для этого аргумента значение true, просто введя слово true.

Последним аргументом является текст, и это массив, который возвращает функция ФИЛЬТР. Другими словами, уже существующая формула ФИЛЬТР становится последним аргументом функции ОБЪЕДИНЕНИЕ ТЕКСТ.

Все вместе наша формула выглядит так:




После завершения нашей формулы текст объединяется (но разделяется разделителями) для любой записи, которая имеет более одного результата.




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

Это подводит нас к следующему решению.

2. Разделить значения на отдельные ячейки с помощью ИНДЕКС

Как и в предыдущем решении, мы сохраним формулу ФИЛЬТР в качестве аргумента в нашей новой функции ИНДЕКС. В этом случае это будет первый аргумент для ИНДЕКС, то есть массив.

Второй аргумент — номер строки. Это номер строки, который мы хотим вернуть из диапазона сброса фильтра. Вы можете ввести цифру 1, чтобы обозначить первую строку электронной таблицы, но я предпочитаю сделать ее немного более динамичной. Вместо ввода 1 я могу выбрать ячейку, которая находится в строке 1, а затем добавить символ доллара ($) перед ссылкой на номер строки (но не добавлять символ доллара перед ссылкой на букву столбца). Пример: 1 доллар США

Это делает мою ссылку на строку абсолютной, а ссылку на столбец - относительной, чтобы значения вычислялись правильно при копировании в ячейки справа.

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




Нажмите Enter. Формула будет скопирована вниз, и в каждой ячейке столбца будет показан первый номер телефона для каждого клиента. При копировании формулы в столбцах справа появятся второй и третий телефонные номера, если применимо.







Сколько столбцов мне нужно?

Небольшой недостаток этого решения заключается в том, что вам нужно выяснить, сколько дополнительных столбцов добавить в таблицу. Относительно простой способ сделать это — перейти к исходным данным и добавить столбец, в котором вы можете ввести функцию СЧЁТЕСЛИ. Функция СЧЁТЕСЛИ подсчитает все повторяющиеся идентификаторы клиентов и вернёт количество раз, когда этот идентификатор встречается в данных.




Отсюда, если ваш набор данных относительно невелик, вы можете просмотреть список, чтобы увидеть наибольшее число. Это будет количество столбцов, которое вам нужно для вашей таблицы. Если у вас большой набор данных, вы можете использовать функцию MAX, чтобы быстро узнать, какое максимальное значение.




Похожие сообщения

Посмотрите эти руководства по связанным темам:

Заключение

Эти решения будут работать для любой формулы динамического массива, которая возвращает ошибку разлива. Это не относится к функции ФИЛЬТР.

Надеюсь, они будут вам полезны. Если у вас есть какие-либо другие комментарии, советы или вопросы относительно диапазонов разлива, я хотел бы услышать их в комментариях ниже.

В Microsoft Excel ошибки SPILL возникают, когда формула возвращает несколько результатов, и Excel не может вернуть результаты в сетку. В этом руководстве мы объясним процедуру удаления ошибки SPILL в Excel

Ошибка SPILL в Excel означает, что формула привела к нескольким значениям и была помещена в соседние ячейки.

Как удалить ошибку SPILL в Excel

1. Диапазон разлива не пуст.
2. Диапазон разлива слишком велик.
3. Выходит за пределы листа
4. Формула таблицы
5. Недостаточно памяти
6. Перенести в ячейки слияния
7. Неизвестный/резервный

1] Диапазон разлива не пустой

< бр />

Эта ошибка возникает, если диапазон переноса для массива переноса не пуст.

Когда выбрана ошибка ПРОБЛЕМА, пунктирная граница будет указывать предполагаемый диапазон разлива.

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

2] Слишком большой диапазон разлива

< бр />

Эта ошибка может возникать, когда Excel не может определить размер разлитого массива, поскольку он непредсказуем и изменяется между проходами вычисления. Динамические изменения размеров массива могут вызвать проходы вычислений, чтобы обеспечить полное вычисление электронной таблицы. Если массив продолжает изменяться во время этих дополнительных проходов и не стабилизируется, Excel разрешает динамический массив как SPILL.

3] Выходит за пределы листа




В Excel формула вызовет ошибку SPILL, если Excel выполнит поиск по всему столбцу, вернет 1 048 576 результатов и достигнет конца сетки Excel.

Есть три способа решить эту проблему:

Способ 1:




Ссылка на интересующие вас значения поиска; этот стиль формулы возвращает динамический массив, но не работает с таблицами Excel.

Способ 2:




Ссылайтесь на значение в той же строке, а затем скопируйте формулу вниз. Традиционный стиль формул будет работать в таблицах, но не будет возвращать динамический массив.

Способ 3:




Используйте оператор @, а затем скопируйте формулу вниз. Традиционный стиль формул будет работать в таблицах, но не будет возвращать динамический массив.

4] Формула таблицы




Формулы массива с переливами не поддерживаются в таблице Excel. Переместите формулу из таблицы или преобразуйте таблицу в диапазон. Нажмите Макет таблицы > Инструменты > Преобразовать в диапазон.

5] Недостаточно памяти

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

6] Перенести в ячейки слияния




В Microsoft Excel формулы переноса массива не могут переноситься в объединенные ячейки. Разъедините ячейки или переместите формулу в другой диапазон, не связанный с объединенными ячейками.

Выберите значок ошибки и выберите параметр Выбрать мешающие ячейки, чтобы сразу перейти к мешающим ячейкам. Разъединить ячейку. Когда объединенные ячейки будут очищены, появится формула массива.

7] Нераспознанный/Резервный вариант

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

Мы надеемся, что это руководство поможет вам понять, как удалять ошибки Spill в Excel; если у вас есть вопросы по поводу руководства, сообщите нам об этом в комментариях.

Дата: 7 февраля 2022 г. Теги: Excel

Похожие записи




Как использовать объединение в Excel для улучшения форматирования данных




Где скачать Microsoft Word, Excel, PowerPoint для Windows 11/10




[электронная почта защищена]

Шантель учится в университете и получает степень бакалавра наук в области информационных технологий. Ее цель — стать администратором базы данных или системным администратором. Она любит читать и смотреть исторические документальные фильмы и драмы.




сообщить об этом объявлении

Читайте также:

  
• Создание презентации в Powerpoint, практическая работа
  
• Как сделать красивую таблицу в Excel
  
• Как сделать выделение в word
  
• Как называется файловый менеджер, который является частью операционной среды Windows
  
• Прошивка модема yota 4g lte для всех операторов