엑셀(EXCEL) 만능 연산함수 AGGREGATE로 오류, 숨긴값, 부분 합 무시하고 계산 (합계, 곱, 분산,표준편차, 크고, 작고, 사분위와 배분위수까지 하나의 함수로 계산)

엑셀로 다양한 수식을 만들다보면 다양한 상황이 있습니다.

중간에 오류가 있을 수도 있고, 겹치는 부분을 여러번 계산할 때도 있습니다.

또 중간에 숨기거나, 오류, 빈칸이 있을 수도 있는데 이걸 선택할 수 있는 함수 AGGREGATE를 알아보겠습니다.

목차

1. AGGREGATE 함수

2. function_num : 연산 결정하기

3. Options : 계산 방법 결정하기

4. array와 k : 계산할 데이터 입력하기

5. 함수의 사용 예시

1. AGGREGATE 함수

AGGREGATE(function_num, options, array, [k]) : 목록, 데이터의 정해진 연산을 합니다. 어떤 계산을 할지와 오류나 숨긴값을 계산 할지를 선택할 수 있습니다.

• function_num : 어떤 함수를 사용할지 지정하는 1에서 19 사이의 숫자입니다. 다른 계산을 수행합니다.
  (아래 설명 참고하세요)
• Options : 함수의 계산 범위에서 무시할 값을 결정하는 0~7 사이의 숫자 값입니다.
  (아래 설명 참고하세요)
• array : 계산할 숫자 혹은 데이터의 범위 또는 배열입니다.
• [k] : 함수에서 요구하는 위치값입니다.

AGGREGATE는 다른 함수의 기능을 사용할 수 있습니다.

사용법이 다양하기 때문에 함수의 인수들을 알아 보겠습니다.

2. function_num : 연산 결정하기

어떤 계산을 할지 결정합니다. 

다양한 계산을 할 수 있고 선택하면 대상 함수와 똑같은 계산을 수행합니다.

어지간한 연산은 이 함수 하나로 다 할 수 있습니다.

다 외우기 힘드니 AGGREGATE 함수를 입력하면 목록이 표시되도록 되어 있습니다.

3. Options : 계산 방법 결정하기

오류값이나 숨긴 값을 어떻게 할 지 선택할 수 있습니다.

무시하거나 계산에 포함하는 것인데 오류값을 계산에 포함하면 오류가 납니다.

오류는 무조건 무시해야 하는거 아니겠냐 할 수 있습니다.

큰 값들을 계산할때 중간에 오류 무시를 하면 문제가 있어도 몰라 차라리 오류를 확인하는게 좋을 때가 있습니다.

option 역시 목록을 표시해서 사용하기 쉽도록 알려줍니다.

숨겨진 값을 무시하거나 중첩된 값을 무시하는 것은 매우 편리합니다.

4. array와 k : 계산할 데이터 입력하기

범위를 입력하는 칸인데 여기서는 함수가 복잡한 만큼 여기도 주의사항이 있습니다.

함수들 중 선택인수가 있는 함수가 있습니다.

• k가 필요한 함수 : LARGE(array,k), SMALL(array,k), PERCENTILE.INC(array,k), QUARTILE.INC(array,quart), PERCENTILE.EXC(array,k), QUARTILE.EXC(array,quart) 

이 경우 공통적으로 k가 그 인수를 대신해 줍니다.

다만 이때는 인수를 AGGREGATE(function_num, options, ref1, [ref2], …) 방식으로 여러개 입력을 못합니다.

예를 들어서 

AGGREGATE(9,0,A1,A2,A3 ... ) : A1, A2, A3외의 값의 합(9)을 구합니다.

라고 입력할 경우 function_num이 9번 sum이면 문제가 없지만 14번 LARGE면 k값을 알 수가 없습니다.

이 함수들은 A1:C10 처럼 Array 양식으로 입력해야 합니다.

5. 함수의 사용 예시

아래 함수를 보면 옵션에 따른 결과 값을 출력할 수 있습니다.

• 4 : 아무것도 무시하지 않음으로 있는 함수를 모두다 구합니다.
• 0 : 범위에 같은 AGGREGATE나 SUBTOTAL 함수로 계산한 값을 무시합니다.
• 1 : 중첩되거나 숨겨진 행은 무시하지만, NA 오류는 무시하지 못해서 결과도 오류가 출력됩니다.
• 3 : 오류와 중첩을 모두 무시하고 계산하고 목록에 오류가 있어서 정상적으로 계산됩니다.

분명하게 AGGREGATE는 만능함수입니다.

하지만 말과 사용법이 어려워서 그런지 다른 함수들을 필요할 때 하나씩 배우게 됩니다.

그래서 사용하는 경우를 본적이 거의 없습니다.

물론 익숙해지면 편하기는 하지만 원하는 함수를 선택해서 사용합시다.

부분합을 구하는 일을 많이 하신다면 여기에 익숙해지는 것도 좋습니다.