엑셀 파워쿼리를 활용한 키워드 자동 분석 | 설문조사 예제

파워쿼리 유사일치(Fuzzy Match)와 피벗테이블을 활용한, 문장 내 유사 키워드 자동 취합 및 분석 예제 (설문조사 취합 실습)

홈페이지 » 엑셀 파워쿼리를 활용한 키워드 자동 분석 | 설문조사 예제

엑셀 파워쿼리 키워드 자동 분석 | 텍스트 마이닝 - 설문조사 분석

파워쿼리 키워드 분석 목차 바로가기
영상 강의
큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [엑셀고급] 파워쿼리 키워드분석, 텍스트마이닝 실습
    예제파일
  • [엑셀고급] 파워쿼리 키워드분석, 텍스트마이닝 실습
    완성파일

.

이전 강의와 이어지는 내용이 있습니다

파워쿼리와 유사일치 기능에 대한 기초 설명은 아래 영상강의를 확인해주세요!


파워쿼리 유사일치로 키워드 분석하기

파워쿼리 유사일치(Fuzzy Match) 기능은 유사도(임계값) 설정에 따라 범위 내 유사한 단어를 검색하는 기능입니다. 현재 M365 버전에서만 제공되고, 엑셀 이전 버전 사용자는 파워쿼리가 있더라도 유사일치 기능을 사용할 수 없습니다.

유사일치를 사용하면 아래와 같이, "삼송전자", "삼싱전자"로 잘못입력된 데이터를 "삼성전자"로 올바르게 매칭할 수 있습니다.

검색할 단어 유사일치
전자 삼성전자
전자 삼성전자
전자 삼성전자
... ...

뿐만 아니라 이번 강의에 소개해드린 것 처럼 문장 내 유사 키워드를 자동으로 인식해서 취합할 수도 있는데요. 이번 강의에서는 키워드 분석 기능만 소개해드렸지만, 문장 분리나 변환테이블 기능을 같이 활용하면 더욱 강력한 키워드 분석 툴을 제작할 수 있습니다.

파워쿼리 유사일치 기능에 대한 기초 내용은 아래 영상강의에서 자세히 설명해 드렸습니다.

  1. 쿼리 만들기 : 파워쿼리로 쿼리를 만들려면, 해당 범위가 반드시 표로 변경되어야 합니다. 예제파일을 실행하면 "표_설문결과"와 "표_키워드" 라는 이름으로 표를 미리 만들어드렸습니다. 표 기능에 대한 자세한 설명은 아래 '표 기능의 모든 것' 기초 입문강의를 참고하세요.

    엑셀 키워드 분석 표 만들기
    예제파일에는 표를 미리 만들어드렸습니다.

  2. 표를 선택한 뒤, [데이터] - [테이블/범위에서]를 클릭하면 파워쿼리가 실행되며 쿼리가 만들어집니다.
    엑셀 표 쿼리 변환
    데이터 - 테이블/범위에서를 클릭해서 파워쿼리를 실행합니다.
    오빠두Tip : M365 베타테스트 버전을 사용중일 경우, '테이블/범위에서' 라는 이름이 '시트에서'로 표시됩니다.
  3. 파워쿼리로 쿼리가 실행되었으면, 좌측 상단의 [파일] - [닫기 및 다음으로 로드]를 클릭해서 쿼리를 저장합니다.

    엑셀 쿼리 닫기 및 다음으로 로드
    파일 - 닫기 및 다음으로 로드를 클릭합니다.

  4. [데이터 가져오기] 창이 나오면, '연결만 만들기'를 체크 한 뒤, [확인] 버튼을 누릅니다.

    연결만 만들기 클릭
    연결만 만들기를 선택해서 쿼리를 생성합니다.

  5. 우측에 [쿼리 및 연결] 창이 표시되면서, 쿼리가 연결로 만들어진 것을 확인할 수 있습니다. 나머지 표도 동일한 방법으로 쿼리를 생성합니다.
    쿼리 완료
    2개의 쿼리가 생성되었습니다.
    오빠두Tip : 만약 화면 오른쪽에 '쿼리 및 연결' 창이 안 보일 경우, [데이터] - [쿼리 및 연결] 버튼을 클릭합니다.
  6. 유사일치 병합하기 : 이제 만든 쿼리를 유사일치로 병합하겠습니다. [데이터] - [데이터 가져오기] - [쿼리 결합] - [병합] 을 선택합니다.

    엑셀 쿼리 결합 유사일치 병합
    데이터 - 데이터 가져오기 - 쿼리결합 - 병합을 클릭합니다.

  7. 병합 대화상자가 나오면, 위쪽 테이블은 '표_설문결과(메인 테이블)', 아래쪽 테이블은 '표_키워드(키워드 테이블)'을 선택합니다. 이후 테이블에서 키워드를 비교할 대상 필드를 선택합니다.

    엑셀 쿼리 병합 테이블 선택
    각 테이블을 선택한 뒤, 키워드를 비교할 필드를 지정합니다.

  8. '조인종류'는 왼쪽 외부를 선택합니다. 이후 조인 종류 아래에 있는 '유사 일치를 사용하여 병합 비교' 옵션을 체크한 다음, 아래에 있는 '유사일치 옵션'을 클릭합니다.

    엑셀 쿼리 유사일치 병합 설정
    유사일치를 사용하여 병합비교를 선택합니다.

  9. 유사성 임계값을 입력합니다. 0.3을 입력하면 테이블의 70개 값 중 67개 값이 일치한다고 표시됩니다. [확인] 버튼을 클릭합니다.
    오빠두Tip : 임계값은 0~1 사이의 숫자로 입력하며, 1에 가까울수록 더욱 정확하게 검색합니다. 실무에서는 보통 0.3~0.8 사이의 값으로 사용하면 됩니다.
  10. 유사일치 비교 쿼리 만들기 : 확인 버튼을 클릭하면 파워쿼리가 실행됩니다. 쿼리 오른쪽 [표 키워드]의 확장버튼을 클릭한 뒤, '키워드'를 선택 후 [확인] 버튼을 클릭하면 유사일치로 검색된 키워드가 표시됩니다.

    엑셀 쿼리 병합 키워드 확장
    키워드의 확장버튼 클릭 후, 키워드 필드를 확장합니다.

  11. [파일] - [닫기 및 다음으로 로드]를 클릭한 다음, '표' 형태로 병합된 쿼리를 기존 워크시트에 출력합니다.

    엑셀 쿼리 시트 출력
    병합된 쿼리를 기존 워크시트에 추가합니다.

  12. 키워드 분석이 완료되었습니다.

    엑셀 파워쿼리 키워드 분석 완료
    키워드 분석 쿼리가 완성되었습니다.

피벗테이블로 분석결과 취합하기

이제 분석된 표를 바탕으로 피벗테이블을 만들어서 각 부서별 희망 강의 주제가 어떻게 되는지 데이터를 분석해보겠습니다.

  1. 피벗테이블 만들기 : 표의 아무 셀을 선택한 뒤, [삽입] - [피벗테이블]을 선택합니다. 이후 피벗테이블을 기존 워크시트의 오른쪽 영역에 추가합니다.

    엑셀 키워드 분석 피벗테이블 만들기
    피벗테이블을 생성합니다.

  2. 이후 아래 표와 같이 피벗테이블 필드를 이동하면 각 부서별 희망 강의 주제에 대한 분석이 완료됩니다.

    키워드 분석 피벗테이블 완성
    각 필드를 이동하면 피벗테이블이 완성됩니다.

  3. 피벗테이블 정렬하기 : 데이터를 더욱 보기좋게 시각화 하기 위하여 선호도가 높은 주제순으로 정렬하겠습니다. 피벗테이블의 행 레이블 필터를 클릭 후, '기타 정렬 옵션'으로 이동합니다.

    피벗테이블 정렬
    행 레이블의 정렬 옵션으로 이동합니다.

  4. 이후 정렬 옵션에서 '내림차순 기준'을 선택한 뒤, '키워드 개수'를 선택합니다. 이후 [확인] 버튼을 클릭하면 선호도가 높은 키워드 순으로 피벗테이블이 정렬됩니다.

    엑셀 피벗테이블 내림차순 정렬
    내림차순 기준으로 피벗테이블을 정렬합니다.

조건부서식으로 데이터 시각화하기

조건부서식의 색조 기능를 사용하면 피벗테이블 보고서를 손쉽게 시각화 할 수 있습니다.

  1. 피벗테이블 시각화하기 : 총 합계 범위를 선택한 뒤, [홈] - [조건부서식] - [색조] 에서 빨간색 배경의 색조를 선택합니다

    엑셀 피벗테이블 색조 시각화
    피벗테이블의 총합계 범위 선택 후, 조건부서식 - 색조를 적용합니다.

  2. 이후 값 영역이 입력된 범위를 선택 한 다음, [홈] - [조건부서식] - [색조] 에서 초록색 배경의 색조를 선택합니다.

    엑셀 피벗테이블 시각화 조건부서식
    값 범위를 선택한 뒤, 초록색 색조를 적용합니다.

  3. 피벗테이블 보고서가 더욱 깔끔하게 정리되었습니다.
  4. 조건부서식에 대한 보다 다양한 예제는 아래 '조건부서식의 모든 것' 기초 입문 강의를 참고해주세요.

키워드별 강의 만족도 조사하기

피벗테이블의 필드를 조금만 변경하면 각 키워드별 강의 만족도도 손쉽게 분석할 수 있습니다.

  1. 피벗테이블 필드 변경하기 : 기존에 만들었던 피벗테이블을 복사한 뒤, 아래에 붙여넣기 합니다. 이후 피벗테이블의 각 필드를 아래 표와 같이 이동하면 키워드별 강의 만족도가 조사됩니다.

    엑셀 키워드 분석 피벗테이블 완성
    피벗테이블을 복사/붙여넣기 후, 키워드 별 만족도를 분석하는 피벗테이블을 생성합니다.

  2. 값 요약방식 변경 : 피벗테이블의 기본 값 요약방식은 '합계' 입니다. 하지만 우리가 보고자 하는 강의 만족도는 '평균'으로 집계되어야 합니다. 따라서 각 필드를 우클릭 한 뒤, 값 요약기준을 '평균'으로 변경합니다.

    피벗테이블 값 요약기준 변경
    각 값필드를 우클릭 한 뒤, 요약기준을 평균으로 변경합니다.

  3. 숫자 표시형식 변경 : 값 요약기준을 평균으로 변경하면 숫자에 소수점이 표시되면서 데이터를 한눈에 파악하기 어려워집니다. 따라서 숫자의 표시형식을 변경합니다. 피벗테이블 범위를 선택한 뒤, [셀 서식]으로 이동합니다.

    피벗테이블 평균 숫자 표시형식
    값 범위 선택 후, 우클릭 - 셀 서식으로 이동합니다.

  4. 이후 사용자 지정에서 표시형식을 "0.0" 으로 변경한 뒤, [확인] 버튼을 클릭하면 데이터가 보기 좋게 변경됩니다.

    숫자 소수점 표시 엑셀
    숫자 표시형식을 0.0 으로 변경합니다.

  5. 피벗테이블 시각화하기 : 범위가 선택된 상태에서 [홈] - [조건부서식] - [색조]를 추가하면 키워드별 강의 만족도가 분석된 피벗테이블 보고서가 완성됩니다.

    파워쿼리 키워드 분석 강의별 만족도
    키워드별 강의 만족도 분석 보고서가 완성되었습니다.

4.6 5 투표
게시글평점
guest
7 댓글
Inline Feedbacks
모든 댓글 보기
zelos511
zelos511
2021년 8월 27일 9:21 오전
게시글평점 :
     

조인종류'는 왼쪽 외부를 선택합니다. 이후 조인 종류 아래에 있는 '유사 일치를 사용하여 병합 비교' 옵션을 체크한 다음, 아래에 있는 '유사일치 옵션'을 클릭합니다.

조인종류 창 밑에 "유사일치사용-" 체크란 창이 없습니다, 어떻게해야 나오나요??

꼬꼬
꼬꼬
2021년 8월 27일 5:17 오후
게시글평점 :
     

많이 배워갑니다. 감사합니다.

엑셀고수
엑셀고수
2021년 8월 31일 12:04 오전
게시글평점 :
     

감사합니다

아이올로스
아이올로스
2021년 9월 2일 1:20 오후
게시글평점 :
     

유용한 정보 감사합니다~~

IRON119
IRON119
2021년 11월 5일 1:20 오후
게시글평점 :
     

마지막 출력하기 우해 병합과정에서 [DataFormat.Error] 잘못된 셀 값 '#NAME?'입니다. 라는 메시지가 뜨는데 뭐가 문제일까요?

7
0
여러분의 생각을 댓글로 남겨주세요.x