엑셀 텍스트 마이닝, 워드 클라우드 차트 만들기
엑셀 텍스트마이닝 워드클라우드 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀고급] 엑셀 텍스트마이닝 자동화 워드클라우드 차트완성파일[엑셀고급] 엑셀 텍스트마이닝 자동화 워드클라우드 차트예제파일
이번 강의를 마무리하면 이런 식의 워드클라우드 차트를 만들 수 있습니다. 각 단계별 사용된 파워쿼리 공식
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함수(파워쿼리에서 사용되는 함수)로 기록됩니다. (엑셀과 비슷합니다!)
파워쿼리의 모든 작업은 M함수로 작성됩니다. 따라서 강의에서 사용된 이름(표이름, 쿼리이름, 머리글이름 등)이 다르게 작성될 경우 "필드를 찾을 수 없습니다. 쿼리가 존재하지 않습니다" 등의 오류가 발생할 수 있는데요.
머리글, 쿼리이름이 다를 경우 파워쿼리 동작 도중 오류가 발생합니다. 만약 파워쿼리 사용에 익숙치 않으실 경우, 강의에서 사용한 것과 동일한 이름으로 쿼리, 머리글을 작성하는 것을 주의해서 진행해주세요. 파워쿼리에 대한 기초내용은 아래 영상강의를 참고해주세요.
파워쿼리를 통한 텍스트 마이닝은 띄어쓰기를 기준으로 어절를 분리한 다음, 값에서 조사와 접미사를 제거하는 순서로 키워드를 추출합니다.
따라서 형태소분석 및 단어사전 레퍼런스를 사용하는 R이나 파이썬과는 달리, 단어의 뜻을 인식하지 못하기 때문에, R이나 파이썬에 비교하면 정확성이 다소 부족할 수 있습니다.
(사실 R이나 파이썬을 사용해도 상당한 형태소/단어 구분작업이 필요합니다..^^;;;)오빠두Tip : 한국어는 자연어처리가 매우 어려운 언어입니다.^^; 한국어 자연어 처리가 어려운 이유는 이전 '파워쿼리 키워드 분석' 강의에서 안내해드렸으니 궁금하신 분은 이전 강의를 참고해주세요.예를 들어, 제거할 접미사로 "은, 는, 이, 가, 입니다"를 입력했고, 문장으로 "철수는 꿈이 화가 그리고 가수입니다." 가 입력되면, 텍스트 마이닝 결과로 아래와 같이 분석됩니다.
텍스트 마이닝 키워드 분석 "철수는 / 꿈이 / 화가 / 그리고 / 가수입니다." 어절 분석 결과 철수 는철수 꿈 이꿈 화 가화 그리고 그리고 가수 입니다가수 형태소분석과 단어사전 레퍼런스를 사용하면 "화가"라는 단어를 인식하겠지만, 파워쿼리의 경우 단어의 뜻을 인식하지 않으므로 "화가"에서 "가"를 제거한 "화" 라는 값만 결과로 반환됩니다.
파워쿼리 텍스트 마이닝 분석
- 범위를 표로 변환하기 : 예제파일을 실행하면 '키워드분석'과 '필터링' 시트가 있습니다. 우선 각 시트에 입력된 범위를 표로 변환합니다. 키워드 분석에 입력된 범위를 선택한 뒤, [삽입] - [표]를 선택합니다. '머리글포함'을 체크한 뒤 [확인] 버튼을 누르면 범위가 표로 변환됩니다.
키워드 시트에 입력된 범위를 표로 변환합니다. - 표를 선택한 뒤, [테이블 디자인] - [표 이름]에서 표 이름을 'main' 으로 변경합니다.
표 이름을 main 으로 변경합니다. - [필터링] 시트로 이동한 뒤, 나머지 범위도 아래 적어드린 순서에 따라 각각 표로 변환합니다.
범위 표이름 한글자 trimvalue1 두글자 trimvalue2 세글자 trimvalue3 제거글자 removechars 구분기호 splitchars 오빠두Tip : 표 만들기에서 '머리글 포함'을 반드시 체크해주세요. - 기본 쿼리 만들기 : 이제 각 표를 쿼리로 변환해주겠습니다. [키워드분석] 시트로 이동한 뒤, 표를 선택 - [데이터] - [테이블/범위에서] 버튼을 클릭하면 파워쿼리가 실행됩니다. 파워쿼리 편집기에서 [파일] - [닫기 및 다음으로 로드]를 선택한 뒤, [연결만 만들기]를 선택 후 [확인] 버튼을 눌러 쿼리를 연결로 추가합니다.
쿼리를 연결로만 생성합니다. 오빠두Tip : [닫기 및 로드] 버튼을 클릭하면 새로운 시트에 표 형태로 쿼리가 만들어집니다. 그럴 경우 추가된 시트를 우클릭한 뒤, 시트 삭제해주세요. (쿼리를 표 형태로 출력해도 괜찮지만, 쿼리를 연결로 만들면 쿼리가 더욱 빠르게 동작합니다.) - 동일한 방법으로 [필터링] 시트의 다른 표로 모두 쿼리로 변경합니다. 쿼리 및 연결 창을 보면 5개 아래 그림과 같이 총 6개의 쿼리가 만들어집니다.
나머지 쿼리도 모두 연결로 생성합니다. 오빠두Tip : 쿼리 및 연결 탭이 안보일 경우, [데이터] - [쿼리 및 연결]을 클릭합니다.
- 구분기호를 띄어쓰기로 분리하기 : 쿼리 및 연결 탭에서 main 쿼리를 우클릭 - [편집]을 클릭해서 파워쿼리 편집기로 이동합니다.
main 쿼리를 우클릭 - 편집으로 이동합니다. - 파워쿼리 편집기의 [열 추가] - [사용자 지정 열]을 선택한 뒤, 새 열 이름으로 "구분기호변경"을 입력합니다. 이후 아래 공식을 복사한 뒤 사용자 지정 열 수식에 붙여넣기합니다.
List.Accumulate( List.Numbers(0, Table.RowCount(splitchars)-1), [값], (string, row) => Text.Replace(string, splitchars{row}[구분기호]," ") )
공식을 복사한 뒤, 사용자 지정 열 수식에 붙여넣기 합니다. 오빠두Tip : 공식의 동작원리는 영상 강의에서 단계별로 설명해드렸습니다. - 문장 안에 입력된 괄호, 큰따옴표 등의 구분기호가 띄어쓰기로 변경되었습니다.
기존 값의 구분기호가 제거되었습니다. 오빠두Tip : List.Accumulate 함수에 대한 설명은 아래 MS 홈페이지 관련 포스트를 참고하세요.
https://docs.microsoft.com/ko-kr/powerquery-m/list-accumulate
- 불필요한 열 제거하기 : 쿼리에서 [값] 필드를 우클릭 후, "제거"를 클릭하면 값 필드가 제거됩니다.
불필요한 열을 우클릭 - 제거를 선택해서 제거합니다. - 띄어쓰기를 기준으로 열 분할 : '구분기호변경' 필드를 선택한 뒤, [변환] - [열 분할] - [구분기호 기준] 버튼을 클릭합니다. 구분기호로 '공백'을 선택한 뒤, [확인] 버튼을 클릭합니다.
변환 - 열 분할에서 공백 기준으로 열을 분할합니다. - 구분기호 기준으로 단어가 구분되었습니다.
문장이 어절 단위로 분리됩니다. - 열 피벗해제로 행열 전환 : 단축키 Ctrl + A 를 눌러 모든 필드를 선택합니다. 그 상태에서 [변환] - [열 피벗 해제] 버튼을 클릭합니다.
전체 범위 선택 후, 열 피벗해제를 클릭합니다. - 각 키워드가 분리되었습니다.
- [특성] 열을 우클릭 - [제거]를 클릭해서 불필요 한 필드를 제거합니다.
불필요한 열을 제거합니다.
- 특수기호 제거하기 : 이제 어절 안에 입력된 값에서 '한글, 숫자, 영어'를 제외한 다른 특수기호를 모두 제거해주겠습니다. 파워쿼리 편집기에서 [열 추가] - [사용자 지정 열]로 이동합니다. 새 열 이름으로 "특수기호제거"를 입력한 뒤, 아래 공식을 복사해서 사용자 지정 열 수식 안에 붙여넣기 합니다.
Text.Select([값],{"가".."힣","a".."z","A".."Z","0".."9"," "})
공식을 복사한 뒤, 새로운 사용자 지정 열에 추가합니다. - [확인] 버튼을 클릭하면 어절안에 입력된 값에서 특수기호가 모두 제거되고 '한국어, 영어, 숫자'만 결과값으로 추출됩니다.
특수기호가 제거되고 한글, 영어, 숫자만 추출됩니다. - [값] 필드를 우클릭한 뒤, [제거]를 클릭해서 불필요한 필드를 제거합니다.
불필요한 필드를 제거합니다.
- 불필요한 단어 제거하기 : 상황에 따라 '그리고, 그래서, 그런데' 와 같이 문장 중간중간에 사용되는 접속사나 연결어를 제거해야 할 경우, 아래 과정을 진행합니다. 단, List.Accumulate 함수로 많은 데이터를 처리할 경우 처리속도에 영향을 줄 수 있으므로, 처리해야 할 데이터가 많을 경우 이번 과정은 생략 후 진행합니다.
- [열 추가] - [사용자 지정 열]을 선택합니다. 새 열 이름으로 "단어제거"를 입력한 뒤, 아래 공식을 복사해서 사용자 지정 열 수식 안에 붙여넣기 합니다.
List.Accumulate( List.Numbers(0, Table.RowCount(removechars)-1), [특수기호제거], (string, row) => Text.Replace(string, removechars{row}[제거글자],"") )
공식을 복사한 뒤, 새로운 사용자 지저 열 수식으로 붙여넣기합니다. - [확인] 버튼을 클릭하면 어절에 사용된 연결어, 접속사가 제거됩니다.
접속사, 연결어가 제거되었습니다.
- 필드 머리글 변경하기 : 이제 텍스트 마이닝의 마지막 단계로 어절에 사용된 접미사/조사(을, 를, 에서..)를 제거합니다. 쿼리의 머리글을 더블클릭 한 뒤, 머리글을 '값'으로 변경합니다.
필드의 머리글을 '값'으로 변경합니다. 오빠두Tip : 또는 필드를 선택한 상태에서, 키보드 F2키를 눌러 머리글을 변경할 수도 있습니다. - 쿼리를 목록으로 변경하기 : 접미사를 제거할 때 사용할 List.Contains 함수는 첫번째 인수로 목록을 사용합니다. 따라서 제거할 접미사가 작성된 쿼리를 목록형태 변환합니다. trimvalue1 쿼리로 이동한 뒤, 머리글을 우클릭 - [드릴다운] 을 클릭하면 쿼리가 목록으로 변환됩니다.
제거할 접미사 쿼리를 목록형태로 드릴다운합니다. 오빠두Tip : 반드시 머리글을 우클릭해서 드릴다운 해야 합니다. 값을 우클릭 한 뒤 드릴다운하면, 우클릭 된 대상 하나만 목록형태로 변환됩니다. - 나머지 trimvalue2, trimvalue3도 모두 목록형태로 변환합니다.
나머지 쿼리도 모두 목록형태로 변환합니다. - 접미사 제거하기 : 다시 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)
새로운 사용자 지정 열을 추가합니다. - 어절에 사용된 "을, 를..." 등의 접미사가 제거되었습니다.
- 불필요한 필드를 우클릭 - [제거] 버튼을 클릭하면 텍스트 마이닝 작업이 완료됩니다.
불필요한 열을 제거합니다.
- 한글자 단어 제거하기 : 만약 분석된 결과 중 한글자 단어가 많이 포함되어어 있어 제거해야 할 경우, 아래 순서대로 진행합니다. main 쿼리로 이동 후, [열 추가] - [사용자 지정 열]을 선택합니다.
열 추가 - 사용자 지정 열을 클릭합니다. - 새 열 이름으로 "글자수"를 입력합니다. 이후 아래 공식을 복사한 뒤, 사용자 지정 열 수식에 붙여넣기 후 [확인] 버튼을 클릭합니다.
Text.Length([접미사제거])
글자수를 계산하는 새로운 열을 추가합니다. - 각 단어의 글자수가 계산되었습니다. 글자수의 필터버튼을 클릭 - [숫자 필터] - [보다 큼]으로 이동합니다.
숫자 필터로 이동합니다. - 1보다 크도록 조건을 입력 후, [확인] 버튼을 클릭하면 글자수가 1개 이상인 값만 필터링됩니다.
글자수가 1보다 큰 값들만 필터링합니다. - [파일] - [닫기 및 로드]를 눌러 쿼리 편집을 종료합니다.
닫기 및 로드로 쿼리 편집을 종료합니다.
- 피벗테이블 만들기 : 쿼리 및 연결에서 'main' 쿼리를 우클릭한 뒤, [다음으로 로드]를 선택합니다.
main 쿼리를 우클릭 - 다음으로 로드를 선택합니다. 오빠두Tip : 만약 쿼리 및 연결이 안 보일 경우, [데이터] 탭 - [쿼리 및 연결] 버튼을 클릭합니다. - 데이터 가져오기 대화상자가 실행되면, 표 형태로 기존 워크시트 비어있는 곳에 표를 출력합니다.
표 형태로 기존 워크시트에 출력합니다. - 표를 선택한 뒤, [삽입] - [피벗테이블]을 클릭합니다. 피벗테이블 만들기 대화상자가 나오면, 기존 워크시트의 비어있는 곳에 새로운 피벗테이블을 생성합니다.
피벗테이블을 추가합니다. - 피벗테이블로 키워드 사용빈도 분석 : 피벗테이블 필드 목록에서 '접미사제거' 필드를 각 행 영역와 값 영역으로 이동하면 각 키워드별로 사용된 개수가 분석됩니다.
접미사가 제거된 단어 필드를 행과 값 영역에 추가합니다. - 행 레이블의 필터버튼을 클릭 - [기타 정렬 옵션]으로 이동한 뒤, "접미사 제거 개수" 기준으로 내림차순 정렬하면 많이 사용된 키워드가 피벗테이블의 위쪽으로 정렬됩니다.
단어 개수 기준으로 내림차순 정렬하면 텍스트 마이닝 분석이 마무리됩니다.
워드 클라우드 차트를 만들기 위한 어려운 작업은 모두 끝났습니다. 이제 마지막 단계로 파워BI나 태블로, 또는 무료 워드클라우드 홈페이지를 방문해서 워드클라우드 차트를 제작하면 되는데요.
무료 워드클라우드 홈페이지를 사용하면 누구나 쉽게 워드클라우드 차트를 만들 수 있습니다. 워드클라우드 차트를 만드는 방법은 아주 간단합니다. 영상 강의 마지막에 안내해드린 내용을 참고해주세요.
파워BI의 워드클라우드는 파워BI Pro 구독자(월11,000원) 또는 M365 E5 이상을 구독중인 사용자에게만 제공됩니다. 파워BI 가격정책에 대한 자세한 설명은 아래 관련 링크를 참고해주세요.
파워BI와 엑셀을 연동하면 실시간으로 키워드를 분석하는 워드클라우드 차트를 만들 수 있습니다.
