【VBA】SumIfとSumIfs関数を使いこなす【実務で使えるレベルになります】
Excel VBAで、「SumIf」や「SumIfs」を使う方法について、網羅的に解説していきます。SumIfやSumIfs関数は、条件一致した値の合計を算出できる便利な関数です。「WorksheetFunction」や「数式埋め込み」を使って、SumIfやSumIfsを使いこなしていきましょう。
はじめに
この記事では、VBAで「SumIf関数」と「SumIfs関数」を使う方法について、ご紹介します。
SumIfやSumIfs関数を使う方法は、「WorksheetFunction」と「数式を埋め込む方法」があります。
合計した値をVBAで使う場合は、「WorksheetFunction」が便利です。
セルに合計値をそのまま入力したいのであれば、「数式を埋め込む方法」が簡単です。
VBAで「SumIf」や「SumIfs」を使いこなしていきましょう。
では、VBAで「SumIf関数」と「SumIfs関数」を使う方法について、解説していきます。
この記事で紹介すること
- VBAで「SumIf関数」と「SumIfs関数」を使う方法
目次
WorksheetFunctionでSumIfやSumifs関数を使う
「WorksheetFunction」でSumIfやSumifs関数を使ってみます。
条件に一致した値の合計値を算出（SumIf関数）
「条件に一致した値」の「合計値」を算出できるのが、SumIf関数です。
WorksheetFunctionで「SumIf関数」を使ってみます。
WorksheetFunctionで「SumIf関数」を使う
'SumIf関数を使う
A = WorksheetFunction.SumIf(範囲, 条件, 合計範囲)
入力する引数は、関数と同じになります。
「SumIf関数」を使ってみる
Sub TEST1()
'「"B"」に一致した価格の合計
Cells(2, "D") = WorksheetFunction.SumIf(Range("A:A"), "B", Range("B:B"))
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
WorksheetFunctionで「SumIf関数」を使って、合計値を算出できました。
「SumIf関数」で「変数」や「セルの値」を参照する方法
「SumIf関数」で「変数」や「セルの値」を参照する方法について、詳細はこちらでまとめています。
実務では、変数を使ってセルの値を参照することが多いです。
複数条件に一致した値の合計値を算出（SumIfs関数）
「複数条件」に一致した値の「合計値」を算出できるのが、SumIfs関数です。
WorksheetFunctionで「SumIfs関数」を使ってみます。
WorksheetFunctionで「SumIfs関数」を使う
'SumIfs関数を使う
A = WorksheetFunction.SumIfs(合計範囲, 範囲1, 条件1, 範囲2, 条件2)
入力する引数は、セルに入力するときと同じです。
「SumIfs関数」を使ってみます。
「SumIfs関数」を使ってみる
Sub TEST2()
'商品が「B」で、支店が「大阪」の価格を合計
Range("E2") = WorksheetFunction.SumIfs(Range("C:C"), Range("A:A"), "B", Range("B:B"), "大阪")
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
WorksheetFunctionで「SumIfs関数」を使って、合計値を算出できました。
「SumIfs関数」で「変数」や「セルの値」を参照する方法
「SumIfs関数」で「変数」や「セルの値」を参照する方法について、詳細はこちらでまとめています。
実務では、変数を使ってセルの値を参照することが多いです。うまく使いこなしましょう。
ワイルドカードを使う
SumIfやSumIfs関数は、ワイルドカードを使うことができます。
ワイルドカードの種類は、2つあります。
ワイルドカードの種類
- 「*」：すべての値
- 「?」：すべての値。ただし1文字
「SumIf関数」でワイルドカードを使ってみます。
「SumIf関数」でワイルドカードを使う
「Bを含む」値の合計を算出してみます。
Sub TEST3()
'「"B"」を含む価格の合計
Cells(2, "D") = WorksheetFunction.SumIf(Range("A:A"), "*B*", Range("B:B"))
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
「SumIf関数」でワイルドカードを使って、合計値を算出できました。
「SumIfs関数」でワイルドカードを使う
次は、「SumIfs関数」でワイルドカードを使ってみます。
A列が「Bを含んで」、B列が「大阪」の合計値を算出します。
Sub TEST4()
'商品が「Bを含む」値で、支店が「大阪」の価格を合計
Range("E2") = WorksheetFunction.SumIfs(Range("C:C"), Range("A:A"), "B*", Range("B:B"), "大阪")
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
「SumIfs関数」でワイルドカードを使って合計値を算出できました。
比較演算子を使う
SumIfとSumIfs関数は、「比較演算子」も使えます。
比較演算子の種類は、次のとおりです。
比較演算子の種類
- 「">値"」 ：値より大きい
- 「"<値"」 ：値より小さい
- 「">=値"」：値以上
- 「"<=値"」：値以下
- 「"<>値"」：値以外
- 「""」 ：空白
- 「"<>"」 ：空白以外
「SumIf関数」で比較演算子を使ってみます。
「SumIf関数」で比較演算子を使う
「3以下」の値の合計値を算出してみます。
Sub TEST5()
'「3以下」の価格の合計
Cells(2, "D") = WorksheetFunction.SumIf(Range("A:A"), "<=3", Range("B:B"))
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
「SumIf関数」で比較演算子を使って合計値を算出できました。
「SumIfs関数」で比較演算子を使う
「SumIfs関数」で比較演算子を使ってみます。
「2021/8/1」～「2021/9/3」の間の合計値を算出してみます。
Sub TEST6()
'「2021/8/1」～「2021/9/3」の売上を合計
Range("D2") = WorksheetFunction.SumIfs(Range("B:B"), Range("A:A"), ">=2021/8/1", Range("A:A"), "<=2021/9/3")
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
「SumIfs関数」で比較演算子を使って合計値を算出できました。
最終行まで繰り返し使う
最終行まで繰り返し使うには、「End」で最終行を取得してループします。
「End」を使ってループする
「End」を使って最終行を取得して、ループしてみます。
Sub TEST7()
'最終行までループする
For i = 2 To Cells(Rows.Count, "D").End(xlUp).Row
With Cells(i, "E")
'合計値を算出
.Value = WorksheetFunction.SumIf(Range("A:A"), .Offset(0, -1), Range("B:B"))
End With
Next
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
「End」を使ってSumIf関数を最終行までループできました。
最終行までループする方法の詳細
「SumIf関数」を使って最終行までループする方法について、詳細な手順はこちらでまとめています。
別シートを参照する
別シートを参照するには、「Worksheets」を使います。
「別シート」を参照する
SumIf関数で、「別シート」を参照してみます。
Sub TEST8()
'別シートの合計値を算出
With Worksheets("Sheet2")
Worksheets("Sheet1").Cells(2, "A") = WorksheetFunction.SumIf(.Range("A:A"), "B", .Range("B:B"))
End With
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
Worksheetsを使って「別シート」を参照できました。
すべての別シートをループする
「For Each」やシート数を「Worksheets.Count」で取得すると、すべての別シートを参照することができます。
すべての別シートを「SumIf関数」で参照する方法について、詳細はこちらでまとめています。
別ブックを参照する
別ブックを参照するには、「Workbooks」を使います。
「別ブック」を参照する
SumIf関数で、「別ブック」を参照してみます。
Sub TEST9()
'「別ブック」を参照して条件一致の合計値を算出
With Workbooks("TEST.xlsx").Worksheets("Sheet1")
ThisWorkbook.Worksheets("Sheet1").Cells(2, "A") = WorksheetFunction.SumIf(.Range("A:A"), "B", .Range("B:B"))
End With
End Sub
「別ブック」を「同じフォルダ」に保存しています。
別ブックを参照するときのポイントは、「別ブックを開いておく」ということです。
では、VBAコードを実行してみます。
Workbooksを使って「別ブック」を参照できました。
別ブックを「開いて」参照する方法
別ブックを参照する場合は、「別ブックを開く」VBAコードを追加して、参照すると便利です。
別ブックを「開いて」参照する方法について、詳細はこちらでまとめています。
埋め込み数式でSumIfやSumifs関数を使う
埋め込み数式でSumIfやSumifs関数を使ってみます。
数式をセルに入力することで、SumIfやSumIfs関数を使うことができます。
条件に一致した値の合計値を算出（SumIf関数）
SumIf関数を埋め込んで、条件に一致した値の合計値を算出してみます。
数式を埋め込んで「SumIf関数」を使う
数式を埋め込んで「SumIf関数」を使うVBAコードは、こんな感じになります。
Sub TEST10()
'「"B"」に一致した価格の合計
Cells(2, "D") = "=SUMIF(A:A,""B"",B:B)"
Cells(2, "D").Value = Cells(2, "D").Value '値に変換
End Sub
ポイントは、「"B"」は、「""B""」のように「""」で囲むということです。
表を用意しておきます。
では、VBAコードを実行してみます。
数式を埋め込んで「SumIf関数」を使って合計値を算出できました。
「SumIf関数」で「変数」や「セル」の値を使って参照する
実務では、「変数」や「セル」の値を参照して、「SumIf関数」を使うことが多いです。
「変数」や「セル」の値を使って、「SumIf関数」を使う方法について、詳細はこちらでまとめています。
複数条件に一致した値の合計値を算出（SumIfs関数）
次は、「SumIfs関数」を使って、「複数条件」に一致した値の合計値を算出してみます。
数式を埋め込んで「SumIfs関数」を使う
数式を埋め込んで「SumIfs関数」を使うVBAコードは、こんな感じです。
Sub TEST11()
'商品が「B」で、支店が「大阪」の価格を合計
Range("E2") = "=SUMIFS(C:C,A:A,""B"",B:B,""大阪"")"
Range("E2").Value = Range("E2").Value '値に変換
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
数式を埋め込んで「SumIfs関数」を使って合計値を算出できました。
「SumIfs関数」で「変数」や「セル」の値を使って参照する
実務では、「変数」や「セル」の値を参照して、「SumIfs関数」を使うことが多いです。
「変数」や「セル」の値を使って、「SumIfs関数」を使う方法について、詳細はこちらでまとめています。
ワイルドカードを使う
数式を埋め込む方法で、SumIfやSumIfsに「ワイルドカード」を使ってみます。
ワイルドカードの種類は、2つあります。
ワイルドカードの種類
- 「*」：すべての値
- 「?」：すべての値。ただし1文字
では、埋め込みの「SumIf関数」でワイルドカードを使ってみます。
埋め込みの「SumIf関数」でワイルドカードを使う
A列に「Bを含む」値の合計を算出してみます。
Sub TEST12()
'「"B"」を含む価格の合計
Cells(2, "D") = "=SUMIF(A:A,""*B*"",B:B)"
Cells(2, "D").Value = Cells(2, "D").Value '値に変換
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
埋め込みの「SumIf関数」でワイルドカードを使って合計値を算出できました。
埋め込みの「SumIfs関数」でワイルドカードを使う
埋め込みの「SumIfs関数」でワイルドカードを使ってみます。
A列に「Bを含む」セルで、B列が「大阪」であるセルの合計値を算出します。
Sub TEST13()
'商品が「Bを含む」値で、支店が「大阪」の価格を合計
Range("E2") = "=SUMIFS(C:C,A:A,""B*"",B:B,""大阪"")"
Range("E2").Value = Range("E2").Value '値に変換
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
埋め込みの「SumIfs関数」でワイルドカードを使って合計値を算出できました。
比較演算子を使う
SumIfやSumIfsで「比較演算子」を使ってみます。
比較演算子の種類は、次のとおりです。
比較演算子の種類
- 「">値"」 ：値より大きい
- 「"<値"」 ：値より小さい
- 「">=値"」：値以上
- 「"<=値"」：値以下
- 「"<>値"」：値以外
- 「""」 ：空白
- 「"<>"」 ：空白以外
埋め込みの「SumIf関数」で比較演算子を使ってみます。
埋め込みの「SumIf関数」で比較演算子を使う
A列が「3以下」の値の合計値を算出してみます。
Sub TEST14()
'「3以下」の価格の合計
Cells(2, "D") = "=SUMIF(A:A,""<=3"",B:B)"
Cells(2, "D").Value = Cells(2, "D").Value '値に変換
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
埋め込みの「SumIf関数」で比較演算子を使って合計値を算出できました。
埋め込みの「SumIfs関数」で比較演算子を使う
次は、埋め込みの「SumIfs関数」で比較演算子を使ってみます。
「2021/8/1」～「2021/9/3」の値の合計値を算出してみます。
Sub TEST15()
'「2021/8/1」～「2021/9/3」の売上を合計
Range("D2") = "=SUMIFS(B:B,A:A,"">=2021/8/1"",A:A,""<=2021/9/3"")"
Range("D2").Value = Range("D2").Value '値に変換
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
埋め込みの「SumIfs関数」で比較演算子を使って合計値を算出できました。
最終行までの合計値を算出する
SumIfを埋め込む方法で、最終行までの合計値を算出してみます。
最終行まで合計値を算出する
数式埋め込みの場合は、ループすることなく、一括で数式を入力すると、最終行まで計算できます。
Sub TEST16()
'最終行を取得
A = Cells(Rows.Count, "D").End(xlUp).Row
'数式を一括で埋め込む
Range("E2:E" & A) = "=SUMIF(A:A,D2,B:B)"
Range("E2:E" & A).Value = Range("E2:E" & A).Value '値に変換
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
Endを使って最終行まで合計値を算出できました。
最終行までの合計値を算出する方法の手順
最終行までの合計値を算出する方法の手順について、詳細はこちらでまとめています。
順を追って解説しています。
別シートを参照する
数式埋め込みの「SumIf関数」で、「別シート」を参照してみます。
「別シート」を参照する
数式埋め込みのSumIfで、「別シート」を参照するVBAコードは、こんな感じになります。
Sub TEST17()
'別シートの合計値を算出
Worksheets("Sheet1").Cells(2, "A") = "=SUMIF(Sheet2!A:A,""B"",Sheet2!B:B)"
Worksheets("Sheet1").Cells(2, "A").Value = Worksheets("Sheet1").Cells(2, "A").Value '値に変換
End Sub
「シート名!」までを入力しています。
一旦、セルに数式を入力して、コピーして使うと簡単に入力できます。
別シートを用意しておきます。
では、VBAコードを実行してみます。
埋め込み数式で「別シート」を参照できました。
すべてのシートを参照する
実務では1つのシートだけではなく、複数のシートを参照することが多いです。
SumIf関数の埋め込み数式で、すべてのシートをループして参照することができます。
埋め込み数式ですべてのシートをループして参照する方法について、詳細はこちらでまとめています。
別ブックを参照する
数式埋め込みの「SumIf関数」で、「別ブック」を参照してみます。
「別ブック」を参照する
「別ブック」を参照するVBAコードは、こんな感じです。
Sub TEST18()
'「別ブック」を参照して条件一致の合計値を算出
With ThisWorkbook.Worksheets("Sheet1")
.Cells(2, "A") = "=SUMIF([TEST.xlsx]Sheet1!A:A,""B"",[TEST.xlsx]Sheet1!B:B)"
.Cells(2, "A").Value = .Cells(2, "A").Value '値に変換
End With
End Sub
「[TEST.xlsx]Sheet1!A:A」というように、「ブック名」までを入力します。
一旦、セルに入力して、コピーして使うと簡単に入力することができます。
同じフォルダ内に別ブックを保存しています。
SumIf関数で別ブックを参照するときのポイントは、「別ブックを開いておく」、ということです。
別ブックを開いておきます。
では、VBAコードを実行してみます。
埋め込み数式で「別ブック」を参照できました。
別ブックを「開いて」参照する方法と「閉じたまま」参照する
別ブックを参照する際は、「ブックを開く」VBAコードを追加すると便利です。
「Sum関数」と「If関数」を組み合わせると閉じたまま参照することもできます。
別ブックを「開いて」参照する方法と「閉じたまま」参照する方法について、詳細はこちらでまとめています。
Dictionaryで高速化する
SumIfやSumIfs関数は、正直遅いです。
SumIfやSumIfsの機能を高速化するには、「Dictionary」が使えます。
SumIf関数を高速化する
「SumIf関数」を「Dictionary」で高速化してみます。
「SumIf関数」を高速化する
「SumIf関数」を高速化するVBAコードです。
手順は、
- 検索元と検索先の値を配列に入力
- 検索元を辞書に登録
- 検索先の値をループして辞書に合計値を加算していく
- 合計値の結果をセルに入力
という感じです。
Sub TEST19()
'辞書を作成
Dim A
Set A = CreateObject("Scripting.Dictionary")
Dim B, C
B = Range("A2:A4") '検索元の値を配列に入力
C = Range("D2:E10") '検索先の値を配列に入力
'検索元をループ
For i = 1 To UBound(B, 1)
'辞書に登録する
A.Add B(i, 1), 0
Next
'検索先をループ
For i = 1 To UBound(C, 1)
'辞書に登録されている場合
If A.exists(C(i, 1)) = True Then
'合計値を算出
A(C(i, 1)) = A(C(i, 1)) + C(i, 2)
End If
Next
'セルに配列を入力
Range("B2").Resize(UBound(A.items) + 1) = WorksheetFunction.Transpose(A.items)
End Sub
大量データを用意しておきます。
では、VBAコードを実行してみます。
SumIf関数で、大量データの合計値を算出できました。
かかった時間は、「0.19 秒」となりました。
SumIf関数をWorksheetFunctionや数式埋め込みの方法でやると、「20 秒以上」かかってしまうので、かなり高速に計算できます。
「Dictionary」でSumIfを高速化する手順の解説
「Dictionary」を使ってSumIfの機能を高速化する手順について、詳細はこちらで解説しています。
SumIfs関数を高速化する
「SumIfs関数」を「Dictionary」を使って高速化してみます。
「SumIfs関数」を高速化する
「SumIfs関数」を高速化するVBAコードです。
手順は、
- 検索元と検索先の値を配列に入力
- 検索元を辞書に登録（区切り文字で結合する）
- 検索先の値をループして辞書に合計値を加算していく
- 合計値の結果をセルに入力
という感じです。
Sub TEST20()
'辞書を作成
Dim A
Set A = CreateObject("Scripting.Dictionary")
Dim B
B = Range("A2:B4") '参照元のデータ
'辞書に登録していく
For i = 1 To UBound(B, 1)
'「商品」と「支店」を「"/"」で区切って登録
A.Add B(i, 1) & "/" & B(i, 2), 0
Next
Dim C
C = Range("E2:G10") '参照先のデータ
'参照先のループ
For i = 1 To UBound(C, 1)
'既に登録されている場合
If A.exists(C(i, 1) & "/" & C(i, 2)) = True Then
'合計値を加算していく
A(C(i, 1) & "/" & C(i, 2)) = A(C(i, 1) & "/" & C(i, 2)) + C(i, 3)
End If
Next
'セルに配列を入力
Range("C2").Resize(UBound(A.items) + 1) = WorksheetFunction.Transpose(A.items)
End Sub
大量データを用意しておきます。
では、VBAコードを実行してみます。
「SumIfs関数」を大量データで条件一致する合計値を算出できました。
かかった時間は、「0.23 秒」です。
SumIfs関数を、WorksheetFunctionや数式埋め込みの方法で使うと、「17 秒以上」かかってしまうので、かなり高速に合計値を計算できています。
「Dictionary」でSumIfsを高速化する手順の解説
「Dictionary」を使ってSumIfsの機能を高速化する手順について、詳細はこちらで解説しています。
おわりに
この記事では、VBAで「SumIf関数」と「SumIfs関数」を使う方法について、ご紹介しました。
SumIfやSumIfs関数を使う方法は、「WorksheetFunction」と「数式を埋め込む方法」があります。
合計した値をVBAで使う場合は、「WorksheetFunction」が便利です。
セルに合計値をそのまま入力したいのであれば、「数式を埋め込む方法」が簡単です。
VBAで「SumIf」や「SumIfs」を使いこなしていきましょう。
参考になればと思います。最後までご覧くださいまして、ありがとうございました。
関連する記事から探す