[엑셀] 다른 시트의 값을 참조하는 방법(INDIRECT 함수)

엑셀에는 수많은 참조 함수가 있습니다. 참조 함수는 다른 셀의 값을 참조하여 값을 나타냅니다. 참조하려는 셀이나 범위가 같은 시트가 아니라 다른 시트에 있어도 참조가 가능한데요. 참조 함수 중 자주 사용되는 VLOOKUP 함수를 예로 들어 다른 시트의 값을 참조하는 방법과 INDIRECT 함수를 사용하여 참조하려는 시트를 빠르게 변경하여 값을 나타내는 방법에 대해 설명드리겠습니다. VLOOKUP 함수의 사용 방법에 대해서는 아래의 포스팅을 참고하시면 되겠습니다.

[엑셀] 실무에서 많이 쓰이는 VLOOKUP 함수의 사용 방법

 

위와 같이 '사무소별 판매 실적' 시트에 각 사무소별 판매량을 나타내고자 합니다.

 

각 사무소의 판매량은 각 시트별로 작성이 되어있습니다. '사무소별 판매 실적' 시트에서 '서울지점' 시트를 참조하여 서울지점의 판매량을 나타내보겠습니다.

 

B4셀에 '=VLOOKUP(A4,'을 입력한 다음 서울지점 시트를 클릭합니다.

 

서울지점 시트를 클릭하면 수식에 '서울지점!'이 추가됩니다. 이 상태에서 참조 범위를 선택하고 절대참조로 바꿔줍니다.

 

이처럼 참조범위를 선택할 때 다른 시트를 클릭하면 '시트이름!'이 추가되는데 이 상태에서 셀을 선택하거나 범위를 선택하면 다른 시트의 값을 참조할 수 있습니다. 시트를 클릭하지 않고 직접 시트이름을 입력해도 가능한데 직접 입력할 경우 시트이름은 작은따옴표(' ')를 사용하여 입력해줘야 합니다.(시트이름에 띄어쓰기가 없을 때는 작은따옴표를 생략해도 됩니다.)

참조 범위를 선택한 다음 나머지 수식을 입력해 줍니다. 그러면 서울지점의 연필 판매량 값을 가져온 것을 확인할 수 있습니다. 입력된 수식을 드래그하여 B9셀까지 채워보겠습니다.

 

서울지점의 판매량 값이 '사무소별 판매 실적' 시트에 나타납니다. 이렇게 참조하려는 다른 시트를 클릭하거나 '시트이름'!을 직접 입력해서 다른 시트의 셀을 참조할 수 있습니다. 그런데 위와 같이 시트가 여러 개인 상황에서 각 지점의 값을 참조할 때마다 수식을 바꿔주는 것은 매우 번거롭습니다. 이럴 때 사용하는 함수가 INDIRECT 함수입니다. INDIRECT 함수를 이용하여 E1 셀에 사무소명을 입력하면 해당 사무소의 시트에 있는 값을 가져올 수 있도록 수식을 입력해 보겠습니다.

 

B4셀의 수식을 '=VLOOKUP(A4,INDIRECT("'"&$E$1&"'!$A$2:$B$7"),2,0)'로 수정합니다. INDIRECT 함수는 텍스트 문자열로 지정한 셀 주소를 돌려주는 함수인데 VLOOKUP 함수의 두 번째 인수를 INDIRECT 함수를 이용하여 입력한 수식입니다. 즉 E1셀의 값을 다르게 입력하면 그 값을 참조하여 VLOOKUP 함수의 두 번째 인수가 실시간으로 바뀌게 됩니다. 이렇게 수식을 입력하면 E1셀의 사무소명을 수정하면 참조하는 시트이름이 바뀌면서 해당 사무소의 판매량을 참조하게 됩니다. 

 

수식이 입력된 B4셀을 드래그하여 B9셀까지 채운 다음 E1셀의 이름을 바꿔보겠습니다.

E1셀을 대구지점으로 입력하면 VLOOKUP 함수 두 번째 인수의 주소가 바뀌면서 대구지점의 판매량 값을 가져오게 됩니다.

 

 INDIRECT 함수는 시트가 여러 개인 상황에서 참조하는 시트의 이름을 빠르게 바꿀 때 사용됩니다. 시트의 양이 많을수록 활용도가 높으므로 함수 활용방법을 숙지하신다면 실무에서 유용하게 사용할 수 있습니다.