Ãö©óHLOOKUP¤ÎIF¤½¦¡ªº³]©w..?

jumky

§Ú¤§«e¦bD12»PG12Äæ¦ì§¡³]¦³¤½¦¡..¦ý²{¦b¼W¥[101¦~ªºÄæ¦ì§YH9:S11ªº¸ê®Æ..©Ò¥H³o¨âÄ檺¤½¦¡¤£ª¾¸Ó¦p¦ó­×§ï..?(¸Ô¦pªþ¥ó 12.rar (1.99 KB)

¤U¸ü¦¸¼Æ: 14

2012-11-29 14:05

)
D12Äæ(¥ÎHLOOKUP)¤½¦¡
­n³]°²¦pA12=99´N¹ï·ÓG1:R3ªº¸ê®Æ
                 A12=100´N¹ï·ÓH5:S7ªº¸ê®Æ
                 A12=101´N¹ï·ÓH9:S11ªº¸ê®Æ
G12Äæ(¥ÎIF)¤½¦¡­n³]°²¦pA12=99´ND12-(C12-1)+E12+F12
                               A12=100´ND12-(C12-1)+E12
                               A12=101´ND12-(C12-1)

Hsieh

¦^´_ 1# jumky


   D12=HLOOKUP($B12,OFFSET($A$1,MATCH(A12&"¦~",A:A,0)-1,7,3,12),3,0)

zz5151353

¨Ó­Ó¨ú¥©¤½¦¡
D12
=INDEX($A$3:$S$11,MATCH($A12&"¦~",$A:$A,),MATCH($B12,$1:$1,))

ML089

¦A¨ú¥©¤@ÂI
D12
=HLOOKUP(B12,OFFSET(H1:S3,MATCH(A12&"¦~",A:A,)-1,),3,)

ML089

¦A¨ú¥©¤@ÂI
D12
=HLOOKUP(B12,OFFSET(H1:S3,MATCH(A12&"¦~",A:A,)-1,),3,)

jumky

¦^´_ 5# ML089

ÁÂÁÂzz5151353.hsieh.ml089¤j¤j,¥iG12Ä檺IF¤½¦¡­n¦p¦ó³]..?¥t¥~=HLOOKUP(B12,OFFSET(H1:S3,MATCH(A12&"¦~",A:A,)-1,),3,)¨ä¤¤A:A¤Î-1¬O¤°»ò·N«ä..:P

ML089

¦^´_ 6# jumky

A:A¬O«ü¾ã­ÓAÄæ
-1¬OOFFSET¥Ñªº«ü¼Ð­p¼Æ¥Ñ0¶}©l¡A©ÒMATCH¬d¨ìªº¦ì¸m´î1

G12¤£ª¾¹D§Aªº°ÝÃD¬O¤°»ò

jumky

¦^´_ 7# ML089


    ML089¤j¤j±z¦n¡G¦]¬°¤§«eG12§Ú¦³¦Û¤v³]¨ç¼Æ=IF(A12=99,D12-(C12-1)+E12,D12-(C12-1))
   ¦ý¦]¬°¦h¤FA9:S11ªº¸ê®Æ§Ú´N¤£¾å±o­n¦p¦ó¥ÎIF¨ç¼Æ³]..?

jumky

G12Äæ­n¥ÎIF¨ç¼Æ³]ªº¤º®e¬OA12=99´ND12-(C12-1)+E12+F12
                                 A12=100´ND12-(C12-1)+E12
                                 A12=101´ND12-(C12-1)

ML089

G12Äæ­n¥ÎIF¨ç¼Æ³]ªº¤º®e¬O
A12=  99´ND12-(C12-1)+E12+F12
A12=100´ND12-(C12-1)+E12
A12=101´ND12-(C12-1)

¤W¤T¦¡¦X¨Ö¬°
D12-(C12-1)+(E12+F12)*(A12=99)+(E12)*(A12=100)