ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

¥»©«³Ì«á¥Ñ jumky ©ó 2012-11-29 14:08 ½s¿è

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

¦^´_ 1# jumky


   D12=HLOOKUP($B12,OFFSET($A$1,MATCH(A12&"¦~",A:A,0)-1,7,3,12),3,0)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

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

TOP

¦A¨ú¥©¤@ÂI
D12
=HLOOKUP(B12,OFFSET(H1:S3,MATCH(A12&"¦~",A:A,)-1,),3,)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦A¨ú¥©¤@ÂI
D12
=HLOOKUP(B12,OFFSET(H1:S3,MATCH(A12&"¦~",A:A,)-1,),3,)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 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

TOP

¦^´_ 6# jumky

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

G12¤£ª¾¹D§Aªº°ÝÃD¬O¤°»ò
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ jumky ©ó 2012-12-8 09:28 ½s¿è

¦^´_ 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¨ç¼Æ³]..?

TOP

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

TOP

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)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¬°¤H³B¥@­n¤p¤ß²Ó¤ß¡A¦ý¤£­n¡u¤p¤ß²´¡v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD