Excel의 웹 스크래핑 작동 방식: 웹에서 데이터 가져오기

많은 사람들이 데이터를 기록하거나 계산을 수행하기 위해 Excel을 사용합니다. 그러나 Excel에서 잘 알려지지 않은 기능 중 하나는 인터넷에서 데이터를 스크랩하여 스프레드시트로 가져오는 기능입니다.

이 기사에서는 Excel의 웹 스크래핑 기능을 사용하는 방법을 배웁니다. 또한 이러한 웹 스크랩 쿼리가 작동하는 방식과 자동화하는 방법도 배우게 됩니다.

Excel의 웹 스크래핑 작동 방식

Excel 내부에서 "외부" 데이터를 가져올 수 있습니다. 다음 요구 사항을 염두에 두십시오.

• 컴퓨터에 인터넷 연결이 활성화되어 있어야 합니다.
• 데이터를 가져오는 URL에는 페이지에 정적 데이터 테이블이 있어야 합니다.
• 파일을 자동화하는 경우 Excel 파일이 저장된 컴퓨터에 활성 인터넷 연결이 필요합니다.

Excel에서 웹 스크래핑을 설정하면 "웹 쿼리"가 생성됩니다. 이 쿼리는 웹 URL을 사용하여 인터넷에 연결하고 웹 서버의 데이터로 웹 페이지를 가져옵니다. Excel은 들어오는 HTML을 처리하고 해당 페이지에 있는 표를 제거합니다. 스프레드시트에서 캡처하려는 표를 선택할 수 있습니다.

Excel 웹 쿼리를 만드는 방법

다음 단계를 사용하여 웹 페이지에서 Excel로 테이블 데이터를 가져옵니다. 이 예에서는 날씨 데이터를 사용하지만 주식 시장 데이터 등 을 사용하여 이 작업을 수행할 수도 있습니다 .

1. Excel을 열고 데이터 메뉴를 선택합니다.

2. 리본의 데이터 가져오기 및 변환 섹션 에서 웹 에서 를 선택 합니다.

3. 웹 쿼리 창이 열립니다. 데이터가 있는 페이지의 URL을 URL 필드 에 붙여넣습니다 . 확인 을 선택 합니다 .

4. 엑셀이 웹 콘텐츠를 내밀어 끌어올 것입니다. 네비게이터 창 왼쪽에서 각 테이블 결과를 선택 하면 오른쪽 창에서 테이블 미리보기 를 볼 수 있습니다.

5. 테이블 미리보기에 긁어내고자 하는 데이터가 포함된 웹 페이지의 테이블이 표시되면 창 하단에 있는 로드 버튼을 선택합니다.

6. 웹 페이지 테이블의 데이터가 활성 Excel 스프레드시트의 첫 번째 셸에 나타나는 것을 볼 수 있습니다.

축하합니다. Excel로 웹 페이지 데이터를 성공적으로 스크랩했습니다!

고급 Excel 웹 쿼리 옵션

위에서 설명한 화면에서 몇 가지 추가 옵션을 확인할 수 있습니다. 웹 데이터를 스크랩하여 Excel 스프레드시트의 다른 위치로 가져오려면 로드 버튼 옆에 있는 드롭다운 화살표를 선택하고 대신 로드 위치를 선택 합니다.

데이터 가져오기 창이 열립니다. 이 창의 옵션을 사용하여 원하는 대로 정확하게 데이터를 포맷하고 배치합니다.

여기에 포함된 옵션은 다음과 같습니다.

• 표준 테이블
• 피벗 테이블 보고서
• 피벗 차트
• 연결을 생성하고 데이터를 가져오지 마십시오.

A1으로 기본 설정하지 않고 특정 셀에서 시작하는 데이터를 배치하는 옵션도 표시됩니다.

URL 페이지에서 기본이 아닌 고급 옵션 을 선택하면 고급 URL 정보를 제공할 수 있습니다.

이는 데이터를 로드하는 페이지에 매개변수(일반적으로 뒤에 "?" 문자가 옴) 또는 JSON HTTP 헤더 요청 매개변수가 필요한 페이지와 같은 특수 HTTP 헤더 요청이 포함된 경우에 유용합니다.

이러한 고급 쿼리 기능을 사용하면 데이터를 가져올 정적 URL이 없는 대화형 웹 페이지에서도 Excel로 데이터를 스크랩할 수 있습니다.

Excel 웹 쿼리를 사용자 지정하는 방법

위의 프로세스를 사용하여 데이터를 가져오는 웹 쿼리를 만든 후 추가로 사용자 지정할 수 있습니다.

웹 쿼리 편집기에 액세스하는 방법에는 두 가지가 있습니다. 첫 번째는 결과가 포함된 셀을 마우스 오른쪽 버튼으로 클릭하고 테이블 을 선택한 다음 쿼리 편집 을 선택하는 것 입니다.

그러면 웹 쿼리 편집기 창이 열립니다. 또는 결과 셀을 선택한 다음 오른쪽에 있는 쿼리 및 연결 상자에서 테이블 오른쪽에 있는 작은 시트 아이콘을 선택할 수 있습니다. 그러면 선택한 테이블 데이터의 미리보기가 열립니다. 이 창 하단에서 편집 버튼을 선택합니다 .

웹 쿼리 편집기 창에서 웹 쿼리가 연결 중인 웹 페이지에서 데이터를 가져오는 방식을 정확하게 사용자 지정할 수 있습니다.

여기에 포함된 옵션은 다음과 같습니다.

• 소스 URL 수정( 적용된 단계 에서 소스 선택).
• 이 Excel 테이블의 데이터에 대해 가져올 원본 테이블 편집( 적용된 단계에서 탐색 선택).
• 시트에 데이터를 배치하기 전에 Excel에서 데이터 형식을 지정하거나 변환하는 방법 변경(적용 단계에서 변경된 유형 선택).

즉, 웹 쿼리가 원래 작동하는 방식이 마음에 들지 않더라도 웹 쿼리 편집기 창을 사용하여 사용자 지정할 수 있습니다.

수동 및 자동으로 테이블을 새로 고치는 방법

원격 웹 페이지 테이블의 최신 데이터를 Excel 시트로 가져올 때 몇 가지 옵션이 있습니다.

새로 고칠 테이블이 있는 시트를 선택하고 데이터 메뉴를 선택한 다음 리본에서 모두 새로 고침을 선택하여 수동으로 새로 고칠 수 있습니다 .

이렇게 하면 외부 소스에서 데이터를 검색하는 해당 시트의 모든 테이블에 대해 일회성 새로 고침이 수행됩니다.

이를 수행하는 보다 자동화된 방법은 새로 고침을 예약하는 것입니다. 이렇게 하려면 모두 새로 고침 단추 아래쪽에 있는 드롭다운 화살표를 선택하고 연결 속성 을 선택합니다 .

그러면 쿼리 속성 창이 열립니다. 사용 탭을 선택하고 xx분마다 새로 고침 을 활성화 합니다.

시트의 표를 새로 고칠 빈도로 시간 설정을 변경합니다. 이 자동 새로 고침은 Excel 파일이 열려 있는 경우에만 발생합니다. 그렇지 않으면 파일 내부의 데이터를 새로 고치지 않습니다.

Excel의 웹 스크래핑

Excel로 웹에서 데이터를 스크랩하는 기능은 강력합니다. 이 기능을 사용하고 자동화하여 웹 전체에서 최신 데이터로 업데이트되는 실시간 대시보드를 생성할 수 있습니다. 관심 있는 정보로 자주 업데이트되는 여러 웹사이트의 데이터 테이블을 가져와 중앙 스프레드시트로 가져옵니다.

그런 다음 자체 업데이트되는 해당 데이터를 기반으로 스프레드시트에서 계산, 시각적 개체 및 전체 대시보드를 만들 수 있습니다.