ziv976688

¦^´_ 13# ML089
Mª©¥D:
ÁÂÁ±zªº­@¤ß½ç±Ð!
¦³½d¨ÒÀɤè«K³\¦h~¤£¤~·|¦n¦n¬ã¨s~·P®¦........^^

ML089

¦^´_ 12# ziv976688

­è¦n¤»¤é³£¦³¨Æ¨S¦³¿ìªk¥ß§Y¦^ÂÐ
(1)
AQ3 =MAX(--TEXT(INDEX($B:$G,$AP3,),"[>="&IF(AO3,AO3,999)&"]!0"))
AR3 =INDEX($B:$G,2,MATCH(AQ3,INDEX($B:$G,$AP3,),))
½Æ»s AQ3:AR3
¶K¤W AQ4:AR7
½Æ»s AQ3:AR7
¶K¤W AS3:AV7

(2)
½Æ»s AQ3:AR3
¶K¤W¦U°Ïªº¥ª¤W¨¤Àx¦s®æ
±N¤½¦¡¤¤ªº $B:$G ­×§ï¬°
  ¤ì($H:$P)
  ¤ô($Q:$X)
  ¤õ($Y:$AF)
  ¤g($AG:$AN)                       

¦p(1) ½Æ»s¤Î¶K¤W ¸Ó°Ï¤½¦¡

(3) ·íµM¤½¦¡¤]¥i¥H¦Û°Ê§ì¨ú ²Ä¤@¦C¤¤¦p¤Uªº¤å¦r¡A§Q¥ÎINDIRECTÂର°Ï°ì¡A¦ý¤£»Ý¥I¥X¶Wªø¤½¦¡§@¬°¥N»ù¡A«Øij¥Î¤â°Ê­×§ï¤ñ¸û®e©öºûÅ@¤½¦¡¡C
  ª÷($B:$G)
  ¤ì($H:$P)
  ¤ô($Q:$X)
  ¤õ($Y:$AF)
  ¤g($AG:$AN)

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

¤U¸ü¦¸¼Æ: 2

2019-9-9 20:40

ziv976688

¦^´_ 10# ML089
Mª©¥D :±z¦n!
AQ3 :AR3  ¨â¦¡¤U©Ô =>µª®×¥¿½T
½Æ»s AQ3:AR7 ¦ÜAS3:AV7 =>AS3:AV7³£¸òAQ3:AR7 ¬Û¦P =>µª®×¤£¥¿½T
¸U¤À·PÁ±z¤@¦Aªº¼ö¤ß«ü¾É^^
===========================
Mª©¥D :
¬°¤FÅý(¶¶À³)¤½¦¡¦n½s¼g
¤£¤~±Nªí®æ·L½Õ­×§ï¦p¤U~
AP9°Å¤U¡A¶K¤WAP10
AP3:AP10ªºÀx¦s®æ¦¡§ï¬°=>¦Û­q=>"¦C"#
¸Ô¦pªþ¥ó : TSET_1.rar (16.52 KB)

¤U¸ü¦¸¼Æ: 3

2019-9-6 23:42

ÂԽбz°Ñ¦Ò!
ÁÂÁ±z!±ß¦w^^

ML089

­n¤@¦¡¥þ³¡©Ô¨ì©³¡A»Ý­n±N¸ê®ÆÄæ¦ì¹ï»ô¡A¤£µM¤½¦¡·|¤S¯ä¤Sªø¡C

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

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))

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

¦³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

³Ì¤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

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