Kursi i përditësuar i këmbimit në Excel

Unë kam analizuar në mënyrë të përsëritur mënyrat për të importuar të dhëna në Excel nga Interneti me përditësimin e mëvonshëm automatik. Veçanërisht:

• Në versionet më të vjetra të Excel 2007-2013, kjo mund të bëhet me një kërkesë të drejtpërdrejtë në ueb.
• Duke filluar nga viti 2010, kjo mund të bëhet shumë lehtë me shtesën Power Query.
  

Tek këto metoda në versionet më të fundit të Microsoft Excel, tani mund të shtoni një tjetër - duke importuar të dhëna nga Interneti në formatin XML duke përdorur funksionet e integruara.

XML (EXtensible Markup Language = Extensible Markup Language) është një gjuhë universale e krijuar për të përshkruar çdo lloj të dhënash. Në fakt, është tekst i thjeshtë, por me etiketa speciale të shtuara për të shënuar strukturën e të dhënave. Shumë sajte ofrojnë transmetime falas të të dhënave të tyre në formatin XML për këdo që të shkarkojë. Në faqen e internetit të Bankës Qendrore të vendit tonë (www.cbr.ru), në veçanti, me ndihmën e një teknologjie të ngjashme, jepen të dhëna për kurset e këmbimit të monedhave të ndryshme. Nga faqja e internetit e Moskës Exchange (www.moex.com) mund të shkarkoni kuotat për aksione, obligacione dhe shumë informacione të tjera të dobishme në të njëjtën mënyrë.

Që nga versioni 2013, Excel ka dy funksione për ngarkimin e drejtpërdrejtë të të dhënave XML nga Interneti në qelizat e fletës së punës: SHËRBIMI WEB (WEBSERVICE) и FILTER.XML (FILTERXML). Ata punojnë në çifte - së pari funksioni SHËRBIMI WEB ekzekuton një kërkesë në faqen e dëshiruar dhe kthen përgjigjen e saj në formatin XML, dhe më pas duke përdorur funksionin FILTER.XML ne e "analizojmë" këtë përgjigje në komponentë, duke nxjerrë të dhënat që na nevojiten prej saj.

Le të shohim funksionimin e këtyre funksioneve duke përdorur një shembull klasik – importimin e kursit të këmbimit të çdo monedhe që na nevojitet për një interval të caktuar data nga faqja e internetit e Bankës Qendrore të vendit tonë. Ne do të përdorim ndërtimin e mëposhtëm si bosh:

këtu:

• Qelizat e verdha përmbajnë datat e fillimit dhe mbarimit të periudhës me interes për ne.
• E kaltra ka një listë rënëse të monedhave duke përdorur komandën Të dhënat – Vleresimi – Lista (Të dhënat - Vërtetimi - Lista).
• Në qelizat e gjelbra, ne do të përdorim funksionet tona për të krijuar një varg pyetjesh dhe për të marrë përgjigjen e serverit.
• Tabela në të djathtë është një referencë për kodet e monedhës (do të na duhet pak më vonë).
  

Shkojme!

Hapi 1. Formimi i një vargu pyetësor

Për të marrë informacionin e kërkuar nga faqja, duhet ta pyesni saktë. Shkojmë në www.cbr.ru dhe hapim lidhjen në fundin e faqes kryesore' Burimet teknike - Marrja e të dhënave duke përdorur XML (http://cbr.ru/development/SXML/). Ne lëvizim pak më poshtë dhe në shembullin e dytë (Shembulli 2) do të jetë ajo që na nevojitet - duke marrë kurset e këmbimit për një interval të caktuar data:

Siç mund ta shihni nga shembulli, vargu i pyetjes duhet të përmbajë datat e fillimit (data_req1) dhe mbaresat (data_req2) të periudhës së interesit për ne dhe kodit të monedhës (VAL_NM_RQ), norma e së cilës duam të marrim. Ju mund të gjeni kodet kryesore të monedhës në tabelën e mëposhtme:

Një udhëzues i plotë për kodet e monedhës është gjithashtu i disponueshëm në faqen e internetit të Bankës Qendrore – shikoni http://cbr.ru/scripts/XML_val.asp?d=0

Tani do të formojmë një varg pyetjesh në një qelizë në një fletë me:

• operatori i lidhjes së tekstit (&) për ta bashkuar;
  
• karakteristika VPR (VLOOKUP)për të gjetur kodin e monedhës që na nevojitet në drejtori;
  
• karakteristika TEXT (TEKSTI), e cila konverton datën sipas modelit të dhënë ditë-muaj-vit përmes një prerje.

="http://cbr.ru/scripts/XML_dynamic.asp?date_req1="&ТЕКСТ(B2;"ДД/ММ/ГГГГ")&  "&date_req2="&ТЕКСТ(B3;"ДД/ММ/ГГГГ")&"&VAL_NM_RQ="&ВПР(B4;M:N;2;0)  

Hapi 2. Ekzekutoni kërkesën

Tani ne përdorim funksionin SHËRBIMI WEB (WEBSERVICE) me vargun e gjeneruar të pyetjes si argument të vetëm. Përgjigja do të jetë një rresht i gjatë kodi XML (është më mirë të aktivizoni mbështjelljen e fjalëve dhe të rrisni madhësinë e qelizës nëse dëshironi ta shihni në tërësi):

Hapi 3. Analizimi i përgjigjes

Për ta bërë më të lehtë për të kuptuar strukturën e të dhënave të përgjigjes, është më mirë të përdorni një nga analizuesit XML në internet (për shembull, http://xpather.com/ ose https://jsonformatter.org/xml-parser), i cili mund të formatojë vizualisht kodin XML, duke shtuar dhëmbëza në të dhe duke theksuar sintaksën me ngjyra. Atëherë gjithçka do të bëhet shumë më e qartë:

Tani mund të shihni qartë se vlerat e kursit janë përshtatur nga etiketat tona ..., dhe datat janë atribute data në etiketa .

Për t'i nxjerrë ato, zgjidhni një kolonë prej dhjetë (ose më shumë - nëse bëhet me një diferencë) qelizash bosh në fletë (sepse ishte caktuar një interval data 10-ditore) dhe futni funksionin në shiritin e formulave FILTER.XML (FILTERXML):

Këtu, argumenti i parë është një lidhje me një qelizë me një përgjigje serveri (B8), dhe i dyti është një varg pyetjesh në XPath, një gjuhë e veçantë që mund të përdoret për të hyrë në fragmentet e nevojshme të kodit XML dhe për t'i nxjerrë ato. Mund të lexoni më shumë rreth gjuhës XPath, për shembull, këtu.

Është e rëndësishme që pasi të keni futur formulën, mos e shtypni Shkruani, dhe shkurtoren e tastierës Ctrl+Ndryshim+Shkruani, dmth futeni si formulë grupi (kllapat kaçurrelë rreth tij do të shtohen automatikisht). Nëse keni versionin më të fundit të Office 365 me mbështetje për vargje dinamike në Excel, atëherë një i thjeshtë Shkruani, dhe nuk keni nevojë të zgjidhni paraprakisht qeliza boshe – vetë funksioni do të marrë aq qeliza sa i duhen.

Për të nxjerrë datat, ne do të bëjmë të njëjtën gjë - do të zgjedhim disa qeliza boshe në kolonën ngjitur dhe do të përdorim të njëjtin funksion, por me një pyetje të ndryshme XPath, për të marrë të gjitha vlerat e atributeve Date nga etiketat Record:

=FILTER.XML(B8;”//Record/@Date”)

Tani në të ardhmen, kur ndryshoni datat në qelizat origjinale B2 dhe B3 ose zgjidhni një monedhë tjetër në listën rënëse të qelizës B3, pyetja jonë do të përditësohet automatikisht, duke iu referuar serverit të Bankës Qendrore për të dhëna të reja. Për të detyruar një përditësim manualisht, mund të përdorni gjithashtu shkurtoren e tastierës Ctrl+Ndalet+F9.

• Importoni normën e bitcoin në Excel përmes Power Query
• Importoni kurset e këmbimit nga interneti në versionet më të vjetra të Excel