
こんばんは。お願いします。
まず、セルA2、B2、C2にそれぞれフィールド名"あ"、"い"、"う"があるとします。
この2列目以下にオートフィルターを使います。
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ベストアンサー
- 回答日時:
こんにちは。
まず、ユーザー定義関数のコードは、まだ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
- 回答日時:
ユーザー定義関数そのものが良いかどうかは分かりませんが
下記で試してみてください。
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
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Excel(エクセル) VBA オリジナル関数で選択セルの合計を作成したい 3 2023/03/19 19:45
- Visual Basic(VBA) 最終列の右へSUM関数を作成するため下記コードを実行しましたが、最終列「10月28日」が上書きされて 3 2022/12/05 20:32
- Excel(エクセル) VBAで組み合わせ算出やCOUNTIFSの処理を高速化したいです。 4 2022/04/07 02:38
- Visual Basic(VBA) ファイル全てを .xlsm に変更したところ、プログラムが途中で落ちてしまっています 17 2022/12/07 12:03
- Visual Basic(VBA) Excel のユーザー定義関数でソルバーが動作しない 1 2022/09/05 19:51
- Visual Basic(VBA) エクセルのマクロで対象ごとにシート分けしてその内容をセルに書き込みたい 9 2022/08/24 13:23
- Visual Basic(VBA) まとめシートから集計シートへA列のコードが一致したら1行コピーするマクロをネット上で見つけました。こ 1 2022/08/30 14:11
- Excel(エクセル) B列に文字がはいったらA列に数字が入るマクロードを完成させたい 4 2023/04/21 01:58
- Visual Basic(VBA) 列と行の名前(重複あり)が交差するセルに、データを入力したい 2 2022/06/25 22:42
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
データ抽出後の個数カウント。
-
EXCELでの計算式を教えてください
-
エクセルで、現住所の空欄セル...
-
行番号の文字の色が青色の理由?
-
エクセルでフィルターをかけて...
-
Excel関数、何がいけないのかわ...
-
=SUBTOTAL に =COUNTIF の機能...
-
Excelの特定のセル内で“X”文字...
-
コードへ追記したら、特定のシ...
-
1行のVBAコードでフィルタの...
-
Excel 対象行のみ別シートに表...
-
エクセルで、このようなセル検...
-
○行おきにデータを読み込む
-
Excelのオートフィルタについて
-
EXCELのオートフィルタ後の入力
-
Excel VBAに詳しい人に質問です。
-
Excel2010 フィルタで抽出できない
-
Excelでオートフィルタ時に交互...
-
エクセルについて。 ソートで絞...
-
エクセルオートフィルタで余計...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルオートフィルタで余計...
-
=SUBTOTAL に =COUNTIF の機能...
-
行番号の文字の色が青色の理由?
-
Excel関数、何がいけないのかわ...
-
教えて下さい!関数SUBTOTALとC...
-
色フィルターをかけた状態で、...
-
エクセルの計算表の下向き三角...
-
オートフィルタで抽出したデー...
-
エクセルで、桁数の異なるデー...
-
エクセルのフィルタをかけると...
-
オートフィルタをかけた表に一...
-
Excel2010 フィルタで抽出できない
-
エクセルのフィルタ リスト範...
-
エクセル:色の付いたデータを...
-
excelで奇数の行のみ削除したい
-
エクセルでのオートフィルタオ...
-
Excelのセルのデータ:年...
-
エクセルについて。 ソートで絞...
-
Excelでオートフィルタ時に交互...
-
5の倍数の日付だけを抽出したい
おすすめ情報