Lowetsani mitengo yosinthira kuchokera pa intaneti

Kuitanitsa mtengo wa ndalama zomwe zaperekedwa kuchokera pa intaneti ndikuzisintha zokha ndi ntchito yofala kwambiri kwa ogwiritsa ntchito ambiri a Microsoft Excel. Tangoganizani kuti muli ndi mndandanda wamitengo womwe umayenera kuwerengedwanso m'mawa uliwonse malinga ndi mtengo wakusintha. Kapena bajeti ya polojekiti. Kapena mtengo wa mgwirizano, womwe uyenera kuwerengedwa pogwiritsa ntchito ndalama za dollar pa tsiku lomaliza la mgwirizano.

Zikatero, mutha kuthetsa vutoli m'njira zosiyanasiyana - zonse zimatengera mtundu wa Excel womwe mwayika komanso zowonjezera zomwe zili pamwamba pake.

Njira 1: Pempho losavuta lapaintaneti pakusintha kwamitengo yapano

Njirayi ndi yoyenera kwa iwo omwe akadali ndi matembenuzidwe akale a Microsoft Office 2003-2007 pakompyuta yawo. Sichigwiritsa ntchito zowonjezera kapena ma macros kapena ma macros ndipo zimagwira ntchito pazomanga.

atolankhani Kuchokera pa intaneti (Webusaiti) tsamba Deta (Tsiku). Pazenera lomwe likuwoneka, mu mzere Address (Adilesi) lowetsani ulalo wa tsamba lomwe chidziwitsocho chidzatengedwa (mwachitsanzo, http://www.finmarket.ru/currency/rates/) ndikusindikiza kiyi Lowani.

Tsambalo likadzaza, mivi yakuda ndi yachikasu idzawonekera pamatebulo omwe Excel angatenge. Kudina pamivi yotereyi ndi chizindikiro cha tebulo loti mutenge kunja.

Matebulo onse ofunikira akalembedwa, dinani batani Lowani (Tengani) pansi pawindo. Patapita nthawi yofunikira kuti muyike deta, zomwe zili m'matebulo olembedwa zidzawoneka m'maselo omwe ali pa pepala:

Kuti muwonjezere makonda, mutha kudina kumanja pamaselo aliwonsewa ndikusankha lamulo kuchokera pazosankha. Range katundu (Zosiyanasiyana za data).M'bokosi ili, ngati mungafune, ndizotheka kukonza ma frequency osinthika ndi magawo ena:

Magawo a stock, akamasintha mphindi zingapo zilizonse, mutha kusintha pafupipafupi (choka bokosi Tsitsani mphindi iliyonse ya N.), koma mitengo yosinthira, nthawi zambiri, ndiyokwanira kusinthira kamodzi patsiku (bokosi loyang'anira Kusintha kwa fayilo kutsegulidwa).

Zindikirani kuti mitundu yonse yotumizidwa kunja imatengedwa ndi Excel ngati gawo limodzi ndikupatsidwa dzina lake, lomwe limatha kuwoneka mu Name Manager pa tabu. chilinganizo (Mafomula - Woyang'anira Dzina).

Njira 2: Funso lapaintaneti la Parametric kuti mupeze kusinthana kwamasiku omwe aperekedwa

Njirayi ndi njira yoyamba yosinthira pang'ono ndipo imapatsa wogwiritsa ntchito mwayi wolandila ndalama zomwe akufuna osati zamasiku ano, komanso tsiku lina lililonse kapena nthawi yachiwongola dzanja. Kuti tichite izi, pempho lathu lapaintaneti liyenera kusinthidwa kukhala parametric, mwachitsanzo, onjezerani magawo awiri owunikira (code yandalama yomwe tikufuna ndi tsiku lomwe tili). Kuti tichite izi, timachita izi:

1. Timapanga pempho la intaneti (onani njira 1) patsamba la webusayiti ya Banki Yaikulu Yadziko Lathu ndi zolemba zamaphunziro: http://cbr.ru/currency_base/dynamics.aspx

2. Mu fomu ili kumanzere, sankhani ndalama zomwe mukufuna ndikukhazikitsa masiku oyambira ndi omaliza:

3. Dinani batani Kuti mupeze data ndipo patadutsa masekondi angapo tikuwona tebulo lomwe lili ndi mfundo zomwe timafunikira pa nthawi yomwe yaperekedwa. Sungani tebulo lotsatira mpaka pansi ndikuyika chizindikiro kuti lilowetse podina muvi wakuda ndi wachikasu pakona yakumanzere kwa tsambali (osafunsa chifukwa chomwe muviwu uli pomwepo osati pafupi ndi tebulo - izi ndi funso kwa opanga malo).

Tsopano tikuyang'ana batani lokhala ndi floppy disk pakona yakumanja kwawindo Sungani Pempho (Sungani Funso) ndikusunga fayiloyo ndi magawo a pempho lathu ku foda iliyonse yoyenera pansi pa dzina lililonse losavuta - mwachitsanzo, mu Zolembedwa zanga pansi pa dzina cbr. ayi.  Pambuyo pake, zenera la Query Web ndi zonse za Excel zitha kutsekedwa pakadali pano.

4. Tsegulani chikwatu chomwe mudasunga pempho ndikuyang'ana fayilo yopempha cbr. ayi, kenako dinani pomwepa - Tsegulani ndi - Notepad (kapena sankhani pamndandanda - nthawi zambiri ndi fayilo notepad.exe kuchokera mufoda C: Mawindo). Mukatsegula fayilo yopempha mu Notepad, muyenera kuwona chonga ichi:

Chinthu chofunika kwambiri apa ndi mzere wokhala ndi adiresi ndi mafunso omwe ali mmenemo, zomwe tidzalowe m'malo - ndondomeko ya ndalama zomwe tikufuna (zosonyezedwa mofiira) ndi tsiku lomaliza, lomwe tidzalowe m'malo ndi lero (lomwe lasonyezedwa mu bulu). Sinthani mzerewu mosamala kuti mupeze zotsatirazi:

http://cbr.ru/currency_base/dynamics.aspx?VAL_NM_RQ=[“Kodi ndalama”]&date_req1=01.01.2000&r1=1&date_req2=["Tsiku"]&rt=1&mode=1

Siyani china chilichonse momwe chilili, sungani ndikutseka fayilo.

5. Pangani bukhu latsopano mu Excel, tsegulani pepala kumene tikufuna kuitanitsa mbiri ya Central Bank mitengo. Mu cell iliyonse yoyenera, lowetsani fomula yomwe ingatipatse tsiku lomwe likubwera m'mawu olembedwa m'malo mwa funso:

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

kapena mu Chingerezi

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

Penapake pafupi timayika khodi ya ndalama yomwe tikufuna patebulo:

Khodi yofunikira imathanso kuyang'ana pamndandanda wamafunso mwachindunji patsamba la Central Bank.

6. Timayika deta pa pepala, pogwiritsa ntchito maselo opangidwa ndi fayilo ya cbr.iqy monga maziko, mwachitsanzo, pitani ku tabu Deta - Zolumikizana - Pezani Zina (Deta - Milumikizidwe yomwe ilipo). Pazenera losankha gwero la data lomwe limatsegula, pezani ndikutsegula fayilo cbr. ayi. Asanalowetse kunja, Excel ifotokoza zinthu zitatu ndi ife.

Choyamba, komwe mungalowetse tebulo la data:

Kachiwiri, komwe mungapeze ndalama kuchokera (mutha kuwona bokosilo Gwiritsani ntchito mtengo wokhazikikawu (Gwiritsani ntchito mtengo/reference kuti mutsitsimutse mtsogolo), kotero kuti pambuyo pake nthawi iliyonse selo ili silinatchulidwe panthawi yosinthidwa ndi bokosi Sinthani zokha ma cell akusintha (Zitsitsimutsani zokha mtengo wa selo ukasintha):

Kachitatu, kuchokera pa cell yomwe ikuyenera kutenga tsiku lomaliza (mutha kuwonanso mabokosi onse awiri apa kuti mawa musakayikire magawo awa pamanja posintha):

Dinani OK, dikirani masekondi angapo ndikupeza mbiri yathunthu yakusinthana kwa ndalama zomwe mukufuna papepala:

Monga mu njira yoyamba, mwa kuwonekera kumanja pa deta yochokera kunja ndikusankha lamulo Range katundu (Zosiyanasiyana za data), mukhoza kusintha mlingo wotsitsimutsa Mukatsegula fayilo (Bwezeretsani pa fayilo yotsegulidwa). Ndiye, ngati muli ndi intaneti, deta idzasinthidwa tsiku ndi tsiku, mwachitsanzo, tebulo lidzasinthidwa ndi deta yatsopano.

Ndizosavuta kuchotsa mtengo watsiku lomwe mukufuna patebulo lathu pogwiritsa ntchito ntchitoyi VPR (VLOOKUP) - ngati simukuzidziwa, ndiye ndikukulangizani mwamphamvu kuti muchite izi. Ndi njira yotereyi, mwachitsanzo, mutha kusankha ndalama zosinthira pa Januware 10, 2000 patebulo lathu:

kapena mu Chingerezi =VLOOKUP(E5,cbr,3,1)

kumene

• E5 - cell yomwe ili ndi tsiku lomwe laperekedwa
  
• cbr - dzina lamtundu wa data (lopangidwa zokha panthawi yotumiza ndipo nthawi zambiri limakhala lofanana ndi dzina la fayilo yafunso)
• 3 - chiwerengero cha seriyoni patebulo lathu, komwe timapezako deta
• 1 - mkangano womwe umaphatikizapo kufufuza pafupifupi ntchito ya VLOOKUP kotero kuti mutha kupeza maphunziro a masiku apakatikati omwe salipo mu gawo A (tsiku lapafupi lapitalo ndi maphunziro ake adzatengedwa). Mutha kuwerenga zambiri za pafupifupi kusaka pogwiritsa ntchito VLOOKUP apa.

• Macro kuti mupeze mtengo wa dollar watsiku lomwe laperekedwa m'selo yamakono
• PLEX ntchito yowonjezerapo kuti mupeze ndalama zosinthira dola, yuro, hryvnia, pound sterling, ndi zina zotero pa tsiku lililonse
• Ikani mtengo uliwonse wandalama tsiku lililonse pazowonjezera za PLEX