Как исправить ошибку #SPILL! ошибка
Резюме
Ошибка #SPILL возникает, когда диапазон разлива блокируется чем-то на листе. Решение обычно состоит в том, чтобы очистить зону разлива от любых препятствующих данных. См. Ниже дополнительную информацию и инструкции по устранению.
Объяснение
О разливе и # РАЗЛИВ! ошибка
С появлением в Excel динамических массивов формулы, возвращающие несколько значений, «переносят» эти значения непосредственно на рабочий лист. Прямоугольник, в котором заключены значения, называется «диапазоном разлива». При изменении данных диапазон разлива будет расширяться или сокращаться по мере необходимости. Вы можете увидеть добавленные новые значения или исчезновение существующих.
Видео: Разлив и дальность разлива
Ошибка #SPILL возникает, когда диапазон разлива блокируется чем-то на листе. Иногда этого ожидают. Например, вы ввели формулу, ожидая, что она разразится, но существующие данные на листе мешают. Решение состоит в том, чтобы просто очистить зону разлива от любых мешающих данных.
Однако иногда ошибка может быть неожиданной и поэтому сбивать с толку. Прочтите ниже, как может быть вызвана эта ошибка и что вы можете сделать для ее устранения.
Поведение при разливе является естественным
Важно понимать, что поведение разлива является автоматическим и естественным. В динамическом Excel (в настоящее время только в Office 365 Excel) любая формула, даже простая формула без функций, может сказаться на результатах. Несмотря на то, что есть способы запретить формуле возвращать несколько результатов, отключение самого разлива с помощью глобального параметра невозможно.
Точно так же в Excel нет возможности «отключить ошибки #SPILL». Чтобы исправить ошибку #SPILL, вам необходимо изучить и устранить основную причину проблемы.
Это самый простой для решения случай. Формула должна содержать несколько значений, но вместо этого возвращает #SPILL! потому что что-то мешает. Чтобы устранить ошибку, выберите любую ячейку в диапазоне разлива, чтобы видеть ее границы. Затем либо переместите данные блокировки в новое место, либо удалите данные полностью. Обратите внимание, что ячейки в диапазоне разлива должны быть пустыми, поэтому обратите внимание на ячейки, содержащие невидимые символы, например пробелы.
На приведенном ниже экране «x» блокирует диапазон разлива:
После удаления символа «x» функция UNIQUE обычно выдаёт результаты:
До появления динамических массивов Excel молча применял поведение, называемое «неявным пересечением», чтобы гарантировать, что определенные формулы, которые могут возвращать несколько результатов, возвращают только один результат. В Excel с нединамическими массивами эти формулы возвращают нормальный результат без ошибок. Однако в некоторых случаях та же формула, введенная в Dynamic Excel, может вызвать ошибку #SPILL. Например, на экране ниже ячейка D5 содержит скопированную формулу:
Эта формула не вызовет ошибки, скажем, в Excel 2016, поскольку неявное пересечение не позволит формуле возвращать несколько результатов. Однако в Dynamic Excel формула автоматически возвращает несколько результатов на рабочий лист, которые врезаются друг в друга, поскольку формула копируется из D5: D10.
С этим изменением каждая формула снова возвращает один результат, и ошибка #SPILL исчезает.
Примечание. Это частично объясняет, почему вы можете внезапно увидеть символ «@» в формулах, созданных в более ранних версиях Excel. Это сделано для сохранения совместимости. Поскольку формулы в более ранних версиях Excel не могут быть разделены на несколько ячеек, добавляется символ @, чтобы обеспечить такое же поведение при открытии формулы в динамическом Excel.
В динамическом Excel эта единственная формула передаст результаты в диапазон D5: D10, как показано на снимке экрана ниже:
Обратите внимание, что нет необходимости использовать абсолютную ссылку.
How to correct a #SPILL! error
#SPILL errors are returned when a formula returns multiple results, and Excel cannot return the results to the grid. For more details on these error types, see the following help topics:
This error occurs when the spill range for a spilled array formula isn’t blank.
When the formula is selected, a dashed border will indicate the intended spill range.
You can select the Error floatie, and choose the Select Obstructing Cells option to immediately go the obstructing cell(s). You can then clear the error by either deleting, or moving the obstructing cell’s entry. As soon as the obstruction is cleared, the array formula will spill as intended.
Excel was unable to determine the size of the spilled array because it’s volatile, and resizes between calculation passes. For instance, the following formula will trigger this #SPILL! error:
Dynamic array resizes may trigger additional calculation passes to ensure the spreadsheet is fully calculated. If the size of the array continues to change during these additional passes and does not stabilize, Excel will resolve the dynamic array as #SPILL!.
This error value is generally associated with the use of RAND, RANDARRAY, and RANDBETWEEN functions. Other volatile functions such as OFFSET, INDIRECT, and TODAY do not return different values on every calculation pass.
There are 3 simple ways to resolve this issue:
Reference just the lookup values you are interested in. This style of formula will return a dynamic array, but does not work with Excel tables.
Reference just the value on the same row, and then copy the formula down. This traditional formula style works in tables, but will not return a dynamic array.
Request that Excel perform implicit intersection using the @ operator, and then copy the formula down. This style of formula works in tables, but will not return a dynamic array.
Spilled array formulas aren’t supported in Excel tables. Try moving your formula out of the table, or converting the table to a range (click Table Design > Tools > Convert to range).
The spilled array formula you’re attempting to enter has caused Excel to run out of memory. Please try referencing a smaller array or range.
Spilled array formulas cannot spill into merged cells. Please un-merge the cells in question, or move the formula to another range that doesn’t intersect with merged cells.
When the formula is selected, a dashed border will indicate the intended spill range.
You can select the Error floatie, and choose the Select Obstructing Cells option to immediately go the obstructing cell(s). As soon as the merged cells are cleared, the array formula will spill as intended.
Excel doesn’t recognize, or can’t reconcile the cause of this error. Please make sure your formula contains all the required arguments for your scenario.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in the Answers community.
How to fix the #SPILL! error
A #SPILL error occurs when a spill range is blocked by something on the worksheet. The solution is usually to clear the spill range of any obstructing data. See below for more information and steps to resolve.
About spilling and the #SPILL! error
With the introduction of Dynamic Arrays in Excel, formulas that return multiple values «spill» these values directly onto the worksheet. The rectangle that encloses the values is called the «spill range». When data changes, the spill range will expand or contract as needed. You might see new values added, or existing values disappear.
A #SPILL error occurs when a spill range is blocked by something on the worksheet. Sometimes this is expected. For example, you have entered a formula, expecting it to spill, but existing data in the worksheet is in the way. The solution is just to clear the spill range of any obstructing data.
Sometimes however the error might be unexpected and therefore confusing. Read below for how this error might be caused, and what you can do to resolve.
Spill behavior is native
It’s important to understand that spill behavior is automatic and native. In Dynamic Excel (currently Office 365 Excel only) any formula, even a simple formula without functions, can spill results. Although there are ways to stop a formula from returning multiple results, spilling itself can’t be disabled with a global setting.
Similarly, there is no option in Excel to «disable #SPILL errors. To fix a #SPILL error, you’ll have to investigate and resolve the root cause of the problem.
This is the simplest case to resolve. The formula should spill multiple values, but instead it returns #SPILL! because something is in the way. To resolve the error, select any cell in the spill range so you can see its boundaries. Then either move the blocking data to a new location, or delete the data altogether. Note that cells in the spill range must be empty, so pay attention to cells that contain invisible characters, like spaces.
In the screen below, the «x» is blocking the spill range:
One the «x» is removed, the UNIQUE function spills results normally:
Before Dynamic Arrays, Excel silently applied a behavior called «implicit intersection» to ensure that certain formulas with the potential to return multiple results only returned a single result. In non-dynamic array Excel, these formulas return a normal looking result with no error. However, in certain cases the same formula entered in Dynamic Excel may generate a #SPILL error. For example, in the screen below, cell D5 contains this formula, copied down:
This formula wouldn’t throw an error in, say Excel 2016 because implicit intersection would prevent the formula from returning multiple results. However, in Dynamic Excel, the formula automatically returns spill multiple results onto to the worksheet, and which crash into each other, since the formula is copied down from D5:D10.
One solution is to use the @ character to enable implicit intersection like this:
With this change, each formula returns a single result again and the #SPILL error disappears.
Note: this partially explains why you might suddenly see the «@» character appear in formulas created in older versions of Excel. This is done to maintain compatibility. Since formulas in older versions of Excel can’t spill into multiple cells, the @ is added to ensure the same behavior when the formula is opened in Dynamic Excel.
Another (better) way to fix the #SPILL error shown above is to use a native dynamic array formula in D5 like this:
In Dynamic Excel, this single formula will spill results into the range D5:D10, as seen in the screenshot below:
Note there is no need to use an absolute reference.
Функция УНИК
Функция УНИК возвращает список уникальных значений в списке или диапазоне.
Возвращение уникальных значений из списка значений
Возвращение уникальных имен из списка имен
Примечание: Эта функция в настоящее время доступна только Microsoft 365 подписчикам.
Аргументы функции УНИКА следующую:
Диапазон или массив, из которого возвращаются уникальные строки или столбцы
Аргумент by_col — это логическое значение, указывающее, как это сделать.
Истина сравнивает столбцы друг с другом и возвращает уникальные столбцы.
Ложь (или опущен) сравнивает строки друг с другом и возвращает уникальные строки.
Аргумент exactly_once является логическим значением, которое возвращает строки или столбцы, которые встречаются в диапазоне или массиве ровно один раз. Это уникальное понятие базы данных.
Истина возвращает все отдельные строки или столбцы, которые встречаются точно один раз из диапазона или массива.
Ложь (или опущен) возвращает все отдельные строки или столбцы из диапазона или массива.
Массив может быть частью строки или столбца со значениями или сочетанием строк и столбцов со значениями. В примерах выше массивами для наших формул УНИКА являются диапазоны D2:D11 и D2:D17 соответственно.
Функция УНИК возвращает массив, который будет рассеиваться, если это будет конечным результатом формулы. Это означает, что Excel будет динамически создавать соответствующий по размеру диапазон массива при нажатии клавиши ВВОД. Если ваши вспомогательные данные хранятся в таблице Excel, тогда массив будет автоматически изменять размер при добавлении и удалении данных из вашего диапазона массива, если вы используете Структурированные ссылки. Дополнительные сведения см. в статье Поведение рассеянного массива.
Приложение Excel ограничило поддержку динамических массивов в операциях между книгами, и этот сценарий поддерживается, только если открыты обе книги. Если закрыть исходную книгу, все связанные формулы динамического массива вернут ошибку #ССЫЛКА! после обновления.
Примеры
В этом примере сортировка и УНИКА используются вместе для возврата уникального списка имен в порядке возрастания.
В этом примере exactly_once имеется аргумент ИСТИНА, а функция возвращает только тех клиентов, которым одно время была назначена услуга. Это может быть полезно, если вы хотите определить людей, которые не вернулись для получения дополнительной службы, чтобы связаться с ними.
В этом примере с помощью амперанда (&) фамилия и имя совмещеются с полным именем. Обратите внимание, что формула ссылается на весь диапазон имен в диапазонах A2:A12 и B2:B12. Это позволяет Excel массив всех имен.
Чтобы отсортировать список имен, можно добавить функцию СОРТ: =СОРТ(УНИКА(B2:B12&» «&A2:A12))
В этом примере сравнивают два столбца и возвращаются только уникальные значения между ними.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
data_client
10 наиболее полезных функций при анализе данных в Excel
Excel содержит огромное количество самых разнообразных функций, однако не все они нужны при анализе данных. В этой статье вы узнаете о 10 наиболее популярных функций, которые будут нужны при работе с информацией. Эти функции позволяют выполнить большинство задач, которые появляются при анализе данных.
1. ВПР
Эта функция является одной из самых популярных и часто используемых в Excel. Если вам необходимо найти данные в одном столбце в таблице и получить значение из другого столбца таблицы, то эта функция вам поможет. Ее синтаксис:
ВПР (искомое значение; таблица; номер столбца; интервальный просмотр)
Если хотите изучить более подробно, как работает функция ВПР, прочитайте нашу статью «Функция ВПР в Excel».
2. ГПР
Функция ГПР выполняет туже задачу, что и ВПР, только она просматривает первую строку в поиске искомого значения и для получения результата сдвигается на указанное количество строк вниз.
ГПР(искомое значение;таблица;номер строки;интервальный просмотр)
3. ЕСЛИ
Функция ЕСЛИ является очень популярной в Excel. Она позволяет автоматически выполнять какое-либо действие, в зависимости от поставленного условия.
Функция ЕСЛИ выполняет проверку логического выражения и если выражение истинно, то поставляется одно значение и альтернативное, если ложь. Синтаксис следующий:
ЕСЛИ(логическое выражение; значение если истина; значение если ложь)
Обратите внимание, что значения при истине или лжи могут быть не только текстовые, числовые, но также и функции(в том числе и ЕСЛИ), что позволяет реализовать достаточно сложные логические конструкции.
4. ЕСЛИОШИБКА
При работе с формулами в Excel, можно время от времени сталкиваться с различными ошибками. Так в примере ниже функция ВПР вернула ошибку #Н/Д из-за того, что в базе данных по ФИО нет искомой нами фамилии (более подробно об ошибке #Н/Д вы можете прочитать в этой статье: «Как исправить ошибку #Н/Д в Excel»)
Для обработки таких ситуаций отлично подойдет функция ЕСЛИОШИБКА. Ее синтаксис следующий:
ЕСЛИОШИБКА(значение; значение если ошибка)
В случае с нашим примером выше, мы можем предположить, что фамилия может быть некорректной, соответственно ЕСЛИОШИБКА вернет нам предупреждение, что бы мы проверили написание фамилии.
5. СУММЕСЛИМН
Функция СУММЕСЛИМН позволяет суммировать значения по определенным условиям. Условий может быть несколько. В Excel также есть функция СУММЕСЛИ, которая позволяет суммировать по одному критерию. Призываю вас использовать более универсальную формулу.
У функции СУММЕСЛИМН следующий синтаксис:
СУММЕСЛИМН(Диапазон суммирования; Диапазон условия 1; Условие 1;. )
Более подробно о функциях СУММЕСЛИМН и СУММЕСЛИ рассказано в статье «СУММЕСЛИ и СУММЕСЛИМН в Excel».
6. СЧЁТЕСЛИМН
СЧЁТЕСЛИМН очень похожа на функцию СУММЕСЛИМН, только в отличии от нее, она не суммируется значения, а только считает количество ячеек, которые соответствуют определенным условиям. Как и в случае с СУММЕСЛИМН, у СЧЁТЕСЛИМН есть упрощенная форма СЧЁТЕСЛИ, который считает количество ячеек только по одному критерию, но лучше используйте более общий вариант.
Синтаксис у функции следующий:
СЧЁТЕСЛИМН(диапазон условия 1; условие 1;. )
7. СЖПРОБЕЛЫ
При работе с данными в Excel, мы можем получать их из разных источников, что может привести к тому, что получаемые значения имеют «мусорную» информацию, очень часто это лишние пробелы, которые надо удалить. Можно удалять вручную, но это долго и муторно. На выручку нам приходит функция СЖПРОБЕЛЫ, которая удаляет лишние пробелы, в случае если их больше одного подряд. Синтаксис у функции очень простой:
8. ЛЕВСИМВ и ПРАВСИМВ
Функции ЛЕВСИМВ и ПРАВСИМВ возвращают определенное количество знаков с начала (ЛЕВСИМВ) либо с конца (ПРАВСИМВ) строки. Эти функции нужны для получения части строки. Синтаксис у функций однотипный:
ЛЕВСИМВ(текст; количество знаков)
ПРАВСИМВ(текст; количество знаков)
В примере выше мы из текста «Пример текста» извлекаем 6 символов слева и получаем текст «Пример».
9. СЦЕПИТЬ
Функция СПЕПИТЬ позволяет объединить значения из нескольких ячеек. Синтаксис у функции достаточно простой:
10.ЗНАЧЕН
Часто данные, которые мы получаем из внешних источников, имеют текстовый формат и мы не можем производить с ними математических действий (складывать, вычитать и т.п.). Нам требуется сначала преобразовать текст в число, для этого используйте функцию ЗНАЧЕН. Синтаксис у функции следующий:
Как видно в примере выше, у нас есть число 12522, которое представлено в виде текста, при помощи функции ЗНАЧЕН мы преобразовали его в число 12 522, с которым в дальнейшем можем работать, как с любыми другими числами.
Спасибо, что дочитали статью. Я постарался выбрать 10 наиболее полезных функций в Excel, которые нужны при анализе данных. Жду ваши комментарии.
