[태그:] 엑셀수식오류

수식이 많은 엑셀 시트에서 #REF! 같은 오류가 보이면 수식 탭의 오류 검사를 열면 된다. 검사 창이 오류가 있는 셀과 설명을 하나씩 보여줘서, 어느 수식부터 살펴볼지 정하기 쉽다.

다만 엑셀의 검사 규칙으로 잘못된 계산을 모두 찾을 수는 없다. 오류 표시 없이 숫자가 나와도 그 숫자가 필요한 항목을 전부 계산했는지는 따로 확인해야 한다.

1. 오류 검사 창 열기

오류를 확인할 워크시트를 클릭한 뒤 수식 탭 → 수식 분석 → 오류 검사를 누른다. 창에 셀 주소와 오류 설명이 나타나면 수식 입력줄에서 해당 셀의 수식도 함께 본다. 설명을 확인한 뒤 다음을 누르면 이어지는 오류를 볼 수 있다.

파일의 계산 옵션이 수동이라면 검사를 시작하기 전에 F9를 눌러 수식을 다시 계산한다. 입력값을 바꿨는데 계산이 아직 갱신되지 않았다면 화면에 표시된 결과가 이전 값일 수 있다.

2. 오류 설명과 수식을 함께 보기

#REF!가 나온 셀은 수식이 유효하지 않은 셀을 참조하고 있다는 뜻이다. 참조하던 행이나 열을 방금 삭제했다면 Ctrl+Z로 되돌릴 수 있다. 이미 되돌릴 수 없는 상태라면 수식 입력줄에서 #REF!가 표시된 부분을 찾고, 원래 참조해야 할 셀을 확인해 주소를 다시 입력한다.

초록색 삼각형이 있다고 해서 수식 결과가 반드시 틀린 것은 아니다. 주변 셀과 다른 수식을 일부러 넣었을 때도 검사 규칙이 경고할 수 있다. 창의 설명을 읽고 수식이 참조하는 범위와 실제로 계산해야 할 범위를 비교한다.

3. 참조한 셀에도 오류가 있을 때

검사 창에 오류 추적 버튼이 나타나면 눌러 본다. 현재 수식이 참조하는 셀에 오류가 있을 때, 엑셀이 오류가 있는 참조 셀까지 빨간 연결선으로 표시한다. 연결된 셀의 수식이나 값을 먼저 고치고 현재 셀의 결과가 바뀌었는지 확인한다. 확인을 마쳤다면 수식 탭의 연결선 제거로 선을 지울 수 있다.

4. 오류 표시가 없는데 결과가 이상할 때

함수가 여러 개 들어간 수식을 확인할 때는 해당 셀을 선택하고 수식 탭 → 수식 분석 → 수식 계산을 연다. 계산을 누를 때마다 수식의 중간 결과를 볼 수 있어, 어느 부분에서 예상과 다른 값이 나오는지 확인할 수 있다. 이 기능은 한 번에 한 셀씩 살펴본다.

오류 검사 창에서 더 이상 오류가 나오지 않아도 합계 범위나 참조 셀이 의도대로 지정됐는지는 확인한다. 오류 검사는 수식 오류를 찾는 데 편리하지만, 계산에 넣어야 할 항목이 빠졌는지까지 항상 판단해 주지는 않는다.

엑셀 표에 오류가 여러 개 뜨면 모두 선택해서 한 번에 고치고 싶다. 먼저 알아둘 점은 ‘오류 셀 선택’과 ‘수식 수정’이 다르다는 것이다. 이동 옵션은 오류가 난 수식 셀을 골라 준다. 여러 셀에 같은 오류가 생긴 이유까지 자동으로 고쳐 주지는 않는다.

1. 오류가 난 수식 셀 한 번에 선택하기

먼저 확인할 범위를 선택한다. 시트 전체를 확인하려면 왼쪽 위에서 행 번호 1과 열 이름 A 사이의 삼각형을 누른다. F5를 누르고 이동 창에서 ‘옵션’을 연다. ‘수식’을 선택한 다음 아래 항목에서는 ‘오류’만 체크하고 확인을 누르면 된다.

이렇게 선택되는 것은 계산 결과가 오류인 수식 셀이다. 숫자나 문자를 직접 입력한 셀, 결과가 숫자로 나오지만 잘못된 셀을 참조하는 수식, 주변 수식과 다르다는 초록색 경고까지 모두 찾아 주지는 않는다.

선택된 오류 셀에 곧바로 0을 입력하거나 Delete를 누르면 그 셀에 있던 수식이 바뀌거나 삭제된다. 여러 오류를 찾았으면 먼저 같은 수식에서 시작된 오류인지 살펴봐야 한다.

2. 참조한 셀의 오류가 다른 셀에도 나타날 때

예를 들어 A2의 조회 수식이 #N/A를 반환하고 B2가 A2를 계산에 사용한다면, B2에도 오류가 나타날 수 있다. 이때 B2의 수식을 바꾸기 전에 A2가 왜 값을 찾지 못했는지 확인한다. 찾을 값이 원본 표에 있는지, 숫자와 문자로 서로 다르게 저장되지는 않았는지 보면 된다.

어느 셀에서 오류가 시작됐는지 모르겠다면 오류 셀을 선택하고 ‘수식’ 탭의 ‘오류 검사’를 연다. 참조한 셀에도 오류가 있는 경우 ‘오류 추적’으로 연결된 셀을 확인할 수 있다. 처음 오류가 난 셀을 고친 뒤 다른 오류 셀의 결과도 바뀌었는지 확인한다.

#REF!가 여러 칸에 뜬 경우에는 참조하던 행이나 열을 삭제했는지 먼저 본다. 방금 잘못 삭제했다면 Ctrl+Z로 되돌릴 수 있다. 이미 되돌릴 수 없다면 각 수식이 원래 어느 셀을 참조해야 했는지 확인해 다시 지정해야 한다.

3. 같은 오타가 반복됐다면 찾아 바꾸기

여러 수식에 같은 오타가 들어갔을 때는 Ctrl+H가 빠르다. 가령 고칠 범위의 수식들에 ‘SUMM(’이 반복돼 있다면 그 범위를 선택하고 Ctrl+H를 누른다. 찾을 내용에 SUMM(, 바꿀 내용에 SUM(를 입력한다.

‘모두 찾기’로 바뀔 셀을 먼저 확인한 뒤 ‘모두 바꾸기’를 누른다. 찾을 문자열이 다른 수식에도 쓰였다면 그 수식까지 수정될 수 있으므로, 바꿀 범위를 먼저 정하는 게 중요하다. #N/A와 #REF!처럼 원인이 다른 오류를 문자열 하나로 함께 고칠 수는 없다.

4. 한 열에 같은 계산식이 필요할 때

C2부터 C100까지 모두 A열과 B열을 더해야 한다면 C2:C100을 선택하고 =A2+B2를 입력한 뒤 Ctrl+Enter를 누른다. 엑셀은 행에 맞춰 아래 셀의 참조를 A3+B3, A4+B4처럼 바꿔 입력한다.

이 방법은 선택한 모든 행에 같은 계산 방식이 필요할 때 쓴다. 중간에 소계나 다른 수식이 들어간 행이 있다면 그 수식도 바뀌므로 범위를 나눠 선택해야 한다.

오류가 많이 보인다는 이유만으로 모든 수식에 IFERROR 함수를 넣어 오류 대신 빈칸을 표시할 필요는 없다. IFERROR로 오류 대신 빈칸을 표시하도록 하면 #N/A뿐 아니라 #REF!, #VALUE! 같은 오류도 표시되지 않는다. 오류가 난 셀을 먼저 선택해 원인을 살펴본 다음, 공통 원인이 확인된 수식만 함께 수정한다.

엑셀 셀의 오류 메시지는 종류에 따라 확인할 곳이 다르다. #N/A라면 찾는 값과 조회 범위를, #REF!라면 깨진 참조를 살펴야 한다. #SPILL!은 여러 결과가 놓일 공간부터 확인한다. 화면에 ####가 보인다면 수식을 고치기 전에 열 너비를 넓혀 볼 일이다.

오류를 한꺼번에 빈칸으로 바꾸면 표는 조용해지지만, 무엇이 잘못됐는지도 보이지 않는다. 셀에 표시된 오류 문구부터 확인하는 편이 빠르다.

1. ####는 값이 안 보이는 상태

####는 대개 열이 좁아 값 전체를 표시하지 못한다는 뜻이다. 열 머리글의 오른쪽 경계를 두 번 클릭해 너비를 맞춰 본다. 음수인 날짜·시간 값도 이런 표시가 나올 수 있으므로, 열을 넓혀도 그대로라면 수식 결과를 확인해야 한다.

긴 숫자가 1E+11처럼 보이는 것은 지수 형식의 표시다. 이 역시 #VALUE! 같은 수식 오류와는 다르다.

2. 기본 수식 오류 일곱 가지

#DIV/0!: 0 또는 빈 셀로 나눴다. 나누는 쪽의 셀을 확인한다.

#VALUE!: 계산에 맞지 않는 값이나 인수가 들어갔다. 참조 셀의 실제 값과 데이터 형식을 살핀다.

#N/A: 조회 수식이 요청한 값을 찾지 못한 경우가 흔하다. 원본에 값이 있는지, 숫자와 텍스트가 섞였거나 앞뒤 공백이 있는지 확인한다.

#REF!: 수식이 유효하지 않은 셀을 가리킨다. 참조하던 행·열을 삭제했을 수도 있고, VLOOKUP에 지정한 반환 열 번호가 조회 범위보다 클 수도 있다.

#NAME?: 엑셀이 수식의 이름을 알아보지 못한다. 함수·정의된 이름의 철자와 텍스트의 따옴표를 확인한다.

#NUM!: 사용할 수 없는 숫자 값이나 계산 결과 때문에 생긴다. 숫자 인수를 살피고, IRR·RATE 같은 함수라면 계산 결과를 찾지 못한 경우도 확인한다.

#NULL!: 교차하지 않는 범위를 공백으로 연결했을 때 나타난다. 연속 범위에는 콜론(:), 별도 범위를 함께 지정할 때는 쉼표(,)가 필요한지 살핀다.

특히 #N/A와 #REF!는 다르다. 앞은 조회할 값이 없거나 일치하지 않는 쪽, 뒤는 수식이 가리키는 위치 자체가 유효하지 않은 쪽이다. 방금 행이나 열을 잘못 삭제해 #REF!가 생겼다면 즉시 실행 취소할 수 있다. 이미 되돌릴 수 없다면 수식의 참조를 올바른 위치로 다시 지정해야 한다.

3. 여러 결과와 외부 자원에서 나는 오류

#SPILL!: 수식의 여러 결과가 들어갈 자리가 막혔다. 오류 셀을 선택해 표시되는 분산 범위를 보고, 그 안의 값·병합 셀을 확인한다. 표 안에서는 동적 배열 수식이 분산되지 않으며, 결과가 시트 끝을 넘어가도 이 오류가 난다.

#CALC!: 계산 엔진이 현재 처리하지 못하는 경우다. FILTER가 결과 없는 빈 배열을 반환했다면 조건을 확인하거나 if_empty 인수를 지정할 수 있다. 중첩 배열처럼 지원되지 않는 수식도 원인이 된다. #SPILL!처럼 주변 셀을 비우는 것만으로 해결되는 오류는 아니다.

#BLOCKED!: 필요한 리소스나 기능에 대한 접근이 차단됐다. 로그인·라이선스·권한·보안 설정 등 표시된 세부 원인을 확인한다. 외부 연결이 막힌 경우라면 수식을 다시 입력하기보다 차단 이유부터 살펴야 한다.

#CONNECT!: 수식이 필요한 리소스에 연결하지 못한다. 연결된 파일의 경로와 접근 권한, 네트워크나 서비스 상태를 확인한다. 외부 통합 문서뿐 아니라 이미지·추가 기능·Python 관련 연결에서도 나타날 수 있다.

같은 #SPILL!이라도 막힌 셀이 있는 경우와 결과 범위가 시트 밖으로 나가는 경우의 손볼 곳은 다르다. 오류 셀의 경고 아이콘에 세부 원인이 표시된다면 그 내용을 먼저 읽는 게 좋다.

4. 입력할 때 뜨는 경고는 별개

셀 안의 #오류 대신 값을 입력하는 순간 ‘중지·경고·정보’ 창이 뜬다면 데이터 유효성 검사의 오류 메시지다. 중지는 규칙에 맞지 않는 입력을 막고, 경고와 정보는 사용자에게 알린 뒤 입력을 계속할 수 있게 한다. 이 경우에는 수식 입력줄보다 해당 셀의 데이터 유효성 검사 규칙을 확인해야 한다.

엑셀 수식을 복사한 뒤 같은 숫자만 반복된다면, 결과를 다시 붙여넣기 전에 복사된 셀의 수식 입력줄을 보자. 수식의 셀 주소는 바뀌었는데 표시값만 같다면 계산 설정을 확인하고, 주소가 의도와 다르게 바뀌었다면 참조를 고쳐야 한다. 입력줄에 수식 없이 숫자만 있다면 값이 붙여넣어진 것이다.

1. 수식 입력줄에 무엇이 남았나

가령 C2의 수식이 =B2*$F$2이고 이를 C3으로 복사했다면 C3에는 =B3*$F$2가 들어가야 한다. 행마다 달라지는 B열은 한 줄 내려가고, 계속 같은 기준값을 읽는 F2는 그대로 남는다. C3을 클릭해 입력줄을 보면 화면의 숫자만 볼 때보다 문제를 빨리 좁힐 수 있다.

C3에 =B3*$F$2가 있다면 수식 복사는 된 것이다. =B2*$F$2가 그대로 있거나 =B3*F3처럼 기준 셀까지 이동했다면 참조를 살펴봐야 한다. 입력줄에 수식이 아닌 숫자만 보인다면 복사할 때 ‘값’을 선택했는지 확인한다.

2. 수식은 달라졌는데 결과가 같을 때

참조가 행에 맞게 바뀌었고 원본 데이터도 다른데 결과가 이전 숫자에 머문다면 계산 옵션을 확인한다. 수동 계산 상태에서는 수식을 채워도 결과가 즉시 다시 계산되지 않을 수 있다.

수식 탭 → 계산 옵션 → 자동을 선택한다. 수동 계산을 유지해야 하는 파일이라면 F9로 다시 계산할 수 있다. 계산 모드는 현재 열려 있는 통합 문서들이 함께 사용하므로, 다른 파일을 연 뒤부터 값이 갱신되지 않았다면 이 설정도 살펴볼 만하다.

화면 전체에 결과 대신 =B3*$F$2 같은 수식이 보인다면 수식 탭의 ‘수식 표시’를 끈다. 한 셀에서만 수식이 글자로 남는다면 셀 서식을 ‘일반’으로 바꾼 뒤 해당 셀에서 F2, Enter를 눌러 다시 적용한다.

3. 복사하면서 참조가 어긋날 때

C3의 수식이 =B3*F3이라면 엑셀은 복사한 위치에 맞춰 두 참조를 모두 옮긴 것이다. 이 예에서 F2가 모든 행의 공통 기준이라면 원래 수식을 =B2*$F$2로 고친 뒤 다시 채운다. Windows용 엑셀에서는 수식을 편집하면서 F2 참조를 선택하고 F4를 눌러 고정 형태를 바꿀 수 있다.

반대로 B2까지 $B$2로 고정하면 아래 행에서도 계속 B2를 읽는다. 그래서 같은 결과가 반복될 수도 있다. 고정이 필요한 주소만 고치고, 복사한 다음 첫 셀과 마지막 셀의 수식을 비교하면 중간 셀을 모두 다시 손보기 전에 참조가 의도대로 바뀌었는지 확인할 수 있다.

복사 뒤 #REF!가 나타났다면 수식에 유효하지 않은 참조가 생겼다는 뜻이다. 방금 행이나 열을 잘못 삭제했다면 즉시 Ctrl+Z로 되돌린다. 이미 되돌릴 수 없다면 수식 입력줄의 #REF! 위치를 확인하고, 이전 파일이나 버전에서 원래 참조를 찾아 수식을 다시 써야 한다.

4. 수식 대신 값만 붙었을 때

기본 Ctrl+C, Ctrl+V는 수식도 붙여넣지만, ‘값’ 붙여넣기는 계산된 결과만 남긴다. 대상 셀의 입력줄에 숫자만 있다면 원본 수식 셀을 다시 복사해 홈 → 붙여넣기 → 수식을 선택한다. 기존 셀 서식을 유지하고 수식만 옮길 때도 이 옵션이 맞다. 결과를 고정하려는 목적일 때만 ‘값’을 쓴다.

붙여넣기를 눌러도 대상 셀에 아무 변화가 없다면 앞의 계산·참조 문제와는 증상이 다르다. 먼저 작은 범위에서 ‘선택하여 붙여넣기 → 수식’이 되는지 시험한다. 이 방법이 작동해도 수식에 연결된 셀 주소가 새 위치에서 의도대로 바뀌었는지는 입력줄에서 확인해야 한다.