엑셀 가로 데이터를 세로로 펼치기 공식
지원 버전 자세히 보기
엑셀 2013 이전엑셀 2016엑셀 2019엑셀 2021엑셀 2024M365웹 엑셀
가로 범위의 값을 열 순서대로 세로로 펼치는 공식입니다.
인수 설명
01모든 버전 INDIRECT 방식
=INDIRECT ( "R" & ( ROW ( ) + ROW ( 데이터시작셀 ) - ROW ( 입력시작셀 ) - 데이터행개수 * ROUNDDOWN ( ( ROW ( ) - ROW ( 입력시작셀 ) ) / 데이터행개수, 0 ) ) & "C" & ( ROUNDDOWN ( ( ROW ( ) - ROW ( 입력시작셀 ) ) / 데이터행개수, 0 ) + COLUMN ( 데이터시작셀 ) ), 0 )인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
데이터시작셀필수원본 데이터의 왼쪽 위 셀입니다. ROW 함수와 COLUMN 함수가 이 셀의 행·열 번호를 계산합니다.
데이터행개수필수원본 데이터에서 한 열에 포함된 행 개수입니다. 이 개수만큼 읽은 뒤 다음 열로 이동합니다.
입력시작셀필수첫 번째 결과 수식을 입력할 셀입니다. 아래로 채워도 기준이 움직이지 않도록 절대참조로 고정합니다.
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
02M365·Excel 2024 TOCOL 방식
=TOCOL ( 데이터범위, 0, TRUE )인수 설명 자세히 보기
인수구분설명
데이터범위필수세로로 펼칠 원본 범위입니다. 두 번째 인수 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
이 공식이 사용하는 함수
동작 원리
01모든 버전 INDIRECT 방식
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 셀의 값입니다. 
파워쿼리를 이용한 정규화방법도 조만간 준비해드리겠습니다.
좋은 의견 감사드립니다.
초보자도 버튼 한 번 눌러서 쉽게 정규화 할 수 있도록~~
좋은 자료 감사합니다.
그럴 경우 함수에 들어가는 셀 참조를 아래와 같이 INDIRECT 함수로 해결해보세요.
머리글이 여러줄인 경우, M365 에 새롭게 추가된 배열 수식을 사용하면 편리합니다.
이전 버전일 경우 함수만으로는 정규화 구현이 어려울 수 있습니다.
아래 영상을 참고해보세요.
https://www.oppadu.com/unpivot-%EC%99%84%EB%B2%BD-%EC%A0%95%EB%A6%AC/