엑셀 파워쿼리 크롤링 - 초보자도 가능한 원클릭 자동화
엑셀 파워쿼리 크롤링 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
이번 강의는 별도의 예제파일 없이 비어있는 통합문서에서 시작합니다.
- [엑셀고급] 엑셀 파워쿼리 크롤링 기초 실습완성파일
라이브 강의 전체영상도 함께 확인해보세요!
위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.
모든 크롤링의 시작은 크롤링 할 대상 웹페이지의 구조를 살펴보는 것 입니다. 따라서 이번 강의에서 크롤링할 웹페이지의 구조를 하나씩 살펴보겠습니다.
페이지설명 URL링크 비트코인시세 https://kr.investing.com/crypto/ 기술요약정보 https://kr.investing.com/indices/investing.com-btc-usd 오빠두Tip : 영상강의에서는 기술요약정보 페이지에서 비트코인 시세와 기술요약정보를 동시에 가져왔지만, 현재 웹페이지 구조가 변경되어 비트코인 시세와 기술요약 정보를 각각 다른 페이지에서 크롤링합니다.- 비트코인 시세 페이지 구조 살펴보기 : 각 가상화폐의 현재 시세를 보여주는 웹사이트로 이동합니다. 이후 키보드 F12키를 누르면 개발자도구가 실행됩니다.
인터넷 브라우저에서 F12키를 누르면 개발자도구가 실행됩니다. 오빠두Tip : 사용중인 브라우저마다 개발자도구의 표시형식이 조금씩 다를 수 있습니다. - 크롬 브라우저 기준, 개발자도구의 좌측 상단을 보면 '마우스 커서모양'의 버튼이 있습니다. 버튼을 클릭한 뒤, 크롤링할 데이터 위로 마우스 커서를 이동하면 데이터의 HTML 구조를 확인할 수 있습니다.
HTML 개체 선택 버튼을 클릭 후 마우스 커서를 개체 위로 이동합니다. - 커서를 데이터 위로 가져간 상태에서 클릭하면 해당 개체의 HTML 코드를 확인할 수 있습니다. 비트코인의 가격은 현재 <td> 태그 안에 표시되는 것을 확인할 수 있습니다.
이후 마우스를 클릭하면 해당 개체의 HTML 코드를 볼 수 있습니다. 오빠두Tip : 엑셀에서 제공하는 파워쿼리는 '<table>' 형태로 이루어진 데이터만 가져올 수 있습니다. 따라서 <table>, <tr>, <td>.. 형태로 이루어진 데이터만 크롤링할 수 있는 것을 주의합니다. (파워BI에서 제공하는 파워쿼리는 더 다양한 형태의 데이터를 크롤링할 수 있습니다.) - 비트코인 기술요약 페이지 구조 살펴보기 : 이번에는 기술요약을 보여주는 페이지 구조를 살펴보기 위해 아래 링크로 이동합니다.
- 마찬가지로 F12키를 눌러 개발자도구를 실행한 뒤, 크롤링할 데이터의 HTML 구조를 살펴봅니다. 기술요약 정보도 table 안에 입력되어 있는 것을 확인할 수 있습니다.
기술요약 표의 구조도 Table로 작성된 것을 확인할 수 있습니다.
엑셀에서 제공되는 파워쿼리를 활용하면 페이지 안에 <table> 형태로 입력된 데이터를 손쉽게 크롤링하고 가공하는 업무를 자동화 할 수 있습니다. 각 버전별 엑셀 파워쿼리 지원 여부와 파워쿼리에 대한 기초 내용은 아래 영상강의를 참고해주세요.
- 웹에서 불러올 데이터 선택하기 : 새로운 통합문서를 실행한 뒤, [데이터] - [데이터 가져오기 및 변환] - [웹] 버튼을 클릭합니다. 버튼을 클릭하면 '웹에서 데이터 불러오기' 대화상자가 실행됩니다.
데이터 - 웹 버튼을 클릭해서 웹에서 불러오기 대화상자를 실행합니다. 오빠두Tip : 사용 중인 엑셀 버전마다 화면구성이 조금씩 다를 수 있습니다. 엑셀 파워쿼리를 추가기능으로 설치해야 하는 2013 이전 버전일 경우, [파워쿼리] 탭 - [웹에서] 버튼을 클릭합니다. - 크롤링할 웹페이지의 URL 주소를 입력합니다. 아래 URL 주소를 입력 후, [확인] 버튼을 클릭합니다.
- URL 주소 입력 후 조금만 기다리면 '탐색창'이 실행되면서 웹페이지 안에 있는 테이블 목록이 표시됩니다. 테이블 목록에서 파워쿼리로 출력할 테이블을 선택 후 [데이터 변환] 버튼을 클릭합니다.
불러올 테이블을 선택 후, [데이터 변환] 버튼을 클릭합니다.
오빠두Tip : 왼쪽 상단에 '여러 항목 선택' 옵션을 활성화하면 동시에 여러 테이블을 파워쿼리로 불러올 수 있습니다. - 파워쿼리 편집기에서 데이터 가공하기 : 파워쿼리 편집기가 실행되면서 방금 전에 선택했던 테이블이 쿼리로 만들어집니다.
선택한 표가 파워쿼리 편집기로 불러와집니다. - 현재 불러온 자료는 '적극매수~적극매도' 형태의 텍스트로 이루어져 있습니다. 하지만 좀 더 효율적인 데이터 분석을 위해 적극매도는 1부터 적극매수를 5로 가정하여 값을 변경합니다. '5분' 열을 선택한 뒤, 키보드 Shift 키를 누른 상태로 '월간' 열을 선택하면 전체 영역이 한 번에 선택됩니다.
값을 바꿀 데이터 영역을 선택합니다. - 그 상태에서 [변환] - [값 바꾸기]로 이동한 뒤, 각 값을 하나씩 변경합니다.
변환 - 값 바꾸기에서 값을 하나씩 변경합니다. 찾을 값 바꿀 항목 적극 매수 5 매수 4 중립 3 매도 2 적극 매도 1 오빠두Tip : 고급 옵션에서 '전체 셀 내용 일치'를 활성화 하면 전체 셀 내용이 일치하는 항목만 값을 바꿀 수 있습니다. 예를 들어 '적극 매도'와 '매도'가 있는 상태에서 '매도'의 값을 '4'로 바꾸면 '적극 4', '4' 로 변경되는데 '전체 셀 내용 일치'를 활성화하면 이런 문제를 방지할 수 있습니다. - 데이터 형식 변경하기 : 파워쿼리는 각 필드마다 데이터 형식을 지정합니다. 따라서 쿼리를 가공하고 출력하는 단계마다 각 필드의 데이터 형식이 올바른지 확인하는 습관을 갖는 것이 좋습니다.
- 우리가 방금 변경한 값은 텍스트 형식이 아닌 숫자 형식입니다. 따라서 각 필드의 데이터 형식을 10진수로 변경합니다.
머리글 좌측의 데이터 형식을 10진수로 변경합니다. 오빠두Tip : 사용 중인 엑셀 버전에 따라, 머릿글 왼쪽에 데이터형식이 보이지 않을 수 있습니다. 그럴 경우, [변환] - [데이터 형식] 에서 데이터 종류를 변경합니다.
- 데이터 가공이 끝났습니다. 이 외에 다른 웹 페이지도 동일한 과정으로 데이터를 크롤링하고 가공할 수 있습니다.
다른 웹페이지의 데이터도 동일한 과정으로 불러올 수 있습니다.
데이터 가공까지 끝났으면, 이제 크롤링 한 결과를 시트 위로 출력해보겠습니다.
- 크롤링 결과를 시트 위로 출력하기 : [파일] - [닫기 및 다음으로 로드]를 클릭합니다.
[파일] - [닫기 및 다음으로 로드] 를 선택합니다. - [데이터 가져오기] 대화상자가 실행되면, '연결만 만들기' 옵션을 선택 후 [확인] 버튼을 클릭합니다.
데이터 가져오기 옵션에서 '연결만 만들기'를 선택 후 확인 버튼을 클릭합니다. 오빠두Tip : 또는 데이터 가져오기 대화상자에서 '표'를 선택 한 후, 시트 위로 바로 출력할 수도 있습니다. - [쿼리 및 연결] 목록에 새로운 쿼리가 등록됩니다. 쿼리를 우클릭 한 뒤, [다음으로 로드] - '표' 형태로 기존 워크시트에 출력합니다.
쿼리 및 연결에서 쿼리를 우클릭 - 다음으로 로드에서 표 형태로 시트위에 출력합니다. - 열 너비 자동맞춤 문제 해결 : 파워쿼리로 출력된 표는 새로고침을 하면 표의 열너비가 자동으로 맞춰집니다. 따라서 보고서를 작성할 때에는 열너비 자동맞춤 옵션을 비활성화 하는 것이 좋습니다. 표를 선택한 뒤, [표 디자인] - [속성] 으로 이동합니다.
표를 선택 한 뒤, [표 디자인] - [속성] 으로 이동합니다. - [열 너비 조정] 옵션을 비활성화 한 뒤, [확인] 버튼을 클릭합니다.
속성에서 '열 너비 조정'을 비활성화 한 뒤, [확인] 버튼을 클릭합니다. - 이제 표의 열 너비를 변경 후, 우클릭 - [새로고침] 을 선택하면 표의 열 너비가 바뀌지 않는 것을 확인할 수 있습니다.
표를 새로고침 해도 열너비가 자동으로 변하지 않습니다.
이번에 크롤링한 데이터는 1~5 사이의 값이 입력되어 있습니다. 보고서를 작성할 때 1, 10, 100, 1000 과 같이 숫자의 자리수 차이가 없고, 한자리수나 두자리수로만 값이 입력될 경우 값의 대소차이를 한 눈에 파악하기 어려운 문제가 있습니다.
따라서 이번에는 크롤링 한 데이터에 조건부서식을 적용해서 표를 시각화하겠습니다. 조건부서식에 대한 보다 자세한 내용은 조건부서식의 모든 것 기초 입문강의를 참고하세요.
- 조건부서식 적용하기 : 표 범위를 선택 후, [홈] 탭 - [조건부서식] - [색조] - [기타규칙]으로 이동합니다.
범위 선택 후, [홈] - [조건부서식] - [색조] - [기타 규칙]으로 이동합니다. - 서식 스타일을 '3가지 색조'로 변경합니다. 이후 최소값과 중간값, 최대값의 종류를 모두 숫자로 변경한 후 각 값을 1,3,5 로 변경합니다. 마지막으로 중간값의 색상을 노란색에서 흰색으로 변경한 후, [확인] 버튼을 클릭합니다.
조건부서식의 옵션을 변경합니다. - 선택한 범위에 조건부서식이 적용되었습니다.
범위에 조건부서식이 적용되었습니다.
쿼리 새로고침 매크로 추가하기
이제 마지막 단계로 시트 위에 버튼을 추가해서 웹페이지의 데이터를 빠르게 불러오는 방법을 알아보겠습니다.
- 매크로 편집기 실행 : [개발도구] - [Visual Basic] 버튼을 클릭하거나, 단축키 Alt + F11 키를 눌러 매크로 편집기를 실행합니다. 만약 [개발도구]가 보이지 않을 경우, 아래 링크를 참고해서 개발도구를 활성화합니다.
- [삽입] - [모듈]을 클릭해서 새로운 모듈을 추가합니다. 이후 아래 명령문을 복사하여 모듈안에 붙여넣기 합니다.
매크로 편집기에서 [삽입] - [모듈] 로 새 모듈 삽입 후, 복사한 명령문을 붙여넣기합니다. Sub RefreshAll() ThisWorkbook.RefreshAll MsgBox "모든 데이터를 새로고침하였습니다." End Sub
- 매크로 실행버튼 만들기 : 다시 시트로 이동한 뒤, [삽입] - [도형]에서 사각형 모양의 도형을 시트 위에 추가합니다.
삽입 - 도형에서 사각형 모양의 도형을 삽입합니다. 오빠두Tip : 키보드 Alt 키를 누른 상태로 도형을 삽입하면 셀 크기에 맞춰서 도형이 삽입됩니다. - 도형을 적절히 꾸며준 뒤, 도형을 우클릭 - [매크로 지정]을 선택하면 '매크로 지정' 대화상자가 실행됩니다. 목록에서 RefreshAll 명령문을 선택 후 [확인] 버튼을 클릭합니다.
도형을 우클릭 한 뒤, [매크로 지정]에서 매크로를 설정합니다. - 쿼리 새로고침 옵션 변경 : 이제 도형을 클릭하면 매크로가 실행되면서 쿼리가 새로고침됩니다. 하지만 쿼리는 기본적으로 '다른 작업을 하면서 새로고침'되므로, 쿼리가 새로고침 되기 전에 미리 안내메시지가 출력되는데요. 만약 쿼리 새로고침이 완료 된 뒤, 안내메시지를 출력하려면 쿼리의 새로고침 옵션을 변경합니다.
- 표를 선택 후, [쿼리] - [속성]으로 이동합니다. 이후 새로고침 옵션에서 '다른 작업하면서 새로 고침' 옵션을 비활성화 후 [확인] 버튼을 클릭합니다. 이제 버튼을 클릭하면 쿼리가 모두 새로고침 된 후, 새로고침이 다 되었다는 안내메시지가 출력됩니다.
쿼리 속성에서 '다른 작업하면서 새로고침'을 비활성화하면, 쿼리 업데이트를 마친 후 매크로가 실행됩니다.
- 비트코인 시세 페이지 구조 살펴보기 : 각 가상화폐의 현재 시세를 보여주는 웹사이트로 이동합니다. 이후 키보드 F12키를 누르면 개발자도구가 실행됩니다.
