엑셀함수 INDIRECT – 참조를 동적으로 바꾸기

엑셀 수식을 사용하다보면 수식에 있는 참조가 동적으로 바뀌어야 하는 경우가 있다. 예를 들면 VLOOKUP함수를 사용할 때 값을 찾는 시트의 이름이 그 때 그 때 바뀌어야 한다면 다음과 같이 INDIRECT함수를 이용하면 해결할 수 있다.

=VLOOKUP(C14,INDIRECT(C13 & “!A1:C11”),2,FALSE)

이 수식을 이해하기 위해 INDIRECT함수를 먼저 살펴보자.

 

문자열로 만들어진 참조를 반환하기

INDIRECT함수를 응용하기 전에 먼저 간단한 예를 살펴보자.

C5셀에 “안녕하세요!” 라는 내용이 입력되어 있을 때 일반적으로 이 값을 참조하려면
=C5
를 입력하면 된다.

그런데
=INDIRECT(“C5”)
를 입력해도 동일한 결과를 얻는다.

위 결과를 보면 INDIRECT함수는 다른 셀을 참조할 수 있도록 “문자열로 만들어진 값”을 참조형태로 바꾸어 주는데 이렇게만 보면 그다지 쓸모가 있어 보이지는 않는다.

INDIRECT함수는 단독으로는 잘 사용되지 않고 아래의 예와 같이 VLOOKUP함수 등 다른 함수에 포함되어서 참조를 동적으로 바꿔야 할 때 많이 사용된다.

 

시트명을 동적으로 참조하여 값 찾기

다음 그림과 같이 판매실적 시트가 월별로 있을 때

     

 

시트별(월별)로 판매실적을 찾을 경우  시트명이 다르므로 보통 아래와 같이 시트명을 바꿔가면서 각각 수식을 입력해야 할 것이다.

사원번호가 102인 직원의 1월 판매실적 가져올 때 :
VLOOKUP(102,’1월실적’!A1:C11,3,FALSE) => 2,000,000

사원번호가 102인 직원의 2월 판매실적 가져올 때 :
VLOOKUP(102,’2월실적’!A1:C11,3,FALSE) => 2,600,000

 

시트의 갯수만큼 수식을 바꿔가면서 작성해야 하는 번거로움이 있는데 다음과 같이 INDIRECT함수를 이용하면 하나의 수식으로 해결이 가능하다.

=VLOOKUP(C14,INDIRECT(C13 & “!A1:C11”),3,FALSE)

C13셀에 시트명을 “1월실적”으로 입력하면 INDIRECT(C13 & “!A1:C11”) 수식이 1월실적 시트를 참조하고 최종적으로 VLOOKUP함수가 1월 판매실적을 찾아준다.

 

C13셀에 시트명을 “2월실적”으로 입력하면 INDIRECT(C13 & “!A1:C11”) 수식이 2월실적 시트를 참조하고 최종적으로 VLOOKUP함수가 2월 판매실적을 찾아준다.

 

 

[ 엑셀 찾기 및 참조영역 함수 ]

• 엑셀함수 HLOOKUP – 표를 수평(가로)으로 따라가면서 값찾기
• 엑셀함수 HYPERLINK – 하이퍼링크 만들기
• 엑셀함수 ROWS – 범위나 배열의 행의 개수 구하기
• 엑셀함수 COLUMNS – 범위나 배열의 열의 개수 구하기
• 엑셀함수 AREAS – 참조영역내의 영역의 개수 구하기
• 엑셀함수 ADDRESS – 행,열 번호로 셀주소 표시하기
• 엑셀함수 TRANSPOSE – 행과 열을 바꾸기
• 엑셀함수 INDIRECT – 참조를 동적으로 바꾸기
• 엑셀함수 LOOKUP – 행 또는 열의 같은 위치에 있는 값 찾기
• 엑셀함수 OFFSET – 행열이동 후 참조구하기
• 엑셀함수 COLUMN – 열번호를 자동으로 매기기
• 엑셀함수 ROW – 행번호를 자동으로 매기기
• 엑셀함수 CHOOSE – 값목록에서 원하는 값 선택하기 – XLWorks
• 엑셀함수 MATCH – 특정 범위에서 값의 위치 찾기 – XLWorks
• 엑셀함수 INDEX – 특정 범위에서 행과 열을 이용하여 값 찾기 – XLWorks
• 엑셀 VLOOKUP 함수로 표에서 값 찾기(간단 버전)
• 엑셀에서 값을 찾을 때 VLOOKUP, HLOOKUP 함수 사용하기
• VLOOKUP으로 안될때 INDEX,MATCH 사용하기
• INDEX, MATCH를 이용한 다중조건으로 값찾기