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

by 오빠두엑셀

학습시간 12분
난이도 고급
작성일 2021.08.25

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

이 강의에서는 파워쿼리의 유사일치(Fuzzy Match) 기능과 피벗테이블을 결합해 설문 응답에 흩어져 있는 유사 키워드를 자동으로 분류하고 분석하는 방법을 다룹니다. 임계값 설정, 표 변환과 쿼리 병합, 색조 시각화까지 한 흐름으로 정리해 텍스트 형태의 응답 데이터에서도 부서별 선호 주제와 만족도를 안정적으로 도출할 수 있습니다.

DOWNLOADS

실습자료를 준비했어요

수업에서 사용한 예제 파일과 보충 자료를 한 곳에 정리했습니다!👇

실습파일.xlsx

로그인 후 다운로드

실습 가이드

파워쿼리 유사일치(Fuzzy Match)는 설정한 유사도(임계값) 기준으로 범위 내 비슷한 단어를 찾아 매칭하는 기능입니다. 현재 M365 버전에서만 제공되며, 그 이전 버전에서는 파워쿼리가 있어도 유사일치 기능을 사용할 수 없습니다.

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

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

이번 강의에서 소개해드린 것처럼 문장 안에 포함된 유사 키워드를 자동으로 인식해 취합하는 작업도 가능합니다. 본 강의에서는 키워드 분석 중심으로 다루지만, 문장 분리나 변환 테이블 기능을 함께 사용하면 더욱 정교한 키워드 분석 도구를 만들 수 있습니다.

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

쿼리 만들기 : 파워쿼리로 쿼리를 만들기 위해서는 대상 범위를 반드시 표로 변환해야 합니다. 예제 파일을 실행하면 "표_설문결과"와 "표_키워드"라는 이름의 표가 미리 준비되어 있습니다. 표 기능에 대한 자세한 설명은 아래 '표 기능의 모든 것' 기초 입문 강의를 참고하세요.
엑셀 표 기능의 모든 것, 기초-응용 완벽정리
표를 선택한 뒤 [데이터] - [테이블/범위에서]를 클릭하면 파워쿼리가 실행되며 쿼리가 생성됩니다.

오빠두Tip : M365 베타테스트 버전을 사용 중이라면 '테이블/범위에서' 메뉴가 '시트에서'로 표시됩니다.
파워쿼리 편집기가 실행되면 좌측 상단의 [파일] - [닫기 및 다음으로 로드]를 클릭해 쿼리를 저장합니다.
[데이터 가져오기] 창이 표시되면 '연결만 만들기'에 체크한 뒤 [확인] 버튼을 클릭합니다.
화면 우측의 [쿼리 및 연결] 창에 쿼리가 연결 형태로 등록된 것을 확인할 수 있습니다. 나머지 표도 동일한 방법으로 쿼리를 생성합니다.

오빠두Tip : 만약 화면 오른쪽에 '쿼리 및 연결' 창이 보이지 않는다면 [데이터] - [쿼리 및 연결] 버튼을 클릭해 표시할 수 있습니다.
유사일치 병합하기 : 생성한 쿼리를 유사일치 방식으로 병합하겠습니다. [데이터] - [데이터 가져오기] - [쿼리 결합] - [병합]을 선택합니다.
병합 대화상자가 나타나면 위쪽 테이블은 '표_설문결과'(메인 테이블), 아래쪽 테이블은 '표_키워드'(키워드 테이블)를 선택합니다. 이어서 각 테이블에서 키워드 비교에 사용할 대상 필드를 지정합니다.
'조인 종류'는 '왼쪽 외부'를 선택합니다. 이후 조인 종류 아래의 '유사 일치를 사용하여 병합 비교' 옵션에 체크한 다음 그 아래의 '유사일치 옵션'을 클릭합니다.
유사성 임계값을 입력합니다. 0.3을 입력하면 테이블의 70개 값 중 67개가 일치한다고 표시됩니다. 값을 확인한 뒤 [확인] 버튼을 클릭합니다.

오빠두Tip : 임계값은 0~1 사이의 숫자로 입력하며, 1에 가까울수록 더 정확하게 매칭됩니다. 실무에서는 일반적으로 0.3~0.8 사이의 값을 사용합니다.
유사일치 비교 쿼리 만들기 : [확인] 버튼을 클릭하면 파워쿼리가 실행됩니다. 쿼리 오른쪽 [표 키워드]의 확장 버튼을 클릭한 뒤 '키워드'를 선택하고 [확인] 버튼을 누르면 유사일치로 매칭된 키워드가 표시됩니다.
[파일] - [닫기 및 다음으로 로드]를 클릭한 뒤 '표' 형태로 병합된 쿼리를 기존 워크시트에 출력합니다.
이로써 키워드 분석을 위한 데이터 준비가 완료되었습니다.

이제 분석된 표를 기반으로 피벗테이블을 만들어 각 부서별 희망 강의 주제 분포를 데이터로 분석해 보겠습니다.

피벗테이블 만들기 : 표의 임의 셀을 선택한 뒤 [삽입] - [피벗테이블]을 선택합니다. 이어서 피벗테이블을 기존 워크시트의 오른쪽 영역에 추가합니다.
이후 아래 표와 같이 피벗테이블 필드를 배치하면 부서별 희망 강의 주제에 대한 분석이 완료됩니다.
피벗테이블 정렬하기 : 데이터를 더 명확하게 시각화하기 위해 선호도가 높은 주제 순으로 정렬하겠습니다. 피벗테이블의 행 레이블 필터를 클릭한 뒤 '기타 정렬 옵션'으로 이동합니다.
정렬 옵션 창에서 '내림차순 기준'을 선택한 뒤 '키워드 개수'를 지정합니다. [확인] 버튼을 클릭하면 선호도가 높은 키워드 순서대로 피벗테이블이 정렬됩니다.

조건부서식의 색조 기능을 활용하면 피벗테이블 보고서를 더 직관적으로 시각화할 수 있습니다.

피벗테이블 시각화하기 : 총 합계 범위를 선택한 뒤 [홈] - [조건부서식] - [색조]에서 빨간색 배경 색조를 선택합니다.
이어서 값 영역이 입력된 범위를 선택한 다음 [홈] - [조건부서식] - [색조]에서 초록색 배경 색조를 선택합니다.
이로써 피벗테이블 보고서가 한층 깔끔하게 정리되었습니다.
조건부서식의 다양한 활용 예제는 아래 '조건부서식의 모든 것' 기초 입문 강의를 참고하세요.
엑셀 조건부서식 기초부터 응용까지 완벽정리 :: 직장인 필수

피벗테이블의 필드 구성을 약간만 변경하면 키워드별 강의 만족도 역시 손쉽게 분석할 수 있습니다.

피벗테이블 필드 변경하기 : 앞서 만든 피벗테이블을 복사해 아래쪽에 붙여넣은 뒤 각 필드를 아래 표와 같이 재배치하면 키워드별 강의 만족도 분석이 완성됩니다.
값 요약방식 변경 : 피벗테이블의 기본 값 요약 방식은 '합계'입니다. 그러나 강의 만족도는 '평균'으로 집계되어야 의미가 있으므로, 각 필드를 우클릭한 뒤 값 요약 기준을 '평균'으로 변경합니다.
숫자 표시형식 변경 : 값 요약 기준을 평균으로 변경하면 소수점 자릿수가 늘어나 데이터 파악이 어려워집니다. 따라서 숫자 표시 형식을 정리합니다. 피벗테이블 범위를 선택한 뒤 [셀 서식]으로 이동합니다.
이어서 사용자 지정에서 표시형식을 "0.0"으로 변경한 뒤 [확인] 버튼을 클릭하면 데이터가 보기 좋게 정리됩니다.
피벗테이블 시각화하기 : 범위가 선택된 상태에서 [홈] - [조건부서식] - [색조]를 적용하면 키워드별 강의 만족도가 분석된 피벗테이블 보고서가 완성됩니다.