1-5 [사례 실습] 파워쿼리로 엑셀에 블로그 글(RSS, XML) 가져오기3

[목표]

1. sheet 정리

2. 보고서 양식 만들기

3. 글목록을 백업하는 매크로 만들기

4. 글목록을 새로 고치는 매크로 만들기

5. 매크로를 실행할 버튼 만들기

6. '조건부 서식'을 이용하여 새로운 글 표시

파워쿼리를 통해 원하는 블로그의 글을 엑셀로 수집하고,

이 글목록을 합친 후 엑셀로 불러오는 작업을 진행했습니다.

이번에는 보다 편리한 업무 처리를 위해 매크로를 이용하여, 글목록을 가져오는 버튼을 만들어 글목록을 새로고치고, 이 글 중 새롭게 올라온 글이 어떤 것인지 표시하는 작업을 진행해보겠습니다.

글목록이 새로운 것인지 아닌지를 알기 위해서 이전에 작업했던 글목록을 보관하겠습니다.

이렇게 하는 이유는, 글목록을 새로 불러왔을 때 이 글목록 중 보관된 글목록에 있는지 확인하고, 만약 새로운 것이라면 새로운 글이라고 표시해주기 위함입니다.

0. 매크로를 위한 사전 작업

일반적으로 사용하는 엑셀 파일 형식이 xls 또는 xlsx 형식에서는 매크로를 사용할 수 없기 때문에

매크로를 사용하기 위해서는 파일을 다른 이름으로 저장하여 xlsm 형식으로 변경해야 합니다.

1. sheet 정리

준비 작업으로 sheet 이름을 정리해보겠습니다.

삼성전자 블로그 글을 가져온 sheet의 이름을 '삼성전자'라고 수정하고,

LG전자 블로그 글을 가져온 sheet의 이름을 'LG전자'라고 수정하고,

두 블로그 글을 통합한 sheet의 이름을 '통합'이라 수정합니다.

필요한 형태로 글목록을 재배열한 '모니터링' sheet와

'통합'에 있는 글목록을 보관해두기 위한 '통합백업' sheet를 만듭니다.

2. 보고서 양식 만들기

아래 화면과 유사한 형태의 간단한 보고서 양식을 만들어 보겠습니다.

번호는 1,2,3,4로 증가하는 형식으로 만드시면 되고,

출처와 날짜는 '통합' sheet에서 가져옵니다.

블로그 글목록을 가져올 때 제목과 함께 가져온 URL을 그대로 표시하면 깔끔하지 않기 때문에, 글제목에 해당 URL의 링크가 걸려서 가져오기 위해 hyperlink라는 함수를 사용했습니다.

이 URL을 제외하고 글제목 가져와도 되지만, 간혹 글내용을 확인할 경우도 있기 때문에 URL을 링크형태로 가져오는 것을 권해드립니다.

- hyperlink : url주소와 글제목을 입력해주면 글제목이 표시되면서 url주소의 링크가 걸립니다.

글제목 =HYPERLINK(삼성LG전자블로그통합[@URL],삼성LG전자블로그통합[@제목])

3. 글목록을 백업하는 매크로 만들기

자 이제 매크로 작업을 시작해보겠습니다.

아래 그림처럼 보기 메뉴 > 매크로 > 매크로 기록을 선택하시면 됩니다. 

매크로에 대해 잘 모르시더라도 순서대로 따라하시면 큰 무리없이 진행하실 수 있으니 자신감을 가지고 따라해보세요.

- 매크로 기록 창이 나타나면 이름을 '글목록백업'이라고 입력한 후 '확인'을 클릭합니다.

이제 매크로 기록이 시작되었습니다. 다른 불필요한 작업을 함께 하시면 매크로에 기록이 되기 때문에 아래에 기재된 필요작업만 진행하시는 것이 좋습니다.

- 대메뉴 왼쪽 아래에 있는 '이름상자'에서 '삼성LG전자블로그통합'이라는 표 선택 > 컨트롤C(ctrl + c)를 눌러 내용을 복사

- '통합백업' sheet를 선택 > A1셀에 마우스 위치 > 우마우스 클릭 > 선택하여 붙여넣기 > 붙여넣기 옵션 중 값 선택

- '모니터링' sheet를 선택

- 보기 메뉴 > 매크로 > 기록 중지 선택

- 보기 메뉴 > 매크로 > 매크로 보기 > 글목록백업 선택 > 편집 클릭하여 매크로가 잘 저장되었는지 확인합니다. 아래와 같은 매크로가 생성되었다면 정상적으로 매크로가 생성된 것입니다. 만약 다른 코드가 들어있다면 수정해주시기 바랍니다.

Sub 글목록백업()

'

' 글목록백업 매크로

'

'

    Application.Goto Reference:="삼성LG전자블로그통합"

    Selection.Copy

    Sheets("통합백업").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("모니터링").Select

End Sub

4. 글목록을 새로 고치는 매크로 만들기

이번에는 블로그글을 새로 가져오는 매크로를 만들어보겠습니다.

3번 매크로 제작 방식을 참고하시어 '새글받기'라는 매크로를 만든 후

데이터 메뉴 > 모두 새로 고침 버튼 클릭 > '모니터링' sheet 선택 > 매크로 기록 중지하시면 아래와 같은 매크로가 생성됩니다.

Sub 새글받기()

'

' 새글받기 매크로

'

'

    ActiveWorkbook.RefreshAll

    Sheets("모니터링").Select

End Sub

5. 매크로를 실행할 버튼 만들기

'글목록백업' 매크로와 '새글받기'매크로가 완성되었다면 버튼을 만들어서 버튼을 누르면 이 매크로가 실행되도록 하겠습니다.

'모니터링' sheet의 표 옆에 아래 그림과 같은 버튼을 2개(새글받기와 글목록 백업) 만듭니다.

버튼을 누를 때 매크로가 동작하게 하기 위해서는 각 버튼에 매크로를 지정해주어야 합니다. 

매크로를 지정하려면 '새글받기' 버튼 위에서 > 우마우스를 클릭 > 매크로지정 > '새글받기'매크로 선택 > 확인을 클릭합니다.

같은 방법으로 '글목록 백업'의 버튼도 '글목록백업'매크로를 지정해주시기 바랍니다.

매크로 지정이 끝났으면 각 버튼을 클릭하여 잘 동작되는지 확인해보시기 바랍니다.

매크로가 지정된 버튼은 클릭하면 매크로를 실행하는 것이 기본이기 때문에 디자인 편집이 약간 까다로운데요. 매크로가 지정된 버튼의 디자인을 편집하시려면 컨트롤키(ctrl)를 누르고 버튼을 클릭하시면 됩니다.

6. '조건부 서식'을 이용하여 새로운 글 표시

자... 이제 마지막 작업인 새글을 표시해주는 작업만 남았습니다.

이를 위해서 새글 여부를 판단하는 열을 하나 만들겠습니다. E열에 '새글여부'라는 제목이 열을 만들고,

수식에

=IF(SUMPRODUCT((C2=통합백업!$A$1:$A$30)*1)=0,1,0)를 입력합니다.

이 내용은 C2에 있는 제목이 통합백업의 제목 전체 중에 없으면 1이라고 표시하고, 있으면 0이라고 표시하라는 것입니다.

즉, 새 글은 1이라고 표시하고, 이미 있던 글은 0이라고 표시합니다.

자, 이제 조건부 서식을 통해 새글을 표시해보겠습니다.

제목에 해당하는 부분을 전체선택하고, 홈 메뉴 > 조건부 서식 > 새 규칙을 클릭합니다.

새 서식 규칙이 나타나면 '수식을 사용하여 서식을 지정할 셀 결정'을 선택 > '다음 수식이 참인 값의 서식 지정'란에 좀 전에 만든 새글여부 열이 첫번째 행(E2)을 선택하고 상대참조 상태로 만든 후 

서식 버튼을 눌러 새글에 적용할 서식을 설정하시면 됩니다.

글꼴은 굵게, 테두리 색은 오렌지색, 채우기 색은 노란색으로 지정해보겠습니다.

바뀌는 것이 하나도 없습니다. 지금은 새 글과 백업한 글이 같기 때문입니다.

테스트를 위해 '통합백업'sheet에 가서 1번째 글의 제목을 임의로 바꿔보시면(저는 쉐리프라는 단어를 삭제해봤습니다.) 아래 그림처럼 서로 다른 제목의 글만 서식이 바뀌는 것을 확인하실 수 있습니다.

(그나저나, 삼성전자 블로그 담당자 분들은 추석인데도 포스팅을 하시네요. 그 노력에 박수를 보냅니다.)

마지막으로 새글여부는 보고서에 보일 필요가 없기 때문에 새글여부 > 우마우스 클릭 > 숨기기로 보이지 않게 처리합니다.

[사용 방법]

아침에 출근해서 새 글을 모니터링할 때 '새글받기' 버튼을 눌러 새글을 수집하시고, 작업을 종료할 때 '글목록 백업'버튼을 눌러 내용을 저장해둡니다.

이 2가지 작업을 한꺼번에 할 수도 있는데요. 이 부분은 여러분들에게 숙제로 남겨두겠습니다. 각자의 방법으로 한 번 해결해보시기 바랍니다.

이렇게 해서 총 3회에 걸쳐 진행한 파워쿼리로 엑셀에 블로그 글 가져오기 작업이 끝났습니다.

블로그 2개를 모니터링한다고 하면, 웹사이트에 접속해서 확인하는 것이 더 빠를 수도 있고,

RSS 리더같은 프로그램, 또는 웹사이트를 통해 모니터링하시는 것이 빠를 수도 있겠습니다.

하지만, 블로그 수십개를 모니터링하거나, 블로그 외에 각종 웹사이트, DB를 통합 모니터링 해야 한다면 파워쿼리를 사용하여 통합 모니터링 체계를 갖추시는 것이 훨씬 효율적일 수 있습니다. 앞으로 블로그 외에도 다양한 정보를 수집하는 방법에 대해 알아보겠습니다.

다음 글에서는 파워쿼리를 통해 엑셀로 뉴스 수집하는 방법을 소개해드리겠습니다.

감사합니다.

[사례 실습 파일]

1-5 사례 실습 파워쿼리로 엑셀에 블로그 글(RSS XML) 가져오기3.xlsm