Excel2003 ユーザー定義関数 で SUBTOTALとSUMPRODUCTの複合できますか？（複数条件）

質問

こんばんは。お願いします。

まず、セルA2、B2、C2にそれぞれフィールド名"あ"、"い"、"う"があるとします。
この２列目以下にオートフィルターを使います。
A列のA3,A4…には文字列"a","b","c"または"d"がランダムに並んでいて、
B列のB3,B4…にはランダムに数値（整数）が入っていて、
C列のC3,C4…には文字列"p","q",または"r"がランダムに並んでいるとします。

このとき、セルB1にVBAで作成した関数を入れたいと思っています。
Excel2003です。

その関数は、以下の条件を満たします。
(1)フィールド「あ」が、たとえば"a"または"b"である場合のみ、その行のB列の数値の合計を計算するように引数を設定した場合、
(2)オートフィルタを用いて、フィールド「あ」に"a"または"b"または"c"のフィルタをかけて、さらにフィールド「う」にも"p"または"r"のフィルタをかけても、B列の、可視セルの、フィールド「あ」が"a"または"b"の行の数値だけの合計を計算する
（添付の図の例で言うと2+3+1+3= 9 が正解となる）
(3)作業列は使えません。

そこで、自分で作成してみたのがこれです。

Function SubIf(c)
Dim i As Long
For i = 3 To Range("A3").End(xlDown).Row
If Rows(i).Hidden = False Then
If Cells(i, 1).Value = "a" Or Cells(i, 1).Value = "b" Then
c(i) = Range("B" & i)
End If
End If
Next i
SubIf = WorksheetFunction.Sum(c)
End Function

が、SubIf(B3:B11)の結果は、#VALUE!に終わりました。
何か、根本的に間違っていますでしょうか。

とにかく、可視セルのうち、条件にあうもののみ足し算したい、ということです。
もしこのようなことがワークシート関数でもできるのであれば、それで構いません。
どうぞ、よろしくお願いいたします。

回答No.2

Wendy02 · ベストアンサー

こんにちは。

まず、ユーザー定義関数のコードは、まだVBAの基本的な知識がありませんね。
Function プロシージャとユーザー定義関数とは、似ていても作業が違います。

図があるというのですが、ありませんね。

簡単に行うには、オートフィルタのオプションではなくて、フィルタ・オプションで行えばよいのではないかと思います。

文章で分かりにくいのは、数学の「または(or)」というのは、「両方とも(plus)」という意味で、英語ネイティブの文章でも、時々問題になりますが、日本語でも「または」と書かれると、どちらか一方(either)という意味もありますので、混乱してしまいます。

たぶん、前回の質問とあわせると、可視セルを合計する、SUBTOTAL(9,範囲)の範囲の中に条件を設けたい、言い換えれば、SUMIF(範囲,検索条件,合計範囲)　の可視セルだけの計算をしたいということだと思います。

もし、そういう意味だとすると、数式ですと、以下のような数式になるかと思います。

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A3,ROW(A3:A23)-3,0))*(A3:A23={"a","b"})*B3:B23)
このようにするか、

ユーザー定義関数ですと、以下のようになります。
検索条件は、ワイルドカードが使えます。
'----------------------------------------------------------------------------------
'注意：検索範囲と合計範囲のセルの数や範囲の形状が違っている場合のエラー処理はされていません。
'-------------------------------------------
'標準モジュール
'-------------------------------------------
Public Function SUBTOTALIF(検索範囲 As Range, 合計範囲 As Range, ParamArray 検索条件() As Variant)
　　'SUBTOTALIF(検索範囲,合計範囲,検索条件)
　　Dim rng1 As Range
　　Dim rng2 As Range
　　Dim k() As Variant
　　'-------------------------------------------
　　'2byte 変数の切り替え
　　Set rng1 = 検索範囲
　　Set rng2 = 合計範囲
　　k() = 検索条件()
　　'-------------------------------------------
　　Dim i As Long
　　Dim c As Range
　　Dim dSum As Double
　　Dim v As Variant
　　For Each c In rng1
　　　　i = i + 1
　　　　If c.Rows.Hidden = False Then
　　　　　　For Each v In k　'複数の条件
　　　　　　　　If c.Text Like v Then
　　　　　　　　　　If VarType(rng2.Cells(i).Value) = vbDouble Then
　　　　　　　　　　　 dSum = dSum + rng2.Cells(i).Value
　　　　　　　　　　End If
　　　　　　　　End If
　　　　　　Next v
　　　　End If
　　Next c
　　SUBTOTALIF = dSum
　　Set rng1 = Nothing
　　Set rng2 = Nothing
End Function

'-------------------------------------------
ユーザー定義関数の入力
= SUBTOTALIF(検索範囲,合計範囲,検索条件)
セルに
=SUBTOTALIF(A3:A23,B3:B23,"a","b")
'-------------------------------------------

お礼

ありがとうございます。
完全に解決しました。非常に感謝しております。
SUBTOTALIFは汎用性のある、すごく便利な関数です。
SUMPRODUCTの使い方もすごいです。Function プロシージャはおろか、配列がどういうものか、まだよくわかっていない気がします。勉強させていただきます。

回答No.1

xls88

ユーザー定義関数そのものが良いかどうかは分かりませんが
下記で試してみてください。

Function SubIf(c)
Dim i As Long
Dim t as Variant
For i = 3 To Range("A3").End(xlDown).Row
If Rows(i).Hidden = False Then
If Cells(i, 1).Value = "a" Or Cells(i, 1).Value = "b" Then
t = t + Range("B" & i)
End If
End If
Next i
SubIf = t
End Function