EXCEL VBA 条件に合う集計・一致する集計・合計の集計「Sumif・Sumifs」の使い方
EXCEL VBA 条件に合う集計・一致する集計・合計の集計「Sumif・Sumifs」関数の使い方
EXCELでデータ一覧を作成した際に、ある条件に合うデータを集計したり、指定したデータに一致するデータを集計する際に、便利な関数が、「Sumif・Sumifs」関数になります。「Sumif・Sumifs」関数を利用する事で、簡単なステップで指定した検索条件に合うデータを集計する事が出来ます。それでは、「Sumif・Sumifs」関数の使用方法をサンプルプログラムを交えて説明いたします。
●【COUNTIFS関数の使い方については、下記の情報を参照して下さい】
EXCEL VBA COUNTIFS関数 複数条件に一致するデータのセル数をカウントする。（条件に合うデータの件数集計・ワークシート関数）
WorksheetFunction.SumIf(検索範囲,検索条件,合計範囲)
※指定された検索条件に一致するセルの値を合計します。
使用例： Ans = WorksheetFunction.SumIf(Range(“E2：E5”), “男”, Range(“F2:F5”))
説明：検索範囲のセルE2～E5の中に、検索条件（性別）の”男”に一致する同じ行のセルF2～F5のデータを集計します。Ansには、1,300,000が代入されます。
セル範囲内で、検索条件を満たすセルの値の合計を返します。（Sumif）
下記のプログラムは、Sumifを使ったサンプルプログラムになります。下表のデータ一覧から検索条件として「役職名」を入力します。入力した役職名の条件を満たす「支給合計」の合計金額をメッセージに表示させます。
（※下表のデータはサンプルデータです。）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
Sub Syukei01() 'sheet1 Dim Gokei, lRow As Long Dim Hani01, Hani02 As Range Dim Kensaku As String lRow = Cells(Rows.Count, "D").End(xlUp).Row 'D列の最終行 Do Kensaku = InputBox("役職名を入力して下さい") Loop Until Kensaku <> "" '役職名が入力されたか確認 Set Hani01 = Range("D2:D" & lRow) '役職のD列を「Hani01」にセット Set Hani02 = Range("L2:L" & lRow) '支給合計のL列を「Hani02」にセット Gokei = WorksheetFunction.SumIf(Hani01, Kensaku, Hani02) '検索範囲は変数Hani01、検索条件はstr、合計反映はHani02 MsgBox Kensaku & "職の支給合計：" & Format(Gokei, "#,###") & "円" '合計値を表示 End Sub |
セル範囲内で、複数検索条件を満たすセルの値の合計を返します。（Sumifs）
WorksheetFunction.SumIfs(合計範囲,検索範囲1,検索条件1,検索範囲2,検索条件２,‥‥)
※指定された複数検索条件に一致するセルの値を合計します。
使用例： Ans = WorksheetFunction.SumIfs(Range(“F2:F5”),Range(“E2：E5”), “男”,Range(“B2：B5”), “<103”)
説明：検索範囲のセルE2～E5の中に、検索条件（性別）の”男”かつ、検索範囲のセルB2~B5の中の社員番号が”103”未満に一致する同じ行のセルF2～F５のデータを集計します。Ansには、750,000が代入されます。
（※下表のデータはサンプルデータです。）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
Sub Syukei02() Dim Gokei, lRow As Long Dim Hani01, Hani02, Hani03 As Range Dim Kensaku01, Kensaku02 As String lRow = Cells(Rows.Count, "D").End(xlUp).Row 'D列の最終行 Do Kensaku01 = InputBox("役職名を入力して下さい") Kensaku02 = InputBox("性別を入して下さい") Loop Until Kensaku01 <> "" And Kensaku02 <> "" '役職名・性別が入力されたか確認 Set Hani01 = Range("D2:D" & lRow) '役職のD列を「Hani01」にセット Set Hani02 = Range("E2:E" & lRow) '性別のE列を「Hani02」にセット Set Hani03 = Range("L2:L" & lRow) '支給合計のL列を「Hani03」にセット Gokei = WorksheetFunction.SumIfs(Hani03, Hani01, Kensaku01, Hani02, Kensaku02) '検索範囲は変数Hani01、検索条件はstr、合計反映はHani02 MsgBox Kensaku01 & "職の" & Kensaku02 & "性の支給合計：" & Format(Gokei, "#,###") & "円" '合計値を表示 End Sub |
セル範囲内で、指定する項目一覧に対して検索条件を満たすセルの値を合計を返します。（Sumif）
（※下表のデータはサンプルデータです。）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Sub Syukei03() Dim Gokei, lRow, mRow As Long Dim Hani01, Hani02 As Range lRow = Cells(Rows.Count, "D").End(xlUp).Row 'D列の最終行 Set Hani01 = Range("D2:D" & lRow) '役職のD列を「Hani01」にセット Set Hani02 = Range("L2:L" & lRow) '支給合計のL列を「Hani02」にセット mRow = 2 Do Until Cells(mRow, "N") = "" 'N行上から文字列がなくなるまで繰り返す。 Gokei = WorksheetFunction.SumIf(Hani01, Cells(mRow, "N"), Hani02) '検索範囲は変数Hani01、検索条件はstr、合計反映はHani02 Cells(mRow, "O") = Gokei mRow = mRow + 1 Loop End Sub |
（画面クリックして拡大）
セル範囲内で、指定する項目一覧に対して検索条件を満たすセルの値を合計を返します。（Sumif）別シート合計表示
（※下表のデータはサンプルデータです。）
【下記のプログラムを実行準備】① シート名を「データ」 ・・・給与データが一覧表示されている。
② シート名を「集計表」 ・・・集計表示されている。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
' '******** AKIRA55.COM ******* https://akira55.com/sumifs/ ' Sub Syukei04() Dim ws01, ws02 As Worksheet Dim lRow, mRow, I As Long Dim Hani01, Hani02 As Variant Set ws01 = Worksheets("データ") 'ワークシート名をセット Set ws02 = Worksheets("集計表") 'ワークシート名をセット lRow = ws01.Cells(Rows.Count, "D").End(xlUp).Row 'ワークシート（データ一覧）のD列の最終行 Set Hani01 = ws01.Range("D2:D" & lRow) '役職のD列を「Hani01」にセット mRow = 2 Do Until ws02.Cells(mRow, "B") = "" 'B列（役職名）を1行ずつづれて役職名がなくなるまで繰り返す。 For I = 0 To 6 'ワークシート（集計表）C列～I列まで順番に繰り返す。 Set Hani02 = Range(ws01.Cells(2, 6 + I), ws01.Cells(lRow, 6 + I)) 'F列⇒L列の順番にセット ws02.Cells(mRow, 3 + I) = WorksheetFunction.SumIf(Hani01, ws02.Cells(mRow, "B"), Hani02) '役職名毎に集計結果を表に記入します。 Next I mRow = mRow + 1 Loop End Sub ’ |
（画面クリックして拡大）
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。