최초 작성일: 2013-01-17
최종 수정일: 2026-09-21
조회수: 68 회
작성자: 권현욱 (엑셀러)
강의 제목: 병합된 셀에서의 수식 복사

들어가기 전에

오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.

// 질문 하나

안녕하세요

자동채우기 사용중 궁금한점이 있어서 질문을 올려봅니다.
시트 2 에서 시트 1의 자료를 가져와서 사용하는데요..
시트 2의 B1은 =Sheet1!A2 이렇게 했는데
그래서 자동 채우기를 사용해서 아래로 쭉 끌면...
2씩 건너띄어서 채우기가 되네요..

2칸을 하나씩 셀병합해서 그런거 같은데요...

시트2의 C 열처럼 저렇게 자동채우기를 쓸수 있는 방법이 없을까요?

자료 찾아봐도 잘 안나오는데..

도움좀 부탁드리겠습니다.

안녕히 계세요.

노트북 교체에 즈음하야… 예전 파일들을 정리하는 과정에서
오래 전에 갈무리해 둔 질문들을 무더기로 발견했습니다.
오늘 강좌는 그 중 하나!
답해 드렸는지 기억나지 않는데 복습 겸해서 다루어보도록 하죠.

저자: 권현욱(엑셀러), 아이엑셀러 대표

필자는 Excel 컨설턴트, 작가, 그리고 크리에이터입니다. 현재 Microsoft Excel MVP이며, 『챗GPT+엑셀 업무자동화 정석』을 비롯한 10여 권의 도서를 집필했습니다. Excel 자동화 및 생산성 향상 분야에서 25년 넘는 경력을 보유하고 있습니다.

권현욱(엑셀러) 님의 최신 포스트:

최신 글을 불러오는 중...

엑셀 병합된 셀에서 수식 복사하기, 자동 채우기가 건너뛸 때

Q: 셀이 병합된 상태에서 자동 채우기를 하면 왜 값이 건너뛰나요?

병합된 셀에 =A2를 넣고 자동 채우기를 하면 병합된 크기(예: 2행)만큼 건너뛴 셀(A2, A4, A6…)을 참조하기 때문입니다. 그래서 1, 3, 5, 7, 9… 값이 채워집니다.

Q: 병합된 셀에서 1, 2, 3, …을 빠짐없이 채우려면 어떻게 하나요?

OFFSET과 ROUNDUP 함수를 씁니다. 2행씩 병합되어 있다면 =OFFSET($A$1,ROUNDUP(ROW()/2,0),0)처럼 행 번호를 2로 나눈 뒤 올림해서 기준 셀(A1)에서 그만큼 아래로 이동한 셀을 가져옵니다.

Q: 셀이 3개씩 병합되어 있을 때는 어떻게 하나요?

행 번호를 나누는 수를 병합된 셀의 개수인 3으로 바꿔 =OFFSET($A$1,ROUNDUP(ROW()/3,0),0)으로 입력합니다.

핵심 요약: 병합된 셀에서 수식을 빠짐없이 복사하기

병합된 셀에서 자동 채우기를 하면 참조가 병합된 크기만큼 건너뜁니다. OFFSET과 ROUNDUP 함수로 행 번호를 병합된 셀 수로 나눈 만큼 이동하게 하면 1, 2, 3…이 빠짐없이 채워집니다.

문제: 2행씩 병합된 셀에 =A2를 자동 채우기하면 1, 3, 5, 7, 9…가 채워집니다.
해결(2행 병합): =OFFSET($A$1,ROUNDUP(ROW()/2,0),0)
해결(3행 병합): =OFFSET($A$1,ROUNDUP(ROW()/3,0),0)
원리: 병합된 셀의 첫 행 번호를 병합 크기로 나눠 올림하면 1, 2, 3…이 되고 OFFSET이 그 행만큼 내려간 셀을 가져옵니다.

1. 무엇이 문제일까요?

예제 파일의 Workplace 시트로 가서 C2, D2 셀에 입력된 수식을 살펴보고 오시기 바랍니다.

번호(A)	Copy 1(C) `=A2`	Copy 2(D) `=OFFSET(…)`
1	1	1
2	1	1
3	3	2
4	3	2
5	5	3
6	5	3
7	7	4
8	7	4
9	9	5
10	9	5
11	11	6
12	11	6
13
14
15
16
17
18
19
20

C2 셀에는 '=A2' 라는 수식이 들어 있습니다.

셀이 병합되어 있는 상태에서 이 수식을 자동 채우기 하면
C열에 나타난 것과 같이 1, 3, 5, 7, 9,… 값이 채워집니다.

이것을 1, 2, 3, 4, 5, 6,… 이렇게 빠짐없이 표시되도록 하려면
어떻게 해야 하는가… 하는 것입니다.

2. OFFSET과 ROUNDUP 함수로 해결하기

D2 셀에 사용된 수식은 이렇습니다.

=OFFSET($A$1,ROUNDUP(ROW()/2,0),0)

Offset 함수와 Roundup 함수를 이용했군요.
2행 간격으로 셀이 병합되어 있으니 행 번호를 2로 나눈 후 올림처리 했습니다.

Offset(reference, rows, cols, height, width)

인수	설명
reference	Offset의 기반으로 삼을 기준 위치
rows	행 방향 이동 수
cols	열 방향 이동 수
(height)	반환되는 참조의 높이(생략하면 1)
(width)	반환되는 참조의 너비(생략하면 1)

위의 수식에서는 height와 width가 생략된 형태로 사용되었습니다.

참고: 수식의 동작

병합된 셀은 첫 행(D2, D4, D6…)에 수식이 들어가므로 ROW()는 2, 4, 6…이고, ROW()/2를 올림하면 1, 2, 3…이 됩니다. OFFSET($A$1,1,0)은 A2, OFFSET($A$1,2,0)은 A3이므로 번호가 순서대로 채워집니다. 이 수식은 첫 번째 병합 셀이 2행(머리글 바로 아래)에서 시작한다고 가정합니다.

3. 셀이 3개씩 병합되어 있다면?

만약 셀이 3개씩 병합되어 있다면 어떻게 하면 될까요?

=OFFSET($A$1,ROUNDUP(ROW()/3,0),0)

이렇게 하시면 되겠지요?
Offset 함수에 대해 잘 이해해 두시기 바랍니다.

정리 — 병합된 셀 수에 따른 수식

병합된 셀 수	수식
2행씩	`=OFFSET($A$1,ROUNDUP(ROW()/2,0),0)`
3행씩	`=OFFSET($A$1,ROUNDUP(ROW()/3,0),0)`
n행씩	`=OFFSET($A$1,ROUNDUP(ROW()/n,0),0)` (n은 병합된 셀 수)