[µo°Ý] ¨ç¼Æ¤½¦¡¡J¨D³Ì«á¤@²Õ¦XªºAÄæ´Á¼Æ¡C

ziv976688

¨D³Ì«á¤@²Õ¦XªºAÄæ´Á¼Æ.rar (7.12 KB)

¤U¸ü¦¸¼Æ: 2

2016-4-4 03:37

¥H¤U°ÝÃD¡A½Ð¦U¦ì¥ý¶i½ç±Ð!ÁÂÁÂ!

¤U¸ü (201.03 KB)

2016-4-4 03:38

I1=¤H¤u¶ñ¤J·j´M«ü©w¬Y¼Æ¦r(01~49)
I2=¤H¤u¶ñ¤J·j´M¨´¤î´Á¼Æ
J1=¤H¤u¶ñ¤J·j´MÅã¥Ü$I$1¼Æ¦rªº´Á¼Æªº¤Un´Á
J2:J50=Åã¥Ü$I$1¼Æ¦rªº´Á¼Æ¤§¤Un´Áªº«ü©w°t¹ï¼Æ¦r(01~49)
K2=·j´M$I$1©MK2:K50³Ì«á¤@¦¸¦P®ÉÅã¥Üªº»Ý¨D¤½¦¡(¤U©Ô¦ÜK50)
K2=¸Ó³Ì«á²Õ¦X¦³Åã¥Ü=$I$1¼Æ¦rªºAÄæ´Á¼Æ¡C
¨´¤î´Á¼Æªº½d³ò¤º¡A³£¨S¦³¦P®ÉÅã¥Ü«ü©wªº°t¹ï²Õ¦X®É¡A«hK2=""

¨ä¾l...½Ð¸Ô¾\ªþ¹Ï©Î¤W¶Ç½d¨Òªþ¥ó¡C ÁÂÁÂ!

ziv976688

¦^´_ 1# ziv976688

TEST_¨D³Ì«á¤@²Õ¦XªºAÄæ´Á¼Æ.rar (10.28 KB)

¤U¸ü¦¸¼Æ: 1

2016-4-6 20:43

ª¦¤åª¦¤F2¤Ñ¡A²×©ó§ä¨ìÃþ¦ü»Ý¨Dªº¤½¦¡
https://tw.answers.yahoo.com/question/index?qid=20150314000016KK03053

¦]¬°µo°ÝªÌªº½d¨Ò¤w¸g¹O®É²¾°£¤F¡A©Ò¥H¥u¯à±q¤å¦r»¡©ú©M­ã´£³¡ªLª©¥Dªº¸Ñµª¤½¦¡¤¤¡AÃä²qÃä°Â´ê¡A°Â´ê¤F¥b¤Ñ¡A²×©ó§¹¦¨¤F¡C

µª®×¬O²Å¦X¡A¦ý¤½¦¡³o¼Ë­×§ï¡A¤£ª¾¬O§_¥¿½T(µLªk«öF8¤À¬qÀ˵ø)?
·Ð½Ð­ã´£³¡ªLª©¥D«ü¾É¤@¤U¡CÁÂÁ±z!
K2
=IF(J2<>"",MATCH(1,0/(MMULT((OFFSET(INDIRECT("$B$"&1+J$1),1,,I$2-J$1,7)=J2)+(OFFSET($B$2,,,I$2-J$1,7)=I$1),{1;1;1;1;1;1;1})=2)),"")
¤U©Ô¶ñº¡

½Æ»s¨ì
Q2
=IF(P2<>"",MATCH(1,0/(MMULT((OFFSET(INDIRECT("$B$"&1+P$1),1,,O$2-P$1,7)=P2)+(OFFSET($B$2,,,O$2-P$1,7)=O$1),{1;1;1;1;1;1;1})=2)),"")

¦pªG¤½¦¡³o¼Ë§ï¬O¥¿½Tªº
·Ð½Ð­ã´£³¡ªLª©¥D¦A«ü¾É¤@¤U¡J
·íA2´Á¼Æ¤£¬O±q²Ä1´Á¶}©l®É¡A¤½¦¡¤S­n«ç»ò­×¥¿?
¸Ô¦p¡JSheet2
ÁÂÁ±z!

OFFSET   ¥HAÄæ´Á¼Æ¬°¼Ð·Ç¡A¸Õ¼g¥b¤Ñ¡A³£¼g¤£¥X¥¿½Tµª®×^^"

«z~¤p¾Ç¥Í¤£¯àµoµu®ø®§
¥u¯àµ¥Åo^^

hcm19522

L2{=TEXT(MAX((MMULT((OFFSET(B$2,,,I$2,7)=I$1)*1,ROW($1:$7)^0)=1)*(MMULT((OFFSET(B$2,J$1,,I$2,7)=J2)*1,ROW($1:$7)^0)=1)*OFFSET(A$2,,,I$2)),"[=] ")
¤½¦¡-->­«ºâ¿ï¶µ§ï¦Û°Ê

ziv976688

¦^´_ 3# hcm19522
hcm¤j¤j:±z¦n!
·PÁ±zªº¦^À³©M¸Ñµª¡C
¤½¦¡´ú¸Õµ²ªG¬O¥i¥Hªº¡A¤]§â¤£²Å¦X±ø¥óªºÀx¦s®æÅã¥ÜªÅ¥Õ¤F¡CÁÂÁ±z^^

¦³¤@­Ó¤pBug:
·í¶W¹L«ü©w½d³òªºÀx¦s®æ¡A²Å¦X±ø¥óªº²Õ¦X¬O¤£­pºâªº
EX:07ªº½d¨Ò
·í«ü©w¨´¤î½d³ò¬O200´Á¡A
200´Á¦³07¡A¤U2´Áªº202´Á¬O¤£ÄÝ©ó­pºâ½d³òªº¡A
©Ò¥H07©M13¡A18¡A26¡A28¡A34¡A42¡A48ªº³Ì«á²Õ¦X´Á¼Æ¤£¬O200
À³¸Ó¬O  ªÅ¥Õ¡A161¡A197¡A128¡AªÅ¥Õ¡A81¡A189

¸Õ¦bI$2«á­±²K¤W"-J$1"¡Aµª®×´N§¹¥þ²Å¦X¤F¡F¦pªG³o¼Ëªº­×¥¿¤£¬O¥¿³Wªº¤½¦¡¼gªk¡A³Ò¾r±z¤£§[½ç¥¿¡CÁÂÁ±z!
§Ú¹ïMMULT¨ç¼Æ§¹¥þ­¯¥Í¡A©Ò¥H¤½¦¡¥u¯à¥H¤ñ¹ïµª®×¨Ó´ú¸Õ¡A©|½Ð¨£½Ì¡C

ÁÙ¦³¥i§_½Ð±z¦A«ü¾É 2#
·íA2´Á¼Æ¤£¬O±q²Ä1´Á¶}©l®Éªº¤½¦¡¡A­n«ç»ò­×¥¿?¤~¯à³q¥Î¡C
·P®¦^^

hcm19522

{=TEXT(TEXT(MAX((MMULT((OFFSET(B$2,,,I$2,7)=I$1)*1,ROW($1:$7)^0)=1)*(MMULT((OFFSET(B$2,J$1,,I$2,7)=J2)*1,ROW($1:$7)^0)=1)*OFFSET(A$2,,,I$2)),"[>="&I$2&"]!0"),"[=] ")

ziv976688

¦^´_ 5# hcm19522
TEST-2_¨D³Ì«á¤@²Õ¦XªºAÄæ´Á¼Æ.rar (10.15 KB)

¤U¸ü¦¸¼Æ: 4

2016-4-7 16:30

hcm¤j¤j:±z¦n!
·PÁ±zªº§Ö³t¦^À³©M¸Ñµª¡C

´ú¸Õµ²ªG¸Ô¦p¤W¶Çªþ¥ó
¼Ð¥Üª´ºÀ¬õ©³¦âªºÀx¦s®æµª®×¨S¦³¥X¨Ó
³Ò¾r±z¦A½ç¥¿!·P¿E¤£ºÉ~~~

ziv976688

¦^´_ 5# hcm19522

¸É¥R:
hcm¤j¤j:±z¦n!
¤£¦n·N«ä¡A¥i§_½Ð±z±NÅã¥Ü­È§ï¬°¼Æ¦r«¬ºA?
¦]¬°³o¨Ç´Á¼Æ­ÈÁÙ­n¨Ï¥Î©ó­pºâ¦¡¡C
·P®¦^^

hcm19522

­ã¤j³Ç§@ «ÜOK
=IF(J2<>"",IFERROR(MATCH(1,0/(MMULT((OFFSET(INDIRECT("$B$"&1+J$1),1,,I$2-J$1,7)=J2)+(OFFSET($B$2,,,I$2-J$1,7)=I$1),{1;1;1;1;1;1;1})=2)),""),"")
¦h¥[ IFERROR(~~,"")

ziv976688

¦^´_ 8# hcm19522
hcm¤j¤j:±z¦n!
IFERROR¨ç¼Æ2003¨S¦³^^"
¯à§ï¥Î¨ä¥L¨ç¼Æ¶Ü?ÁÂÁ±z!

­ã´£ª©¥Dªº¤½¦¡µLªk®M¥Î©óA2´Á¼Æ>1
^^"

hcm19522

=IF(J2<>"",IF(ISNA(MATCH(1,0/(MMULT((OFFSET(INDIRECT("$B$"&1+J$1),1,,I$2-J$1,7)=J2)+(OFFSET($B$2,,,I$2-J$1,7)=I$1),{1;1;1;1;1;1;1})=2))),"",MATCH(1,0/(MMULT((OFFSET(INDIRECT("$B$"&1+J$1),1,,I$2-J$1,7)=J2)+(OFFSET($B$2,,,I$2-J$1,7)=I$1),{1;1;1;1;1;1;1})=2))),"")