ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

¦p¦ó¦C¥X¨C­Ó¤ëªº¤£¥]§t¬P´Á¤»¤éªº©T©w¶¡¹j¤é´Á?

¦p¦ó¦C¥X¨C­Ó¤ëªº¤£¥]§t¬P´Á¤»¤éªº©T©w¶¡¹j¤é´Á?

¥»©«³Ì«á¥Ñ q1a2z5 ©ó 2011-5-19 10:02 ½s¿è

½Ð°Ý :
·Q¦C¥X¨C­Ó¤ëªº¤£¥]§t¬P´Á¤»¤éªº©T©w¶¡¹j¤é´Á¡A¸Ó¦p¦ó³B²z¡H

¥t¥~¡A¤é´Á®æ¦¡·QÅã¥Ü100¦~5¤ë18¤é¤Î2011/05/18³o¤GºØ®æ¦¡ªº¸Ü¡AÁä½L¸Ó¿é¤J¤°»òÁä¡H

ÁÂÁ¡I

¥»©«³Ì«á¥Ñ mark15jill ©ó 2011-5-19 09:43 ½s¿è

¦^´_ 1# q1a2z5


    ªO¤W¦³«Ü¦hÃþ¦üªº°ÝÃD  ¼Ó¥D±z¦³¬Ý¹L¶Ü-0-..
½Ðµ½¥Î ¥k¤W¨¤ ·j¯Áªº¥\¯à..
­è­èµy·Lª¦¤F¤@¤U¤å
µo²{¦³«Ü¦h©«¤l³£¬OÃö©ó¦¹¤è­±ªº ¥i¯à­n³Â·Ð¼Ó¥D¬Ý¤@¤U

TOP

§Ú¦bºô¸ô¤W¬Ý¨ì¥i¦C¥X¨C­Ó¤ëªº¬P´Á¤» '¤éªº¤@­Ó¨ç¼Æ±Ð¾Ç¡A¦ý¬O§Ú§@¤£¥X¨Ó¡A
½Ð°Ý¸Ó¦p¦ó§@©O¡H¡@ÁÂÁ¡I

±Ð¾Ç¤º®e¦p¤U¡G
Excel-¦C¥X¶g¤»¤éªº¤é´Á
¦b Excel ¤¤´£¨Ñ¤F¦hºØ¤è«K¨Ï¥Îªº¤é´Á¨ç¼Æ¡A¦pªG­n¦C¥X¬Y¦~ªº¶g¤»¡B¶g¤é¤§¤é´Á(¦p¤U¹Ï)¡A¸Ó¦p¦ó³]­p¡H
¥ý¦bÀx¦s®æB1¤¤¥H¸ê®ÆÅçÃҤ覡¡A«ü©wÀx¦s®æ¬°¡u²M³æ¡v¡A¤º®e¬°¡u2010,2011,2012,2013¡v¡C

±µµÛ³]©w¤@­Ó¦WºÙ¡GDATA
DATA¡G=INDIRECT("A"&DATE($B$1,1,1)&":A"&DATE($B$1,12,31))
¤½¦¡¤¤ªº¡G"A"&DATE($B$1,1,1)&":A"&DATE($B$1,12,31)
°²³]Àx¦s®æB1ªº¤º®e¬°2011¡A
«h¤½¦¡=A40544:A40908¡A40544¬°2011/1/1ªº¼Æ­È¡A¦Ó40908¬°2011/12/31ªº¼Æ­È¡C
¦A³z¹LINDIRECT¨ç¼ÆÂনÀx¦s®æ¦ì§}¡C¤]´N¬O·í¿ï¾Ü¤F¤@­Ó¦~«×®É¡A·|²£¥Í¤@²Õ¦ì§}©M¤é´Á¼Æ­È¬Û¦PªºÀx¦s®æ°}¦C¡C

(1) §ä¥X¤é´Á
Àx¦s®æB3¡G{=SMALL(IF(WEEKDAY(ROW(DATA),2)>5,ROW(DATA),FALSE),ROW(1:1))}
ROW(DATA)·|±N¤é´Á¦ì§}ªº°}¦CÂà´«¦^¼Æ­È(¨Ò¦p¡G40544¡B40545¡B40546¡K)°}¦C¡A¦ÓWEEKDAY¨ç¼Æ¥Î¥H§ä¥X¬O§_¬°¬P´Á¤»¡B¤é(¶Ç¦^­È¬°6,7)¡C
SMALL¨ç¼Æ¥i¥H¨Ì§Ç(ÂÇ¥ÑROW¨ç¼Æ)¥Ñ¤p¨ì¤j¦C¥X¤é´Á¡C
½Æ»sÀx¦s®æB3¡A©¹¤U¶K¤W¡C

(2) Åã¥Ü¬P´Á¤»¡B¤é
Àx¦s®æC3¡G=CHOOSE(WEEKDAY(B3,2)-5,"¤»","¤é")
¦]¬°WEEKDAY(B3,2)·|¶Ç¦^6©Î7¡A©Ò¥H´î5«á±o1©Î2¡A¦bCHOOSE¨ç¼Æ¤¤¥i±o¡u¤»©Î¤é¡v¡C
½Æ»sÀx¦s®æC3¡A©¹¤U¶K¤W¡C

(3) Åã¥Ü¤ë¥÷
Àx¦s®æA4¡G=IF(MONTH(B4)=MONTH(B3),"",MONTH(B4)&"¤ë")
¦]¬°¥u¦³¸Ó¤ëªº²Ä¤@¤Ñ·|Åã¥Ü¤ë¥÷¡A©Ò¥H¥u­n¤ñ¹ï©M¤W¤@­ÓÀx¦s®æ©Ò±oªº¤ë¥÷¤£¦PªÌÅã¥Ü¤ë¥÷¡A§_«hÅã¥ÜªÅ¥Õ¡C
½Æ»sÀx¦s®æA4¡A©¹¤U¶K¤W¡C
¦P²z¡A¦pªG§A·Q­n¥u§ä¥X¬Y¤@¦~¤¤¬P´Á¤Gªº©Ò¦³¤é´Á¡G
Àx¦s®æF3¡G{=SMALL(IF(WEEKDAY(ROW(DATA),2)=2,ROW(DATA),FALSE),ROW(1:1))}
½Æ»sÀx¦s®æF3¡A©¹¤U¶K¤W¡C

image[3].png (19.03 KB)

image[3].png

TOP

¦^´_ 3# q1a2z5

ªí®æ¦p¹Ï
¤u¨ã/¼W¯q¶°/¤Ä¿ï¤ÀªR¤u¨ã½c
A2¿é¤J¦~¥÷¡AB2¿é¤J¤ë¥÷
B3=WORKDAY(WORKDAY(DATE($A$2,$B$2,0),1,$E$2:$E$4),ROW(A1)-1,$E$2:$E$4)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¤@¥y·Å·xªº¸Ü¡A´N¹³©¹§O¤H¨­¤WÅx­»¤ô¡A¦Û¤v·|ªg¨ì¨â¤Tºw¡C
ªð¦^¦Cªí ¤W¤@¥DÃD