엑셀 Excel 오류 값, AGGREGATE 함수 숨은 행 무시하고 숫자를 계산

전체 데이터에서 숨겨진 행이나 빈 셀, 오류가 있는 셀을 무시하고 정상적인 데이터만 집계할 수 있는 방법이 있을 까요? 물론 에러 여부를 판단해서 수식을 만들 수도 있지만 복잡해 질 수 있기 때문에 비효율적입니다. 이 때 간단하게 사용할 수 있는 함수가 있는데 AGGREGATE 가 그것입니다. 이 함수는 옵션에 따라 계산에 사용될 함수와 무시할 조건들을 선택할 수 있습니다.

 

▼ AGGREGAGE 은 참조형과 배열형이 있는데 수식의 원형은 다음과 같습니다. 여기에서 function_num 과 options 은 공통적으로 들어갑니다. 

 

 

참조형 : AGGREGATE(function_num, options, ref1, [ref2], …) 배열형 : AGGREGATE(function_num, options, array, [k]) l  function_num : 사용할 함수를 지정하는 1~19 사이의 숫자를 입력한다. l  options : 무시할 값을 결정하는 숫자를 입력한다. l  ref1 : 집계 값을 구할 인수가 여러 개인 함수의 첫 번째 인수이다. l  array : 집계 값을 계산할 숫자 데이터의 범위 또는 배열을 입력한다.

 

 

그럼 첫 번째 인수로 들어가는 function_num 에 대해서 알아 보겠습니다. 사용할 수 있는 함수 종류는 19개가 있으며 번호가 부여되어 있습니다. 예를 들어 SUM 을 사용하려면 function_num 에 9를 입력해야 합니다.

 

▼ 다음은 두 번째 인수로 어떤 값을 제외시킬 것이지 정해야 합니다. 오류 값을 계산에서 제외시키려면 options 으로 6을 입력해야 합니다. 

 

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀(Excel) 셀에서 입력오류 메시지 종류 ▶ 엑셀 Excel 수식의 오류 검사와 추적하기로 에러를 쉽게 찾기 ▶ 엑셀 Excel 계산 오류가 발생했을 때 오류 표시(#DIV/0!) 없애기 ▶ 엑셀 Excel 시트에서 순환 참조 오류를 찾아 수정하기 ▶ 엑셀 Excel 오류 검사 규칙 재설정하기

 

 

 

▼ 이론적인 설명은 끝났습니다. 샘플을 통해서 직접 함수를 적용해 보겠습니다. 그림처럼 중간에 #DIV/0! 에러가 포함된 영역을 합치게 되면 정상적인 값이 나올 수 없겠죠. 에러를 제외한 숫자를 합치고 싶다면 AGGREGATE 함수를 적용해야 합니다. 

 

▼ 수식 편집기에서 AGGREGATE 함수의 첫 번째 인수 입력 단계가 되면 자동으로 함수를 선택할 수 있도록 목록을 제공해 줍니다. 합계를 구해야 하므로 9 를 선택합니다. 

 

▼ 두 번째 옵션도 첫 번째 인수처럼 선택 가능한 목록을 팝업창으로 제공해 줍니다. 오류가 있는 셀을 제외하고 합계를 구해야 되므로 6번을 선택합니다. 

 

▼ 마지막으로 합계를 구할 영역을 지정합니다. 

 

▼ AGGREGATE 함수를 적용한 결과는 다음과 같습니다. #DIV/0! 에러가 난 셀은 제외하고 20, 30 을 더한 50 이 나왔네요. 이 외에도 숨겨진 행, 중복 값이 있는 영역에도 많이 사용합니다. 

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀(Excel) 셀에서 입력오류 메시지 종류 ▶ 엑셀 Excel 수식의 오류 검사와 추적하기로 에러를 쉽게 찾기 ▶ 엑셀 Excel 계산 오류가 발생했을 때 오류 표시(#DIV/0!) 없애기 ▶ 엑셀 Excel 시트에서 순환 참조 오류를 찾아 수정하기 ▶ 엑셀 Excel 오류 검사 규칙 재설정하기