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

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

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

«e3¤j.png
2019-9-19 04:16
      
«e3¤p.png
2019-9-19 04:17

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

¦^´_ 11# ML089
Mª©¥D : ±z¦n!
©Ò¦³¤£¤~¯à·Q¨ìªº»Ý¨D¡A³£¤w¶êº¡¹F¦¨¡C
±zªº­@¤ß¨ó§U©M¼ö¤ß«ü¾É~¤£¤~»Ê·P¤­¤º~¦b¦¹«ôÁÂ!!!!!!!!!

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2019-9-24 17:02 ½s¿è

¦^´_ 9# ziv976688

AQ3 =TEXT(MAX(--TEXT(INDEX(INDIRECT($AP3),AQ$1,),"[>="&IF(N(AP$1),AO3,999)&"]-9;;0")),"0;!N;0")

AR3 =IF(AQ3="N","",SUBSTITUTE(TEXT(SUM(LARGE(IF(INDEX(INDIRECT($AP3),AR$1,)=AQ3*1,INDEX(INDIRECT($AP3),2,),0),{1,2,3,4})*10^{0,2,4,6}),"00"&REPT("!,00",4)),"00,",))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

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

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¹Ï¤ù ~
¥¼©R¦W_1.png
2019-9-24 16:18
        
¥¼©R¦W_2.png
2019-9-24 16:19

´ú¸Õªþ¥ó : 539_§À¼Æ±Æ§Ç-V2.rar (4.06 KB)

TOP

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

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

¦^´_ 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

¥»©«³Ì«á¥Ñ 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¼Ó°ÝÃD§Ú¦b§ä®É¶¡¬Ý¬Ý
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¬O«D·í±Ð¨|¡AÆg¬ü§@ĵ±§¡C
ªð¦^¦Cªí ¤W¤@¥DÃD