Kadar pertukaran yang dikemas kini dalam Excel

Saya telah berulang kali menganalisis cara untuk mengimport data ke dalam Excel daripada Internet dengan pengemaskinian automatik seterusnya. khususnya:

• Dalam versi Excel 2007-2013 yang lebih lama, ini boleh dilakukan dengan permintaan web langsung.
• Mulai tahun 2010, ini boleh dilakukan dengan sangat mudah dengan tambahan Power Query.
  

Untuk kaedah ini dalam versi terkini Microsoft Excel, anda kini boleh menambah satu lagi – mengimport data daripada Internet dalam format XML menggunakan fungsi terbina dalam.

XML (Bahasa Penanda Boleh Diperluas = Bahasa Penanda Boleh Diperluas) ialah bahasa universal yang direka untuk menerangkan sebarang jenis data. Sebenarnya, ia adalah teks biasa, tetapi dengan teg khas ditambahkan padanya untuk menandakan struktur data. Banyak tapak menyediakan aliran percuma data mereka dalam format XML untuk dimuat turun oleh sesiapa sahaja. Di laman web Bank Pusat Negara Kita (www.cbr.ru), khususnya, dengan bantuan teknologi yang sama, data mengenai kadar pertukaran pelbagai mata wang diberikan. Dari laman web Moscow Exchange (www.moex.com) anda boleh memuat turun sebut harga untuk saham, bon dan banyak maklumat berguna lain dengan cara yang sama.

Sejak versi 2013, Excel mempunyai dua fungsi untuk memuatkan terus data XML daripada Internet ke dalam sel lembaran kerja: PERKHIDMATAN WEB (PERKHIDMATAN WEB) и FILTER.XML (FILTERXML). Mereka bekerja secara berpasangan - pertama fungsi PERKHIDMATAN WEB melaksanakan permintaan ke tapak yang dikehendaki dan mengembalikan responsnya dalam format XML, dan kemudian menggunakan fungsi tersebut FILTER.XML kami "menghuraikan" jawapan ini kepada komponen, mengekstrak data yang kami perlukan daripadanya.

Mari kita lihat operasi fungsi ini menggunakan contoh klasik – mengimport kadar pertukaran mana-mana mata wang yang kita perlukan untuk selang tarikh tertentu daripada tapak web Bank Negara Kita. Kami akan menggunakan pembinaan berikut sebagai kosong:

Di sini:

• Sel kuning mengandungi tarikh mula dan tamat tempoh yang diminati oleh kami.
• Yang biru mempunyai senarai juntai bawah mata wang menggunakan arahan Data – Pengesahan – Senarai (Data — Pengesahan — Senarai).
• Dalam sel hijau, kami akan menggunakan fungsi kami untuk mencipta rentetan pertanyaan dan mendapatkan respons pelayan.
• Jadual di sebelah kanan adalah rujukan kepada kod mata wang (kami akan memerlukannya sedikit kemudian).
  

Jom!

Langkah 1. Membentuk rentetan pertanyaan

Untuk mendapatkan maklumat yang diperlukan dari tapak, anda perlu bertanya dengan betul. Kami pergi ke www.cbr.ru dan membuka pautan di pengaki halaman utama' Sumber Teknikal'- Mendapatkan data menggunakan XML (http://cbr.ru/development/SXML/). Kami menatal lebih rendah sedikit dan dalam contoh kedua (Contoh 2) akan ada perkara yang kami perlukan - mendapatkan kadar pertukaran untuk selang tarikh tertentu:

Seperti yang anda lihat daripada contoh, rentetan pertanyaan mesti mengandungi tarikh mula (tarikh_req1) dan kesudahan (tarikh_req2) tempoh faedah kepada kami dan kod mata wang (VAL_NM_RQ), kadar yang kita mahu dapatkan. Anda boleh menemui kod mata wang utama dalam jadual di bawah:

Panduan lengkap untuk kod mata wang juga tersedia di laman web Bank Pusat – lihat http://cbr.ru/scripts/XML_val.asp?d=0

Sekarang kita akan membentuk rentetan pertanyaan dalam sel pada helaian dengan:

• operator penggabungan teks (&) untuk meletakkannya bersama;
  
• Ciri-ciri VPR (TAMPILAN)untuk mencari kod mata wang yang kami perlukan dalam direktori;
  
• Ciri-ciri TEXT (TEKS), yang menukar tarikh mengikut corak yang diberikan hari-bulan-tahun melalui 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. Laksanakan permintaan

Sekarang kita menggunakan fungsi tersebut PERKHIDMATAN WEB (PERKHIDMATAN WEB) dengan rentetan pertanyaan yang dijana sebagai satu-satunya hujah. Jawapannya ialah barisan panjang kod XML (lebih baik untuk menghidupkan bungkus perkataan dan meningkatkan saiz sel jika anda ingin melihatnya secara keseluruhan):

Langkah 3. Menghuraikan jawapan

Untuk menjadikannya lebih mudah untuk memahami struktur data respons, lebih baik menggunakan salah satu penghurai XML dalam talian (contohnya, http://xpather.com/ atau https://jsonformatter.org/xml-parser), yang boleh memformat kod XML secara visual, menambah inden padanya dan menyerlahkan sintaks dengan warna. Kemudian semuanya akan menjadi lebih jelas:

Kini anda dapat melihat dengan jelas bahawa nilai kursus dibingkai oleh tag kami ..., dan tarikh ialah atribut Tarikh dalam tag .

Untuk mengekstraknya, pilih lajur sepuluh (atau lebih – jika dilakukan dengan margin) sel kosong pada helaian (kerana selang tarikh 10 hari telah ditetapkan) dan masukkan fungsi dalam bar formula FILTER.XML (PENAPISXML):

Di sini, hujah pertama ialah pautan ke sel dengan respons pelayan (B8), dan yang kedua ialah rentetan pertanyaan dalam XPath, bahasa khas yang boleh digunakan untuk mengakses serpihan kod XML yang diperlukan dan mengekstraknya. Anda boleh membaca lebih lanjut tentang bahasa XPath, sebagai contoh, di sini.

Adalah penting bahawa selepas memasukkan formula, jangan tekan Masukkan, dan pintasan papan kekunci Ctrl+Beralih+Masukkan, iaitu masukkannya sebagai formula tatasusunan (kurung kerinting di sekelilingnya akan ditambah secara automatik). Jika anda mempunyai versi terkini Office 365 dengan sokongan untuk tatasusunan dinamik dalam Excel, maka mudah Masukkan, dan anda tidak perlu memilih sel kosong terlebih dahulu – fungsi itu sendiri akan mengambil seberapa banyak sel yang diperlukan.

Untuk mengekstrak tarikh, kami akan melakukan perkara yang sama – kami akan memilih beberapa sel kosong dalam lajur bersebelahan dan menggunakan fungsi yang sama, tetapi dengan pertanyaan XPath yang berbeza, untuk mendapatkan semua nilai atribut Tarikh daripada teg Rekod:

=FILTER.XML(B8;”//Rekod/@Tarikh”)

Kini pada masa hadapan, apabila menukar tarikh dalam sel asal B2 dan B3 atau memilih mata wang yang berbeza dalam senarai juntai bawah sel B3, pertanyaan kami akan dikemas kini secara automatik, merujuk kepada pelayan Bank Pusat untuk data baharu. Untuk memaksa kemas kini secara manual, anda juga boleh menggunakan pintasan papan kekunci Ctrl+Alt+F9.

• Import kadar bitcoin ke Excel melalui Power Query
• Import kadar pertukaran daripada Internet dalam versi Excel yang lebih lama