Computer, Software
VBA Excel: Iinkqubo isampula. Macro e Excel
Bambalwa abantu bazi ukuba inguqulelo yokuqala imveliso ezithandwayo Microsoft Excel wabonakala ngowe-1985. Ukususela ngoko olu suku luye unenzondelelo ukuguqulwa eziliqela kunye nemfuno izigidi zabasebenzisi ehlabathini lonke. Noko ke, abaninzi umsebenzi kuphela kancinci kancinci i izakhono yale spreadsheet kwaye musa bazi ukuba bakwazi ukwenza ubomi lula ukuba ubuchule Excel kweenkqubo.
Yintoni VBA
Programming e Excel kwenziwa Visual Basic for isicelo senkqubo ulwimi yakhiwa ekuqaleni kweli spreadsheet idumileyo-Microsoft.
Ukuze yokufaneleka kwalo iingcali wawo kalula zophuhliso. Njengoko yokusebenza ibonisa, VBA uyakwazi kakuhle iziseko, nkqu abasebenzisi abangenazo izakhono zobungcali kweenkqubo. Kuba VBA zibandakanya script ukubulawa kwindawo isicelo ofisi.
Engxakini kule nkqubo kukho ezi ngxaki zinxulumene kunye zokungahambisani kweenguqulelo ezahlukeneyo. Bona kubangelwa yinto yokuba VBA ikhowudi ye-nkqubo ubhekiselele ukusebenza ekhoyo kwi kwinguqulelo entsha imveliso, kodwa hayi kwi endala. Kukho kwakhona engxakini enkulu kunye nokuvuleleka ephakame gqitha ikhowudi ukutshintsha ubuso lowolunye uhlanga. Noko ke, Microsoft Office, kunye IBM Lotus Symphony kuvumela abasebenzisi ukuba ukusebenzisa ufihlo ikhowudi yokungena kunye lokugqitha nezicwangciso ukuyijonga.
Izinto, ingqokelela, iimpawu, kunye neendlela
Ke la magama kufuneka uqonde abo baza kusebenza bume VBA. Okokuqala, kufuneka siqonde ukuba yintoni na into ayikho. Ngo-Excel, kwesi senzo njenge sheet, incwadi, kunye leseli. Ezi izinto kufuneka zabefundisi ezizodwa, ngamanye ukuthobela omnye.
OyiNtloko phakathi kwabo lo Application, iprogram ye-Excel engqinelanayo ngokwayo. Iincwadi belandela, okusebenzela, kunye Range. Umzekelo, ukuba ujonge A1 kuxwebhu ethile ambonise indlela, kuthathelwa ingqalelo elinegunya.
Ngokuphathelele ingqiqo ka "ukuqokelelwa," eli iqela lezinto ze kwiklasi enye, apho ukurekhodwa unikwa ChartObjects. izinto zayo nazo izinto.
into elandelayo - iimpawu. Basimisela inkalo kuyimfuneko nayiphi na into. Umzekelo, kuba Range - lixabiso okanye Formula.
Iindlela - lo umyalelo ukubonisa ukuba ufuna ukwenza. Xa ubhala ikhowudi kwi VBA ukuze zahlulwe ukusuka kwindawo into. Ngokomzekelo, njengoko ziya kuboniswa kamva, ngokufuthi xa lwenkqubo kwi "Excel" ukusetyenziswa Iiseli umyalelo (1.1) .Khetha. Oku kuthetha ukuba kuyimfuneko ukukhetha iseli kunye zilungelelanise (1.1), ngamanye amazwi A1.
Noko ke, loo nto idla ngokusetyenziswa Selection.ClearContents. Ukuphunyezwa kwawo msulwa imixholo yeseli ekhethiweyo.
Indlela ukuqala
Kuqala kwinto zonke, ufuna ukwenza ifayile kwaye ugcine loo nto, ayithiye igama uze ukhethe uhlobo «Book Excel enakho ngamandla."
Ke, hamba uye isicelo VB, leyo kwanele ukuba usebenzise indibaniselwano «Alt» isitshixo kwaye «F11». Next:
- kwi menu bar phezulu kwe window, nqakraza kwi icon ecaleni Excel icon;
- Mudule okhethiweyo umyalelo;
- siqhubeke ngokunqakraza kwi icon kunye ifloppy diski;
- ukubhala, nithi, ulwandlalo ikhowudi.
Kubonakala ngolu hlobo lulandelayo:
INkqutyana ()
'Ikhowudi yethu
isiphelo Sub
Nceda uqaphele ukuba umgca " 'ikhowudi yethu" izaku phawulwa ngombala owahlukileyo (green). Isizathu soku isimeli, kunikelwa ekuqaleni bomtya, nto leyo ibonisa ukuba yintoni elandelayo na izimvo.
Ngoku bhala naluphi ikhowudi kunye nokudala kuba baziqwebela isixhobo esitsha VBA Excel (bona iinkqubo isampula. Njl). Kakade ke, abo aqhelene kwiziseko Visual Basic, kuya kuba lula kakhulu. Noko ke, nkqu nabo bangenalo, ukuba ufuna ukwazi ukuba ukhululeke kakhulu ngokukhawuleza.
Macro e Excel
Emva eli gama kufihla iinkqubo abhalwe Basic zokuBonakalayo ulwimi Application. Ngoko ke, lo zokuhlela Excel - kukudala macro kwikhowudi oyifunayo. Ngesi ukwazi, spreadsheet Microsoft self-esivelayo, sivumelane neemfuno ngumsebenzisi othile. Ukuba iyitshinizele kunye nokudala indlela iimodyuli zokubhala imacros zesikocan, kunokwenzeka ukuba uqhubeke ngemizekelo eziphathekayo lwenkqubo VBA Excel. Kubalulekile kakhulu ukuba uqale ezineekhowudi ezininzi ezingundoqo.
UMZEKELO 1
Umsebenzi: Bhala iprogram eya bakope ixabiso okubhalwe kwiseli enye uze ubhale kwenye.
Ukuze wenze oku:
- ukuvula i tab "View";
- hambisa icon "imacros zesikocan";
- kunyikima kwi "Macro Record";
- agcwalise ifomu wavula.
Ukuze kube lula, kwi "Igama Macro" ashiye "Makros1" kunye "iqhosha ezimfutshane" ifakwe, umzekelo, hh (oku kuthetha ukuba usebenzise inkqubo umzekelo iya kuba «Ctrl + h» Blitz team). Press Enter.
Ke kaloku, ukuba sele uqalile ebhala macro, enze imixholo yeseli ukuya kwenye. Buyela icon yokuqala. Nqakraza kwi "Macro Record". Oku kuphawula kugqitywe applets.
Next:
- kwakhona kokuya ku umtya "Macro";
- olukhethiweyo kwi kuluhlu "Macro 1";
- nqakraza "Baleka" (iqalisa amanyathelo efanayo wasungula shortcut keys «Ctrl + hh»).
Ngenxa yoko, i-intshukumo ethe yenziwa ngexesha ekulobeni macro.
Kwenza ingqondo ukubona indlela ikhowudi ikhangeleka ngathi. Ukuze wenze oku, buyela umtya "Macro" kwaye nqakraza "Hlela" okanye "Faka". Ngenxa yoko, bezifumana kwindawo VBA. Eneneni, ikhowudi ngokwayo ibekwe phakathi kwemigca macro Makros1 Sub () kunye End Inkqutyana.
Ukuba kokukopisha sele wenziwe, umzekelo, ukusuka kwiseli A1 kwisisele C1, omnye imigca ikhowudi iza kukhangeleka ngathi Range ( "C1"). Khetha. Xa translation, kubonakala ngathi "Range (" C1 "). Khetha", ngamanye amazwi, yenza inguqulelo VBA Excel, kuseli C1.
Inxalenye elikhutheleyo ikhowudi ligqibe ActiveSheet.Paste qela. Kuthetha ukurekhoda imixholo yeseli ekhethiweyo (kulo mzekelo, A1) esiseleni C1 ekhethiweyo.
UMZEKELO 2
VBA imijikelo kunceda ukudala imacros zesikocan ezahlukeneyo Excel.
VBA imijikelo kunceda ukudala imacros zesikocan ezahlukeneyo. Ukucinga ukuba kukho umsebenzi y = x + x 3 + 3x 2 - cos (x). Ufuna ukwenza macro imizobo yalo. Oku kunokwenziwa kuphela ngokusebenzisa VBA mijikelo.
Kuba ixabiso lokuqala nelokugqibela kwemisebenzi argument kuthatha x1 = 0 kunye x2 = 10. Ngaphezu koko, kuyimfuneko ukuba ukuqalisa rhoqo - ixabiso kwayelo etshintsha impikiswano kunye ixabiso okokuqala ekhawuntarini.
Yonke imizekelo VBA Excel imacros zesikocan badalwe kusetyenziswa inkqubo efanayo njengoko echazwe ngentla. Kule imeko, ikhowudi kubonakala ngathi:
senkqubo Sub ()
x1 = 1
X2 = 10
noAbhishagi = 0.1
i = 1
Ngaba Nangona x1
y = x1 + x1 ^ 2 + 3 * x1 ^ 3 - eKosi (x1)
Iiseli (i, 1) .Value = x1 (ixabiso x1 ebhaliweyo kwimemori kunye uququzelelo (i, 1))
Iiseli (i, 2) .Value = y (ixabiso y ibhalwe esiseleni kunye uququzelelo (i, 2))
i = i + 1 (Ubalo esebenzayo);
x1 = x1 + noAbhishagi (ingxoxo kutshintshwe ubungakanani inqwanqwa);
loop
Sub End.
Ngenxa yale macro run "Excel" ukufumana ntsika zimbini, owokuqala apho abhalwe amanani x, kwaye eyesibini - to y.
Emva koko ukuhlela bakwazi ukwakha phezu kwabo, umgangatho for "Excel".
UMZEKELO 3
Ukwenza yemijikelo VBA Excel 2010, kwakunye kwezinye iinguqulelo, kunye Do sele kunciphise Nangona uyilo Ukugcina.
Cinga inkqubo kudala mhlathi. Xa iseli nganye iya kurekhodwa izikwere efanayo inombolo yomgca. Ngokuba ukusetyenziswa loyilo iya kuvumela ukuba abhale oko kancinane kakhulu, ngaphandle kokusebenzisa ekhawuntareni.
Okokuqala kuyimfuneko ukwenza i macro, njengoko kuchaziwe apha ngasentla. Emva koko, bhala ikhowudi ngokwayo. Siyakholwa ukuba asinamdla amanani iiseli 10. Ikhowudi ngulo ulandelayo.
Kuba i = 1 ukuya 10 Okulandelayo
Umyalelo idluliselwa ulwimi i "zabantu", njengoko 'phindwa 1 ukuya 10 ngokwemizuzu elinye. "
Ukuba umsebenzi ukufumana ikholam kunye izikwere, umzekelo, zonke integers odd phakathi kwe-1 no-11, asinibhaleli:
Kuba i = 1 ukuya kwi-10 Inyathelo 1 Okulandelayo.
Apha, step - inyathelo. Kulo mzekelo, nto ilingana ezimbini. Ngokungagqibekanga, lingekho ilizwi kwi loop kuthetha ukuba inyathelo elinye.
Iziphumo kufuneka ukuba lugcinwe inombolo yeselula (i, 1). Ke lonke ixesha uqala umjikelo kunye nokwanda kwixabiso i inyathelo iya kukhula kunye nenombolo line ngokuzenzekelayo. Ngaloo ndlela, apho kuya kubakho ikhowudi noun.
Ngokubanzi, ikhowudi ngabe unjani:
INkqutyana ()
Kuba i = 1 To-10 Inyathelo 1 (Ingayingxelo ebhaliweyo kuphela Ngokuba i = 1 Ukuze 10)
Iiseli (i, 1) .Value = i ^ 2 (ngamanye ixabiso square ebhaliweyo yeseli (i, 1) i)
Okulandelayo (ngandlel 'ithile udlala indima indlela counter kunye nesinye isiqalo umjikelezo)
Sub End.
Ukuba yenziwe ngokuchanekileyo, kuqukwa ushicilelo kunye nokubaleka imacros zesikocan (bona. Le miyalelo ngasentla), ngoko kuthiwa ngayo ixesha ngalinye ubungakanani elinikiweyo ziya kufumaneka umhlathi (kulo mzekelo eziquka iiseli 10).
UMZEKELO 4
Kubomi bemihla ngemihla, ngokufuthi kuyimfuneko ukuba sithathe oku okanye eso sigqibo ngokuxhomekeke kwezinye iimeko. Ayikwazi ukwenza ngaphandle kubo VBA Excel. Imizekelo iinkqubo apho ikhondo ngakumbi algorithm ekhethiweyo endaweni ekuqaleni kwangaphambili, ezininzi ezisetyenziswayo ngokuqhelekileyo uyilo Ukuba ... Ngoko (kuba iimeko ezinzima) Ukuba ... Emva koko ... ISIPHELO Ukuba.
Cinga ngemeko ethile. Masithi ufuna ukwenza macro "Excel" ukuba iseli kunye uququzelelo (1.1) iye yabhalwa:
1 Ukuba ingxabano liyavumelana;
0 ukuba impikiswano ngu zero;
1, ukuba impikiswano negative.
Ukudaleka ezikulu for "Excel" iqala ngendlela eqhelekileyo, ngokusebenzisa izitshixo "shushu" Alt kunye F11. Ngaphezu koko yabhalwa ikhodi elandelayo:
INkqutyana ()
x = Iiseli (1, 1) .Value (Lo myalelo unikela ixabiso x zilungelelanise lwe imixholo yeseli (1, 1))
Ukuba x> 0 ke Iiseli (1, 1) .Value = 1
Ukuba x = 0 ke Iiseli (1, 1) .Value = 0
Ukuba x <0 ke Iiseli (1, 1) .Value = -1
Sub End.
Kuhleli yokuqhuba olukhulu kunye uthole "Excel" ixabiso ezinqwenelekayo ingxabano.
imisebenzi VBA
Njengoko uqaphele, kwinkqubo isicelo idumileyo spreadsheet Microsoft ayikho nzima kakhulu. Ingakumbi xa ufunda ukusebenzisa ngayo imisebenzi VBA. Xa zizonke, lo ulwimi lokuprograma wadalelwa yokubhala izicelo "Excel" kunye neLizwi, malunga 160 imisebenzi. Baya lungahlulwa-hlulwa zibe ngamaqela amakhulu. Zezi:
- imisebenzi Mathematical. Ukusebenzisa kwabo impikiswano yexabiso cosine ifunyenwe, i logarithm yendalo, kwaye ngoko ke isigaba yonke.
- imisebenzi zemali. Ngenxa ngokufumaneka kunye nokusetyenziswa kwazo lwenkqubo e Excel, ungafumana izixhobo olusebenzayo accounting zokuhlala zemali.
- imisebenzi Array processing. Ezi ziquka ngohlelo, IsArray; LBound; UBound.
- VBA Excel isebenze ukuba kude kufikwe kumgca. Le iqela enkulu kangangoko. Oku kuquka, umzekelo, umsebenzi Space ukudala umtya kunye nenani izikhewu elilingana impikiswano elipheleleyo okanye iisimboli transfers eASC ukuya ikhowudi ANSI. Bonke bephela esisetyenziswa ngokubanzi kwaye kuvumela ukuba ukusebenza kunye umtya "Excel" ukudala izicelo lula kakhulu ukuba ukusebenza kwezi theyibhile.
- imisebenzi ukuguqulwa kohlobo Data. Umzekelo, CVar ibuyisela Expression ingxabano, uyayiguqulela ukuba uhlobo data umehluko.
- imisebenzi Umhla. Baya ukwandisa kakhulu iimpawu umgangatho "Excel". Ngoko ke, WeekdayName umsebenzi ubuyisela igama (ngokupheleleyo okanye ngokuyinxenye) ngosuku lweveki ngenani yayo. Isebenziseke ngakumbi lo Timer. Yena unika inani lemizuzwana ukuba sikuhambisele kobusuku ngomzuzwana ethile le mini.
- Ukusebenza ukuguqula ukuxambulisana lenombolo iinkqubo inani ezahlukeneyo. Umzekelo, Oct ovelisa ukumelwa elinekona ezintandathu yenombolo.
- imisebenzi ukufomathwa. Eyona ibalulekileyo kwezi yi- Format. It ubuyisela eyongeziweyo lobeko kunye ibinzana efomatiweyo ngokwemiyalelo enikwe kweli inkcazelo fomathi.
- kwaye njalo njalo.
Ufundo iimpawu le misebenzi kwaye izicelo zazo ziya ukwandisa kakhulu umda "Excel".
UMZEKELO 5
Makhe uzame ukusombulula iingxaki ezintsonkothileyo ngakumbi. Umzekelo:
Dan uxwebhu lwephepha ngqo umgangatho iindleko ingxelo yeshishini elo. idinga:
- ukuphuhlisa nesabelo salo iphethini spreadsheet "Excel";
- ukwenza inkqubo VBA eza kubuza ukuba data ekrwada ukugcwalisa ngayo, ukuze enze izibalo eziyimfuneko ze ugcwalise le template yeseli ahambelanayo.
Cinga enye yezisombululo zilandelayo.
Ukudala template
Zonke izenzo lwenziwa kwi unekhasi elisezantsi e-Excel. iiseli Free zigcinelwe kokufaka idatha kwi nyanga, nyaka, iinkampani isihloko-nkonzo, isixa samaxabiso, inqanaba labo ingeniso. Njengoko inani iinkampani (iinkampani), apho le ngxelo ayifakwanga ingxelo, iiseli ukwenza ngokusekelwe lwamaxabiso kunye negama neyobungcali Akundibekelanga kwangaphambili. Iphepha onikwe igama elitsha. Umzekelo, "Ingxelo Օ".
eziguquguqukayo
Ukubhala inkqubo ngokuzenzekelayo uzalise le template, khetha nobhalo. Baya kusetyenziselwa variables:
- NN- inani kumqolo yangoku etafileni;
- TP kunye TF - ezicwangcisiweyo kunye nengeniso kanye;
- SF kunye SP - iindleko ezikhoyo kunye ocwangcisiweyo lilonke;
- IP kunye IF - okucwangcisiweyo level iindleko ezizizo.
Thina isho ngoonobumba efanayo, kodwa i "kwisimaphambili» Itog olwenzeka iyonke loo mhlathi. Umzekelo, ItogTP - Ngokumalunga komxholo othi, njengoko "ingeniso ekucwangciselwa yena."
Isisombululo kule ngxaki usebenzisa VBA lokuprograma
Ukusebenzisa le notation, siya bafumane Ifomula mahluko. Ukuba ufuna ukwenza ukubalwa kwi% esinayo (F - P) / P * 100, yaye isixa-mali ka - (F - P).
Iziphumo zezi izibalo kunokuba ngcono nje ukuba "Excel" spreadsheet iiseli efanelekileyo.
Kuba siphumo yaye ALS enyanisweni afunyenwe ifomula ItogP = ItogP + P kunye ItogF = ItogF + F.
Kuba ezinxaxhileyo sebenzisa = (ItogF - ItogP) / ItogP * 100 ukuba ubalo lwenziwa njengepesenti, kwaye ukuba ixabiso lilonke - (ItogF - ItogP).
Iziphumo abhalwe kwakhona kwi iiseli efanelekileyo, ngoko akukho mfuneko yokuba kunikezwe ukuba nesebenzayo.
Phambi kokuqala ukudala inkqubo ufuna ukugcina yomsebenzi, umzekelo, phantsi kwegama "Otchet1.xls".
Cinezela "Yenza itafile ingxelo," kufuneka cinezela emva kokungena ulwazi iheader ixesha 1 kuphela. Kufuneka wazi kunye neminye imithetho. Ngokukodwa, kwiqhosha "Faka umgca" kufuneka licinezelwe emva kokungena etafileni amaxabiso kumsebenzi ngamnye ngexesha ngalinye. Emva kokungena zonke iinkcukacha ezifunekayo ukuze cofa iqosha le "Gqiba", uze batshintshele "Excel" kwi window.
Ngoku uyazi indlela yokucombulula ingxaki kuba Excel kunye imacros zesikocan. Ukukwazi ukusebenzisa vba Excel (bona iinkqubo isampula. Ngaphezu) kwaye onokufuna ukusebenza kwindawo adume ngalo mzuzu, umhleli wokubhaliweyo "iLizwi." Ngokukodwa, ngokubhala, njengoko kubonisiwe ekuqaleni kweli nqaku, okanye ngokubhala ikhowudi ukudala amaqhosha menu ngalo ezininzi imisebenzi okubhaliweyo kunokwenziwa ngokucinezela izitshixo emsebenzini okanye ngokusebenzisa i "Mboniselo" icon kwaye "Macro 'tab.
Similar articles
Trending Now