엑셀 INDIRECT 함수 기능을 바로 활용 할 수 있는 기본 3가지 방법에 대해 알아보도록 하겠습니다. 이 함수는 참조하는 셀 및 범위의 위치가 변경되어 참조 위치가 변경될 때 자동으로 업데이트되는 동적인 참조를 생성할 수 있습니다.
엑셀 INDIRECT 함수 3가지 활용 방법
텍스트 문자열을 기반으로 간접적으로 셀이나 셀 범위를 참조할 수 있는 참조 함수입니다.
동적 셀 범위 참조
INDIRECT 함수의 첫 번째 활용 방법은 셀 범위의 참조를 동적으로 실시하는 방법입니다.
아래와 같이 요일별 판매 수량이 있는 표 테이블이 있습니다.
각 열에 월, 화, 수, 목, 금의 판매수량이 기입되어 있습니다.
우선 각 요일별 열의 범위 이름 설정 기능을 실시하겠습니다.
엑셀 범위 이름 설정 기능의 간단한 설명은 아래와 같습니다.
- 이름을 설정한 범위를 선택합니다.
- 좌측 상단에 입력창에 원하는 이름을 입력합니다.
C열 부터 G열 까지 모두 범위 이름을 아래와 같이 설정하였습니다.
마지막으로, 동적 범위의 요일별 총합을 엑셀 INDIRECT 함수를 활용하여 만들기 위해 아래와 같은 함수 수식을 사용합니다.
엑셀 함수 수식을 살펴보면 아래와 같습니다.
=SUM(INDIRECT(I3))
여러시트 셀값 더하기
아래와 같이 4개의 엑셀 시트에 각주차의 요일별 판매수량이 있습니다.
해당 4개의 엑셀 시트 값들을 “SUM INDIRECT” 의 이름의 시트에 아래와 같은 테이블을 사용하여 데이터 분석을 실시하겠습니다.
“DAY” 의 요일별 셀과 “PRODUCT”의 상품별 셀에는 아래와 같이 엑셀 리스트 기능을 사용하였습니다.
결론적으로, 위의 표에 INDEX MATCH 함수를 사용한 뒤, INDIRECT 함수를 통해 각 4개의 엑셀 시트의 문자를 참조범위로 변환합니다.
우선 C cell 에는 INDEX MATCH 함수를 통해, 기준 값을 요일과 상품 두가지로 하여 다른 시트의 값을 불러왔습니다.
그 다음 D cell 에는 INDIRECT 함수를 사용하여, 4개의 엑셀 시트의 이름을 시트 참조로 전환 하였습니다.
엑셀 시트 이름을 범위 참조로 변환하는 핵심 기능은 아래와 같습니다.
이와 같이 INDIRECT 함수는 INDEX MATCH 함수 및 SUMIFS 함수 등과도 좋은 조합을 이룹니다.
동적 드롭 다운 기능
위에서 사용된 “엑셀 리스트 기능” 과 “범위 이름 설정” 기능을 INDIRECT 함수와 함께 사용하면, 조건에 따라 리스트가 자동으로 변경되도록 만들 수 있습니다.
아래와 같이 “Zone” 에는 A와 B가 있으며, A zone은 {1,2,3,4,5}로 구성되어 있고, B zone은 {one,two,three,four,five} 로 구성되어 있습니다.
A와 B의 선택에 따라 INDIRECT 함수를 활용하여 엑셀 리스트가 자동으로 변경되는 기능을 만드는 방법에 대해 이야기하도록 하겠습니다.
우선 리스트로 사용할 각각의 범위의 이름을 지정합니다.
아래와 같이 OPTION1에서는 A,B 의 선택을 실시하고, OPTION2에서는 OPTION1 선택에 따라 A와 B의 리스트를 자동으로 변경되도록 하겠습니다.
Option1 리스트로는 아래의 이름으로 지정된 범위를 사용합니다.
Option2 리스트로는 아래의 이름으로 지정된 범위를 사용합니다.
Option1의 결괏값에 대한 INDIRECT 함수와 절대 참조를 사용하여 리스트를 만듭니다.
위와 같이 진행이 완료되면 아래와 같이 각각 선택에 따라 자동으로 변경되는 리스트가 만들어집니다.
엑셀 범위 이름 변경
엑셀 범위 이름 설정을 하다보면, 범위 이름의 삭제 및 범위의 변경 등이 필요할 때가 있습니다.
아래와 같이 수식 탭에서 이름 매니저를 사용하여 실시 할 수 있습니다.
결론
엑셀 INDIRECT 함수 기능을 사용하여 3 가지 효율 높은 범위 참조 방식에 대해 알아보았습니다. 이렇듯 셀 범위의 이름 참조, 시트 이름 참조를 사용하여 본인의 엑셀 파일을 조금 더 자동화 시킬 수 있습니다.