Ошибка spill в excel
Обновлено: 07.07.2024
Before we dive into specific use cases, let's get a general understanding of spilling in Excel.
For the most part, this behavior is understandable and predictable. For instance, if your formula is expected to return more than one value, but the nearby cells are filled with some other data, simply delete that data, and an error will be gone.
But sometimes the reasons are not so obvious and therefore confusing. Perhaps, one or more cells in the spilled area contain a space or a non-printing character invisible to a human eye. Or the same formula copied across the entire column blocks a spill range. Or, you might have run into one of the very few features that do not support dynamic arrays. To fix an error, you'll have to investigate each case individually and determine the root of the problem.
Once you determined the cause, please find the corresponding example below with the detailed instructions on how to resolve that particular case.
Spill range isn't blank
Reason: The area to be populated with the formula results contains non-empty cells.
Solution: Clear the expected spill range.
In a simplest scenario, just click the formula cell and you will see a dashed border indicating the spill range boundaries - any data inside it is an obstacle. So, either remove the existing data from the spill area or move the formula to another location where there is no blockage.
In some situations, however, a spill range may look blank, but in fact it is not. The problem may be in invisible characters such as a space lurking in some cells, or formulas returning an empty string.
To detect such cells, click a warning sign, and you will see this explanation - Spill range isn't blank. Underneath it, there are a number of options. Click Select Obstructing Cells, and Excel will show you which cells prevent the formula from spilling.
In the screenshot below, the obstructing cell is A6, which contains an empty string ("") returned by the formula.
To clear blocking cells, go to the Home tab > Editing group, and click Clear > Clear All.
Spill range contains merged cells
Reason: Spilling does not work with merged cells.
Solution: Unmerge cells in the spilled area or move the formula to another location that has no merged cells.
In case there are one or more merged cells in a projected spilled array, the following error message is displayed - Spill range has merged cell.
If you have difficulties detecting the merged cells visually, make use of Select Obstructing Cells option to jump to the problematic cells:
Spill range in table
Reason: Dynamic arrays are not supported in Excel tables.
Solution: Convert the table to a normal range or place the formula outside the table to allow it to spill. It's not quite clear why dynamic array formulas do not work from within Excel tables (maybe because of the specific syntax of structured references), but anyway these two very useful things do not get along :(
To confirm the root cause of the problem, click an icon with an exclamation mark, and you will see this text in the first line - Spill range in table.
In this case, the best thing you could do is to convert table to range. For this, right-click anywhere within the table, and then click Table > Convert to Range. Alternatively, you can move your formula beyond the table boundaries.
Spill range is unknown
Reason: Excel is unable to establish the size of the spilt array.
Solution: Try to work out a different formula for your task.
In such situation, an error message says - Spill range is unknown.
Spill range is too big
Reason: Excel is unable to output a spilt array as it extends beyond the spreadsheet edges.
Solution: Instead of calculating entire columns, reference a used range, a single cell, or add the @ operator to perform implicit intersection.
It is the most complex case and depending on your goal and the worksheet's structure you may need to adjust your formulas differently. Here, we are going to demonstrate a general approach on a very simple example.
Supposing you have a list of numbers in column B, and in column C you wish to return 10% of those numbers.
In traditional Excel 2019 and earlier, the following formula worked without a hitch (in the left part of the image below, it's in cells C2 through C7):
The cause of the error is Spill range is too big.
Why is the formula broken in new Excel? Because implicit intersection is no longer silently performed in the background. The term sounds mysterious, but the logic behind it is quite simple - reduce multiple values to a single value.
In pre-365 versions, that was the default behavior of Excel - since a cell could only contain one value, a formula was forced to return a single result. In our example, even though we supply a whole column (B:B), Excel processes just one value on the same row as the formula. So, the formula in C2 calculates 10% of the value in B2, the formula in C3 calculates 10% of the value in B3, and so on.
Solution 1. Refer to ranges rather than columns
For our sample dataset, the formula is:
The formula is entered just in one cell (C2). The result is a dynamic array that spills into multiple cells automatically:
Solution 2. Calculate a single cell and copy the formula down
It is the traditional approach - reference one cell on the same row and copy the formula down.
In our case, the below formula goes to C2, and then you drag it down through C10:
Unlike dynamic array formulas, this conventional formula style works in ranges and tables equally well:
Solution 3. Apply implicit intersection
This approach is a bit more complicated - use the implicit intersection operator (@ symbol) which was introduced in Excel 365 as part of the dynamic arrays update.
In your formula, insert the @ character where you want to reduce an array or a range to a single value, e.g. before a range/column/row reference, function or expression. Enter the formula in the topmost cell, and then drag it down across as many rows as needed.
For our sample dataset, the formula takes this shape:
Since the result is a single value, not a dynamic array, this formula can be used both in ranges and tables. In the latter case, however, a structured reference would be a more elegant and effective solution:
To some extent, this explains why the "@" character occasionally appears in formulas created in older versions - to retain compatibility. Because in pre-dynamic Excel, formulas cannot spill into multiple cells, the implicit intersection operator secures the same behavior when the formula is imported in dynamic array Excel.
My personal advice is to use this approach with care because it disables a great new feature of Excel that is very efficient and helpful in many scenarios.
You may also be interested in:
Bizzare! Suddenly this appears in an Excel 10 created workbook, used for years, and the sell is a simple "+" addition of a range name + the Sum() of a group of sequential cells in one column on another tab.
Some serious cursing (for me) was thrown at Msoft software engineers this morning.
The solution? Remove the "+_" symbol, and the added "@" and encase the range name and reference to cells on the other tab in a SUM() formula.
I HATE this Office 365. It is a MESS.
Thanks you for these well-written, in-depth, and to-the point articles (I've been looking at Excel-related articles).
It's rare to see write-ups of this quality in the wild on the web.
Much appreciated , and keep it up.
I have an old Excel spreadsheet that accesses multiple VBA functions. I haven't used it in years, but when pull up I can't save it as it goes into saving mode forever until I 3 finger it. I noticed that an "@" sign has been put in front of my functions and IF statements. I tried deleting among other things, but a couple time when opening the file I get an error that spill over from old versions of Excel. I'm unclear what actions to take to get the spreadsheet to save so that I can do some editing.
I have literally NO interest in this bug masquerading as a feature. If I want formulas copying over to adjacent cells, I will copy them.
HOW can I disable the SPILL "feature" and get back to productive use of Excel?
I agree with Ethel Aardvaark. This so called feature is more of a hinderance than anything else!
It is not possible to disable the Spill feature globally in Excel. However, you can easily prevent the spilling of a particular formula by using the implicit intersection operator.
I want to have sequence function 1 below the other. Eg SEQUENCE('Base Data'!B1,1,'Base Data'!B3,1)) in A1 and once that is done, I want this formula SEQUENCE('Base Data'!C1,1,'Base Data'!C3,1) in A13. Is there a dynamic way to define this?
Эта ошибка возникает, если диапазон для формулы с пролитым массивом не пуст.
При выборе формулы пунктирый обтекает предполагаемый диапазон отлива.
Вы можете выбрать всплывающий элемент Ошибка и выбрать параметр Выбор ячеек, чтобы сразу же перейти к тем ячейкам, которые не должны быть заметивы. После этого вы можете удалить или перечеркить доступ к запрещенной ячейке. После очистки этой функции формула массива будет пролита по замечу.
Это значение ошибки обычно связано с использованием функций СЛ RAND,RANDARRAYи RANDBETWEEN. Другие переменные функции, такие как СМЕЩЕНИЕ,ДВСИМВи СЕГОДНЯ, не возвращают разных значений при каждом прочете вычислений.
Существует три простых способа решения этой проблемы:
Ссылаясь только на значения подытогов, которые вас интересуют. Этот стиль формулы возвращает динамический массив, ноне работает с Excel таблицами.
=ВРОТ.В.В.В.(A2:A7;A:C;2;ЛОЖЬ)
Ссылаясь только на значение в той же строке, скопируйте формулу вниз. Этот традиционный стиль формул работает в таблицах,но не возвращаетдинамический массив.
Запрос на Excel неявное пересечение с помощью оператора @, а затем скопируйте формулу вниз. Этот стиль формулы работает в таблицах,но не возвращаетдинамический массив.
Формулы разлитого массива не поддерживаются в Excel таблицах. Попробуйте вынуть формулу из таблицы или преобразовать ее в диапазон (щелкните Конструктор таблиц > Инструменты > Преобразовать в диапазон).
Из-за пролитой формулы массива, который вы попытались ввести, Excel не потери памяти. Попробуйте ссылку на меньший массив или диапазон.
Пролитые формулы массива не могут пролиться в объединенные ячейки. Отодвигайте слияние ячеек с вопросом или перемещайте формулу в другой диапазон, который не пересекается с объединенными ячейками.
При выборе формулы пунктирый обтекает предполагаемый диапазон отлива.
Вы можете выбрать всплывающий элемент Ошибка и выбрать параметр Выбор ячеек, чтобы сразу же перейти к тем ячейкам, которые не должны быть заметивы. После очистки объединенных ячеек формула массива будет пролита по замечу.
Excel не распознает или не может выверять причину этой ошибки. Убедитесь, что формула содержит все аргументы, необходимые для сценария.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Объяснение
С появлением в Excel динамических массивов формулы, возвращающие несколько значений, «переносят» эти значения непосредственно на рабочий лист. Прямоугольник, в котором заключены значения, называется «диапазоном разлива». При изменении данных диапазон разлива будет расширяться или сокращаться по мере необходимости. Вы можете увидеть добавленные новые значения или исчезновение существующих.
Видео: Разлив и дальность разлива
Однако иногда ошибка может быть неожиданной и поэтому сбивать с толку. Прочтите ниже, как может быть вызвана эта ошибка и что вы можете сделать для ее устранения.
Поведение при разливе является естественным
Важно понимать, что поведение разлива является автоматическим и естественным. В динамическом Excel (в настоящее время только в Office 365 Excel) любая формула, даже простая формула без функций, может сказаться на результатах. Несмотря на то, что есть способы запретить формуле возвращать несколько результатов, отключение самого разлива с помощью глобального параметра невозможно.
Исправление №1 - очистить зону разлива
На приведенном ниже экране "x" блокирует диапазон разлива:
После удаления символа «x» функция UNIQUE обычно выдаёт результаты:
Эта формула не вызовет ошибки, скажем, в Excel 2016, поскольку неявное пересечение не позволит формуле возвращать несколько результатов. Однако в Dynamic Excel формула автоматически возвращает несколько результатов на рабочий лист, которые врезаются друг в друга, поскольку формула копируется из D5: D10.
Одно из решений - использовать символ @, чтобы включить неявное пересечение, например:
Примечание. Это частично объясняет, почему вы можете внезапно увидеть символ «@» в формулах, созданных в более ранних версиях Excel. Это сделано для сохранения совместимости. Поскольку формулы в более ранних версиях Excel не могут быть разделены на несколько ячеек, добавляется символ @, чтобы обеспечить такое же поведение при открытии формулы в динамическом Excel.
В динамическом Excel эта единственная формула передаст результаты в диапазон D5: D10, как показано на снимке экрана ниже:
Formula errors are useful, because they tell you clearly that something is wrong. This guide shows examples of each of the 9 formula errors you might run into, with information on how to investigate and correct the error.
The more formulas you write, the more errors you'll run into :)
Although frustrating, formula errors are useful, because they tell you clearly that something is wrong. This is much better than not knowing. The most disastrous Excel mistakes usually come from normal-looking formulas that quietly return incorrect results.
When you run into a formula error, don't panic. Stay calm and methodically investigate until you find the cause. Ask yourself, "What is this error telling me?" Experiment with trial and error. As you gain more experience, you'll be able to avoid many errors, and more quickly correct errors that do arise.
Fixing Errors
Here is a basic process for fixing errors below. Remember that formula errors often "cascade" through a worksheet, when one error triggers another. As you find and fix the core issue, things often come together quickly.
1. Find errors. You can use Go to Special > Formula as described below.
2. Trace the error back to its source. If this is difficult, try the trace error feature.
3. Figure out what's causing the error. If needed, break the formula into parts.
4. Fix the error at the source.
Finding all errors
You can find all errors at once with Go To Special. Use the keyboard shortcut Control + G, then click the "Special" button. Excel will display the dialog with many options seen below. To select only errors, choose Formulas + Errors, then click "OK":
Trapping Errors
Trapping errors is a way of "catching" errors to stop them appearing in the first place. This makes sense when you know certain errors are likely and you want to stop error messages from appearing. There are two basic approaches:
2. Trap the error with IFERROR or ISERROR. With this approach you are watching for an error, and providing an alternative when an error is detected. This page shows a VLOOKUP example.
3. Prevent calculation until required values are available. In this case, instead of watching for an error, you try to prevent the error from occurring by checking values first. This page shows several examples.
Excel's error codes
There are 9 error codes that you're likely to run into at some point as you work with Excel's formulas. This section shows examples of each formula error, with information and links on how to correct the error.
For example, in the worksheet below, the DIV error displayed in cell D4 because C4 is empty. Empty cells are evaluated as zero by Excel, and B4 can't be divided by zero:
If the value in E3 is changed to "coffee", "eggs", etc. VLOOKUP will work normally and retrieve the item cost.
Below, the MONTH function can't extract a month value from "apple", since "apple" is not a date:
When "apple" is deleted from D5, the formula will work normally, and return "Joe", "Sam", and "Mary".
If the formula is adjusted to filter on group "A", the formula will work normally:
Читайте также: