Как мы все знаем, Office 365 поставляется с Excel 365 в комплекте. Microsoft добавила в Excel 365 различные новые функции. Одна из таких функций - Формулы динамических массивов. Обычно формула возвращает в ячейку только одно значение. Но теперь, благодаря этой новой функции, можно возвращать несколько значений.
Например, в Excel 2019 и более ранних версиях предположим, что вы применяете формулу = D2: D5 к ячейке, результат будет ограничен первой ячейкой.
Когда нам нужно было применить формулу ко всем соответствующим ячейкам, мы использовали нотацию массива (Ctrl + Shift + Enter). Однако в Excel 365 это не так. Когда вы применяете ту же формулу, значения автоматически распределяются по всем соответствующим ячейкам. Более подробную информацию см. На изображении ниже.
Область ячеек, в которую разливается результат, называется Диапазон разлива. См. Изображение ниже
ЗАМЕТКА:
- В Проливать автоматически включается с помощью динамических массивов (в настоящее время эта функция поддерживается только в Excel 365), и эту функцию нельзя отключить.
- Функция «Разлив» включена для всех формул с функциями или без них.
Ошибки разлива видны, когда формула предназначена для возврата нескольких значений, однако результаты не могут быть помещены в ячейки. Ошибка выглядит следующим образом:
Возможные причины возникновения ошибки #SPILL:
- Диапазон Spill содержит некоторое значение, из-за которого результаты не могут быть занесены в ячейки.
- В диапазоне разлива объединены ячейки.
- Когда старые листы (созданные с помощью Excel 2016 или более ранней версии) с формулами, поддерживающими неявное пересечение, открываются в Excel365.
- Когда вы применяете формулу динамического массива к таблице Excel.
Если вы видите ошибку #SPILL в excel, не беспокойтесь. В этой статье мы продемонстрируем различные способы определения основной причины этой проблемы, а также рассмотрим способы исправления ошибки #SPILL.
Оглавление
Определите, что вызывает ошибку #SPILL
Когда вы видите ошибку разлива, сначала проверьте, почему вы видите эту ошибку, для этого
Шаг 1. Нажмите на ячейку, в которой отображается #ПРОЛИВАТЬ! ошибка
Шаг 2: Нажмите на Восклицательный знак как показано ниже
Шаг 3: Первая строка сообщает нам, что вызывает ошибку. Например, в этом случае ошибка отображается, поскольку диапазон разлива не пустой.
Исправления, которые необходимо выполнить, если диапазон разлива не пустой
Следуйте приведенным ниже исправлениям, когда увидите, что Диапазон разлива не пуст
Исправление 1. Удалите данные, которые блокируют диапазон разлива.
Если в ячейках диапазона разлива уже есть данные, при применении формулы вы увидите ошибку #SPILL.
Когда вы можете четко видеть данные, которые блокируют диапазон разлива
Рассмотрим приведенный ниже пример. Когда вы применяете формулу = D2: D5 к данным, выдается ошибка SPILL, поскольку есть Я здесь в пределах зоны разлива.
Чтобы избавиться от ошибки #SPILL, просто переместите данные или удалите данные из диапазона разлива.
Когда данные, блокирующие диапазон разлива, скрыты
В некоторых случаях данные, которые блокируют диапазон разлива, скрыты и не очень очевидны, как в случае 1. Рассмотрим приведенный ниже пример.
В таких случаях, чтобы найти ячейку, блокирующую диапазон разлива, выполните следующие действия:
Шаг 1. Нажмите на ячейку, в которой отображается #ПРОЛИВАТЬ! ошибка
Шаг 2: Нажмите на Восклицательный знак как показано ниже, вы можете видеть, что ошибка связана с тем, что Диапазон разлива не пуст.
Шаг 3. В раскрывающемся списке нажмите Выберите препятствующие клетки
Шаг 4. Ячейка, блокирующая диапазон разлива, выделяется, как показано ниже.
Теперь, когда вы знаете, какая ячейка блокируется, проверьте, что именно вызывает проблему.
Шаг 5: При внимательном изучении ячейки вы можете увидеть некоторые данные, скрытые внутри ячеек.
Как видно на изображении выше, есть некоторые данные. Поскольку шрифт имеет белый цвет, распознать засор непросто. Чтобы избавиться от ошибки, удалите данные из ячейки в диапазоне Spill.
Исправить 2: Удалите форматирование произвольных чисел;;; нанесен на ячейку
Иногда, когда настраиваемое форматирование чисел ;; ; нанесен на ячейку, есть вероятность увидеть ошибку SPILL. В таких случаях,
Шаг 1. Нажмите на ячейку, в которой отображается #ПРОЛИВАТЬ! ошибка
Шаг 2: Нажмите на Восклицательный знак как показано ниже.
Шаг 3. В раскрывающемся списке нажмите Выберите препятствующие клетки
Шаг 4. Ячейка, блокирующая диапазон разлива, выделяется, как показано ниже.
Шаг 5: Щелкните правой кнопкой мыши блокирующую ячейку.
Шаг 6: выберите Формат ячеек
Шаг 7. Откроется окно «Форматирование ячеек». Перейти к Число вкладка
Шаг 8: На левой панели выберите Обычай
Шаг 9: На правой боковой панели измените Тип с;;; к Общий
Шаг 10: Нажмите на Ok кнопка
Исправление, которое должно выполняться, когда диапазон разлива объединил ячейки
Если вы видите, что ошибка связана с Диапазон разлива объединил ячейки как показано ниже,
Шаг 1. Нажмите Выберите препятствующие клетки из раскрывающегося списка
Шаг 2: блокирующая ячейка будет выделил
Шаг 3: Под Дом вкладку, нажмите на Слияние и центр
Шаг 4. В раскрывающемся списке выберите Разъединить ячейки
Исправление, которое необходимо соблюдать, когда Диапазон разлива в таблице
Формулы динамического массива не поддерживаются в таблицах Excel. Если вы видите ошибку #SPILL в таблице Excel, как показано ниже, с сообщением Диапазон разлива в таблице,
Шаг 1. Полностью выберите таблицу
Шаг 2: Нажмите на Дизайн стола вкладка в верхней строке меню
Шаг 3: выберите Преобразовать в диапазон
Шаг 4: Вы увидите всплывающее диалоговое окно подтверждения, нажмите да
Исправление, которое необходимо соблюдать, когда диапазон разлива выходит за пределы памяти
Когда вы пытаетесь определить причину ошибки #SPILL, если вы видите, что ошибка указывает Недостаточно памяти, то это потому, что формула динамического массива, которую вы используете, ссылается на большой диапазон, в таких случаях excel запускает недостаточно памяти вызывая ошибку разлива. Чтобы преодолеть ошибку, можно попробовать обратиться к меньшему диапазону.
Исправление, которое необходимо выполнить, если диапазон разлива неизвестен
Эта ошибка возникает, когда размер Spilled Array изменяется и Excel не может установить размер Spilled Array. Обычно, когда вы используете случайные функции, такие как RANDARRAY, RAND или RANDBETWEEN, вместе с функциями динамического массива, такими как SEQUENCE, эта ошибка видна.
Чтобы лучше понять это, рассмотрим приведенный ниже пример, допустим, используется функция ПОСЛЕДОВАТЕЛЬНОСТЬ (СЛУЧМЕЖДУ (1,100)). Здесь RANDBETWEEN генерирует случайное целое число, которое больше или равно 1 и меньше или равно 100. И ПОСЛЕДОВАТЕЛЬНОСТЬ генерирует последовательные числа (например, ПОСЛЕДОВАТЕЛЬНОСТЬ (5) генерирует 1,2,3,4,5). Однако RANDBETWEEN - это непостоянная функция, которая постоянно меняет свое значение каждый раз, когда открывается или изменяется лист Excel. Из-за этого функция SEQUENCE не сможет определить размер массива, который она должна сгенерировать. Он не знает, сколько значений нужно сгенерировать, и поэтому выдает ошибку SPILL.
Когда вы определяете причину ошибки, вы видите Дальность разлива неизвестна
Чтобы исправить такую ошибку, попробуйте найти другую формулу, которая соответствует вашим потребностям.
Исправления, которые необходимо соблюдать при слишком большом диапазоне разлива.
Допустим, вы определяете причину и замечаете, что ошибка видна, потому что Диапазон разлива слишком велик как показано ниже.
Когда динамический массив отсутствовал, в Excel было что-то, называемое неявным пересечением, которое вынужден возвращать один результат, даже если формула могла вернуть несколько полученные результаты. Рассмотрим пример, если формула = B: B * 5% применяется в Excel 2019 или более ранних версиях с неявным пересечением, результат будет следующим:
Однако, когда та же формула используется в Excel 365, вы видите следующую ошибку
Чтобы решить эту проблему, попробуйте следующие исправления
Исправление 1: примените неявное пересечение с помощью оператора @
Когда мы говорим = B: B, динамический массив будет ссылаться на весь столбец B. Вместо этого мы можем заставить excel наложить неявное пересечение с помощью оператора @
Измените формулу на[электронная почта защищена]: B * 5%
Поскольку добавлено неявное пересечение, формула будет применена к одной ячейке. Чтобы расширить формулу,
1. Просто нажмите на точку, как показано ниже.
2. При необходимости перетащите его на ячейки. К этим ячейкам будет применена та же формула.
Исправление 2: вместо ссылки на столбец обратитесь к диапазону
В формуле = B: B * 5%, мы имеем в виду столбец B. Вместо этого обратитесь к определенному диапазону, = B2: B4 * 5%
Это все
Надеемся, эта статья была информативной.
Пожалуйста, поставьте лайк и прокомментируйте, если вам удалось решить проблему с помощью вышеуказанных методов.
Спасибо за чтение.
