[엑셀] INDIRECT 함수의 이해, 쓰임새, 활용

 

 

찾기/참조 함수 중 indirect 함수는

어떨 때 사용하는건가요??

엑셀의 찾기/참조 함수 중 indirect 함수에 대해 질문 하시는분들이 계셔서

indirect 함수의 개념, 쓰임새, 활용 할 수 있는 예제를 다루어 보겠습니다.

여러 영역으로 활용될 수 있지만이중 유효성 검사, 여러 시트의 흩어진 값을 하나의 시트로 모아야 할 경우에 활용하면 편리합니다.

​

1) indirect 함수는 텍스트 문자열로 지정한 셀 주소지의 값을 반환합니다.

"수식 자체는 변경하지 않고, 수식 안에 있는 셀에 대한 참조를 변경하려는

경우에 INDIRECT 함수를 사용합니다."이 의미는 아래 2)번 ~3)번 을 보며 이해 하도록 합니다.

형식 : INDIRECT(텍스트 형태의 주소, [주소 스타일]) :

[주소스타일 ]은 생략가능하고, 생략하면 A1 형태의 주소가 됩니다.

그림의 수식을 보면 『=INDIRECT("B2")』 로 셀 주소를 텍스트 형태로 지정하여 [B2] 셀의 값을 반환합니다.

이렇게 쓰는 이유는 간접적으로 값을 참조하기 위함입니다.

 

2) [E5], [F5] 셀에는 부산의 값인 '200'을 참조하였습니다.

[E5]는 직접 참조, [F5]는 indirect 함수로 간접 참조하였습니다. 결과는 동일합니다.

3) 그런데, 참조한 원래의 값 '부산' 행을 삭제하면 '창원' 행이 위로 올라옵니다.

직접 참조한 [E5] 셀은 에러가 나지만, 간접 참조한[F5] 셀은 창원의 값 100을 그대로 반환합니다.

위에서 얘기한 "수식 자체는 변경하지 않고, 수식 안에 있는 셀에 대한 참조를 변경하려는

경우에 INDIRECT 함수를 사용합니다." 를 이해하시겠지요??

4) 이제 indirect 함수를 업무에서 활용해보겠습니다.

[1월], [2월], [3월] 시트를 참조하여 [indirect활용] 시트에 월별 합계를 가져오려 하려 합니다.

5) [B4] 셀에서 『=』 입력 후 [1월] 시트를 클릭하여 [D4] 셀을 선택한 후 <Enter>를 누릅니다.

수식은 『='1월'!D4』 형태로 작성됩니다. 즉 [1월] 시트의 [D4] 셀의 값을 가져옵니다.

2월과 3월의 값도 가져오기 위해선 위와 동일한 방법으로 작성해야 겠죠?

참조할 시트가 다르므로 수식을 복사할 수 없습니다.

이런.....지금처럼 참조할 시트가 3개라면 수식을 3번 작성하면 되지만

참조할 시트가 12개라면 12번 작성해야 하나요..??

[B4] : 『='1월'!D4』

[C4] : 『='2월'!D4』

[D4]: 『='3월'!D4』

 

6) 그래서 indirect 함수를 사용합니다.

『=indirect( 』 을 입력한 후 <Ctrl+A>를 눌러 [함수 인수] 창을 호출합니다.

7) [함수 인수] 창에 다음과 같은 수식을 작성한 후 [확인] 버튼을 클릭합니다.

『=INDIRECT(B$3&"!"&"D"&ROW())』 ​

☞ 수식풀이 :

B$3 : '1월' 시트를 참조해야 하므로 [B3]셀의 '1월' 텍스트를 참조함.

2월, 3월 로 시트명이 바뀌어야 하므로 혼합 참조함

"!" : 시트명 다음에 구분기호인 "!"를 조인

"D" : "D"를 조인 ,1월, 2월, 3월 시트에서 가져올 값은 D열에 존재함

ROW() : 현재 위치의 행 값을 반환, '미주' 지역은 4, '동남아시아' 지역은 5, '유럽'지역은 6.....

==> 결과적으로 수식은 『='1월'!D4』이 됨.

8) [B4] 셀의 수식을 아래로 오른쪽으로 복사하여 한 번에 여러 시트의 값을 가져올 수 있습니다.

이해 되셨나요?? 텍스트로 설명하기엔 한계가 있네요....^^