함수 오류값 대체 및 제외하고 계산하는 방법

이번 포스팅에서는 함수 오류값 대체 및 제외하고 계산하는 방법 에 대해 설명하겠습니다.

대체하는 함수는 IFERROR 함수와 IFNA함수로 두 함수 모두 오류 값이 있을 때 지정한 값으로 반환하는 함수입니다. 오류값을 제외하고 계산할 때는 AGGREGATE 함수를 사용해야 합니다.

따라서 데이터 수정, 업데이트 등으로 인해 오류값이 나타날 때 일일이 오류를 지우는 것보다는 오류값을 다른 텍스트로 대체하는 것이 좋습니다.

함수 오류값 대체 및 제외하고 계산하는 방법 _IFERROR

함수값에 오류(#N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NULL!, #NAME?)가 발생하면 지정한 값으로 표시합니다.

- 오류를 다른 문자로 교체하면 수식에서 오류가 있는 셀은 제외하고 계산합니다.

=IFERROR(수식, 오류 대체값)

• 수식 : 오류가 있는지 검사합니다.
• 오류 대체값 : 오류가 있을 시 표시할 값입니다.

 

2023년-2024년 연봉 증감률의 오류값을 IFERROR함수를 사용하여 하이픈(-)으로 변경해 보겠습니다.

IFERROR함수입니다.

 

1. G2셀에 '=IFERROR((F2-E2)/F2, "-")'을 입력합니다.
2. (F2-E2)/F2에서 오류가 발생했을 시 하이픈(-)이 입력됩니다. 그렇기에 해당 값이 없는 '하하', '뉴진스'의 증감률 값이 하이픈으로 대체되었습니다.
   - 에스파와 이제훈의 연봉 증감률이 부럽습니다.

함수 오류값 대체 및 제외하고 계산하는 방법 _IFNA

#N/A 오류가 나타낸 경우에만 지정한 값을 표시합니다.

- 오류를 다른 문자로 교체하면 수식에서 오류가 있는 셀은 제외하고 계산합니다.

=IFNA(수식, #N/A오류 대체값)

 

IFNA함수를 사용하여 2023년 월급여에 #N/A값을 "미입사"로 대체해 보겠습니다.

IFNA함수입니다.

1. D열은 VLOOKUP함수를 사용하여 이름에 따른 월급여 데이터를 I2:J11셀에서 불러왔습니다.
2. D2셀에 '=IFNA(VLOOKUP($A2,$I$2:$J$11, 2, FALSE), "미입사")'을 입력합니다.
   - 기존 VLOOKUP함수에 IFNA함수와 "미입사"만 추가하였습니다.
   - '하하'와 '뉴진스'의 값은 미입사로 대체되었습니다.

함수 오류값 대체 및 제외하고 계산하는 방법 _AGGREGATE

함수 수식의 오류 값이나 숨겨진 행을 제외하고 계산합니다.

=AGGREGATE(함수 종류, 제외할 옵션, 범위)

• 함수 종류 :사용할 함수의 종류를 숫자로 나타냅니다. 함수를 입력하고 괄호까지 작성하면 자동으로 함수의 종류가 보입니다.
• 제외할 옵션 : 계산 범위에서 제외할 옵션입니다.
• 범위 : 참조할 범위입니다.

 

2023년 월급여의 평균값을 구하겠습니다.

만약 오류값이 있는 셀 범위에 AVERAGE 함수를 사용하면 D13셀처럼 #N/A 오류가 나타납니다. 이럴 때 본 함수를 사용합니다.

AGGREGATE함수입니다.

1. D12셀에 '=AGGREGATE(1,6, D2:D11)'을 입력합니다.
   - '1'은 VAERAGE 함수입니다.(하단 이미지 참고)
   - '6'은 오류값 무시입니다.(하단 이미지 참고)
   - D2:D11은 평균을 구할 범위입니다.
2. D12셀에 오류값을 제외한 나머지 숫자의 평균 3,951,875를 구하였습니다.

AGGREGATE함수의 인수입니다.

 

이상 함수 오류값 대체 및 제외하고 계산하는 방법 에 대해 설명하였습니다.

회사에서 숫자 데이터를 많이 사용하실 경우에는 반드시 알고 사용하는 것이 좋습니다.

감사합니다.