Excel 수식 오류에 대처하는 10가지 방법

원활한 데이터 관리를 위해서는 Excel의 오류를 해결하는 방법을 알고 있는 것이 중요합니다. ISERROR 및 IF 함수를 활용하여 Excel 오류를 수정하는 10가지 방법을 소개합니다.

 

권현욱(엑셀러) | 아이엑셀러 닷컴 대표 · Microsoft Excel MVP · Excel 솔루션 프로바이더 · 작가

 

(이미지: 아이엑셀러 닷컴)

 

※ 이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.

---

• 원문: The Guide to Troubleshooting Excel Errors: Top 10 Ways to Use ISERROR and IF Functions for Seamless Data Management
• URL: https://www.bollyinside.com/news/technology/guides/the-guide-to-troubleshooting-excel-errors-top-10-ways-to-use-iserror-and-if-functions-for-seamless-data-management-2/

---

ISERROR 및 IF 함수의 마법

ISERROR 함수는 이름에서 알 수 있듯이 셀에 오류 값이 포함되어 있는지 확인합니다. 대상 셀을 평가하고 오류가 있으면 TRUE를 반환하고 오류가 없으면 FALSE를 반환합니다.

IF 함수를 사용하면 논리적 테스트를 수행하고 테스트 결과에 따라 다양한 작업을 지정할 수 있습니다. 마치 Excel에 미니 의사결정 시스템이 있는 것과 같습니다. IF와 ISERROR 함수를 결합하면 오류를 자동으로 처리하고 사용자 지정 메시지나 대체 계산을 표시하는 강력한 수식을 만들 수 있습니다.

 

기본 사항을 이해했으므로 이러한 기능을 사용하여 데이터 관리 프로세스를 간소화하고 Excel 오류 해결 전문가가 될 수 있는 10가지 방법을 살펴보겠습니다.

 

1. 오류 식별 및 표시

ISERROR 및 IF 함수의 가장 기본적이지만 유용한 응용 방법 중 하나는 오류를 명확하고 간결하게 식별하고 표시하는 것입니다. ISERROR 함수와 함께 간단한 IF 문을 사용하면 수식에서 오류가 발생할 때마다 Excel이 사용자 지정 메시지를 표시하도록 지시할 수 있습니다.

=IF(ISERROR(A1), "Error in calculation", A1)

 

이 수식은 Excel에게 A1 셀에 오류가 있는지 확인니다. 오류가 있다면 'Error in calculation'라는 메시지가 표시됩니다. 오류가 없으면 A1 셀에 값이 표시됩니다. 간단하면서도 효과적입니다.

 

2. 0으로 나누기 오류 처리

나눗셈이 포함된 복잡한 공식이 있는데 갑자기 0으로 나누어지는 경우가 있다면 Excel은 어김없이 오류를 발생시킵니다. 하지만 두려워할 필요가 없습니다. IF 함수가 도움이 될 수 있습니다. IF, ISERROR 및 나누기 수식을 결합하면 오류를 0으로 나누는 작업을 원활하게 처리할 수 있습니다. 예는 다음과 같습니다.

=IF(ISERROR(A1/B1), "Cannot divide by zero", A1/B1)

 

이 수식에서 Excel은 먼저 A1을 B1로 나눌 때 오류가 있는지 확인합니다. 오류가 있는 경우 “Cannot divide by zero”라는 메시지가 표시됩니다. 오류가 없으면 나누기를 수행합니다. 이 방법을 사용하면 '0 나누기 오류'로 인해 계산이 중단되는 것을 방지할 수 있습니다.

 

3. 계산 오류 무시

때로는 계산 오류로 인해 전체 데이터세트가 손상되고 분석이 쓸모없게 될 수도 있습니다. 하지만 ISERROR 및 IF 함수는 이러한 귀찮은 오류를 무시하고 계산을 계속하는 데 도움이 될 수 있습니다. ISERROR, IF 및 간단한 산술 연산자를 결합하면 Excel에서 오류를 0 값으로 처리하도록 지시할 수 있습니다.

=IF(ISERROR(A1), 0, A1) + IF(ISERROR(B1), 0, B1)

 

이 수식에서 Excel은 A1 셀에 오류가 있는지 확인합니다. 오류가 있는 경우 A1을 0으로 처리합니다. B1 셀에도 동일한 작업이 수행됩니다. 이를 통해 오류로 인해 중단되지 않고 계산을 수행할 수 있습니다.

 

4. 오류가 있는 셀 강조 표시

대규모 데이터 세트와 복잡한 수식을 처리할 때 오류가 포함된 셀을 식별하는 것은 건초 더미에서 바늘을 찾는 것과 같을 수 있습니다. 하지만 걱정하지 마세요. Excel에는 오류가 있는 셀을 강조 표시하는 데 도움이 되는 조건부 서식 기능이 있습니다. 조건부 서식과 함께 ISERROR 함수를 사용하면 오류를 쉽게 찾아낼 수 있는 시각적 단서를 만들 수 있습니다.

1. 조건부 서식을 지정할 영역을 범위로 지정합니다.
2. [홈] 탭 - [스타일] 그룹 - [조건부 서식] - [새 규칙]을 선택합니다.
3. [새 서식 규칙] 대화상자에서 [수식을 사용하여 서식을 지정할 셀 결정] 항목을 선택합니다.
4. 수식 필드에 '=ISERROR(A1)'이라고 입력합니다.
5. 오류 셀에 대해 원하는 형식(예: 글꼴 색상, 채우기 색상)을 선택합니다.
6. [확인]을 클릭합니다.

 

이 간단한 방법을 사용하면 데이터 세트의 오류를 시각적으로 식별하고 해결할 수 있습니다.

 

5. 단순화된 오류 처리

IF와 ISERROR 함수를 반복적으로 사용하는 경우 시간과 노력을 절약할 수 있는 바로가기가 있습니다. 바로 IFERROR 함수입니다. 이 함수는 IF와 ISERROR의 기능을 간결하게 결합합니다. IFERROR의 구문은 다음과 같습니다.

=IFERROR(value, value_if_error)

 

첫 번째 인수인 value는 평가하려는 표현식 또는 계산입니다. 두 번째 인수인 value_if_error는 첫 번째 인수에서 오류가 발생할 경우 Excel에서 수행할 값 또는 작업입니다. 예를 들어 셀 A1에 오류가 발생하면 "계산 오류"라는 메시지를 표시하고 그렇지 않으면 A1의 값을 표시하려면 다음 수식을 사용할 수 있습니다.

=IFERROR(A1, "Error in calculation")

 

이렇게 하면 ISERROR 함수가 필요하지 않으며 오류 처리 프로세스가 단순화됩니다.

6. 오류 숨기기

스프레드시트의 기본 수식이나 계산을 변경하지 않고 오류를 숨기고 싶었던 적이 있었나요? ISERROR 함수와 함께 IF 함수를 사용하면 Excel에서 오류 메시지 대신 빈 셀을 표시하도록 지시할 수 있습니다.

=IF(ISERROR(A1), "", A1)

 

이 수식은 Excel에게 A1 셀에 오류가 있는지 확인하도록 지시합니다. 그렇다면 빈 셀이 표시됩니다. 오류가 없으면 A1 셀에 값이 표시됩니다. 이 영리한 트릭을 사용하면 계산의 무결성을 손상시키지 않으면서 오류를 숨길 수 있습니다.

 

7. 고급 오류 처리

IF 및 ISERROR 함수 사용에 익숙하다면 오류 처리 기술을 한 단계 더 발전시킬 수 있습니다. IF 문을 중첩하면 다양한 오류를 처리할 수 있는 복잡한 오류 처리 논리를 만들 수 있습니다. 예를 들어, 0으로 나누기 및 유효하지 않은 참조를 포함하여 다양한 계산 오류가 있는 데이터세트가 있다고 가정해 보겠습니다. 여러 IF 문을 결합하면 각 오류 사례를 처리하고 사용자 정의 메시지나 대체 계산을 표시할 수 있습니다.

=IF(ISERROR(A1), "", IF(A1=0, "Cannot divide by zero", A2/A1))

 

이 수식에서 Excel은 먼저 셀 A1에 오류가 있는지 확인합니다. 오류가 있다면 아무 것도 표시되지 않습니다. 오류가 없으면 이번에는 A1이 0과 같은지 확인합니다. 0이라 “Cannot divide by zero”라는 메시지가 표시됩니다. 두 조건 모두 충족되지 않으면 오류가 없으므로 정상적인 나눗셈(A2/A1)을 실행합니다.

IF 문을 중첩하면 다양한 시나리오에 대응하고 계산을 정확하게 처리할 수 있는 일종의 오류 처리 시스템을 만들 수 있습니다.

 

8. IF + ISERROR + 논리 함수 사용

IF 및 ISERROR 함수는 유연하고 다른 Excel 함수와 호환성도 뛰어납니다. AND, OR 및 NOT과 같은 논리 연산자와 결합하면 특정 요구 사항에 맞는 정교한 오류 처리 시스템을 만들 수 있습니다. 예를 들어, 여러 계산이 포함된 데이터세트가 있고 셀에 오류가 포함되어 있는지 확인하려고 한다고 가정해 보겠습니다. 이를 달성하기 위해 OR 함수와 함께 IF 함수를 사용할 수 있습니다.

=IF(OR(ISERROR(A1), ISERROR(B1), ISERROR(C1)), "Calculation errors detected", "")

 

이 수식은 A1, B1 또는 C1 셀에 오류가 있는지 확인합니다. 셀에서 오류가 감지되면 "Calculation errors detected"라는 메시지가 표시됩니다. 오류가 없으면 빈 셀이 표시됩니다. IF 및 ISERROR와 함께 논리 함수를 활용하면 복잡한 데이터 세트에 적응하고 강력한 오류 처리 시스템을 만들 수 있습니다.

 

9. 사용자 정의 함수(VBA)로 오류 처리 단순화하기

Excel 시트의 오류를 처리하기 위해 복잡한 수식을 반복적으로 사용한다면 사용자 정의 함수(UDF: user-defined function)를 고려할 만 합니다. UDF는 Excel에서 사용할 수 있는 VBA(Visual Basic for Application)를 사용하여 만든 사용자 지정 함수로, 복잡한 계산과 오류 처리를 단순화할 수 있습니다.

오류 처리 논리가 통합된 사용자 정의 UDF를 생성하면 오류 처리 프로세스를 간소화하고 수식을 더 읽기 쉽고 유지 관리하기 쉽게 만들 수 있습니다. 다음은 셀에 오류가 있는지 확인하는 UDF의 간단한 예입니다.

Function CheckError(cell)
    If IsError(cell.Value) Then
        CheckError = True
    Else
        CheckError = False
    End If
End Function

 

이 UDF는 셀을 입력으로 사용하여 오류 값이 포함되어 있는지 확인합니다. 오류가 있으면 True를, 없으면 False를 반환합니다. UDF를 활용하면 수식을 단순화하고 스프레드시트의 가독성을 높이며 오류 처리 프로세스를 간소화할 수 있습니다.

[참고] 사용자 정의 함수를 만들기 위해서는 VBA 지식이 필요합니다. 다음 포스트 내용을 참고하세요.
💎 [VBA 입문] 15강. 사용자 정의 함수 만들기

 

10. 스프레드시트 정기 감사하기(Auditing)

Excel에서 오류를 처리하고 숨기는 다양한 기술을 다루었습니다. 모든 일이 그러하듯 예방이 항상 치료보다 낫다는 점을 기억하세요. 스프레드시트를 정기적으로 감사하고 잠재적인 오류 원인을 식별하면 문제 해결에 드는 시간을 절약할 수 있습니다.

계산 및 데이터 분석을 마무리하기 전에 시간을 내어 수식을 다시 확인하고, 입력 내용을 검증하고, 데이터 무결성을 확인하세요. 이 간단한 단계를 통해 오류를 조기에 발견하고 스프레드시트 전체에 오류가 증가하는 것을 방지할 수 있습니다.

Excel과 VBA의 모든 것 아이엑셀러 닷컴 · 강사들이 숨겨 놓고 보는 엑셀러TV