- 최초 작성일: 2025-01-26
- 최종 수정일: 2026-09-23
- 조회수: 22 회
- 작성자: 권현욱 (엑셀러)
- 강의 제목: 데이터 배치 형태 바꾸기
들어가기 전에
오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.
지난 겨울은 '삼한사미(사흘 춥고 나흘 미세먼지)'였는데, 이번 봄은 '미세먼지 지나가니 황사가 오는' 모양새입니다. 환절기 건강 관리 잘들 하시기 바랍니다.
- 최신 글을 불러오는 중...
데이터 배치 형태 바꾸기
핵심 요약: INDIRECT+ROW+COLUMN으로 표 재구성하기
5행 간격으로 반복되는 세로 자료의 패턴을 찾아, INDIRECT로 텍스트 주소를 셀 참조로 바꾸고 ROW·COLUMN으로 위치를 계산하면 4열 표로 재구성할 수 있습니다.
- 1단계: 반복 패턴 파악 — 5행 간격, 회원번호·회원명·생년월일·주소 순서
- 2단계: ROW·COLUMN으로 원본 셀 위치를 계산하는 수식 작성
- 3단계: INDIRECT로 계산된 텍스트 주소를 실제 참조로 변환
- 4단계: A2 셀 수식을 나머지 영역에 복사
한 동안 재미있는 것들을 살펴 보았으니까 이번 시간에는 조금 난이도가 있는 것을 다뤄볼까요?(그렇다고 해서 이번 강좌는 재미가 덜한 것이라고는 말할 수 없습니다. ^^;)
아래 왼쪽과 같은 형태의 자료를 오른쪽과 같은 표로 만들려면 어떻게 해야 할까요? VBA로 할 수도 있겠으나 이번 시간은 Excel 강좌 시간이므로 함수를 사용하여 해결해 보겠습니다.
어떤 문제를 풀려고 할 때 맨 먼저 해야할 것은 어떤 규칙성 또는 반복되는 패턴이 있는지 파악하는 일입니다. 이 사례의 경우에는 다음과 같은 규칙을 발견할 수 있습니다.
- 5행 간격으로 데이터가 반복됨
- 회원번호는 A열에 배치(1, 6, 11, 16, ...)
- 회원명은 B열에 배치(2, 7, 12, 17, ...)
- 생년월일은 C열에 배치(3, 8, 13, 18, ...)
- 주소는 D열에 배치(4, 9, 14, 19, ...)
A2 셀에 입력된 수식은 다음과 같습니다. 얼핏 무시무시해 보입니다만, 위의 규칙에 해당하는 사항을 엑셀이 알아들을 수 있게 수식으로 표현한 것입니다.
=INDIRECT("Sheet1!A"&ROW(Sheet1!$A$1)+(ROW(Sheet1!A1)-1)*5+COLUMN(Sheet1!A1)-1)Indirect는 '텍스트 문자열로 지정한 셀 주소로 돌려주는 함수'로, 다음과 같은 형태로 사용됩니다.
A1 셀로부터 데이터가 시작되므로 기준이 되는 절대 주소로 지정하였고, 5행 간격으로 데이터를 불러오기 위해 '(ROW(Sheet1!A1)-1)*5', Sheet1의 A열에 있는 데이터를 다른 시트의 A2셀부터 오른쪽으로 가져오기 위해 '+COLUMN(Sheet1!C2)-1' 해주었습니다.
절대 주소와 상대 주소를 혼합하여 사용하였으므로 A2 셀에 수식을 작성한 다음, 나머지 영역으로 수식 복사하면 되겠습니다. 수식을 그냥 눈으로만 훑지 말고 빈 종이에 셀 주소를 적어가며 몇 번 흐름을 따라가보면 보다 쉽게 이해되리라 생각합니다.
참고: 이미지에 대하여
이 글은 원래 네이버 포스트에 게재되었던 글로, 네이버 포스트 서비스 종료로 네이버 블로그로 옮기는 과정에서 원본 이미지가 소실되었습니다. 위 이미지는 본문 설명을 바탕으로 재구성한 예시 화면이며, 실제 엑셀 화면과 세부 디자인은 다를 수 있습니다.
정리 — 반복 패턴을 수식으로 표현하기
| 요소 | 역할 |
|---|---|
| 패턴 파악 | 5행 간격, 항목 순서(번호·이름·생년월일·주소) 확인 |
| ROW/COLUMN | 현재 위치를 기준으로 원본 행·열 번호 계산 |
| INDIRECT | 계산된 텍스트 주소를 실제 셀 참조로 변환 |
| 수식 복사 | 절대/상대 참조를 혼합해 한 번에 전체 범위로 확장 |
자주 묻는 질문 (FAQ)
Q1. 이런 데이터 재배열 문제는 어떻게 접근해야 하나요?
가장 먼저 데이터가 몇 행(또는 열) 간격으로 반복되는지, 각 항목이 몇 번째 위치에 오는지 규칙을 찾아냅니다. 이 사례에서는 5행 간격으로 회원번호·회원명·생년월일·주소·빈 줄이 반복됩니다.
Q2. INDIRECT 함수는 어떤 역할을 하나요?
텍스트 문자열로 만든 셀 주소(예: "Sheet1!A6")를 실제 셀 참조로 바꿔주는 함수입니다. ROW, COLUMN으로 계산한 위치 값을 문자열로 조합한 뒤 INDIRECT로 감싸면 원하는 셀의 값을 가져올 수 있습니다.
Q3. 복잡한 수식을 이해하기 어려울 때는 어떻게 하나요?
눈으로만 보지 말고 종이에 실제 셀 주소를 하나씩 적어가며 수식이 어떤 값을 계산하는지 손으로 따라가 보면 이해하기 쉽습니다.
마치며
VBA에 대한 기초 지식을 공부하실 분은 아이엑셀러 닷컴 사이트 상단 메뉴에서 [Excel 강의] - [Excel 입문]을 먼저 보시면 이해하기 쉽습니다.
