- 최초 작성일: 2008-02-04
- 최종 수정일: 2026-09-24
- 조회수: 41 회
- 작성자: 권현욱 (엑셀러)
- 강의 제목: OFFSET 함수 응용예제 두 가지
들어가기 전에
오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.
질문 하나
안녕하세요! 셀 병합이 되어 있는 경우에는 자동 채우기를 사용할 수 없는 것인가요? 시트2에 있는 데이터를 시트1로 가져와야 하는데 시트2에는 셀이 병합되어 있거든요. =Sheet1!A2라고 한 다음 아래로 끌어보면 2칸씩 건너 뛰면서 복사가 되어 버려요. 방법이 없을까요?
또 비슷한 질문 하나
안녕하세요. 게시판을 찾다가 못 찾겠어서 질문 드립니다. A열에 데이터가 들어 있습니다. 특정 위치로부터 3번째마다 데이터를 가져오도록 하려면 어떻게 하나요? 저는 엑셀 2003 사용자입니다. 방법이 없을까요? ㅜ.ㅜ
이 시점에서… 불현듯, '용비어천가'의 다음 한 구절이 떠오릅니다(왜지~~?).
불휘 기픈 남간 바라매 아니 뮐쌔, 곶 됴코 여름 하나니
새미 기픈 므른 가마래 아니 그츨쌔, 내히 이러 바라래 가나니
- 최신 글을 불러오는 중...
OFFSET 함수 응용예제 두 가지
핵심 요약: OFFSET + ROUNDUP/배수로 원하는 위치의 값 가져오기
병합된 셀 채우기는 ROUNDUP((ROW()-기준행)/병합행수,0)을, N번째 값 추출은 (ROW()-기준행)*N-1을 OFFSET의 이동 행수로 쓰면 해결됩니다.
- 문제 1: 병합된 셀에 수식 채우기 시 값이 건너뜀
- 해결 1: =OFFSET(Workplace!$A$1,ROUNDUP((ROW()-33)/2,0),0)
- 문제 2: N번째 값마다 가져오고 싶음
- 해결 2: =OFFSET(Workplace!$A$1,(ROW()-68)*$B$68-1,0)
개요
Workplace 시트의 A1:A20 영역에 데이터가 들어 있습니다. 또한 아래의 노란색 영역은 2개의 셀 단위로 셀 병합되어 있습니다.
Workplace 시트의 값을 가져오기 위해, '=Workplace!A1'라고 입력하고 수식을 아래로 복사해 보면, A1, A3, A5,… 셀의 값들만 가져오게 됩니다. 하지만 빨간색 영역에는 원하는 값들이 제대로 표시되어 있습니다.
| 노란색(=Workplace!A1 복사) | 빨간색(OFFSET 수식) |
|---|---|
| 10 | 10 |
| 30 | 20 |
| 50 | 30 |
| 70 | 40 |
| 90 | 50 |
D33 셀에는 이런 수식이 들어 있습니다.
=OFFSET(Workplace!$A$1,ROUNDUP((ROW()-33)/2,0),0,1,1)Offset, Roundup, Row 함수를 조합해서 사용하였군요. Roundup과 Row 함수는 사용법이 간단하니 아실테고… Offset 함수는 다음과 같은 형식으로 사용합니다.
사용 형식
Offset(참조영역, 행, 열, 높이, 너비)
말로 풀어쓰면, '특정한 셀의 위치를 기준으로, 지정한 행이나 열의 수만큼 떨어진 곳의 참조 영역'을 구해주는 함수입니다(무슨 소리인지 잘 이해가 안되는 분은 X0024 강좌를 참고하세요).
이 경우에는 2행 단위로 병합이 되어 있으므로 Row 함수로 행 값을 2로 나눈 결과값을 강제 반올림처리(Roundup) 해 준 것입니다. 여기서 높이와 너비는 생략할 경우 디폴트 값으로 1이 지정되므로 다음과 같이 수식을 간단히 할 수도 있습니다.
=OFFSET(Workplace!$A$1,ROUNDUP((ROW()-33)/2,0),0)이제 두 번째 질문을 해결해 보도록 하지요. 노란색 셀을 선택하고 몇번째마다 값을 가져올 것인지 적당한 값을 선택해 보세요.
| 몇번째? | 결과값 |
|---|---|
| 2 | 20 |
| 40 | |
| 60 | |
| 80 | |
| 100 | |
| 120 | |
| 140 | |
| 160 | |
| 180 | |
| 200 |
어떤 숫자를 선택하였느냐에 따라 결과값이 다르게 표시되지요? 결과값 부분에는 다음과 같은 수식이 사용되었습니다.
=OFFSET(Workplace!$A$1,(ROW()-68)*$B$68-1,0)여기서 68을 빼 주었을까요? 그렇습니다! 결과값을 표시할 위치가 C68 셀이니까 그 행의 위치값만큼을 뺀 것입니다.
Offset 함수는 아주 유용하고 중요한 함수의 하나이니까 이 기회에 잘 정리해 두세요.
설 연휴가 얼마 남지 않았습니다. 고향 가시는 분들 안녕히 잘 다녀오시고, 다들 즐거운 설 연휴 보내시기 바랍니다.
다음에 또…
정리 — OFFSET 함수 응용 수식 두 가지
| 문제 | 수식 |
|---|---|
| 병합된 셀 채우기(2행 병합) | =OFFSET(Workplace!$A$1,ROUNDUP((ROW()-33)/2,0),0,1,1) |
| N번째 값마다 가져오기 | =OFFSET(Workplace!$A$1,(ROW()-68)*$B$68-1,0) |
자주 묻는 질문 (FAQ)
Q1. 병합된 셀에서 수식을 채우면 왜 값이 건너뛰나요?
2행이 하나로 병합된 셀에 =Workplace!A1을 입력하고 아래로 채우면, 병합 셀 하나가 화면상 한 칸으로 취급되어 참조가 매번 두 행씩 이동합니다. 그 결과 A1, A3, A5처럼 홀수 행만 가져오게 됩니다.
Q2. ROUNDUP((ROW()-33)/2,0)는 어떤 역할을 하나요?
현재 행 번호에서 기준 행(33)을 뺀 값을 2(병합된 행 수)로 나누고 올림 처리해서, 지금 이 셀이 원본 데이터의 몇 번째 값을 가져와야 하는지 계산합니다. 이 값을 OFFSET의 이동 행수로 쓰면 병합 구조와 상관없이 순서대로 값을 채울 수 있습니다.
Q3. N번째 값마다 가져오는 수식은 어떻게 응용하나요?
=OFFSET(Workplace!$A$1,(ROW()-68)*$B$68-1,0)에서 B68 셀에 원하는 간격(N)을 입력하면, 현재 행 위치와 N을 곱해 몇 번째 값을 가져올지 계산합니다. B68 값을 2에서 3, 5 등으로 바꾸기만 하면 간격을 자유롭게 조절할 수 있습니다.
마치며
VBA에 대한 기초 지식을 공부하실 분은 아이엑셀러 닷컴 사이트 상단 메뉴에서 [Excel 강의] - [Excel 입문]을 먼저 보시면 이해하기 쉽습니다.
