Nilai tukar sing dianyari ing Excel

Aku wis bola-bali nganalisa cara ngimpor data menyang Excel saka Internet kanthi nganyari otomatis. utamané:

• Ing versi lawas Excel 2007-2013, iki bisa ditindakake kanthi panyuwunan web langsung.
• Wiwit taun 2010, iki bisa ditindakake kanthi trep karo tambahan Power Query.
  

Kanggo metode iki ing versi paling anyar saka Microsoft Excel, sampeyan saiki bisa nambah siji liyane - ngimpor data saka Internet ing format XML nggunakake fungsi dibangun ing.

XML (Extensible Markup Language = Extensible Markup Language) iku basa universal sing dirancang kanggo njlèntrèhaké data apa waé. Nyatane, iki minangka teks biasa, nanging kanthi tag khusus ditambahake kanggo menehi tandha struktur data. Akeh situs nyedhiyakake aliran data gratis ing format XML kanggo diundhuh sapa wae. Ing situs web Bank Sentral Negara Kita (www.cbr.ru), utamane, kanthi bantuan teknologi sing padha, data babagan nilai tukar saka macem-macem mata uang diwenehake. Saka situs web Moscow Exchange (www.moex.com) sampeyan bisa ngundhuh kuotasi kanggo saham, obligasi lan akeh informasi migunani liyane kanthi cara sing padha.

Wiwit versi 2013, Excel nduweni rong fungsi kanggo langsung ngemot data XML saka Internet menyang sel lembar kerja: LAYANAN WEB (LAYANAN WEB) и FILTER.XML (FILTERXML). Padha bisa ing pasangan - pisanan fungsi LAYANAN WEB nglakokake panjalukan menyang situs sing dikarepake lan ngasilake respon ing format XML, banjur nggunakake fungsi kasebut FILTER.XML kita "ngurai" jawaban iki dadi komponen, ngekstrak data sing dibutuhake.

Ayo goleki operasi fungsi kasebut kanthi nggunakake conto klasik - ngimpor kurs mata uang apa wae sing dibutuhake kanggo interval tanggal tartamtu saka situs web Bank Sentral Negara Kita. Kita bakal nggunakake konstruksi ing ngisor iki minangka kothong:

Kene:

• Sèl kuning ngemot tanggal wiwitan lan pungkasan periode kapentingan kanggo kita.
• Sing biru duwe dhaptar gulung mudhun mata uang nggunakake perintah kasebut Data – Validasi – Daftar (Data - Validasi - Dhaptar).
• Ing sel ijo, kita bakal nggunakake fungsi kita kanggo nggawe string query lan njaluk respon server.
• Tabel ing sisih tengen minangka referensi kanggo kode mata uang (kita butuh sethithik mengko).
  

Ayo ayo!

Langkah 1. Nggawe string query

Kanggo entuk informasi sing dibutuhake saka situs kasebut, sampeyan kudu takon kanthi bener. Kita menyang www.cbr.ru lan mbukak link ing footer kaca utama' Sumber Daya Teknis'- Njupuk data nggunakake XML (http://cbr.ru/development/SXML/). Kita nggulung rada ngisor lan ing conto kapindho (Conto 2) bakal ana sing dibutuhake - entuk kurs kanggo interval tanggal tartamtu:

Kaya sing sampeyan deleng saka conto, string pitakon kudu ngemot tanggal wiwitan (tanggal_req1) lan pungkasan (tanggal_req2) saka periode kapentingan kanggo kita lan kode mata uang (VAL_NM_RQ), tingkat sing pengin kita entuk. Sampeyan bisa nemokake kode mata uang utama ing tabel ing ngisor iki:

Pandhuan lengkap kanggo kode mata uang uga kasedhiya ing situs web Bank Sentral - deleng http://cbr.ru/scripts/XML_val.asp?d=0

Saiki kita bakal mbentuk string query ing sel ing sheet kanthi:

• operator concatenation teks (&) kanggo sijine iku bebarengan;
  
• fitur VPR (VLOOKUP)kanggo nemokake kode mata uang sing kita butuhake ing direktori kasebut;
  
• fitur TEXT (TEKS), sing ngowahi tanggal miturut pola tartamtu dina-sasi-taun liwat garis miring.

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

Langkah 2. Nglakokaké panjalukan

Saiki kita nggunakake fungsi kasebut LAYANAN WEB (LAYANAN WEB) karo string query sing digawe minangka siji-sijine argumen. Jawaban bakal dadi baris kode XML sing dawa (luwih apik kanggo nguripake bungkus tembung lan nambah ukuran sel yen sampeyan pengin ndeleng kabeh):

Langkah 3. Parsing jawaban

Kanggo luwih gampang mangerteni struktur data respon, luwih becik nggunakake salah siji parser XML online (contone, http://xpather.com/ utawa https://jsonformatter.org/xml-parser), sing bisa ngowahi format XML kanthi visual, nambah indentasi lan nyorot sintaks kanthi werna. Banjur kabeh bakal dadi luwih jelas:

Saiki sampeyan bisa ndeleng kanthi jelas manawa nilai kursus dibingkai dening tag kita ..., lan tanggal minangka atribut tanggal ing tags .

Kanggo ngekstrak, pilih kolom sepuluh (utawa luwih - yen rampung kanthi wates) sel kosong ing sheet (amarga interval tanggal 10 dina wis disetel) lan ketik fungsi kasebut ing garis rumus. FILTER.XML (FILTERXML):

Ing kene, argumen pisanan minangka link menyang sel kanthi respon server (B8), lan sing kapindho yaiku string query ing XPath, basa khusus sing bisa digunakake kanggo ngakses fragmen kode XML sing dibutuhake lan ngekstrak. Sampeyan bisa maca liyane babagan basa XPath, contone, kene.

Penting yen sawise ngetik rumus, aja pencet Ketik, lan trabasan keyboard Ctrl+shift+Ketik, IE ketik minangka rumus array (kurung kriting watara bakal ditambahake kanthi otomatis). Yen sampeyan duwe versi paling anyar saka Office 365 kanthi dhukungan kanggo susunan dinamis ing Excel, mula gampang Ketik, lan sampeyan ora perlu milih sel kosong luwih dhisik - fungsi kasebut bakal njupuk akeh sel sing dibutuhake.

Kanggo ngekstrak tanggal, kita bakal nindakake perkara sing padha - kita bakal milih sawetara sel kosong ing kolom jejer lan nggunakake fungsi sing padha, nanging kanthi pitakon XPath sing beda, kanggo entuk kabeh nilai atribut Tanggal saka tag Rekam:

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

Saiki ing mangsa ngarep, nalika ngganti tanggal ing sel asli B2 lan B3 utawa milih mata uang sing beda ing dhaptar gulung mudhun sel B3, pitakon kita bakal dianyari kanthi otomatis, ngrujuk menyang server Bank Sentral kanggo data anyar. Kanggo meksa nganyari kanthi manual, sampeyan uga bisa nggunakake trabasan keyboard Ctrl+Alt+F9.

• Ngimpor tarif bitcoin menyang Excel liwat Power Query
• Ngimpor kurs saka Internet ing versi lawas Excel