Kiwango cha ubadilishaji kilichosasishwa katika Excel

Nimechambua mara kwa mara njia za kuingiza data kwenye Excel kutoka kwa Mtandao na kusasisha kiotomatiki. Hasa:

• Katika matoleo ya zamani ya Excel 2007-2013, hii inaweza kufanywa kwa ombi la moja kwa moja la wavuti.
• Kuanzia 2010, hii inaweza kufanywa kwa urahisi sana na programu jalizi ya Hoja ya Nguvu.
  

Kwa njia hizi katika matoleo ya hivi karibuni ya Microsoft Excel, sasa unaweza kuongeza nyingine - kuagiza data kutoka kwenye mtandao katika muundo wa XML kwa kutumia kazi zilizojengwa.

XML (EXtensible Markup Language = Extensible Markup Language) ni lugha ya ulimwengu wote iliyoundwa kuelezea aina yoyote ya data. Kwa kweli, ni maandishi wazi, lakini ikiwa na vitambulisho maalum vilivyoongezwa kwake ili kuashiria muundo wa data. Tovuti nyingi hutoa mitiririko ya bila malipo ya data zao katika umbizo la XML kwa mtu yeyote kupakua. Kwenye tovuti ya Benki Kuu ya Nchi Yetu (www.cbr.ru), hasa, kwa msaada wa teknolojia sawa, data juu ya viwango vya ubadilishaji wa sarafu mbalimbali hutolewa. Kutoka kwenye tovuti ya Moscow Exchange (www.moex.com) unaweza kupakua quotes kwa hifadhi, vifungo na maelezo mengine mengi muhimu kwa njia sawa.

Tangu toleo la 2013, Excel ina kazi mbili za kupakia moja kwa moja data ya XML kutoka kwa Mtandao hadi seli za laha kazi: HUDUMA YA MTANDAO (WEBSERVICE) и FILTER.XML (FILTERXML). Wanafanya kazi kwa jozi - kwanza kazi HUDUMA YA MTANDAO hutekeleza ombi kwa tovuti inayotakiwa na kurudisha majibu yake katika umbizo la XML, na kisha kutumia kitendakazi FILTER.XML "tunachanganua" jibu hili katika vipengele, tukitoa data tunayohitaji kutoka kwayo.

Hebu tuangalie utendakazi wa huduma hizi kwa kutumia mfano wa kawaida - kuagiza kiwango cha ubadilishaji wa sarafu yoyote tunayohitaji kwa muda fulani kutoka kwa tovuti ya Benki Kuu ya Nchi Yetu. Tutatumia ujenzi ufuatao kama tupu:

hapa:

• Seli za manjano huwa na tarehe za kuanza na mwisho za kipindi tunachopenda.
• Ya bluu ina orodha kunjuzi ya sarafu kwa kutumia amri Data - Uthibitishaji - Orodha (Data - Uthibitishaji - Orodha).
• Katika seli za kijani, tutatumia vitendaji vyetu kuunda safu ya hoja na kupata majibu ya seva.
• Jedwali lililo upande wa kulia ni rejeleo la misimbo ya sarafu (tutaihitaji baadaye kidogo).
  

Hebu tuende!

Hatua ya 1. Kuunda kamba ya swala

Ili kupata taarifa zinazohitajika kutoka kwenye tovuti, unahitaji kuuliza kwa usahihi. Tunaenda kwa www.cbr.ru na kufungua kiungo katika sehemu ya chini ya ukurasa kuu' Rasilimali za Kiufundi'- Kupata data kwa kutumia XML (http://cbr.ru/development/SXML/). Tunasogeza chini kidogo na katika mfano wa pili (Mfano wa 2) kutakuwa na kile tunachohitaji - kupata viwango vya ubadilishaji wa muda uliowekwa:

Kama unavyoona kutoka kwa mfano, kamba ya hoja lazima iwe na tarehe za kuanza (tarehe_req1) na mwisho (tarehe_req2) ya kipindi cha riba kwetu na msimbo wa sarafu (VAL_NM_RQ), kiwango ambacho tunataka kupata. Unaweza kupata misimbo kuu ya sarafu kwenye jedwali hapa chini:

Mwongozo kamili wa misimbo ya sarafu unapatikana pia kwenye tovuti ya Benki Kuu - tazama http://cbr.ru/scripts/XML_val.asp?d=0

Sasa tutaunda kamba ya hoja kwenye seli kwenye laha iliyo na:

• opereta wa uunganishaji wa maandishi (&) ili kuiweka pamoja;
  
• Vipengele VPR (VLOOKUP)kupata msimbo wa sarafu tunayohitaji kwenye saraka;
  
• Vipengele TEXT (TEXT), ambayo hubadilisha tarehe kulingana na muundo uliotolewa siku-mwaka-mwaka kupitia kufyeka.

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

Hatua ya 2. Tekeleza ombi

Sasa tunatumia kazi HUDUMA YA MTANDAO (WEBSERVICE) na kamba ya hoja inayotokana kama hoja pekee. Jibu litakuwa safu ndefu ya nambari ya XML (ni bora kuwasha maandishi na kuongeza saizi ya seli ikiwa unataka kuiona kwa ukamilifu):

Hatua ya 3. Kuchanganua jibu

Ili kurahisisha kuelewa muundo wa data ya majibu, ni bora kutumia mojawapo ya vichanganuzi vya XML mtandaoni (kwa mfano, http://xpather.com/ au https://jsonformatter.org/xml-parser), ambayo inaweza kuumbiza msimbo wa XML, na kuongeza indents kwake na kuangazia sintaksia yenye rangi. Kisha kila kitu kitakuwa wazi zaidi:

Sasa unaweza kuona wazi kwamba maadili ya kozi yameandaliwa na vitambulisho vyetu ..., na tarehe ni sifa tarehe katika vitambulisho .

Ili kuzitoa, chagua safu ya seli kumi (au zaidi - ikiwa imefanywa kwa ukingo) kwenye laha (kwa sababu muda wa siku 10 umewekwa) na uweke chaguo la kukokotoa kwenye upau wa fomula. FILTER.XML (CHUJAXML):

Hapa, hoja ya kwanza ni kiungo kwa seli na jibu la seva (B8), na ya pili ni kamba ya swali katika XPath, lugha maalum ambayo inaweza kutumika kufikia vipande muhimu vya msimbo wa XML na kuziondoa. Unaweza kusoma zaidi kuhusu lugha ya XPath, kwa mfano, hapa.

Ni muhimu kwamba baada ya kuingia formula, usisisitize kuingia, na njia ya mkato ya kibodi Ctrl+Kuhama+kuingia, yaani, ingiza kama fomula ya safu (viunga vya curly karibu nayo vitaongezwa kiotomatiki). Ikiwa una toleo la hivi karibuni la Ofisi ya 365 na usaidizi wa safu zinazobadilika katika Excel, basi rahisi kuingia, na huna haja ya kuchagua seli tupu mapema - kazi yenyewe itachukua seli nyingi kama inavyohitaji.

Ili kutoa tarehe, tutafanya vivyo hivyo - tutachagua seli kadhaa tupu kwenye safu iliyo karibu na kutumia kazi sawa, lakini kwa swali tofauti la XPath, kupata maadili yote ya sifa za Tarehe kutoka kwa lebo za Rekodi:

=FILTER.XML(B8;”//Rekodi/@Tarehe”)

Sasa katika siku zijazo, wakati wa kubadilisha tarehe katika seli asili B2 na B3 au kuchagua sarafu tofauti katika orodha kunjuzi ya seli B3, swali letu litasasishwa kiotomatiki, likirejelea seva ya Benki Kuu kwa data mpya. Ili kulazimisha kusasisha wewe mwenyewe, unaweza kutumia njia ya mkato ya kibodi Ctrl+Alt+F9.

• Ingiza kiwango cha bitcoin kwa Excel kupitia Hoja ya Nguvu
• Ingiza viwango vya ubadilishaji kutoka kwa Mtandao katika matoleo ya zamani ya Excel