수식에서 오류 감지

다음 표에서는 사용자가 수식을 입력할 때 가장 일반적으로 저지르는 실수 몇 가지와 그 오류를 수정하는 방법을 요약하여 설명합니다.

수식에서 오류를 검사하는 특정 규칙을 구현할 수 있습니다. 이러한 규칙을 사용한다고 해서 워크시트에서 오류를 완전히 없앨 수 있는 것은 아니지만 흔히 저지르는 실수를 찾는 데 도움이 될 수 있습니다. 이러한 규칙은 필요에 따라 개별적으로 설정하거나 해제할 수 있습니다.

오류를 표시하고 수정하는 데는 두 가지 방법이 있습니다. 맞춤법 검사기처럼 오류를 한 번에 한 개씩 표시 및 수정하거나, 데이터를 입력하면서 워크시트에서 오류가 나타날 때마다 이를 즉시 표시하고 수정할 수 있습니다.

Excel에서 표시하는 옵션을 사용하여 오류를 resolve 오류 무시를 선택하여 오류를 무시할 수 있습니다. 특정 셀에서 오류를 무시하면 이후에 오류를 검사할 때 해당 셀에는 더 이상 오류가 표시되지 않습니다. 그러나 이전에 무시했던 오류가 모두 다시 나타나도록 설정을 되돌릴 수도 있습니다.

1. Windows의 Excel의 경우 파일 > 옵션 > 수식으로 이동하거나
   Mac용 Excel의 경우 Excel 메뉴 > 기본 설정 > 오류 검사를 선택합니다. 

   Excel 2007 Excel 옵션 > 수식을 > Microsoft Office 단추를 선택합니다.

2. 오류 검사에서 백그라운드 오류 검사 사용을 선택합니다. 발견된 오류는 셀의 왼쪽 위 모서리에 삼각형으로 표시됩니다.

   

3. 오류가 발생한 위치를 표시하는 삼각형의 색을 변경하려면 오류 표시 색 상자에서 원하는 색을 선택합니다.

4. 오류 검사 규칙 아래에서 다음 규칙의 확인란을 필요에 따라 선택하거나 선택 취소합니다.

   • 오류가 발생하는 수식이 포함된 셀: 수식은 예상 구문, 인수 또는 데이터 형식을 사용하지 않습니다. 오류 값으로는 #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!가 있습니다. 이러한 각 오류 값은 서로 다른 원인을 가지며 다른 방법으로 해결됩니다.

     참고: 셀에 직접 오류 값을 입력하면 해당 오류 값으로 저장되지만 오류로 표시되지는 않습니다. 그러나 다른 셀의 수식에서 해당 셀을 참조하는 경우 이 수식은 해당 셀의 오류 값을 반환합니다.

   • 한 표에서 다른 계산된 열 수식이 사용된 셀 표시: 계산된 열에 포함된 개별 수식이 마스터 열 수식과 달라서 예외가 발생할 수 있습니다. 계산된 열 예외는 다음 중 하나를 실행할 때 발생합니다.

     • 계산된 열 셀에 수식 이외의 데이터를 입력한 경우

     • 계산 열 셀에 수식을 입력한 다음 Ctrl +Z를 사용하거나 빠른 실행 도구 모음에서 실행 취소를 선택합니다.

     • 이미 하나 이상의 예외가 들어 있는 계산된 열에 새 수식을 입력한 경우

     • 계산된 열에 계산된 열 수식과 일치하지 않는 데이터를 복사한 경우 복사한 데이터에 수식이 있을 경우 이 수식은 계산된 열의 데이터를 덮어씁니다.

     • 계산된 열의 행 중 하나에서 참조하는 다른 워크시트 영역의 셀을 이동하거나 삭제한 경우

   • 2자리로 표시된 연도가 있는 셀: 수식에서 잘못된 연도로 인식될 수 있는 텍스트 날짜가 셀에 들어 있습니다. 예를 들면 수식 =YEAR("1/1/31")의 날짜는 1931년이 될 수도 있고 2031년이 될 수도 있습니다. 이 규칙을 사용하여 불확실한 텍스트 날짜를 검사합니다.

   • 앞에 아포스트로피가 있거나 텍스트로 서식이 지정된 숫자: 텍스트로 저장된 숫자가 셀에 들어 있습니다. 이러한 숫자는 대개 다른 원본에서 가져온 데이터로부터 만들어진 것입니다. 텍스트로 저장된 숫자는 예기치 않은 정렬 결과를 유발할 수 있으므로 숫자로 변환하는 것이 가장 좋습니다. 텍스트로 ‘=SUM(A1:A10)이 표시됩니다.

   • 영역의 다른 수식과 일치하지 않는 수식: 수식이 그 근처의 다른 수식 패턴과 일치하지 않습니다. 대부분의 경우 다른 수식에 인접한 수식은 사용된 참조에서만 다릅니다. 인접한 수식 4개 예제에서 인접한 수식이 한 행씩 증가하며 한 행이 8행씩 증가하므로 Excel에서는 수식 =SUM(A4:C4)이 예상되므로 셀 D4의 수식 =SUM(A10:C10) 옆에 오류가 표시됩니다.

     

     수식에 사용된 참조가 인접한 수식의 참조와 일치하지 않으면 오류가 표시됩니다.

   • 수식에 사용된 영역에 누락된 셀 있음 표시: 데이터의 원래 범위와 수식이 들어 있는 셀 사이에 데이터를 삽입하면 새로 삽입한 데이터에 대한 참조가 수식에 자동으로 포함되지 않을 수 있습니다. 이 규칙을 사용하면 수식이 들어 있는 셀에 인접한 셀의 실제 범위를 대상으로 수식의 참조를 비교할 수 있습니다. 인접한 셀에 값이 추가로 포함되어 있고 셀이 비어 있지 않으면 수식 옆에 오류가 표시됩니다.

     예를 들어 셀 D5, D6 및 D7은 수식에 참조된 셀과 수식(D8)이 포함된 셀에 인접하고 해당 셀에는 수식에서 참조해야 하는 데이터가 포함되어 있으므로 이 규칙이 적용될 때 Excel에서 = SUM(D2:D4) 옆에 오류를 삽입합니다.

     

   • 수식을 포함한 셀 잠그지 않음 보호할 수식이 잠겨 있지 않습니다. 기본적으로 워크시트가 보호될 때는 셀을 변경할 수 없도록 워크시트의 모든 셀이 잠겨 있습니다. 이렇게 하면 우연히 수식을 삭제하거나 변경하는 등의 우발적인 실수를 방지할 수 있습니다. 이 오류는 셀이 잠금 해제되도록 설정되었지만 시트가 보호되지 않았음을 나타냅니다. 셀을 잠그지 않도록 해야 합니다.

   • 빈 셀을 참조하는 수식 사용: 빈 셀에 대한 참조가 수식에 포함되어 있습니다. 다음 예제에서와 같이 의도하지 않았던 결과로 이러한 문제가 발생할 수 있습니다.

     다음과 같은 셀로 이루어진 열의 숫자 값 평균을 계산하려는 경우를 생각해 봅시다. 세 번째 셀이 비어 있으면 계산에 포함되지 않으며 결과는 22.75입니다. 세 번째 셀에 0이 들어 있으면 결과는 18.2가 됩니다.

     

   • 표 데이터 유효성 오류: 표에 유효성 검사 오류가 있습니다. 데이터 탭 > 데이터 도구 그룹 > 데이터 유효성 검사로 이동하여 셀의 유효성 검사 설정을 확인합니다.

1. 오류를 검사할 워크시트를 선택합니다.

2. 워크시트가 수동으로 계산된 경우 F9 키를 눌러 다시 계산합니다.

   오류 검사 대화 상자가 표시되지 않으면 수식 > 수식 감사 > 오류 검사를 선택합니다.

3. 이전에 오류를 무시한 경우 파일 > 옵션 > 수식으로 이동하여 해당 오류를 다시 검사 수 있습니다. Mac용 Excel의 경우 Excel 메뉴 > 기본 설정 > 오류 검사를 선택합니다.

   오류 검사 섹션에서 무시된 오류 재설정 > 확인을 선택합니다.

   

   참고: 무시했던 오류를 다시 검사하도록 설정하면 현재 통합 문서의 모든 시트에서 모든 오류가 재설정됩니다.

   팁:  오류 검사 대화 상자를 수식 입력줄 바로 아래로 이동하면 도움이 됩니다.

   

4. 대화 상자의 오른쪽에 있는 작업 단추 중 하나를 선택합니다. 실행할 수 있는 작업은 각 오류 유형에 따라 다릅니다.

5. 다음을 선택합니다.

1. 셀 옆에 있는 오류 검사 를 선택한 다음 원하는 옵션을 선택합니다. 사용할 수 있는 명령은 오류 유형에 따라 각각 다르며 첫 번째 항목에는 오류에 대한 설명이 표시됩니다.

   오류 무시를 선택하면 각 연속 검사 대해 오류가 무시됨으로 표시됩니다.

   

수식이 결과를 올바르게 평가할 수 없는 경우 Excel에서는 #####, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!과 같은 오류 값을 표시합니다. 각 오류 유형에는 다른 원인과 다른 솔루션이 있습니다.

다음 표에는 이러한 오류를 자세히 설명하는 문서에 대한 링크와 시작하는 방법에 대한 간략한 설명이 나와 있습니다.

항목	설명
#### 오류 수정	Excel에서 열 너비가 좁아 셀의 일부 문자를 표시할 수 없거나 셀에 음수로 된 날짜 또는 시간 값이 포함된 경우 이 오류가 표시됩니다. 예를 들어 =06/15/2008-07/01/2008과 같이 과거의 날짜에서 미래의 날짜를 빼는 수식은 음수 날짜 값을 반환합니다. 팁: 열 머리글 사이를 클릭해서 셀을 자동 맞춤해 보세요. Excel에서 모든 문자를 표시할 수 없으므로 ###이 표시되면 수정됩니다.
#DIV/0! 오류 수정	Excel에서 값이 포함되지 않은 셀이나 영(0)으로 숫자를 나누면 이 오류가 표시됩니다. 팁: 다음 예제와 같이 오류 처리기(=IF(C2,B2/C2,0))를 추가합니다.
#N/A 오류 수정	Excel에서 함수나 수식에 값을 사용할 수 없는 경우 이 오류가 표시됩니다. VLOOKUP 같은 함수를 사용 중인 경우 조회하는 내용과 일치하는 항목이 조회 범위에 있나요? 대부분은 그렇지 않습니다. #N/A를 표시하지 않으려면 IFERROR를 사용해 보세요. 이 경우 다음을 사용할 수 있습니다. =IFERROR(VLOOKUP(D2,$D$6:$E$8,2,TRUE),0)
#NAME? 오류 수정	Excel에서 수식의 텍스트를 인식할 수 없는 경우 이 오류가 표시됩니다. 예를 들어 범위 이름 또는 함수 이름을 잘못 입력할 수 있습니다. 참고: 함수를 사용 중인 경우 함수 이름의 철자가 올바른지 확인합니다. 이 경우 SUM의 철자가 잘못되었습니다. "e"를 제거하고 Excel에서 수정합니다.
#NULL! 오류 수정	Excel에서 교차하지 않는 두 영역의 논리곱을 지정한 경우 이 오류가 표시됩니다. 논리곱 연산자는 수식에서 참조를 구분하는 공백 문자입니다. 참고: 범위가 올바르게 구분되어 있는지 확인 - 영역 C2:C3 및 E4:E6은 교차하지 않으므로 수식 =SUM(C2:C3 E4:E6)을 입력하면 #NULL! 오류가 반환됩니다. C 범위와 E 범위 사이에 쉼표가 있으면 =SUM(C2:C3,E4:E6)이 수정됩니다.
#NUM! 오류 수정	Excel에서 수식이나 함수에 잘못된 숫자 값이 포함되어 있으면 이 오류가 표시됩니다. IRR 또는 RATE와 같이 반복되는 함수를 사용하고 있나요? 그렇다면 함수가 결과를 찾을 수 없는 경우 #NUM! 오류가 발생할 수 있습니다. 해결 단계에 대한 도움말 항목을 참조하세요.
#REF! 오류 수정	셀 참조가 유효하지 않은 경우 이 오류가 표시됩니다. 예를 들어 다른 수식에서 참조한 셀을 삭제했거나 다른 수식에서 참조한 셀 위에 이동한 셀을 붙여넣었을 수 있습니다. 우연히 행 또는 열을 삭제했나요? 수식 =SUM(A2,B2,C2)에서 열 B를 삭제한 경우 어떻게 되는지 확인하세요. 실행 취소(Ctrl+Z)를 사용하여 삭제를 실행 취소하고 수식을 다시 작성하거나, 열 B가 삭제되었을 때 자동으로 업데이트되는 =SUM(A2:C2)와 같은 연속 범위 참조를 사용합니다.
#VALUE! 오류 수정	수식에 여러 가지 데이터 형식이 있는 셀이 포함되면 Excel에서 이 오류가 표시될 수 있습니다. 수학 연산자(+, -, *, /, ^)를 여러 가지 데이터 형식과 함께 사용하고 있나요? 그렇다면 함수를 사용해 보세요. 이 경우 =SUM(F2:F5)를 사용하면 문제가 해결됩니다.

워크시트에 셀이 표시되지 않으면 조사식 창 도구 모음에서 해당 셀과 해당 수식을 watch 수 있습니다. 워크시트가 큰 경우 조사식 창을 사용하여 수식 계산과 결과를 편리하게 검사, 감사 또는 확인할 수 있습니다. 조사식 창을 사용하면 워크시트에서 반복적으로 스크롤하거나 다른 부분으로 이동하지 않아도 됩니다.

이 도구 모음은 다른 도구 모음처럼 이동하거나 도킹할 수 있습니다. 예를 들어 창 아래에 도구 모음을 고정할 수 있습니다. 도구 모음은 1) 통합 문서, 2) 시트, 3) 이름(셀에 해당하는 명명된 범위가 있는 경우), 4) 셀 주소, 5) 값 및 6) 수식을 추적합니다.

조사식 창에 셀 추가

1. 조사할 셀을 선택합니다.

   수식이 있는 워크시트의 모든 셀을 선택하려면 홈 > 편집 > & 찾기 선택 (또는 Mac에서 Ctrl+G 또는 Control+G 를 사용할 수 있습니다)> 특수 > 수식으로 이동을 선택합니다.

   

2. 수식 > 수식 감사 > 조사식 창을 선택합니다.

3. 조사식 추가를 선택합니다.

   

4. watch 셀을 모두 선택했는지 확인하고 추가를 선택합니다.

   

5. 조사식 창 열 너비를 변경하려면 열 제목의 오른쪽 경계선을 끕니다.

6. 조사식 창 도구 모음의 항목이 가리키는 셀을 표시하려면 항목을 두 번 클릭합니다.

   참고: 다른 통합 문서에 대한 외부 참조가 있는 셀은 해당 통합 문서가 열려 있는 경우에만 조사식 창 도구 모음에 표시됩니다.

조사식 창에서 셀 제거

1. 조사식 창 도구 모음이 표시되지 않으면 수식 > 수식 감사로 이동한 > 조사식 창을 선택합니다.

2. 제거할 셀을 선택합니다.

   여러 셀을 선택하려면 Ctrl 키를 누른 다음 셀을 선택합니다.

3. 조사식 삭제를 선택합니다.

   

중첩된 수식은 여러 중간 계산 단계와 논리 검사를 거치기 때문에 최종 결과가 어떻게 계산되는지 파악하기 어려울 수 있습니다. 그러나, 수식 계산 대화 상자를 사용하면 중첩된 수식의 여러 부분을 수식 계산 순서에 따라 살펴볼 수 있습니다. 예를 들어 =IF(AVERAGE(D2:D5)>50,SUM(E2:E5),0)수식은 다음 중간 결과를 볼 수 있는 시기를 더 쉽게 이해할 수 있습니다.

1. 계산할 셀을 선택합니다. 한 번에 하나의 셀만 계산할 수 있습니다.

2. 수식 > 수식 감사 > 수식 평가로 이동합니다.

3. 계산을 선택하여 밑줄이 그어진 참조 값을 검사합니다. 평가 결과는 기울임꼴로 표시됩니다.

   수식의 밑줄이 그은 부분이 다른 수식에 대한 참조인 경우 단계 별 실행을 선택하여 평가 상자에 다른 수식을 표시합니다. 나가기를 선택하면 이전 셀과 수식으로 돌아갑니다.

   참조가 수식에서 두 번째로 나타나는 경우나 수식이 별도의 통합 문서에 있는 셀을 참조하는 경우에는 들어가기 단추를 사용할 수 없습니다.

4. 수식의 각 부분이 평가될 때까지 평가를 계속 선택합니다.

5. 평가를 다시 보려면 다시 시작을 선택합니다.

6. 평가를 종료하려면 닫기를 선택합니다.