엑셀 워드 클라우드 차트, 텍스트 마이닝 자동화 기법

실습 가이드

각 단계별 사용된 파워쿼리 공식

1. 구분기호를 띄어쓰기로 변경하기

List.Accumulate(
    List.Numbers(0, Table.RowCount(splitchars)-1),
    [값],
    (string, row) =>
    Text.Replace(string, splitchars{row}[구분기호]," ")
)

2. 특수기호 제거하기 (한글·영어·숫자만 추출)

Text.Select([값],{"가".."힣","a".."z","A".."Z","0".."9"," "})

3. 불필요한 단어 제거하기 (접속사·연결어 제거)

List.Accumulate(
    List.Numbers(0, Table.RowCount(removechars)-1),
    [특수기호제거],
    (string, row) =>
    Text.Replace(string, removechars{row}[제거글자],"")
)

4. 접미사 제거하기

if List.Contains(trimvalue3,Text.End([값],3)) then
Text.Start([값],Text.Length([값])-3) else if
List.Contains(trimvalue2,Text.End([값],2)) then
Text.Start([값],Text.Length([값])-2) else
Text.TrimEnd([값],trimvalue1)

5. 글자수 세기

Text.Length([접미사제거])

파워쿼리의 모든 작업 단계는 M함수(파워쿼리 전용 함수)로 자동 기록됩니다. 엑셀의 매크로와 비슷한 구조이므로, 작업 순서를 그대로 재실행하거나 다른 데이터에 적용할 수 있습니다.

따라서 강의에서 사용된 이름(표 이름, 쿼리 이름, 머리글 이름 등)과 다르게 작성하면 "필드를 찾을 수 없습니다", "쿼리가 존재하지 않습니다" 와 같은 오류가 발생할 수 있습니다.

파워쿼리 사용이 익숙하지 않으신 분은 강의에서 사용한 이름과 동일하게 쿼리·머리글을 작성하시면 오류 없이 안정적으로 진행할 수 있습니다. 파워쿼리의 기초 내용은 아래 영상강의를 참고해주세요.

파워쿼리를 활용한 텍스트 마이닝은 띄어쓰기를 기준으로 어절을 분리한 뒤, 각 어절에서 조사와 접미사를 제거하는 순서로 키워드를 추출합니다.

형태소 분석과 단어사전 레퍼런스를 활용하는 R이나 파이썬과 달리, 파워쿼리는 단어의 의미를 직접 인식하지 못하기 때문에 R이나 파이썬과 비교하면 분석 정확도가 다소 떨어질 수 있습니다. (사실 R이나 파이썬을 사용해도 상당한 형태소/단어 구분작업이 필요합니다..^^;;;)

예를 들어 제거할 접미사로 "은, 는, 이, 가, 입니다" 를 입력하고, 분석 대상 문장으로 "철수는 꿈이 화가 그리고 가수입니다." 를 입력하면, 텍스트 마이닝 결과는 아래와 같이 분석됩니다.

형태소 분석과 단어사전 레퍼런스를 사용하면 "화가" 라는 단어를 하나의 의미 단위로 인식하지만, 파워쿼리는 단어의 뜻을 판단하지 않으므로 "화가" 에서 접미사 "가" 가 제거된 "화" 만 결과로 반환되는 한계가 있습니다.

파워쿼리 텍스트 마이닝 분석

1. 범위를 표로 변환하기 : 예제파일을 실행하면 '키워드분석' 시트와 '필터링' 시트가 함께 제공됩니다. 우선 각 시트에 입력된 범위를 표 형식으로 변환합니다. 키워드 분석 시트의 범위를 선택한 뒤 [삽입] - [표] 버튼을 클릭합니다. '머리글 포함' 옵션을 체크한 뒤 [확인] 버튼을 누르면 범위가 표로 변환됩니다.

   

2. 변환된 표를 선택한 뒤 [테이블 디자인] - [표 이름] 입력란에서 표 이름을 'main' 으로 변경합니다.

   

3. [필터링] 시트로 이동한 뒤, 나머지 범위도 아래 순서에 따라 각각 표로 변환합니다.
   

   범위	표이름
   한글자	trimvalue1
   두글자	trimvalue2
   세글자	trimvalue3
   제거글자	removechars
   구분기호	splitchars

   오빠두Tip : 표 만들기 단계에서 '머리글 포함' 옵션을 반드시 체크해주세요.
4. 기본 쿼리 만들기 : 이제 각 표를 쿼리로 변환하겠습니다. [키워드분석] 시트로 이동한 뒤 표를 선택한 상태에서 [데이터] - [테이블/범위에서] 버튼을 클릭하면 파워쿼리 편집기가 실행됩니다. 파워쿼리 편집기에서 [파일] - [닫기 및 다음으로 로드]를 선택한 뒤, [연결만 만들기]를 선택하고 [확인] 버튼을 눌러 쿼리를 연결로 추가합니다.

   

   오빠두Tip : [닫기 및 로드] 버튼을 그대로 클릭하면 새 시트에 표 형태로 쿼리가 출력됩니다. 이 경우 추가된 시트를 우클릭한 뒤 시트를 삭제해주세요. 쿼리를 표 형태로 출력해도 동작에는 문제가 없지만, 연결 형식으로 만들면 쿼리 처리 속도가 더 빠릅니다.
5. 같은 방법으로 [필터링] 시트의 다른 표도 모두 쿼리로 변환합니다. 쿼리 및 연결 창을 확인하면 아래 그림과 같이 총 6개의 쿼리가 만들어진 것을 볼 수 있습니다.

   

   오빠두Tip : 쿼리 및 연결 탭이 보이지 않을 경우 [데이터] - [쿼리 및 연결] 버튼을 클릭합니다.

1. 구분기호를 띄어쓰기로 분리하기 : 쿼리 및 연결 탭에서 main 쿼리를 우클릭한 뒤 [편집] 을 클릭하여 파워쿼리 편집기로 이동합니다.

   

2. 파워쿼리 편집기의 [열 추가] - [사용자 지정 열]을 선택한 뒤, 새 열 이름으로 "구분기호변경" 을 입력합니다. 이어서 아래 공식을 복사하여 사용자 지정 열 수식 입력란에 붙여넣기 합니다.

   List.Accumulate(
       List.Numbers(0, Table.RowCount(splitchars)-1),
       [값],
       (string, row) =>
       Text.Replace(string, splitchars{row}[구분기호]," ")
   )

   

   오빠두Tip : 공식의 동작 원리는 영상 강의에서 단계별로 자세히 설명해드렸으니 함께 참고해주세요.
3. 문장 안에 입력된 괄호, 큰따옴표 등의 구분기호가 모두 띄어쓰기로 변경되었습니다.

   

   오빠두Tip : List.Accumulate 함수에 대한 자세한 설명은 아래 마이크로소프트 공식 문서를 참고하세요.
   https://docs.microsoft.com/ko-kr/powerquery-m/list-accumulate

1. 불필요한 열 제거하기 : 쿼리에서 [값] 필드를 우클릭한 뒤, "제거" 를 클릭하면 값 필드가 삭제됩니다.

   

2. 띄어쓰기를 기준으로 열 분할 : '구분기호변경' 필드를 선택한 뒤, [변환] - [열 분할] - [구분기호 기준] 버튼을 클릭합니다. 구분기호로 '공백' 을 선택한 뒤 [확인] 버튼을 클릭합니다.

   

3. 구분기호를 기준으로 단어가 각각의 열로 분리되었습니다.

   

4. 열 피벗해제로 행열 전환 : 단축키 Ctrl + A 를 눌러 모든 필드를 선택합니다. 그 상태에서 [변환] - [열 피벗 해제] 버튼을 클릭합니다.

   

5. 각 키워드가 행 단위로 분리되었습니다.
6. [특성] 열을 우클릭한 뒤 [제거] 를 클릭하여 불필요한 필드를 제거합니다.

   

1. 특수기호 제거하기 : 이제 어절 안에 입력된 값에서 '한글, 숫자, 영어' 를 제외한 다른 특수기호를 모두 제거하겠습니다. 파워쿼리 편집기에서 [열 추가] - [사용자 지정 열]을 클릭합니다. 새 열 이름으로 "특수기호제거" 를 입력한 뒤, 아래 공식을 복사해서 사용자 지정 열 수식 입력란에 붙여넣기 합니다.

   Text.Select([값],{"가".."힣","a".."z","A".."Z","0".."9"," "})

   

2. [확인] 버튼을 클릭하면 어절 안의 값에서 특수기호가 모두 제거되고 '한국어, 영어, 숫자' 만 결과로 추출됩니다.

   

3. [값] 필드를 우클릭한 뒤 [제거] 를 클릭하여 불필요한 필드를 제거합니다.

   

1. 불필요한 단어 제거하기 : 상황에 따라 '그리고, 그래서, 그런데' 와 같이 문장 중간에 사용되는 접속사나 연결어를 제거해야 할 경우, 아래 단계를 진행합니다. 단, List.Accumulate 함수는 데이터 양이 많아질수록 처리 속도에 영향을 줄 수 있으므로, 처리할 데이터가 많을 경우에는 이 단계를 생략하고 진행합니다.
2. [열 추가] - [사용자 지정 열]을 선택합니다. 새 열 이름으로 "단어제거" 를 입력한 뒤, 아래 공식을 복사하여 사용자 지정 열 수식 입력란에 붙여넣기 합니다.

   List.Accumulate(
       List.Numbers(0, Table.RowCount(removechars)-1),
       [특수기호제거],
       (string, row) =>
       Text.Replace(string, removechars{row}[제거글자],"")
   )

   

3. [확인] 버튼을 클릭하면 어절에 사용된 연결어와 접속사가 제거됩니다.

   

1. 필드 머리글 변경하기 : 이제 텍스트 마이닝의 마지막 단계로 어절에 사용된 접미사·조사(을, 를, 에서 등)를 제거합니다. 쿼리 머리글을 더블클릭한 뒤, 머리글을 '값' 으로 변경합니다.

   

   오빠두Tip : 필드를 선택한 상태에서 키보드 F2 키를 눌러 머리글을 변경할 수도 있습니다.
2. 쿼리를 목록으로 변경하기 : 접미사 제거에 사용할 List.Contains 함수는 첫 번째 인수로 목록(List) 형식을 사용합니다. 따라서 제거할 접미사가 정리된 쿼리를 목록 형태로 변환해야 합니다. trimvalue1 쿼리로 이동한 뒤, 머리글을 우클릭하고 [드릴다운] 을 클릭하면 쿼리가 목록으로 변환됩니다.

   

   오빠두Tip : 반드시 머리글을 우클릭해서 드릴다운 해야 합니다. 값을 우클릭한 뒤 드릴다운하면 우클릭한 항목 한 건만 목록 형태로 변환됩니다.
3. 같은 방법으로 trimvalue2, trimvalue3 도 모두 목록 형태로 변환합니다.

   

4. 접미사 제거하기 : 다시 main 쿼리로 이동한 뒤, [열 추가] - [사용자 지정 열]을 선택합니다. 새 열 이름으로 "접미사제거" 를 입력하고, 아래 공식을 복사하여 사용자 지정 열 수식 입력란에 붙여넣은 뒤 [확인] 버튼을 클릭합니다.

   if List.Contains(trimvalue3,Text.End([값],3)) then
   Text.Start([값],Text.Length([값])-3) else if
   List.Contains(trimvalue2,Text.End([값],2)) then
   Text.Start([값],Text.Length([값])-2) else
   Text.TrimEnd([값],trimvalue1)

   

5. 어절에 사용된 "을, 를…" 등의 접미사가 제거되었습니다.
6. 불필요한 필드를 우클릭한 뒤 [제거] 버튼을 클릭하면 텍스트 마이닝 작업이 완료됩니다.

   

1. 한글자 단어 제거하기 : 분석 결과에 한 글자 단어가 많이 포함되어 있어 제외해야 할 경우, 아래 순서대로 진행합니다. main 쿼리로 이동한 뒤 [열 추가] - [사용자 지정 열]을 선택합니다.

   

2. 새 열 이름으로 "글자수" 를 입력합니다. 이어서 아래 공식을 복사하여 사용자 지정 열 수식 입력란에 붙여넣은 뒤 [확인] 버튼을 클릭합니다.

   Text.Length([접미사제거])

   

3. 각 단어의 글자수가 계산되었습니다. 글자수 필드의 필터 버튼을 클릭한 뒤 [숫자 필터] - [보다 큼] 으로 이동합니다.

   

4. 1보다 크도록 조건을 입력하고 [확인] 버튼을 클릭하면 글자수가 2 이상인 값만 필터링됩니다.

   

5. [파일] - [닫기 및 로드] 를 클릭하여 쿼리 편집을 종료합니다.

   

1. 피벗테이블 만들기 : 쿼리 및 연결 창에서 'main' 쿼리를 우클릭한 뒤 [다음으로 로드] 를 선택합니다.

   

   오빠두Tip : 쿼리 및 연결 탭이 보이지 않을 경우, [데이터] 탭 - [쿼리 및 연결] 버튼을 클릭합니다.
2. 데이터 가져오기 대화상자가 실행되면, 표 형식으로 기존 워크시트의 빈 영역에 표를 출력합니다.

   

3. 표를 선택한 뒤 [삽입] - [피벗테이블] 을 클릭합니다. 피벗테이블 만들기 대화상자가 나오면 기존 워크시트의 빈 영역에 새 피벗테이블을 생성합니다.

   

4. 피벗테이블로 키워드 사용빈도 분석 : 피벗테이블 필드 목록에서 '접미사제거' 필드를 행 영역과 값 영역으로 각각 이동하면 키워드별 사용 빈도수가 분석됩니다.

   

5. 행 레이블 필터 버튼을 클릭한 뒤 [기타 정렬 옵션] 으로 이동합니다. "접미사제거 개수" 를 기준으로 내림차순 정렬하면 사용 빈도가 높은 키워드가 피벗테이블 위쪽으로 정렬됩니다.

   

이제 워드 클라우드 차트를 만들기 위한 사전 준비가 모두 끝났습니다. 마지막 단계로 파워BI나 태블로, 또는 무료 워드클라우드 홈페이지를 활용하여 워드클라우드 차트를 제작합니다.

워드클라우드 차트 제작 방법은 매우 간단합니다. 영상 강의 후반부에서 단계별로 안내해드리니 함께 참고해주세요.

파워BI의 워드클라우드 시각화는 파워BI Pro 구독자(월 11,000원) 또는 M365 E5 이상 사용자에게만 제공됩니다. 파워BI 가격 정책에 대한 자세한 내용은 아래 공식 페이지를 참고해주세요.