Excel에서 데이터 전치하는 완벽 가이드 (4가지 방법)

Excel에서 데이터를 전치하면 데이터 방향을 뒤집어 행을 열로, 열을 행으로 바꿀 수 있습니다. 이 기술은 더 나은 분석, 시각화, 또는 보고를 위해 데이터셋을 재구성해야 할 때 특히 유용합니다.

피벗 테이블을 위해 데이터를 준비하거나, 요약 보기를 만들거나, 단순히 데이터 레이아웃을 변경해야 하는 경우, Excel은 데이터를 전치하는 여러 방법을 제공합니다. 전통적으로 다음과 같습니다:

1. 선택하여 붙여넣기 - 빠른 일회성 변환
2. TRANSPOSE() 함수 - 동적 수식 기반 접근법
3. Power Query - 더 큰 데이터셋을 위한 고급 방법

그러나 더 빠른 새로운 방법이 등장했습니다:

4. Excelmatic과 같은 AI 기반 도구 - 일반 언어 명령을 사용하여 작업을 자동화하는 지능형 접근법.

이 글에서는 각 방법을 단계별로 사용하는 방법을 배우고, 주요 차이점을 이해하며, 특정 작업에 가장 적합한 방법을 결정할 수 있습니다.

빠른 답변

데이터를 급히 전치해야 하나요? 가장 빠른 옵션은 다음과 같습니다:

• 가장 빠른 방법 (AI 기반): Excelmatic과 같은 AI 도구를 사용하세요. 파일을 업로드하고 "Sheet1의 A1에서 B5까지 데이터를 전치해 줘"라고 요청하기만 하면 됩니다. AI가 수동 단계 없이 모든 것을 즉시 처리합니다.

• 가장 빠른 수동 방법 (선택하여 붙여넣기):

  • 데이터를 선택하고 복사합니다(Ctrl + C).
  • 대상 셀을 마우스 오른쪽 버튼으로 클릭합니다.
  • **"선택하여 붙여넣기"**를 선택합니다.
  • "전치" 상자를 선택하고 확인을 클릭합니다.

이렇게 하면 행을 열로, 열을 행으로 즉시 뒤집어 대부분의 전치 요구에 빠른 수동 솔루션을 제공합니다.

Excel에서 전치란 무엇인가요?

Excel에서 전치란 행을 열로, 열을 행으로 변환하는 것을 의미합니다. 본질적으로 데이터를 90도 회전시켜 방향을 변경하면서 모든 정보를 보존합니다.

데이터를 전치하면 첫 번째 행이 첫 번째 열이 되고, 두 번째 행이 두 번째 열이 되는 식입니다.

예를 들어, 제품 이름이 행에 있고 월별 매출이 열에 있는 데이터셋이 있다면, 전치하면 제품 이름이 열에, 월별 매출이 행에 표시됩니다.

Excel에서 데이터 전치 방법

Excel은 데이터를 전치하는 네 가지 고유한 방법을 제공하며, 각각 고유한 장점과 이상적인 사용 사례가 있습니다. 적절한 접근법을 선택할 수 있도록 각 기술을 단계별로 살펴보겠습니다.

1. 선택하여 붙여넣기로 데이터 전치하기

선택하여 붙여넣기 방법은 Excel에서 데이터를 전치하는 가장 간단한 수동 방법입니다. 이 방법은 전치된 방향으로 데이터의 일회성 정적 복사본을 생성합니다. 예를 들어 살펴보겠습니다. 다음과 같은 세로 형식의 지역별 매출 데이터가 있다고 가정해 보겠습니다:

이 데이터를 세로에서 가로 레이아웃으로 전치하려면:

1. 전치하려는 모든 데이터를 선택합니다(이 경우 셀 A1:B5).
2. 데이터를 복사합니다(Ctrl+C 또는 마우스 오른쪽 버튼 클릭 후 복사 선택).
3. 전치된 데이터가 나타나길 원하는 셀을 클릭합니다(예: 셀 D1).
4. 마우스 오른쪽 버튼을 클릭하고 상황에 맞는 메뉴에서 선택하여 붙여넣기를 선택합니다.

5. 선택하여 붙여넣기 대화 상자에서 전치 상자를 선택하고 확인을 클릭합니다.

전치 후 결과는 아래와 같습니다:

데이터가 완전히 회전되었음을 확인하세요. 이전에 행에 있던 것이 이제 열에 있고, 그 반대도 마찬가지입니다.

이 방법을 사용할 때:

• 빠른, 일회성 데이터 변환을 위해.
• 수식이 필요 없는 간단한 솔루션이 필요할 때.
• 자주 업데이트할 필요가 없는 작은 데이터셋의 경우.

장점: 몇 번의 클릭만으로 간단하고 빠르게 적용 가능. 한계: 원본 데이터가 변경되어도 업데이트되지 않는 정적 복사본을 생성합니다.

2. TRANSPOSE() 함수 사용하기

TRANSPOSE() 함수는 선택하여 붙여넣기에 대한 동적 대안을 제공합니다. 이전 방법과 달리, 이 함수는 원본 데이터에 대한 실시간 연결을 유지하여 소스 데이터가 변경될 때마다 전치된 결과를 자동으로 업데이트합니다.

동일한 지역별 매출 데이터를 사용하여 설명해 보겠습니다:

1. 먼저 대상 범위를 선택해야 합니다. 원본 데이터가 5행 x 2열이므로, 전치된 구조를 수용하려면 2행 x 5열 범위를 선택해야 합니다.
2. 대상 셀을 선택한 상태에서 수식 입력줄에 TRANSPOSE() 수식을 입력합니다:

3. 최신 Excel( Microsoft 365 또는 2021)에서는 Enter 키를 누르기만 하면 됩니다. 이전 Excel 버전의 경우, 이는 배열 수식이므로 Ctrl+Shift+Enter를 눌러야 합니다.

결과는 이전 예제와 유사하게 보이지만, 원본 데이터의 값을 변경하면 전치된 버전이 자동으로 업데이트됩니다.

이 방법을 사용할 때:

• 소스 데이터가 자주 변경될 때.
• 원본 데이터와 전치된 데이터 간의 실시간 연결을 유지해야 할 때.
• 동적 보고서나 대시보드를 만들 때.

장점: 자동으로 업데이트되는 동적 링크를 생성합니다. 한계: 올바른 대상 범위 크기를 신중하게 선택해야 하며, 초보자에게는 혼란스러울 수 있습니다.

3. Power Query로 데이터 전치하기

Power Query는 Excel의 가장 강력한 데이터 변환 도구로, 더 큰 데이터셋과 복잡한 반복 가능한 워크플로우를 처리하는 데 완벽합니다. 더 많은 단계가 필요하지만, 뛰어난 유연성을 제공합니다.

동일한 지역별 매출 데이터를 사용하여 설명해 보겠습니다:

1. 데이터 범위를 선택하고 데이터 탭을 클릭한 다음 테이블/범위에서를 선택합니다.
2. Power Query 편집기가 열리며 데이터가 로드됩니다. 이제 리본 메뉴의 변환 탭을 클릭하고 메뉴 옵션에서 전치를 선택합니다.

3. 미리 보기 창에서 데이터가 즉시 전치된 것을 볼 수 있습니다. 수식 입력줄에는 적용된 변환을 나타내는 Table.Transpose(Source)가 표시됩니다.

4. 만족스러우면 홈 탭에서 닫기 및 로드를 클릭하여 전치된 데이터를 Excel 워크시트로 다시 보냅니다.

이 방법을 사용할 때:

• 다른 방법이 느린 대규모 데이터셋의 경우.
• 단순 전치 외에 추가 변환(예: 정리, 필터링)을 수행해야 할 때.
• 자동화하고 새로 고치고 싶은 반복 작업의 경우.

장점: 대규모 데이터셋과 복잡한 워크플로우에 이상적이며, 새로 고칠 수 있는 쿼리를 생성합니다. 한계: 초보자에게는 학습 곡선이 더 가파르며, 간단한 전치에도 더 많은 단계가 포함됩니다.

4. Excelmatic을 사용한 AI 기반 접근법

속도와 단순성의 극치를 위해, Excelmatic과 같은 AI 기반 도구가 게임 체인저가 되고 있습니다. 메뉴를 탐색하거나 수식을 작성하는 대신, 일반 언어로 AI에게 원하는 것을 간단히 말할 수 있습니다.

Excelmatic은 복잡한 작업을 처리해 주는 Excel AI 에이전트입니다. 동일한 지역별 매출 데이터를 전치하는 방법은 다음과 같습니다:

1. 파일 업로드: Excel 파일을 Excelmatic으로 드래그 앤 드롭합니다.
2. 요청 명시: 채팅 인터페이스에서 다음과 같은 간단한 명령을 입력합니다:

   A1:B5 범위의 데이터를 전치해 줘.

또는 더 대화적으로: > 지역별 매출 데이터의 행과 열을 바꿔 줘.

3. 즉각적인 결과 얻기: Excelmatic이 요청을 처리하고, 전치를 수행하며, 올바르게 구조화된 데이터를 다운로드하거나 추가 분석할 수 있도록 제공합니다. 대상 범위를 선택하거나 배열 수식에 대해 걱정할 필요가 없습니다.

이 방법을 사용할 때:

• 가장 빠르고 쉬운 솔루션을 원할 때.
• Excel의 특정 함수나 메뉴에 익숙하지 않은 사용자의 경우.
• 여러 데이터 정리 또는 분석 단계를 순차적으로 수행해야 할 때.

장점:

• 수월함: 학습 곡선 없음. 필요한 것을 설명하기만 하면 됩니다.
• 매우 빠름: 전체 프로세스를 몇 초 만에 자동화합니다.
• 지능적: 출력 범위를 수동으로 정의하거나 다른 기술적 세부 사항을 처리할 필요가 없습니다.
• 다용도: "이 데이터를 전치한 다음 매출의 막대 그래프를 만들어 줘"와 같이 더 큰 분석의 일부로 전치를 처리할 수 있습니다.

한계:

• 인터넷 연결과 타사 도구 사용이 필요합니다.
• 출력은 새로운 정적 데이터셋입니다(프로세스를 쉽게 다시 실행할 수 있음).

방법 비교 한눈에 보기

열, 행 및 테이블 전치하기

네 가지 주요 방법을 살펴보았지만, 몇 가지 특정 시나리오를 간략히 다루겠습니다. 네 가지 방법 모두 다음과 같은 결과를 달성할 수 있습니다.

Excel에서 열을 행으로 전치하는 방법

데이터가 열로 구성되어 있고 행으로 필요할 때:

• 선택하여 붙여넣기: 열을 복사하고, 대상을 마우스 오른쪽 버튼으로 클릭한 후 선택하여 붙여넣기 → 전치를 선택합니다.
• TRANSPOSE() 함수: 대상 행을 선택하고 =TRANSPOSE(A1:A5)를 입력합니다.
• Power Query / Excelmatic: 두 도구 모두 각각의 전치 기능으로 이 변환을 원활하게 처리합니다.

Excel에서 행을 열로 전치하는 방법

데이터가 행에 있지만 열로 필요하다면, 단계는 동일합니다. Excel의 전치 도구는 행과 열을 상호 교환 가능한 것으로 봅니다.

• 선택하여 붙여넣기: 행을 복사하고, 대상을 마우스 오른쪽 버튼으로 클릭한 후 선택하여 붙여넣기 → 전치를 선택합니다.
• TRANSPOSE() 함수: 대상 열을 선택하고 =TRANSPOSE(A1:F1)을 입력합니다.

더 큰 데이터셋을 작업하는 경우, Power Query 또는 Excelmatic은 행-열 변환을 자동화하는 데 매우 효과적입니다.

Excel에서 테이블 전치하는 방법

전체 테이블을 작업할 때:

1. 작은 테이블의 경우 선택하여 붙여넣기가 효과적입니다.
2. 큰 테이블의 경우 Power Query가 더 많은 제어를 제공하며 모든 테이블 크기를 효율적으로 처리할 수 있습니다.
3. 가장 빠른 결과를 얻으려면 Excelmatic에게 "'SalesData'라는 테이블을 전치해 줘"라고 요청하기만 하면 됩니다.

테이블을 전치하면 데이터뿐만 아니라 헤더도 전환된다는 점을 기억하세요.

추가 고려 사항

Excel에서 데이터를 전치할 때 다음 사항을 염두에 두세요:

빈 셀 처리하기

TRANSPOSE() 함수는 빈 칸을 0으로 대체할 수 있습니다. 이를 방지하려면 중첩된 IF() 문을 사용하세요: =TRANSPOSE(IF(A1:F5="","",A1:F5))

이 수식은 빈 셀을 확인하고 전치된 결과에서 빈 상태로 유지합니다. 선택하여 붙여넣기, Power Query, Excelmatic과 같은 도구는 일반적으로 빈 셀을 올바르게 보존합니다.

소스 데이터에 대한 링크 유지하기

• 선택하여 붙여넣기는 정적 복사본을 생성합니다(링크 없음).
• **TRANSPOSE()**는 실시간 동적 링크를 생성합니다.
• Power Query는 새로 고칠 수 있는 링크를 생성합니다.
• Excelmatic은 정적 출력을 생성하지만, 프로세스가 너무 빨라 업데이트된 데이터에 대해 즉시 다시 실행할 수 있습니다.

데이터 업데이트를 어떻게 처리해야 하는지에 따라 방법을 선택하세요.

조건부 서식과 함께 TRANSPOSE() 사용하기

서식(색상, 글꼴, 조건부 서식)은 TRANSPOSE() 함수나 Power Query를 사용할 때 이전되지 않습니다. 새로운 전치된 범위에 서식 규칙을 다시 적용해야 합니다. 선택하여 붙여넣기는 때때로 서식을 붙여넣는 옵션을 제공하지만 일관성이 없을 수 있습니다.

결론

데이터 전치는 데이터셋을 재구성하는 기본적인 Excel 기술입니다. 우리는 네 가지 강력한 방법을 살펴보았으며, 각각 고유한 강점이 있습니다:

• 빠른 정적 변경을 위한 선택하여 붙여넣기.
• 동적, 수식 연결 결과를 위한 TRANSPOSE() 함수.
• 대규모 데이터셋에 대한 강력하고 반복 가능한 워크플로우를 위한 Power Query.
• 수동 단계를 제거하기 위해