[Excel] 엑셀 수식 오류 찾기 및 수정

---

 

[Excel] 엑셀 수식 오류 찾기

---

때로는 수식을 통해 반환되는 결과가 의도와는 다른 것일 수도 있고 심지어 오류 값이 결과로 반환될 수도 있습니다.

아래에서 설명하는 몇 가지 도구를 사용하면 이러한 오류를 일으킨 원인을 찾아 조사하고 해결 방법을 모색할 수 있습니다.

 

우선 수식 입력 방법에 대해 알아 보겠습니다. 

정확한 수식 입력 방법을 알아야 오류를 발견할 수 있고 수정할 수 있습니다.

 

수식 입력 방법

수식은 워크시트의 값에 대해 계산을 실행하는 방정식입니다. 수식은 등호(=)로 시작합니다. 예를 들어 다음은 3과 1을 더하는 수식입니다.

=3+1

수식에는 함수, 참조, 연산자, 상수 중 일부 또는 모두가 포함될 수 있습니다.

엑셀 수식 설명

수식의 각 부분

1. 함수: Excel함수는 특정 계산을 수행하는 엔지니어링된 수식입니다. PI() 함수는 pi 값, 즉 3.142...를 반환합니다.
2. 참조: 개별 셀이나 셀 범위를 참조합니다. A2는 셀 A2의 값을 반환합니다.
3. 상수: 수식에 직접 입력한 숫자 또는 텍스트 값(예: 2)입니다.
4. 연산자: ^(캐럿) 연산자는 숫자의 거듭제곱을 구하고 *(별표) 연산자는 숫자를 곱합니다. + 및 –를 사용하여 값을 더하거나 빼고 /를 사용하여 나눕니다.
5. 참고: 일부 함수에는 인수라는 항목이 필요합니다. 인수는 특정 함수가 계산을 수행하는 데 사용하는 값입니다. 필요할 경우 인수가 함수의 괄호 () 사이에 배치됩니다. PI 함수는 비어 있기 때문에 인수가 필요하지 않습니다. 일부 함수에는 하나 이상의 인수가 필요하며 인수를 추가할 공간을 남겨 놓을 수 있습니다. 인수를 구분하는 데 쉼표를 사용하거나 위치 설정에 따라 세미콜론(;)을 사용해야 합니다.

예를 들어 SUM 함수에는 인수가 하나만 필요하지만 총 255개의 인수를 사용할 수 있습니다.

엑셀 sum 함수

=SUM(A1:A10)은 단일 인수의 예입니다.

=SUM(A1:A10, C1:C10)은 복수 인수의 예입니다.

 

그럼 위와 같이 수식을 작성할 때 발생되는 오류에 대해 말씀드리겠습니다.

 

수식을 입력할 때 주로 발생하는 오류 

확인 사항	추가 정보
모든 함수가 등호(=)로 시작하는지 확인	등호를 빠뜨리면 입력하는 내용이 텍스트나 날짜로 표시될 수 있습니다. 예를 들어 SUM(A1:A10)을 입력하면 Excel에서 SUM(A1:A10)이라는 텍스트 문자열이 표시되고 계산은 실행되지 않습니다. 11/2를 입력하면 Excel11을 2로 나누는 대신 11월 02일이라는 날짜가 표시됩니다(셀 서식이 일반인 경우).
모든 괄호 짝 맞추기	모든 괄호의 짝(여는 괄호와 닫는 괄호)이 맞는지 확인합니다. 수식에 함수를 사용하는 경우 함수가 제대로 작동하려면 각 괄호가 올바른 위치에 있어야 합니다. 예를 들어 수식 =IF(B5<0),"올바르지 않음",B5*1.05)는 괄호가 하나씩만 있어야 하는데 닫는 괄호가 두 개이고 여는 괄호가 하나뿐이므로 작동하지 않습니다. 이 수식은 =IF(B5<0,"올바르지 않음",B5*1.05)와 같이 표시되어야 합니다.
콜론을 사용하여 범위 표시	셀 범위를 참조 하는 경우에 콜론 (:)을 사용 하 여 구분 하는 첫 번째에 대 한 참조 셀 범위와 참조의 마지막 범위에서 셀 합니다. 예: =SUM(A1:A5), 하지 = SUM (A1 A5)는 #NULL을 반환 합니다. 오류가 발생 했습니다.
필요한 모든 인수 입력	일부 함수의 경우 반드시 입력해야 하는 인수가 있습니다. 또한 인수를 너무 많이 입력하지 않도록 주의합니다.
올바른 유형의 인수 입력	SUM 등의 일부 함수에는 숫자 인수가 필요합니다. REPLACE 등의 다른 함수에는 해당 인수 중 적어도 한 개 이상이 텍스트 값이어야 합니다. 잘못된 유형의 데이터를 인수로 사용하면 Excel의도하지 않은 결과가 반환되거나 오류가 표시될 수 있습니다.
64개까지만 함수 중첩 가능	함수 안에 중첩하여 입력할 수 있는 함수의 수는 64개까지로 제한됩니다.
다른 시트 이름을 작은따옴표로 묶기	수식에서 다른 워크시트나 통합 문서의 값 또는 셀을 참조하는 경우 해당 워크시트나 통합 문서의 이름에 공백이나 알파벳 이외의 문자가 들어 있으면 이름을 작은따옴표(')로 묶어야 합니다(예: ='Quarterly Data'!D3 또는 =‘123’!A1).
수식에서 참조할 워크시트 이름 뒤에 느낌표(!) 배치	예를 들어 같은 통합 문서에 있는 "분기별 데이터" 워크시트의 셀 D3에서 값을 반환하려면 ='분기별 데이터'!D3 수식을 사용합니다.
외부 통합 문서의 경로 포함	각 외부 참조에 통합 문서의 이름과 경로가 포함되어야 합니다. 통합 문서에 대 한 참조 통합 문서의 이름을 포함 한 대괄호 ([Workbookname.xlsx])로 묶어야 합니다. 참조가 통합 문서에서 워크시트의 이름을 있어야 합니다. 참조할 통합 문서가 Excel에서 열려 있지 않은 경우에도 해당 통합 문서에 대한 참조를 수식에 포함할 수 있습니다. 예를 들어 =ROWS('C:\내 문서\[Q2 Operations.xlsx]판매량'!A1:A8)처럼 파일의 전체 경로를 입력하면 됩니다. 이 수식은 다른 통합 문서의 셀 A1-A8을 포함하는 범위의 행 수를 반환합니다(8). 참고: 전체 경로에 공백 문자가 들어 있으면 위의 예와 같이 경로의 시작 부분부터 워크시트의 이름 뒤 느낌표 앞까지에 해당하는 경로를 작은따옴표로 묶어야 합니다.
서식 없이 숫자 입력	수식에서 숫자를 입력할 때는 숫자에 서식을 지정하지 않습니다. 예를 들어 입력할 값이 \1,000인 경우 수식에는 1000을 입력합니다. 숫자의 일부로 입력된 쉼표는 Excel에서 구분 문자로 인식됩니다. 천 단위 또는 백만 단위 구분 기호나 통화 기호와 함께 숫자를 표시하려면 숫자를 입력한 후에 셀 서식을 지정합니다. 예를 들어 셀 A3의 값에 3100을 더하기 위해 수식 =SUM(3,100,A3)을 입력한 경우 Excel에서는 =SUM(3100,A3)의 경우처럼 3100과 A3이 더해지지 않고 숫자 3과 100을 더한 합계에 A3 값이 더해집니다. 또는 수식 =ABS(-2,134)를 입력한 경우 ABS 함수에는 하나의 인수만 사용할 수 있으므로(=ABS(-2134)) Excel에서 오류가 표시됩니다.

 

일반적인 수식 오류를 하나씩 수정하는 방법

1. 오류를 검사할 워크시트를 선택합니다.
2. 워크시트가 수동으로 계산된 경우 F9 키를 눌러 다시 계산합니다.
3. 오류 검사 대화 상자가 표시되지 않으면 수식 탭 > 수식 분석 > 오류 검사 단추를 클릭합니다.
4. 이전에 오류를 무시한 적이 있는 경우 파일 > 옵션 > 수식을 클릭하여 해당 오류를 다시 검사할 수 있습니다.
5. 오류 검사 섹션에서 무시한 오류를 원래대로 > 확인을 클릭합니다.

   

   오류 검사

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

   

   일반적인 오류 검사

6. 팁: 오류 검사 대화 상자를 수식 입력줄 바로 아래로 이동하면 도움이 됩니다.
7. 대화 상자의 오른쪽에 있는 실행 단추 중 하나를 클릭합니다. 실행할 수 있는 작업은 각 오류 유형에 따라 다릅니다.
8. 다음을 클릭합니다.

참고: 오류 무시를 클릭하면 연속된 검사마다 해당 오류가 무시되는 것으로 표시됩니다.

 

오류 값 수정

엑셀에서 오류 값, 수식 결과 계산이 제대로 될 수 없는 경우 ####, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!,  #VALUE! 으로 표시 됩니다. 각 오류에 대한 수정 방법에 대해 설명해 드리겠습니다.

항목	설명
#### 오류 수정	Excel에서 열 너비가 좁아 셀의 일부 문자를 표시할 수 없거나 셀에 음수로 된 날짜 또는 시간 값이 포함된 경우 이 오류가 표시됩니다. 예를 들어 =06/15/2008-07/01/2008과 같이 과거의 날짜에서 미래의 날짜를 빼는 수식은 음수 날짜 값을 반환합니다.
#DIV/0! 오류 수정	Excel에서 값이 포함되지 않은 셀이나 영(0)으로 숫자를 나누면 이 오류가 표시됩니다.
#N/A 오류 수정	Excel에서 함수나 수식에 값을 사용할 수 없는 경우 이 오류가 표시됩니다. VLOOKUP 같은 함수를 사용 중인 경우 조회하는 내용과 일치하는 항목이 조회 범위에 없는 경우 이 오류가 표시됩니다. #N/A를 표시하지 않으려면 IFERROR를 사용하십시오. 이 경우 다음과 같이 사용할 수 있습니다. =IFERROR(VLOOKUP(D2,$D$6:$E$8,2,TRUE),0)
#NAME? 오류 수정	Excel에서 수식의 텍스트를 인식할 수 없는 경우 이 오류가 표시됩니다.  예를 들어 범위 이름이나 함수 이름을 잘못 입력한 경우가 여기에 해당됩니다. 함수를 사용 중인 경우 함수 이름의 철자가 올바른지 확인합니다.
#NULL! 오류 수정	Excel에서 교차하지 않는 두 영역의 논리곱을 지정한 경우 이 오류가 표시됩니다. 논리곱 연산자는 수식에서 참조를 구분하는 공백 문자입니다.
#NUM! 오류 수정	Excel에서 수식이나 함수에 잘못된 숫자 값이 포함되어 있으면 이 오류가 표시됩니다. IRR 또는 RATE와 같이 반복되는 함수를 사용할 때, 함수가 결과를 찾을 수 없는 경우 #NUM! 오류가 발생할 수 있습니다.
#REF! 오류 수정	셀 참조가 유효하지 않은 경우 이 오류가 표시됩니다. 예를 들어 다른 수식에서 참조하는 셀을 삭제하였거나 참조 값이 분명하지 않거나 참조 값이 있는 행 또는 열을 삭제했을 때 발생합니다.
#VALUE! 오류 수정	수식에 여러 가지 데이터 형식이 있는 셀이 포함되면 Excel에서 이 오류가 표시될 수 있습니다. 수학 연산자 +, -, *, /, ^ 와 같이 여러 가지 데이터 형식과 함께 사용하고 있는 경우 발생할 수 있습니다. 이 경우에는 함수를 사용하십시오. 예를 들어, =SUM(F2:F5)를 사용하면 문제가 해결됩니다.