엑셀을 사용하여 데이터 분석을 하다 보면 복잡한 집계 작업을 간편하게 처리해야 하는 상황이 자주 발생합니다. 이때 유용하게 사용할 수 있는 함수가 바로 AGGREGATE입니다. AGGREGATE 함수는 여러 집계 함수를 한데 모아 제공하며, 오류 무시와 숨겨진 셀 무시 등 다양한 옵션을 제공합니다. 이번 칼럼에서는 실무에서 바로 활용할 수 있는 AGGREGATE 함수의 활용꿀팁 10가지를 소개합니다.
1. AGGREGATE 함수의 기본 개념 이해하기
AGGREGATE 함수는 다양한 집계 작업을 수행할 수 있는 강력한 함수입니다. 기본 구문은 다음과 같습니다:
=AGGREGATE(function_num, options, array, [k])
function_num: 사용할 함수의 번호 (예: AVERAGE는 1, COUNT는 2 등).options: 오류 무시 또는 숨겨진 셀 무시 여부를 지정하는 옵션.array: 데이터를 포함하는 범위.[k]: 함수에 따라 필요한 추가 인수.
2. 평균값 구하기 (오류 무시)
데이터 집계 중 오류가 포함되어 있을 때 평균값을 구하는 방법입니다.
=AGGREGATE(1, 6, A1:A10)
여기서 1은 AVERAGE 함수를 의미하며, 6은 오류를 무시하고 숨겨진 셀을 포함하라는 옵션입니다.
3. 숨겨진 셀 무시하고 합계 구하기 활용꿀팁
필터링된 데이터를 합계할 때 숨겨진 셀을 무시하는 방법입니다.
=AGGREGATE(9, 3, A1:A10)
여기서 9는 SUM 함수를 의미하며, 3은 오류와 숨겨진 셀을 무시하라는 옵션입니다.
4. 조건에 맞는 최대값 찾기 활용꿀팁
조건에 맞는 최대값을 찾을 때 AGGREGATE 함수를 사용하여 오류를 무시할 수 있습니다.
=AGGREGATE(14, 6, A1:A10/(B1:B10="조건"))
여기서 14는 LARGE 함수를 의미합니다.
5. 조건에 맞는 최소값 찾기 활용꿀팁
최소값을 구할 때도 AGGREGATE 함수는 유용합니다.
=AGGREGATE(15, 6, A1:A10/(B1:B10="조건"))
여기서 15는 SMALL 함수를 의미합니다.
6. 데이터를 필터링하여 고유값 개수 세기
필터링된 데이터에서 고유값의 개수를 세는 방법입니다.
=AGGREGATE(3, 7, A1:A10)
여기서 3은 COUNTA 함수를 의미하며, 7은 오류와 숨겨진 셀을 모두 무시하라는 옵션입니다.
7. 순위 구하기
특정 값의 순위를 구할 때도 AGGREGATE 함수를 사용할 수 있습니다.
=AGGREGATE(13, 6, A1:A10)
여기서 13은 RANK 함수를 의미합니다.
8. 백분위수 구하기
데이터의 특정 백분위수를 구하는 방법입니다.
=AGGREGATE(19, 6, A1:A10, 0.9)
여기서 19는 PERCENTILE.INC 함수를 의미하며, 0.9는 90번째 백분위수를 나타냅니다.
9. 중간값 구하기
데이터의 중간값을 구할 때 AGGREGATE 함수를 사용하면 간편합니다.
=AGGREGATE(12, 6, A1:A10)
여기서 12는 MEDIAN 함수를 의미합니다.
10. 데이터 필터링 및 오류 무시하여 합계 구하기
필터링된 데이터에서 오류를 무시하고 합계를 구하는 방법입니다.
=AGGREGATE(9, 7, A1:A10)
여기서 9는 SUM 함수를 의미하며, 7은 오류와 숨겨진 셀을 모두 무시하라는 옵션입니다.