Okuqukethwe
I-Excel inamandla, kepha ngasikhathi sinye akuvamile ukusetshenziswa, ikhono lokudala ukulandelana kwezenzo okuzenzakalelayo usebenzisa ama-macros. I-macro iyindlela ekahle yokuphuma uma ubhekene nohlobo olufanayo lomsebenzi ophindwa kaningi. Isibonelo, ukucutshungulwa kwedatha noma ukufometha kwedokhumenti ngokuya kwesifanekiso esisezingeni. Kulokhu, awudingi ulwazi lwezilimi zokuhlela.
Ingabe usuvele unelukuluku lokwazi ukuthi iyini i-macro nokuthi isebenza kanjani? Bese uqhubeka ngesibindi - khona-ke sizokwenza isinyathelo ngesinyathelo yonke inqubo yokwakha i-macro nawe.
Iyini iMacro?
I-macro ku-Microsoft Office (yebo, lokhu kusebenza kusebenza ngendlela efanayo ezinhlelweni eziningi zephakheji le-Microsoft Office) ikhodi yohlelo ngolimi lokuhlela. I-Visual Basic yezinhlelo zokusebenza (VBA) egcinwe ngaphakathi kwedokhumenti. Ukwenza kucace kakhudlwana, idokhumenti ye-Microsoft Office ingafaniswa nekhasi le-HTML, bese i-macro iyi-analogue ye-Javascript. Lokho i-Javascript engakwenza ngedatha ye-HTML ekhasini lewebhu kufana kakhulu nalokho okungenziwa i-macro ngedatha kudokhumenti ye-Microsoft Office.
Amamakhro angenza cishe noma yini oyifunayo kudokhumenti. Nazi ezinye zazo (ingxenye encane kakhulu):
- Sebenzisa izitayela nokufometha.
- Yenza imisebenzi ehlukahlukene ngedatha yezinombolo neyombhalo.
- Sebenzisa imithombo yedatha yangaphandle (amafayela esizindalwazi, imibhalo yombhalo, njll.)
- Dala idokhumenti entsha.
- Yenza konke okungenhla kunoma iyiphi inhlanganisela.
Ukudala i-macro - isibonelo esisebenzayo
Isibonelo, ake sithathe ifayela elivame kakhulu CSV. Leli yithebula le-10×20 elilula eligcwele izinombolo ukusuka ku-0 kuye ku-100 elinezihloko zamakholomu nemigqa. Umsebenzi wethu uwukuguqula le sethi yedatha ibe ithebula elifomethiwe ngendlela efanele futhi sikhiqize amanani kumugqa ngamunye.
Njengoba sekushiwo, i-macro ikhodi ebhalwe ngolimi lokuhlela lwe-VBA. Kodwa ku-Excel, ungakha uhlelo ngaphandle kokubhala umugqa wekhodi, esizokwenza khona manje.
Ukwakha imakhro, vula Buka (Uhlobo) > amamakhro (Makhro) > Qopha iMacro (Ukuqoshwa okuningi…)
Nikeza imakhro yakho igama (akukho zikhala) bese uchofoza OK.
Kusukela kulo mzuzu, ZONKE izenzo zakho ngedokhumenti ziyarekhodwa: izinguquko kumaseli, ukuskrola etafuleni, ngisho nokushintsha usayizi wewindi.
I-Excel ikhombisa ukuthi imodi yokuqopha enkulu inikwe amandla ezindaweni ezimbili. Okokuqala, kumenyu amamakhro (Amakhrosi) – esikhundleni sentambo Qopha iMacro (Irekhoda i-macro…) umugqa uvele Misa Ukuqopha (Yeka ukuqopha).
Okwesibili, ekhoneni elingezansi kwesokunxele lewindi le-Excel. Isithonjana Misa (isikwele esincane) sibonisa ukuthi imodi yokurekhoda enkulu ivuliwe. Ukuchofoza kuyo kuzomisa ukurekhoda. Ngokuphambene, uma imodi yokurekhoda ingavunyelwe, kunesithonjana sokuvumela ukuqoshwa okukhulu kule ndawo. Ukuchofoza kuyo kuzonikeza umphumela ofanayo nokuvula ukurekhoda ngemenyu.
Manje njengoba imodi yokurekhoda enkulu isinikwe amandla, ake sifinyelele emsebenzini wethu. Okokuqala, ake sengeze izihloko zedatha yesifinyezo.
Okulandelayo, faka amafomula kumaseli ngokuhambisana namagama ezihloko (izinhlobonhlobo zamafomula esiNgisi nezinguqulo ze-Excel zinikezwa, amakheli eseli ahlala ayizinhlamvu zesiLatini nezinombolo):
- =ISUM(B2:K2) or =ISUM(B2:K2)
- =ISILINGANISO(B2:K2) or =СРЗНАЧ(B2:K2)
- =MIN(B2:K2) or =MIN(B2:K2)
- =UMAX(B2:K2) or =UMAX(B2:K2)
- =MEDIAN(B2:K2) or =MEDIAN(B2:K2)
Manje khetha amaseli anamafomula bese uwakopishela kuyo yonke imigqa yethebula lethu ngokuhudula isibambo sokugcwalisa okuzenzakalelayo.
Ngemva kokuqeda lesi sinyathelo, umugqa ngamunye kufanele ube nengqikithi ehambisanayo.
Okulandelayo, sizofingqa imiphumela yethebula lonke, kulokhu senza imisebenzi embalwa yezibalo:
Ngokulandelana:
- =ISUM(L2:L21) or =ISUM(L2:L21)
- =ISILINGANISO(B2:K21) or =СРЗНАЧ(B2:K21) - ukubala leli nani, kuyadingeka ukuthatha idatha yokuqala yetafula. Uma uthatha isilinganiso sezilinganiso zemigqa ngayinye, umphumela uzohluka.
- =MIN(N2:N21) or =MIN(N2:N21)
- =UMAX(O2:O21) or =UMAX(O2:O21)
- =MEDIAN(B2:K21) or =MEDIAN(B2:K21) - sicabangela ukusebenzisa idatha yokuqala yethebula, ngesizathu esiboniswe ngenhla.
Manje njengoba sesiqedile ngezibalo, ake senze ukufometha. Okokuqala, ake sisethe ifomethi efanayo yokubonisa idatha yawo wonke amaseli. Khetha wonke amaseli eshidini, ukwenza lokhu, sebenzisa isinqamuleli sekhibhodi Ctrl + Anoma chofoza isithonjana Khetha konke, etholakala empambanweni yomugqa nezihloko zekholomu. Bese uchofoza Isitayela Sokhefana (Ifomethi enqunyiwe) ithebhu Ikhaya (Ekhaya).
Okulandelayo, shintsha ukubukeka kwekholomu nezihloko zomugqa:
- Isitayela sefonti egqamile.
- Ukuqondanisa kwamaphakathi.
- Gcwalisa umbala.
Futhi ekugcineni, ake simise ifomethi yamathothali.
Nansi indlela okufanele ibukeke ngayo ekugcineni:
Uma konke kukufanele, yeka ukuqopha imakhro.
Siyakuhalalisela! Usanda kurekhoda i-macro yakho yokuqala ku-Excel ngokwakho.
Ukusebenzisa i-macro ekhiqiziwe, sidinga ukugcina idokhumenti ye-Excel ngefomethi esekela ama-macros. Okokuqala, sidinga ukususa yonke idatha kuthebula esilidalile, okungukuthi silenze isifanekiso esingenalutho. Iqiniso liwukuthi ngokuzayo, ngokusebenza nalesi sifanekiso, sizongenisa idatha yakamuva nefanele kuso.
Ukuze usule wonke amaseli kudatha, chofoza kwesokudla kusithonjana Khetha konke, etholakala empambanweni yomugqa nezihloko zekholomu, futhi kumenyu yokuqukethwe, khetha Susa (Susa).
Manje ishidi lethu selisulwe ngokuphelele kuyo yonke idatha, kuyilapho i-macro isala irekhodiwe. Sidinga ukulondoloza ibhuku lokusebenza njengesifanekiso se-Excel esinikwe amandla ngokuningi esinesandiso I-XLTM.
Iphuzu elibalulekile! Uma ulondoloza ifayela ngesandiso XLTX, khona-ke i-macro ngeke isebenze kuyo. Ngendlela, ungagcina incwadi yokusebenzela njengesifanekiso se-Excel 97-2003, esinefomethi I-XLT, iphinde isekele ama-macros.
Uma isifanekiso silondoloziwe, ungavala ngokuphephile i-Excel.
Isebenzisa iMacro ku-Excel
Ngaphambi kokudalula wonke amathuba we-macro oyidalile, ngicabanga ukuthi kulungile ukunaka amaphuzu ambalwa abalulekile mayelana nama-macros jikelele:
- Amamakhro angaba yingozi.
- Funda isigaba esandulele futhi.
Ikhodi ye-VBA inamandla kakhulu. Ikakhulukazi, ingenza imisebenzi kumafayela angaphandle kwedokhumenti yamanje. Isibonelo, i-macro ingasusa noma iguqule noma yimaphi amafayela kufolda Amadokhumenti ami. Ngalesi sizathu, sebenzisa kuphela futhi uvumele ama-macros avela emithonjeni oyithembayo.
Ukuze usebenzise i-macro yethu yokufometha idatha, vula ifayela lesifanekiso esilidalile engxenyeni yokuqala yalesi sifundo. Uma unezilungiselelo zokuphepha ezijwayelekile, lapho-ke uvula ifayela, kuzovela isexwayiso ngenhla kwetafula ukuthi ama-macros akhutshaziwe, kanye nenkinobho yokuwavumela. Njengoba sizenzele isifanekiso ngokwethu futhi siyazethemba, sicindezela inkinobho Nika amandla okuqukethwe (Faka nokuqukethwe).
Isinyathelo esilandelayo ukungenisa idathasethi yakamuva ebuyekeziwe efayelini CSV (ngokusekelwe kufayela elinjalo, sidale i-macro yethu).
Uma ungenisa idatha esuka efayeleni le-CSV, i-Excel ingase ikucele ukuthi uhlele ezinye izilungiselelo ukuze udlulisele ngokulungile idatha kuthebula.
Uma ukungenisa sekuqediwe, hamba kumenyu amamakhro (Macros) ithebhu Buka (Buka) bese ukhetha umyalo Buka amamakhro (Makhro).
Ebhokisini lengxoxo elivulayo, sizobona umugqa onegama le-macro yethu IfomethiData. Ikhethe bese uchofoza Qalisa (Kwenza).
Uma imakhro iqala ukusebenza, uzobona ikhesa yetafula igxuma isuka kuseli iye kuseli. Ngemuva kwemizuzwana embalwa, imisebenzi efanayo izokwenziwa ngedatha njengalapho kuqoshwa i-macro. Uma yonke into isilungile, ithebula kufanele libukeke lifana nelokuqala esilifomethe ngesandla, kuphela ngedatha ehlukile kumaseli.
Ake sibheke ngaphansi kwe-hood: Isebenza kanjani i-macro?
Njengoba kushiwo izikhathi ezingaphezu kwesisodwa, i-macro ikhodi yohlelo ngolimi lokuhlela. I-Visual Basic yezinhlelo zokusebenza (VBA). Uma uvula imodi yokurekhoda enkulu, i-Excel empeleni irekhoda zonke izenzo ozenzayo ngendlela yemiyalelo ye-VBA. Kalula nje, i-Excel ikubhalela ikhodi.
Ukuze ubone le khodi yohlelo, udinga kumenyu amamakhro (Macros) ithebhu Buka (buka) chofoza Buka amamakhro (Amamakhro) futhi ebhokisini lengxoxo elivulayo, chofoza Hlela (Shintsha).
Iwindi liyavuleka. I-Visual Basic yezinhlelo zokusebenza, lapho sizobona khona ikhodi yohlelo ye-macro esiyiqophe. Yebo, uqonde kahle, lapha ungashintsha le khodi futhi udale i-macro entsha. Izenzo esizenze ngethebula kulesi sifundo zingarekhodwa kusetshenziswa ukurekhoda okuzenzakalelayo kwe-macro ku-Excel. Kodwa amamakhro ayinkimbinkimbi kakhulu, anokulandelana okushunwe kahle kanye nengqondo yesenzo, adinga ukuhlelwa okwenziwa ngesandla.
Ake sengeze esinye isinyathelo emsebenzini wethu...
Cabanga ukuthi ifayela lethu lokuqala ledatha idatha.csv idalwa ngokuzenzakalelayo ngenye inqubo futhi ihlale igcinwe kudiski endaweni efanayo. Ngokwesibonelo, C:Datadata.csv - indlela eya kufayela enedatha ebuyekeziwe. Inqubo yokuvula leli fayela kanye nokungenisa idatha kuyo ingaqoshwa futhi ku-macro:
- Vula ifayela lesifanekiso lapho sigcine khona i-macro − IfomethiData.
- Dala imakhro entsha enegama LoadData.
- Ngenkathi uqopha imakhro LoadData ngenisa idatha kufayela idatha.csv – njengoba senzile engxenyeni edlule yesifundo.
- Uma ukungenisa sekuqediwe, yeka ukurekhoda imakhro.
- Susa yonke idatha kumaseli.
- Londoloza ifayela njengesifanekiso se-Excel esinikwe amandla amakhulu (isandiso se-XLTM).
Ngakho-ke, ngokusebenzisa lesi sifanekiso, uthola ukufinyelela kumamakhro amabili - eyodwa ilayisha idatha, enye iyayifometha.
Uma ufuna ukungena ezinhlelweni, ungahlanganisa izenzo zalawa macros amabili zibe yinye - ngokumane ukopishe ikhodi ku. LoadData ekuqaleni kwekhodi IfomethiData.