donod

¥Ñ­ì¥ý
X5=IF(AND($U5+$V5<>0,$AO5+$AP5<>0),"    *","")

§ï¬°
X5=IF(AND($AO5<>0,$AP5<>0,$U5<>0,$V5<>0),"    *",IF(AND($AO5<>0,$AP5=0,$U5<>0,$V5<>0),"    *",IF(AND($AO5=0,$AP5<>0,$U5<>0,$V5=0.6216),"    *",IF(AND($AO5<>0,$AP5=0.6216,$U5=0,$V5<>0),"    *",IF(AND($AO5=0,$AP5<>0,$U5<>0,$V5=0),"    *",IF(AND($AO5<>0,$AP5=0,$U5=0,$V5<>0),"    *",IF(AND($AO5<>0,$AP5<>0,$U5<>0,$V5=0),"    *",IF(AND($AO5<>0,$AP5=0,$U5<>0,$V5=0),"    *",IF(AND($AO5=0,$AP5<>0,$U5=0,$V5<>0),"    *","")))))))))

ª©¤jªº¤½¦¡¦p¦ó§ï¼g¡AÁÂÁÂ!

donod

¦^´_ 5# ­ã´£³¡ªL

ÁÂÁª©¤j¦^ÂСA¨ä¹ê¬O·Q¥N¤Jª©¤jªº¤½¦¡:
=IF(($U5+$V5)*($AO5+$AP5)=0,"",IF(($U6+$V6)*($AO6+$AP6),"    *",W5-LOOKUP(,0/(X$4:X4=""),W$4:W4)))

­ã´£³¡ªL

¦^´_ 4# donod

³o»ò¦h if , ¥Î + §Y¥i:(¥ô¤@¦¨¥ß)
=IF(AND($AO5<>0,$AP5<>0,$U5<>0,$V5<>0)+AND($AO5<>0,$AP5=0,$U5<>0,$V5<>0)+AND($AO5=0,$AP5<>0,$U5<>0,$V5=0.6216)+¡ã¡ã¡ã,"    *","")

¨ä¹êAND(($AO5+$AP5)<>0,($U5+$V5)<>0)¤w¥]§t¨ä¤¤´X­Ó±ø¥ó,
½Ð¦Û¦æ¦A¼f°u¦U±ø¥ó, ¯à¬Ù«h¬Ù¤§~~

donod

¦^´_ 3# ­ã´£³¡ªL
ÁÂÁ­㴣³¡ªLª©¤j!

¦pªGX5ªº±ø¥ó§ï¬°:
=IF(AND($AO5<>0,$AP5<>0,$U5<>0,$V5<>0),"    *",IF(AND($AO5<>0,$AP5=0,$U5<>0,$V5<>0),"    *",IF(AND($AO5=0,$AP5<>0,$U5<>0,$V5=0.6216),"    *",IF(AND($AO5<>0,$AP5=0.6216,$U5=0,$V5<>0),"    *",IF(AND($AO5=0,$AP5<>0,$U5<>0,$V5=0),"    *",IF(AND($AO5<>0,$AP5=0,$U5=0,$V5<>0),"    *",IF(AND($AO5<>0,$AP5<>0,$U5<>0,$V5=0),"    *",IF(AND($AO5<>0,$AP5=0,$U5<>0,$V5=0),"    *",IF(AND($AO5=0,$AP5<>0,$U5=0,$V5<>0),"    *","")))))))))

½Ð°Ý¦p¦ó®M¥Î¦bª©¤jªº¤½¦¡¤¤¡AÁÂÁÂ!

­ã´£³¡ªL

X5:
=IF(($U5+$V5)*($AO5+$AP5)=0,"",IF(($U6+$V6)*($AO6+$AP6),"    *",W5-LOOKUP(,0/(X$4:X4=""),W$4:W4)))

AJ5:
=IF(($C5+$D5)*($AE5+$AF5)=0,"",IF(($C6+$D6)*($AE6+$AF6),"    *",AG5-LOOKUP(,0/(AJ$4:AJ4=""),AG$4:AG4)))


­Y­nªº¬O³o­Ó®ÄªG¡A¨ä¥¦Ä椽¦¡¦Û¦æ¥h®M¡ã¡ã
³o¥ÎVBA¤Ï¦Ó¤ÓÁc½Æ¤F¡I

donod

½Ð±Ð¤j¤j¡A
¨Ò¤l¤¤¡A¦³"*"ªºÀx¦s®æ´£¥Ü­«½Æ¤F¬Û¹ïÄæ¦ìªºÀx¦s®æ¡A¨Ò¦p¨Ò¤l¤¤X12-X14¦³"*"¡A´£¥ÜW12-W14­«½Æ¤FÄæ¦ìAQ12-AQ14
1) ¦pªG·Q±N¤@³s¦ê¦³"*"³¡¤Àªº³Ì¤U¤@­ÓÀx¦s®æ(¤@³s¦ê¦³"*"³¡¤À¤§¤U¦³¾÷·|¦A¥X²{¤@³s¦ê¦³"*"³¡¤À©Î1­Ó¦³"*"³¡¤À)¡A¦^¶Ç¬Û¹ïÀx¦s®æ¤§¶¡¬Û´î(¥H¤W¨Ò¤l:X14=W14-W11) ¤Î
2) ±N¥u¦³1­Ó¦³"*"³¡¤ÀªºÀx¦s®æ¡A¦^¶Ç¬Û¹ïÀx¦s®æ¤§¶¡¬Û´î(¨Ò¤l¤¤:AJ7=AG7-AG6)
(¦³"*"³¡¤À¥i¥Î¨ä¥L¦r²Å¥N´Àªº)
¦A½Ð±Ð¤j¤j¡AVBA¦p¦ó¼g¡AÁÂÁÂ!

¦]¬°OFFICEª©¥»°ÝÃD¡A¤§«eªºªþ¥ó¥X²{¿ù»~¡A²{ªþ¤W¥¿½Tªº¡A½Ð±Ð¤j¤j«ü¾É¡AÁÂÁÂ!
test1.rar (626.37 KB)

¤U¸ü¦¸¼Æ: 9

2015-11-3 18:43