엑셀 INDIRECT OFFSET 함수 사용법 - 동적 참조와 동적 범위

지금까지 다룬 함수들은 참조 범위가 수식에 고정되어 있었어요.
=SUM(B2:B10)처럼 범위를 직접 지정하면, 데이터가 추가되어도 범위가 자동으로 늘어나지 않습니다.

 

INDIRECT와 OFFSET은 참조 범위를 "동적"으로 만들어주는 함수예요.
데이터가 변하거나 사용자의 선택에 따라 참조 대상이 자동으로 바뀌도록 할 수 있습니다.

 

이 글에서는 INDIRECT의 문법과 활용, OFFSET의 문법과 동적 범위 만들기, 실무 예제를 다룹니다.
다른 함수에 비해 난이도가 있으므로 천천히 따라와 주세요.

INDIRECT 함수 - 텍스트를 셀 주소로 변환

=INDIRECT(참조텍스트)

 

INDIRECT는 텍스트 문자열을 실제 셀 참조로 바꿔주는 함수예요.
예를 들어 셀에 "B5"라는 텍스트가 입력되어 있으면, 그 텍스트를 실제 B5 셀 주소로 인식합니다.

 

A1에 "B5" 입력

=INDIRECT(A1)    → B5 셀의 값을 반환
=INDIRECT("B5")  → 동일한 결과

 

언뜻 보면 왜 필요한지 모르겠지만, 핵심은 "참조 대상을 텍스트로 조립할 수 있다"는 점이에요.
셀 값이나 수식 결과에 따라 어떤 셀/범위를 참조할지 동적으로 결정할 수 있습니다.

INDIRECT 예제 1 - 다른 시트를 동적으로 참조

시트가 "1월", "2월", "3월"로 나뉘어 있고, 각 시트의 B2셀에 매출 합계가 있다고 가정할게요.
A1 셀에서 월을 선택하면 해당 시트의 데이터를 자동으로 가져오고 싶을 때:

 

=INDIRECT("'" & A1 & "'!B2")

 

A1에 "2월"을 입력하면:

• 수식이 "'" & "2월" & "'!B2" → "'2월'!B2"로 조립됨
• INDIRECT가 이것을 실제 시트 참조로 변환
• 2월 시트의 B2 값이 반환됨

 

A1 값을 "3월"로 바꾸면 자동으로 3월 시트의 B2를 참조해요.
9편에서 다룬 드롭다운 목록과 함께 쓰면 월을 선택할 때마다 데이터가 바뀌는 대시보드를 만들 수 있습니다.

INDIRECT 예제 2 - 종속 드롭다운 목록

9편에서 드롭다운 목록을 다뤘는데, INDIRECT를 활용하면 "상위 선택에 따라 하위 목록이 바뀌는" 이중 드롭다운을 만들 수 있어요.

 

원리:

1. 상위 항목(예: 영업팀, 개발팀)을 이름 정의로 등록
2. 하위 드롭다운의 유효성 검사 원본에 =INDIRECT(상위셀) 입력
3. 상위에서 "영업팀"을 선택하면, "영업팀"이라는 이름 정의 범위가 하위 목록에 표시됨

 

설정 순서:

1. 각 하위 목록 범위에 이름 정의 ([수식] → [이름 관리자])
   • 영업팀: H2:H5 범위, 개발팀: I2:I5 범위
2. 상위 셀(예: C2)에 드롭다운 설정 (영업팀, 개발팀)
3. 하위 셀(예: D2)의 유효성 검사 원본에 =INDIRECT($C2) 입력

 

C2에서 "영업팀"을 선택하면 D2의 드롭다운에 영업팀 소속 직원만 표시돼요.

OFFSET 함수 - 기준 셀에서 이동한 위치 참조

=OFFSET(기준셀, 행이동, 열이동, [높이], [너비])

 

인수	설명	필수
기준셀	시작 위치	필수
행이동	아래로 이동할 행 수 (음수=위로)	필수
열이동	오른쪽으로 이동할 열 수 (음수=왼쪽)	필수
높이	반환할 범위의 행 수	선택 (기본: 1)
너비	반환할 범위의 열 수	선택 (기본: 1)

 

기본 예시:

=OFFSET(A1, 3, 1)

A1에서 3행 아래, 1열 오른쪽 → B4 셀의 값을 반환

 

범위 반환 예시:

=SUM(OFFSET(A1, 0, 0, 5, 1))

A1에서 시작해서 5행 x 1열 범위(A1:A5)의 합계를 구합니다.

OFFSET으로 동적 범위 만들기

OFFSET의 가장 대표적인 활용이에요.
데이터가 추가되면 범위가 자동으로 확장되는 "동적 범위"를 만들 수 있습니다.

 

핵심 공식:

=OFFSET(시작셀, 0, 0, COUNTA(열전체)-1, 1)

 

COUNTA는 비어 있지 않은 셀 수를 세는 함수예요.
데이터가 추가되면 COUNTA 결과가 커지고, OFFSET의 높이가 늘어나면서 범위가 자동 확장됩니다.

 

구체적인 예시:

B열에 머리글(B1)과 데이터(B2:B8)가 있을 때:

=OFFSET(B2, 0, 0, COUNTA(B:B)-1, 1)

• COUNTA(B:B) = 8 (머리글 포함 8개 셀에 값이 있음)
• 8 - 1 = 7 (머리글 제외)
• OFFSET(B2,0,0,7,1) → B2:B8 범위를 반환

 

B9에 새 데이터를 추가하면 COUNTA가 9가 되고, 범위가 B2:B9로 자동 확장돼요.

동적 범위 실무 활용 - 자동 확장 드롭다운

동적 범위를 이름 정의에 등록하면, 드롭다운 목록이 데이터 추가에 따라 자동으로 늘어나요.

 

1. [수식] → [이름 관리자] → [새로 만들기]
2. 이름: 부서목록
3. 참조 대상: =OFFSET(Sheet1!$H$2,0,0,COUNTA(Sheet1!$H:$H)-1,1)
4. [확인]
5. 드롭다운 유효성 검사 원본에 =부서목록 입력

 

이제 H열에 부서를 추가하면 드롭다운에도 자동 반영됩니다.
9편에서 "표(Ctrl+T)로 자동 확장"을 소개했는데, OFFSET 방식은 표 없이도 같은 효과를 낼 수 있어요.

INDIRECT vs OFFSET 비교

항목	INDIRECT	OFFSET
역할	텍스트를 참조로 변환	기준 셀에서 이동한 위치 참조
주요 용도	시트 동적 참조, 종속 드롭다운	동적 범위, 이동 참조
범위 크기 지정	불가 (텍스트로 직접 써야 함)	높이/너비 인수로 가능
휘발성	예 (매번 재계산)	예 (매번 재계산)
성능 영향	대량 사용 시 느려질 수 있음	대량 사용 시 느려질 수 있음

 

두 함수 모두 "휘발성(volatile)" 함수예요.
셀이 변경될 때마다 항상 다시 계산되므로, 수만 행에 걸쳐 대량으로 사용하면 파일이 느려질 수 있습니다.

 

소규모 데이터(수천 행 이하)에서는 성능 문제가 거의 없어요.
대용량 데이터에서는 표(Table) 기능이나 INDEX 기반 동적 범위를 대안으로 고려하세요.

자주 하는 실수 / 주의사항

1. INDIRECT에서 시트명에 공백이 있으면 작은따옴표 필수

시트명이 "1월 매출"처럼 공백을 포함하면, 작은따옴표로 감싸야 해요.

=INDIRECT("'" & A1 & "'!B2")

작은따옴표를 빠뜨리면 #REF! 오류가 납니다.

2. INDIRECT는 닫힌 통합문서를 참조할 수 없음

다른 파일을 INDIRECT로 참조하려면 해당 파일이 열려 있어야 해요.
닫힌 파일을 참조하면 #REF! 오류가 발생합니다.

3. OFFSET의 높이/너비에 0이나 음수를 넣으면 오류

OFFSET의 높이와 너비는 1 이상의 양수여야 해요.
COUNTA 결과가 0이 되는 경우를 대비해 MAX 함수로 감싸는 게 안전합니다.

=OFFSET(B2, 0, 0, MAX(1, COUNTA(B:B)-1), 1)

관련 함수 안내

Excel 365에서는 FILTER 함수로 동적 범위를 더 간단하게 만들 수 있어요.
OFFSET+COUNTA 조합보다 직관적이고 성능도 좋습니다.

 

다음 글에서는 엑셀 차트 기초를 다룰 예정이에요.
막대 차트, 꺾은선 차트, 원형 차트 등 기본 차트 유형과 서식 설정 방법입니다.