- 최초 작성일: 2002-09-17
- 최종 수정일: 2026-09-29
- 조회수: 26 회
- 작성자: 권현욱 (엑셀러)
- 강의 제목: OFFSET, ROW, MOD로 가로 데이터를 세로 한 줄로 재배치하기
들어가기 전에
오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.
A high heart ought to bear calamities and not flee them, since in bearing them appears the grandeur of the mind and in fleeing them cowardice of the heart.
숭고한 마음은 고난에 부딪쳤을 때 당당히 극복해 나가되 결코 물러서는 일이 있어서는 안 됩니다. 왜냐하면 고난을 극복할 때는 훌륭한 정신이 발휘되지만 고난에서 도망치면 소심한 마음이 길러지기 때문입니다.
위기란 '위험'과 '기회'의 복합어로서, 겁쟁이에게는 '위험'으로 다가오지만 용감한 사람한테는 '기회'가 된다지요?
- 최신 글을 불러오는 중...
OFFSET, ROW, MOD로 가로 데이터를 세로 한 줄로 재배치하기
핵심 요약
행마다 3개씩 가로로 나열된 데이터(A B C / D E F / G H I …)를 세로 한 줄(A / B / C / D …)로 재배치할 때, OFFSET과 ROW, MOD 함수를 조합하면 순서대로 자동 추출할 수 있습니다.
- INT((ROW()-1)/3)으로 원본의 몇 번째 행인지 계산합니다.
- MOD(ROW()-1,3)으로 그 행 안에서 몇 번째 열인지 계산합니다.
- OFFSET($A$1,계산된행,계산된열)로 정확한 위치의 값을 순서대로 가져옵니다.
질문: 가로로 나열된 데이터를 세로 한 줄로 바꾸고 싶어요
(중략) 아래와 같이 행 방향으로 3개씩 데이터가 있다고 할 때(A B C / D E F / G H I …), 이것을 세로로 A, B, C, D, E, F … 순서가 되도록 만들고자 합니다. 답변 부탁드립니다.
언젠가 강좌 시간에 다룬 것도 같고 Q&A 게시판에 그냥 답글만 단 것도 같고… 하여튼 무지하게 헷갈립니다만, OFFSET 함수에 대해 다시 생각해 볼 수 있는 예제가 될 것 같아 강좌 시간에 다시금 설명을 드립니다.
강좌가 거의 300회(VBA 강좌를 포함하면 450회를 훌쩍 넘는군요 ^^)에 육박하다 보니 언제 무엇을 하였는지 잘 기억이 나지 않을 때가 대부분입니다. 어서 홈페이지에 검색 기능을 만들어 넣어야 많은 분들이 편리하게 검색하실 텐데… 현재 Exceller의 홈페이지는 무료 웹 호스팅을 이용하다 보니 ASP가 지원이 안 됩니다. 혹 강좌를 보시는 분 중에서 서버 용량이 팡팡 남아도는 분이 계시거나 ASP가 지원되는 무료(혹은 거의 무료에 가까운… ^^) 웹 호스팅 업체를 아시는 분이 계시다면 소개해 주시기 바랍니다. ^^
그건 그렇고, 아래와 같은 수식을 사용하면 원하는 재배치가 가능합니다.
=IF(ISBLANK(OFFSET($A$1,INT((ROW()-1)/3),MOD(ROW()-1,3))),"",
OFFSET($A$1,INT((ROW()-1)/3),MOD(ROW()-1,3)))좀 길고 복잡해 보이지요? 이 식에서 정작 중요한 부분은 아래와 같습니다.
=OFFSET($A$1,INT((ROW()-1)/3),MOD(ROW()-1,3))OFFSET 함수의 본질만 잘 이해하고 계시다면 어려운 부분은 없을 것입니다. 많이 응용해 보시기 바랍니다.
정리 — OFFSET, ROW, MOD로 데이터 방향 재배치하기
| 구성요소 | 역할 |
|---|---|
ROW()-1 | 현재 셀이 결과 목록에서 몇 번째(0부터)인지 계산 |
INT((ROW()-1)/3) | 원본에서 몇 번째 행에 있는 값인지 계산(3은 한 행의 열 개수) |
MOD(ROW()-1,3) | 그 행 안에서 몇 번째 열(0,1,2)에 있는 값인지 계산 |
OFFSET($A$1,행,열) | 계산된 행·열만큼 기준 셀(A1)에서 이동한 위치의 값을 반환 |
IF(ISBLANK(...),"",...) | 원본 데이터가 끝난 뒤 빈 셀을 참조할 때 0 대신 빈 문자열 표시 |
자주 묻는 질문 (FAQ)
Q1. 한 행에 3개가 아니라 다른 개수로 나뉜 데이터는 어떻게 처리하나요?
수식에서 3으로 되어 있는 부분(INT((ROW()-1)/3)과 MOD(ROW()-1,3))을 원하는 열 개수로 바꾸면 동일한 원리로 적용됩니다.
Q2. 빈 셀이 나오면 왜 IF(ISBLANK(...))로 감싸나요?
원본 데이터의 끝에 도달해 더 이상 값이 없는 셀을 참조하게 되면 0이 표시되는데, ISBLANK로 빈 셀 여부를 먼저 확인해 빈 문자열("")을 반환하게 하면 불필요한 0 표시를 막을 수 있습니다.
Q3. 세로로 바꾼 데이터를 다시 가로로 되돌릴 수도 있나요?
네, 반대 방향 변환도 OFFSET과 ROW, COLUMN을 조합하면 가능합니다. 원리는 동일하게 '몇 번째 값인지'를 계산해 원본의 정확한 위치를 참조하는 것입니다.
마치며
INT와 MOD를 조합해 '몇 번째 행, 몇 번째 열'을 계산하고 OFFSET으로 그 위치의 값을 가져오는 패턴은 데이터 방향 재배치뿐 아니라 다양한 순서 변환 문제에 두루 응용할 수 있는 기본기입니다. OFFSET 함수의 본질을 한 번 제대로 익혀 두면 오래도록 요긴하게 쓰실 수 있습니다.
