INDIRECT 함수

INDIRECT(ref_text,a1)
INDIRECT(셀참조영역,셀참조형태)
문자열로 표시된 셀참조영역을  실제 셀참조로 바꾸어주는 역할

Ref_text: 셀참조 영역을 나타내는 문자열값이다.
A1(셀참조영역의 참조방식을 결정) : TURE    A1참조방식
               0;                                          FALSE  R1C1참조방식

참조주소를 문자열로 사용할 수 있는 함수

 

=OFFSET(B2, 0, 0, COUNTA(B2:B100), 1)

=INDIRECT(“B2:B” & COUNTA(B:B))

 

* Address() 셀주소를 텍스트(문자열)로 리턴함.

indirect (Address)

 

* (항상 같은 절대좌표값을 참조할수 있도록) 셀의 주소를 고정하고 싶을 때

A1 ~C3의 같은 값을 참조해도, 행이 삽입되었을때

  =$C$3*10  ---> 상대주소로 변경됨 =$C$4*10

= =INDIRECT("C3")*10 –> 안바뀜 --------> INDIRECT("C3")*10

          

 

* 특정 셀을 드롭다운(유효성검사)으로 만들고 변경하면서 결과값을 볼때

C19셀 : =IF(ISERROR(VLOOKUP($B19,INDIRECT($C$16),2,0)),"",VLOOKUP($B19,INDIRECT($C$16),2,0))
D19셀 : =IF(ISERROR(VLOOKUP($B19,INDIRECT($C$16),3,0)),"",VLOOKUP($B19,INDIRECT($C$16),3,0))
E19셀 : =IF(ISERROR(VLOOKUP($B19,INDIRECT($C$16),4,0)),"",VLOOKUP($B119,INDIRECT($C$16),4,0))

INDIRECT함수만 가지고 동적차트를 만드려고 할때 #VALUE라는 에러가 뜹니다.
한 시트안에서 각각의 이름으로 정의된 3개의 범위를 제대로 참조를 못해서 이런 오류메시지가 뜨게 됩니다.
이를 해결하기 위해서 VLOOKUP함수의 두번째 인수에 indirect함수를 이용해서 값을 가져오는 식을 사용하면 됩니다.

-------------------------------------------------------------------------

 

Indirect(“E1”)  즉 “텍스트로 된 주소(주소를 문자열로 받는다)”를 참조로 값을 리턴함.
<==> =E1

index(C:C,F2) 는 전화번호를 값을 보여주는데,
만약 직책을 찾고 싶다면? index(D:D,F2)

근데 매번 같은 일을 해야한다면 어떻게 하면 될까요?
수식을 변경하기 보다는 Indirect()를 활용해

 

Index(Indirect(F6),F2)  <== Index(B:B,F2)

그럼 여기서 직책을 검색할려면 어떻게 해야 할까요? F7의 수식을 변경하지 않고 가능할까요?

F6의 문자열 내용만 바꾸는 것으로 간단하게 수정이 되었습니다.

조금더 확장을 하면 D:D 라고 치는 것도 귀찮지요 간단히 D열이라는 의미에서 D만 입력해도 되도록 해보겠습니다.

F6이 문자열이 아닌 수식으로 바뀌었습니다. F5에 입력된 D라는 문자열을 가지고 indirect에 넣어줄 인자를 만들었네요.

그럼 조금만 더 머리를 써 볼까요? 지금은 자료랑 같은 페이지라 D열이 직책열임을 쉽게 알 수 있지만 다른 페이지였다면 번거롭겠지요?

D대신에 "직책" 이라고 입력하면 검색이 되도록 하고 싶어요.

먼저 match함수를 사용하여 직책이 몇번째 행인지를 알아냅니다. a, b, c, d 4번째 열이군요 그래서 결과값은 4 입니다.

그럼 이 4라는 숫자로부터 어떻게 D라는 값을 얻어낼 수 있을까요? 다양한 방법이 있지만 쉬운 방법으로 char 함수를 쓰겠습니다.

컴퓨터 내부에는 ascii라는 문자열을 쓰고 거기에 정의되기를 'A'는 65라고 정의 되어 있습니다. B는 66, C는 77 이런식이죠.

너무 길어지면 짜증낼테니....

그림과 같이 수식을 만들면 됩니다. match에서 계산된 결과 + 64를 해줌으로 64+4 = 68 , char(68)은 "D"가 됩니다.

char함수에 대해서는 도움말을 확인해보시면 됩니다.

다른 방법도 있으나 그것까지 설명하면 또 너무 길어지니 여기서 줄이기로 하지요.

 

 

= INDIRECT(ADDRESS(A1,A2))

잇힝~~ 드뎌!!! 엑셀의 고급스킬(?) 하나를 터득했다.

참조셀이나 영역의 위치 혹은 범위를

다른 셀의 값을 이용해서 조절 할 수 있다는 말씀!

이걸로 뭘 할 수 있냐하면

예를들어 xx 칸의 셀들의 합을 내고 싶다고 할때

=SUM(주소1:주소2) 요렇게 입력해야 하는데

(혹은 영역 부분을 마우스로 드래그 해야한다)

칸의 길이를 바꾸고 싶을때는 이 주소부분을 타이핑으로 수정하거나

영역을 다시 드래그 해줘야하지만,

위 인다이렉트+주소를 이용하면

몇 칸을 합할 건지에 대한 숫자를 가진 셀의 값만 바꾸면

자동으로 합계되는 셀의 범위가 가변하는 마술을 발휘 할 수 있다. **

이 기술은 데이터 테이블 (2007에서는 데이터>가상분석>데이터표 에서 쓸수 있음)

기술과 혼합하면 정말 환상적인 스킬로 바뀔 수 있겠다.

**

흠... 쉽게 쓰긴 힘들겠군. 아래는 설명한 방식대로 합을 내는 참조 코드

= SUM(E12:INDIRECT(ADDRESS(CELL("row",E12)+G10,CELL("col",E12))))

혹은

= SUM(E12:INDIRECT(ADDRESS(ROW(E12)+G10,COLUMN(E12))))

=ㅅ=;;;

혹시나 설명을 덧붙이면,

E12 셀 아래쪽으로 G10에 써있는 개수만큼 셀을 더하는 코드닷.

[출처] =INDIRECT(ADDRESS(A1,A2))|작성자 모래알

 

-----------------------------------------------------

* Indirect와 이름정의

B1은 "가격"이라는 이름정의가 되어 있고
= INDIRECT(가격) 은 그 이름정의도니 B1의 값을 가져온다.
(근데, 그냥 = 가격과 같다.)

* &연산자 사용

=INDIRECT("B"&$A$1)
CONCATENATE사용가능

* INDIRECT($A$1)과 INDIRECT(“$A$1”)의 차이점

셀A1에는 “B3”라는 문자열이 입력되어 있고, 셀B3에는 “룰루랄라”가 입력되어 있을때,
=INDIRECT($A$1)     결과값 룰루랄라
=INDIRECT(“$A$1”)  결과값 B3

* 시트명도 INDIRECT함수에서 활용가능

ex)   일데이타를  각각의의시트로 관리할 경우 
        월집계시트에서 일자를 가로(또는 세로)양식에 일을 나열하고 
        그것을 시트명으로 참조하여 활용한다면 편리하겠지요.

<참조>
http://www.trueonot.com/102