Excel 에서 데이터를 전치하는 5 가지 방법(단계별 튜토리얼)

데이터 전치란 배열의 방향을 변경하여 행을 열로， 열을 행으로 변환하는 것을 의미합니다。 이 튜토리얼에서는 배열의 방향을 전환하고 원하는 결과를 얻기 위한 다섯 가지 서로 다른 방법을 소개합니다。

특수 붙여넣기로 열을 행으로 전환

전치하고 데이터 연결 소스로

• TRANSPOSE 함수 사용
• INDIRECT， ADDRESS， COLUMN 및 ROW 함수 사용
• 특수 붙여넣기 및 찾기 및 바꾸기 사용
• Power Query 함수 사용

전치 데이터 예제 다운로드(。xlsx)

동영상: Excel 에서 데이터 전치하기

특수 붙여넣기로 열을 행으로 전환

단계 1: 전치할 범위 복사

행과 열을 전환할 셀 범위를 선택한 후 「Ctrl」 + 「C」를 눌러 범위를 복사합니다。

단계 2: 붙여넣기 전치 옵션 선택

대상 범위의 첫 번째 셀을 마우스 오른쪽 버튼으로 클릭한 후， 컨텍스트 메뉴의 「선택적 붙여넣기」 아래에 있는 「전치」 아이콘을 클릭합니다。

결과

짜잔！ 범위가 순식간에 전치되었습니다！

전치하고 데이터 연결 소스에 연결

범위의 방향을 동적으로 전환(열을 행으로， 행을 열로)하고 전환된 결과를 원본 데이터셋과 연결하려면 다음 중 하나의 방법을 사용할 수 있습니다：

• 「TRANSPOSE 함수」(사용은 간편하지만 결과는 편집 불가능함)
• 「INDIRECT, ADDRESS, COLUMN 및 ROW 함수」(긴 수식이지만 결과를 수정 가능함)
• 「특수 붙여넣기 및 찾기 및 바꾸기」(복잡하지만 이해하기 쉬움)
• 「Power Query」(표 형식의 결과로 간편함)

TRANSPOSE 함수를 사용하여 행을 열로 변경

「TRANSPOSE」 함수를 사용하여 행을 열로， 열을 행으로 전환하려면 다음 단계를 따르세요。

단계 1: 원본 셀과 동일한 수의 빈 셀을 다른 방향으로 선택

팁: Excel 365 또는 Excel 2021 을 사용하는 경우 이 단계를 건너뛸 수 있습니다。

예를 들어 원본 테이블이 A1:C4 범위에 있다고 가정하면, 이 테이블은 4 행 3 열로 구성됩니다. 따라서 전치된 테이블은 3 행 4 열이 되므로, 3 행 4 열의 빈 셀을 선택해야 합니다。

단계 2: TRANSPOSE 수식 입력

수식 표시줄에 아래 수식을 입력한 후 「Ctrl」 + 「Shift」 + 「Enter」를 눌러 결과를 얻습니다。

팁： Excel 365 또는 Excel 2021 사용자의 경우 「Enter」를 누르면 됩니다。

=TRANSPOSE(A1:C4)

결과

행이 열로 변환되고， 열이 행으로 변환되었습니다。

INDIRECT， ADDRESS， COLUMN 및 ROW 함수를 사용하여 데이터 회전

앞서 설명한 수식은 이해하고 사용하기 쉽지만， 회전된 테이블 내 어떤 셀도 편집하거나 삭제할 수 없다는 단점이 있습니다。 따라서 「INDIRECT」， 「ADDRESS」， 「COLUMN」 및 「ROW」 함수를 사용하는 수식을 소개합니다。 원본 테이블이 A1:C4 범위에 있다고 가정하고， 열을 행으로 변환하면서 회전된 데이터를 원본 데이터셋과 연결하려면 다음 단계를 따르세요。

단계 1: 수식 입력

대상 범위의 가장 왼쪽 상단 셀(A6, 본 예시 기준)에 아래 수식을 입력하고 「Enter」를 누릅니다：

=INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))

단계 2: 수식을 오른쪽 및 아래쪽 셀로 복사

수식이 입력된 셀을 선택한 후 채우기 핸들(셀의 오른쪽 하단 모서리에 있는 작은 녹색 사각형)을 필요한 만큼 아래로， 그리고 오른쪽으로 드래그합니다。

결과

열과 행이 즉시 전환되었습니다。

특수 붙여넣기 및 찾기 및 바꾸기를 사용하여 열을 행으로 변환

특수 붙여넣기 방법과 찾기 및 바꾸기 기능을 함께 사용하면 몇 단계 더 거쳐 데이터를 전치하면서 전치된 셀을 원본 셀에 연결할 수 있습니다。

단계 1: 전치할 범위 복사

전치할 셀 범위를 선택한 후 「Ctrl」 + 「C」를 눌러 범위를 복사합니다。

단계 2: 링크 붙여넣기 옵션 적용

1. 빈 셀을 마우스 오른쪽 버튼으로 클릭한 후 「특수 붙여넣기」을 클릭합니다。

   

2. 「링크 붙여넣기」 버튼을 클릭합니다。

   

아래와 같은 결과를 얻게 됩니다：

단계 3: 링크 붙여넣기 결과에서 등호(=) 제거

1. 결과 범위(A6:C9)를 선택하고 「Ctrl」 + 「H」를 누른 후 찾기 및 바꾸기 대화 상자에서 「=」을 “@EO“(범위 선택에 존재하지 않는 문자)로 바꿉니다。

   

2. 「모두 바꾸기」를 클릭한 후 대화 상자를 닫습니다。 다음은 데이터가 어떻게 보이는지 보여줍니다。

   

단계 4: 교체된 링크 붙여넣기 결과 전치

범위(A6:C9)를 선택하고 「Ctrl」 + 「C」를 눌러 복사합니다。 빈 셀(A11, 본 예시 기준)을 마우스 오른쪽 버튼으로 클릭한 후 선택적 붙여넣기에서 「전치」 아이콘을 선택하여 전치된 결과를 붙여넣습니다。

단계 5: 전치된 결과를 원본 데이터에 연결하기 위해 등호(=) 다시 추가

1. 전치된 결과 데이터 A11:D13 을 계속 선택한 상태에서 「Ctrl」 + "H"를 누르고 “@EO"를 「=」(“단계 3"의 반대)로 바꿉니다。

   

2. 「모두 바꾸기」를 클릭한 후 대화 상자를 닫습니다。

결과

데이터가 전치되었으며 원본 셀과 연결되어 있습니다。

Power Query 로 전치하고 데이터 연결 소스에 연결

Power Query 는 Excel 에서 데이터를 쉽게 전치할 수 있는 강력한 데이터 자동화 도구입니다。 다음 단계에 따라 작업을 완료할 수 있습니다：

단계 1: 전치할 범위 선택 및 Power Query 편집기 열기

전치할 데이터 범위를 선택합니다。 그런 다음 「데이터」 탭의 「데이터 가져오기 및 변환」 그룹에서 「테이블/범위에서」를 클릭합니다。

단계 2: Power Query 로 열을 행으로 변환

1. 「변환」 탭으로 이동합니다。 「첫 번째 행을 머리글로 사용」 드롭다운 메뉴에서 「머리글을 첫 번째 행으로 사용」을 선택합니다。

   

2. 「전치」를 클릭합니다。

   

단계 3: 전치된 데이터를 시트에 저장

「파일」 탭에서 「닫기 및 로드」를 클릭하여 「Power 편집기」 창을 닫고 전치된 데이터를 로드할 새 워크시트을 생성합니다。

결과

전치된 데이터는 새로 생성된 워크시트에 테이블로 변환됩니다。

관련 문서

• Excel 에서 범위를 빠르게 전치하고 빈 셀을 건너뛰려면 어떻게 해야 하나요？
• 범위를 전치하여 붙여넣을 때 빈 셀도 함께 붙여넣어집니다. 그러나 아래 스크린샷과 같이 빈 셀을 무시하고 범위만 전치하려는 경우가 있습니다. Excel 에서 범위를 빠르게 전치하면서 빈 셀을 건너뛸 수 있는 방법이 있을까요？
• 한 열에서 매 5 행(또는 n 행)씩 여러 열로 전치하려면 어떻게 해야 하나요？
• A 열에 긴 데이터가 있고, 이제 A 열의 매 5 행씩 여러 열로 전치하려고 합니다. 예를 들어 A1:A5 를 C6:G6 으로, A6:A10 을 C7:G7 로 전치하는 식입니다(아래 스크린샷 참조). Excel 에서 반복적인 복사 및 붙여넣기 없이 이 작업을 처리하려면 어떻게 해야 할까요？
• 열과 행을 단일 열로 전치/변환하려면 어떻게 해야 하나요？
• Excel 워크시트를 사용하다 보면 다음과 같은 문제에 직면할 수 있습니다。 데이터 범위를 단일 열로 변환하거나 전치하려면 어떻게 해야 할까요？(다음 스크린샷 참조) 이제 이 문제를 해결할 수 있는 세 가지 빠른 팁을 소개합니다。
• 열과 행을 단일 행으로 전치/변환하려면 어떻게 해야 하나요？
• 여러 행과 열을 하나의 긴 행으로 결합하려면 어떻게 해야 할까요？ 아마도 수동으로 하나씩 복사하여 행으로 결합하는 것이 쉬워 보일 수 있습니다。 그러나 행과 열이 수백 개라면 이 작업은 시간이 많이 걸리고 번거로울 수 있습니다。 여기서 이를 해결할 수 있는 몇 가지 빠른 팁을 소개하겠습니다。