Excel2010 セルのカウント

質問

Excel2010で、E4～G103の範囲内に、M3～M17に入力された文字列のいずれかを含む言葉が入力されています。さらに、E4～G103の範囲内には、色のついているセルとついていないセルがあります。
E4～G103の中で、色がついていて、かつM3に入力されている文字列を含む言葉の個数を、N3に返すにはどのように対応すればいいでしょうか。
よろしくお願いします。

回答No.9

mt2015

ANo.3です。

何度もすみません。
ユーザ定義関数にするなら範囲は引数渡しの方が良いので訂正します。
セルN3に　=fSample3(E4:G103,M3)　と入れてください。

Function fSample3(rArea As Range, sTarget As String) As Long
　　　Dim rRng As Range
　　　For Each rRng In rArea.Cells
　　　　　　If (rRng.Interior.ColorIndex > 0) And (InStr(rRng.Value, sTarget) > 0) Then fSample3 = fSample3 + 1
　　　Next
End Function

補足

ありがとうございます！無事数字が入りました！

回答No.8

mt2015

ANo.3です。

すみません、よく確認せずにちょこっとコードを直してユーザ定義関数にしたつもりになっていました。
Find関数はユーザ定義関数で使用するには色々と問題があったことを思い出して修正しました。
セルN3に　=fSample2() と入れてください。

Function fSample2() As Long
　　　Dim rRng As Range
　　　For Each rRng In Range("E4:G103").Cells
　　　　　　If (rRng.Interior.ColorIndex > 0) And (InStr(rRng.Value, Range("M3")) > 0) Then fSample2 = fSample2 + 1
　　　Next
End Function

回答No.7

mt2015

ANo.3です。

「CountIFColor」が何のことかさっぱり解りませんでしたが、ようやく理解しました。
この質問↓のANo.3の方が回答されたユーザ関数ですね。
http://oshiete1.nifty.com/qa9256327.html

当方は、その質問に回答された方とは別人です。

もし、CountIFColorの様にユーザ関数が良いなら以下のコードを標準モジュールに入れ、セルN3に　=fSample() と入れてください。

Function fSample() As Long
　　　Dim rRng As Range, nCount, sAddress

　　　nCount = 0
　　　Set rRng = Range("E4:G103").Find(What:=Range("M3"))
　　　If Not rRng Is Nothing Then
　　　　　　sAddress = rRng.Address
　　　　　　Do
　　　　　　　　　If rRng.Interior.ColorIndex > 0 Then nCount = nCount + 1
　　　　　　　　　Set rRng = Range("E4:G103").FindNext(rRng)
　　　　　　　　　If rRng Is Nothing Then Exit Do
　　　　　　Loop Until rRng.Address = sAddress
　　　End If
　　　fSample = nCount
End Function

補足

たびたびありがとうございます。すみません、ごっちゃになってしまっていたようです、失礼いたしました。

教えていただいたコードをコピーし、=fSample() も入れましたが・・・「１」と返ってきて、私自身、意味が分かっていない状況です・・・引数不要らしいですが・・・。例で栄養は書きましたがこれが15個あるのですが・・・。

今回「文字列に（たとえば）栄養を含む」「色つきのセル」を聞きたかったので、そもそもCountIFColorがおかしかったんですね・・・。すみません。

回答No.6

mt2015

ANo.3です。

> =CountIFColor("*"&P3&"*",$D$4:$G$103,6)はQ3に入力されています。

このユーザ関数と私のサンプルマクロ(ANo.3)の関係が良く解りません。
念のために確認しますが、Module3に入れたサンプルマクロを実行してもセルN3には0が入ると言う状態でよろしいでしょうか。

M3に入る文字列や、E4:G103に入る文字列の具体例を挙げてみてください。

補足

M列の1例として「栄養」、それがE4:G103だと「栄養バランスのよい食事をとる」だったり「栄養バランス」だったり「栄養バランス」だったりとかするので、栄養でまとめればカウントできると思った次第です。各行は人で、挑戦した項目が入力されていて、条件をクリアしていたら黄色で塗りつぶしてあります。項目別にクリアしている人数を出す式になっていると思ったのですが・・・。

実行とは、Alt+F11で出てくる、コード入力されている画面の実行のことでしょうか？今まで（というか今回の集計作業で初めてマクロを使ったので数えるほどですが）コードを入力して閉じればよかったのですが、違うのでしょうか？とりあえずコードが入っている画面の実行ボタンはクリックしてみました。やはり０が入る状態です。

回答No.5

bunjii

>E4～G103の中で、色がついていて、かつM3に入力されている文字列を含む言葉の個数を、N3に返すにはどのように対応すればいいでしょうか。
関数のみでカウントするには「4.0マクロ関数」を使って作業用のSheetを使う必要があるようです。
http://miyahorinn.fc2web.com/faq/faq030.html
質問の対象シートがSheet1で作業用にSheet2を使うとすれば次のように色付きセルを検出します。
Sheet1のA1セルをアクティブ（作業対象）にして「数式」タブの「名前の定義」で「cellcolor」等の名前を定義します。
「範囲」へは次の数式を入力します。
=GET.CELL(63,Sheet1!A1)+NOW()*0
作業用のSheet2!E4へは次の数式を入力します。
=cellcolor
Sheet2!E4セルをオートフィルでG4までコピーします。
そのままオートフィルでG103まで更にコピーすればSheet1!E4:G103の色付きセルに対応するSheet2のセルに0以外の数値が代入されます。（黄色のときは6になるでしょう）
「塗りつぶしなし」が０で「白」は2になるようです。
Sheet1のセルの色塗りを変更しても再計算を実行しないとSheet2!E4:G103の値は変化しないかも知れませんので注意してください。
N3セルには次の数式を入力すれば目的のセル数を算出できるはずです。
=SUMPRODUCT((LEN(SUBSTITUTE($E$4:$G$103,M3,""))<LEN($E$4:$G$103))*1,(Sheet2!$E$4:$G$103>0)*1)

回答No.4

mt2015

ANo.3です。

> 現在
> =CountIFColor("*"&P3&"*",$D$4:$G$103,6)
> となっています。

これはどのセルに入っているのでしょうか？
「CountIFColor」と言う関数はExcelにはありませんのでユーザ関数を作成されていませんか？
セルP3やD列に何が入っているのかも気になります……。

補足

=CountIFColor("*"&P3&"*",$D$4:$G$103,6)はQ3に入力されています。

ユーザー関数とはマクロのことですかね・・・であれば、使っています。
先の回答（No.3）の
Sub Sample()以下はModule3にそのまま投入しました。

D4～G103の範囲に入力されている項目名が詳しく書かれており、M3～M17に羅列した項目名と文言が必ずしも100％一致していません。（書きながら、D4ではなく「E4～G103の範囲」が正しかったと気付き、念のため訂正しました。
なお、色のついたセルとついていないセルがあるのは、項目をクリアしたかどうかを提出書類で確認しクリアしていれば色をつけてあります。

回答No.3

mt2015

色は検索条件としては不向きです。
と、言うのも普通にセルを塗りつぶした場合と、条件付き書式でセルを塗りつぶした場合で見た目は一緒ですが、VBAで条件付き書式でセルを塗りつぶした物を取得する事は出来ません。
と、言う事で色がついているセル＝普通にセルを塗りつぶした　と、言う前提で作ってみました。

Sub Sample()
　　　Dim rRng As Range, nCount, sAddress

　　　nCount = 0
　　　Set rRng = Range("E4:G103").Find(What:=Range("M3"))
　　　If Not rRng Is Nothing Then
　　　　　　sAddress = rRng.Address
　　　　　　Do
　　　　　　　　　If rRng.Interior.ColorIndex > 0 Then nCount = nCount + 1
　　　　　　　　　Set rRng = Range("E4:G103").FindNext(rRng)
　　　　　　　　　If rRng Is Nothing Then Exit Do
　　　　　　Loop Until rRng.Address = sAddress
　　　End If
　　　Range("N3") = nCount
End Sub

補足

ありがとうございます。
色の塗りつぶしは、手動ですので大丈夫そうです。
しかし、そちらを入れた結果全部ゼロになってしまい・・・数式が間違っているようです。

現在
=CountIFColor("*"&P3&"*",$D$4:$G$103,6)
となっています。
エラーではなくゼロが出るのは、意図しない数式になっていると思うのですが、こちらの数式の意味を教えていただけますか？
質問のとおり、「E4～G103の中で、色（黄色です）がついていて、かつM3に入力されている文字列を含む言葉の個数を、N3に返す」ということをしたいのですが・・・。
よろしくお願いいたします。

回答No.2

heisukewada

VBAを使わないのであれば、作業領域として　Sheet2,Sheet3を使います。

Sheet2のE4～G103を選択して
「数式」→　「名前の定義」とすすみ
「名前」に color　「参照範囲(R)」に　=GET.CELL(63,Sheet1!E4)+NOW( )*0 を入力
（添付画像を参考にしてください）
Sheet2の　E4に　=color　と入力して　G4　までコピペ　それから　G103　までコピペ
背景色に合わせて　色のついているところは数字がでてくるとおもいます。なにもないところは”０”
今度は　Sheet3 の E４に　=Sheet1!E4&Sheet2!E4　と入力
G4までコピペ　その後　G103　までコピペ
Sheet1の N3　に　=COUNTIF(Sheet3!$E$4:$G$103,Sheet1!M3&6) ここでは黄色の６ですが、Sheet2の色の数字を入れるようにします。
N17までコピペ

あとから色を付けた場合は　「数式」　「再計算実行」をしてください。

回答No.1

Prome_Lin

Option Explicit
Sub Test()
Dim c, i, j As Long
c = 0
For i = 4 To 103
For j = 5 To 7
If Cells(i, j).Interior.ColorIndex > 0 And InStr(Cells(i, j).Value, Range("M3").Value) > 0 Then
c = c + 1
End If
Next j
Next i
Range("N3").Value = c
End Sub

簡単な説明です。

c = 0

数をカウントする用で、初期値を設定しています。

For i = 4 To 103

行「４」から行「１０３」まで繰り返し。

For j = 5 To 7

列「Ｅ」から列「Ｇ」まで繰り返し。

If Cells(i, j).Interior.ColorIndex > 0 And InStr(Cells(i, j).Value, Range("M3").Value) > 0 Then

もし、セル「i, j」の背景色が「０」以上（「０」は白）で、かつセル「i, j」の中にセル「M3」の文字が含まれていたら

c = c + 1

１個プラス。

Range("N3").Value = c

「c」の合計をセル「N3」に入力。