COUNTIF セルに色を塗るとカウントされないようにするには？

質問

EXCEL2003です。
　　　Ａ
１　あいうえお
２　かきくけこ
３　さしすせそ
４　あいうえお
５　あいうえお
と、セルに入っていたとします。
A6のセルに　=COUNTIF（A1:A5,"あいうえお")　と入力。
すると、３と返してきます。

今からやりたいのは、もし、Ａ１のセルの「あいうえお」に色が付いたら、
それはカウントせず、２と返してくる。というのは、出来るのでしょうか？

回答No.2

zap35 · ベストアンサー

２つの方法を書きます。ワークシート関数ではセル背景色の有無は取得できませんから、どちらもユーザ定義関数を使用します。

まず以下のマクロをALT＋F11でVBE画面を開き、左上のVBA Projectでシート名を右クリックし「挿入」→「標準モジュール」で表示される画面に貼り付けて下さい

Function irrCOUNTIF(ByVal trg As Range, par As String) As Long
Dim r As Range
　For Each r In trg
　　If r.Interior.ColorIndex = xlNone And r.Value = par Then
　　　irrCOUNTIF = irrCOUNTIF + 1
　　End If
　Next r
End Function

Function cIndex(ByVal Target As Range) As Integer
　If Target.Interior.ColorIndex = xlNone Then
　　cIndex = 0
　Else
　　cIndex = Target.Interior.ColorIndex
　End If
End Function

方法１
ワークシート画面に戻り、適当なセルに
　=irrCOUNTIF(A1:A5,"あいうえお")
と入力してください。すると質問の例では「２」が返されるはずです。方法１では補助セルを使用しません。

でもこのユーザ定義関数では"あいうえお*"や"*あいうえお*"のような部分一致の場合はカウントしません。（完全一致だけでよければ方法１でよいです）

方法２
補助セルを使用する方法です。ワークシートのB1セルに
　=cIndex(A1)
と入力して下方向にコピーしてください。するとA列のセルのカラーインデックスを表示できます。背景色がないセルは「0」が返ります。
　=SUMPRODUCT(NOT(ISERROR(FIND("あいうえお",A1:A5)))*(B1:B5=0))
で、方法１と同様に「２」を得ることができます

ちなみに式を
　=SUMPRODUCT((A1:A8="a")*(B1:B8=0))
に変更すれば方法１と同じく完全一致のセルだけをカウントします。

お礼

OKWebのサーバーが変で、お礼遅れました。^^;
ありがとうございました。
関数では無理がありますね。
VBAで出来ました。

回答No.4

hallo-2007

少し手がかかりますが、
A列を全て、B列にコピィ
B列を選択、「置き換え」のウイザードで、「オプション」を押す
検索する文字列の書式で、付けた色を選択
置き換え後の文字は、ブランクのまま、全てを置き換え

選択した色がついた行が空白になりますので、
A6セルのIF関数をそのまま、横フィルでは、いかがでしょうか。

お礼

おお、盲点！その方法もあったのか！！
ありがとうございました。

回答No.3

Bubuca

こんにちは

エクセル2003ならば名前定義でget.cellを使えばできると思いますが、
作業列が必要になるのでお好みではないかもしれません。
ちょっと試してみましょう。

B1セルを選択しておいてcoloredという名前を定義しましょう。内容は、
　=get.cell(24,A1)*1　　（←１を乗じて数値に変換してます）
コピーして使いますので絶対参照にならないように注意してください。
引数24はセルの文字色を調べる場合で戻り値は色番号です。
デフォルト色の場合は0が戻りますが、今回はSumproductで集計したいので、これをif文で１に変換します。

B1セルには次の式を入れます。
　=if(colored=0,1,"")
こうすればA1セルの文字色がデフォルトならばB1の値は１に、色が付いている場合は空白になります。
これを表の下までコピーします。

セルの背景色ならばGet.cell(39,A1)*1です。
あ、38だったかもしれません、試してみてください。

あとはカウント結果を出したいセルに
　=Sumproduct((A1:A5="あいうえお")*(B1:B5=1))

これでいけると思うのですが、どうでしょうか。

お礼

一つの結果にも、色々な方法があるのですね。
勉強になります。
ありがとうございました。出来ました。

回答No.1

pbforce

VBAで関数を作っちゃえば可能かもしれません。

お礼

ありがとうございました。やってみます