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

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

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

°²¦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
ss

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

TOP

¦^´_ 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
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

·PÁÂ.......
ss

TOP

¦^´_ 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*")
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

±j¤H👍......
ss

TOP

¦^´_ 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
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-4-29 08:45 ½s¿è

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

TOP

¦^´_ 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*
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

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

TOP

        ÀR«ä¦Û¦b : ¦Û¤v®`¦Û¤v¡A²ö¹L©ó¶ÃµoµÊ®ð¡C
ªð¦^¦Cªí ¤W¤@¥DÃD