ປັບປຸງອັດຕາແລກປ່ຽນໃນ Excel

ຂ້ອຍໄດ້ວິເຄາະວິທີການນໍາເຂົ້າຂໍ້ມູນເຂົ້າໄປໃນ Excel ຈາກອິນເຕີເນັດຫຼາຍຄັ້ງດ້ວຍການອັບເດດອັດຕະໂນມັດຕໍ່ມາ. ໂດຍ​ສະ​ເພາະ:

• ໃນ Excel ຮຸ່ນເກົ່າ 2007-2013, ນີ້ສາມາດເຮັດໄດ້ດ້ວຍການຮ້ອງຂໍເວັບໄຊຕ໌ໂດຍກົງ.
• ເລີ່ມຕົ້ນໃນປີ 2010, ນີ້ສາມາດເຮັດໄດ້ຢ່າງສະດວກຫຼາຍດ້ວຍການເພີ່ມ Power Query.
  

ໃນວິທີການເຫຼົ່ານີ້ໃນ Microsoft Excel ຮຸ່ນຫຼ້າສຸດ, ຕອນນີ້ທ່ານສາມາດເພີ່ມອີກອັນຫນຶ່ງ - ການນໍາເຂົ້າຂໍ້ມູນຈາກອິນເຕີເນັດໃນຮູບແບບ XML ໂດຍໃຊ້ຟັງຊັນໃນຕົວ.

XML (eXtensible Markup Language = Extensible Markup Language) ເປັນພາສາສາກົນທີ່ອອກແບບມາເພື່ອອະທິບາຍຂໍ້ມູນປະເພດໃດນຶ່ງ. ໃນຄວາມເປັນຈິງ, ມັນເປັນຂໍ້ຄວາມທໍາມະດາ, ແຕ່ມີແທັກພິເສດທີ່ເພີ່ມໃສ່ມັນເພື່ອເຮັດເຄື່ອງຫມາຍເຖິງໂຄງສ້າງຂໍ້ມູນ. ຫຼາຍໆເວັບໄຊສະໜອງການຖ່າຍທອດຂໍ້ມູນຂອງເຂົາເຈົ້າໃນຮູບແບບ XML ໃຫ້ກັບທຸກຄົນເພື່ອດາວໂຫຼດ. ຢູ່ໃນເວັບໄຊທ໌ຂອງທະນາຄານກາງຂອງປະເທດຂອງພວກເຮົາ (www.cbr.ru), ໂດຍສະເພາະ, ດ້ວຍການຊ່ວຍເຫຼືອຂອງເຕັກໂນໂລຢີທີ່ຄ້າຍຄືກັນ, ຂໍ້ມູນກ່ຽວກັບອັດຕາແລກປ່ຽນຂອງສະກຸນເງິນຕ່າງໆແມ່ນໄດ້ຮັບ. ຈາກເວັບໄຊທ໌ Moscow Exchange (www.moex.com) ທ່ານສາມາດດາວໂຫລດວົງຢືມສໍາລັບຫຼັກຊັບ, ພັນທະບັດແລະຂໍ້ມູນທີ່ເປັນປະໂຫຍດອື່ນໆຫຼາຍໃນລັກສະນະດຽວກັນ.

ນັບຕັ້ງແ​​ຕ່ສະບັບ 2013, Excel ມີສອງຫນ້າທີ່ສໍາລັບການໂຫຼດໂດຍກົງຂໍ້ມູນ XML ຈາກອິນເຕີເນັດເຂົ້າໄປໃນຈຸລັງແຜ່ນ: ການບໍລິການເວັບ (ບໍລິການເວັບ) и FILTER.XML (FILTERXML). ພວກເຂົາເຮັດວຽກເປັນຄູ່ - ທໍາອິດແມ່ນຫນ້າທີ່ ການບໍລິການເວັບ ປະຕິບັດການຮ້ອງຂໍໄປຫາເວັບໄຊທ໌ທີ່ຕ້ອງການແລະສົ່ງຄືນການຕອບສະຫນອງຂອງມັນໃນຮູບແບບ XML, ແລະຫຼັງຈາກນັ້ນນໍາໃຊ້ຟັງຊັນ FILTER.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

ດຽວນີ້ພວກເຮົາຈະສ້າງສະຕຣິງແບບສອບຖາມໃນຕາລາງໃນແຜ່ນທີ່ມີ:

• ຕົວປະຕິບັດການ concatenation ຂໍ້ຄວາມ (&) ເພື່ອເອົາມັນເຂົ້າກັນ;
  
• ຄຸນ​ລັກ​ສະ​ນະ VPR (VLOOKUP)ເພື່ອຊອກຫາລະຫັດຂອງສະກຸນເງິນທີ່ພວກເຮົາຕ້ອງການຢູ່ໃນໄດເລກະທໍລີ;
  
• ຄຸນ​ລັກ​ສະ​ນະ ຂໍ້​ຄວາມ (ຂໍ້​ຄວາມ), ເຊິ່ງປ່ຽນວັນທີຕາມຮູບແບບທີ່ໃຫ້ໄວ້ ວັນ-ເດືອນ-ປີ ໂດຍຜ່ານເຄື່ອງໝາຍເລກ.

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

ຂັ້ນຕອນທີ 2. ປະຕິບັດຄໍາຮ້ອງຂໍ

ໃນປັດຈຸບັນພວກເຮົານໍາໃຊ້ຫນ້າທີ່ ການບໍລິການເວັບ (ບໍລິການເວັບ) ກັບ string query ທີ່ສ້າງຂຶ້ນເປັນ argument ເທົ່ານັ້ນ. ຄໍາຕອບຈະເປັນແຖວຍາວຂອງລະຫັດ XML (ມັນດີກວ່າທີ່ຈະເປີດການຫໍ່ຄໍາແລະເພີ່ມຂະຫນາດຂອງເຊນຖ້າທ່ານຕ້ອງການເບິ່ງມັນທັງຫມົດ):

ຂັ້ນຕອນທີ 3. ການແຍກຄໍາຕອບ

ເພື່ອເຮັດໃຫ້ມັນເຂົ້າໃຈໂຄງສ້າງຂອງຂໍ້ມູນການຕອບໂຕ້ງ່າຍຂຶ້ນ, ມັນດີກວ່າທີ່ຈະໃຊ້ຕົວວິເຄາະ XML ອອນລາຍອັນໜຶ່ງ (ຕົວຢ່າງ: http://xpather.com/ ຫຼື https://jsonformatter.org/xml-parser), ເຊິ່ງສາມາດຈັດຮູບແບບລະຫັດ XML ໄດ້ຕາມສາຍຕາ, ເພີ່ມຫຍໍ້ໜ້າໃສ່ມັນ ແລະເນັ້ນໄວຍະກອນດ້ວຍສີ. ຫຼັງຈາກນັ້ນ, ທຸກສິ່ງທຸກຢ່າງຈະກາຍເປັນທີ່ຊັດເຈນຫຼາຍ:

ໃນປັດຈຸບັນທ່ານສາມາດເຫັນໄດ້ຊັດເຈນວ່າຄຸນຄ່າຂອງຫຼັກສູດແມ່ນ framed ໂດຍ tags ຂອງພວກເຮົາ ..., ແລະວັນທີແມ່ນຄຸນລັກສະນະ ວັນທີ່ສະຫມັກ ໃນ tags .

ເພື່ອສະກັດພວກມັນ, ເລືອກຖັນຂອງສິບ (ຫຼືຫຼາຍກວ່ານັ້ນ - ຖ້າເຮັດດ້ວຍຂອບ) ຕາລາງຫວ່າງຢູ່ໃນຊີດ (ເພາະວ່າໄລຍະເວລາວັນທີ 10 ມື້ຖືກຕັ້ງ) ແລະໃສ່ຟັງຊັນໃນແຖບສູດ. FILTER.XML (ການກັ່ນຕອງXML):

ທີ່ນີ້, ການໂຕ້ຖຽງທໍາອິດແມ່ນການເຊື່ອມຕໍ່ກັບຈຸລັງທີ່ມີການຕອບສະຫນອງຂອງເຄື່ອງແມ່ຂ່າຍ (B8), ແລະອັນທີສອງແມ່ນສາຍແບບສອບຖາມໃນ XPath, ພາສາພິເສດທີ່ສາມາດນໍາໃຊ້ເພື່ອເຂົ້າເຖິງຊິ້ນສ່ວນລະຫັດ XML ທີ່ຈໍາເປັນແລະສະກັດພວກມັນ. ທ່ານສາມາດອ່ານເພີ່ມເຕີມກ່ຽວກັບພາສາ XPath, ສໍາລັບການຍົກຕົວຢ່າງ, ທີ່ນີ້.

ມັນເປັນສິ່ງສໍາຄັນທີ່ຫຼັງຈາກເຂົ້າໄປໃນສູດ, ຢ່າກົດດັນ ກະລຸນາໃສ່, ແລະປຸ່ມລັດແປ້ນພິມ Ctrl+ການປ່ຽນແປງ+ກະລຸນາໃສ່, ie ໃສ່ມັນເປັນສູດ array (ວົງເລັບ curly ປະມານມັນຈະຖືກເພີ່ມອັດຕະໂນມັດ). ຖ້າທ່ານມີ Office 365 ຮຸ່ນຫຼ້າສຸດທີ່ມີການສະຫນັບສະຫນູນ array dynamic ໃນ Excel, ຫຼັງຈາກນັ້ນງ່າຍດາຍ ກະລຸນາໃສ່, ແລະທ່ານບໍ່ ຈຳ ເປັນຕ້ອງເລືອກເຊລທີ່ຫວ່າງໄວ້ລ່ວງ ໜ້າ - ຟັງຊັນຕົວມັນເອງຈະໃຊ້ຈຸລັງຫຼາຍເທົ່າທີ່ມັນຕ້ອງການ.

ເພື່ອສະກັດວັນທີ, ພວກເຮົາຈະເຮັດເຊັ່ນດຽວກັນ - ພວກເຮົາຈະເລືອກຕາລາງຫວ່າງເປົ່າຫຼາຍອັນໃນຖັນທີ່ຢູ່ຕິດກັນແລະໃຊ້ຫນ້າທີ່ດຽວກັນ, ແຕ່ດ້ວຍການສອບຖາມ XPath ທີ່ແຕກຕ່າງກັນ, ເພື່ອໃຫ້ໄດ້ຄ່າທັງຫມົດຂອງຄຸນລັກສະນະວັນທີຈາກແທໍກບັນທຶກ:

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

ໃນປັດຈຸບັນໃນອະນາຄົດ, ເມື່ອປ່ຽນວັນທີໃນຈຸລັງເດີມ B2 ແລະ B3 ຫຼືເລືອກສະກຸນເງິນທີ່ແຕກຕ່າງກັນໃນບັນຊີລາຍຊື່ເລື່ອນລົງຂອງເຊນ B3, ຄໍາຖາມຂອງພວກເຮົາຈະຖືກປັບປຸງໂດຍອັດຕະໂນມັດ, ໂດຍອ້າງອີງໃສ່ເຄື່ອງແມ່ຂ່າຍຂອງທະນາຄານກາງສໍາລັບຂໍ້ມູນໃຫມ່. ເພື່ອບັງຄັບການອັບເດດດ້ວຍຕົນເອງ, ທ່ານຍັງສາມາດໃຊ້ທາງລັດແປ້ນພິມໄດ້ Ctrl+alt+F9.

• ນໍາເຂົ້າອັດຕາ bitcoin ກັບ Excel ຜ່ານ Power Query
• ນຳເຂົ້າອັດຕາແລກປ່ຽນຈາກອິນເຕີເນັດໃນ Excel ຮຸ່ນເກົ່າ