Թարմացված փոխարժեքը Excel-ում

Ես բազմիցս վերլուծել եմ ինտերնետից տվյալների Excel ներմուծման եղանակները՝ հետագա ավտոմատ թարմացմամբ: Մասնավորապես:

• Excel 2007-2013-ի հին տարբերակներում դա կարելի է անել ուղղակի վեբ հարցումով:
• 2010 թվականից սկսած՝ դա կարելի է շատ հարմար անել Power Query հավելվածի միջոցով:
  

Microsoft Excel-ի վերջին տարբերակների այս մեթոդներին այժմ կարող եք ավելացնել ևս մեկը՝ տվյալների ներմուծում ինտերնետից XML ձևաչափով՝ ներկառուցված գործառույթների միջոցով:

XML-ը (eXtensible Markup Language = Extensible Markup Language) ունիվերսալ լեզու է, որը նախատեսված է ցանկացած տեսակի տվյալներ նկարագրելու համար: Իրականում, դա պարզ տեքստ է, բայց դրան ավելացված հատուկ պիտակներ՝ տվյալների կառուցվածքը նշելու համար: Շատ կայքեր տրամադրում են իրենց տվյալների անվճար հոսքեր XML ձևաչափով, որպեսզի բոլորը կարողանան ներբեռնել: Մեր երկրի կենտրոնական բանկի կայքում (www.cbr.ru), մասնավորապես, նմանատիպ տեխնոլոգիայի միջոցով տրված են տվյալներ տարբեր արժույթների փոխարժեքների վերաբերյալ։ Մոսկվայի բորսայի կայքից (www.moex.com) կարող եք ներբեռնել բաժնետոմսերի, պարտատոմսերի և շատ այլ օգտակար տեղեկություններ նույն կերպ:

2013 թվականի տարբերակից ի վեր, Excel-ն ունի երկու գործառույթ՝ ինտերնետից XML տվյալները աշխատաթերթի բջիջներում ուղղակիորեն բեռնելու համար. ՎԵԲ ՍԵՐՎԻՍ (ՎԵԲՍԵՐՎԻՍ) и FILTER.XML (FILTERXML). Նրանք աշխատում են զույգերով՝ նախ ֆունկցիան ՎԵԲ ՍԵՐՎԻՍ կատարում է հարցումը ցանկալի կայքին և վերադարձնում է դրա պատասխանը XML ձևաչափով, այնուհետև՝ օգտագործելով ֆունկցիան FILTER.XML մենք այս պատասխանը «վերլուծում ենք» բաղադրիչների` դրանից հանելով մեզ անհրաժեշտ տվյալները:

Դիտարկենք այս գործառույթների գործառնությունը՝ օգտագործելով դասական օրինակ՝ ներմուծելով մեզ անհրաժեշտ ցանկացած արժույթի փոխարժեքը տվյալ ամսաթվերի համար մեր երկրի Կենտրոնական բանկի կայքից: Մենք կօգտագործենք հետևյալ կառուցվածքը որպես դատարկ.

Այստեղ `

• Դեղին բջիջները պարունակում են մեզ հետաքրքրող ժամանակաշրջանի սկզբի և ավարտի ամսաթվերը:
• Կապույտն ունի հրամանի օգտագործմամբ արժույթների բացվող ցուցակ Տվյալներ – Վավերացում – Ցուցակ (Տվյալներ — Վավերացում — Ցուցակ).
• Կանաչ բջիջներում մենք կօգտագործենք մեր գործառույթները հարցումների տող ստեղծելու և սերվերի պատասխանը ստանալու համար:
• Աջ կողմի աղյուսակը հղում է արժույթի կոդերին (մեզ այն պետք կգա մի փոքր ուշ):
  

Գնացինք!

Քայլ 1. Հարցման տողի ձևավորում

Կայքից պահանջվող տեղեկատվությունը ստանալու համար անհրաժեշտ է ճիշտ հարցնել: Մենք գնում ենք www.cbr.ru և բացում ենք հղումը գլխավոր էջի ստորոտում։ Տեխնիկական ռեսուրսներ - Տվյալների ստացում XML-ի միջոցով (http://cbr.ru/development/SXML/): Մենք ոլորում ենք մի փոքր ներքև և երկրորդ օրինակում (Օրինակ 2) կլինի այն, ինչ մեզ անհրաժեշտ է՝ ստանալով փոխարժեքները տվյալ ամսաթվի միջակայքի համար.

Ինչպես տեսնում եք օրինակից, հարցման տողը պետք է պարունակի մեկնարկի ամսաթվեր (date_req1) և վերջավորություններ (date_req2) մեզ հետաքրքրող ժամանակաշրջանի և արժույթի կոդը (VAL_NM_RQ), որի դրույքաչափը մենք ուզում ենք ստանալ։ Հիմնական արժույթի կոդերը կարող եք գտնել ստորև բերված աղյուսակում.

Արտարժույթի կոդերի ամբողջական ուղեցույցը հասանելի է նաև Կենտրոնական բանկի կայքում. տե՛ս http://cbr.ru/scripts/XML_val.asp?d=0

Այժմ թերթի վրա գտնվող բջիջում հարցման տող կձևավորենք հետևյալով.

• տեքստի կապակցման օպերատորը (&)՝ այն միասին դնելու համար;
  
• Հատկություններ VPR- ը (VLOOKUP)գրացուցակում գտնել մեզ անհրաժեշտ արժույթի կոդը.
  
• Հատկություններ TEXT (ՏԵՔՍՏ), որը փոխակերպում է ամսաթիվը ըստ տրված օրինաչափության օր-ամիս-տարի կտրվածքի միջոցով:

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

Քայլ 2. Կատարեք հարցումը

Այժմ մենք օգտագործում ենք գործառույթը ՎԵԲ ՍԵՐՎԻՍ (ՎԵԲՍԵՐՎԻՍ) գեներացված հարցման տողը որպես միակ արգումենտ: Պատասխանը կլինի XML կոդի երկար տող (ավելի լավ է միացնել բառի փաթեթավորումը և ավելացնել բջիջի չափը, եթե ցանկանում եք այն ամբողջությամբ տեսնել):

Քայլ 3. Պատասխանի վերլուծություն

Պատասխանների տվյալների կառուցվածքն ավելի հեշտ հասկանալու համար ավելի լավ է օգտագործել առցանց XML վերլուծիչներից մեկը (օրինակ՝ http://xpather.com/ կամ https://jsonformatter.org/xml-parser), որը կարող է տեսողականորեն ձևաչափել XML կոդը՝ դրան ավելացնելով նահանջներ և ընդգծելով շարահյուսությունը գույներով։ Այնուհետև ամեն ինչ շատ ավելի պարզ կդառնա.

Այժմ դուք կարող եք հստակ տեսնել, որ դասընթացի արժեքները շրջանակված են մեր պիտակներով ..., իսկ ամսաթվերը ատրիբուտներ են Ամսաթիվ պիտակներում .

Դրանք հանելու համար թերթիկի վրա ընտրեք տասը (կամ ավելի, եթե արված է լուսանցքով) դատարկ բջիջներից բաղկացած սյունակ (քանի որ 10-օրյա ամսաթվի ընդմիջում է սահմանված) և մուտքագրեք ֆունկցիան բանաձևի տողում։ FILTER.XML (ԶԻՏՐXML):

Այստեղ առաջին արգումենտը սերվերի պատասխանով (B8) բջիջի հղումն է, իսկ երկրորդը XPath-ի հարցման տող է՝ հատուկ լեզու, որը կարող է օգտագործվել անհրաժեշտ XML կոդի բեկորները մուտք գործելու և դրանք հանելու համար: Դուք կարող եք կարդալ ավելին XPath լեզվի մասին, օրինակ, այստեղ:

Կարևոր է, որ բանաձևը մուտքագրելուց հետո չսեղմեք Մտնելև ստեղնաշարի դյուրանցումը Ctrl+Հերթափոխություն+Մտնել, այսինքն մուտքագրեք այն որպես զանգվածի բանաձև (դրա շուրջը գանգուր փակագծերը կավելացվեն ավտոմատ կերպով): Եթե ​​ունեք Office 365-ի վերջին տարբերակը՝ Excel-ում դինամիկ զանգվածների աջակցությամբ, ապա պարզ Մտնել, և ձեզ հարկավոր չէ նախապես դատարկ բջիջներ ընտրել. գործառույթն ինքնին կվերցնի այնքան բջիջ, որքան անհրաժեշտ է:

Ամսաթվերը հանելու համար մենք կանենք նույնը. մենք կընտրենք մի քանի դատարկ բջիջ հարակից սյունակում և կօգտագործենք նույն գործառույթը, բայց մեկ այլ XPath հարցումով, որպեսզի ստանանք Date ատրիբուտների բոլոր արժեքները Record tags-ից.

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

Այժմ ապագայում, B2 և B3 բջջի սկզբնական բջիջների ամսաթվերը փոխելիս կամ B3 բջիջի բացվող ցանկում այլ արժույթ ընտրելիս, մեր հարցումը ավտոմատ կերպով կթարմացվի՝ հղում անելով Կենտրոնական բանկի սերվերին՝ նոր տվյալների համար: Ձեռքով թարմացում պարտադրելու համար կարող եք լրացուցիչ օգտագործել ստեղնաշարի դյուրանցումը Ctrl+ալտ+F9.

• Ներմուծեք բիթքոինի տոկոսադրույքը Excel-ի միջոցով Power Query-ի միջոցով
• Ներմուծեք փոխարժեքներ ինտերնետից Excel-ի հին տարբերակներում