vba SUMIF関数で合計を出す

同一ブック内の別シート「売上一覧」の条件付合計ですが
セルＡ～Ｖまであり、行は増えていきます。Ａ２～Ｖ２は見出し、データは３行目から入っています。

検索条件に設定し、Ｕ列の金額合計を出したいです。
検索条件は２種類あります。 下記の条件でコードをお教え下さい。

①シート「残高集計」のセルＣ９とセルＣ１４に検索条件を入力
②合計は「残高集計」のセルＢ９とセルＢ１４に表示
③シート「売上一覧」のセルＡ３～Ｖ（行は増えるので最終行を求めたい）の”Ｍ”欄に
担当者名が入っています。１つはこの「担当者」ごとの合計
④もう１つの条件は「売上一覧」の「年・月」です。セルの書式は数値になっています。他のブックへ転記するvbaも組み込まれているので、書式変更しないで、４月は「４」10月は「10」
また、日にちも１桁の場合は「0」を頭に付けず「４」「９」という状態で・・・
「売上一覧」の年はＢ列、月はＣ列に入っています
⑤合計したいのは「売上一覧」のＵ列です

以上の条件で２種類の合計金額を求めるコードをお教え下さい。

下記のコードは以前お教えいただいた物を利用して自分なりに書いてみました。
初心者なので良く解っていませんが・・・
「************」の部分にSUMIF関数を入れるのだと思いますが、構文がわからずにいます。
下記のコードを元にお助け願えないでしょうか？
また、間違っているところがあったら何が間違いなのかお教え下さい。

Sub　未収金合計()

' 変数宣言
Dim sh As Worksheet, EndRow As Long, sh1 As Worksheet, sh2 As Worksheet

' 変数shに代入
Set sh1 = Worksheets("売上一覧")
Set sh2 = Worksheets("残高集計")

With sh1

'U列の最終行の行番号を取得
EndRow = .Range("U" & Rows.Count).End(xlUp).Row

'ワークシート関数を使って「U1からUの最終行までの合計結果を残高集計シートのセルB9に代入

***********************************************

End With

End Sub

No.2 ベストアンサー 回答者： soixante 回答日時： 2015/06/02 00:41

#1です。

SUMIFSが使えないとなると、通常のエクセル関数では、複数条件を満たすレコードの数値を計算
するには、SUMPRODUCT関数を用いても出来ます。
ただし、その書式は、
=SUMPRODUCT((年の列=検索する年）*（月の列＝検索する月）*（残高の列））
となり、正直めんどくさいので、全然形は違いますが、以下のようにしてみました。

■概要
売上一覧の3行目から最終行まで、For Next でループさせています。
それぞれの行において、残高集計で検索条件として入れた年・月が合致する場合
（If 文で判定）にのみ、U列の数値を、変数 Kei に足しこんでいき、全行のループが
終了したら、その時のKei　を残高集計のB9セルに吐き出しています。

----------------------------------------------------------------------------
Sub 未収金合計月別()

' 変数宣言
Dim sh As Worksheet, EndRow As Long, sh1 As Worksheet, sh2 As Worksheet
Dim r As Integer, Nen As Integer, Tsuki As Integer　　　’年・月を入れる変数
Dim Kei As Long　　　’合計値を入れる変数

' 変数shに代入
Set sh1 = Worksheets("売上一覧")
Set sh2 = Worksheets("残高集計")

'最終行を取得
EndRow = sh1.Range("U" & Rows.Count).End(xlUp).Row

'残高集計シートで検索条件とした年・月を取得
Nen = sh2.Range("C9").Value
Tsuki = sh2.Range("D9").Value

'売上一覧シートの3行目から最終行までループ
'年・月が両方とも合致する場合のみ、U列の数値を加算してKeiへ追加

With sh1
　　　For r = 3 To EndRow
　　　　　　If .Range("B" & r).Value = Nen And .Range("C" & r).Value = Tsuki Then
　　　　　　　　　Kei = Kei + .Range("U" & r).Value
　　　　　　End If
　　　Next r
End With

sh2.Range("B9").Value = Kei 　　'Keiの値を残高集計シートのB9セルに出力

End Sub
-------------------------------------------------------------------

ワークシート関数を使ったほうが楽なケースと、そうでないケースがあります。
複数条件がごちゃごちゃ混じるケースだったりすると、上記のように、全データを
ループさせる中でIf 文による条件分岐を入れつつ、条件に合致した場合だけ数値を
拾っていくというのも一策です。

もっと複雑な条件の場合は、If文をネストさせてもいいですし、無理にワークシート関数を
使うよりも柔軟性のある設計ができることも多いです。

この回答へのお礼

有難うございました。
最初オーバーフローになりましたが、Dim r As IntegerのところをLong型にしたら動作しました。
思うとおりのものになりました。
計算＝ワークシート関数と思い込んでいましたが、拘る必要は無いのですね。if文を使う・・・勉強になりました。

遅くなりましたが、カテゴリが英語になっているのは単なる間違いです（笑）
本当に有難うございました。

お礼日時：2015/06/02 08:21

No.1 回答者： soixante 回答日時： 2015/05/30 18:14

なぜ英語カテゴリなのかは置いといて（笑。

今までの質問の流れも知りませんし、今回の質問もすべて読んで理解していないのですが、
以下の部分についてお知らせします。

＞「************」の部分にSUMIF関数を入れるのだと思いますが、構文がわからずにいます。

ワークシート関数をVBA上で使用するには、
Application.WorksheetFunction を頭に付ければよいです。

たとえば、
sh2.Range("B9").Value=Application.WorksheetFunction.Sumif(範囲、検索条件、合計範囲）
のように。

これを踏まえて。
【１】担当者ごとの集計
売上一覧のC14に集計したい担当者名を入れるということでしょうか。
ちょっと図が見えにくくて間違ってたらすみません。
たとえばなんですが。
-------------------------------------------------------------------------------
Sub　未収金合計()
' 変数宣言
Dim sh As Worksheet, EndRow As Long, sh1 As Worksheet, sh2 As Worksheet
Dim Rng1 As Range,Rng2 As Range 'セル範囲を格納する変数
Dim Tanto As String '担当者名を格納する変数
' 変数shに代入
Set sh1 = Worksheets("売上一覧")
Set sh2 = Worksheets("残高集計")
Tanto = sh2.Range("C14").Value '担当者名を取得

With sh1
'U列の最終行の行番号を取得
EndRow = .Range("U" & Rows.Count).End(xlUp).Row
’計算に使うセル範囲を格納
Set Rng1 = .Range("M2 : M" & EndRow) '担当者の入っている列
Set Rng2 = .Range("U2 : U" & EndRow)　’残高の列


'ワークシート関数を使って「U1からUの最終行までの合計結果を残高集計シートのセルB9に代入
sh2.Range("B9").Value=Application.WorksheetFunction.SumIf(Rng1,Tanto,Rng2)

End With
End Sub
-------------------------------------------------------------------------------

２つめは複数条件（年と月）がありますが、基本的は同じです。
SUMIF ではなくて、SUMIFS　を使ってみましょう。

セル範囲とか検索値を変数に格納するのは必ずしも必要ではありませんが、のちのちの可読性とか
メンテを考えるとわたしはこんなようにします。

動作確認はしていませんけどご参考まで。

この回答へのお礼

有難うございます。一つ目の担当者別は問題なく動作しました。二つ目ですが、エクセル2003を使用しているのでsumifs関数が使えません。そのあたりを検索して対応できる数式を探しています。
初心者なので変数とか格納とか一つずつやらないと覚えないので、詳しく書いて頂けて助かります。

お礼日時：2015/06/01 08:57