엑셀에서 행과 열 제목이 있는 데이터 표(교차 표)를 작업하다 보면, 특정 목적에 따라 이를 세로 목록 형태로 변환해야 하는 경우가 종종 있습니다. 예를 들어 피벗 분석, 데이터 정리, 또는 다른 시스템에 맞는 입력 형식을 만들 때가 그렇습니다. 이 글에서는 교차 표를 목록으로 쉽고 빠르게 변환할 수 있는 3가지 방법을 소개합니다.
연습용 파일 다운로드
아래 무료 엑셀 템플릿을 다운로드하여 직접 따라 해볼 수 있습니다.
엑셀에서 표를 목록으로 변환하는 3가지 방법
방법 1: 피벗테이블 및 피벗차트 마법사 활용하기
먼저 예제 데이터를 살펴보겠습니다. 영업사원별로 3주간의 매출액이 정리되어 있으며, 2행에는 주(週)별 제목이, B열에는 영업사원 이름이 배치되어 있습니다. 우리의 목표는 주 제목을 한 열에, 매출액을 다른 열에 나열한 목록을 만드는 것입니다. 이때 피벗테이블 및 피벗차트 마법사(PivotTable And PivotChart Wizard)를 사용하면 됩니다. 절차는 다소 길지만 어렵지 않습니다.
먼저 상단 메뉴 모음에 피벗테이블 및 피벗차트 마법사를 추가해야 합니다.
1단계:
➥ 파일(File)을 클릭하면 다양한 옵션 메뉴가 나타납니다.
2단계:
➥ 하단의 옵션(Options) 메뉴를 클릭합니다. 그러면 Excel 옵션 창이 열립니다.
3단계:
➥ 다음 순서대로 클릭합니다: 리본 사용자 지정 > 리본에 없는 명령 > 피벗테이블 및 피벗차트 마법사.
4단계:
➥ 이어서 데이터 > 새 그룹 > 새 그룹(사용자 지정) > 추가를 차례로 누릅니다.
잠시 후 아래 이미지처럼 피벗테이블 및 피벗차트 마법사 항목이 추가된 것을 확인할 수 있습니다.
5단계:
➥ 확인을 누르면 상단 메뉴 모음에 마법사 옵션이 생성됩니다.
6단계:
➥ 데이터 > 피벗테이블 및 피벗차트 마법사를 순서대로 클릭하면 대화상자가 나타납니다.
7단계:
➥ 여러 병합 범위(Multiple consolidation ranges)와 피벗테이블에 체크 표시를 합니다.
➥ 그런 다음 다음을 누릅니다.
8단계:
➥ 1개 페이지 필드 직접 만들기(Create a single page field for me) 라디오 버튼을 선택하고 다음을 누릅니다.
9단계:
➥ 데이터 범위를 선택한 후 다시 다음을 클릭합니다.
10단계:
➥ 원하는 워크시트 위치를 지정합니다. 여기서는 새 워크시트를 선택했습니다.
➥ 마침(Finish)을 클릭합니다.
그러면 엑셀 창 오른쪽에 피벗테이블 필드 창이 나타납니다.
11단계:
➥ 행(Row)과 열(Column) 옵션의 선택을 해제합니다.
➥ 마지막으로 값 영역의 값 합계(Sum of Value) 셀을 더블클릭합니다.
그러면 새 시트에 원하는 목록 형태의 결과가 완성됩니다.
함께 읽으면 좋은 글: 엑셀에서 피벗테이블 삽입하는 방법
방법 2: 파워 쿼리(Power Query)로 표를 목록으로 바꾸기
파워 쿼리(Power Query)는 다양한 출처의 데이터를 손쉽게 수집하고 엑셀에서 사용하기 좋은 형식으로 가공할 수 있는 강력한 도구입니다. 이 기능을 활용하면 표를 목록으로 변환하는 작업도 몇 번의 클릭만으로 처리할 수 있습니다.
1단계:
➥ 데이터 범위를 선택합니다.
➥ 그런 다음 데이터 > 테이블/범위에서(From Table/Range)를 클릭합니다.
그러면 테이블 만들기(Create Table) 대화상자가 열립니다.
2단계:
➥ 확인을 누르면 파워 쿼리 편집기(Power Query Editor)라는 새 창이 열립니다.
3단계:
➥ Ctrl 키를 누른 상태에서 마우스 왼쪽 버튼으로 주(週)별 3개 열을 모두 선택합니다.
➥ 그런 다음 변형 > 열 피벗 해제(Unpivot Columns)를 클릭합니다.
잠시 후 아래와 같은 목록 형태로 변환된 것을 확인할 수 있습니다.
4단계:
➥ 이제 닫기 및 로드(Close & Load)를 누르기만 하면 됩니다.
그러면 새 워크시트에 기대했던 목록이 생성된 것을 볼 수 있습니다.
함께 읽으면 좋은 글: 엑셀에서 범위를 표로 변환하는 방법
방법 3: VBA 매크로로 표를 목록으로 변환하기
코딩에 익숙하다면 엑셀 VBA를 사용해 이 작업을 자동화할 수도 있습니다. 과정을 살펴보겠습니다.
1단계:
➥ 시트 탭에서 마우스 오른쪽 버튼을 클릭합니다.
➥ 바로 가기 메뉴에서 코드 보기(View Code)를 선택합니다.
그러면 VBA 편집기 창이 열립니다.
2단계:
➥ 아래 코드를 입력합니다.
Sub TransposeThis()
Set TheRange = Sheets("VBA").Range("B5:B11")
Set TheRange_output = Sheets("sheet4").Range("B2")
For i = 1 To TheRange.Cells.Count
Set range_values = Range(TheRange.Cells(i).Offset(0, 1), TheRange.Cells(i).End(xlToRight))
If range_values.Cells.Count < 15000 Then
For j = 1 To range_values.Cells.Count
TheRange_output.Value = TheRange.Cells(i).Value
TheRange_output.Offset(0, 1).Value = range_values.Cells(j).Value
Set TheRange_output = TheRange_output.Offset(1, 0)
Next j
End If
Next i
End Sub➥ 그런 다음 실행(Play) 아이콘을 눌러 코드를 실행합니다.
잠시 후 새 시트에 원하는 목록이 완성됩니다.
함께 읽으면 좋은 글: VBA로 엑셀 표 활용하는 방법
마무리
지금까지 소개한 세 가지 방법—피벗테이블 및 피벗차트 마법사, 파워 쿼리, VBA—을 활용하면 어떤 상황에서도 교차 표를 목록 형태로 손쉽게 변환할 수 있습니다. 각 방법은 장단점이 있으므로, 데이터 크기와 반복 작업 여부에 따라 적절한 방식을 선택하시기 바랍니다. 궁금한 점이 있다면 댓글로 남겨주세요. 피드백도 환영합니다!
추천 자료
- 엑셀에 TABLE 함수가 존재하나요?
- 엑셀에서 피벗테이블 편집하기 (5가지 방법)
- 엑셀에서 피벗테이블 자동 새로 고침 설정하기 (2가지 방법)
- 엑셀 표의 종류: 완벽 정리 가이드