[태그:] 엑셀오류검사

수식이 많은 엑셀 시트에서 #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! 같은 오류도 표시되지 않는다. 오류가 난 셀을 먼저 선택해 원인을 살펴본 다음, 공통 원인이 확인된 수식만 함께 수정한다.

엑셀 셀 왼쪽 위의 초록색 삼각형을 없애려면 그 셀을 선택하고 옆에 나타나는 경고 단추에서 ‘오류 무시’를 누르면 된다. 경고가 여러 시트에 걸쳐 계속 나타난다면 Windows용 엑셀의 파일 → 옵션 → 수식에서 검사 규칙을 조정할 수 있다. 어느 쪽이든 먼저 경고 내용을 읽어 보는 게 좋다. 무시는 셀의 값이나 수식을 고치는 명령이 아니다.

1. 셀 하나 또는 선택한 범위에서 무시하기

초록색 삼각형이 있는 셀을 선택하면 옆에 경고 단추가 나타난다. 단추를 눌러 첫 항목에 적힌 경고 이유를 확인한 뒤, 현재 값이 의도한 것이라면 ‘오류 무시’를 선택한다. 선택한 범위에 같은 종류의 경고가 여러 개 있다면 범위를 먼저 선택하고 경고 메뉴에서 한꺼번에 무시할 수도 있다.

가령 ‘00123’이 상품 코드라면 앞의 0까지 값의 일부다. 이때 ‘숫자로 변환’을 누르면 123으로 바뀔 수 있으므로, 코드를 그대로 둘 셀에는 ‘오류 무시’가 맞다. 반대로 합계에 넣어야 할 금액이 텍스트로 저장돼 경고가 뜬 것이라면 ‘숫자로 변환’을 골라야 한다. 삼각형만 숨기면 계산에 영향을 주는 데이터 형식은 그대로 남는다.

2. 같은 경고가 계속 뜰 때 설정하기

Windows용 엑셀에서 파일 → 옵션 → 수식으로 들어간다. ‘오류 검사 규칙’ 아래에서 원치 않는 경고 종류의 체크만 해제하면 된다. 예를 들어 문자로 보관할 코드 때문에 ‘앞에 아포스트로피가 있거나 텍스트로 서식이 지정된 숫자’ 경고가 반복된다면 이 규칙을 끌 수 있다. 다만 이 선택은 다른 파일에서 계산해야 할 숫자가 텍스트로 들어와도 경고하지 않게 만든다.

모든 초록색 오류 표시를 끄려면 같은 화면의 ‘오류 검사’에서 ‘백그라운드 오류 검사 사용’ 체크를 해제하고 확인을 누른다. 이 옵션은 지금 보고 있는 시트만을 위한 설정이 아니라 내 엑셀 프로그램의 검사 설정이다. 파일을 다른 사람에게 보내도 상대방 엑셀의 표시 설정까지 바뀌지는 않는다.

Mac용 엑셀은 Excel 메뉴 → 기본 설정 → 오류 검사에서 백그라운드 오류 검사를 끌 수 있다.

3. 무시한 경고 다시 표시하기

검사를 다시 켜려면 앞에서 해제한 체크를 선택한다. 특정 셀에서 이미 ‘오류 무시’를 눌렀다면, Windows용 엑셀의 파일 → 옵션 → 수식 → 오류 검사에서 ‘무시된 오류 다시 설정’을 사용한다. 이 작업은 현재 통합 문서의 모든 시트에서 무시해 둔 오류를 다시 검사 대상으로 돌린다.

셀에 #VALUE!나 #REF! 같은 오류 값이 보인다면 삼각형을 무시해도 그 값은 사라지지 않는다. 그런 경우에는 수식이나 참조를 살펴야 한다. 오류 무시 설정은 경고 표시를 관리하는 기능이라는 점만 구별하면 된다.