エクセル関数で条件の組合わせのカウントをする方法

質問

先日、条件付のセルのカウントの質問をしました。方法がいくつかあることはわかったのですが、具体的な表記にたどり着けませんでした…。

「●データ」のようなデータから
「●結果」のような結果を得たいと思っています。
このときのエクセル関数の具体的な表記を教えてください。

●データ
発生日 種別 完了日
2006/5/1 A 2006/5/1
2006/5/1 A
2006/5/1 B 2006/5/3
2006/5/3 A 2006/5/4
2006/5/5 A

●結果
日付 A発生 A完了 A残
2006/5/1 2 1 1
2006/5/2 0 0 1
2006/5/3 1 0 2
2006/5/4 0 1 1
2006/5/5 1 0 2

今はとりあえず下記のソースのようにVBAで対処したのですが、後学のために関数だけで行う方法も学びたいと思いました。よろしくお願いします。

=countA発生("A",[日付セルアドレス])
=countA対策("A",[日付セルアドレス])
=countA残("A",[日付セルアドレス])

Function countA発生(指定種別 As String, 基準日 As Date)

i = 2
Count = 0

While Cells(i, 1).Value <> ""

基準日の発生だ = (Cells(i, 1).Value = 基準日)
種別が指定のものだ = (Cells(i, 2).Value = 指定種別)

If 基準日の発生だ And 種別が指定のものだ Then
Count = Count + 1
End If

i = i + 1

Wend

countA発生 = Count

End Function

※countA対策とcountA残のソースは800字制限に引っかかったため省略

回答No.2

papayuka · ベストアンサー

返事が遅くなりました。
私が例にあげた データ表が A1～C6、結果表はE1:H6として

K2:K6 を選択した後、K2 に =OR(($C$2:$C$6>$E2),($C$2:$C$6="")) と入力して Ctrl+Shift+Enter
これで K2:K6 に配列関数が入ります。

L2:L6 を選択した後、L2 に =NOT(($C$2:$C$6<=$E2)*($C$2:$C$6<>"")) と入力して Ctrl+Shift+Enter
これで L2:L6 に配列関数が入ります。

それぞれを比較すると

TRUE　　FALSE
TRUE　　TRUE
TRUE　　TRUE
TRUE　　TRUE
TRUE　　TRUE

となり、ORは配列結果をちゃんと返していません。
確証は無いですが、ORは引数に配列を受付無いのかも知れませんね。

あと、釈迦に説法かも知れませんがユーザー関数は範囲まで指定すると少しだけ汎用性が広がると思います。
=mycounta("A",E2,$A$2:$B$6)

Function myCountA(種別 As String, 基準日 As Date, 範囲 As Range) As Long
Dim r As Range
　Application.Volatile
　myCountA = 0
　For Each r In 範囲
　　 If r.Value = 基準日 And r.Offset(0, 1).Value = 種別 Then
　　　 myCountA = myCountA + 1
　　 End If
　Next r
End Function

お礼

>確証は無いですが、ORは引数に配列を
>受付無いのかも知れませんね。

よく分からないのですが、論理演算で同じように使えそうなもの同士でも微妙に仕様が異なる場合がある(のかもしれない)ということなのですね。

今後気をつけて注目してみます。

>あと、釈迦に説法かも知れませんが
>ユーザー関数は範囲まで指定すると
>少しだけ汎用性が広がると思います。

なるほど。範囲は使ったことはあるのですが、
r.Offset(0, 1)
こういう使い方を知らなかったために、すべてのセルに対して作用するという場合にしか使ったことがありませんでした。

今後の参考にします。大変参考になりました。
ありがとうございました。

回答No.1

papayuka

データ表が A1～C6 とします。
結果表は E1 ～ H6 とします。

F1のタイトル「A発生」は、関数の引数に使いたいので表示形式だけ「A発生」となるようにします。
具体的には A だけ入力して表示形式をユーザー定義で　@"発生"　とします。

F2 に　=SUMPRODUCT(($A$2:$A$6=E2)*($B$2:$B$6=F$1))
G2 に　=SUMPRODUCT(($B$2:$B$6=F$1)*($C$2:$C$6=E2))
H2 に　=SUM(H1,F2)-G2

これで F2～H2 を下にコピーします。

お礼

なるほど。完璧です。
意外とシンプルにできるのですね。

VBAだけでやり続けようとせずに質問してよかったです。
ありがとうございました。

…といいつつで、追加で質問なのですが、
さらに少し条件が複雑になった場合も教えていただきたいです。

具体的には
A残の算出についてA発生とA対策の結果を使用せずに
算出する方法です。VBAでは下記のコードで実現できているので
SUMPRODUCTを使用してできるはずだと思い記述してみたのですが
何かが間違っているようです…。

=SUMPRODUCT(($A$2:$A$6<=A32)*OR(($C$2:$C$6>A32),($C$2:$C$6=""))*($B$2:$B$6="A"))

これについてもできれば教えていただきたいです。
よろしくお願いします。

Function countA残(指定種別 As String, 基準日 As Date)

i = 2
Count = 0

While Cells(i, 1).Value <> ""

基準日以前の発生だ = (Cells(i, 1).Value <= 基準日)
基準日に未対策だ = ((Cells(i, 3).Value > 基準日) Or (Cells(i, 3).Value = ""))
種別が指定のものだ = (Cells(i, 2).Value = 指定種別)

If 基準日以前の発生だ And 基準日に未対策だ And 種別が指定のものだ Then
Count = Count + 1
End If

i = i + 1

Wend

countA残 = Count

End Function

補足

もう少し自分でやってみました。

=SUMPRODUCT(($A$2:$A$6<=A32)*NOT(($C$2:$C$6<=A32)*($C$2:$C$6<>""))*($B$2:$B$6="A"))

とか

=SUMPRODUCT(($A$2:$A$6<=A32)*($C$2:$C$6>A32)*($B$2:$B$6="A"))+SUMPRODUCT(($A$2:$A$6<=A32)*($C$2:$C$6="")*($B$2:$B$6="A"))

で期待通りの結果が得られました。ありがとうございました。

でも

=SUMPRODUCT(($A$2:$A$6<=A32)*OR(($C$2:$C$6>A32),($C$2:$C$6=""))*($B$2:$B$6="A"))

については相変わらずうまく行っていません…。これが一番素直な記述だと思うのでこれがうまく行かないことが納得いかないのです…