[µo°Ý] ¨D«ü©wªº¹ïÀ³¼Æ­È

ziv976688

¨D«ü©wªº¹ïÀ³¼Æ­È.rar (5.01 KB)

¤U¸ü¦¸¼Æ: 10

2017-9-1 06:26

Q6,R5,R6=¤â°Ê¶ñ¤J¼Æ­È

»Ý¨D¡J
1.½Ð±NJ¡JP¦³Åã¥Ü=$R$5­ÈªºIÄæ´Á¼Æ¶ñ¤JR7
EX¡JSheet1!R7=05¡FR8=11¡F....¡FR19=100
EX¡JSheet2!R7=60¡FR8=62¡F....¡FR13=100

2.½Ð±NJ¡JP¦³Åã¥Ü=$R$5­È¤§B¡JHªº¹ïÀ³¼Æ­È¶ñ¤JQ7
EX¡JSheet1!Q7=26¡FR8=23¡F....¡FQ19=20
EX¡JSheet2!Q7=05¡FR8=20¡F....¡FQ13=07

PS¡JSheet1©MSheet2ªºIÄæ°_©l´Á¼Æ¤£¦P¡C

¨DQ7©MR7¨ç¼Æªº¤½¦¡¸Ñ¡C

½Ð¦U¦ì¤j¤jÀ°¦£!ÁÂÁÂ¥ý!

ziv976688

¦^´_ 2# hcm19522

¦pªG±NB:H©MJ:P¸ê®Æ¹ï½Õ¡A
§Ú¨S¦³¿ìªk¥H±zªºR7¤½¦¡§@­×§ï¡F
³Ì«á§Ú¥u¯à§ï¦¨R7=
=IF(COUNTIF(INDIRECT("J7:P"&COUNT(I:I)+6),$R$5)<ROW(1:1),"",INDEX(INDIRECT("I7:I"&COUNT(I:I)+6),SMALL(IF(INDIRECT("J7:P"&COUNT(I:I)+6)=$R$5,INDIRECT("A7:A"&COUNT(A:A)+6)-$A$7+1,""),ROW(1:1))))
°}¦C
¤½¦¡Nªøªº^^"

¦A¦¸ÁÂÁ±zªº¼ö¤ßÀ°¦£!·P®¦!

ziv976688

¦^´_ 4# hcm19522
¦pªG±NB:H©MJ:P¸ê®Æ¤¬´«¡A
«hQ7ªº¤½¦¡¥ç¥i²Å¦X»Ý¨D¡F
¦ýR7ªº¤½¦¡µLªk²Å¦X»Ý¨D¡C

½Ð°Ý:
¬°¯à¾A¥Î·sªí®æ¡AR7ªº¤½¦¡À³¦p¦ó­×§ï?
ÁÂÁ±z!

ziv976688

¦^´_ 4# hcm19522
¨D«ü©wªº¹ïÀ³¼Æ­È-2.rar (5.6 KB)

¤U¸ü¦¸¼Æ: 2

2017-9-1 18:16

hcm19522¤j¤j:
§¹¥þ¸Ñ¨M¤F!·P®¦!

¦A½Ð±Ð:
¦pªG±NB:H©MJ:P¸ê®Æ¤¬´«¡AR7»PQ7ªº¤½¦¡­n¦p¦ó­×§ï?
ÁÂÁ±z!

hcm19522

¦^´_ 3# ziv976688

OFFSET(A$6,MATCH(R7,I$7:I$199,),MATCH(R$5,OFFSET(J$6,MATCH(R7,I$7:I$199,),,,7),))

ziv976688

¦^´_ 2# hcm19522
hcm19522¤j¤j:
¶Q¸Ñ¤½¦¡ªº´ú¸Õµª®×¥¿½T!·P®¦!

¤£¦n·N«ä~
Q7 §Q¥ÎR7´Á¼Æ=OFFSET(A$6,R7,MATCH(R$5,OFFSET(J$6,R7,,,7),))
Sheet2!Q7µLªkÅã¥Ü¥¿½Tµª®×¡F
À³¸Ó¦p¦ó­×§ï?
ÁÂÁ±z!

hcm19522

Q7{=INDIRECT(TEXT(SMALL(IF(J$7:P$199=R$5,ROW(I$7:I$199)*1000+COLUMN(B$7:H$199)),ROW(A1)),"!R0C000"),)

R7{=SMALL(IF(J$7:P$199=R$5,I$7:I$199),ROW(A1))

Q7 §Q¥ÎR7´Á¼Æ=OFFSET(A$6,R7,MATCH(R$5,OFFSET(J$6,R7,,,7),))