Что такое ошибка разлива? Как решить проблему # РАЗЛИВ! ошибка в Excel 365

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

Например, в Excel 2019 и более ранних версиях предположим, что вы применяете формулу = D2: D5 к ячейке, результат будет ограничен первой ячейкой.

Ошибка разлива Excel перед динамическим массивом

Программы для Windows, мобильные приложения, игры - ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале - Подписывайтесь:)

Когда нам нужно было применить формулу ко всем соответствующим ячейкам, мы использовали нотацию массива (Ctrl + Shift + Enter). Однако в Excel 365 это не так. Когда вы применяете ту же формулу, значения автоматически распределяются по всем соответствующим ячейкам. Более подробную информацию см. На изображении ниже.

Ошибка разлива Excel после динамических массивов

Область ячеек, в которую попадает результат, называется диапазоном разлива. См. Изображение ниже

Ошибка разлива Excel Диапазон разлива

ЗАМЕТКА:

  • Разлив автоматически активируется с помощью динамических массивов (в настоящее время эта функция поддерживается только в Excel 365), и эту функцию нельзя отключить.
  • Функция «Разлив» включена для всех формул с функциями или без них.

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

Отображение ошибок разлива в Excel

Возможные причины возникновения ошибки #SPILL:

  • Диапазон Spill содержит какое-то значение, из-за которого результаты не могут быть помещены в ячейки.
  • В диапазоне разлива объединены ячейки.
  • Когда старые листы (созданные с помощью Excel 2016 или более ранней версии) с формулами, поддерживающими неявное пересечение, открываются в Excel365.
  • Когда вы применяете формулу динамического массива к таблице Excel.

Если вы видите ошибку #SPILL в excel, не беспокойтесь. В этой статье мы продемонстрируем различные способы определения основной причины этой проблемы, а также рассмотрим способы исправить ошибку #SPILL.

Определите, что вызывает ошибку #SPILL

Когда вы видите ошибку разлива, сначала проверьте, почему вы видите ошибку, для этого

Шаг 1. Щелкните ячейку с надписью #SPILL! ошибка

Шаг 2. Щелкните восклицательный знак, как показано ниже.

Шаг 3: Первая строка сообщает нам, что вызывает ошибку. Например, в этом случае ошибка видна, поскольку диапазон разлива не пуст.

Причины ошибки разлива Excel

Исправления, которые необходимо выполнить, если диапазон разлива не пустой

Если вы видите, что диапазон разлива не пустой, выполните следующие исправления.

Исправление 1. Удалите данные, блокирующие диапазон разлива.

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

Когда вы можете четко видеть данные, которые блокируют диапазон разлива

Рассмотрим приведенный ниже пример. Когда вы применяете формулу = D2: D5 к данным, выдается ошибка РАЗЛИВА, так как здесь I m находится в пределах диапазона разлива.

Текст данных ошибки разлива в пределах диапазона разлива

Чтобы избавиться от ошибки #SPILL, просто переместите данные или удалите данные из диапазона разлива.

Когда данные, блокирующие диапазон разлива, скрыты

В некоторых случаях данные, которые блокируют диапазон разлива, скрыты и не очень очевидны, как в случае 1. Рассмотрим пример ниже.

Ошибка разлива в белом шрифте Excel

В таких случаях, чтобы найти ячейку, блокирующую диапазон разлива, выполните следующие действия:

Шаг 1. Щелкните ячейку с надписью #SPILL! ошибка

Шаг 2: Щелкните восклицательный знак, как показано ниже. Вы можете видеть, что ошибка связана с тем, что диапазон разлива не пустой.

Шаг 3: В раскрывающемся списке нажмите «Выбрать препятствующие ячейки».

Ошибка разлива Excel. Выбор препятствующих ячеек

Шаг 4. Ячейка, блокирующая диапазон разлива, выделяется, как показано ниже.

Ошибка разлива Excel, препятствующая выделению ячейки

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

Шаг 5: При внимательном изучении ячейки вы можете увидеть некоторые данные, скрытые внутри ячеек.

Цвет шрифта ошибки разлива в Excel белый

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

Исправление 2: удалите форматирование произвольных чисел; ; ; нанесен на ячейку

Иногда при произвольном форматировании чисел; ; ; нанесен на ячейку, есть вероятность увидеть ошибку SPILL. В таких случаях,

Шаг 1. Щелкните ячейку с надписью #SPILL! ошибка

Шаг 2: Щелкните восклицательный знак, как показано ниже.

Шаг 3: В раскрывающемся списке нажмите «Выбрать препятствующие ячейки».

Ошибка разлива Excel. Выбор препятствующих ячеек

Шаг 4. Ячейка, блокирующая диапазон разлива, выделяется, как показано ниже.

Ошибка разлива Excel, препятствующая выделению ячейки

Шаг 5: Щелкните правой кнопкой мыши блокирующую ячейку.

Шаг 6: выберите формат ячеек

Ячейки формата Excel

Шаг 7. Откроется окно «Форматирование ячеек». Перейдите на вкладку Number

Шаг 8. На левой панели выберите Пользовательский.

Шаг 9: На правой боковой панели измените Тип с; ; ; генералу

Шаг 10: нажмите кнопку ОК.

Формат ячеек

Исправление, которое должно выполняться, когда диапазон разлива объединил ячейки

Если вы видите, что ошибка связана с тем, что диапазон разлива объединил ячейки, как показано ниже,

Ошибка разлива Excel Диапазон разлива объединил ячейки

Шаг 1. В раскрывающемся списке нажмите «Выбрать препятствующие ячейки».

Выберите препятствующие клетки

Шаг 2: блокирующая ячейка будет выделена

Шаг 3. На вкладке «Главная» нажмите «Объединить и центрировать».

Шаг 4. В раскрывающемся списке выберите «Разъединить ячейки».

Ошибка разлива Excel. Разблокируйте ячейки

Исправление, которое необходимо соблюдать, когда диапазон разлива в таблице

Формулы динамических массивов не поддерживаются в таблицах Excel. Если вы видите ошибку #SPILL в таблице Excel, как показано ниже, с сообщением Диапазон разлива в таблице,

Ошибка разлива Excel на таблице

Шаг 1: полностью выберите стол

Шаг 2. Щелкните вкладку «Дизайн таблицы» в верхней строке меню.

Шаг 3. Выберите «Преобразовать в диапазон».

Ошибка разлива на таблице преобразовать в простую таблицу

Шаг 4: Вы увидите всплывающее диалоговое окно подтверждения, нажмите Да

Диалоговое окно подтверждения ошибки разлива Excel

Исправление, которое необходимо соблюдать, когда диапазон разлива выходит за пределы памяти

Когда вы пытаетесь определить причину ошибки #SPILL, если вы видите, что ошибка указывает Out of Memory, то это связано с тем, что формула динамического массива, которую вы используете, ссылается на большой диапазон, в таких случаях excel исчерпывает память вызывая ошибку разлива. Чтобы преодолеть ошибку, можно попробовать обратиться к меньшему диапазону.

Исправление, которое необходимо выполнить, если диапазон разлива неизвестен

Эта ошибка возникает, когда размер разлитого массива изменяется и Excel не может установить размер разлитого диапазона. Обычно, когда вы используете случайные функции, такие как RANDARRAY, RAND или RANDBETWEEN, вместе с функциями динамического массива, такими как SEQUENCE, эта ошибка видна.

Чтобы лучше понять это, рассмотрим приведенный ниже пример, допустим, используется функция SEQUENCE (RANDBETWEEN (1,100)). Здесь RANDBETWEEN генерирует случайное целое число, которое больше или равно 1 и меньше или равно 100. SEQUENCE генерирует последовательные числа (Eg-SEQUENCE (5) генерирует 1,2,3,4,5). Однако RANDBETWEEN — это непостоянная функция, которая постоянно меняет свое значение каждый раз, когда открывается или изменяется лист Excel. Из-за этого функция SEQUENCE не сможет определить размер массива, который она должна сгенерировать. Он не знает, сколько значений нужно сгенерировать, и поэтому выдает ошибку SPILL.

Когда вы определяете причину ошибки, вы видите, что диапазон разлива неизвестен.

Диапазон ошибок разлива Excel неизвестен

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

Исправления, которые необходимо соблюдать, если диапазон разлива слишком велик.

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

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

Когда динамический массив отсутствовал, в Excel происходило нечто, называемое неявным пересечением, которое заставляло возвращать один результат, даже если формула могла возвращать несколько результатов. Рассмотрим пример: если формула = B: B * 5% применяется в Excel 2019 или более ранних версиях с неявным пересечением, результат будет следующим:

Диапазон ошибок разлива Excel раньше был слишком большим

Однако, когда та же формула используется в Excel 365, вы видите следующую ошибку

Слишком большой диапазон ошибок разлива Excel после

Чтобы решить эту проблему, попробуйте следующие исправления

Исправление 1: примените неявное пересечение с помощью оператора @

Когда мы говорим = B: B, динамический массив будет ссылаться на весь столбец B. Вместо этого мы можем заставить Excel наложить неявное пересечение с помощью оператора @

Измените формулу на [email protected]: B * 5%

2021 08 30 10х20 46

Поскольку добавлено неявное пересечение, формула будет применена к одной ячейке. Чтобы расширить формулу,

1. Просто нажмите на точку, как показано ниже.

Расширить формулу

2. При необходимости перетащите его на ячейки. К этим ячейкам будет применена та же формула.

Расширенная формула

Исправление 2: вместо ссылки на столбец обратитесь к диапазону

В формуле = B: B * 5% мы ссылаемся на столбец B. Вместо этого ссылаемся на конкретный диапазон = B2: B4 * 5%.

Обратитесь к диапазону

Это все

Надеемся, эта статья была информативной.

Пожалуйста, поставьте лайк и прокомментируйте, если вам удалось решить проблему с помощью вышеуказанных методов.

Спасибо за чтение.

Программы для Windows, мобильные приложения, игры - ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале - Подписывайтесь:)

Добавить комментарий

Ваш адрес email не будет опубликован.