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

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

ÁÙ¤£¬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
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

­n¤@¦¡¥þ³¡©Ô¨ì©³¡A»Ý­n±N¸ê®ÆÄæ¦ì¹ï»ô¡A¤£µM¤½¦¡·|¤S¯ä¤Sªø¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2019-9-9 20:40 ½s¿è

¦^´_ 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)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2019-9-11 10:43 ½s¿è

¦^´_ 14# ziv976688

(1)        AQ3 =MAX(--TEXT(INDEX($B:$G,$AP3,),"[>="&IF(AQ$1="",AO3,999)&"]!0"))
        AR3 =INDEX($B:$G,2,MATCH(AQ3,INDEX($B:$G,$AP3,),))
        °}¦C¤½¦¡

(2)        AQ3 =MAX(--TEXT(INDEX($B:$G,$AP3,),"[>="&IF(AQ$1="",AO3,999)&"]!0"))
        AR3 =SUBSTITUTE(TEXT(SUM(LARGE(IF(INDEX($B:$G,$AP3,)=AQ3,$B$2:$G$2,0),{1,2,3,4,5})*10^{0,2,4,6,8}),"00"&REPT("!,00",4)),"00,",)
        °}¦C¤½¦¡

(1)¦¡ªºAR7·|Åã¥Ü 21  (¼Æ¦r)
(2)¦¡ªºAR7·|Åã¥Ü 21,22 (¤å¦r)

¤ñ¤j¤p&­«½Æ¨ú¨ä1ªº¨ç¼Æ¤½¦¡(2).rar (20.94 KB)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 17# ziv976688

¨ú¤j¦Ü¤p
AQ3 =MAX(--TEXT(INDEX($B:$G,$AP3,),"[>="&IF(AQ$1="",AO3,999)&"]!0"))

¨ú¤p¦Ü¤j
AQ3 =MIN(--TEXT(INDEX($B:$G,$AP3,),"[<="&IF(AQ$1="",AO3,-1)&"]!9!9"))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 20# ziv976688

¦]¬°¦¸¼ÆªºÅã¥Ü­È9999¡A·|Åý¤ñ¤j¤pªº§@¥Î¥¢¯u¡A©Ò¥H¬O§_¯à¤£­nÅã¥Ü"9999"¡A¦Ó¯àÅã¥Ü"0"¡A
·íµM¯àÅã¥Ü"ªÅ¥Õ"¬O³Ì¦n¤£¹L¤F~¦ý¤£´±±j¨D¡A¥]¬A«e3¤jªº¦¸¼ÆÅã¥Ü"0"¯à§ïÅã¥Ü"ªÅ¥Õ"¥ç¦P¡C


§Ú²ßºD¥Î 999 9999 ³o¼Ëªº¼Æ¦r¥Nªí ·¸¦ì
¤½¦¡¬O¬Ý«e­±ªº¼Æ¦r¥ÎTEXTªº¼Æ¦r¤j¤p¿z¿ï¥\¯à¨Ó¨M©w­þ¨Ç¼Æ¦r­n¯d¤U¡A­Y«e­±¬O "" ®É¤½¦¡´N¨S¦³§PÂ_¨Ì¾Ú
·íµM§AÁ¿ªº³£¯à°µ¡A¤½¦¡ÅܽÆÂø
¤£²ßÅw¬Ý¨ì 9999 ¤]¥i¥H¥Î Àx¦s®æ®æ¦¡¥\¯à¨Ó¾B½ª "[=9999] ;0"¡A©Î ³]©w®æ¦¡¤Æ±ø¥ó ·í9999®É¦rªºÃC¦â»P©³¦â¬Û¦P¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¦Û¤v®`¦Û¤v¡A²ö¹L©ó¶ÃµoµÊ®ð¡C
ªð¦^¦Cªí ¤W¤@¥DÃD