อัปเดตอัตราแลกเปลี่ยนใน Excel

ฉันได้วิเคราะห์วิธีการนำเข้าข้อมูลไปยัง Excel จากอินเทอร์เน็ตซ้ำแล้วซ้ำเล่าด้วยการอัปเดตอัตโนมัติในภายหลัง โดยเฉพาะอย่างยิ่ง:

• ใน Excel 2007-2013 เวอร์ชันเก่า สามารถทำได้โดยใช้คำขอทางเว็บโดยตรง
• เริ่มต้นในปี 2010 สามารถทำได้สะดวกด้วย Add-in ของ Power Query
  

สำหรับวิธีการเหล่านี้ใน Microsoft Excel เวอร์ชันล่าสุด คุณสามารถเพิ่มวิธีอื่นได้ นั่นคือการนำเข้าข้อมูลจากอินเทอร์เน็ตในรูปแบบ XML โดยใช้ฟังก์ชันในตัว

XML (eXtensible Markup Language = Extensible Markup Language) เป็นภาษาสากลที่ออกแบบมาเพื่ออธิบายข้อมูลทุกประเภท อันที่จริง มันเป็นข้อความธรรมดา แต่มีแท็กพิเศษที่เพิ่มเข้าไปเพื่อทำเครื่องหมายโครงสร้างข้อมูล ไซต์จำนวนมากให้สตรีมข้อมูลในรูปแบบ XML ฟรีสำหรับทุกคนในการดาวน์โหลด บนเว็บไซต์ของธนาคารกลางของประเทศของเรา (www.cbr.ru) โดยเฉพาะอย่างยิ่งด้วยเทคโนโลยีที่คล้ายคลึงกันจะมีการให้ข้อมูลเกี่ยวกับอัตราแลกเปลี่ยนของสกุลเงินต่างๆ จากเว็บไซต์แลกเปลี่ยนมอสโก (www.moex.com) คุณสามารถดาวน์โหลดราคาหุ้น พันธบัตร และข้อมูลที่เป็นประโยชน์อื่นๆ ได้ในลักษณะเดียวกัน

ตั้งแต่เวอร์ชัน 2013 Excel มีสองฟังก์ชันสำหรับการโหลดข้อมูล XML จากอินเทอร์เน็ตลงในเซลล์เวิร์กชีตโดยตรง: บริการเว็บ (บริการเว็บ) и ฟิลเตอร์.XML (ตัวกรองXML). พวกเขาทำงานเป็นคู่ – อันดับแรกคือฟังก์ชั่น บริการเว็บ ดำเนินการร้องขอไปยังไซต์ที่ต้องการและส่งคืนการตอบกลับในรูปแบบ XML จากนั้นใช้ฟังก์ชัน ฟิลเตอร์.XML เรา "แยกวิเคราะห์" คำตอบนี้เป็นส่วนประกอบ โดยแยกข้อมูลที่เราต้องการจากคำตอบนั้น

ลองดูการทำงานของฟังก์ชันเหล่านี้โดยใช้ตัวอย่างคลาสสิก การนำเข้าอัตราแลกเปลี่ยนของสกุลเงินใด ๆ ที่เราต้องการสำหรับช่วงวันที่ที่กำหนดจากเว็บไซต์ของธนาคารกลางของประเทศของเรา เราจะใช้โครงสร้างต่อไปนี้เป็นช่องว่าง:

ที่นี่:

• เซลล์สีเหลืองประกอบด้วยวันที่เริ่มต้นและสิ้นสุดของระยะเวลาที่เราสนใจ
• สีน้ำเงินมีรายการสกุลเงินแบบดรอปดาวน์โดยใช้คำสั่ง ข้อมูล – การตรวจสอบ – รายการ (ข้อมูล — การตรวจสอบ — รายการ).
• ในเซลล์สีเขียว เราจะใช้ฟังก์ชันของเราเพื่อสร้างสตริงการสืบค้นและรับการตอบกลับจากเซิร์ฟเวอร์
• ตารางทางด้านขวาคือการอ้างอิงถึงรหัสสกุลเงิน (เราจะต้องใช้ในภายหลัง)
  

ไปกันเถอะ!

ขั้นตอนที่ 1 การสร้างสตริงการสืบค้น

ในการรับข้อมูลที่จำเป็นจากเว็บไซต์ คุณต้องถามให้ถูกต้อง เราไปที่ www.cbr.ru และเปิดลิงก์ในส่วนท้ายของหน้าหลัก' ทรัพยากรทางเทคนิค'- การรับข้อมูลโดยใช้ XML (http://cbr.ru/development/SXML/) เราเลื่อนลงมาด้านล่างเล็กน้อย และในตัวอย่างที่สอง (ตัวอย่างที่ 2) จะมีสิ่งที่เราต้องการ – รับอัตราแลกเปลี่ยนสำหรับช่วงวันที่ที่กำหนด:

ดังที่คุณเห็นจากตัวอย่าง สตริงการสืบค้นต้องมีวันที่เริ่มต้น (date_req1) และตอนจบ (date_req2) ของระยะเวลาที่เราสนใจและรหัสสกุลเงิน (VAL_NM_RQ) อัตราที่เราต้องการได้ คุณสามารถค้นหารหัสสกุลเงินหลักได้ในตารางด้านล่าง:

คู่มือฉบับสมบูรณ์เกี่ยวกับรหัสสกุลเงินมีอยู่ในเว็บไซต์ของธนาคารกลาง - ดู http://cbr.ru/scripts/XML_val.asp?d=0

ตอนนี้เราจะสร้างสตริงข้อความค้นหาในเซลล์บนแผ่นงานด้วย:

• ตัวดำเนินการต่อข้อความ (&) เพื่อรวมเข้าด้วยกัน
  
• คุณสมบัติ VPR (วีลุคอัพ)เพื่อค้นหารหัสของสกุลเงินที่เราต้องการในไดเร็กทอรี
  
• คุณสมบัติ TEXT (ข้อความ)ซึ่งแปลงวันที่ตามรูปแบบที่กำหนด วัน เดือน ปี ผ่านเครื่องหมายทับ

="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 เซลล์ (หรือมากกว่า – ถ้าทำด้วยระยะขอบ) บนแผ่นงาน (เนื่องจากมีการตั้งค่าช่วงวันที่ XNUMX วัน) และป้อนฟังก์ชันในแถบสูตร ฟิลเตอร์.XML (กรองXML):

ในที่นี้ อาร์กิวเมนต์แรกคือลิงก์ไปยังเซลล์ที่มีการตอบสนองของเซิร์ฟเวอร์ (B8) และอาร์กิวเมนต์ที่สองคือสตริงการสืบค้นใน XPath ซึ่งเป็นภาษาพิเศษที่สามารถใช้เข้าถึงส่วนย่อยของโค้ด XML ที่จำเป็นและดึงข้อมูลออก คุณสามารถอ่านเพิ่มเติมเกี่ยวกับภาษา XPath ได้ที่นี่

ที่สำคัญหลังจากใส่สูตรแล้วห้ามกด เข้าสู่และแป้นพิมพ์ลัด Ctrl+เปลี่ยน+เข้าสู่กล่าวคือป้อนเป็นสูตรอาร์เรย์ (วงเล็บปีกการอบ ๆ จะถูกเพิ่มโดยอัตโนมัติ) หากคุณมี Office 365 เวอร์ชันล่าสุดพร้อมรองรับไดนามิกอาร์เรย์ใน Excel แสดงว่า เข้าสู่และคุณไม่จำเป็นต้องเลือกเซลล์ว่างล่วงหน้า ฟังก์ชันนี้ใช้เซลล์ได้มากเท่าที่ต้องการ

ในการดึงข้อมูลวันที่ เราจะทำเช่นเดียวกัน – เราจะเลือกเซลล์ว่างหลายเซลล์ในคอลัมน์ที่อยู่ติดกันและใช้ฟังก์ชันเดียวกัน แต่ด้วยแบบสอบถาม XPath อื่น เพื่อรับค่าทั้งหมดของแอตทริบิวต์ Date จากแท็ก Record:

=FILTER.XML(B8;”//บันทึก/@วันที่”)

ในอนาคต เมื่อเปลี่ยนวันที่ในเซลล์เดิม B2 และ B3 หรือเลือกสกุลเงินอื่นในรายการแบบหล่นลงของเซลล์ B3 การสืบค้นของเราจะอัปเดตโดยอัตโนมัติ โดยอ้างอิงถึงเซิร์ฟเวอร์ของธนาคารกลางสำหรับข้อมูลใหม่ หากต้องการบังคับการอัปเดตด้วยตนเอง คุณสามารถใช้แป้นพิมพ์ลัดเพิ่มเติม Ctrl+อื่น ๆ+F9.

• นำเข้าอัตรา bitcoin ไปยัง Excel ผ่าน Power Query
• นำเข้าอัตราแลกเปลี่ยนจากอินเทอร์เน็ตใน Excel รุ่นเก่ากว่า