공식설명
기존 범위의 행열을 바꿔서 출력하는 '행열 전환' 함수 공식을 알아봅니다.
엑셀의 '행열 바꾸기' 기능은 1회성으로 사용되는 반면, 본 공식은 새로 추가되는 데이터도 실시간으로 반영됩니다. 따라서 행열을 바꿔야 할 범위가 매번 추가되는 경우, 공식을 한번만 입력해두면 이후에 필요시 적용범위를 확장하여 쉽게 사용할 수 있습니다.
단, 행열 전환 함수 공식을 사용할 경우 아래 2가지 주의사항이 있습니다.
- 기존 범위의 표시형식 및 셀 서식은 반영되지 않습니다. 즉 기존 범위의 값만 참조하여 행열을 전환합니다.
- OFFSET 함수는 지속 계산되는 함수로 넓은 범위에 공식을 적용시 동작속도에 영향을 미칠 수 있습니다. 만약 10만행 이상 넓은 범위에 공식을 적용할 경우, 엑셀의 계산옵션을 '수동'으로 변경하면 문제를 해결할 수 있습니다.
엑셀의 행열전환 기능 또는 TRANSPOSE 함수를 사용한 기타 행열 바꾸기 방법은 관련 포스트에 자세히 적어드렸습니다.
함수공식
= OFFSET ( $시작셀, COLUMN(시작셀)-COLUMN($시작셀), ROW(시작셀)-ROW($시작셀) )
사용된 인수
- 시작셀 : 행열을 전환할 기존범위 좌측 상단에 위치한 시작셀입니다. 공식에서 $가 표시된 셀은 반드시 '절대참조'로 입력하는 것에 주의합니다.
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀공식] 행열 바꾸기 / 행열 전환 공식예제파일
관련 기초함수
엑셀 행열 전환 함수 공식 자세히 알아보기
본 예제파일에서 사용된 함수 공식
=OFFSET($B$3,COLUMN(B3)-COLUMN($B$3),ROW(B3)-ROW($B$3))
행열을 바꿔 출력할 시작셀에 공식을 입력합니다. 공식 원리 이해하기
-
ROW 함수와 COLUMN 함수
ROW 함수와 COLUMN 함수는 해당 셀의 행번호와 열번호를 반환하는 함수입니다. COLUMN 함수를 예로 보겠습니다. 위 공식에서 사용된 COLUMN 함수의 계산결과는 아래와 같습니다.
= COLUMN(B3) - COLUMN($B$3)
= 2 - 2
= 0만약 우측으로 한칸 자동채우기를 했다고 가정합니다. 그럴 경우, 절대참조를 한 $B$3셀은 고정되고 그렇지 않은 B3만 우측으로 한칸이동하여 C3이 됩니다.
= COLUMN(C3) - COLUMN($B$3) '// $B$3은 고정
= 3 - 2
= 1 -
OFFSET 함수 구문 설명
OFFSET 함수의 구문은 아래와 같습니다.
= OFFSET ( 1)기준셀에서 , 2)아래로 몇칸을 이동하고 , 3)오른쪽으로 몇칸을 이동하세요 )
OFFSET 함수의 두번째/세번째 인수를 조절하여 출력할 값을 결정합니다.= OFFSET($B$3, 0, 0) : B3셀
= OFFSET($B$3, 1, 0) : B4셀 '// 아래로 한칸 이동
= OFFSET($B$3, 1, 1) : C4셀 '// 아래로 한칸, 오른쪽 한칸 이동 -
'우측 이동한 만큼 아래로', '아래로 이동한 만큼 우측으로' 바뀌도록 인수를 입력합니다.
OFFSET 함수의 두번째 인수는 '아래로 몇칸을 이동할지 결정'하는 자리인데요.
해당 공식은 아래로 몇칸을 이동할지 결정하는 자리에 COLUMN 함수를 사용합니다. 따라서 셀이 '우측으로 이동한 만큼 아래로 이동' 하여 값을 출력합니다.
동일한 원리로 세번째 자리에는 ROW 함수를 입력하여 '아래로 이동한 만큼 우측으로 이동' 하게 됩니다.
오른쪽으로 이동한만큼 아래로 이동한 값을 반환합니다. (행열 전환)
-
