엑셀 OFFSET 함수 완벽 가이드 – 동적 범위 설정하는 방법

엑셀을 사용하다 보면 특정 셀을 기준으로 몇 행 또는 몇 열 떨어진 위치의 값을 가져오거나, 특정 범위를 동적으로 설정해야 하는 경우가 있습니다. 이런 상황에서 유용한 함수가 바로 OFFSET() 함수입니다. 이번 포스팅에서는 OFFSET 함수의 사용법과 실전 활용 예제를 자세히 설명해 드리겠습니다! 😊

[목차]

---

1. OFFSET 함수란?

✅ OFFSET 함수의 역할

OFFSET 함수는 특정 기준 셀에서 지정한 행(Row)과 열(Column)만큼 이동한 셀의 값을 반환하는 함수입니다. 또한, 특정 크기의 범위를 반환할 수도 있습니다.

✅ OFFSET 함수의 기본 공식

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

• 기준셀: 기준이 되는 셀 (필수)
• 행 이동 수: 기준셀에서 이동할 행(Row) 수 (양수=아래, 음수=위) (필수)
• 열 이동 수: 기준셀에서 이동할 열(Column) 수 (양수=오른쪽, 음수=왼쪽) (필수)
• [높이]: 반환할 범위의 행(Row) 개수 (선택)
• [너비]: 반환할 범위의 열(Column) 개수 (선택)

✅ 기본적으로 OFFSET 함수는 이동한 위치의 단일 셀 값을 반환하지만, 높이와 너비를 지정하면 범위를 반환할 수도 있습니다.

---

2. OFFSET 함수의 기본 사용법

✅ 예제 1: 특정 셀에서 이동한 위치의 값 반환

📌 B2 셀에서 1행 아래, 1열 오른쪽의 값을 가져오기

=OFFSET(B2, 1, 1)

✅ 결과: 60 (B2에서 1행 아래(50) → 1열 오른쪽(60))

✅ 예제 2: 특정 크기의 범위 반환 (SUM 함수와 함께 사용)

OFFSET 함수로 특정 크기의 범위를 반환할 때, 단독으로 사용하면 #VALUE! 오류가 발생합니다. 따라서 SUM()이나 AVERAGE()와 같은 함수와 함께 사용해야 합니다.

📌 B2를 기준으로 2행 × 2열 크기의 범위 합산

=SUM(OFFSET(B2, 0, 0, 2, 2))

✅ 결과: 160 (20 + 30 + 50 + 60)

 

📌 B2를 기준으로 2행 × 2열 크기의 평균 계산

=AVERAGE(OFFSET(B2, 0, 0, 2, 2))

✅ 결과: 40 (평균 값)

💡 참고: Excel 365에서는 OFFSET이 동적 배열을 반환할 수 있지만, 이전 버전에서는 단독으로 사용하면 #VALUE! 오류가 발생하므로 반드시 다른 함수와 함께 사용해야 합니다.

---

3. OFFSET 함수의 실전 활용법

✅ 활용 예제 1: 최근 N개 데이터의 평균 구하기

OFFSET 함수를 사용하면 새로운 데이터가 추가될 때마다 자동으로 최근 N개 데이터를 참조할 수 있습니다.

📌 마지막 3개의 데이터를 평균 내기

=AVERAGE(OFFSET(A1, COUNTA(A:A)-3, 0, 3, 1))

✅ 설명:

• COUNTA(A:A)-3 → 데이터 개수에서 마지막 3개의 데이터를 참조
• 3,1 → 3개의 행을 포함한 1열 범위 지정

✅ 활용 예제 2: 동적 차트 데이터 범위 설정하기

차트를 만들 때 데이터 개수가 계속 변경된다면 OFFSET과 COUNTA를 조합하여 자동으로 업데이트되는 차트 범위를 만들 수 있습니다.

📌 차트 데이터 범위를 동적으로 지정하기

=OFFSET(A1, 0, 0, COUNTA(A:A), 1)

✅ 설명:

• A1을 기준으로 데이터 개수만큼 범위를 자동 설정
• 차트의 데이터 범위를 이 범위로 설정하면, 새로운 데이터가 추가될 때 자동 반영

---

4. OFFSET 함수 사용 시 주의할 점

✅ 1. 동적 범위 설정 시 성능 문제

• OFFSET은 동적 범위를 만들어 실시간으로 업데이트하는 특성이 있지만, 대량의 데이터에서 사용할 경우 엑셀 속도가 느려질 수 있습니다.

✅ 2. 범위를 벗어난 참조를 하지 않도록 주의

• 잘못된 값(예: 너무 큰 이동 값)을 사용하면 #REF! 오류 발생

✅ 3. INDEX 함수와 비교하여 사용 고려

• OFFSET()은 동적인 범위를 만드는 데 유용하지만, 특정 위치 값을 가져오는 경우 INDEX() 함수가 더 효율적일 수 있습니다.

---

5. 마치며

엑셀 OFFSET 함수는 특정 셀을 기준으로 동적으로 데이터 범위를 지정하거나 값을 가져올 때 유용한 함수입니다.

✅ 기본적인 사용법: 특정 행과 열만큼 이동한 위치의 값 가져오기

✅ 실전 활용: 최근 데이터 평균 구하기, 동적 차트 범위 설정

✅ 주의점: 성능 문제 고려, INDEX 함수와 비교하여 적절하게 활용

 

📌 이제 OFFSET 함수를 자유롭게 활용하여 동적인 데이터 분석과 차트를 만들어 보세요! 🚀