엑셀 OFFSET 으로 행열 바꾸기 방법에 관해 알아보겠습니다. 엑셀 실시간 행열 전환을 해야 하는 경우 OFFSET 함수를 사용할 수 있습니다. 이 글에서는 OFFSET 함수를 사용하여 엑셀 가로 세로 바꾸기를 간단하게 해보겠습니다.
엑셀 연습 파일을 다운로드 받아서 연습을 해보면 이해가 훨씬 쉽습니다.
엑셀 OFFSET 으로 행열 바꾸기
OFFSET 을 이용하는 방법 외에도 행열 바꾸기를 하는 방법으로 행열 바꾸기 기능을 이용하는 방법, TRANSPOSE 함수를 이용하는 방법이 있습니다.
그런데 행열 바꾸기 기능을 이용하는 경우 데이터가 갱신 될 때 행열 전환된 데이터에 새로운 정보가 반영되지 않는다는 단점이 있습니다.
따라서 새로운 정보를 자동으로 반영하기 위해 OFFSET 함수를 사용해 볼 수 있습니다. 지금부터 엑셀 OFFSET 으로 행열 바꾸기 방법을 알아보겠습니다.
- OFFSET 함수: 지정한 범위의 행, 열을 바꿔주는 함수. 실시간으로 갱신이 됨.
- =OFFSET (시작위치, 행이동, 열이동)
OFFSET 함수는 행, 열을 바꿔주는 함수로 실시간으로 갱신이 됩니다. OFFSET 함수에 관한 자세한 내용은 아래 링크를 참조해주세요.
OFFSET 함수를 이용하여 행열을 바꾸려면 아래 공식에 각 시작위치를 대입하면 됩니다. 여기서 말하는 시작위치란 행열을 바꾸기 전 범위에서 가장 왼쪽 상단에 위치한 셀을 말합니다.
- =OFFSET ($시작위치, COLUMN(시작위치)-COLUMN($시작위치), ROW(시작위치)-$ROW(시작위치))
- 시작위치: 범위의 가장 왼쪽 상단에 위치한 셀
- =COLUMN(셀) → 열번호를 반환하는 함수
- =ROW(셀) → 행번호를 반환하는 함수
OFFSET 함수를 이용해서 행열을 바꾸는 경우 COLUMN, ROW 함수를 이용해야 하는데요. COLUMN, ROW 함수는 각각 열번호, 행번호를 반환해주는 함수입니다.
단, OFFSET 함수를 이용하는 경우 셀서식 및 표시형식은 반영이 되지 않습니다.
그리고 OFFSET 함수는 실시간 반영을 해주는 함수이므로 1만 행, 10만 행 이상의 넓은 범위에 공식을 적용하는 경우 동작속도에 영향을 미칠 수 있습니다.
이제 바로 OFFSET 함수를 이용해서 행열을 바꾸는 방법을 알아 볼게요.
아래 사진에서 위에 있는 데이터의 행열을 바꿔서 입력이 되도록 해보겠습니다. 그러기 위해서 아래 사진의 A8 셀에 아래와 같이 함수식을 입력합니다.
=OFFSET($A$1,COLUMN(A1)-COLUMN($A$1),ROW(A1)-ROW($A$1))
그 후 엔터키를 누르고 아래로 4번째 까지, 오른쪽으로 7번째 까지 자동채우기를 하면 위 사진처럼 행열이 전환이 됩니다.
만약 행열을 바꾼 후의 날짜가 일반적인 날짜로 나오지 않았다면 아래 사진처럼 상단 메뉴에서 ‘일반’을 ‘간단한 날짜’로 바꿔주면 됩니다.
참고로 여기서 행열 전환을 하기 전의 데이터에 새로운 값을 추가로 입력하면, 행열 전환을 한 후의 데이터에도 자동으로 새로운 값이 입력이 됩니다.
단, 이때 행열 전한을 한 후의 데이터에서 자동채우기를 범위 이상으로 넉넉하게 해주어야 합니다.
이제 함수식 풀이를 해보겠습니다.
=OFFSET($A$1,COLUMN(A1)-COLUMN($A$1),ROW(A1)-ROW($A$1))
=OFFSET (시작위치, 행이동, 열이동)
OFFSET 함수 내 첫 번째 인수는 ‘시작위치’ 입니다. 즉, 행열을 바꾸기 전의 범위에서 가장 왼쪽 상단에 위치한 셀을 말합니다.
시작위치를 입력한 후 F4 키를 눌러서 셀을 고정시켜야 합니다. 다른 셀에 자동채우기를 할 때 시작위치가 바뀌면 안되기 때문이죠.
=OFFSET($A$1,COLUMN(A1)-COLUMN($A$1),ROW(A1)-ROW($A$1))
=OFFSET (시작위치, 행이동, 열이동)
OFFSET 두 번째 인수로 ‘행이동’ 을 입력해야 합니다. 즉, 아래로(행으로) 얼마나 이동할 건지 입력하는 부분입니다.
열번호를 반환해 주는 함수인 COLUMN 함수를 이용해서 값을 구해야 합니다.
첫 번째 COLUMN 함수 내에는 그냥 셀 값을 입력합니다. 그리고 두 번째 COLUMN 함수 내에는 절대참조를 한 셀 값을 입력합니다.
OFFSET 함수를 입력하면 행열 바꾸기 전의 행 부분이 행열 바꾼 후의 열로 바뀌게 되는데요. 오른쪽으로 자동채우기를 할 때, 각 결과값은 아래와 같이 산출이 됩니다.
- =COLUMN(A1)-COLUMN($A$1) → 0
- =COLUMN(B1)-COLUMN($A$1) → 1
- =COLUMN(C1)-COLUMN($A$1) → 2
- =COLUMN(D1)-COLUMN($A$1) → 3
- =COLUMN(E1)-COLUMN($A$1) → 4
- =COLUMN(F1)-COLUMN($A$1) → 5
=OFFSET (A1, 행이동, 열이동) 에서 행으로 1,2,3… 만큼 이동한 값이 열로 반환이 되는 것입니다.
=OFFSET($A$1,COLUMN(A1)-COLUMN($A$1),ROW(A1)-ROW($A$1))
=OFFSET (시작위치, 행이동, 열이동)
그리고 이번에는 OFFSET 세 번째 인수로 ‘열 이동’ 부분을 입력해야 합니다. 즉, 오른쪽(열 방향)으로 얼마나 이동할 것인지 입력을 하는 부분인데요.
행번호를 반환해주는 함수인 ROW 함수를 이용해야 합니다.
첫 번째 ROW 함수 내에서는 단순히 A1 셀을 선택합니다. 그리고 빼기 기호를 입력합니다. 두 번째 ROW 함수 내에서는 A1 셀을 선택하고 F4 키를 눌러서 절대참조를 해줍니다.
그 후 아래로 자동채우기를 할 때 각 결과값은 아래와 같이 산출이 됩니다.
- =ROW(A1)-ROW($A$1) → 0
- =ROW(A2)-ROW($A$1) → 1
- =ROW(A3)-ROW($A$1) → 2
- =ROW(A4)-ROW($A$1) → 3
- =ROW(A5)-ROW($A$1) → 4
- =ROW(A6)-ROW($A$1) → 5
=OFFSET (A1, 행이동, 열이동) 에서 열이동 숫자만큼 이동을 하는 것인데요. 열로 1,2,3… 만큼 이동한 결과값이 행으로 반환이 되는 것입니다.
<결론>
- 엑셀 OFFSET 으로 행열 바꾸기
- OFF=OFFSET ($시작위치, COLUMN(시작위치)-COLUMN($시작위치), ROW(시작위치)-$ROW(시작위치)) ⬅️ 이 공식 이용해서 바꾸기
이상 엑셀 OFFSET 으로 행열 바꾸기에 관한 글이었습니다.