Kusinthana kosinthidwa ku Excel

Ndasanthula mobwerezabwereza njira zotumizira deta ku Excel kuchokera pa intaneti ndikuzikonzanso zokha. Makamaka:

• M'mitundu yakale ya Excel 2007-2013, izi zitha kuchitika ndi pempho lachindunji.
• Kuyambira mu 2010, izi zitha kuchitika mosavuta ndi Power Query add-in.
  

Kwa njira izi m'matembenuzidwe aposachedwa a Microsoft Excel, mutha kuwonjezera ina - kuitanitsa deta kuchokera pa intaneti mumtundu wa XML pogwiritsa ntchito ntchito zomanga.

XML (eXtensible Markup Language = Extensible Markup Language) ndi chinenero chapadziko lonse chomwe chimapangidwira kufotokoza mtundu uliwonse wa deta. M'malo mwake, ndizolemba zomveka, koma zokhala ndi ma tag apadera omwe amawonjezeredwa kuti alembe mawonekedwe a data. Masamba ambiri amapereka mitsinje yaulere ya data yawo mumtundu wa XML kuti aliyense atsitse. Pa webusaiti ya Banki Yaikulu ya Dziko Lathu (www.cbr.ru), makamaka, mothandizidwa ndi teknoloji yofanana, deta pamitengo yosinthanitsa ya ndalama zosiyanasiyana imaperekedwa. Kuchokera pa webusaiti ya Moscow Exchange (www.moex.com) mukhoza kukopera zolemba zamagulu, ma bond ndi zina zambiri zothandiza mofananamo.

Popeza mtundu wa 2013, Excel ili ndi ntchito ziwiri zotsitsa mwachindunji data ya XML kuchokera pa intaneti kupita ku ma cell sheet: WEB SERVICE (WEBSERVICE) и FILTER.XML (FILTERXML). Iwo amagwira ntchito awiriawiri - choyamba ntchito WEB SERVICE imagwira pempho patsamba lomwe mukufuna ndikubwezera yankho mumtundu wa XML, kenako ndikugwiritsa ntchito ntchitoyi FILTER.XML "timasanthula" yankho ili m'zigawo, ndikuchotsa zomwe tikufuna kuchokera pamenepo.

Tiyeni tiwone momwe ntchitozi zikuyendera pogwiritsa ntchito chitsanzo chapamwamba - kuitanitsa ndalama zosinthira ndalama zilizonse zomwe tingafune pa nthawi yomwe yaperekedwa kuchokera pa webusaiti ya Banki Yaikulu ya Dziko Lathu. Tidzagwiritsa ntchito zomanga zotsatirazi ngati zopanda kanthu:

Pano:

• Ma cell achikasu amakhala ndi masiku oyambira ndi omaliza a nthawi yomwe tikufuna.
• Buluu ili ndi mndandanda wotsitsa wandalama pogwiritsa ntchito lamulo Deta - Kutsimikizira - Mndandanda (Deta - Kutsimikizika - Mndandanda).
• M'maselo obiriwira, tidzagwiritsa ntchito ntchito zathu kupanga chingwe cha mafunso ndikupeza yankho la seva.
• Gome lomwe lili kumanja ndikulozera ku ma code a ndalama (tidzawafuna pambuyo pake).
  

Tiyeni tizipita!

Khwerero 1. Kupanga chingwe chofunsira

Kuti mudziwe zofunikira pa tsambalo, muyenera kufunsa molondola. Timapita ku www.cbr.ru ndikutsegula ulalo womwe uli m'munsi mwa tsamba lalikulu' Technical Resources'- Kupeza data pogwiritsa ntchito XML (http://cbr.ru/development/SXML/). Timapukuta pang'ono ndipo mu chitsanzo chachiwiri (Chitsanzo 2) padzakhala zomwe tikufuna - kupeza ndalama zosinthira pa nthawi yomwe yaperekedwa:

Monga mukuwonera pachitsanzo, mzere wamafunso uyenera kukhala ndi masiku oyambira (tsiku_req1) ndi zomaliza (tsiku_req2) ya nthawi yachiwongola dzanja kwa ife ndi ndondomeko ya ndalama (VAL_NM_RQ), mlingo womwe tikufuna kupeza. Mutha kupeza ma code andalama patebulo ili m'munsiyi:

Kalozera wathunthu wamakhodi a ndalama akupezekanso patsamba la Central Bank - onani http://cbr.ru/scripts/XML_val.asp?d=0

Tsopano tipanga chingwe chofunsira mu cell papepala ndi:

• wogwiritsa ntchito mawu (&) kuti ayike pamodzi;
  
• Mawonekedwe VPR (VLOOKUP)kuti tipeze kachidindo ka ndalama zomwe tikufuna m'ndandanda;
  
• Mawonekedwe TEXT (Zolemba), zomwe zimasintha tsikulo malinga ndi dongosolo lomwe laperekedwa mwezi-chaka-chaka.

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

Gawo 2. Perekani pempho

Tsopano timagwiritsa ntchito WEB SERVICE (WEBSERVICE) ndi chingwe chofunsidwa ngati mkangano wokhawo. Yankho lidzakhala mzere wautali wa nambala ya XML (ndibwino kuyatsa kukulunga kwa mawu ndikuwonjezera kukula kwa selo ngati mukufuna kuwona yonse):

Gawo 3. Kusanthula yankho

Kuti zikhale zosavuta kumvetsetsa kapangidwe ka data yoyankhira, ndikwabwino kugwiritsa ntchito imodzi mwazosankha zapa intaneti za XML (mwachitsanzo, http://xpather.com/ kapena https://jsonformatter.org/xml-parser), yomwe imatha kupanga mawonekedwe a XML, ndikuwonjezera ma indents kwa iyo ndikuwunikira ma syntax ndi mtundu. Ndiye zonse zidzamveka bwino:

Tsopano mutha kuwona bwino lomwe kuti maphunzirowa amapangidwa ndi ma tag athu ..., ndipo madeti ndi mikhalidwe Date mu tags .

Kuti muwachotse, sankhani magawo khumi (kapena kupitilira apo - ngati achita ndi malire) papepalalo (chifukwa nthawi ya masiku 10 idakhazikitsidwa) ndikulowetsa ntchitoyo mu bar ya formula. FILTER.XML (SEFAXML):

Pano, mkangano woyamba ndi chiyanjano cha selo ndi yankho la seva (B8), ndipo chachiwiri ndi chingwe chafunso mu XPath, chinenero chapadera chomwe chingagwiritsidwe ntchito kupeza zidutswa zofunikira za XML code ndikuzichotsa. Mutha kuwerenga zambiri za chilankhulo cha XPath, mwachitsanzo, apa.

Ndikofunika kuti mutatha kulowa mu fomuyi, musakanize Lowani, ndi njira yachidule ya kiyibodi Ctrl+kosangalatsa+Lowani, mwachitsanzo, lowetsani ngati ndondomeko yotsatizana (zingwe zopindika kuzungulira izo zidzawonjezedwa zokha). Ngati muli ndi mtundu waposachedwa wa Office 365 ndi chithandizo chamagulu osinthika mu Excel, ndiye yosavuta Lowani, ndipo simukusowa kusankha ma cell opanda kanthu pasadakhale - ntchito yokhayo idzatenga ma cell ambiri momwe ingafunikire.

Kuti tichotse madeti, tidzachitanso chimodzimodzi - tidzasankha ma cell angapo opanda kanthu pamzere woyandikana ndikugwiritsa ntchito ntchito yomweyo, koma ndi funso losiyana la XPath, kuti tipeze zikhalidwe zonse za Date kuchokera ku Record tag:

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

Tsopano m'tsogolomu, posintha masiku m'maselo oyambirira a B2 ndi B3 kapena kusankha ndalama zosiyana pamndandanda wotsitsa wa selo B3, funso lathu lidzasinthidwa zokha, ponena za seva ya Central Bank kwa deta yatsopano. Kuti muumirize kusintha pamanja, mutha kugwiritsanso ntchito njira yachidule ya kiyibodi Ctrl+alt+F9.

• Lowetsani mtengo wa bitcoin ku Excel kudzera pa Power Query
• Lowetsani mitengo yosinthira kuchokera pa intaneti m'mitundu yakale ya Excel