[µo°Ý] ¤ñ¤j¤p&­«½Æ¨ú¨ä1ªº¨ç¼Æ¤½¦¡¡C

ziv976688

ªþ¥ó : ¤ñ¤j¤p&­«½Æ¨ú¨ä1ªº¨ç¼Æ¤½¦¡.rar (16.4 KB)

¤U¸ü¦¸¼Æ: 7

2019-9-2 07:29

¸Ô¦pªþ¥ó¤º½d¨Òªº»¡©ú¡C
ÂԽЦU¦ì¤j¤j°Ñ¦Ò©M½ç±Ð ! ÁÂÁ¦U¦ì !

ziv976688

»Ý¨D1_
Sheet1!AQ : BW¦U¦¸¼ÆÄæ¦p»¡©ú±ø¥óÅ޿誺¼Æ­È¡C
»Ý¨D2_
Sheet2!AQ : BW¦U¦¸¼ÆÄæ¦p»¡©ú±ø¥óÅ޿誺¼Æ­È¡C
ÁÂÁÂ !

ziv976688

¸É¥R :¥u­n½s¼g¤U¦CÀx¦s®æªº¤½¦¡´N¥i¥H¤F¡A¨ä¾lÀx¦s®æªº¤½¦¡¡A¤p§Ì¦Û¦æ®M¥Î¶ñº¡¡CÁÂÁ !
Sheet1ªº
AQ3=B9:G9ªº³Ì¤j¼Æ­È¡A­«½Æ¨ú¨ä1                               
AS3=B9:G9ªº¦¸¤j¼Æ­È¡A­«½Æ¨ú¨ä1
AU3=B9:G9ªº²Ä¤T¤j¼Æ­È¡A­«½Æ¨ú¨ä1

Sheet2ªº
AQ3=B9:G9ªº³Ì¤p¼Æ­È¡A­«½Æ¨ú¨ä1
AS3=B9:G9ªº¦¸¤p¼Æ­È¡A­«½Æ¨ú¨ä1
AU3=B9:G9ªº²Ä¤T¤p¼Æ­È¡A­«½Æ¨ú¨ä1

ziv976688

­«·s¾ã²z»Ý¨D :
»Ý¨D1_
Sheet1ªº
AQ3=B9:G9ªº³Ì¤j¼Æ­È¡A­«½Æ¨ú¨ä1                              
AS3=B9:G9ªº¦¸¤j¼Æ­È¡A­«½Æ¨ú¨ä1 (¦pªG¨S¦³¦¸¤j­È®É¡A«hAS3Åã¥ÜªÅ¥Õ)
AU3=B9:G9ªº²Ä¤T¤j¼Æ­È¡A­«½Æ¨ú¨ä1 (¦pªG¨S¦³²Ä¤T¤j­È®É¡A«hAU3Åã¥ÜªÅ¥Õ)

»Ý¨D2_
Sheet2ªº
AQ3=B9:G9ªº³Ì¤p¼Æ­È¡A­«½Æ¨ú¨ä1
AS3=B9:G9ªº¦¸¤p¼Æ­È¡A­«½Æ¨ú¨ä1(¦pªG¨S¦³¦¸¤p­È®É¡A«hAS3Åã¥ÜªÅ¥Õ)
AU3=B9:G9ªº²Ä¤T¤p¼Æ­È¡A­«½Æ¨ú¨ä1(¦pªG¨S¦³²Ä¤T¤p­È®É¡A«hAU3Åã¥ÜªÅ¥Õ)

¥H¤W  ¸Û½Ð¦U¦ì¤j¤j½ç±Ð !ÁÂÁ¦U¦ì !

ziv976688

¦³ª¦¤å§ä¨ì
https://tw.answers.yahoo.com/question/index?qid=20141006000015KK02830
¦ý¤£ª¾«ç»ò®M¥Î¨ì²Ä2¤j©Î¤p©M²Ä3¤j©Î¤p^^"

ziv976688

³Ì¤jSheet1!AQ3=MAX($B$9:$G$9)¡F³Ì¤pSheet2!AQ3=MIN($B$9:$G$9) =>¥»¨Ó¥u·|³o²Õ¡F

¦¸¤jSheet1!AS3=IF(MAX(IF(B9:G9<MAX(B9:G9),B9:G9))>0,MAX(IF(B9:G9<MAX(B9:G9),B9:G9)),"") °}¦C¡F
¦¸¤pSheet2!AS3=IF(MIN(IF(B9:G9>MIN(B9:G9),B9:G9))>0,MIN(IF(B9:G9>MIN(B9:G9),B9:G9)),"") °}¦C¡F

²Ä3¤jSheet1!AU3 & ²Ä3¤pSheet2!AU3  => =>§ì¯}ÀY¡AÁÙ¬O®M¼g¤£¥X¨Ó^^///

¡¹PS : ¬O§_¦¸¤j(¤p)©M²Ä3¤j(¤p)³£¯à¥H¤@¯ë¤½¦¡(³¡­nÂí¦C)§e²{?

¸Û½Ð°ª¤â½ç±Ð !  ÁÂÁ !

ziv976688

¦³Google¨ìÃþ¦ü¤½¦¡¡A¸g®M¼g«á~
¦¸¤jSheet1!AS3=IF(MAX(IF(B9:G9<AQ3,B9:G9))>0,MAX(IF(B9:G9<AQ3,B9:G9)),"") °}¦C
¤T¤jSheet1!AU3=IF(MAX(IF(B9:G9<AS3,B9:G9))>0,MAX(IF(B9:G9<AS3,B9:G9)),"")  °}¦C

¦¸¤pSheet2!AS3=IF(MIN(IF(B9:G9>AQ3,B9:G9))>0,MIN(IF(B9:G9>AQ3,B9:G9)),"")  °}¦C
¤T¤pSheet2!AU3=IF(MIN(IF(B9:G9>AS3,B9:G9))>0,MIN(IF(B9:G9>AS3,B9:G9)),"")  °}¦C
¥i±¤³£¬O°}¦C¤½¦¡¡C

¦³ª¦¨ì"­ã´£ª©¥D"¦^µªªº¬ÛÃöªº¤@¯ë¤½¦¡¸Ñµª~~
https://tw.answers.yahoo.com/question/index?qid=20110720000015KK10105
¥u¬O§ÚÁÙ¨S¦³®©¥X¦p¦ó±Nª½¦¡(ROW)¤½¦¡¡A§ï¬°¾î¦¡(column)¤½¦¡?
¥u¯à¦AºCºC¬ã¨s~@~@~

PS :
¨ä¤¤¦³¤@­Ó¤½¦¡¡A¦n¹³«Ü²¼ä¡A¥u¬O¤£ª¾¦p¦ó§ï^^"
¡i=SUMPRODUCT(1/COUNTIF($A$1:$A$11,$A$1:$A$11)) «D°}¦C
³o¦¡¤l¤§«e¬Ý¹L¡A¤]´¿¦¬¿ý¡A¬OÃø±o¤½¦¡¡C ¡j

ziv976688

¥J²Ó¬ãŪhttps://tw.answers.yahoo.com/question/index?qid=20110720000015KK10105«á~
=SUMPRODUCT(1/COUNTIF($A$1:A$11,$A$1:A$11)) «D°}¦C
¤W­z¤½¦¡¥u¬O¦b²Î­p½d³ò¤º¿z¿ï­«½Æ­È«áªº¼Æ­È­Ó¼Æ¥[Á`¡A¤£¬O±Æ¤j¤p¡C

±Æ¤j¤pªº¤@¯ë¤½¦¡
¦¸¤jSheet1!AS3=IF(MIN($A$9:A9)>=MIN($B9:$G9),"",LARGE($B9:$G9,COUNTIF($B9:$G9,">="&A9)+2))
¤T¤jSheet1!AU3=IF(MIN($A$9:A9)>=MIN($B9:$G9),"",LARGE($B9:$G9,COUNTIF($B9:$G9,">="&A9)+3))

¦¸¤pSheet2!AS3=IF(MAX($A$9:A9)>=MAX($B9:$G9),"",SMALL($B9:$G9,COUNTIF($B9:$G9,"<="&A9)+2))
¤T¤pSheet2!AU3=IF(MAX($A$9:A9)>=MAX($B9:$G9),"",SMALL($B9:$G9,COUNTIF($B9:$G9,"<="&A9)+3))

ziv976688

¤£¦n·N«ä¡A8¼Ó±Æ¤j¤pªº¤@¯ë¤½¦¡¨S¦³¿z¿ï­«½Æ­È
­×§ï¬°
¦¸¤jSheet1!AS3
=IF(MIN($B$9:$G$9)=AQ3,"",LARGE($B9:$G9,COUNTIF($B9:$G9,">="&AQ3)+1))
¤T¤jSheet1!AU3
=IF(OR(MIN($B$9:$G$9)=AS3,AS3=""),"",LARGE($B9:$G9,COUNTIF($B9:$G9,">="&AS3)+1))

¦¸¤pSheet2!AS3
=IF(MAX($B$9:$G$9)=AQ3,"",SMALL($B9:$G9,COUNTIF($B9:$G9,"<="&AQ3)+1))
¤T¤pSheet2!AU3
=IF(OR(MAX($B$9:$G$9)=AS3,AS3=""),"",SMALL($B9:$G9,COUNTIF($B9:$G9,"<="&AS3)+1))

ML089

ÁÙ¤£¬O«Ü§¹¬ü¡A°Ñ¦Ò¬Ý¬Ý
°}¦C¤½¦¡
AQ3 =MAX(--TEXT(INDEX(INDIRECT(MID($AQ$1,3,3)),MID($AP3,2,3),),"[>="&IF(AO13,AO13,999)&"]!0"))
AR3 =INDEX(INDIRECT(MID($AQ$1,3,3)),2,MATCH(AQ3,INDEX(INDIRECT(MID($AQ$1,3,3)),MID($AP3,2,3),),))
¨â¦¡¤U©Ô

½Æ»s AQ3:AR7 ¦ÜAS3:AV7