엑셀 INDIRECT 함수: 기본·중급·고급 활용을 한 번에 끝내는 가이드
엑셀 INDIRECT 함수는 “텍스트로 적힌 주소”를 실제 참조로 바꾸어 주는 도구입니다. 예를 들어 셀에 "B2:D10"처럼 적혀 있으면 이를 진짜 범위로 바꿔 SUM/AVERAGE/INDEX 등이 읽게 해 줍니다. 이 글에서는 기본 문법부터 동적 시트 참조, 마지막 행까지 합계, 3D(여러 시트) 집계, 데이터 유효성, 그리고 성능 대안(INDEX/XMATCH)까지 실무 패턴을 차례로 설명합니다.
빠른 해결(Quick Fix)
① 셀/영역/시트 동적 참조
- 셀 주소가 D1에 있음(예: “B2”):
=INDIRECT(D1) - 시트명이 B1, 그 시트의 C5:
=INDIRECT("'"&$B$1&"'!C5") - 시트+B1, 범위문자열 C1(예: “B2:D10”):
=INDIRECT("'"&$B$1&"'!"&$C$1)
② 텍스트 영역으로 합계/평균
- 합계:
=SUM(INDIRECT("B2:B"&E1)) - 평균:
=AVERAGE(INDIRECT("C2:C"&MATCH(9.9E+307,C:C)))
③ R1C1 모드 한 줄 트릭
=INDIRECT("R"&ROW()&"C"&$C$1, FALSE) — C1에 열 번호가 있을 때 현재 행의 “그 열”을 가리킵니다. 두 번째 인수 FALSE는 R1C1 모드임을 뜻합니다.
왜 이런 문제가 생기나?(개념·원리)
INDIRECT의 동작 원리와 볼래틸리티
INDIRECT(ref_text,[a1])는 문자열(ref_text)을 참조로 평가합니다.[a1]이 생략/TRUE면 A1 스타일, FALSE면 R1C1 스타일입니다.- 볼래틸(Volatile) 함수입니다. 통합문서가 계산될 때마다 항상 다시 계산되어 대량 사용 시 속도가 느릴 수 있습니다. 대안은 아래 “대체 방법” 참고.
닫힌 통합문서·구조화참조(테이블) 제약
- 닫힌 통합문서의 범위를 텍스트로 참조하면
#REF!가 납니다. 간접 참조는 대상 통합문서가 열려 있어야 합니다. - 대부분의 버전에서 구조화참조(테이블) 문자열(예:
"Table1[Amount]")을INDIRECT로 평가하지 못합니다. 테이블은INDEX·XMATCH조합이 안전합니다.
실무 예제
샘플 데이터
| 항목 | 값 | 설명 |
|---|---|---|
| 마지막 행 번호 | 셀 E1 | 예: 500 |
| 선택 시트명 | 셀 B1 | 예: “Jan”, “Feb”, … |
| 범위 문자열 | 셀 C1 | 예: “B2:D10” |
| 조회 키 | 셀 G2 | 예: 제품코드 |
| 열 머리글 | 셀 H1 | 예: “매출” |
1) 시트 선택 드롭다운 + 동적 조회
데이터 유효성(데이터 > 데이터 유효성 검사)으로 B1에 시트 목록을 고르게 한 다음, 선택된 시트에서 키(G2)와 머리글(H1)에 맞춰 값을 가져옵니다.
=LET(
sht, $B$1,
rng, INDIRECT("'"&sht&"'!A1:Z100"),
rowN, XMATCH($G$2, INDEX(rng,,1), 0), /* 첫 열에서 키 찾기 */
colN, XMATCH($H$1, INDEX(rng,1,), 0), /* 첫 행에서 머리글 찾기 */
INDEX(rng, rowN, colN)
)
POINT: 범위는 텍스트로 만들되, 실제 검색은 XMATCH/INDEX로 수행하면 빠르고 탄탄합니다(테이블에도 쉽게 전환 가능).
2) “마지막 행까지” 동적 합계
늘어나는 목록을 자동 합계:
=SUM(INDIRECT("B2:B"&MATCH(9.99999999999999E+307, B:B)))
숫자가 아닌 행이 섞이면 COUNTA나 다른 기준 열로 바꾸세요.
3) 3D(비연속 시트) 합계/조회
시트명이 A2:A6에 있을 때 각 시트의 동일 주소 B2를 더하기:
=SUMPRODUCT( N(INDIRECT("'"&$A$2:$A$6&"'!B2")) )
비연속 시트도 가능하며, 목록만 갈아끼우면 됩니다. 조회도 비슷한 방식으로 할 수 있습니다.
4) 데이터 유효성 목록에 간접범위 쓰기
유효성 목록의 원본에 =INDIRECT($C$1)을 넣으면, C1에 있는 이름 정의(또는 주소 문자열)의 범위를 드롭다운으로 쓸 수 있습니다. 예: C1에 “catList”, 이름관리자에 =Sheet1!$A$2:$A$10을 등록.
5) 구조화참조(테이블) 대응 대안
테이블 tblSales에서 머리글(H1)과 행키(G2)로 값을 찾는다면:
=LET(
hdrs, TAKE(tblSales,1),
colN, XMATCH($H$1, hdrs, 0),
rowN, XMATCH($G$2, tblSales[Item], 0),
INDEX(tblSales, rowN, colN)
)
구조화참조는 자동 확장과 성능 면에서 INDIRECT보다 유리합니다.
6) ADDRESS와 결합해 주소 문자열 만들기
열 문자/행 번호로 텍스트 주소를 생성한 후 INDIRECT로 평가:
=INDIRECT( ADDRESS(2, $C$1) & ":" & ADDRESS($E$1, $C$1) )
여기서 C1=열 번호, E1=마지막 행. ADDRESS는 ADDRESS(row, column, [abs], [a1], [sheet]) 형식입니다.
대체 방법/주의/체크리스트
- 성능:
INDIRECT는 볼래틸. 보고서가 느리면INDEX+XMATCH,OFFSET(역시 볼래틸) 대신INDEX기반 범위 만들기, 또는 테이블 전환을 검토. - 보안/안전: 사용자가 입력한 문자열을
INDIRECT로 평가하면 예기치 않은 범위를 읽을 수 있습니다. 허용 목록과 검증으로 한정하세요. - 닫힌 통합문서: 간접 참조는 실패합니다. 외부 파일을 열거나 Power Query로 불러온 뒤 일반 참조를 사용하세요.
- 구조화참조: 대부분의 버전에서
INDIRECT("Table[Col]")는 실패합니다. 테이블은INDEX·XMATCH를 권장. - 언어/지역: 시트명·공백이 있는 경우 작은따옴표로 감싸세요(예:
'2025 Sales').
Troubleshooting
| 증상 | 원인 | 해결법 |
|---|---|---|
| #REF! 오류 | 닫힌 통합문서 참조, 잘못된 주소 문자열 | 대상 파일을 열거나 주소를 '시트'!A1 형식으로 재검증 |
| 계산이 느림 | 볼래틸 함수 대량 사용 | INDEX/XMATCH로 대체, 테이블로 전환, 참조 수 축소 |
| 유효성 목록이 비어있음 | 이름 정의/주소 오탈자 | 이름관리자에서 범위 확인, =INDIRECT($C$1) 원본 재검토 |
| 시트명에 공백/한글 | 작은따옴표 누락 | "'"&시트&"'!B2"처럼 감싸기 |
| 테이블 열을 간접 참조 불가 | 구조화참조 미지원 | INDEX(tbl, row, col) 또는 CHOOSECOLS 등 사용 |
마무리: 관련 글 추천
추가 내부 링크(본문 중 자연 배치): 워크시트 단축키, 동적 배열 함수 핵심