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

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

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

    Видеоуроки

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