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

[µo°Ý] ¨Ï¥ÎVlookup + Offset ¸Ñ¤G±ø¥ó

[µo°Ý] ¨Ï¥ÎVlookup + Offset ¸Ñ¤G±ø¥ó

¥»©«³Ì«á¥Ñ cw3076 ©ó 2010-7-30 07:35 ½s¿è

Dear ¤j¤j,
½Ð°Ý­Y¨Ï¥Î Vlookup+Offset ¸Ó¦p¦ó±NGÄ檺Result¹ïÀ³¨ìB2
¨ÌA2,A3....¤ÎB1¤G­Ó±ø¥ó

¦^´_ 1# cw3076

¦pªG¨S¦³­«½Æ°ÝÃD
b2=OFFSET($G$1,SUMPRODUCT(($E$2:$E$6=$A2)*($F$2:$F$6=$B$1),ROW($2:$6))-1,)
¦V¤U½Æ»s
­Y¥i¯à­«½Æ­n§ä¥X²Ä¤@µ§
b2°}¦C¤½¦¡
{=OFFSET($G$1,MIN(IF(($E$2:$E$6=$A3)*($F$2:$F$6=$B$1),ROW($2:$6)))-1,)}
¦V¤U½Æ»s
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ jackdream ©ó 2010-7-30 11:36 ½s¿è

°²¦p E Äæ¦ì(No) ªº¤º®e¦³±Æ§Çªº¸Ü..
B2=VLOOKUP($B$1,OFFSET($F$1,MATCH(A2,$E$1:$E$6,0)-1,0,6,2),2,FALSE)
¤½¦¡©¹¤U©Ô

TOP

¬°¤°»ò¤@©w­n¥Î vlookup+offset

{=INDEX(G$1:G$100,MATCH($A2&B$1,E$1:E$100&F$1:F$100,))}
300 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦pªG¬Û¦P±ø¥ó¤£¥u¤@­Ó
b2
{=INDEX($G$2:$G$6,SMALL(IF(($E$2:$E$6=$A2)*($F$2:$F$6=$B$1),ROW($G$1:$G$5)),COLUMN(A1)))}
¦V¥k¤Î¤U½Æ»s
¾÷¾¹¤H¤T¤j©w«ß-«OÅ@¤HÃþ¡BªA±q©R¥O¡B«OÅ@¦Û¤v
§Úªº¤T¤j©w«ß-¥Í©R¡B¥Í¦s¡B¥Í¬¡

TOP

        ÀR«ä¦Û¦b : ¯àµ½¥Î®É¶¡ªº¤H¡A¥²¯à´x´¤¦Û¤v§V¤Oªº¤è¦V¡C
ªð¦^¦Cªí ¤W¤@¥DÃD