Ua hōʻano hou ʻia ke kumukūʻai ma Excel

Ua ʻimi pinepine au i nā ala e hoʻokomo ai i ka ʻikepili i Excel mai ka Pūnaewele me ka hoʻonui ʻana ma hope. I ke ʻano wae:

• Ma nā mana kahiko o Excel 2007-2013, hiki ke hana i kēia me kahi noi pūnaewele pololei.
• E hoʻomaka ana i ka makahiki 2010, hiki ke hana maʻalahi me ka Power Query add-in.
  

I kēia mau ʻano hana i nā mana hou loa o Microsoft Excel, hiki iā ʻoe ke hoʻohui i kahi ʻē aʻe - ka lawe ʻana i ka ʻikepili mai ka Pūnaewele ma ke ʻano XML me ka hoʻohana ʻana i nā hana i kūkulu ʻia.

ʻO XML (eXtensible Markup Language = Extensible Markup Language) he ʻōlelo holoʻokoʻa i hoʻolālā ʻia e wehewehe i kekahi ʻano ʻikepili. ʻO ka ʻoiaʻiʻo, he kikokikona maʻalahi, akā me nā hōʻailona kūikawā i hoʻohui ʻia iā ia e hōʻailona ai i ke ʻano o ka ʻikepili. Hāʻawi ka nui o nā pūnaewele i nā kahawai manuahi o kā lākou ʻikepili ma ke ʻano XML no kekahi e hoʻoiho. Ma ka pūnaewele o ka Central Bank of Our Country (www.cbr.ru), ma ke ʻano, me ke kōkua o kahi ʻenehana like, hāʻawi ʻia nā ʻikepili e pili ana i nā kumukūʻai o nā kālā like ʻole. Mai ka pūnaewele Moscow Exchange (www.moex.com) hiki iā ʻoe ke hoʻoiho i nā huaʻōlelo no nā waihona, nā mea paʻa a me nā ʻike ʻē aʻe he nui ma ke ʻano like.

Mai ka mana 2013, ʻelua mau hana ʻo Excel no ka hoʻouka pololei ʻana i ka ʻikepili XML mai ka Pūnaewele i loko o nā keena pepa haʻawina: HANA WEB (WEBSERVICE) и FILTER.XML (FILTERXML). Hana pū lāua - ʻo ka hana mua HANA WEB hoʻokō i kahi noi i ka pūnaewele i makemake ʻia a hoʻihoʻi i kāna pane ma ka format XML, a laila hoʻohana i ka hana FILTER.XML "parse" mākou i kēia pane i nā ʻāpana, e unuhi i ka ʻikepili a mākou e pono ai.

E nānā kākou i ka hana ʻana o kēia mau hana me ka hoʻohana ʻana i kahi hiʻohiʻona maʻamau - ka lawe ʻana mai i ke kumukūʻai kūʻai o kekahi kālā a mākou e pono ai no kahi manawa i hāʻawi ʻia mai ka pūnaewele o ka Central Bank of Our Country. E hoʻohana mākou i kēia hana ma ke ʻano he blank:

Maʻaneʻi:

• Aia i loko o nā pūnaewele melemele nā ​​lā hoʻomaka a me nā lā hopena o ka manawa hoihoi iā mākou.
• Loaʻa i ka polū kahi papa inoa o nā kālā e hoʻohana ana i ke kauoha ʻIkepili - Hōʻoia - Papa inoa (ʻIkepili — Hōʻoia — Papa inoa).
• Ma nā pūnaewele ʻōmaʻomaʻo, e hoʻohana mākou i kā mākou mau hana e hana i kahi kaula nīnau a loaʻa ka pane a ke kikowaena.
• ʻO ka papa ma ka ʻaoʻao ʻākau kahi kuhikuhi i nā code kālā (pono mākou ma hope iki).
  

E hele kāua!

KaʻAnuʻu 1. Ka hana ʻana i kahi kaula nīnau

No ka loaʻa ʻana o ka ʻike i koi ʻia mai ka pūnaewele, pono ʻoe e nīnau pololei. Hele mākou i www.cbr.ru a wehe i ka loulou ma ka wāwae o ka ʻaoʻao nui' Nā kumuwaiwai ʻenehana'- Loaʻa ka ʻikepili me ka XML (http://cbr.ru/development/SXML/). Holo mākou i lalo a ma ka lua o ka laʻana (Example 2) e loaʻa iā mākou ka mea e pono ai - ka loaʻa ʻana o nā kumukūʻai kūʻai no ka wā i hāʻawi ʻia:

E like me kāu e ʻike ai mai ka laʻana, pono e loaʻa i ke kaula nīnau nā lā hoʻomaka (lā_req1) a me nā hopena (lā_req2) o ka manawa hoihoi iā mākou a me ke code kālā (VAL_NM_RQ), ka helu o kā mākou makemake e loaʻa. Hiki iā ʻoe ke loaʻa nā code kālā nui ma ka papa ma lalo nei:

Loaʻa kahi alakaʻi piha i nā code kālā ma ka pūnaewele Central Bank - ʻike http://cbr.ru/scripts/XML_val.asp?d=0

I kēia manawa, e hana mākou i kahi kaula nīnau i loko o kahi kelepona ma kahi pepa me:

• ka mea hoʻopili kikokikona (&) e hui pū;
  
• hiʻona ʻO VPR (VLOOKUP)e ʻike i ke code o ke kālā e pono ai mākou ma ka papa kuhikuhi;
  
• hiʻona KAUA (KANAKA), ka mea e hoʻololi i ka lā e like me ke kumu i hāʻawi ʻia i ka lā-mahina-makahiki ma o ka slash.

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

KaʻAnuʻu 2. E hoʻokō i ka noi

I kēia manawa hoʻohana mākou i ka hana HANA WEB (WEBSERVICE) me ke kaula nīnau i haku ʻia ʻo ia wale nō ka hoʻopaʻapaʻa. ʻO ka pane he laina lōʻihi o ka code XML (ʻoi aku ka maikaʻi o ka hoʻohuli ʻana i ka huaʻōlelo a hoʻonui i ka nui o ke kelepona inā makemake ʻoe e ʻike iā ia i kona holoʻokoʻa):

KaʻAnuʻu 3. Hoʻopili i ka pane

I mea e maʻalahi ai ka hoʻomaopopo ʻana i ke ʻano o ka ʻikepili pane, ʻoi aku ka maikaʻi o ka hoʻohana ʻana i kekahi o nā parser XML pūnaewele (no ka laʻana, http://xpather.com/ a i ʻole https://jsonformatter.org/xml-parser), hiki iā ia ke hoʻohālikelike i ka code XML me ka ʻike maka, e hoʻohui i nā indents iā ia a e hōʻike ana i ka syntax me ke kala. A laila e maopopo loa nā mea a pau:

I kēia manawa hiki iā ʻoe ke ʻike maopopo ua hoʻonohonoho ʻia nā waiwai papa e kā mākou mau inoa ..., a he mau ʻano nā lā lā i nā hōʻailona .

No ka unuhi ʻana iā lākou, koho i kahi kolamu o ʻumi (a ʻoi aku paha - inā hana ʻia me ka palena) nā cell hakahaka ma ka pepa (no ka mea, ua hoʻonohonoho ʻia kahi lā he 10 lā) a hoʻokomo i ka hana ma ka pahu formula. FILTER.XML (FILTERXML):

Maʻaneʻi,ʻo ka hoʻopaʻapaʻa mua he loulou i kahi pūnaewele me kahi pane server (B8), aʻo ka lua he string nīnau ma XPath, he'ōlelo kūikawā hiki ke hoʻohanaʻia no ke komoʻana i nā'āpana XML code pono a unuhi iā lākou. Hiki iā ʻoe ke heluhelu hou aku e pili ana i ka ʻōlelo XPath, no ka laʻana, maanei.

He mea nui ma hope o ke komo ʻana i ke ʻano, mai kaomi komo, a me ka pōkole kīpē Ctrl+Shift+komo, ʻo ia hoʻi, e hoʻokomo iā ia ma ke ʻano he papa kuhikuhi (e hoʻohui ʻia nā pale ʻili a puni ia). Inā loaʻa iā ʻoe ka mana hou o Office 365 me ke kākoʻo no nā arrays dynamic ma Excel, a laila maʻalahi komo, a ʻaʻole pono ʻoe e koho i nā cell hakahaka ma mua – ʻo ka hana ponoʻī e lawe i ka nui o nā cell e like me ka mea e pono ai.

No ka unuhi ʻana i nā lā, e hana like mākou - e koho mākou i kekahi mau cell blank ma ke kolamu pili a hoʻohana i ka hana like, akā me kahi nīnau XPath ʻē aʻe, e kiʻi i nā waiwai āpau o nā ʻano lā mai nā hōʻailona Record:

=FILTER.XML(B8;”//Hoʻopaʻa/@La”)

I kēia wā e hiki mai ana, i ka wā e hoʻololi ai i nā lā ma nā pūnaewele B2 a me B3 a i ʻole ke koho ʻana i kahi kālā ʻē aʻe i ka papa inoa hāʻule iho o ka cell B3, e hoʻonui ʻia kā mākou nīnau, e pili ana i ka kikowaena Central Bank no ka ʻikepili hou. No ka hoʻoikaika lima ʻana i ka hoʻohou, hiki iā ʻoe ke hoʻohana i ka pōkole keyboard Ctrl+Alt+F9.

• E lawe mai i ka helu bitcoin i Excel ma o Power Query
• E lawe mai i nā kumukūʻai hoʻololi mai ka Pūnaewele ma nā mana kahiko o Excel