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

[µo°Ý] ¤½¦¡Àu¤Æªº°ÝÃD¡C

[µo°Ý] ¤½¦¡Àu¤Æªº°ÝÃD¡C

      
±NAP13:BX20ªº®æ¦¡§ï¬°AP1:CE7ªº®æ¦¡«á¡A¤½¦¡¹ïÀ³½Õ¾ã¬°~~
«e¤T¤j¡J
AQ3=MAX(--TEXT(INDEX($B:$G,AQ$1,),"[>="&IF(AQ$1="",AO3,999)&"]!0"))
AS3=MAX(--TEXT(INDEX($B:$G,AS$1,),"[>="&IF(AS$1<>"",AQ3,999)&"]!0"))
AU3=MAX(--TEXT(INDEX($B:$G,AU$1,),"[>="&IF(AU$1<>"",AS3,999)&"]!0"))
¨ä¾l......Ãþ±À¡C

«e¤T¤p¡J
AQ3==MIN(--TEXT(INDEX($B:$G,AQ$1,),"[<="&IF(AQ$1="",AO3,-1)&"]!99!9"))
AS3=MIN(--TEXT(INDEX($B:$G,AS$1,),"[<="&IF(AS$1<>"",AQ3,-1)&"]!99!9"))
AU3 =MIN(--TEXT(INDEX($B:$G,AU$1,),"[<="&IF(AU$1<>"",AS3,-1)&"]!99!9"))
¨ä¾l......Ãþ±À¡C

¦]¬°¤£¯à½Æ»sAQ3:AR3ª½±µ¶K¤WAS3©MAU3¡A¥u¯à½Æ»sAS3:AT¶K¤WAU3
©Ò¥H½Ð°Ý¡J
¤½¦¡¯à§_¦AÀu¤Æ¶Ü?
ÁÂÁÂ!
´ú¸Õªþ¥óÀÉ®× : ¤½¦¡Àu¤Æªº°ÝÃD.rar (20.23 KB)

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2019-9-24 04:21 ½s¿è

¦^´_ 1# ziv976688
1¼Óªº»Ý¨D¥i©¿²¤¤£±j¨D~¤@¦]¥u¬O¦h¼g1­ÓAS3ªº¤½¦¡¡C¤]¤£¬O¦h³Â·Ðªº¨Æ¡A¤G¦]¦³§ó­«­nªº°ÝÃD¨D¸Ñ~
¤­¦æªº«e3¤p¤u§@ªíªºAR3
=IF(AQ3=999,"",SUBSTITUTE(TEXT(SUM(LARGE(IF(INDEX($B:$G,AR$1,)=AQ3,$B$2:$G$2,0),{1,2,3,4,5})*10^{0,2,4,6,8}),"00"&REPT("!,00",4)),"00,",))
½Ð°Ý: ¤½¦¡¤¤ªº1,2,3,4,5©M0,2,4,6,8©M4¥Nªí¤°»ò·N¸q?
PS :¤­¦æªºÄæ¼Æ¤ñ=6:9:8:8:8
¦p¤U¹Ï :


¦]¬°·í®æ¦¡§ï¬°¦p§À¼Æªº«e3¤p¤u§@ªí¡A¨äAR3¤½¦¡´N¥²¶·§ï¬°
=IF(AQ3=999,"",SUBSTITUTE(TEXT(SUM(LARGE(IF(INDEX($B:$E,AR$1,)=AQ3,$B$2:$E$2,0),{1,2,3})*10^{0,2,4}),"00"&REPT("!,00",4)),"00,",))
¤~¯àÅã¥Ü¥¿½Tªºµª®×¡C
½Ð°Ý: AR3ªº¤½¦¡§ï¦¨³o¼Ë¥¿½T¶Ü?
PS :§À¼ÆªºÄæ¼Æ¤ñ=4:4:4:4:4:4:4:4:4:3
¦p¤U¹Ï :


¦]¬°539ÁÙ¦³
¥Í¨vªºÄæ¦ì¤ñ=3:3:3:3:3:3:3:3:4:4:4:3
¤K¨öªºÄæ¦ì¤ñ=4:5:5:5:5:5:5:5

¥t¥~¤j¼Ö³z49¦³
¤­¦æªºÄæ¦ì¤ñ=8:12:10:10:9
§À¼ÆªºÄæ¦ì¤ñ=5:5:5:5:5:5:5:5:5:4
¥Í¨vªºÄæ¦ì¤ñ=4:4:4:4:4:4:4:4:4:4:5:4
¤K¨öªºÄæ¦ì¤ñ=6:6:7:6:6:6:6:6

©Ò¥H½Ð«ü¾É©M»¡©ú¤½¦¡¤¤¦U¬qªº·N²[¡A¥H§Q¤£¤~¯à¦]À³®æ¦¡ªºÅܤƦӶñ¤J¾A·í¥¿½Tªº¤½¦¡¡C
ÁÂÁÂ!

´ú¸ÕÀÉ : ´ú¸ÕÀÉ.rar (42.52 KB)

TOP

¦^´_ 1# ziv976688

­ì°ÝÃD¥ý¦^ÂСA
AQ3­ì¦¡ =MAX(--TEXT(INDEX($B:$G,AQ$1,),"[>="&IF(AQ$1="",AO3,999)&"]!0"))

AQ3§ï¬° =MAX(--TEXT(INDEX(INDIRECT($AP3),AQ$1,),"[>="&IF(N(AP$1),AO3,999)&"]!0"))
-----------------------
AR3­ì¦¡ =SUBSTITUTE(TEXT(SUM(LARGE(IF(INDEX($B:$G,AR$1,)=AQ3,$B$2:$G$2,0),{1,2,3,4,5})*10^{0,2,4,6,8}),"00"&REPT("!,00",4)),"00,",)

AR3§ï¬° =SUBSTITUTE(TEXT(SUM(LARGE(IF(INDEX(INDIRECT($AP3),AR$1,)=AQ3,INDEX(INDIRECT($AP3),2,),0),{1,2,3,4,5})*10^{0,2,4,6,8}),"00"&REPT("!,00",4)),"00,",)

³o¼Ë AQ3¡GAR3 ¥i¥H©¹¤U½Æ»s¡A©¹¥k½Æ»s¡A
¤]¥i¥H AQ3¡GAV7 ¶ôª¬½Æ»s¨ì AX3 ¡BBE3

»¡©ú¡G
¸ê®Æ±Æ¦C¥¿±`¤Æ¥i¥HÅý¤½¦¡¸û¨ã¦³³q¥Î©Ê
­ì¥ý $B:$G ¥i¥H¥Ñ AP3¤¤ªº¤å¦rÂà´«¡A¤½¦¡¬° INDIRECT($AP3)
­ì¥ý $B2:$G2 ¥i¥H¥Ñ AP3¤¤ªº¤å¦rÂà´«¡A¤½¦¡¬° INDEX(INDIRECT($AP3),2,)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

2¼Ó°ÝÃD§Ú¦b§ä®É¶¡¬Ý¬Ý
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2019-9-24 13:42 ½s¿è

¦^´_ 4# ML089
Mª©¥D : ±z¦n!
·PÁ±z3¼ÓªººëÅP¸ÑÃD¤½¦¡~¹ê¦b¬O¤Ó¯«¤Ó¤è«K¤F^^
......................................................................................................................
¦]¬°¥Ñ3¼Óªº¶Q¸Ñ¡A±oª¾APÄ檺¤å¦r¡A¥i¥H¥Î"INDIRECT"Âà´«~(¥»¨Ó¤£¤~¬O¥H¬°$B$2:$G$2µL¨ç¼Æ¥iÂà´«)~
©Ò¥H2¼Óªº¹Ï¤ù©Mªþ¥ó½d¨Òªº§À¼ÆÀɮפ§AP3:AP12¡A¤]§ï¬°B:E¡FF:I¡F......¡FAL:AN~¦pªþ¥ó : ´ú¸ÕÀÉ_V1.rar (45.92 KB)
ÁÂÁ±z^^

PS: ¥Ø«e¨Ì3¼Óªº¶Q¸Ñ
¤­¦æªºAR3  =SUBSTITUTE(TEXT(SUM(LARGE(IF(INDEX(INDIRECT($AP3),AR$1,)=AQ3,INDEX(INDIRECT($AP3),2,),0),{1,2,3,4,5})*10^{0,2,4,6,8}),"00"&REPT("!,00",4)),"00,",)

§À¼ÆªºAR3  =SUBSTITUTE(TEXT(SUM(LARGE(IF(INDEX(INDIRECT($AP3),AR$1,)=AQ3,INDEX(INDIRECT($AP3),2,),0),{1,2,3})*10^{0,2,4}),"00"&REPT("!,00",4)),"00,",)

TOP

¦^´_ 2# ziv976688

¤­¦æªº«e3¤p¤u§@ªíªºAR3
=IF(AQ3=999,"",SUBSTITUTE(TEXT(SUM(LARGE(IF(INDEX($B:$G,AR$1,)=AQ3,$B$2:$G$2,0),{1,2,3,4,5})*10^{0,2,4,6,8}),"00"&REPT("!,00",4)),"00,",))
½Ð°Ý: ¤½¦¡¤¤ªº1,2,3,4,5©M0,2,4,6,8©M4¥Nªí¤°»ò·N¸q?
   


LARGE(IF(INDEX($B:$G,AR$1,)=AQ3,$B$2:$G$2,0),{1,2,3,4,5}) ¨ä¤¤ªº {1,2,3,4,5} ´N¬O¤½¦¡¤@¦¸§ì¥X«e5¤j¼Æ­È¡A
³o5­Ó¼Æ¦r¦U§O­¼©ó 10^{0,2,4,6,8} (³o¤]¬O5­Ó­¼¼Æ {10^0, 10^2, 10^4, 10^6, 10^8} ¡A¤]´N¬O {1,  100, 10000, 1000000, 100000000})

°²³]«e5¤j¼Æ¦r¬° {20, 15, 0, 0, 0}  ¦A­¼©ó 10^{0,2,4,6,8} ´NÅܦ¨ {20, 1500,0,0,0} ¦A±N5¼Æ¦r¦X­p«á¬° 1520¡A
¥Î TEXT±N¼Æ¦r¤À¹j¶}¨Ó TEXT(1520, ,"00"&REPT("!,00",4)) Åܦ¨ "00,00,00,15,20"
¥Î SUBSTITUTE,"00,00,00,15,20" ,"00,",) ¥h°£«e­±ªº "00,"  ´NÅܦ¨ "15,20"
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 5# ziv976688


"00"&REPT("!,00",4) ®i¶}¬O "00,00,00,00,00" ¬O5²Õ 00¡A©ó«e­±ªº {1,2,3,4,5} ¨ú5­Ó¼Æ¦r¬O¹ïÀ³ªº¡C
·íµM "00"&REPT("!,00",4) ¹ïÀ³ {1,2,3} ¨ú3­Ó¼Æ¤l¤]¤£·|¦³¿ù¡A¦]¬°3­Ó¼Æ¦r¦X­p«á¤]´N¬O¤@­Ó¼Æ¦r¡A³»¦h«e­±·|¦h¥X "00,00," ¡A³Ì«áSUBTITUTE·|±N¦h¾lªº "00," §R°£
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 7# ML089
Mª©¥D : ±z¦n!
·PÁ±zªº­@¤ß»¡©ú»P«ü¾É~¤£¤~ÁA¸Ñ¤F^^
¨ä¾l¤£¦PÄæ¦ì¤ñªº¤½¦¡¡A¤£¤~À³¸Ó¥i¥H¦Û¦æ½Õ¾ã¤F~·P®¦...........n+1

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2019-9-24 16:34 ½s¿è

¦^´_ 7# ML089

Mª©¥D : ±z¦n!
¤£¦n·N«ä¡A«e3¤j¤½¦¡¦³1­Ó¦a¤è¡A¤£ª¾¯à§_½Õ¾ã?
ÁÂÁ±z!

¤å¦r»¡©ú :
·í¨S¦³¦¸¤jªº¦¸¼Æ­È(AS3)©Î²Ä¤T¤jªº¦¸¼Æ­È(AU3)®É¡A§Æ±æ(AS3)©Î(AU3)¯àÅã¥Ü"N"~
¦]¬°AS3©ÎAU3Åã¥Ü0¡A¦b¬Y±¡ªp¤U(¤×¨ä¬O¦b¤ñ¹ïÄæ¦ì¼Æ¸û¤Ö®É¡A±`·|µo¥Í)¡AAT3©ÎAV3·|²£¥Í"¿ù»~­È"¡C
¸Ô¦p¤U¦C¹Ï¤ù ~
        
´ú¸Õªþ¥ó : 539_§À¼Æ±Æ§Ç-V2.rar (4.06 KB)

TOP

¸É¥R :
¦pªGÅã¥Ü¤å¦r¡A·|¤ñ¥ô¦ó¯Â¼Æ­È³£¤j¦Ó¤£¥i¦æ®É¡A¬O§_¯àÅã¥Ü"­t­È"~EX : "-999"¡C
ÁÂÁ±z!

TOP

        ÀR«ä¦Û¦b : ª¾ÃÑ­n¥Î¤ßÅé·|¡A¤~¯àÅܦ¨¦Û¤vªº´¼¼z¡C
ªð¦^¦Cªí ¤W¤@¥DÃD