An sabunta ƙimar musanya a cikin Excel

Na sha nanata hanyoyin shigo da bayanai cikin Excel daga Intanet tare da sabuntawa ta atomatik na gaba. Musamman:

• A cikin tsofaffin sigogin Excel 2007-2013, ana iya yin wannan tare da buƙatar yanar gizo kai tsaye.
• Farawa a cikin 2010, ana iya yin wannan cikin dacewa tare da ƙara-in Query Query.
  

Zuwa waɗannan hanyoyin a cikin sabbin nau'ikan Microsoft Excel, yanzu zaku iya ƙara wani - shigo da bayanai daga Intanet a cikin tsarin XML ta amfani da ginanniyar ayyuka.

XML (eXtensible Markup Language = Extensible Markup Language) harshe ne na duniya da aka tsara don bayyana kowane irin bayanai. A haƙiƙa, rubutu ne bayyananne, amma tare da ƙara masa tags na musamman don daidaita tsarin bayanai. Shafukan da yawa suna ba da rafukan bayanansu kyauta a tsarin XML don kowa ya zazzage shi. A kan gidan yanar gizon Babban Bankin Ƙasar mu (www.cbr.ru), musamman, tare da taimakon irin wannan fasaha, an ba da bayanai game da farashin musayar kudade daban-daban. Daga shafin yanar gizon Exchange na Moscow (www.moex.com) za ku iya zazzage ƙididdiga don hannun jari, shaidu da sauran bayanai masu amfani da yawa a cikin wannan hanya.

Tun daga sigar 2013, Excel yana da ayyuka guda biyu don loda bayanan XML kai tsaye daga Intanet cikin sel ɗin aiki: HIDIMAR Yanar Gizo (WEBSERVICE) и FILTER.XML (FILTERXML). Suna aiki a cikin nau'i-nau'i - na farko aikin HIDIMAR Yanar Gizo aiwatar da buƙatun zuwa rukunin yanar gizon da ake so kuma ya mayar da martani a cikin tsarin XML, sannan ta amfani da aikin FILTER.XML muna “fasa” wannan amsar zuwa sassa, muna fitar da bayanan da muke buƙata daga gare ta.

Bari mu kalli yadda waɗannan ayyuka ke gudana ta hanyar amfani da misali na yau da kullun - shigo da canjin kuɗin kowane kuɗi da muke buƙata don tazarar kwanan wata daga gidan yanar gizon Babban Bankin ƙasarmu. Za mu yi amfani da ginin mai zuwa a matsayin fanko:

Nan:

• Kwayoyin rawaya sun ƙunshi farkon da ƙarshen kwanakin lokacin sha'awar mu.
• Mai shuɗi yana da jerin zaɓuka na agogo ta amfani da umarnin Bayanai - Tabbatarwa - Lissafi (Bayanai - Tabbatarwa - Jerin).
• A cikin koren sel, za mu yi amfani da ayyukanmu don ƙirƙirar igiyoyin tambaya da samun martanin uwar garken.
• Teburin da ke hannun dama yana nufin lambobin kuɗi (za mu buƙaci shi kadan daga baya).
  

Bari mu tafi!

Mataki 1. Samar da igiyar tambaya

Don samun bayanan da ake buƙata daga rukunin yanar gizon, kuna buƙatar tambayarsa daidai. Muna zuwa www.cbr.ru kuma bude hanyar haɗin yanar gizon a cikin kasan babban shafin' Albarkatun Fasaha'- Samun bayanai ta amfani da XML (http://cbr.ru/development/SXML/). Mun gungura ƙasa kaɗan kuma a cikin misali na biyu (Misali 2) za a sami abin da muke buƙata - samun ƙimar musanya don tazarar kwanan wata:

Kamar yadda kuke gani daga misalin, layin tambaya dole ne ya ƙunshi kwanakin farawa (kwanan_req1) da kuma karshen (kwanan_req2) na lokacin sha'awa a gare mu da lambar kuɗin kuɗi (VAL_NM_RQ), adadin da muke son samu. Kuna iya samun manyan lambobin kuɗi a cikin tebur da ke ƙasa:

Hakanan ana samun cikakken jagora ga lambobin kuɗi akan gidan yanar gizon Babban Bankin - duba http://cbr.ru/scripts/XML_val.asp?d=0

Yanzu za mu samar da igiyar tambaya a cikin tantanin halitta akan takarda mai:

• ma'aikacin haɗin gwiwar rubutu (&) don haɗa shi tare;
  
• Features VPR (VLOOKUP)don nemo lambar kuɗin da muke buƙata a cikin kundin adireshi;
  
• Features rubutu (Rubutu), wanda ke canza kwanan wata bisa ga tsarin da aka bayar na rana-wata-shekara ta hanyar slash.

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

Mataki 2. Yi buƙatar

Yanzu muna amfani da aikin HIDIMAR Yanar Gizo (WEBSERVICE) tare da zaren tambaya da aka ƙirƙira azaman hujja kaɗai. Amsar za ta kasance dogon layi na lambar XML (zai fi kyau kunna kullin kalma kuma ƙara girman tantanin halitta idan kuna son ganinta gaba ɗaya):

Mataki na 3. Yin nazarin amsar

Don sauƙaƙe fahimtar tsarin bayanan amsa, yana da kyau a yi amfani da ɗaya daga cikin masu binciken XML na kan layi (misali, http://xpather.com/ ko https://jsonformatter.org/xml-parser), wanda zai iya tsara lambar XML ta gani, yana ƙara indents zuwa gare shi kuma yana nuna ma'auni tare da launi. Sa'an nan duk abin da zai bayyana da yawa:

Yanzu zaku iya gani a sarari cewa ƙimar kwas ɗin an tsara su ta alamun mu ..., kuma kwanan wata sifa ce Rana cikin tags .

Don cire su, zaɓi ginshiƙi na goma (ko fiye - idan an yi tare da gefe) sel mara komai akan takardar (saboda an saita tazarar kwanaki 10) kuma shigar da aikin a cikin ma'aunin dabara. FILTER.XML (TACEXML):

Anan, hujja ta farko ita ce hanyar haɗi zuwa tantanin halitta tare da amsawar uwar garke (B8), na biyu kuma shine kirtani ta tambaya a cikin XPath, harshe na musamman wanda za'a iya amfani dashi don samun dama ga gutsure lambar XML da ake bukata da kuma cire su. Kuna iya karanta ƙarin game da yaren XPath, misali, anan.

Yana da mahimmanci cewa bayan shigar da dabarar, kada ku danna Shigar, da gajeriyar hanyar keyboard Ctrl+Motsi+Shigar, watau shigar da shi azaman tsari na tsararru (za a ƙara maƙallan takalmin da ke kewaye da shi ta atomatik). Idan kuna da sabon sigar Office 365 tare da goyan baya don tsararru masu ƙarfi a cikin Excel, to mai sauƙi Shigar, kuma ba kwa buƙatar zaɓar sel marasa komai a gaba - aikin da kansa zai ɗauki sel da yawa kamar yadda yake buƙata.

Don cire kwanan wata, za mu yi iri ɗaya - za mu zaɓi sel marasa komai da yawa a cikin ginshiƙan da ke kusa kuma mu yi amfani da wannan aikin, amma tare da tambayar XPath daban, don samun duk ƙimar halayen Kwanan wata daga alamun rikodin:

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

Yanzu a nan gaba, lokacin canza kwanan wata a cikin sel na asali B2 da B3 ko zabar kuɗi daban-daban a cikin jerin abubuwan da aka saukar na cell B3, za a sabunta tambayar mu ta atomatik, tana nufin uwar garken Babban Bankin don sabbin bayanai. Don tilasta sabuntawa da hannu, Hakanan zaka iya amfani da gajeriyar hanyar madannai Ctrl+alt+F9.

• Shigo da ƙimar bitcoin zuwa Excel ta hanyar Query Query
• Shigo da farashin musaya daga Intanet a cikin tsoffin nau'ikan Excel