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) бидэнд хэрэгтэй зүйл байх болно - өгөгдсөн огнооны интервалын ханшийг авах:

Жишээнээс харахад асуулгын мөр нь эхлэх огноог агуулсан байх ёстой (огноо_шалгалт1) ба төгсгөлүүд (огноо_шалгалт2) бидний сонирхож буй хугацаа болон валютын код (VAL_NM_RQ), бидний авахыг хүсч буй хувь хэмжээ. Та доорх хүснэгтээс үндсэн валютын кодуудыг олж болно.

Мөнгөний кодын бүрэн гарын авлагыг Төв банкны вэбсайтаас авах боломжтой - http://cbr.ru/scripts/XML_val.asp?d=0-г үзнэ үү.

Одоо бид хуудасны нүдэнд асуулгын мөр үүсгэх болно:

• текстийг нэгтгэх оператор (&) үүнийг нэгтгэх;
  
• онцлог VPR (ХАРАХ)лавлахаас бидэнд хэрэгтэй валютын кодыг олох;
  
• онцлог Текст (Текст), энэ нь огноог өгөгдсөн загварын өдөр-сар-жилийн дагуу налуу зураасаар хөрвүүлдэг.

="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) бүхий нүд рүү холбох холбоос, хоёр дахь нь шаардлагатай XML кодын фрагментуудад хандаж, тэдгээрийг задлахад ашиглаж болох тусгай хэл болох XPath дахь асуулгын мөр юм. Жишээлбэл, та XPath хэлний талаар илүү ихийг эндээс уншиж болно.

Томьёог оруулсны дараа дарахгүй байх нь чухал юм оруулна уу, болон гарын товчлол Ctrl+Shift+оруулна уу, өөрөөр хэлбэл массивын томьёо болгон оруулна (түүний эргэн тойрон дахь буржгар хаалт автоматаар нэмэгдэх болно). Хэрэв танд Excel-ийн динамик массивыг дэмждэг Office 365-ийн хамгийн сүүлийн хувилбар байгаа бол энгийн оруулна уу, мөн та хоосон нүднүүдийг урьдчилан сонгох шаардлагагүй - функц өөрөө шаардлагатай тооны нүдийг авах болно.

Огноог задлахын тулд бид ижил зүйлийг хийх болно - бид зэргэлдээ баганын хэд хэдэн хоосон нүдийг сонгоод ижил функцийг ашиглана, гэхдээ өөр XPath асуулга ашиглан Бичлэгийн шошгон дээрх Date шинж чанаруудын бүх утгыг авна.

=FILTER.XML(B8;”//Бичлэг/@Огноо”)

Цаашид B2 ба B3 нүднүүдийн огноог өөрчлөх эсвэл B3 нүдний унадаг жагсаалтаас өөр валют сонгох үед бидний хүсэлт автоматаар шинэчлэгдэж, Төв банкны серверээс шинэ мэдээлэл авах болно. Шинэчлэлтийг гараар оруулахын тулд та гарын товчлолыг нэмж ашиглаж болно Ctrl+Alt+F9.

• Power Query-ээр дамжуулан Excel-д биткойны ханшийг импортлох
• Excel-ийн хуучин хувилбаруудад интернетээс валютын ханшийг импортлох