Эксель ошибка перенос

Содержание

• Как исправить ошибку #ПЕРЕНОС! в Excel
• Как исправить ошибку #ЗНАЧ! в Excel
• Как исправить ошибку #ПУСТО! в Excel
• Как исправить ошибку #ИМЯ? в Excel

Как исправить ошибку #ПЕРЕНОС! в Excel

Прежде чем рассмотреть ошибку #ПЕРЕНОС! (#SPILL), рассмотрим, что такое перенос. В Excel это означает, что формула возвращает несколько значений (массив), и они автоматически переносятся в соседние ячейки.

Диапазон переноса и значения внутри будут изменяться при обновлении источника данных.

Ошибка #ПЕРЕНОС! возникает, когда формула возвращает несколько значений, но Excel не может вывести один или несколько результатов.

У ошибки может быть множество причин. Поиск решения будет зависеть от используемой версии Excel.

• Веб-версия: наведите мышь на зеленый треугольник в верхнем левом углу ячейки с ошибкой #ПЕРЕНОС!. Появится сообщение с описанием ошибки.
• Десктопная версия: щелкните по ячейке с ошибкой #ПЕРЕНОС!. Нажмите на треугольник, который появится слева от ячейки. Причина ошибки будет указана в верхней части меню справки.

Рассмотрим решения, которые подойдут для наиболее распространенных вариаций этой ошибки.

Диапазон для переноса содержит одну или более ячеек с значениями

Решение: очистить диапазон для переноса.

Диапазон для переноса находится внутри таблицы

Решение 1: преобразовать таблицу в диапазон данных.

Для этого выполните следующие шаги:

1. нажмите на любую ячейку в таблице,
2. в меню в верхней части окна выберите «Конструктор»,
3. выберите команду «Преобразовать в диапазон».

Обратите внимание, что расположение меню и списка команд могут отличаться в зависимости от версии.

Решение 2: переместить формулу за границы таблицы.

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

Решение: разделить ячейки внутри диапазона.

Как исправить ошибку #ЗНАЧ! в Excel

Ошибка #ЗНАЧ! (#VALUE) возникает в следующих случаях:

• что-то не так с ячейкой (ячейками), на которую ссылается формула,
• что-то не так с самой формулой.

Иногда найти источник проблемы не просто. Ниже — о самых распространенных случаях и как и их исправить.

Математическая формула ссылается на текст

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

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

Например, на примере ниже функция =СУММ(B2,B11) будет игнорировать текст в ячейке B11.

Скриншот: Zapier.com

Стоит помнить, что эта функция не оповещает автоматически о пропусках ячеек, и ее результат может вводить в заблуждение.

Одна или более ячеек содержат пробелы

В этих случаях ячейка выглядит пустой.

Решение: найти и заменить пробелы. Вот как это сделать:

1. выделите диапазон ячеек, к которому обращается формула;
2. нажмите на иконку с биноклем, нажмите «Найти и выделить»;
3. в окне «Найти и заменить» выберите «Заменить»;
4. в поле «Найти» вставьте пробелы;
5. поле «Заменить на» оставьте пустым;
6. нажмите «Заменить все».

Как исправить ошибку #ПУСТО! в 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. Когда пользователь начинает печатать название формулы, программа автоматически предложит список названий, содержащих те же буквы.

Неверное название формулы

Иногда пользователи вводят название несуществующей формулы или формулы, которая называется иначе (например, ПЛЮС вместо реальной функции СУММ). В этом случае формула вернет ошибку #ИМЯ?.

Решение: найти правильное имя формулы и обновить ее.

Вот как это сделать:

1. выберите в меню раздел «Формулы»;
2. нажмите на «Вставить функцию» в левой части панели инструментов, в открывшемся окне «Вставка функции» просмотрите недавно использовавшиеся функции или полный список всех доступных функций;
3. выберите и вставьте нужную функцию;
4. в редакторе формул обновите ячейки, к которым будет отсылаться формула (поля зависят от выбранной функции).

Эти советы применимы и к ошибкам в Google Таблицах (за исключением #ПЕРЕНОС!, которая часто отображается как #REF).

Источник.

Фото на обложке: Aihr.com

Резюме

Ошибка #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 не может правильно оценить формулу или функцию рабочего листа; он отобразит значение ошибки – например, #ИМЯ?, #ЧИСЛО!, #ЗНАЧ!, #Н/Д, #ПУСТО!, #ССЫЛКА! – в ячейке, где находится формула. Разберем типы ошибок в Excel, их возможные причины, и как их устранить.

Ошибка #ИМЯ?

Ошибка #ИМЯ появляется, когда имя, которое используется в формуле, было удалено или не было ранее определено.

Причины возникновения ошибки #ИМЯ?:

1. Если в формуле используется имя, которое было удалено или не определено.

Ошибки в Excel – Использование имени в формуле

Устранение ошибки: определите имя. Как это сделать описано в этой статье.

1. Ошибка в написании имени функции:

Ошибки в Excel – Ошибка в написании функции ПОИСКПОЗ

Устранение ошибки: проверьте правильность написания функции.

1. В ссылке на диапазон ячеек пропущен знак двоеточия (:).

Ошибки в Excel – Ошибка в написании диапазона ячеек

Устранение ошибки: исправьте формулу. В вышеприведенном примере это =СУММ(A1:A3).

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

Ошибки в Excel – Ошибка в объединении текста с числом

Устранение ошибки: заключите текст формулы в двойные кавычки.

Ошибки в Excel – Правильное объединение текста

Ошибка #ЧИСЛО!

Ошибка #ЧИСЛО! в Excel выводится, если в формуле содержится некорректное число. Например:

1. Используете отрицательное число, когда требуется положительное значение.

Ошибки в Excel – Ошибка в формуле, отрицательное значение аргумента в функции КОРЕНЬ

Устранение ошибки: проверьте корректность введенных аргументов в функции.

1. Формула возвращает число, которое слишком велико или слишком мало, чтобы его можно было представить в Excel.

Ошибки в Excel – Ошибка в формуле из-за слишком большого значения

Устранение ошибки: откорректируйте формулу так, чтобы в результате получалось число в доступном диапазоне Excel.

Ошибка #ЗНАЧ!

Данная ошибка Excel возникает в том случае, когда в формуле введён аргумент недопустимого значения.

Причины ошибки #ЗНАЧ!:

1. Формула содержит пробелы, символы или текст, но в ней должно быть число. Например:

Ошибки в Excel – Суммирование числовых и текстовых значений

Устранение ошибки: проверьте правильно ли заданы типы аргументов в формуле.

1. В аргументе функции введен диапазон, а функция предполагается ввод одного значения.

Ошибки в Excel – В функции ВПР в качестве аргумента используется диапазон, вместо одного значения

Устранение ошибки: укажите в функции правильные аргументы.

1. При использовании формулы массива нажимается клавиша Enter и Excel выводит ошибку, так как воспринимает ее как обычную формулу.

Устранение ошибки: для завершения ввода формулы используйте комбинацию клавиш Ctrl+Shift+Enter.

Ошибки в Excel – Использование формулы массива

Ошибка #ССЫЛКА

В случае если формула содержит ссылку на ячейку, которая не существует или удалена, то Excel выдает ошибку #ССЫЛКА.

Ошибки в Excel – Ошибка в формуле, из-за удаленного столбца А

Устранение ошибки: измените формулу.

Ошибка #ДЕЛ/0!

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

Ошибки в Excel – Ошибка #ДЕЛ/0!

Устранение ошибки: исправьте формулу.

Ошибка #Н/Д

Ошибка #Н/Д в Excel означает, что в формуле используется недоступное значение.

Причины ошибки #Н/Д:

1. При использовании функции ВПР, ГПР, ПРОСМОТР, ПОИСКПОЗ используется неверный аргумент искомое_значение:

Ошибки в Excel – Искомого значения нет в просматриваемом массиве

Устранение ошибки: задайте правильный аргумент искомое значение.

1. Ошибки в использовании функций ВПР или ГПР.

Устранение ошибки: см. раздел посвященный ошибкам функции ВПР

1. Ошибки в работе с массивами: использование не соответствующих размеров диапазонов. Например, аргументы массива имеют меньший размер, чем результирующий массив:

Ошибки в Excel – Ошибки в формуле массива

Устранение ошибки: откорректируйте диапазон ссылок формулы с соответствием строк и столбцов или введите формулу массива в недостающие ячейки.

1. В функции не заданы один или несколько обязательных аргументов.

Ошибки в Excel – Ошибки в формуле, нет обязательного аргумента

Устранение ошибки: введите все необходимые аргументы функции.

Ошибка #ПУСТО!

Ошибка #ПУСТО! в Excel возникает когда, в формуле используются непересекающиеся диапазоны.

Ошибки в Excel – Использование в формуле СУММ непересекающиеся диапазоны

Устранение ошибки: проверьте правильность написания формулы.

Ошибка ####

Причины возникновения ошибки

1. Ширины столбца недостаточно, чтобы отобразить содержимое ячейки.

Ошибки в Excel – Увеличение ширины столбца для отображения значения в ячейке

Устранение ошибки: увеличение ширины столбца/столбцов.

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

Ошибки в Excel – Разница дат и часов не должна быть отрицательной

Устранение ошибки: проверьте правильность написания формулы, число дней или часов было положительным числом.