¦p¦ó§ä¥X¦r¦ê¤¤§t"3" ¨Ã¥BÅã¥Ü¥X¨Ó?

lucky12345

A1=0,1,0,2,0,0,0,2,0,0     B1=0   
A2=0,1,0,1,0,0,3,0,0,0     B2=1   
A3=0,2,0,1,0,0,3,0,3,0     B3=2  
¦p¤W©Ò­z  §ä¥X§t¦³"3"ªº¦r¦ê     B1=0          B2=1    A2¤¤¦³¤@­Ó"3"          B3=2   A3¤¤¦³¨â­Ó"3"
½Ð¦U¦ì¤j¤j¤£§[«ü¾É  ÁÂÁÂ

hcm19522

http://blog.xuite.net/hcm19522/twblog/365650073

K_Wing

¼Ó¥D­Y¯à¶ñ¤JExcel
¦A¾e¹Ïªº¸Ü
·|¸û®e©ö²z¸Ñ
=COUNTIF(A1:K1,"=3")

¤U¸ü (43.09 KB)

2015-12-16 20:18

A1:K1¬°«ü©w½d³ò§ä¥X«ü©w¼Æ­È(3)

lucky12345

¦^´_ 2# hcm19522

¾Ç¨ì¤F...
·PÁ¤j¤jªº«ü¾É.......

­ã´£³¡ªL

=MMULT(LEN(SUBSTITUTE(","&A1&",",","&3&",",{11,1})),{1;-1})
=SUM(LEN(SUBSTITUTE(","&A1&",",","&3&",",{11,1}))*{1,-1})

lucky12345

¦^´_ 5# ­ã´£³¡ªL
·PÁÂÁ¤j¤jªº«ü¾É...........

yen956

¦^´_ 2# hcm19522
¤j¤j§A¦n, ¥Î F9 ¬ã¨s¤F¥b¤Ñ²×©óµy·L¬ÝÀ´¤F, ¦¬¤U, ÁÂÁÂ!!
¤£¹L¤½¦¡À³µy§ï¦p¤U, §_«h²Ä1­Ó3 ·|§ì¤£¨ì,
=SUMPRODUCT((TRIM(MID(SUBSTITUTE(","&A2,",",REPT(" ",50)),ROW($2:$19)*50-49,50))="3")*1)

hcm19522

¦^´_ 7# yen956
=SUMPRODUCT((TRIM(MID(SUBSTITUTE(B2,",",REPT(" ",50)),ROW($1:$19)*50-49,50))="3")*1)
ROW($2:$19)§ïROW($1:$19) µL¶·¥[ ","&

yen956

¦^´_ 8# hcm19522
¤j¤j§A¦n!!
¯u¬O©êºp, ¥i¯à¬O§Ú¦b´ú¸Õ®É, °Ê¨ì
­ì¤½¦¡ ROW($1:$19) ªº³¡¤À¬° ROW($2:$19),
¸g¦A¦¸¥Î­ì¤½¦¡´ú¸Õ, ­ì¤½¦¡ªGµM¥¿½TµL»~,
¦A¦¸©êºp, ©|¬è¨£½Ì!! Sorry!!
¤£¹L¤½¦¡¬O¦p¦ó¹B§@ªº, ÁÙ¬O¤@ª¾¥b¸Ñ, ¥u¯à¨Ì¼Ëµe­JĪ¤F,
¦pªG§A¯à¦b§AªºBlog²¤¥[»¡©ú´N§ó¬üÄR¤F!!

hcm19522

¦^´_ 9# yen956
MID(SUBSTITUTE(B2,",",REPT(" ",50)),ROW($1:$19)*50-49,50)-->±NB2©Ò¦³ "," §ï¬°50ªÅ®æ ,¦¹®É¼Æ¦r¶¡¦³50ªÅ®æ ,¦A±q²Ä 1 ,51 ,101 ,151¡K¡K¦ì¸m¶}©l ,¦U¨ú50¦r¤¸ ,¦A¥ÎTRIM±NªÅ®æ®ø°£ ,¦¹®É¬Ý¬O¼Æ¦r ,¹ê¬O¤å¦r ,©Ò¥H¥Î ="3" ,²£¥ÍTRUE(¯u)©ÎFALSE(°²) ,*1¬O¤Æ¬°1©Î0 ³Ì«á¥ÎSUMPRODUCT¥[Á`
     Blog¦³¥[»¡©ú