[µo°Ý] ±zÁä¤Jªº¤½¦¡¦³¿ù»~?(¥H¸Ñ¨M¡A¶W¯Å·PÁÂA¤jH¤j)

av8d

IF(B3="","",LOOKUP(,-FIND({"101","201","301","401","501","601","701","801","901","1001","1101","1201"},B3),{"1231","131","230","331","430","531","630","731","831","930","1031","1130"}),IF(ISERROR(FIND("®ð²y",$D3))+(ISNUMBER(FIND("®ð²y",$D3))*(--TEXT(C3,"00!:00")>0.5)),B3,B3-1))


¥i¬O¤£ª¾¹D¿ù¦b­þ¸Ì?

---------------------------------------------------------------------------
8¼ÓªºH¤j¸Ñµª¤F§ÚªººÃ°Ý~±z©Ò»¡ªº¸Ü~§Ú·|ÂÔ°O¦b¤ßªº~ÁÂÁÂ!!

ANGELA

²q¬O

=IF(B3="","",IF(ISERROR(LOOKUP(,-FIND({"101","201","301","401","501","601","701","801","901","1001","1101","1201"},B3),{"1231","131","230","331","430","531","630","731","831","930","1031","1130"})),IF(ISERROR(FIND("®ð²y",$D3))+(ISNUMBER(FIND("®ð²y",$D3))*(--TEXT(C3,"00!:00")>0.5)),B3,B3-1)))

av8d

¦^´_ 2# ANGELA


    ÁÂÁ¤j¤j~§Ú·Q¸ß°Ý¤@¤U~¬O¨ç¼Æ¤U¿ù¶Ü?

¬°¤°»ò¥L¬OFALSE  §Ú­nªºµª®×¬O1231

123.zip (4.14 KB)

¤U¸ü¦¸¼Æ: 8

2011-10-4 15:22

ANGELA

=IF(B3="","",IF(ISERROR(LOOKUP(,-FIND({"101","201","301","401","501","601","701","801","901","1001","1101","1201"},B3),{"1231","131","230","331","430","531","630","731","831","930","1031","1130"})),IF(ISERROR(FIND("®ð²y",$D3))+(ISNUMBER(FIND("®ð²y",$D3))*(--TEXT(C3,"00!:00")>0.5)),B3,B3-1),--LOOKUP(,-FIND({"101","201","301","401","501","601","701","801","901","1001","1101","1201"},B3),{"1231","131","230","331","430","531","630","731","831","930","1031","1130"})))
¨ä¹ê»¡©ú§Aªº¥Î·N,·|¸û¦n¤U¤â,¤]³\¥i¥Î¨ä¥L¤½¦¡¸Ñ¨M,§_«h¥u¦n±N´N§Aªº¤½¦¡¥h½M²q.

av8d

¦^´_ 4# ANGELA


    ¤j¤j±z¦n~¦b¦¹»¡©ú§Úªº¥Î·N~¦A¦¸·PÁÂ~±z¯uªº¦n¼F®`~¥i¬O¦n¹³¬O§Ú¦Û¤v¼g¿ù~¸Ô²Ó»¡©ú¦p¤U!

=IF(ISERROR(FIND("®ð²y",$D3))+(ISNUMBER(FIND("®ð²y",$D3))*(--TEXT(C3,"00!:00")>0.5)),B3,B3-1)
­ì¥Î·N¬°¡G·íDÄæ¥X²{®ð²yªº®É­Ô~¥h§PÂ_CÄæ¬O§_¶W¹L50%¦pªG¶W¹L´N¶ñ¤WBÄæ¦ì~­Y§C©ó50%¥L´N·|¶ñ¤WBÄ檺­È-1

¦ý¬O§Ú²{¦b­n¦bBÄæ-1(¦p¤U)³o­Ó¦a¤è¦b°µªº®É­Ô§PÂ_BÄæ¬O§_¬°101,201,301,401.......1201µM«á¥h¹ïÀ³ª½±µ¶ñ¤J1231,131,230,331........1130
=IF(ISERROR(FIND("®ð²y",$D3))+(ISNUMBER(FIND("®ð²y",$D3))*(--TEXT(C3,"00!:00")>0.5)),B3,B3-1)

¥i¬O§Ú§PÂ_¦¡¤U¿ù¤F~¥Ø«eÁÙ¨S¥X²{§Ú­nªº­È~¦A¦¸·PÁ¤j¤j¦h¦¸¨ó§U!!

ANGELA

Book1.zip (4.1 KB)

¤U¸ü¦¸¼Æ: 3

2011-10-5 09:55

°Ñ¦Ò¬Ý¬Ý,¬O§_¬O§A­nªº.

av8d

¦^´_ 6# ANGELA


    ¤j¤j~¤£¦n·N«ä³á~¥Ñ©ó¥²¶·¿é¤J¤è«K~¤£¥i¥H§ó§ï¦Û­q¤è¦¡~
¥Lªº§PÂ_¦³»~~·í¤é´Á¬O1¸¹~¥Lªº§PÂ_À³¸Ó¬O¥ý§PÂ_¥Ø«e¬O§_¦³®ð²y~®É¶¡¬O§_¦b12ÂI¥H«e~
¦pªG¬O~´NÅã¥Ü¤ë©³~¦pªG¬O12ÂI¥H«á~´NÅã¥Ü·í¤Ñ~¦ý¬O¥L¨ÌµMÅã¥Ü¤ë©³!

§Ú·Qª¾¹D¦p¦ó¦b¨ç¼Æ¤¤¦b°µ§PÂ_~¹³¬O¼gVBA¨º¼Ë!

Hsieh

¦^´_ 7# av8d


    ¸ê®Æ­n³o¼Ëª±¬O¦Û§ä³Â·Ð
G3=IF(B3="","",IF(ISERROR(FIND("®ð²y",$D3))+(ISNUMBER(FIND("®ð²y",$D3))*(--TEXT(C3,"00!:00")>0.5)),--TEXT(TEXT(B3,"00!/00"),"mdd"),--TEXT(TEXT(B3,"00!/00")-1,"mdd")))