최초 작성일: 2025-01-26
최종 수정일: 2026-09-23
조회수: 25 회
작성자: 권현욱 (엑셀러)
강의 제목: 데이터 배치 형태 바꾸기 #2

들어가기 전에

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

보통 주 2회 정도 주기로 강좌를 업데이트 해 왔는데, 당분간은 빈도가 줄어들 수도 있겠습니다. 소재가 떨어졌거나 노느라 바빠서 그런 건 아니고, 공사가 다망(?)하여 그런 것이니 양해 바랍니다. 엎어진 김에 쉬어 간다고, 이 참에 복습들 하세요.^^ 건성으로 많이 아는 것보다 하나를 알더라도 확실하게 아는 것이 중요하니까요.

지난 X0545 강좌에서 '데이터 배치 형태 바꾸기'에 대해 다루었습니다. 조금 난이도가 있는 편이라 어떨까 싶었는데, 반응이 '폭발적'... 까지는 아니고... 예상보다 뜨거웠습니다. 이에 힘을 얻어서 데이터 배치 형태와 관련된 강좌를 하나 더 준비했습니다.

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

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

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

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

데이터 배치 형태 바꾸기 #2

핵심 요약: 패턴을 찾아 INDEX+MOD로 재배열하기

데이터 재배열 문제는 반복되는 패턴을 먼저 찾아내는 것이 핵심입니다. 행·열 번호가 언제 리셋되고 언제 증가하는지 파악하면 INDEX + MOD(+ROWS·ISODD·INT) 조합으로 표현할 수 있습니다.

Case 1: 세로 방향 읽기 — 행 1~6 반복, 6에서 열 증가
Case 2: 가로 방향 읽기 — 열 1~5 반복, 5에서 행 증가
Case 3: 2행씩 교차 읽기 — 10행 주기 + 짝/홀 분기

Case 1: 세로 방향으로 읽어오기

첫 번째 상황은 왼쪽과 같은 표에서 데이터를 세로 방향으로 읽어오는 경우입니다. 즉 A1:A6 셀 값은 G1:G6 셀, B1:B6 셀 값은 G7:G12 셀,... 이런 방식으로 데이터를 세로 방향으로 가져오려면 어떻게 해야 할까요?

아이엑셀러

이런 종류의 문제를 해결하기 위해서는 '반복되는 패턴'이나 '규칙'을 찾아내는 것이 중요합니다. 아래의 그림에서 알 수 있듯이, 행 번호가 1부터 6이 될 때까지 1씩 증가하며, 6이 되는 순간, 열 번호를 1씩 증가합니다.

아이엑셀러

이것을 엑셀이 알아들을 수 있게 수식으로 나타낸 것이 다음 수식입니다. G1 셀에는 다음과 같은 수식이 들어 있습니다.

=INDEX($A$1:$E$6, MOD(ROWS($1:6), 6)+1, ROWS($1:6)/6)

물론 이 수식 말고도 다른 방법이 있을 수 있으니 연구해 보세요.

Case 2: 가로 방향으로 읽어오기

이번에는 Case 1과는 반대로 A1:E1 셀의 데이터는 G1:G5, A2:E2 셀의 데이터는 G6:G10, 이런 식으로 데이터를 가로 방향으로 읽어오는 경우입니다.

A1:E6 표를 행 단위로 가로 방향에서 세로(G열)로 재배열하는 목표 예시

아이엑셀러

이 때에는 어떤 규칙이 있는 지 찾아보세요.

아이엑셀러

열 번호가 1부터 5가 될 때까지 1씩 증가하며, 5가 되는 순간, 행 번호를 1씩 증가시킵니다. 이 규칙을 수식으로 나타내면 다음과 같이 됩니다.

=INDEX($A$1:$E$6, ROWS($1:5)/5, MOD(ROWS($5:5)-1, 5)+1)

Case 3: 가로 방향으로 2행씩 읽어오기

아~~ 슬슬 머리가 아프고 신경질이 날려고 합니다.
'뭐가 이렇게 복잡해?'
기왕 머리 아픈 김에 화끈하게 하나 더 해보도록 하죠.^^;

다음 그림과 같이 2개 행씩 데이터를 읽어오는 겁니다.

아이엑셀러

눈으로만 따라하면 잘 파악되지 않습니다. 종이를 한 장 꺼내놓고 좌표를 대입해가면서 규칙을 찾아보세요.

Case 3은 어떤 규칙이 있나요? 무질서해 보여도 잘 들여다보면 반복되는 패턴이 보입니다. 대략 다음과 같은 것들을 발견할 수 있겠습니다.

1) 10행 간격으로 무언가 규칙이 반복됨.
2) 첫 10개 행의 행 번호는 짝수행일 경우 1, 홀수행일 경우에는 2가 사용됨.
3) 다음 10개 행의 행 번호는 짝수행일 경우 3, 홀수행일 경우에는 4가 사용됨.
4) 행 번호가 10을 넘어가더라도 열 번호는 5를 넘지 않음.

이 규칙들을 수식으로 바꾼 것이 아래 수식입니다.

Case 3의 네 가지 규칙과 이를 조합한 IF·MOD·INT·ISODD 수식

아이엑셀러

=IF(MOD(ROW(), 10)=0,
INDEX($A$1:$E$6, ROW()/10*2, (ROW()/2)/(ROW()/10)+MOD(ROW(), 2)),
INDEX($A$1:$E$6, INT(ROW()/10)*2+IF(ISODD(ROW()), 1, 2),
INT(MOD(ROW(), 10)/2)+MOD(ROW(), 2)))

헛, 이렇게 복잡할 수가...

심하게 복잡해 보입니다만, 위 1) ~ 4)번 조건을 단계를 나누어 수식으로 바꿔 준 것 뿐입니다. 겁먹지 말고 수식을 토막내어 살펴보세요.

이것보다 나은 방법이 있을 것도 같은데 지금은 떠오르지 않는군요. 나중에라도 찾게 되면 공유하겠습니다. 다른 좋은 아이디어를 찾은 분은 알려주시기 바랍니다.

이렇게 여운(?)을 남겼더니 답변을 주신 분이 있군요. 역시나...^^;

'원이' 님이 이런 방법을 공유해 주셨습니다.

=INDEX($A$1:$E$6,(MOD(ROW()-1,2)+1)+(QUOTIENT(ROW()-1,10)*2),MOD((QUOTIENT(ROW()-1,2)+1)-1,5)+1)

참고: 이미지에 대하여

이 글은 원래 네이버 포스트에 게재되었던 글로, 네이버 포스트 서비스 종료로 네이버 블로그로 옮기는 과정에서 원본 이미지가 소실되었습니다. 위 이미지는 본문 설명을 바탕으로 재구성한 예시 화면이며, 실제 엑셀 화면과 세부 디자인은 다를 수 있습니다. 본문에서 언급된 'X0545 강좌'는 실제 URL을 확인하지 못해 제목만 언급된 형태로 남겨두었습니다.

정리 — 세 가지 데이터 재배열 사례

사례	패턴	핵심 함수
Case 1: 세로 읽기	행 1~6 반복, 6에서 열 +1	INDEX, MOD, ROWS
Case 2: 가로 읽기	열 1~5 반복, 5에서 행 +1	INDEX, MOD, ROWS
Case 3: 2행씩 교차	10행 주기 + 짝/홀 분기	INDEX, MOD, INT, ISODD, IF