메뉴
실무 위키›응용 공식›엑셀 가로 데이터를 세로로 펼치기 공식

엑셀 가로 데이터를 세로로 펼치기 공식

지원 버전 자세히 보기
엑셀 2013 이전엑셀 2016엑셀 2019엑셀 2021엑셀 2024M365웹 엑셀

가로로 나열된 데이터를 열 순서대로 세로 한 열로 펼치는 공식입니다. 표를 데이터베이스 형태로 정규화할 때 사용합니다.

예제파일 다운로드
엑셀 가로 데이터를 세로로 펼치기 공식 예제 미리보기
예제 미리보기 크게 보기

인수 설명

01INDIRECT 함수 공식 (모든 버전)

=INDIRECT ( "R" & ( ROW ( ) + ROW ( 데이터시작셀 ) - ROW ( 입력시작셀 ) - 데이터행개수 * ROUNDDOWN ( ( ROW ( ) - ROW ( 입력시작셀 ) ) / 데이터행개수, 0 ) ) & "C" & ( ROUNDDOWN ( ( ROW ( ) - ROW ( 입력시작셀 ) ) / 데이터행개수, 0 ) + COLUMN ( 데이터시작셀 ) ), 0 )
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
데이터시작셀필수원본 데이터의 왼쪽 위 셀입니다. ROW 함수와 COLUMN 함수가 이 셀의 행·열 번호를 계산합니다.
데이터행개수필수원본 데이터에서 한 열에 포함된 행 개수입니다. 이 개수만큼 읽은 뒤 다음 열로 이동합니다.
입력시작셀필수첫 번째 결과 수식을 입력할 셀입니다. 아래로 채워도 기준이 움직이지 않도록 절대참조로 고정합니다.
E2=INDIRECT("R"&(ROW()+ROW($A$2)-ROW($E$2)-2*ROUNDDOWN((ROW()-ROW($E$2))/2,0))&"C"&(ROUNDDOWN((ROW()-ROW($E$2))/2,0)+COLUMN($A$2)),0)
A
B
C
D
E
F
1
1월
2월
3월
세로 데이터
2
12
18
15
12
3
9
11
14
9
4
18
5
11
6
15
7
14
8
수식을 E2부터 아래로 채우면 원본을 A2:A3, B2:B3, C2:C3 순서로 읽어 12, 9, 18, 11, 15, 14를 반환합니다.

02TOCOL 함수 공식 (엑셀 2024 이후)

=TOCOL ( 데이터범위, 0, TRUE )
인수 설명 자세히 보기
인수구분설명
데이터범위필수세로로 펼칠 원본 범위입니다. 두 번째 인수 0은 값을 제외하지 않고, 세 번째 인수 TRUE는 열을 먼저 읽도록 지정합니다.
E2=TOCOL(A2:C3,0,TRUE)
A
B
C
D
E
F
1
1월
2월
3월
세로 데이터
2
12
18
15
12
3
9
11
14
9
4
18
5
11
6
15
7
14
8

수식을 입력한 셀자동으로 채워진 범위

TOCOL 함수가 A2:A3, B2:B3, C2:C3 순서로 읽어 E2:E7에 12, 9, 18, 11, 15, 14를 스필합니다.
이 공식이 사용하는 함수
INDIRECT 함수 ROW 함수 ROUNDDOWN 함수 COLUMN 함수 TOCOL 함수

동작 원리

01INDIRECT 함수 공식 (모든 버전)

ROW 함수와 ROUNDDOWN 함수가 반복할 행과 열 묶음을 계산하고, COLUMN 함수와 INDIRECT 함수가 R1C1 주소의 값을 반환합니다.

INDIRECT 함수 공식의 예제 시트에서 E2 셀이 12를 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

열 묶음 번호를 계산합니다

인수를 생략한 ROW 함수는 활성 셀이 아니라 수식이 입력된 셀의 행 번호를 반환합니다. E2에서는 시작 셀과의 행 차이가 0이므로 ROUNDDOWN 함수가 첫 번째 열 묶음 번호 0을 반환합니다.

=ROUNDDOWN ( ( ROW ( ) - ROW ( $E$2 ) ) / 2, 0 ) E2에서 계산한 열 묶음 번호입니다.
= 0 첫 번째 원본 열을 읽습니다.
2

대상 행 번호를 계산합니다

현재 수식 행에서 두 행씩 묶은 횟수를 빼면 원본 행 번호가 2와 3으로 반복됩니다. E2에서는 대상 행 번호 2를 반환합니다.

=ROW ( ) + ROW ( $A$2 ) - ROW ( $E$2 ) - 2 * 0 E2에서 계산한 원본 행 번호입니다.
= 2 원본 범위의 첫 번째 행입니다.
3

대상 열 번호를 계산합니다

열 묶음 번호 0에 데이터 시작 열 A열의 번호 1을 더합니다. E2에서는 대상 열 번호 1을 반환합니다.

=0 + COLUMN ( $A$2 ) E2에서 계산한 원본 열 번호입니다.
= 1 원본 범위의 첫 번째 열입니다.
4

INDIRECT 함수가 원본 값을 반환합니다

R2C1은 A2를 가리킵니다. INDIRECT 함수는 A2의 값 12를 반환하고, 수식을 아래로 채우면 두 행마다 다음 열로 이동합니다. INDIRECT 함수는 참조를 다시 계산하는 휘발성 함수입니다.

=INDIRECT ( "R"&2&"C"&1, 0 ) R1C1 형식의 R2C1은 A2를 가리킵니다.
= 12 A2 셀의 값입니다.
댓글 11
5 (8개 평가)
엑셀고고
엑셀고고 2020.04.13 10:18
선생님, 이 수식을 이용하는 방법 외에 파워쿼리로도 가능할 것 같은데 강의를 부탁드려도 될까요?
오빠두엑셀
오빠두엑셀 작성자 2020.04.13 14:19
안녕하세요?^^
파워쿼리를 이용한 정규화방법도 조만간 준비해드리겠습니다.
좋은 의견 감사드립니다.
아이둘
아이둘 2020.05.05 12:27
엑셀 add-in을 만들고 있는데 추가할 기능을 고민하던 중에 이 자료를 보게되었네요.
초보자도 버튼 한 번 눌러서 쉽게 정규화 할 수 있도록~~
좋은 자료 감사합니다.
임이사
임이사 2020.12.16 10:55
감사합니다~
이승원
이승원 2021.04.28 23:16
진짜... 감사합니다!!!!!!!!!
qwa****
qwa**** 2021.11.19 10:04
항상 보면서 많은 도움을 받고 있습니다. MOD, Floor 활용하여 응용할 수도 있겠네요. ㅎ
임정모
임정모 2022.03.17 16:00
안녕하세요 강의 보고 궁금한게 있어서 질문드립니다. 정규화된 표를 바탕으로 lookup 함수를 이용해 데이터를 가져오고 그 옆셀에 직접 입력한 데이터가 있을 때 새로운 데이터가 추가되어 오름차순 내림차순 정렬을 하면 직접 입력한 셀은 고정상태가 되고 lookup을 이용한 데이터들은 위치가 변동되거든요.. 이부분을 좀 해결하고 싶습니다!
오빠두엑셀
오빠두엑셀 작성자 2022.03.21 20:49
안녕하세요.
그럴 경우 함수에 들어가는 셀 참조를 아래와 같이 INDIRECT 함수로 해결해보세요.
기존 : A1

변경 : INDIRECT("A"&ROW())
강민준🤗
강민준🤗 2024.08.11 16:57
좋은 강의 감사합니다🙇‍♂️
하늘SS
하늘SS 2025.02.19 16:05
선생님, 좌측 행으로 1월 구분2개, 열로 날짜가 있는 데이터의 경우 위 수식으로는 안되는데 수식 알려주실 수 있나요??
오빠두엑셀
오빠두엑셀 작성자 2025.02.20 16:39
안녕하세요. 오빠두엑셀입니다.
머리글이 여러줄인 경우, M365 에 새롭게 추가된 배열 수식을 사용하면 편리합니다.
이전 버전일 경우 함수만으로는 정규화 구현이 어려울 수 있습니다.
아래 영상을 참고해보세요.
https://www.oppadu.com/unpivot-%EC%99%84%EB%B2%BD-%EC%A0%95%EB%A6%AC/
스크랩 완료