ExcelOFFSET동적 범위

엑셀 OFFSET

기준 셀에서 이동·크기 지정해 범위 반환 — 동적 차트·자동 확장 범위의 표준

OFFSET 수식 구조

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

기준 (reference)

이동의 출발점이 되는 셀 또는 범위

행이동, 열이동 (rows, cols)

기준에서 떨어진 거리. 음수면 위쪽·왼쪽

높이, 너비 (height, width, 선택)

결과 범위의 크기. 생략하면 기준과 동일한 크기. 1·1이면 단일 셀

OFFSET 자체는 결과를 표시하지 않습니다 — 범위를 반환할 뿐이라 보통 SUM·AVERAGE· COUNTA 안에 감싸서 씁니다. INDIRECT처럼 휘발성 함수라 잦은 재계산이 일어납니다.

직접 체험해보세요

기준 셀 B2(주황)에서 4개 슬라이더로 이동·크기를 조절하면 결과 영역(녹색)이 실시간으로 움직입니다

B11=SUM(OFFSET(B2, 2, 1, 2, 2))→194
2
1
2
2
ABCDEF
1111213141516
2212223242526
3313233343536
4414243444546
5515253545556
6616263646566
7717273747576
8818283848586

기준

B2

반환 범위

C4:D5

SUM

194

실전 활용 예제

=OFFSET(A1, 2, 3)

단일 셀 이동 — A1에서 2행 아래, 3열 오른쪽인 D3 셀을 반환. 좌표 기반 셀 참조에 사용.

예시: =OFFSET(A1, 2, 3) → D3의 값
=SUM(OFFSET(A1, 0, 0, COUNTA(A:A), 1))

자동 확장 합계 — A열에 값이 늘어날 때마다 SUM 범위가 자동으로 확장. 매일 데이터가 쌓이는 시트에 유용.

예시: A열에 100개 → 100개 합 → 동적 합계
=AVERAGE(OFFSET(B1, COUNTA(B:B)-7, 0, 7, 1))

최근 N개 평균 — B열의 마지막 7개 값 평균. 이동 평균(MA7) 만들 때 클래식 패턴.

예시: 최근 7일 매출 평균 → 이동 평균
=OFFSET(B1, 0, 0, COUNTA(B:B), 1)

이름 정의로 동적 차트 범위 — 이 수식을 이름 정의에 넣으면 데이터 추가에 따라 차트가 자동으로 늘어남.

예시: 이름 '매출범위'로 정의 → 차트 데이터 범위

다른 엑셀 함수도 알아보세요

INDIRECT (동적 참조) →INDEX (좌표 조회) →ROW / COLUMN (위치 함수) →VLOOKUP →
더 알아보기

OFFSET vs INDEX vs INDIRECT — 동적 범위 3대장

▼

셋 다 "동적 범위"를 만들지만 입력 방식과 비용이 다릅니다. 실무에서는 가능하면 INDEX, 어쩔 수 없을 때 OFFSET·INDIRECT 순서로 고려하는 게 정석입니다.

함수강점휘발성
OFFSET크기 가변YES
INDEX위치 지정NO ⭐
INDIRECT시트 동적YES

대체 패턴: OFFSET(A1, n, 0)은 INDEX(A:A, n+1)로 대부분 대체 가능합니다. INDEX가 비휘발성이라 큰 시트에서 훨씬 빠릅니다. 범위 크기 자체를 가변으로 만들어야 할 때만 OFFSET이 유일한 답입니다.

동적 차트 범위 — OFFSET의 대표 응용

▼

"데이터를 추가할 때마다 차트가 자동으로 늘어나는" 시나리오는 OFFSET + 이름 정의 조합으로 만듭니다. M365의 표(Ctrl+T)가 같은 일을 해주지만 호환성을 챙긴다면 여전히 유효한 기법입니다.

3단계 레시피

1. 수식 → 이름 관리자 → 새로 만들기

2. 이름 매출범위, 참조: =OFFSET($B$2, 0, 0, COUNTA($B:$B)-1, 1)

3. 차트의 데이터 계열 편집 → 값: =Sheet1!매출범위

B2부터 시작해 COUNTA로 측정한 만큼 자동으로 영역을 잡으므로, 새 행을 추가하면 차트가 따라 늘어납니다. 빈 셀이 중간에 끼면 COUNTA가 정확치 않으니 깔끔하게 채워진 데이터에서만 안전합니다.

OFFSET의 함정 — 휘발성과 디버깅 난이도

▼

OFFSET은 INDIRECT와 함께 엑셀의 대표적인 휘발성 함수입니다. 어떤 셀이 바뀌어도 모두 재계산되므로 수백 개를 깔면 시트가 체감 가능하게 느려집니다.

- 영역이 시트 밖으로 나가면 #REF!

- 셀 추적(F5 → 참조 셀)에서 잘 안 보임 → 디버깅 어려움

- 차트 데이터 범위로 쓰면 차트가 깜빡거릴 수 있음

실무 권장: 데이터가 안정적으로 늘어나는 시트면 표(Ctrl+T)로 전환하세요. 표는 자동 확장에 비휘발성, 구조적 참조까지 지원합니다. M365라면 FILTER가 더 깔끔한 대안일 때가 많습니다.

자주 묻는 질문 (FAQ)

▼

Q. OFFSET을 그냥 셀에 입력하면 왜 값이 안 보여요?

OFFSET은 범위를 반환합니다. 단일 셀이면 그 셀의 값이 보이고, 여러 셀이면 첫 셀만 보이거나 M365에선 배열로 펼쳐집니다. 대부분은 SUM(OFFSET(...))처럼 집계 함수로 감쌉니다.

Q. 음수 행/열 이동도 되나요?

네. OFFSET(C5, -2, -1)은 위로 2칸, 왼쪽 1칸 — B3. 단, 이동 결과가 시트 밖이면 #REF! 오류.

Q. 높이·너비를 음수로?

안 됩니다. 크기는 양의 정수만 허용. 음수면 #REF!. 정 음수 방향이 필요하면 기준점을 이동시키고 양수 크기를 쓰세요.

Q. M365의 표나 FILTER로 OFFSET을 완전히 없앨 수 있나요?

"자동 확장" 시나리오는 대부분 표가 더 우아하게 처리합니다. "최근 N개"는 TAKE(범위, -N)으로, "조건 부합 행 추출"은 FILTER로 대체 가능합니다. 최신 엑셀을 쓴다면 OFFSET 의존도를 줄이는 게 장기적으로 유리합니다.