[엑셀 실무] 잘못된 범위 연산자 사용으로 인한 #NULL! 오류

엑셀의 여러 오류중 이 게시물에서 해결해 볼 것은 #NULL! 에러 입니다. null 이 의미하듯 이 엑셀 오류는 교차값이 없는 두 범위가 교차될 때 발생합니다.

엑셀 교차 범위 참조(intersect range references)

엑셀 #NULL! 오류를 이해하기 위해서는 교차 참조를 이해하셔야 합니다. 이 교차 참조는 두 참조 범위를 공백(SPACE)으로 구분하고 이 두 범위가 교차하는 셀들이 계산에 사용 되는 것입니다. 간단한 예로 아래의 그림을 봅니다.

[그림1] 엑셀 #NULL! 함수 - 교차 참조란?

A1:C1 과 B1:D1 이 공백으로 구분되어 교차 참조 되고 있습니다. 두 범위가 교차하는 셀들이 SUM 함수의 인수로 사용되는 것입니다. B1:C1 이 교차 부분이네요. 즉 2와 3의 합이 결과가 되는 것입니다. 이것이 엑셀 교차 범위 참조입니다.

엑셀 #NULL! 오류의 원인

1. 참조와 참조 사이 삽입 문자의 잘못된 입력 (실수로 인한 오타).
    - 조합 연산자 콤마( , ) 대신의 공백 삽입
    - 사칙 연산자 미삽입
    - 범위 연산자 콜론( : )의 미삽입
2. 공백으로 연결된 둘 이상의 범위에 교차하는 셀이 없을 때.

#NULL! 오류의 원인은 위 두가지입니다. 사실 따지고 보자면 위 둘은 같은 원인입니다. 두 범위 이상이 함수에서 참조로 사용되었지만 이 두 영역이 교차하는 범위가 없다면 엑셀은 참조값이 없다는 오류인 #NULL! 에러를 발생합니다.

엑셀 #NULL! 오류 수식 예

[그림2] 엑셀 #NULL 오류 수식 - 교차하지 않는 두 범위

=SUM(A1:B2 C1:D2)

위 수식은 합을 구하는 엑셀 함수에서 A1:B2 와 C1:D2 , 두 범위의 합을 구하기 위해 두 범위 사이에 콤마( , ) 를 입력하려다 실수로 공백이 입력된 경우입니다. 콤마( , )로 두 범위가 구분되어 인수로 전달 되었다면 아무런 오류가 없었을 것입니다. 결과는 0 이 되겠지요. 범위가 모두 비어 있으니까요.

위와 같이 공백이 두 범위 사이에 입력되었다면 두 범위의 교차 셀들이 SUM 함수의 인수로 사용됩니다. 하지만 위 수식에 사용된 두 범위는 서로 교차하는 부분이 없습니다. 그래서 참조할 수 있는 값이 없다는 오류, #NULL! 에러가 발생하는 것입니다.

엑셀 #NULL! 오류 구문 찾기

위 오류 수식 예는 너무도 단순한 수식이고 오류가 있음을 알고 살폈기에 바로 보였을 것입니다. 하지만 많은 수식에서 이렇게 쉽게 오류 계산 부분이 보이지는 않습니다. #NULL! 오류가 발생한 수식에서 그 발생 지점을 찾기 힘들때는 다음의 순서를 따릅니다.

[그림3] 엑셀 #NULL! 오류 - 오류 부분 찾기

1) #NULL! 오류 수식에 셀 포인터 놓기
2) 엑셀 에러 옵션 버튼 클릭
3) '계산 단계 표시...' 클릭
4) 수식 계산 창 > '계산' 버튼 클릭 > 오류 인수 #NULL! 로 표시

[그림4] 엑셀 #NULL! 오류 - 수식 계산 창>계산 클릭 후

이렇게 오류 부분을 확인하고 수정하여 엑셀 #NULL! 오류를 해결할 수 있습니다.

엑셀 #NULL! 오류 발생 대비

만일 엑셀에서 사용하는 수식이 교차 범위가 없을 수도 있다는 것을 미리 알고 있고 그럴 경우를 대비한다면 오류가 생길수 있는 수식을 엑셀 IFERROR 함수로 감싸줍니다. 엑셀 오류가 발생하는 것을 대비하는 것입니다.

=IFERROR( SUM(A1:B2 C1:D2) , 0 )

위에서 보셨듯이 SUM 함수 수식만으로는 두 범위의 교차 셀이 없어 엑셀이 #NULL! 에러를 반환합니다. 이 #NULL! 오류를 0 값으로 대신하여 표시하는 것이 위의 수식이며 IFERROR 함수의 기능입니다. 에러 발생시 에러 메시지를 두 번째 인수로 대신하는 것입니다.

이상이 우리가 엑셀을 사용하면서 만나는 #NULL! 에러의 발생 원인과 대처 방법, 즉 오류 해결 방법입니다. 위에서 이 엑셀 오류의 원인을 살폈지만 이 엑셀 오류를 만나게 되는 가장 큰 원인은 오타입니다.

엑셀 교차 범위 참조(범위 논리곱) : [실무 엑셀] 엑셀의 기본적인 연산자와 기호의 의미 게시물의 참조 연산자 부분
[엑셀 문답] 수식 입력후 #NAME? 에러가 표시됩니다
[엑셀 실무] 수식의 잘못된 값으로 인한 오류, #VALUE!

마늘빵.