エクセル配列数式について

質問

A列(A3:A42)に氏名
B列(B3:B42)に性別
C列(C3:C42)に第1回テスト成績
D列(D3:D42)に第2回テスト成績
が入力してあります。
性別が"男"
かつ
第1回テスト成績が350以上
かつ
第2回テスト成績が350以上
の条件を満たす配列数式を
{=COUNT(IF((B3:B42="男")＊(C3:C42>=350)＊(D3:D42>=350),B3:B42,""))}
だと正しい回答がでません
IFの真の場合の欄をC3:C42またはD3:D42にすると正しい値がでます
なぜC3:C42だとだめなのでしょうか？

回答No.4

ham_kamo · ベストアンサー

No.1さんが仰るようにCOUNTは数字を数える関数です。それで、

=COUNT(IF((B3:B42="男")＊(C3:C42>=350)＊(D3:D42>=350),B3:B42,""))

のでは、条件に一致するときは"男"、一致しないときは""となり、数値はないので、この式は常に０になるはずです。
式の途中の式の値をF9で見ると納得すると思います。
式の中の、
IF((B3:B42="男")*(C3:C42>=350)*(D3:D42>=350),B3:B42,"")

の部分を選択してF9を押すと、以下のような配列数式が一時的に見えるはずです。（ここでEnterを押すと、この値に式が変わってしまうのでEscキーを押して戻してください）
{"男";"";"";"男";"";"";"男";"";"";"男";"";"";"男…(省略)}
と出ると思います。これでは数値が１つもないので、COUNTでは０になるのです。

これを、B3:B42をC3:C42にした場合、同じようにIF文を選択してF9を押すと、
{350;FALSE;FALSE;350;FALSE;FALSE;350;FALSE;FALSE;350;FALSE;…(省略)}
のようになり、条件に一致しない場合は「FALSE」という論理値になり、数値ではないので、数値とみなされる点数部分だけがカウントされ、正しい値が表示されます。

なお、FALSEはあくまでも論理値のFALSEです。明示的あるいは暗黙のキャスティングにより、式の中でFALSE=0と変換された場合は数値としてCOUNTの対象になりますが、そうでなく純粋にBoolean(論理値）のFALSEの場合は数値とはみなされません。

このようなケースのやり方は他にもいくつかあります。

=COUNT(IF((B3:B42="男")*(C3:C42>=350)*(D3:D42>=350),1,""))
としてCtrl+Shift+Enter。

=SUM(IF((B3:B42="男")*(C3:C42>=350)*(D3:D42>=350),1,0))
としてCtrl+Shift+Enter。

しかし、もっとも一般的なのは、

=SUMPRODUCT((B3:B42="男")*(C3:C42>=350)*(D3:D42>=350))

だと思います。配列数式ですがSUMPRODUCTなので、普通にEnterで入力すればいいです。

お礼

SUMPRODUCT等で値を求めることはできますが
いま配列数式を勉強中で、この質問の答えが「なぜなのか？」でつまづいています。
有難うございました

補足

すみません。質問を間違えました
「なぜB3:B42だとだめなのでしょうか？」に訂正します

回答No.7

imogasi

IFがTRUEの時、B3:B42を指定すると、男か女かの文字列のうち最左の条件から、男を（中間的に）返します。それをＣＯＵＮＴで勘定してはダメです。ＣＯＵＮＴは数値の個数を数えるものです。
空白でないセルの数はＣＯＵＮＴＡです。
そういう事情ではないですか。

回答No.6

zap35

＃０２です。
＞この質問の答えが「なぜなのか？」でつまづいています。

＃０４さんの回答にも書かれているのですが、
　{=COUNT(IF((B3:B42="男")＊(C3:C42>=350)＊(D3:D42>=350),B3:B42,""))}
はIF文の条件式がどの行もTRUEだとすると
　=COUNT(B3:B42)
と同じことになります。COUNT関数は数値を数える関数ですからこの結果は０になり、C,D列を対象とした場合はC,D列が数値のため正しい結果になります。

COUNT関数の代わりにCOUNTA関数で試すと確かに行数と同じ結果が返りました。これはセルに「=""」と入力した場合COUNTA関数では「値がない状態」とは見なさないからです。

COUNT関数にこだわるなら
{=COUNT(IF((B3:B42="男")＊(C3:C42>=350)＊(D3:D42>=350),1,""))}
とすれば良いでしょう。そうすれば「TRUE、TRUE、FALSE、TRUE　…」の行データがIF文によって
　1、１、""、１　…
の配列に置き換わりますからCOUNT関数でカウントすることが可能です。

回答No.5

noname#77845

＃１です。
「{=COUNTA(IF((B3:B42="男")＊(C3:C42>=350)＊(D3:D42>=350),B3:B42,""))}
列データの総個数40という値が出てだめでした」
失礼しました。m(_ _)m
どうしても配列数式を使う場合は
{=COUNT(IF((B3:B42="男")＊(C3:C42>=350)＊(D3:D42>=350),1,""))}
に変更しましょう。

お礼

SUMPRODUCT等で値を求めることはできますが
いま配列数式を勉強中で
{=COUNT(IF((B3:B42="男")＊(C3:C42>=350)＊(D3:D42>=350),1,""))}
で正しい値が出て
{=COUNT(IF((B3:B42="男")＊(C3:C42>=350)＊(B3:B42>=350),1,""))}
では正しい値がでないのか「なぜなのか？」がわかりません

有難うございました

回答No.3

noname#77845

＃１です。
「TRUE=1
FALS=0
ということはB3:B42でもいいと思うのですが？」
TRUEの時に１をカウントするのではなく、B3:B42セルの該当する部分をカウントしようとしています。
（FALSEの時は""をカウントします。）

「COUNT」関数は、「数値」の個数をカウントします。
性別には、「男」又は「女」が入っていますよね？
実際にカウントするセルがB1:B42で、入っているものが数値ではないので何もカウントできません。
「COUNTA」なら、空白ではないセルをカウントするのでOKです。

補足

edomin2004さんのおっしゃるとおりCOUNTAにしたら
{=COUNTA(IF((B3:B42="男")＊(C3:C42>=350)＊(D3:D42>=350),B3:B42,""))}
列データの総個数40という値が出てだめでした

回答No.2

zap35

B列は文字列なのでCOUNT関数では引っかかりません。COUNTA関数かOUNTIF関数を使う必要があります。

でも配列式にしなくても
=SUMPRODUCT((B3:B7="男")*(C3:C7>350)*(D3:D7>350)*1)
=COUNT(INDEX(1/((B3:B7="男")*(C3:C7>350)*(D3:D7>350)),))
で求めることも可能です。

補足

SUMPRODUCT等で値を求めることはできますが
いま配列数式を勉強中で、この質問の答えが「なぜなのか？」でつまづいています。
有難うございました

回答No.1

noname#77845

「COUNT」を「COUNTA」に変更してみてはいかがでしょう？
（性別は数値ではありませんので…）

お礼

TRUE=1
FALS=0
ということはB3:B42でもいいと思うのですが？

補足

すみません。質問を間違えました
「なぜB3:B42だとだめなのでしょうか？」です