엑셀 수식을 작성하다 보면 예기치 않은 오류 메시지를 마주할 때가 있습니다.
#N/A, #VALUE!, #DIV/0!, #REF! 등 다양한 오류들은 보고서의 가독성을 해치고, 때로는 분석 결과에 대한 신뢰도를 떨어뜨리기도 합니다.
IF의 기본, 다중 조건 처리 방법을 배웠다면, 이제는 이러한 오류를 깔끔하게 처리하고, 데이터에 대한 다양한 정보를 확인하는 논리 함수들을 익힐 차례입니다.
IFERROR 함수를 중심으로 오류를 제어하는 방법과 함께, 데이터의 상태를 확인하는 다양한 'IS' 계열 정보 함수들을 집중적으로 다룰 예정입니다.
왜 오류 처리 및 정보 함수를 마스터해야 할까요?
잘못된 데이터 입력, 참조 오류, 계산 오류 등은 엑셀 작업에서 흔히 발생합니다.
이러한 오류 메시지가 그대로 노출되면 보고서를 받아보는 사람들에게 혼란을 주거나, 데이터가 잘못되었다는 인상을 줄 수 있죠. IFERROR와 같은 오류 처리 함수는 이러한 메시지를 숨기거나, 사용자 친화적인 메시지로 대체하여 보고서의 가독성과 완성도를 높여줍니다.
또한, ISERROR, ISNA 같은 정보 함수들은 특정 유형의 오류 발생 여부를 논리값으로 반환하여, 오류를 감지하고 그에 따른 자동화된 조치를 취하는 데 활용됩니다. 데이터의 유효성을 검사하고, 잠재적인 문제를 사전에 파악하는 데 필수적인 역량인 셈이죠.
주요 함수들
다음 함수들을 실무 예제와 함께 자세히 살펴보겠습니다.
- IFERROR: 수식에서 오류가 발생했을 때 지정된 값 반환
- ISERROR: 모든 유형의 오류 값(#N/A, #VALUE!, #REF! 등)을 확인
- ISNA: #N/A 오류만 확인
- ISREF: 셀 참조가 유효한지 확인
- ERROR.TYPE: 발생한 오류의 유형을 숫자로 반환
실무 예제로 배우는 엑셀 함수 마스터하기
각 함수를 실제 비즈니스 시나리오에 적용하여 보고서의 안정성과 데이터 관리의 효율성을 높이는 방법을 보여드리겠습니다.
1. IFERROR: 오류 메시지를 깔끔하게 처리하는 만능 도구
IFERROR 함수는 수식의 결과가 오류일 경우 지정된 값을 반환하고, 오류가 아닐 경우 수식의 원래 결과를 반환합니다. 가장 널리 사용되는 오류 처리 함수이며, 보고서의 가독성을 크게 향상시킵니다.
- 기본 형식: =IFERROR(값, 오류일_때_값)
- 값: 오류인지 검사할 수식 또는 값
- 오류일_때_값: 값에서 오류가 발생했을 때 반환할 값 (예: "", "오류", 0, "데이터 없음" 등)
실무 예제: 고객 등급별 할인율을 계산해야 하는데, 간혹 '기준 단가'가 입력되지 않아 #DIV/0! 오류가 발생합니다.
| 고객 ID | 구매 금액 | 기준 단가 |
| C001 | 120,000 | 10,000 |
| C002 | 80,000 | |
| C003 | 150,000 | 12,500 |
| C004 | 90,000 | 0 |
예제 목표: D열에 할인율(구매 금액 / 기준 단가)을 계산하되, 오류 발생 시 "단가 입력 필요"라고 표시하세요.
해결 방법: D2 셀에 다음 수식을 입력하고 아래로 채웁니다. =IFERROR(B2/C2, "단가 입력 필요")
결과:
| 고객 ID | 구매 금액 | 기준 단가 | 할인율 |
| C001 | 120,000 | 10,000 | 12 |
| C002 | 80,000 | 단가 입력 필요 | |
| C003 | 150,000 | 12,500 | 12 |
| C004 | 90,000 | 0 | 단가 입력 필요 |
최상급 실무 활용: IFERROR는 VLOOKUP, INDEX/MATCH 같은 조회 함수에서 데이터가 없을 때 발생하는 #N/A 오류를 처리하는 데 특히 유용합니다.
예를 들어, VLOOKUP 결과가 없을 때 "해당 상품 없음"이라고 표시하거나, ""(빈 문자열)로 처리하여 보고서를 깔끔하게 만들 수 있습니다. 데이터 조회, 계산, 보고서 자동화 등 오류가 발생할 수 있는 모든 상황에서 필수적으로 사용됩니다.
2. ISERROR: 모든 오류 유형을 포괄적으로 확인
ISERROR 함수는 인수로 전달된 값이 어떤 유형의 오류이든지 (예: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!) TRUE를 반환하고, 오류가 아니면 FALSE를 반환합니다.
- 기본 형식: =ISERROR(값)
실무 예제: 위 할인율 계산 예제에서 오류가 발생한 셀 옆에 "오류 발생" 여부를 표시하고 싶습니다.
예제 목표: E열에 D열의 할인율 결과가 오류이면 "오류 발생!", 그렇지 않으면 "정상"을 표시하세요.
해결 방법: E2 셀에 다음 수식을 입력하고 아래로 채웁니다. =IF(ISERROR(D2), "오류 발생!", "정상")
결과:
| 고객 ID | 구매 금액 | 기준 단가 | 할인율 | 상태 |
| C001 | 120,000 | 10,000 | 12 | 정상 |
| C002 | 80,000 | 단가 입력 필요 | 오류 발생! | |
| C003 | 150,000 | 12,500 | 12 | 정상 |
| C004 | 90,000 | 0 | 단가 입력 필요 | 오류 발생! |
최상급 실무 활용: IFERROR와 유사하게 오류를 처리하지만, ISERROR는 주로 다른 함수와 결합하여 특정 오류 발생 시 다른 작업을 수행하도록 지시하는 데 사용됩니다.
예를 들어, IF(ISERROR(VLOOKUP(...)), "대체 값", VLOOKUP(...))처럼 IFERROR가 없는 구형 엑셀 버전에서 오류를 처리할 때 유용하며, 오류 자체의 발생 여부를 조건으로 사용할 때 좋습니다.
3. ISNA: 특정 오류 유형 (#N/A)만 확인
ISNA 함수는 인수가 #N/A 오류(Not Available)일 경우에만 TRUE를 반환하고, 다른 오류 유형이나 오류가 아닐 경우에는 FALSE를 반환합니다. 주로 VLOOKUP, MATCH 등에서 검색 값이 없을 때 발생하는 #N/A 오류를 특정하여 처리할 때 사용됩니다.
- 기본 형식: =ISNA(값)
실무 예제: 제품 코드에 따라 제품명을 조회해야 하는데, 존재하지 않는 제품 코드에 대해 #N/A 오류가 발생합니다.
| 제품 코드 |
| P-101 |
| P-105 |
| P-102 |
| P-108 |
(참조할 제품 목록이 E2:F4에 있다고 가정)
| 제품 코드 | 제품명 |
| P-101 | 노트북 |
| P-102 | 태블릿 |
| P-103 | 스마트폰 |
예제 목표: B열에 VLOOKUP 함수로 제품명을 조회하되, #N/A 오류가 발생하면 "제품 정보 없음"이라고 표시하세요. (단, IFERROR를 사용하지 않는 방식으로)
해결 방법: B2 셀에 다음 수식을 입력하고 아래로 채웁니다. =IF(ISNA(VLOOKUP(A2, $E$2:$F$4, 2, FALSE)), "제품 정보 없음", VLOOKUP(A2, $E$2:$F$4, 2, FALSE))
결과:
| 제품 코드 | 제품명 |
| P-101 | 노트북 |
| P-105 | 제품 정보 없음 |
| P-102 | 태블릿 |
| P-108 | 제품 정보 없음 |
최상급 실무 활용: IFERROR가 없는 이전 버전의 엑셀에서 #N/A 오류를 처리하는 표준 방법이었습니다. 현재는 IFERROR가 더 간결하지만, 특정 오류 유형(N/A만)을 구분하여 처리해야 할 때 유용하게 사용될 수 있습니다.
예를 들어, 조회 실패로 인한 N/A 오류는 특정 메시지로, 계산 오류(DIV/0!)는 다른 메시지로 처리하고 싶을 때 ISNA와 ISERROR를 조합할 수 있습니다.
4. ISREF: 셀 참조의 유효성 확인
ISREF 함수는 인수로 전달된 값이 유효한 참조(셀, 범위, 정의된 이름)인 경우 TRUE를 반환하고, 그렇지 않으면 FALSE를 반환합니다. 주로 INDIRECT 함수 등 간접 참조를 사용할 때 유효성을 검사하는 데 활용됩니다.
- 기본 형식: =ISREF(값)
실무 예제: 어떤 보고서가 특정 시트 이름(Sheet1, Sheet2, Sheet3 등)에 따라 데이터를 가져오도록 되어 있는데, 시트 이름이 잘못 입력될 경우 오류가 발생합니다.
| 시트 이름 |
| Sheet1 |
| SheetX |
| Sheet2 |
| 참조불가 |
예제 목표: B열에 시트 이름이 유효한 참조인지 확인하여 "유효한 참조" 또는 "유효하지 않은 참조"를 표시하세요.
해결 방법: B2 셀에 다음 수식을 입력하고 아래로 채웁니다. =IF(ISREF(INDIRECT(A2&"!A1")), "유효한 참조", "유효하지 않은 참조") (INDIRECT 함수는 텍스트 문자열을 실제 참조로 변환해줍니다. A1은 시트에 항상 존재하는 셀이므로 검사에 사용됩니다.)
결과:
| 시트 이름 | 상태 |
| Sheet1 | 유효한 참조 |
| SheetX | 유효하지 않은 참조 |
| Sheet2 | 유효한 참조 |
| 참조불가 | 유효하지 않은 참조 |
최상급 실무 활용: 다이나믹하게 변하는 시트 이름이나 파일 경로를 참조할 때 유효성을 검사하여 수식 오류를 방지합니다.
예를 들어, INDIRECT 함수를 사용하여 여러 시트에서 데이터를 가져올 때, 해당 시트가 실제로 존재하는지 ISREF로 미리 확인하여 오류를 줄일 수 있습니다. 복잡한 보고서 시스템이나 데이터 통합 작업에서 안정성을 높이는 데 기여합니다.
5. ERROR.TYPE: 오류의 종류를 숫자로 반환
ERROR.TYPE 함수는 오류 값에 해당하는 숫자를 반환합니다. 이 숫자를 사용하여 특정 오류 유형에 따라 다른 조치를 취할 수 있습니다.
- 오류 유형 숫자:
- 1: #NULL! (교차하지 않는 영역 참조)
- 2: #DIV/0! (0으로 나눔)
- 3: #VALUE! (잘못된 인수나 피연산자)
- 4: #REF! (잘못된 셀 참조)
- 5: #NAME? (수식에서 이름이 잘못됨)
- 6: #NUM! (숫자 문제)
- 7: #N/A (값을 사용할 수 없음)
- 기본 형식: =ERROR.TYPE(오류_값)
실무 예제: 다양한 오류가 발생할 수 있는 계산 결과가 B2:B5 셀에 있다고 가정해봅시다.
| 계산 결과 |
| #DIV/0! |
| 123 |
| #N/A |
| #VALUE! |
예제 목표: C열에 각 오류의 유형을 숫자로 표시하고, D열에 해당 오류 유형에 따른 사용자 정의 메시지를 표시하세요.
해결 방법:
- C2 셀에 =ERROR.TYPE(B2) 입력 후 아래로 채우기
- D2 셀에 IF와 CHOOSE 함수를 조합하여 다음 수식을 입력하고 아래로 채웁니다. =IF(ISERROR(B2), CHOOSE(ERROR.TYPE(B2), "참조 오류", "0으로 나눔", "값 오류", "참조 오류", "이름 오류", "숫자 오류", "데이터 없음"), "오류 아님")
결과:
| 계산 결과오류 | 유형 번호사용자 | 정의 메시지 |
| #DIV/0! | 2 | 0으로 나눔 |
| 123 | #N/A | 오류 아님 |
| #N/A | 7 | 데이터 없음 |
| #VALUE! | 3 | 값 오류 |
최상급 실무 활용: 복잡한 오류 진단 및 맞춤형 오류 보고 시스템을 구축할 때 유용합니다.
예를 들어, 사용자에게 오류 메시지를 제공할 때 단순히 "오류 발생"이 아니라, "데이터 조회 실패 (#N/A)" 또는 "숫자 입력 오류 (#VALUE!)"와 같이 구체적인 원인을 알려주어 문제 해결을 돕는 데 활용될 수 있습니다.
IFERROR를 활용한 간결한 오류 처리부터, ISERROR, ISNA, ISREF, ERROR.TYPE 같은 정보 함수들을 통해 오류의 유형을 분석하고 데이터의 유효성을 검사하는 방법을 심층적으로 살펴보았습니다.
이 함수들을 통해 여러분의 엑셀 보고서는 훨씬 더 안정적이고, 사용자 친화적이며, 전문적인 모습을 갖추게 될 것입니다.