=OFFSET(A1,3,2)는 A1에서 3행 아래, 2열 옆의 셀인 C4를 반환합니다. 높이와 너비도 주면 범위 전체를 반환하는데, OFFSET은 주로 이렇게 씁니다. 움직이거나 늘어나는 범위의 합계와 평균을 구할 때입니다.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Rows | Cols | Result | |
| 2 | Apple | Fruit | $1.20 | 3 | 2 | $0.80 | |
| 3 | Pear | Fruit | $1.50 | ||||
| 4 | Carrot | Vegetable | $0.80 | ||||
| 5 | Bread | Bakery | $2.40 | ||||
| 6 | Milk | Dairy | $1.10 |
A1에서 3행 아래, 2열 옆으로 가면 Carrot의 가격이 있는 C4에 도착해 $0.80이 나옵니다. Cols를 0으로 바꾸면 이름 Carrot이, Rows를 5로 바꾸면 Milk의 행이 나옵니다. 행과 열은 음수로 써서 위나 뒤로 갈 수 있으며, 시트의 위쪽이나 가장자리를 벗어나면 #REF!입니다.
OFFSET 구문
=OFFSET(reference, rows, cols, [height], [width])
reference: 시작 셀(또는 범위)입니다.rows,cols: 얼마나 이동할지입니다. 0은 그대로입니다.height,width: 이동한 셀부터 센, 반환할 범위의 크기입니다. 생략하면reference의 크기입니다.
여러 셀을 반환하는 OFFSET을 셀에 단독으로 쓰면 Excel 365에서는 결과가 분산되고, 이전 버전에서는 보통 #VALUE!가 나옵니다. SUM, AVERAGE, COUNT, MAX 안에서는 범위로 동작합니다.
마지막 N개 행의 합계
OFFSET의 대표적인 쓰임입니다. 행이 몇 개 추가되든 항상 가장 최근 행들을 덮는 합계입니다. COUNT가 값의 개수를 찾고, OFFSET이 마지막 N개 중 첫 번째까지 내려가며, 높이가 N개 행을 가져옵니다.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | Last N | Total | ||
| 2 | Jan | 4,200 | 3 | 14,900 | ||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
값이 7개이므로 OFFSET은 B1에서 7-3+1, 즉 5행 아래인 B6에서 시작해 3개 행을 가져옵니다. May부터 Jul까지, 14,900입니다. B9(8월)에 4900을 입력하면 COUNT가 이제 8을 찾으므로 합계가 Jun, Jul, Aug로 옮겨 갑니다. 범위 B2:B13은 남은 달을 위한 자리를 둡니다. 열 중간에 빈 셀이 없어야 합니다. 빈 셀이 있으면 COUNT가 적게 세어 범위가 엉뚱한 곳에 놓입니다.
이동 평균
음수 행 오프셋을 쓴 OFFSET을 열 아래로 채우면 각 행이 위쪽 행들의 구간을 갖게 됩니다. 여기서는 이번 달과 그 앞 두 달의 평균입니다.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | 3-month average |
| 2 | Jan | 4,200 | |
| 3 | Feb | 3,900 | |
| 4 | Mar | 4,800 | 4,300 |
| 5 | Apr | 5,100 | 4,600 |
| 6 | May | 4,600 | 4,833 |
| 7 | Jun | 5,300 | 5,000 |
| 8 | Jul | 5,000 | 4,967 |
C4는 B2:B4(Jan부터 Mar)의 평균인 4,300을 냅니다. 아래 행마다 구간이 한 칸씩 내려갑니다. 3을 6으로, -2를 -5로 바꾸면 6개월 평균이 됩니다(이때는 수식을 7행에서 시작하세요). 사실 이 경우에는 OFFSET이 전혀 필요 없습니다. 상대 참조가 이미 움직이므로 C4부터 아래로 채운 =AVERAGE(B2:B4)도 같은 일을 합니다. OFFSET이 제 몫을 하는 것은 구간 크기를 셀에서 가져올 때입니다.
INDEX가 더 나은 경우가 많은 이유
OFFSET은 휘발성입니다. 엑셀은 OFFSET이 어떤 셀을 가리킬지 미리 알 수 없으므로 통합 문서 어디에서든 편집이 있으면 모든 OFFSET을 다시 계산합니다. OFFSET이 수천 개인 시트는 느려집니다. INDEX도 참조를 반환하며, 시작:INDEX(...) 형태로 쓴 범위는 휘발성 없이 똑같이 늘어납니다:
=SUM(OFFSET(B2, 0, 0, E2, 1)) first E2 rows, volatile
=SUM(B2:INDEX(B2:B13, E2)) same rows, not volatile
둘 다 열의 처음 E2개 행을 읽습니다. OFFSET은 검토하기도 더 어렵습니다. 참조되는 셀 추적이나 수식을 편집할 때 엑셀이 그리는 색깔 테두리는 OFFSET이 최종적으로 반환하는 범위가 아니라 시작 셀과 인수를 보여 줍니다. 간단한 모델이나 차트 범위에는 OFFSET을, 큰 통합 문서에서는 INDEX를 쓰세요. 범위를 반환하는 방법은 INDEX에 더 있고, INDIRECT는 또 다른 휘발성 참조 함수입니다.
연습: 처음 N개월 합계
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | First N | Total | ||
| 2 | Jan | 4,200 | 4 | |||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
직접 해 보세요: F2에서 SUM 안에 OFFSET을 써서 처음 N개월의 합계를 구하세요. N은 E2에 있습니다.
자주 묻는 질문
엑셀 OFFSET은 무엇을 하나요?
시작 셀에서 지정한 행과 열만큼 떨어진 참조를 반환하며, 크기를 바꿀 수도 있습니다. =OFFSET(A1,3,2)는 A1에서 3행 아래, 2열 오른쪽의 셀인 C4입니다.
엑셀에서 마지막 N개 행의 합계는 어떻게 구하나요?
머리글에서 시작해 마지막 N개 값 중 첫 번째까지 내려갑니다: =SUM(OFFSET(B1,COUNT(B2:B100)-N+1,0,N,1)). COUNT가 값의 개수를 찾고, 높이 N이 그만큼의 행을 가져옵니다. 열에 빈칸이 없을 때만 동작합니다.
OFFSET이 휘발성인 이유는 무엇인가요?
OFFSET이 가리키는 셀은 실행한 뒤에야 알 수 있으므로, 엑셀은 통합 문서에서 변경이 있을 때마다 모든 OFFSET을 다시 계산합니다. 큰 통합 문서에서는 이것이 속도를 늦춥니다. B2:INDEX(B2:B100,N)처럼 INDEX로 만든 범위는 휘발성 없이 같은 일을 합니다.
OFFSET의 인수는 무엇인가요?
OFFSET(reference, rows, cols, [height], [width]): 시작 셀, 아래로 몇 행(음수는 위로), 옆으로 몇 열(음수는 뒤로), 그리고 선택적으로 반환할 범위의 크기입니다.
