INDIRECT 함수 실무 패턴 — 시트 이름을 수식 안에서 동적으로 쓰는 법
지점별 매출 시트가 서울, 부산, 대구와 같이 개별 시트로 분리되어 있을 때, 드롭다운 메뉴에서 선택한 지점의 데이터를 하나의 수식로 자동으로 집계하려면 INDIRECT 함수를 사용할 수 있습니다. 이 함수는 문자열로 된 주소를 실제 셀 참조로 변환해 주는 역할을 하며, 특히 대시보드나 리포트 작성 시 업무 효율을 극대화하는 핵심 기능입니다.
하지만 INDIRECT는 휘발성 함수(Volatile Function)라는 특성이 있어, 참조한 값이 바뀌지 않아도 재계산이 일어날 때 다시 계산되므로 대용량 파일에서는 성능 저하를 유발할 수 있습니다. 따라서 INDIRECT의 정확한 문법부터 실전 예제, 오류 예방법, 대안 기법까지 상세히 다루어 실무에서 바로 적용할 수 있는 전체 패턴을 정리합니다.
1. INDIRECT 함수의 기본 원리와 구조
INDIRECT 함수는 단순히 셀 주소를 읽어오는 것이 아니라, 텍스트로 된 주소를 실제 셀 주소로 해석해 주는 역할을 합니다. 기본 문법은 다음과 같습니다.
=INDIRECT(참조_문자열, [a1])- 참조_문자열 (ref_text): 주소를 나타내는 텍스트, 주소 텍스트가 든 셀, 또는 참조로 정의된 이름을 전달합니다. 주소를 직접 쓰면
INDIRECT("B2")처럼 따옴표로 감싸고, A1에 주소 텍스트가 있으면INDIRECT(A1)처럼 씁니다. - [a1] (a1): 참조 스타일을 지정하는 선택 인자입니다.
TRUE또는 생략 시: A1 스타일 (예:B2,C5:D10)을 사용합니다.FALSE시: R1C1 스타일 (예:R2C2,R5C5:R10C10)을 사용합니다.
1.1 기본 사용 예시
가장 기본적인 형태는 A1 스타일을 사용하는 경우입니다.
=INDIRECT("B2")이 수식은 현재 시트의 B2 셀 값을 반환합니다.
시트 이름을 포함한 범위 참조도 가능합니다.
=SUM(INDIRECT("서울!B2:B10"))이 수식은 서울 시트의 B2부터 B10까지의 셀 값을 합산합니다. 이것이 바로 다양한 시트를 동적으로 다루는 핵심입니다.
1.2 R1C1 스타일 활용
R1C1 스타일은 행과 열 번호를 모두 숫자로 표현합니다. 보통 수식 분석이나 매크로 기록 시 사용되지만, INDIRECT와 함께 쓸 때도 유용합니다.
=INDIRECT("R2C2", FALSE)이 수식은 현재 시트의 2행 2열(B2)에 있는 값을 반환합니다.
2. 실전 예제: 지점별 매출 합계 자동화
집계 시트에서 지점을 선택하면 해당 지점 시트의 매출을 합산하는 구조입니다.
2.1 시트 구조 설정
목록시트의 A2:A4에서울,부산,대구를 한 셀씩 입력합니다.목록!A2:A4를 선택해 이름 상자에지점목록이라는 통합 문서 범위 이름을 만듭니다.집계시트 A2의 데이터 유효성 검사에서 목록 원본을=지점목록으로 지정합니다. 지점 선택은 이 셀에서 합니다.서울,부산,대구시트의 B2:B100에는 숫자 형식의 매출 데이터가 있다고 가정합니다.집계시트의 B2에는 합계 수식을 입력합니다.
2.2 수식 작성 (기본 패턴)
집계 시트의 B2 셀에 다음 수식을 입력합니다.
=SUM(INDIRECT(A2&"!$B$2:$B$100"))해설:
A2는 드롭다운에서 선택된 지점명(예: "서울")을 담고 있습니다.&"!$B$2:$B$100"은 이 지점명 뒤에!$B$2:$B$100을 붙여 문자열을 완성합니다.INDIRECT는"서울!$B$2:$B$100"이라는 문자열을 실제 셀 참조로 변환하여SUM함수에 전달합니다.
이제 A2 셀의 값을 서울에서 부산으로 변경하면, 수식은 자동으로 부산!$B$2:$B$100을 참조하여 합계를 계산합니다.
2.3 공백이 포함된 시트명 처리
만약 시트 이름에 공백이나 특수문자가 포함되어 있다면, 시트명 앞뒤에 작은따옴표(')를 추가해야 합니다. 예를 들어 시트명이 2026 Q1이라면 다음과 같이 작성해야 합니다.
=SUM(INDIRECT("'2026 Q1'!$B$2:$B$100"))이를 수식에 적용하려면 다음과 같이 작성합니다.
=SUM(INDIRECT("'"&A2&"'!$B$2:$B$100"))"'"은 시트명 앞에 붙이는 작은따옴표 문자열입니다.A2는 시트명입니다."'"은 시트명 뒤에 붙이는 작은따옴표입니다.
시트명 자체에 작은따옴표가 들어 있으면 두 번 써서 이스케이프해야 합니다. 다음 패턴은 공백과 작은따옴표를 함께 처리합니다.
=SUM(INDIRECT("'"&SUBSTITUTE(A2,"'","''")&"'!$B$2:$B$100"))3. 명명 범위(Named Range)와의 연동
INDIRECT 함수는 명명 범위(Named Range)와 결합하면 더욱 강력해집니다. 시트 이름 앞에 고정된 접두사를 붙여 명명 범위를 미리 만들어두면, 수식을 더 직관적으로 만들 수 있습니다.
3.1 명명 범위 생성
서울시트의 B2:B100 셀을 선택합니다.- 수식 입력줄 왼쪽의 이름 상자에
매출_서울이라고 입력하고 Enter를 누릅니다. 부산시트의 B2:B100 셀을 선택하고 이름을매출_부산으로 지정합니다.대구시트의 B2:B100 셀을 선택하고 이름을매출_대구로 지정합니다.
3.2 수식 적용
이제 집계 시트의 B2 셀에는 다음과 같이 수식을 작성할 수 있습니다.
=SUM(INDIRECT("매출_"&A2))해설:
A2가서울이면"매출_"&"서울"은"매출_서울"이 됩니다.INDIRECT는 해당 이름의 범위 참조를 반환하고, 바깥의SUM이 합계를 계산합니다.
이 방식은 시트 구조가 바뀌더라도 명명 범위가 유지된다면 수식을 수정할 필요가 없다는 장점이 있습니다. 또한, 드롭다운 목록과 자주 짝을 이루어 사용됩니다.
4. 사용자 지정 수식 유효성 검사
이 예제는 선택한 지점의 B2:B100에 이미 있는 숫자만 입력하도록 확인합니다. 합계 수식이 있는 B2는 유지하고, 별도 입력 셀인 집계!C2에 규칙을 적용합니다.
4.1 선택한 시트에 값이 있는지 확인
C2를 선택한 뒤 데이터 → 데이터 유효성 검사 → 사용자 지정에 다음 수식을 입력합니다.
=IFERROR(AND(ISNUMBER($C2),$C2<>"",COUNTIF(INDIRECT("'"&SUBSTITUTE($A2,"'","''")&"'!$B$2:$B$100"),$C2)>0),FALSE)- A2에는 지점 시트명이 들어 있습니다.
COUNTIF는 해당 지점 범위에서 C2와 같은 값을 셉니다. - 값이 있으면 TRUE, 없거나 참조가 잘못되면 FALSE를 반환합니다. 중복을 금지하는 규칙이 아니라 기존 값의 존재 여부를 확인하는 규칙입니다.
- 오류 메시지를 사용하도록 설정하고 스타일은 중지를 선택합니다. 빈 입력도 제한하려면 공백 무시 옵션을 해제합니다.
유효성 검사는 직접 입력할 때의 제한입니다. 붙여넣기로 우회되거나, A2를 나중에 변경해도 기존 C2 값에 경고가 자동 표시되지 않을 수 있으므로 지점을 바꾼 뒤에는 다시 점검해야 합니다. Microsoft의 데이터 유효성 검사 주의사항을 참고하세요.
5. 성능 저하와 대안 (휘발성 함수)
INDIRECT 함수는 휘발성(Volatile) 함수입니다. 참조 값의 변경 여부와 관계없이 재계산 때 다시 평가되며, 이에 종속된 수식도 계산 부담을 늘릴 수 있습니다. 따라서 파일이 커지거나 수식이 많아지면 엑셀의 응답 속도가 현저히 느려질 수 있습니다.
Microsoft의 Excel 재계산 성능 설명에서 휘발성 함수의 특성을 확인할 수 있습니다.
5.1 성능 저하 원인
- 재계산 비용: 자동 계산 모드의 셀 변경 등으로 재계산이 발생하면
INDIRECT도 다시 평가됩니다. 통합 문서의 모든 무관한 수식이 항상 전부 재계산된다는 뜻은 아닙니다. - 반복 사용: 같은 참조를 여러 수식에서 반복 계산하거나 큰 범위를 사용하면 비용이 늘 수 있습니다. 증가 폭은 수식 구조와 데이터에 따라 달라집니다.
5.2 대안 기법
파일의 성능을 최우선으로 고려해야 한다면 다음과 같은 대안을 고려해야 합니다.
| 상황 | 추천 대안 | 설명 |
|---|---|---|
| 고정된 시트가 몇 개 | CHOOSE + MATCH |
선택할 시트가 정해져 있다면 CHOOSE에 직접 범위를 나열하고 MATCH로 항목 위치를 찾습니다. 이 함수 조합 자체는 비휘발성이며 실제 속도는 범위와 수식 구조에 따라 달라집니다. |
| 데이터를 한 표로 통합 가능 | XLOOKUP 또는 FILTER |
지점 열을 둔 통합 표에서 값을 찾거나 행을 추출합니다. 두 함수가 시트명 텍스트를 참조로 바꾸는 INDIRECT의 역할을 그대로 대체하지는 않습니다. |
| 구조가 고정되어 있음 | 구조적 참조 (Ctrl+T) | 먼저 같은 구조의 데이터를 한 표로 모은 뒤 피벗 테이블이나 SUMIFS로 집계합니다. 표 변환이나 SUMIFS 자체가 여러 시트를 자동 결합하는 것은 아닙니다. |
CHOOSE+MATCH 예시:
=SUM(CHOOSE(MATCH(A2, {"서울", "부산", "대구"}, 0), 서울!$B$2:$B$100, 부산!$B$2:$B$100, 대구!$B$2:$B$100))6. 자주 발생하는 오류(#REF!)와 예방법
INDIRECT 함수를 사용할 때 가장 흔하게 마주치는 오류는 #REF!입니다. 이 오류는 수식이 참조하는 대상이 존재하지 않거나 잘못되었을 때 발생합니다.
6.1 주요 원인 및 해결책
- 시트명 변경 또는 삭제: 문자열로 만든 주소는 시트 이름 변경에 맞춰 자동 수정되지 않습니다. A2의 선택값, 목록 원본, 실제 시트명을 함께 점검합니다.
- 따옴표 누락: 공백이 있는 시트명은
'2026 Q1'!B2처럼 감싸야 합니다. 시트명 안의 작은따옴표는 두 번 적습니다. - 잘못된 주소 또는 이름: 철자, 앞뒤 공백, 존재하지 않는 이름, 행·열 범위 초과를 확인합니다. 일반적인 Excel 시트 참조는 영문 대소문자를 구분하지 않으므로 대소문자 차이만을 원인으로 보지 않습니다.
- 닫힌 외부 통합 문서: 외부 참조라면 데이터를 읽어 오는 원본 파일이 열려 있는지 확인합니다.
6.2 팀 규칙 수립
실무에서 INDIRECT를 사용할 때는 팀의 규칙을 미리 정해두는 것이 중요합니다. 특히 시트명 관리는 오류를 예방하는 첫걸음입니다.
- 규칙 1: 시트명과 드롭다운 목록의 철자·공백을 일치시키고, 이름 변경 시 함께 갱신합니다.
- 규칙 2: 공백 등이 있는 시트명을 수식에서 참조할 때는 전체 이름을 작은따옴표로 감쌉니다. 예:
'2026 Q1'!B2. 실제 시트 탭 이름에 따옴표를 추가하는 것은 아닙니다. - 규칙 3: 수식 입력줄이나
F2편집 모드에서 조합된 주소와 따옴표를 확인합니다.
7. OFFSET과의 비교
INDIRECT와 비슷한 맥락에서 자주 언급되는 함수가 OFFSET입니다. 두 함수의 차이점을 명확히 이해하는 것이 중요합니다.
- INDIRECT: 텍스트로 만든 주소나 정의된 이름을 셀 또는 범위 참조로 해석합니다.
- OFFSET: 기준 셀로부터의 오프셋(이동 거리)을 계산하여 범위를 지정합니다. (예: 현재 셀에서 2행 아래, 3열 오른쪽)
두 함수는 모두 휘발성이므로 반복 사용량을 점검합니다. 동적 배열 Excel에서 @는 여러 값 중 하나를 선택하는 암시적 교차 연산자입니다. 펼쳐진 배열 전체를 참조하는 연산자는 #이며, 예를 들어 A2#처럼 씁니다. FILTER는 조건에 맞는 행을 추출할 때 검토할 수 있지만 용도는 다릅니다. Microsoft의 @ 설명과 참조 연산자 설명을 참고하세요.
8. 외부 통합 문서 참조 제한
INDIRECT로 다른 통합 문서를 참조하려면 데이터를 제공하는 원본 통합 문서가 열려 있어야 합니다. 닫혀 있으면 #REF!가 발생합니다. Excel 웹에서는 INDIRECT의 외부 참조가 지원되지 않습니다. 닫힌 파일에서 데이터를 가져와야 한다면 사용 환경에 맞는 Power Query 가져오기를 검토하세요. GETPIVOTDATA는 피벗 테이블 값을 조회하는 함수이므로 임의의 닫힌 파일 참조를 대신하지 않습니다.
9. 레거시 파일의 마이그레이션
기존에 INDIRECT로 복잡하게 연결된 대용량 파일을 관리하기 어렵다면, 파일 구조를 단순화하는 마이그레이션 작업이 필요합니다.
- 시트별 동일 열 구조: 모든 지점 시트가 동일한 열(B열: 매출, C열: 수량 등)을 가지고 있는지 확인합니다.
- Power Query 활용: 같은 열 구조의 데이터를 추가(Append)하여 하나의 표로 불러옵니다. 이렇게 하면
INDIRECT없이도 피벗 테이블로 쉽게 시트별 데이터를 분석할 수 있습니다. - 목록 갱신: 시트 이름을 바꾸면
목록시트의 지점명도 함께 수정합니다. 이 글의 기본 예제에는 탭 이름을 자동 수집하는 기능이 없습니다.
10. 요약 및 팁
INDIRECT 함수는 시트 이름을 동적으로 조작할 때 여러 조건에서 사용할 수 있는 도구입니다. 하지만 그 힘만큼 주의해야 할 점도 많습니다.
- 필요할 때만 사용: 파일의 속도가 중요하다면 최대한 피하고, 드롭다운과의 연동이나 특정 시트의 데이터를 동적으로 가져올 때만 사용하세요.
- 시트명 관리: 시트명을 변경할 때 수식이 깨지지 않도록, 시트명과 수식의 패턴을 체계적으로 관리하세요.
- 오류 확인:
#REF!가 발생할 때는 문자열의 따옴표가 제대로 들어갔는지, 시트명이 실제로 존재하는지 확인해야 합니다. - 대안 검토: 파일이 커지거나 성능이 저하된다면,
CHOOSE/MATCH,XLOOKUP,Power Query등의 대안을 적극적으로 고려해야 합니다.
이 패턴들을 숙지하면 여러 개의 시트를 일일이 복사해서 붙여넣는 수고로움 없이, 한 번의 드롭다운 선택으로 모든 지점의 데이터를 통합 관리할 수 있게 됩니다.
구체 입력 실행 예시
아래는 가상 숫자로 예상값을 확인하는 예제입니다. Excel 실행 결과를 기록한 것이 아니므로 새 통합 문서에서 입력과 출력을 대조하세요.
지점 선택과 합계 확인
서울시트의 B2에 5000, B3에 3000을 입력하고 B4:B100은 비워 둡니다.부산시트의 B2에 2000, B3에 4000을 입력하고 B4:B100은 비워 둡니다.집계시트의 A2에 서울을 선택하고 B2에 다음 수식을 입력합니다.
=SUM(INDIRECT("'"&SUBSTITUTE(A2,"'","''")&"'!$B$2:$B$100"))서울일 때 예상 합계는 8000, 부산으로 바꾸면 6000입니다. A2가 서울이면 따옴표와 주소를 붙이는 식이 '서울'!$B$2:$B$100이라는 텍스트를 만듭니다. INDIRECT가 반환하는 것은 그 텍스트가 가리키는 범위 참조이며, SUM이 참조 안의 숫자를 합산합니다.
공백 포함 시트명 확인
2026 Q1 시트를 만들고 B2에 1200, B3에 800을 입력하며 B4:B100은 비워 둡니다. 목록!A5에 이 이름을 추가하고 수식 → 이름 관리자에서 지점목록의 참조 대상을 =목록!$A$2:$A$5로 넓힌 뒤 집계!A2에서 선택합니다. 같은 수식의 예상 결과는 2000입니다.
참고 자료
내용 점검일: 2026-10-01 · 함수 동작은 공식 문서, 위 합계는 가상 입력의 산술 검산 기준입니다.
메뉴 이름과 옵션은 프로그램 버전마다 다를 수 있으니, 적용 전에 공식 문서나 실제 화면에서 한 번 더 확인하세요.
작성자 소개
