요약
본 포스트에서는 엑셀 '행열 바꾸기' (또는 '행열 전환')의 다양한 방법을 소개합니다. 이번 포스트에서 다룬 내용을 응용하면 데이터를 열해서 행으로 또는 행에서 열로 회전할 수 있습니다.
- "난 매번 추가되는 데이터를 실시간으로 행/열 전환해야 한다!"
:: OFFSET 함수 공식을 사용하세요. [방법3] - "복잡한 함수/공식은 모른다! 무조건 간단하게 최고다!"
:: 행열 바꾸기 기능을 사용하세요 [방법1]
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀사전] 엑셀 행열 바꾸기 총정리예제파일
관련 기초 함수
엑셀 행열 바꾸기 알아보기
아래와 같이 분기별 각 매장의 매출이 나열되어 있는 데이터를 예로 들어보겠습니다. 현재는 매장이 좌우로, 분기가 위아래로 나열되어 있습니다.
좌우로 나열된 매장명이 위아래로 나열되도록 행열전환 합니다. 이 데이터를 행열 바꾸기를 통해 매장이 위아래로, 분기가 좌우로 나오도록 변경합니다.
행열 바꾸기로 원하는 데이터를 추출하였습니다. 본 포스트에서는 엑셀 행열 바꾸기 방법으로 3가지를 소개해드립니다.
첫번째: '행열 바꾸기 기능' 사용하기 [바로가기]
- 기존 범위의 데이터 형식, 셀 색상등을 모두 유지한 채로 행열이 전환됩니다.
- 기존 범위안에 수식이 입력되어있을 경우, 수식도 같이 복사됩니다. (단, 참조셀이 절대참조가 아닐경우 옳지않은 값을 반환하므로 주의합니다.)
- 기존 범위에 새로운 값이 추가될 시, 실시간으로 반영되지 않으므로, 매번 행열 바꾸기 작업을 다시 실행해야 합니다.
두번째: 'TRANSPOSE 함수' 사용하기 [바로가기]
- 범위의 값만 참조합니다. 기존 범위의 데이터형식, 셀색상 등은 무시합니다.
- 기존 범위안에 수식이 입력되어 있더라도, TRANSPOSE로 전환된 범위는 값만 참조합니다. (따라서 기존 범위의 수식이 상대참조로 입력되어도 괜찮습니다.)
- 배열 수식이므로 넓은 범위의 데이터에 적용 시 동작속도에 영향을 미칠 수 있습니다. 이는 배열수식의 한계점으로 해결방법이 없습니다.
- 넓은 범위를 참조하여 수식을 입력 할 경우, 새로 추가된 데이터도 실시간으로 반영됩니다.
세번째: 'OFFSET 행열 전환 공식' 응용하기 [바로가기]
- 범위의 값만 참조합니다. 기존 범위의 데이터형식, 셀색상 등은 무시합니다.
- 기존 범위안에 수식이 입력되어 있더라도, 값만 참조하여 출력합니다.
- OFFSET 함수는 지속 변경되는 함수이므로, 1만행 이상 넓은 범위의 데이터를 참조할 시 동작속도에 영향을 미칠 수 있습니다. 이 경우 엑셀의 계산옵션을 '수동'으로 변경하면 해결할 수 있습니다.
방법1. 엑셀 행열바꾸기 기능 이용하기
- 행열을 전환할 범위를 선택한 뒤 복사합니다. (단축키: CTRL + C)
행열 전환할 범위를 복사합니다. - 붙여넣기할 셀을 우클릭 한 뒤, 선택하여 붙여넣기 → 행/열 바꿈 선택
붙여넣기 할 셀을 우클릭한 뒤, '선택하여 붙여넣기' - '행열바꿈' 을 아이콘을 클릭합니다. - 행열이 전환되어 붙여넣기 되었습니다.
행열 바꾸기가 완료되었습니다.
단축키로 행열바꾸기 붙여넣기 하는 방법
단축키로 행열 바꾸기해서 붙여넣기 하는 방법은 아주 간단합니다. 아래 5단계만 기억하세요!
"CTRL + C (복사)" ⇨ "셀 선택" ⇨ "CTRL + ALT + V(선택하여 붙여넣기)"
⇨ "E" (행열전환) ⇨ "ENTER (확인)"
단축키를 활용하여 보다 빠르게 행열을 바꿀 수 있습니다. 방법2. 엑셀 행열바꾸기 함수 (TRANSPOSE 함수) 이용
- TRANSPOSE 함수는 배열함수입니다. 따라서 함수를 입력하기 전, 행열 바꾸기를 할 범위의 넓이를 확인합니다.
행/열을 바꿀 범위의 가로/세로 넓이를 확인합니다. - 행열을 바꿔 붙여넣기 할 범위를 선택합니다. 붙여넣기할 범위는 기존범위보다 같거나 넉넉하게 선택합니다.
행열 바꾸기로 붙여넣기 할 범위를 선택합니다. - TRANSPOSE 함수를 입력한 뒤, 반드시 'CTRL+SHIFT+ENTER' 로 입력합니다. (배열수식)
TRANSPOSE 함수를 입력합니다. (반드시 CTRL+SHIFT+ENTER로 입력하는것에 주의합니다.) - 행열 전환이 완료되었습니다. 두번째 단계에서 범위를 넉넉하게 선택했다면,
선택된 범위 안에서 새로 추가되는 데이터는 실시간으로 행열전환됩니다.
범위를 넉넉하게 설정했다면, 새로운 데이터도 실시간으로 갱신됩니다.
방법3. 엑셀 행열 바꾸기 함수 공식 사용(OFFSET 함수 응용)
행열 바꾸기 공식은 OFFSET 함수를 사용합니다. OFFSET 함수는 지속갱신되는 함수이므로 만행 이상 넓은범위에 사용시 동작속도에 영향을 미칠 수 있으므로 주의합니다.
OFFSET 함수 행열바꾸기 공식에 대한 자세한 내용은 관련포스트를 참고하세요.
= OFFSET ( $기준셀, COLUMN(기준셀)-COLUMN($기준셀), ROW(기준셀)-ROW($기준셀) )
* 기준셀 : 행/열 바꾸기를 할 기준범위의 왼쪽상단 시작셀입니다.
- 행/열을 바꿔 출력하고자 하는 시작셀에 OFFSET 함수 공식을 입력합니다.
OFFSET 함수의 첫번째 인수를 포함하여 $표시가 있는 셀은 반드시 '절대참조'로 입력하는 것에 주의합니다.
행열 바꾸기 공식을 입력합니다. $표시가 있는 셀은 절대참조로 입력하는것에 주의합니다. - 공식을 입력하였으면 행열 전환하고자 하는 넓이만큼 자동채우기 합니다.
행열 전환할 넓이만큼 공식을 자동채우기 합니다. - 필요에따라 범위를 넓혀가면서 사용하면, 새롭게 추가되는 데이터도 실시간으로 행/열 전환되어 갱신됩니다.
필요한 범위만큼 공식을 자동채우기하면, 새로운 데이터도 실시간으로 갱신됩니다.
