[엑셀] INDIRECT 함수의 이해, 쓰임새, 활용
* 출처 : https://m.blog.naver.com/yjlecture/221897735324
엑셀의 찾기/참조 함수 중 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] 셀의 수식을 아래로 오른쪽으로 복사하여 한 번에 여러 시트의 값을 가져올 수 있습니다.
이해 되셨나요?? 텍스트로 설명하기엔 한계가 있네요....^^
빨리 코로나19가 잠잠해져서 직접 대면하여 교육하고 싶네요!~~
같은 찾기/참조 함수인 offset 함수에 대해 알아보실 분은 다음 강좌를 참고하세요!~
