Makombiyuta, Software
VBA Excel: mharidzo zvirongwa. Macro muna Excel
vanhu vashoma vanoziva kuti wokutanga shanduro yakakurumbira Microsoft Excel chigadzirwa akazviratidza muna 1985. Kubvira ipapo kwave kuchiita okunatsiridza anoverengeka uye kudiwa emamiriyoni vanoshandisa pose. Zvisinei, zhinji basa pamwe zvishoma zvatinokwanisa kuti spreadsheet ichi chete uye havatombozivi kuti vaikwanisa kuita upenyu nyore kukwanisa Excel zvirongwa.
Chii VBA
Zvirongwa Excel zvinoitwa Visual Basic nokuda Application mutauro zvirongwa kuti pakutanga yakavakwa yakakurumbira spreadsheet kubva Microsoft.
To munorumbidzirweiko ake nyanzvi vanoti hama nyore okukura. Sezvo tsika inoratidza VBA anogona ishe zvinokosha, kunyange vanoshandisa vasina nyanzvi zvirongwa unyanzvi. For VBA zvinhu zvinosanganisira manyorero nokuurayiwa muhofisi chikumbiro kwezvakatipoteredza.
Zvakaipira chepurogiramu ndivo matambudziko chokuita kugarisana versioner siyana. Vari anokonzerwa nokuti VBA bumbiro chirongwa rinoreva functionality kuti aripo itsva shanduro chigadzirwa, asi kwete mune hwekare. Panewo guru Vakaomerwa uye zvakanyanyisa yakakwirira kubudirana pachena kuti bumbiro kushandura chiso romueni. Zvisinei, Microsoft Office, uye IBM Lotus Symphony anobvumira vanoshandisa kushandisa encryption kundopinda bumbiro uye pasiwedhi Kurongwa kuhuona.
Zvinhu, akaunganidzwa, zvivako, uye nzira
Zviri izvi pfungwa unofanira kunzwisisa avo vari kuenda kubasa muna VBA zvakatipoteredza. Chokutanga, unofanira kunzwisisa kuti chinhu chiri. In Excel, mune chiito ichi sezvo jira, rimwe bhuku, uye masero siyana. Izvi zvinhu vane chinokosha akanyatsorongwa, i.e. kuteerera mumwe.
Chief pakati pavo ndiye Application, kuti unoenderana Excel chirongwa pacharo. Akatevera Workbook, Mapepa, uye Range. Somuenzaniso, kureva sero A1 pamusoro yakananga jira anofanira anongedzere nzira, kufungisisa vakuru.
Achitaura pfungwa "muunganidzwa," boka iri vanovengwa kirasi chete, umo kurekodha anopiwa ChartObjects. zvinhu zvayo ndiwo zvinhu.
Next chinhu - ehupfumi. Ivo chinodiwa chinokosha chero chinhu. Somuenzaniso, nokuti Range - ari Kukosha kana Formula.
Nzira - ndiwo murayiro kuratidza kuti unoda kuti. Paainyorera bumbiro iri VBA kuti kuparadzaniswa kubva chinhu pfungwa. Somuenzaniso, sezvo kucharatidzwa Papera kakawanda apo zvirongwa mu "Excel" kushandiswa Masero murayiro (1,1) .Select. Zvinoreva kuti zvinokosha kusarudza muchitokisi Coordonatele (1,1), i.e. A1.
Zvisinei, izvozvo rinowanzoshandiswa Selection.ClearContents. Implementation Its iri kuchena zviri musero yasarudzwa.
How kutanga
Chokutanga, unoda pakusika faira uye kunze, kurongera zita uye wasarudza mhando «Book Excel makro-akwanise."
Zvadaro, enda VB chikumbiro, inova zvakakwana kushandisa ibatanidzo «alt» kiyi uye «F11». Next:
- ari Muterere pakapuranga iri pamusoro hwindo, tinya icon pedyo Excel kuvake;
- Mudule yasarudzwa murayiro;
- rambai nekudzvanya pamusoro icon pamwe floppy dhisiki;
- kunyora, muti, bumbiro pepa.
Zvinoita sezvinotevera:
Sub chirongwa ()
'Bumbiro Our
End Sub
Tapota ona kuti mutsetse " 'bumbiro redu" chichange inosimbiswa yakasiyana ruvara (rwegirini). Chikonzero apositurofi ichi, akarwira kumavambo tambo, izvo zvinoratidza kuti chii kunotevera chinhu mhinduro.
Zvino unogona kunyora chero bumbiro nokusika vega mutsva chishandiso VBA Excel (ona mharidzo zvirongwa. Nezvimwewo). Chokwadi, vaya vanoziva zvinokosha Visual Basic, zvichava nyore. Zvisinei, kunyange vaya vasina, kana uchida kukwanisa kuwana hwomutambarakede nokukurumidza zvikuru.
Macro muna Excel
Shure zita iri anovanza zvirongwa akanyorwa Visual Basic kuti mutauro Application. Nokudaro, zvirongwa kuna Excel - ndiko kuti pave makro kuna achida bumbiro. With kugona ichi, Microsoft spreadsheet kuzvidzora zvikamuka, achichinja kuita zvinodiwa imwe user. Wakabata pamwe sei kuti pave modules kunyora macro, zvinokwanisika kuti asaende kongiri mienzaniso VBA Excel chirongwa. Zvakanyanyokosha kutange inokosha Codes.
MUENZANISO 1
Basa: Nyora chirongwa kuti zvichaita kutevedzera kukosha zviri rimwe muchitokisi uye nyora mumwe.
Kuti aite izvi,
- kuzarura Tab "View";
- kufamba icon "macro";
- zunzai kuti "Record Macro";
- zadza akazarura chimiro.
Kuti zvive nyore, munyaya "Zita Macro" siya "Makros1" uye mu "yokudimbudzira kiyi" akaiswa Somuenzaniso, hh (ichi zvinoreva kuti iwe unogona kumhanya purogiramu muenzaniso achava «Ctrl + H» blitz chikwata). Press Enter.
Zvino kuti imi yakatanga achinyora makro, vanoumba zviri sero kune mumwe. Dzokera chepakutanga kuvake. Click on "Record Macro". chiito ichi chinoratidza anopedza applets.
Next:
- zvakare kutamira tambo "macro";
- anosarudzwa mu ndandanda "Macro 1";
- baya "Mhanya" (zvakafanana chiito kunotangira raparurwa yokudimbudzira makiyi «Ctrl + hh»).
Somugumisiro, nezviito rave anoitwa panguva achinyora makro.
Zvine musoro kuona kuti bumbiro sokunge. Kuti aite izvi, udzokere tambo "macro" uye baya "Edit" kana "Pindai". Somugumisiro, vanowana pachavo vari VBA zvakatipoteredza. Chaizvoizvo, bumbiro remitemo pacharo riri pakati pemitsara makro Makros1 Sub () uye End Sub.
Kana akatevedzera yave mabasa Somuenzaniso, kubva muchitokisi A1 muchitokisi C1, mumwe mitsetse remitemo vachatarira akafanana Range ( "C1"). Sarudza. In rokushandura, zvinoita sokunge "Range (" C1 "). Sarudza", mune mamwe mazwi, anoita yezwi VBA Excel, muchitokisi C1.
Chinoshingaira chikamu bumbiro rinopedzisa ActiveSheet.Paste chikwata. Kunoreva Pakunyora mukati yasarudzwa sero (iyi, A1) ari dzakasarudzwa muchitokisi C1.
MUENZANISO 2
VBA zvinogara kubatsira kuumba macro yakasiyana Excel.
VBA zvinogara kubatsira kusika macro siyana. Vanofunga kuti pane basa Y = X + x 3 + 3x 2 - Kosi (x). Unoda kusika munhu makro nokuda Graphics ayo. Izvi zvinogona kuitwa chete nokushandisa VBA zvinogara.
Nokuti kutanga uye yokupedzisira ukoshi nharo mashandiro tora x1 = 0 uye x2 = 10. Uyezve, zvinokosha kuti ataure dzose - mutengo danho anochinja nharo uye kwokutanga ukoshi nokuda yemabhuku.
All mienzaniso VBA Excel macro vakasikwa kushandisa imwe nzira sokwakarongwa kumusoro. Akanyanya nyaya iyi, bumbiro remitemo wakatodzana:
Sub programm ()
x1 = 1
x2 = 10
shag = 0.1
Ini = 1
Ko Nepo x1
Y = x1 + x1 ^ 2 + 3 * x1 ^ 3 - Kosi (x1)
Masero (i, 1) .Value = x1 (x1 ukoshi zvakanyorwa ndangariro pamwe Coordonatele (i, 1))
Masero (i, 2) .Value = y (Y ukoshi zvakanyorwa muchitokisi pamwe Coordonatele (i, 2))
Ini = Ini + 1 (nechikonzero count);
x1 = x1 + shag (nharo ikaita danho kukura);
Loop
End Sub.
Somugumisiro run ichi makro "Excel" awane mbiru mbiri, yokutanga iyo zvinonyorwa tsika kuti X, uye wechipiri - kuti ja.
Zvadaro yaunoti akakwanisa kuvaka pamusoro pavo, mureza kuti "Excel".
MUENZANISO 3
Kuti dzika zvinogara muna VBA Excel 2010, uyewo mune dzimwe shanduro, pamwe kare wakaderedzwa Do Nepo kugadzira kushandiswa For.
Funga purogiramu pave mbiru. Muchitokisi chimwe nechimwe zvichanzi kunyorwa dzinoungana zvinoenderana mutsetse nhamba. Nokuti kushandiswa akagadzira kuchaita kuti anyore izvozvo zvishomanana kwazvo, pasina kushandisa pakaunda.
Chokutanga zvakafanira kusika munhu makro, sezvinorondedzerwa pamusoro. Next, nyora bumbiro remitemo pachayo. Tinotenda kuti tiri kufarira tsika nokuda 10 masero. The code sezvinotevera.
Nokuti I = 1 kusvika 10 Next
Shoko hwakatamisirwa mutauro "munhu", se "inodzokororwa 1 kusvika 10 increments imwe."
Kana basa kugamuchira mbiru pamwe dzinoungana Somuenzaniso, zvose vasinganzwisisike integers pakati 1 uye 11, tinonyora:
Nokuti ini = 1 kusvika 10 danho 1 Next.
Pano, vanotsika - danho. Muchiitiko chino, zviri akaenzana maviri. By default, kusavapo shoko loop zvinoreva kuti rimwe danho.
Migumisiro vanofanira kuchengetwa muchitokisi nhamba (i, 1). Zvadaro nguva dzose iwe itange kutenderera pamwe nokuwedzera kukosha ini vanozopinda vachangoerekana kukura uye mutsetse nhamba. Saka, pachava bumbiro Optimization.
Kazhinji, bumbiro remitemo taitsvaka akafanana:
Sub chirongwa ()
Nokuti ini = 1 To 10 Step 1 (anogona kunyorwa Nokuti ini chete = 1 To 10)
Masero (i, 1) .Value = Ini ^ 2 (i.e. mativi ukoshi zvakanyorwa mu sero (i, 1) i)
Next (mupfungwa anoridza basa pakaunda zvinoreva uye mumwe kutenderera kutanga)
End Sub.
Kana waita zvakarurama, kusanganisira kurekodha uye kumhanya macro (ona. Mirayiridzo pamusoro), zvino inonzi nguva imwe neimwe kupiwa saizi achava kuwanikwa Mbiru (mu iyi zvinosanganisira masero 10).
MUENZANISO 4
Muupenyu hwezuva nezuva, kazhinji kacho zvakakodzera kutora ichi kana kuti sarudzo zvichienderana mamwe mamiriro. Kuita pasina kwavari VBA Excel. Mienzaniso zvirongwa umo mberi weboka algorithm anosarudzwa pachinzvimbo pakutanga zvakafanorongwa, anowanzodanwa kushandiswa magadzirirwe kana ... Ipapo (nokuti akaoma nyaya) Kana ... Zvadaro ... KUPERA Kana.
Funga nezveimwe nyaya. Ngatitii unoda pave makro kuti "Excel" kuti sero pamwe Coordonatele (1,1) yakanyorwa:
1 kana nharo akanaka;
0 kana nharo iri razero;
1, kana nharo iri kunaka.
The kusikwa yakadaro makro kuti "Excel" inotangira mwero nzira, kuburikidza kushandiswa "inopisa" makiyi alt uye F11. Uyezve akanyorwa anotevera code:
Sub chirongwa ()
x = Masero (1, 1) .Value (murayiro uyu ibasa ukoshi X Coordonatele muchitokisi mukati (1, 1))
Kana X> 0 Zvino Masero (1, 1) .Value = 1
Kana x = 0 Zvino Masero (1, 1) .Value = 0
Kana X <0 Zvino Masero (1, 1) .Value = -1
End Sub.
Zvazvinosarira kumhanya makro uye kuwana mu "Excel" achida ukoshi nokuda nharo.
VBA mabasa
Sezvaungave wakacherechedza, mu yakakurumbira chirongwa Microsoft spreadsheet kushanda hakusi kuoma. Kunyanya kana ukadzidza kuishandisa VBA mabasa. In zvachose, mutauro uyu zvirongwa akasikwa zvakananga kunyora mafomu mu "Excel" uye Shoko, vanenge 160 mabasa. Vanogona kukamurwa kuva mapoka makuru akawanda. Ivo:
- Kwemasvomhu mabasa. Kushandisa navo nharo pamusoro cosine mutengo inotengwa, yepanyama logarithm, uye saka yose chikamu.
- Financial mabasa. Nokuda kwavo Kuwanikwa uye kushandisa zvirongwa Excel, unogona kuwana vanobudirira maturusi kuti chizvidavirire uye misha zvemari.
- Array kugadzira mabasa. Izvi zvinosanganisira Array, IsArray; LBound; UBound.
- VBA Excel kushanda kuti mutsetse. Uyu kumbonyanya guru boka. Unosanganisira Somuenzaniso, Space basa kusika tambo nevakawanda Makwanza akaenzana integer nharo kana Asc anotamisa zviratidzo kuna ANSI romutemo. Vose vari zvinowanzoshandiswa uye kubvumira kuti kushanda netambo mu "Excel" kuumba chikumbira nyore kushanda matafura izvi.
- Data mhando kutendeuka mabasa. Somuenzaniso, CVar anodzoka Rwekutaura nharo, vachichinja kuna Musiyano date mhando.
- Date mabasa. Ivo wedzera zvikuru mureza zviri "Excel". Saka, WeekdayName basa akadzorera zita (dzizere kana rushoma) pazuva kwevhiki maererano nhamba yacho. Kunyange zvikuru ndiyo Timer. Anopa uwandu mumasekonzi kuti kubva pakatsaurirwa pakati pousiku kuti imwe nguva zuva.
- Kushanda kutendeutsa munhu Numeric nharo iri zvakasiyana nhamba hurongwa. Somuenzaniso, Oct outputs kune octal cherudo nhamba.
- formatting mabasa. Anokosha aya ndiwo Format. It anodzoka munhu Musiyano ane mashoko mashoko maererano nemirayiridzo kupiwa rondedzero pamanyorerwo.
- nezvakadaro.
The kudzidza zvivako mitambo iyi uye chikumbiro yavo yekudyidzana akudziridze kuri kuitwa "Excel".
MUENZANISO 5
Ngatitaurei kuedza kugadzirisa zvinetso zvikuru kunzwisisa. Somuenzaniso:
Dan bepa gwaro chaiko pamwero mari ebhizimusi mushumo. kunoda:
- kukudziridza wayo muenzaniso mugove ne spreadsheet "Excel";
- kugadzira VBA chirongwa kuti angakumbirwa mbishi Data kuzadza, ndokuatakurira kunze anodiwa Masvomhu uye zadza template pamusoro runoenzanirana muchitokisi.
Chimbofunga mumwe anotevera mhinduro.
Kutanga template
Zviito All anoteererwa pamusoro mureza jira iri Excel. Free masero dzichengeterwe kupinda mashoko pamusoro mwedzi, gore, zita-nevatengi makambani, kuwanda mari, nezera ravo turnover. Sezvo nhamba makambani (makambani), nekuda iyo mashoko asina kunyorwa, masero kuti pachishandiswa tsika uye zita nyanzvi haina chengeterwa pachine. Pepa kupiwa zita idzva. Somuenzaniso, "Օ Report".
variables
Kunyora chirongwa vapiwa zadza template, kusarudza notation. Vachava kushandiswa variables:
- NN- Kuwanda kuitika mutsara patafura;
- TP uye TF - aronga uye chaiyo turnover;
- SF uye SP - zvomene uye akaronga zvachose mari;
- IP uye KANA - aronga uye zvomene mari pamwero.
We kureva nokuda chete tsamba, asi ane "chivakashure» Itog kuwanda zvachose kuti mbiru. Somuenzaniso, ItogTP - With Panyaya mbiru yaiti, se "vakaronga turnover."
Solution dambudziko vachishandisa VBA zvirongwa
Tichishandisa notation ichi, tawana nzira nokuda makakava. Kana uchida kuita kuverenga zviri% tine (F - P) / P * 100, uye ari uwandu - (F - P).
Zvakabuda Masvomhu izvi zvinogona yakanakisisa chete kuti "Excel" spreadsheet mumasero akakodzera.
Nokuti zvaitika uye fungidziro chaizvoizvo yaunganidzwa Masvomhu ItogP = ItogP + P uye ItogF = ItogF + F.
For zvarakasiya vanoshandisa = (ItogF - ItogP) / ItogP * 100 kana dzemaitirwe rinoitwa kuva chikamu, uye kana mari - (ItogF - ItogP).
Zvakabuda zvakare yakanyorwa mumasero chakakodzera, saka hapana chikonzero kupa kune variables.
Asati atanga kusika chirongwa unoda kuponesa workbook Somuenzaniso, pasi zita "Otchet1.xls".
Press "Sikai tafura nhau," unofanira achizviendesa chete 1 nguva tapinda Header ruzivo. Unofanira kuziva uye dzimwe mitemo. Kunyanya, bhatani "Wedzerai mutsetse" anofanira chakatsindirwa neimwe nguva pashure vachipinda tafura tsika nokuda kwebasa nerimwe. Tapinda ose Data kunodiwa kuti achizviendesa bhatani "Finish", uye ipapo chinja kuti "Excel" pahwindo.
Zvino kuti munoziva kugadzirisa dambudziko Excel pamwe macro. Kugona kushandisa vba shasha (ona mharidzo zvirongwa. Pamusoro) uye ungafanira kushanda zvakatipoteredza zvikuru nevanhu panguva iyoyo, rugwaro mupepeti "Shoko." Zvikurukuru kupfurikidza pakunyora, sezvinoratidzwa pakutanga ino, kana nokunyorera romutemo kusika Muterere mabhatani vazhinji remapazi pamusoro rugwaro zvinogona kuitwa kuburikidza izvo nokutsikirira kiyi pabasa kana kuburikidza "View" mifananidzo uye "macro" Tab.
Similar articles
Trending Now