Pinza mitengo yekuchinja kubva paInternet

Kupinza muyero wemari yakapihwa kubva paInternet nekuvandudza otomatiki ibasa rakajairika kune vakawanda vashandisi veMicrosoft Excel. Fungidzira kuti une runyorwa rwemitengo iyo inofanirwa kuverengerwa mangwanani ega ega zvichienderana nemutengo wekuchinjana. Kana bhajeti reprojekiti. Kana mari yekontrakiti, iyo inofanirwa kuverengerwa uchishandisa dhora rekuchinjana pamusi wekupera kwechibvumirano.

Mumamiriro ezvinhu akadaro, unogona kugadzirisa dambudziko nenzira dzakasiyana - zvose zvinoenderana neshanduro ipi yeExcel yawakaisa uye kuti ndeapi ma-add-ons ari pamusoro payo.

Method 1: Chikumbiro chakareruka chewebhu cheiyo ikozvino shanduko mwero

Iyi nzira yakakodzera kune avo vachiri neshanduro dzekare dzeMicrosoft Office 2003-2007 pakombuta yavo. Iyo haishandise chero yechitatu-bato ma-add-ons kana macros uye inongoshanda pane yakavakirwa-mukati mabasa.

dzvanya Kubva paInternet (Webhu) Tab Data (Zuva). Muhwindo rinoonekwa, mumutsetse adhiresi (Kero) isa iyo URL yesaiti iyo ichatorwa ruzivo (semuenzaniso, http://www.finmarket.ru/currency/rates/) uye tinya kiyi pinda.

Kana peji racho richitakura, miseve yakasviba neyero ichaonekwa pamatafura anogona kutorwa neExcel. Kudzvanya pamuseve wakadaro kunomaka tafura yekutenga kunze.

Kana matafura ese anodiwa akamakwa, tinya bhatani Tumira (Import) pazasi pehwindo. Mushure mechinguva chinodiwa kurodha data, zviri mukati mematafura akamisikidzwa zvichaonekwa mumaseru ari papepa:

Kuti uwedzere kugadzirisa, unogona kudzvanya-kurudyi pane chero eaya maseru uye wosarudza iwo murairo kubva pane yemukati menyu. Range zvivakwa (Data range zvivakwa).Mune ino dialog box, kana zvichidikanwa, zvinokwanisika kugadzirisa iyo frequency yekuvandudza uye mamwe ma parameter:

Stock quotes, sezvavanochinja maminetsi mashoma ega ega, unogona kugadzirisa kakawanda (checkbox Zorodza yega N min.), asi mitengo yekuchinjana, kazhinji, inokwana kugadzirisa kamwe chete pazuva (iyo cheki bhokisi Update pafaira vhura).

Ziva kuti huwandu hwese hwakatengeswa kunze kwenyika hunobatwa neExcel sechikamu chimwe chete uye kupihwa zita rayo, iro rinogona kuoneka muZita Maneja pane tab. Formula (Mafomula - Maneja Wezita).

Method 2: Parametric web query kuti uwane mutengo wekuchinjana kune yakapihwa zuva renji

Iyi nzira isarudzo yakagadziridzwa zvishoma yekutanga uye inopa mushandisi mukana wekugamuchira mwero wekuchinjana wemari yaunoda kwete yezuva razvino chete, asiwo kune chero imwe zuva kana zuva rekufarira. Kuti tiite izvi, chikumbiro chedu chewebhu chinofanira kushandurwa kuita parametric imwe, kureva kuwedzera maviri ekujekesa paramita kwairi (code yemari yatinoda uye zuva razvino). Kuti tiite izvi, tinoita zvinotevera:

1. Isu tinogadzira chikumbiro chewebhu (ona nzira 1) kune peji rewebhusaiti yeCentral Bank yeNyika Yedu ine archive yezvidzidzo: http://cbr.ru/currency_base/dynamics.aspx

2. Mune fomu iri kuruboshwe, sarudza mari yaunoda uye isa mazuva ekutanga neanopera:

3. Dzvanya bhatani Kuti uwane data uye mushure memasekonzi mashoma tinoona tafura ine makosi ekosi yatinoda yenguva yakapihwa yemazuva. Skrodza tafura yabuda kusvika pasi womaka kuti itore kunze nekudzvanya pane museve mutema neyero uri pakona yekuruboshwe yewebhu peji (iwe usabvunze kuti sei museve uyu uripo uye usiri padivi petafura - izvi ndizvo. mubvunzo wevagadziri vesaiti).

Iye zvino tiri kutsvaga bhatani rine floppy disk mukona yepamusoro yekona yehwindo Sevha Chikumbiro (Sevha Mubvunzo) uye chengetedza iyo faira nemaparamendi echikumbiro chedu kune chero akakodzera dhairekitori pasi ripi zvaro zita rakanakira - semuenzaniso, in Zvinyorwa zvangu pasi pezita cbr. iqy.  Mushure meizvozvo, iyo Webhu Query hwindo uye ese eExcel anogona kuvharwa izvozvi.

4. Vhura iyo folda yawakachengeta chikumbiro uye tsvaga faira rekukumbira cbr. iqy, wobva wadzvanya pairi - Vhura Ne - Notepad (kana sarudza kubva pane rondedzero - kazhinji ifaira notepad.exe kubva pafolda C: Mahwindo) Mushure mekuvhura faira rekukumbira muNotepad, iwe unofanirwa kuona chimwe chinhu chakadai:

Chinhu chinonyanya kukosha pano mutsara une kero uye mibvunzo yemibvunzo mairi, iyo yatichatsiva - kodhi yemari yatinoda (yakasimbiswa mutsvuku) uye zuva rekupedzisira, ratinozotsiva neranhasi (rakasimbiswa mukati. bhuruu). Nyatsogadzirisa mutsara kuti uwane zvinotevera:

http://cbr.ru/currency_base/dynamics.aspx?VAL_NM_RQ=[“Kodhi yemari”]&date_req1=01.01.2000&r1=1&date_req2=["Date»]&rt=1&modhi=1

Siya zvimwe zvese sezvazviri, chengetedza uye vhara faira.

5. Gadzira bhuku idzva muExcel, vhura pepa ratinoda kuunza archive yeCentral Bank rates. Mune chero sero yakakodzera, isa fomula inozotipa zuva razvino in text format pakutsiva mubvunzo:

=TEXT(NHASI();”DD.MM.YYYY”)

kana muChirungu

=TEXT(NHASI(),»dd.mm.yyyy»)

Pane imwe nzvimbo padyo tinoisa kodhi yemari yatinoda kubva patafura:

Iyo kodhi inodiwa inogona zvakare kupepwa mumutsara tambo zvakananga paCentral Bank webhusaiti.

6. Isu tinotakura data pane pepa, tichishandisa masero akagadzirwa uye cbr.iqy faira senheyo, kureva kuenda kune tab. Dhata - Mabatanidza - Tsvaga Vamwe (Data - Kubatanidza Kuripo). Muwindo rekusarudza dhata rinovhura, tsvaga uye vhura iyo faira cbr. iqy. Tisati taunza kunze kwenyika, Excel inojekesa zvinhu zvitatu nesu.

Kutanga, kupi kupinza tafura yedata:

Chechipiri, kwaungawane kodhi yemari kubva (unogona kutarisa bhokisi Shandisa iyi default value (Shandisa kukosha uku/referensi yezvekuzorodzwa mune ramangwana), kuitira kuti gare gare nguva imwe neimwe sero iyi isina kutaurwa panguva yekuvandudza uye cheki bhokisi Gadzirisa otomatiki kana kukosha kwesero kwachinja (Refresh otomatiki kana kukosha kwesero kwachinja):

Chechitatu, kubva pasero ripi rekutora zuva rekupedzisira (iwe unogona zvakare kutarisa mabhokisi ese ari pano kuitira kuti mangwana haufanirwe kuseta aya maparamita pamaoko paunenge uchivandudza):

Click OK, mirira mashoma mashoma uye utore dura rakazara rekuchinjana kwemari yaunoda pane pepa:

Semune nzira yekutanga, nekudzvanya-kurudyi pane yakaunzwa kunze data uye kusarudza murairo Range zvivakwa (Data range zvivakwa), unogona kugadzirisa chiyero chekuvandudza Pakuvhura faira (Dzorera pafaira rakavhurika). Zvino, kana iwe uchikwanisa kuwana Internet, iyo data inozogadziridzwa otomatiki zuva rega rega, kureva Tafura inozogadziridzwa nedata nyowani.

Zviri nyore kubvisa chiyero chezuva raunoda kubva patafura yedu uchishandisa basa VPR (VLOOKUP) - kana iwe usiri kujairana nazvo, saka ndinokukurudzira kuti uite izvi. Neformula yakadai, semuenzaniso, unogona kusarudza dhora rekuchinjana kwaNdira 10, 2000 kubva patafura yedu:

kana muChirungu =VLOOKUP(E5,cbr,3,1)

apo

• E5 - sero rine zuva rakapihwa
  
• cbr -Zita reiyo data renji (inogadzirwa otomatiki panguva yekutumira uye kazhinji yakafanana sezita remubvunzo faira)
• 3 - iyo serial nhamba yekoramu mutafura yedu, kwatinowana iyo data kubva
• 1 - gakava rinosanganisira kufungidzira kutsvaga kweVLOOKUP basa kuitira kuti iwe uwane makosi emazuva epakati ayo asipo chaizvo mumutsara A (zuva rapfuura uye kosi yaro ichatorwa). Unogona kuverenga zvakawanda nezve fungidziro yekutsvaga uchishandisa VLOOKUP basa pano.

• Macro kuti uwane chiyero chedhora chezuva rakapihwa muchitokisi chazvino
• PLEX yekuwedzera-basa kuti uwane mwero wekuchinjana wedhora, euro, hryvnia, pondo sterling, nezvimwe kune chero zuva rakapihwa.
• Isa chero chiyero chemari pane chero zuva muPLEX yekuwedzera