엑셀 파워 쿼리를 사용하여 셀 데이터 다루기 I

1. 텍스트 나누기가 아닌 파워 쿼리로 데이터 분리하기

 이전에 한 셀에 여러 데이터가 함께 입력되어 있는 경우 텍스트 나누기 기능을 사용하여 데이터들을 분리하는 방법을 알아봤었습니다. 이번에는 파워 쿼리를 이용하여 데이터를 분리 입력하도록 하겠습니다.

 

 

 

 

[그림1] 연락처

 [그림 1]과 같이 사내 연락처를 입력한 시트가 있습니다. 이름, 직급, 전화번호가 B열에 모두 작성되어 있는 형태입니다. 이 데이터를 분리되어 입력되도록 파워 쿼리를 이용하겠습니다.

 

 B3:B14 셀 중에서 임의의 셀을 클릭한 상태에서 데이터 메뉴의 가져오기 및 변환 그룹에서 테이블에서를 클릭합니다.

[그림2] 표 만들기 메세지 창

[그림2]와 같은 메시지 창이 출력됩니다. 현재 연락처에는 머리글이 없으므로 머리글 포함에는 V 체크를 하지 않고 데이터 범위가 맞는지 확인 후 확인 버튼을 클릭합니다.

[그림3] 파워 쿼리 편집기 창

[그림 3]과 같이 파워 쿼리 편집기 창이 출력됩니다. 

입력된 데이터를 보시면 직급과 전화번호 사이에 '/'로 구분되어 있는 경우도 있고 ', '로 구분되어 있는 경우도 있습니다. 우선 이 구분을 '/'로 통일시키겠습니다. 

[그림4] 파워 쿼리 편집기의 홈 메뉴

[그림 4]에서 보시는 것은 파워 쿼리 편집기의 홈 메뉴입니다. 이제 홈 메뉴의 변환 그룹에서 값 바꾸기를 클릭합니다.

[그림5] 값 바꾸기 메세지 창

그러면 [그림5]와 같이 값 바꾸기 메시지 창이 출력됩니다. 이제 하고자 하는 바는 콤마(,)를 역슬래시(/)로 바꾸는 것이므로 찾을 값에는 ','를 바꿀 항목에는 '/'를 입력하고 확인 버튼을 클릭합니다.

 

[그림6] 값 바꾸기 결과

[그림6]을 보시면 콤마(,)로 구분되었던 항목이 모두 역슬래시(/)로 변경된 것을 확인하실 수 있습니다.

이제 데이터를 분할하는 것을 알아보겠습니다.

파워 쿼리 편집기 메뉴 중 홈 메뉴의 변환 그룹에서 열병합을 클릭합니다.

[그림7] 변환 그룹의 열변환 메뉴

[그림7]과 같이 메뉴가 뜨는데 구분 기호 기준을 선택합니다.

[그림8] 열 분할 메세지 창

 

 

 

 

[그림8]과 같은 열 분할 메시지 창이 뜹니다. 출력된 메시지 창에서 구분 기호 선택 또는 입력에서 위쪽 선택란은 --사용자 지정--을 선택하고 아래쪽 선택란에는 '/'를 입력합니다. 그리고, 다음 위치에 분할은 각 구분 기호에서를 선택합니다. 그리고 확인 버튼을 클릭합니다.

[그림9] 열 분할 결과

역슬래시(/)를 기준으로 좌우 데이터가 분리되어 이름과 직책이 열 1.1에 전화번호가 열 1.2에 입력되었습니다. 이제 이름과 직책도 같은 방식으로 분리해줍니다. 이번에 구분 기호는 공백입니다.

열 1.1이 선택된 상태에서 파워 쿼리 편집기 메뉴 중 홈 메뉴의 변환 그룹에서 열병합을 클릭합니다. 그리고, 구분 기호 기준을 선택합니다. 

[그림10] 열 분할 메세지 창에서 구분 기호로 공백을 선택

[그림10]과 같이 출력된 열 분할 메시지 창에서 구분 기호 선택 또는 입력란에 공백을 선택합니다. 그리고 확인 버튼을 클릭합니다.

[그림11] 열 분할 최종 결과

[그림11]과 같이 이름과 직책과 전화번호가 각 열에 분리되어 입력되었습니다. 이제 홈 메뉴의 닫기 그룹의 닫기 및 로드을 클릭하고 출력된 메뉴에서 닫기 및 다음으로 로드... 를 선택합니다.

 

 

 

 

그럼 파워 쿼리 편집기 창은 닫히면서 [그림 12]와 같은 다음으로 로드 메시지 창이 출력됩니다.

 

[그림12] 다음으로 로드 메세지 창

다음으로 로드 메세지 창에서 테이블을 선택하고 기존 워크시트를 선택한 다음 기존 워크시트 위치 입력란에 D3를 입력합니다. (옆의 아이콘을 마우스 클릭하여 D3 셀을 클릭하시면 됩니다.) 그리고, 로드 버튼을 클릭합니다.

 

[그림13] 파워 쿼리를 이용하여 열 분할한 결과

[그림13]과 같이 D3:F15 셀에 처음의 B열 데이터가 분리되어 표시되었습니다. 이제 열 1.1.1은 이름으로, 열 1.1.2는 직책으로, 열 1.2는 전화번호로 키보드 입력으로 바꾸면 표가 완성됩니다. ([그림 14] 참조)

 

[그림14] 머리글과 가운데 정렬한 결과

이와 같이 텍스트 나누기가 아닌 파워 쿼리를 이용하여 데이터를 분리하여 저장할 수 있습니다. 파워 쿼리는 이외에도 여러 기능들을 사용할 수 있으므로 많이 알아두시면 엑셀을 사용하시는데 도움이 되실 것입니다. 

 

 

 

이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.