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

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

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

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

¥»©«³Ì«á¥Ñ 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«ä¦Û¦b : ·O´d¨S¦³¼Ä¤H¡A´¼¼z¤£°_·Ð´o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD