[ 응용 ] 엑셀 여러 줄의 데이터를 한 셀에 합치기(파워쿼리-PowerQuery)

[ 들어가며 ]

 

지난 블로그에서

한 셀에 입력된 여러 개의 데이터를

여러 줄로 나누는 방법을 알아보았습니다.

 

오늘은 그 반대인

여러 줄로 입력된 데이터를 하나의 셀로

취합하는 방법에 대해 알아보겠습니다.

 

최종 모양은 아래 그림과 같이 됩니다.

 

한 셀에 여러 개의 데이터가 입력되어 있는 경우

여러 줄로 나누는 방법은 아래 링크를 참고하시면 됩니다.

※ 엑셀 한 줄로 입력된 데이터 여러 줄로 나누기(파워쿼리-PowerQuery)

반응형

 

[ 표 형식으로 변환하기 ]

 

기본 데이터가 표 형식으로 되어있지 않다면

표 형식으로 만들어 주어야 합니다.

하늘색 네모와 같이 [ 데이터의 범위를 선택 ]한 후

[ 삽입 ] → [ 표 ] → [ 머리글 포함 체크 ] → [ 확인 ]을 눌러

표의 형태로 변환을 해줍니다. 

 

그러면 아래 그림과 같이

표의 형태로 변환이 됩니다.

표의 형태는 사용자마다 다를 수 있습니다.

 

[ 파워쿼리 연결하기 ]

 

그다음 

[ 표의 아무 셀이나 선택 ] 한 후에

[ 데이터 ] → [ 테이블 / 범위에서 ]를 선택하면

아래 그림과 같이 파워쿼리가 실행이 됩니다.

 

 

[ 데이터 그룹화 하기 ]

 

기존 데이터에서 [ 구분 ]에는

하나의 값이 [ 속성 ]에 따라 여러 줄로 분리되어 있습니다.

이것들을 하나로 그룹화를 먼저 해야 합니다.

 

먼저 [ 구분 ]을 선택하신 후

[ 변환 ] → [ 그룹화 ]를 클릭하면

아래 그림의 보라색 네모와 같이 

[ 그룹화 ] 창이 나타납니다.

 

 

이 창이 뜨면

그룹화는 [ 구분 ]을 기준으로 할 것이기 때문에

[ 구분 ]을 선택하고

[ 새 열 이름 ]은 속성으로 하겠습니다.

[ 연산 ]은 현재는 무엇을 해도 오류가 나타나기에 저는 합계를 선택하겠습니다.

[ 열 ]은 연산을 시행할 열을 선택하는 것이므로 속성을 선택했습니다.

 

이렇게 입력하고

[ 확인 ]을 누릅니다.

그러면 아래 그림과 같이 

[ 속성 ] 부분에 에러가 나타납니다.

 

에러가 나타나는 이유는

입력된 데이터는 텍스트인데

합계를 구하라고 했기 때문입니다.

 

텍스트를 합치는데 큰 의미는 없습니다.

 

이제 수식을 변경해 보겠습니다.

  

수식은 아래 그림의 빨간색 네모 부분에 보입니다.

 

만약 수식이 보이지 않는다면

[ 보기 ] → [ 수식 입력줄 ]이 

체크되어 있는지 확인해 보시기 바랍니다.

 

현재 입력된 수식은 다음과 같습니다.

이 부분 중에서 빨간색 부분을 수정할 것입니다.

= Table.Group(#"변경된 유형", {"구분"}, {{"속성", each List.Sum([속성]), type nullable text}})

 

위 빨간색 부분을 아래와 같이 수정합니다.

[ ", " ]를 넣은 이유는

각 문자열을 콤마와 띄어쓰기로 구분하라는 의미입니다.

= Table.Group(#"변경된 유형", {"구분"}, {{"속성", each Text.Combine([속성], ", "), type nullable text}})

 

이렇게 수식을 수정하면

아래 그림과 같이 결과가 나타납니다.

 

이제 이 결과를 엑셀에 옮기기만 하면 됩니다.

[ 홈 ] → [ 닫기 및 로드 ] → [ 닫기 및 다음으로 로드 ]를 눌러줍니다.

 

이제 아래 그림과 같이 [ 데이터 가져오기 ] 창이 나타나는데

저는 [ 기존 워크시트 ]의 [ E1 ] 셀에 데이터를 가지고 오겠습니다.

 

 

[ 결    과 ] 

 

이렇게 하면 아래 그림과 같이

원하는 형태의 데이터가 만들어지게 됩니다.

 

도움이 되셨다면 자주 방문해 주세요

 

여기를 방문하시면 더 많은 엑셀 관련 자료를 확인할 수 있습니다.

엑셀 공부하기
VBA 공부하기