엑셀 Excel 오류 값, 숨은 행 무시하고 숫자를 계산하고 싶을 때 사용하는 AGGREGATE 함수

 

환경: Microsoft Excel 2013

 

전체 데이터에서 숨겨진 행이나 빈 셀, 오류가 있는 셀을 무시하고 정상적인 데이터만 집계할 수 있는 방법이 있을 까요? 물론 에러 여부를 판단해서 수식을 만들 수도 있지만 복잡해 질 수 있기 때문에 비효율적입니다. 이 때 간단하게 사용할 수 있는 함수가 있는데 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를 입력해야 합니다.

엑셀 Excel 오류 값, 숨은 행 무시하고 숫자를 계산하고 싶을 때 사용하는 AGGREGATE 함수

 

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

엑셀 Excel 오류 값, 숨은 행 무시하고 숫자를 계산하고 싶을 때 사용하는 AGGREGATE 함수

 


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

엑셀 Excel 오류 값, 숨은 행 무시하고 숫자를 계산하고 싶을 때 사용하는 AGGREGATE 함수

 

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

엑셀 Excel 오류 값, 숨은 행 무시하고 숫자를 계산하고 싶을 때 사용하는 AGGREGATE 함수

 

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

엑셀 Excel 오류 값, 숨은 행 무시하고 숫자를 계산하고 싶을 때 사용하는 AGGREGATE 함수

 

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

엑셀 Excel 오류 값, 숨은 행 무시하고 숫자를 계산하고 싶을 때 사용하는 AGGREGATE 함수

 

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

엑셀 Excel 오류 값, 숨은 행 무시하고 숫자를 계산하고 싶을 때 사용하는 AGGREGATE 함수

Posted by 녹두장군

댓글을 달아 주세요

  1. 엘랑 2019.09.20 09:35  댓글주소  수정/삭제  댓글쓰기

    항상 감사드립니다 ㅠㅠㅠㅠ
    유학생이라 수업시간때 모르는점 투성이에요,,
    감사드려용 <3