Microsoft Excel macros muaj peev xwm cuam tshuam txog kev ua haujlwm nrog cov ntaub ntawv hauv cov ntawv tshaj xo no. Qhov no yog ua tiav los ntawm kev ua rov ua qhov rov ua tau sau tseg hauv cov cai tshwj xeeb. Cia peb pom yuav tsim cov macros hauv Excel, thiab yuav kho lawv li cas.
Txoj kev teev cia Macro
Daim ntawv macro tuaj yeem sau ua ob txoj kev:
- tsis siv neeg;
- los ntawm txhais tes.
Siv thawj qhov kev xaiv, koj tsuas yog sau qee cov yeeb yam hauv Microsoft Excel qhov kev pab uas koj tab tom ua. Tom qab ntawd, koj tuaj yeem ua cov ntawv no. Txoj kev no yog qhov yooj yim heev, thiab tsis tas yuav muaj kev paub txog cov cai, tab sis nws daim ntawv thov hauv kev siv yog tsawg.
Kev tuav macro cov ntaubntawv povthawj, ntawm qhov tsis sib xws, yuav tsum muaj kev paub sau ntawv, txij li cov lej sau rau ntawm tus kheej ntawm cov keyboard. Tab sis, kom raug sau tus lej nyob rau hauv no txoj kev tuaj yeem hloov kho kom sai ntawm kev coj ua.
Tsis Siv Neeg Macro Teev
Ua ntej koj tuaj yeem pib tsis siv neeg macro sau cia, koj yuav tsum muaj peev xwm kom loj dua hauv Microsoft Excel.
Tom ntej no, mus rau "Developer" tab. Nyem rau ntawm "Macro Cov Ntaub Ntawv" khawm, uas yog nyob ntawm lub pob xov ntawm "Code" cov khoom siv thaiv.
Lub macro sau qhov rai teeb qhib. Ntawm no koj tuaj yeem hais qhia lub npe macro yog lub neej ntawd ib txwm tsis haum koj. Qhov loj tshaj plaws yog tias lub npe pib nrog tus ntawv, thiab tsis nrog tus lej. Tsis tas li, cov npe yuav tsum tsis txhob muaj chaw seem. Peb tso lub neej ntawd lub npe - "Macro1".
Tam sim ntawd, yog qhov xav tau, koj tuaj yeem teeb tsa tus lej keyboard, thaum nias, lub macro yuav raug tsim tawm. Thawj tus yuam sij yuav tsum yog tus yuam sij Ctrl, thiab tus neeg siv lub pob thib ob ntawm nws tus kheej. Piv txwv, peb, ua piv txwv, teeb tsa tus yuam sij M.
Tom ntej no, koj yuav tsum txiav txim siab qhov twg macro yuav cia. Los ntawm lub neej ntawd, nws yuav muab khaws cia rau hauv tib phau ntawv (cov ntaub ntawv), tab sis yog tias koj xav tau, koj tuaj yeem tsim qhov chaw cia rau hauv phau ntawv tshiab, lossis hauv phau ntawv macros cais. Peb yuav tawm tus nqi qub.
Hauv thaj teb kawg ntawm kev teeb tsa macro, koj tuaj yeem tso ib qho lus piav qhia ntawm daim macro uas haum rau hauv cov ntsiab lus. Tab sis, qhov no tsis tsim nyog.
Thaum txhua qhov kev teeb tsa tiav, nyem rau khawm "OK".
Tom qab ntawd, txhua yam koj ua nyob rau hauv Excel phau ntawv no (cov ntaub ntawv) yuav sau cia hauv macro kom txog thaum koj tus kheej nres tsis sau cia.
Piv txwv, peb sau qhov kev ua lej ua kom yooj yim: ntxiv cov ntsiab lus ntawm peb lub hlwb (= C4 + C5 + C6).
Tom qab ntawv, nyem rau ntawm lub pob ntawv "Nres Sau Npe". Cov khawm no tau hloov los ntawm "Macro Cov Ntaub Ntawv" khawm, tom qab sau cia tau pib.
Macro khiav
Txhawm rau tshuaj xyuas seb cov ntawv sau loj ua haujlwm li cas, nyem khawm "Macros" hauv tib qho "Code" toolbar, lossis nias Alt + F8.
Tom qab ntawd, lub qhov rais qhib nrog cov npe teev tseg macros. Peb tab tom nrhiav macro uas peb tau kaw tseg, xaiv nws, thiab nias rau ntawm "Run" khawm.
Koj tuaj yeem ua kom yooj yim dua, thiab tsis txawm hu rau lub qhov rais macro xaiv. Peb nco ntsoov tias peb tau sau ua ke ntawm "cov yuam sij kub" rau kev ceev macro invocation. Hauv peb li, qhov no yog Ctrl + M. Peb ntaus ua ke ntawm cov keyboard, tom qab ntawd macro pib.
Raws li koj tuaj yeem pom, macro ua raws nraim txhua yam kev ua haujlwm uas tau sau tseg ua ntej.
Macro ua tiav
Txhawm rau hloov cov ntawv loj, nyem rau ntawm "Macros" khawm ntxiv. Nyob rau hauv lub qhov rai uas qhib, xaiv cov macro xav tau, thiab nyem rau khawm "Hloov".
Qhib Microsoft Visual Basic (VBE) - ib puag ncig qhov hloov macros.
Kev kaw ntawm txhua macro pib nrog Sub hais kom ua, thiab xaus nrog End Sub cov lus txib. Sai li sai tau tom qab hais kom ua, lub npe macro yog qhia. Tus neeg teb xov tooj "Ntau Yam (" ... "). Xaiv xaiv xaiv ntawm tes. Piv txwv li, nrog cov lus txib "Khwv (" C4 "). Xaiv," cell C4 yog xaiv. Tus neeg teb xov tooj "ActiveCell.FormulaR1C1" yog siv los sau cov yeeb yam hauv cov qauv, thiab rau lwm cov kev suav.
Cia peb sim hloov macro me ntsis. Ua li no, ntxiv cov lus pom rau macro:
Cov Khwv ("C3"). Xaiv
ActiveCell.FormulaR1C1 = "11"
Cov lus qhia "ActiveCell.FormulaR1C1 =" = R [-3] C + R [-2] C + R [-1] C "yog hloov los ntawm" ActiveCell.FormulaR1C1 = "= R [-4] C + R [-3 ] C + R [-2] C + R [-1] C "."
Peb kaw cov editor, thiab khiav loj heev, yog zaum kawg. Raws li koj tuaj yeem pom, vim yog hloov pauv peb tau qhia, lwm cov ntaub ntawv xov tooj ntawm tes tau ntxiv. Nws tseem tau suav nrog qhov kev ntsuas ntawm tag nrho cov nyiaj.
Yog tias macro loj dhau lawm, nws tuaj yeem siv sijhawm ntev kom muaj txim tuag. Tab sis, los ntawm kev ua ib phau ntawv hloov pauv ntawm txoj cai, peb tuaj yeem ceev txoj kev. Ntxiv rau qhov hais kom ua "Application.ScreenUpdating = cuav". Nws yuav txuag tau lub zog suav, uas txhais tau hais tias ua haujlwm nrawm. Qhov no yog ua tiav los ntawm kev rov qab kho dua ntawm lub vijtsam thaum lub sijhawm ua haujlwm. Kom rov pib kho dua tshiab tom qab kev siv macro, thaum kawg peb sau cov lus txib "Application.ScreenUpdating = Tseeb"
Ntxiv lo lus txib "Application.Calculation = xlCalculationManual" thaum pib ntawm tus lej, thiab tom kawg ntawm tus lej peb ntxiv "Application.Calculation = xlCalculationAutomatic". Yog li, thaum pib loj heev, peb tua tawm qhov rov ua haujlwm tsis siv neeg rov qab los ntawm qhov tshwm sim tom qab txhua tus hloov cell, thiab tom kawg macro, tig nws. Yog li, Excel yuav suav qhov tshwm sim ib zaug xwb, thiab yuav tsis nco qab nws tas li, uas yuav txuag lub sijhawm.
Sau cov cim macro los ntawm kos
Cov neeg siv siab tsis tuaj yeem tsuas yog kho kom raug thiab ua kom zoo tshaj plaws sau tseg macros, tab sis kuj sau macro code los ntawm kos. Txhawm rau pib qhov no, koj yuav tsum nyem lub pob "Visual Basic", uas nyob rau ntawm qhov pib ntawm tus tsim tawm pob tw.
Tom qab ntawd, tus paub qhov rais VBE editor qhib.
Tus programmer sau macro code nws muaj manually.
Raws li koj tuaj yeem pom, macros hauv Microsoft Excel tuaj yeem txo qhov kev tua ntawm cov txheej txheem niaj hnub thiab niaj hnub zoo ib yam. Tab sis, feem ntau, macros uas nws sau nws tus kheej es tsis cia sau cov kev ua yog qhov zoo dua rau qhov no. Ntxiv rau, macro code tuaj yeem kho kom zoo los ntawm VBE tus editor kom ceev lub luag haujlwm.
