(부제 : INDIRECT 함수는 도대체 언제 사용할까? – 여러 시트 자료 정리)
엑셀함수의 INDIRECT 함수에 대해 알아보고자 합니다.
보통 INDIRECT 함수에 대해 접촉할 일이 많지 않다고 생각합니다.
다른 사람이 작업한 엑셀 자료에서 간혹 INDIRECT 함수를 보거나
아니면 찾기, 참조할 때 사용하는 함수로 INDIRECT가 있다는 이야기를 듣는 정도인 것 같습니다.
특히, 개인적으로 INDIRECT 함수는 다른 찾기 또는 참조 함수 대비
직관적이지 않고, 상대적으로 많이 사용하지 않아서 이해하기 어려운 측면이 있습니다.
그래서 다른 찾기나 참조 함수를 사용하는 것을 추천을 합니다.
하지만, 우리가 엑셀로 작업을 할 때,
다른 어떤 함수보다 INDIRECT를 사용하면 편하게 진행할 수 있는 경우가 있습니다.
바로 여러 개의 시트를 대상으로 찾기나 참조를 할 때 사용하면 편리합니다.
그러면 어떤 경우인지 예제를 통해서 설명을 드리도록 하겠습니다.
#INDIRECT함수기초
INDIRECT 함수의 구성은 다음과 같습니다.
= INDIRECT(참조범위,참조방식)
참조범위는 참조할 셀 주소 또는 범위의 주소입니다.
참조방식은 셀 주소를 참조할 방식인데, 이 부분은 복잡하니 상관없이 보시면 됩니다.
INDIRECT함수는 참조범위에 대한 “값” 또는 “값에 표현된 셀주소의 값”을 가져올 수 있습니다.
위의 그림에서 보듯이
E열에 INDIRECT 함수를 사용해서 A열에 대한 값을 가져올 수 있는데
쌍따옴표가 양쪽에 있으면 해당 셀주소의 “값”을 가지고 오고
없으면 해당 셀주소에 표시된 셀주소의 값을 가지고 옵니다.
둘 다 사용을 하지만 보통 “값”을 가지고 오는 것을 많이 사용합니다.
#INDIRECT함수활용
지금부터가 중요한 부분입니다.
그냥 E2 셀에 “=A2″이라고 넣으면 간단하게 해결이 될텐데
굳이 INDIRECT 함수를 사용하는 이유는 무엇인가입니다.
위와 같이 단순한 경우에는 굳이 INDIRECT 함수를 사용할 필요가 없습니다.
아래와 같은 예시를 보겠습니다.
위와 같이 “시도별 발생(17시도_검역)_정리” 시트에
각 시도별 시트의 데이터를 합쳐서 정리를 해야한다고 가정을 해보겠습니다.
VLOOKUP 함수를 사용해서 각 지역별 시트(예: 서울, 부산, 대구…)의
데이터를 참조를 해서 가져온다고 하면
C열에서 VLOOKUP 함수에서 “서울”시트로 범위 지정을 한번
D열에서 VLOOKUP 함수에서 “부산”시트로 범위 지정을 한번
E열에서 VLOOKUP 함수에서 “대구”시트로 범위 지정을 한번 등등
지역별로 VLOOKUP 함수를 지정을 해줘야 합니다.
즉, C1열에 =VLOOKUP(A7,서울!A:B,2,0) 넣고 C열 전체 복사
D1열에 =VLOOKUP(A7,부산!A:B,2,0) 넣고 D열 전체 복사
E1열에 =VLOOKUP(A7,대구!A:B,2,0) 넣고 E열 전체 복사, 등등
각 열별로 작업을 해줘야 합니다.
하지만 INDIRECT 함수를 사용하면
각 시트별로 따로 지정을 할 필요가 없습니다.
즉 C1열에는 아래와 같이 표현을 할 수 있는데
=VLOOKUP(A7,INDIRECT(“‘서울’!A:B”),2,0)
시트명이 5행에 정보가 있기 때문에 아래와 같이 변경을 할 수 있습니다.
=VLOOKUP($A7,INDIRECT(“‘”&C5&”‘!A:B”),2,0)
※ 참고
VLOOKUP에서 범위 지정시 시트명 정보(5행, 주소)을 활용하면 에러가 발생함
예 : =VLOOKUP(A7,C5&”!A:B”,2,0) → 에러 발생
INDIRECT 함수는 범위 지정시 시트명 정보(5행, 주소)를 활용해도 에러가 안남
다만, 시트명을 포함한 주소를 활용해서 INDIRECT 함수의 참조범위를 지정을 할 때에는
쌍따옴표를 붙이는 방법에 약간 혼동이 있으니 주의가 필요합니다.
아래와 같이 순차적으로 진행을 하면 크게 문제가 없을 것 같습니다.
① 시트명을 직접 사용해서 함수문을 작성합니다.
=VLOOKUP(A7,INDIRECT(“‘서울’!A:B”),2,0)
→ 값을 가지고 오는 것이기 때문에 양 끝에 쌍따옴표가 있습니다.
② 시트명 정보를 가지고 있는 주소로 해당 시트명을 변경합니다.
=VLOOKUP(A7,INDIRECT(“‘C5‘!A:B”),2,0)
③ 해당 주소 옆에 & 를 붙입니다.
=VLOOKUP(A7,INDIRECT(“‘&C5&‘!A:B”),2,0)
④ 그 옆으로 쌍따옴표를 하나씩 더 붙입니다.
=VLOOKUP(A7,INDIRECT(“‘“&C5&“‘!A:B”),2,0)
⑤ 고정값($)을 설정해줍니다.
=VLOOKUP($A7,INDIRECT(“‘”&C$5&”‘!A:B”),2,0)
위와 같이 진행을 한 후에 해당 셀을 복사한 후에
데이터가 필요한 셀에 붙여넣기를 하면 자동으로 해당 값을 불러올 수 있습니다.
지금까지 여러 시트의 데이터를 참조할 경우에
용이하게 활용할 수 있는 INDIRECT 함수에 대해 알아봤습니다.
조금이라도 작업을 하시는데 도움이 되시길 바랍니다.