Вычисляемая в настоящий момент ячейка содержит константу как убрать
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Как в ячейке с формулой вместо ошибки показать 0
Случаются ситуации, когда в рабочей книге на листах создано много формул, выполняющих различные задачи. При этом формулы созданы когда-то давно, возможно даже на вами. И формулы возвращают ошибки. Например #ДЕЛ/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 )
Если вычислить пошагово, то увидим, что сначала происходит вычисление выражения A1 / B1 (т.е. деление). И если его результат ошибка – то ЕОШ вернет ИСТИНА (TRUE) , которое будет передано в ЕСЛИ (IF) . И тогда функцией ЕСЛИ(IF) будет возвращено значение из второго аргумента 0.
Но если результат не является ошибочным и ЕОШ (ISERR) возвращает ЛОЖЬ (FALSE) – то функция заново будет вычислять уже вычисленное ранее выражение: A1 / B1
С приведенной формулой это особой роли не играет. Но если применяется формула вроде ВПР (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
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
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 = «0»
Удалить, а перед строкой
'Const sToReturnVal As String = """"""
‘Const sToReturnVal As String = «»»»»»
Удалить апостроф ( ‘ )
Так же можно данный код вызывать нажатием кнопки(Как создать кнопку для вызова макроса на листе) или поместить в надстройку(Как создать свою надстройку?), чтобы можно было вызывать из любого файла.
И небольшое дополнение: старайтесь применять код вдумчиво. Не всегда возврат ошибки мешает. Например, при использовании ВПР иногда полезно видеть какие значения не были найдены.
Так же хочу отметить, что применять надо к реально работающим формулам. Потому как если формула возвращает #ИМЯ! (#NAME!) , то это означает, что в формуле неверно записан какой-то аргумент и это ошибка записи формулы, а не ошибка результата вычисления. Такие формулы лучше проанализировать и найти ошибку, чтобы избежать логических ошибок расчетов на листе.
Статья помогла? Поделись ссылкой с друзьями!
Вычисляемая в настоящий момент ячейка содержит константу как убрать
Здравствуйте уважаемые гуру , столкнулся с такой проблемой:
появляется ошибка #Н/Д, грешу на какой-нибудь хитрый формат ячеек в скачанном мною прайс-листе.
и как понимать это сообщение — «вычисляемая ячейка содержит константу» т.е.по логике, ячейка имеет постоянное значение? тогда в чем проблема — и там и там постоянные значения, почему бы не найти их ВПРом?
Что мною было сделано:
все документы были перемещены в один документ на разные листы
формат был выбран «ОБЩИЙ» выделением через ктрл+А, формат ячеек, число, общий.
Где подвох? я весь день думал и пробовал различные варианты, быстрее было уже 260 позиций вручную найти
Здравствуйте уважаемые гуру , столкнулся с такой проблемой:
появляется ошибка #Н/Д, грешу на какой-нибудь хитрый формат ячеек в скачанном мною прайс-листе.
и как понимать это сообщение — «вычисляемая ячейка содержит константу» т.е.по логике, ячейка имеет постоянное значение? тогда в чем проблема — и там и там постоянные значения, почему бы не найти их ВПРом?
Что мною было сделано:
все документы были перемещены в один документ на разные листы
формат был выбран «ОБЩИЙ» выделением через ктрл+А, формат ячеек, число, общий.
Где подвох? я весь день думал и пробовал различные варианты, быстрее было уже 260 позиций вручную найти Maxutor
К сообщению приложен файл: 4657011.xls (31.5 Kb)
Сообщение Здравствуйте уважаемые гуру , столкнулся с такой проблемой:
появляется ошибка #Н/Д, грешу на какой-нибудь хитрый формат ячеек в скачанном мною прайс-листе.
и как понимать это сообщение — «вычисляемая ячейка содержит константу» т.е.по логике, ячейка имеет постоянное значение? тогда в чем проблема — и там и там постоянные значения, почему бы не найти их ВПРом?
Что мною было сделано:
все документы были перемещены в один документ на разные листы
формат был выбран «ОБЩИЙ» выделением через ктрл+А, формат ячеек, число, общий.
Где подвох? я весь день думал и пробовал различные варианты, быстрее было уже 260 позиций вручную найти Автор — Maxutor
Дата добавления — 03.03.2015 в 00:47
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4352
Замечаний: 0% ±
Excel 2003
Надо внимательно читать описание функции
Надо внимательно читать описание функции AlexM
К сообщению приложен файл: 4657011_1.xls (34.0 Kb)
Сообщение Надо внимательно читать описание функции Автор — AlexM
Дата добавления — 03.03.2015 в 02:00
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Замечаний: 20% ±
2010, 2013, 2016 RUS / ENG
=ВПР(C2;Лист1!$C$4:$E$27;2;0)
, но я бы вообще сделал:
=ИНДЕКС(Лист1!$D$4:$D$27;ПОИСКПОЗ(C2;Лист1!$C$4:$C$27;0))
чтоб про номер столбца не думать
=ВПР(C2;Лист1!$C$4:$E$27;2;0)
, но я бы вообще сделал:
=ИНДЕКС(Лист1!$D$4:$D$27;ПОИСКПОЗ(C2;Лист1!$C$4:$C$27;0))
чтоб про номер столбца не думать buchlotnik
К сообщению приложен файл: _4657011.xls (33.0 Kb)
Сообщение по ВПР-у :
=ВПР(C2;Лист1!$C$4:$E$27;2;0)
, но я бы вообще сделал:
=ИНДЕКС(Лист1!$D$4:$D$27;ПОИСКПОЗ(C2;Лист1!$C$4:$C$27;0))
чтоб про номер столбца не думать Автор — buchlotnik
Дата добавления — 03.03.2015 в 09:14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4352
Замечаний: 0% ±
Excel 2003
Цитата buchlotnik, 03.03.2015 в 09:14, в сообщении № 3
чтоб про номер столбца не думать
Так больше думать надо, о двух функциях и о двух разных массивах.
Цитата buchlotnik, 03.03.2015 в 09:14, в сообщении № 3
чтоб про номер столбца не думать
Так больше думать надо, о двух функциях и о двух разных массивах. AlexM
Цитата buchlotnik, 03.03.2015 в 09:14, в сообщении № 3
чтоб про номер столбца не думать
Так больше думать надо, о двух функциях и о двух разных массивах. Автор — AlexM
Дата добавления — 03.03.2015 в 09:19
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Замечаний: 20% ±
2010, 2013, 2016 RUS / ENG
AlexM, но согласитесь, что вариант более гибкий получается и не съезжает, если структура данных «вдруг» поменялась
AlexM, но согласитесь, что вариант более гибкий получается и не съезжает, если структура данных «вдруг» поменялась buchlotnik
Сообщение AlexM, но согласитесь, что вариант более гибкий получается и не съезжает, если структура данных «вдруг» поменялась Автор — buchlotnik
Дата добавления — 03.03.2015 в 09:23
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16621
Замечаний: 0% ±
2003; 2007; 2010; 2013 RUS
А я согласен с Михаилом. Сотни раз уже было, что зовут меня воплями дикими, дескать, расчет не работает. Спрашиваю: Что делали? Ничего, говорят, только столбец вставили, нужно им было. И все, полетел ВПР их любимый.
Поэтому я практически никогда ВПР-ом не использую. Только в том случае, когда таблица состоит не более, чем из 2-х столбцов, не слишком большая по размеру вниз и точно никогда не влезут туда лишние столбцы.
Кстати, Леш, а почему ты решил, что ВПР быстрее связки ИНДЕКС+ПОИСКПОЗ? Я как-то проверял на досуге — ничего подобного.
А я согласен с Михаилом. Сотни раз уже было, что зовут меня воплями дикими, дескать, расчет не работает. Спрашиваю: Что делали? Ничего, говорят, только столбец вставили, нужно им было. И все, полетел ВПР их любимый.
Поэтому я практически никогда ВПР-ом не использую. Только в том случае, когда таблица состоит не более, чем из 2-х столбцов, не слишком большая по размеру вниз и точно никогда не влезут туда лишние столбцы.
Кстати, Леш, а почему ты решил, что ВПР быстрее связки ИНДЕКС+ПОИСКПОЗ? Я как-то проверял на досуге — ничего подобного. _Boroda_
Сообщение А я согласен с Михаилом. Сотни раз уже было, что зовут меня воплями дикими, дескать, расчет не работает. Спрашиваю: Что делали? Ничего, говорят, только столбец вставили, нужно им было. И все, полетел ВПР их любимый.
Поэтому я практически никогда ВПР-ом не использую. Только в том случае, когда таблица состоит не более, чем из 2-х столбцов, не слишком большая по размеру вниз и точно никогда не влезут туда лишние столбцы.
Кстати, Леш, а почему ты решил, что ВПР быстрее связки ИНДЕКС+ПОИСКПОЗ? Я как-то проверял на досуге — ничего подобного. Автор — _Boroda_
Дата добавления — 03.03.2015 в 09:53
Проблема с с Екселем
Этот гад нехочет прибавить 5+5 ячейка числовая. Скидываю файл. Да заранее напишите ответ если у вас получилось это. Выдаёт это «Вычисляемая в настоящий момент ячейка содержит константу» Неполучилось скинуть файл можно только видимо либо фота либо видио. Кошмар
Голосование за лучший ответ
файл выкладываешь на обменник (облако майл. ру или яндекс. диск) и даёшь ссылку.
mail.ruЗнаток (296) 3 года назад
погоди братан щас
mail.ruЗнаток (296) 3 года назад
слушай братан мож ты почту скинещ свою я те лично кину
KPbICMAH Искусственный Интеллект (122302) можешь попробовать вот здесь: https://www.planetaexcel.ru/forum/?PAGE_NAME=read&FID=1&TID=50004 совет, данный The_Prist.
KPbICMAHИскусственный Интеллект (122302) 3 года назад
мне не надо. добавь лучше ссылку в вопрос, здесь много товарищей, которые лучше меня в экселе разбираются.