엑셀 작업을 하다 보면 한 열에 길게 나열된 데이터를 행과 열 형태로 정리해야 하는 경우가 종종 있습니다. 특히 워드 편집기 등에서 엑셀 시트로 데이터를 복사해 붙여넣으면 모든 내용이 하나의 열에 몰려 들어가기 쉽습니다.
엑셀 수식으로 열을 행으로 변환하기
정리된 워크시트나 그 일부를 행에서 열로 바꾸는 것은 'Transpose(전치)' 기능으로 간단히 처리할 수 있습니다. 하지만 데이터가 정리되지 않은 채 한 열에 길게 늘어져 있다면 이야기가 다릅니다.
만약 데이터에 아무런 패턴이 없다면 먼저 패턴을 찾아내야 합니다. 다행히 대부분의 경우 정리되지 않은 데이터라도 일정한 반복 패턴을 가지고 있습니다.
예를 들어, 한 열에 이름(Name), 생년월일(DOB), 학점(Grade) 순서로 반복되는 데이터 목록이 있다고 가정해 보겠습니다.
Karan
01/06/1987
A
Justin
09/08/1964
D
Bob
04/05/1996
B
Jason
08/09/1984
C
이 경우 데이터를 단순히 한 열에 두지 않고, 이름·생년월일·학점 순서대로 행과 열에 배치하고 싶을 것입니다. 이때 OFFSET 함수를 활용하면 됩니다. OFFSET 함수의 기본 구문은 다음과 같습니다.
OFFSET($A$1,(ROW()-f_row)*rows_in_set+INT((COLUMN()-f_col)/col_in_set), MOD(COLUMN()-f_col,col_in_set))
위 예시처럼 정보가 A열의 A1 셀부터 시작한다면 실제 수식은 다음과 같이 작성됩니다.
=OFFSET($A$1,(ROW()-2)*3+INT((COLUMN()-3)),MOD(COLUMN()-3,1))
이 수식은 정보를 3개 행으로 나누어 배치하며, 열의 개수는 고정되어 있지 않습니다. 열 개수는 데이터의 전체 길이에 따라 달라지며, 중간에 빈 열이 있으면 해당 자리는 0으로 표시됩니다.
예를 들어 C2 셀부터 데이터를 배치하고 싶고, 데이터가 3개 행씩 반복된다는 것을 이미 알고 있다면 C2 셀에 위 수식을 입력합니다. 그다음 수식을 가로 방향으로 3행 범위까지 드래그하고, 세로 방향으로는 결과값이 0으로 표시되기 시작할 때까지 아래로 늘려 주면 됩니다.
데이터가 모두 정리되면 맨 윗줄에 제목(머리글)을 입력하고, 필요한 위치로 잘라내기·붙여넣기를 하면 완성됩니다.
도움이 되길 바랍니다!