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

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

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

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

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À°¦£!ÁÂÁÂ¥ý!

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

TOP

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

TOP

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

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

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

TOP

¦^´_ 3# ziv976688

OFFSET(A$6,MATCH(R7,I$7:I$199,),MATCH(R$5,OFFSET(J$6,MATCH(R7,I$7:I$199,),,,7),))
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

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

TOP

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),))
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

        ÀR«ä¦Û¦b : ¨Ã«D¦³¿ú¾{¬O§Ö¼Ö¡A°Ý¤ßµL·\¤ß³Ì¦w¡C
ªð¦^¦Cªí ¤W¤@¥DÃD