Если формула содержит ошибку, то «Excel 2007» отобразит специальное сообщение об ошибке. При этом каждый тип ошибки обозначается своим сообщением, вызывается разными причинами и соответственно требует различных способов разрешения.
##### — Что обозначает и как исправить?
Эти символы сообщают, что столбец, содержащий числа, недостаточно широк для них, или же дата и время, введенные в ячейки данного столбца, содержат отрицательные числа.
В первом случае достаточно просто увеличить ширину столбца или изменить числовой формат данных (например, уменьшить число знаков после запятой).
Во втором же случае надо:
- проверить формулу, если вычисляется число дней между двумя датами;
- если формула не содержит ошибок, то необходимо изменить формат ячейки и перейти, например, с формата «Дата и время» на «Общий» или «Числовой» формат.
#ЗНАЧ! — Что обозначает и как исправить?
Эти сообщение об использовании текста вместо числа или логического значения (ИСТИНА или ЛОЖЬ). То есть Excel такой плейбой и не может преобразовать данный текст в ячейке в правильный тип данных.
Необходимо убедиться, что формула или функция ссылается на те ячейки, которые содержат действительные значения.
Например, если в ячейке A2 содержится число, а в ячейке A3 содержится текст, то в ячейке А1 с формулой =A2+A3 будет отображаться #ЗНАЧ!.
#ДЕЛ/0! — Что обозначает и как исправить?
Эти сообщение, что в ячейке происходит деление числа на 0 (ноль) или же используются ссылки на пустую ячейку.
- В окне открытого листа выделите ячейку с данной ошибкой и нажмите клавишу F2.
- Когда в ячейке отобразится сама формула или функция, а также станут выделенными все ячейки, связанные ссылками с данной формулой или ячейкой, внимательно проверьте значения в выделенных ячейках и при необходимости внесите коррективы в формулу или измените ссылки на пустые ячейки.
- Нажмите Enter или кнопку «Ввод» на строке формул.
[stextbox id=»warning»]Если в качестве операнда используется пустая ячейка, то он автоматически считается равным нулю.[/stextbox]
#ИМЯ? — Что обозначает и как исправить?
Эти символы сообщают, что в формуле используется несуществующее имя или неправильный оператор.
1 вариант
Если используется имя, которое не было определено, то необходимо сделать следующее:
- В окне открытого листа перейдите к вкладке «Формулы» и в группе «Определенные имена» щелкните по кнопке «Диспетчер имен».
- В окне «Диспетчер имен» просмотрите, присутствует ли данное имя в списке.
Если данное имя отсутствует, то необходимо добавить его согласно инструкции «Как присвоить имя ячейке или диапазону ячеек?».
2 вариант
Если существует ошибка в написании имени, то необходимо проверить его орфографию.
- В окне открытого листа нажмите клавишу F3.
- В окошке «Вставка имени» выберите в списке имя нужное имя и нажмите кнопку «ОК».
- Внесите исправления (при необходимости) в формуле, которая отобразится в соответствующей ячейке.
- Для закрепления нажмите клавишу Enter.
3 вариант
Если в формуле используется функция с ошибкой в написании.
Например, СУМ(A1:А10) вместо СУММ(A1:А10).
- В окне открытого листа выделите ячейку с ошибкой в написании функции.
- Раскройте меню кнопки «Источник ошибки» рядом с данной ячейкой.
- В списке команд выберите пункт «Изменить в строке формул».
- На строке формул в окошке имени отобразится правильно написание нужной формул, согласно которому и измените ошибочное написание.
- Закрепите результат щелчком по клавише Enter.
4 вариант
Если в формулу введен текст, который не заключен в двойные кавычки, то необходимо проверить все текстовые записи в формуле и заключить их в двойные кавычки. Иначе Excel будет пытаться распознать данный текст как имя диапазона ячеек, хотя это и не предполагалось.
Например, СУММ(A1 А10) вместо СУММ(A1:А10).
5 вариант
Если в ссылке на диапазон ячеек пропущено двоеточие, то для исправления необходимо в формуле во всех подобных ссылках проверить знак двоеточия и исправить по мере необходимости.
Например, СУММ(A1 А10) вместо СУММ(A1:А10).
6 вариант
Если в формулу включена ссылка на значения ячеек других листов или книг, при этом имя не заключено в ординарные кавычки, то необходимо это имя заключить в апострофы ( “ ).
#Н/Д — Что обозначает и как исправить?
Эти символы сообщают, что нужное значение не доступно для функции или формулы.
1 вариант
Если в формулу были введены недостающие данные, а также #Н/Д или НД(), то #Н/Д необходимо заменить новыми данными.
[stextbox id=»warning»]Обозначение #Н/Д вводится в ячейки, данные из которых еще не доступны.[/stextbox]
2 вариант
Если в функциях ГПР, ПРОСМОТР, ПОИСКПОЗ или ВПР указывается неверное значение аргумента «искомое_значение» (например, ссылка на диапазон ячеек, что недопустимо), то необходимо соответственно указать ссылку только на нужную ячейку.
3 вариант
Если не заданы необходимые аргументы стандартной функции листа, то нужно ввести все необходимые соответствующие аргументы функции.
4 вариант
Если в формуле используется недоступная в данный момент функция, то необходимо проверить, что книга, использующая функцию листа, открыта, а также то, что функция правильно работает.
5 вариант
Если для просмотра значений в несортированной таблице используются функции ВПР, ГПР или ПОИСКПОЗ, для которых по умолчанию сведения для просмотра таблиц должны располагаться в возрастающем порядке.
В функциях ВПР и ГПР содержится аргумент «интервальный _просмотр», позволяющий искать определенное значение и в несортированной таблице. Но при этом, чтобы отыскать определенное значение, аргумент «интервальный_просмотр» должен иметь значение ЛОЖЬ.
В функции ПОИСКПОЗ содержится аргумент «тип_сопоставления», позволяющий сортировать данные для поиска. Если же соответствующее значение отыскать невозможно, то рекомендуется задать аргумент «тип_сопоставления» равный 0.
6 вариант
Если в формуле массива используется аргумент, не соответствующий диапазону, указанному в формуле массива, то необходимо проверить диапазон ссылок формулы на соответствие числу строк и столбцов или ввести формулу массива в меньшее число ячеек.
7 вариант
Если не заданы один или несколько необходимых аргументов стандартной или созданной функции листа, необходимо проверить и задать все необходимые аргументы функций.
#ССЫЛКА! — Что обозначает и как исправить?
Эти символы сообщают о неверной ссылке на ячейку.
1 вариант
Если ячейка, на которую ссылается формула, была удалена или же в данную ячейку помещено значение скопированных ячеек, то необходимо изменить формулу с учетом новых ссылок.
2 вариант
Если используется функция OLE, связанная с незапущенной программой, то необходимо запустить требуемую программу.
[stextbox id=»info»]Интерфейс OLE (Object Linking and Embedding — связывание и внедрение объектов) поддерживается множеством различных программ и используется для помещения документа, созданного в одной программе, в другую программу. Например, можно вставить документ Word в книгу Excel и наоборот.[/stextbox]
3 вариант
Если используется ссылка на недоступный объект DDE (Dynamic Data Exchange – динамический обмен данными), например «system», то необходимо проверить, что используется правильный раздел DDE.
4 вариант
Если используется макрос, вызывающий макрофункцию, которая при определенных вариантах выдает значение #ССЫЛКА!. Необходимо проверить аргумент функции и удостовериться, что он ссылается на допустимые ячейки или диапазоны ячеек.
#ЧИСЛО! — Что обозначает и как исправить?
Это сообщение об использовании неправильных числовых значений в формуле или функции.
1 вариант
Если в функцию, использующую числовой аргумент, было вставлено неприемлемое значение, то необходимо проверить все аргументы функции и при необходимости исправить написание всех чисел и формат соответствующих ячеек.
2 вариант
Если в функции с итерацией (подбором параметров), например «ВСД» или «СТАВКА», невозможно найти результат, то необходимо попробовать иное начальное приближение или изменить число итераций.
3 вариант
Если в результате вычисления формулы получается число, которое слишком велико или, наоборот, слишком мало, чтобы оно могло быть отображено в Excel, то необходимо изменить формулу и добиться, чтобы результат находился в диапазоне от 1*10307 до 1*10307.
#ПУСТО! — Что обозначает и как исправить?
Эти сообщение об отсутствии общих ячеек, когда задано пере
сечение двух областей.
1 вариант
Если используется ошибочный оператор диапазона, то необходимо внести исправления, а именно:
- для обозначения ссылки на непрерывный диапазон ячеек используется двоеточие (:) в качестве разделителя между начальной и конечной ячейкой диапазона. Например,
СУММ(С1:С20). - для обозначения ссылки на два непересекающихся диапазона используется оператор объединения – точкой с запятой (;). Например,
СУММ(С1:С20;D1:D20).
2 вариант
Если указанные диапазоны не имеют общих ячеек, то необходимо изменить ссылки, добиваясь нужного пересечения.
Ошибка #ЗНАЧ! в Excel означает: «Формула вводится неправильно. Или что-то не так с ячейками, на которые указывают ссылки». Эта ошибка возникает в самых разных случаях, и найти ее точную причину может быть сложно. Сведения на этой странице включают распространенные проблемы и решения ошибки.
Используйте раскрывающийся список ниже или перейдите к одной из других областей:
-
Проблемы с вычитанием
-
Проблемы с пробелами и текстом
-
Другие решения
Исправление ошибок определенных функций
Ваша функция не отображается в этом списке? Попробуйте другие решения, перечисленные ниже.
Проблемы с вычитанием
Если вы раньше не работали в Excel, вероятно, вы неправильно вводите формулу вычитания. Это можно сделать двумя способами:
Вычтите одну ссылку на ячейку из другой
Введите два значения в двух отдельных ячейках. В третьей ячейке вычтите одну ссылку на ячейку из другой. В этом примере ячейка D2 содержит плановую сумму, а ячейка E2 — фактическую. F2 содержит формулу =D2-E2.
Или используйте функцию СУММ с положительными и отрицательными числами
Введите положительное значение в одной ячейке и отрицательное — в другой. В третьей ячейке используйте функцию СУММ, чтобы сложить две ячейки. В этом примере ячейка D6 содержит плановую сумму, а ячейка E6 — фактическую как негативное число. F6 содержит формулу =СУММ(D6;E6).
Если используется Windows, ошибка #ЗНАЧ! может возникнуть даже при вводе самой обычной формулы вычитания. Проблему можно решить следующим образом.
-
Для начала выполните быструю проверку. В новой книге введите 2 в ячейке A1. Введите 4 в ячейке B1. Затем введите формулу =B1-A1 в ячейке C1. Если возникнет ошибка #ЗНАЧ! перейдите к следующему шагу. Если сообщение об ошибке не появилось, попробуйте другие решения на этой странице.
-
В Windows откройте панель управления «Региональные стандарты».
-
Windows 10. Встроке поиска введите регион и выберите панель управления Региональные стандарты.
-
Windows 8. На начальном экране введите регион, щелкните Параметры и выберите пункт Региональные стандарты.
-
Windows 7. Нажмите кнопку Пуск, введите регион и выберите пункт Язык и региональные стандарты.
-
-
На вкладке Форматы нажмите кнопку Дополнительные параметры.
-
Найдите пункт Разделитель элементов списка. Если в поле разделителя элементов списка указан знак «минус», замените его на что-то другое. Например, разделителем нередко выступает запятая. Также часто используется точка с запятой. Однако для вашего конкретного региона может подходить другой разделитель элементов списка.
-
Нажмите кнопку ОК.
-
Откройте книгу. Если ячейка содержит ошибку #VALUE!, дважды щелкните ее для редактирования.
-
Если там, где для вычитания должны быть знаки «минус», стоят запятые, замените их на знаки «минус».
-
Нажмите клавишу ВВОД.
-
Повторите эти действия для других ячеек, в которых возникает ошибка.
Вычтите одну ссылку на ячейку из другой
Введите две даты в двух отдельных ячейках. В третьей ячейке вычтите одну ссылку на ячейку из другой. В этом примере ячейка D10 содержит дату начала, а ячейка E10 — дату окончания. F10 содержит формулу =E10-D10.
Или используйте функцию РАЗНДАТ
Введите две даты в двух отдельных ячейках. В третьей ячейке используйте функцию РАЗНДАТ, чтобы найти разницу дат. Дополнительные сведения о функции РАЗНДАТ см. в статье Вычисление разницы двух дат.
Растяните столбец по ширине. Если значение выравнивается по правому краю — это дата. Но если оно выравнивается по левому краю, это значит, что в ячейке на самом деле не дата. Это текст. И Excel не распознает текст как дату. Ниже приведены некоторые решения, которые помогут решить эту проблему.
Проверка наличия начальных пробелов
-
Дважды щелкните дату, которая используется в формуле вычитания.
-
Разместите курсор в начале и посмотрите, можно ли выбрать один или несколько пробелов. Вот как выглядит выбранный пробел в начале ячейки:
Если в ячейке обнаружена эта проблема, перейдите к следующему шагу. Если вы не видите один или несколько пробелов, перейдите к следующему разделу и проверьте параметры даты на компьютере.
-
Выделите столбец, содержащий дату, щелкнув его заголовок.
-
Выберите Данные > Текст по столбцам.
-
Дважды нажмите кнопку Далее.
-
На шаге 3 из 3 в мастере в разделе Формат данных столбца установите переключатель дата.
-
Выберите формат даты и нажмите кнопку Готово.
-
Повторите эти действия для других столбцов, чтобы убедиться, что они не содержат пробелы перед датами.
Проверка параметров даты на компьютере
Excel полагается на систему дат вашего компьютера. Если дата в ячейке введена в другой системе дат, Excel не распознает ее как настоящую дату.
Например, предположим, что компьютер отображает даты в формате мм.дд.гггг. Если ввести такую дату в ячейке, Excel распознает ее как дату, а вы сможете использовать ее в формуле вычитания. Однако если ввести дату в формате мм.дд.гг, Excel не распознает ее как дату. Вместо этого дата будет рассматриваться как текст.
Существует два решения этой проблемы: Вы можете изменить систему дат, которая используется на компьютере, чтобы она соответствовала системе дат, которая нужна в Excel. Или в Excel можно создать новый столбец и использовать функцию ДАТА, чтобы создать настоящую дату на основе даты в текстовом формате. Вот как это сделать, если система дат вашего компьютера — дд.мм.гггг, а в ячейке A1 записан текст 12/31/2017.
-
Создайте такую формулу: =ДАТА(ПРАВСИМВ(A1;4);ЛЕВСИМВ(A1;2);ПСТР(A1;4;2))
-
Результат будет 31.12.2017.
-
Чтобы использовать формат дд.мм.гг, нажмите клавиши CTRL+1 (или
+ 1 на Mac). -
Выберите другой языковой стандарт, в котором используется формат дд.мм.гг, например Немецкий (Германия). После применения формата результат будет 31.12.2017, причем это будет настоящая дата, а не ее текстовая запись.
+ 1 на Mac).Примечание: Формула выше написана с использованием функций ДАТА, ПРАВСИМВ, ПСТР и ЛЕВСИМВ. Обратите внимание, что формула записана с учетом того, что в текстовой дате используется два символа для дней, два символа для месяцев и четыре символа для года. Возможно, вам понадобится откорректировать формулу под свою запись даты.
Проблемы с пробелами и текстом
Часто ошибка #ЗНАЧ! возникает, потому что формула ссылается на другие ячейки, содержащие пробелы или (что еще сложнее) скрытые пробелы. Из-за этих пробелов ячейка может выглядеть пустой, хотя на самом деле таковой не является.
1. Выберите ячейки, на которые указывают ссылки
Найдите ячейки, на которые ссылается формула, и выделите их. Во многих случаях рекомендуется удалить пробелы для всего столбца, так как можно заменить несколько пробелов одновременно. В этом примере, если щелкнуть E, выделится весь столбец.
2. Найдите и замените
На вкладке Главная щелкните Найти и выделить > Заменить.
3. Удалите пробелы
В поле Найти введите один пробел. Затем в поле Заменить удалите все, что там может быть.
4. Замените одно или все вхождения
Если вы уверены, что следует удалить все пробелы в столбце, нажмите кнопку Заменить все. Если вы хотите просмотреть и удалить пробелы по отдельности, можно сначала нажать кнопку Найти далее, а затем — Заменить, когда вы будете уверены, что пробел не нужен. После этого ошибка #ЗНАЧ! должна исчезнуть. Если нет — перейдите к следующему шагу.
5. Включите фильтр
Иногда из-за скрытых символов (а не просто пробелов) ячейка может выглядеть пустой, хотя на самом деле таковой не является. Например, это может происходить из-за одинарных кавычек в ячейке. Чтобы убрать эти символы из столбца, включите фильтр, последовательно выбрав Главная > Сортировка и фильтр > Фильтр.
6. Установите фильтр
Щелкните стрелку фильтра 
и снимите флажок Выделить все. Затем установите флажок Пустые.
7. Установите все флажки без названия
Установите все флажки, напротив которых ничего не указано, как на этом рисунке.
8. Выделите пустые ячейки и удалите их
Если Excel вернет пустые ячейки, выделите их. Затем нажмите клавишу DELETE. Все скрытые символы в ячейках будут удалены.
9. Очистите фильтр
Щелкните стрелку фильтра и выберите команду Удалить фильтр из… для отображения всех ячеек.
10. Результат
Если причиной появления ошибки #ЗНАЧ! были пробелы, вместо ошибки отобразится результат формулы, как показано в нашем примере. Если нет — повторите эти действия для других ячеек, на которые ссылается формула. Или попробуйте другие решения на этой странице.
Примечание: В этом примере обратите внимание, что ячейка E4 содержит зеленый треугольник, а число выравнивается по левому краю. Это значит, что число имеет текстовый формат. Это может вызвать проблемы в дальнейшем. Если вы заметили эту проблему, рекомендуем преобразовать числа из текстового формата в числовой.
Ошибку #ЗНАЧ! могут вызвать текст и специальные знаки в ячейке. Но иногда сложно понять, в каких именно ячейках они присутствуют. Решение: используйте функцию ЕТЕКСТ для проверки ячеек. Обратите внимание, что функция ЕТЕКСТ не устраняет ошибку, она просто находит ячейки, которые могут ее вызывать.
Пример с ошибкой #ЗНАЧ!
Вот пример формулы с ошибкой #ЗНАЧ! . Ошибка, скорее всего, возникает из-за ячейки E2. Здесь есть специальный знак, который выглядит как небольшой прямоугольник «00». Или, как показано на следующем рисунке, можно использовать функцию ЕТЕКСТ в отдельном столбце для поиска текста.
Этот же пример с функцией ЕТЕКСТ
Здесь в столбец F добавлена функция ЕТЕКСТ. Все ячейки в порядке, кроме одной со значением ИСТИНА. Это значит, что ячейка E2 содержит текст. Чтобы решить эту проблему, можно просто удалить содержимое ячейки и еще раз ввести число 1865,00. Вы также можете использовать функцию ПЕЧСИМВ, чтобы убрать символы, или функцию ЗАМЕНИТЬ, чтобы заменить специальные знаки на другие значения.
Использовав функцию ПЕЧСИМВ или ЗАМЕНИТЬ, вы можете скопировать результат в буфер обмена, а затем выбрать Главная > Вставить > Специальная вставка > Значения. Кроме того, может потребоваться преобразовать числа из текстового формата в числовой.
Формулам с математическими операторами (такими как + и *) не всегда удается вычислить ячейки, содержащие текст или пробелы. В таком случае попробуйте использовать вместо них функцию. Функции часто пропускают текстовые значения и определяют все значения как числовые, избегая ошибки #ЗНАЧ! . Например, вместо =A2+B2+C2 введите =СУММ(A2:C2). Или вместо =A2*B2 введите =ПРОИЗВЕД(A2,B2).
Другие решения
Выберите ошибку
Сначала выделите ячейку с ошибкой #ЗНАЧ! .
Щелкните «Формулы» > «Вычислить формулу»
Щелкните Формулы > Вычислить формулу > Вычислить. Excel обработает каждую часть формулы по отдельности. В данном случае формула =E2+E3+E4+E5 выдает ошибку из-за скрытого пробела в ячейке E2. Пробела не видно, если смотреть на ячейку E2. Но его можно увидеть здесь. Он отображается как » «.
Иногда вам может быть нужно вместо ошибки #ЗНАЧ! выводить что-то свое, например собственный текст, ноль или пустую ячейку. В этом случае можно добавить в формулу функцию ЕСЛИОШИБКА. Она проверяет, есть ли ошибка, и если да, заменяет ее другим значением по вашему выбору. Если ошибки нет, вычисляется исходная формула. Функция ЕСЛИОШИБКА работает только в Excel 2007 и более поздних версиях. В предыдущих версиях можно использовать функцию ЕСЛИ(ЕОШИБКА()).
Предупреждение: Функция ЕСЛИОШИБКА скроет все ошибки, а не только ошибку #ЗНАЧ! . Ошибки не рекомендуется скрывать, так как они часто указывают на то, что какое-то значение нужно исправить, а не просто скрыть. Используйте эту функцию, только если вы абсолютно уверены, что формула работает правильно.
Ячейка с ошибкой #ЗНАЧ!
Вот пример формулы, в которой ошибка #ЗНАЧ! вызвана скрытым пробелом в ячейке E2.
Ошибка, скрытая функцией ЕСЛИОШИБКА
А вот эта же формула с добавленной функцией ЕСЛИОШИБКА. Ее можно прочитать как «Вычислить формулу, но если возникнет какая-либо ошибка, заменить ее двумя дефисами.» Помните, что также можно использовать «», чтобы ничего не отображать вместо двух дефисов. Или вы можете подставить свой текст, например: «Ошибка суммирования».
К сожалению, как вы видите, функция ЕСЛИОШИБКА не устраняет ошибку, а только скрывает ее. Так что используйте ее, если точно уверены, что ошибку лучше скрыть, чем исправить.
В какой-то момент подключения к данным могут стать недоступны. Чтобы исправить ошибку, восстановите подключение или, если это возможно, импортируйте данные. Если у вас нет доступа к подключению, попросите автора книги создать для вас новый файл. В идеале в нем будут только значения (без подключений). Для этого можно скопировать все ячейки и вставить только значения, щелкнув Главная > Вставить > Специальная вставка > Значения. При этом будут удалены все формулы и подключения, а ошибки #ЗНАЧ! исчезнут.
См. также
Полные сведения о формулах в Excel
Рекомендации, позволяющие избежать появления неработающих формул
Skip to content
В статье описано, как использовать функцию ЕСЛИОШИБКА в Excel для обнаружения ошибок и замены их пустой ячейкой, другим значением или определённым сообщением. Покажем примеры, как использовать функцию ЕСЛИОШИБКА с функциями визуального просмотра и сопоставления индексов, а также как она сравнивается с ЕСЛИ ОШИБКА и ЕСНД.
«Дайте мне точку опоры, и я переверну землю», — сказал однажды Архимед. «Дайте мне формулу, и я заставлю ее вернуть ошибку», — сказал бы пользователь Excel. Здесь мы не будем рассматривать, как получить ошибки в Excel. Мы узнаем, как предотвратить их, чтобы ваши таблицы были чистыми, а формулы — понятными и точными.
Итак, вот о чем мы поговорим:
Что означает функция Excel ЕСЛИОШИБКА
Функция ЕСЛИОШИБКА (IFERROR по-английски) предназначена для обнаружения и устранения ошибок в формулах и вычислениях. Это значит, что функция ЕСЛИОШИБКА должна выполнить определенные действия, если видит какую-либо ошибку. Более конкретно, она проверяет формулу и, если вычисление дает ошибку, то она возвращает какое-то другое значение, которое вы ей укажете. Если же всё хорошо, то просто возвращает результат формулы.
Синтаксис функции Excel ЕСЛИОШИБКА следующий:
ЕСЛИОШИБКА(значение; значение_если_ошибка)
Где:
- Значение (обязательно) — что проверять на наличие ошибок. Это может быть формула, выражение или ссылка на ячейку.
- Значение_если_ошибка (обязательно) — что возвращать при обнаружении ошибки. Это может быть пустая строка (получится пустая ячейка), текстовое сообщение, числовое значение, другая формула или вычисление.
Например, при делении двух столбцов чисел можно получить кучу разных ошибок, если в одном из столбцов есть пустые ячейки, нули или текст.
Рассмотрим простой пример:
Чтобы этого не произошло, используйте формулу ЕСЛИОШИБКА, чтобы перехватывать и обрабатывать их нужным вам образом.
Если ошибка, то пусто
Укажите пустую строку (“”) в аргументе значение_если_ошибка, чтобы вернуть пустую ячейку, если обнаружена ошибка:
=ЕСЛИОШИБКА(A4/B4; «»)
Вернемся к нашему примеру и используем ЕСЛИОШИБКА:
Как видите по сравнению с первым скриншотом, вместо стандартных сообщений мы видим просто пустые ячейки.
Если ошибка, то показать сообщение
Вы также можете отобразить собственное сообщение вместо стандартного обозначения ошибок Excel:
=ЕСЛИОШИБКА(A4/B4; «Ошибка в вычислениях»)
Перед вами – третий вариант нашей небольшой таблицы.
5 фактов, которые нужно знать о функции ЕСЛИОШИБКА в Excel
- ЕСЛИОШИБКА в Excel обрабатывает все типы ошибок, включая #ДЕЛ/0!, #Н/Д, #ИМЯ?, #NULL!, #ЧИСЛО!, #ССЫЛКА! и #ЗНАЧ!.
- В зависимости от содержимого аргумента значение_если_ошибка функция может заменить ошибки вашим текстовым сообщением, числом, датой или логическим значением, результатом другой формулы или пустой строкой (пустой ячейкой).
- Если аргумент значение является пустой ячейкой, он обрабатывается как пустая строка (»’), но не как ошибка.
- ЕСЛИОШИБКА появилась в Excel 2007 и доступна во всех последующих версиях Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021 и Excel 365.
- Чтобы перехватывать ошибки в Excel 2003 и более ранних версиях, используйте функцию ЕОШИБКА в сочетании с функцией ЕСЛИ, например как показано ниже:
=ЕСЛИ(ЕОШИБКА(A4/B4);»Ошибка в вычислениях»;A4/B4)
Далее вы увидите, как можно использовать ЕСЛИОШИБКА в Excel в сочетании с другими функциями для выполнения более сложных задач.
ЕСЛИОШИБКА с функцией ВПР
Часто встречающаяся задача в Excel – поиск нужного значения в таблице в соответствии с определёнными критериями. И не всегда этот поиск бывает успешным. Одним из наиболее распространенных применений функции ЕСЛИОШИБКА является сообщение пользователям, что искомое значение не найдено в базе данных. Для этого вы заключаете формулу ВПР в функцию ЕСЛИОШИБКА примерно следующим образом:
ЕСЛИОШИБКА(ВПР( … );»Не найдено»)
Если искомое значение отсутствует в таблице, которую вы просматриваете, обычная формула ВПР вернет ошибку #Н/Д:
Для лучшего понимания таблицы и улучшения ее внешнего вида, заключите функцию ВПР в ЕСЛИОШИБКА и покажите более понятное для пользователя сообщение:
=ЕСЛИОШИБКА(ВПР(D3; $A$3:$B$5; 2;ЛОЖЬ); «Не найдено»)
На скриншоте ниже показан пример ЕСЛИОШИБКА вместе с ВПР в Excel:
Если вы хотите перехватывать только #Н/Д, но не все подряд ошибки, используйте функцию ЕНД вместо ЕСЛИОШИБКА. Она просто возвращает ИСТИНА или ЛОЖЬ в зависимости от появления ошибки #Н/Д. Поэтому нам здесь еще понадобится функция ЕСЛИ, чтобы обработать эти логические значения:
=ЕСЛИ(ЕНД(ВПР(D3; $A$3:$B$5; 2;ЛОЖЬ)); «Не найдено»;ВПР(D3; $A$3:$B$5; 2;ЛОЖЬ))
Дополнительные примеры формул Excel ЕСЛИОШИБКА ВПР можно также найти в нашей статье Как убрать сообщение #Н/Д в ВПР?
Вложенные функции ЕСЛИОШИБКА для выполнения последовательных ВПР
В ситуациях, когда вам нужно выполнить несколько операций ВПР в зависимости от того, была ли предыдущая ВПР успешной или неудачной, вы можете вложить две или более функции ЕСЛИОШИБКА одну в другую.
Предположим, у вас есть несколько отчетов о продажах из региональных отделений вашей компании, и вы хотите получить сумму по определенному идентификатору заказа. С ячейкой В9 в качестве критерия поиска (номер заказа) и тремя небольшими таблицами поиска (таблица 1, 2 и 3), формула выглядит следующим образом:
=ЕСЛИОШИБКА(ВПР(B9;A3:B6;2;0);ЕСЛИОШИБКА(ВПР(B9;D3:E6;2;0);ЕСЛИОШИБКА(ВПР(B9;G3:H6;2;0);»Не найден»)))
Результат будет выглядеть примерно так, как на рисунке ниже:
То есть, если поиск завершился неудачей (то есть, ошибкой) первой таблице, начинаем искать во второй, и так далее. Если нигде ничего не нашли, получим сообщение «Не найден».
ЕСЛИОШИБКА в формулах массива
Как вы, наверное, знаете, формулы массива в Excel предназначены для выполнения нескольких вычислений внутри одной формулы. Если вы в аргументе значение функции ЕСЛИОШИБКА укажете формулу или выражение, которое возвращает массив, она также обработает и вернет массив значений для каждой ячейки в указанном диапазоне. Пример ниже поможет пояснить это.
Допустим, у вас есть Сумма в столбце B и Цена в столбце C, и вы хотите вычислить Количество. Это можно сделать с помощью следующей формулы массива, которая делит каждую ячейку в диапазоне B2:B4 на соответствующую ячейку в диапазоне C2:C4, а затем суммирует результаты:
=СУММ(($B$2:$B$4/$C$2:$C$4))
Формула работает нормально, пока в диапазоне делителей нет нулей или пустых ячеек. Если есть хотя бы одно значение 0 или пустая строка, то возвращается ошибка: #ДЕЛ/0! Из-за одной некорректной позиции мы не можем получить итоговый результат.
Чтобы исправить эту ситуацию, просто вложите деление внутрь формулы ЕСЛИОШИБКА:
=СУММ(ЕСЛИОШИБКА($B$2:$B$4/$C$2:$C$4;0))
Что делает эта формула? Делит значение в столбце B на значение в столбце C в каждой строке (3500/100, 2000/50 и 0/0) и возвращает массив результатов {35; 40; #ДЕЛ/0!}. Функция ЕСЛИОШИБКА перехватывает все ошибки #ДЕЛ/0! и заменяет их нулями. Затем функция СУММ суммирует значения в итоговом массиве {35; 40; 0} и выводит окончательный результат (35+40=75).
Примечание. Помните, что ввод формулы массива должен быть завершен нажатием комбинации Ctrl + Shift + Enter (если у вас не Office365 или Excel2021 – они понимают формулы массива без дополнительных телодвижений).
ЕСЛИОШИБКА или ЕСЛИ + ЕОШИБКА?
Теперь, когда вы знаете, как использовать функцию ЕСЛИОШИБКА в Excel, вы можете удивиться, почему некоторые люди все еще склоняются к использованию комбинации ЕСЛИ + ЕОШИБКА. Есть ли у этого старого метода преимущества по сравнению с ЕСЛИОШИБКА?
В старые недобрые времена Excel 2003 и более ранних версий, когда ЕСЛИОШИБКА не существовало, совместное использование ЕСЛИ и ЕОШИБКА было единственным возможным способом перехвата ошибок. Это просто немного более сложный способ достижения того же результата.
Например, чтобы отловить ошибки ВПР, вы можете использовать любую из приведенных ниже формул.
В Excel 2007 — Excel 2016:
ЕСЛИОШИБКА(ВПР( … ); «Не найдено»)
Во всех версиях Excel:
ЕСЛИ(ЕОШИБКА(ВПР(…)); «Не найдено»; ВПР(…))
Обратите внимание, что в формуле ЕСЛИ ЕОШИБКА ВПР вам нужно дважды выполнить ВПР. Чтобы лучше понять, расшифруем: если ВПР приводит к ошибке, вернуть «Не найдено», в противном случае вывести результат ВПР.
А вот простой пример формулы Excel ЕСЛИ ЕОШИБКА ВПР:
=ЕСЛИ(ЕОШИБКА(ВПР(D2; A2:B5;2;ЛОЖЬ)); «Не найдено»; ВПР(D2; A2:B5;2;ЛОЖЬ ))
ЕСЛИОШИБКА против ЕСНД
Представленная в Excel 2013, ЕСНД (IFNA в английской версии) — это еще одна функция для проверки формулы на наличие ошибок. Его синтаксис похож на синтаксис ЕСЛИОШИБКА:
ЕСНД(значение; значение_если_НД)
Чем ЕСНД отличается от ЕСЛИОШИБКА? Функция ЕСНД перехватывает только ошибки #Н/Д, тогда как ЕСЛИОШИБКА обрабатывает все типы ошибок.
В каких ситуациях вы можете использовать ЕСНД? Когда нецелесообразно скрывать все ошибки. Например, при работе с важными данными вы можете захотеть получать предупреждения о возможных ошибках в вашем наборе данных (случайном делении на ноль и т.п.), а стандартные сообщения об ошибках Excel с символом «#» могут быть яркими визуальными индикаторами проблем.
Давайте посмотрим, как можно создать формулу, отображающую сообщение «Не найдено» вместо ошибки «Н/Д», которая появляется, когда искомое значение отсутствует в наборе данных, но при этом вы будете видеть все другие ошибки Excel.
Предположим, вы хотите получить Количество из таблицы поиска в таблицу с результатами, как показано на рисунке ниже. Проще всего было бы использовать ЕСЛИОШИБКА с ВПР. Таблица приобрела бы красивый вид, но при этом за надписью «Не найдено» были бы скрыты не только ошибки поиска, но и все другие ошибки. И мы не заметили бы, что в исходной таблице поиска у нас есть ошибка деления на ноль, так как не заполнена цена персиков. Поэтому более разумно использовать ЕСНД, чтобы с ее помощью обработать только ошибки поиска:
=ЕСНД(ВПР(F3; $A$3:$D$6; 4;ЛОЖЬ); «Не найдено»)
Или подойдет комбинация ЕСЛИ ЕНД для старых версий Excel:
=ЕСЛИ(ЕНД(ВПР(F3; $A$3:$D$6; 4;ЛОЖЬ));»Не найдено»; ВПР(F3; $A$3:$D$6; 4;ЛОЖЬ))
Как видите, формула ЕСНД с ВПР возвращает «Не найдено» только для товара, которого нет в таблице поиска (Сливы). Для персиков она показывает #ДЕЛ/0! что указывает на то, что наша таблица поиска содержит ошибку деления на ноль.
Рекомендации по использованию ЕСЛИОШИБКА в Excel
Итак, вы уже знаете, что функция ЕСЛИОШИБКА — это самый простой способ отлавливать ошибки в Excel и маскировать их пустыми ячейками, нулевыми значениями или собственными сообщениями. Однако это не означает, что вы должны обернуть каждую формулу в функцию обработки ошибок.
Эти простые рекомендации могут помочь вам сохранить баланс.
- Не ловите ошибки без весомой на то причины.
- Оберните в ЕСЛИОШИБКА только ту часть формулы, где по вашему мнению могут возникнуть проблемы.
- Чтобы обрабатывать только определенные ошибки, используйте другую функцию обработки ошибок с меньшей областью действия:
- ЕСНД или ЕСЛИ ЕНД для обнаружения только ошибок #H/Д.
- ЕОШ для обнаружения всех ошибок, кроме #Н/Д.
Мы постарались рассказать, как можно использовать функцию ЕСЛИОШИБКА в Excel. Примеры перехвата и обработки ошибок могут быть полезны и для «чайников», и для более опытных пользователей.
Также рекомендуем:
Если Excel не может правильно оценить формулу или функцию рабочего листа; он отобразит значение ошибки – например, #ИМЯ?, #ЧИСЛО!, #ЗНАЧ!, #Н/Д, #ПУСТО!, #ССЫЛКА! – в ячейке, где находится формула. Разберем типы ошибок в Excel, их возможные причины, и как их устранить.
Ошибка #ИМЯ?
Ошибка #ИМЯ появляется, когда имя, которое используется в формуле, было удалено или не было ранее определено.
Причины возникновения ошибки #ИМЯ?:
- Если в формуле используется имя, которое было удалено или не определено.
Ошибки в Excel – Использование имени в формуле
Устранение ошибки: определите имя. Как это сделать описано в этой статье.
- Ошибка в написании имени функции:
Ошибки в Excel – Ошибка в написании функции ПОИСКПОЗ
Устранение ошибки: проверьте правильность написания функции.
- В ссылке на диапазон ячеек пропущен знак двоеточия (:).
Ошибки в Excel – Ошибка в написании диапазона ячеек
Устранение ошибки: исправьте формулу. В вышеприведенном примере это =СУММ(A1:A3).
- В формуле используется текст, не заключенный в двойные кавычки. Excel выдает ошибку, так как воспринимает такой текст как имя.
Ошибки в Excel – Ошибка в объединении текста с числом
Устранение ошибки: заключите текст формулы в двойные кавычки.
Ошибки в Excel – Правильное объединение текста
Ошибка #ЧИСЛО!
Ошибка #ЧИСЛО! в Excel выводится, если в формуле содержится некорректное число. Например:
- Используете отрицательное число, когда требуется положительное значение.
Ошибки в Excel – Ошибка в формуле, отрицательное значение аргумента в функции КОРЕНЬ
Устранение ошибки: проверьте корректность введенных аргументов в функции.
- Формула возвращает число, которое слишком велико или слишком мало, чтобы его можно было представить в Excel.
Ошибки в Excel – Ошибка в формуле из-за слишком большого значения
Устранение ошибки: откорректируйте формулу так, чтобы в результате получалось число в доступном диапазоне Excel.
Ошибка #ЗНАЧ!
Данная ошибка Excel возникает в том случае, когда в формуле введён аргумент недопустимого значения.
Причины ошибки #ЗНАЧ!:
- Формула содержит пробелы, символы или текст, но в ней должно быть число. Например:
Ошибки в Excel – Суммирование числовых и текстовых значений
Устранение ошибки: проверьте правильно ли заданы типы аргументов в формуле.
- В аргументе функции введен диапазон, а функция предполагается ввод одного значения.
Ошибки в Excel – В функции ВПР в качестве аргумента используется диапазон, вместо одного значения
Устранение ошибки: укажите в функции правильные аргументы.
- При использовании формулы массива нажимается клавиша Enter и Excel выводит ошибку, так как воспринимает ее как обычную формулу.
Устранение ошибки: для завершения ввода формулы используйте комбинацию клавиш Ctrl+Shift+Enter.
Ошибки в Excel – Использование формулы массива
Ошибка #ССЫЛКА
В случае если формула содержит ссылку на ячейку, которая не существует или удалена, то Excel выдает ошибку #ССЫЛКА.
Ошибки в Excel – Ошибка в формуле, из-за удаленного столбца А
Устранение ошибки: измените формулу.
Ошибка #ДЕЛ/0!
Данная ошибка Excel возникает при делении на ноль, то есть когда в качестве делителя используется ссылка на ячейку, которая содержит нулевое значение, или ссылка на пустую ячейку.
Ошибки в Excel – Ошибка #ДЕЛ/0!
Устранение ошибки: исправьте формулу.
Ошибка #Н/Д
Ошибка #Н/Д в Excel означает, что в формуле используется недоступное значение.
Причины ошибки #Н/Д:
- При использовании функции ВПР, ГПР, ПРОСМОТР, ПОИСКПОЗ используется неверный аргумент искомое_значение:
Ошибки в Excel – Искомого значения нет в просматриваемом массиве
Устранение ошибки: задайте правильный аргумент искомое значение.
- Ошибки в использовании функций ВПР или ГПР.
Устранение ошибки: см. раздел посвященный ошибкам функции ВПР
- Ошибки в работе с массивами: использование не соответствующих размеров диапазонов. Например, аргументы массива имеют меньший размер, чем результирующий массив:
Ошибки в Excel – Ошибки в формуле массива
Устранение ошибки: откорректируйте диапазон ссылок формулы с соответствием строк и столбцов или введите формулу массива в недостающие ячейки.
- В функции не заданы один или несколько обязательных аргументов.
Ошибки в Excel – Ошибки в формуле, нет обязательного аргумента
Устранение ошибки: введите все необходимые аргументы функции.
Ошибка #ПУСТО!
Ошибка #ПУСТО! в Excel возникает когда, в формуле используются непересекающиеся диапазоны.
Ошибки в Excel – Использование в формуле СУММ непересекающиеся диапазоны
Устранение ошибки: проверьте правильность написания формулы.
Ошибка ####
Причины возникновения ошибки
- Ширины столбца недостаточно, чтобы отобразить содержимое ячейки.
Ошибки в Excel – Увеличение ширины столбца для отображения значения в ячейке
Устранение ошибки: увеличение ширины столбца/столбцов.
- Ячейка содержит формулу, которая возвращает отрицательное значение при расчете даты или времени. Дата и время в Excel должны быть положительными значениями.
Ошибки в Excel – Разница дат и часов не должна быть отрицательной
Устранение ошибки: проверьте правильность написания формулы, число дней или часов было положительным числом.
|
В ячейке таблицы Microsoft Excel появилась запись #ЗНАЧ! Что это значит? Формула подсчёта данных в этой ячейке не работает. Какой выход из создавшейся ситуации?
Самая частая причина такой ошибки — несовпадение типов в операциях. Условно говоря, если у вас формула которая перемножает значения в двух ячейках, и хотя бы в одной из этих ячейках не число, а текст, то вы получите эту ошибку. Проверьте корректность всех данных. Кроме того, если вы наведете курсор на эту ячейку и немного подождете, то получите некоторую подсказку по ошибке. Кроме того, такая ошибка может быть из-за того, что результат выполнения не влезает в ячейку по размеру. автор вопроса выбрал этот ответ лучшим
Спаанч Бооб 6 лет назад Такой знак часто встречается в ячейках, когда в них прописаны формулы, и в том случае, если формула приписана не корректно или элементы уравнения имеют разные форматы, либо указаны с ошибкой, например текстовое значение в ячейке вместо цифрового, появляется такой символ.
Ксарфакс 6 лет назад Такая ошибка, как #ЗНАЧ, в программе Microsoft Excel является довольно распространённой. Если вы ввели формулу, сделали ссылки на ячейки, а после этого вместо значения появилась подобная ошибка, то причин может быть несколько. Обычно такая проблема бывает, когда в ячейках, на которые вы ссылаетесь в формуле, находятся данные разных типов. 1) Если вы выполняете математические действия, а в одной из ячеек текстовое значение. 2) Частный случай — когда вы в ячейку внесли дробное число «0.1» вместо «0,1», то появится ошибка #ЗНАЧ. Вообще, имеется возможность посмотреть, на каком этапе вычисления произошла ошибка и после этого, обратить внимание именно на ту ячейку, при работе с которой возникла эта проблема.
Иногда может потребоваться удалить лишние пробелы в ячейках с числами (например, если вы импортировали эти данные) или какие-то специальные символы. В случае с ошибкой при вычитании дат нужно привести их к одинаковому формату. Также, если в книге вы используете подключение к данным, но в данный момент не удаётся получить доступ, то тоже появится подобная ошибка.
Master key 111 6 лет назад Чаще всего запись #ЗНАЧ! в ячейке таблицы Microsoft Excel говорит об ошибке при вводе данных следует проверить совпадение типов в операциях, то есть все что вы ввели, скорее всего вы просто забыли поставить какой-то знак, или символ, а может ввели что-то лишнее, попробуйте внимательно найти ошибку и тогда запись #ЗНАЧ пропадет.
galvanna 6 лет назад Программа Excel значительно облегчает многие трудоёмкие процессы в работе, поэтому желательно умело пользоваться ей. Неопытного пользователя может поставить в затруднительное положение неожиданно появившаяся запись в ячейке таблицы, вроде этой #ЗНАЧ!. Не стоит паниковать — это Excel сообщает нам о допущенной нами же ошибке. Есть и другие сообщения, указывающие на ошибки пользователя, например #ДЕЛ/0. Это сообщение появляется тогда, когда пользователь пытается разделить на 0,то есть в качестве делителя он пытается задействовать пустую ячейку или ячейку, содержащую значение равное 0. Одним словом, подобными сообщениями Excel указывает на не верные действия с нашей стороны. Как выглядят сообщения Excel об ошибке и, что следует предпринять для их устранения, можно узнать из этой статьи,например.
Искатель приключений 6 лет назад Добрый день. В программе «Excel» ошибка «#ЗНАЧ!» может встречается, когда вы пытаетесь сложить два значения, которые находятся в разных форматах. Например, в одной ячейки стоит число «5», а в другом тест «5 00»,тогда и выйдет такая ошибка, чтобы её избежать нужно привести все значения в один формат. Отформатировать ячейку, поставив везде формат «число», и проверить нет ли чисел с пробелами, тогда ошибка не должна возникать.
moreljuba 6 лет назад Наиболее распрастранён тот случай, когда такая запись вывскакивает при отсутсвии места для значения — то есть не влезает просто-напросто результат в ячейку, её нужно раздвинуть и всё будет нормально. Если дело не в этом, то некоректно были введены вами данные, нужно проверить точность формулы. А при наведении на эту надпись подсказка об ошибке будет перед вами.
Татьяна1718685 9 лет назад Найдите описание используемых функций и проверьте условия возврата значения #ЗНАЧ!.В Microsoft Excel операндами могут быть значения, ссылки на ячейки, имена, метки и функции… A5 содержит число, а ячейка A6 содержит текст «Недоступно», формула =A5+A6 возвратит значение ошибки #ЗНАЧ…
Ksyusha26 8 лет назад Чаще всего такая запись выплывает когда вы не совсем корректно вводите данные. Возможно, что забыли знак какой-нибудь поставить, либо не то, что-то поставили. Также может всплыть такое значение, когда ячейка мала по размеру для результата. Для этого просто растяните ячейку
ворчунов 6 лет назад 
