Uppfært gengi í Excel

Ég hef ítrekað greint leiðir til að flytja inn gögn í Excel af internetinu með sjálfvirkri uppfærslu í kjölfarið. Einkum:

• Í eldri útgáfum af Excel 2007-2013 gæti þetta verið gert með beinni vefbeiðni.
• Frá og með 2010 er hægt að gera þetta mjög þægilegt með Power Query viðbótinni.
  

Við þessar aðferðir í nýjustu útgáfum af Microsoft Excel geturðu nú bætt annarri - að flytja inn gögn af internetinu á XML-sniði með því að nota innbyggðar aðgerðir.

XML (eXtensible Markup Language = Extensible Markup Language) er alhliða tungumál sem er hannað til að lýsa hvers kyns gögnum. Í raun er þetta venjulegur texti, en með sérstökum merkjum bætt við hann til að merkja upp gagnaskipulagið. Margar síður bjóða upp á ókeypis strauma af gögnum sínum á XML-sniði sem allir geta hlaðið niður. Á heimasíðu Seðlabanka lands okkar (www.cbr.ru), einkum með hjálp svipaðrar tækni, eru gefin upp gögn um gengi mismunandi gjaldmiðla. Á heimasíðu Moscow Exchange (www.moex.com) er hægt að hlaða niður verðtilboðum í hlutabréf, skuldabréf og fullt af öðrum gagnlegum upplýsingum á sama hátt.

Frá útgáfu 2013 hefur Excel tvær aðgerðir til að hlaða XML gögnum beint af internetinu í vinnublaðsfrumur: VEFÞJÓNUSTA (VEFÞJÓNUSTA) и SÍA.XML (FILTERXML). Þeir vinna í pörum - fyrst aðgerðin VEFÞJÓNUSTA framkvæmir beiðni á viðkomandi síðu og skilar svari sínu á XML-sniði og notar síðan aðgerðina SÍA.XML við „þáttum“ þessu svari í íhluti og vinnum úr þeim gögnin sem við þurfum.

Við skulum skoða virkni þessara aðgerða með því að nota klassískt dæmi - að flytja inn gengi hvers gjaldmiðils sem við þurfum fyrir tiltekið dagsetningarbil af vefsíðu Seðlabanka lands okkar. Við munum nota eftirfarandi smíði sem autt:

hér:

• Gulu reitirnir innihalda upphafs- og lokadagsetningar tímabilsins sem vekur áhuga okkar.
• Blái er með fellilista yfir gjaldmiðla með skipuninni Gögn – Staðfesting – Listi (Gögn — Staðfesting — Listi).
• Í grænu reitunum munum við nota aðgerðir okkar til að búa til fyrirspurnarstreng og fá svar þjónsins.
• Taflan til hægri er tilvísun í gjaldmiðilskóða (við munum þurfa hana aðeins síðar).
  

Förum!

Skref 1. Mynda fyrirspurnarstreng

Til að fá nauðsynlegar upplýsingar af síðunni þarftu að spyrja rétt. Við förum á www.cbr.ru og opnum hlekkinn í síðufæti aðalsíðunnar' Tæknilegar auðlindir'- Að sækja gögn með XML (http://cbr.ru/development/SXML/). Við flettum aðeins neðar og í öðru dæminu (dæmi 2) verður það sem við þurfum - að fá gengi fyrir tiltekið dagsetningarbil:

Eins og þú sérð í dæminu verður fyrirspurnarstrengurinn að innihalda upphafsdagsetningar (dagsetning_beiðni1) og endir (dagsetning_beiðni2) af tímabilinu sem vekur áhuga okkar og gjaldmiðilskóðann (VAL_NM_RQ), það gengi sem við viljum fá. Þú getur fundið helstu gjaldmiðlakóðana í töflunni hér að neðan:

Heildar leiðbeiningar um gjaldmiðlakóða er einnig að finna á heimasíðu Seðlabankans – sjá http://cbr.ru/scripts/XML_val.asp?d=0

Nú munum við mynda fyrirspurnarstreng í reit á blaði með:

• textasamtengingaraðgerðina (&) til að setja hann saman;
  
• Aðstaða VPR (SKRÁNING)til að finna kóða gjaldmiðilsins sem við þurfum í skránni;
  
• Aðstaða TEXT (TEXTI), sem breytir dagsetningu í samræmi við tiltekið mynstur dag-mánaðar-ár í gegnum skástrik.

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

Skref 2. Framkvæmdu beiðnina

Nú notum við aðgerðina VEFÞJÓNUSTA (VEFÞJÓNUSTA) með myndaðan fyrirspurnarstreng sem einu rökin. Svarið verður löng lína af XML kóða (betra er að kveikja á orðapakkningum og auka stærð hólfa ef þú vilt sjá hann í heild sinni):

Skref 3. Að greina svarið

Til að gera það auðveldara að skilja uppbyggingu svargagnanna er betra að nota einn af XML-þáttunum á netinu (til dæmis http://xpather.com/ eða https://jsonformatter.org/xml-parser), sem getur sjónrænt sniðið XML kóða, bætt inndráttum við hann og auðkennt setningafræðina með lit. Þá verður allt miklu skýrara:

Nú sérðu greinilega að námskeiðsgildin eru rammuð inn af merkjum okkar ..., og dagsetningar eru eiginleikar Dagsetning í merkjum .

Til að draga þær út skaltu velja dálk með tíu (eða fleiri - ef gert er með spássíu) tómum hólfum á blaðinu (vegna þess að 10 daga dagsetningarbil var stillt) og sláðu inn fallið í formúlustikunni SÍA.XML (SÍAXML):

Hér er fyrsta rökin tengill á reit með svarþjónssvörun (B8) og sú seinni er fyrirspurnarstrengur í XPath, sérstöku tungumáli sem hægt er að nota til að fá aðgang að nauðsynlegum XML kóðabrotum og vinna úr þeim. Þú getur lesið meira um XPath tungumálið, til dæmis, hér.

Það er mikilvægt að eftir að hafa slegið inn formúluna, ekki ýta á Sláðu inn, og flýtilykla Ctrl+Shift+Sláðu inn, þ.e. sláðu það inn sem fylkisformúlu (hrokknu axlaböndin utan um hana bætast sjálfkrafa við). Ef þú ert með nýjustu útgáfuna af Office 365 með stuðningi fyrir kraftmikla fylki í Excel, þá er einfalt Sláðu inn, og þú þarft ekki að velja tómar frumur fyrirfram - aðgerðin sjálf mun taka eins margar frumur og hún þarfnast.

Til að draga út dagsetningar munum við gera það sama - við munum velja nokkrar tómar frumur í aðliggjandi dálki og nota sömu aðgerðina, en með annarri XPath fyrirspurn, til að fá öll gildi dagsetningareigindanna úr Record tags:

=SÍA.XML(B8;”//Record/@Date”)

Nú í framtíðinni, þegar þú breytir dagsetningum í upprunalegu reitunum B2 og B3 eða velur annan gjaldmiðil í fellilistanum fyrir reit B3, verður fyrirspurn okkar sjálfkrafa uppfærð og vísar til Seðlabankans miðlara fyrir ný gögn. Til að þvinga fram uppfærslu handvirkt geturðu notað flýtilykla til viðbótar Ctrl+Alt+F9.

• Flytja inn bitcoin hlutfall í Excel með Power Query
• Flytja inn gengi af netinu í eldri útgáfum af Excel