Cyfradd gyfnewid wedi'i diweddaru yn Excel

Rwyf wedi dadansoddi dro ar ôl tro ffyrdd o fewnforio data i Excel o'r Rhyngrwyd gyda diweddaru awtomatig dilynol. Yn benodol:

• Mewn fersiynau hŷn o Excel 2007-2013, gellid gwneud hyn gyda chais uniongyrchol ar y we.
• Gan ddechrau yn 2010, gellir gwneud hyn yn gyfleus iawn gyda'r ychwanegiad Power Query.
  

At y dulliau hyn yn y fersiynau diweddaraf o Microsoft Excel, gallwch nawr ychwanegu un arall - mewnforio data o'r Rhyngrwyd mewn fformat XML gan ddefnyddio swyddogaethau adeiledig.

Mae XML (Iaith Marcio eXtensible = Iaith Marcio Estynadwy) yn iaith gyffredinol sydd wedi'i dylunio i ddisgrifio unrhyw fath o ddata. Mewn gwirionedd, mae'n destun plaen, ond gyda thagiau arbennig wedi'u hychwanegu ato i nodi'r strwythur data. Mae llawer o wefannau yn darparu ffrydiau am ddim o'u data ar ffurf XML i unrhyw un eu llwytho i lawr. Ar wefan Banc Canolog Ein Gwlad (www.cbr.ru), yn arbennig, gyda chymorth technoleg debyg, rhoddir data ar gyfraddau cyfnewid arian cyfred amrywiol. O wefan Moscow Exchange (www.moex.com) gallwch lawrlwytho dyfynbrisiau ar gyfer stociau, bondiau a llawer o wybodaeth ddefnyddiol arall yn yr un modd.

Ers fersiwn 2013, mae gan Excel ddwy swyddogaeth ar gyfer llwytho data XML yn uniongyrchol o'r Rhyngrwyd i gelloedd taflen waith: GWASANAETH GWE (GWASANAETH) и HIDLYDD.XML (FILTERXML). Maen nhw'n gweithio mewn parau – yn gyntaf y swyddogaeth GWASANAETH GWE yn gweithredu cais i'r safle dymunol ac yn dychwelyd ei ymateb mewn fformat XML, ac yna'n defnyddio'r ffwythiant HIDLYDD.XML rydym yn “dosrannu” yr ateb hwn yn gydrannau, gan dynnu'r data sydd ei angen arnom ohono.

Gadewch i ni edrych ar weithrediad y swyddogaethau hyn gan ddefnyddio enghraifft glasurol - mewnforio cyfradd cyfnewid unrhyw arian cyfred sydd ei angen arnom ar gyfer cyfnod penodol o amser o wefan Banc Canolog Ein Gwlad. Byddwn yn defnyddio'r adeiladwaith canlynol yn wag:

yma:

• Mae'r celloedd melyn yn cynnwys dyddiadau dechrau a diwedd y cyfnod sydd o ddiddordeb i ni.
• Mae gan yr un glas gwymplen o arian cyfred gan ddefnyddio'r gorchymyn Data – Dilysu – Rhestr (Data - Dilysu - Rhestr).
• Yn y celloedd gwyrdd, byddwn yn defnyddio ein swyddogaethau i greu llinyn ymholiad a chael ymateb y gweinydd.
• Mae'r tabl ar y dde yn gyfeiriad at godau arian cyfred (bydd ei angen arnom ychydig yn ddiweddarach).
  

Awn ni!

Cam 1. Ffurfio llinyn ymholiad

I gael y wybodaeth ofynnol o'r wefan, mae angen i chi ei gofyn yn gywir. Rydyn ni'n mynd i www.cbr.ru ac yn agor y ddolen yn nhroedyn y brif dudalen' Adnoddau Technegol'- Cael data gan ddefnyddio XML ( http://cbr.ru/development/SXML/ ). Rydym yn sgrolio ychydig yn is ac yn yr ail enghraifft (Enghraifft 2) bydd yr hyn sydd ei angen arnom - cael y cyfraddau cyfnewid ar gyfer cyfnod penodol:

Fel y gwelwch o'r enghraifft, rhaid i'r llinyn ymholiad gynnwys dyddiadau cychwyn (dyddiad_req1) a therfyniadau (dyddiad_req2) o'r cyfnod o ddiddordeb i ni a'r cod arian cyfred (VAL_NM_RQ), y gyfradd yr ydym am ei chael. Gallwch ddod o hyd i'r prif godau arian cyfred yn y tabl isod:

Mae canllaw cyflawn i godau arian cyfred hefyd ar gael ar wefan y Banc Canolog – gweler http://cbr.ru/scripts/XML_val.asp?d=0

Nawr byddwn yn ffurfio llinyn ymholiad mewn cell ar ddalen gyda:

• y gweithredwr cydgateniad testun (&) i'w roi at ei gilydd;
  
• Nodweddion VPR (VLOOKUP)i ddod o hyd i god yr arian cyfred sydd ei angen arnom yn y cyfeiriadur;
  
• Nodweddion TEXT (TESTUN), sy'n trosi'r dyddiad yn ôl y patrwm diwrnod-mis-blwyddyn a roddwyd trwy slaes.

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

Cam 2. Gweithredu'r cais

Nawr rydym yn defnyddio'r swyddogaeth GWASANAETH GWE (GWASANAETH) gyda'r llinyn ymholiad a gynhyrchir fel yr unig ddadl. Yr ateb fydd llinell hir o god XML (mae'n well troi papur lapio ymlaen a chynyddu maint y gell os ydych chi am ei weld yn ei gyfanrwydd):

Cam 3. Dosrannu'r ateb

Er mwyn ei gwneud yn haws deall strwythur y data ymateb, mae'n well defnyddio un o'r parsers XML ar-lein (er enghraifft, http://xpather.com/ neu https://jsonformatter.org/xml-parser), sy'n gallu fformatio cod XML yn weledol, gan ychwanegu indentau ato ac amlygu'r gystrawen â lliw. Yna bydd popeth yn dod yn llawer cliriach:

Nawr gallwch chi weld yn glir bod gwerthoedd y cwrs yn cael eu fframio gan ein tagiau ..., a dyddiadau yn briodoleddau dyddiad mewn tagiau .

I'w hechdynnu, dewiswch golofn o ddeg (neu fwy - os gwneir hynny gydag ymyl) celloedd gwag ar y ddalen (oherwydd bod cyfnod dyddiad 10 diwrnod wedi'i osod) a rhowch y ffwythiant yn y bar fformiwla HIDLYDD.XML (HILYDDXML):

Yma, mae'r ddadl gyntaf yn ddolen i gell ag ymateb gweinydd (B8), ac mae'r ail yn llinyn ymholiad yn XPath, iaith arbennig y gellir ei defnyddio i gael mynediad i'r darnau cod XML angenrheidiol a'u tynnu. Gallwch ddarllen mwy am yr iaith XPath, er enghraifft, yma.

Mae'n bwysig, ar ôl mynd i mewn i'r fformiwla, peidiwch â phwyso Rhowch, a llwybr byr y bysellfwrdd Ctrl+Symud+Rhowch, hy ei nodi fel fformiwla arae (bydd y braces cyrliog o'i gwmpas yn cael eu hychwanegu'n awtomatig). Os oes gennych y fersiwn diweddaraf o Office 365 gyda chefnogaeth ar gyfer araeau deinamig yn Excel, yna fersiwn syml Rhowch, ac nid oes angen i chi ddewis celloedd gwag ymlaen llaw - bydd y swyddogaeth ei hun yn cymryd cymaint o gelloedd ag sydd ei angen.

I dynnu dyddiadau, byddwn yn gwneud yr un peth - byddwn yn dewis sawl cell wag yn y golofn gyfagos ac yn defnyddio'r un swyddogaeth, ond gydag ymholiad XPath gwahanol, i gael holl werthoedd y priodoleddau Dyddiad o'r tagiau Cofnod:

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

Nawr yn y dyfodol, wrth newid y dyddiadau yn y celloedd gwreiddiol B2 a B3 neu ddewis arian cyfred gwahanol yn y gwymplen o gell B3, bydd ein hymholiad yn cael ei ddiweddaru'n awtomatig, gan gyfeirio at weinydd y Banc Canolog am ddata newydd. I orfodi diweddariad â llaw, gallwch hefyd ddefnyddio'r llwybr byr bysellfwrdd Ctrl+Alt+F9.

• Mewnforio cyfradd bitcoin i Excel trwy Power Query
• Mewnforio cyfraddau cyfnewid o'r Rhyngrwyd mewn fersiynau hŷn o Excel