엑셀 실시간 행열 전환하기 :: 엑셀 함수 공식

기존 범위의 행열을 실시간으로 전환하여 출력하는 엑셀 함수 공식의 동작원리와 실전 예제를 알아봅니다.

홈페이지 » 엑셀 실시간 행열 전환하기 :: 엑셀 함수 공식

공식설명

기존 범위의 행열을 바꿔서 출력하는 '행열 전환' 함수 공식을 알아봅니다.

엑셀의 '행열 바꾸기' 기능은 1회성으로 사용되는 반면, 본 공식은 새로 추가되는 데이터도 실시간으로 반영됩니다. 따라서 행열을 바꿔야 할 범위가 매번 추가되는 경우, 공식을 한번만 입력해두면 이후에 필요시 적용범위를 확장하여 쉽게 사용할 수 있습니다.

단, 행열 전환 함수 공식을 사용할 경우 아래 2가지 주의사항이 있습니다.

  1. 기존 범위의 표시형식 및 셀 서식은 반영되지 않습니다. 즉 기존 범위의 값만 참조하여 행열을 전환합니다.
  2. OFFSET 함수는 지속 계산되는 함수로 넓은 범위에 공식을 적용시 동작속도에 영향을 미칠 수 있습니다. 만약 10만행 이상 넓은 범위에 공식을 적용할 경우, 엑셀의 계산옵션을 '수동'으로 변경하면 문제를 해결할 수 있습니다.

엑셀의 행열전환 기능 또는 TRANSPOSE 함수를 사용한 기타 행열 바꾸기 방법은 관련 포스트에 자세히 적어드렸습니다.

함수공식

= OFFSET ( $시작셀, COLUMN(시작셀)-COLUMN($시작셀), ROW(시작셀)-ROW($시작셀) )

사용된 인수
  • 시작셀 : 행열을 전환할 기존범위 좌측 상단에 위치한 시작셀입니다. 공식에서 $가 표시된 셀은 반드시 '절대참조'로 입력하는 것에 주의합니다.

예제파일 다운로드

관련 기초함수
시작지점으로부터 지정한만큼 이동한 곳의 셀 참조 또는 값을 반환합니다.
셀의 행 번호를 반환합니다.

엑셀 행열 전환 함수 공식 자세히 알아보기

본 예제파일에서 사용된 함수 공식

=OFFSET($B$3,COLUMN(B3)-COLUMN($B$3),ROW(B3)-ROW($B$3))

1a 행열 전환 함수 공식 입력
행열을 바꿔 출력할 시작셀에 공식을 입력합니다.
공식 원리 이해하기
  1. 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
  2. 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셀   '// 아래로 한칸, 오른쪽 한칸 이동
  3. '우측 이동한 만큼 아래로', '아래로 이동한 만큼 우측으로' 바뀌도록 인수를 입력합니다.

    OFFSET 함수의 두번째 인수는 '아래로 몇칸을 이동할지 결정'하는 자리인데요.
    해당 공식은 아래로 몇칸을 이동할지 결정하는 자리에 COLUMN 함수를 사용합니다. 따라서 셀이 '우측으로 이동한 만큼 아래로 이동' 하여 값을 출력합니다.
    동일한 원리로 세번째 자리에는 ROW 함수를 입력하여 '아래로 이동한 만큼 우측으로 이동' 하게 됩니다.

    1C OFFSET 행열 전환 함수 공식 설명
    오른쪽으로 이동한만큼 아래로 이동한 값을 반환합니다. (행열 전환)

같은 함수가 사용된 다른 공식

문자와 숫자가 섞인 문자열에서 숫자만 추출합니다.
조건별 가로로 나열된 데이터를 세로로 변환하여 1차 정규화합니다.
2중 조건을 만족하는 데이터유효성 목록상자를 생성합니다.
VLOOKUP 함수 결과값으로 여러개의 값을 세로/가로 방향으로 출력합니다.
자동필터 적용 후 화면에 보이는 값의 순번을 자동으로 생성합니다.
현재 페이지 댓글알림 신청
알림 설정
guest
0 Comments
Inline Feedbacks
View all comments
0
여러분의 생각을 댓글로 남겨주세요.x