Microsoft Excel macros tuaj yeem ua kom cov hauj lwm nrog cov ntaub ntawv hauv cov ntaub ntawv xov xwm no. Qhov no yog tiav los ntawm automating repetitive ua nyob rau hauv ib txoj cai tshwj xeeb. Cia wb mus saib yuav ua li cas los tsim cov macros nyob rau hauv Excel, thiab ua li cas lawv thiaj raug kho.
Txoj kev rau cov ntaub ntawv Macros
Macros sau tau ua ob txoj hauv kev:
- txais;
- manually.
Siv qhov kev xaiv thawj zaug, koj tsuas sau cov kev nqis tes ua hauv Microsoft Excel uas koj ua tau zoo ntawm qhov taw tes rau hauv lub sijhawm. Tom qab ntawd, koj tuaj yeem ua cov ntaub ntawv no. Txoj kev no yooj yim heev, thiab tsis tas yuav tsum paub txog ntawm txoj cai, tab sis nws cov tswv yim zoo siv tsis yog.
Kev ceev cov ntaubntawv povthawj siv ntawm cov tshuab, ntawm qhov tsis yog, yuav tsum muaj kev paub txog programming, vim hais tias cov cai yog ntaus los ntawm cov keyboard. Tab sis, muab cov cai sau rau hauv txoj kev no tuaj yeem ua raws li kev ua tiav cov txheej txheem.
Tsis zoo no feem Macro Recording
Ua ntej koj pib cov ntaubntawv povthawj siv tsis tiav, koj yuav tau pab kom tau cov macros hauv Microsoft Excel.
Tom ntej, mus rau ntawm tab "Developer". Ndliv lub khawm "Macro Daim Ntawv", uas yog nyob rau ntawm daim kab xev hauv "Tool" cuab yeej thaiv.
Lub qhov rais qhib qhov tsos tawm qhib qhov rai. Ntawm no koj tuaj yeem qhia meej txog txhua lub npe macro yog lub neej ntawd tsis haum koj. Qhov loj tshaj plaws yog hais tias lub npe pib nrog ib tsab ntawv, tsis yog ib tug xov tooj. Tsis tas li, yuav tsum tsis muaj chaw nyob hauv lub npe. Peb sab laug lub neej ntawd - "Macro1".
Ntawm no, yog tias koj xav tau, koj tuaj yeem tsim qhov tseem ceeb shortcut, thaum clicked, lub macro yuav tsum launched. Thawj qhov tseem ceeb yuav tsum yog Ctrl qhov tseem ceeb, thiab tus yuam sij thib ob yog tus neeg siv nws tus kheej. Piv txwv li, peb, ua piv txwv, teeb tsa lub ntsiab ntawm M.
Tom ntej no, koj yuav tsum txiav txim siab seb qhov twg yuav khaws tau qhov twg. Los ntawm kev ua neej, nws yuav raug khaws cia hauv tib phau ntawv (cov ntaub ntawv), tab sis yog tias koj xav tau, koj tuaj yeem teev cov khoom cia rau hauv ib phau ntawv tshiab, los yog hauv ib phau ntawv ntawm macros. Peb yuav tso cov nqi pheej yig.
Nyob rau ntawm qhov qis tshaj plaws ntawm qhov chaw macro, koj tuaj yeem tawm hauv cov ntsiab lus-qhov kev piav qhia ntawm qhov kev lag luam no. Tab sis nws tsis tsim nyog los ua qhov no.
Thaum txhua qhov chaw ua tiav, nias qhov "OK" khawm.
Tom qab ntawd, tag nrho koj cov yeeb yam hauv no Excel phau ntawv (cov ntaub ntawv) yuav tsum muab kaw rau hauv lub macro kom txog thaum koj nres qhov sau koj tus kheej.
Piv txwv li, peb sau qhov kev ua zauv nyuaj: qhov sib ntxiv ntawm cov ntsiab lus ntawm peb lub hlwb (= C4 + C5 + C6).
Tom qab ntawd, nyem rau ntawm "Stop recording" khawm. Cov pob no tau hloov dua siab tshiab los ntawm "Cov Ntaub Ntawv Macro" khawm, tom qab teev ntawv tau qhib.
Khiav Macro
Yuav kom tshawb xyuas li cas cov ntaub ntawv kaw tseg ua haujlwm, nias qhov Macros khawm tib lub thev naus caj npab, lossis nias khawm Alt + F8 ua ke.
Tom qab ntawd, lub qhov rais qhib nrog cov npe ntawm cov ntaub ntawv kaw tseg. Peb tab tom nrhiav ib qho lag luam uas peb kaw tseg, xaiv nws, thiab nias rau ntawm "Run" khawm.
Koj tuaj yeem ua tau yooj yim txawm tias, thiab tsis txawm hu rau qhov kev xaiv xaiv macro. Peb nco ntsoov hais tias peb sau ib qho kev sib txuas ntawm "kub tuav" rau kev hu xov ceev ceev. Nyob rau hauv peb cov ntaub ntawv, qhov no yog Ctrl + M. Peb sau no ua ke ntawm cov keyboard, tom qab uas lub macro sau.
Raws li koj tau pom, qhov macro tau ua tiav raws nraim tag nrho cov kev ua uas tau sau tseg ntxov.
Macro editing
Yuav kom hloov lub macro, rov qab nyem rau ntawm "Macros" khawm. Nyob rau hauv lub qhov rai uas tau qhib, xaiv qhov yam xav tau, thiab nias rau ntawm "Kho kom raug" khawm.
Visual Basic Microsoft Visual Basic (VBE) opens - qhov chaw nyob qhov twg yog qhov kev hloov ua tiav.
Cov ntaubntawv povthawj siv ntawm txhua lub phom pib nrog cov lus txib Sub, thiab xaus nrog qhov kawg Sub hais kom ua. Tam sim ntawd tom qab tus hais kom ua Sub, lub npe macro yog teev. Tus neeg teb xov tooj "Range (" ... ") Xaiv" qhia qhov kev xaiv ntawm lub xov tooj. Piv txwv, thaum hais kom ua "Range (" C4 "). Xaiv" xaiv cell C4. Tus neeg teb xov tooj "ActiveCell.FormulaR1C1" yog siv los sau cov yeeb yam hauv cov qauv, thiab rau lwm cov lus teb.
Peb sim hloov lub macro me ntsis. Ua li no, peb ntxiv ib qho kev qhia rau lub macro:
Ntau ("C3"). Xaiv
ActiveCell.FormulaR1C1 = "11"
Qhov kev qhia "ActiveCell.FormulaR1C1 =" = R [-3] C + R [-2] C + R [-1] C "" hloov los ntawm "ActiveCell.FormulaR1C1 =" = R [-4] C + R [-3 ] C + R [-2] C + R [-1] C "".
Kaw cov editor, thiab khiav lub macro, ib yam li lub sijhawm dhau los. Raws li koj tau pom, vim yog cov kev hloov peb pib, lwm cov ntaub ntawv xovtooj ntawm xov tooj ntxiv. Nws kuj tau muaj nyob hauv kev xam cov nyiaj tag nrho.
Nyob rau hauv cov ntaub ntawv lub macro yog loj heev, nws tiav yuav ua rau ntau ntau lub sij hawm. Tab sis, los ntawm kev ua ib qho kev hloov ntawm txoj cai, peb tuaj yeem ua kom sai dua. Ntxiv qhov hais kom ua "Application.ScreenUpdating = Cuav". Nws yuav cia koj txuag xam zog, thiab yog li ua kom txoj haujlwm tau. Qhov no yog tiav los ntawm tsis kam hloov qhov kev ntsuam xyuas thaum ua cov txiaj ntsim ntawm kev ua haujlwm. Kom resume hloov tshiab tom qab khiav lub macro, qhov kawg ntawm nws sau cov lus txib "Application.ScreenUpdating = True"
Peb kuj ntxiv qhov "Command.Calculation = xlCalculationManual" thaum pib ntawm txoj cai, thiab qhov kawg ntawm qhov chaws peb ntxiv "Application.Calculation = xlCalculationAutomatic". Ntawm qhov no peb ua ntej lov tes taw qhov kev rho tawm ntawm qhov tshwm sim tom qab txhua qhov kev hloov ntawm cov hlwb, thiab tig nws kawg ntawm lub qhov kawg. Yog li, Excel yuav xam qhov tshwm sim tsuas yog ib zaug, thiab yuav tsis pheej rov kho nws, uas yuav txuag lub sij hawm.
Sau cov ntawv teev lus macro ntawm kos
Cov neeg siv cov cuab yeej tuaj yeem tsis tsuas yog kho kom zoo thiab optimize tseg cov ntaub ntawv, tab sis kuj sau cov ntawv teev lus macro ntawm kos. Yuav kom pib nrog qhov no, koj yuav tsum nias qhov "Visual Basic" khawm, uas nyob ntawm qhov pib ntawm tus tsim tawm lub xov.
Tom qab ntawd, qhib lub VBE editor qhov rais qhib.
Tus programmer sau cov macro code muaj manually.
Raws li koj tau pom, macros nyob rau hauv Microsoft Excel tau yeem kom ua tiav kev tua cov txheej txheem niaj hnub thiab kev sib txuam. Tab sis, feem ntau, cov kab lus uas nws sau los ntawm kev xuas tes los ntawm kev xuas tes ua tsis zoo rau qhov no. Ntxiv mus, macro code tau optimized los ntawm VBE editor kom ceev cov neeg ua hauj lwm tiav kev.
