심화학습 - OFFSET

심화학습 - OFFSET

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

• 기준셀: 시작 기준이 되는 셀
• 행이동 / 열이동: 기준 셀로부터 이동할 위치 (음수도 가능)
• 높이/너비: 반환할 범위 크기 설정 (기본값은 1x1)

---

1. 동적 범위로 최근 데이터 평균 구하기

예제: 최근 7일간 판매량 평균을 자동 계산

날짜판매량

	A	B
1	날짜	판매량
2	1일	100
...	...	...
32	31일	230

=AVERAGE(OFFSET(B2, COUNTA(B2:B100)-7, 0, 7, 1))

• COUNTA로 데이터 개수 측정 → 마지막 7개 선택
• 매일 데이터가 늘어나도 자동으로 최근 7개 평균 계산

---

2. 사용자 입력 값에 따라 범위 동적 설정

	A	B
1	5	=SUM(OFFSET(B1, 0, 0, A1, 1))

 

→ A1에 입력된 숫자만큼 B열의 합계를 자동 계산
(예: A1에 5 입력 → B1~B5 합산)

---

3. 월별 자동 차트 범위 설정 (이름 정의와 함께)

이름 정의:

매출범위 = OFFSET(Sheet1!$B$2, 0, 0, COUNTA(Sheet1!$B:$B)-1)

• B2부터 시작하여, 입력된 행 수만큼 자동으로 범위 확장
• 이 이름을 차트의 데이터 범위로 지정하면
  → 데이터 추가 시 차트 자동 업데이트!

---

4. 조건에 따라 셀 참조 이동

=OFFSET(B2, MATCH("부산", A3:A100, 0)-1, 2)

• A열에서 "부산"이 입력된 행의 B열 오른쪽 2열(=D열) 값을 반환
• 특정 키워드 기준으로 동적으로 셀 참조 가능

---

5. OFFSET + INDIRECT로 시트 분기 참조

=SUM(OFFSET(INDIRECT("'"&A1&"'!B2"), 0, 0, 5, 1))

• A1에 "1월" 입력 시 → '1월' 시트의 B2:B6 합산
• 시트 이름 + 위치 이동을 조합해 범용 참조 가능

---

6. OFFSET + MATCH + INDEX 조합 (고급 응용)

=INDEX(OFFSET(A1, MATCH("제품A", A2:A100, 0), 1, 1, 5), 1, 3)

• "제품A"가 입력된 행에서 오른쪽 5칸 범위 중 3번째 값을 반환
• 수평 범위 + 수직 위치를 지정해 정확히 필요한 데이터 추출

---

| 실무 자동화 활용 요약

목적	활용 예시
자동 합계/평균	최근 데이터, 지정 개수에 따라 동적 집계
동적 차트 구성	데이터 추가 시 자동 범위 확장
셀 이동 참조	특정 조건에 따라 셀을 이동해 값 추출
보고서 유연성 확보	입력 값이나 시트명에 따라 참조 범위 자동화

 

---

| 사용 시 주의사항

• OFFSET은 배열 형태의 참조를 반환하므로
  → SUM, AVERAGE, INDEX, CHART 등과 함께 써야 유효
• 계산이 반복되면 성능 저하 발생 가능 (대량 문서 주의)