SUMIFS+INDIRECT 조합으로 동적 범위 집계하는 고급 기법
엑셀은 단순한 계산 기능을 넘어, 조건에 따라 유연하게 데이터를 집계하는 데 매우 강력한 도구입니다. 오늘은 그 중 고급 사용자들에게 유용한 SUMIFS 함수와 INDIRECT 함수를 조합하여 동적 범위를 생성하고 집계하는 기법을 다뤄보겠습니다.
SUMIFS 함수란?
SUMIFS 함수는 둘 이상의 조건에 맞는 셀 값을 합산하는 함수입니다. 기본 구문은 다음과 같습니다:
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
예시:
=SUMIFS(B2:B10, A2:A10, "사과", C2:C10, ">=2024-01-01")
이 공식은 A열에 '사과'가 있고 C열의 날짜가 2024년 이후인 행의 B열 값을 합산합니다.
INDIRECT 함수란?
INDIRECT 함수는 문자열로 된 주소를 실제 셀 참조로 변환하는 함수입니다. 이를 활용하면 셀 주소를 동적으로 변경할 수 있습니다.
=INDIRECT("A" & 2)
위 함수는 A2 셀을 참조하게 됩니다.
본격적인 조합: SUMIFS + INDIRECT
이제 두 함수를 조합하여 동적으로 변하는 범위에 조건을 걸고 값을 합산해봅시다. 아래는 기본적인 예입니다:
기본 예제:
=SUMIFS(INDIRECT("매출!B2:B100"), INDIRECT("매출!A2:A100"), "사과")
위 수식은 '매출' 시트의 A열에서 '사과' 조건을 만족하는 B열 값을 합산합니다. 하지만 무엇이 동적인가요?
동적 범위를 활용한 고급 기법
동적 범위를 구성하려면 사용자가 입력한 값을 범위에 반영해야 합니다. 예를 들어, 분석할 시트를 드롭다운을 통해 고르게 구성할 수 있습니다.
예제:
=SUMIFS(INDIRECT(A1 & "!B2:B100"), INDIRECT(A1 & "!A2:A100"), "사과")
여기서 A1 셀에는 '1월' 또는 '2월'과 같은 시트 이름이 들어갑니다.
이런 구조를 활용하면 사용자가 시트 이름, 범위, 조건까지 자유롭게 바꿀 수 있습니다.
실전 응용 사례
다음은 여러 시트에 흩어진 매출 데이터를 한꺼번에 집계하는 방법입니다.
구성:
- 시트1: 1월, 시트2: 2월, 시트3: 3월 ...
- 각 시트는 A열 - 제품명, B열 - 매출
- 요약 시트에 월별 매출 합계를 출력
수식:
=SUMIFS(INDIRECT("'" & B1 & "'!B2:B100"), INDIRECT("'" & B1 & "'!A2:A100"), A2)
| A열 (제품명) | B열 (시트명) | C열 (매출합계 계산 수식) |
|---|---|---|
| 사과 | 1월 | =SUMIFS(...) |
| 바나나 | 1월 | =SUMIFS(...) |
이 방식은 확장성과 자동화 측면에서 매우 효과적입니다.
INDIRECT 함수 사용 시 유의사항
1. 간접 참조는 성능에 영향을 줌
INDIRECT 함수는 빈번하게 사용되면 속도가 저하될 수 있습니다. 특히 대규모 데이터에서 성능 저하가 뚜렷합니다.
2. 참조 오류에 주의
시트 이름이나 범위를 잘못 입력하면 #REF! 오류가 발생할 수 있으므로, 입력 값에 대한 철저한 검증이 필요합니다.
3. 구조 변경에 민감함
행/열 삭제로 인해 참조 범위가 깨질 수 있으므로 통제된 환경에서 사용하는 것이 좋습니다.
SUMIFS+INDIRECT 조합의 장점
- 조건 기반으로 복수 범위 집계 가능
- 사용자 입력에 따라 동적으로 범위 설정 가능
- 매크로 작성 없이도 유사 자동화 구현 가능
단점 및 한계
- 구조가 복잡해져 가독성이 떨어질 수 있음
- 함수 내 문자열 입력 실수 시 오류 발생
- 내부적으로 처리 속도가 느려질 수 있음
결론
SUMIFS+INDIRECT 함수 조합은 고급 사용자에게 매우 강력한 도구입니다. 다양한 시트나 테이블에서 원하는 조건을 충족하는 데이터를 동적으로 집계할 수 있는 유연성이 그 핵심입니다. 하지만 그만큼 세심한 설계와 정확한 문법 이해가 요구됩니다.
정리하자면, 이 기법을 마스터하면 엑셀로 다룰 수 있는 업무 자동화의 수준이 한층 높아집니다. 관련 내용을 반복적으로 연습하면서 실무에 적용해보세요.
지금 당장 직접 SUMIFS+INDIRECT 조합을 실험해보고, 데이터 처리 능력을 레벨업 해보시기 바랍니다!
.jpg)
.jpg)
.jpg)
.jpg)
0 댓글