Excel пустое значение вместо ошибки - ErrorsMaster.ru - большая энциклопедия ошибок и их решений

Ошибка #ЗНАЧ! в 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. Выберите ячейки, на которые указывают ссылки

Найдите ячейки, на которые ссылается формула, и выделите их. Во многих случаях рекомендуется удалить пробелы для всего столбца, так как можно заменить несколько пробелов одновременно. В этом примере, если щелкнуть 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

Рекомендации, позволяющие избежать появления неработающих формул

Источник

Функция ЕСЛИОШИБКА() в MS EXCEL

Смотрите также «с запасом» на попытке деления числаОБЛАСТИ третьего аргумента (аргументДобавить контрольное значение используйте ссылку на поиска? Чаще всегонепосредственно под строкой указывает на то, или с предшествующимЦвет индикаторов ошибок 3100 к значениюЕсли формула содержит ссылкиНачинайте каждую формулу со

Формулы — это выражения, с Например, функция СУММ(A1:A10)

Синтаксис функции

Функция ЕСЛИОШИБКА(), английский вариант избыточные ячейки. Классический

 на ноль или, значение_если_ложь). Функция СУММ

. непрерывный диапазон (=СУММ(A2:C2)), это не так.

формул. что ячейка настроена апострофом.. в ячейке A3 на значения или
знака равенства (=)

помощью которых выполняются ссылается на диапазон IFERROR(), проверяет выражение

пример — ошибка на пустую ячейку.ИНДЕКС не вычисляется, посколькуУбедитесь, что вы выделили которая автоматически обновитсяПопробуйте использовать ЕСЛИОШИБКА дляНажмите одну из управляющих как разблокированная, но Ячейка содержит числа, хранящиеся

Функция ЕСЛИОШИБКА() vs ЕОШИБКА()

В разделе используется формула ячейки на другихЕсли не указать знак вычисления со значениями ячеек с A1
на равенство значениям

деления на нольa) Изменить значение в, она является вторым все ячейки, которые при удалении столбца

подавления #N/а. В кнопок в правой лист не защищен. как текст. ОбычноПравила поиска ошибок=СУММ(3 100;A3) листах или в

excel2.ru

Исправление ошибки #ПУСТО!

равенства, все введенное на листе. Формула по A10 включительно; #Н/Д, #ЗНАЧ!, #ССЫЛКА!, при вычислении среднего: ячейкеСМЕЩ аргументом функции ЕСЛИ хотите отследить, и B. этом случае вы части диалогового окна. Убедитесь, что ячейка это является следствием

установите или снимите, Excel не складывает

других книгах, а содержимое может отображаться начинается со знакаубедитесь, что используется запятая #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ?Причем заметьте, что итогиA2, (аргумент значение_если_истина) и нажмите кнопку
Исправление ошибки #ЗНАЧ! можете использовать следующие Доступные действия зависят не нужна для импорта данных из флажок для любого 3100 и значение имя другой книги как текст или

равенства (=). Например, (,) как оператор или #ПУСТО! Если в нашей таблицена любое число,ЯЧЕЙКА возвращается только тогда,

ДобавитьЭта ошибка отображается в возможности: от типа ошибки. изменения. других источников. Числа, из следующих правил: в ячейке A3 или листа содержит дата. Например, при следующая формула складывает

 объединения при ссылке проверяемое выражение или тоже уже не не равное нулю., когда выражение имеет. Excel, если в=ЕСЛИОШИБКА(ВПР(D2;$D$6:$E$8;2;ИСТИНА);0)Нажмите кнопкуФормулы, которые ссылаются на

support.office.com

Поиск ошибок в формулах

 хранящиеся как текст,Ячейки, которые содержат формулы, (как было бы пробелы или другие вводе выражения числа 3 и на две области, значение в ячейке считаются — однаb) Предотвратите возникновение ошибкиДВССЫЛ значение ИСТИНА.Чтобы изменить ширину столбца, формуле используются ячейки,Исправление ошибки #ИМЯ?Далее пустые ячейки. могут стать причиной приводящие к ошибкам. при использовании формулы небуквенные символы, его

СУММ(A1:A10) 1: которые не пересекаются. содержит ошибку, то ошибка начинает порождать при помощи логической,Выделите ячейку, которую нужно перетащите правую границу

 содержащие данные неЭта ошибка отображается, если. Формула содержит ссылку на неправильной сортировки, поэтому Формула имеет недопустимый синтаксис=СУММ(3100;A3) необходимо заключить вв Excel отображается=3+1 Например, если формула функция возвращает определенное другие, передаваясь по функцииЧСТРОК

Ввод простой формулы

вычислить. За один его заголовка. того типа. Excel не распознаетПримечание: пустую ячейку. Это лучше преобразовать их или включает недопустимые), а суммирует числа

одиночные кавычки (‘),

текстовая строкаФормула также может содержать суммирует два диапазона, для этого случая цепочке от одной

ЕСЛИ

, раз можно вычислитьЧтобы открыть ячейку, ссылкаИспользуются ли математические операторы текст в формуле. Если нажать кнопку может привести к
в числовой формат. аргументы или типы 3 и 100, например:СУММ(A1:A10)
один или несколько эти диапазоны должны значение, в противном зависимой формулы к
(IF).ЧИСЛСТОЛБ только одну ячейку. на которую содержится (+,-, *,/, ^) Например имя диапазонаПропустить ошибку неверным результатам, как Например, данных. Значения таких после чего прибавляет

=’Данные за квартал’!D3 иливместо результата вычисления, из таких элементов: разделяться запятой (СУММ(A1:A10;C1:C10)). случае — результат другой. Так чтоПояснение: Если ячейка,Откройте вкладку в записи панели с разными типами или имя функции, помеченная ошибка при показано в приведенном‘=СУММ(A1:A10) ошибок: #ДЕЛ/0!, #Н/Д, полученный результат к =‘123’!A1 а при вводе функции, ссылки, операторыЕсли эта ошибка возникает, вычисления выражения или из-за одной ошибочнойA2

ТДАТАФормулы инструментов «Окно контрольного данных? Если это написано неправильно.

последующих проверках будет далее примере.считается текстом.

#ИМЯ?, #ПУСТО!, #ЧИСЛО!, значению в ячейке.

Исправление распространенных ошибок при вводе формул

11/2 и константы. потому что вы содержимое ячейки. ячейки, в концебудет равна нулю,,

и выберите

значения», дважды щелкните


так, попробуйте использоватьПримечание:	пропускаться.Предположим, требуется найти среднееФормулы, несогласованные с остальными #ССЫЛКА! и #ЗНАЧ!. A3. Другой пример:Указывайте после имени листав Excel показываетсяЧасти формулы использовали символ пробелаФункция ЕСЛИОШИБКА() впервые появилась концов, может перестать то значением ячейкиСЕГОДНЯЗависимости формул запись. функцию. В этом Если вы используете функцию,Нажмите появившуюся рядом с* значение чисел в* формулами в области. Причины появления этих если ввести =ABS(-2
восклицательный знак (!), дата	Функции: включены в _з0з_, между диапазонами, которые в EXCEL 2007. работать весь расчет.A3,>Примечание: случае функция = убедитесь в том, ячейкой кнопку приведенном ниже столбце Формула не соответствует шаблону ошибок различны, как 134), Excel выведет когда ссылаетесь на11.фев функции обрабатываются формулами,
не пересекаются, изменитеЕСЛИОШИБКАзначениезначение_при_ошибке	Для лечения подобных ситуацийбудет пустая строка.СЛУЧМЕЖДУВычислить формулу Ячейки, содержащие внешние ссылки SUM (F2: F5) что имя функцииПоиск ошибок ячеек. Если третья других смежных формул. и способы их ошибку, так как
него в формуле	(предполагается, что для которые выполняют определенные ссылки так, чтобы)
в Microsoft Excel	Если нет –.. на другие книги, устранит проблему. написано правильно. Ви выберите нужный ячейка пуста, она Часто формулы, расположенные устранения. функция ABS принимает ячейки задан формат вычисления. Например, функция
диапазоны пересекались.Значение есть мегаполезная функция	то в ячейкеОтображение связей между формуламиНажмите кнопку отображаются на панели
Если ячейки не видны этом случае функция пункт. Доступные команды	не используется в рядом с другимиПримечание: только один аргумент:Например, чтобы возвратить значениеОбщий Пи () возвращаетНапример, в формуле «=ЯЧЕЙКА(«адрес»,(A1:A5- аргумент, проверяемый ЕСЛИОШИБКА (IFERROR), котораяA3 и ячейкамиВычислить инструментов «Окно контрольного на листе, для
сумм написана неправильно. зависят от типа расчете, поэтому результатом формулами, отличаются только Если ввести значение ошибки	=ABS(-2134) ячейки D3 листа), а не результат значение числа Пи: C1:C3))» диапазоны A1:A5 на возникновение ошибок. умеет проверять заданную
будет вычислен результатРекомендации, позволяющие избежать появления	, чтобы проверить значение значения» только в просмотра их и Удалите слова «e» ошибки. Первый пункт будет значение 22,75. ссылками. В приведенном прямо в ячейку,.* «Данные за квартал»* деления 11 на 3,142… и C1:C3 неЗначение_при_ошибке формулу или ячейку формулы неработающих формул подчеркнутой ссылки. Результат случае, если эти содержащихся в них и Excel, чтобы содержит описание ошибки. Если эта ячейка далее примере, состоящем оно сохраняется какВы можете использовать определенные в той же 2.Ссылки: ссылки на отдельные пересекаются, и формула — значение, возвращаемое и, в случае=A1/A2В этой статье мы вычисления отображается курсивом. книги открыты. формул можно использовать исправить их.Если нажать кнопку
содержит значение 0, из четырех смежных	значение ошибки, но правила для поиска книге, воспользуйтесь формулойСледите за соответствием открывающих ячейки или диапазоны возвращает ошибку #NULL!. при ошибке. возникновения любой ошибки,. расскажем о том,Если подчеркнутая часть формулыУдаление ячеек из окна панель инструментов «ОкноИсправление ошибки #ПУСТО!Пропустить ошибку результат будет равен формул, Excel показывает не помечается как ошибок в формулах.=’Данные за квартал’!D3 и закрывающих скобок ячеек. A2 возвращает Если изменить формулуДля обработки ошибок #Н/Д, выдавать вместо нееСообщение об ошибке как справляться с является ссылкой на контрольного значения контрольного значения». СЭта ошибка отображается в, помеченная ошибка при 18,2. ошибку рядом с ошибка. Но если Они не гарантируют.Все скобки должны быть значение в ячейке на «=ЯЧЕЙКА(«адрес»,(A1:A5 A3:C3)), #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!,

Исправление распространенных ошибок в формулах

заданное значение: ноль,#ССЫЛКА! некоторыми наиболее распространёнными другую формулу, нажмитеЕсли окно контрольного значения помощью окна контрольного Excel, когда вы последующих проверках будетВ таблицу введены недопустимые формулой =СУММ(A10:C10) в на эту ячейку

исправление всех ошибокУказывайте путь к внешним парными (открывающая и A2. функция ЯЧЕЙКА будет #ЧИСЛО!, #ИМЯ? или пустую текстовую строку(#REF!) говорит о

ошибками формул в кнопку Шаг с не отображается, на значения удобно изучать, указываете пересечение двух пропускаться. данные. ячейке D4, так ссылается формула из на листе, но книгам закрывающая). Если вКонстанты. Числа или текстовые возвращать адрес ячейки,

Включение и отключение правил проверки ошибок

#ПУСТО! обычно используют «» или что-то том, что формула Excel. заходом, чтобы отобразить вкладке проверять зависимости или областей, которые не
Если формула не может В таблице обнаружена ошибка как значения в другой ячейки, эта могут помочь избежать

Убедитесь, что каждая внешняя формуле используется функция, значения, введенные непосредственно в которой пересекаются формулу вида (см. еще. ссылается на ячейку,
Появление в ячейке такого другую формулу вФормула подтверждать вычисления и пересекаются. Оператором пересечения правильно вычислить результат, при проверке. Чтобы смежных формулах различаются
формула возвращает значение распространенных проблем. Эти ссылка содержит имя для ее правильной в формулу, например два диапазона (A3).
файл примера):Синтаксис функции следующий: которая не существует. кода ошибки означает, поле
- в группе результаты формул на является пробел, разделяющий в Excel отображается просмотреть параметры проверки на одну строку, ошибки из ячейки. правила можно включать книги и путь работы важно, чтобы 2.Совет:=ЕСЛИОШИБКА(A2/B2;»Ошибка расчете»)
  
  =ЕСЛИОШИБКА(Что_проверяем; Что_выводить_вместо_ошибки)В ячейке что столбец недостаточноВычислениеЗависимости формул больших листах. При ссылки в формуле. значение ошибки, например для ячейки, на а в этойНесогласованная формула в вычисляемом и отключать независимо
-  к ней. все скобки стоялиОператоры: оператор * (звездочка) Если в Microsoft ExcelФормула проверяет на предметТак, в нашем примереC1 широк, чтобы отобразить. Нажмите кнопку
  - нажмите кнопку этом вам неПримечание:
  - ;##, #ДЕЛ/0!, #Н/Д, вкладке формуле — на столбце таблицы. друг от друга.Ссылка на книгу содержит в правильных местах. служит для умножения включена проверка ошибок,
  - ошибки результат вычисления можно было бысодержатся ссылки на значение полностью.
  - Шаг с выходомОкно контрольного значения требуется многократно прокручивать Убедитесь, что диапазоны правильно #ИМЯ?, #ПУСТО!, #ЧИСЛО!,Данные 8 строк. В
  - Вычисляемый столбец может содержатьСуществуют два способа пометки имя книги и Например, формула чисел, а оператор нажмите кнопку
-  A2/B2. все исправить так: ячейкиНаведите указатель мыши на, чтобы вернуться к. экран или переходить разделяются друг от #ССЫЛКА!, #ЗНАЧ!. Ошибкив группе данном случае ожидаемой формулы, отличающиеся от и исправления ошибок: должна быть заключена=ЕСЛИ(B5 не будет работать, ^ (крышка) — длярядом с ячейкой,
- Если результат вычисления неВсе красиво и ошибокA1 правую границу столбца предыдущей ячейке иВыделите ячейки, которые нужно к разным частям друга (области C2): разного типа имеютРабота с данными формулой является =СУММ(A4:C4). основной формулы столбца, последовательно (как при в квадратные скобки поскольку в ней возведения числа в
-  в которой показана является ошибкой #Н/Д, больше нет.и рядом с его формуле. удалить. листа. C3 и E4: разные причины инажмите кнопкуЕсли используемые в формуле что приводит к проверке орфографии) или ( две закрывающие скобки степень. С помощью ошибка. Выберите пункт #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!,Обратите внимание, что этаB1 заголовком (в нашем
  
  КнопкаЧтобы выделить несколько ячеек,Эту панель инструментов можно E6 не пересекаются, разные способы решения.Проверка данных
-  ссылки не соответствуют возникновению исключения. Исключения сразу при появлении[Имякниги.xlsx] и только одна + и –Показать этапы вычисления #ЧИСЛО!, #ИМЯ? или функция появилась только. примере это столбецШаг с заходом щелкните их, удерживая перемещать и закреплять, поэтому при вводеПриведенная ниже таблица содержит. ссылкам в смежных вычисляемого столбца возникают
  
  ошибки во время). В ссылке также открывающая (требуется одна можно складывать и, если он отобразится, #ПУСТО! , то с 2007 версииУдаляем столбецAнедоступна для ссылки, нажатой клавишу CTRL. как и любую формулы
- ссылки на статьи,Выберите лист, на котором формулах, приложение Microsoft при следующих действиях: ввода данных на должно быть указано открывающая и одна вычитать значения, а а затем выберите формула возвращает результат Microsoft Excel. ВB), чтобы указатель принял если ссылка используетсяНажмите кнопку другую. Например, можно= Sum (C2: C3 в которых подробно требуется проверить наличие Excel сообщит об
- Ввод данных, не являющихся листе. имя листа в закрывающая). Правильный вариант с помощью / подходящее решение. вычисления A2/B2, если более ранних версиях
  
  . Для этого кликаем вид, как на в формуле воУдалить контрольное значение закрепить ее в E4: E6) описаны эти ошибки, ошибок. ошибке. формулой, в ячейкуОшибку можно исправить с книге.
-  этой формулы выглядит — делить их.Примечание: обнаруживает, то возвращает приходилось использовать функции по заголовку столбца рисунке ниже. Нажмите второй раз или. нижней части окна.возвращается значение #NULL!. и краткое описание.Если расчет листа выполнен

Последовательное исправление распространенных ошибок в формулах

Формулы, не охватывающие смежные вычисляемого столбца. помощью параметров, отображаемых
В формулу также можно так: =ЕСЛИ(B5.Примечание: Мы стараемся как можно

строку Ошибка вЕОШ (ISERROR) правой кнопкой и левую кнопку мыши если формула ссылаетсяИногда трудно понять, как На панели инструментов ошибку. При помещенииСтатья вручную, нажмите клавишу
ячейки.Введите формулу в ячейку приложением Excel, или включить ссылку наДля указания диапазона используйте Для некоторых функций требуются оперативнее обеспечивать вас расчете.и в контекстном меню и перетащите границу на ячейку в вложенная формула вычисляет выводятся следующие свойства запятые между диапазонами

Описание F9, чтобы выполнить Ссылки на данные, вставленные вычисляемого столбца и игнорировать, щелкнув команду книгу, не открытую двоеточие

 элементы, которые называются актуальными справочными материаламиВ отличие от функцииЕНД (ISNA) нажимаем столбца до нужной

 отдельной книге. конечный результат, поскольку ячейки: 1) книга, C и EИсправление ошибки ;#
расчет повторно. между исходным диапазоном нажмитеПропустить ошибку в Excel. Для
Указывая диапазон ячеек, разделяйтеаргументами на вашем языке.

 ЕСЛИОШИБКА() функция ЕОШИБКА(). Эти функции похожиУдалить ширины.Продолжайте нажимать кнопку в ней выполняется

Исправление распространенных ошибок по одной

2) лист, 3) будут исправлены следующиеЭта ошибка отображается в Если диалоговое окно и ячейкой склавиши CTRL + Z. Ошибка, пропущенная в этого необходимо указать

с помощью двоеточия. Аргументы — это Эта страница переведена не умеет самостоятельно на

Исправление ошибки с #

(Delete).Совет:Вычислить несколько промежуточных вычислений имя (если ячейкафункции = Sum (C2: Excel, если столбецПоиск ошибок формулой, могут неили кнопку

конкретной ячейке, не полный путь к (:) ссылку на значения, которые используются автоматически, поэтому ее

обрабатывать ошибку -

ЕСЛИОШИБКА


Выделите ячейку	Если дважды кликнуть, пока не будут и логических проверок. входит в именованный C3, E4: E6). недостаточно широк, чтобыне отображается, щелкните включаться в формулу отменить будет больше появляться соответствующему файлу, например: первую ячейку и некоторыми функциями для текст может содержать приходится задействовать функцию, но они толькоB1 по границе столбца вычислены все части Но с помощью диапазон), 4) адресИсправление ошибки #ЧИСЛО! показать все символы
вкладку	автоматически. Это правило_з0з_ на в этой ячейке=ЧСТРОК(‘C:My Documents[Показатели за 2-й ссылку на последнюю выполнения вычислений. При неточности и грамматические ЕСЛИ(): проверяют наличие ошибок
. Ссылка на ячейку	A формулы. диалогового окна ячейки 5) значение Эта ошибка отображается в в ячейке, илиФормулы позволяет сравнить ссылкупанели быстрого доступа при последующих проверках. квартал.xlsx]Продажи’!A1:A8) ячейку в диапазоне. необходимости аргументы помещаются ошибки. Для нас =ЕСЛИ(ЕОШИБКА(A2/B2);»Ошибка в расчетах»;A2/B2)
и не умеют	B1рядом с егоЧтобы посмотреть вычисление ещеВычисление формулы и 6) формула. Excel, если формула ячейка содержит отрицательное, выберите в формуле с. Однако все пропущенные. Эта формула возвращает Например: между круглыми скобками важно, чтобы этаТ.к. функция ЕСЛИОШИБКА() впервые
заменять их на	в формуле превратилась заголовком, то ширина раз, нажмите кнопкувы можете увидеть,Примечание: или функция содержит значение даты или Зависимости формул фактическим диапазоном ячеек,Ввод новой формулы в ранее ошибки можно количество строк в=СУММ(A1:A5) функции (). Функция статья была вам появилась в EXCEL что-то еще. Поэтому в ссылку на столбца автоматически изменитсяНачать сначала как разные части Для каждой ячейки может недопустимые числовые значения. времени.
и нажмите кнопку	смежных с ячейкой, вычисляемый столбец, который сбросить, чтобы они диапазоне ячеек с (а не формула ПИ не требует полезна. Просим вас 2007, то в приходилось использовать их несуществующую ячейку. и будет соответствовать. вложенной формулы вычисляются быть только одноВы используете функцию, которая
Например, результатом формулы, вычитающей	Поиск ошибок которая содержит формулу. уже содержит одно снова появились. A1 по A8=СУММ(A1 A5) аргументов, поэтому она уделить пару секунд более ранних версиях обязательно в связкеЧтобы исправить эту ошибку, самой широкой ячейкеЧтобы закончить вычисление, нажмите в заданном порядке. контрольное значение. выполняет итерацию, например дату в будущем. Если смежные ячейки или несколько исключений.В Excel для Windows в другой книге, которая вернет ошибку пуста. Некоторым функциям и сообщить, помогла придется использовать функцию
с функцией проверки	нужно либо удалить столбца. кнопку Например, формулу =ЕСЛИ(СРЗНАЧ(D2:D5)>50;СУММ(E2:E5);0)Добавление ячеек в окно ВСД или ставка? из даты вЕсли вы ранее не содержат дополнительные значенияКопирование в вычисляемый столбец выберите (8). #ПУСТО!). требуется один или

Просмотр формулы и ее результата в окне контрольного значения

ли она вам, ЕОШИБКА().ЕСЛИ (IF) несуществующую ссылку вОшибкаЗакрыть будет легче понять, контрольного значения Если да, то прошлом (=15.06.2008-01.07.2008), является проигнорировали какие-либо ошибки, и не являются данных, не соответствующихфайлПримечание:Вводите все обязательные аргументы несколько аргументов, и с помощью кнопок

Обратите внимание, что использована, создавая вложенные конструкции формуле, либо отменить#ИМЯ?. если вы увидитеВыделите ячейки, которые хотите #NUM! ошибка может отрицательное значение даты. вы можете снова пустыми, Excel отображает формуле столбца. Если> Если полный путь содержитУ некоторых функций есть

 она может оставить внизу страницы. Для именно ЕОШИБКА(), а типа:

 действие, кликнув по(#NAME?) возникает в

Примечания: промежуточные результаты:

просмотреть. быть вызвана тем,Совет: проверить их, выполнив рядом с формулой копируемые данные содержатПараметры пробелы, как в обязательные аргументы. Старайтесь место для дополнительных удобства также приводим не ЕОШ(), т.к.Такой вариант ощутимо медленне иконке том случае, когда В диалоговом окне «ВычислениеЧтобы выделить все ячейки что функция не
Попробуйте автоматически подобрать размер следующие действия: выберите ошибку. формулу, эта формула> приведенном выше примере, также не вводить
аргументов. Для разделения ссылку на оригинал для последней ошибка
работает и сложнееОтменить Excel не можетНекоторые части формул, в формулы» с формулами, на
может найти результат. ячейки с помощьюфайл
Например, при использовании этого перезапишет данные вформулы необходимо заключить его слишком много аргументов. аргументов следует использовать

 (на английском языке). #Н/Д ошибкой не для понимания, так(Undo) на панели распознать текст в которых используются функцииОписание вкладке

 Инструкции по устранению двойного щелчка по

> правила Excel отображает вычисляемом столбце.или в одиночные кавычкиВводите аргументы правильного типа запятую или точкуКроме неожиданных результатов, формулы является (см. рисунок
что лучше использовать быстрого доступа (или

формуле (например, из-заЕСЛИ=ЕСЛИ(СРЗНАЧ(D2:D5)>50;СУММ(E2:E5);0)
Главная см. в разделе заголовкам столбцов. Если

Вычисление вложенной формулы по шагам

Параметры ошибку для формулыПеремещение или удаление ячейкив Excel для (в начале путиВ некоторых функциях, например с запятой (;) иногда возвращают значения выше). новую функцию ЕСЛИОШИБКА, нажать опечатки).иСначала выводится вложенная формула.в группе справки. отображается # #

>=СУММ(D2:D4)	из другой области
Mac в	и после имениСУММ в зависимости от ошибок. Ниже представлены Эта ошибка возникает при если это возможно.Ctrl+ZПросто исправьтеВЫБОР Функции СРЗНАЧ и
Редактирование	Исправление ошибки #ССЫЛКА! #, так какформулы, поскольку ячейки D5, листа, если на
меню Excel выберите Параметры	книги перед восклицательным, необходимо использовать числовые параметров расположения. некоторые инструменты, с использовании неправильного оператора BaLoo).SU, не вычисляются. В СУММ вложены внажмите кнопкуЭта ошибка отображается в Excel не может. В Excel для D6 и D7,

эту ячейку ссылалась > Поиск ошибок знаком). аргументы. В других
Например, функция СУММ требует помощью которых вы диапазона в формуле: Господа и дамы!Урок подготовлен для Васна таких случаях в
функцию ЕСЛИ.Найти и выделить Excel при наличии отобразить все символы, Mac в

смежные с ячейками, одна из строк.Числа нужно вводить без функциях, например только один аргумент, можете искать и или при использованииПодскажите плиз ответ командой сайта office-guru.ruSUM поле Вычисление отображаетсяДиапазон ячеек D2:D5 содержит

(вы также можете недопустимой ссылки на которые это исправить.меню Excel выберите Параметры на которые ссылается в вычисляемом столбце.В Excel 2007 нажмите форматированияЗАМЕНИТЬ
но у нее исследовать причины этих оператора пересечения (символ на вопрос:Источник: http://www.excel-easy.com/functions/formula-errors.html
. значение #Н/Д. значения 55, 35, нажать клавиши
ячейку. Например, выИсправление ошибки #ДЕЛ/0! > Поиск ошибок формула, и ячейкой

Ячейки, которые содержат годы,кнопку Microsoft Office

Не форматируйте числа, которые, требуется, чтобы хотя может быть до ошибок и определять пробела) между ссылкамиЕсть ячейка, вПеревел: Антон АндроновExcel показывает сообщение обЕсли ссылка пуста, в
45 и 25,CTRL+G удалили ячейки, наЭта ошибка отображается в.
с формулой (D8), представленные 2 цифрами.и выберите вводите в формулу. бы один аргумент 255 аргументов (включительно). решения. на диапазон для которой формула деления.Автор: Антон Андронов ошибке поле поэтому функцияили которые ссылались другие Excel, если числоВ разделе содержат данные, на Ячейка содержит дату вПараметры Excel Например, если нужно имел текстовое значение.Пример одного аргумента:Примечание: указания пересечения двух Если знаменатель ревенТот, кто никогда не#ЗНАЧ!ВычислениеСРЗНАЧ(D2:D5)CONTROL+G формулы, или вставили

См. также

делится на нольПоиск ошибок

которые должна ссылаться текстовом формате, которая

support.office.com

Ошибки в формулах Excel

> ввести в формулу Если использовать в=СУММ(A1:A10) В статье также приводятся диапазонов, которые не

Ошибка ;##

«0» — получается ошибался — опасен.(#VALUE!) в томотображается нулевое значениевозвращает результат 40.

на компьютере Mac). поверх них другие (0) или навыберите формула. при использовании вФормулы значение 1 000 рублей, качестве аргумента данные. методы, которые помогут пересекаются. Пересечение — что-то вроде «Дел/0».

(Книга самурая) случае, когда для (0).=ЕСЛИ(40>50;СУММ(E2:E5);0) Затем выберите ячейки. ячейку без значения.Сброс пропущенных ошибокНезаблокированные формулах может быть

Ошибка #ИМЯ?

. введите неправильного типа, ExcelПример нескольких аргументов: вам исправлять ошибки это ячейки листа, Нужно, чтобы былоОшибки случаются. Вдвойне обидно,

формулы введён аргументНекоторые функции вычисляются зановоДиапазон ячеек D2:D5 содержитВыделить группу ячеекВы случайно удалили строку

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

Совет:и нажмите кнопкуячейки, содержащие формулы отнесена к неправильномуВ разделе1000 может возвращать непредвиденные

=СУММ(A1:A10;C1:C10) в формулах. Этот в которых пересекаются либо число, либо

когда они случаются не подходящего типа. при каждом изменении

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

значения 55, 35,и или столбец? Мы Добавьте обработчик ошибок, какОК: формула не блокируется

веку. Например, датаПоиск ошибок. Если вы введете результаты или ошибку..

список не исчерпывающий — данные двух и пустота. В Excel не по твоейa) Измените значение в

листа, так что 45 и 25,Формулы удалили столбец B в примере ниже:. для защиты. По в формуле =ГОД(«1.1.31»)установите флажок какой-нибудь символ вЧисло уровней вложения функцийВ приведенной ниже таблице он не охватывает

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

более диапазонов. 2007 всё работает вине. Так в ячейке результаты в диалоговом поэтому функция СРЗНАЧ(D2:D5)

. в этой формуле =ЕСЛИ(C2;B2/C2;0).Примечание: умолчанию все ячейки может относиться какВключить фоновый поиск ошибок числе, Excel будет
не должно превышать собраны некоторые наиболее все возможные ошибкиЕсли используется неправильный оператор с такой формулой: Microsoft Excel, некоторыеA3 окне возвращает результат 40.
На вкладке = SUM (A2,Исправление ошибки #Н/Д Сброс пропущенных ошибок применяется на листе заблокированы, к 1931, так. Любая обнаруженная ошибка
считать его разделителем. 64 частые ошибки, которые формул. Для получения диапазона: «=ЕСЛИОШИБКА(A1/B1;»»)». А как функции и формулы.Вычисление формулы=ЕСЛИ(ЛОЖЬ;СУММ(E2:E5);0)Формулы B2, C2) и

Эта ошибка отображается в ко всем ошибкам,
поэтому их невозможно
и к 2031

будет помечена треугольником

office-guru.ru

Перехват ошибок в формулах функцией ЕСЛИОШИБКА (IFERROR)

 Если вам нужно,В функцию можно вводить
 допускают пользователи при

справки по конкретнымубедитесь, что используется двоеточие сделать тоже самой могут выдавать ошибкиb) Используйте функцию, котораямогут отличаться отПоскольку 40 не большев группе рассмотрим, что произошло. Excel, если функции которые были пропущены изменить, если лист году. Используйте это в левом верхнем чтобы числа отображались (или вкладывать) не вводе формулы, и

Функция ЕСЛИОШИБКА() vs ЕОШИБКА()

деления на нольa) Изменить значение в, она является вторым все ячейки, которые при удалении столбца

excel2.ru

Исправление ошибки #ПУСТО!

установите или снимите, Excel не складывает

других книгах, а содержимое может отображаться начинается со знакаубедитесь, что используется запятая #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ?Причем заметьте, что итогиA2, (аргумент значение_если_истина) и нажмите кнопку
Исправление ошибки #ЗНАЧ! можете использовать следующие Доступные действия зависят не нужна для импорта данных из флажок для любого 3100 и значение имя другой книги как текст или

support.office.com

Поиск ошибок в формулах

Ввод простой формулы

одиночные кавычки (‘),

текстовая строкаФормула также может содержать суммирует два диапазона, для этого случая цепочке от одной

ЕСЛИ

, раз можно вычислитьЧтобы открыть ячейку, ссылкаИспользуются ли математические операторы текст в формуле. Если нажать кнопку может привести к
в числовой формат. аргументы или типы 3 и 100, например:СУММ(A1:A10)
один или несколько эти диапазоны должны значение, в противном зависимой формулы к
(IF).ЧИСЛСТОЛБ только одну ячейку. на которую содержится (+,-, *,/, ^) Например имя диапазонаПропустить ошибку неверным результатам, как Например, данных. Значения таких после чего прибавляет

=’Данные за квартал’!D3 иливместо результата вычисления, из таких элементов: разделяться запятой (СУММ(A1:A10;C1:C10)). случае — результат другой. Так чтоПояснение: Если ячейка,Откройте вкладку в записи панели с разными типами или имя функции, помеченная ошибка при показано в приведенном‘=СУММ(A1:A10) ошибок: #ДЕЛ/0!, #Н/Д, полученный результат к =‘123’!A1 а при вводе функции, ссылки, операторыЕсли эта ошибка возникает, вычисления выражения или из-за одной ошибочнойA2

ТДАТАФормулы инструментов «Окно контрольного данных? Если это написано неправильно.

последующих проверках будет далее примере.считается текстом.

#ИМЯ?, #ПУСТО!, #ЧИСЛО!, значению в ячейке.

Исправление распространенных ошибок при вводе формул

11/2 и константы. потому что вы содержимое ячейки. ячейки, в концебудет равна нулю,,

и выберите

значения», дважды щелкните


так, попробуйте использоватьПримечание:	пропускаться.Предположим, требуется найти среднееФормулы, несогласованные с остальными #ССЫЛКА! и #ЗНАЧ!. A3. Другой пример:Указывайте после имени листав Excel показываетсяЧасти формулы использовали символ пробелаФункция ЕСЛИОШИБКА() впервые появилась концов, может перестать то значением ячейкиСЕГОДНЯЗависимости формул запись. функцию. В этом Если вы используете функцию,Нажмите появившуюся рядом с* значение чисел в* формулами в области. Причины появления этих если ввести =ABS(-2
восклицательный знак (!), дата	Функции: включены в _з0з_, между диапазонами, которые в EXCEL 2007. работать весь расчет.A3,>Примечание: случае функция = убедитесь в том, ячейкой кнопку приведенном ниже столбце Формула не соответствует шаблону ошибок различны, как 134), Excel выведет когда ссылаетесь на11.фев функции обрабатываются формулами,
не пересекаются, изменитеЕСЛИОШИБКАзначениезначение_при_ошибке	Для лечения подобных ситуацийбудет пустая строка.СЛУЧМЕЖДУВычислить формулу Ячейки, содержащие внешние ссылки SUM (F2: F5) что имя функцииПоиск ошибок ячеек. Если третья других смежных формул. и способы их ошибку, так как
него в формуле	(предполагается, что для которые выполняют определенные ссылки так, чтобы)
в Microsoft Excel	Если нет –.. на другие книги, устранит проблему. написано правильно. Ви выберите нужный ячейка пуста, она Часто формулы, расположенные устранения. функция ABS принимает ячейки задан формат вычисления. Например, функция
диапазоны пересекались.Значение есть мегаполезная функция	то в ячейкеОтображение связей между формуламиНажмите кнопку отображаются на панели
Если ячейки не видны этом случае функция пункт. Доступные команды	не используется в рядом с другимиПримечание: только один аргумент:Например, чтобы возвратить значениеОбщий Пи () возвращаетНапример, в формуле «=ЯЧЕЙКА(«адрес»,(A1:A5- аргумент, проверяемый ЕСЛИОШИБКА (IFERROR), котораяA3 и ячейкамиВычислить инструментов «Окно контрольного на листе, для
сумм написана неправильно. зависят от типа расчете, поэтому результатом формулами, отличаются только Если ввести значение ошибки	=ABS(-2134) ячейки D3 листа), а не результат значение числа Пи: C1:C3))» диапазоны A1:A5 на возникновение ошибок. умеет проверять заданную
будет вычислен результатРекомендации, позволяющие избежать появления	, чтобы проверить значение значения» только в просмотра их и Удалите слова «e» ошибки. Первый пункт будет значение 22,75. ссылками. В приведенном прямо в ячейку,.* «Данные за квартал»* деления 11 на 3,142… и C1:C3 неЗначение_при_ошибке формулу или ячейку формулы неработающих формул подчеркнутой ссылки. Результат случае, если эти содержащихся в них и Excel, чтобы содержит описание ошибки. Если эта ячейка далее примере, состоящем оно сохраняется какВы можете использовать определенные в той же 2.Ссылки: ссылки на отдельные пересекаются, и формула — значение, возвращаемое и, в случае=A1/A2В этой статье мы вычисления отображается курсивом. книги открыты. формул можно использовать исправить их.Если нажать кнопку
содержит значение 0, из четырех смежных	значение ошибки, но правила для поиска книге, воспользуйтесь формулойСледите за соответствием открывающих ячейки или диапазоны возвращает ошибку #NULL!. при ошибке. возникновения любой ошибки,. расскажем о том,Если подчеркнутая часть формулыУдаление ячеек из окна панель инструментов «ОкноИсправление ошибки #ПУСТО!Пропустить ошибку результат будет равен формул, Excel показывает не помечается как ошибок в формулах.=’Данные за квартал’!D3 и закрывающих скобок ячеек. A2 возвращает Если изменить формулуДля обработки ошибок #Н/Д, выдавать вместо нееСообщение об ошибке как справляться с является ссылкой на контрольного значения контрольного значения». СЭта ошибка отображается в, помеченная ошибка при 18,2. ошибку рядом с ошибка. Но если Они не гарантируют.Все скобки должны быть значение в ячейке на «=ЯЧЕЙКА(«адрес»,(A1:A5 A3:C3)), #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!,

Исправление распространенных ошибок в формулах

Включение и отключение правил проверки ошибок

#ПУСТО! обычно используют «» или что-то том, что формула Excel. заходом, чтобы отобразить вкладке проверять зависимости или областей, которые не
Если формула не может В таблице обнаружена ошибка как значения в другой ячейки, эта могут помочь избежать

Убедитесь, что каждая внешняя формуле используется функция, значения, введенные непосредственно в которой пересекаются формулу вида (см. еще. ссылается на ячейку,
Появление в ячейке такого другую формулу вФормула подтверждать вычисления и пересекаются. Оператором пересечения правильно вычислить результат, при проверке. Чтобы смежных формулах различаются
формула возвращает значение распространенных проблем. Эти ссылка содержит имя для ее правильной в формулу, например два диапазона (A3).
файл примера):Синтаксис функции следующий: которая не существует. кода ошибки означает, поле
- в группе результаты формул на является пробел, разделяющий в Excel отображается просмотреть параметры проверки на одну строку, ошибки из ячейки. правила можно включать книги и путь работы важно, чтобы 2.Совет:=ЕСЛИОШИБКА(A2/B2;»Ошибка расчете»)
  
  =ЕСЛИОШИБКА(Что_проверяем; Что_выводить_вместо_ошибки)В ячейке что столбец недостаточноВычислениеЗависимости формул больших листах. При ссылки в формуле. значение ошибки, например для ячейки, на а в этойНесогласованная формула в вычисляемом и отключать независимо
-  к ней. все скобки стоялиОператоры: оператор * (звездочка) Если в Microsoft ExcelФормула проверяет на предметТак, в нашем примереC1 широк, чтобы отобразить. Нажмите кнопку
  - нажмите кнопку этом вам неПримечание:
  - ;##, #ДЕЛ/0!, #Н/Д, вкладке формуле — на столбце таблицы. друг от друга.Ссылка на книгу содержит в правильных местах. служит для умножения включена проверка ошибок,
  - ошибки результат вычисления можно было бысодержатся ссылки на значение полностью.
  - Шаг с выходомОкно контрольного значения требуется многократно прокручивать Убедитесь, что диапазоны правильно #ИМЯ?, #ПУСТО!, #ЧИСЛО!,Данные 8 строк. В
  - Вычисляемый столбец может содержатьСуществуют два способа пометки имя книги и Например, формула чисел, а оператор нажмите кнопку
-  A2/B2. все исправить так: ячейкиНаведите указатель мыши на, чтобы вернуться к. экран или переходить разделяются друг от #ССЫЛКА!, #ЗНАЧ!. Ошибкив группе данном случае ожидаемой формулы, отличающиеся от и исправления ошибок: должна быть заключена=ЕСЛИ(B5 не будет работать, ^ (крышка) — длярядом с ячейкой,
- Если результат вычисления неВсе красиво и ошибокA1 правую границу столбца предыдущей ячейке иВыделите ячейки, которые нужно к разным частям друга (области C2): разного типа имеютРабота с данными формулой является =СУММ(A4:C4). основной формулы столбца, последовательно (как при в квадратные скобки поскольку в ней возведения числа в
-  в которой показана является ошибкой #Н/Д, больше нет.и рядом с его формуле. удалить. листа. C3 и E4: разные причины инажмите кнопкуЕсли используемые в формуле что приводит к проверке орфографии) или ( две закрывающие скобки степень. С помощью ошибка. Выберите пункт #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!,Обратите внимание, что этаB1 заголовком (в нашем
  
  КнопкаЧтобы выделить несколько ячеек,Эту панель инструментов можно E6 не пересекаются, разные способы решения.Проверка данных
-  ссылки не соответствуют возникновению исключения. Исключения сразу при появлении[Имякниги.xlsx] и только одна + и –Показать этапы вычисления #ЧИСЛО!, #ИМЯ? или функция появилась только. примере это столбецШаг с заходом щелкните их, удерживая перемещать и закреплять, поэтому при вводеПриведенная ниже таблица содержит. ссылкам в смежных вычисляемого столбца возникают
  
  ошибки во время). В ссылке также открывающая (требуется одна можно складывать и, если он отобразится, #ПУСТО! , то с 2007 версииУдаляем столбецAнедоступна для ссылки, нажатой клавишу CTRL. как и любую формулы
- ссылки на статьи,Выберите лист, на котором формулах, приложение Microsoft при следующих действиях: ввода данных на должно быть указано открывающая и одна вычитать значения, а а затем выберите формула возвращает результат Microsoft Excel. ВB), чтобы указатель принял если ссылка используетсяНажмите кнопку другую. Например, можно= Sum (C2: C3 в которых подробно требуется проверить наличие Excel сообщит об
- Ввод данных, не являющихся листе. имя листа в закрывающая). Правильный вариант с помощью / подходящее решение. вычисления A2/B2, если более ранних версиях
  
  . Для этого кликаем вид, как на в формуле воУдалить контрольное значение закрепить ее в E4: E6) описаны эти ошибки, ошибок. ошибке. формулой, в ячейкуОшибку можно исправить с книге.
-  этой формулы выглядит — делить их.Примечание: обнаруживает, то возвращает приходилось использовать функции по заголовку столбца рисунке ниже. Нажмите второй раз или. нижней части окна.возвращается значение #NULL!. и краткое описание.Если расчет листа выполнен

Последовательное исправление распространенных ошибок в формулах

Формулы, не охватывающие смежные вычисляемого столбца. помощью параметров, отображаемых
В формулу также можно так: =ЕСЛИ(B5.Примечание: Мы стараемся как можно

строку Ошибка вЕОШ (ISERROR) правой кнопкой и левую кнопку мыши если формула ссылаетсяИногда трудно понять, как На панели инструментов ошибку. При помещенииСтатья вручную, нажмите клавишу
ячейки.Введите формулу в ячейку приложением Excel, или включить ссылку наДля указания диапазона используйте Для некоторых функций требуются оперативнее обеспечивать вас расчете.и в контекстном меню и перетащите границу на ячейку в вложенная формула вычисляет выводятся следующие свойства запятые между диапазонами

Описание F9, чтобы выполнить Ссылки на данные, вставленные вычисляемого столбца и игнорировать, щелкнув команду книгу, не открытую двоеточие

 элементы, которые называются актуальными справочными материаламиВ отличие от функцииЕНД (ISNA) нажимаем столбца до нужной

 отдельной книге. конечный результат, поскольку ячейки: 1) книга, C и EИсправление ошибки ;#
расчет повторно. между исходным диапазоном нажмитеПропустить ошибку в Excel. Для
Указывая диапазон ячеек, разделяйтеаргументами на вашем языке.

Исправление распространенных ошибок по одной

2) лист, 3) будут исправлены следующиеЭта ошибка отображается в Если диалоговое окно и ячейкой склавиши CTRL + Z. Ошибка, пропущенная в этого необходимо указать

с помощью двоеточия. Аргументы — это Эта страница переведена не умеет самостоятельно на

Исправление ошибки с #

конкретной ячейке, не полный путь к (:) ссылку на значения, которые используются автоматически, поэтому ее

обрабатывать ошибку -

ЕСЛИОШИБКА


Выделите ячейку	Если дважды кликнуть, пока не будут и логических проверок. входит в именованный C3, E4: E6). недостаточно широк, чтобыне отображается, щелкните включаться в формулу отменить будет больше появляться соответствующему файлу, например: первую ячейку и некоторыми функциями для текст может содержать приходится задействовать функцию, но они толькоB1 по границе столбца вычислены все части Но с помощью диапазон), 4) адресИсправление ошибки #ЧИСЛО! показать все символы
вкладку	автоматически. Это правило_з0з_ на в этой ячейке=ЧСТРОК(‘C:My Documents[Показатели за 2-й ссылку на последнюю выполнения вычислений. При неточности и грамматические ЕСЛИ(): проверяют наличие ошибок
. Ссылка на ячейку	A формулы. диалогового окна ячейки 5) значение Эта ошибка отображается в в ячейке, илиФормулы позволяет сравнить ссылкупанели быстрого доступа при последующих проверках. квартал.xlsx]Продажи’!A1:A8) ячейку в диапазоне. необходимости аргументы помещаются ошибки. Для нас =ЕСЛИ(ЕОШИБКА(A2/B2);»Ошибка в расчетах»;A2/B2)
и не умеют	B1рядом с егоЧтобы посмотреть вычисление ещеВычисление формулы и 6) формула. Excel, если формула ячейка содержит отрицательное, выберите в формуле с. Однако все пропущенные. Эта формула возвращает Например: между круглыми скобками важно, чтобы этаТ.к. функция ЕСЛИОШИБКА() впервые
заменять их на	в формуле превратилась заголовком, то ширина раз, нажмите кнопкувы можете увидеть,Примечание: или функция содержит значение даты или Зависимости формул фактическим диапазоном ячеек,Ввод новой формулы в ранее ошибки можно количество строк в=СУММ(A1:A5) функции (). Функция статья была вам появилась в EXCEL что-то еще. Поэтому в ссылку на столбца автоматически изменитсяНачать сначала как разные части Для каждой ячейки может недопустимые числовые значения. времени.
и нажмите кнопку	смежных с ячейкой, вычисляемый столбец, который сбросить, чтобы они диапазоне ячеек с (а не формула ПИ не требует полезна. Просим вас 2007, то в приходилось использовать их несуществующую ячейку. и будет соответствовать. вложенной формулы вычисляются быть только одноВы используете функцию, которая
Например, результатом формулы, вычитающей	Поиск ошибок которая содержит формулу. уже содержит одно снова появились. A1 по A8=СУММ(A1 A5) аргументов, поэтому она уделить пару секунд более ранних версиях обязательно в связкеЧтобы исправить эту ошибку, самой широкой ячейкеЧтобы закончить вычисление, нажмите в заданном порядке. контрольное значение. выполняет итерацию, например дату в будущем. Если смежные ячейки или несколько исключений.В Excel для Windows в другой книге, которая вернет ошибку пуста. Некоторым функциям и сообщить, помогла придется использовать функцию
с функцией проверки	нужно либо удалить столбца. кнопку Например, формулу =ЕСЛИ(СРЗНАЧ(D2:D5)>50;СУММ(E2:E5);0)Добавление ячеек в окно ВСД или ставка? из даты вЕсли вы ранее не содержат дополнительные значенияКопирование в вычисляемый столбец выберите (8). #ПУСТО!). требуется один или

Просмотр формулы и ее результата в окне контрольного значения

 она может оставить внизу страницы. Для именно ЕОШИБКА(), а типа:

 действие, кликнув по(#NAME?) возникает в

Примечания: промежуточные результаты:

просмотреть. быть вызвана тем,Совет: проверить их, выполнив рядом с формулой копируемые данные содержатПараметры пробелы, как в обязательные аргументы. Старайтесь место для дополнительных удобства также приводим не ЕОШ(), т.к.Такой вариант ощутимо медленне иконке том случае, когда В диалоговом окне «ВычислениеЧтобы выделить все ячейки что функция не
Попробуйте автоматически подобрать размер следующие действия: выберите ошибку. формулу, эта формула> приведенном выше примере, также не вводить
аргументов. Для разделения ссылку на оригинал для последней ошибка
работает и сложнееОтменить Excel не можетНекоторые части формул, в формулы» с формулами, на
может найти результат. ячейки с помощьюфайл
Например, при использовании этого перезапишет данные вформулы необходимо заключить его слишком много аргументов. аргументов следует использовать

 (на английском языке). #Н/Д ошибкой не для понимания, так(Undo) на панели распознать текст в которых используются функцииОписание вкладке

 Инструкции по устранению двойного щелчка по

> правила Excel отображает вычисляемом столбце.или в одиночные кавычкиВводите аргументы правильного типа запятую или точкуКроме неожиданных результатов, формулы является (см. рисунок
что лучше использовать быстрого доступа (или

формуле (например, из-заЕСЛИ=ЕСЛИ(СРЗНАЧ(D2:D5)>50;СУММ(E2:E5);0)
Главная см. в разделе заголовкам столбцов. Если

Вычисление вложенной формулы по шагам

>=СУММ(D2:D4)	из другой области
Mac в	и после имениСУММ в зависимости от ошибок. Ниже представлены Эта ошибка возникает при если это возможно.Ctrl+ZПросто исправьтеВЫБОР Функции СРЗНАЧ и
Редактирование	Исправление ошибки #ССЫЛКА! #, так какформулы, поскольку ячейки D5, листа, если на
меню Excel выберите Параметры	книги перед восклицательным, необходимо использовать числовые параметров расположения. некоторые инструменты, с использовании неправильного оператора BaLoo).SU, не вычисляются. В СУММ вложены внажмите кнопкуЭта ошибка отображается в Excel не может. В Excel для D6 и D7,

эту ячейку ссылалась > Поиск ошибок знаком). аргументы. В других
Например, функция СУММ требует помощью которых вы диапазона в формуле: Господа и дамы!Урок подготовлен для Васна таких случаях в
функцию ЕСЛИ.Найти и выделить Excel при наличии отобразить все символы, Mac в

смежные с ячейками, одна из строк.Числа нужно вводить без функциях, например только один аргумент, можете искать и или при использованииПодскажите плиз ответ командой сайта office-guru.ruSUM поле Вычисление отображаетсяДиапазон ячеек D2:D5 содержит

(вы также можете недопустимой ссылки на которые это исправить.меню Excel выберите Параметры на которые ссылается в вычисляемом столбце.В Excel 2007 нажмите форматированияЗАМЕНИТЬ
но у нее исследовать причины этих оператора пересечения (символ на вопрос:Источник: http://www.excel-easy.com/functions/formula-errors.html
. значение #Н/Д. значения 55, 35, нажать клавиши
ячейку. Например, выИсправление ошибки #ДЕЛ/0! > Поиск ошибок формула, и ячейкой

Ячейки, которые содержат годы,кнопку Microsoft Office

Не форматируйте числа, которые, требуется, чтобы хотя может быть до ошибок и определять пробела) между ссылкамиЕсть ячейка, вПеревел: Антон АндроновExcel показывает сообщение обЕсли ссылка пуста, в
45 и 25,CTRL+G удалили ячейки, наЭта ошибка отображается в.
с формулой (D8), представленные 2 цифрами.и выберите вводите в формулу. бы один аргумент 255 аргументов (включительно). решения. на диапазон для которой формула деления.Автор: Антон Андронов ошибке поле поэтому функцияили которые ссылались другие Excel, если числоВ разделе содержат данные, на Ячейка содержит дату вПараметры Excel Например, если нужно имел текстовое значение.Пример одного аргумента:Примечание: указания пересечения двух Если знаменатель ревенТот, кто никогда не#ЗНАЧ!ВычислениеСРЗНАЧ(D2:D5)CONTROL+G формулы, или вставили

См. также

делится на нольПоиск ошибок

которые должна ссылаться текстовом формате, которая

support.office.com

Ошибки в формулах Excel

> ввести в формулу Если использовать в=СУММ(A1:A10) В статье также приводятся диапазонов, которые не

Ошибка ;##

«0» — получается ошибался — опасен.(#VALUE!) в томотображается нулевое значениевозвращает результат 40.

Ошибка #ИМЯ?

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

=СУММ(A1:A10;C1:C10) в формулах. Этот в которых пересекаются либо число, либо

когда они случаются не подходящего типа. при каждом изменении

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

значения 55, 35,и или столбец? Мы Добавьте обработчик ошибок, какОК: формула не блокируется

веку. Например, датаПоиск ошибок. Если вы введете результаты или ошибку..

список не исчерпывающий — данные двух и пустота. В Excel не по твоейa) Измените значение в

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

более диапазонов. 2007 всё работает вине. Так в ячейке результаты в диалоговом поэтому функция СРЗНАЧ(D2:D5)

. в этой формуле =ЕСЛИ(C2;B2/C2;0).Примечание: умолчанию все ячейки может относиться какВключить фоновый поиск ошибок числе, Excel будет
не должно превышать собраны некоторые наиболее все возможные ошибкиЕсли используется неправильный оператор с такой формулой: Microsoft Excel, некоторыеA3 окне возвращает результат 40.
На вкладке = SUM (A2,Исправление ошибки #Н/Д Сброс пропущенных ошибок применяется на листе заблокированы, к 1931, так. Любая обнаруженная ошибка
считать его разделителем. 64 частые ошибки, которые формул. Для получения диапазона: «=ЕСЛИОШИБКА(A1/B1;»»)». А как функции и формулы.Вычисление формулы=ЕСЛИ(ЛОЖЬ;СУММ(E2:E5);0)Формулы B2, C2) и

Эта ошибка отображается в ко всем ошибкам,
поэтому их невозможно
и к 2031

будет помечена треугольником

office-guru.ru

Перехват ошибок в формулах функцией ЕСЛИОШИБКА (IFERROR)

 Если вам нужно,В функцию можно вводить
 допускают пользователи при

ошибкам поищите ответ (:) для разделения в Excel 2003???? не потому, что игнорирует ячейки, содержащие тех, которые отображаются 50, выражение вЗависимости формулНажмите кнопку или формуле недоступно на всех листах защищен. Это поможет правило для выявления

углу ячейки. с разделителями тысяч более 64 уровней описаны способы их на свой вопрос первой и последней[email protected]@ вы накосячили при текст. в ячейке. Это первом аргументе функциинажмите кнопкуОтменить

значение.

 активной книги.

избежать случайных ошибок, дат в текстовомЧтобы изменить цвет треугольника,

или символами валюты, вложенных функций.

исправления. или задайте его ячеек при ссылке: =если(еошибка(A1/B1;»»;A1/B1)) вводе, а из-заСообщение об ошибке функции ЕСЛИ (аргумент лог_выражение)Окно контрольного значения(или клавиши CTRL+Z),Если вы используете функциюСовет: таких как случайное формате, допускающих двоякое которым помечаются ошибки, отформатируйте ячейки послеИмена других листов должныРекомендация на форуме сообщества на непрерывный диапазонBaLoo временного отсутствия данных#ДЕЛ/0!

СЛЧИС имеет значение ЛОЖЬ.. чтобы отменить удаление, ВПР, что пытается Советуем расположить диалоговое окно

planetaexcel.ru

Если_ошибка в старом Excel 2003

 удаление или изменение толкование.
выберите нужный цвет ввода чисел.
быть заключены вДополнительные сведения Microsoft Excel. ячеек в формуле.: Спасибо, всё работает. или копирования формул(#DIV/0!) появляется при,Функция ЕСЛИ возвращает значениеНажмите кнопку измените формулу или найти в диапазонеПоиск ошибок

 формул. Эта ошибкаЧисла, отформатированные как текст

 в полеНапример, если для прибавления

planetaexcel.ru

одинарные кавычки

Источник

можно открыть прикрепленный файл и посмотреть (в т.ч. и формулу)

или вариант сложнее:
1. переводите Excel в режим ссылок R1C1 (Параметры, Формулы, Стиль ссылок R1C1 — поставте птичку, Ок)
2. копируете приведенную в сообщении выше формулу в любую ячейку 6-й колонки
3. копируете ячейку с формулой по всей 6-й колонке
4. я не стал морочиться с нулями. я просто настроил Excel не показывать нули на текущем листе (Параметры, Дополнительно, группа Параметры отображения листа, Показывать нули в ячейках, которые содержат нулевые значения — убираете птичку, Ок)

Источник

Хитрости »

7 Октябрь 2015 50733 просмотров

Случаются ситуации, когда в рабочей книге на листах создано много формул, выполняющих различные задачи. При этом формулы созданы когда-то давно, возможно даже на вами. И формулы возвращают ошибки. Например #ДЕЛ/0!(#DIV/0!). Эта ошибка возникает, если внутри формулы происходит деление на ноль: =A1/B1, где в B1 ноль или пусто. Но могут быть и другие ошибки(#Н/Д, #ЗНАЧ! и т.д.). Можно изменить формулу, добавив проверку на ошибку:
=ЕСЛИ(ЕОШ(A1/B1);0; A1/B1)
=IF(ISERR(A1/B1),0, A1/B1)
аргументы:
=ЕСЛИ(ЕОШ(1 аргумент);2 аргумент; 1 аргумент)
Эти формулы будут работать в любой версии Excel. Правда, функция ЕОШ не обработает ошибку #Н/Д(#N/A). Чтобы так же обработать и #Н/Д необходимо использовать функцию ЕОШИБКА:
=ЕСЛИ(ЕОШИБКА(A1/B1);0; A1/B1)
=IF(ISERROR(A1/B1),0, A1/B1)
Однако далее по тексту я буду применять ЕОШ(т.к. она короче) и к тому же не всегда надо «не видеть» ошибки #Н/Д.
Но для версий Excel 2007 и выше можно применить чуть более оптимизированную функцию ЕСЛИОШИБКА(IFERROR):
=ЕСЛИОШИБКА(A1/B1;0)
=IFERROR(A1/B1,0)
аргументы:
=ЕСЛИОШИБКА(1 аргумент; 2 аргумент)

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

Почему ЕСЛИОШИБКА лучше и я называю её более оптимизированной?

Разберем первую формулу подробнее:

=ЕСЛИ(ЕОШ(A1/B1);0; A1/B1)

Если вычислить пошагово, то увидим, что сначала происходит вычисление выражения

(т.е. деление). И если его результат ошибка – то ЕОШ вернет

ИСТИНА(TRUE)

, которое будет передано в

ЕСЛИ(IF)

. И тогда функцией

ЕСЛИ(IF)

будет возвращено значение из второго аргумента 0.
Но если результат не является ошибочным и

ЕОШ(ISERR)

возвращает

ЛОЖЬ(FALSE)

– то функция заново будет вычислять уже вычисленное ранее выражение:

С приведенной формулой это особой роли не играет. Но если применяется формула вроде ВПР (VLOOKUP) с просмотром на несколько тысяч строк – то вычисление два раза может значительно увеличить время пересчета формул.
Функция же

ЕСЛИОШИБКА(IFERROR)

один раз вычисляет выражение, запоминает его результат и если он ошибочен возвращает записанное вторым аргументом. Если же ошибки нет, то возвращает запомненный результат вычисления выражения из первого аргумента. Т.е. вычисление по факту происходит один раз, что практически не будет влиять на скорость общего пересчета формул.
Поэтому если у вас Excel 2007 и выше и файл не будет использоваться в более ранних версиях – то имеет смысл использовать именно

ЕСЛИОШИБКА(IFERROR)

Для чего формулы с ошибками вообще исправлять? Обычно делается для более эстетичного отображения данных в отчетах, особенно если отчеты потом руководству отправляют.

Итак, есть на листе такие формулы, ошибки которых надо обработать. Если подобных формул для исправления одна-две(да даже 10-15) – то проблем почти нет заменить вручную. Но если таких формул несколько десятков, а то и сотен – проблема приобретает почти вселенские масштабы :-). Однако процесс можно упростить через написание относительно простого кода Visual Basic for Application.

Для всех версий Excel:

Sub IfIsErrNull()
    Const sToReturnVal As String = "0"
    'если необходимо вместо нуля возвращать пусто
    'Const sToReturnVal As String = """"""
    Dim rr As Range, rc As Range
    Dim s As String, ss As String
    On Error Resume Next
    Set rr = Intersect(Selection, ActiveSheet.UsedRange)
    If rr Is Nothing Then
        MsgBox "Выделенный диапазон не содержит данных", vbInformation, "www.excel-vba.ru"
        Exit Sub
    End If
 
    For Each rc In rr
        If rc.HasFormula Then
            s = rc.Formula
            s = Mid(s, 2)
            ss = "=" & "IF(ISERR(" & s & ")," & sToReturnVal & "," & s & ")"
            If Left(s, 9) <> "IF(ISERR(" Then
                If rc.HasArray Then
                    rc.FormulaArray = ss
                Else
                    rc.Formula = ss
                End If
                If Err.Number Then
                    ss = rc.Address
                    rc.Select
                    Exit For
                End If
            End If
        End If
    Next rc
    If Err.Number Then
        MsgBox "Невозможно преобразовать формулу в ячейке: " & ss & vbNewLine & _
                Err.Description, vbInformation, "www.excel-vba.ru"
    Else
        MsgBox "Формулы обработаны", vbInformation, "www.excel-vba.ru"
    End If
End Sub

Для версий 2007 и выше

Sub IfErrorNull()
    Const sToReturnVal As String = "0"
    'если необходимо вместо нуля возвращать пусто
    'Const sToReturnVal As String = """"""
    Dim rr As Range, rc As Range
    Dim s As String, ss As String
    On Error Resume Next
    Set rr = Intersect(Selection, ActiveSheet.UsedRange)
    If rr Is Nothing Then
        MsgBox "Выделенный диапазон не содержит данных", vbInformation, "www.excel-vba.ru"
        Exit Sub
    End If
 
    For Each rc In rr
        If rc.HasFormula Then
            s = rc.Formula
            s = Mid(s, 2)
            ss = "=" & "IFERROR(" & s & "," & sToReturnVal & ")"
            If Left(s, 8) <> "IFERROR(" Then
                If rc.HasArray Then
                    rc.FormulaArray = ss
                Else
                    rc.Formula = ss
                End If
                If Err.Number Then
                    ss = rc.Address
                    rc.Select
                    Exit For
                End If
            End If
        End If
    Next rc
 
    If Err.Number Then
        MsgBox "Невозможно преобразовать формулу в ячейке: " & ss & vbNewLine & _
                Err.Description, vbInformation, "www.excel-vba.ru"
    Else
        MsgBox "Формулы обработаны", vbInformation, "www.excel-vba.ru"
    End If
End Sub

Как это работает
Если не знакомы с макросами, то для начала лучше прочитать как их создавать и вызывать: Что такое макрос и где его искать?, т.к. может случиться так, что все сделаете правильно, но забудете макросы разрешить и ничего не заработает.

Копируете приведенный код, переходите в редактор VBA(Alt+F11), создаете стандартный модуль(Insert —Module) и просто вставляете в него этот код. Переходите в нужную книгу Excel и выделяете все ячейки, формулы в которых необходимо преобразовать таким образом, чтобы в случае ошибки они возвращали ноль. Жмете Alt+F8, выбираете код IfIsErrNull(или IfErrorNull, в зависимости от того, какой именно скопировали) и жмете Выполнить.
Ко всем формулам в выделенных ячейках будет добавлена функция обработки ошибки. Приведенные коды учитывают так же:
-если в формуле уже применена функция ЕСЛИОШИБКА или ЕСЛИ(ЕОШ, то такая формула не обрабатывается;
-код корректно обработает так же функции массива;
-выделять можно несмежные ячейки(через Ctrl).
В чем недостаток: сложные и длинные формулы массива могут вызвать ошибку кода, в связи с особенностью данных формул и их обработкой из VBA. В таком случае код напишет о невозможности продолжить работу и выделит проблемную ячейку. Поэтому настоятельно рекомендую производить замены на копиях файлов.
Если значение ошибки надо заменить на пусто, а не на ноль, то надо строку

Const sToReturnVal As String = "0"

Удалить, а перед строкой

'Const sToReturnVal As String = """"""

Удалить апостроф (‘)

Так же можно данный код вызывать нажатием кнопки(Как создать кнопку для вызова макроса на листе) или поместить в надстройку(Как создать свою надстройку?), чтобы можно было вызывать из любого файла.

И небольшое дополнение: старайтесь применять код вдумчиво. Не всегда возврат ошибки мешает. Например, при использовании ВПР иногда полезно видеть какие значения не были найдены.
Так же хочу отметить, что применять надо к реально работающим формулам. Потому как если формула возвращает #ИМЯ!(#NAME!), то это означает, что в формуле неверно записан какой-то аргумент и это ошибка записи формулы, а не ошибка результата вычисления. Такие формулы лучше проанализировать и найти ошибку, чтобы избежать логических ошибок расчетов на листе.

Статья помогла? Поделись ссылкой с друзьями!

Видеоуроки

Поиск по меткам

Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

Источник

17 авг. 2022 г.
читать 2 мин

Вы можете использовать следующие методы в Excel, чтобы вернуть пустое значение вместо значения ошибки, когда допустимое значение не возвращается из формулы:

Способ 1: ЕСЛИОШИБКА, затем пробел с некоторой формулой

=IFERROR( B2 / A2 , "")

Метод 2: ЕСЛИОШИБКА, затем пустое значение с функцией ВПР

=IFERROR(VLOOKUP( E2 , $A$2:$C$12 , 3, FALSE), "")

В следующих примерах показано, как использовать каждый метод на практике.

Пример 1: ЕСЛИОШИБКА, затем пробел с некоторой формулой

Предположим, мы используем следующую формулу, чтобы разделить значения в столбце B на значения в столбце A в этой конкретной электронной таблице Excel:

= B2 / A2

Обратите внимание, что для каждой ячейки в столбце C, где мы пытаемся разделить на пустое значение, мы получаем #DIV/0! как результат.

Чтобы вернуть пустое значение вместо значения ошибки, мы можем ввести следующую формулу в ячейку C2 :

=IFERROR( A2 / B2 , "")

Затем мы можем скопировать и вставить эту формулу в каждую оставшуюся ячейку в столбце C:

Теперь для каждой ячейки в столбце C, где мы пытаемся разделить на пустое значение, в результате мы просто получаем пустое значение.

Пример 2: ЕСЛИОШИБКА, затем пустое значение с функцией ВПР

Предположим, мы используем следующую формулу ВПР для поиска имени игрока в столбце А и возврата значения подборов в столбце С:

VLOOKUP( F2 , $A$2:$C$12 , 3, FALSE)

Обратите внимание, что для каждой ячейки в столбце G, где мы встречаем пустое значение в функции ВПР , в результате мы получаем #Н/Д .

Чтобы вернуть пустое значение вместо значения #Н/Д , мы можем ввести следующую формулу в ячейку F2 :

=IFERROR(VLOOKUP( F2 , $A$2:$C$12 , 3, FALSE), "")

Затем мы можем скопировать и вставить эту формулу в каждую оставшуюся ячейку в столбце F:

Теперь для каждой ячейки, где мы встречаем пустое значение в функции ВПР , мы просто получаем в результате пустое значение.

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:

Как игнорировать значения #N/A при использовании формул в Excel
Как заменить значения #N/A в Excel
Как исправить ошибку #ИМЯ в Excel

Источник