Excel бағдарламасындағы жаңартылған валюта бағамы

Мен Excel бағдарламасына деректерді кейіннен автоматты түрде жаңарту арқылы Интернеттен импорттау жолдарын бірнеше рет талдадым. Сондай-ақ:

• Excel 2007-2013 ескі нұсқаларында мұны тікелей веб-сұраныс арқылы жасауға болады.
• 2010 жылдан бастап мұны Power Query қондырмасы арқылы өте ыңғайлы жасауға болады.
  

Microsoft Excel бағдарламасының соңғы нұсқаларындағы осы әдістерге енді тағы біреуін қосуға болады – кірістірілген функцияларды пайдаланып XML пішімінде Интернеттен деректерді импорттау.

XML (eXtensible Markup Language = Extensible Markup Language) – кез келген деректер түрін сипаттауға арналған әмбебап тіл. Шын мәнінде, бұл қарапайым мәтін, бірақ деректер құрылымын белгілеу үшін оған арнайы тегтер қосылған. Көптеген сайттар кез келген жүктеп алу үшін XML пішімінде өз деректерінің тегін ағындарын ұсынады. Біздің еліміздің Орталық банкінің веб-сайтында (www.cbr.ru), атап айтқанда, ұқсас технологияның көмегімен әртүрлі валюталардың айырбас бағамдары туралы деректер берілген. Мәскеу биржасының веб-сайтынан (www.moex.com) акцияларға, облигацияларға және басқа да көптеген пайдалы ақпаратты дәл осылай жүктеп алуға болады.

2013 нұсқасынан бастап Excel бағдарламасында XML деректерін Интернеттен жұмыс парағының ұяшықтарына тікелей жүктеуге арналған екі функциясы бар: WEB ҚЫЗМЕТІ (ВЕБСЕРВИС) и FILTER.XML (FILTERXML). Олар жұппен жұмыс істейді - алдымен функция WEB ҚЫЗМЕТІ қалаған сайтқа сұрауды орындайды және оның жауабын XML пішімінде қайтарады, содан кейін функцияны пайдаланады FILTER.XML біз бұл жауапты құрамдастарға «талдаймыз», одан қажетті деректерді аламыз.

Бұл функциялардың жұмысын классикалық мысал арқылы қарастырайық – біздің еліміздің Орталық банкінің веб-сайтынан белгілі бір күн аралығына қажет кез келген валютаның бағамын импорттау. Біз дайындама ретінде келесі құрылысты қолданамыз:

Мұнда:

• Сары ұяшықтар бізді қызықтыратын кезеңнің басталу және аяқталу күндерін қамтиды.
• Көк түсте пәрменді қолданатын валюталардың ашылмалы тізімі бар Деректер – Валидация – Тізім (Деректер — Валидация — Тізім).
• Жасыл ұяшықтарда сұрау жолын жасау және сервердің жауабын алу үшін функцияларымызды қолданамыз.
• Оң жақтағы кесте валюта кодтарына сілтеме болып табылады (бұл бізге сәл кейінірек қажет болады).
  

Барайық!

Қадам 1. Сұрау жолын қалыптастыру

Сайттан қажетті ақпаратты алу үшін оны дұрыс сұрау керек. Біз www.cbr.ru сайтына кіріп, негізгі беттің төменгі колонтитулындағы сілтемені ашамыз' Техникалық ресурстар'- XML көмегімен деректерді алу (http://cbr.ru/development/SXML/). Біз сәл төмен айналдырамыз және екінші мысалда (2-мысал) бізге қажет нәрсе болады - берілген күн аралығы үшін айырбас бағамдарын алу:

Мысалдан көріп отырғаныңыздай, сұрау жолында басталу күндері болуы керек (date_req1) және аяқталуы (date_req2) бізді қызықтыратын кезең және валюта коды (VAL_NM_RQ), біз алғымыз келетін мөлшерлеме. Негізгі валюта кодтарын төмендегі кестеден таба аласыз:

Валюта кодтары бойынша толық нұсқаулық Орталық банктің веб-сайтында да бар – 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-қадам. Сұранысты орындаңыз

Енді біз функцияны қолданамыз WEB ҚЫЗМЕТІ (ВЕБСЕРВИС) жалғыз аргумент ретінде жасалған сұрау жолымен. Жауап XML кодының ұзын сызығы болады (егер сіз оны толығымен көргіңіз келсе, сөзді орау және ұяшық өлшемін ұлғайту жақсы):

3-қадам. Жауапты талдау

Жауап деректерінің құрылымын түсінуді жеңілдету үшін онлайн XML талдауларының бірін қолданған дұрыс (мысалы, http://xpather.com/ немесе https://jsonformatter.org/xml-parser), ол XML кодын көрнекі түрде пішімдей алады, оған шегіністерді қосады және синтаксисті түспен бөлектей алады. Сонда бәрі анық болады:

Енді сіз курс мәндерінің біздің тегтерімізбен жиектелетінін анық көре аласыз ..., және күндер төлсипат болып табылады дата тегтерде .

Оларды шығару үшін парақта он (немесе одан да көп – шетімен жасалса) бос ұяшықтан тұратын бағанды ​​таңдаңыз (өйткені 10 күндік күн аралығы орнатылған) және функцияны формула жолына енгізіңіз. FILTER.XML (СҮЗГІXML):

Мұнда бірінші аргумент – сервер жауабы бар ұяшыққа сілтеме (B8), ал екіншісі – XPath бағдарламасындағы сұраныс жолы, ол XML кодының қажетті фрагменттеріне қол жеткізу және оларды шығару үшін пайдаланылуы мүмкін арнайы тіл. XPath тілі туралы көбірек оқи аласыз, мысалы, мына жерден.

Формула енгізгеннен кейін баспау маңызды кіру, және пернелер тіркесімі Ctrl+ауысым+кіру, яғни оны массив формуласы ретінде енгізіңіз (оның айналасындағы бұйра жақшалар автоматты түрде қосылады). Excel бағдарламасындағы динамикалық массивтерге қолдау көрсететін Office 365 бағдарламасының соңғы нұсқасы болса, қарапайым кіру, және бос ұяшықтарды алдын ала таңдаудың қажеті жоқ – функцияның өзі қанша қажет болса, сонша ұяшық алады.

Күндерді шығару үшін біз де солай істейміз – біз көрші бағандағы бірнеше бос ұяшықтарды таңдаймыз және бірдей функцияны қолданамыз, бірақ Жазба тегтерінен Date атрибуттарының барлық мәндерін алу үшін басқа XPath сұрауымен:

=FILTER.XML(B8;”//Жазба/@Күні”)

Енді болашақта бастапқы B2 және B3 ұяшықтарындағы күндерді өзгерткенде немесе B3 ұяшығының ашылмалы тізімінде басқа валютаны таңдағанда, жаңа деректер үшін Орталық банк серверіне сілтеме жасай отырып, біздің сұрауымыз автоматты түрде жаңартылады. Қолмен жаңартуды мәжбүрлеу үшін пернелер тіркесімін қосымша пайдалануға болады Ctrl+Alt+F9.

• Power Query арқылы Excel бағдарламасына биткоин мөлшерлемесін импорттаңыз
• Excel бағдарламасының ескі нұсқаларында интернеттен валюта бағамдарын импорттаңыз