이 글에서는 엑셀에서 세로 열(열 데이터)을 가로 행으로 변환하는 방법을 알아봅니다. 마이크로소프트 엑셀은 강력한 도구로, 다양한 작업을 손쉽게 처리할 수 있도록 도와줍니다. 사용자는 데이터를 표현하기 위해 행과 열로 구성된 데이터셋을 만들어 사용하는데, 경우에 따라 세로로 정렬된 열을 가로 방향의 행으로 바꿔야 할 필요가 생깁니다. 오늘은 이를 해결할 수 있는 6가지 방법을 소개하겠습니다. 각 방법을 익히면 누구나 쉽게 데이터를 원하는 형태로 전환할 수 있습니다.
엑셀에서 세로 열을 가로 행으로 바꾸는 6가지 방법
방법을 설명하기 위해 여러 판매자(Sellers)의 판매 금액(Sales Amount)이 담긴 데이터셋을 활용하겠습니다. 아래와 같이 세로로 나열된 열 데이터를 가로 방향의 행으로 전환하는 것이 목표입니다. 흔히 말하는 '전치(Transpose)' 작업이라고 생각하시면 됩니다.
변환이 완료되면 데이터셋은 아래 그림처럼 가로 형태로 배치됩니다.
1. 선택하여 붙여넣기(Paste Special)로 세로 열을 가로 행으로 변경하기
세로 열을 가로 행으로 바꾸는 가장 간단한 방법은 엑셀의 선택하여 붙여넣기(Paste Special) 기능을 활용하는 것입니다. 이 방법의 장점은 변환 과정에서 서식까지 그대로 유지되므로, 별도의 서식 작업 없이 한 번에 완성할 수 있다는 점입니다. 아래 단계를 따라 해 보세요.
실행 단계:
- 먼저 가로 행으로 변환할 범위를 선택합니다. 여기서는 B4:C10 범위를 선택했습니다.
- 마우스 오른쪽 버튼을 클릭해 메뉴를 연 뒤 복사(Copy)를 선택합니다.
- 단축키 Ctrl + C를 사용해도 됩니다.
- 붙여넣기를 시작할 위치의 셀을 선택합니다. 예시에서는 B12 셀을 선택했습니다.
- 홈(Home) 탭에서 붙여넣기(Paste) 아이콘을 클릭하면 드롭다운 메뉴가 나타납니다.
- 메뉴에서 선택하여 붙여넣기(Paste Special)를 클릭합니다.
- 단축키 Ctrl + Alt + V를 눌러도 동일하게 실행됩니다.
- 대화상자에서 바꾸어 붙이기(Transpose) 옵션에 체크하고 확인(OK)을 클릭합니다.
- 이제 세로 열이 가로 행으로 성공적으로 변환됩니다.
참고: 이 방법은 매우 편리하지만 한 가지 단점이 있습니다. 원본 세로 열의 내용을 수정해도 가로 행에는 자동으로 반영되지 않습니다. 원본 데이터 변경 시 결과가 함께 업데이트되기를 원한다면 아래에서 소개하는 함수 기반 방법을 사용하는 것이 좋습니다.
2. TRANSPOSE 함수로 세로 열을 가로 행으로 변환하기
엑셀 함수를 활용해서도 세로 열을 가로 행으로 변환할 수 있습니다. 대표적인 방법이 TRANSPOSE 함수입니다. 함수를 사용하면 원본 데이터가 변경될 때 결과도 자동으로 업데이트된다는 큰 장점이 있습니다. 다만 함수 방식은 서식이 함께 복사되지 않으므로, 변환 후 서식을 직접 적용해야 합니다.
실행 단계:
- B12 셀에 아래 수식을 입력합니다.
=TRANSPOSE(B4:C10)
- Ctrl + Shift + Enter를 눌러 배열 수식으로 확정하면 아래 그림과 같은 결과가 나타납니다.
참고: 변환된 행에는 서식이 포함되지 않으므로, 원본과 같은 모양을 만들려면 서식을 다시 지정해 주어야 합니다.
3. 수식을 텍스트로 입력해 세로 열을 가로 행으로 변환하기
수식을 일부러 텍스트 형태로 입력한 뒤 등호로 바꾸는 재미있는 꼼수도 있습니다. 먼저 특수 문자를 붙인 수식을 입력해 두고, 나중에 '찾기 및 바꾸기' 기능으로 등호(=)로 일괄 교체하는 방식입니다. 단계를 자세히 살펴보겠습니다.
실행 단계:
- B12 셀에 EdB4라고 입력합니다.
- B13 셀에는 EdC4라고 입력합니다.
예시에서는 B12 셀에 판매자(Seller), B13 셀에 판매 금액(Sales Amount)을 표시하려고 합니다. 원본 데이터에서 판매자 이름은 B4 셀에 있으므로 B12에는 EdB4를, 판매 금액은 C4 셀에 있으므로 B13에는 EdC4를 입력한 것입니다.
- B12 셀과 B13 셀을 함께 선택합니다.
- 채우기 핸들을 오른쪽으로 끌어 H 열까지 드래그합니다.
- B12:H13 범위를 선택합니다.
- Ctrl + H를 눌러 찾기 및 바꾸기(Find and Replace) 대화상자를 엽니다.
- '찾을 내용' 칸에는 Ed를, '바꿀 내용' 칸에는 =(등호)를 입력합니다.
- 모두 바꾸기(Replace All)를 클릭합니다.
- 교체 완료 메시지가 나타나면 확인(OK)을 클릭합니다.
- 모든 텍스트가 수식으로 바뀌면서 아래 그림과 같이 세로 데이터가 가로로 전환됩니다.
4. INDEX 함수로 세로 열을 가로 행으로 바꾸기
INDEX 함수를 사용해서도 세로 열을 가로 행으로 변환할 수 있습니다. INDEX 함수는 지정한 행 번호와 열 번호가 교차하는 위치의 셀 값을 반환하는 함수입니다. 여기에 ROW 함수와 COLUMN 함수를 조합해 수식을 완성합니다.
실행 단계:
- B12 셀에 아래 수식을 입력합니다.
=INDEX($B$4:$C$10,COLUMN(A1),ROW(A1))
첫 번째 인수는 변환 대상인 B4:C10 범위입니다. COLUMN(A1)은 A1 셀의 열 번호인 1을 반환하고, ROW(A1)은 A1 셀의 행 번호인 1을 반환합니다. 따라서 수식은 INDEX($B$4:$C$10,1,1)이 되며, B12 셀에는 해당 범위의 첫 번째 값인 'Seller'가 표시됩니다.
- Enter를 누른 뒤 채우기 핸들을 아래로 끌어 B13 셀까지 복사합니다.
- B12 셀과 B13 셀을 선택한 상태에서 채우기 핸들을 오른쪽으로 드래그합니다.
- 세로 열이 가로 행으로 변환됩니다.
- 마지막으로 서식을 적용하면 원본과 동일한 깔끔한 형태의 데이터셋이 완성됩니다.
5. OFFSET 함수로 세로 열을 가로 행으로 전환하기
함수 중에서는 OFFSET 함수를 활용하는 방법도 있습니다. OFFSET 함수는 기준 셀에서 지정한 행 수와 열 수만큼 떨어진 위치의 셀 값을 반환합니다. 이 역시 ROW 함수와 COLUMN 함수와 함께 사용합니다.
실행 단계:
- B12 셀에 아래 수식을 입력합니다.
=OFFSET($B$4,COLUMN(A1)-1,ROW(A1)-1)
여기서 OFFSET 함수의 기준 셀은 B4입니다. COLUMN(A1)-1과 ROW(A1)-1은 각각 기준 셀에서 얼마나 떨어진 행과 열의 값을 가져올지 결정합니다.
- Enter를 누르고 채우기 핸들을 아래로 드래그합니다.
- B12 셀과 B13 셀을 선택한 후, 채우기 핸들을 오른쪽으로 끌어 H 열까지 확장합니다.
- 세로 열 데이터가 가로 행으로 변환됩니다.
- 마지막으로 서식을 적용해 원본과 비슷한 모양으로 마무리합니다.
6. INDIRECT 함수로 세로 열을 가로 행으로 전치하기
마지막 방법은 INDIRECT 함수를 사용하는 것입니다. INDIRECT 함수는 텍스트 문자열로 지정된 셀 참조를 실제 참조로 변환해 값을 반환합니다. 앞서 소개한 OFFSET 방식과 유사하게 활용할 수 있습니다.
실행 단계:
- C12 셀에 아래 수식을 입력합니다.
=INDIRECT("B"&COLUMN()+1)
C12 셀에서 COLUMN()의 반환값은 3입니다. 따라서 수식은 INDIRECT(B4)와 같아지고, 결과적으로 C12 셀에 'Seller'가 표시됩니다.
- Enter를 누른 후 아래 수식을 입력합니다.
=INDIRECT("C"&COLUMN()+1)
- 채우기 핸들을 오른쪽으로 끌어 I 열까지 드래그합니다.
- 아래 그림과 같이 세로 열이 가로 행으로 성공적으로 전치됩니다.
마무리
이번 글에서는 엑셀에서 세로 열을 가로 행으로 변환하는 6가지 방법을 살펴보았습니다. 선택하여 붙여넣기부터 TRANSPOSE, INDEX, OFFSET, INDIRECT 함수까지 상황에 맞는 방법을 골라 사용하면 업무 효율을 크게 높일 수 있습니다. 원본 데이터의 변경 사항이 자동 반영되기를 원한다면 함수 기반 방법을, 빠른 작업이 우선이라면 선택하여 붙여넣기를 추천합니다. 추가 질문이나 의견이 있다면 댓글로 남겨주세요.