Rata tal-kambju aġġornata f'Excel

Għandi ripetutament analizzat modi kif timporta data f'Excel mill-Internet b'aġġornament awtomatiku sussegwenti. Partikolarment:

• F'verżjonijiet eqdem ta' Excel 2007-2013, dan jista' jsir b'talba diretta mill-web.
• Mill-2010, dan jista 'jsir b'mod konvenjenti ħafna bil-Power Query add-in.
  

Ma 'dawn il-metodi fl-aħħar verżjonijiet ta' Microsoft Excel, issa tista 'żżid ieħor - timporta data mill-Internet f'format XML billi tuża funzjonijiet integrati.

XML (eXtensible Markup Language = Extensible Markup Language) hija lingwa universali mfassla biex tiddeskrivi kwalunkwe tip ta' data. Fil-fatt, huwa test sempliċi, iżda b'tikketti speċjali miżjuda miegħu biex jimmarkaw l-istruttura tad-dejta. Bosta siti jipprovdu flussi b'xejn tad-dejta tagħhom f'format XML għal kulħadd biex tniżżel. Fuq il-websajt tal-Bank Ċentrali ta 'Pajjiżna (www.cbr.ru), b'mod partikolari, bl-għajnuna ta' teknoloġija simili, tingħata data dwar ir-rati tal-kambju ta 'diversi muniti. Mill-websajt tal-Iskambju ta 'Moska (www.moex.com) tista' tniżżel kwotazzjonijiet għal stokks, bonds u ħafna informazzjoni utli oħra bl-istess mod.

Mill-verżjoni 2013, Excel għandu żewġ funzjonijiet għat-tagħbija diretta tad-dejta XML mill-Internet fiċ-ċelloli tal-worksheet: SERVIZZ WEB (SERVIZZ TAL-WEB) и FILTER.XML (FILTERXML). Jaħdmu f'pari - l-ewwel il-funzjoni SERVIZZ WEB jesegwixxi talba lis-sit mixtieq u jirritorna r-rispons tiegħu f'format XML, u mbagħad juża l-funzjoni FILTER.XML aħna "parse" din it-tweġiba f'komponenti, estratt id-data li għandna bżonn minnha.

Ejja nħarsu lejn l-operat ta’ dawn il-funzjonijiet billi tuża eżempju klassiku – l-importazzjoni tar-rata tal-kambju ta’ kwalunkwe munita li għandna bżonn għal intervall ta’ data partikolari mill-websajt tal-Bank Ċentrali ta’ Pajjiżna. Se nużaw il-kostruzzjoni li ġejja bħala vojt:

Hawnhekk:

• Iċ-ċelloli sofor fihom id-dati tal-bidu u tat-tmiem tal-perjodu ta' interess għalina.
• Il-blu għandu lista drop-down ta 'muniti li jużaw il-kmand Data – Validazzjoni – Lista (Data — Validazzjoni — Lista).
• Fiċ-ċelloli ħodor, se nużaw il-funzjonijiet tagħna biex noħolqu sekwenza ta 'mistoqsijiet u niksbu r-rispons tas-server.
• It-tabella fuq il-lemin hija referenza għall-kodiċi tal-munita (se jkollna bżonnha ftit aktar tard).
  

Tlaqna!

Pass 1. Li tifforma sekwenza ta' mistoqsijiet

Biex tikseb l-informazzjoni meħtieġa mis-sit, trid titlobha b'mod korrett. Immorru fuq www.cbr.ru u niftħu l-link fil-footer tal-paġna ewlenija' Riżorsi Tekniċi'- Ikseb data bl-użu ta' XML (http://cbr.ru/development/SXML/). Aħna tiskrollja ftit aktar 'l isfel u fit-tieni eżempju (Eżempju 2) se jkun hemm dak li neħtieġu - niksbu r-rati tal-kambju għal intervall ta' data partikolari:

Kif tistgħu taraw mill-eżempju, is-sekwenza ta' mistoqsija għandu jkun fiha d-dati tal-bidu (data_req1) u truf (data_req2) tal-perjodu ta' interess għalina u l-kodiċi tal-munita (VAL_NM_RQ), ir-rata li rridu nġibu. Tista' ssib il-kodiċijiet tal-munita prinċipali fit-tabella hawn taħt:

Gwida sħiħa għall-kodiċijiet tal-munita hija wkoll disponibbli fuq il-websajt tal-Bank Ċentrali – ara http://cbr.ru/scripts/XML_val.asp?d=0

Issa se niffurmaw sekwenza ta' mistoqsijiet f'ċellula fuq folja bi:

• l-operatur tal-konkatenazzjoni tat-test (&) biex tgħaqqadha;
  
• Karatteristiċi VPR (LOOKUP)biex issib il-kodiċi tal-munita li għandna bżonn fid-direttorju;
  
• Karatteristiċi TEST (TEST), li tikkonverti d-data skond il-mudell mogħti jum-xahar-sena permezz ta 'slash.

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

Pass 2. Esegwi t-talba

Issa nużaw il-funzjoni SERVIZZ WEB (SERVIZZ TAL-WEB) bis-sekwenza ta' mistoqsija ġġenerata bħala l-uniku argument. It-tweġiba se tkun linja twila ta 'kodiċi XML (aħjar li tixgħel il-kelma wrap u żżid id-daqs taċ-ċellula jekk trid taraha kollha kemm hi):

Pass 3. Parsing tat-tweġiba

Biex tagħmilha aktar faċli li tifhem l-istruttura tad-dejta tar-rispons, huwa aħjar li tuża wieħed mill-parsers XML onlajn (per eżempju, http://xpather.com/ jew https://jsonformatter.org/xml-parser), li jista 'viżwalment jifformattja kodiċi XML, iżid inċiżi miegħu u jenfasizza s-sintassi bil-kulur. Imbagħad kollox isir ħafna aktar ċar:

Issa tista 'tara b'mod ċar li l-valuri tal-kors huma inkwadrati mit-tikketti tagħna ..., u d-dati huma attributi data f'tikketti .

Biex estrattihom, agħżel kolonna ta 'għaxar (jew aktar - jekk isir b'marġni) ċelluli vojta fuq il-folja (minħabba intervall ta' data ta '10 ijiem ġie stabbilit) u daħħal il-funzjoni fil-bar tal-formula FILTER.XML (FILTRIXML):

Hawnhekk, l-ewwel argument huwa link għal ċellula b'rispons ta 'server (B8), u t-tieni huwa string ta' mistoqsija f'XPath, lingwa speċjali li tista 'tintuża biex taċċessa l-frammenti tal-kodiċi XML meħtieġa u estratthom. Tista' taqra aktar dwar il-lingwa XPath, pereżempju, hawn.

Huwa importanti li wara li ddaħħal il-formula, tagħfasx Ikteb, u s-shortcut tat-tastiera Ctrl+Shift+Ikteb, jiġifieri daħħalha bħala formula array (iċ-ċingi kaboċċi madwaru se jiżdiedu awtomatikament). Jekk għandek l-aħħar verżjoni ta 'Office 365 b'appoġġ għal arrays dinamiċi f'Excel, allura sempliċi Ikteb, u m'għandekx bżonn tagħżel ċelluli vojta minn qabel - il-funzjoni nnifisha se tieħu ċelluli kemm teħtieġ.

Biex niġbdu d-dati, se nagħmlu l-istess - aħna se nagħżlu diversi ċelloli vojta fil-kolonna ta' maġenb u nużaw l-istess funzjoni, iżda b'mistoqsija XPath differenti, biex niksbu l-valuri kollha tal-attributi tad-Data mit-tikketti tar-Rekord:

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

Issa fil-futur, meta tbiddel id-dati fiċ-ċelloli oriġinali B2 u B3 jew tagħżel munita differenti fil-lista drop-down taċ-ċellula B3, il-mistoqsija tagħna tiġi aġġornata awtomatikament, billi tirreferi għas-server tal-Bank Ċentrali għal data ġdida. Biex iġiegħel aġġornament manwalment, tista' wkoll tuża s-shortcut tat-tastiera Ctrl+Alt+F9.

• Importa rata bitcoin għal Excel permezz ta 'Power Query
• Importa rati tal-kambju mill-Internet f'verżjonijiet eqdem ta' Excel