엑셀에서 데이터 분석을 하다보면 다양한 오류들이 발생합니다. #DIV/0!, #N/A, #VALUE!, #REF! 등의 에러는 데이터 무결성을 해치고 정확한 분석을 방해할 수 있습니다. 이런 문제를 해결하기 위해 에러셀의 개수를 정확히 파악하고 관리하는 것이 매우 중요합니다. 엑셀의 ISERROR 함수와 SUM, SUMPRODUCT 등의 함수들을 조합하면 효과적으로 에러셀을 계산할 수 있습니다.
ISERROR 함수의 기본 이해와 동작 원리
ISERROR 함수는 엑셀에서 제공하는 정보함수 중 하나로, 셀에 에러 값이 있는지 확인하는 함수입니다. 이 함수는 모든 종류의 에러값을 감지할 수 있으며, 에러가 있으면 TRUE를, 없으면 FALSE를 반환합니다. #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!, #CALC!, #SPILL! 등 모든 에러 유형을 포괄적으로 검사합니다. 단일 셀에 적용할 때는 간단한 논리값을 반환하지만, 범위에 적용하면 배열 형태의 TRUE/FALSE 값들을 생성합니다. 이러한 특성을 활용하여 다른 함수들과 조합하면 강력한 에러 계산 도구가 됩니다. 마이크로소프트 공식 문서에서 더 자세한 정보를 확인할 수 있습니다.
SUM과 ISERROR를 활용한 배열 수식 방법
가장 전통적이고 널리 사용되는 방법은 SUM 함수와 ISERROR를 조합한 배열 수식입니다. =SUM(ISERROR(범위)*1)과 같은 형태로 사용하며, 엑셀 365 이전 버전에서는 Ctrl+Shift+Enter를 눌러 배열 수식으로 만들어야 합니다. ISERROR 함수가 범위의 각 셀을 검사하여 TRUE/FALSE 배열을 생성하면, 이를 1과 곱해 1과 0의 숫자 배열로 변환합니다. 그 다음 SUM 함수가 모든 1을 더해서 에러 개수를 반환하는 원리입니다.
- =SUM(ISERROR(A1:A10)*1) – 기본 형태의 배열 수식으로 A1부터 A10까지의 에러 개수를 계산합니다
- Ctrl+Shift+Enter 키 조합을 통해 배열 수식으로 만들면 중괄호가 자동으로 표시됩니다
- 엑셀 365나 2021 버전에서는 동적 배열 기능으로 인해 Enter만 눌러도 정상 작동합니다
- 논리값 TRUE는 1로, FALSE는 0으로 자동 변환되어 계산에 활용됩니다
SUMPRODUCT 함수를 이용한 현대적 접근법
SUMPRODUCT 함수는 배열을 기본적으로 처리하므로 더 간편하고 현대적인 방법입니다. =SUMPRODUCT(–ISERROR(범위)) 또는 =SUMPRODUCT(ISERROR(범위)*1)로 사용할 수 있으며, 모든 엑셀 버전에서 일반적인 Enter 키만으로도 정상 작동합니다. 이중 부정 연산자(–)를 사용하면 TRUE/FALSE 값을 1/0으로 변환하여 계산합니다.
| 방법 | 수식 | 장점 |
|---|---|---|
| SUMPRODUCT with 이중부정 | =SUMPRODUCT(–ISERROR(A1:A10)) | 간단하고 모든 버전 호환 |
| SUMPRODUCT with 곱하기 | =SUMPRODUCT(ISERROR(A1:A10)*1) | 의미가 명확하고 이해하기 쉬움 |
| 배열 수식 | =SUM(ISERROR(A1:A10)*1) | 전통적 방법으로 안정적 |
| COUNT + IF 조합 | =COUNT(IF(ISERROR(A1:A10),1)) | 조건부 계산에 유리 |
특정 에러 유형별 개수 계산하기
모든 에러를 한번에 계산하는 것 외에도 특정 유형의 에러만 계산하고 싶을 때가 있습니다. 이런 경우 COUNTIF 함수를 활용하면 효과적입니다. =COUNTIF(범위,”#N/A”)처럼 사용하여 #N/A 에러만 계산하거나, =COUNTIF(범위,”#DIV/0!”)로 나누기 에러만 계산할 수 있습니다. 여러 에러 유형을 동시에 계산하려면 각각의 COUNTIF 결과를 더하면 됩니다. 예를 들어 =COUNTIF(A1:A10,”#N/A”)+COUNTIF(A1:A10,”#VALUE!”)처럼 사용할 수 있습니다. Exceljet에서 다양한 에러 계산 방법을 확인할 수 있습니다.
또한 ISNA, ISERR 등의 특화된 함수들도 활용할 수 있습니다. ISNA는 #N/A 에러만 감지하고, ISERR은 #N/A를 제외한 모든 에러를 감지합니다. 이러한 함수들을 상황에 맞게 조합하면 더 정밀한 에러 분석이 가능합니다.
에러가 없는 셀의 개수 계산하기
때로는 에러 개수가 아닌 정상적인 셀의 개수를 알고 싶을 때가 있습니다. 이런 경우 NOT 함수를 ISERROR와 조합하여 사용할 수 있습니다. =SUMPRODUCT(–NOT(ISERROR(범위)))처럼 사용하면 에러가 없는 셀의 개수를 계산할 수 있습니다. NOT 함수는 논리값을 반대로 바꿔주므로, ISERROR가 반환한 TRUE/FALSE 값을 뒤집어서 에러가 없는 셀에 TRUE가 오도록 만듭니다.
또 다른 방법으로는 전체 셀 개수에서 에러 개수를 빼는 것입니다. =COUNTA(범위)-SUMPRODUCT(–ISERROR(범위))처럼 사용하면 같은 결과를 얻을 수 있습니다. COUNTA 함수는 빈 셀을 제외한 모든 셀의 개수를 세어주므로, 여기서 에러 개수를 빼면 정상 셀의 개수가 됩니다. Ablebits 튜토리얼에서 더 많은 실용적인 예제를 볼 수 있습니다.
실무에서의 활용 사례와 주의사항
실제 업무에서 에러셀 계산은 데이터 품질 관리의 핵심 요소입니다. 특히 VLOOKUP, INDEX/MATCH, 나누기 계산 등이 많이 포함된 스프레드시트에서는 정기적인 에러 체크가 필요합니다. 에러 개수가 갑자기 증가했다면 원본 데이터나 수식에 문제가 생겼을 가능성이 높습니다. 이런 경우 에러 위치를 파악하여 신속하게 수정해야 합니다.
주의할 점은 ISERROR 함수가 모든 에러를 동일하게 취급한다는 것입니다. #N/A는 찾지 못한 값을 의미할 수 있어 정상적인 경우일 수도 있지만, #DIV/0!은 명백한 계산 오류입니다. 따라서 상황에 따라 적절한 함수를 선택해야 합니다. 또한 큰 데이터셋에서는 SUMPRODUCT나 배열 수식이 성능에 영향을 줄 수 있으므로, 필요한 범위만 지정하는 것이 좋습니다. Corporate Finance Institute에서 금융 분석에서의 에러 처리 방법을 자세히 알아볼 수 있습니다.
