엑셀 실시간 환율 조회, 기본 함수 3개로 해결하기
엑셀 환율 조회 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- 엑셀 모든 버전에서 사용 가능! 환율 조회 함수 만들기예제파일엑셀 모든 버전 가능! 환율 조회 함수 만들기 - 완성파일완성파일
라이브 강의 전체영상도 함께 확인해보세요!
위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.
강의 관련 자료 빠르게 살펴보기
외국한 중개소 환욜 조회 페이지 링크
'외국환 중개소' 환율 조회 페이지는 아래 링크를 클릭해서 이동하거나, 구글/네이버에서 '외국환 중개소' 검색 → '환율 조회' → '오늘의 환율' 로 이동하면 됩니다.
M365 버전 : ExchangeRate 함수
M365 최신 버전에서 바로 사용할 수 있는 ExchangeRate 사용자 함수는 아래 링크를 확인하세요! 함수 사용법은 영상 14분 15초에 꼼꼼하게 정리했습니다.
=LAMBDA(날짜,[통화],[통화표시], LET(date,날짜, curr,통화, print_header,통화표시, is_header,OR(ISOMITTED(curr),curr="",N(print_header)), curr_list,{"USD";"JPY";"EUR";"GBP";"CAD";"CHF";"AUD";"NZD";"CNH";"HKD";"TWD";"MNT";"KZT";"THB";"SGD";"IDR";"MYR";"PHP";"VND";"BND";"INR";"PKR";"BDT";"KHR";"MOP";"NPR";"LKR";"UZS";"MMK";"MXN";"BRL";"ARS";"CLP";"COP";"SEK";"DKK";"NOK";"RUB";"HUF";"PLN";"CZK";"RON";"SAR";"QAR";"ILS";"JOD";"KWD";"BHD";"AED";"TRY";"OMR";"ZAR";"EGP";"KES";"LYD";"ETB";"FJD"}, url,"http://www.smbs.biz/Flash/TodayExRate_flash.jsp?tr_date="&TEXT(date,"yyyy-mm-dd"), print_list,IF(ISOMITTED(curr),curr_list,curr), result,SUBSTITUTE(WEBSERVICE(url),"?test0=test&",""), tocol_result,DROP(TEXTSPLIT(result,,"&&"),-1), pos_1,FIND("&",tocol_result), pos_2,FIND("&",tocol_result,pos_1+1), fin_result,MID(tocol_result,pos_1+1,pos_2-pos_1-1), arr_result,TEXTSPLIT(TEXTJOIN("|",,fin_result),"=","|"), arr_value,IFERROR(VLOOKUP(print_list,arr_result,2,0),"-"), IF(is_header,HSTACK(print_list,arr_value),arr_value) ) )환율 조회에 사용할 엑셀 기초 함수 살펴보기
- WEBSERVICE 함수는 URL 주소에서 반환하는 웹 데이터를 반환합니다. 단, 주의사항으로 URL에서 반환된 데이터가 셀 안에 입력 가능한 최대 글자수인 32,767자를 초과할 경우 WEBSERVICE 함수는 #VALUE! 오류를 반환합니다.
=WEBSERVICE(URL주소)
=WEBSERVICE("http://www.smbs.biz/Flash/TodayExRate_flash.jsp?tr_date=2024-03-12")
→ 외국환중개소에서 제공하는 3월 12일의 고시환율 데이터를 반환합니다. - MID 함수는 문장의 특정 위치부터 원하는 문자수 만큼 단어를 추출합니다.
=MID(문장,시작위치,추출할문자수)
=MID("서울시 구로구 구로동",5,3)
→ "구로구"를 추출합니다. (5번째 위치부터 3글자를 추출) - SEARCH 함수는 문장에서 특정 단어의 시작 위치를 반환합니다. 시작위치를 지정하면, 시작 지점 이후에 첫번째로 나타난 단어의 위치를 반환합니다.
=SEARCH(단어,문장,[시작위치])
=SEARCH("사과","사과나무 사과열렸네",3)
→ 3번째 글자 이후 '사과'가 나타난 위치인 6을 반환합니다. - MID 함수와 SEARCH 함수를 함께 활용하면, 문장에서 특정 두 단어 사이에 있는 값을 편리하게 추출할 수 있습니다.
=MID(문장,SEARCH(단어1,문장),SEARCH(단어2,문장)-SEARCH(단어1,문장)-LEN(단어1))
'문장에서 단어1과 단어2 사이에 있는 단어를 추출합니다.
오빠두Tip : 위 공식의 자세한 동작원리는 영상 강의 06:57 이후 내용을 참고하세요!
외국환 중개소 웹 페이지 분석하기
- 네트워크 탭 이동하기 : 웹 브라우저에서 제공하는 개발자도구를 사용하면 간단하게 웹 페이지에서 발생하는 네트워크 정보를 확인하고 분석할 수 있습니다. 아래 링크를 클릭하여 외국환중개소 오늘의 환율 조회 페이지로 이동합니다.
- 브라우저에서 단축키 F12키 또는 Ctrl + Shift + i 를 동시에 누르거나 [설정] - [도구] - [개발자 도구]를 클릭하여 개발자 도구를 실행합니다. 이후 개발자 도구에서 '네트워크' 탭으로 이동합니다.
개발자도구를 실행한 후, 네트워크 탭으로 이동합니다. - 네트워크 탭을 실행한 상태로 F5키를 눌러 페이지를 새로고침하거나 [조회하기] 버튼을 클릭하여 페이지를 새로고침하면, 웹 페이지를 출력하기 위해 받아오는 모든 항목이 네트워크 탭에 표시됩니다.
페이지를 새로고침하면 현재 웹페이지를 출력하는 과정에 받아오는 모든 항목이 표시됩니다. - 환율 정보를 반환하는 URL 확인하기 : 우리에게 필요한 항목은 '데이터(값)' 입니다. 따라서, 표시할 항목에서 [Fetch/XHR] 을 클릭하면 웹 페이지를 출력하기 위해 받아온 데이터 중, XHR로 받아온 항목만 표시됩니다. 이후 첫번째 항목인 "TodayExRate..."를 클릭합니다.
표시할 항목 중, [Fetch/XHR]을 선택한 후, 첫번째 항목을 클릭합니다. - 이후 [미리보기] 탭을 클릭하면, 우리에게 필요한 환율 정보가 올바르게 반환되는 것을 확인할 수 있습니다.
미리보기 탭을 클릭해서 선택한 항목이 반환하는 데이터를 확인할 수 있습니다. - 마지막으로 [머리글]로 이동하면, 요청 URL에서 환율 정보를 반환하는 URL 주소를 확인할 수 있습니다.
http://www.smbs.biz/Flash/TodayExRate_flash.jsp?tr_date=날짜(YYYY-MM-DD)
[머리글] 탭으로 이동한 후, 요청 URL 주소를 확인합니다. 오빠두Tip : 초보자를 위한 API 기초 이론은 아래 10분 영상 강의에서 알기 쉽게 정리했습니다! 아직 보지 못하신 분은, 이전 영상 강의를 꼭 확인하세요!👇
엑셀 환율 조회 함수 작성하기
- URL에서 환율정보 받아오기 : 예제파일을 실행한 후 [기초] 시트로 이동합니다. 첫번째로 날짜를 입력하는 C7셀에 환율을 조회할 날짜를 입력합니다. 이번 강의에서는 2024-03-22 으로 입력했습니다.
환율을 조회할 날짜를 입력합니다. - 이후 C4 셀에는 아래 수식을 입력하여 환율 검색 URL을 완성합니다.
="http://www.smbs.biz/Flash/TodayExRate_flash.jsp?tr_date="&TEXT(C7,"YYYY-MM-DD")
입력한 날짜의 환율을 조회하는 URL 주소를 완성합니다. 오빠두Tip : TEXT 함수는 값을 특정 형식으로 표시하는 함수입니다. 실무에서 TEXT 함수를 활용하는 방법은 아래 기초 영상 강의를 참고하세요! - 이후 URL 결과 셀에 아래 수식을 입력하면 선택한 날짜의 환율 정보 데이터가 반환됩니다.
=WEBSERVICE(C4)
WEBSERVICE 함수로 외국환 중개소에서 제공하는 환율 정보를 받아옵니다. - 특정 통화의 환율 추출하기 : 이후 통화선택에서 검색하고자 하는 통화를 선택한 후, ②, ③, ④ 순서에 해당하는 C11,C12,C13셀에 아래 수식을 하나씩 작성합니다.
② 검색단어 (C11셀) : =C8&"="
③ 단어위치 (C12셀) : =SEARCH(C11,C5)
④ 다음&위치 (C13셀) : =SEARCH("&",C5,C12)
받아온 환율 정보에서 특정 통화의 환율을 추출하기 위해, 각 단계별로 수식을 작성합니다. - 마지막으로 C9셀을 선택한 후, 아래 수식을 입력하면 선택한 통화의 환율이 추출됩니다.
C9셀에 완성된 수식을 입력하면 선택한 통화의 환율이 추출됩니다. 오빠두Tip : 통화의 증가/감소 여부를 확인 후, 조건부서식을 적용하는 방법은 위캔두 멤버십 라이브 전체 영상을 확인하세요!👇
(M365) ExchangeRate 함수 사용법
- EXCHANGERATE 함수 등록하기 : M365 최신 버전 사용자는 LAMBDA 함수를 활용하면 더욱 편리하게 환율을 조회하고 자동화 서식을 만들 수 있습니다. 예제파일에서 [M365] 시트로 이동한 후, 오른쪽에 정리해드린 LAMBDA 함수 수식을 복사합니다.
예제파일에서 M365 시트로 이동한 후, LAMBDA 함수를 복사합니다. - [수식] 탭 - [이름관리자]를 클릭하거나 Ctrl + F3을 동시에 눌러서 이름관리자를 실행한 후, [새로 만들기] 버튼을 클릭합니다.
이름관리자를 실행한 후, [새로만들기]로 새 이름을 등록합니다. - [새 이름] 대화상자가 실행되면, 다음과 같이 새 이름을 정의 후 [확인] 버튼을 클릭합니다.
이름 : EXCHANGERATE
설명 : 외국환 중개소에서 제공하는 환율 정보를 반환합니다.
참조 대상 : 복사한 수식 붙여넣기
참조 대상으로 복사한 함수를 붙여넣기 한 후, EXCHANGERATE 함수를 등록합니다. - EXCHANGERATE 함수 사용하기 : 이제 날짜 범위에 환율을 조회하고자 하는 날짜를 입력한 후, C3셀에 아래와 같이 EXCHANGERATE 함수를 작성합니다.
=EXCHANGERATE(B3,$C$2:$H$2)
이제 파일에서 EXCHANGERATE 함수를 바로 사용할 수 있습니다. - 작성한 수식을 아래로 자동채우기하면, 여러 날짜의 환율이 한 번에 조회됩니다.
수식을 자동채우기해서 여러 날짜의 환율을 한 번에 조회합니다.
주말/공휴일일 시, 전날 환율을 표시하려면 URL을 생성하는 공식에 주말/공휴일 조건을 추가해보세요.
=URL&TEXT(C7,"YYYY-MM-DD") 이 부분을
=URL&TEXT(IF(WEEKDAY(C7,2)>=6,C7-WEEKDAY(C7,2)+5,C7),"YYYY-MM-DD")
로 수정해보시길 바랍니다. 공휴일 체크는 VLOOKUP 함수를 사용하면 됩니다.
공휴일을 체크하는 방법은 아래 자동화 달력 만들기 강의를 한번 참고해서 적용해보세요.
엑셀 달력 자동화, 공휴일+일정까지 완벽 해결! | CALENDAR 함수 만들기 - 오빠두엑셀 (oppadu.com)
남겨드린 답변이 문제를 해결하시는데 도움이 되었길 바랍니다. 감사합니다.
먼저 이번 강의에서 소개해드린 내용은 '윈도우' 엑셀에서만 사용 가능합니다.
파일을 실행한 후, 웹 서비스 (URL 검색)을 사용할 수 있도록 활성화 했는지 확인해보세요. :)
감사합니다.
아래 링크를 확인해보시길 바랍니다.
엑셀 신규 기능 업데이트! '데이터 형식'으로 VLOOKUP 함수 원클릭 자동화 서식 만들기 - 오빠두엑셀 (oppadu.com)
감사합니다.
추출값이 구로구가 나와야 하는데
구로동으로 설명되어있습니다
구로구가 맞습니다. 게시글을 방금 수정하였습니다.
확인해주셔서 감사합니다.
사용하고 계신 엑셀 버전을 확인해보시겠어요? :) LAMBDA 함수는 M365 버전에서만 제공됩니다.
버전은 파일 - 계정에서 확인할 수 있습니다.