Yakagadziridzwa mwero wekuchinjana muExcel

Ndakaongorora kakawanda nzira dzekutumira data muExcel kubva paInternet nekuzotevera otomatiki kugadzirisa. Zvikuru sei:

• Mune shanduro dzekare dzeExcel 2007-2013, izvi zvinogona kuitwa nechikumbiro chakananga chewebhu.
• Kutanga muna 2010, izvi zvinogona kuitwa zviri nyore neiyo Power Query yekuwedzera-mukati.
  

Kune idzi nzira mushanduro dzekupedzisira dzeMicrosoft Excel, iwe unogona ikozvino kuwedzera imwe - kupinza data kubva kuInternet muXML fomati uchishandisa akavakirwa-mukati mabasa.

XML (eXtensible Markup Language = Extensible Markup Language) mutauro wepasi rose wakagadzirirwa kutsanangura chero rudzi rwe data. Muchokwadi, iwo mavara akajeka, asi ane akakosha ma tag akawedzerwa kwairi kumaka iyo data chimiro. Mawebhusaiti mazhinji anopa emahara nzizi dze data ravo muXML fomati kuti chero munhu atore. Pawebhusaiti yeCentral Bank yeNyika Yedu (www.cbr.ru), kunyanya, nerubatsiro rwehunyanzvi hwakafanana, data pamitengo yekutsinhana kwemari dzakasiyana siyana inopiwa. Kubva pawebsite yeMoscow Exchange (www.moex.com) unogona kutora zvinyorwa zvematokisi, mabhondi uye mamwe akawanda anobatsira ruzivo nenzira imwecheteyo.

Kubva vhezheni 2013, Excel ine mabasa maviri ekurodha zvakananga XML data kubva paInternet kuita maseru ebasa: WEB SERVICE (WEBSERVICE) и FILTER.XML (FILTERXML). Vanoshanda vaviri vaviri - kutanga basa WEB SERVICE inoita chikumbiro kune inodiwa saiti uye inodzosera mhinduro yayo muXML fomati, uyezve kushandisa basa racho FILTER.XML isu "tinotsanangura" mhinduro iyi muzvikamu, tichitora data yatinoda kubva mairi.

Ngatitarisei kushanda kweaya mabasa tichishandisa muenzaniso wekare - kupinza chiyero chekuchinjana chero mari yatinoda yenguva yakapihwa yemazuva kubva pawebhusaiti yeCentral Bank yeNyika Yedu. Tichashandisa chivakwa chinotevera sechisina chinhu:

Pano:

• Mayero maseru ane mazuva ekutanga neanogumisira enguva yekufarira kwatiri.
• Iyo yebhuruu ine yekudonha-pasi runyorwa rwemari uchishandisa rairo Data – Validation – Rondedzero (Data - Validation - Rondedzero).
• Mumaseru akasvibira, isu tichashandisa mabasa edu kugadzira tambo yemibvunzo uye kuwana mhinduro yeseva.
• Tafura iri kurudyi inoreva macode emari (tichaida gare gare).
  

Handeyi!

Danho 1. Kugadzira tambo yemibvunzo

Kuti uwane ruzivo rwunodiwa kubva kune saiti, unofanirwa kuibvunza nemazvo. Isu tinoenda ku www.cbr.ru uye tovhura chinongedzo chiri muzasi pepeji huru' Technical Resources'- Kuwana data uchishandisa XML (http://cbr.ru/development/SXML/). Isu tinopenya zvishoma uye mumuenzaniso wechipiri (Muenzaniso 2) pachave nezvatinoda - kuwana mitengo yekuchinjana yenguva yakapihwa yemazuva:

Sezvauri kuona kubva pamuenzaniso, mutsara wemubvunzo unofanirwa kunge uine mazuva ekutanga (date_req1) uye magumo (date_req2) yenguva yekufarira kwatiri uye kodhi yemari (VAL_NM_RQ), mwero watinoda kuwana. Unogona kuwana macode emari makuru patafura pazasi:

Gwaro rakazara remakodhi emari rinowanikwawo pawebhusaiti yeCentral Bank - ona http://cbr.ru/scripts/XML_val.asp?d=0

Iye zvino tichagadzira tambo yemibvunzo muchitokisi pane pepa rine:

• the text concatenation operator (&) kuti iise pamwechete;
  
• Features VPR (VLOOKUP)kuwana kodhi yemari yatinoda mudhairekitori;
  
• Features TEXT (TEKSTO), iyo inoshandura zuva maererano nemaitiro akapihwa zuva-mwedzi-gore kuburikidza ne slash.

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

Danho 2. Ita chikumbiro

Iye zvino tinoshandisa basa WEB SERVICE (WEBSERVICE) netambo yebvunzo yakagadzirwa seyoga nharo. Mhinduro ichave mutsara wakareba weXML kodhi (zviri nani kubatidza kuputira mazwi uye kuwedzera saizi yesero kana iwe uchida kuiona yakazara):

Danho 3. Kuongorora mhinduro

Kuti zvive nyore kunzwisisa magadzirirwo edata remhinduro, zviri nani kushandisa imwe yepamhepo XML parser (semuenzaniso, http://xpather.com/ kana https://jsonformatter.org/xml-parser), iyo inogona nekuona fomati XML kodhi, ichiwedzera indents kwairi uye nekusimbisa syntax ine ruvara. Zvadaro zvinhu zvose zvichava zvakajeka zvikuru:

Iye zvino iwe unogona kuona zvakajeka kuti makosi ekosi akaumbwa nema tag edu ..., uye mazuva hunhu zuva muma tags .

Kuti uzviburitse, sarudza koramu yegumi (kana kupfuura - kana yakaitwa nemargin) maseru asina chinhu pabepa (nekuti mazuva gumi emazuva akatarwa) uye isa basa racho mubhawa remafomula. FILTER.XML (SEFAXML):

Pano, nharo yekutanga inobatanidza kune sero ine mhinduro yevhavha (B8), uye yechipiri itambo yemibvunzo mu XPath, mutauro unokosha unogona kushandiswa kuwana zvimedu zvekodhi zve XML zvinodiwa uye kuzvibudisa. Iwe unogona kuverenga zvakawanda nezve XPath mutauro, semuenzaniso, pano.

Izvo zvakakosha kuti mushure mekupinda muchimiro, usadzvanya pinda, uye nzira yekudimbudzira keyboard Ctrl+kusuduruka+pinda, kureva kuti pinda iyo seyakarongwa fomula (iyo curly braces yakatenderedza ichawedzerwa otomatiki). Kana iwe uine yazvino vhezheni yeHofisi 365 nerutsigiro rweane simba arrays muExcel, saka iri nyore pinda, uye haufanire kusarudza maseru asina chinhu pachine nguva - basa racho pacharo richatora maseru akawanda sezvarinoda.

Kuti tibvise misi, isu tichaita zvimwe chete - isu tichasarudza akati wandei masero asina chinhu mukoramu yakatarisana uye toshandisa basa rimwe chete, asi nemubvunzo wakasiyana weXPath, kuti tiwane zvese zvakakosha zveDate hunhu kubva kuRekodha tags:

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

Iye zvino mune ramangwana, pakuchinja misi mumaseru ekutanga B2 neB3 kana kusarudza imwe mari mune yekudonha-pasi runyorwa rwesero B3, mubvunzo wedu uchagadziridzwa otomatiki, uchireva kuCentral Bank server kune data nyowani. Kumanikidza kugadziridza nemaoko, iwe unogona zvakare kushandisa iyo keyboard nzira yekudimbudzira Ctrl+alt+F9.

• Ngenisa bitcoin mwero kuExcel kuburikidza nePower Query
• Ngenisa mitengo yekuchinjana kubva paInternet mumavhezheni ekare eExcel