Sau koj cov haujlwm ua ntej ntawm daim ntawv ua haujlwm yog tias koj tuaj yeem tsoo lawv thaum tav su. Nrog cov dej num nkag los hauv tsev lossis ua haujlwm, ntau tus tau nqa mus rau hnub tom ntej (lossis lub lim tiam lossis hli). Daim ntawv Excel no saib xyuas cov sijhawm ze thiab hloov ua haujlwm ua ntej raws li. Hauv 20 feeb cov kauj ruam hauv qab no muab txoj hauv kev zoo tshaj los tswj hwm koj li haujlwm ua ntej.
Cov kauj ruam
Kauj Ruam 1. Tsim "Tsev" lossis "Chaw Ua Haujlwm" tab
Qhib daim ntawv Excel tshiab. Txoj cai nyem rau ntawm "Sheet1" tab hauv qab thiab nyem rau Rename. Ntaus hauv "Tsev" lossis "Chaw Haujlwm".
Kauj Ruam 2. Hloov Npe Sheet2 ua "Template" thiab Sheet3 li "Cov Ntsiab Lus" los ntawm rov ua ntu 1
Kauj Ruam 3. Tsim Cov Lus Tseem Ceeb
Ntawm Cov Ntsiab Lus tab, sau kab kab A, B thiab C:
Kauj Ruam 4. Txhais lub npe "Qhov tseem ceeb"
Xaiv los ntawm Cell A2 rau C7. Nyem rau Insert Name Define
Txhais lub npe ua "Qhov tseem ceeb" thiab nyem OK
Kauj Ruam 5. Tsim Lub Rooj Sib Tham
Rov ua cov kauj ruam 3, 4 thiab 5 los tsim Lub Rooj Sib Tham hauv kab E, F, thiab G. Xaiv cov cell E2 rau G6 thiab muab lawv lub npe "Kev rau siab".
Kauj Ruam 6. Tsim cov lus Ceev
Rov ua cov kauj ruam 3, 4 thiab 5 los tsim Lub Rooj Ceev Xwm hauv kab I, J thiab K. Sau lawv lub npe "Ceev".
Kauj Ruam 7. Nkag mus rau kab lus hauv Tsev tab
Nyem rau ntawm Lub Tsev tab thiab ntxig cov npe hauv kab 1:
- A - Qhov tseem ceeb Ib tus qauv, 1 rau txoj haujlwm tseem ceeb tshaj plaws, tab sis tuaj yeem dhau 100
- B - Lub luag haujlwm Lub npe ntawm txoj haujlwm.
- C - Qhov tseem ceeb A, B, C, D, E, lossis F los ntawm lub rooj.
- D - Kev mob siab rau A 1-5, los ntawm Cov Lus Sib Tham.
- E - Ceev Xwm Ib tus qauv raws li Hnub Kawg.
- F - Hnub Kawg thaum txoj haujlwm yuav tsum ua tiav. Cov sijhawm teem sijhawm tsis nyuaj thiab nrawm. Ramp Up qhia koj thaum ntxov koj tuaj yeem pib ua haujlwm thiab Txuas ntxiv qhia koj pes tsawg hnub nws tuaj yeem swb. Kev txiav plaub hau tej zaum yuav muaj Ramp Up ntawm 5 thiab txuas ntxiv ntawm 4 - nws tsis muaj qhov nkag siab kom tau txiav plaub hau 2 lub lis piam ntxov thiab tib neeg yuav pom tias yog nws ntau dua 5 hnub lig.
- G - Ramp Up Hnub ua ntej hnub kawg koj tuaj yeem pib ua haujlwm.
- H - Extension Tsis siv neeg ncua ntawm Hnub Kawg
- Kuv - Hnub Sab Hnub Poob. Cov hnub ua ntej hnub tas sij hawm; tsis zoo yog hnub kawg tau dhau mus.
- J - Tiav Hnub Ua Haujlwm tiav tau ua tiav.
- K - Tawm tswv yim Cov ntsiab lus rau txoj haujlwm.
Kauj Ruam 8. Sau koj daim ntawv teev npe ntawm cov haujlwm
Nco tseg Ua Ntej Ua Ntej, Kev Ceev Xwm thiab Hnub Sab Hnub Poob yog tsis muaj dab tsi. Lawv yuav tau sau nrog cov qauv. Nov yog qhov piv txwv ntawm kev ua haujlwm hauv tsev.
Kauj Ruam 9. Nkag mus rau cov qauv rau Hnub Laus, Qhov Cuam Tshuam thiab Ua Ntej Ua Ntej
Cov qauv hauv qab no yog rau kab 2.
- Kuv (Hnub Laus) = F2-YOG (ISBLANK (J2), TODAY (), J2)
- E (Ceev) = YOG (I2> G2, 5, IF (I2> 0, 4, IF (I2 = 0, 3, IF (I2+H2> 0, 2, 1)))))
- A (Ua Ntej Ua Ntej) = VLOOKUP (C2, Qhov Tseem Ceeb, 2, FALSE) + VLOOKUP (D2, Kev rau siab, 2, FALSE) + VLOOKUP (E2, Xwm Ceev, 2, FALSE)
Kauj Ruam 10. Hloov hom ntawv rau Cell I2 rau Integer los ntawm txoj nyem rau ntawm tes, xaiv hom ntawv thiab ua nws Tus Zauv nrog 0 Zauv qhov chaw
Kauj Ruam 11. Luam cov qauv rau Ua Ntej Ua Ntej, Ceev thiab Hnub Taug Qab mus rau cov cell uas seem hauv txhua kab ntawv
Xaiv cell E2 thiab ntaus CTRL-C kev.
Xaiv cov cell E3 rau E10 thiab nyem CTRL-V ua. Rov ua dua los theej cell I2 rau cell I3 rau I10. Thaum kawg, rov ua dua los theej cov cell A2 rau cell A3 rau A10. Tsis quav ntsej qhov txawv qhov txiaj ntsig koj tau txais rau cov haujlwm tsis tau teev tseg.
Kauj Ruam 12. Txheeb kab ua ntej
Xaiv los ntawm cov hlwb A1 txog K, rau ntau kab raws li koj muaj cov ntaub ntawv. Tom qab ntawd nyem rau Cov Ntaub Ntawv Sort.
Kauj Ruam 13. Khaws koj daim ntawv nthuav qhia ua ntej, suav nrog hnub hloov kho
Kauj Ruam 14. Kos cov haujlwm tiav
Thaum koj ua tiav cov haujlwm, kos hnub tim hauv kab ntawv Ua tiav. Nco ntsoov CTRL- ;
(tswj qhov tseem ceeb thiab ib nrab txoj hnyuv) tam sim nkag mus rau hnub tam sim no.
Kauj Ruam 15. Saib Ua Ntej Ua Ntej hloov pauv txhua hnub
Nov yog qhov muaj feem thib ob peb hnub. Thaum Lub Xya Hli 13 txhua txoj haujlwm yog ua ntej lub Ramp Up sijhawm, yog li muaj cov lej loj. Thaum Lub Xya Hli 20, qhov muaj feem thib siab dua (tus lej me) tshwm rau plaub yam haujlwm, suav nrog Moob Lawn uas tau tsoo nws Ncua sijhawm hnub tim Hnub tim 21, qhov muaj feem thib yog siab dua vim tias peb nyob hauv Txuas ntxiv lub sijhawm thiab thaum Lub Xya Hli 23rd nws tseem siab dua vim tias nws dhau ntawm Txuas ntxiv lub sij hawm. Them nqi kuj tseem dhau los ntawm kev nce qib ntawm 23 thiab 25.
Lub tswv yim
- Xav tias dawb ntxiv ntau kab - leej twg tau ua txoj haujlwm, qeb, thiab lwm yam.
- Muab cov dej num loj faib ua qhov me.
- Txheeb cov npe ua haujlwm txhua hnub yog tias tsim nyog.
- Khaws cais Excel cov ntaub ntawv rau tsev neeg/tsev thiab ua haujlwm.
- = YOG (WEEKDAY (TODAY (), 2)> 5, TODAY ()-(WEEKDAY (TODAY (), 2) -5) +7, TODAY ()-(WEEKDAY (TODAY (), 2) -5))
- Siv AutoFilter los xaiv cov haujlwm dhau los (Qhov xwm txheej = 1) lossis cov haujlwm tseem ceeb heev (Qhov tseem ceeb = "A")
- Nyeem cov kab lus hauv qab no ntawm kev ua ntej thiab kev tswj hwm lub sijhawm.
- Luam cov haujlwm rov ua dua rau Cov Qauv tab, yog li koj tuaj yeem nrhiav thiab rov ua lawv rov qab tau yooj yim.
- Xav tias hloov pauv cov ntsiab lus muab rau txhua feem ntawm qhov muaj feem thib.
- Ib txoj hauv kev kom rov ua haujlwm tas li hloov kho tas li yog nkag mus rau hauv kab ntawv hnub tim: (Qhov piv txwv no siv hnub Friday, uas yog "5" ntawm no.)
- Ntxiv cov xwm txheej raws hnub uas yuav tsum tau qhia thaum nws dhau sijhawm.
- Ib lub lim tiam, txhua hli (lossis tej zaum ob npaug ntawd) suav tus lej ntawm cov haujlwm tiav hauv txhua lub sijhawm. Qhov no tuaj yeem tsuas yog cov lus pom zoo uas koj tuaj yeem tau txais ntawm koj txoj haujlwm.
- Qhov no pom "hnub no" cov hnub (Monday = 1, Tuesday = 2,… Sun = 7) thiab txheeb xyuas seb nws puas lig dhau hnub Friday. Yog tias yog, nws yuav ntxiv xya rau hnub Friday tam sim no, muab lub lim tiam tom ntej rau hnub Friday. Yog tias nws tsis dhau hnub Friday, nws tsuas yog qhia hnub Friday rau lub lim tiam no.
- Hauv qhov piv txwv no, Hnub Monday = 1, Tuesday = 2, Wednesday = 3, Thursday = 4, Friday = 5, Saturday = 6, thiab Sunday = 7.
- Txhawm rau hloov cov mis no kom nws ua haujlwm rau, hais, Hnub Tuesday, peb tuaj yeem pom los ntawm kev saib cov npe saum toj no hnub Tuesday = 2, yog li tsuas yog sib pauv tawm peb 5s hauv cov qauv rau 2s.
Lus ceeb toom
- Hloov chaw nqa koj cov ntaub ntawv Excel nrog koj txhua lub sijhawm, siv Google Daim Ntawv Qhia kom koj cov ntaub ntawv ib txwm muaj rau koj.
- Txuag daim ntawv nthuav qhia txhua ob peb zaug kom rov zoo.
- Kev tswj hwm lub sijhawm yog tus kheej heev thiab daim ntawv nthuav qhia no yuav tsis haum nrog koj cov kev xav tau thiab nyiam. Tej zaum nws yuav xav tias nkag mus lossis tsuas yog xav kom tuaj ntsib txhua hnub. Nws yuav ua haujlwm rau koj, tab sis tsis yog koj cov phooj ywg lossis lwm tus.
- Qhov suav ua ntej tsis yog cov lej. Ua kom tiav "1" txoj haujlwm tseem ceeb tsis hloov tag nrho lwm tus. Qhov muaj feem thib yuav muaj ntau tshaj li ib puas thiab tsis yog txhua tus lej ua tau. Feem ntau tsom mus rau qhov muaj feem thib 1 thiab 12.
- Tsis txhob txhawj xeeb txog kev muaj ntau txoj haujlwm - txawm tias ob lub hlis ntawm cov haujlwm tuaj yeem tos hauv qab ntawm cov npe kom txog rau thaum lawv txog lub sijhawm ze.
