1열의 세로로 나열된 리스트를 x * y 형태의 가로 배열로 재배치하기

작성자
프로스트
작성일
2023-10-04 11:30
조회
1474

엑셀을 활용하다 보면 세로로 나열된 목록을 일정 규칙에 맞게 가로로 전환해야 할 상황이 일어날 수 있다. 예를 들자면 아래와 같은 상황이다.

 

 

세로로 나열된 28개의 목록을 7개씩 끊어 가로로 편성한 모습이다. 총 수량이 얼마 되지 않는다면 수작업을 통해 변경하는 것이 오히려 빠를 수 있지만 수량이 100 단위를 넘어간다면 이를 일일이 수작업으로 수행하는 건 큰 곤욕이 된다. 이번 시간에는 세로로 나열된 목록을 사용자가 지정한 규칙을 통해 가로로 전환하는 방법을 알아보고자 한다.

위 배열에는 규칙이 존재하는데 기존 배열에서 가로로 전환시 한 행을 내려갈 때마다 셀 참조 주소상 행 번호가 7씩 올라간다는 것이다. 단군에서 시작해 다음 줄 첫번째 리스트에는 의자왕에서 시작하게 되는 것이 바로 그것이다. 즉 C 열에서의 참조 행 번호를 살펴보자면, {1, 8, 15, 22} 가 된다는 것이고 D열에서의 참조 행 번호는 {2, 9, 16, 23}이 되며 이런 식으로 I열에서의 참조 행 번호는 {7, 14, 21, 28} 이 되는 것이다. 이 패턴은 우리가 수학 시간에 배웠던 등차 수열의 규칙이며 초항과 공차의 값에 따라 순차적으로 값이 변동하게 됨을 알 수 있다. 등차 수열의 공식은 아래와 같다.

An = a + (n-1)d

An은 n번째에 해당하는 숫자,
a는 시작하는 값으로 초항이라 부른다.
n은 진행한 횟수,
d는 진행할 때마다 변동되는 변화폭, 즉 공차다.

이 공식에 따라 작업할 셀에 함수를 작성하면 아래와 같다.

 

 

INDIRECT 함수는 참조할 셀을 지정해 해당 위치에 원하는 값을 표출하는 함수다. 참조할 위치는 A열이므로 A를 기입한 후 행 번호를 지정해야 하므로 & 기입 후 참조해야 할 행 번호의 규칙을 지정하면 된다. 해당 위치는 셀 C1부터 시작하므로 C1을 절대 참조로 지정 후 앞에서 살펴본 등차수열의 공식대로 C1의 행 번호를 호출 후 공차를 곱셈하는 방식을 취했다. 저기에 기재된 ROW는 지정한 셀의 행 번호를 호출하는 함수로 저 기능이 등차수열에서의 n 값에 해당한다. 이런 식으로 D ~ I 열까지 함수를 기입한다. 변동되는 항목은 처음에 기입하는 초항의 위치다. D열에서는 $C$2가 될 것이고 E열에서는 $C$3, 마찬가지로 I열에서는 $C$7이 된다. 이렇게 작성된 한 줄을 모두 블록 선택하고 아래로 드래그하면 원하는대로 배열이 된 리스트를 볼 수 있다.

 


 

이전에 자주 쓸 일이 있어 잊지 않고 쓸 수 있도록 제 블로그에 정리했던 글을 퍼왔습니다. 혹시나 도움이 되기를...

신고
스크랩
공유
회원등급 : 3레벨
포인트 : 149 EP
전체 18

  • 2024-03-08 10:10

    @프로스트 님 와웅~~ 좋은자료 공유 감사합니다.


  • 2023-12-29 11:26

    @프로스트 님 좋은 정보 감사합니다.


  • 2024-01-10 16:11

    @프로스트 님 좋은 정보 감사합니다.


  • 2023-10-14 23:30

    @프로스트 님 좋은팁 감사드립니다.


  • 2023-12-18 14:19

    @프로스트 님 indirect 함수를 어떻게 활용할지 몰랐는데 제대로 배우고 갑니다.


  • 2023-11-06 10:52

    @프로스트 님 저걸 다시 푸는 것도 부탁드립니다. 비슷한 것 응용해보려니 머리가 안 돌아가네요 ㅠㅠ


    • 2023-12-18 14:22

      @크놉스 님 indirect함수를 응용해서 다시 풀면 되지 않을까 합니다.

      화면-캡처-2023-12-18-142159.png


  • 2024-04-15 15:03

    @프로스트 님 오 찾던것이어요. 감사합니다. ㅎ


  • 삭제된 댓글입니다.

    • 2023-12-07 18:35

      @마법의손 님 안녕하세요, 저 수식은 한 줄을 다 만든 다음 그 한 줄을 모두 블록 설정 하셔서 아래로 내리셔야 의도한 기능이 수행됩니다. 열마다 초항으로 잡아야 할 셀이 다르기 때문입니다. 작성한 글 후반부 내용을 다시 읽어주시면 감사하겠습니다. 다만 제시하신 수식대로 작성하면 1개 셀만 작성해도 바로 드래그해서 나머지를 표시하는 게 가능하니 더 간편한 방안이 되겠군요. 보다 효율적인 수식을 알려주셔서 감사합니다.


  • 2024-04-05 15:39

    @프로스트 님 좋은 정보 감사합니다


  • 2023-10-04 17:01

    @프로스트 님 좋은 팁 감사합니다. 365에서는 wrarprows 함수로 한 번에 되는데 이전 버전에서 공유할 땐 이렇게 하면 되겠네요


    • 2023-10-24 11:45

      @엑셀킹이될꺼야 님 오... 저는 이 함수 알게 된 것도 너무 좋네요.

      감사합니다.


  • 2023-11-07 12:36

    @프로스트 님 좋은팁 감사합니다.


  • 2023-11-24 09:10

    @프로스트 님 와우 멋진팁 감사드립니다 ^^

     


  • 2023-11-23 12:06

    @프로스트 님 저장해놨다가 필요할때 봐야겠어요. 고맙습니다.


  • 2023-12-05 17:20

    @프로스트 님 유익한 정보 감사합니다.


  • 2024-04-12 15:06

    @프로스트 님 좋은 정보 감사합니다.


전체 125
번호 제목 작성자 작성일 추천 조회
알림
[📚진짜쓰는 실무엑셀] 2024년 엑셀 공부, 이 책 한권으로 마스터하세요! (291)
오빠두엑셀 | 2022.02.03 | 추천 554 | 조회 845584
오빠두엑셀 2022.02.03 554 845584
74491
New 이름관리 엑셀 업그레이드 버전 입니다. 첨부파일 (2)
박정호 | 2024.10.28 | 추천 0 | 조회 48
박정호 2024.10.28 - 48
74354
월드컵 랭킹 계산(ELO 점수 계산) 첨부파일 (4)
박정호 | 2024.10.22 | 추천 0 | 조회 57
박정호 2024.10.22 - 57
74269
엑셀2021이하에서 CHOOSECOLS, CHOOSEROWS 처럼 동작시키기 첨부파일 (13)
마법의손 | 2024.10.18 | 추천 2 | 조회 109
마법의손 2024.10.18 2 109
74204
이름관리 엑셀입니다.(모듈) 첨부파일 (6)
박정호 | 2024.10.15 | 추천 0 | 조회 142
박정호 2024.10.15 - 142
74028
로또번호2(반자동 기능 업데이트) 첨부파일 (4)
박정호 | 2024.10.05 | 추천 1 | 조회 133
박정호 2024.10.05 1 133
74012
매장 근태관리시트 공유 첨부파일 (3)
체리로그 | 2024.10.03 | 추천 1 | 조회 128
체리로그 2024.10.03 1 128
73964
[파워쿼리] 여러열 열분할하기 첨부파일 (4)
마법의손 | 2024.10.01 | 추천 1 | 조회 107
마법의손 2024.10.01 1 107
73915
M365 GroupBy, PivotBy 함수 공식 배포 (6)
원조백수 | 2024.09.27 | 추천 2 | 조회 141
원조백수 2024.09.27 2 141
73485
m365 신규함수(베타) : TRIMRANGE (7)
더블유에이 | 2024.09.04 | 추천 1 | 조회 212
더블유에이 2024.09.04 1 212
73454
고등학교 선택과목 편성 방법 (4)
toug**** | 2024.09.03 | 추천 0 | 조회 124
toug**** 2024.09.03 - 124
73241
로또 번호 엑셀입니다. 첨부파일 (9)
박정호 | 2024.08.24 | 추천 1 | 조회 282
박정호 2024.08.24 1 282
73096
그룹별 데이터 나누기 [가로정렬] 첨부파일 (7)
마법의손 | 2024.08.20 | 추천 2 | 조회 203
마법의손 2024.08.20 2 203
73093
ppt에 둥근 표 삽입하기 (4)
강민준🤗 | 2024.08.20 | 추천 1 | 조회 177
강민준🤗 2024.08.20 1 177
73029
XLOOKUP, XMATCH 함수에 새로운 Match_mode로 RegEx 추가 (2)
원조백수 | 2024.08.16 | 추천 3 | 조회 215
원조백수 2024.08.16 3 215
72413
아웃룩에 개빡쳐서 만든 ThunderBird로 수신자 별 대량 메일 발송 VBA 매크로 공유합니다.(업무자동화) 첨부파일 (4)
알파고 | 2024.07.22 | 추천 1 | 조회 499
알파고 2024.07.22 1 499
72045
조합짜주는 엑셀(20명까지) 출력은 1만항까지 첨부파일 (13)
박정호 | 2024.07.10 | 추천 1 | 조회 369
박정호 2024.07.10 1 369
71965
서양달력 업데이트 입니다. 첨부파일 (14)
박정호 | 2024.07.07 | 추천 0 | 조회 229
박정호 2024.07.07 - 229
71902
Excel에서 ImportRange, ImportHTML 함수 사용하기 (4)
원조백수 | 2024.07.05 | 추천 0 | 조회 169
원조백수 2024.07.05 - 169
71838
Microsoft 365 신규 번역 함수 (8)
원조백수 | 2024.07.02 | 추천 2 | 조회 391
원조백수 2024.07.02 2 391
71804
음력->양력->서양달력 변환 첨부파일 (2)
박정호 | 2024.07.01 | 추천 0 | 조회 235
박정호 2024.07.01 - 235