Nilai tukeur diropéa dina Excel

Kuring tos sababaraha kali nganalisis cara pikeun ngimpor data kana Excel tina Internét sareng ngamutahirkeun otomatis salajengna. Khususna:

• Dina vérsi Excel 2007-2013 anu langkung lami, ieu tiasa dilakukeun ku pamundut wéb langsung.
• Dimimitian taun 2010, ieu tiasa dilakukeun kalayan gampang kalayan tambihan Power Query.
  

Pikeun metodeu ieu dina vérsi panganyarna tina Microsoft Excel, anjeun ayeuna tiasa nambihan anu sanés - ngimpor data tina Internét dina format XML nganggo fungsi anu diwangun.

XML (eXtensible Markup Language = Extensible Markup Language) nyaéta basa universal anu dirancang pikeun ngajelaskeun sagala jinis data. Kanyataanna, éta téks polos, tapi kalawan tag husus ditambahkeun kana eta pikeun nandaan up struktur data. Loba loka nyadiakeun aliran bébas data maranéhanana dina format XML keur saha pikeun ngundeur. Dina situs wéb Bank Séntral Nagara Urang (www.cbr.ru), khususna, kalayan bantosan téknologi anu sami, data ngeunaan nilai tukeur tina sababaraha mata uang dipasihkeun. Tina situs wéb Moscow Exchange (www.moex.com) anjeun tiasa ngaunduh tanda petik pikeun saham, obligasi sareng seueur inpormasi mangpaat anu sanés dina cara anu sami.

Kusabab vérsi 2013, Excel ngagaduhan dua pungsi pikeun ngamuat data XML langsung tina Internét kana sél lembar kerja: LAYANAN WEB (LAYANAN WEB) и FILTER.XML (FILTERXML). Aranjeunna dianggo dina pasangan - kahiji fungsi LAYANAN WEB executes pamundut ka situs nu dipikahoyong tur mulih respon na dina format XML, lajeng ngagunakeun fungsi FILTER.XML urang "parse" jawaban ieu kana komponén, extracting data urang kudu ti dinya.

Hayu urang tingali operasi fungsi ieu nganggo conto klasik - ngimpor kurs mata uang naon waé anu urang peryogikeun pikeun interval tanggal anu ditangtukeun tina situs wéb Bank Séntral Nagara Kita. Kami bakal ngagunakeun konstruksi di handap ieu salaku kosong:

Ieuh:

• Sél konéng ngandung tanggal mimiti sareng ahir periode anu dipikaresep ku urang.
• Biru gaduh daptar turun-handap tina mata uang nganggo paréntah Data – Validasi – Daptar (Data — Validasi — Daptar).
• Dina sél héjo, urang bakal ngagunakeun fungsi urang pikeun nyieun string query tur meunangkeun respon server urang.
• Tabel di sisi katuhu mangrupikeun rujukan pikeun kode mata uang (urang peryogi sakedik engké).
  

Hayu angkat!

Lengkah 1. Ngabentuk string query

Pikeun kéngingkeun inpormasi anu diperyogikeun tina situs éta, anjeun kedah naroskeun éta leres. Kami buka www.cbr.ru sareng buka tautan dina footer halaman utama' Sumberdaya Téknis'- Kéngingkeun data nganggo XML (http://cbr.ru/development/SXML/). Urang ngagulung saeutik leuwih handap sarta dina conto kadua (Conto 2) bakal aya naon urang kudu - meunangkeun ongkos bursa keur interval tanggal dibikeun:

Sakumaha anjeun tiasa tingali tina conto, string query kedah ngandung kaping mimiti (date_req1) jeung tungtung (date_req2) tina periode dipikaresep ku urang jeung kode mata uang (VAL_NM_RQ), laju nu urang hayang meunang. Anjeun tiasa mendakan kodeu mata uang utama dina tabel di handap ieu:

Pitunjuk lengkep pikeun kode mata uang ogé sayogi dina situs wéb Bank Séntral - tingali http://cbr.ru/scripts/XML_val.asp?d=0

Ayeuna urang bakal ngabentuk string query dina sél dina lambar kalayan:

• operator concatenation téks (&) pikeun nunda babarengan;
  
• Fitur VPR (VLOOKUP)pikeun milarian kodeu mata uang anu urang peryogikeun dina diréktori;
  
• Fitur naskah (TÉKS), nu ngarobah tanggal nurutkeun pola dibikeun dinten-bulan-taun ngaliwatan slash a.

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

Lengkah 2. Ngaéksekusi pamundut

Ayeuna kami nganggo fungsi LAYANAN WEB (LAYANAN WEB) kalawan string query dihasilkeun salaku hijina argumen. Jawabanna bakal janten garis panjang kode XML (langkung saé pikeun ngaktipkeun bungkus kecap sareng ningkatkeun ukuran sél upami anjeun hoyong ningali éta sadayana):

Lengkah 3. Parsing jawaban

Sangkan leuwih gampang ngartos struktur data respon, eta leuwih hade migunakeun salah sahiji parsers XML online (contona, http://xpather.com/ atawa https://jsonformatter.org/xml-parser), nu visually bisa pormat kode XML, nambahkeun indents ka dinya tur panyorot sintaksis kalawan warna. Teras sadayana bakal langkung jelas:

Ayeuna anjeun jelas tiasa ningali yén nilai kursus dipiguraan ku tag kami ..., sareng kaping mangrupikeun atribut tanggal dina tag .

Pikeun nimba aranjeunna, pilih kolom sapuluh (atanapi langkung - upami dilakukeun ku margin) sél kosong dina lambaran (kusabab interval tanggal 10 dinten disetél) sareng lebetkeun fungsina dina bar rumus. FILTER.XML (SARINGXML):

Di dieu, argumen kahiji nyaéta tumbu ka sél kalawan respon server (B8), sarta kadua mangrupa string query di XPath, basa husus nu bisa dipaké pikeun ngakses fragmen kode XML diperlukeun tur nimba aranjeunna. Anjeun tiasa maca langkung seueur ngeunaan basa XPath, contona, di dieu.

Kadé sanggeus ngasupkeun rumus, ulah mencet asup, sareng potong kompas keyboard Ctrl+ngarobah+asup, nyaéta lebetkeun salaku rumus Asép Sunandar Sunarya (kurung keriting di sabudeureun éta bakal otomatis ditambahkeun). Upami Anjeun gaduh versi panganyarna tina Office 365 kalayan rojongan pikeun arrays dinamis dina Excel, lajeng basajan asup, sareng anjeun henteu kedah milih sél kosong sateuacanna - fungsina sorangan bakal nyandak saloba sél anu diperyogikeun.

Pikeun nimba tanggal, urang bakal ngalakukeun hal anu sami - urang bakal milih sababaraha sél kosong dina kolom anu padeukeut sareng nganggo fungsi anu sami, tapi kalayan query XPath anu béda, pikeun kéngingkeun sadaya nilai atribut Tanggal tina tag Rékam:

=FILTER.XML(B8;”//Rekam/@Tanggal”)

Ayeuna di mangsa nu bakal datang, nalika ngarobah tanggal dina sél aslina B2 na B3 atawa milih mata uang béda dina daptar turun-handap sél B3, pamundut urang bakal otomatis diropéa, ngarujuk kana server Bank Séntral data anyar. Pikeun maksakeun apdet ku cara manual, Anjeun oge bisa make potong kompas keyboard Ctrl+Alt+F9.

• Impor laju bitcoin ka Excel via Power Query
• Impor ongkos bursa tina Internét dina versi Excel heubeul