- 최초 작성일: 2002-10-14
- 최종 수정일: 2026-09-29
- 조회수: 27 회
- 작성자: 권현욱 (엑셀러)
- 강의 제목: 병합된 셀에서 자동 채우기가 깨질 때 OFFSET과 ROW로 해결하기
들어가기 전에
오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.
Having nothing, nothing can he lose.
아무것도 가진 것이 없으니 아무것도 잃을 수가 없습니다. 무엇을 갖는다는 것, 소유한다는 것은 거기에 속박되는 것임을 언제쯤에나 깨달을 수 있을까요.
- 최신 글을 불러오는 중...
병합된 셀에서 자동 채우기가 깨질 때 OFFSET과 ROW로 해결하기
핵심 요약
2행 단위로 병합된 셀에 VLOOKUP 수식을 자동 채우기 하면 짝수 번째 행의 데이터가 제대로 표시되지 않는데, 이는 행 번호가 그대로 참조되기 때문입니다. OFFSET과 ROW 함수를 조합하면 병합 주기에 맞춰 참조 행을 정확히 계산할 수 있습니다.
- 병합된 셀에 자동 채우기를 하면 참조 행 번호가 한 칸씩만 밀려 짝수 행이 어긋납니다.
- ROW() 함수로 현재 행을 구하고 병합 주기(2, 3행 등)로 나누면 올바른 참조 행을 계산할 수 있습니다.
- OFFSET 함수로 계산된 행만큼 이동한 셀을 참조하면 문제가 해결됩니다.
질문: 병합된 셀에서 자동 채우기가 짝수 행만 이상해요
안녕하세요, 병합된 셀에 자동 채우기를 하여 아래로 끌었더니 다음과 같이 나타납니다.
| NO | 부서 | 성명 |
|---|---|---|
| 입사일자 | 근속년수 | |
=IF(OR(Source!A2=""),"",VLOOKUP(Source!A2,Source!A2:E16,1)) | =IF(OR($B20=""),"",VLOOKUP($B20,Source!$A$1:$E$16,2)) | =IF(OR($B20=""),"",VLOOKUP($B20,Source!$A$1:$E$16,3)) |
=IF(OR($B20=""),"",VLOOKUP($B20,Source!$A$1:$E$16,4)) | =IF(OR($B20=""),"",VLOOKUP($B20,Source!$A$1:$E$16,5)) | |
=IF(OR(Source!A4=""),"",VLOOKUP(Source!A4,Source!A4:E18,1)) | =IF(OR($B22=""),"",VLOOKUP($B22,Source!$A$1:$E$16,2)) | =IF(OR($B22=""),"",VLOOKUP($B22,Source!$A$1:$E$16,3)) |
=IF(OR($B22=""),"",VLOOKUP($B22,Source!$A$1:$E$16,4)) | =IF(OR($B22=""),"",VLOOKUP($B22,Source!$A$1:$E$16,5)) | |
=IF(OR(Source!A6=""),"",VLOOKUP(Source!A6,Source!A6:E20,1)) | =IF(OR($B24=""),"",VLOOKUP($B24,Source!$A$1:$E$16,2)) | =IF(OR($B24=""),"",VLOOKUP($B24,Source!$A$1:$E$16,3)) |
=IF(OR($B24=""),"",VLOOKUP($B24,Source!$A$1:$E$16,4)) | =IF(OR($B24=""),"",VLOOKUP($B24,Source!$A$1:$E$16,5)) | |
=IF(OR($B26=""),"",VLOOKUP($B26,Source!$A$1:$E$16,2)) | =IF(OR($B26=""),"",VLOOKUP($B26,Source!$A$1:$E$16,3)) | |
=IF(OR($B26=""),"",VLOOKUP($B26,Source!$A$1:$E$16,4)) | =IF(OR($B26=""),"",VLOOKUP($B26,Source!$A$1:$E$16,5)) |
보시는 것처럼 짝수 번째 자료는 제대로 표시되지 않습니다. 자동 채우기를 사용하여 해결할 수 있는 방법이 있을까요?
자주 드리는 말씀입니다만, 불규칙해 보이는 현상 속에서 일정한 패턴을 발견해 내는 일이 무엇보다 중요합니다. 이런 경우에는 어떤 패턴이 있을까요? 그렇습니다! 2행을 주기로 같은 작업이 반복되지요? 따라서 우리가 잘 아는 OFFSET 함수와 ROW 함수를 적당히 이용하시면 될 것입니다.
위 빨간색 셀에 들어있는 수식은…
=IF(OR(Source!A2=""),"",VLOOKUP(Source!A2,Source!A2:E16,1))아래와 같이 수정해 주셔야 합니다.
=OFFSET(Source!$A$1,(ROW()-1)/2,0)2행 간격으로 반복이 되므로 행 번호를 2로 나누어 준 것입니다. 그렇다면 아래와 같이 3행 간격으로 데이터가 반복되게 하려면 어떻게 해야 할까요? 그것은 오랜만에 숙제로 내드리겠습니다. 숙제치고는 너무 쉽지요? ^^
| NO | 부서 | 근속년수 |
|---|---|---|
| 성명 | 생년월일 | |
| 입사일자 | 부서전입 |
예시 사번: 02-1-001, 02-1-002, 02-1-003
정리 — 병합된 셀에 OFFSET과 ROW로 자동 채우기 적용하기
| 구성요소 | 역할 |
|---|---|
ROW() | 현재 셀의 행 번호를 반환 |
(ROW()-1)/2 | 2행 단위 병합 주기에 맞춰 실제 참조해야 할 원본 행을 계산 |
OFFSET(기준셀, 이동행수, 0) | 계산된 행만큼 이동한 셀 값을 참조 |
| 병합 주기 응용 | 병합 주기가 N행이면 나누는 수를 N으로 바꿔 동일하게 응용 가능 |
자주 묻는 질문 (FAQ)
Q1. 병합 주기가 3행이면 수식을 어떻게 바꿔야 하나요?
(ROW()-1)/2 대신 (ROW()-1)/3으로 나누는 값만 바꾸면 동일한 원리로 적용됩니다.
Q2. OFFSET 대신 다른 함수로도 해결할 수 있나요?
INDEX와 ROW를 조합해도 같은 결과를 얻을 수 있습니다. 예: INDEX(Source!$A:$A,(ROW()-1)/2+1).
Q3. 애초에 셀 병합을 하지 않으면 이 문제가 생기지 않는 것 아닌가요?
맞습니다. 가능하면 병합 대신 '선택 영역의 가운데로' 서식을 사용하는 것이 수식 작성이나 자동 채우기 측면에서 더 편리합니다. 다만 기존에 병합된 양식을 그대로 써야 하는 경우에는 이 방법이 유용합니다.
마치며
병합된 셀은 보기에는 깔끔하지만 수식 작성이나 자동 채우기에서는 예상치 못한 문제를 일으키기 쉽습니다. 오늘처럼 반복되는 주기를 파악해 ROW와 OFFSET을 조합하면, 병합 셀 환경에서도 자동 채우기를 문제없이 사용할 수 있습니다.
