VBAで任意の文字列の数のカウント方法を教えてください。

質問

VBAで任意の文字列の数のカウント方法を教えてください。

A
a:b
c:d
a:b:d
c:d
a
a:b:c:d
・
・
・

というデータがあります。
ひとつのセルにa～dまでの項目がランダムに入っており、項目は：で区切られています。
1つのセルに項目が重複することはありませんが、入っている項目数はバラバラです。
空白もあり得ます。
データの数（行数）は都度、変わります。
この時のa～dの項目のそれぞれの集計を取りたいのですが、VBAで作成する方法を教えてください。
トータルで、aが4個、bが3個、cが3個、という集計を一度に出したいのです。
よろしくお願いします。

回答No.5

Wendy02 · ベストアンサー

マクロにするにはマクロの理由があると思いますが、いろんな質問パターンがあるものですね。

>Sheet2のA列には項目名が入っているので、それぞれの項目名の横のB列に
というなら、難しいことを考えなくていいと思いますね。もし、その検索リストを作るとなれば、話は違います。以下は、私としては、あまり好きなパターンではありません。
いずれにしても、質問は最初から具体的に書いてくださいね。

'//
Sub Test1()
　Dim sh1 As Worksheet, sh2 As Worksheet
　Dim rng As Range
　Dim c As Variant, cnt As Long
　Set sh1 = Worksheets("Sheet1")
　Set sh2 = Worksheets("Sheet2")
　With sh1
　　Set rng = .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
　End With
　Application.ScreenUpdating = False
　With sh2
　　For Each c In .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
　　　cnt = WorksheetFunction.CountIf(rng, "*" & c.Value & "*")
　　　c.Offset(, 1).Value = cnt
　　Next
　End With
　Application.ScreenUpdating = True
　Set sh1 = Nothing : Set sh2 = Nothing
End Sub

お礼

できました！まさにやりたかったことはコレです。
何度も書き直して、行数ももっと多かったのに、こんなに簡潔に書けるんですね。勉強になりました。ありがとうございました。

回答No.4

noname#144013

こんにちは。

以下は一例です。
的外れな場合は、すみません。

VBAではなく、ワークシート関数を使用した例です。
宜しければ検証してみて下さい。
※当方は、Excel2000で検証してみました。
※VBAマクロをご要望の際は、無視して下さい。

■シート１（参照元シート）のレイアウト例
　　　　　　A　　　　
　1　抽出元文字列
　2　りんご：バナナ
　3　ぶどう
　4　かき：りんご：みかん
　5　バナナ：ぶどう
　6　りんご
　：　　　　：

■シート２（抽出先シート）のレイアウト例
　　　　　　A　　　　　　　B　　
　1　抽出文字列　　抽出個数
　2　りんご　　　　　　（数式）
　3　ぶどう　　　　　　（数式）
　4　かき　　　　　　　（数式）
　5　バナナ　　　　　 （数式）
　6　みかん　　　　　 （数式）
　：　　　：　　　　　　　　　：　

■シート２に設定する数式
上記のシートレイアウトだった場合、シート２の該当セルに以下のような数式を設定
します。

　＜B2セルの数式＞
　　=IF($A2="","",SUMPRODUCT(ISNUMBER(FIND($A2,Sheet1!$A$2:$A$65536))*1))

　　※B列のB3以降のセルには、B2セルを選択し、オートフィル操作などで下方向に
　　　数式をコピーして下さい。

　　注１)数式中の参照セル名（上記では、$A2 及び、Sheet1!$A$2:$A$65536）の
　　　　　『シート名』及び『セル範囲』等は、ご使用のExcelシートの構成に合わせて、
　　　　　適せん変更して下さい。
　　注２）シート１の参照元セル範囲（上記では、Sheet1!$A$2:$A$65536）は、想定
　　　　　として考えられる最大行数分のセル範囲を指定して下さい。
　　　　　※ワークシートの最大行番号は、Excelのバージョンにより異なります。

※添付画像は、当方で検証した際のExcelシートの画面例です。

以上です。

お礼

ご丁寧な回答、ありがとうございました。
今回は続きも作りたかったので、VBAで作成しました。
関数で作成する場合、参考にさせていただきます。

回答No.3

imogasi

質問に挙げた例がわかりにくい。もう少し丁寧に説明してほしい。
ただし
文字数なら、各セルでa、ｂ、ｃ、ｄの４文字それぞれで、セルの文字列を削除して、元の文字列と
削除後の文字列の長さを引き算すれば、文字数が出る。
●この4文字の削除による、文字数の前後比較カウントをそれぞれ４変数に累積していって、
●セルの数だけ繰り返す
が文字数カウントの定石だと思う。
ーー
＞項目は：で区切られています。が良くわからない、１セルの実例を挙げるべきだ。
対象セル範囲も質問異書いてない。A列、質問のようなことで良いが、コードをそっくり作ってクレと言うレベルの場合は、シートの（行、列のデータの有様）情況を書かないとコードの形にしにくい。

補足

説明がわかりづらくてすみません。

-----------sheet1-----------------

A
1 りんご:バナナ
2 ぶどう
3 かき:りんご：みかん
4 バナナ：ぶどう
5 りんご
・
・
・

-----------sheet2-----------------
　　A B
1　りんご 3
2　ぶどう 2
3　かき 1
4　バナナ 2
5　みかん 1

やりたいことは、ExcelでSheet2のようにSheet1の集計を出したいのです。
Sheet1のひとつのセルの中に複数の項目（果物）が入っています。
Sheet2のA列には項目名が入っているので、それぞれの項目名の横のB列に
Sheet1の集計を出したいのです。

Sheet1は毎月行数や項目が変わるので、VBAを実行した時にSheet2のBに
数字が入るようにならないかと考えています。

VBAの初心者です。
よろしくお願いします。

回答No.2

n-jun

aからdまでが含まれるセルの個数を
COUNTIF関数で数えれば宜しいのでは？

Sub try()
Dim v As Variant
Dim st As String

st = ""

For Each v In Array("a", "b", "c", "d")

st = st & v & " が " & WorksheetFunction.CountIf(Range("A:A"), "*" & v & "*") & "個" & vbCrLf

Next

MsgBox st

End Sub

とか？

お礼

今回は使えませんでしたが、VBAをさわり始めたばかりの私にもわかりやすく書いていただき、
ありがとうございました。今後の参考になります。

回答No.1

msx68000

全セルのデータをくっつけて、
参考URLで紹介されているように
Replaceを使った差分計算を
a～dまでくりかえす

っていうのはダメですか？

参考URL：

http://www.atmarkit.co.jp/fdotnet/dotnettips/911countchar/countchar.html

お礼

私には少し高度でなかなか理解できませんが、こういう書き方があるんですね。
ありがとうございました。