[EXCEL,엑셀] 주소 및 문자열 함수 indirect , address , concantenate

 엑셀 함수 작성에 있어서 참조는 한눈에 알아보기 힘들고, 상황에 따라 바꾸기가 어려울 때가 많다.

 예를 들어 =SUM(A1:B1) 을  =SUM(A3:B3) 로 바꾸고 싶다고 할때, 직접 바꾸면 되지만 특정 조건에 따라 =SUM(A1:B1) ,=SUM(A2:B2) ,=SUM(A3:B3) 등으로 계속 바꿔야 하는 상황이면, =SUM(concatenate(ADDRESS(변경값,1),":",ADDRESS(변경값,2)) 와 같은 형식으로 바꾸어 표현후 변경값 부분만 다른 Cell 에 연결 하여 바꿀수 있다. 

그러면 함수 들 부터 하나씩 알아보자

A. 함수 설명

1. =SUM(숫자,[숫자], ...)

더하는 기능을 가지고 있는 함수이다. Text 와 논리 값은 무시한다.

2. =CONCATENATE(문자,[문자], ...)

 문자열 들을 합해서 표현한다. 이 함수와 같은 기능을 가진 연산자는 & 이다. 

=CONCATENATE(문자,[문자], ...) 와  =문자&[문자]&,.... 은 같은 표현이다. 

3. =ADDRESS(행, 열, [주소 형식<1,2,3,4 >)], [참조 형식 <True,False >], [워크시트 이름_문자])

 행과 열을 입력하면 참조 형식으로 출력 한다. 

=ADDRESS(2,1) 로 입력하면 $A$2 값이 출력 된다. 

주소 형식의 기본값은 1 이고 값에 따라 설정 되는 형식은 1:절대행 절대열 / 2:절대 행, 상대 열 / 3:상대 행, 절대 열 /4:상대 행, 상대 열 이다. 

참조 형식의 기본값은 True 이고, 값에 따라 설정 되는 참조 형식은 TRUE:A1 / False:R1C1 이다. 

워크시트 이름은 입력하면 주소앞에 워크시트 이름이 붙는다. 

4.INDIRECT(참조_문자, [참조형식 <True,False >])  

문자를 주소값으로 바꿔준다. 참조 형식의 기본값은 True 이고, 값에 따라 설정 되는 참조 형식은 TRUE:A1 / FALSE:R1C1  이다. 입력한 참조의 형식에 맞는 형식을 선택 하면 된다.   단독으로 사용하기 보다는 다른 함수와 같이 사용한다. 

아래와 같이 입력하면

A	B	C
INDIRECT 함수 예시
1	=SUM(A2:A3)	A2:A3
2	=SUM(C2)	=INDIRECT(C2)
	=SUM(INDIRECT(C2))
	=SUM(C3)

아래와 같은 결과 값을 얻을수 있다. 

A	B	C
INDIRECT 함수 예시
1	3	A2:A3
2	0	2
	3
	2

B. 실제 사용 예

위에서 설명한 함수들을 합쳐서 사용하는 예를 표현 하면 아래와 같다. 

B11 과 같은 결과를 만들기 위해 B10 에 INDIRECT 함수를 이용한 예시이다. 

입력예 (행 번호 표기하지 않음)

A	B
워크시트	Sheet1
시작행	14
종료행	15
시작열	1
종료열	2
주소 표현	=CONCATENATE(B7,B8,":",B9)
주소 표현 1	=CONCATENATE("'",B1,"'!")
주소 표현 2	=ADDRESS(B2,B4,1,1)
주소 표현 3	=ADDRESS(B3,B5,1,1)
합계	=SUM(INDIRECT(B6,1))
합계	=SUM(A14:B15)
같은지 확인	=B10=B11
예시 값 1	예시 값 2
1	3
2	4

출력값

A	B
워크시트	Sheet1
시작행	14
종료행	15
시작열	1
종료열	2
주소 표현	'Sheet1'!$A$14:$B$15
주소 표현 1	'Sheet1'!
주소 표현 2	$A$14
주소 표현 3	$B$15
합계	10
합계	10
같은지 확인	TRUE
예시 값 1	예시 값 2
1	3
2	4

* 주의 사항

INDIRECT 와 ADDRESS 함수로 연결할 경우 가져와야할 DATA들의 위치는 워크시트에서 이동으로 붙여넣기를 해도 추적되지 않으므로 유의 하자.