엑셀 OFFSET 함수로 열 데이터와 행 데이터 전환하기

이전 글에서 OFFSET 함수로 데이터를 동적으로 참조하는 방법에 대해 알아보았습니다. 이번에는 OFFSET 함수를 이용하여 행 방향 데이터를 열 방향으로, 열 방향 데이터를 행 방향으로 변환하는 방법에 대해 알아보겠습니다.

 

엑셀 OFFSET 함수로 지정한 열 또는 행 참조하기

엑셀 OFFSET 함수로 지정한 열 또는 행 참조하기

 

 

 

 

 

1. OFFSET 함수로 열과 행 데이터 서로 전환하기

  표 형식으로 데이터를 작성하는 경우 행 또는 열방향으로 작성하였으나 이를 반대로 변환하여 데이터를 참조하여야 하는 경우가 발생합니다. 이럴 때 변환하려는 범위를 선택하고 Ctrl + C 키를 누른 후 변환할 위치의 셀을 클릭하고 선택하여 붙여넣기-행/열 바꿈으로 변환이 가능하며, OFFSET 함수를 이용하여 행 방향 데이터를 열 방향 데이터로 전환하거나 그 반대로 전환할 수 있습니다.

 

두 방법의 차이점은 엑셀 메뉴를 사용하여 행과 열 변환을 한 경우에는 참조하는 데이터가 변경되었을 때 이를 다시 실행하여야 하지만, OFFSET 함수를 이용하여 동적으로 변환을 하면 참조하는 데이터가 변경되면 자동으로 이를 반영하게 됩니다.

 

열 방향으로 입력된 데이터를 행 방향으로 변환할 때 다음과 같이 사용합니다.

 

   = OFFSET( Reference, COLUMN(A1), 0)

 

Reference : 참조하려는 데이터의 첫 번째 셀의 바로 앞 셀 주소를 입력

COLUMN(A1) : A1 셀의 열 주소 값인 1을 반환

 

반대로 행 방향으로 입력된 데이터를 열 방향으로 변환할 때는 다음과 같이 사용합니다.

 

   =OFFSET( Reference, 0, ROW(A1))

 

Reference : 참조하려는 데이터의 첫 번째 셀의 바로 앞 셀 주소를 입력

ROW(A1) : A1 셀의 행 주소 값인 1을 반환

 

 

 

 

 

2. 예제로 확인

  [그림 1]과 같이 영업실적 시트를 예제로 OFFSET 함수를 사용하여 열 방향 데이터를 행 방향으로 변환하는 방법에 대해 확인해 보겠습니다.

 

[그림 1] 실적현황 예제

[그림 1]을 보시면 직원명을 열 방향으로 작성하여 데이터를 작성하였습니다. 이를 행 방향으로 변경하려면 변경하여 작성할 위치의 셀을 정합니다. 저는 L3 셀을 시작 셀로 정하겠습니다.

 

L3 셀에 다음과 같이 수식을 입력합니다.

 

=OFFSET($B$2,COLUMN(A1),0)

 

: [그림 1]을 보시면 B3 셀에 '성명'이 입력되어 있습니다. 바로 B3 셀이 참조하려는 데이터 시작 셀입니다. 그런데. OFFSET 함수를 사용하려면 그 바로 앞의 셀인 B2 셀을 Reference로 입력해야 합니다. 만약, 참조하려는 데이터의 시작 셀을 입력하시려면 수식에서 COLUMN(A1)을 COLUMN(A1)-1로 변경하셔야 합니다.

 

  =OFFSET($B$3,COLUMN(A1)-1,0)

 

Reference를 $B$2로 입력하였을 때 수식을 설명하면 B2 셀에서 한 개의 셀만큼 떨어진 셀인 B3 셀의 값을 참조하라는 의미가 됩니다. 여기에 B3 셀을 입력하게 되면 참조하는 값은 B4 셀의 값을 참조하게 되므로 원하는 결과를 얻지 못하게 됩니다. 그래서, B3 셀을 Reference로 입력하였을 때는 참조하려는 셀과의 간격을 0으로 바꾸기 위해 COLUMN(A1)-1을 입력합니다.

 

 

 

 

이제 L4 셀부터 L7 셀까지 다음과 같이 수식을 입력하면 전체 데이터를 행 방향으로 변경하게 됩니다.

 

L4 셀 : =OFFSET($C$2,COLUMN(A2),0)

L5 셀 : =OFFSET($D$2,COLUMN(A3),0)

L6 셀 : =OFFSET($E$2,COLUMN(A4),0)

L7 셀 : =OFFSET($F$2,COLUMN(A5),0)

 

수식을 입력하시고 L3 셀부터 L7 셀까지 범위 지정하시고 마우스 드래그로 수식을 복사하여 표를 완성하시면 됩니다.

 

이번에는 행 방향으로 변환한 L3 셀부터 R3 셀까지의 행 방향 데이터를 열 방향으로 변환해 보겠습니다.

열 방향으로 변환할 시작 셀을 K4 셀로 정하고 다음과 같이 수식을 입력합니다.

 

  =OFFSET($K$3,0,ROW(A1))

 

이번에도 위에서 살펴본 바와 같이 참조할 데이터의 시작 셀(L3 셀) 바로 앞의 셀인 K3 셀을 Reference로 입력하였습니다. 이유는 앞의 예제에서 설명드린 이유와 동일합니다. 또한, L3 셀을 입력하시면 ROW(A1)을 ROW(A1)-1로 입력하셔야 합니다.

 

입력하시고 엔터를 치시면 '성명'이라는 값이 뜨는 것을 보실 수 있습니다. 그러면, K4 셀을 선택하시고 마우스 드래그로 K10 셀까지 수식 복사를 하시면 직원 이름이 열 방향으로 입력되는 것을 확인하실 수 있습니다.