[µo°Ý] Ãö©óIF

eghost

½Ð±Ð¦U¦ì¥ý¶i
°²³]§Ú¦³A1¡BA2¡BA3¤T­Ó¤é´Á(A1¤@©w·|¦³¤é´Á¡AA2¤ÎA3«h¤£¤@©w¡AA2ªº¤é´Á¤@©w¤ñA3¦­¡A¤T­Ó¤é´Á¦b¿é¤J®É¡A¤@©w¬O¤µ¤Ñ¤§«áªº¤é´Á)

A4­n­pºâ¥XA1©ÎA2©ÎA3¨ì¤µ¤Ñªº¤é´ÁÁÙ¦³¦h¤Ö¤Ñ¼Æ(¥i¥H¬O­t¼Æ)
A4Åã¥Üµ²ªGªº±ø¥ó¦p¤U¡G

1¡B¦pªGA2»PA3³£¨S¦³¸ê®Æ¡A«hÅã¥Ü¤µ¤é-A1ªº¤Ñ¼Æ
2¡B¦pªGA2©ÎA3¦³¸ê®Æ¡A«hÅã¥Ü¤µ¤é-A2©Î¤µ¤é-A3ªº¤Ñ¼Æ
3¡B¦pªGA2¤ÎA3³£¦³¸ê®Æ¡A«hÅã¥Ü¤µ¤é-A2ªº¤Ñ¼Æ
4¡B¦pªG¤µ¤éªº¤é´Á¤w¸g¶W¹L¤FA2ªº¤é´Á¡A«hÅã¥Ü¤µ¤é-A3ªº¤Ñ¼Æ

³o¼Ë¥i¥H¥ÎIFªº¨ç¼Æ±o¨ì§Ú­nªºµª®×¶Ü?
ÁÙ¬O¤@©w­n¥ÎVBA?

jackson7015

¦^´_ 7# eghost


    ³o¼Ë²Ä¥|ÂI±ø¥ó´N­nÀu¥ý²Ä2©Î3¶µ±Æ§Ç
§Ú§â¤½¦¡±ø¥ó©ñ¦b²Ä1§PÂ_
=IF(AND(TODAY()>A2,LEN(A3)>0),A3-TODAY(),IF(AND(LEN(A2)<1,LEN(A3)<1),A1-TODAY(),IF(LEN(A2)>0,A2-TODAY(),IF(LEN(A3)>0,A3-TODAY(),IF(AND(LEN(A2)>0,LEN(A3)>0),A2-TODAY(),"")))))
¦b´ú¸Õ¬Ý¬Ý¦³¨S¦³²Å¦X§a

eghost

¦^´_ 6# jackson7015


¸ÑÄÀ¤@¤U¡G
¤é´Á­è¶}©l¦b½ü¤J®É¡A¤@©w¬O¨Ì<A1¤@©w·|¦³¤é´Á¡AA2¤ÎA3«h¤£¤@©w¡AA2ªº¤é´Á¤@©w¤ñA3¦­¡A¤T­Ó¤é´Á¦b¿é¤J®É¡A¤@©w¬O¤µ¤Ñ¤§«áªº¤é´Á>³o­Ó±ø¥ó¤U¥h½ü¤Jªº¡A

¥B¥ý«á¶¶§Ç·|¬O       ¹L¥hªº¤é¤l---¤µ¤é---A1---A2---A3---¥¼¨Ó§ó¤§«áªº¤é¤l

¤é´Á¿é¤J«á´N¤£¤j·|¥h§ó°Ê¤F¡A©Ò¥H·íA1¡BA2¡BA3ªº¤é´Á³£ÁÙ¦b¤µ¤Ñ¤§«á®É¡A¾A¥Î±ø¥ó1~3¡A
¦ýÁ`¦³¤@¤Ñ¶}Àɮתº®É«JA1¡BA2¤ÎA3ªº¤é´Á³£¹L¤F¤µ¤Ñ¤F¡A

¨Ò¦p¹³¬O³o¼Ë           ¹L¥hªº¤é¤l---A1---A2---¤µ¤é---A3---¥¼¨Ó§ó¤§«áªº¤é¤l
©Î¬O³o¼Ëªº®É«J       ¹L¥hªº¤é¤l---A1---A2---A3---¤µ¤é---¥¼¨Ó§ó¤§«áªº¤é¤l

ÁÙ¬O­n­pºâ¨ìA3ÁÙ¦³´X¤Ñ©Î¬O¶W¹L¤FA3´X¤Ñ¡A¶W¹L®É­nÅã¥Ü­t¼Æ¡A©Ò¥H¤~·|¦³²Ä¥|­Ó±ø¥ó²£¥Í¡C

¥H¤W¸É¥R¡A·PÁ¦U¦ì¥ý¶i«ü¥¿¡C

jackson7015

¦^´_  jackson7015


¤j¤j¡Aºâ¥X¨Ó¥¿­t¸¹¬O¬Û¤Ïªº¡A¬°¤°»ò¡H
¦Ó¥B¥u­n¤µ¤Ñªº¤é´Á¬O¦bA2¤ÎA3¤§«á¡A¥L´N ...
eghost µoªí©ó 2014-9-9 19:49

1.°ÝÃD¬Ý¿ù¡A­×¥¿¤@¤U
=IF(AND(LEN(A2)<1,LEN(A3)<1),A1-TODAY(),IF(LEN(A2)>0,A2-TODAY(),IF(LEN(A3)>0,A3-TODAY(),IF(AND(LEN(A2)>0,LEN(A3)>0),A2-TODAY(),IF(TODAY()>A2,A3-TODAY(),"")))))

2.¦]¬°¦pªGA2©ÎA3¦³¸ê®Æ¡A«hÅã¥Ü¤µ¤é-A2©Î¤µ¤é-A3ªº¤Ñ¼Æ¡A¦]¬°¤µ¤é¤é´Á¤@©w¦bA1:A3¤é´Á¤§«e¡A¥B¦³A2©ÎA3¨ä¤@¸ê®Æ¤~·|­pºâ¡A©Ò¥H¨Ì·Ó§Aªº±Æ§ÇA2>A3­pºâ

´N¦p¦P#2¤j¤j©Ò¨¥¡A²Ä4¶µ±ø¥ó¬O©M§A°ÝÃD¦³½Ä¬ð

eghost

¦^´_ 2# jackson7015


¤j¤j¡Aºâ¥X¨Ó¥¿­t¸¹¬O¬Û¤Ïªº¡A¬°¤°»ò¡H
¦Ó¥B¥u­n¤µ¤Ñªº¤é´Á¬O¦bA2¤ÎA3¤§«á¡A¥L´N·|ºâ¤µ¤é-A2ªº¤Ñ¼Æ

luhpro

¦^´_ 1# eghost
¸ÑªRÃD¥Ø:
1¡B¦pªGA2»PA3³£¨S¦³¸ê®Æ¡A«hÅã¥Ü¤µ¤é-A1ªº¤Ñ¼Æ
2¡B¦pªGA2©ÎA3¦³¸ê®Æ¡A«hÅã¥Ü¤µ¤é-A2©Î¤µ¤é-A3ªº¤Ñ¼Æ
3¡B¦pªGA2¤ÎA3³£¦³¸ê®Æ¡A«hÅã¥Ü¤µ¤é-A2ªº¤Ñ¼Æ
4¡B¦pªG¤µ¤éªº¤é´Á¤w¸g¶W¹L¤FA2ªº¤é´Á¡A«hÅã¥Ü¤µ¤é-A3ªº¤Ñ¼Æ
¥iª¾:
1. ­Y A2 ¦³¸ê®Æ, «hÅã¥Ü ¤µ¤é-A2 ªº¤Ñ¼Æ
2. §_«h ­Y A3 ¦³¸ê®Æ, «hÅã¥Ü ¤µ¤é-A3 ªº¤Ñ¼Æ
3. §_«hÅã¥Ü ¤µ¤é-A1 ªº¤Ñ¼Æ
¥i±o :
A4=TODAY()-IF(A2<>0,A2,IF(A3<>0,A3,A1))

¥H¤U¬°¨Ï¥Î CHOOSE ¨ç¼Æªº¤èªk:
A4=TODAY()-CHOOSE((A1<>0)+((A2<>0)*2)+((A3<>0)*4),A1,0,A2,A3,A3,0,A2)

p212

¦^´_ 1# eghost
¨Ï¥Î©w¸q¦WºÙªº¸Ñªk
1¡B©w¸q¦WºÙ¡ux¡v¡A¡u°Ñ·Ó¨ì¡v¿é¤J
=IF((¤u§@ªí1!$A$2="")*(¤u§@ªí1!$A$3=""),1,IF(OR((¤u§@ªí1!$A$2<>"")*(¤u§@ªí1!$A$3<>"")*((TODAY()-¤u§@ªí1!$A$2)<=0),(¤u§@ªí1!$A$2<>"")*(¤u§@ªí1!$A$3="")),2,IF((¤u§@ªí1!$A$2="")*(¤u§@ªí1!$A$3<>""),3,3)))
2¡BÀx¦s®æA4¿é¤J¤½¦¡
=TODAY()-INDEX($A$1:$A$3,x)
½Ð°Ñ¦Ò¡I
[µù] ­ì°Ý1#¦³ÂI©_©Ç¡B½Ä¬ð¡I§í¬°§^¤H¤£¸Ñ¡H¦ü¥GÀx¦s®æA4Åã¥Üµ²ªGªº±ø¥ó¤§4µL¦s¦bªº¥²­n¡C
°²³]§Ú¦³A1¡BA2¡BA3¤T­Ó¤é´Á(A1¤@©w·|¦³¤é´Á¡AA2¤ÎA3«h¤£¤@©w¡AA2ªº¤é´Á¤@©w¤ñA3¦­¡A¤T­Ó¤é´Á¦b¿é¤J®É¡A¤@©w¬O¤µ¤Ñ¤§«áªº¤é´Á)
A4Åã¥Üµ²ªGªº±ø¥ó¦p¤U¡G
4¡B¦pªG¤µ¤éªº¤é´Á¤w¸g¶W¹L¤FA2ªº¤é´Á¡A«hÅã¥Ü¤µ¤é-A3ªº¤Ñ¼Æ

jackson7015

¦^´_ 1# eghost
³Ì²Âªº¦U¼h¼gªk¡A°Ñ¦Ò¬Ý¬Ý
=IF(AND(LEN(A2)<1,LEN(A3)<1),TODAY()-A1,IF(LEN(A2)>0,TODAY()-A2,IF(LEN(A3)>0,TODAY()-A3,IF(AND(LEN(A2)>0,LEN(A3)>0),TODAY()-A2,IF(TODAY()>A2,TODAY()-A3,"")))))

¦b½Ð¨ä¥L«e½ú²¤Æ