Yog tias koj xav tau daim ntawv qhia hnub ntawm Microsoft Excel daim ntawv, tom qab ntawd koj muaj ntau txoj hauv kev - los ntawm kev mob siab rau nkag mus rau hnub tim manually mus txuas cov pop-up daim ntawv qhia hnub los ntawm ntau yam add-ons thiab macros. Lwm qhov kev xaiv yog siv daim ntawv qhia hnub thoob ntiaj teb rau txhua hnub uas siv tsuas yog ib qho (txawm tias ntshai heev ntawm tus cwj pwm) array formula.
Txhawm rau siv nws, tsim ib qho khoob ntawm daim ntawv zoo li no:
Hnub tim hauv cell B2 tuaj yeem yog txhua yam, tsuas yog lub hli thiab xyoo tseem ceeb ntawm no. Cov hlwb hauv qhov ntau B3: H3 tuaj yeem muaj cov npe ntawm hnub ntawm lub limtiam hauv txhua hom ntawv tsim nyog.
Tam sim no xaiv qhov ntau B4: H9 thiab nkag mus rau cov qauv hauv qab no muaj:
=ЕСЛИ(МЕСЯЦ(ДАТА(ГОД(B2);МЕСЯЦ(B2);1)) <>МЕСЯЦ(ДАТА(ГОД(B2);МЕСЯЦ(B2);1)- (ДЕНЬНЕД(ДАТА(ГОД(B2);МЕСЯЦ(B2);1);2)-1) +{0:1:2:3:4:5}*7+{1;2;3;4;5;6;7}-1);» «; ДАТА(ГОД(B2);МЕСЯЦ(B2);1)- (ДЕНЬНЕД(ДАТА(ГОД(B2);МЕСЯЦ(B2);1);2)-1) +{0:1:2:3:4:5}*7+{1;2;3;4;5;6;7}-1)
Hauv Askiv version nws yuav yog:
=IF(MONTH(DATE(YEAR(B2),MONTH(B2),1)) <>MONTH(DATE(YEAR(B2),MONTH(B2),1)- (WEEKDAY(DATE(YEAR(B2),MONTH(B2),1))-1) +{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),””, DATE(YEAR(B2),MONTH(B2),1)- (WEEKDAY(DATE(YEAR(B2),MONTH(B2),1))-1) +{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)
Ces ntaus ua ke Ctrl + ua haujlwm + nkagkom nkag mus rau cov qauv no raws li cov qauv array. Txhua lub hlwb raug xaiv yuav tsum tau sau nrog cov hnub ntawm lub hli teev hauv B2:
Txhua yam uas tseem tshuav yog txhawm rau txhawm rau saib los ntawm kev ntxiv formatting thiab zais hnub nyob rau hauv nqe lus B2 thiab lub hli thiab xyoo nyob rau hauv seem ntawm lub hlwb siv lub qhov rais Format Cells (Ctrl + 1):
Tam sim no, los ntawm kev hloov hnub hauv xov tooj ntawm tes B2, peb yuav tau txais daim ntawv qhia hnub raug rau txhua lub hlis twg ntawm ib xyoos raws li peb cov qauv. Yuav luag ib daim calendar perpetual 😉
- Yuav ua li cas txuas popup daim ntawv qhia hnub rau daim ntawv Excel
- Hnub thiab sijhawm nkag ceev nrog PLEX add-on
- Excel ua haujlwm li cas nrog cov hnub thiab sijhawm
- Cov hnub thiab lub sij hawm nkag ceev yam tsis muaj kev sib cais