Excelda yangilangan valyuta kursi

Men bir necha bor ma'lumotlarni Excelga Internetdan avtomatik yangilash bilan import qilish usullarini tahlil qildim. Ayniqsa:

• Excel 2007-2013 ning eski versiyalarida bu to'g'ridan-to'g'ri veb-so'rov bilan amalga oshirilishi mumkin.
• 2010 yildan boshlab, bu Power Query plaginida juda qulay tarzda amalga oshirilishi mumkin.
  

Microsoft Excelning so'nggi versiyalaridagi ushbu usullarga endi boshqasini qo'shishingiz mumkin - o'rnatilgan funktsiyalardan foydalangan holda Internetdan ma'lumotlarni XML formatida import qilish.

XML (eXtensible Markup Language = Extensible Markup Language) har qanday turdagi ma'lumotlarni tavsiflash uchun mo'ljallangan universal tildir. Aslida, bu oddiy matn, lekin ma'lumotlar strukturasini belgilash uchun unga maxsus teglar qo'shilgan. Ko'pgina saytlar har kim yuklab olishi uchun XML formatida o'z ma'lumotlarining bepul oqimini taqdim etadi. Mamlakatimiz Markaziy bankining veb-saytida (www.cbr.ru), xususan, shunga o'xshash texnologiya yordamida turli valyutalarning kurslari to'g'risidagi ma'lumotlar keltirilgan. Moskva birjasi veb-saytidan (www.moex.com) siz aksiyalar, obligatsiyalar va boshqa ko'plab foydali ma'lumotlar uchun kotirovkalarni xuddi shu tarzda yuklab olishingiz mumkin.

2013-yildan beri Excel XML ma'lumotlarini Internetdan ishchi varaq hujayralariga to'g'ridan-to'g'ri yuklash uchun ikkita funktsiyaga ega: WEB XIZMAT (WEBSXIZMAT) и FILTER.XML (FILTERXML). Ular juftlikda ishlaydi - birinchi navbatda funktsiya WEB XIZMAT kerakli saytga so'rovni bajaradi va uning javobini XML formatida qaytaradi, so'ngra funksiyadan foydalanadi FILTER.XML biz ushbu javobni tarkibiy qismlarga ajratamiz va undan kerakli ma'lumotlarni chiqaramiz.

Keling, klassik misol yordamida ushbu funktsiyalarning ishlashini ko'rib chiqaylik - mamlakatimiz Markaziy banki veb-saytidan ma'lum bir sana oralig'ida bizga kerak bo'lgan istalgan valyuta kursini import qilish. Bo'sh joy sifatida biz quyidagi konstruktsiyadan foydalanamiz:

Bu yerga:

• Sariq hujayralar bizni qiziqtirgan davrning boshlanish va tugash sanalarini o'z ichiga oladi.
• Ko'k rangda buyruq yordamida valyutalarning ochiladigan ro'yxati mavjud Ma'lumotlar - Tasdiqlash - Ro'yxat (Ma'lumotlar - Tasdiqlash - Ro'yxat).
• Yashil katakchalarda biz so'rovlar qatorini yaratish va serverning javobini olish uchun o'z funksiyalarimizdan foydalanamiz.
• O'ngdagi jadval valyuta kodlariga havola (bu bizga birozdan keyin kerak bo'ladi).
  

Qani ketdik!

Qadam 1. So'rovlar qatorini shakllantirish

Saytdan kerakli ma'lumotlarni olish uchun uni to'g'ri so'rash kerak. Biz www.cbr.ru saytiga o'tamiz va asosiy sahifaning pastki qismidagi havolani ochamiz. Texnik manbalar - XML yordamida ma'lumotlarni olish (http://cbr.ru/development/SXML/). Biz biroz pastga tushamiz va ikkinchi misolda (2-misol) bizga kerak bo'lgan narsa bo'ladi - ma'lum bir sana oralig'ida valyuta kurslarini olish:

Misoldan ko'rinib turibdiki, so'rovlar qatorida boshlanish sanalari bo'lishi kerak (date_req1) va oxiri (date_req2) bizni qiziqtirgan davr va valyuta kodi (VAL_NM_RQ), biz olishni istagan stavka. Siz asosiy valyuta kodlarini quyidagi jadvalda topishingiz mumkin:

Valyuta kodlari bo'yicha to'liq qo'llanma Markaziy bankning veb-saytida ham mavjud - qarang: http://cbr.ru/scripts/XML_val.asp?d=0

Endi biz quyidagi varaqdagi katakchada so'rovlar qatorini hosil qilamiz:

• uni birlashtirish uchun matnni birlashtirish operatori (&);
  
• Xususiyatlari VPR (KO'RISH)katalogdan bizga kerak bo'lgan valyuta kodini topish;
  
• Xususiyatlari TEXT (MATN), qaysi kun-oy-yil berilgan naqshga muvofiq sanani qiyshiq chiziq orqali aylantiradi.

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

Qadam 2. So'rovni bajaring

Endi biz funktsiyadan foydalanamiz WEB XIZMAT (WEBSXIZMAT) yagona argument sifatida yaratilgan so'rovlar qatori bilan. Javob XML kodining uzun qatori bo'ladi (agar siz uni to'liq ko'rishni istasangiz, so'zlarni o'rashni yoqing va hujayra hajmini oshiring):

3-qadam. Javobni tahlil qilish

Javob ma'lumotlarining tuzilishini tushunishni osonlashtirish uchun onlayn XML tahlilchilaridan birini ishlatish yaxshiroqdir (masalan, http://xpather.com/ yoki https://jsonformatter.org/xml-parser), XML kodini vizual ravishda formatlash, unga chekinishlar qo'shish va sintaksisni rang bilan ta'kidlash mumkin. Keyin hamma narsa aniqroq bo'ladi:

Endi siz kurs qiymatlari bizning teglar bilan belgilanganligini aniq ko'rishingiz mumkin ..., va sanalar atributlardir sana teglarda .

Ularni ajratib olish uchun varaqdagi o'nta (yoki undan ko'p - chegara bilan qilingan bo'lsa) bo'sh katakchalardan iborat ustunni tanlang (chunki 10 kunlik sana oralig'i o'rnatilgan) va funktsiyani formulalar qatoriga kiriting. FILTER.XML (FILTRXML):

Bu erda birinchi argument server javobiga ega bo'lgan katakchaga havola (B8), ikkinchisi esa XPath-dagi so'rovlar qatori bo'lib, zarur XML kod fragmentlariga kirish va ularni ajratib olish uchun ishlatilishi mumkin bo'lgan maxsus tildir. XPath tili haqida ko'proq o'qishingiz mumkin, masalan, bu erda.

Formulani kiritgandan so'ng, bosmaslik muhimdir kiriting, va klaviatura yorlig'i Ctrl+smena+kiriting, ya'ni uni massiv formulasi sifatida kiriting (uning atrofidagi jingalak qavslar avtomatik ravishda qo'shiladi). Agar sizda Excel-da dinamik massivlarni qo'llab-quvvatlaydigan Office 365-ning so'nggi versiyasi bo'lsa, unda oddiy kiriting, va oldindan bo'sh katakchalarni tanlash shart emas - funksiyaning o'zi qancha kerak bo'lsa, shuncha hujayra oladi.

Sanalarni chiqarish uchun biz xuddi shunday qilamiz - qo'shni ustundagi bir nechta bo'sh katakchalarni tanlaymiz va bir xil funktsiyadan foydalanamiz, lekin Record teglaridan Sana atributlarining barcha qiymatlarini olish uchun boshqa XPath so'rovi bilan:

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

Endi kelajakda, B2 va B3 asl katakchalaridagi sanalarni o'zgartirganda yoki B3 katakchaning ochiladigan ro'yxatida boshqa valyutani tanlashda bizning so'rovimiz yangi ma'lumotlar uchun Markaziy bank serveriga murojaat qilib, avtomatik ravishda yangilanadi. Yangilashni qo'lda majburlash uchun siz qo'shimcha ravishda klaviatura yorliqlaridan foydalanishingiz mumkin Ctrl+Alt+F9.

• Power Query orqali bitcoin kursini Excelga import qiling
• Excelning eski versiyalarida Internetdan valyuta kurslarini import qiling