[엑셀] INDIRECT 함수와 테이블 함께 쓰기

 

 

INDIRECT 함수는 데이터가 정형화되어 있을 때 사용하기 좋습니다. 예시 파일의 회사별 주요 재무정보처럼 행 또는 열 항목이 표마다 동일할 때 INDIRECT 함수를 활용하면 편리하게 테이블의 데이터를 호출할 수 있습니다. 
*주요 재무정보 출처: 네이버증권 

 

예시.xlsx

0.02MB

 

#1. 예시파일 설명

 

▲ 예시 파일에는 각 시트마다 삼성전자와 SK하이닉스의 주요 재무정보가 담긴 테이블이 있습니다. 각 테이블의 이름은 '삼성전자', 'SK하이닉스'로 설정되어 있습니다. 

 

▲ 이번 글에서는 위의 사진처럼 조회 시트에서 회사명을 입력하면 각 회사에 맞는 표를 출력하는 방법을 MS오피스365 이전 버전과 MS오피스 365 버전 모두 소개해 드리겠습니다.  

 

728x90

 

#2. 엑셀 Microsoft 365 이전 버전 (INDEX, MATCH 함수 활용)

 

▲ 조회시트 설명

1) 조회시트에 각 회사별 주요 재무정보 표와 동일한 항목의 표를 만듭니다. 

2) B1셀에 아래의 수식을 입력합니다.   
=INDEX(INDIRECT($C$2 & "[" & C$4 & "]"), MATCH($B5, INDIRECT($C$2 & "[" & $B$4 & "]"), 0), 1)

3) B1셀을 복사후 표 영역에 수식을 붙여넣습니다.

4) C2셀에 회사명을 입력하면 해당 표를 호출합니다.

 

<수식 설명>

▲ 테이블에서 열의 데이터 부분은 테이블 이름[열 이름] 형식으로 표현됩니다.
따라서, INDIRECT("삼성전자" & "[" & "2021.12. & "]")는 삼성전자 테이블의 '2012.12.' 열의 데이터 영역을 호출합니다. 

이러한 성질을 활용하여 회사명이 들어갈 셀을 절대참조($C$2)로 열 이름이 들어갈 셀을 절대참조($B$4)  및
부분 절대참조(C$4)로 두어 INDEX, MATCH, INDIRECT 함수를 활용하면 예시파일의 조회시트와 같은 수식이 완성됩니다. 

 

#3. 엑셀  Microsoft 365 버전 (INDIRECT 함수만 활용)

▲ 조회시트 설명

1) MS오피스 365 버전부터는 범위에 대한 수식을 입력하면 그 범위를 자동적으로 출력합니다.

2) 따라서 R4에 INDIRECT("테이블 이름[#모두]") 만 입력해도, 해당 테이블 범위가 위의 사진처럼 출력됩니다.
'테이블 이름[#모두]'는 엑셀 수식에서 테이블 전체 범위(테이블 머릿글행 포함)를 의미합니다.

3) 회사명을 입력함에 따라 동적으로 테이블을 끌고 오기 위해서 아래와 같이 수식을 작성해줍니다. 
=INDIRECT($S$2&"[#모두]")