엑셀과 구글 시트 행열 바꾸기의 모든 방법을 설명합니다.
구글 시트에서는 transpose 함수와 순서 바꾸기 기능을 통해서 가로로 나열된 데이터를 세로로 구조를 변환해서 피벗테이블로 분석해보겠습니다.
엑셀은 함수, 행열 변환, 파워쿼리 세 가지 모든 기능을 설명합니다.
1. 데이터에서 왜 행열 변환이 필요한가
데이터 분석에 있어서 세로 방향 데이터 쌓기는 정말 중요합니다. 결국 세로로 나열된 데이터들을 피벗테이블을 활용해서 분석할 수 있기 때문입니다.
하지만 실무에서는 간혹 세로가 아닌 가로로 입력된 수많은 데이터를 받곤 합니다. 오늘 이 글을 통해서 가로로 나열된 많은 양의 데이터를 행/열 변환 기능을 통해서 쉽게 엑셀과 구글 시트 모두에서 세로형 데이터로 변환하고 분석할 수 있는 방법까지 알아보는 시간을 가지겠습니다.
1-1. 구글 시트 행열 바꾸기
구글 시트 행열 바꾸기는 총 두가지 방법을 통해서 설명 합니다. 순서 바꾸기와 함수를 사용해서 바꿀 수 있습니다. 각각의 차이점도 함께 알아봅니다.
1-1-1. 순서 바꾸기
아래와 같이 어떤 매장의 매출액 데이터가 있습니다. 날짜,월,휴일 및 매출의 모든 데이터가 세로가 아닌 가로 방향으로 입력되어 있습니다.
먼저, Ctrl과 A를 눌러서 전체 데이터를 선택 – 복사 – 새로운 시트에 선택하여 붙여넣기 – 순서 바꾸기를 클릭합니다.
모든 가로 방향 데이터가 행/열 변환이 되어서 세로 방향 데이터로 바뀌었습니다. 여기서 간단하게 첫 번째 행을 추가해서 머리글만 추가해주면 피벗테이블 분석을 위한 모든 준비가 끝났습니다.
피벗테이블을 추가해서 간단하게 월별 매출을 나타내는 차트를 완성해 보았습니다. 어떤가요? 의외로 클릭 몇 번으로 금방 데이터 분석을 위한 준비를 모두 마첬습니다.
순서 바꾸기는 데이터의 형식과 셀의 글자 크기나 색상 등의 서식을 있는 그대로 유지한 채로 행열 전환 됩니다.
물론 수식으로 입력된 데이터는 수식도 함께 있는 그대로 변환 됩니다.
1-1-2. TRANSPOSE 함수 사용
TRANSPOSE 함수를 사용해서 행열 변환할 수 있습니다. 아래 사진처럼 한 셀에서 Transpose 함수를 입력 후 가로 셀의 데이터 범위를 입력합니다.
오른쪽 열으로 그 다음 행의 데이터를 차례로 입력하면 모든 가로 방향 데이터가 세로 방향으로 변환 됩니다.
마찬가지로 머리글을 추가해주면 피벗테이블을 활용한 데이터 분석의 모든 준비가 끝났습니다.
TRANSPOSE 함수를 사용한 행열 변환은 오로지 데이터의 값만 참조를 해서 텍스트, 글씨체, 크기, 색상 등의 데이터의 서식은 유지되지 않습니다.
1-2. 엑셀 행열 변환
구글 시트와 마찬가지로 엑셀에서도 행/열 변환 기능이 있습니다. 행/열 바꾸기, TRANSPOSE 함수 외에도 파워쿼리를 활용한 행열 변환 방법이 있습니다.
1-2-1. 행/열 바꿈
먼저 데이터를 Ctrl + A 버튼으로 전체 선택 – 복사하고 새로운 시트에서 선택하여 붙여넣기 – 행/열 바꿈(T)을 클릭합니다.
세로 방향 데이터가 가로 방향으로 변환되었습니다. 머리글만 추가해 줍니다.
선택하여 붙여 넣기를 통한 행열 변환도 구글 시트와 마찬가지로 데이터의 형식과 셀의 글자 크기나 색상 등의 서식을 있는 그대로 유지한 채로 행열 전환 되고 수식도 함께 변환 됩니다.
1-2-2. TRANSPOSE 함수
구글 시트 처럼 TRANSPOSE 함수를 사용해서 동일한 방법으로 데이터를 변환할 수 있습니다.
엑셀의 TRANSPOSE 함수도 마찬가지로 텍스트, 글씨체, 크기, 색상 등 서식은 유지되지 않습니다.
마이크로소프트 TRANSPOSE 함수 사용법 바로가기 – 공식홈페이지
1-2-3. 파워쿼리를 통한 행/열 바꿈
파워쿼리는 엑셀이 제공하는 데이터 구조를 변환시키는 기능에 정말 강력한 기능입니다. 파워쿼리는 행/열 전환 기능 외에도 많은 다양한 기능들을 제공하지만 이 글에서는 행/열 변환 기능만 간단히 설명하겠습니다.
데이터 분석을 위해서는 실무자가 다루면 아주 유용한 기능임으로 다른 기능도 꼭 함께 사용해보기를 추천합니다.
Ctrl + A 를 눌러 데이터 전체 선택 – Ctrl +T 선택된 데이터 표 만들기를 클릭합니다. 아래는 머리글 포함을 해제하고 표를 만들고 데이터 – 테이블/범위에서를 클릭하면 파워쿼리 편집기가 실행됩니다.
파워쿼리 편집기가 실행되면, 변환 – 행/열 바꿈을 클릭하면 화살표 아래와 같이 데이터가 세로로 변환됩니다. 여기서 머리글에 Column1 , 2, 3 등만 더블 클릭해서 이름을 변경 합니다.
머리글을 변환하고 닫기 및 로드를 클릭해서 데이터를 위치할 시트를 선택합니다.
아래와 같이 파워쿼리를 통한 데이터 가공이 완료 되었습니다. 이 데이터는 파워쿼리에 언제든지 편집기를 실행해서 데이터 구조를 편집할 수 있습니다.
엑셀과 구글 시트 행열 바꾸기 방법에 대해서 알아 보았습니다. 구글 시트에 대한 다른 유용한 정보는 아래 링크를 참고 바랍니다.