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

½Ð±Ð¤j¤j¦p¦ó¶ë¿ï¦h¶µ ¦p¹Ï³o¼Ë

½Ð±Ð¤j¤j¦p¦ó¶ë¿ï¦h¶µ ¦p¹Ï³o¼Ë

½Ð±Ð¤j¤j  ¦p¦ó¥Î¶ë¿ï§PÂ_


¥»©«³Ì«á¥Ñ p212 ©ó 2016-2-2 10:39 ½s¿è

¦^´_ 1# keovin790916
1¡B»s§@¸ê®Æ¨Ó·½°Ï¤§¡u°ÊºA½d³ò¡v
(1)©w¸q¦WºÙ¡u¥N¸¹¡v¡A¡u°Ñ·Ó¨ì¡v¿é¤J
=OFFSET(¤u§@ªí1!$B$2,,,COUNT(¤u§@ªí1!$A:$A))
(2)©w¸q¦WºÙ¡u½s¸¹¡v¡A¡u°Ñ·Ó¨ì¡v¿é¤J
=OFFSET(¤u§@ªí1!$A$2,,,COUNT(¤u§@ªí1!$A:$A))
2¡B´£¨ú¡u¥N¸¹¡vªº°ß¤@­È
Àx¦s®æE4¿é¤J°}¦C¤½¦¡(Ctrl+Shift+Enter¤TÁä»ô«ö¿é¤J¤½¦¡)
=IFERROR(INDEX(¥N¸¹,MATCH(0,COUNTIF($E$3:E3,¥N¸¹),0)),"")
¦V¤U½Æ»s¤½¦¡
3¡B¦^À³¡u¥N¸¹¡v¹ïÀ³ªº¡u½s¸¹¡v
Àx¦s®æF4¿é¤J°}¦C¤½¦¡
=IFERROR(INDEX(½s¸¹,SMALL(IF(¥N¸¹=$E4,ROW(¥N¸¹),""),COLUMN(A$1))-1),"")
¦V¥k¡B¦V¤U½Æ»s¤½¦¡
½Ð°Ñ¦Ò¡I

°Ñ¦Ò.zip (12.76 KB)

TOP

¦^´_ 2# p212


    ¤j¤j³o¼Ëªº¸Ü ¥i¦æ?  ±z¬Ý¬Ý

½d¨Ò.rar (16.4 KB)

¤£¦P¤u§@­¶

TOP

¤j¤j ¦pªG¹J¨ì­«½Æªº §Ú¤£­nÅã¥Ü  ¨º»ò­n«ç»ò­×§ï??

TOP

¦^´_ 4# keovin790916
½Ð°Ñ¦Ò¡I

½d¨Ò_ref.zip (44.46 KB)

TOP

¦p¦ó¦b!
D2¤U©Ô{=INDEX(B:B,SMALL(IF(MATCH(B$2:B$728,B$2:B$728,)=ROW(B$2:B$728)-1,ROW(B$2:B$728),4^8),ROW(A1)))&""
E2¥k©Ô¦A¤U©Ô{=INDEX($A:$A,SMALL(IF((MATCH($B$2:$B$278&$A$2:$A$278,$B$2:$B$278&$A$2:$A$278,)=ROW(A$2:A$278)-1)*($B$2:$B$278=--$D2),ROW($B$2:$B$278),4^8),COLUMN(A1)))&""

TOP

        ÀR«ä¦Û¦b : §Ú­Ì³Ì¤jªº¼Ä¤H¤£¬O§O¤H¡D¥i¯à¬O¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD