Пользователи просили нас ответить на два наиболее часто задаваемых вопроса: что такое ошибка Excel Spill и как от нее избавиться. Бывают случаи, когда объяснение этому простое, но бывают и ситуации, когда оно не столь ясно.
Поскольку в Excel была добавлена функция динамических массивов, формулы, которые возвращают несколько значений, теперь переносят эти значения прямо на рабочий лист, где они были первоначально рассчитаны.
Область, содержащая значения, называется диапазоном разлива и представляет собой прямоугольник. При обновлении статистики диапазон разливов будет скорректирован с учетом любого необходимого расширения или сокращения. Вы можете увидеть, как добавляются новые значения, или вы можете видеть, что уже существующие значения удаляются.
Что означает ошибка разлива в Excel?
Ошибка Spill возникает чаще всего, когда диапазон разлива на рабочем листе блокируется другим элементом рабочего листа. Это следует ожидать при случае.
Например, вы ввели формулу и ожидаете, что она прольется. Но на листе уже есть данные, которые его блокируют.
Источник: поддержка Майкрософт
Проблема может быть легко решена путем удаления любых данных, которые могут блокировать диапазон разлива. Если щелкнуть указание ошибки Spill, вы получите более подробную информацию о проблеме, вызвавшей проблему.
Важно понимать, что поведение разливов является как инстинктивным, так и естественным. Любая формула, даже не включающая никаких функций, способна выдать результаты в Dynamic Excel (Excel 365).
Несмотря на то, что существуют методы, предотвращающие получение формулой многочисленных результатов, сброс нельзя отключить с помощью глобальной опции.
Точно так же в Excel нет параметра, позволяющего пользователям отключать ошибки Spill. Вам нужно будет провести расследование и найти решение основной причины проблемы, чтобы исправить ошибку Spill.
Когда возникает ошибка SPill?
Пользователи сообщали о нескольких сценариях, в которых они получали ошибку Spill после использования формулы. Вот некоторые из них:
- Ошибка VLOOKUP Excel Spill — вертикальный поиск — это то, что означает аббревиатура VLOOKUP. Эта функция позволяет Excel искать определенное значение в столбце.
- Ошибка Excel Spill COUNTIF — функция Excel COUNTIF используется для подсчета количества ячеек внутри диапазона, удовлетворяющих определенным критериям. Более того, ячейки, содержащие текст, даты или целые числа, можно подсчитать с помощью функции СЧЁТЕСЛИ.
- Функция ЕСЛИ Excel Spill error — функция ЕСЛИ в Excel — один из наиболее часто используемых инструментов в программе. Это позволяет пользователям проводить логическое сравнение между числом и тем, что, по их мнению, оно будет. Таким образом, оператор IF может дать два разных результата.
- Ошибка разлива Excel СУММЕСЛИ. Функция СУММЕСЛИ представляет собой тип функции электронной таблицы, которая суммирует все значения в определенном диапазоне ячеек. Он основан на наличии или отсутствии одного условия.
- Ошибка разлива Excel ИНДЕКС и ПОИСКПОЗ. Результатом использования функции ИНДЕКС для диапазона или массива является значение, соответствующее предоставленному индексу. Тем временем функция ПОИСКПОЗ просматривает заданный диапазон ячеек в поисках определенного элемента, а затем возвращает относительное положение этого элемента.
Независимо от того, какая формула привела к ошибке Spill, вы можете использовать три наиболее полезных решения, которые мы перечислили ниже. Продолжайте читать!
Как исправить ошибку Spill в Excel?
1. Преобразование таблицы Excel
- Таблицы Excel не поддерживают формулы динамического массива, поэтому вам придется преобразовать таблицу в диапазон. Таким образом, начните с выбора опции Table Design на панели инструментов.
- Теперь нажмите на опцию «Преобразовать в диапазон». Это позволит вам использовать формулы динамического массива и избежать ошибки Excel Spill в таблице.
Excel предлагает нам возможность преобразовать таблицу в диапазон с сохранением формата таблицы. Диапазон — это любая последовательная группировка данных на листе.
2. Удалите пересекающиеся элементы
- Если ошибка Spill возникла из-за обнаруженного блокирующего элемента, просто щелкните блокирующую ячейку и нажмите backspaceклавишу на клавиатуре.
- Ячейки в диапазоне разлива должны быть пустыми, чтобы формула работала. Обязательно удалите все другие элементы, которые находятся в диапазоне разлива, чтобы исправить ошибку.
3. Ограничьте диапазон формулы
- Лист Excel имеет 16 384 столбца и 1 048 576 строк. Если вы используете формулу, которая расширяет диапазон разлива за пределы этих чисел, вы получите ошибку Excel Spill.
- Таким образом, обязательно помните об этих числах, прежде чем создавать формулы, в которых используются числа за ними.
Кроме того, некоторые функции являются изменчивыми, и вы не можете использовать их с функциями динамического массива, потому что результат будет неизвестен. Динамические формулы не принимают массивы неопределенной длины, что приводит к ошибке Spill. Одним из примеров таких формул является ПОСЛЕДОВАТЕЛЬНОСТЬ(СЛУЧМЕЖДУ(1,1000)).
Мы надеемся, что это руководство оказалось полезным для вас. Не стесняйтесь оставлять нам комментарии в разделе ниже и рассказывать нам, что вы думаете. Спасибо за чтение!
Резюме
Ошибка #SPILL возникает, когда диапазон разлива блокируется чем-то на листе. Решение обычно состоит в том, чтобы очистить зону разлива от любых препятствующих данных. См. Ниже дополнительную информацию и инструкции по устранению.
Объяснение
О разливе и # РАЗЛИВ! ошибка
С появлением в Excel динамических массивов формулы, возвращающие несколько значений, «переносят» эти значения непосредственно на рабочий лист. Прямоугольник, в котором заключены значения, называется «диапазоном разлива». При изменении данных диапазон разлива будет расширяться или сокращаться по мере необходимости. Вы можете увидеть добавленные новые значения или исчезновение существующих.
Видео: Разлив и дальность разлива
Ошибка #SPILL возникает, когда диапазон разлива блокируется чем-то на листе. Иногда этого ожидают. Например, вы ввели формулу, ожидая, что она разразится, но существующие данные на листе мешают. Решение состоит в том, чтобы просто очистить зону разлива от любых мешающих данных.
Однако иногда ошибка может быть неожиданной и поэтому сбивать с толку. Прочтите ниже, как может быть вызвана эта ошибка и что вы можете сделать для ее устранения.
Поведение при разливе является естественным
Важно понимать, что поведение разлива является автоматическим и естественным. В динамическом Excel (в настоящее время только в Office 365 Excel) любая формула, даже простая формула без функций, может сказаться на результатах. Несмотря на то, что есть способы запретить формуле возвращать несколько результатов, отключение самого разлива с помощью глобального параметра невозможно.
Точно так же в Excel нет возможности «отключить ошибки #SPILL». Чтобы исправить ошибку #SPILL, вам необходимо изучить и устранить основную причину проблемы.
Исправление №1 — очистить зону разлива
Это самый простой для решения случай. Формула должна содержать несколько значений, но вместо этого возвращает #SPILL! потому что что-то мешает. Чтобы устранить ошибку, выберите любую ячейку в диапазоне разлива, чтобы видеть ее границы. Затем либо переместите данные блокировки в новое место, либо удалите данные полностью. Обратите внимание, что ячейки в диапазоне разлива должны быть пустыми, поэтому обратите внимание на ячейки, содержащие невидимые символы, например пробелы.
На приведенном ниже экране «x» блокирует диапазон разлива:
После удаления символа «x» функция UNIQUE обычно выдаёт результаты:
Исправление # 2 — добавить символ @
До появления динамических массивов Excel молча применял поведение, называемое «неявным пересечением», чтобы гарантировать, что определенные формулы, которые могут возвращать несколько результатов, возвращают только один результат. В Excel с нединамическими массивами эти формулы возвращают нормальный результат без ошибок. Однако в некоторых случаях та же формула, введенная в Dynamic Excel, может вызвать ошибку #SPILL. Например, на экране ниже ячейка D5 содержит скопированную формулу:
=$B$5:$B$10+3
Эта формула не вызовет ошибки, скажем, в Excel 2016, поскольку неявное пересечение не позволит формуле возвращать несколько результатов. Однако в Dynamic Excel формула автоматически возвращает несколько результатов на рабочий лист, которые врезаются друг в друга, поскольку формула копируется из D5: D10.
Одно из решений — использовать символ @, чтобы включить неявное пересечение, например:
= @$B$5:$B$10+3
С этим изменением каждая формула снова возвращает один результат, и ошибка #SPILL исчезает.
Примечание. Это частично объясняет, почему вы можете внезапно увидеть символ «@» в формулах, созданных в более ранних версиях Excel. Это сделано для сохранения совместимости. Поскольку формулы в более ранних версиях Excel не могут быть разделены на несколько ячеек, добавляется символ @, чтобы обеспечить такое же поведение при открытии формулы в динамическом Excel.
Исправление # 3 — формула встроенного динамического массива
Другой (лучший) способ исправить ошибку #SPILL, показанную выше, — использовать формулу встроенного динамического массива в D5 следующим образом:
=B5:B10+3
В динамическом Excel эта единственная формула передаст результаты в диапазон D5: D10, как показано на снимке экрана ниже:
Обратите внимание, что нет необходимости использовать абсолютную ссылку.
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel for iPad Excel Web App Excel for iPhone Excel for Android tablets Excel for Android phones More…Less
#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:
=SEQUENCE(RANDBETWEEN(1,1000))
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.
For example, when placed in cell E2 as in the example below, the formula =VLOOKUP(A:A,A:C,2,FALSE) would previously only lookup the ID in cell A2 . However, in dynamic array Excel, the formula will cause a #SPILL! error because Excel will lookup the entire column, return 1,048,576 results, and hit the end of the Excel grid.
There are 3 simple ways to resolve this issue:
|
# |
Approach |
Formula |
|---|---|---|
|
1 |
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.
|
=VLOOKUP(A2:A7,A:C,2,FALSE) |
|
2 |
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.
|
=VLOOKUP(A2,A:C,2,FALSE) |
|
3 |
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.
|
=VLOOKUP(@A:A,A:C,2,FALSE) |
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?
See also
You can always ask an expert in the Excel Tech Community or get support in the Answers community.
FILTER function
RANDARRAY function
SEQUENCE function
SORT function
SORTBY function
UNIQUE function
Dynamic arrays and spilled array behavior
Implicit intersection operator: @
Need more help?
Want more options?
Explore subscription benefits, browse training courses, learn how to secure your device, and more.
Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.
Как мы все знаем, 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. Щелкните ячейку с надписью #SPILL! ошибка
Шаг 2. Щелкните восклицательный знак, как показано ниже.
Шаг 3: Первая строка сообщает нам, что вызывает ошибку. Например, в этом случае ошибка видна, поскольку диапазон разлива не пуст.
Исправления, которые необходимо выполнить, если диапазон разлива не пустой
Если вы видите, что диапазон разлива не пустой, выполните следующие исправления.
Исправление 1. Удалите данные, блокирующие диапазон разлива.
Если в ячейках диапазона разлива уже есть данные, при применении формулы вы увидите ошибку #SPILL.
Когда вы можете четко видеть данные, которые блокируют диапазон разлива
Рассмотрим приведенный ниже пример. Когда вы применяете формулу = D2: D5 к данным, выдается ошибка РАЗЛИВА, так как здесь I m находится в пределах диапазона разлива.
Чтобы избавиться от ошибки #SPILL, просто переместите данные или удалите данные из диапазона разлива.
Когда данные, блокирующие диапазон разлива, скрыты
В некоторых случаях данные, которые блокируют диапазон разлива, скрыты и не очень очевидны, как в случае 1. Рассмотрим пример ниже.
В таких случаях, чтобы найти ячейку, блокирующую диапазон разлива, выполните следующие действия:
Шаг 1. Щелкните ячейку с надписью #SPILL! ошибка
Шаг 2: Щелкните восклицательный знак, как показано ниже. Вы можете видеть, что ошибка связана с тем, что диапазон разлива не пустой.
Шаг 3: В раскрывающемся списке нажмите «Выбрать препятствующие ячейки».
Шаг 4. Ячейка, блокирующая диапазон разлива, выделяется, как показано ниже.
Теперь, когда вы знаете, какая ячейка блокируется, проверьте, что именно вызывает проблему.
Шаг 5: При внимательном изучении ячейки вы можете увидеть некоторые данные, скрытые внутри ячеек.
Как видно на изображении выше, есть некоторые данные. Поскольку шрифт имеет белый цвет, распознать засор непросто. Чтобы избавиться от ошибки, удалите данные из ячейки в диапазоне Spill.
Исправление 2: удалите форматирование произвольных чисел; ; ; нанесен на ячейку
Иногда при произвольном форматировании чисел; ; ; нанесен на ячейку, есть вероятность увидеть ошибку SPILL. В таких случаях,
Шаг 1. Щелкните ячейку с надписью #SPILL! ошибка
Шаг 2: Щелкните восклицательный знак, как показано ниже.
Шаг 3: В раскрывающемся списке нажмите «Выбрать препятствующие ячейки».
Шаг 4. Ячейка, блокирующая диапазон разлива, выделяется, как показано ниже.
Шаг 5: Щелкните правой кнопкой мыши блокирующую ячейку.
Шаг 6: выберите формат ячеек
Шаг 7. Откроется окно «Форматирование ячеек». Перейдите на вкладку Number
Шаг 8. На левой панели выберите Пользовательский.
Шаг 9: На правой боковой панели измените Тип с; ; ; генералу
Шаг 10: нажмите кнопку ОК.
Исправление, которое должно выполняться, когда диапазон разлива объединил ячейки
Если вы видите, что ошибка связана с тем, что диапазон разлива объединил ячейки, как показано ниже,
Шаг 1. В раскрывающемся списке нажмите «Выбрать препятствующие ячейки».
Шаг 2: блокирующая ячейка будет выделена
Шаг 3. На вкладке «Главная» нажмите «Объединить и центрировать».
Шаг 4. В раскрывающемся списке выберите «Разъединить ячейки».
Исправление, которое необходимо соблюдать, когда диапазон разлива в таблице
Формулы динамических массивов не поддерживаются в таблицах Excel. Если вы видите ошибку #SPILL в таблице Excel, как показано ниже, с сообщением Диапазон разлива в таблице,
Шаг 1: полностью выберите стол
Шаг 2. Щелкните вкладку «Дизайн таблицы» в верхней строке меню.
Шаг 3. Выберите «Преобразовать в диапазон».
Шаг 4: Вы увидите всплывающее диалоговое окно подтверждения, нажмите Да
Исправление, которое необходимо соблюдать, когда диапазон разлива выходит за пределы памяти
Когда вы пытаетесь определить причину ошибки #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 происходило нечто, называемое неявным пересечением, которое заставляло возвращать один результат, даже если формула могла возвращать несколько результатов. Рассмотрим пример: если формула = B: B * 5% применяется в Excel 2019 или более ранних версиях с неявным пересечением, результат будет следующим:
Однако, когда та же формула используется в Excel 365, вы видите следующую ошибку
Чтобы решить эту проблему, попробуйте следующие исправления
Исправление 1: примените неявное пересечение с помощью оператора @
Когда мы говорим = B: B, динамический массив будет ссылаться на весь столбец B. Вместо этого мы можем заставить Excel наложить неявное пересечение с помощью оператора @
Измените формулу на [email protected]: B * 5%
Поскольку добавлено неявное пересечение, формула будет применена к одной ячейке. Чтобы расширить формулу,
1. Просто нажмите на точку, как показано ниже.
2. При необходимости перетащите его на ячейки. К этим ячейкам будет применена та же формула.
Исправление 2: вместо ссылки на столбец обратитесь к диапазону
В формуле = B: B * 5% мы ссылаемся на столбец B. Вместо этого ссылаемся на конкретный диапазон = B2: B4 * 5%.
Это все
Надеемся, эта статья была информативной.
Пожалуйста, поставьте лайк и прокомментируйте, если вам удалось решить проблему с помощью вышеуказанных методов.
Спасибо за чтение.
Содержание
- Как исправить ошибку #ПЕРЕНОС! в Excel
- Как исправить ошибку #ЗНАЧ! в Excel
- Как исправить ошибку #ПУСТО! в Excel
- Как исправить ошибку #ИМЯ? в Excel
Как исправить ошибку #ПЕРЕНОС! в Excel
Прежде чем рассмотреть ошибку #ПЕРЕНОС! (#SPILL), рассмотрим, что такое перенос. В Excel это означает, что формула возвращает несколько значений (массив), и они автоматически переносятся в соседние ячейки.
Диапазон переноса и значения внутри будут изменяться при обновлении источника данных.
Ошибка #ПЕРЕНОС! возникает, когда формула возвращает несколько значений, но Excel не может вывести один или несколько результатов.
У ошибки может быть множество причин. Поиск решения будет зависеть от используемой версии Excel.
- Веб-версия: наведите мышь на зеленый треугольник в верхнем левом углу ячейки с ошибкой #ПЕРЕНОС!. Появится сообщение с описанием ошибки.
- Десктопная версия: щелкните по ячейке с ошибкой #ПЕРЕНОС!. Нажмите на треугольник, который появится слева от ячейки. Причина ошибки будет указана в верхней части меню справки.
Рассмотрим решения, которые подойдут для наиболее распространенных вариаций этой ошибки.
Диапазон для переноса содержит одну или более ячеек с значениями
Решение: очистить диапазон для переноса.
.gif)
Диапазон для переноса находится внутри таблицы
Решение 1: преобразовать таблицу в диапазон данных.
Для этого выполните следующие шаги:
- нажмите на любую ячейку в таблице,
- в меню в верхней части окна выберите «Конструктор»,
- выберите команду «Преобразовать в диапазон».
Обратите внимание, что расположение меню и списка команд могут отличаться в зависимости от версии.
Решение 2: переместить формулу за границы таблицы.
Диапазон для переноса содержит объединенные ячейки
Решение: разделить ячейки внутри диапазона.
.gif)
Как исправить ошибку #ЗНАЧ! в Excel
Ошибка #ЗНАЧ! (#VALUE) возникает в следующих случаях:
- что-то не так с ячейкой (ячейками), на которую ссылается формула,
- что-то не так с самой формулой.
Иногда найти источник проблемы не просто. Ниже — о самых распространенных случаях и как и их исправить.
Математическая формула ссылается на текст
Формулы с математическими операторами могут вычислять только числа. Если одна или несколько ячеек, на которые ссылается формула, содержит текст, формула будет возвращать ошибку.
Решение: использовать вместо формулы (уравнения, составленного пользователем) функцию (формулу, заранее заданную Excel). Функции по умолчанию игнорируют большую часть текстовых значений и производят расчеты лишь с числами.
Например, на примере ниже функция =СУММ(B2,B11) будет игнорировать текст в ячейке B11.
.png.700x416_q95.jpg)
Скриншот: Zapier.com
Стоит помнить, что эта функция не оповещает автоматически о пропусках ячеек, и ее результат может вводить в заблуждение.
Одна или более ячеек содержат пробелы
В этих случаях ячейка выглядит пустой.
Решение: найти и заменить пробелы. Вот как это сделать:
- выделите диапазон ячеек, к которому обращается формула;
- нажмите на иконку с биноклем, нажмите «Найти и выделить»;
- в окне «Найти и заменить» выберите «Заменить»;
- в поле «Найти» вставьте пробелы;
- поле «Заменить на» оставьте пустым;
- нажмите «Заменить все».
Как исправить ошибку #ПУСТО! в Excel
Ошибка #ПУСТО! (#REF) возникает, когда формулы ссылается на ячейку, которая уже не существует. Разберем наиболее распространенные причины возникновения ошибки и как их исправить.
Ячейка, на которую ссылается формула, удалена
Формула использует прямые ссылки на ячейки (каждая ячейка отделена запятой), но одна или несколько ячеек удалены. Это основная причина, почему Excel не рекомендует использовать прямые ссылки на ячейки.
Решение 1: Если ячейки удалены случайно, отмените это действие.
Решение 2: Обновите формулу, чтобы она ссылалась на диапазон ячеек. В этом случае Excel сможет произвести вычисления, даже если одна из ячеек была удалена.
Формула содержит относительные ссылки
Относительная ссылка означает, что используемые данные привязаны к ячейке, в которую вставлена формула. Например, если формулу =СУММ(B2:E2) скопировать из ячейки G2 в G3, Excel предположит, что должен суммировать все ячейки из колонок B-E в ряду 3.
Каждый раз, когда формула с относительными ссылками переносится в другую ячейку, ссылки автоматически меняются. И если установить связь невозможно, происходит ошибка.
Например, если формулу =СУММ(G2:G7) перенести в ячейку I4, Excel предположит, что пользователю требуется сложить шесть ячеек над клеткой I4. В данном случае это невозможно, так как доступны лишь три ячейки.
Решение: обновите формулу, чтобы включить в нее абсолютные значения. Это позволит созранить исходные ссылки, даже если формула будет перенесена. Для этого вставьте символ $ перед каждой буквой, обозначающей столбец, и номером ряда.
Например, формула =СУММ(G2:G7) с абсолютными ссылками будет выглядеть как: =СУММ($G$2:$G$7).
Как исправить ошибку #ИМЯ? в Excel
Ошибка #ИМЯ? (#NAME) возникает, если название формулы неверно написано. Рассмотрим основные решения проблемы.
Название формулы содержит опечатку
Решение: обновить название формулы. Лучший способ избегать опечаток — использовать встроенный редактор формул Excel. Когда пользователь начинает печатать название формулы, программа автоматически предложит список названий, содержащих те же буквы.
Неверное название формулы
Иногда пользователи вводят название несуществующей формулы или формулы, которая называется иначе (например, ПЛЮС вместо реальной функции СУММ). В этом случае формула вернет ошибку #ИМЯ?.
Решение: найти правильное имя формулы и обновить ее.
Вот как это сделать:
- выберите в меню раздел «Формулы»;
- нажмите на «Вставить функцию» в левой части панели инструментов, в открывшемся окне «Вставка функции» просмотрите недавно использовавшиеся функции или полный список всех доступных функций;
- выберите и вставьте нужную функцию;
- в редакторе формул обновите ячейки, к которым будет отсылаться формула (поля зависят от выбранной функции).
Эти советы применимы и к ошибкам в Google Таблицах (за исключением #ПЕРЕНОС!, которая часто отображается как #REF).
Источник.
Фото на обложке: Aihr.com
Подписывайтесь на наш Telegram-канал, чтобы быть в курсе последних новостей и событий!