엑셀 파워쿼리 키워드 자동 분석 | 텍스트 마이닝 - 설문조사 분석
파워쿼리 키워드 분석 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀고급] 파워쿼리 키워드분석, 텍스트마이닝 실습완성파일[엑셀고급] 파워쿼리 키워드분석, 텍스트마이닝 실습예제파일
파워쿼리 유사일치(Fuzzy Match) 기능은 유사도(임계값) 설정에 따라 범위 내 유사한 단어를 검색하는 기능입니다. 현재 M365 버전에서만 제공되고, 엑셀 이전 버전 사용자는 파워쿼리가 있더라도 유사일치 기능을 사용할 수 없습니다.
유사일치를 사용하면 아래와 같이, "삼송전자", "삼싱전자"로 잘못입력된 데이터를 "삼성전자"로 올바르게 매칭할 수 있습니다.
검색할 단어 유사일치 삼성전자 삼성전자 삼싱전자 삼성전자 삼송전자 삼성전자 ... ... 뿐만 아니라 이번 강의에 소개해드린 것 처럼 문장 내 유사 키워드를 자동으로 인식해서 취합할 수도 있는데요. 이번 강의에서는 키워드 분석 기능만 소개해드렸지만, 문장 분리나 변환테이블 기능을 같이 활용하면 더욱 강력한 키워드 분석 툴을 제작할 수 있습니다.
파워쿼리 유사일치 기능에 대한 기초 내용은 아래 영상강의에서 자세히 설명해 드렸습니다.
- 쿼리 만들기 : 파워쿼리로 쿼리를 만들려면, 해당 범위가 반드시 표로 변경되어야 합니다. 예제파일을 실행하면 "표_설문결과"와 "표_키워드" 라는 이름으로 표를 미리 만들어드렸습니다. 표 기능에 대한 자세한 설명은 아래 '표 기능의 모든 것' 기초 입문강의를 참고하세요.
예제파일에는 표를 미리 만들어드렸습니다. - 표를 선택한 뒤, [데이터] - [테이블/범위에서]를 클릭하면 파워쿼리가 실행되며 쿼리가 만들어집니다.
데이터 - 테이블/범위에서를 클릭해서 파워쿼리를 실행합니다. 오빠두Tip : M365 베타테스트 버전을 사용중일 경우, '테이블/범위에서' 라는 이름이 '시트에서'로 표시됩니다. - 파워쿼리로 쿼리가 실행되었으면, 좌측 상단의 [파일] - [닫기 및 다음으로 로드]를 클릭해서 쿼리를 저장합니다.
파일 - 닫기 및 다음으로 로드를 클릭합니다. - [데이터 가져오기] 창이 나오면, '연결만 만들기'를 체크 한 뒤, [확인] 버튼을 누릅니다.
연결만 만들기를 선택해서 쿼리를 생성합니다. - 우측에 [쿼리 및 연결] 창이 표시되면서, 쿼리가 연결로 만들어진 것을 확인할 수 있습니다. 나머지 표도 동일한 방법으로 쿼리를 생성합니다.
2개의 쿼리가 생성되었습니다. 오빠두Tip : 만약 화면 오른쪽에 '쿼리 및 연결' 창이 안 보일 경우, [데이터] - [쿼리 및 연결] 버튼을 클릭합니다. - 유사일치 병합하기 : 이제 만든 쿼리를 유사일치로 병합하겠습니다. [데이터] - [데이터 가져오기] - [쿼리 결합] - [병합] 을 선택합니다.
데이터 - 데이터 가져오기 - 쿼리결합 - 병합을 클릭합니다. - 병합 대화상자가 나오면, 위쪽 테이블은 '표_설문결과(메인 테이블)', 아래쪽 테이블은 '표_키워드(키워드 테이블)'을 선택합니다. 이후 테이블에서 키워드를 비교할 대상 필드를 선택합니다.
각 테이블을 선택한 뒤, 키워드를 비교할 필드를 지정합니다. - '조인종류'는 왼쪽 외부를 선택합니다. 이후 조인 종류 아래에 있는 '유사 일치를 사용하여 병합 비교' 옵션을 체크한 다음, 아래에 있는 '유사일치 옵션'을 클릭합니다.
유사일치를 사용하여 병합비교를 선택합니다. - 유사성 임계값을 입력합니다. 0.3을 입력하면 테이블의 70개 값 중 67개 값이 일치한다고 표시됩니다. [확인] 버튼을 클릭합니다.
오빠두Tip : 임계값은 0~1 사이의 숫자로 입력하며, 1에 가까울수록 더욱 정확하게 검색합니다. 실무에서는 보통 0.3~0.8 사이의 값으로 사용하면 됩니다. - 유사일치 비교 쿼리 만들기 : 확인 버튼을 클릭하면 파워쿼리가 실행됩니다. 쿼리 오른쪽 [표 키워드]의 확장버튼을 클릭한 뒤, '키워드'를 선택 후 [확인] 버튼을 클릭하면 유사일치로 검색된 키워드가 표시됩니다.
키워드의 확장버튼 클릭 후, 키워드 필드를 확장합니다. - [파일] - [닫기 및 다음으로 로드]를 클릭한 다음, '표' 형태로 병합된 쿼리를 기존 워크시트에 출력합니다.
병합된 쿼리를 기존 워크시트에 추가합니다. - 키워드 분석이 완료되었습니다.
키워드 분석 쿼리가 완성되었습니다.
이제 분석된 표를 바탕으로 피벗테이블을 만들어서 각 부서별 희망 강의 주제가 어떻게 되는지 데이터를 분석해보겠습니다.
- 피벗테이블 만들기 : 표의 아무 셀을 선택한 뒤, [삽입] - [피벗테이블]을 선택합니다. 이후 피벗테이블을 기존 워크시트의 오른쪽 영역에 추가합니다.
피벗테이블을 생성합니다. - 이후 아래 표와 같이 피벗테이블 필드를 이동하면 각 부서별 희망 강의 주제에 대한 분석이 완료됩니다.
각 필드를 이동하면 피벗테이블이 완성됩니다. - 피벗테이블 정렬하기 : 데이터를 더욱 보기좋게 시각화 하기 위하여 선호도가 높은 주제순으로 정렬하겠습니다. 피벗테이블의 행 레이블 필터를 클릭 후, '기타 정렬 옵션'으로 이동합니다.
행 레이블의 정렬 옵션으로 이동합니다. - 이후 정렬 옵션에서 '내림차순 기준'을 선택한 뒤, '키워드 개수'를 선택합니다. 이후 [확인] 버튼을 클릭하면 선호도가 높은 키워드 순으로 피벗테이블이 정렬됩니다.
내림차순 기준으로 피벗테이블을 정렬합니다.
조건부서식의 색조 기능를 사용하면 피벗테이블 보고서를 손쉽게 시각화 할 수 있습니다.
- 피벗테이블 시각화하기 : 총 합계 범위를 선택한 뒤, [홈] - [조건부서식] - [색조] 에서 빨간색 배경의 색조를 선택합니다
피벗테이블의 총합계 범위 선택 후, 조건부서식 - 색조를 적용합니다. - 이후 값 영역이 입력된 범위를 선택 한 다음, [홈] - [조건부서식] - [색조] 에서 초록색 배경의 색조를 선택합니다.
값 범위를 선택한 뒤, 초록색 색조를 적용합니다. - 피벗테이블 보고서가 더욱 깔끔하게 정리되었습니다.
- 조건부서식에 대한 보다 다양한 예제는 아래 '조건부서식의 모든 것' 기초 입문 강의를 참고해주세요.
피벗테이블의 필드를 조금만 변경하면 각 키워드별 강의 만족도도 손쉽게 분석할 수 있습니다.
- 피벗테이블 필드 변경하기 : 기존에 만들었던 피벗테이블을 복사한 뒤, 아래에 붙여넣기 합니다. 이후 피벗테이블의 각 필드를 아래 표와 같이 이동하면 키워드별 강의 만족도가 조사됩니다.
피벗테이블을 복사/붙여넣기 후, 키워드 별 만족도를 분석하는 피벗테이블을 생성합니다. - 값 요약방식 변경 : 피벗테이블의 기본 값 요약방식은 '합계' 입니다. 하지만 우리가 보고자 하는 강의 만족도는 '평균'으로 집계되어야 합니다. 따라서 각 필드를 우클릭 한 뒤, 값 요약기준을 '평균'으로 변경합니다.
각 값필드를 우클릭 한 뒤, 요약기준을 평균으로 변경합니다. - 숫자 표시형식 변경 : 값 요약기준을 평균으로 변경하면 숫자에 소수점이 표시되면서 데이터를 한눈에 파악하기 어려워집니다. 따라서 숫자의 표시형식을 변경합니다. 피벗테이블 범위를 선택한 뒤, [셀 서식]으로 이동합니다.
값 범위 선택 후, 우클릭 - 셀 서식으로 이동합니다. - 이후 사용자 지정에서 표시형식을 "0.0" 으로 변경한 뒤, [확인] 버튼을 클릭하면 데이터가 보기 좋게 변경됩니다.
숫자 표시형식을 0.0 으로 변경합니다. - 피벗테이블 시각화하기 : 범위가 선택된 상태에서 [홈] - [조건부서식] - [색조]를 추가하면 키워드별 강의 만족도가 분석된 피벗테이블 보고서가 완성됩니다.
키워드별 강의 만족도 분석 보고서가 완성되었습니다.
