구글 드라이브 시트 사용법: 외부 웹페이지 테이블 데이타 표 가져오는 방법 - IMPORTHTML 함수 사용법
구글 드라이브 시트(sheets)에서 웹페이지에 있는 테이블 데이타를 가져오거나 연동시키려면, IMPORTHTML 함수를 사용하여 할 수 있습니다.
IMPORTHTML 함수는 데이타를 연동(聯動)시키는 것이므로, 원본 웹페이지 테이블의 셀 값(수자, 문자 등)이 변경되면, 시트의 셀 값도 그에 따라 자동으로 수정됩니다. 변경된 데이타가 반영되는 데는 다소 시간이 걸립니다. 정확한 것은 알 수 없지만, 경험상으로 보면, 길면 20분 정도 걸리고, 보통은 5분 안쪽인 것 같습니다.
이 방법은 매우 편리한 방법인 반면에, 해당 웹페이지가 변경되어 테이블의 순서가 바뀌면, 제대로 작동하지 않게 되는 단점이 있습니다. 웹페이지는 수시로 변경.개선 되는 경우가 많습니다.
IMPORTHTML 함수의 사용법은, 테이블 데이타를 가져올 웹페이지 주소(URL)와 테이블 번호를 지정해 주면 되므로, 간단합니다.
테이블 번호를 찾는 방법은 정형화 된 것은 없는 것 같습니다. 하나의 방법이라고 한다면,
>> 해당 웹페이지에서, 마우스 오른쪽 버튼을 눌러, '페이지 소스보기' 를 선택하여, HTML 코드 페이지를 엽니다. 그런 다음에,
>> 키보드에서 [컨트롤 Ctrl + F]키를 치면, 현재 페이지에서 찾기 검색창이 열립니다.
>> 검색창에 table를 입력하여 찾아 보면, 페이지 안에 테이블이 몇 개 들어 있는지 알 수 있습니다. 들어 있는 테이블 수에 비추어, 가져 오려는 테이블은 몇 번째 쯤 테이블이겠구나 하고 추정하여, IMPORTHTML 함수에 테이블 번호를 입력해 보면서, 원하는 테이블을 찾으면 됩니다.
주의 할 것은, 시트에서 함수를 입력한 셀 오른쪽과 아래쪽에 충분한 공간이 있어야 합니다. 그렇지 않으면, 가져온 데이타를 표시할 수 없으므로 오류가 납니다. 예컨데, 가져 올 행이 5개라면, IMPORTHTML 함수를 입력한 셀의 아래 쪽에 적어도 5개의 빈 행이 있어야 합니다. 열의 경우에도 마찬가지 입니다.
QUERY 함수를 함께 사용하면, 특정한 열, 특정한 행만을 가져올 수도 있습니다. 아래의 예제를 보면, 누구나 쉽게 할 수 있을 것입니다.
참고: 아래의 예제에서 사용한 원본 데이타 웹페이지 보기 >>
참고: 아래 예제 이미지의 실제 구글 드라이브 시트 보기 >>
[ IMPORTHTML 함수의 기본 구조]
예제 1 : 특정 테이블을 통째로 가져오기 - 첫 번째 테이블 통째로 가져오기
[기본 구조]
[실제 적용한 예제]
[위 예제를 실제로 적용한 모습]
예제 2 : 특정한 열만 가져오기: 첫 번째 표의 A열(1열)과 C열(3열)만 가져오기
[기본 구조]
ㅇ 가져 올 열을 콤마(,)로 구분하여 나열함: Col1,Col2,Col5 ....
[실제 적용한 예제]
[위 예제를 실제로 적용한 모습]
예제 3 : 특정한 행만 가져오기: 첫 번째 표의 첫 행부터 2개의 행만 가져오기
[기본 구조]
ㅇ Limit - 몇 개의 행을 가져올지를 지정
ㅇ Offset - 제외 할 행 개수, 첫 행부터 몇 개의 행을 제외할 것인지 지정
ㅇ limit [리미트] 제한, 한계 ㅇ offset [오프 셑] 상쇄
[실제 적용한 예제]
[위 예제를 실제로 적용한 모습]
예제 4 : 특정한 행만 가져오기 2 : 첫 번째 표의 3행부터 5개 행만 가져오기
[기본 구조]
[실제 적용한 예제]
[위 예제를 실제로 적용한 모습]
예제 5 : 특정한 열의 특정한 행만 가져오기: 표의 A열(1열)과 E열(5열), 그리고 첫 행부터 3개의 행만 가져오기
[기본 구조]
[실제 적용한 예제]
[위 예제를 실제로 적용한 모습]
웹페이지에서 테이블 데이타를 가져오면, 예컨대 [999원]과 같이 데이타에 문자가 포함되어 있는 경우가 있습니다. 데이타에 문자가 포함되어 있으면, 합계(sum) 평균(average) ... 등과 같은 함수의 계산이 되지 않습니다.
이럴 경우에는, 일단, 웹페이지에서 데이타를 가져온 다음, 포함되어 있는 문자를 제거하고, 계산을 하면 됩니다. 데이타에 포함된 문자를 제거하는 방법은 아래 링크 페이지에 설명되어 있습니다.
>> 구글 드라이브 시트 사용법: 데이타에 포함된 문자를 제거하고, 수자만 가져오는 방법 : VALUE 함수 + SUBSTITUTE 함수
>> 구글 드라이브 (Google Drive) 사용법 : 글 목록
IMPORTHTML 함수는 데이타를 연동(聯動)시키는 것이므로, 원본 웹페이지 테이블의 셀 값(수자, 문자 등)이 변경되면, 시트의 셀 값도 그에 따라 자동으로 수정됩니다. 변경된 데이타가 반영되는 데는 다소 시간이 걸립니다. 정확한 것은 알 수 없지만, 경험상으로 보면, 길면 20분 정도 걸리고, 보통은 5분 안쪽인 것 같습니다.
이 방법은 매우 편리한 방법인 반면에, 해당 웹페이지가 변경되어 테이블의 순서가 바뀌면, 제대로 작동하지 않게 되는 단점이 있습니다. 웹페이지는 수시로 변경.개선 되는 경우가 많습니다.
IMPORTHTML 함수의 사용법은, 테이블 데이타를 가져올 웹페이지 주소(URL)와 테이블 번호를 지정해 주면 되므로, 간단합니다.
테이블 번호를 찾는 방법은 정형화 된 것은 없는 것 같습니다. 하나의 방법이라고 한다면,
>> 해당 웹페이지에서, 마우스 오른쪽 버튼을 눌러, '페이지 소스보기' 를 선택하여, HTML 코드 페이지를 엽니다. 그런 다음에,
>> 키보드에서 [컨트롤 Ctrl + F]키를 치면, 현재 페이지에서 찾기 검색창이 열립니다.
>> 검색창에 table를 입력하여 찾아 보면, 페이지 안에 테이블이 몇 개 들어 있는지 알 수 있습니다. 들어 있는 테이블 수에 비추어, 가져 오려는 테이블은 몇 번째 쯤 테이블이겠구나 하고 추정하여, IMPORTHTML 함수에 테이블 번호를 입력해 보면서, 원하는 테이블을 찾으면 됩니다.
주의 할 것은, 시트에서 함수를 입력한 셀 오른쪽과 아래쪽에 충분한 공간이 있어야 합니다. 그렇지 않으면, 가져온 데이타를 표시할 수 없으므로 오류가 납니다. 예컨데, 가져 올 행이 5개라면, IMPORTHTML 함수를 입력한 셀의 아래 쪽에 적어도 5개의 빈 행이 있어야 합니다. 열의 경우에도 마찬가지 입니다.
QUERY 함수를 함께 사용하면, 특정한 열, 특정한 행만을 가져올 수도 있습니다. 아래의 예제를 보면, 누구나 쉽게 할 수 있을 것입니다.
참고: 아래의 예제에서 사용한 원본 데이타 웹페이지 보기 >>
참고: 아래 예제 이미지의 실제 구글 드라이브 시트 보기 >>
[ IMPORTHTML 함수의 기본 구조]
IMPORTHTML(웹페이지 주소 URL, 테이블 번호)
예제 1 : 특정 테이블을 통째로 가져오기 - 첫 번째 테이블 통째로 가져오기
[기본 구조]
=IMPORTHTML("https://ojji.wayful.com ..... Table.html","table",1)
[실제 적용한 예제]
=IMPORTHTML("https://ojji.wayful.com/2018/03/Google-Drive-Sheet-How-to-Import-and-Display-Web-Data-and-Table.html","table",1)
[위 예제를 실제로 적용한 모습]
예제 2 : 특정한 열만 가져오기: 첫 번째 표의 A열(1열)과 C열(3열)만 가져오기
[기본 구조]
=QUERY(IMPORTHTML("https://ojji.wayful.com/...Table.html","table",1),"Select
Col1,Col3")
참고: col = column (세로 열)ㅇ 가져 올 열을 콤마(,)로 구분하여 나열함: Col1,Col2,Col5 ....
[실제 적용한 예제]
=QUERY(IMPORTHTML("https://ojji.wayful.com/2018/03/Google-Drive-Sheet-How-to-Import-and-Display-Web-Data-and-Table.html","table",1),"Select Col1,Col3")
[위 예제를 실제로 적용한 모습]
예제 3 : 특정한 행만 가져오기: 첫 번째 표의 첫 행부터 2개의 행만 가져오기
[기본 구조]
=QUERY(IMPORTHTML("https://ojji.wayful.com/...Table.html","table",1),"LIMIT
2 OFFSET 0")
참고: ㅇ 행을 헤아릴 때, 항목 이름이 들어 있는 첫 번째 행은 포함되지 않습니다.ㅇ Limit - 몇 개의 행을 가져올지를 지정
ㅇ Offset - 제외 할 행 개수, 첫 행부터 몇 개의 행을 제외할 것인지 지정
ㅇ limit [리미트] 제한, 한계 ㅇ offset [오프 셑] 상쇄
[실제 적용한 예제]
=QUERY(IMPORTHTML("https://ojji.wayful.com/2018/03/Google-Drive-Sheet-How-to-Import-and-Display-Web-Data-and-Table.html","table",1),"LIMIT 2 OFFSET 0")
[위 예제를 실제로 적용한 모습]
예제 4 : 특정한 행만 가져오기 2 : 첫 번째 표의 3행부터 5개 행만 가져오기
[기본 구조]
=QUERY(IMPORTHTML("https://ojji.wayful.com/...Table.html","table",1),"LIMIT
5 OFFSET 2")
[실제 적용한 예제]
=QUERY(IMPORTHTML("https://ojji.wayful.com/2018/03/Google-Drive-Sheet-How-to-Import-and-Display-Web-Data-and-Table.html","table",1),"LIMIT 5 OFFSET 2")
[위 예제를 실제로 적용한 모습]
예제 5 : 특정한 열의 특정한 행만 가져오기: 표의 A열(1열)과 E열(5열), 그리고 첫 행부터 3개의 행만 가져오기
[기본 구조]
=QUERY(IMPORTHTML("https://ojji.wayful.com/...Table.html","table",1),"Select
Col1,Col5 LIMIT 3 OFFSET 0")
[실제 적용한 예제]
=QUERY(IMPORTHTML("https://ojji.wayful.com/2018/03/Google-Drive-Sheet-How-to-Import-and-Display-Web-Data-and-Table.html","table",1),"Select Col1,Col5 LIMIT 3 OFFSET 0")
[위 예제를 실제로 적용한 모습]
웹페이지에서 테이블 데이타를 가져오면, 예컨대 [999원]과 같이 데이타에 문자가 포함되어 있는 경우가 있습니다. 데이타에 문자가 포함되어 있으면, 합계(sum) 평균(average) ... 등과 같은 함수의 계산이 되지 않습니다.
이럴 경우에는, 일단, 웹페이지에서 데이타를 가져온 다음, 포함되어 있는 문자를 제거하고, 계산을 하면 됩니다. 데이타에 포함된 문자를 제거하는 방법은 아래 링크 페이지에 설명되어 있습니다.
>> 구글 드라이브 시트 사용법: 데이타에 포함된 문자를 제거하고, 수자만 가져오는 방법 : VALUE 함수 + SUBSTITUTE 함수
>> 구글 드라이브 (Google Drive) 사용법 : 글 목록