[µo°Ý] ½Ð±Ð¤j¤jJ11ªº¤½¦¡¦p¦ó§ï¼g

donod

test1.rar (3.65 KB)

¤U¸ü¦¸¼Æ: 7

2013-11-17 18:17

½Ð±Ð¤j¤jJ11ªº¤½¦¡¦p¦ó§ï¼g¡A¦PJ7µ²ªG¤@¼Ë¡AÁÂÁ¤j¤j!

sunnyso

½Ð§âJ7ªºÅ޿軡©ú¤@¤U,

sunnyso

¦^´_ 1# donod

°}¦C¤½¦¡

1. {=SUMPRODUCT(IF(LEFT($B11:$H11,3)="10K",(--MID($B11:$H11,4,LEN($B11:$H11)-3)<$F$6)*(--MID($B11:$H11,4,LEN($B11:$H11)-3)>$G$6)*($C11:$I11<$F$6)*($C11:$I11>$G$6),),$C11:$I11)+(COUNT(1/(IF(LEFT($B11:$H11,3)="10K",(--MID($B11:$H11,4,LEN($B11:$H11)-3)<$F$6)*(--MID($B11:$H11,4,LEN($B11:$H11)-3)>$G$6)*($C11:$I11>=$F$6))))*$F$6)+(COUNT(1/(IF(LEFT($B11:$H11,3)="10K",(--MID($B11:$H11,4,LEN($B11:$H11)-3)>=$F$6))))*$F$6)+(COUNT(1/(IF(LEFT($B11:$H11,3)="10K",(--MID($B11:$H11,4,LEN($B11:$H11)-3)<$F$6)*(--MID($B11:$H11,4,LEN($B11:$H11)-3)>$G$6)*($C11:$I11<=$G$6))))*$G$6)+(COUNT(1/(IF(LEFT($B11:$I11,3)="10K",(--MID($B11:$I11,4,LEN($B11:$I11)-3)<=$G$6))))*$G$6)}

½Æ»s¥N½X

sunnyso

¦^´_  donod

°}¦C¤½¦¡
sunnyso µoªí©ó 2013-11-17 20:38

¸É¤Wªþ¥ó
test1-sonny.rar (3.85 KB)

¤U¸ü¦¸¼Æ: 1

2013-11-17 20:40

donod

¦^´_ 4# sunnyso
¬O³o­Ó¡AÁÂÁ¤j¤j!

sunnyso

¦^´_ 5# donod

½Ð°ÝÅ޿褰»ò,

©ÎªÌ»¡­n¹F¨ì¤°»ò®ÄªG

donod

¦^´_ 6# sunnyso
if K¦rÀYªº¼Æ¦r<72¤Î>-90 AND ¥¦¥k°¼ªº¼Æ­È>=72¡AJ7=72

sunnyso

¦^´_ 7# donod

®Ú¾Ú§AªºÅÞ¿è

J7 =IF(LEFT(B7,1)="K", IF(C7>=F6, LOOKUP(---SUBSTITUTE(B7,"K",""),-$F$6:$G$6,$F$6:$G$6),C7),0)

§Y¥i

donod

¦^´_ 8# sunnyso

¦AÁÂÁ¤j¤j«ü±Ð!
½Ð°Ý(---SUBSTITUTE¤¤ªº"---"¬O¦³¤°»ò·N¸q?

sunnyso

¦^´_ 9# donod

¥Î¨Ó§â¤å¦rÂন¼Æ¦r, ---(-41) = 41