이후 자동 업데이트를 통해 인터넷에서 Excel로 데이터를 가져오는 방법을 반복적으로 분석했습니다. 특히:
- 이전 버전의 Excel 2007-2013에서는 직접 웹 요청으로 이 작업을 수행할 수 있었습니다.
- 2010년부터는 파워 쿼리 추가 기능을 사용하여 매우 편리하게 이 작업을 수행할 수 있습니다.
최신 버전의 Microsoft Excel에 있는 이러한 방법에 이제 내장 기능을 사용하여 인터넷에서 XML 형식으로 데이터를 가져오는 다른 방법을 추가할 수 있습니다.
XML(eXtensible Markup Language = Extensible Markup Language)은 모든 종류의 데이터를 설명하도록 설계된 범용 언어입니다. 실제로는 일반 텍스트이지만 데이터 구조를 표시하기 위해 특수 태그가 추가됩니다. 많은 사이트에서 누구나 다운로드할 수 있는 XML 형식의 무료 데이터 스트림을 제공합니다. 우리 나라 중앙 은행 (www.cbr.ru) 웹 사이트, 특히 유사한 기술의 도움으로 다양한 통화의 환율에 대한 데이터가 제공됩니다. 모스크바 거래소 웹사이트(www.moex.com)에서 주식, 채권 및 기타 유용한 정보에 대한 시세를 같은 방식으로 다운로드할 수 있습니다.
버전 2013부터 Excel에는 인터넷에서 워크시트 셀로 XML 데이터를 직접 로드하는 두 가지 기능이 있습니다. 웹 서비스 (웹 서비스) и 필터.XML (필터 XML). 그들은 쌍으로 작동합니다 – 먼저 기능 웹 서비스 원하는 사이트에 대한 요청을 실행하고 XML 형식으로 응답을 반환한 다음 함수를 사용합니다. 필터.XML 우리는 이 답변을 구성 요소로 "파싱"하여 필요한 데이터를 추출합니다.
우리 나라 중앙 은행 웹 사이트에서 주어진 날짜 간격에 필요한 모든 통화의 환율을 가져오는 고전적인 예를 사용하여 이러한 기능의 작동을 살펴보겠습니다. 다음 구성을 공백으로 사용합니다.
이리:
- 노란색 셀에는 관심 기간의 시작 날짜와 종료 날짜가 포함되어 있습니다.
- 파란색에는 명령을 사용하는 통화 드롭다운 목록이 있습니다. 데이터 – 검증 – 목록 (데이터 — 검증 — 목록).
- 녹색 셀에서 함수를 사용하여 쿼리 문자열을 만들고 서버의 응답을 받습니다.
- 오른쪽 표는 통화 코드에 대한 참조입니다(조금 후에 필요합니다).
가자!
1단계. 쿼리 문자열 구성
사이트에서 필요한 정보를 얻으려면 올바르게 요청해야 합니다. 우리는 www.cbr.ru로 이동하여 메인 페이지의 바닥글에 있는 링크를 엽니다' 기술 자료'- XML을 사용하여 데이터 가져오기 (http://cbr.ru/development/SXML/). 조금 더 아래로 스크롤하면 두 번째 예(예제 2)에서 필요한 것이 있을 것입니다. 즉, 주어진 날짜 간격에 대한 환율을 가져옵니다.
예제에서 볼 수 있듯이 쿼리 문자열에는 시작 날짜(날짜_요구1) 및 엔딩(날짜_요구2) 당사의 관심 기간 및 통화 코드(VAL_NM_RQ), 우리가 얻고자 하는 비율. 아래 표에서 주요 통화 코드를 찾을 수 있습니다.
환율 | 암호 | | 환율 | 암호 |
| 호주 달러 | R01010 | 리투아니아 리 타스 | R01435 | |
오스트리아 실링 | R01015 | 리투아니아 쿠폰 | R01435 | |
아제르바이잔 마낫 | R01020 | 몰도바누 | R01500 | |
파운드 | R01035 | РќРμРјРμС † РєР ° СЏ РјР ° СЂРєР ° | R01510 | |
앙골라 뉴 콴자 | R01040 | 네덜란드 길더 | R01523 | |
아르메니아 드람 | R01060 | 노르웨이 크로네 | R01535 | |
벨로루시 루블 | R01090 | 폴란드 즐 로티 | R01565 | |
벨기에 프랑 | R01095 | 포르투갈 에스쿠도 | R01570 | |
불가리아 사자 | R01100 | 루마니아 류 | R01585 | |
브라질 진짜 | R01115 | 싱가포르 달러 | R01625 | |
헝가리 포린트 | R01135 | 수리남 달러 | R01665 | |
홍콩 달러 | R01200 | 타직소모니 | R01670 | |
그리스 드라크마 | R01205 | 타직 루블 | R01670 | |
덴마크 크로네 | R01215 | 터키 리라 | R01700 | |
미국 달러 | R01235 | 투르크멘 마나 트 | R01710 | |
유로 | R01239 | 새로운 투르크멘 마나트 | R01710 | |
인도 루피 | R01270 | 우즈베키스탄 화폐 | R01717 | |
아일랜드 파운드 | R01305 | 우크라이나의 그리브 나 | R01720 | |
아이슬란드 크로네 | R01310 | 우크라이나 카르보바네츠 | R01720 | |
스페인 페세타 | R01315 | 핀란드 마크 | R01740 | |
이탈리아 리라 | R01325 | 프랑스 프랑 | R01750 | |
카자흐스탄 텡게 | R01335 | 체코 코 루나 | R01760 | |
캐나다 달러 | R01350 | 스웨덴 크로나 | R01770 | |
키르기스스탄 솜 | R01370 | 스위스 프랑 | R01775 | |
중국 위안 | R01375 | 에스토니아 크룬 | R01795 | |
쿠웨이트 디나르 | R01390 | 유고슬라비아 뉴 디나르 | R01804 | |
라트비아어 | R01405 | 남아프리카 랜드 | R01810 | |
레바논 파운드 | R01420 | 대한민국 원 | R01815 | |
일본 엔 | R01820 |
통화 코드에 대한 전체 가이드는 중앙 은행 웹사이트에서도 볼 수 있습니다. http://cbr.ru/scripts/XML_val.asp?d=0 참조
이제 다음을 사용하여 시트의 셀에 쿼리 문자열을 구성합니다.
- 텍스트 연결 연산자(&)를 사용하여 결합합니다.
- 특징 VPR (조회)디렉토리에서 필요한 통화 코드를 찾으려면
- 특징 TEXT (본문), 슬래시를 통해 주어진 패턴 일-월-년에 따라 날짜를 변환합니다.
="http://cbr.ru/scripts/XML_dynamic.asp?date_req1="&ТЕКСТ(B2;"ДД/ММ/ГГГГ")& "&date_req2="&ТЕКСТ(B3;"ДД/ММ/ГГГГ")&"&VAL_NM_RQ="&ВПР(B4;M:N;2;0)
2단계. 요청 실행
이제 우리는 기능을 사용합니다 웹 서비스 (웹 서비스) 생성된 쿼리 문자열을 유일한 인수로 사용합니다. 대답은 긴 XML 코드 줄입니다(전체 내용을 보려면 줄 바꿈을 켜고 셀 크기를 늘리는 것이 좋습니다).
3단계. 답변 파싱
응답 데이터의 구조를 더 쉽게 이해하려면 온라인 XML 파서(예: http://xpather.com/ 또는 https://jsonformatter.org/xml-parser) 중 하나를 사용하는 것이 좋습니다. XML 코드의 형식을 시각적으로 지정하고 들여쓰기를 추가하고 구문을 색상으로 강조 표시할 수 있습니다. 그러면 모든 것이 훨씬 더 명확해질 것입니다.
이제 코스 값이 태그로 구성되어 있음을 분명히 알 수 있습니다.
그것들을 추출하려면 시트에서 10개(또는 그 이상 – 여백이 있는 경우)의 빈 셀을 선택하고(XNUMX일 날짜 간격이 설정되었기 때문에) 수식 입력줄에 함수를 입력합니다. 필터.XML (필터XML):
여기서 첫 번째 인수는 서버 응답(B8)이 있는 셀에 대한 링크이고 두 번째 인수는 필요한 XML 코드 조각에 액세스하고 추출하는 데 사용할 수 있는 특수 언어인 XPath의 쿼리 문자열입니다. 예를 들어 여기에서 XPath 언어에 대한 자세한 내용을 읽을 수 있습니다.
수식을 입력한 후 키를 누르지 않는 것이 중요합니다. 엔터 버튼, 및 키보드 단축키 Ctrl 키+변화+엔터 버튼, 즉 배열 수식으로 입력합니다(중괄호 주위가 자동으로 추가됨). Excel에서 동적 배열을 지원하는 최신 버전의 Office 365가 있는 경우 간단한 엔터 버튼, 그리고 미리 빈 셀을 선택할 필요가 없습니다. 함수 자체는 필요한 만큼의 셀을 사용합니다.
날짜를 추출하기 위해 동일한 작업을 수행합니다. 인접한 열에서 여러 개의 빈 셀을 선택하고 동일한 기능을 사용하지만 다른 XPath 쿼리를 사용하여 Record 태그에서 Date 속성의 모든 값을 가져옵니다.
=FILTER.XML(B8;”//레코드/@날짜”)
이제 미래에 원래 셀 B2 및 B3의 날짜를 변경하거나 B3 셀의 드롭다운 목록에서 다른 통화를 선택하면 중앙 은행 서버에서 새 데이터를 참조하여 쿼리가 자동으로 업데이트됩니다. 수동으로 업데이트를 강제 실행하려면 키보드 단축키를 추가로 사용할 수 있습니다. Ctrl 키+다른+F9.
- 파워 쿼리를 통해 비트코인 환율을 Excel로 가져오기
- 이전 버전의 Excel에서 인터넷에서 환율 가져오기