Что такое ошибка #SPILL в Excel и как ее исправить?

Эта статья поможет вам понять все причины ошибок #SPILL, а также способы их устранения в Excel 365.

#ПРОЛИВАТЬ! - это новый вид ошибки Excel, которая в основном возникает, когда формула, которая дает несколько результатов вычислений, пытается отобразить свои выходные данные в диапазоне разлива, но этот диапазон уже содержит некоторые другие данные.

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

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

Что вызывает ошибку разлива?

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

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

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

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

  • ПОСЛЕДОВАТЕЛЬНОСТЬ
  • ФИЛЬТР
  • ТРАНСПОРТИРОВКА
  • СОРТИРОВАТЬ
  • СОРТИРОВАТЬ ПО
  • СЛУЧАЙНЫЙ
  • УНИКАЛЬНЫЙ
  • XLOOKUP
  • XMATCH

Формулы динамических массивов доступны только в Excel 365 и в настоящее время не поддерживаются ни одним автономным программным обеспечением Excel (например, Microsoft Excel 2016, 2019).

Ошибки разлива возникают не только из-за препятствий для данных, есть несколько причин, по которым вы можете получить ошибку #Spill. Давайте рассмотрим различные ситуации, в которых вы можете столкнуться с #SPILL! ошибки и как их исправить.

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

Одна из основных причин ошибки разлива заключается в том, что диапазон разлива не пуст. Например, если вы пытаетесь отобразить 10 результатов, но если в любой из ячеек в области разлива есть какие-либо данные, формула вернет #SPILL! ошибка.

Пример 1:

В приведенном ниже примере мы ввели функцию TRANSPOSE в ячейку C2, чтобы преобразовать вертикальный диапазон ячеек (B2: B5) в горизонтальный диапазон (C2: F2). Вместо того, чтобы переключать столбец на строку, Excel показывает нам #SPILL! ошибка.

И когда вы нажмете на ячейку формулы, вы увидите синюю пунктирную границу, указывающую область / диапазон разлива (C2: F2), которая необходима для отображения результатов, как показано ниже. Также вы заметите желтый предупреждающий знак с восклицательным знаком.

Чтобы понять причину ошибки, щелкните значок предупреждения рядом с ошибкой и просмотрите сообщение в первой строке, выделенное серым цветом. Как видите, здесь написано: «Диапазон разлива не пустой».

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

Решение:

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

Как только вы удалите или переместите блокировку, Excel автоматически заполнит ячейки результатами формулы. Здесь, когда мы очищаем текст в D2 и E2, формула перемещает столбец в строку, как и предполагалось.

Пример 2:

В приведенном ниже примере, хотя диапазон разлива кажется пустым, в формуле по-прежнему отображается значение «Разлив! ошибка. Это потому, что разлив на самом деле не пустой, у него есть невидимый пробел в одной из ячеек.

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

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

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

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

Иногда #SPILL! ошибка возникает, когда диапазон разлива содержит объединенные ячейки. Формула динамического массива не работает с объединенными ячейками. Чтобы исправить это, все, что вам нужно сделать, - это разделить ячейки в диапазоне разлива или переместить формулу в другой диапазон, в котором нет объединенных ячеек.

В приведенном ниже примере, даже если диапазон разлива пуст (C2: CC8), формула возвращает ошибку разлива. Это потому, что ячейки C4 и C5 объединены.

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

Решение:

Чтобы разделить ячейки, выберите объединенные ячейки, затем на вкладке «Главная» нажмите кнопку «Объединить и центрировать» и выберите «Разъединить ячейки».

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

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

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

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

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

Чтобы подтвердить причину этой ошибки, нажмите на предупреждающий знак и посмотрите причину ошибки - «Диапазон разлива в таблице».

Решение:

Чтобы исправить ошибку, вам нужно будет вернуть таблицу Excel обратно в диапазон. Для этого щелкните правой кнопкой мыши в любом месте таблицы, выберите «Таблица», а затем выберите параметр «Преобразовать в диапазон». Кроме того, вы можете щелкнуть левой кнопкой мыши в любом месте таблицы, затем перейти на вкладку «Дизайн таблицы» и выбрать параметр «Преобразовать в диапазон».

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

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

Этот тип ошибки разлива обычно возникает при использовании энергозависимых функций, таких как RAND, RANDARRAY, RANDBETWEEN, OFFSET и INDIRECT.

Например, когда мы используем приведенную ниже формулу в ячейке B3, мы получаем ошибку разлива:

= ПОСЛЕДОВАТЕЛЬНОСТЬ (СЛУЧМЕЖДУ (1; 500))

В этом примере функция RANDBETWEEN возвращает случайное целое число от 1 до 500, и ее выходные данные постоянно меняются. А функция ПОСЛЕДОВАТЕЛЬНОСТЬ не знает, сколько значений создать в массиве сброса. Следовательно, ошибка #SPILL.

Вы также можете подтвердить причину ошибки, нажав на предупреждающий знак - «Диапазон разлива неизвестен».

Решение:

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

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

Иногда вы можете выполнить формулу, которая выводит разлитый диапазон, который слишком велик для обработки рабочего листа, и он может выходить за края рабочего листа. Когда это произойдет, вы можете получить # РАЗЛИВ! ошибка. Чтобы решить эту проблему, вы можете попробовать указать конкретный диапазон или одну ячейку вместо целых столбцов или использовать символ «@», чтобы включить неявное пересечение

В приведенном ниже примере мы пытаемся вычислить 20% показателей продаж в столбце A и вернуть результаты в столбце B, но вместо этого мы получаем ошибку разлива.

Формула в B3 вычисляет 20% значения в A3, затем 20% значения в A4 и так далее. Он дает более миллиона результатов (1 048 576) и выводит все из них в столбец B, начиная с ячейки B3, но достигает конца рабочего листа. Недостаточно места для отображения всех выходных данных, в результате мы получаем ошибку #SPILL.

Как видите, причина этой ошибки в том, что - «Диапазон разлива слишком велик».

Решения:

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

Исправить 1: Вы можете попробовать ссылаться на диапазоны, а не на целые столбцы. Здесь мы меняем весь диапазон A: A на A3: A11 в формуле, и формула автоматически заполнит диапазон результатами.

Исправление 2: Замените весь столбец только ссылкой на ячейку в той же строке (A3), а затем скопируйте формулу вниз по диапазону, используя маркер заполнения.

Исправление 3: Вы также можете попробовать добавить оператор @ перед ссылкой, чтобы выполнить неявное пересечение. Это отобразит результат только в ячейке формулы.

Затем скопируйте формулу из ячейки B3 в остальную часть диапазона.

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

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

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

Неизвестный / Резервный

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

Теперь вы знаете все причины и решения проблемы #SPILL! ошибки в Excel 365.