[µo°Ý] ¥i§_¥Îcountif? ­Y¬O¦p¦ó?

sunnyso

°²¦pAÄ榳¤U­±ªº¸ê®Æ, ¦p¦ó²Î­p¥X¶È¦³¤@­ÓAªº¦³¦h¤Ö? ¦³¨â­ÓAªº¦³¦h¤Ö­Ó? ¦³¤T­ÓAªº¦³¦h¤Ö­Ó? ¨S¦³Aªº¦³¦h¤Ö?

¥i¥H¥Îcountif ¶Ü? ½Ð¤j¤j«ü±Ð.

1. AAA
   
2. AAB
   
3. AAC
   
4. AAD
   
5. ABA
   
6. ABB
   
7. ABC
   
8. ABD
   
9. ACA
   
10. ACB
    
11. ACC
    
12. ACD
    
13. ADA
    
14. ADB
    
15. ADC
    
16. ADD
    
17. BAA
    
18. BAB
    
19. BAC
    
20. BAD
    
21. BBA
    
22. BBB
    
23. BBC
    
24. BBD
    
25. BCA
    
26. BCB
    
27. BCC
    
28. BCD
    
29. BDA
    
30. BDB
    
31. BDC
    
32. BDD
    
33. CAA
    
34. CAB
    
35. CAC
    
36. CAD
    
37. CBA
    
38. CBB
    
39. CBC
    
40. CBD
    
41. CCA
    
42. CCB
    
43. CCC
    
44. CCD
    
45. CDA
    
46. CDB
    
47. CDC
    
48. CDD
    
49. DAA
    
50. DAB
    
51. DAC
    
52. DAD
    
53. DBA
    
54. DBB
    
55. DBC
    
56. DBD
    
57. DCA
    
58. DCB
    
59. DCC
    
60. DCD
    
61. DDA
    
62. DDB
    
63. DDC
    
64. DDD

½Æ»s¥N½X

renfu

¥Ø«e·Q¨ìªº¤èªk¬O¥ÎLike ¥h°µ§PÂ_¡A
§Ú¤]¬O·s¤â¡A§Æ±æ¦^µª¹ï§A·|¦³À°§U ^^

softsadwind

¦^´_ 1# sunnyso


    ¤T­ÓA =SUMPRODUCT((LEN(SUBSTITUTE(A1:A64,"A",""))=0)*1)
    ¨â­ÓA=SUMPRODUCT((LEN(SUBSTITUTE(A1:A64,"A",""))=1)*1)
    ¤@­ÓA=SUMPRODUCT((LEN(SUBSTITUTE(A1:A64,"A",""))=2)*1)
    ¤£§tA=SUMPRODUCT((LEN(SUBSTITUTE(A1:A64,"A",""))=3)*1)

     ¦pªG­n¥Îcountif ´N¬O­n¦h¤@Ä滲§U¦C
     B1=LEN(A1)-LEN(SUBSTITUTE(A1,"A","")) ¤U©Ô¡A¥hºâ¦³´X­ÓA ¡A©ÎªÌ¥Î³Q¨ú¥N¼Æ¨Ó¤Ï±À´X­ÓA

sunnyso

·PÁÂ.......

softsadwind

¦^´_ 4# sunnyso


    µw¬O­n¥Îcountif

1­ÓA =COUNTIF(A$1:A$100,"*A*")-COUNTIF(A$1:A$100,"AA*")-COUNTIF(A$1:A$100,"A*A")-COUNTIF(A$1:A$100,"*AA")+2*COUNTIF(A$1:A$100,"AAA")
2­ÓA =COUNTIF(A$1:A$100,"AA*")+COUNTIF(A$1:A$100,"A*A")+COUNTIF(A$1:A$100,"*AA")-3*COUNTIF(A$1:A$100,"AAA")

¤T­ÓA=COUNTIF(A$1:A$100,"AAA")
¨S¦³A =COUNTA(A$1:A$100)-COUNTIF(A$1:A$100,"*A*")

sunnyso

±j¤H👍......

ML089

¦^´_ 1# sunnyso

³oºâ½ì¨ýÃD

=SUM(COUNTIF(A:A,"*"&REPT("A",ROW(A1)+{-1,0})&"*")*{1,-1})
¤U©Ô
¬°4­Ó¤½¦¡(¦p¤U)²Î­p­È
0A
1A
2A
3A

p212

¦^´_ 7# ML089
½Ð±ÐML089ª©¥D¡G
1¡B¹ï©óREPT("A",ROW(A1)+{-1,0})¡A¬°¦ó¤£ª½±µ¥ÎREPT("A",ROW(A1)-1)¡H
2¡B¤SCOUNTIFµ²ªG*{1,-1}¡A¨ä*{1,-1}¥Î·N¦ó¦b¡H¦b»s³y¤°»ò¼Ëªº°}¦Cµ²ªG¨ÑSUM¹Bºâ¡H
ÁÂÁ¡I

ML089

¦^´_ 8# p212
1¡B¹ï©óREPT("A",ROW(A1)+{-1,0})¡A¬°¦ó¤£ª½±µ¥ÎREPT("A",ROW(A1)-1)¡H
2¡B¤SCOUNTIFµ²ªG*{1,-1}¡A¨ä*{1,-1}¥Î·N¦ó¦b¡H¦b»s³y¤°»ò¼Ëªº°}¦Cµ²ªG¨ÑSUM¹Bºâ¡H

¦^ÂÐ1
REPT("A",ROW(A1)+{-1,0}) ==> ·íA1®É REPT("A", {0,1}) = {"", "A"} ¥i±o¨â­Ó°}¦C­È
REPT("A",ROW(A1)-1) ==> ·íA1®É REPT("A", {0}) = {""} ¥i±o¤@­Ó°}¦C­È

¦^ÂÐ2
=SUM(COUNTIF(A:A,"*"&REPT("A",ROW(A1)+{-1,0})&"*")*{1,-1})
=SUM(COUNTIF(A:A,{"**","*A*"})*{1,-1})
=SUM({64,37}*{1,-1})
=SUM({64,-37})
=27
   
COUNTIF(A:A,"**") ¥i±o¥þ³¡ªº¼Æ¶q
COUNTIF(A:A,"*A*") ¥i±o*A*ªº¼Æ¶q
¨âªÌ¬Û´î´N¬O¤£§tAªº¼Æ¶q

³Æµù
** ¥]§t ¥þ³¡
*A*  ¥]§t *A*¡B*AA*¡B*AAA*
*AA* ¥]§t *AA*¡B*AAA*

p212

¦^´_ 9# ML089
ÁA¸Ñ¡I
«D±`·PÁÂML089ª©¥D¸Ô²Óªº¸Ñ»¡