¦p¦óÅýEXCEL¤À¿ì©Î­pºâ¥X¦P¤@Ä椺ªº´X­Ó¼Æ­È

lamlamboy

¤U¸ü (126.26 KB)

2014-11-4 00:32

¦U¦ì°ª¤â, ¥i¥HÀ°¦£¤@¤U, ¦p¦óÅýEXCEL¤À¿ì©Î­pºâ¥X¦P¤@Ä椺ªº´X­Ó¼Æ­È, ¨Ò¦p¤À¿ì©Î­pºâ¥X¦P¤@Ä椤´X­Ó¼Æ­È¤ºªºÁ`©M¬O§_100%, ÁÂÁÂ

ML089

¨C­Ó¼Æ­È³£¦³ %¡A©Ò¥H­p꿦³´X­Ó%´N¯àª¾¹D¦³´X­Ó¼Æ­È

=SUMPRODUCT(LEN(SUBSTITUTE(A2:A15,{"","%"},))*{1,-1})

lamlamboy

ÁÂÁª©¥D¸Ñµª
¥t¥~¥Ñ©ó¨C¦æ³£¥]§t¤å¦r, ¦³¤èªk¯à¤À§O¥X¨C¤@¦æªº¦Ê¥÷¤ñ¬O§_100% ©ÎªÌ¤Ö©ó100%¶Ü?

jomeow

½Ð°Ý¥i¤£¥i¥H¥Î«á­±ªºcolumn¨Ó­pºâ¶Ü...

ML089

¦^´_ 3# lamlamboy

¥t¥~¥Ñ©ó¨C¦æ³£¥]§t¤å¦r, ¦³¤èªk¯à¤À§O¥X¨C¤@¦æªº¦Ê¥÷¤ñ¬O§_100% ©ÎªÌ¤Ö©ó100%¶Ü?

²Î­p¦³¦h¤Ö­Ó 100%
C2 =COUNTIF(A:A, "*100%*")

­Y¬O¨C¦C­pºâ
B2=IF(COUNTIF(A2, "*100%*"), "Y", "N")

¨Ì½d¨Ò 100%³£¦b³Ì¥ªÃä
B2 =IF(LEFT(A2)="100%", "Y", "N")

jomeow

¦^´_ 5# ML089

¦ý°ÝÃD¬O¨C­ÓCELL¸Ì­±¦³¤@­Ó©Î¨â­Ó%.. ­n«ç¤~¥i¥H§ä¥X¨Ã­pºâ¨C­ÓCELLªºÁ`¼Æ©O..

ML089

¦^´_ 6# jomeow

½Ð°Ñ¦Ò 2#ªº¤èªk

samwang

¦^´_ 7# ML089

ÂǥѦ¹ÃD½Ð°Ýª©¥D¦pªþ¥óB2 ªº¤½¦¡¬°¦ó?

ML089

¦^´_ 8# samwang

¥i¥H§Q¥ÎªÅ®æ±N¼Æ¦r ¤ÀÂ÷ - ¨ú¥X - ¦X­p

=SUMPRODUCT(--TEXT(MID(SUBSTITUTE(A2," ",REPT(" ",99)),ROW($1:$9)*99-98,99),"[<>];!0;!0;!0"))

samwang

¦^´_ 9# ML089

ÁÂÁ¡A¦A½Ð°Ý
1. TEXT( value, format_text) >> format_text  "[<>];!0;!0;!0" ¬O¦p¦ó³]©w¦³µL¥i°Ñ·Ó

{"abc      ";"   30%        ";"      aabbcc    ";"            50%";"";"";"";"";"";"";""}
{"0";"0.3";"0";"0.5";"0";"0";"0";"0";"0";"0";"0"}