Amakhompyutha, Isofthiwe
VBA Excel: isampula izinhlelo. Amamakhro Excel
Bambalwa abantu bazi ukuthi inguqulo yokuqala ethandwa umkhiqizo-Microsoft Excel wabonakala ngo-1985. Kusukela ngaleso sikhathi kube usebhekene ukulungiswa eziningana uphoqe zabasebenzisi emhlabeni wonke. Nokho, abaningi umsebenzi kuphela kancane amakhono kancane yalesi spreadsheet futhi alazi ukuthi babekwazi kanjani kube lula ukuphila ikhono Excel izinhlelo.
Kuyini VBA
UHlelo Excel kwenziwa Visual Basic Isicelo ulimi lokwakha ukuthi kuleli gatsha elakhiwa ngo-spreadsheet edume kakhulu kusuka ku-Microsoft.
Ukuze yokufaneleka kwalo ochwepheshe uqaphele lo kalula isihlobo yentuthuko. Njengoba umkhuba ubonisa, VBA ungakwazi kahle izisekelo, ngisho abasebenzisi abangenayo professional amakhono inhlelo. Ukuze VBA izici zihlanganisa kweskriphthi imvelo ehhovisi isicelo.
Ububi hlelo yilezi lapho umuntu enezinkinga ezihambisana ukuhambisana izinguqulo ezihlukahlukene. Basuke kubangelwa iqiniso lokuthi VBA ikhodi zohlelo libhekisela ukusebenza elikhona inguqulo entsha lomkhiqizo, kodwa hhayi lokhu okudala. Kukhona kusimo enkulu nokuvuleka eziphakeme ngokweqile ikhodi ukushintsha ubuso lesihambi. Nokho, i-Microsoft Office, bese-IBM Lotus Symphony ivumela abasebenzisi ukusebenzisa ukubethela yokufaka ikhodi nephasiwedi izilungiselelo ukuyibuka.
Izinto, amaqoqo, izakhiwo, kanye nezindlela
Kuyinto nalezi imiqondo udinga ukuqonda kulabo ozosebenza ku VBA imvelo. Okokuqala, kufanele siqonde ukuthi into. Ku-Excel, kule isenzo njengoba ishidi, incwadi, futhi ibanga leseli. Lezi izinto kufanele wokwehlayo ekhethekile, isb ukulalela nomunye.
Chief phakathi kwazo sohlelo lokusebenza, elihambisana Excel hlelo ngokwalo. Letincwadzi tekusebentela tikhicitelwe balandela, okusebenzela, futhi Range. Ngokwesibonelo, ukuze ubheke kuseli A1 uyibhala ethize kumele ukhombe indlela, ikhumbula sabaphathi.
Ngokuphathelene welithi "iqoqo," leli qembu kwezinto ekilasini efanayo, lapho ukuqopha kunikezwa ChartObjects. izakhi zayo nazo lezinto.
Into elandelayo - izakhiwo. Ziyakwazi isici esidingekayo kunoma yimuphi into. Ngokwesibonelo, i-Range - kuyinto Inani noma ifomula.
Izindlela - okuwumyalo ukukhombisa ukuthi ufuna ukwenza. Lapho ebhalela ikhodi ye-VBA ukuba kwehlukaniswa iphuzu into. Ngokwesibonelo, njengoba uyoboniswa kamuva, ngokuvamile kakhulu lapho uHlelo ku "Excel" ukusetshenziswa Amaseli umyalo (1,1) .Khetha. Kusho ukuthi-ke kuyadingeka ukukhetha iseli nge izixhumanisi (1.1), isb A1.
Nokho, ngokuvamile sisetshenziswa Selection.ClearContents. Ukuqaliswa Its ukusula okuqukethwe kuseli elikhethiwe.
Kanjani ukuqala
Okokuqala, ufuna ukwakha ifayela bese ugcine it, nika igama bese ukhetha uhlobo «Incwadi Excel vumelekile amandla."
Khona-ke, uye kuhlelo lokusebenza VB, okuyinto kwanele ukusebenzisa inhlanganisela «Alt» ukhiye bese «F11». Okulandelayo:
- kubha imenyu phezulu ewindini, chofoza uphawu eduze Excel isithonjana;
- Mudule akhethiwe umyalo;
- ukugcina ngokuchofoza uphawu ne iflophidiski;
- ukubhala, bathi, uhlaka ikhodi.
Kubukeka kanje:
Uhlelo Sub ()
'Ikhodi yethu
Ukuphela Sub
Sicela uqaphele ukuthi ulayini " 'ikhodi yethu" kuzobekwa obala ngombala ohlukile (green). Isizathu salokhu aphostrofi, zilethwe ekuqaleni string, okubonisa ukuthi yini elandelayo kuyinto amazwana.
Manje ungakwazi ukubhala noma iyiphi ikhodi bese udala bona ithuluzi elisha e VBA Excel (bheka izinhlelo isampula. Njll). Yiqiniso, labo abangababiziweyo nolwazi oluyisisekelo Eziyisisekelo Visual, ke kuyoba lula kakhulu. Nokho, ngisho nalabo abangenalo, uma ufuna ukwazi ukuthola ukhululekile ngokushesha impela.
Amamakhro Excel
Ngemuva leli gama efihla izinhlelo abhalwe Visual Basic kulimi Isicelo. Ngakho, zokuhlela Excel - ukudala osezingeni ikhodi oyifunayo. Nge leli khono, Microsoft-spreadsheet self-eba, uvumelane nezimo nezidingo umsebenzisi othile. Njengoba asebenzelana ngayo indlela ukudala amamojuli zokubhala amamakhro, kungenzeka ukuqhubeka izibonelo ezishaya emhloleni zendlela azisebenzisa Uhlelo VBA Excel. Kungcono ukuqala nge amakhodi eyisisekelo.
ISIBONELO 1
Umsebenti: Bhala uhlelo ezokwenza ukukopisha ukubaluleka okuqukethwe iseli bese ubhala kwenye.
Ukuze wenze lokhu:
- ukuvula ithebhu "Buka";
- ukuhambisa uphawu "amamakhro";
- nyakazisa ukuze lo "Macro Record";
- ugcwalise ifomu ivulekile.
Ukuze kube lula, "Igama Macro 'ashiye' Makros1" futhi esithi "key Isinqamuleli" sifakwe, isibonelo, hh (lokhu kusho ukuthi ungakwazi ukusebenzisa uhlelo Ngokwesibonelo kuyoba «Ctrl h» Blitz ithimba). Cindezela u-Enter.
Manje ukuthi usuqalile ukuqopha Macro, bakha okuqukethwe isitokisi bazifake kwesinye. Buyela isithonjana yasekuqaleni. Chofoza le "Macro Record". Lesi senzo kumaka kwaqedwa applets.
Okulandelayo:
- futhi bathuthela string "Amamakhro";
- kuyakhethwa ohlwini "Macro 1";
- chofoza "Run" (okufanayo isinyathelo kuqala Yethulwe ukunqamulela okhiye «Ctrl hh»).
Ngenxa yalokho, isenzo eye eyenziwa ngesikhathi ebhala umbhalo ogcinwayo Macro.
Kunengqondo ukuba sibone ukuthi ikhodi libukeka. Ukuze wenze lokhu, phindela string "Amamakhro" bese uchofoza "Hlela" noma "Faka". Ngenxa yalokho, bazithola kule VBA imvelo. Empeleni, ikhodi ngokwayo itholakala phakathi imigqa Macro Makros1 Sub () kanye Ukuphela Sub.
Uma ukukopisha eye eyenziwa, isibonelo, kusukela kuseli A1 kuseli C1, omunye imigqa yekhodi izobukeka Ibanga ( "C1"). Khetha. Enguqulweni, kubukeka sengathi "Ibanga (" C1 "). Khetha", ngamanye amazwi, yenza ushintsho ukuze VBA Excel, kuseli C1.
Liyingxenye ematasa ikhodi uqeda ActiveSheet.Paste ithimba. Kusho ukuqopha okuqukethwe akhethiwe cell (kuleli cala, A1) elikhethiwe kuseli C1.
ISIBONELO 2
VBA imijikelezo ukusiza ukwakha amamakhro ezahlukene Excel.
VBA imijikelezo ukusiza ukwakha amamakhro ezahlukene. Ucabange ukuthi kukhona y umsebenzi = x + x 3 + 3x 2 - cos (x). Ufuna ukwakha Macro for ihluzo zayo. Lokhu kungenziwa kuphela ngokusebenzisa VBA imijikelezo.
Ukuze ukubaluleka kokuqala nokokugcina KWEMIBONO imisebenzi ukuthatha x1 = 0 futhi x2 = 10. Ngaphezu kwalokho, kubalulekile ukuba lapho ethula umyalo othile njalo - inani isinyathelo eshintsha i-agumenti futhi inani lokuqala counter.
Zonke izibonelo VBA Excel amamakhro zidalwe kusetshenziswa inqubo efanayo ebhalwe ngenhla. Kulolu daba, ikhodi libukeka:
Programm Sub ()
x1 = 1
X2 = 10
Shag = 0.1
i = 1
Ingabe Nakuba x1
y = x1 + x1 ^ 2 + 3 * x1 ^ 3 - Cos (x1)
Amaseli (i, 1) .Value = x1 (x1 ukubaluleka kulotshiwe ngohlelo izixhumanisi (i, 1))
Amaseli (i, 2) .Value = y (y Inani okulotshwe esitokisini izixhumanisi (i, 2))
i = i + 1 (ukubala evumelekile);
x1 = x1 + Shag (agumenti washintsha usayizi isinyathelo);
Loop
Ukuphela Sub.
Ngenxa yalokhu Macro run "Excel" ukuthola amakholomu amabili, owokuqala zazo ezilotshwe amanani x, kanti eyesibili - ukuze y.
Khona-ke, wakwazi ukwakha kubo, indinganiso "Excel".
ISIBONELO 3
Ukuqalisa imijikelezo e VBA Excel 2010, kanye nezinye izinguqulo, kanye Do encishisiwe kakade Ngesikhathi design esetshenziswa Ukuze.
Cabanga uhlelo kudala ikholomu. Engqamuzaneni ngalinye kuyothiwa izikwele elihambisana Inambalayini eqoshiwe. Ukuze ukusetshenziswa design kuzovumela ukurekhoda ke kafushane kakhulu, ngaphandle kokusebenzisa esiphikisayo.
Okokuqala kubalulekile ukwakha Macro, njengoba kuchaziwe ngenhla. Ngokulandelayo, ukubhala ikhodi ngokwayo. Sikholelwa ukuthi sinesithakazelo kwesikwenzayo amanani amaseli 10. Ikhodi kanje.
Ukuze i = 1 kuya ku-10 Landelayo
Umyalo udluliselwa ulimi 'ongumuntu ", njengoba" ngokuphindaphindiwe kusuka 1 kuya 10 e ziyakhula elilodwa. "
Uma umsebenzi ukwamukela ikholomu izikwele, isibonelo, zonke integers lingalingani phakathi kuka 1 no 11, sibhala:
Ukuze i = 1 kuya ku-10 Isinyathelo 1 Landelayo.
Lapha, ushiya - sinyathelo. Kulokhu, kubalulekile elilingana ezimbili. Ngokuzenzakalelayo, ukungabi khona izwi kumjikelezo kusho ukuthi isinyathelo esisodwa.
Imiphumela kufanele agcinwe ku inombolo cell (i, 1). Bese kuthi njalo lapho uqala umjikelezo begodu kukhula ukubaluleka i ushiya luyokhula ngokuzenzakalelayo futhi Inambalayini. Ngakho, ngeke kube khona ikhodi nokwenza.
Ngokuvamile, ikhodi ngabe unjani:
Uhlelo Sub ()
Ukuze i = 1 Ukuze 10 Isinyathelo 1 (kungenziwa ebhaliwe kuphela Ukuze i = 1 Ukuze 10)
Amaseli (i, 1) .Value = i ^ 2 (isb ukubaluleka isikwele kulotshiwe esitokisini (i, 1) i)
Okulandelayo (ngomqondo udlala indima izindlela esiphikisayo bese enye isiqalo umjikelezo)
Ukuphela Sub.
Uma kwenziwe ngendlela efanele, kufaka phakathi ukuqoshwa kanye nokugijima amamakhro (bheka. I imiyalelo ngenhla), khona-ke kubizwa ngokuthi isikhathi ngasinye usayizi inikezwe izotholwa ikholomu (kuleli cala ehlanganisa amaseli 10).
ISIBONELO 4
Ekuphileni kwethu kwansuku zonke, ngokuvamile kakhulu kubalulekile ukuthatha leli noma leso sinqumo kuye kwezinye izimo. Ayikwazi ukwenza ngaphandle nabo VBA Excel. Izibonelo izinhlelo lapho Yiqiniso eminye ye-algorithm kuyakhethwa esikhundleni ekuqaleni elinqunyiwe, ngokuvamile ezisetshenziswa Umklamo Uma ... Bese (ngoba amacala anzima) Uma ... Bese ... UKUPHELA Uma.
Cabanga ngendaba ethile. Ake sithi ufuna ukwakha Macro for "Excel" ingqamuzana nge izixhumanisi (1,1) ilotshwe:
1 uma i-agumenti kuyinto omuhle;
0 uma i-agumenti zero;
1, uma i-agumenti alivumi.
Ukudalwa Macro esinjalo "Excel" iqala ngendlela esezingeni, ngokusebenzisa izinkinobho "hot" Alt futhi F11. Ngaphezu kwalokho ebhaliwe ikhodi elandelayo:
Uhlelo Sub ()
x = Amaseli (1 1) .Value (Lo myalo esabela ukubaluleka x izixhumanisi okuqukethwe cell (1 1))
Uma Amaseli x> 0 Khona (1 1) .Value = 1
Uma Amaseli x = 0 Khona (1 1) .Value = 0
Uma x <0 Khona Amaseli (1 1) .Value = -1
Ukuphela Sub.
It uhlala ukugijima Macro bese uthole "Excel" Inani wazifunela agumenti.
VBA imisebenzi
Njengoba kungenzeka uye waphawula, ku uhlelo lwesipredishithi isicelo edume kunazo Microsoft akunzima kakhulu. Ikakhulukazi uma ufunde indlela yokusebenzisa VBA imisebenzi. Sekukonke, lolu limi izinhlelo badalelwa ngokubhala izicelo e "Excel" futhi iZwi, abangaba ngu-160 imisebenzi. Abakwazi ahlukaniswe abe amaqembu amaningi amakhulu. Ziyakwazi:
- imisebenzi zezibalo. Kokuzisebenzisa agumenti yenani cosine etholwe, logarithm engokwemvelo, ngakho yonke ingxenye.
- imisebenzi yezezimali. Ngenxa ukutholakala kanye nokusetshenziswa yabo izinhlelo Excel, ungathola amathuluzi amahle accounting nemizana zezimali.
- Array processing nemisebenzi. Lezi zihlanganisa Array, IsArray; LBound; UBound.
- VBA Excel ukusebenza umugqa. Lena iqembu kahle esikhulu. Kuhlanganisa, isibonelo, Space umsebenzi ukudala yezinhlamvu ngenombolo kwezikhala elilingana agumenti inamba noma ASC Ukudluliselwa izimpawu ANSI ikhodi. Bonke bangabangasokile esetshenziswa kabanzi futhi zikuvumela ukusebenza ngogubhu ku "Excel" ukudala izicelo lula kakhulu ukusebenza nalaba amatafula.
- Idatha uhlobo lokuguqulwa imisebenzi. Ngokwesibonelo, CVar ibuyisela Expression agumenti, ukuguqulela yona Okuhlukile uhlobo idatha.
- Usuku imisebenzi. Bona kakhulu ukwandisa izici esezingeni "Excel". Ngakho, WeekdayName umsebenzi ubuyisela igama (ngokugcwele noma kancane) kosuku lwesonto ngenombolo yayo. ewusizo nakakhulu Isikhathi. Unika isibalo imizuzwana ukuthi selokhu kwanikezelwa phakathi kwamabili kuya isikhashana esithile sosuku.
- Umsebenzi ukuguqula i-agumenti lenombolo nenombolo izinhlelo ezahlukene. Ngokwesibonelo, u-October okukhiphayo nokulinganiswa octal yenomboro.
- ukufomatha imisebenzi. Into ebaluleke kakhulu sazo Ifomethi. It ibuyisa Okuhlukile ngamazwi aveza ihlelwe ngokuvumelana ziyalezo encazelweni ifomethi.
- nokunjalo.
Ucwaningo we izakhiwo kwenta lemisebenti siphindze isicelo sabo ngeke kakhulu ukwandisa kwenkululeko "Excel".
ISIBONELO 5
Ake sizame ukuxazulula izinkinga eziyinkimbinkimbi. Ngokwesibonelo:
Dan iphepha document ezingeni langempela izindleko kombiko ibhizinisi. Idinga:
- ukuthuthukisa nesifanekiso salo ingxenye ngesipredishithi "Excel";
- ukwenza uhlelo VBA ukuthi azocela idatha yoqobo ukugcwalisa ke, ukuze sifeze izibalo ezidingekayo futhi ugcwalise isifanekiso yeseli okuhambisanayo.
Cabangela esinye izixazululo ezilandelayo.
Ukudala ithempulethi
Zonke izenzo ukwenziwa ishidi ejwayelekile Excel. amaseli Mahhala agodliwe for kokufaka idatha kwi ngenyanga, ngonyaka, izinkampani isihloko-umthengi, inani izindleko, izinga labo inzuzo. Njengoba inani izinkampani (izinkampani), maqondana nalapho umbiko awurekhodiwe, amaseli ukwenza ngesisekelo amagugu negama lochwepheshe hhayi agodliwe kusengaphambili. Iphepha yabelwe igama elisha. Ngokwesibonelo, "Օ Umbiko".
eziguquguqukayo
Ukubhala uhlelo ugcwalise ngokuzenzakalelayo isifanekiso, khetha notation. Bayoba isetshenziselwe eziguquguqukayo:
- NN- inani irowu samanje etafuleni;
- TP TF - kuhlelwe futhi inzuzo langempela;
- SF kanye SP - langempela futhi lohleliwe izindleko Imininingwane;
- IP UMA - kuhlelwe futhi Izindleko zangempela ezingeni.
Thina selisho by izinhlamvu ezifanayo, kodwa nge "Isiqalo» Itog ekuqongeleleni ingqikithi ukuthi ikholomu. Ngokwesibonelo, ItogTP - Ngokuphathelene kukholamu unelungelo, ngokuthi "imali okuhleliwe."
Isixazululo yenkinga usebenzisa VBA izinhlelo
Ukusebenzisa le notation, sithola ifomula ukuze ukuhluka. Uma ufuna ukufeza ukubala e% esinalo (F - P) / P * 100, futhi inani - (F - P).
Imiphumela yalezi izibalo kungaba engcono nje ukwenza "Excel"-spreadsheet kumaseli ezifanele.
Ukuze umphumela futhi Izibikezelo eqinisweni etholwe amafomula ItogP = ItogP + P futhi ItogF = ItogF + F.
Ukuze ngeziphambeko zisebenzisa = (ItogF - ItogP) / ItogP * 100 uma ukubala Kwenziwa njengoba amaphesenti, futhi uma inani eliphelele - (ItogF - ItogP).
Imiphumela waphinda ezilotshwe amaseli ezifanele, ngakho asikho isidingo yokunika ukuba eziguquguqukayo.
Ngaphambi kokuqala ukwakha uhlelo ofuna ugcine ibhuku lokusebenza, isibonelo, ngaphansi kwegama "Otchet1.xls".
Cindezela "Dala itafula yokubika," kumelwe cindezela ngemva kokufaka ulwazi unhlokweni isikhathi 1 kuphela. Kufanele wazi futhi ezinye izimiso. Ikakhulu, i-inkinobho "Engeza umugqa" Zulu ngemva kokufaka kuthebula yamanani ngomsebenzi ngamunye isikhathi ngasinye. Ngemuva kokufaka yonke idatha edingekayo ukuze cindezela inkinobho ka- "Qedile", bese ushintshele ku "Excel" ewindini.
Manje ngoba sewuyazi ukuthi indlela yokuxazulula inkinga i-Excel ne-macros. Ikhono ukusebenzisa VBA Excel (bheka izinhlelo isampula. Ngenhla) futhi kungase kudingeke ukuba asebenze imvelo ethandwa kakhulu okwamanje, umhleli umbhalo "uLizwi." Ikakhulukazi, uqopha, njengoba kuboniswe ekuqaleni kwalesi sihloko, noma ngokubhalela ikhodi ukudala izinkinobho imenyu lapho eziningi imisebenzi umbhalo kungenziwa ngokucindezela izinkinobho on duty noma ngokusebenzisa "Buka" isithonjana futhi "Amamakhro" ithebhu.
Similar articles
Trending Now