Okuqukethwe
Ngihlaziye kaningi izindlela zokungenisa idatha ku-Excel isuka ku-inthanethi ngokuvuselelwa okuzenzakalelayo okwalandela. Ngokuqondene:
- Ezinguqulweni ezindala ze-Excel 2007-2013, lokhu kungenziwa ngesicelo sewebhu esiqondile.
- Kusukela ngo-2010, lokhu kungenziwa kalula kakhulu ngesengezo Sombuzo Wamandla.
Kulezi zindlela ezinguqulweni zakamuva ze-Microsoft Excel, ungakwazi manje ukwengeza enye - ukungenisa idatha ku-inthanethi ngefomethi ye-XML usebenzisa imisebenzi eyakhelwe ngaphakathi.
I-XML (i-eXtensible Markup Language = Ulimi Lwemakhaphu Enwetshiwe) iwulimi lomhlaba wonke oluklanyelwe ukuchaza noma yiluphi uhlobo lwedatha. Eqinisweni, umbhalo ongenalutho, kodwa onamathegi akhethekile angeziwe kuwo ukuze kuphawulwe isakhiwo sedatha. Amasayithi amaningi ahlinzeka ngokusakazwa kwamahhala kwedatha yawo ngefomethi ye-XML ukuze noma ubani ayilande. Kuwebhusayithi yeBhange Elikhulu Lezwe Lethu (www.cbr.ru), ikakhulukazi, ngosizo lobuchwepheshe obufanayo, idatha emazingeni okushintshaniswa kwezimali ezihlukahlukene inikezwa. Kusukela kuwebhusayithi ye-Moscow Exchange (www.moex.com) ungalanda izingcaphuno zamasheya, amabhondi kanye nolunye ulwazi oluningi oluwusizo ngendlela efanayo.
Kusukela kunguqulo ka-2013, i-Excel inemisebenzi emibili yokulayisha ngokuqondile idatha ye-XML isuka ku-inthanethi iye kumaseli eshidi lokusebenzela: INKONZO YEWEBHU (WEBSERVICE) и FILTER.XML (FILTERXML). Basebenza ngababili - okokuqala umsebenzi INKONZO YEWEBHU yenza isicelo kusayithi oyifunayo bese ibuyisela impendulo yayo ngefomethi ye-XML, bese isebenzisa umsebenzi FILTER.XML "sihlaziya" le mpendulo ibe izingxenye, sikhipha idatha esiyidingayo kuyo.
Ake sibheke ukusebenza kwale misebenzi sisebenzisa isibonelo sakudala - ukungenisa izinga lokushintshisana kwanoma yiluphi uhlobo lwemali esiludingayo ngesikhathi esimisiwe sosuku kusuka kuwebhusayithi yeBhange Elikhulu Lezwe Lethu. Sizosebenzisa ukwakhiwa okulandelayo njengento engenalutho:
Lapha:
- Amaseli aphuzi aqukethe izinsuku zokuqala nezokugcina zesikhathi esithakaselayo kithi.
- Oluluhlaza okwesibhakabhaka lunohlu lokudonsela phansi lwezinhlobo zemali kusetshenziswa umyalo Idatha - Ukuqinisekisa - Uhlu (Idatha — Ukuqinisekisa — Uhlu).
- Kumaseli aluhlaza, sizosebenzisa imisebenzi yethu ukuze sakhe iyunithi yezinhlamvu zombuzo futhi sithole impendulo yeseva.
- Ithebula elingakwesokudla liyireferensi yamakhodi emali (sizoyidinga kamuva).
Asambe!
Isinyathelo 1. Ukwakha iyunithi yezinhlamvu zombuzo
Ukuze uthole ulwazi oludingekayo kusayithi, udinga ukubuza ngendlela efanele. Siya ku-www.cbr.ru bese sivula isixhumanisi ngaphansi kwekhasi eliyinhloko' Izinsiza Zobuchwepheshe'- Ukuthola idatha usebenzisa i-XML (http://cbr.ru/development/SXML/). Siskrola kancane bese esibonelweni sesibili (Isibonelo 2) kuzoba khona esikudingayo - ukuthola amanani okushintshanisa ngesikhathi sosuku oluthile:
Njengoba ubona esibonelweni, iyunithi yezinhlamvu yombuzo kufanele ibe nezinsuku zokuqala (idethi_req1) neziphetho (idethi_req2) yesikhathi sentshisekelo yethu kanye nekhodi yemali (VAL_NM_RQ), izinga esifuna ukulithola. Ungathola amakhodi ohlobo lwemali ayinhloko kuthebula elingezansi:
Currency | Ikhodi | | Currency | Ikhodi |
| I-dollar yase-Australia | R01010 | I-litas yaseLithuania | R01435 | |
Usheleni wase-Austrian | R01015 | isigqebhezana Lithuanian | R01435 | |
I-Azerbaijani manat | R01020 | I-Moldovan leu | R01500 | |
I-Pound | R01035 | РќРµРјРµС † РєР ° СЏ РјР ° РєР ° | R01510 | |
I-Angolan entsha ye-Kwanza | R01040 | I-Dutch guilder | R01523 | |
I-Armeni Dram | R01060 | I-Norwegian Krone | R01535 | |
I-ruble yaseBelarusian | R01090 | Polish Zloty | R01565 | |
I-franc yaseBelgium | R01095 | I-escudo yesiPutukezi | R01570 | |
Ibhubesi laseBulgaria | R01100 | Leu yaseRomania | R01585 | |
I-real yaseBrazil | R01115 | Singapore Dollar | R01625 | |
Forint Hungarian | R01135 | Idola laseSuriname | R01665 | |
Hong Kong Dollar | R01200 | I-Tajik somoni | R01670 | |
idrachma yesiGreki | R01205 | I-ruble yaseTajik | R01670 | |
Isikhonkwane saseDanish | R01215 | I-Turkish lira | R01700 | |
I-dollar yase-US | R01235 | I-Turkmen manat | R01710 | |
Euro | R01239 | I-New Turkmen manat | R01710 | |
Indian Rupee | R01270 | Isamba se-Uzbek | R01717 | |
Ipondo lase-Ireland | R01305 | I-hryvnia yase-our country | R01720 | |
I-Icelandic krone | R01310 | Ama-karbovanets ase-our country | R01720 | |
I-Spanish peseta | R01315 | Uphawu lwesiFinnish | R01740 | |
I-Italian lira | R01325 | frank French | R01750 | |
Kazakhstan tenge | R01335 | I-Czech koruna | R01760 | |
Canadian Dollar | R01350 | Isi-Swedish krona | R01770 | |
IsiKyrgyz som | R01370 | I-Swiss frank | R01775 | |
Chinese Yuan | R01375 | I-Estonian kroon | R01795 | |
Kuwaiti dinar | R01390 | I-Yugoslavia dinar entsha | R01804 | |
Isi-Latvian lats | R01405 | I-rand yaseNingizimu Afrika | R01810 | |
I-Lebanese pound | R01420 | IRiphabhulikhi yaseKorea iwinile | R01815 | |
Yen Japanese | R01820 |
Umhlahlandlela ophelele wamakhodi wemali uyatholakala nakuwebhusayithi yeBhange Elikhulu - bheka ku-http://cbr.ru/scripts/XML_val.asp?d=0
Manje sizokwakha iyunithi yezinhlamvu zemibuzo kuseli eshidini eline:
- u-opharetha wokuhlanganisa umbhalo (&) ukuze uyihlanganise;
- Izici VPR (VLOOKUP)ukuthola ikhodi yemali esiyidingayo ohlwini lwemibhalo;
- Izici TEXT (UMBHALO), okuguqula idethi ngokuya ngephethini yosuku-unyaka-inyanga nge-slash.
="http://cbr.ru/scripts/XML_dynamic.asp?date_req1="&ТЕКСТ(B2;"ДД/ММ/ГГГГ")& "&date_req2="&ТЕКСТ(B3;"ДД/ММ/ГГГГ")&"&VAL_NM_RQ="&ВПР(B4;M:N;2;0)
Isinyathelo sesi-2. Faka isicelo
Manje sisebenzisa umsebenzi INKONZO YEWEBHU (WEBSERVICE) ngeyunithi yezinhlamvu yombuzo ekhiqiziwe njengokuwukuphela kwempikiswano. Impendulo izoba umugqa omude wekhodi ye-XML (kungcono ukuvula ukugoqa kwamagama futhi wandise usayizi weseli uma ufuna ukuyibona iphelele):
Isinyathelo sesi-3. Ukuhlaziya impendulo
Ukwenza kube lula ukuqonda ukwakheka kwedatha yempendulo, kungcono ukusebenzisa omunye wabahlahleli be-XML abaku-inthanethi (isibonelo, http://xpather.com/ noma https://jsonformatter.org/xml-parser), engafometha ngokubukeka ikhodi ye-XML, yengeze ama-indenti kuyo futhi igqamise i-syntax ngombala. Khona-ke konke kuzocaca nakakhulu:
Manje ungabona ngokucacile ukuthi amanani wezifundo afakwe ngomaka bethu
Ukuze uwakhiphe, khetha ikholomu yamaseli ayishumi (noma ngaphezulu - uma kwenziwa ngemajini) angenalutho eshidini (ngoba isikhawu sedethi esiyizinsuku eziyi-10 sasethiwe) bese ufaka umsebenzi kubha yefomula. FILTER.XML (ISIHLUNGIXML):
Lapha, ingxabano yokuqala iyisixhumanisi seseli enempendulo yeseva (B8), kanti eyesibili iyunithi yezinhlamvu yombuzo ku-XPath, ulimi olukhethekile olungasetshenziswa ukufinyelela izingcezu zekhodi ye-XML ezidingekayo futhi zikhishwe. Ungafunda kabanzi ngolimi lwe-XPath, isibonelo, lapha.
Kubalulekile ukuthi ngemva kokufaka ifomula, ungacindezeli Faka, kanye nesinqamuleli sekhibhodi Ctrl+Shift+Faka, okungukuthi ifake njengefomula yohlu (ama-curly braces azungezile azokwengezwa ngokuzenzakalelayo). Uma unenguqulo yakamuva ye-Office 365 esekelwa amalungu afanayo aguqukayo ku-Excel, bese kuba lula Faka, futhi awudingi ukukhetha amaseli angenalutho kusengaphambili - umsebenzi ngokwawo uzothatha amaseli amaningi njengoba udinga.
Ukuze sikhiphe amadethi, sizokwenza okufanayo - sizokhetha amaseli amaningana angenalutho kukholamu eseduze futhi sisebenzise umsebenzi ofanayo, kodwa ngombuzo ohlukile we-XPath, ukuze sithole wonke amanani wezimpawu zedethi kumathegi weRekhoda:
=FILTER.XML(B8;”//Rekhoda/@Usuku”)
Manje esikhathini esizayo, lapho sishintsha izinsuku kumaseli okuqala e-B2 ne-B3 noma sikhetha uhlobo lwemali oluhlukile ohlwini lokudonsela phansi lweseli B3, umbuzo wethu uzobuyekezwa ngokuzenzakalelayo, ubhekise kuseva yeBhange Elikhulu ukuze uthole idatha entsha. Ukuze uphoqelele isibuyekezo mathupha, ungasebenzisa futhi isinqamuleli sekhibhodi Ctrl+Alt+F9.
- Ngenisa izinga le-bitcoin ku-Excel usebenzisa Umbuzo Wamandla
- Ngenisa amanani okushintshanisa asuka ku-inthanethi ezinguqulweni ezindala ze-Excel