Ìre iomlaid air ùrachadh ann an Excel

Tha mi air grunn dhòighean a mhion-sgrùdadh gus dàta a thoirt a-steach gu Excel bhon eadar-lìn le ùrachadh fèin-ghluasadach às deidh sin. Gu sònraichte:

• Ann an dreachan nas sine de Excel 2007-2013, ghabhadh seo a dhèanamh le iarrtas lìn dìreach.
• A’ tòiseachadh ann an 2010, faodar seo a dhèanamh gu math goireasach leis an tuilleadan Power Query.
  

Gus na dòighean sin anns na dreachan as ùire de Microsoft Excel, faodaidh tu a-nis fear eile a chuir ris - toirt a-steach dàta bhon eadar-lìn ann an cruth XML a ’cleachdadh gnìomhan togte.

Tha XML (eXtensible Markup Language = Cànan Comharrachaidh Leudaichte) na chànan uile-choitcheann a chaidh a dhealbhadh gus cunntas a thoirt air seòrsa sam bith de dhàta. Gu dearbh, is e teacsa sìmplidh a th’ ann, ach le tagaichean sònraichte air an cur ris gus structar an dàta a chomharrachadh. Bidh mòran làraich a’ toirt seachad sruthan an-asgaidh den dàta aca ann an cruth XML airson duine sam bith a luchdachadh sìos. Air làrach-lìn Banca Meadhanach ar Dùthaich (www.cbr.ru), gu sònraichte, le cuideachadh bho theicneòlas coltach ris, tha dàta air ìrean iomlaid diofar airgeadan air a thoirt seachad. Bho làrach-lìn Moscow Exchange (www.moex.com) faodaidh tu luachan a luchdachadh sìos airson stocan, bannan agus tòrr fiosrachaidh feumail eile san aon dòigh.

Bho dhreach 2013, tha dà ghnìomh aig Excel airson dàta XML a luchdachadh gu dìreach bhon eadar-lìn gu ceallan duilleag-obrach: SEIRBHEIS WEB (SEIRBHEIS Lìn) и FAILTE.XML (FILTEXML). Bidh iad ag obair ann an càraidean - an gnìomh an toiseach SEIRBHEIS WEB a’ cur an gnìomh iarrtas chun làraich a tha thu ag iarraidh agus a’ tilleadh a fhreagairt ann an cruth XML, agus an uairsin a’ cleachdadh a’ ghnìomh FAILTE.XML bidh sinn a’ “parsadh” an fhreagairt seo gu co-phàirtean, a’ toirt a-mach an dàta a tha a dhìth oirnn.

Bheir sinn sùil air obrachadh nan gnìomhan sin a’ cleachdadh eisimpleir clasaigeach - a’ toirt a-steach ìre iomlaid airgead sam bith a dh’ fheumas sinn airson ùine ceann-latha sònraichte bho làrach-lìn Banca Meadhanach na Dùthcha againn. Cleachdaidh sinn an togail a leanas mar bhàn:

Seo:

• Anns na ceallan buidhe tha cinn-latha tòiseachaidh is crìochnachaidh na h-ùine anns a bheil ùidh againn.
• Tha liosta tuiteam-sìos de airgeadan aig an fhear ghorm a’ cleachdadh an àithne Dàta - Dearbhadh - Liosta (Dàta - Dearbhadh - Liosta).
• Anns na ceallan uaine, cleachdaidh sinn ar gnìomhan gus sreang ceist a chruthachadh agus freagairt an fhrithealaiche fhaighinn.
• Tha an clàr air an làimh dheis a 'toirt iomradh air còdan airgid (bidh feum againn air beagan nas fhaide air adhart).
  

Tiugainn!

Ceum 1. A 'cruthachadh sreang ceist

Gus am fiosrachadh riatanach fhaighinn bhon làrach, feumaidh tu faighneachd gu ceart. Bidh sinn a’ dol gu www.cbr.ru agus a’ fosgladh a’ cheangail aig bonn na prìomh dhuilleag’ Goireasan Teicnigeach'- A’ faighinn dàta a’ cleachdadh XML (http://cbr.ru/development/SXML/). Bidh sinn a’ sgrolaich beagan nas ìsle agus anns an dàrna eisimpleir (Eisimpleir 2) bidh na tha a dhìth oirnn - a’ faighinn na h-ìrean iomlaid airson ceann-latha sònraichte:

Mar a chì thu bhon eisimpleir, feumaidh cinn-latha tòiseachaidh (ceann-latha_riat1) agus crìochnachaidhean (ceann-latha_riat2) den ùine anns a bheil ùidh againn agus an còd airgead (VAL_NM_RQ), an ìre a tha sinn airson faighinn. Gheibh thu na prìomh chòdan airgead sa chlàr gu h-ìosal:

Tha stiùireadh coileanta mu chòdan airgead cuideachd ri fhaighinn air làrach-lìn a’ Bhanca Mheadhain - faic http://cbr.ru/scripts/XML_val.asp?d=0

A-nis cruthaichidh sinn sreang ceist ann an cealla air duilleag le:

• an gnìomhaiche concatenation teacsa (&) airson a chuir ri chèile;
  
• feartan VPR (VLOOKUP)gus còd an airgid a tha a dhìth oirnn a lorg anns an eòlaire;
  
• feartan TEXT (TEXT), a thionndaidheas an ceann-latha a rèir a’ phàtrain a chaidh a thoirt seachad latha-mìos-bliadhna tro shlaodadh.

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

Ceum 2. Cuir an gnìomh an iarrtas

A-nis tha sinn a 'cleachdadh an gnìomh SEIRBHEIS WEB (SEIRBHEIS Lìn) leis an t-sreath ceist a chaidh a chruthachadh mar an aon argamaid. Bidh am freagairt na loidhne fhada de chòd XML (tha e nas fheàrr còmhdach fhaclan a thionndadh air agus meud na cealla àrdachadh ma tha thu airson fhaicinn gu h-iomlan):

Ceum 3. Parsing am freagairt

Gus a dhèanamh nas fhasa structar an dàta freagairt a thuigsinn, tha e nas fheàrr aon de na parsers XML air-loidhne a chleachdadh (mar eisimpleir, http://xpather.com/ no https://jsonformatter.org/xml-parser), as urrainn còd XML a chruth gu fradharcach, a’ cur indents ris agus a’ soilleireachadh a’ cho-chòrdadh le dath. An uairsin bidh a h-uile dad tòrr nas soilleire:

A-nis chì thu gu soilleir gu bheil luachan a’ chùrsa air am frèamadh leis na tagaichean againn ..., agus tha cinn-latha nam feartan Ceann-là ann an tagaichean .

Gus an toirt a-mach, tagh colbh de dheich (no barrachd - ma thèid a dhèanamh le iomall) ceallan falamh air an duilleag (seach gun deach eadar-ama ceann-latha 10-latha a shuidheachadh) agus cuir a-steach an gnìomh sa bhàr fhoirmeil FAILTE.XML (SGILEANXML):

An seo, tha a’ chiad argamaid na cheangal ri cealla le freagairt frithealaiche (B8), agus tha an dàrna fear na shreang ceist ann an XPath, cànan sònraichte a dh’ fhaodar a chleachdadh gus faighinn gu na mìrean còd XML riatanach agus an toirt a-mach. Faodaidh tu barrachd a leughadh mun chànan XPath, mar eisimpleir, an seo.

Tha e cudromach, às deidh dhut a dhol a-steach don fhoirmle, na brùth Cuir a-steach, agus ath-ghoirid a’ mheur-chlàir Ctrl+Shift+Cuir a-steach, ie cuir a-steach e mar fhoirmle rèite (thèid na braces lùbach timcheall air a chuir ris gu fèin-ghluasadach). Ma tha an tionndadh as ùire de Office 365 agad le taic airson arrays fiùghantach ann an Excel, an uairsin sìmplidh Cuir a-steach, agus chan fheum thu ceallan falamh a thaghadh ro-làimh - bheir an gnìomh fhèin na h-uimhir de cheallan a dh’ fheumas e.

Gus cinn-latha a tharraing, nì sinn an aon rud - taghaidh sinn grunn cheallan falamh sa cholbh ri thaobh agus cleachdaidh sinn an aon ghnìomh, ach le ceist XPath eadar-dhealaichte, gus luachan uile nam buadhan Ceann-latha fhaighinn bho na tagaichean Clàraidh:

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

A-nis san àm ri teachd, nuair a bhios tu ag atharrachadh cinn-latha anns na ceallan tùsail B2 agus B3 no a’ taghadh airgead eile anns an liosta tuiteam-sìos de chealla B3, thèid ar ceist ùrachadh gu fèin-ghluasadach, a’ toirt iomradh air frithealaiche a’ Bhanca Mheadhain airson dàta ùr. Gus ùrachadh a sparradh le làimh, faodaidh tu cuideachd ath-ghoirid a’ mheur-chlàr a chleachdadh Ctrl+Alt+F9.

• Cuir a-steach ìre bitcoin gu Excel tro Power Query
• Cuir a-steach ìrean iomlaid bhon eadar-lìn ann an dreachan nas sine de Excel