자동 업데이트를 사용하여 인터넷에서 주어진 통화의 환율을 가져오는 것은 많은 Microsoft Excel 사용자에게 매우 일반적인 작업입니다. 매일 아침 환율에 따라 다시 계산해야 하는 가격 목록이 있다고 상상해 보십시오. 또는 프로젝트 예산. 또는 계약 체결일의 달러 환율을 사용하여 계산해야 하는 계약 비용.
이러한 상황에서는 다양한 방법으로 문제를 해결할 수 있습니다. 모든 문제는 설치한 Excel 버전과 그 위에 있는 추가 기능에 따라 다릅니다.
방법 1: 현재 환율에 대한 간단한 웹 요청
이 방법은 컴퓨터에 여전히 이전 버전의 Microsoft Office 2003-2007이 있는 사용자에게 적합합니다. 타사 애드온이나 매크로를 사용하지 않으며 내장 기능에서만 작동합니다.
버튼을 클릭 인터넷에서 (편물) 탭 Data (데이트). 나타나는 창에서 줄에 주소 (주소) 정보를 가져올 사이트의 URL(예: http://www.finmarket.ru/currency/rates/)을 입력하고 키를 누릅니다. 엔터 버튼.
페이지가 로드되면 Excel에서 가져올 수 있는 테이블에 검은색 및 노란색 화살표가 나타납니다. 이러한 화살표를 클릭하면 가져올 테이블이 표시됩니다.
필요한 테이블이 모두 표시되면 버튼을 클릭하십시오. 수입 (수입) 창 하단에. 데이터를 로드하는 데 필요한 시간이 지나면 표시된 표의 내용이 시트의 셀에 나타납니다.
추가 사용자 지정을 위해 이러한 셀을 마우스 오른쪽 버튼으로 클릭하고 상황에 맞는 메뉴에서 명령을 선택할 수 있습니다. 범위 속성 (데이터 범위 속성).이 대화 상자에서 원하는 경우 업데이트 빈도 및 기타 매개변수를 구성할 수 있습니다.
주식 시세는 몇 분마다 변경되므로 더 자주 업데이트할 수 있습니다(확인란 N분마다 새로 고침합니다.), 하지만 환율은 대부분의 경우 하루에 한 번 업데이트하면 충분합니다(체크박스 파일 열기 시 업데이트).
가져온 전체 데이터 범위는 Excel에서 단일 단위로 처리되고 탭의 이름 관리자에서 볼 수 있는 고유한 이름이 지정됩니다. 공식 (수식 — 이름 관리자).
방법 2: 지정된 날짜 범위의 환율을 가져오는 매개변수 웹 쿼리
이 방법은 약간 현대화된 첫 번째 옵션이며 사용자에게 현재 날짜뿐만 아니라 관심 있는 다른 날짜 또는 날짜 간격에 대한 원하는 통화의 환율을 받을 수 있는 기회를 제공합니다. 이렇게 하려면 웹 요청을 매개변수로 변환해야 합니다. 즉, 두 개의 명확한 매개변수(필요한 통화 코드와 현재 날짜)를 추가해야 합니다. 이를 위해 다음을 수행합니다.
1. 코스 아카이브가있는 우리 나라 중앙 은행 웹 사이트 페이지에 대한 웹 요청 (방법 1 참조)을 만듭니다. http://cbr.ru/currency_base/dynamics.aspx
2. 왼쪽 양식에서 원하는 통화를 선택하고 시작 및 종료 날짜를 설정합니다.
3. 버튼을 클릭 데이터를 얻으려면 몇 초 후에 주어진 날짜 간격에 필요한 과정 값이 포함된 테이블이 표시됩니다. 결과 테이블을 아래로 스크롤하고 웹 페이지의 왼쪽 하단 모서리에 있는 검은색 및 노란색 화살표를 클릭하여 가져오기용으로 표시합니다(이 화살표가 테이블 옆에 있고 테이블 옆에 없는 이유는 묻지 마십시오. 사이트 디자이너에게 질문).
이제 창의 오른쪽 상단 모서리에 플로피 디스크가 있는 버튼을 찾고 있습니다. 요청 저장 (쿼리 저장) 요청 매개변수가 있는 파일을 편리한 이름으로 적절한 폴더에 저장합니다. 예를 들어 내 문서 이름 아래 cbr. 아이큐. 그 후 웹 쿼리 창과 모든 Excel을 지금은 닫을 수 있습니다.
4. 요청을 저장한 폴더를 열고 요청 파일을 찾습니다. cbr. 아이큐, 그런 다음 마우스 오른쪽 버튼으로 클릭하십시오. 연결 프로그램 – 메모장 (또는 목록에서 선택 - 일반적으로 파일 notepad.exe를 폴더에서 C : 윈도우). 메모장에서 요청 파일을 열면 다음과 같은 내용이 표시되어야 합니다.
여기서 가장 중요한 것은 주소와 쿼리 매개변수가 포함된 줄입니다. 필요한 통화 코드(빨간색으로 강조 표시됨)와 종료 날짜를 오늘 날짜로 대체합니다. 푸른). 다음을 얻으려면 줄을 조심스럽게 편집하십시오.
http://cbr.ru/currency_base/dynamics.aspx?VAL_NM_RQ=["통화 코드"]&date_req1=01.01.2000&r1=1&date_req2=["날짜"]&rt=1&모드=1
나머지는 그대로 두고 파일을 저장하고 닫습니다.
5. Excel에서 새 책을 만들고 중앙 은행 환율 아카이브를 가져올 시트를 엽니다. 적절한 셀에 현재 날짜를 제공하는 수식을 입력하십시오. 텍스트 형식으로 쿼리 대체:
=TEXT(오늘();”DD.MM.YYYY”)
또는 영어 버전
=TEXT(오늘(),»dd.mm.yyyy»)
근처 어딘가에 테이블에서 필요한 통화 코드를 입력합니다.
환율 | 암호 |
미국 달러 | R01235 |
유로 | R01239 |
파운드 | R01035 |
일본 엔 | R01820 |
필요한 코드는 중앙 은행 웹 사이트의 쿼리 문자열에서 직접 확인할 수도 있습니다.
6. 생성된 셀과 cbr.iqy 파일을 기본으로 사용하여 시트에 데이터를 로드합니다. 즉, 탭으로 이동합니다. 데이터 – 연결 – 다른 사람 찾기 (데이터 — 기존 연결). 열리는 데이터 소스 선택 창에서 파일을 찾아 엽니다. cbr. 아이큐. 가져오기 전에 Excel에서 세 가지 사항을 명확하게 설명합니다.
먼저 데이터 테이블을 가져올 위치:
둘째, 통화 코드를 얻을 수 있는 위치(상자를 선택할 수 있습니다. 이 기본값 사용 (향후 새로 고침을 위해 이 값/참조 사용), 나중에 업데이트 중에 이 셀이 지정되지 않을 때마다 및 확인란 셀 값이 변경되면 자동으로 업데이트 (셀 값이 변경되면 자동으로 새로 고침):
셋째, 종료 날짜를 가져올 셀(내일 업데이트 시 이러한 매개변수를 수동으로 설정할 필요가 없도록 여기에서 두 상자를 모두 선택할 수도 있습니다):
OK, 몇 초 기다렸다가 시트에서 원하는 통화의 환율에 대한 완전한 아카이브를 얻으십시오.
첫 번째 방법과 마찬가지로 가져온 데이터를 마우스 오른쪽 버튼으로 클릭하고 명령을 선택하여 범위 속성 (데이터 범위 속성), 재생 빈도를 조정할 수 있습니다 파일을 열 때 (파일을 열 때 새로 고침). 그런 다음 인터넷에 액세스할 수 있는 경우 데이터가 매일 자동으로 업데이트됩니다. 즉, 테이블이 새 데이터로 자동 업데이트됩니다.
함수를 사용하여 테이블에서 원하는 날짜의 환율을 추출하는 것이 가장 쉽습니다. VPR (조회) – 익숙하지 않은 경우 이렇게 하는 것이 좋습니다. 예를 들어 이러한 공식을 사용하면 다음 표에서 10년 2000월 XNUMX일의 달러 환율을 선택할 수 있습니다.
또는 영어로 =VLOOKUP(E5,cbr,3,1)
어디에
- E5 – 주어진 날짜를 포함하는 셀
- CBR – 데이터 범위의 이름(가져오는 동안 자동으로 생성되며 일반적으로 쿼리 파일의 이름과 동일)
- 3 – 데이터를 가져오는 테이블의 열 일련 번호
- 1 – VLOOKUP 함수에 대한 대략적인 검색을 포함하는 인수로 A 열에 실제로 존재하지 않는 중간 날짜에 대한 과정을 찾을 수 있습니다(가장 가까운 이전 날짜 및 해당 과정이 수강됨). 여기에서 VLOOKUP 기능을 사용한 대략적인 검색에 대해 자세히 알아볼 수 있습니다.
- 현재 셀의 지정된 날짜에 대한 달러 환율을 가져오는 매크로
- 특정 날짜의 달러, 유로, 그리브냐, 파운드 등의 환율을 가져오는 PLEX 애드온 기능
- PLEX 애드온에서 모든 날짜의 환율을 삽입하십시오.