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 Editor" 창을 닫고 전치된 데이터를 로드할 새 워크시트를 생성합니다.

결과

전치된 데이터는 새로 생성된 워크시트에서 표로 변환됩니다.

관련 기사

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