Excel2003の関数で文字の抽出

質問

Excle初心者です。以下のことを関数を使用して実行したいのですが・・・。宜しくお願いします。

列の各セルには文字の羅列があります。これらの特定文字のみを抽出またはカウントしたい。

　　列1
-----------
(1)(2)(3)
(2)(3)
(3)(5)

(1)→1
(2)→2
(3)→2
(4)→0
(5)→1

回答No.2

KenKen_SP · ベストアンサー

#1です。失礼しました。#1のような面倒なことしなくともできます。

＝ COUNTIF(A1:A3,"*(1)*")

A1:A3がデータのある部分で、検索値をワイルドカードではさんでやります。

(2)ならこうです。

＝ COUNTIF(A1:A3,"*(2)*")

お礼

ありがとうございます。ワイルドカードですね。
大変助かりました。
もしも以下の場合はどうするのでしょう?
　列1
-----------
(1)(2)(2)
(2)(3)
(3)(5)(5)

回答No.10

Wendy02

#8のmacchan1 さんの補正案です。<m(__)m>
(私は、関数は自信がありません)

D1 から下に検索の結果を出すことにします。
ただし、今回はサンプルのみの範囲とします。

C1 に、
'(1) と、「'」アフォストロ付きで、入力してください。
「()」 付きは、-(マイナス)とキャストされることがありますから。

D1に、

=SUMPRODUCT((LEN($A$1:$A$3)-LEN(SUBSTITUTE($A$1:$A$3,C1,"")))/LEN(C1))

後は、C1 と D1 を選択して、フィルハンドル(+)で、下にドラッグしてコピー
してください。今回の場合は、(5)まででたら、終了のはずです。

もちろん、結果は、

(1)　2
(2)　2
(3)　3
(4)　0
(5)　1

ということになりますから、それ以外の結果の場合は、また、別の条件の提示が必要です。

お礼

ありがとうございます。関数って奥が深いんですね。勉強になりました。

回答No.9

KenKen_SP

関数による解が盛り上がってきましたが、えーーーと、、さりげなく#4の関数バグフィックスです。#4は第一引数に空セルが渡されると-1となるバグがありました。また、たぶん、寝ぼけてたのでしょうが、単純に検索語でSPLITすれば良かったところ、何を思ったのか意味不明なREPLACEがありまして、、、
ええ、またまた訂正コメントです。重ね重ね申し訳ありません。

'関数：対象（セル/文字列）に含まれる検索語の数を返す
Function CountInStr(対象 As Variant, 検索語 As String) As Long

　　Dim strTarget As String
　　Dim aryTmp

　　On Error Resume Next
　　'引数処理
　　Select Case TypeName(対象)
　　　　Case Is = "Range"
　　　　　　'配列に格納
　　　　　　aryTmp = Sheets(対象.Parent.Name).Range(対象.Address).Value
　　　　　　'配列要素をテキスト連結
　　　　　　If IsArray(aryTmp) Then
　　　　　　　　strTarget = Join(Application.Transpose(aryTmp))
　　　　　　　　Erase aryTmp
　　　　　　Else
　　　　　　　　strTarget = aryTmp
　　　　　　End If
　　　　Case Else: strTarget = 対象
　　End Select

　　'Splitで分割された要素数＝検索語の数
　　aryTmp = Split(strTarget, 検索語)
　　'戻り値セット
　　If UBound(aryTmp) = -1 Then
　　　　CountInStr = 0
　　Else
　　　　CountInStr = UBound(aryTmp)
　　End If

End Function

お礼

なんだか大掛かりになってきてしまいました。
参考にしてチャレンジしてみます。
ありがとうございました。

回答No.8

macchan1

#6の回答ですが、「(1)」を検索すると３倍の値（文字数分だけ掛けた数）になってしまいます。

以下のように修正してください。

=SUMPRODUCT(LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,"(1)","")))/LEN("(1)")

お礼

わかりました。ありがとうこざいます。

回答No.7

quit123

No.6　macchan1へ

=SUMPRODUCT(LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,"(1)","")))

これは件数ではなくて文字数ですよね。

=SUMPRODUCT(LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,"(1)","")))/3

最後に／３が抜けてます・・・よね。(^^;;;
以上です。

回答No.6

macchan1

1つの数式で出すなら例えば以下の式でできます。

=SUMPRODUCT(LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,"(1)","")))

回答No.5

macchan1

＞確かに(2)(3)(1)(1) のときでも 1 と数えてしまいます。この場合はどうすればよいでしょう?

2つ以上の場合は以下のように2つ以上入るセルの数をプラスする必要があります(同じデータが3つまでの例）。

=COUNTIF(A:A,"*(1)*")+COUNTIF(A:A,"*(1)*(1)*")+COUNTIF(A:A,"*(1)*(1)*(1)*")

お礼

なるほど。理解できました。ありがとうございます。

回答No.4

KenKen_SP

＞確かに(2)(3)(1)(1) のときでも 1 と数えてしまいます。
＞この場合はどうすればよいでしょう?

私の頭で浮かぶのは次の2つの方法です。

1.　#1のように各要素をセルに分割してカウントする方法
2.　VBAで関数を作成しカウントする

1.の方法は#1をお読みいただくとして、#2の方法です。VBAでユーザー定義関数（自作関数）を作成します。

【手順】
1.　EXCEL画面で[Alt]+[F11]キーを同時に押す。
　　--＞Visual Basic Editor起動（以下VBE）
2.　VBE画面のメニュー[挿入]-[標準モジュール]クリック
3.　2.で開いたウインドウに下記VBAコードをコピー＆ペースト
4.　VBEを閉じる

【使い方】

普通のワークシート関数のように使います。

＝CountInStr(A1:C10,"(1)")

これで、A1:C10のセル範囲の値に 文字列 (1) の個数を返します。第一引数A1:A10の部分はセル範囲でも文字列でも数値でも構いません。

ex) ＝CountInStr("(1)(2)(3)(1)(1)","(1)") --＞結果 3

【以下VBAコード：場所＝標準モジュール】（次行から終わりまで）

'関数：対象（セル/文字列）に含まれる検索語の数を返す
Function CountInStr(対象 As Variant, 検索語 As String) As Long

　　Dim strTarget As String
　　Dim aryTmp

　　On Error Resume Next
　　'引数処理
　　Select Case TypeName(対象)
　　　　Case Is = "Range"
　　　　　　'配列に格納
　　　　　　aryTmp = Sheets(対象.Parent.Name).Range(対象.Address).Value
　　　　　　'配列要素をテキスト連結
　　　　　　If IsArray(aryTmp) Then
　　　　　　　　strTarget = Join(Application.Transpose(aryTmp))
　　　　　　　　Erase aryTmp
　　　　　　Else
　　　　　　　　strTarget = aryTmp
　　　　　　End If
　　　　Case Else: strTarget = 対象
　　End Select

　　'検索文字をありそうもない文字に置換し、Splitで分割
　　'分割された要素数＝検索語の数
　　aryTmp = Split(Replace(strTarget, 検索語, "&&&"), "&&&")
　　'戻り値セット
　　CountInStr = UBound(aryTmp)

End Function

お礼

やはりVBAですか(汗
チャレンジしてみます。

回答No.3

KenKen_SP

何度も済みません、、、#1#2です（汗）

#2の手法は例えば、 (2)(3)(1)(1) のときでも 1 と数えてしまいますので、ご注意下さい。

こういうケースが考えられる場合には、やはり#1の方法が良いと思います。VBAで関数を作ってしますという手もありますが。

お礼

ありがとうございます。
確かに(2)(3)(1)(1) のときでも 1 と数えてしまいます。この場合はどうすればよいでしょう?何度もすいません。

回答No.1

KenKen_SP

(1)(2)(3)
(2)(3)
(3)(5)

のデータをEXCELの置換機能を使って、[ ( ] の記号をカットします。次に、[ ) ]の記号をカンマ[ , ]に置換します。これで

1，2，3，
2，3，
3，5，

こんな感じになります。そうしたら、

1.　[データ]-[区切り位置]をクリック
2.　[カンマやスペースで区切られた...(略)]を選択し、[次へ]クリック
3.　[区切り文字]にカンマを指定して、あとは画面の指示どおり進めます

これで要素ごとにセルに分割できました。あとは表全体を対象としてCOUNTIF関数で集計します。

例えば、 1 なら ＝ COUNTIF(A1:C3,1) です。