Shigo da farashin musaya daga Intanet

Shigo da ƙimar kuɗin da aka bayar daga Intanet tare da sabuntawa ta atomatik aiki ne na gama gari ga yawancin masu amfani da Microsoft Excel. Ka yi tunanin cewa kana da lissafin farashi wanda dole ne a sake ƙididdige shi kowace safiya bisa ga farashin canji. Ko kasafin aikin. Ko kuma kudin kwangilar, wanda dole ne a lissafta ta hanyar amfani da canjin dala a ranar da aka kammala kwangilar.

A irin waɗannan yanayi, zaku iya magance matsalar ta hanyoyi daban-daban - duk ya dogara da nau'in sigar Excel da kuka shigar da abin da ƙari ke saman sa.

Hanyar 1: Buƙatar yanar gizo mai sauƙi don ƙimar musayar yanzu

Wannan hanyar ta dace da waɗanda har yanzu suna da tsoffin nau'ikan Microsoft Office 2003-2007 akan kwamfutar su. Ba ya amfani da kowane ƙari ko macros kuma yana aiki akan ginanniyar ayyuka kawai.

latsa Daga Intanet (Yanar gizo) tab data (Kwanan wata). A cikin taga wanda ya bayyana, a cikin layi Adireshin (Adireshi) shigar da URL na rukunin yanar gizon da za a ɗauki bayanin (misali, http://www.finmarket.ru/currency/rates/) kuma danna maɓallin. Shigar.

Lokacin da shafin ya yi lodi, kibiyoyi baƙi da rawaya za su bayyana akan tebur waɗanda Excel za su iya shigo da su. Danna kan irin wannan kibiya alama ce ta tebur don shigo da kaya.

Lokacin da aka yiwa dukkan allunan da ake buƙata, danna maɓallin Import (Shigo) a gindin taga. Bayan ɗan lokaci da ake buƙata don loda bayanan, abubuwan da ke cikin allunan da aka yiwa alama za su bayyana a cikin sel akan takardar:

Don ƙarin keɓancewa, zaku iya danna-dama akan kowane ɗayan waɗannan sel kuma zaɓi umarni daga menu na mahallin. Range Properties (Kayan bayanan kewayon).A cikin wannan akwatin maganganu, idan ana so, yana yiwuwa a daidaita mitar sabuntawa da sauran sigogi:

Ƙididdigar hannun jari, yayin da suke canzawa kowane 'yan mintoci kaɗan, za ku iya sabuntawa akai-akai (akwatin Sake sabunta kowane minti N.), amma farashin musayar, a mafi yawan lokuta, ya isa a sabunta sau ɗaya a rana (akwatin Sabuntawa a buɗe fayil ɗin).

Lura cewa dukkanin bayanan da aka shigo da su Excel suna ɗaukar su azaman raka'a ɗaya kuma suna ba da sunanta, wanda za'a iya gani a cikin Mai sarrafa Suna akan shafin. tsari (Formulas - Name Manager).

Hanyar 2: Tambayar gidan yanar gizo mai ma'ana don samun ƙimar musanya ta kewayon kwanan wata

Wannan hanya wani zaɓi ne na farko da aka sabunta dan kadan kuma yana ba mai amfani damar karɓar canjin kuɗin kuɗin da ake so ba kawai don ranar ta yanzu ba, har ma da kowane tazarar kwanan wata ko kwanan wata na sha'awa. Don yin wannan, buƙatar mu ta yanar gizo dole ne a juya zuwa ma'auni, watau ƙara sigogi biyu masu bayyanawa gare shi (lambar kuɗin da muke buƙata da kwanan wata). Don yin wannan, muna yin haka:

1. Mun ƙirƙiri buƙatun yanar gizo (duba hanyar 1) zuwa shafin yanar gizon Babban Bankin ƙasarmu tare da tarihin darussa: http://cbr.ru/currency_base/dynamics.aspx

2. A cikin fom na hagu, zaɓi kuɗin da ake so kuma saita kwanakin farawa da ƙarshen:

3. Danna maɓallin Don samun bayanai kuma bayan daƙiƙa biyu muna ganin tebur tare da ƙimar kwas ɗin da muke buƙata don tazarar kwanan wata. Gungura sakamakon tebur har zuwa ƙasa kuma yi masa alama don shigo da shi ta danna kan kibiya baƙar fata da rawaya a cikin kusurwar hagu na shafin yanar gizon (kawai kar ku tambayi dalilin da yasa wannan kibiya take kuma ba kusa da tebur ba - wannan shine tambaya ga masu zanen rukunin yanar gizon).

Yanzu muna neman maɓalli mai floppy disk a saman kusurwar dama ta taga Ajiye nema (Ajiye tambaya) kuma ajiye fayil ɗin tare da sigogin buƙatarmu zuwa kowane babban fayil mai dacewa a ƙarƙashin kowane suna mai dacewa - misali, in My takardun a karkashin sunan cbr. iqy.  Bayan haka, taga tambayar Yanar Gizo da duk Excel ana iya rufewa a yanzu.

4. Buɗe babban fayil ɗin da kuka ajiye buƙatun kuma nemi fayil ɗin buƙatar cbr. iqy, sannan danna-dama akan shi - Buɗe Tare da - Notepad (ko zaɓi shi daga lissafin - yawanci fayil ne notepad.exe daga babban fayil C: Windows). Bayan buɗe fayil ɗin buƙatun a cikin Notepad, yakamata ku ga wani abu kamar haka:

Abu mafi mahimmanci a nan shi ne layi tare da adireshi da sigogin tambaya a cikinsa, wanda za mu musanya shi - lambar kudin da muke bukata (wanda aka kwatanta da ja) da ranar ƙarshe, wanda za mu maye gurbin da na yau (wanda aka haskaka a ciki). blue). Yi gyaran layi a hankali don samun abubuwa masu zuwa:

http://cbr.ru/currency_base/dynamics.aspx?VAL_NM_RQ=["Lambar kudi"]&date_req1=01.01.2000&r1=1&date_req2=["Kwanan Wata"]&rt=1&mode=1

Bar duk abin da yake, ajiye kuma rufe fayil ɗin.

5. Ƙirƙiri sabon littafi a cikin Excel, buɗe takardar inda muke son shigo da ma'ajiyar kudaden babban bankin kasa. A kowane tantanin halitta da ya dace, shigar da dabarar da za ta ba mu kwanan wata a tsarin rubutu don maye gurbin tambaya:

= RUBUTU (YAU ();”DD.MM.YYYY”)

ko kuma a cikin Turanci

= RUBUTU(YAU(),»dd.mm.yyyy»)

Wani wuri kusa muna shigar da lambar kuɗin da muke buƙata daga tebur:

Hakanan ana iya leƙe lambar da ake buƙata a cikin layin tambaya kai tsaye a gidan yanar gizon Babban Bankin.

6. Muna loda bayanan akan takardar, ta yin amfani da sel da aka ƙirƙira da fayil ɗin cbr.iqy azaman tushen, watau je zuwa tab. Bayanai - Haɗin kai - Nemo Wasu (Bayanai - Abubuwan Haɗin kai). A cikin taga zaɓin tushen bayanai da ke buɗewa, nemo kuma buɗe fayil ɗin cbr. iqy. Kafin shigo da shi, Excel zai fayyace abubuwa uku tare da mu.

Na farko, inda za a shigo da teburin bayanai:

Na biyu, inda za a sami lambar kuɗin daga (zaku iya duba akwatin Yi amfani da wannan tsohuwar ƙima (Yi amfani da wannan ƙima/magana don wartsakewa nan gaba), ta yadda daga baya kowane lokaci ba a ƙayyade wannan tantanin halitta ba yayin sabuntawa da akwatin rajistan Sabuntawa ta atomatik lokacin da ƙimar tantanin halitta ta canza (Sake sabuntawa ta atomatik lokacin da ƙimar tantanin halitta ta canza):

Na uku, daga wane tantanin halitta zai ɗauki ƙarshen kwanan wata (zaka iya duba akwatunan nan don gobe ba sai ka saita waɗannan sigogi da hannu lokacin ɗaukakawa):

Click OK, jira 'yan daƙiƙa biyu kuma sami cikakken tarihin adadin kuɗin da ake so akan takardar:

Kamar yadda yake a hanya ta farko, ta danna dama akan bayanan da aka shigo da su kuma zaɓi umarnin Range Properties (Kayan bayanan kewayon), za ku iya daidaita ƙimar wartsakewa Lokacin buɗe fayil (Sake sabuntawa a buɗe fayil). Bayan haka, idan kuna da Intanet, za a sabunta bayanan ta atomatik kowace rana, watau Tebur za a sabunta ta atomatik tare da sabbin bayanai.

Zai fi sauƙi don cire ƙimar don kwanan watan da ake so daga teburin mu ta amfani da aikin VPR (VLOOKUP) - idan ba ku saba da shi ba, to ina ba ku shawara sosai don yin wannan. Tare da irin wannan tsari, alal misali, zaku iya zaɓar canjin dala na 10 ga Janairu, 2000 daga teburinmu:

ko a Turanci = VLOOKUP(E5,cbr,3,1)

inda

• E5 – tantanin halitta dauke da kwanan wata da aka bayar
  
• cbr - sunan kewayon bayanai (an ƙirƙira ta atomatik yayin shigo da shi kuma yawanci iri ɗaya da sunan fayil ɗin tambaya)
• 3 - lambar serial na ginshiƙi a cikin tebur ɗinmu, inda muke samun bayanai daga
• 1 - hujjar da ta haɗa da kimanin bincike na aikin VLOOKUP don ku sami kwasa-kwasan kwanakin tsaka-tsakin da ba a zahiri ba a cikin shafi na A (kwanan kwanan wata mafi kusa da kuma karatunsa za a ɗauka). Kuna iya karanta ƙarin game da kimanin bincike ta amfani da aikin VLOOKUP anan.

• Macro don samun ƙimar dala na kwanan wata da aka bayar a cikin tantanin halitta na yanzu
• Ayyukan ƙarawa na PLEX don samun canjin dala, Yuro, hryvnia, laban Sterling, da dai sauransu na kowace rana.
• Saka kowane kuɗin kuɗi a kowace rana a cikin ƙarawa na PLEX